eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.HZ_MATCH_RULE_7 dependencies on CONTAINS

Line 70: p_contains_str VARCHAR2) RETURN NUMBER IS

66: END IF;
67: END ;
68: FUNCTION check_estimate_hits (
69: p_entity VARCHAR2,
70: p_contains_str VARCHAR2) RETURN NUMBER IS
71:
72: ustatus VARCHAR2(255);
73: dstatus VARCHAR2(255);
74: l_bool BOOLEAN;

Line 83: g_index_owner||'.'||'HZ_STAGE_PARTIES_T1',p_contains_str, false);

79: END IF;
80: IF p_entity='PARTY' THEN
81:
82: l_hits := CTX_QUERY.count_hits(
83: g_index_owner||'.'||'HZ_STAGE_PARTIES_T1',p_contains_str, false);
84: ELSIF p_entity='PARTY_SITES' THEN
85: l_hits := CTX_QUERY.count_hits(
86: g_index_owner||'.'||'HZ_STAGE_PARTY_SITES_T1',p_contains_str, false);
87: ELSIF p_entity='CONTACTS' THEN

Line 86: g_index_owner||'.'||'HZ_STAGE_PARTY_SITES_T1',p_contains_str, false);

82: l_hits := CTX_QUERY.count_hits(
83: g_index_owner||'.'||'HZ_STAGE_PARTIES_T1',p_contains_str, false);
84: ELSIF p_entity='PARTY_SITES' THEN
85: l_hits := CTX_QUERY.count_hits(
86: g_index_owner||'.'||'HZ_STAGE_PARTY_SITES_T1',p_contains_str, false);
87: ELSIF p_entity='CONTACTS' THEN
88: l_hits := CTX_QUERY.count_hits(
89: g_index_owner||'.'||'HZ_STAGE_CONTACT_T1',p_contains_str, false);
90: ELSIF p_entity='CONTACT_POINTS' THEN

Line 89: g_index_owner||'.'||'HZ_STAGE_CONTACT_T1',p_contains_str, false);

85: l_hits := CTX_QUERY.count_hits(
86: g_index_owner||'.'||'HZ_STAGE_PARTY_SITES_T1',p_contains_str, false);
87: ELSIF p_entity='CONTACTS' THEN
88: l_hits := CTX_QUERY.count_hits(
89: g_index_owner||'.'||'HZ_STAGE_CONTACT_T1',p_contains_str, false);
90: ELSIF p_entity='CONTACT_POINTS' THEN
91: l_hits := CTX_QUERY.count_hits(
92: g_index_owner||'.'||'HZ_STAGE_CPT_T1',p_contains_str, false);
93: END IF;

Line 92: g_index_owner||'.'||'HZ_STAGE_CPT_T1',p_contains_str, false);

88: l_hits := CTX_QUERY.count_hits(
89: g_index_owner||'.'||'HZ_STAGE_CONTACT_T1',p_contains_str, false);
90: ELSIF p_entity='CONTACT_POINTS' THEN
91: l_hits := CTX_QUERY.count_hits(
92: g_index_owner||'.'||'HZ_STAGE_CPT_T1',p_contains_str, false);
93: END IF;
94: RETURN floor(l_hits/2) ;
95:
96:

Line 972: l_party_contains_str VARCHAR2(32000);

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 1041: IF lengthb(l_party_contains_str) > 4000 THEN

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

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

