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 /*+ INDEX(stage HZ_STAGE_PARTIES_T1) */ 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 1618: ' WHERE contains( concat_col, :cont)>0'||

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

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

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

Line 1632: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

Line 1663: p_contains_str VARCHAR2,

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

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

1691: IF p_search_rel_sites = 'N' THEN
1692: OPEN x_cursor FOR
1693: 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
1694: FROM HZ_STAGED_PARTY_SITES stage
1695: WHERE contains( concat_col, p_contains_str)>0
1696: AND( (l_search_merged ='Y' )
1697: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1698: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1699: AND stage.party_id = p_party_id;

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

1700: ELSE
1701: OPEN x_cursor FOR
1702: 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
1703: FROM HZ_STAGED_PARTY_SITES stage
1704: WHERE contains( concat_col, p_contains_str)>0
1705: AND( (l_search_merged ='Y' )
1706: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1707: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1708: AND stage.party_id = p_party_id

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

1708: AND stage.party_id = p_party_id
1709: UNION
1710: 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
1711: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1712: WHERE contains( concat_col, p_contains_str)>0
1713: AND( (l_search_merged ='Y' )
1714: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1715: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1716: AND r.object_id = p_party_id

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

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

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

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

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

1760: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1761: UNION
1762: 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
1763: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1764: WHERE contains( concat_col, p_contains_str)>0
1765: AND( (l_search_merged ='Y' )
1766: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1767: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1768: AND d.search_context_id = p_search_ctx_id

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

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

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

1818: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1819: END IF;
1820: 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'||
1821: ' FROM HZ_STAGED_PARTY_SITES stage'||
1822: ' WHERE contains( concat_col, :cont)>0'||
1823: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1824: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1825: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1826: ' AND (ORG_CONTACT_ID IS NULL ' ||

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

1828: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1829: ' UNION ' ||
1830: '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'||
1831: ' FROM HZ_STAGED_PARTY_SITES stage'||
1832: ' WHERE contains( concat_col, :cont)>0'||
1833: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1834: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1835: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1836: ' AND ORG_CONTACT_ID IN ' ||

Line 1842: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

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

Line 1843: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

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

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

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

Line 1857: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 1874: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 1908: p_contains_str VARCHAR2,

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

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

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

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

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

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

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

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

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

Line 2022: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 2055: p_contains_str VARCHAR2,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 2261: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

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

Line 2262: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

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

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

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

Line 2276: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 2293: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 2421: l_party_contains_str VARCHAR2(32000);

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

Line 2422: l_party_site_contains_str VARCHAR2(32000);

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

Line 2423: l_contact_contains_str VARCHAR2(32000);

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

Line 2424: l_contact_pt_contains_str VARCHAR2(32000);

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

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

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

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

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

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

Line 2565: l_contact_contains_str IS NULL AND

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

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

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

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

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

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

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

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

Line 2617: AND l_contact_pt_contains_str IS NULL THEN

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

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

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

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

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

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

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

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

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

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

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

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

2708: END IF;
2709: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2710: 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);
2711: END IF;
2712: 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);
2713: LOOP
2714: FETCH l_party_site_cur INTO
2715: 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;
2716: EXIT WHEN l_party_site_cur%NOTFOUND;

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

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

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

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

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

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

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

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

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

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

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

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

Line 3116: IF l_party_contains_str IS NOT NULL THEN

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

Line 3119: IF l_party_site_contains_str IS NOT NULL THEN

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

Line 3122: IF l_contact_contains_str IS NOT NULL THEN

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

Line 3125: IF l_contact_pt_contains_str IS NOT NULL THEN

3121: END IF;
3122: IF l_contact_contains_str IS NOT NULL THEN
3123: acq_cnt := acq_cnt+1;
3124: END IF;
3125: IF l_contact_pt_contains_str IS NOT NULL THEN
3126: acq_cnt := acq_cnt+1;
3127: END IF;
3128: IF acq_cnt>1 THEN
3129: other_acq_criteria_exists := TRUE;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3268: END IF;
3269: ELSE
3270: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
3271: END IF;
3272: 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));
3273: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3274:
3275: -- Setup Search Context ID
3276: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 3282: IF l_party_contains_str IS NULL THEN

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

