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_8 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 1164: l_party_contains_str VARCHAR2(32000);

1160: p_party_max_score NUMBER,
1161: p_denorm_max_score NUMBER,
1162: p_non_denorm_max_score NUMBER,
1163: p_threshold NUMBER) RETURN VARCHAR2 IS
1164: l_party_contains_str VARCHAR2(32000);
1165: l_party_filter VARCHAR2(1) := null;
1166: l_prim_temp VARCHAR2(4000);
1167: l_denorm_str VARCHAR2(4000);
1168: BEGIN

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

1204:
1205: IF l_denorm_str IS NOT NULL THEN
1206: IF (p_non_denorm_max_score+40)>=p_threshold THEN
1207: l_denorm_str := NULL;
1208: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1209: ELSIF (p_non_denorm_max_score+p_denorm_max_score+40)>=p_threshold THEN
1210: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1211: l_denorm_str := NULL;
1212: END IF;

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

1206: IF (p_non_denorm_max_score+40)>=p_threshold THEN
1207: l_denorm_str := NULL;
1208: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1209: ELSIF (p_non_denorm_max_score+p_denorm_max_score+40)>=p_threshold THEN
1210: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1211: l_denorm_str := NULL;
1212: END IF;
1213: ELSE
1214: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

1210: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1211: l_denorm_str := NULL;
1212: END IF;
1213: ELSE
1214: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1215: END IF;
1216:
1217: -- Setup query string for DUNS_NUMBER_C
1218: l_prim_temp := null;

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

1227:
1228: IF l_denorm_str IS NOT NULL THEN
1229: IF (p_non_denorm_max_score+80)>=p_threshold THEN
1230: l_denorm_str := NULL;
1231: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1232: ELSIF (p_non_denorm_max_score+p_denorm_max_score+80)>=p_threshold THEN
1233: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1234: l_denorm_str := NULL;
1235: END IF;

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

1229: IF (p_non_denorm_max_score+80)>=p_threshold THEN
1230: l_denorm_str := NULL;
1231: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1232: ELSIF (p_non_denorm_max_score+p_denorm_max_score+80)>=p_threshold THEN
1233: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1234: l_denorm_str := NULL;
1235: END IF;
1236: ELSE
1237: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

1233: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1234: l_denorm_str := NULL;
1235: END IF;
1236: ELSE
1237: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1238: END IF;
1239: IF lengthb(l_party_contains_str) > 4000 THEN
1240: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1241: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');

Line 1239: IF lengthb(l_party_contains_str) > 4000 THEN

1235: END IF;
1236: ELSE
1237: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1238: END IF;
1239: IF lengthb(l_party_contains_str) > 4000 THEN
1240: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1241: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1242: FND_MSG_PUB.ADD;
1243: RAISE FND_API.G_EXC_ERROR;

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

1241: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1242: FND_MSG_PUB.ADD;
1243: RAISE FND_API.G_EXC_ERROR;
1244: END IF;
1245: IF (p_match_str = ' AND ' OR p_party_max_score 1246: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1247: 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);
1248: END IF;
1249: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

1243: RAISE FND_API.G_EXC_ERROR;
1244: END IF;
1245: IF (p_match_str = ' AND ' OR p_party_max_score 1246: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1247: 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);
1248: END IF;
1249: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1250: 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);
1251: END IF;

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

1246: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1247: 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);
1248: END IF;
1249: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1250: 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);
1251: END IF;
1252: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1253: 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);
1254: END IF;

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

1251: END IF;
1252: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1253: 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);
1254: END IF;
1255: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1256: ELSE
1257: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1258: 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);
1259: 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 1258: 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);

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

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

1255: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1256: ELSE
1257: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1258: 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);
1259: 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);
1260: END IF;
1261: RETURN l_party_contains_str;
1262: END IF;
1263: END;

Line 1261: RETURN l_party_contains_str;

1257: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1258: 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);
1259: 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);
1260: END IF;
1261: RETURN l_party_contains_str;
1262: END IF;
1263: END;
1264: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1265: l_contains_str VARCHAR2(32000);

Line 1265: l_contains_str VARCHAR2(32000);

1261: RETURN l_party_contains_str;
1262: END IF;
1263: END;
1264: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1265: l_contains_str VARCHAR2(32000);
1266: l_contains_str_temp VARCHAR2(32000);
1267: l_den_contains_str VARCHAR2(32000);
1268: l_den_contains_str_temp VARCHAR2(32000);
1269: l_filter_str VARCHAR2(4000) := null;

Line 1266: l_contains_str_temp VARCHAR2(32000);

1262: END IF;
1263: END;
1264: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1265: l_contains_str VARCHAR2(32000);
1266: l_contains_str_temp VARCHAR2(32000);
1267: l_den_contains_str VARCHAR2(32000);
1268: l_den_contains_str_temp VARCHAR2(32000);
1269: l_filter_str VARCHAR2(4000) := null;
1270: l_prim_temp VARCHAR2(4000) := null;

Line 1267: l_den_contains_str VARCHAR2(32000);

1263: END;
1264: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1265: l_contains_str VARCHAR2(32000);
1266: l_contains_str_temp VARCHAR2(32000);
1267: l_den_contains_str VARCHAR2(32000);
1268: l_den_contains_str_temp VARCHAR2(32000);
1269: l_filter_str VARCHAR2(4000) := null;
1270: l_prim_temp VARCHAR2(4000) := null;
1271: l_prim_temp_den VARCHAR2(4000) := null;

Line 1268: l_den_contains_str_temp VARCHAR2(32000);

1264: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1265: l_contains_str VARCHAR2(32000);
1266: l_contains_str_temp VARCHAR2(32000);
1267: l_den_contains_str VARCHAR2(32000);
1268: l_den_contains_str_temp VARCHAR2(32000);
1269: l_filter_str VARCHAR2(4000) := null;
1270: l_prim_temp VARCHAR2(4000) := null;
1271: l_prim_temp_den VARCHAR2(4000) := null;
1272: BEGIN

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

1274: 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);
1275: END IF;
1276: x_denorm_str := NULL;
1277:
1278: -- Dynamic setup of party site contains str
1279: --
1280: -- For each primary transformation add to intermedia query if it
1281: -- is not null
1282: FOR I IN 1..g_party_site_stage_list.COUNT LOOP

Line 1283: l_contains_str_temp := null;

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

Line 1284: l_den_contains_str_temp := null;

1280: -- For each primary transformation add to intermedia query if it
1281: -- is not null
1282: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1283: l_contains_str_temp := null;
1284: l_den_contains_str_temp := null;
1285: l_filter_str := null;
1286:
1287: -- Setup query string for ADDRESS
1288: l_prim_temp := null;

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

1288: l_prim_temp := null;
1289: l_prim_temp_den := null;
1290: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRADDRESS
1291: g_party_site_stage_list(I).TX4,'A27',l_prim_temp);
1292: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1293:
1294: -- Setup query string for CITY
1295: l_prim_temp := null;
1296: l_prim_temp_den := null;

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

1297: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1298: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1299: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1300: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1301: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1302: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1303:
1304: -- Setup query string for POSTAL_CODE
1305: l_prim_temp := null;

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

1298: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1299: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1300: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1301: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1302: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1303:
1304: -- Setup query string for POSTAL_CODE
1305: l_prim_temp := null;
1306: l_prim_temp_den := null;

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

1307: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1308: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1309: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1310: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1311: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1312: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1313:
1314: -- Setup query string for STATE
1315: l_prim_temp := null;

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

1308: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1309: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1310: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1311: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1312: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1313:
1314: -- Setup query string for STATE
1315: l_prim_temp := null;
1316: l_prim_temp_den := null;

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

1317: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1318: g_party_site_stage_list(I).TX15,'A32',l_prim_temp);
1319: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1320: g_party_site_stage_list(I).TX15,'D_PS',l_prim_temp_den);
1321: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1322: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1323:
1324: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1325: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);

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

1318: g_party_site_stage_list(I).TX15,'A32',l_prim_temp);
1319: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1320: g_party_site_stage_list(I).TX15,'D_PS',l_prim_temp_den);
1321: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1322: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1323:
1324: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1325: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1326: END LOOP;

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

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

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

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

Line 1328: IF lengthb(l_contains_str) > 4000 THEN

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

Line 1334: x_denorm_str := l_den_contains_str;

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

Line 1335: RETURN l_contains_str;

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

Line 1339: l_contains_str VARCHAR2(32000);

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

Line 1340: l_contains_str_temp VARCHAR2(32000);

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

Line 1341: l_den_contains_str VARCHAR2(32000);

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

Line 1342: l_den_contains_str_temp VARCHAR2(32000);

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

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

1348: hz_utility_v2pub.debug(p_message=>'INIT_CONTACTS_QUERY ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
1349: END IF;
1350: x_denorm_str := NULL;
1351:
1352: -- Dynamic setup of party site contains str
1353: --
1354: -- For each primary transformation add to intermedia query if it
1355: -- is not null
1356: FOR I IN 1..g_contact_stage_list.COUNT LOOP

Line 1357: l_contains_str_temp := null;

1353: --
1354: -- For each primary transformation add to intermedia query if it
1355: -- is not null
1356: FOR I IN 1..g_contact_stage_list.COUNT LOOP
1357: l_contains_str_temp := null;
1358: l_den_contains_str_temp := null;
1359: l_filter_str := null;
1360:
1361: -- Setup query string for CONTACT_NAME

Line 1358: l_den_contains_str_temp := null;

1354: -- For each primary transformation add to intermedia query if it
1355: -- is not null
1356: FOR I IN 1..g_contact_stage_list.COUNT LOOP
1357: l_contains_str_temp := null;
1358: l_den_contains_str_temp := null;
1359: l_filter_str := null;
1360:
1361: -- Setup query string for CONTACT_NAME
1362: l_prim_temp := null;

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

1362: l_prim_temp := null;
1363: l_prim_temp_den := null;
1364: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRPERSON
1365: g_contact_stage_list(I).TX6,'A39',l_prim_temp);
1366: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1367:
1368: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1369: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1370: END LOOP;

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

