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_36 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 1143: l_party_contains_str VARCHAR2(32000);

1139: p_party_max_score NUMBER,
1140: p_denorm_max_score NUMBER,
1141: p_non_denorm_max_score NUMBER,
1142: p_threshold NUMBER) RETURN VARCHAR2 IS
1143: l_party_contains_str VARCHAR2(32000);
1144: l_party_filter VARCHAR2(1) := null;
1145: l_prim_temp VARCHAR2(4000);
1146: l_denorm_str VARCHAR2(4000);
1147: BEGIN

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

1183:
1184: IF l_denorm_str IS NOT NULL THEN
1185: IF (p_non_denorm_max_score+40)>=p_threshold THEN
1186: l_denorm_str := NULL;
1187: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1188: ELSIF (p_non_denorm_max_score+p_denorm_max_score+40)>=p_threshold THEN
1189: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1190: l_denorm_str := NULL;
1191: END IF;

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

1185: IF (p_non_denorm_max_score+40)>=p_threshold THEN
1186: l_denorm_str := NULL;
1187: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1188: ELSIF (p_non_denorm_max_score+p_denorm_max_score+40)>=p_threshold THEN
1189: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1190: l_denorm_str := NULL;
1191: END IF;
1192: ELSE
1193: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

1189: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1190: l_denorm_str := NULL;
1191: END IF;
1192: ELSE
1193: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1194: END IF;
1195:
1196: -- Setup query string for PARTY_NUMBER
1197: l_prim_temp := null;

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

1200:
1201: IF l_denorm_str IS NOT NULL THEN
1202: IF (p_non_denorm_max_score+80)>=p_threshold THEN
1203: l_denorm_str := NULL;
1204: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1205: ELSIF (p_non_denorm_max_score+p_denorm_max_score+80)>=p_threshold THEN
1206: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1207: l_denorm_str := NULL;
1208: END IF;

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

1202: IF (p_non_denorm_max_score+80)>=p_threshold THEN
1203: l_denorm_str := NULL;
1204: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1205: ELSIF (p_non_denorm_max_score+p_denorm_max_score+80)>=p_threshold THEN
1206: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1207: l_denorm_str := NULL;
1208: END IF;
1209: ELSE
1210: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

1206: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1207: l_denorm_str := NULL;
1208: END IF;
1209: ELSE
1210: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1211: END IF;
1212: IF lengthb(l_party_contains_str) > 4000 THEN
1213: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1214: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');

Line 1212: IF lengthb(l_party_contains_str) > 4000 THEN

1208: END IF;
1209: ELSE
1210: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1211: END IF;
1212: IF lengthb(l_party_contains_str) > 4000 THEN
1213: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1214: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1215: FND_MSG_PUB.ADD;
1216: RAISE FND_API.G_EXC_ERROR;

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

1214: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1215: FND_MSG_PUB.ADD;
1216: RAISE FND_API.G_EXC_ERROR;
1217: END IF;
1218: IF (p_match_str = ' AND ' OR p_party_max_score 1219: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1220: 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);
1221: END IF;
1222: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

1216: RAISE FND_API.G_EXC_ERROR;
1217: END IF;
1218: IF (p_match_str = ' AND ' OR p_party_max_score 1219: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1220: 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);
1221: END IF;
1222: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1223: 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);
1224: END IF;

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

1219: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1220: 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);
1221: END IF;
1222: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1223: 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);
1224: END IF;
1225: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1226: 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);
1227: END IF;

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

1224: END IF;
1225: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1226: 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);
1227: END IF;
1228: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1229: ELSE
1230: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1231: 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);
1232: 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 1231: 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);

1227: END IF;
1228: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1229: ELSE
1230: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1231: 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);
1232: 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);
1233: END IF;
1234: RETURN l_party_contains_str;
1235: END IF;

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

1228: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1229: ELSE
1230: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1231: 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);
1232: 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);
1233: END IF;
1234: RETURN l_party_contains_str;
1235: END IF;
1236: END;

Line 1234: RETURN l_party_contains_str;

1230: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1231: 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);
1232: 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);
1233: END IF;
1234: RETURN l_party_contains_str;
1235: END IF;
1236: END;
1237: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1238: l_contains_str VARCHAR2(32000);

Line 1238: l_contains_str VARCHAR2(32000);

1234: RETURN l_party_contains_str;
1235: END IF;
1236: END;
1237: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1238: l_contains_str VARCHAR2(32000);
1239: l_contains_str_temp VARCHAR2(32000);
1240: l_den_contains_str VARCHAR2(32000);
1241: l_den_contains_str_temp VARCHAR2(32000);
1242: l_filter_str VARCHAR2(4000) := null;

Line 1239: l_contains_str_temp VARCHAR2(32000);

1235: END IF;
1236: END;
1237: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1238: l_contains_str VARCHAR2(32000);
1239: l_contains_str_temp VARCHAR2(32000);
1240: l_den_contains_str VARCHAR2(32000);
1241: l_den_contains_str_temp VARCHAR2(32000);
1242: l_filter_str VARCHAR2(4000) := null;
1243: l_prim_temp VARCHAR2(4000) := null;

Line 1240: l_den_contains_str VARCHAR2(32000);

1236: END;
1237: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1238: l_contains_str VARCHAR2(32000);
1239: l_contains_str_temp VARCHAR2(32000);
1240: l_den_contains_str VARCHAR2(32000);
1241: l_den_contains_str_temp VARCHAR2(32000);
1242: l_filter_str VARCHAR2(4000) := null;
1243: l_prim_temp VARCHAR2(4000) := null;
1244: l_prim_temp_den VARCHAR2(4000) := null;

Line 1241: l_den_contains_str_temp VARCHAR2(32000);

1237: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1238: l_contains_str VARCHAR2(32000);
1239: l_contains_str_temp VARCHAR2(32000);
1240: l_den_contains_str VARCHAR2(32000);
1241: l_den_contains_str_temp VARCHAR2(32000);
1242: l_filter_str VARCHAR2(4000) := null;
1243: l_prim_temp VARCHAR2(4000) := null;
1244: l_prim_temp_den VARCHAR2(4000) := null;
1245: BEGIN

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

1247: 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);
1248: END IF;
1249: x_denorm_str := NULL;
1250:
1251: -- Dynamic setup of party site contains str
1252: --
1253: -- For each primary transformation add to intermedia query if it
1254: -- is not null
1255: FOR I IN 1..g_party_site_stage_list.COUNT LOOP

Line 1256: l_contains_str_temp := null;

1252: --
1253: -- For each primary transformation add to intermedia query if it
1254: -- is not null
1255: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1256: l_contains_str_temp := null;
1257: l_den_contains_str_temp := null;
1258: l_filter_str := null;
1259:
1260: -- Setup query string for ADDRESS

Line 1257: l_den_contains_str_temp := null;

1253: -- For each primary transformation add to intermedia query if it
1254: -- is not null
1255: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1256: l_contains_str_temp := null;
1257: l_den_contains_str_temp := null;
1258: l_filter_str := null;
1259:
1260: -- Setup query string for ADDRESS
1261: l_prim_temp := null;

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

1261: l_prim_temp := null;
1262: l_prim_temp_den := null;
1263: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRADDRESS
1264: g_party_site_stage_list(I).TX4,'A27',l_prim_temp);
1265: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1266:
1267: -- Setup query string for CITY
1268: l_prim_temp := null;
1269: l_prim_temp_den := null;

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

1270: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1271: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1272: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1273: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1274: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1275: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1276:
1277: -- Setup query string for POSTAL_CODE
1278: l_prim_temp := null;

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

1271: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1272: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1273: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1274: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1275: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1276:
1277: -- Setup query string for POSTAL_CODE
1278: l_prim_temp := null;
1279: l_prim_temp_den := null;

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

1280: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1281: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1282: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1283: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1284: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1285: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1286:
1287: -- Setup query string for PROVINCE
1288: l_prim_temp := null;

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

1281: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1282: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1283: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1284: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1285: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1286:
1287: -- Setup query string for PROVINCE
1288: l_prim_temp := null;
1289: l_prim_temp_den := null;

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

1290: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1291: g_party_site_stage_list(I).TX13,'A31',l_prim_temp);
1292: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1293: g_party_site_stage_list(I).TX13,'D_PS',l_prim_temp_den);
1294: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1295: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1296:
1297: -- Setup query string for STATE
1298: l_prim_temp := null;

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

1291: g_party_site_stage_list(I).TX13,'A31',l_prim_temp);
1292: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1293: g_party_site_stage_list(I).TX13,'D_PS',l_prim_temp_den);
1294: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1295: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1296:
1297: -- Setup query string for STATE
1298: l_prim_temp := null;
1299: l_prim_temp_den := null;

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

1300: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1301: g_party_site_stage_list(I).TX15,'A32',l_prim_temp);
1302: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1303: g_party_site_stage_list(I).TX15,'D_PS',l_prim_temp_den);
1304: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1305: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1306:
1307: -- Setup query string for COUNTY
1308: l_prim_temp := null;

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

1301: g_party_site_stage_list(I).TX15,'A32',l_prim_temp);
1302: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1303: g_party_site_stage_list(I).TX15,'D_PS',l_prim_temp_den);
1304: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1305: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1306:
1307: -- Setup query string for COUNTY
1308: l_prim_temp := null;
1309: l_prim_temp_den := null;

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

1310: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1311: g_party_site_stage_list(I).TX21,'A35',l_prim_temp);
1312: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1313: g_party_site_stage_list(I).TX21,'D_PS',l_prim_temp_den);
1314: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1315: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1316:
1317: -- Setup query string for COUNTRY
1318: l_prim_temp := null;

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