Line 3285: IF l_party_site_contains_str IS NULL THEN

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

Line 3288: IF l_contact_contains_str IS NULL THEN

3284: END IF;
3285: IF l_party_site_contains_str IS NULL THEN
3286: defps := 1;
3287: END IF;
3288: IF l_contact_contains_str IS NULL THEN
3289: defct := 1;
3290: END IF;
3291: IF l_contact_pt_contains_str IS NULL THEN
3292: defcpt := 1;

Line 3291: IF l_contact_pt_contains_str IS NULL THEN

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

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

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

3297: 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);
3298: END IF;
3299: 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);
3300: 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);
3301: 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);
3302: 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);
3303: 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);
3304: 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);
3305: END IF;

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

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

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

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

Line 3385: l_party_contains_str VARCHAR2(32000);

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

Line 3386: l_party_site_contains_str VARCHAR2(32000);

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

Line 3387: l_contact_contains_str VARCHAR2(32000);

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

Line 3388: l_contact_pt_contains_str VARCHAR2(32000);

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

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

3514: call_type(2) := 'OR';
3515: END;
3516:
3517: /** Private procedure to acquire and score at party level ***/
3518: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3519: l_party_id_idx NUMBER:=1;
3520: l_ctx_id NUMBER;
3521: l_TX35_new varchar2(4000);
3522: BEGIN

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

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

Line 3548: IF (l_party_site_contains_str IS NULL AND

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

Line 3549: l_contact_contains_str IS NULL AND

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

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

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

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

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

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

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

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

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

Line 3598: AND l_contact_pt_contains_str IS NULL THEN

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

Line 3627: p_contains_str VARCHAR2,

3623: CLOSE l_party_cur;
3624: ROLLBACK to eval_start;
3625: END;
3626: PROCEDURE open_person_contact_cursor(
3627: p_contains_str VARCHAR2,
3628: p_search_ctx_id NUMBER,
3629: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3630: BEGIN
3631: OPEN x_cursor FOR

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

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

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

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

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

3682: ELSE
3683: l_party_id_idx := H_PARTY_ID.COUNT+1;
3684: l_ctx_id := NULL;
3685: END IF;
3686: 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);
3687: LOOP
3688: FETCH l_party_site_cur INTO
3689: 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;
3690: EXIT WHEN l_party_site_cur%NOTFOUND;

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

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

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

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

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

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

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

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

Line 4063: IF l_party_contains_str IS NOT NULL THEN

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

Line 4066: IF l_party_site_contains_str IS NOT NULL THEN

4062: --check if acquisition criteria exists for any other entity
4063: IF l_party_contains_str IS NOT NULL THEN
4064: acq_cnt := 1;
4065: END IF;
4066: IF l_party_site_contains_str IS NOT NULL THEN
4067: acq_cnt := acq_cnt+1;
4068: END IF;
4069: IF l_contact_contains_str IS NOT NULL THEN
4070: acq_cnt := acq_cnt+1;

Line 4069: IF l_contact_contains_str IS NOT NULL THEN

4065: END IF;
4066: IF l_party_site_contains_str IS NOT NULL THEN
4067: acq_cnt := acq_cnt+1;
4068: END IF;
4069: IF l_contact_contains_str IS NOT NULL THEN
4070: acq_cnt := acq_cnt+1;
4071: END IF;
4072: IF l_contact_pt_contains_str IS NOT NULL THEN
4073: acq_cnt := acq_cnt+1;

Line 4072: IF l_contact_pt_contains_str IS NOT NULL THEN

4068: END IF;
4069: IF l_contact_contains_str IS NOT NULL THEN
4070: acq_cnt := acq_cnt+1;
4071: END IF;
4072: IF l_contact_pt_contains_str IS NOT NULL THEN
4073: acq_cnt := acq_cnt+1;
4074: END IF;
4075: IF acq_cnt>1 THEN
4076: other_acq_criteria_exists := TRUE;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 4156: IF l_party_contains_str IS NULL THEN

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

Line 4159: IF l_party_site_contains_str IS NULL THEN

4155:
4156: IF l_party_contains_str IS NULL THEN
4157: defpt := 1;
4158: END IF;
4159: IF l_party_site_contains_str IS NULL THEN
4160: defps := 1;
4161: END IF;
4162: IF l_contact_contains_str IS NULL THEN
4163: defct := 1;

Line 4162: IF l_contact_contains_str IS NULL THEN

4158: END IF;
4159: IF l_party_site_contains_str IS NULL THEN
4160: defps := 1;
4161: END IF;
4162: IF l_contact_contains_str IS NULL THEN
4163: defct := 1;
4164: END IF;
4165: IF l_contact_pt_contains_str IS NULL THEN
4166: defcpt := 1;

Line 4165: IF l_contact_pt_contains_str IS NULL THEN

4161: END IF;
4162: IF l_contact_contains_str IS NULL THEN
4163: defct := 1;
4164: END IF;
4165: IF l_contact_pt_contains_str IS NULL THEN
4166: defcpt := 1;
4167: END IF;
4168:
4169: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

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

4168:
4169: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4170: 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);
4171: 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);
4172: 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);
4173: 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);
4174: 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);
4175: 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);
4176: END IF;

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