1364: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRPERSON
1365: g_contact_stage_list(I).TX6,'A39',l_prim_temp);
1366: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1367:
1368: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1369: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1370: END LOOP;
1371: -- Add the search criteria to query string
1372: IF lengthb(l_contains_str) > 4000 THEN

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

1365: g_contact_stage_list(I).TX6,'A39',l_prim_temp);
1366: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1367:
1368: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1369: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1370: END LOOP;
1371: -- Add the search criteria to query string
1372: IF lengthb(l_contains_str) > 4000 THEN
1373: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');

Line 1372: IF lengthb(l_contains_str) > 4000 THEN

1368: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1369: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1370: END LOOP;
1371: -- Add the search criteria to query string
1372: IF lengthb(l_contains_str) > 4000 THEN
1373: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1374: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACTS');
1375: FND_MSG_PUB.ADD;
1376: RAISE FND_API.G_EXC_ERROR;

Line 1378: x_denorm_str := l_den_contains_str;

1374: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACTS');
1375: FND_MSG_PUB.ADD;
1376: RAISE FND_API.G_EXC_ERROR;
1377: END IF;
1378: x_denorm_str := l_den_contains_str;
1379: RETURN l_contains_str;
1380: END;
1381:
1382: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS

Line 1379: RETURN l_contains_str;

1375: FND_MSG_PUB.ADD;
1376: RAISE FND_API.G_EXC_ERROR;
1377: END IF;
1378: x_denorm_str := l_den_contains_str;
1379: RETURN l_contains_str;
1380: END;
1381:
1382: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1383: l_contains_str VARCHAR2(32000);

Line 1383: l_contains_str VARCHAR2(32000);

1379: RETURN l_contains_str;
1380: END;
1381:
1382: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1383: l_contains_str VARCHAR2(32000);
1384: l_contains_str_temp VARCHAR2(32000);
1385: l_den_contains_str VARCHAR2(32000);
1386: l_den_contains_str_temp VARCHAR2(32000);
1387: l_filter_str VARCHAR2(4000) := null;

Line 1384: l_contains_str_temp VARCHAR2(32000);

1380: END;
1381:
1382: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1383: l_contains_str VARCHAR2(32000);
1384: l_contains_str_temp VARCHAR2(32000);
1385: l_den_contains_str VARCHAR2(32000);
1386: l_den_contains_str_temp VARCHAR2(32000);
1387: l_filter_str VARCHAR2(4000) := null;
1388: l_prim_temp VARCHAR2(4000) := null;

Line 1385: l_den_contains_str VARCHAR2(32000);

1381:
1382: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1383: l_contains_str VARCHAR2(32000);
1384: l_contains_str_temp VARCHAR2(32000);
1385: l_den_contains_str VARCHAR2(32000);
1386: l_den_contains_str_temp VARCHAR2(32000);
1387: l_filter_str VARCHAR2(4000) := null;
1388: l_prim_temp VARCHAR2(4000) := null;
1389: l_prim_temp_den VARCHAR2(4000) := null;

Line 1386: l_den_contains_str_temp VARCHAR2(32000);

1382: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1383: l_contains_str VARCHAR2(32000);
1384: l_contains_str_temp VARCHAR2(32000);
1385: l_den_contains_str VARCHAR2(32000);
1386: l_den_contains_str_temp VARCHAR2(32000);
1387: l_filter_str VARCHAR2(4000) := null;
1388: l_prim_temp VARCHAR2(4000) := null;
1389: l_prim_temp_den VARCHAR2(4000) := null;
1390: TYPE CONTACT_PT_REC_TYPE IS RECORD (

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

1398: hz_utility_v2pub.debug(p_message=>'INIT_CONTACT_POINTS_QUERY ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
1399: END IF;
1400: x_denorm_str := NULL;
1401:
1402: -- Dynamic setup of party site contains str
1403: --
1404: -- For each primary transformation add to intermedia query if it
1405: -- is not null
1406: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP

Line 1407: l_contains_str_temp := null;

1403: --
1404: -- For each primary transformation add to intermedia query if it
1405: -- is not null
1406: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1407: l_contains_str_temp := null;
1408: l_den_contains_str_temp := null;
1409: l_filter_str := null;
1410: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1411: if(l_cnt_pt_type.count>0) then

Line 1408: l_den_contains_str_temp := null;

1404: -- For each primary transformation add to intermedia query if it
1405: -- is not null
1406: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1407: l_contains_str_temp := null;
1408: l_den_contains_str_temp := null;
1409: l_filter_str := null;
1410: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1411: if(l_cnt_pt_type.count>0) then
1412: x_modify := 'Y';

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

1428: l_prim_temp := null;
1429: l_prim_temp_den := null;
1430: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL CTX
1431: g_contact_pt_stage_list(I).TX1,'A44',l_prim_temp);
1432: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1433:
1434: -- Setup query string for EMAIL_ADDRESS
1435: l_prim_temp := null;
1436: l_prim_temp_den := null;

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

1435: l_prim_temp := null;
1436: l_prim_temp_den := null;
1437: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (EMAIL) + WRDOMAIN
1438: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1439: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1440:
1441: -- Setup query string for URL
1442: l_prim_temp := null;
1443: l_prim_temp_den := null;

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

1442: l_prim_temp := null;
1443: l_prim_temp_den := null;
1444: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (URL) + WRDOMAIN
1445: g_contact_pt_stage_list(I).TX8,'A49',l_prim_temp);
1446: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1447:
1448: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1449: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1450: IF N>1 THEN

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

1444: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (URL) + WRDOMAIN
1445: g_contact_pt_stage_list(I).TX8,'A49',l_prim_temp);
1446: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1447:
1448: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1449: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1450: IF N>1 THEN
1451: distinct_search_cpt_types := N-1;
1452: ELSE

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

1445: g_contact_pt_stage_list(I).TX8,'A49',l_prim_temp);
1446: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1447:
1448: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1449: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1450: IF N>1 THEN
1451: distinct_search_cpt_types := N-1;
1452: ELSE
1453: distinct_search_cpt_types := N;

Line 1460: IF lengthb(l_contains_str) > 4000 THEN

1456: hz_utility_v2pub.debug(p_message=>'distinct_search_cpt_types is - '||distinct_search_cpt_types,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1457: END IF;
1458: END LOOP;
1459: -- Add the search criteria to query string
1460: IF lengthb(l_contains_str) > 4000 THEN
1461: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1462: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1463: FND_MSG_PUB.ADD;
1464: RAISE FND_API.G_EXC_ERROR;

Line 1466: x_denorm_str := l_den_contains_str;

1462: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1463: FND_MSG_PUB.ADD;
1464: RAISE FND_API.G_EXC_ERROR;
1465: END IF;
1466: x_denorm_str := l_den_contains_str;
1467: RETURN l_contains_str;
1468: END;
1469:
1470: PROCEDURE open_party_cursor_direct (

Line 1467: RETURN l_contains_str;

1463: FND_MSG_PUB.ADD;
1464: RAISE FND_API.G_EXC_ERROR;
1465: END IF;
1466: x_denorm_str := l_den_contains_str;
1467: RETURN l_contains_str;
1468: END;
1469:
1470: PROCEDURE open_party_cursor_direct (
1471: p_dup_party_id NUMBER,

Line 1475: p_party_contains_str VARCHAR2,

1471: p_dup_party_id NUMBER,
1472: p_restrict_sql VARCHAR2,
1473: p_match_str VARCHAR2,
1474: p_search_merged VARCHAR2,
1475: p_party_contains_str VARCHAR2,
1476: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1477: l_sqlstr VARCHAR2(4000);
1478: l_search_merged VARCHAR2(1);
1479: BEGIN

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

1488: END IF;
1489: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1490: 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);
1491: END IF;
1492: 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
1493: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1494: 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);
1495: END IF;
1496: OPEN x_cursor FOR

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

1523: l_sqlstr := l_sqlstr || ' (TX41 like :TX41||'' %'' ';
1524: l_sqlstr := l_sqlstr || ' )) ';
1525: l_sqlstr := l_sqlstr || ' ) ';
1526: END IF;
1527: 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
1528: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1529: 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);
1530: END IF;
1531: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';

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

1525: l_sqlstr := l_sqlstr || ' ) ';
1526: END IF;
1527: 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
1528: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1529: 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);
1530: END IF;
1531: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1532: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX10||'' %'' ';
1533: l_sqlstr := l_sqlstr || ' )) ';

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

1552: END IF;
1553: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1554: 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);
1555: END IF;
1556: 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
1557: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1558: 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);
1559: END IF;
1560: 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 1558: 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);

1554: 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);
1555: END IF;
1556: 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
1557: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1558: 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);
1559: END IF;
1560: 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
1561: ,g_party_stage_rec.TX40
1562: ,g_party_stage_rec.TX40

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

1578: ,g_party_stage_rec.TX40
1579: ,g_party_stage_rec.TX41
1580: ,g_party_stage_rec.TX41
1581: ;
1582: 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
1583: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1584: 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);
1585: END IF;
1586: 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 1584: 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);

1580: ,g_party_stage_rec.TX41
1581: ;
1582: 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
1583: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1584: 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);
1585: END IF;
1586: 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
1587: ,g_party_site_stage_list(1).TX10
1588: ,g_party_site_stage_list(1).TX10

Line 1605: p_contains_str VARCHAR2,

1601: END;
1602: PROCEDURE open_party_cursor(
1603: p_dup_party_id NUMBER,
1604: p_restrict_sql VARCHAR2,
1605: p_contains_str VARCHAR2,
1606: p_search_ctx_id NUMBER,
1607: p_match_str VARCHAR2,
1608: p_search_merged VARCHAR2,
1609: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS

Line 1624: IF p_contains_str IS NULL THEN

1620: l_search_merged := 'N';
1621: ELSE
1622: l_search_merged := p_search_merged;
1623: END IF;
1624: IF p_contains_str IS NULL THEN
1625: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1626: 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);
1627: END IF;
1628: OPEN x_cursor FOR

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