1311: g_party_site_stage_list(I).TX21,'A35',l_prim_temp);
1312: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1313: g_party_site_stage_list(I).TX21,'D_PS',l_prim_temp_den);
1314: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1315: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1316:
1317: -- Setup query string for COUNTRY
1318: l_prim_temp := null;
1319: l_prim_temp_den := null;

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

1320: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1321: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
1322: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1323: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1324: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1325: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1326:
1327: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1328: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);

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

1321: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
1322: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1323: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1324: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1325: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1326:
1327: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1328: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1329: END LOOP;

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

1323: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1324: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1325: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1326:
1327: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1328: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1329: END LOOP;
1330: -- Add the search criteria to query string
1331: IF lengthb(l_contains_str) > 4000 THEN

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

1324: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1325: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1326:
1327: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1328: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1329: END LOOP;
1330: -- Add the search criteria to query string
1331: IF lengthb(l_contains_str) > 4000 THEN
1332: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');

Line 1331: IF lengthb(l_contains_str) > 4000 THEN

1327: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1328: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1329: END LOOP;
1330: -- Add the search criteria to query string
1331: IF lengthb(l_contains_str) > 4000 THEN
1332: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1333: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1334: FND_MSG_PUB.ADD;
1335: RAISE FND_API.G_EXC_ERROR;

Line 1337: x_denorm_str := l_den_contains_str;

1333: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1334: FND_MSG_PUB.ADD;
1335: RAISE FND_API.G_EXC_ERROR;
1336: END IF;
1337: x_denorm_str := l_den_contains_str;
1338: RETURN l_contains_str;
1339: END;
1340:
1341: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS

Line 1338: RETURN l_contains_str;

1334: FND_MSG_PUB.ADD;
1335: RAISE FND_API.G_EXC_ERROR;
1336: END IF;
1337: x_denorm_str := l_den_contains_str;
1338: RETURN l_contains_str;
1339: END;
1340:
1341: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1342: l_contains_str VARCHAR2(32000);

Line 1342: l_contains_str VARCHAR2(32000);

1338: RETURN l_contains_str;
1339: END;
1340:
1341: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1342: l_contains_str VARCHAR2(32000);
1343: l_contains_str_temp VARCHAR2(32000);
1344: l_den_contains_str VARCHAR2(32000);
1345: l_den_contains_str_temp VARCHAR2(32000);
1346: l_filter_str VARCHAR2(4000) := null;

Line 1343: l_contains_str_temp VARCHAR2(32000);

1339: END;
1340:
1341: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1342: l_contains_str VARCHAR2(32000);
1343: l_contains_str_temp VARCHAR2(32000);
1344: l_den_contains_str VARCHAR2(32000);
1345: l_den_contains_str_temp VARCHAR2(32000);
1346: l_filter_str VARCHAR2(4000) := null;
1347: l_prim_temp VARCHAR2(4000) := null;

Line 1344: l_den_contains_str VARCHAR2(32000);

1340:
1341: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1342: l_contains_str VARCHAR2(32000);
1343: l_contains_str_temp VARCHAR2(32000);
1344: l_den_contains_str VARCHAR2(32000);
1345: l_den_contains_str_temp VARCHAR2(32000);
1346: l_filter_str VARCHAR2(4000) := null;
1347: l_prim_temp VARCHAR2(4000) := null;
1348: l_prim_temp_den VARCHAR2(4000) := null;

Line 1345: l_den_contains_str_temp VARCHAR2(32000);

1341: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1342: l_contains_str VARCHAR2(32000);
1343: l_contains_str_temp VARCHAR2(32000);
1344: l_den_contains_str VARCHAR2(32000);
1345: l_den_contains_str_temp VARCHAR2(32000);
1346: l_filter_str VARCHAR2(4000) := null;
1347: l_prim_temp VARCHAR2(4000) := null;
1348: l_prim_temp_den VARCHAR2(4000) := null;
1349: BEGIN

Line 1358: l_contains_str VARCHAR2(32000);

1354: RETURN NULL;
1355: END;
1356:
1357: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1358: l_contains_str VARCHAR2(32000);
1359: l_contains_str_temp VARCHAR2(32000);
1360: l_den_contains_str VARCHAR2(32000);
1361: l_den_contains_str_temp VARCHAR2(32000);
1362: l_filter_str VARCHAR2(4000) := null;

Line 1359: l_contains_str_temp VARCHAR2(32000);

1355: END;
1356:
1357: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1358: l_contains_str VARCHAR2(32000);
1359: l_contains_str_temp VARCHAR2(32000);
1360: l_den_contains_str VARCHAR2(32000);
1361: l_den_contains_str_temp VARCHAR2(32000);
1362: l_filter_str VARCHAR2(4000) := null;
1363: l_prim_temp VARCHAR2(4000) := null;

Line 1360: l_den_contains_str VARCHAR2(32000);

1356:
1357: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1358: l_contains_str VARCHAR2(32000);
1359: l_contains_str_temp VARCHAR2(32000);
1360: l_den_contains_str VARCHAR2(32000);
1361: l_den_contains_str_temp VARCHAR2(32000);
1362: l_filter_str VARCHAR2(4000) := null;
1363: l_prim_temp VARCHAR2(4000) := null;
1364: l_prim_temp_den VARCHAR2(4000) := null;

Line 1361: l_den_contains_str_temp VARCHAR2(32000);

1357: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1358: l_contains_str VARCHAR2(32000);
1359: l_contains_str_temp VARCHAR2(32000);
1360: l_den_contains_str VARCHAR2(32000);
1361: l_den_contains_str_temp VARCHAR2(32000);
1362: l_filter_str VARCHAR2(4000) := null;
1363: l_prim_temp VARCHAR2(4000) := null;
1364: l_prim_temp_den VARCHAR2(4000) := null;
1365: TYPE CONTACT_PT_REC_TYPE IS RECORD (

Line 1384: p_party_contains_str VARCHAR2,

1380: p_dup_party_id NUMBER,
1381: p_restrict_sql VARCHAR2,
1382: p_match_str VARCHAR2,
1383: p_search_merged VARCHAR2,
1384: p_party_contains_str VARCHAR2,
1385: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1386: l_sqlstr VARCHAR2(4000);
1387: l_search_merged VARCHAR2(1);
1388: BEGIN

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

1397: END IF;
1398: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1399: 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);
1400: END IF;
1401: 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
1402: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1403: 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);
1404: END IF;
1405: OPEN x_cursor FOR

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

1435: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1436: l_sqlstr := l_sqlstr || ' (TX36 like :TX36||'' %'' ';
1437: l_sqlstr := l_sqlstr || ' )) ';
1438: END IF;
1439: 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
1440: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1441: 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);
1442: END IF;
1443: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';

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

1437: l_sqlstr := l_sqlstr || ' )) ';
1438: END IF;
1439: 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
1440: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1441: 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);
1442: END IF;
1443: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1444: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX10||'' %'' ';
1445: l_sqlstr := l_sqlstr || ' )) ';

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

1473: END IF;
1474: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1475: 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);
1476: END IF;
1477: 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
1478: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1479: 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);
1480: END IF;
1481: 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 1479: 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);

1475: 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);
1476: END IF;
1477: 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
1478: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1479: 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);
1480: END IF;
1481: 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
1482: ,g_party_stage_rec.TX34
1483: ,g_party_stage_rec.TX34

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

1509: ,g_party_stage_rec.TX40
1510: ,g_party_stage_rec.TX36
1511: ,g_party_stage_rec.TX36
1512: ;
1513: 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
1514: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1515: 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);
1516: END IF;
1517: 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 1515: 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);

1511: ,g_party_stage_rec.TX36
1512: ;
1513: 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
1514: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1515: 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);
1516: END IF;
1517: 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
1518: ,g_party_site_stage_list(1).TX10
1519: ,g_party_site_stage_list(1).TX10

Line 1542: p_contains_str VARCHAR2,

1538: END;
1539: PROCEDURE open_party_cursor(
1540: p_dup_party_id NUMBER,
1541: p_restrict_sql VARCHAR2,
1542: p_contains_str VARCHAR2,
1543: p_search_ctx_id NUMBER,
1544: p_match_str VARCHAR2,
1545: p_search_merged VARCHAR2,
1546: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS

Line 1561: IF p_contains_str IS NULL THEN

1557: l_search_merged := 'N';
1558: ELSE
1559: l_search_merged := p_search_merged;
1560: END IF;
1561: IF p_contains_str IS NULL THEN
1562: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1563: 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);
1564: END IF;
1565: OPEN x_cursor FOR

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

1559: l_search_merged := p_search_merged;
1560: END IF;
1561: IF p_contains_str IS NULL THEN
1562: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1563: 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);
1564: END IF;
1565: OPEN x_cursor FOR
1566: SELECT PARTY_ID , TX34, TX39, TX40
1567: FROM HZ_STAGED_PARTIES stage

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

1576: END IF;
1577: OPEN x_cursor FOR
1578: SELECT PARTY_ID , TX34, TX39, TX40
1579: FROM HZ_STAGED_PARTIES stage
1580: WHERE contains( concat_col, p_contains_str)>0
1581: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1582: AND( (l_search_merged ='Y' )
1583: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1584: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )

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

1589: END IF;
1590: OPEN x_cursor FOR
1591: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX34, TX39, TX40
1592: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1593: WHERE contains( concat_col, p_contains_str)>0
1594: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1595: AND d.party_id = stage.party_id
1596: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1597: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)

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

1612: END IF;
1613: IF p_search_ctx_id IS NULL THEN
1614: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX34, TX39, TX40'||
1615: ' FROM HZ_STAGED_PARTIES stage'||
1616: ' WHERE contains( concat_col, :cont)>0'||
1617: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1618: ' AND ('||p_restrict_sql||')' ||
1619: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1620: IF l_search_merged = 'Y' THEN

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