4169: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4170: 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);
4171: 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);
4172: 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);
4173: 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);
4174: 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);
4175: 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);
4176: END IF;
4177: FOR I in 1..5 LOOP

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

4170: 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);
4171: 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);
4172: 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);
4173: 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);
4174: 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);
4175: 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);
4176: END IF;
4177: FOR I in 1..5 LOOP
4178: do_eval(I);

Line 4180: IF l_contact_contains_str IS NOT NULL THEN

4176: END IF;
4177: FOR I in 1..5 LOOP
4178: do_eval(I);
4179: END LOOP;
4180: IF l_contact_contains_str IS NOT NULL THEN
4181: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4182: END IF;
4183: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4184: 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 4181: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);

4177: FOR I in 1..5 LOOP
4178: do_eval(I);
4179: END LOOP;
4180: IF l_contact_contains_str IS NOT NULL THEN
4181: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4182: END IF;
4183: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4184: 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);
4185: END IF;

Line 4977: l_party_contains_str VARCHAR2(32000);

4973: x_num_matches OUT NUMBER
4974: ) IS
4975:
4976: -- Strings to hold the generated Intermedia query strings
4977: l_party_contains_str VARCHAR2(32000);
4978: l_match_idx NUMBER;
4979: l_party_site_contains_str VARCHAR2(32000);
4980: l_contact_contains_str VARCHAR2(32000);
4981: l_contact_pt_contains_str VARCHAR2(32000);

Line 4979: l_party_site_contains_str VARCHAR2(32000);

4975:
4976: -- Strings to hold the generated Intermedia query strings
4977: l_party_contains_str VARCHAR2(32000);
4978: l_match_idx NUMBER;
4979: l_party_site_contains_str VARCHAR2(32000);
4980: l_contact_contains_str VARCHAR2(32000);
4981: l_contact_pt_contains_str VARCHAR2(32000);
4982: l_tmp VARCHAR2(32000);
4983:

Line 4980: l_contact_contains_str VARCHAR2(32000);

4976: -- Strings to hold the generated Intermedia query strings
4977: l_party_contains_str VARCHAR2(32000);
4978: l_match_idx NUMBER;
4979: l_party_site_contains_str VARCHAR2(32000);
4980: l_contact_contains_str VARCHAR2(32000);
4981: l_contact_pt_contains_str VARCHAR2(32000);
4982: l_tmp VARCHAR2(32000);
4983:
4984: -- Other local variables

Line 4981: l_contact_pt_contains_str VARCHAR2(32000);

4977: l_party_contains_str VARCHAR2(32000);
4978: l_match_idx NUMBER;
4979: l_party_site_contains_str VARCHAR2(32000);
4980: l_contact_contains_str VARCHAR2(32000);
4981: l_contact_pt_contains_str VARCHAR2(32000);
4982: l_tmp VARCHAR2(32000);
4983:
4984: -- Other local variables
4985: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

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

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

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

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

5072: x_search_ctx_id := l_search_ctx_id;
5073:
5074: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5075: 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);
5076: 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);
5077: 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);
5078: 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);
5079: END IF;
5080: IF l_party_site_contains_str IS NULL THEN

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