1043: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1044: FND_MSG_PUB.ADD;
1045: RAISE FND_API.G_EXC_ERROR;
1046: END IF;
1047: IF (p_match_str = ' AND ' OR p_party_max_score 1048: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1049: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is an AND between these strings ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1050: END IF;
1051: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

Line 1049: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is an AND between these strings ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

1045: RAISE FND_API.G_EXC_ERROR;
1046: END IF;
1047: IF (p_match_str = ' AND ' OR p_party_max_score 1048: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1049: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is an AND between these strings ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1050: END IF;
1051: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1052: hz_utility_v2pub.debug(p_message=>'l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1053: END IF;

Line 1052: hz_utility_v2pub.debug(p_message=>'l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

1048: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1049: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is an AND between these strings ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1050: END IF;
1051: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1052: hz_utility_v2pub.debug(p_message=>'l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1053: END IF;
1054: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1055: hz_utility_v2pub.debug(p_message=>'p_denorm_str = '||p_denorm_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1056: END IF;

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

1053: END IF;
1054: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1055: hz_utility_v2pub.debug(p_message=>'p_denorm_str = '||p_denorm_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1056: END IF;
1057: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1058: ELSE
1059: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1060: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1061: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line 1060: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

1056: END IF;
1057: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1058: ELSE
1059: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1060: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1061: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1062: END IF;
1063: RETURN l_party_contains_str;
1064: END IF;

Line 1061: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

1057: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1058: ELSE
1059: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1060: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1061: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1062: END IF;
1063: RETURN l_party_contains_str;
1064: END IF;
1065: END;

Line 1063: RETURN l_party_contains_str;

1059: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1060: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1061: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1062: END IF;
1063: RETURN l_party_contains_str;
1064: END IF;
1065: END;
1066: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1067: l_contains_str VARCHAR2(32000);

Line 1067: l_contains_str VARCHAR2(32000);

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

Line 1068: l_contains_str_temp VARCHAR2(32000);

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

Line 1069: l_den_contains_str VARCHAR2(32000);

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

Line 1070: l_den_contains_str_temp VARCHAR2(32000);

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

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

1076: hz_utility_v2pub.debug(p_message=>'INIT_PARTY_SITES_QUERY ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
1077: END IF;
1078: x_denorm_str := NULL;
1079:
1080: -- Dynamic setup of party site contains str
1081: --
1082: -- For each primary transformation add to intermedia query if it
1083: -- is not null
1084: FOR I IN 1..g_party_site_stage_list.COUNT LOOP

Line 1085: l_contains_str_temp := null;

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

Line 1086: l_den_contains_str_temp := null;

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

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

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

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

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

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

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

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

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

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

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

Line 1110: IF lengthb(l_contains_str) > 4000 THEN

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

Line 1116: x_denorm_str := l_den_contains_str;

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

Line 1117: RETURN l_contains_str;

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

Line 1121: l_contains_str VARCHAR2(32000);

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

Line 1122: l_contains_str_temp VARCHAR2(32000);

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

Line 1123: l_den_contains_str VARCHAR2(32000);

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

Line 1124: l_den_contains_str_temp VARCHAR2(32000);

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

Line 1137: l_contains_str VARCHAR2(32000);

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

Line 1138: l_contains_str_temp VARCHAR2(32000);

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

Line 1139: l_den_contains_str VARCHAR2(32000);

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

Line 1140: l_den_contains_str_temp VARCHAR2(32000);

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

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

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

Line 1155: l_contains_str_temp := null;

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

Line 1156: l_den_contains_str_temp := null;

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

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

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

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

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

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

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

Line 1171: IF lengthb(l_contains_str) > 4000 THEN

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

Line 1177: x_denorm_str := l_den_contains_str;

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

Line 1178: RETURN l_contains_str;

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

Line 1186: p_party_contains_str VARCHAR2,

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

Line 1203: IF p_restrict_sql IS NULL AND NOT g_other_party_level_attribs AND NOT (p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1) THEN

1199: END IF;
1200: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1201: hz_utility_v2pub.debug(p_message=>'Search Merged Flag - '||l_search_merged,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1202: END IF;
1203: IF p_restrict_sql IS NULL AND NOT g_other_party_level_attribs AND NOT (p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1) THEN
1204: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1205: hz_utility_v2pub.debug(p_message=>'Restrict SQL is NULL and other conditions met to OPEN x_cursor',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1206: END IF;
1207: OPEN x_cursor FOR

Line 1235: IF p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN

1231: l_sqlstr := l_sqlstr || ' (TX44 like :TX44||'' %'' ';
1232: l_sqlstr := l_sqlstr || ' )) ';
1233: l_sqlstr := l_sqlstr || ' ) ';
1234: END IF;
1235: IF p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
1236: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1237: hz_utility_v2pub.debug(p_message=>'p_party_contains_str string is NOT NULL and other conditions met',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1238: END IF;
1239: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';

Line 1237: hz_utility_v2pub.debug(p_message=>'p_party_contains_str string is NOT NULL and other conditions met',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

1233: l_sqlstr := l_sqlstr || ' ) ';
1234: END IF;
1235: IF p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
1236: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1237: hz_utility_v2pub.debug(p_message=>'p_party_contains_str string is NOT NULL and other conditions met',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1238: END IF;
1239: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1240: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX11||'' %'' ';
1241: l_sqlstr := l_sqlstr || ' )) ';

Line 1258: IF g_other_party_level_attribs AND p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN

1254: END IF;
1255: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1256: hz_utility_v2pub.debug(p_message=>'l_sqlstr is - '||l_sqlstr,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1257: END IF;
1258: IF g_other_party_level_attribs AND p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
1259: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1260: hz_utility_v2pub.debug(p_message=>'IF g_other_party_level_attribs AND p_party_contains_str IS NOT NULL AND ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1261: END IF;
1262: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id

Line 1260: hz_utility_v2pub.debug(p_message=>'IF g_other_party_level_attribs AND p_party_contains_str IS NOT NULL AND ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

1256: hz_utility_v2pub.debug(p_message=>'l_sqlstr is - '||l_sqlstr,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1257: END IF;
1258: IF g_other_party_level_attribs AND p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
1259: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1260: hz_utility_v2pub.debug(p_message=>'IF g_other_party_level_attribs AND p_party_contains_str IS NOT NULL AND ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1261: END IF;
1262: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id
1263: ,g_party_stage_rec.TX44
1264: ,g_party_stage_rec.TX44

Line 1276: ELSIF p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN

1272: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id
1273: ,g_party_stage_rec.TX44
1274: ,g_party_stage_rec.TX44
1275: ;
1276: ELSIF p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
1277: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1278: hz_utility_v2pub.debug(p_message=>'ELSIF p_party_contains_str IS NOT NULL AND ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1279: END IF;
1280: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id

Line 1278: hz_utility_v2pub.debug(p_message=>'ELSIF p_party_contains_str IS NOT NULL AND ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

1274: ,g_party_stage_rec.TX44
1275: ;
1276: ELSIF p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
1277: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1278: hz_utility_v2pub.debug(p_message=>'ELSIF p_party_contains_str IS NOT NULL AND ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1279: END IF;
1280: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id
1281: ,g_party_site_stage_list(1).TX11
1282: ,g_party_site_stage_list(1).TX11

Line 1295: p_contains_str VARCHAR2,

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

Line 1314: IF p_contains_str IS NULL THEN

1310: l_search_merged := 'N';
1311: ELSE
1312: l_search_merged := p_search_merged;
1313: END IF;
1314: IF p_contains_str IS NULL THEN
1315: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1316: hz_utility_v2pub.debug(p_message=>'part contains string is null',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1317: END IF;
1318: OPEN x_cursor FOR

Line 1316: hz_utility_v2pub.debug(p_message=>'part contains string is null',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

1312: l_search_merged := p_search_merged;
1313: END IF;
1314: IF p_contains_str IS NULL THEN
1315: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1316: hz_utility_v2pub.debug(p_message=>'part contains string is null',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1317: END IF;
1318: OPEN x_cursor FOR
1319: SELECT PARTY_ID , TX2, TX8, TX4, TX44
1320: FROM HZ_STAGED_PARTIES stage

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

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

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

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

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

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

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

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

Line 1383: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

Line 1414: p_contains_str VARCHAR2,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 1593: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

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

Line 1594: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

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

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

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

Line 1608: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 1625: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 1659: p_contains_str VARCHAR2,

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

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

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

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

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

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

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

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

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

Line 1773: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 1806: p_contains_str VARCHAR2,

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

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

1832: THEN
1833: OPEN x_cursor FOR
1834: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1835: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1836: WHERE contains( concat_col, p_contains_str)>0
1837: AND d.search_context_id = p_search_ctx_id
1838: AND( (l_search_merged ='Y' )
1839: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1840: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1844: THEN
1845: OPEN x_cursor FOR
1846: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N3) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1847: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1848: WHERE contains( concat_col, p_contains_str)>0
1849: AND d.search_context_id = p_search_ctx_id
1850: AND( (l_search_merged ='Y' )
1851: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1852: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

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

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

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

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

1878: AND stage.party_id = p_party_id
1879: UNION
1880: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ stage.CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , TX1, TX6, TX5
1881: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1882: WHERE contains( concat_col, p_contains_str)>0
1883: AND r.object_id = p_party_id
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') )

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

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

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

1920: IF p_person_api = 'Y' THEN
1921: OPEN x_cursor FOR
1922: 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, TX6, TX5
1923: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1924: WHERE contains( concat_col, p_contains_str)>0
1925: AND d.search_context_id = p_search_ctx_id
1926: AND d.party_id = stage.party_id
1927: AND( (l_search_merged ='Y' )
1928: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1930: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1931: UNION
1932: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, r.subject_id, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , TX1, TX6, TX5
1933: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1934: WHERE contains( concat_col, p_contains_str)>0
1935: AND d.search_context_id = p_search_ctx_id
1936: AND d.party_id = r.subject_id
1937: AND r.relationship_id = oc.party_relationship_id
1938: AND oc.org_contact_id = stage.org_contact_id

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

1943: ELSE
1944: OPEN x_cursor FOR
1945: 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, TX6, TX5
1946: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1947: WHERE contains( concat_col, p_contains_str)>0
1948: AND d.search_context_id = p_search_ctx_id
1949: AND d.party_id = stage.party_id
1950: AND( (l_search_merged ='Y' )
1951: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1988: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1989: END IF;
1990: l_sqlstr := ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
1991: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1992: ' WHERE contains( concat_col, :cont)>0 '||
1993: ' AND (stage.org_contact_id is null '||
1994: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1995: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1996: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

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

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

Line 2012: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

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

Line 2013: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

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

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

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

Line 2027: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 2044: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 2172: l_party_contains_str VARCHAR2(32000);

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

Line 2173: l_party_site_contains_str VARCHAR2(32000);

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

Line 2174: l_contact_contains_str VARCHAR2(32000);

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

Line 2175: l_contact_pt_contains_str VARCHAR2(32000);

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

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

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

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

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

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

Line 2309: l_contact_contains_str IS NULL AND

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

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

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

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

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

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

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

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

Line 2361: AND l_contact_pt_contains_str IS NULL THEN

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

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

2386: ROLLBACK to eval_start;
2387: END;
2388:
2389: /** Private procedure to acquire and score at party site level ***/
2390: 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
2391: l_party_id_idx NUMBER:=1;
2392: l_ctx_id NUMBER;
2393: h_ps_id HZ_PARTY_SEARCH.IDList;
2394: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

2398: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2399: 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);
2400: 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);
2401: END IF;
2402: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2403: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2404: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2405: l_party_id := H_SCORES.FIRST;
2406: WHILE l_party_id IS NOT NULL LOOP

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

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

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

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

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

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

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

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

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

2508: END IF;
2509: END;
2510:
2511: /** Private procedure to acquire and score at contact point level ***/
2512: 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
2513: l_party_id_idx NUMBER:=1;
2514: l_ctx_id NUMBER;
2515: h_ct_id HZ_PARTY_SEARCH.IDList;
2516: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

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

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

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

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

2617: END IF;
2618: END;
2619:
2620: /** Private procedure to acquire and score at contact point level ***/
2621: 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
2622: l_party_id_idx NUMBER:=1;
2623: l_ctx_id NUMBER;
2624: h_cpt_id HZ_PARTY_SEARCH.IDList;
2625: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

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

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

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

Line 2779: IF l_party_contains_str IS NOT NULL THEN

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

Line 2782: IF l_party_site_contains_str IS NOT NULL THEN

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

Line 2785: IF l_contact_contains_str IS NOT NULL THEN

2781: END IF;
2782: IF l_party_site_contains_str IS NOT NULL THEN
2783: acq_cnt := acq_cnt+1;
2784: END IF;
2785: IF l_contact_contains_str IS NOT NULL THEN
2786: acq_cnt := acq_cnt+1;
2787: END IF;
2788: IF l_contact_pt_contains_str IS NOT NULL THEN
2789: acq_cnt := acq_cnt+1;

Line 2788: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 2946: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, l_denorm_str, l_party_max_score, l_denorm_max_score, l_non_denorm_max_score, round((100/360)*l_entered_max_score));

2942: END IF;
2943: ELSE
2944: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
2945: END IF;
2946: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, l_denorm_str, l_party_max_score, l_denorm_max_score, l_non_denorm_max_score, round((100/360)*l_entered_max_score));
2947: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
2948:
2949: -- Setup Search Context ID
2950: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 2956: IF l_party_contains_str IS NULL THEN

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

Line 2959: IF l_party_site_contains_str IS NULL THEN

2955: END IF;
2956: IF l_party_contains_str IS NULL THEN
2957: defpt := 1;
2958: END IF;
2959: IF l_party_site_contains_str IS NULL THEN
2960: defps := 1;
2961: END IF;
2962: IF l_contact_contains_str IS NULL THEN
2963: defct := 1;

Line 2962: IF l_contact_contains_str IS NULL THEN

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

Line 2965: IF l_contact_pt_contains_str IS NULL THEN

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

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

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

2971: 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);
2972: END IF;
2973: 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);
2974: 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);
2975: 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);
2976: 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);
2977: 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);
2978: 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);
2979: END IF;

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

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

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

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

Line 3091: l_party_contains_str VARCHAR2(32000);

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

Line 3092: l_party_site_contains_str VARCHAR2(32000);

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

Line 3093: l_contact_contains_str VARCHAR2(32000);

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

Line 3094: l_contact_pt_contains_str VARCHAR2(32000);

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

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

3214: call_type(2) := 'OR';
3215: END;
3216:
3217: /** Private procedure to acquire and score at party level ***/
3218: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3219: l_party_id_idx NUMBER:=1;
3220: l_ctx_id NUMBER;
3221: BEGIN
3222: SAVEPOINT eval_start;

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

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

Line 3247: IF (l_party_site_contains_str IS NULL AND

3243: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3244: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3245: hz_utility_v2pub.debug(p_message=>'In eval party level 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);
3246: END IF;
3247: IF (l_party_site_contains_str IS NULL AND
3248: l_contact_contains_str IS NULL AND
3249: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3250: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3251: RAISE L_RETURN_IMM_EXC;

Line 3248: l_contact_contains_str IS NULL AND

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

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

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

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

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

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

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

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

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

Line 3297: AND l_contact_pt_contains_str IS NULL THEN

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

Line 3326: p_contains_str VARCHAR2,

3322: CLOSE l_party_cur;
3323: ROLLBACK to eval_start;
3324: END;
3325: PROCEDURE open_person_contact_cursor(
3326: p_contains_str VARCHAR2,
3327: p_search_ctx_id NUMBER,
3328: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3329: BEGIN
3330: OPEN x_cursor FOR

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

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

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

3353: end if;
3354: END;
3355:
3356: /** Private procedure to acquire and score at party site level ***/
3357: 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
3358: l_party_id_idx NUMBER:=1;
3359: l_ctx_id NUMBER;
3360: h_ps_id HZ_PARTY_SEARCH.IDList;
3361: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

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

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

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

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

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

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

3510: END IF;
3511: END;
3512:
3513: /** Private procedure to acquire and score at contact point level ***/
3514: 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
3515: l_party_id_idx NUMBER:=1;
3516: l_ctx_id NUMBER;
3517: h_cpt_id HZ_PARTY_SEARCH.IDList;
3518: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

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

Line 3667: IF l_party_contains_str IS NOT NULL THEN

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

Line 3670: IF l_party_site_contains_str IS NOT NULL THEN

3666: --check if acquisition criteria exists for any other entity
3667: IF l_party_contains_str IS NOT NULL THEN
3668: acq_cnt := 1;
3669: END IF;
3670: IF l_party_site_contains_str IS NOT NULL THEN
3671: acq_cnt := acq_cnt+1;
3672: END IF;
3673: IF l_contact_contains_str IS NOT NULL THEN
3674: acq_cnt := acq_cnt+1;

Line 3673: IF l_contact_contains_str IS NOT NULL THEN

3669: END IF;
3670: IF l_party_site_contains_str IS NOT NULL THEN
3671: acq_cnt := acq_cnt+1;
3672: END IF;
3673: IF l_contact_contains_str IS NOT NULL THEN
3674: acq_cnt := acq_cnt+1;
3675: END IF;
3676: IF l_contact_pt_contains_str IS NOT NULL THEN
3677: acq_cnt := acq_cnt+1;

Line 3676: IF l_contact_pt_contains_str IS NOT NULL THEN

3672: END IF;
3673: IF l_contact_contains_str IS NOT NULL THEN
3674: acq_cnt := acq_cnt+1;
3675: END IF;
3676: IF l_contact_pt_contains_str IS NOT NULL THEN
3677: acq_cnt := acq_cnt+1;
3678: END IF;
3679: IF acq_cnt>1 THEN
3680: other_acq_criteria_exists := TRUE;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 3760: IF l_party_contains_str IS NULL THEN

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

Line 3763: IF l_party_site_contains_str IS NULL THEN

3759:
3760: IF l_party_contains_str IS NULL THEN
3761: defpt := 1;
3762: END IF;
3763: IF l_party_site_contains_str IS NULL THEN
3764: defps := 1;
3765: END IF;
3766: IF l_contact_contains_str IS NULL THEN
3767: defct := 1;

Line 3766: IF l_contact_contains_str IS NULL THEN

3762: END IF;
3763: IF l_party_site_contains_str IS NULL THEN
3764: defps := 1;
3765: END IF;
3766: IF l_contact_contains_str IS NULL THEN
3767: defct := 1;
3768: END IF;
3769: IF l_contact_pt_contains_str IS NULL THEN
3770: defcpt := 1;

Line 3769: IF l_contact_pt_contains_str IS NULL THEN

3765: END IF;
3766: IF l_contact_contains_str IS NULL THEN
3767: defct := 1;
3768: END IF;
3769: IF l_contact_pt_contains_str IS NULL THEN
3770: defcpt := 1;
3771: END IF;
3772:
3773: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3771: END IF;
3772:
3773: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3774: 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);
3775: 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);
3776: 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);
3777: 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);
3778: 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);
3779: 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 3776: 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);

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

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

3773: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3774: 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);
3775: 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);
3776: 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);
3777: 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);
3778: 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);
3779: 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);
3780: END IF;
3781: FOR I in 1..5 LOOP

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

3774: 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);
3775: 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);
3776: 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);
3777: 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);
3778: 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);
3779: 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);
3780: END IF;
3781: FOR I in 1..5 LOOP
3782: do_eval(I);

Line 3784: IF l_contact_contains_str IS NOT NULL THEN

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

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

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

Line 4613: l_party_contains_str VARCHAR2(32000);

4609: x_num_matches OUT NUMBER
4610: ) IS
4611:
4612: -- Strings to hold the generated Intermedia query strings
4613: l_party_contains_str VARCHAR2(32000);
4614: l_match_idx NUMBER;
4615: l_party_site_contains_str VARCHAR2(32000);
4616: l_contact_contains_str VARCHAR2(32000);
4617: l_contact_pt_contains_str VARCHAR2(32000);

Line 4615: l_party_site_contains_str VARCHAR2(32000);

4611:
4612: -- Strings to hold the generated Intermedia query strings
4613: l_party_contains_str VARCHAR2(32000);
4614: l_match_idx NUMBER;
4615: l_party_site_contains_str VARCHAR2(32000);
4616: l_contact_contains_str VARCHAR2(32000);
4617: l_contact_pt_contains_str VARCHAR2(32000);
4618: l_tmp VARCHAR2(32000);
4619:

Line 4616: l_contact_contains_str VARCHAR2(32000);

4612: -- Strings to hold the generated Intermedia query strings
4613: l_party_contains_str VARCHAR2(32000);
4614: l_match_idx NUMBER;
4615: l_party_site_contains_str VARCHAR2(32000);
4616: l_contact_contains_str VARCHAR2(32000);
4617: l_contact_pt_contains_str VARCHAR2(32000);
4618: l_tmp VARCHAR2(32000);
4619:
4620: -- Other local variables

Line 4617: l_contact_pt_contains_str VARCHAR2(32000);

4613: l_party_contains_str VARCHAR2(32000);
4614: l_match_idx NUMBER;
4615: l_party_site_contains_str VARCHAR2(32000);
4616: l_contact_contains_str VARCHAR2(32000);
4617: l_contact_pt_contains_str VARCHAR2(32000);
4618: l_tmp VARCHAR2(32000);
4619:
4620: -- Other local variables
4621: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4692: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4693:
4694: -- ************************************************************
4695: -- STEP 2. Setup of intermedia query strings for Acquisition query
4696: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4697: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4698: 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);
4699:
4700: -- Setup Search Context ID

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