1624: ELSE
1625: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1626: END IF;
1627: output_long_strings('----------------------------------------------------------');
1628: output_long_strings('Party Contains String = '||p_contains_str);
1629: output_long_strings('Restrict Sql = '||p_restrict_sql);
1630: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1631: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1632: ,p_dup_party_id, p_dup_party_id;

Line 1630: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1626: END IF;
1627: output_long_strings('----------------------------------------------------------');
1628: output_long_strings('Party Contains String = '||p_contains_str);
1629: output_long_strings('Restrict Sql = '||p_restrict_sql);
1630: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1631: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1632: ,p_dup_party_id, p_dup_party_id;
1633: END IF;
1634: END IF;

Line 1661: p_contains_str VARCHAR2,

1657: PROCEDURE open_party_site_cursor(
1658: p_dup_party_id NUMBER,
1659: p_party_id NUMBER,
1660: p_restrict_sql VARCHAR2,
1661: p_contains_str VARCHAR2,
1662: p_search_ctx_id NUMBER,
1663: p_search_merged VARCHAR2,
1664: p_search_rel_sites VARCHAR2,
1665: p_person_api VARCHAR2,

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

1689: IF p_search_rel_sites = 'N' THEN
1690: OPEN x_cursor FOR
1691: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22
1692: FROM 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 stage.party_id = p_party_id;

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

1698: ELSE
1699: OPEN x_cursor FOR
1700: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22
1701: FROM HZ_STAGED_PARTY_SITES stage
1702: WHERE contains( concat_col, p_contains_str)>0
1703: AND( (l_search_merged ='Y' )
1704: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1705: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1706: AND stage.party_id = p_party_id

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

1706: AND stage.party_id = p_party_id
1707: UNION
1708: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22
1709: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1710: WHERE contains( concat_col, p_contains_str)>0
1711: AND( (l_search_merged ='Y' )
1712: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1713: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1714: AND r.object_id = p_party_id

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

1728: END IF;
1729: OPEN x_cursor FOR
1730: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22
1731: FROM HZ_STAGED_PARTY_SITES stage
1732: WHERE contains( concat_col, p_contains_str)>0
1733: AND EXISTS (
1734: SELECT 1 FROM HZ_STAGED_PARTIES p
1735: WHERE p.PARTY_ID = stage.PARTY_ID
1736: AND( (l_search_merged ='Y' )

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

1748: IF p_person_api = 'Y' THEN
1749: OPEN x_cursor FOR
1750: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22
1751: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1752: WHERE contains( concat_col, p_contains_str)>0
1753: AND( (l_search_merged ='Y' )
1754: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1755: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1756: AND d.search_context_id = p_search_ctx_id

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

1758: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1759: UNION
1760: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22
1761: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1762: WHERE contains( concat_col, p_contains_str)>0
1763: AND( (l_search_merged ='Y' )
1764: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1765: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1766: AND d.search_context_id = p_search_ctx_id

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

1771: ELSE
1772: OPEN x_cursor FOR
1773: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22
1774: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1775: WHERE contains( concat_col, p_contains_str)>0
1776: AND( (l_search_merged ='Y' )
1777: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1778: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1779: AND d.search_context_id = p_search_ctx_id

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

1816: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1817: END IF;
1818: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22'||
1819: ' FROM HZ_STAGED_PARTY_SITES stage'||
1820: ' WHERE contains( concat_col, :cont)>0'||
1821: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1822: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1823: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1824: ' AND (ORG_CONTACT_ID IS NULL ' ||

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

1826: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1827: ' UNION ' ||
1828: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22'||
1829: ' FROM HZ_STAGED_PARTY_SITES stage'||
1830: ' WHERE contains( concat_col, :cont)>0'||
1831: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1832: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1833: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1834: ' AND ORG_CONTACT_ID IN ' ||

Line 1840: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1836: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1837: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1838: ' and ('||p_restrict_sql1|| ') )' ||
1839: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1840: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1841: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1842: ELSE
1843: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22'||
1844: ' FROM HZ_STAGED_PARTY_SITES stage'||

Line 1841: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1837: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1838: ' and ('||p_restrict_sql1|| ') )' ||
1839: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1840: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1841: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1842: ELSE
1843: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22'||
1844: ' FROM HZ_STAGED_PARTY_SITES stage'||
1845: ' WHERE contains( concat_col, :cont)>0'||

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

1841: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1842: ELSE
1843: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22'||
1844: ' FROM HZ_STAGED_PARTY_SITES stage'||
1845: ' WHERE contains( concat_col, :cont)>0'||
1846: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1847: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1848: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1849: ' AND EXISTS ('||

Line 1855: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1851: ' WHERE p.party_id = stage.party_id ' ||
1852: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1853: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1854: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1855: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1856: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1857: ,p_dup_party_id, p_dup_party_id;
1858: END IF;
1859: ELSE

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

1858: END IF;
1859: ELSE
1860: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22'||
1861: ' FROM HZ_STAGED_PARTY_SITES stage'||
1862: ' WHERE contains( concat_col, :cont)>0'||
1863: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1864: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1865: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1866: ' AND EXISTS ('||

Line 1872: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1868: ' WHERE p.party_id = stage.party_id ' ||
1869: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1870: ' AND ('||p_restrict_sql||')' ||
1871: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1872: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1873: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1874: ,p_dup_party_id, p_dup_party_id;
1875: END IF;
1876: END IF;

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

1874: ,p_dup_party_id, p_dup_party_id;
1875: END IF;
1876: END IF;
1877: output_long_strings('----------------------------------------------------------');
1878: output_long_strings('Party Site Contains String = '||p_contains_str);
1879: output_long_strings('Restrict Sql = '||p_restrict_sql);
1880: exception
1881: when others then
1882: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1906: p_contains_str VARCHAR2,

1902: PROCEDURE open_contact_cursor(
1903: p_dup_party_id NUMBER,
1904: p_party_id NUMBER,
1905: p_restrict_sql VARCHAR2,
1906: p_contains_str VARCHAR2,
1907: p_search_ctx_id NUMBER,
1908: p_search_merged VARCHAR2,
1909: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1910: l_sqlstr VARCHAR2(4000);

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

1930: END IF;
1931: OPEN x_cursor FOR
1932: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
1933: FROM HZ_STAGED_CONTACTS stage
1934: WHERE contains( concat_col, p_contains_str)>0
1935: AND EXISTS (
1936: SELECT 1 FROM HZ_STAGED_PARTIES p
1937: WHERE p.PARTY_ID = stage.PARTY_ID
1938: AND( (l_search_merged ='Y' )

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

1953: END IF;
1954: OPEN x_cursor FOR
1955: SELECT ORG_CONTACT_ID, PARTY_ID
1956: FROM HZ_STAGED_CONTACTS stage
1957: WHERE contains( concat_col, p_contains_str)>0
1958: AND EXISTS (
1959: SELECT 1 FROM HZ_STAGED_PARTIES p
1960: WHERE p.PARTY_ID = stage.PARTY_ID
1961: AND( (l_search_merged ='Y' )

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

1972: END IF;
1973: OPEN x_cursor FOR
1974: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
1975: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1976: WHERE contains( concat_col, p_contains_str)>0
1977: AND d.search_context_id = p_search_ctx_id
1978: AND d.party_id = stage.party_id
1979: AND( (l_search_merged ='Y' )
1980: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2006: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
2007: END IF;
2008: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
2009: ' FROM HZ_STAGED_CONTACTS stage'||
2010: ' WHERE contains( concat_col, :cont)>0'||
2011: ' AND EXISTS ('||
2012: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2013: ' WHERE p.party_id = stage.party_id ' ||
2014: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 2020: OPEN x_cursor FOR l_sqlstr USING p_contains_str

2016: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2017: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2018: ' AND ('||p_restrict_sql||')' ||
2019: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2020: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2021: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2022: ,p_dup_party_id, p_dup_party_id;
2023: END IF;
2024: output_long_strings('----------------------------------------------------------');

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

2021: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2022: ,p_dup_party_id, p_dup_party_id;
2023: END IF;
2024: output_long_strings('----------------------------------------------------------');
2025: output_long_strings('Contacts Contains String = '||p_contains_str);
2026: output_long_strings('Restrict Sql = '||p_restrict_sql);
2027: exception
2028: when others then
2029: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 2053: p_contains_str VARCHAR2,

2049: PROCEDURE open_contact_pt_cursor(
2050: p_dup_party_id NUMBER,
2051: p_party_id NUMBER,
2052: p_restrict_sql VARCHAR2,
2053: p_contains_str VARCHAR2,
2054: p_search_ctx_id NUMBER,
2055: p_search_merged VARCHAR2,
2056: p_search_rel_cpts VARCHAR2,
2057: p_person_api VARCHAR2,

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

2079: THEN
2080: OPEN x_cursor FOR
2081: 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
2082: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2083: WHERE contains( concat_col, p_contains_str)>0
2084: AND d.search_context_id = p_search_ctx_id
2085: AND( (l_search_merged ='Y' )
2086: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2087: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2091: THEN
2092: OPEN x_cursor FOR
2093: 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
2094: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2095: WHERE contains( concat_col, p_contains_str)>0
2096: AND d.search_context_id = p_search_ctx_id
2097: AND( (l_search_merged ='Y' )
2098: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2099: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2108: IF p_search_rel_cpts = 'N' THEN
2109: OPEN x_cursor FOR
2110: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2111: FROM HZ_STAGED_CONTACT_POINTS stage
2112: WHERE contains( concat_col, p_contains_str)>0
2113: AND( (l_search_merged ='Y' )
2114: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2115: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2116: AND stage.party_id = p_party_id;

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

2117: ELSE
2118: OPEN x_cursor FOR
2119: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2120: FROM HZ_STAGED_CONTACT_POINTS stage
2121: WHERE contains( concat_col, p_contains_str)>0
2122: AND( (l_search_merged ='Y' )
2123: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2124: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2125: AND stage.party_id = p_party_id

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

2125: AND stage.party_id = p_party_id
2126: UNION
2127: 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
2128: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2129: WHERE contains( concat_col, p_contains_str)>0
2130: AND r.object_id = p_party_id
2131: AND( (l_search_merged ='Y' )
2132: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2133: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2147: END IF;
2148: OPEN x_cursor FOR
2149: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2150: FROM HZ_STAGED_CONTACT_POINTS stage
2151: WHERE contains( concat_col, p_contains_str)>0
2152: AND EXISTS (
2153: SELECT 1 FROM HZ_STAGED_PARTIES p
2154: WHERE p.PARTY_ID = stage.PARTY_ID
2155: AND( (l_search_merged ='Y' )

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

2167: IF p_person_api = 'Y' THEN
2168: OPEN x_cursor FOR
2169: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2170: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2171: WHERE contains( concat_col, p_contains_str)>0
2172: AND d.search_context_id = p_search_ctx_id
2173: AND d.party_id = stage.party_id
2174: AND( (l_search_merged ='Y' )
2175: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2177: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2178: UNION
2179: 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
2180: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2181: WHERE contains( concat_col, p_contains_str)>0
2182: AND d.search_context_id = p_search_ctx_id
2183: AND d.party_id = r.subject_id
2184: AND r.relationship_id = oc.party_relationship_id
2185: AND oc.org_contact_id = stage.org_contact_id

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

2190: ELSE
2191: OPEN x_cursor FOR
2192: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2193: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2194: WHERE contains( concat_col, p_contains_str)>0
2195: AND d.search_context_id = p_search_ctx_id
2196: AND d.party_id = stage.party_id
2197: AND( (l_search_merged ='Y' )
2198: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2235: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2236: END IF;
2237: 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 '||
2238: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2239: ' WHERE contains( concat_col, :cont)>0 '||
2240: ' AND (stage.org_contact_id is null '||
2241: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2242: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2243: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

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

2245: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2246: ' UNION ' ||
2247: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2248: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2249: ' WHERE contains( concat_col, :cont)>0 '||
2250: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2251: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2252: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2253: ' AND (stage.org_contact_id in ' ||

Line 2259: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

2255: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2256: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2257: ' and ('||p_restrict_sql1 || ') ) )' ||
2258: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2259: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2260: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2261: ELSE
2262: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2263: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

Line 2260: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

2256: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2257: ' and ('||p_restrict_sql1 || ') ) )' ||
2258: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2259: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2260: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2261: ELSE
2262: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2263: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2264: ' WHERE contains( concat_col, :cont)>0'||

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

2260: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2261: ELSE
2262: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2263: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2264: ' WHERE contains( concat_col, :cont)>0'||
2265: ' AND EXISTS ('||
2266: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2267: ' WHERE p.party_id = stage.party_id ' ||
2268: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 2274: OPEN x_cursor FOR l_sqlstr USING p_contains_str

2270: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2271: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2272: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2273: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2274: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2275: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2276: ,p_dup_party_id, p_dup_party_id;
2277: END IF;
2278: ELSE

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

2277: END IF;
2278: ELSE
2279: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2280: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2281: ' WHERE contains( concat_col, :cont)>0'||
2282: ' AND EXISTS ('||
2283: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2284: ' WHERE p.party_id = stage.party_id ' ||
2285: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 2291: OPEN x_cursor FOR l_sqlstr USING p_contains_str

2287: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2288: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2289: ' AND ('||p_restrict_sql||')' ||
2290: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2291: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2292: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2293: ,p_dup_party_id, p_dup_party_id;
2294: END IF;
2295: END IF;

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

2294: END IF;
2295: END IF;
2296: END IF;
2297: output_long_strings('----------------------------------------------------------');
2298: output_long_strings('Contact Points Contains String = '||p_contains_str);
2299: output_long_strings('Restrict Sql = '||p_restrict_sql);
2300: exception
2301: when others then
2302: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 2419: l_party_contains_str VARCHAR2(32000);

2415: x_num_matches OUT NUMBER
2416: ) IS
2417:
2418: -- Strings to hold the generated Intermedia query strings
2419: l_party_contains_str VARCHAR2(32000);
2420: l_party_site_contains_str VARCHAR2(32000);
2421: l_contact_contains_str VARCHAR2(32000);
2422: l_contact_pt_contains_str VARCHAR2(32000);
2423: l_denorm_str VARCHAR2(32000);

Line 2420: l_party_site_contains_str VARCHAR2(32000);

2416: ) IS
2417:
2418: -- Strings to hold the generated Intermedia query strings
2419: l_party_contains_str VARCHAR2(32000);
2420: l_party_site_contains_str VARCHAR2(32000);
2421: l_contact_contains_str VARCHAR2(32000);
2422: l_contact_pt_contains_str VARCHAR2(32000);
2423: l_denorm_str VARCHAR2(32000);
2424: l_ps_denorm_str VARCHAR2(32000);

Line 2421: l_contact_contains_str VARCHAR2(32000);

2417:
2418: -- Strings to hold the generated Intermedia query strings
2419: l_party_contains_str VARCHAR2(32000);
2420: l_party_site_contains_str VARCHAR2(32000);
2421: l_contact_contains_str VARCHAR2(32000);
2422: l_contact_pt_contains_str VARCHAR2(32000);
2423: l_denorm_str VARCHAR2(32000);
2424: l_ps_denorm_str VARCHAR2(32000);
2425: l_ct_denorm_str VARCHAR2(32000);

Line 2422: l_contact_pt_contains_str VARCHAR2(32000);

2418: -- Strings to hold the generated Intermedia query strings
2419: l_party_contains_str VARCHAR2(32000);
2420: l_party_site_contains_str VARCHAR2(32000);
2421: l_contact_contains_str VARCHAR2(32000);
2422: l_contact_pt_contains_str VARCHAR2(32000);
2423: l_denorm_str VARCHAR2(32000);
2424: l_ps_denorm_str VARCHAR2(32000);
2425: l_ct_denorm_str VARCHAR2(32000);
2426: l_cpt_denorm_str VARCHAR2(32000);

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

2518: call_type(2) := 'OR';
2519: END;
2520:
2521: /** Private procedure to acquire and score at party level ***/
2522: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2523: l_party_id_idx NUMBER:=1;
2524: l_ctx_id NUMBER;
2525: l_precalc_score BOOLEAN := FALSE;
2526: BEGIN

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

2549: l_search_ctx_id,H_PARTY_ID(I));
2550: l_party_id_idx := H_PARTY_ID.COUNT+1;
2551: ELSE
2552: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2553: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2554: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2555: 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);
2556: END IF;
2557: 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 2561: IF (l_party_site_contains_str IS NULL AND

2557: 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
2558: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2559: 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);
2560: END IF;
2561: IF (l_party_site_contains_str IS NULL AND
2562: l_contact_contains_str IS NULL AND
2563: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2564: 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);
2565: RAISE L_RETURN_IMM_EXC;

Line 2562: l_contact_contains_str IS NULL AND

2558: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2559: 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);
2560: END IF;
2561: IF (l_party_site_contains_str IS NULL AND
2562: l_contact_contains_str IS NULL AND
2563: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2564: 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);
2565: RAISE L_RETURN_IMM_EXC;
2566: ELSE

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

2559: 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);
2560: END IF;
2561: IF (l_party_site_contains_str IS NULL AND
2562: l_contact_contains_str IS NULL AND
2563: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2564: 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);
2565: RAISE L_RETURN_IMM_EXC;
2566: ELSE
2567: 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 2567: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);

2563: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2564: 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);
2565: RAISE L_RETURN_IMM_EXC;
2566: ELSE
2567: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2568: END IF;
2569: ELSE
2570: IF p_index>1 THEN
2571: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

2586: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2587: 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);
2588: END IF;
2589: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2590: 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);
2591: END IF;
2592: LOOP
2593: FETCH l_party_cur INTO
2594: l_party_id , l_TX34, l_TX39, l_TX40;

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

2608: l_party_id_idx:= l_party_id_idx+1;
2609: END IF;
2610: IF (l_party_id_idx-1)>l_max_thresh THEN
2611: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2612: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2613: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2614: AND l_contact_pt_contains_str IS NULL THEN
2615: EXIT;
2616: END IF;

Line 2614: AND l_contact_pt_contains_str IS NULL THEN

2610: IF (l_party_id_idx-1)>l_max_thresh THEN
2611: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2612: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2613: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2614: AND l_contact_pt_contains_str IS NULL THEN
2615: EXIT;
2616: END IF;
2617: CLOSE l_party_cur;
2618: IF p_index>1 THEN

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

2639: ROLLBACK to eval_start;
2640: END;
2641:
2642: /** Private procedure to acquire and score at party site level ***/
2643: 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
2644: l_party_id_idx NUMBER:=1;
2645: l_ctx_id NUMBER;
2646: h_ps_id HZ_PARTY_SEARCH.IDList;
2647: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

2651: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2652: 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);
2653: 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);
2654: END IF;
2655: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2656: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2657: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2658: l_party_id := H_SCORES.FIRST;
2659: WHILE l_party_id IS NOT NULL LOOP

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

2652: 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);
2653: 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);
2654: END IF;
2655: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2656: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2657: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2658: l_party_id := H_SCORES.FIRST;
2659: WHILE l_party_id IS NOT NULL LOOP
2660: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;

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

2653: 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);
2654: END IF;
2655: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2656: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2657: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2658: l_party_id := H_SCORES.FIRST;
2659: WHILE l_party_id IS NOT NULL LOOP
2660: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2661: l_party_id:=H_SCORES.NEXT(l_party_id);

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

2683: l_search_ctx_id,H_PARTY_ID(I));
2684: l_party_id_idx := H_PARTY_ID.COUNT+1;
2685: ELSE
2686: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2687: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2688: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2689: 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);
2690: END IF;
2691: IF p_index>1 THEN

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

2705: END IF;
2706: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2707: 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);
2708: END IF;
2709: 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);
2710: LOOP
2711: FETCH l_party_site_cur INTO
2712: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX20, l_TX21, l_TX22;
2713: EXIT WHEN l_party_site_cur%NOTFOUND;

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

2761: END IF;
2762: END;
2763:
2764: /** Private procedure to acquire and score at contact point level ***/
2765: 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
2766: l_party_id_idx NUMBER:=1;
2767: l_ctx_id NUMBER;
2768: h_ct_id HZ_PARTY_SEARCH.IDList;
2769: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

2795: l_search_ctx_id,H_PARTY_ID(I));
2796: l_party_id_idx := H_PARTY_ID.COUNT+1;
2797: ELSE
2798: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2799: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
2800: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2801: 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);
2802: END IF;
2803: IF p_index>1 THEN

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

2817: END IF;
2818: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2819: 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);
2820: END IF;
2821: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2822: LOOP
2823: FETCH l_contact_cur INTO
2824: l_org_contact_id, l_ct_party_id ;
2825: EXIT WHEN l_contact_cur%NOTFOUND;

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

2870: END IF;
2871: END;
2872:
2873: /** Private procedure to acquire and score at contact point level ***/
2874: 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
2875: l_party_id_idx NUMBER:=1;
2876: l_ctx_id NUMBER;
2877: h_cpt_id HZ_PARTY_SEARCH.IDList;
2878: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

2906: l_search_ctx_id,H_PARTY_ID(I));
2907: l_party_id_idx := H_PARTY_ID.COUNT+1;
2908: ELSE
2909: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2910: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
2911: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2912: 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);
2913: END IF;
2914: IF p_index>1 THEN

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