5073:
5074: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5075: 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);
5076: 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);
5077: 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);
5078: 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);
5079: END IF;
5080: IF l_party_site_contains_str IS NULL THEN
5081: defps := 1;

Line 5080: IF l_party_site_contains_str IS NULL THEN

5076: 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);
5077: 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);
5078: 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);
5079: END IF;
5080: IF l_party_site_contains_str IS NULL THEN
5081: defps := 1;
5082: END IF;
5083: IF l_contact_pt_contains_str IS NULL THEN
5084: defcpt := 1;

Line 5083: IF l_contact_pt_contains_str IS NULL THEN

5079: END IF;
5080: IF l_party_site_contains_str IS NULL THEN
5081: defps := 1;
5082: END IF;
5083: IF l_contact_pt_contains_str IS NULL THEN
5084: defcpt := 1;
5085: END IF;
5086:
5087: IF l_party_site_contains_str IS NOT NULL THEN

Line 5087: IF l_party_site_contains_str IS NOT NULL THEN

5083: IF l_contact_pt_contains_str IS NULL THEN
5084: defcpt := 1;
5085: END IF;
5086:
5087: IF l_party_site_contains_str IS NOT NULL THEN
5088: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5089: LOOP
5090: FETCH l_party_site_cur INTO
5091: 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 5088: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);

5084: defcpt := 1;
5085: END IF;
5086:
5087: IF l_party_site_contains_str IS NOT NULL THEN
5088: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5089: LOOP
5090: FETCH l_party_site_cur INTO
5091: 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;
5092: EXIT WHEN l_party_site_cur%NOTFOUND;

Line 5111: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

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

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

Line 5221: l_party_contains_str VARCHAR2(32000);

5217: x_num_matches OUT NUMBER
5218: ) IS
5219:
5220: -- Strings to hold the generated Intermedia query strings
5221: l_party_contains_str VARCHAR2(32000);
5222: l_party_site_contains_str VARCHAR2(32000);
5223: l_contact_contains_str VARCHAR2(32000);
5224: l_contact_pt_contains_str VARCHAR2(32000);
5225: l_tmp VARCHAR2(32000);

Line 5222: l_party_site_contains_str VARCHAR2(32000);

5218: ) IS
5219:
5220: -- Strings to hold the generated Intermedia query strings
5221: l_party_contains_str VARCHAR2(32000);
5222: l_party_site_contains_str VARCHAR2(32000);
5223: l_contact_contains_str VARCHAR2(32000);
5224: l_contact_pt_contains_str VARCHAR2(32000);
5225: l_tmp VARCHAR2(32000);
5226:

Line 5223: l_contact_contains_str VARCHAR2(32000);

5219:
5220: -- Strings to hold the generated Intermedia query strings
5221: l_party_contains_str VARCHAR2(32000);
5222: l_party_site_contains_str VARCHAR2(32000);
5223: l_contact_contains_str VARCHAR2(32000);
5224: l_contact_pt_contains_str VARCHAR2(32000);
5225: l_tmp VARCHAR2(32000);
5226:
5227: -- Other local variables

Line 5224: l_contact_pt_contains_str VARCHAR2(32000);

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

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

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

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

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

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

5301: x_search_ctx_id := l_search_ctx_id;
5302:
5303: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5304: 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);
5305: 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);
5306: 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);
5307: 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);
5308: END IF;
5309: IF l_contact_contains_str IS NULL THEN

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

5302:
5303: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5304: 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);
5305: 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);
5306: 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);
5307: 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);
5308: END IF;
5309: IF l_contact_contains_str IS NULL THEN
5310: defct := 1;

Line 5309: IF l_contact_contains_str IS NULL THEN

5305: 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);
5306: 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);
5307: 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);
5308: END IF;
5309: IF l_contact_contains_str IS NULL THEN
5310: defct := 1;
5311: END IF;
5312: IF l_contact_pt_contains_str IS NULL THEN
5313: defcpt := 1;

Line 5312: IF l_contact_pt_contains_str IS NULL THEN

5308: END IF;
5309: IF l_contact_contains_str IS NULL THEN
5310: defct := 1;
5311: END IF;
5312: IF l_contact_pt_contains_str IS NULL THEN
5313: defcpt := 1;
5314: END IF;
5315:
5316: IF l_contact_contains_str IS NOT NULL THEN