4693:
4694: -- ************************************************************
4695: -- STEP 2. Setup of intermedia query strings for Acquisition query
4696: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4697: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4698: 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);
4699:
4700: -- Setup Search Context ID
4701: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4702: x_search_ctx_id := l_search_ctx_id;
4703:
4704: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4705: 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);
4706: 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);
4707: 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);
4708: 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);
4709: END IF;
4710: IF l_party_site_contains_str IS NULL THEN

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

4703:
4704: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4705: 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);
4706: 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);
4707: 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);
4708: 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);
4709: END IF;
4710: IF l_party_site_contains_str IS NULL THEN
4711: defps := 1;

Line 4710: IF l_party_site_contains_str IS NULL THEN

4706: 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);
4707: 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);
4708: 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);
4709: END IF;
4710: IF l_party_site_contains_str IS NULL THEN
4711: defps := 1;
4712: END IF;
4713: IF l_contact_pt_contains_str IS NULL THEN
4714: defcpt := 1;

Line 4713: IF l_contact_pt_contains_str IS NULL THEN

4709: END IF;
4710: IF l_party_site_contains_str IS NULL THEN
4711: defps := 1;
4712: END IF;
4713: IF l_contact_pt_contains_str IS NULL THEN
4714: defcpt := 1;
4715: END IF;
4716:
4717: IF l_party_site_contains_str IS NOT NULL THEN