2928: END IF;
2929: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2930: 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);
2931: END IF;
2932: 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);
2933: LOOP
2934: FETCH l_contact_pt_cur INTO
2935: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
2936: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 3113: IF l_party_contains_str IS NOT NULL THEN

3109: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3110: l_threshold := round(( l_entered_max_score / 300) * 81);
3111: other_acq_criteria_exists := TRUE ;
3112: --check if acquisition criteria exists for any other entity
3113: IF l_party_contains_str IS NOT NULL THEN
3114: acq_cnt := 1;
3115: END IF;
3116: IF l_party_site_contains_str IS NOT NULL THEN
3117: acq_cnt := acq_cnt+1;

Line 3116: IF l_party_site_contains_str IS NOT NULL THEN

3112: --check if acquisition criteria exists for any other entity
3113: IF l_party_contains_str IS NOT NULL THEN
3114: acq_cnt := 1;
3115: END IF;
3116: IF l_party_site_contains_str IS NOT NULL THEN
3117: acq_cnt := acq_cnt+1;
3118: END IF;
3119: IF l_contact_contains_str IS NOT NULL THEN
3120: acq_cnt := acq_cnt+1;

Line 3119: IF l_contact_contains_str IS NOT NULL THEN

3115: END IF;
3116: IF l_party_site_contains_str IS NOT NULL THEN
3117: acq_cnt := acq_cnt+1;
3118: END IF;
3119: IF l_contact_contains_str IS NOT NULL THEN
3120: acq_cnt := acq_cnt+1;
3121: END IF;
3122: IF l_contact_pt_contains_str IS NOT NULL THEN
3123: acq_cnt := acq_cnt+1;