1622: l_search_merged := p_search_merged;
1623: END IF;
1624: IF p_contains_str IS NULL THEN
1625: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1626: 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);
1627: END IF;
1628: OPEN x_cursor FOR
1629: SELECT PARTY_ID , TX39, TX40, lpad(rtrim(TX41),9,chr(48))
1630: FROM HZ_STAGED_PARTIES stage

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

1639: END IF;
1640: OPEN x_cursor FOR
1641: SELECT PARTY_ID , TX39, TX40, lpad(rtrim(TX41),9,chr(48))
1642: FROM HZ_STAGED_PARTIES stage
1643: WHERE contains( concat_col, p_contains_str)>0
1644: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1645: AND( (l_search_merged ='Y' )
1646: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1647: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )

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

1652: END IF;
1653: OPEN x_cursor FOR
1654: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX39, TX40, lpad(rtrim(TX41),9,chr(48))
1655: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1656: WHERE contains( concat_col, p_contains_str)>0
1657: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1658: AND d.party_id = stage.party_id
1659: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1660: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)

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

1675: END IF;
1676: IF p_search_ctx_id IS NULL THEN
1677: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX39, TX40, lpad(rtrim(TX41),9,chr(48))'||
1678: ' FROM HZ_STAGED_PARTIES stage'||
1679: ' WHERE contains( concat_col, :cont)>0'||
1680: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1681: ' AND ('||p_restrict_sql||')' ||
1682: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1683: IF l_search_merged = 'Y' THEN

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

1687: ELSE
1688: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1689: END IF;
1690: output_long_strings('----------------------------------------------------------');
1691: output_long_strings('Party Contains String = '||p_contains_str);
1692: output_long_strings('Restrict Sql = '||p_restrict_sql);
1693: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1694: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1695: ,p_dup_party_id, p_dup_party_id;

Line 1693: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1689: END IF;
1690: output_long_strings('----------------------------------------------------------');
1691: output_long_strings('Party Contains String = '||p_contains_str);
1692: output_long_strings('Restrict Sql = '||p_restrict_sql);
1693: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1694: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1695: ,p_dup_party_id, p_dup_party_id;
1696: END IF;
1697: END IF;

Line 1724: p_contains_str VARCHAR2,