Line 4717: IF l_party_site_contains_str IS NOT NULL THEN

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

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

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

Line 4741: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

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

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

Line 4851: l_party_contains_str VARCHAR2(32000);

4847: x_num_matches OUT NUMBER
4848: ) IS
4849:
4850: -- Strings to hold the generated Intermedia query strings
4851: l_party_contains_str VARCHAR2(32000);
4852: l_party_site_contains_str VARCHAR2(32000);
4853: l_contact_contains_str VARCHAR2(32000);
4854: l_contact_pt_contains_str VARCHAR2(32000);
4855: l_tmp VARCHAR2(32000);

Line 4852: l_party_site_contains_str VARCHAR2(32000);

4848: ) IS
4849:
4850: -- Strings to hold the generated Intermedia query strings
4851: l_party_contains_str VARCHAR2(32000);
4852: l_party_site_contains_str VARCHAR2(32000);
4853: l_contact_contains_str VARCHAR2(32000);
4854: l_contact_pt_contains_str VARCHAR2(32000);
4855: l_tmp VARCHAR2(32000);
4856:

Line 4853: l_contact_contains_str VARCHAR2(32000);

4849:
4850: -- Strings to hold the generated Intermedia query strings
4851: l_party_contains_str VARCHAR2(32000);
4852: l_party_site_contains_str VARCHAR2(32000);
4853: l_contact_contains_str VARCHAR2(32000);
4854: l_contact_pt_contains_str VARCHAR2(32000);
4855: l_tmp VARCHAR2(32000);
4856:
4857: -- Other local variables