Line 3122: IF l_contact_pt_contains_str IS NOT NULL THEN

3118: END IF;
3119: IF l_contact_contains_str IS NOT NULL THEN
3120: acq_cnt := acq_cnt+1;
3121: END IF;
3122: IF l_contact_pt_contains_str IS NOT NULL THEN
3123: acq_cnt := acq_cnt+1;
3124: END IF;
3125: IF acq_cnt>1 THEN
3126: other_acq_criteria_exists := TRUE;

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

3152: FND_MSG_PUB.ADD;
3153: RAISE FND_API.G_EXC_ERROR;
3154: END IF;
3155: END IF;
3156: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3157: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3158: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3159: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3160: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN

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

3153: RAISE FND_API.G_EXC_ERROR;
3154: END IF;
3155: END IF;
3156: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3157: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3158: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3159: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3160: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3161: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

3154: END IF;
3155: END IF;
3156: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3157: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3158: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3159: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3160: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3161: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3162: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

3155: END IF;
3156: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3157: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3158: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3159: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3160: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3161: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3162: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3163: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

3156: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3157: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3158: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3159: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3160: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3161: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3162: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3163: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3164: END IF;

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

3157: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3158: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3159: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3160: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3161: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3162: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3163: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3164: END IF;
3165: END;

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

3158: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3159: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3160: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3161: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3162: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3163: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3164: END IF;
3165: END;
3166: /************ End of find_parties private procedures **********/

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

3159: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3160: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3161: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3162: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3163: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3164: END IF;
3165: END;
3166: /************ End of find_parties private procedures **********/
3167:

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

3231: 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);
3232: END IF;
3233: -- ************************************************************
3234: -- STEP 2. Setup of intermedia query strings for Acquisition query
3235: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3236: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3237: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3238: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3239: 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 3236: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);

3232: END IF;
3233: -- ************************************************************
3234: -- STEP 2. Setup of intermedia query strings for Acquisition query
3235: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3236: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3237: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3238: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3239: 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);
3240: END IF;

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

3233: -- ************************************************************
3234: -- STEP 2. Setup of intermedia query strings for Acquisition query
3235: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3236: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3237: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3238: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3239: 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);
3240: END IF;
3241: l_denorm_max_score:=0;

Line 3269: 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((81/300)*l_entered_max_score));

3265: END IF;
3266: ELSE
3267: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
3268: END IF;
3269: 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((81/300)*l_entered_max_score));
3270: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3271:
3272: -- Setup Search Context ID
3273: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 3279: IF l_party_contains_str IS NULL THEN

3275:
3276: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3277: 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);
3278: END IF;
3279: IF l_party_contains_str IS NULL THEN
3280: defpt := 1;
3281: END IF;
3282: IF l_party_site_contains_str IS NULL THEN
3283: defps := 1;

Line 3282: IF l_party_site_contains_str IS NULL THEN

3278: END IF;
3279: IF l_party_contains_str IS NULL THEN
3280: defpt := 1;
3281: END IF;
3282: IF l_party_site_contains_str IS NULL THEN
3283: defps := 1;
3284: END IF;
3285: IF l_contact_contains_str IS NULL THEN
3286: defct := 1;

Line 3285: IF l_contact_contains_str IS NULL THEN

3281: END IF;
3282: IF l_party_site_contains_str IS NULL THEN
3283: defps := 1;
3284: END IF;
3285: IF l_contact_contains_str IS NULL THEN
3286: defct := 1;
3287: END IF;
3288: IF l_contact_pt_contains_str IS NULL THEN
3289: defcpt := 1;

Line 3288: IF l_contact_pt_contains_str IS NULL THEN

3284: END IF;
3285: IF l_contact_contains_str IS NULL THEN
3286: defct := 1;
3287: END IF;
3288: IF l_contact_pt_contains_str IS NULL THEN
3289: defcpt := 1;
3290: END IF;
3291:
3292: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3293: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3294: 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);
3295: END IF;
3296: 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);
3297: 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);
3298: 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);
3299: 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);
3300: 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);
3301: 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 3298: 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);

3294: 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);
3295: END IF;
3296: 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);
3297: 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);
3298: 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);
3299: 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);
3300: 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);
3301: 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);
3302: END IF;

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

3295: END IF;
3296: 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);
3297: 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);
3298: 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);
3299: 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);
3300: 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);
3301: 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);
3302: END IF;
3303: FOR I in 1..5 LOOP

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

3296: 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);
3297: 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);
3298: 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);
3299: 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);
3300: 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);
3301: 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);
3302: END IF;
3303: FOR I in 1..5 LOOP
3304: do_eval(I);

Line 3382: l_party_contains_str VARCHAR2(32000);

3378: x_num_matches OUT NUMBER
3379: ) IS
3380:
3381: -- Strings to hold the generated Intermedia query strings
3382: l_party_contains_str VARCHAR2(32000);
3383: l_party_site_contains_str VARCHAR2(32000);
3384: l_contact_contains_str VARCHAR2(32000);
3385: l_contact_pt_contains_str VARCHAR2(32000);
3386: l_denorm_str VARCHAR2(32000);

Line 3383: l_party_site_contains_str VARCHAR2(32000);

3379: ) IS
3380:
3381: -- Strings to hold the generated Intermedia query strings
3382: l_party_contains_str VARCHAR2(32000);
3383: l_party_site_contains_str VARCHAR2(32000);
3384: l_contact_contains_str VARCHAR2(32000);
3385: l_contact_pt_contains_str VARCHAR2(32000);
3386: l_denorm_str VARCHAR2(32000);
3387: l_ps_denorm_str VARCHAR2(32000);

Line 3384: l_contact_contains_str VARCHAR2(32000);