1720: PROCEDURE open_party_site_cursor(
1721: p_dup_party_id NUMBER,
1722: p_party_id NUMBER,
1723: p_restrict_sql VARCHAR2,
1724: p_contains_str VARCHAR2,
1725: p_search_ctx_id NUMBER,
1726: p_search_merged VARCHAR2,
1727: p_search_rel_sites VARCHAR2,
1728: p_person_api VARCHAR2,

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

1752: IF p_search_rel_sites = 'N' THEN
1753: OPEN x_cursor FOR
1754: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15
1755: FROM HZ_STAGED_PARTY_SITES stage
1756: WHERE contains( concat_col, p_contains_str)>0
1757: AND( (l_search_merged ='Y' )
1758: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1759: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1760: AND stage.party_id = p_party_id;

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

1761: ELSE
1762: OPEN x_cursor FOR
1763: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15
1764: FROM HZ_STAGED_PARTY_SITES stage
1765: WHERE contains( concat_col, p_contains_str)>0
1766: AND( (l_search_merged ='Y' )
1767: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1768: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1769: AND stage.party_id = p_party_id

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

1769: AND stage.party_id = p_party_id
1770: UNION
1771: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15
1772: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1773: WHERE contains( concat_col, p_contains_str)>0
1774: AND( (l_search_merged ='Y' )
1775: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1776: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1777: AND r.object_id = p_party_id

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

1791: END IF;
1792: OPEN x_cursor FOR
1793: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15
1794: FROM HZ_STAGED_PARTY_SITES stage
1795: WHERE contains( concat_col, p_contains_str)>0
1796: AND EXISTS (
1797: SELECT 1 FROM HZ_STAGED_PARTIES p
1798: WHERE p.PARTY_ID = stage.PARTY_ID
1799: AND( (l_search_merged ='Y' )

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

1811: IF p_person_api = 'Y' THEN
1812: OPEN x_cursor FOR
1813: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15
1814: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1815: WHERE contains( concat_col, p_contains_str)>0
1816: AND( (l_search_merged ='Y' )
1817: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1818: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1819: AND d.search_context_id = p_search_ctx_id

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

1821: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1822: UNION
1823: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15
1824: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1825: WHERE contains( concat_col, p_contains_str)>0
1826: AND( (l_search_merged ='Y' )
1827: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1828: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1829: AND d.search_context_id = p_search_ctx_id

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

1834: ELSE
1835: OPEN x_cursor FOR
1836: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX14, TX15
1837: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1838: WHERE contains( concat_col, p_contains_str)>0
1839: AND( (l_search_merged ='Y' )
1840: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1841: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1842: AND d.search_context_id = p_search_ctx_id

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

1879: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1880: END IF;
1881: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX14, TX15'||
1882: ' FROM HZ_STAGED_PARTY_SITES stage'||
1883: ' WHERE contains( concat_col, :cont)>0'||
1884: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1885: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1886: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1887: ' AND (ORG_CONTACT_ID IS NULL ' ||

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

1889: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1890: ' UNION ' ||
1891: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX14, TX15'||
1892: ' FROM HZ_STAGED_PARTY_SITES stage'||
1893: ' WHERE contains( concat_col, :cont)>0'||
1894: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1895: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1896: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1897: ' AND ORG_CONTACT_ID IN ' ||

Line 1903: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1899: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1900: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1901: ' and ('||p_restrict_sql1|| ') )' ||
1902: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1903: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1904: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1905: ELSE
1906: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX14, TX15'||
1907: ' FROM HZ_STAGED_PARTY_SITES stage'||

Line 1904: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1900: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1901: ' and ('||p_restrict_sql1|| ') )' ||
1902: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1903: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1904: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1905: ELSE
1906: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX14, TX15'||
1907: ' FROM HZ_STAGED_PARTY_SITES stage'||
1908: ' WHERE contains( concat_col, :cont)>0'||

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

1904: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1905: ELSE
1906: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX14, TX15'||
1907: ' FROM HZ_STAGED_PARTY_SITES stage'||
1908: ' WHERE contains( concat_col, :cont)>0'||
1909: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1910: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1911: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1912: ' AND EXISTS ('||

Line 1918: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1914: ' WHERE p.party_id = stage.party_id ' ||
1915: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1916: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1917: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1918: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1919: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1920: ,p_dup_party_id, p_dup_party_id;
1921: END IF;
1922: ELSE

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

1921: END IF;
1922: ELSE
1923: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX14, TX15'||
1924: ' FROM HZ_STAGED_PARTY_SITES stage'||
1925: ' WHERE contains( concat_col, :cont)>0'||
1926: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1927: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1928: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1929: ' AND EXISTS ('||

Line 1935: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1931: ' WHERE p.party_id = stage.party_id ' ||
1932: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1933: ' AND ('||p_restrict_sql||')' ||
1934: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1935: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1936: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1937: ,p_dup_party_id, p_dup_party_id;
1938: END IF;
1939: END IF;

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

1937: ,p_dup_party_id, p_dup_party_id;
1938: END IF;
1939: END IF;
1940: output_long_strings('----------------------------------------------------------');
1941: output_long_strings('Party Site Contains String = '||p_contains_str);
1942: output_long_strings('Restrict Sql = '||p_restrict_sql);
1943: exception
1944: when others then
1945: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1969: p_contains_str VARCHAR2,

1965: PROCEDURE open_contact_cursor(
1966: p_dup_party_id NUMBER,
1967: p_party_id NUMBER,
1968: p_restrict_sql VARCHAR2,
1969: p_contains_str VARCHAR2,
1970: p_search_ctx_id NUMBER,
1971: p_search_merged VARCHAR2,
1972: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1973: l_sqlstr VARCHAR2(4000);

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

1993: END IF;
1994: OPEN x_cursor FOR
1995: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID, TX6, TX5
1996: FROM HZ_STAGED_CONTACTS stage
1997: WHERE contains( concat_col, p_contains_str)>0
1998: AND EXISTS (
1999: SELECT 1 FROM HZ_STAGED_PARTIES p
2000: WHERE p.PARTY_ID = stage.PARTY_ID
2001: AND( (l_search_merged ='Y' )

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

2016: END IF;
2017: OPEN x_cursor FOR
2018: SELECT ORG_CONTACT_ID, PARTY_ID, TX6, TX5
2019: FROM HZ_STAGED_CONTACTS stage
2020: WHERE contains( concat_col, p_contains_str)>0
2021: AND EXISTS (
2022: SELECT 1 FROM HZ_STAGED_PARTIES p
2023: WHERE p.PARTY_ID = stage.PARTY_ID
2024: AND( (l_search_merged ='Y' )

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

2035: END IF;
2036: OPEN x_cursor FOR
2037: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID, TX6, TX5
2038: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
2039: WHERE contains( concat_col, p_contains_str)>0
2040: AND d.search_context_id = p_search_ctx_id
2041: AND d.party_id = stage.party_id
2042: AND( (l_search_merged ='Y' )
2043: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2069: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
2070: END IF;
2071: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID , TX6, TX5'||
2072: ' FROM HZ_STAGED_CONTACTS stage'||
2073: ' WHERE contains( concat_col, :cont)>0'||
2074: ' AND EXISTS ('||
2075: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2076: ' WHERE p.party_id = stage.party_id ' ||
2077: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 2083: OPEN x_cursor FOR l_sqlstr USING p_contains_str

2079: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2080: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2081: ' AND ('||p_restrict_sql||')' ||
2082: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2083: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2084: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2085: ,p_dup_party_id, p_dup_party_id;
2086: END IF;
2087: output_long_strings('----------------------------------------------------------');

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

2084: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2085: ,p_dup_party_id, p_dup_party_id;
2086: END IF;
2087: output_long_strings('----------------------------------------------------------');
2088: output_long_strings('Contacts Contains String = '||p_contains_str);
2089: output_long_strings('Restrict Sql = '||p_restrict_sql);
2090: exception
2091: when others then
2092: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 2116: p_contains_str VARCHAR2,

2112: PROCEDURE open_contact_pt_cursor(
2113: p_dup_party_id NUMBER,
2114: p_party_id NUMBER,
2115: p_restrict_sql VARCHAR2,
2116: p_contains_str VARCHAR2,
2117: p_search_ctx_id NUMBER,
2118: p_search_merged VARCHAR2,
2119: p_search_rel_cpts VARCHAR2,
2120: p_person_api VARCHAR2,

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

2142: THEN
2143: OPEN x_cursor FOR
2144: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5, TX6, TX8
2145: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2146: WHERE contains( concat_col, p_contains_str)>0
2147: AND d.search_context_id = p_search_ctx_id
2148: AND( (l_search_merged ='Y' )
2149: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2150: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2154: THEN
2155: OPEN x_cursor FOR
2156: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N3) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5, TX6, TX8
2157: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2158: WHERE contains( concat_col, p_contains_str)>0
2159: AND d.search_context_id = p_search_ctx_id
2160: AND( (l_search_merged ='Y' )
2161: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2162: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2171: IF p_search_rel_cpts = 'N' THEN
2172: OPEN x_cursor FOR
2173: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5, TX6, TX8
2174: FROM HZ_STAGED_CONTACT_POINTS stage
2175: WHERE contains( concat_col, p_contains_str)>0
2176: AND( (l_search_merged ='Y' )
2177: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2178: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2179: AND stage.party_id = p_party_id;

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

2180: ELSE
2181: OPEN x_cursor FOR
2182: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5, TX6, TX8
2183: FROM HZ_STAGED_CONTACT_POINTS stage
2184: WHERE contains( concat_col, p_contains_str)>0
2185: AND( (l_search_merged ='Y' )
2186: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2187: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2188: AND stage.party_id = p_party_id

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

2188: AND stage.party_id = p_party_id
2189: UNION
2190: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ stage.CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , TX1, TX5, TX6, TX8
2191: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2192: WHERE contains( concat_col, p_contains_str)>0
2193: AND r.object_id = p_party_id
2194: AND( (l_search_merged ='Y' )
2195: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2196: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2210: END IF;
2211: OPEN x_cursor FOR
2212: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5, TX6, TX8
2213: FROM HZ_STAGED_CONTACT_POINTS stage
2214: WHERE contains( concat_col, p_contains_str)>0
2215: AND EXISTS (
2216: SELECT 1 FROM HZ_STAGED_PARTIES p
2217: WHERE p.PARTY_ID = stage.PARTY_ID
2218: AND( (l_search_merged ='Y' )

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

2230: IF p_person_api = 'Y' THEN
2231: OPEN x_cursor FOR
2232: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5, TX6, TX8
2233: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2234: WHERE contains( concat_col, p_contains_str)>0
2235: AND d.search_context_id = p_search_ctx_id
2236: AND d.party_id = stage.party_id
2237: AND( (l_search_merged ='Y' )
2238: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2240: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2241: UNION
2242: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, r.subject_id, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , TX1, TX5, TX6, TX8
2243: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2244: WHERE contains( concat_col, p_contains_str)>0
2245: AND d.search_context_id = p_search_ctx_id
2246: AND d.party_id = r.subject_id
2247: AND r.relationship_id = oc.party_relationship_id
2248: AND oc.org_contact_id = stage.org_contact_id

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

2253: ELSE
2254: OPEN x_cursor FOR
2255: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5, TX6, TX8
2256: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2257: WHERE contains( concat_col, p_contains_str)>0
2258: AND d.search_context_id = p_search_ctx_id
2259: AND d.party_id = stage.party_id
2260: AND( (l_search_merged ='Y' )
2261: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2298: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2299: END IF;
2300: l_sqlstr := ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5, TX6, TX8'||
2301: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2302: ' WHERE contains( concat_col, :cont)>0 '||
2303: ' AND (stage.org_contact_id is null '||
2304: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2305: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2306: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

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

2308: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2309: ' UNION ' ||
2310: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5, TX6, TX8'||
2311: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2312: ' WHERE contains( concat_col, :cont)>0 '||
2313: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2314: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2315: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2316: ' AND (stage.org_contact_id in ' ||

Line 2322: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

2318: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2319: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2320: ' and ('||p_restrict_sql1 || ') ) )' ||
2321: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2322: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2323: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2324: ELSE
2325: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5, TX6, TX8'||
2326: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

Line 2323: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

2319: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2320: ' and ('||p_restrict_sql1 || ') ) )' ||
2321: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2322: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2323: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2324: ELSE
2325: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5, TX6, TX8'||
2326: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2327: ' WHERE contains( concat_col, :cont)>0'||

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

2323: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2324: ELSE
2325: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5, TX6, TX8'||
2326: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2327: ' WHERE contains( concat_col, :cont)>0'||
2328: ' AND EXISTS ('||
2329: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2330: ' WHERE p.party_id = stage.party_id ' ||
2331: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 2337: OPEN x_cursor FOR l_sqlstr USING p_contains_str

2333: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2334: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2335: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2336: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2337: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2338: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2339: ,p_dup_party_id, p_dup_party_id;
2340: END IF;
2341: ELSE

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

2340: END IF;
2341: ELSE
2342: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5, TX6, TX8'||
2343: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2344: ' WHERE contains( concat_col, :cont)>0'||
2345: ' AND EXISTS ('||
2346: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2347: ' WHERE p.party_id = stage.party_id ' ||
2348: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 2354: OPEN x_cursor FOR l_sqlstr USING p_contains_str

2350: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2351: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2352: ' AND ('||p_restrict_sql||')' ||
2353: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2354: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2355: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2356: ,p_dup_party_id, p_dup_party_id;
2357: END IF;
2358: END IF;

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

2357: END IF;
2358: END IF;
2359: END IF;
2360: output_long_strings('----------------------------------------------------------');
2361: output_long_strings('Contact Points Contains String = '||p_contains_str);
2362: output_long_strings('Restrict Sql = '||p_restrict_sql);
2363: exception
2364: when others then
2365: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 2482: l_party_contains_str VARCHAR2(32000);

2478: x_num_matches OUT NUMBER
2479: ) IS
2480:
2481: -- Strings to hold the generated Intermedia query strings
2482: l_party_contains_str VARCHAR2(32000);
2483: l_party_site_contains_str VARCHAR2(32000);
2484: l_contact_contains_str VARCHAR2(32000);
2485: l_contact_pt_contains_str VARCHAR2(32000);
2486: l_denorm_str VARCHAR2(32000);

Line 2483: l_party_site_contains_str VARCHAR2(32000);

2479: ) IS
2480:
2481: -- Strings to hold the generated Intermedia query strings
2482: l_party_contains_str VARCHAR2(32000);
2483: l_party_site_contains_str VARCHAR2(32000);
2484: l_contact_contains_str VARCHAR2(32000);
2485: l_contact_pt_contains_str VARCHAR2(32000);
2486: l_denorm_str VARCHAR2(32000);
2487: l_ps_denorm_str VARCHAR2(32000);

Line 2484: l_contact_contains_str VARCHAR2(32000);

2480:
2481: -- Strings to hold the generated Intermedia query strings
2482: l_party_contains_str VARCHAR2(32000);
2483: l_party_site_contains_str VARCHAR2(32000);
2484: l_contact_contains_str VARCHAR2(32000);
2485: l_contact_pt_contains_str VARCHAR2(32000);
2486: l_denorm_str VARCHAR2(32000);
2487: l_ps_denorm_str VARCHAR2(32000);
2488: l_ct_denorm_str VARCHAR2(32000);

Line 2485: l_contact_pt_contains_str VARCHAR2(32000);

2481: -- Strings to hold the generated Intermedia query strings
2482: l_party_contains_str VARCHAR2(32000);
2483: l_party_site_contains_str VARCHAR2(32000);
2484: l_contact_contains_str VARCHAR2(32000);
2485: l_contact_pt_contains_str VARCHAR2(32000);
2486: l_denorm_str VARCHAR2(32000);
2487: l_ps_denorm_str VARCHAR2(32000);
2488: l_ct_denorm_str VARCHAR2(32000);
2489: l_cpt_denorm_str VARCHAR2(32000);

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

2580: call_type(2) := 'OR';
2581: END;
2582:
2583: /** Private procedure to acquire and score at party level ***/
2584: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2585: l_party_id_idx NUMBER:=1;
2586: l_ctx_id NUMBER;
2587: l_precalc_score BOOLEAN := FALSE;
2588: BEGIN

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

2611: l_search_ctx_id,H_PARTY_ID(I));
2612: l_party_id_idx := H_PARTY_ID.COUNT+1;
2613: ELSE
2614: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2615: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2616: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2617: hz_utility_v2pub.debug(p_message=>'In eval party level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2618: END IF;
2619: IF 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 2623: IF (l_party_site_contains_str IS NULL AND

2619: 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
2620: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2621: 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);
2622: END IF;
2623: IF (l_party_site_contains_str IS NULL AND
2624: l_contact_contains_str IS NULL AND
2625: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2626: 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);
2627: RAISE L_RETURN_IMM_EXC;

Line 2624: l_contact_contains_str IS NULL AND

2620: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2621: 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);
2622: END IF;
2623: IF (l_party_site_contains_str IS NULL AND
2624: l_contact_contains_str IS NULL AND
2625: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2626: 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);
2627: RAISE L_RETURN_IMM_EXC;
2628: ELSE

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

2621: 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);
2622: END IF;
2623: IF (l_party_site_contains_str IS NULL AND
2624: l_contact_contains_str IS NULL AND
2625: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2626: 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);
2627: RAISE L_RETURN_IMM_EXC;
2628: ELSE
2629: 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 2629: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);

2625: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2626: 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);
2627: RAISE L_RETURN_IMM_EXC;
2628: ELSE
2629: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2630: END IF;
2631: ELSE
2632: IF p_index>1 THEN
2633: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

2648: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2649: 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);
2650: END IF;
2651: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2652: 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);
2653: END IF;
2654: LOOP
2655: FETCH l_party_cur INTO
2656: l_party_id , l_TX39, l_TX40, l_TX41;

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

2670: l_party_id_idx:= l_party_id_idx+1;
2671: END IF;
2672: IF (l_party_id_idx-1)>l_max_thresh THEN
2673: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2674: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2675: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2676: AND l_contact_pt_contains_str IS NULL THEN
2677: EXIT;
2678: END IF;

Line 2676: AND l_contact_pt_contains_str IS NULL THEN

2672: IF (l_party_id_idx-1)>l_max_thresh THEN
2673: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2674: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2675: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2676: AND l_contact_pt_contains_str IS NULL THEN
2677: EXIT;
2678: END IF;
2679: CLOSE l_party_cur;
2680: IF p_index>1 THEN

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

2701: ROLLBACK to eval_start;
2702: END;
2703:
2704: /** Private procedure to acquire and score at party site level ***/
2705: 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
2706: l_party_id_idx NUMBER:=1;
2707: l_ctx_id NUMBER;
2708: h_ps_id HZ_PARTY_SEARCH.IDList;
2709: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

2713: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2714: hz_utility_v2pub.debug(p_message=>'-----------------',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2715: 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);
2716: END IF;
2717: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2718: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2719: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2720: l_party_id := H_SCORES.FIRST;
2721: WHILE l_party_id IS NOT NULL LOOP

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

2714: hz_utility_v2pub.debug(p_message=>'-----------------',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2715: 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);
2716: END IF;
2717: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2718: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2719: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2720: l_party_id := H_SCORES.FIRST;
2721: WHILE l_party_id IS NOT NULL LOOP
2722: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;

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

2715: 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);
2716: END IF;
2717: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2718: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2719: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2720: l_party_id := H_SCORES.FIRST;
2721: WHILE l_party_id IS NOT NULL LOOP
2722: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2723: l_party_id:=H_SCORES.NEXT(l_party_id);

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

2745: l_search_ctx_id,H_PARTY_ID(I));
2746: l_party_id_idx := H_PARTY_ID.COUNT+1;
2747: ELSE
2748: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2749: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2750: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2751: 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);
2752: END IF;
2753: IF p_index>1 THEN

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

2767: END IF;
2768: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2769: 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);
2770: END IF;
2771: 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);
2772: LOOP
2773: FETCH l_party_site_cur INTO
2774: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX14, l_TX15;
2775: EXIT WHEN l_party_site_cur%NOTFOUND;

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

2823: END IF;
2824: END;
2825:
2826: /** Private procedure to acquire and score at contact point level ***/
2827: 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
2828: l_party_id_idx NUMBER:=1;
2829: l_ctx_id NUMBER;
2830: h_ct_id HZ_PARTY_SEARCH.IDList;
2831: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

2857: l_search_ctx_id,H_PARTY_ID(I));
2858: l_party_id_idx := H_PARTY_ID.COUNT+1;
2859: ELSE
2860: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2861: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
2862: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2863: 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);
2864: END IF;
2865: IF p_index>1 THEN

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

2879: END IF;
2880: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2881: 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);
2882: END IF;
2883: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2884: LOOP
2885: FETCH l_contact_cur INTO
2886: l_org_contact_id, l_ct_party_id , l_TX6, l_TX5;
2887: EXIT WHEN l_contact_cur%NOTFOUND;

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

2932: END IF;
2933: END;
2934:
2935: /** Private procedure to acquire and score at contact point level ***/
2936: 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
2937: l_party_id_idx NUMBER:=1;
2938: l_ctx_id NUMBER;
2939: h_cpt_id HZ_PARTY_SEARCH.IDList;
2940: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

2968: l_search_ctx_id,H_PARTY_ID(I));
2969: l_party_id_idx := H_PARTY_ID.COUNT+1;
2970: ELSE
2971: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2972: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
2973: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2974: 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);
2975: END IF;
2976: IF p_index>1 THEN

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

2990: END IF;
2991: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2992: 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);
2993: END IF;
2994: 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);
2995: LOOP
2996: FETCH l_contact_pt_cur INTO
2997: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX5, l_TX6, l_TX8;
2998: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 3175: IF l_party_contains_str IS NOT NULL THEN

3171: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3172: l_threshold := round(( l_entered_max_score / 460) * 78);
3173: other_acq_criteria_exists := TRUE ;
3174: --check if acquisition criteria exists for any other entity
3175: IF l_party_contains_str IS NOT NULL THEN
3176: acq_cnt := 1;
3177: END IF;
3178: IF l_party_site_contains_str IS NOT NULL THEN
3179: acq_cnt := acq_cnt+1;

Line 3178: IF l_party_site_contains_str IS NOT NULL THEN

3174: --check if acquisition criteria exists for any other entity
3175: IF l_party_contains_str IS NOT NULL THEN
3176: acq_cnt := 1;
3177: END IF;
3178: IF l_party_site_contains_str IS NOT NULL THEN
3179: acq_cnt := acq_cnt+1;
3180: END IF;
3181: IF l_contact_contains_str IS NOT NULL THEN
3182: acq_cnt := acq_cnt+1;

Line 3181: IF l_contact_contains_str IS NOT NULL THEN

3177: END IF;
3178: IF l_party_site_contains_str IS NOT NULL THEN
3179: acq_cnt := acq_cnt+1;
3180: END IF;
3181: IF l_contact_contains_str IS NOT NULL THEN
3182: acq_cnt := acq_cnt+1;
3183: END IF;
3184: IF l_contact_pt_contains_str IS NOT NULL THEN
3185: acq_cnt := acq_cnt+1;

Line 3184: IF l_contact_pt_contains_str IS NOT NULL THEN

3180: END IF;
3181: IF l_contact_contains_str IS NOT NULL THEN
3182: acq_cnt := acq_cnt+1;
3183: END IF;
3184: IF l_contact_pt_contains_str IS NOT NULL THEN
3185: acq_cnt := acq_cnt+1;
3186: END IF;
3187: IF acq_cnt>1 THEN
3188: other_acq_criteria_exists := TRUE;

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

3214: FND_MSG_PUB.ADD;
3215: RAISE FND_API.G_EXC_ERROR;
3216: END IF;
3217: END IF;
3218: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3219: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3220: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3221: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3222: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN

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

3215: RAISE FND_API.G_EXC_ERROR;
3216: END IF;
3217: END IF;
3218: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3219: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3220: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3221: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3222: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3223: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

3216: END IF;
3217: END IF;
3218: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3219: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3220: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3221: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3222: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3223: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3224: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

3217: END IF;
3218: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3219: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3220: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3221: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3222: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3223: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3224: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3225: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

3218: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3219: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3220: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3221: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3222: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3223: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3224: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3225: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3226: END IF;

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

3219: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3220: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3221: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3222: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3223: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3224: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3225: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3226: END IF;
3227: END;

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

3220: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3221: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3222: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3223: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3224: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3225: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3226: END IF;
3227: END;
3228: /************ End of find_parties private procedures **********/

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

3221: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3222: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3223: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3224: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3225: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3226: END IF;
3227: END;
3228: /************ End of find_parties private procedures **********/
3229:

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

3293: 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);
3294: END IF;
3295: -- ************************************************************
3296: -- STEP 2. Setup of intermedia query strings for Acquisition query
3297: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3298: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3299: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3300: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3301: 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 3298: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);

3294: END IF;
3295: -- ************************************************************
3296: -- STEP 2. Setup of intermedia query strings for Acquisition query
3297: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3298: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3299: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3300: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3301: 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);
3302: END IF;

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

3295: -- ************************************************************
3296: -- STEP 2. Setup of intermedia query strings for Acquisition query
3297: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3298: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3299: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3300: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3301: 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);
3302: END IF;
3303: l_denorm_max_score:=0;