Line 4854: l_contact_pt_contains_str VARCHAR2(32000);

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

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

4924: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4925:
4926: -- ************************************************************
4927: -- STEP 2. Setup of intermedia query strings for Acquisition query
4928: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4929: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4930: 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);
4931:
4932: -- Setup Search Context ID

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

4925:
4926: -- ************************************************************
4927: -- STEP 2. Setup of intermedia query strings for Acquisition query
4928: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4929: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4930: 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);
4931:
4932: -- Setup Search Context ID
4933: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4934: x_search_ctx_id := l_search_ctx_id;
4935:
4936: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4937: 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);
4938: 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);
4939: 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);
4940: 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);
4941: END IF;
4942: IF l_contact_contains_str IS NULL THEN

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

4935:
4936: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4937: 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);
4938: 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);
4939: 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);
4940: 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);
4941: END IF;
4942: IF l_contact_contains_str IS NULL THEN
4943: defct := 1;

Line 4942: IF l_contact_contains_str IS NULL THEN

4938: 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);
4939: 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);
4940: 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);
4941: END IF;
4942: IF l_contact_contains_str IS NULL THEN
4943: defct := 1;
4944: END IF;
4945: IF l_contact_pt_contains_str IS NULL THEN
4946: defcpt := 1;

Line 4945: IF l_contact_pt_contains_str IS NULL THEN