3380:
3381: -- Strings to hold the generated Intermedia query strings
3382: l_party_contains_str VARCHAR2(32000);
3383: l_party_site_contains_str VARCHAR2(32000);
3384: l_contact_contains_str VARCHAR2(32000);
3385: l_contact_pt_contains_str VARCHAR2(32000);
3386: l_denorm_str VARCHAR2(32000);
3387: l_ps_denorm_str VARCHAR2(32000);
3388: l_ct_denorm_str VARCHAR2(32000);

Line 3385: l_contact_pt_contains_str VARCHAR2(32000);

3381: -- Strings to hold the generated Intermedia query strings
3382: l_party_contains_str VARCHAR2(32000);
3383: l_party_site_contains_str VARCHAR2(32000);
3384: l_contact_contains_str VARCHAR2(32000);
3385: l_contact_pt_contains_str VARCHAR2(32000);
3386: l_denorm_str VARCHAR2(32000);
3387: l_ps_denorm_str VARCHAR2(32000);
3388: l_ct_denorm_str VARCHAR2(32000);
3389: l_cpt_denorm_str VARCHAR2(32000);

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

3511: call_type(2) := 'OR';
3512: END;
3513:
3514: /** Private procedure to acquire and score at party level ***/
3515: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3516: l_party_id_idx NUMBER:=1;
3517: l_ctx_id NUMBER;
3518: BEGIN
3519: SAVEPOINT eval_start;

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

3532: l_search_ctx_id,H_PARTY_ID(I));
3533: l_party_id_idx := H_PARTY_ID.COUNT+1;
3534: ELSE
3535: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3536: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3537: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3538: 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);
3539: END IF;
3540: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN

Line 3544: IF (l_party_site_contains_str IS NULL AND

3540: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3541: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3542: 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);
3543: END IF;
3544: IF (l_party_site_contains_str IS NULL AND
3545: l_contact_contains_str IS NULL AND
3546: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3547: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3548: RAISE L_RETURN_IMM_EXC;

Line 3545: l_contact_contains_str IS NULL AND

3541: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3542: 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);
3543: END IF;
3544: IF (l_party_site_contains_str IS NULL AND
3545: l_contact_contains_str IS NULL AND
3546: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3547: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3548: RAISE L_RETURN_IMM_EXC;
3549: ELSE

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

3542: 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);
3543: END IF;
3544: IF (l_party_site_contains_str IS NULL AND
3545: l_contact_contains_str IS NULL AND
3546: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3547: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3548: RAISE L_RETURN_IMM_EXC;
3549: ELSE
3550: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);

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

3546: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3547: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3548: RAISE L_RETURN_IMM_EXC;
3549: ELSE
3550: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3551: END IF;
3552: ELSE
3553: IF p_index>1 THEN
3554: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3566: l_ctx_id := NULL;
3567: l_party_id_idx := H_PARTY_ID.COUNT+1;
3568: END IF;
3569: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3570: 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);
3571: END IF;
3572: LOOP
3573: FETCH l_party_cur INTO
3574: l_party_id , l_TX34, l_TX39, l_TX40;

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

3588: l_party_id_idx:= l_party_id_idx+1;
3589: END IF;
3590: IF (l_party_id_idx-1)>l_max_thresh THEN
3591: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3592: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3593: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3594: AND l_contact_pt_contains_str IS NULL THEN
3595: H_PARTY_ID.DELETE(l_party_id_idx-1);
3596: H_PARTY_ID_LIST.DELETE(l_index);

Line 3594: AND l_contact_pt_contains_str IS NULL THEN

3590: IF (l_party_id_idx-1)>l_max_thresh THEN
3591: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3592: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3593: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3594: AND l_contact_pt_contains_str IS NULL THEN
3595: H_PARTY_ID.DELETE(l_party_id_idx-1);
3596: H_PARTY_ID_LIST.DELETE(l_index);
3597: H_SCORES.DELETE(l_index);
3598: EXIT;

Line 3623: p_contains_str VARCHAR2,

3619: CLOSE l_party_cur;
3620: ROLLBACK to eval_start;
3621: END;
3622: PROCEDURE open_person_contact_cursor(
3623: p_contains_str VARCHAR2,
3624: p_search_ctx_id NUMBER,
3625: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3626: BEGIN
3627: OPEN x_cursor FOR

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

3626: BEGIN
3627: OPEN x_cursor FOR
3628: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
3629: FROM HZ_STAGED_CONTACTS stage
3630: WHERE contains( concat_col, p_contains_str)>0
3631: AND ORG_CONTACT_ID in (
3632: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3633: org_contact_id
3634: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc

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

3650: end if;
3651: END;
3652:
3653: /** Private procedure to acquire and score at party site level ***/
3654: 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
3655: l_party_id_idx NUMBER:=1;
3656: l_ctx_id NUMBER;
3657: h_ps_id HZ_PARTY_SEARCH.IDList;
3658: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

3678: ELSE
3679: l_party_id_idx := H_PARTY_ID.COUNT+1;
3680: l_ctx_id := NULL;
3681: END IF;
3682: 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);
3683: LOOP
3684: FETCH l_party_site_cur INTO
3685: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX20, l_TX21, l_TX22;
3686: EXIT WHEN l_party_site_cur%NOTFOUND;

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

3749: END IF;
3750: END;
3751:
3752: /** Private procedure to acquire and score at party site level ***/
3753: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3754: l_party_id_idx NUMBER:=1;
3755: l_ctx_id NUMBER;
3756: h_ct_id HZ_PARTY_SEARCH.IDList;
3757: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

3765: FORALL I in 1..H_PARTY_ID.COUNT
3766: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
3767: l_search_ctx_id,H_PARTY_ID(I));
3768: l_party_id_idx := H_PARTY_ID.COUNT+1;
3769: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
3770: LOOP
3771: FETCH l_contact_cur INTO
3772: l_org_contact_id, l_ct_party_id ;
3773: EXIT WHEN l_contact_cur%NOTFOUND;

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

3807: END IF;
3808: END;
3809:
3810: /** Private procedure to acquire and score at contact point level ***/
3811: 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
3812: l_party_id_idx NUMBER:=1;
3813: l_ctx_id NUMBER;
3814: h_cpt_id HZ_PARTY_SEARCH.IDList;
3815: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

3852: END IF;
3853: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3854: 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);
3855: END IF;
3856: 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);
3857: LOOP
3858: FETCH l_contact_pt_cur INTO
3859: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
3860: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 4059: IF l_party_contains_str IS NOT NULL THEN

4055: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
4056: l_threshold := round(( l_entered_max_score / 300) * 81);
4057: other_acq_criteria_exists := TRUE ;
4058: --check if acquisition criteria exists for any other entity
4059: IF l_party_contains_str IS NOT NULL THEN
4060: acq_cnt := 1;
4061: END IF;
4062: IF l_party_site_contains_str IS NOT NULL THEN
4063: acq_cnt := acq_cnt+1;

Line 4062: IF l_party_site_contains_str IS NOT NULL THEN

4058: --check if acquisition criteria exists for any other entity
4059: IF l_party_contains_str IS NOT NULL THEN
4060: acq_cnt := 1;
4061: END IF;
4062: IF l_party_site_contains_str IS NOT NULL THEN
4063: acq_cnt := acq_cnt+1;
4064: END IF;
4065: IF l_contact_contains_str IS NOT NULL THEN
4066: acq_cnt := acq_cnt+1;

Line 4065: IF l_contact_contains_str IS NOT NULL THEN

4061: END IF;
4062: IF l_party_site_contains_str IS NOT NULL THEN
4063: acq_cnt := acq_cnt+1;
4064: END IF;
4065: IF l_contact_contains_str IS NOT NULL THEN
4066: acq_cnt := acq_cnt+1;
4067: END IF;
4068: IF l_contact_pt_contains_str IS NOT NULL THEN
4069: acq_cnt := acq_cnt+1;

Line 4068: IF l_contact_pt_contains_str IS NOT NULL THEN

4064: END IF;
4065: IF l_contact_contains_str IS NOT NULL THEN
4066: acq_cnt := acq_cnt+1;
4067: END IF;
4068: IF l_contact_pt_contains_str IS NOT NULL THEN
4069: acq_cnt := acq_cnt+1;
4070: END IF;
4071: IF acq_cnt>1 THEN
4072: other_acq_criteria_exists := TRUE;

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

4098: FND_MSG_PUB.ADD;
4099: RAISE FND_API.G_EXC_ERROR;
4100: END IF;
4101: END IF;
4102: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4103: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4104: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4105: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4106: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

4099: RAISE FND_API.G_EXC_ERROR;
4100: END IF;
4101: END IF;
4102: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4103: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4104: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4105: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4106: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4107: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

4100: END IF;
4101: END IF;
4102: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4103: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4104: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4105: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4106: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4107: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4108: END IF;

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

4101: END IF;
4102: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4103: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4104: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4105: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4106: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4107: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4108: END IF;
4109: END;

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

4102: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4103: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4104: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4105: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4106: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4107: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4108: END IF;
4109: END;
4110: /************ End of find_persons private procedures **********/

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

4103: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4104: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4105: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4106: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4107: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4108: END IF;
4109: END;
4110: /************ End of find_persons private procedures **********/
4111:

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

4138: g_score_until_thresh := false;
4139: END IF;
4140: -- ************************************************************
4141: -- STEP 2. Setup of intermedia query strings for Acquisition query
4142: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4143: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4144: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4145: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4146: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);

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

4139: END IF;
4140: -- ************************************************************
4141: -- STEP 2. Setup of intermedia query strings for Acquisition query
4142: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4143: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4144: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4145: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4146: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4147:

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

4140: -- ************************************************************
4141: -- STEP 2. Setup of intermedia query strings for Acquisition query
4142: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4143: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4144: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4145: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4146: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4147:
4148: -- Setup Search Context ID

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