Line 3331: 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((78/460)*l_entered_max_score));

3327: END IF;
3328: ELSE
3329: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
3330: END IF;
3331: 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((78/460)*l_entered_max_score));
3332: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3333:
3334: -- Setup Search Context ID
3335: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 3341: IF l_party_contains_str IS NULL THEN

3337:
3338: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3339: 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);
3340: END IF;
3341: IF l_party_contains_str IS NULL THEN
3342: defpt := 1;
3343: END IF;
3344: IF l_party_site_contains_str IS NULL THEN
3345: defps := 1;

Line 3344: IF l_party_site_contains_str IS NULL THEN

3340: END IF;
3341: IF l_party_contains_str IS NULL THEN
3342: defpt := 1;
3343: END IF;
3344: IF l_party_site_contains_str IS NULL THEN
3345: defps := 1;
3346: END IF;
3347: IF l_contact_contains_str IS NULL THEN
3348: defct := 1;

Line 3347: IF l_contact_contains_str IS NULL THEN

3343: END IF;
3344: IF l_party_site_contains_str IS NULL THEN
3345: defps := 1;
3346: END IF;
3347: IF l_contact_contains_str IS NULL THEN
3348: defct := 1;
3349: END IF;
3350: IF l_contact_pt_contains_str IS NULL THEN
3351: defcpt := 1;

Line 3350: IF l_contact_pt_contains_str IS NULL THEN

3346: END IF;
3347: IF l_contact_contains_str IS NULL THEN
3348: defct := 1;
3349: END IF;
3350: IF l_contact_pt_contains_str IS NULL THEN
3351: defcpt := 1;
3352: END IF;
3353:
3354: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3355: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3356: 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);
3357: END IF;
3358: 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);
3359: 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);
3360: 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);
3361: 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);
3362: 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);
3363: 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 3360: 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);

3356: 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);
3357: END IF;
3358: 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);
3359: 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);
3360: 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);
3361: 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);
3362: 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);
3363: 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);
3364: END IF;

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

3357: END IF;
3358: 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);
3359: 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);
3360: 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);
3361: 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);
3362: 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);
3363: 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);
3364: END IF;
3365: FOR I in 1..5 LOOP

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

3358: 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);
3359: 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);
3360: 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);
3361: 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);
3362: 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);
3363: 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);
3364: END IF;
3365: FOR I in 1..5 LOOP
3366: do_eval(I);

Line 3444: l_party_contains_str VARCHAR2(32000);