4941: END IF;
4942: IF l_contact_contains_str IS NULL THEN
4943: defct := 1;
4944: END IF;
4945: IF l_contact_pt_contains_str IS NULL THEN
4946: defcpt := 1;
4947: END IF;
4948:
4949: IF l_contact_contains_str IS NOT NULL THEN

Line 4949: IF l_contact_contains_str IS NOT NULL THEN

4945: IF l_contact_pt_contains_str IS NULL THEN
4946: defcpt := 1;
4947: END IF;
4948:
4949: IF l_contact_contains_str IS NOT NULL THEN
4950: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
4951: LOOP
4952: FETCH l_contact_cur INTO
4953: l_org_contact_id, l_ct_party_id ;

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

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

Line 4971: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

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

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

Line 5079: l_contact_pt_contains_str VARCHAR2(32000);

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

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

5136: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5137:
5138: -- ************************************************************
5139: -- STEP 2. Setup of intermedia query strings for Acquisition query
5140: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5141: 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);
5142:
5143: -- Setup Search Context ID
5144: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

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

Line 5152: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

Line 5224: l_party_contains_str VARCHAR2(32000);

5220: x_search_ctx_id IN OUT NUMBER
5221: ) IS
5222:
5223: -- Strings to hold the generated Intermedia query strings
5224: l_party_contains_str VARCHAR2(32000);
5225: l_party_site_contains_str VARCHAR2(32000);
5226: l_contact_contains_str VARCHAR2(32000);
5227: l_contact_pt_contains_str VARCHAR2(32000);
5228: l_tmp VARCHAR2(32000);