4141: -- STEP 2. Setup of intermedia query strings for Acquisition query
4142: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4143: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4144: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4145: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4146: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4147:
4148: -- Setup Search Context ID
4149: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 4152: IF l_party_contains_str IS NULL THEN

4148: -- Setup Search Context ID
4149: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4150: x_search_ctx_id := l_search_ctx_id;
4151:
4152: IF l_party_contains_str IS NULL THEN
4153: defpt := 1;
4154: END IF;
4155: IF l_party_site_contains_str IS NULL THEN
4156: defps := 1;

Line 4155: IF l_party_site_contains_str IS NULL THEN

4151:
4152: IF l_party_contains_str IS NULL THEN
4153: defpt := 1;
4154: END IF;
4155: IF l_party_site_contains_str IS NULL THEN
4156: defps := 1;
4157: END IF;
4158: IF l_contact_contains_str IS NULL THEN
4159: defct := 1;

Line 4158: IF l_contact_contains_str IS NULL THEN

4154: END IF;
4155: IF l_party_site_contains_str IS NULL THEN
4156: defps := 1;
4157: END IF;
4158: IF l_contact_contains_str IS NULL THEN
4159: defct := 1;
4160: END IF;
4161: IF l_contact_pt_contains_str IS NULL THEN
4162: defcpt := 1;

Line 4161: IF l_contact_pt_contains_str IS NULL THEN

4157: END IF;
4158: IF l_contact_contains_str IS NULL THEN
4159: defct := 1;
4160: END IF;
4161: IF l_contact_pt_contains_str IS NULL THEN
4162: defcpt := 1;
4163: END IF;
4164:
4165: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

4163: END IF;
4164:
4165: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4166: 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);
4167: 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);
4168: 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);
4169: 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);
4170: 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);
4171: 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 4168: 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);

4164:
4165: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4166: 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);
4167: 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);
4168: 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);
4169: 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);
4170: 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);
4171: 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);
4172: END IF;

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

4165: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4166: 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);
4167: 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);
4168: 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);
4169: 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);
4170: 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);
4171: 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);
4172: END IF;
4173: FOR I in 1..5 LOOP

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

4166: 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);
4167: 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);
4168: 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);
4169: 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);
4170: 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);
4171: 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);
4172: END IF;
4173: FOR I in 1..5 LOOP
4174: do_eval(I);

Line 4176: IF l_contact_contains_str IS NOT NULL THEN

4172: END IF;
4173: FOR I in 1..5 LOOP
4174: do_eval(I);
4175: END LOOP;
4176: IF l_contact_contains_str IS NOT NULL THEN
4177: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4178: END IF;
4179: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4180: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 27 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

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

4173: FOR I in 1..5 LOOP
4174: do_eval(I);
4175: END LOOP;
4176: IF l_contact_contains_str IS NOT NULL THEN
4177: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4178: END IF;
4179: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4180: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 27 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4181: END IF;

Line 4973: l_party_contains_str VARCHAR2(32000);

4969: x_num_matches OUT NUMBER
4970: ) IS
4971:
4972: -- Strings to hold the generated Intermedia query strings
4973: l_party_contains_str VARCHAR2(32000);
4974: l_match_idx NUMBER;
4975: l_party_site_contains_str VARCHAR2(32000);
4976: l_contact_contains_str VARCHAR2(32000);
4977: l_contact_pt_contains_str VARCHAR2(32000);

Line 4975: l_party_site_contains_str VARCHAR2(32000);

4971:
4972: -- Strings to hold the generated Intermedia query strings
4973: l_party_contains_str VARCHAR2(32000);
4974: l_match_idx NUMBER;
4975: l_party_site_contains_str VARCHAR2(32000);
4976: l_contact_contains_str VARCHAR2(32000);
4977: l_contact_pt_contains_str VARCHAR2(32000);
4978: l_tmp VARCHAR2(32000);
4979:

Line 4976: l_contact_contains_str VARCHAR2(32000);

4972: -- Strings to hold the generated Intermedia query strings
4973: l_party_contains_str VARCHAR2(32000);
4974: l_match_idx NUMBER;
4975: l_party_site_contains_str VARCHAR2(32000);
4976: l_contact_contains_str VARCHAR2(32000);
4977: l_contact_pt_contains_str VARCHAR2(32000);
4978: l_tmp VARCHAR2(32000);
4979:
4980: -- Other local variables

Line 4977: l_contact_pt_contains_str VARCHAR2(32000);

4973: l_party_contains_str VARCHAR2(32000);
4974: l_match_idx NUMBER;
4975: l_party_site_contains_str VARCHAR2(32000);
4976: l_contact_contains_str VARCHAR2(32000);
4977: l_contact_pt_contains_str VARCHAR2(32000);
4978: l_tmp VARCHAR2(32000);
4979:
4980: -- Other local variables
4981: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5058: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5059:
5060: -- ************************************************************
5061: -- STEP 2. Setup of intermedia query strings for Acquisition query
5062: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5063: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5064: 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);
5065:
5066: -- Setup Search Context ID

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

5059:
5060: -- ************************************************************
5061: -- STEP 2. Setup of intermedia query strings for Acquisition query
5062: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5063: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5064: 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);
5065:
5066: -- Setup Search Context ID
5067: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

5068: x_search_ctx_id := l_search_ctx_id;
5069:
5070: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5071: 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);
5072: 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);
5073: 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);
5074: 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);
5075: END IF;
5076: IF l_party_site_contains_str IS NULL THEN

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

5069:
5070: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5071: 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);
5072: 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);
5073: 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);
5074: 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);
5075: END IF;
5076: IF l_party_site_contains_str IS NULL THEN
5077: defps := 1;

Line 5076: IF l_party_site_contains_str IS NULL THEN

5072: 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);
5073: 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);
5074: 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);
5075: END IF;
5076: IF l_party_site_contains_str IS NULL THEN
5077: defps := 1;
5078: END IF;
5079: IF l_contact_pt_contains_str IS NULL THEN
5080: defcpt := 1;

Line 5079: IF l_contact_pt_contains_str IS NULL THEN

5075: END IF;
5076: IF l_party_site_contains_str IS NULL THEN
5077: defps := 1;
5078: END IF;
5079: IF l_contact_pt_contains_str IS NULL THEN
5080: defcpt := 1;
5081: END IF;
5082:
5083: IF l_party_site_contains_str IS NOT NULL THEN

Line 5083: IF l_party_site_contains_str IS NOT NULL THEN

5079: IF l_contact_pt_contains_str IS NULL THEN
5080: defcpt := 1;
5081: END IF;
5082:
5083: IF l_party_site_contains_str IS NOT NULL THEN
5084: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5085: LOOP
5086: FETCH l_party_site_cur INTO
5087: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX20, l_TX21, l_TX22;

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

5080: defcpt := 1;
5081: END IF;
5082:
5083: IF l_party_site_contains_str IS NOT NULL THEN
5084: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5085: LOOP
5086: FETCH l_party_site_cur INTO
5087: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX20, l_TX21, l_TX22;
5088: EXIT WHEN l_party_site_cur%NOTFOUND;

Line 5107: IF l_contact_pt_contains_str IS NOT NULL THEN

5103: END LOOP;
5104: CLOSE l_party_site_cur;
5105: END IF;
5106:
5107: IF l_contact_pt_contains_str IS NOT NULL THEN
5108: SAVEPOINT eval_start;
5109: IF l_match_str = ' AND ' OR (
5110: ((l_cpt_max_score/l_entered_max_score)<(81/300))
5111: ) THEN

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

5111: ) THEN
5112: FORALL I in 1..l_match_ps_list.COUNT
5113: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5114: values (l_search_ctx_id,l_match_ps_list(I));
5115: 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');
5116: ELSE
5117: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5118: END IF;
5119: LOOP

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

5113: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5114: values (l_search_ctx_id,l_match_ps_list(I));
5115: 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');
5116: ELSE
5117: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5118: END IF;
5119: LOOP
5120: FETCH l_contact_pt_cur INTO
5121: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 5217: l_party_contains_str VARCHAR2(32000);

5213: x_num_matches OUT NUMBER
5214: ) IS
5215:
5216: -- Strings to hold the generated Intermedia query strings
5217: l_party_contains_str VARCHAR2(32000);
5218: l_party_site_contains_str VARCHAR2(32000);
5219: l_contact_contains_str VARCHAR2(32000);
5220: l_contact_pt_contains_str VARCHAR2(32000);
5221: l_tmp VARCHAR2(32000);

Line 5218: l_party_site_contains_str VARCHAR2(32000);

5214: ) IS
5215:
5216: -- Strings to hold the generated Intermedia query strings
5217: l_party_contains_str VARCHAR2(32000);
5218: l_party_site_contains_str VARCHAR2(32000);
5219: l_contact_contains_str VARCHAR2(32000);
5220: l_contact_pt_contains_str VARCHAR2(32000);
5221: l_tmp VARCHAR2(32000);
5222:

Line 5219: l_contact_contains_str VARCHAR2(32000);

5215:
5216: -- Strings to hold the generated Intermedia query strings
5217: l_party_contains_str VARCHAR2(32000);
5218: l_party_site_contains_str VARCHAR2(32000);
5219: l_contact_contains_str VARCHAR2(32000);
5220: l_contact_pt_contains_str VARCHAR2(32000);
5221: l_tmp VARCHAR2(32000);
5222:
5223: -- Other local variables

Line 5220: l_contact_pt_contains_str VARCHAR2(32000);