3440: x_num_matches OUT NUMBER
3441: ) IS
3442:
3443: -- Strings to hold the generated Intermedia query strings
3444: l_party_contains_str VARCHAR2(32000);
3445: l_party_site_contains_str VARCHAR2(32000);
3446: l_contact_contains_str VARCHAR2(32000);
3447: l_contact_pt_contains_str VARCHAR2(32000);
3448: l_denorm_str VARCHAR2(32000);

Line 3445: l_party_site_contains_str VARCHAR2(32000);

3441: ) IS
3442:
3443: -- Strings to hold the generated Intermedia query strings
3444: l_party_contains_str VARCHAR2(32000);
3445: l_party_site_contains_str VARCHAR2(32000);
3446: l_contact_contains_str VARCHAR2(32000);
3447: l_contact_pt_contains_str VARCHAR2(32000);
3448: l_denorm_str VARCHAR2(32000);
3449: l_ps_denorm_str VARCHAR2(32000);

Line 3446: l_contact_contains_str VARCHAR2(32000);

3442:
3443: -- Strings to hold the generated Intermedia query strings
3444: l_party_contains_str VARCHAR2(32000);
3445: l_party_site_contains_str VARCHAR2(32000);
3446: l_contact_contains_str VARCHAR2(32000);
3447: l_contact_pt_contains_str VARCHAR2(32000);
3448: l_denorm_str VARCHAR2(32000);
3449: l_ps_denorm_str VARCHAR2(32000);
3450: l_ct_denorm_str VARCHAR2(32000);

Line 3447: l_contact_pt_contains_str VARCHAR2(32000);

3443: -- Strings to hold the generated Intermedia query strings
3444: l_party_contains_str VARCHAR2(32000);
3445: l_party_site_contains_str VARCHAR2(32000);
3446: l_contact_contains_str VARCHAR2(32000);
3447: l_contact_pt_contains_str VARCHAR2(32000);
3448: l_denorm_str VARCHAR2(32000);
3449: l_ps_denorm_str VARCHAR2(32000);
3450: l_ct_denorm_str VARCHAR2(32000);
3451: l_cpt_denorm_str VARCHAR2(32000);

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

3572: call_type(2) := 'OR';
3573: END;
3574:
3575: /** Private procedure to acquire and score at party level ***/
3576: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3577: l_party_id_idx NUMBER:=1;
3578: l_ctx_id NUMBER;
3579: BEGIN
3580: SAVEPOINT eval_start;

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

3593: l_search_ctx_id,H_PARTY_ID(I));
3594: l_party_id_idx := H_PARTY_ID.COUNT+1;
3595: ELSE
3596: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3597: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3598: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3599: 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);
3600: END IF;
3601: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN

Line 3605: IF (l_party_site_contains_str IS NULL AND

3601: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3602: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3603: 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);
3604: END IF;
3605: IF (l_party_site_contains_str IS NULL AND
3606: l_contact_contains_str IS NULL AND
3607: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3608: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3609: RAISE L_RETURN_IMM_EXC;

Line 3606: l_contact_contains_str IS NULL AND

3602: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3603: 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);
3604: END IF;
3605: IF (l_party_site_contains_str IS NULL AND
3606: l_contact_contains_str IS NULL AND
3607: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3608: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3609: RAISE L_RETURN_IMM_EXC;
3610: ELSE

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

3603: 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);
3604: END IF;
3605: IF (l_party_site_contains_str IS NULL AND
3606: l_contact_contains_str IS NULL AND
3607: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3608: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3609: RAISE L_RETURN_IMM_EXC;
3610: ELSE
3611: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);

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

3607: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3608: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3609: RAISE L_RETURN_IMM_EXC;
3610: ELSE
3611: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3612: END IF;
3613: ELSE
3614: IF p_index>1 THEN
3615: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3627: l_ctx_id := NULL;
3628: l_party_id_idx := H_PARTY_ID.COUNT+1;
3629: END IF;
3630: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3631: 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);
3632: END IF;
3633: LOOP
3634: FETCH l_party_cur INTO
3635: l_party_id , l_TX39, l_TX40, l_TX41;

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

3649: l_party_id_idx:= l_party_id_idx+1;
3650: END IF;
3651: IF (l_party_id_idx-1)>l_max_thresh THEN
3652: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3653: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3654: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3655: AND l_contact_pt_contains_str IS NULL THEN
3656: H_PARTY_ID.DELETE(l_party_id_idx-1);
3657: H_PARTY_ID_LIST.DELETE(l_index);

Line 3655: AND l_contact_pt_contains_str IS NULL THEN

3651: IF (l_party_id_idx-1)>l_max_thresh THEN
3652: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3653: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3654: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3655: AND l_contact_pt_contains_str IS NULL THEN
3656: H_PARTY_ID.DELETE(l_party_id_idx-1);
3657: H_PARTY_ID_LIST.DELETE(l_index);
3658: H_SCORES.DELETE(l_index);
3659: EXIT;

Line 3684: p_contains_str VARCHAR2,

3680: CLOSE l_party_cur;
3681: ROLLBACK to eval_start;
3682: END;
3683: PROCEDURE open_person_contact_cursor(
3684: p_contains_str VARCHAR2,
3685: p_search_ctx_id NUMBER,
3686: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3687: BEGIN
3688: OPEN x_cursor FOR

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

3687: BEGIN
3688: OPEN x_cursor FOR
3689: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID, TX6, TX5
3690: FROM HZ_STAGED_CONTACTS stage
3691: WHERE contains( concat_col, p_contains_str)>0
3692: AND ORG_CONTACT_ID in (
3693: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3694: org_contact_id
3695: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc

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

3711: end if;
3712: END;
3713:
3714: /** Private procedure to acquire and score at party site level ***/
3715: 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
3716: l_party_id_idx NUMBER:=1;
3717: l_ctx_id NUMBER;
3718: h_ps_id HZ_PARTY_SEARCH.IDList;
3719: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

3739: ELSE
3740: l_party_id_idx := H_PARTY_ID.COUNT+1;
3741: l_ctx_id := NULL;
3742: END IF;
3743: 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);
3744: LOOP
3745: FETCH l_party_site_cur INTO
3746: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX14, l_TX15;
3747: EXIT WHEN l_party_site_cur%NOTFOUND;

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

3810: END IF;
3811: END;
3812:
3813: /** Private procedure to acquire and score at party site level ***/
3814: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3815: l_party_id_idx NUMBER:=1;
3816: l_ctx_id NUMBER;
3817: h_ct_id HZ_PARTY_SEARCH.IDList;
3818: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

3826: FORALL I in 1..H_PARTY_ID.COUNT
3827: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
3828: l_search_ctx_id,H_PARTY_ID(I));
3829: l_party_id_idx := H_PARTY_ID.COUNT+1;
3830: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
3831: LOOP
3832: FETCH l_contact_cur INTO
3833: l_org_contact_id, l_ct_party_id , l_TX6, l_TX5;
3834: EXIT WHEN l_contact_cur%NOTFOUND;

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

3868: END IF;
3869: END;
3870:
3871: /** Private procedure to acquire and score at contact point level ***/
3872: 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
3873: l_party_id_idx NUMBER:=1;
3874: l_ctx_id NUMBER;
3875: h_cpt_id HZ_PARTY_SEARCH.IDList;
3876: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

3913: END IF;
3914: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3915: 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);
3916: END IF;
3917: 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);
3918: LOOP
3919: FETCH l_contact_pt_cur INTO
3920: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX5, l_TX6, l_TX8;
3921: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 4120: IF l_party_contains_str IS NOT NULL THEN

4116: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
4117: l_threshold := round(( l_entered_max_score / 460) * 78);
4118: other_acq_criteria_exists := TRUE ;
4119: --check if acquisition criteria exists for any other entity
4120: IF l_party_contains_str IS NOT NULL THEN
4121: acq_cnt := 1;
4122: END IF;
4123: IF l_party_site_contains_str IS NOT NULL THEN
4124: acq_cnt := acq_cnt+1;

Line 4123: IF l_party_site_contains_str IS NOT NULL THEN

4119: --check if acquisition criteria exists for any other entity
4120: IF l_party_contains_str IS NOT NULL THEN
4121: acq_cnt := 1;
4122: END IF;
4123: IF l_party_site_contains_str IS NOT NULL THEN
4124: acq_cnt := acq_cnt+1;
4125: END IF;
4126: IF l_contact_contains_str IS NOT NULL THEN
4127: acq_cnt := acq_cnt+1;

Line 4126: IF l_contact_contains_str IS NOT NULL THEN

4122: END IF;
4123: IF l_party_site_contains_str IS NOT NULL THEN
4124: acq_cnt := acq_cnt+1;
4125: END IF;
4126: IF l_contact_contains_str IS NOT NULL THEN
4127: acq_cnt := acq_cnt+1;
4128: END IF;
4129: IF l_contact_pt_contains_str IS NOT NULL THEN
4130: acq_cnt := acq_cnt+1;

Line 4129: IF l_contact_pt_contains_str IS NOT NULL THEN

4125: END IF;
4126: IF l_contact_contains_str IS NOT NULL THEN
4127: acq_cnt := acq_cnt+1;
4128: END IF;
4129: IF l_contact_pt_contains_str IS NOT NULL THEN
4130: acq_cnt := acq_cnt+1;
4131: END IF;
4132: IF acq_cnt>1 THEN
4133: other_acq_criteria_exists := TRUE;

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

4159: FND_MSG_PUB.ADD;
4160: RAISE FND_API.G_EXC_ERROR;
4161: END IF;
4162: END IF;
4163: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4164: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4165: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4166: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4167: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

4160: RAISE FND_API.G_EXC_ERROR;
4161: END IF;
4162: END IF;
4163: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4164: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4165: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4166: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4167: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4168: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

4161: END IF;
4162: END IF;
4163: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4164: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4165: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4166: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4167: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4168: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4169: END IF;

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

4162: END IF;
4163: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4164: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4165: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4166: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4167: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4168: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4169: END IF;
4170: END;

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