Line 5225: l_party_site_contains_str VARCHAR2(32000);

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

Line 5226: l_contact_contains_str VARCHAR2(32000);

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

Line 5227: l_contact_pt_contains_str VARCHAR2(32000);

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

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

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

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

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

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

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

Line 5324: IF l_party_site_contains_str IS NOT NULL THEN

5320: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX2,l_TX8,l_TX4,l_TX44,1);
5321: END LOOP;
5322: CLOSE l_party_cur;
5323:
5324: IF l_party_site_contains_str IS NOT NULL THEN
5325: l_max_score := 0;
5326: l_max_id := 0;
5327: l_max_idx := 0;
5328: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN

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

5325: l_max_score := 0;
5326: l_max_id := 0;
5327: l_max_idx := 0;
5328: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5329: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5330: ELSE
5331: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5332: END IF;
5333: LOOP

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

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

Line 5353: IF l_contact_contains_str IS NOT NULL THEN

5349: INSERT_PARTY_SITES_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_party_site_list(l_max_idx), g_party_site_stage_list(l_max_idx) ,l_max_TX3,l_max_TX4,l_max_TX11,l_max_idx);
5350: END IF;
5351: END IF;
5352:
5353: IF l_contact_contains_str IS NOT NULL THEN
5354: l_max_score := 0;
5355: l_max_id := 0;
5356: l_max_idx := 0;
5357: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

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

5353: IF l_contact_contains_str IS NOT NULL THEN
5354: l_max_score := 0;
5355: l_max_id := 0;
5356: l_max_idx := 0;
5357: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5358: LOOP
5359: FETCH l_contact_cur INTO
5360: l_org_contact_id, l_ct_party_id ;
5361: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5375: IF l_contact_pt_contains_str IS NOT NULL THEN

5371: INSERT_CONTACTS_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_contact_list(l_max_idx), g_contact_stage_list(l_max_idx) ,l_max_idx);
5372: END IF;
5373: END IF;
5374:
5375: IF l_contact_pt_contains_str IS NOT NULL THEN
5376: l_max_score := 0;
5377: l_max_id := 0;
5378: l_max_idx := 0;
5379: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN

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

5376: l_max_score := 0;
5377: l_max_id := 0;
5378: l_max_idx := 0;
5379: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5380: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5381: ELSE
5382: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5383: END IF;
5384: LOOP

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

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