5216: -- Strings to hold the generated Intermedia query strings
5217: l_party_contains_str VARCHAR2(32000);
5218: l_party_site_contains_str VARCHAR2(32000);
5219: l_contact_contains_str VARCHAR2(32000);
5220: l_contact_pt_contains_str VARCHAR2(32000);
5221: l_tmp VARCHAR2(32000);
5222:
5223: -- Other local variables
5224: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5287: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5288:
5289: -- ************************************************************
5290: -- STEP 2. Setup of intermedia query strings for Acquisition query
5291: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5292: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5293: 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);
5294:
5295: -- Setup Search Context ID

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

5288:
5289: -- ************************************************************
5290: -- STEP 2. Setup of intermedia query strings for Acquisition query
5291: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5292: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5293: 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);
5294:
5295: -- Setup Search Context ID
5296: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

5297: x_search_ctx_id := l_search_ctx_id;
5298:
5299: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5300: 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);
5301: 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);
5302: 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);
5303: 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);
5304: END IF;
5305: IF l_contact_contains_str IS NULL THEN

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

5298:
5299: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5300: 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);
5301: 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);
5302: 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);
5303: 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);
5304: END IF;
5305: IF l_contact_contains_str IS NULL THEN
5306: defct := 1;

Line 5305: IF l_contact_contains_str IS NULL THEN

5301: 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);
5302: 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);
5303: 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);
5304: END IF;
5305: IF l_contact_contains_str IS NULL THEN
5306: defct := 1;
5307: END IF;
5308: IF l_contact_pt_contains_str IS NULL THEN
5309: defcpt := 1;

Line 5308: IF l_contact_pt_contains_str IS NULL THEN

5304: END IF;
5305: IF l_contact_contains_str IS NULL THEN
5306: defct := 1;
5307: END IF;
5308: IF l_contact_pt_contains_str IS NULL THEN
5309: defcpt := 1;
5310: END IF;
5311:
5312: IF l_contact_contains_str IS NOT NULL THEN

Line 5312: IF l_contact_contains_str IS NOT NULL THEN

5308: IF l_contact_pt_contains_str IS NULL THEN
5309: defcpt := 1;
5310: END IF;
5311:
5312: IF l_contact_contains_str IS NOT NULL THEN
5313: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5314: LOOP
5315: FETCH l_contact_cur INTO
5316: l_org_contact_id, l_ct_party_id ;

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

5309: defcpt := 1;
5310: END IF;
5311:
5312: IF l_contact_contains_str IS NOT NULL THEN
5313: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5314: LOOP
5315: FETCH l_contact_cur INTO
5316: l_org_contact_id, l_ct_party_id ;
5317: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5334: IF l_contact_pt_contains_str IS NOT NULL THEN

5330: END LOOP;
5331: CLOSE l_contact_cur;
5332: END IF;
5333:
5334: IF l_contact_pt_contains_str IS NOT NULL THEN
5335: SAVEPOINT eval_start;
5336: IF l_match_str = ' AND ' OR (
5337: ((l_cpt_max_score/l_entered_max_score)<(81/300))
5338: ) THEN

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

5338: ) THEN
5339: FORALL I in 1..l_match_contact_list.COUNT
5340: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5341: values (l_search_ctx_id,l_match_contact_list(I));
5342: 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');
5343: ELSE
5344: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5345: END IF;
5346: LOOP

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

5340: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5341: values (l_search_ctx_id,l_match_contact_list(I));
5342: 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');
5343: ELSE
5344: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5345: END IF;
5346: LOOP
5347: FETCH l_contact_pt_cur INTO
5348: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 5442: l_contact_pt_contains_str VARCHAR2(32000);

5438: ) IS
5439:
5440:
5441: -- Strings to hold the generated Intermedia query strings
5442: l_contact_pt_contains_str VARCHAR2(32000);
5443: l_tmp VARCHAR2(32000);
5444:
5445: -- Other local variables
5446: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5496: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5497:
5498: -- ************************************************************
5499: -- STEP 2. Setup of intermedia query strings for Acquisition query
5500: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5501: 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);
5502:
5503: -- Setup Search Context ID
5504: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

5504: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5505: x_search_ctx_id := l_search_ctx_id;
5506: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5507: 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);
5508: 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);
5509: 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);
5510: END IF;
5511:
5512: IF l_contact_pt_contains_str IS NOT NULL THEN

Line 5512: IF l_contact_pt_contains_str IS NOT NULL THEN

5508: 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);
5509: 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);
5510: END IF;
5511:
5512: IF l_contact_pt_contains_str IS NOT NULL THEN
5513: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5514: cnt := 1;
5515: LOOP
5516: FETCH l_contact_pt_cur INTO

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

5509: 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);
5510: END IF;
5511:
5512: IF l_contact_pt_contains_str IS NOT NULL THEN
5513: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5514: cnt := 1;
5515: LOOP
5516: FETCH l_contact_pt_cur INTO
5517: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 5584: l_party_contains_str VARCHAR2(32000);

5580: x_search_ctx_id IN OUT NUMBER
5581: ) IS
5582:
5583: -- Strings to hold the generated Intermedia query strings
5584: l_party_contains_str VARCHAR2(32000);
5585: l_party_site_contains_str VARCHAR2(32000);
5586: l_contact_contains_str VARCHAR2(32000);
5587: l_contact_pt_contains_str VARCHAR2(32000);
5588: l_tmp VARCHAR2(32000);

Line 5585: l_party_site_contains_str VARCHAR2(32000);

5581: ) IS
5582:
5583: -- Strings to hold the generated Intermedia query strings
5584: l_party_contains_str VARCHAR2(32000);
5585: l_party_site_contains_str VARCHAR2(32000);
5586: l_contact_contains_str VARCHAR2(32000);
5587: l_contact_pt_contains_str VARCHAR2(32000);
5588: l_tmp VARCHAR2(32000);
5589:

Line 5586: l_contact_contains_str VARCHAR2(32000);

5582:
5583: -- Strings to hold the generated Intermedia query strings
5584: l_party_contains_str VARCHAR2(32000);
5585: l_party_site_contains_str VARCHAR2(32000);
5586: l_contact_contains_str VARCHAR2(32000);
5587: l_contact_pt_contains_str VARCHAR2(32000);
5588: l_tmp VARCHAR2(32000);
5589:
5590: -- Other local variables

Line 5587: l_contact_pt_contains_str VARCHAR2(32000);

5583: -- Strings to hold the generated Intermedia query strings
5584: l_party_contains_str VARCHAR2(32000);
5585: l_party_site_contains_str VARCHAR2(32000);
5586: l_contact_contains_str VARCHAR2(32000);
5587: l_contact_pt_contains_str VARCHAR2(32000);
5588: l_tmp VARCHAR2(32000);
5589:
5590: -- Other local variables
5591: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5670: g_score_until_thresh := false;
5671: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5672: -- ************************************************************
5673: -- STEP 2. Setup of intermedia query strings for Acquisition query
5674: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5675: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5676: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5677: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5678:

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

5671: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5672: -- ************************************************************
5673: -- STEP 2. Setup of intermedia query strings for Acquisition query
5674: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5675: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5676: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5677: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5678:
5679: -- Setup Search Context ID

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

5672: -- ************************************************************
5673: -- STEP 2. Setup of intermedia query strings for Acquisition query
5674: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5675: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5676: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5677: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5678:
5679: -- Setup Search Context ID
5680: IF x_search_ctx_id IS NULL THEN

Line 5696: IF l_party_site_contains_str IS NOT NULL THEN

5692: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX34,l_TX39,l_TX40,1);
5693: END LOOP;
5694: CLOSE l_party_cur;
5695:
5696: IF l_party_site_contains_str IS NOT NULL THEN
5697: l_max_score := 0;
5698: l_max_id := 0;
5699: l_max_idx := 0;
5700: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN

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

5697: l_max_score := 0;
5698: l_max_id := 0;
5699: l_max_idx := 0;
5700: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5701: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5702: ELSE
5703: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5704: END IF;
5705: LOOP

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

5699: l_max_idx := 0;
5700: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5701: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5702: ELSE
5703: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5704: END IF;
5705: LOOP
5706: FETCH l_party_site_cur INTO
5707: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX20, l_TX21, l_TX22;

Line 5734: IF l_contact_contains_str IS NOT NULL THEN

5730: 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_TX9,l_max_TX10,l_max_TX11,l_max_TX12,l_max_TX13,l_max_TX14,l_max_TX15,l_max_TX20,l_max_TX21,l_max_TX22,l_max_idx);
5731: END IF;
5732: END IF;
5733:
5734: IF l_contact_contains_str IS NOT NULL THEN
5735: l_max_score := 0;
5736: l_max_id := 0;
5737: l_max_idx := 0;
5738: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

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

5734: IF l_contact_contains_str IS NOT NULL THEN
5735: l_max_score := 0;
5736: l_max_id := 0;
5737: l_max_idx := 0;
5738: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5739: LOOP
5740: FETCH l_contact_cur INTO
5741: l_org_contact_id, l_ct_party_id ;
5742: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5756: IF l_contact_pt_contains_str IS NOT NULL THEN

5752: INSERT_CONTACTS_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_contact_list(l_max_idx), g_contact_stage_list(l_max_idx) ,l_max_idx);
5753: END IF;
5754: END IF;
5755:
5756: IF l_contact_pt_contains_str IS NOT NULL THEN
5757: l_max_score := 0;
5758: l_max_id := 0;
5759: l_max_idx := 0;
5760: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN

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

5757: l_max_score := 0;
5758: l_max_id := 0;
5759: l_max_idx := 0;
5760: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5761: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5762: ELSE
5763: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5764: END IF;
5765: LOOP

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

5759: l_max_idx := 0;
5760: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5761: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5762: ELSE
5763: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5764: END IF;
5765: LOOP
5766: FETCH l_contact_pt_cur INTO
5767: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;