4163: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4164: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4165: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4166: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4167: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4168: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4169: END IF;
4170: END;
4171: /************ End of find_persons private procedures **********/

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

4164: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4165: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4166: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4167: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4168: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4169: END IF;
4170: END;
4171: /************ End of find_persons private procedures **********/
4172:

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

4199: g_score_until_thresh := false;
4200: END IF;
4201: -- ************************************************************
4202: -- STEP 2. Setup of intermedia query strings for Acquisition query
4203: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4204: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4205: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4206: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4207: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);

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

4200: END IF;
4201: -- ************************************************************
4202: -- STEP 2. Setup of intermedia query strings for Acquisition query
4203: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4204: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4205: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4206: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4207: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4208:

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

4201: -- ************************************************************
4202: -- STEP 2. Setup of intermedia query strings for Acquisition query
4203: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4204: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4205: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4206: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4207: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4208:
4209: -- Setup Search Context ID

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

4202: -- STEP 2. Setup of intermedia query strings for Acquisition query
4203: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4204: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4205: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4206: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4207: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4208:
4209: -- Setup Search Context ID
4210: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 4213: IF l_party_contains_str IS NULL THEN

4209: -- Setup Search Context ID
4210: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4211: x_search_ctx_id := l_search_ctx_id;
4212:
4213: IF l_party_contains_str IS NULL THEN
4214: defpt := 1;
4215: END IF;
4216: IF l_party_site_contains_str IS NULL THEN
4217: defps := 1;

Line 4216: IF l_party_site_contains_str IS NULL THEN

4212:
4213: IF l_party_contains_str IS NULL THEN
4214: defpt := 1;
4215: END IF;
4216: IF l_party_site_contains_str IS NULL THEN
4217: defps := 1;
4218: END IF;
4219: IF l_contact_contains_str IS NULL THEN
4220: defct := 1;

Line 4219: IF l_contact_contains_str IS NULL THEN

4215: END IF;
4216: IF l_party_site_contains_str IS NULL THEN
4217: defps := 1;
4218: END IF;
4219: IF l_contact_contains_str IS NULL THEN
4220: defct := 1;
4221: END IF;
4222: IF l_contact_pt_contains_str IS NULL THEN
4223: defcpt := 1;

Line 4222: IF l_contact_pt_contains_str IS NULL THEN

4218: END IF;
4219: IF l_contact_contains_str IS NULL THEN
4220: defct := 1;
4221: END IF;
4222: IF l_contact_pt_contains_str IS NULL THEN
4223: defcpt := 1;
4224: END IF;
4225:
4226: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

4224: END IF;
4225:
4226: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4227: 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);
4228: 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);
4229: 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);
4230: 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);
4231: 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);
4232: 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 4229: 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);

4225:
4226: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4227: 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);
4228: 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);
4229: 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);
4230: 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);
4231: 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);
4232: 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);
4233: END IF;

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

4226: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4227: 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);
4228: 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);
4229: 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);
4230: 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);
4231: 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);
4232: 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);
4233: END IF;
4234: FOR I in 1..5 LOOP

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

4227: 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);
4228: 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);
4229: 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);
4230: 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);
4231: 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);
4232: 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);
4233: END IF;
4234: FOR I in 1..5 LOOP
4235: do_eval(I);

Line 4237: IF l_contact_contains_str IS NOT NULL THEN

4233: END IF;
4234: FOR I in 1..5 LOOP
4235: do_eval(I);
4236: END LOOP;
4237: IF l_contact_contains_str IS NOT NULL THEN
4238: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4239: END IF;
4240: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4241: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 17 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

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

4234: FOR I in 1..5 LOOP
4235: do_eval(I);
4236: END LOOP;
4237: IF l_contact_contains_str IS NOT NULL THEN
4238: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4239: END IF;
4240: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4241: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 17 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4242: END IF;

Line 5085: l_party_contains_str VARCHAR2(32000);

5081: x_num_matches OUT NUMBER
5082: ) IS
5083:
5084: -- Strings to hold the generated Intermedia query strings
5085: l_party_contains_str VARCHAR2(32000);
5086: l_match_idx NUMBER;
5087: l_party_site_contains_str VARCHAR2(32000);
5088: l_contact_contains_str VARCHAR2(32000);
5089: l_contact_pt_contains_str VARCHAR2(32000);

Line 5087: l_party_site_contains_str VARCHAR2(32000);

5083:
5084: -- Strings to hold the generated Intermedia query strings
5085: l_party_contains_str VARCHAR2(32000);
5086: l_match_idx NUMBER;
5087: l_party_site_contains_str VARCHAR2(32000);
5088: l_contact_contains_str VARCHAR2(32000);
5089: l_contact_pt_contains_str VARCHAR2(32000);
5090: l_tmp VARCHAR2(32000);
5091:

Line 5088: l_contact_contains_str VARCHAR2(32000);

5084: -- Strings to hold the generated Intermedia query strings
5085: l_party_contains_str VARCHAR2(32000);
5086: l_match_idx NUMBER;
5087: l_party_site_contains_str VARCHAR2(32000);
5088: l_contact_contains_str VARCHAR2(32000);
5089: l_contact_pt_contains_str VARCHAR2(32000);
5090: l_tmp VARCHAR2(32000);
5091:
5092: -- Other local variables

Line 5089: l_contact_pt_contains_str VARCHAR2(32000);

5085: l_party_contains_str VARCHAR2(32000);
5086: l_match_idx NUMBER;
5087: l_party_site_contains_str VARCHAR2(32000);
5088: l_contact_contains_str VARCHAR2(32000);
5089: l_contact_pt_contains_str VARCHAR2(32000);
5090: l_tmp VARCHAR2(32000);
5091:
5092: -- Other local variables
5093: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5169: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5170:
5171: -- ************************************************************
5172: -- STEP 2. Setup of intermedia query strings for Acquisition query
5173: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5174: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5175: 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);
5176:
5177: -- Setup Search Context ID

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

5170:
5171: -- ************************************************************
5172: -- STEP 2. Setup of intermedia query strings for Acquisition query
5173: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5174: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5175: 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);
5176:
5177: -- Setup Search Context ID
5178: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

5179: x_search_ctx_id := l_search_ctx_id;
5180:
5181: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5182: 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);
5183: 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);
5184: 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);
5185: 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);
5186: END IF;
5187: IF l_party_site_contains_str IS NULL THEN

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

5180:
5181: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5182: 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);
5183: 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);
5184: 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);
5185: 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);
5186: END IF;
5187: IF l_party_site_contains_str IS NULL THEN
5188: defps := 1;

Line 5187: IF l_party_site_contains_str IS NULL THEN

5183: 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);
5184: 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);
5185: 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);
5186: END IF;
5187: IF l_party_site_contains_str IS NULL THEN
5188: defps := 1;
5189: END IF;
5190: IF l_contact_pt_contains_str IS NULL THEN
5191: defcpt := 1;

Line 5190: IF l_contact_pt_contains_str IS NULL THEN

5186: END IF;
5187: IF l_party_site_contains_str IS NULL THEN
5188: defps := 1;
5189: END IF;
5190: IF l_contact_pt_contains_str IS NULL THEN
5191: defcpt := 1;
5192: END IF;
5193:
5194: IF l_party_site_contains_str IS NOT NULL THEN

Line 5194: IF l_party_site_contains_str IS NOT NULL THEN

5190: IF l_contact_pt_contains_str IS NULL THEN
5191: defcpt := 1;
5192: END IF;
5193:
5194: IF l_party_site_contains_str IS NOT NULL THEN
5195: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5196: LOOP
5197: FETCH l_party_site_cur INTO
5198: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX14, l_TX15;

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

5191: defcpt := 1;
5192: END IF;
5193:
5194: IF l_party_site_contains_str IS NOT NULL THEN
5195: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
5196: LOOP
5197: FETCH l_party_site_cur INTO
5198: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX14, l_TX15;
5199: EXIT WHEN l_party_site_cur%NOTFOUND;

Line 5218: IF l_contact_pt_contains_str IS NOT NULL THEN

5214: END LOOP;
5215: CLOSE l_party_site_cur;
5216: END IF;
5217:
5218: IF l_contact_pt_contains_str IS NOT NULL THEN
5219: SAVEPOINT eval_start;
5220: IF l_match_str = ' AND ' OR (
5221: ((l_cpt_max_score/l_entered_max_score)<(78/460))
5222: ) THEN

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

5222: ) THEN
5223: FORALL I in 1..l_match_ps_list.COUNT
5224: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5225: values (l_search_ctx_id,l_match_ps_list(I));
5226: 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');
5227: ELSE
5228: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5229: END IF;
5230: LOOP

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

5224: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5225: values (l_search_ctx_id,l_match_ps_list(I));
5226: 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');
5227: ELSE
5228: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5229: END IF;
5230: LOOP
5231: FETCH l_contact_pt_cur INTO
5232: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX5, l_TX6, l_TX8;

Line 5328: l_party_contains_str VARCHAR2(32000);

5324: x_num_matches OUT NUMBER
5325: ) IS
5326:
5327: -- Strings to hold the generated Intermedia query strings
5328: l_party_contains_str VARCHAR2(32000);
5329: l_party_site_contains_str VARCHAR2(32000);
5330: l_contact_contains_str VARCHAR2(32000);
5331: l_contact_pt_contains_str VARCHAR2(32000);
5332: l_tmp VARCHAR2(32000);

Line 5329: l_party_site_contains_str VARCHAR2(32000);

5325: ) IS
5326:
5327: -- Strings to hold the generated Intermedia query strings
5328: l_party_contains_str VARCHAR2(32000);
5329: l_party_site_contains_str VARCHAR2(32000);
5330: l_contact_contains_str VARCHAR2(32000);
5331: l_contact_pt_contains_str VARCHAR2(32000);
5332: l_tmp VARCHAR2(32000);
5333:

Line 5330: l_contact_contains_str VARCHAR2(32000);

5326:
5327: -- Strings to hold the generated Intermedia query strings
5328: l_party_contains_str VARCHAR2(32000);
5329: l_party_site_contains_str VARCHAR2(32000);
5330: l_contact_contains_str VARCHAR2(32000);
5331: l_contact_pt_contains_str VARCHAR2(32000);
5332: l_tmp VARCHAR2(32000);
5333:
5334: -- Other local variables