Line 5316: IF l_contact_contains_str IS NOT NULL THEN

5312: IF l_contact_pt_contains_str IS NULL THEN
5313: defcpt := 1;
5314: END IF;
5315:
5316: IF l_contact_contains_str IS NOT NULL THEN
5317: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5318: LOOP
5319: FETCH l_contact_cur INTO
5320: l_org_contact_id, l_ct_party_id ;

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

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

Line 5338: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

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

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

Line 5446: l_contact_pt_contains_str VARCHAR2(32000);

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

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

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

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

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

Line 5516: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

Line 5588: l_party_contains_str VARCHAR2(32000);

5584: x_search_ctx_id IN OUT NUMBER
5585: ) IS
5586:
5587: -- Strings to hold the generated Intermedia query strings
5588: l_party_contains_str VARCHAR2(32000);
5589: l_party_site_contains_str VARCHAR2(32000);
5590: l_contact_contains_str VARCHAR2(32000);
5591: l_contact_pt_contains_str VARCHAR2(32000);
5592: l_tmp VARCHAR2(32000);

Line 5589: l_party_site_contains_str VARCHAR2(32000);

5585: ) IS
5586:
5587: -- Strings to hold the generated Intermedia query strings
5588: l_party_contains_str VARCHAR2(32000);
5589: l_party_site_contains_str VARCHAR2(32000);
5590: l_contact_contains_str VARCHAR2(32000);
5591: l_contact_pt_contains_str VARCHAR2(32000);
5592: l_tmp VARCHAR2(32000);
5593:

Line 5590: l_contact_contains_str VARCHAR2(32000);

5586:
5587: -- Strings to hold the generated Intermedia query strings
5588: l_party_contains_str VARCHAR2(32000);
5589: l_party_site_contains_str VARCHAR2(32000);
5590: l_contact_contains_str VARCHAR2(32000);
5591: l_contact_pt_contains_str VARCHAR2(32000);
5592: l_tmp VARCHAR2(32000);
5593:
5594: -- Other local variables

Line 5591: l_contact_pt_contains_str VARCHAR2(32000);

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

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

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

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

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

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

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

Line 5701: IF l_party_site_contains_str IS NOT NULL THEN

5697: 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);
5698: END LOOP;
5699: CLOSE l_party_cur;
5700:
5701: IF l_party_site_contains_str IS NOT NULL THEN
5702: l_max_score := 0;
5703: l_max_id := 0;
5704: l_max_idx := 0;
5705: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN

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

5702: l_max_score := 0;
5703: l_max_id := 0;
5704: l_max_idx := 0;
5705: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5706: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5707: ELSE
5708: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5709: END IF;
5710: LOOP

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

5704: l_max_idx := 0;
5705: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5706: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5707: ELSE
5708: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5709: END IF;
5710: LOOP
5711: FETCH l_party_site_cur INTO
5712: 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 5739: IF l_contact_contains_str IS NOT NULL THEN

5735: 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);
5736: END IF;
5737: END IF;
5738:
5739: IF l_contact_contains_str IS NOT NULL THEN
5740: l_max_score := 0;
5741: l_max_id := 0;
5742: l_max_idx := 0;
5743: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

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

5739: IF l_contact_contains_str IS NOT NULL THEN
5740: l_max_score := 0;
5741: l_max_id := 0;
5742: l_max_idx := 0;
5743: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5744: LOOP
5745: FETCH l_contact_cur INTO
5746: l_org_contact_id, l_ct_party_id ;
5747: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5761: IF l_contact_pt_contains_str IS NOT NULL THEN

5757: 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);
5758: END IF;
5759: END IF;
5760:
5761: IF l_contact_pt_contains_str IS NOT NULL THEN
5762: l_max_score := 0;
5763: l_max_id := 0;
5764: l_max_idx := 0;
5765: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN

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

5762: l_max_score := 0;
5763: l_max_id := 0;
5764: l_max_idx := 0;
5765: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5766: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5767: ELSE
5768: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5769: END IF;
5770: LOOP

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

5764: l_max_idx := 0;
5765: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5766: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5767: ELSE
5768: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5769: END IF;
5770: LOOP
5771: FETCH l_contact_pt_cur INTO
5772: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;