Line 5331: l_contact_pt_contains_str VARCHAR2(32000);

5327: -- Strings to hold the generated Intermedia query strings
5328: l_party_contains_str VARCHAR2(32000);
5329: l_party_site_contains_str VARCHAR2(32000);
5330: l_contact_contains_str VARCHAR2(32000);
5331: l_contact_pt_contains_str VARCHAR2(32000);
5332: l_tmp VARCHAR2(32000);
5333:
5334: -- Other local variables
5335: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5402: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5403:
5404: -- ************************************************************
5405: -- STEP 2. Setup of intermedia query strings for Acquisition query
5406: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5407: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5408: 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);
5409:
5410: -- Setup Search Context ID

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

5403:
5404: -- ************************************************************
5405: -- STEP 2. Setup of intermedia query strings for Acquisition query
5406: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5407: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5408: 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);
5409:
5410: -- Setup Search Context ID
5411: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

5412: x_search_ctx_id := l_search_ctx_id;
5413:
5414: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5415: 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);
5416: 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);
5417: 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);
5418: 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);
5419: END IF;
5420: IF l_contact_contains_str IS NULL THEN

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

5413:
5414: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5415: 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);
5416: 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);
5417: 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);
5418: 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);
5419: END IF;
5420: IF l_contact_contains_str IS NULL THEN
5421: defct := 1;

Line 5420: IF l_contact_contains_str IS NULL THEN

5416: 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);
5417: 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);
5418: 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);
5419: END IF;
5420: IF l_contact_contains_str IS NULL THEN
5421: defct := 1;
5422: END IF;
5423: IF l_contact_pt_contains_str IS NULL THEN
5424: defcpt := 1;

Line 5423: IF l_contact_pt_contains_str IS NULL THEN

5419: END IF;
5420: IF l_contact_contains_str IS NULL THEN
5421: defct := 1;
5422: END IF;
5423: IF l_contact_pt_contains_str IS NULL THEN
5424: defcpt := 1;
5425: END IF;
5426:
5427: IF l_contact_contains_str IS NOT NULL THEN

Line 5427: IF l_contact_contains_str IS NOT NULL THEN

5423: IF l_contact_pt_contains_str IS NULL THEN
5424: defcpt := 1;
5425: END IF;
5426:
5427: IF l_contact_contains_str IS NOT NULL THEN
5428: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5429: LOOP
5430: FETCH l_contact_cur INTO
5431: l_org_contact_id, l_ct_party_id , l_TX6, l_TX5;

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

5424: defcpt := 1;
5425: END IF;
5426:
5427: IF l_contact_contains_str IS NOT NULL THEN
5428: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5429: LOOP
5430: FETCH l_contact_cur INTO
5431: l_org_contact_id, l_ct_party_id , l_TX6, l_TX5;
5432: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5449: IF l_contact_pt_contains_str IS NOT NULL THEN

5445: END LOOP;
5446: CLOSE l_contact_cur;
5447: END IF;
5448:
5449: IF l_contact_pt_contains_str IS NOT NULL THEN
5450: SAVEPOINT eval_start;
5451: IF l_match_str = ' AND ' OR (
5452: ((l_cpt_max_score/l_entered_max_score)<(78/460))
5453: ) THEN

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

5453: ) THEN
5454: FORALL I in 1..l_match_contact_list.COUNT
5455: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5456: values (l_search_ctx_id,l_match_contact_list(I));
5457: 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');
5458: ELSE
5459: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5460: END IF;
5461: LOOP

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

5455: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5456: values (l_search_ctx_id,l_match_contact_list(I));
5457: 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');
5458: ELSE
5459: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5460: END IF;
5461: LOOP
5462: FETCH l_contact_pt_cur INTO
5463: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX5, l_TX6, l_TX8;

Line 5557: l_contact_pt_contains_str VARCHAR2(32000);

5553: ) IS
5554:
5555:
5556: -- Strings to hold the generated Intermedia query strings
5557: l_contact_pt_contains_str VARCHAR2(32000);
5558: l_tmp VARCHAR2(32000);
5559:
5560: -- Other local variables
5561: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5615: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5616:
5617: -- ************************************************************
5618: -- STEP 2. Setup of intermedia query strings for Acquisition query
5619: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5620: 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);
5621:
5622: -- Setup Search Context ID
5623: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

5623: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5624: x_search_ctx_id := l_search_ctx_id;
5625: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5626: 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);
5627: 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);
5628: 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);
5629: END IF;
5630:
5631: IF l_contact_pt_contains_str IS NOT NULL THEN

Line 5631: IF l_contact_pt_contains_str IS NOT NULL THEN

5627: 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);
5628: 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);
5629: END IF;
5630:
5631: IF l_contact_pt_contains_str IS NOT NULL THEN
5632: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5633: cnt := 1;
5634: LOOP
5635: FETCH l_contact_pt_cur INTO

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

5628: 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);
5629: END IF;
5630:
5631: IF l_contact_pt_contains_str IS NOT NULL THEN
5632: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5633: cnt := 1;
5634: LOOP
5635: FETCH l_contact_pt_cur INTO
5636: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX5, l_TX6, l_TX8;

Line 5703: l_party_contains_str VARCHAR2(32000);

5699: x_search_ctx_id IN OUT NUMBER
5700: ) IS
5701:
5702: -- Strings to hold the generated Intermedia query strings
5703: l_party_contains_str VARCHAR2(32000);
5704: l_party_site_contains_str VARCHAR2(32000);
5705: l_contact_contains_str VARCHAR2(32000);
5706: l_contact_pt_contains_str VARCHAR2(32000);
5707: l_tmp VARCHAR2(32000);

Line 5704: l_party_site_contains_str VARCHAR2(32000);

5700: ) IS
5701:
5702: -- Strings to hold the generated Intermedia query strings
5703: l_party_contains_str VARCHAR2(32000);
5704: l_party_site_contains_str VARCHAR2(32000);
5705: l_contact_contains_str VARCHAR2(32000);
5706: l_contact_pt_contains_str VARCHAR2(32000);
5707: l_tmp VARCHAR2(32000);
5708:

Line 5705: l_contact_contains_str VARCHAR2(32000);

5701:
5702: -- Strings to hold the generated Intermedia query strings
5703: l_party_contains_str VARCHAR2(32000);
5704: l_party_site_contains_str VARCHAR2(32000);
5705: l_contact_contains_str VARCHAR2(32000);
5706: l_contact_pt_contains_str VARCHAR2(32000);
5707: l_tmp VARCHAR2(32000);
5708:
5709: -- Other local variables

Line 5706: l_contact_pt_contains_str VARCHAR2(32000);

5702: -- Strings to hold the generated Intermedia query strings
5703: l_party_contains_str VARCHAR2(32000);
5704: l_party_site_contains_str VARCHAR2(32000);
5705: l_contact_contains_str VARCHAR2(32000);
5706: l_contact_pt_contains_str VARCHAR2(32000);
5707: l_tmp VARCHAR2(32000);
5708:
5709: -- Other local variables
5710: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5787: g_score_until_thresh := false;
5788: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5789: -- ************************************************************
5790: -- STEP 2. Setup of intermedia query strings for Acquisition query
5791: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5792: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5793: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5794: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5795:

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

5788: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5789: -- ************************************************************
5790: -- STEP 2. Setup of intermedia query strings for Acquisition query
5791: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5792: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5793: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5794: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5795:
5796: -- Setup Search Context ID

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

5789: -- ************************************************************
5790: -- STEP 2. Setup of intermedia query strings for Acquisition query
5791: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5792: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5793: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5794: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5795:
5796: -- Setup Search Context ID
5797: IF x_search_ctx_id IS NULL THEN

Line 5813: IF l_party_site_contains_str IS NOT NULL THEN

5809: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX39,l_TX40,l_TX41,1);
5810: END LOOP;
5811: CLOSE l_party_cur;
5812:
5813: IF l_party_site_contains_str IS NOT NULL THEN
5814: l_max_score := 0;
5815: l_max_id := 0;
5816: l_max_idx := 0;
5817: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN

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

5814: l_max_score := 0;
5815: l_max_id := 0;
5816: l_max_idx := 0;
5817: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5818: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5819: ELSE
5820: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5821: END IF;
5822: LOOP

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

5816: l_max_idx := 0;
5817: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5818: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5819: ELSE
5820: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5821: END IF;
5822: LOOP
5823: FETCH l_party_site_cur INTO
5824: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX14, l_TX15;

Line 5846: IF l_contact_contains_str IS NOT NULL THEN

5842: 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_TX14,l_max_TX15,l_max_idx);
5843: END IF;
5844: END IF;
5845:
5846: IF l_contact_contains_str IS NOT NULL THEN
5847: l_max_score := 0;
5848: l_max_id := 0;
5849: l_max_idx := 0;
5850: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

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

5846: IF l_contact_contains_str IS NOT NULL THEN
5847: l_max_score := 0;
5848: l_max_id := 0;
5849: l_max_idx := 0;
5850: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5851: LOOP
5852: FETCH l_contact_cur INTO
5853: l_org_contact_id, l_ct_party_id , l_TX6, l_TX5;
5854: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5870: IF l_contact_pt_contains_str IS NOT NULL THEN

5866: 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_TX6,l_max_TX5,l_max_idx);
5867: END IF;
5868: END IF;
5869:
5870: IF l_contact_pt_contains_str IS NOT NULL THEN
5871: l_max_score := 0;
5872: l_max_id := 0;
5873: l_max_idx := 0;
5874: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN

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

5871: l_max_score := 0;
5872: l_max_id := 0;
5873: l_max_idx := 0;
5874: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5875: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5876: ELSE
5877: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5878: END IF;
5879: LOOP

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

5873: l_max_idx := 0;
5874: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5875: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5876: ELSE
5877: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5878: END IF;
5879: LOOP
5880: FETCH l_contact_pt_cur INTO
5881: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX5, l_TX6, l_TX8;