[Home] [Help]
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;
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
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
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;
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:
951: p_party_max_score NUMBER,
952: p_denorm_max_score NUMBER,
953: p_non_denorm_max_score NUMBER,
954: p_threshold NUMBER) RETURN VARCHAR2 IS
955: l_party_contains_str VARCHAR2(32000);
956: l_party_filter VARCHAR2(1) := null;
957: l_prim_temp VARCHAR2(4000);
958: l_denorm_str VARCHAR2(4000);
959: BEGIN
995:
996: IF l_denorm_str IS NOT NULL THEN
997: IF (p_non_denorm_max_score+40)>=p_threshold THEN
998: l_denorm_str := NULL;
999: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1000: ELSIF (p_non_denorm_max_score+p_denorm_max_score+40)>=p_threshold THEN
1001: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1002: l_denorm_str := NULL;
1003: END IF;
997: IF (p_non_denorm_max_score+40)>=p_threshold THEN
998: l_denorm_str := NULL;
999: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1000: ELSIF (p_non_denorm_max_score+p_denorm_max_score+40)>=p_threshold THEN
1001: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1002: l_denorm_str := NULL;
1003: END IF;
1004: ELSE
1005: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1001: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1002: l_denorm_str := NULL;
1003: END IF;
1004: ELSE
1005: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1006: END IF;
1007:
1008: -- Setup query string for DUNS_NUMBER_C
1009: l_prim_temp := null;
1018:
1019: IF l_denorm_str IS NOT NULL THEN
1020: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1021: l_denorm_str := NULL;
1022: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1023: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1024: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1025: l_denorm_str := NULL;
1026: END IF;
1020: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1021: l_denorm_str := NULL;
1022: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1023: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1024: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1025: l_denorm_str := NULL;
1026: END IF;
1027: ELSE
1028: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1024: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1025: l_denorm_str := NULL;
1026: END IF;
1027: ELSE
1028: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1029: END IF;
1030:
1031: -- Setup query string for JGZZ_FISCAL_CODE
1032: l_prim_temp := null;
1035:
1036: IF l_denorm_str IS NOT NULL THEN
1037: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1038: l_denorm_str := NULL;
1039: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1040: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1041: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1042: l_denorm_str := NULL;
1043: END IF;
1037: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1038: l_denorm_str := NULL;
1039: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1040: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1041: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1042: l_denorm_str := NULL;
1043: END IF;
1044: ELSE
1045: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1041: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1042: l_denorm_str := NULL;
1043: END IF;
1044: ELSE
1045: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1046: END IF;
1047:
1048: -- Setup query string for PARTY_NUMBER
1049: l_prim_temp := null;
1052:
1053: IF l_denorm_str IS NOT NULL THEN
1054: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1055: l_denorm_str := NULL;
1056: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1057: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1058: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1059: l_denorm_str := NULL;
1060: END IF;
1054: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1055: l_denorm_str := NULL;
1056: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1057: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1058: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1059: l_denorm_str := NULL;
1060: END IF;
1061: ELSE
1062: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1058: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1059: l_denorm_str := NULL;
1060: END IF;
1061: ELSE
1062: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1063: END IF;
1064:
1065: -- Setup query string for ALL_ACCOUNT_NUMBERS
1066: l_prim_temp := null;
1069:
1070: IF l_denorm_str IS NOT NULL THEN
1071: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1072: l_denorm_str := NULL;
1073: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1074: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1075: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1076: l_denorm_str := NULL;
1077: END IF;
1071: IF (p_non_denorm_max_score+60)>=p_threshold THEN
1072: l_denorm_str := NULL;
1073: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1074: ELSIF (p_non_denorm_max_score+p_denorm_max_score+60)>=p_threshold THEN
1075: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1076: l_denorm_str := NULL;
1077: END IF;
1078: ELSE
1079: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1075: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1076: l_denorm_str := NULL;
1077: END IF;
1078: ELSE
1079: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1080: END IF;
1081: IF lengthb(l_party_contains_str) > 4000 THEN
1082: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1083: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1077: END IF;
1078: ELSE
1079: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1080: END IF;
1081: IF lengthb(l_party_contains_str) > 4000 THEN
1082: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1083: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1084: FND_MSG_PUB.ADD;
1085: RAISE FND_API.G_EXC_ERROR;
1083: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1084: FND_MSG_PUB.ADD;
1085: RAISE FND_API.G_EXC_ERROR;
1086: END IF;
1087: IF (p_match_str = ' AND ' OR p_party_max_score
1089: 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);
1090: END IF;
1091: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1085: RAISE FND_API.G_EXC_ERROR;
1086: END IF;
1087: IF (p_match_str = ' AND ' OR p_party_max_score
1089: 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);
1090: END IF;
1091: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1092: 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);
1093: END IF;
1088: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1089: 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);
1090: END IF;
1091: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1092: 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);
1093: END IF;
1094: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1095: 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);
1096: END IF;
1093: END IF;
1094: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1095: 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);
1096: END IF;
1097: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1098: ELSE
1099: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1100: 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);
1101: 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);
1096: END IF;
1097: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1098: ELSE
1099: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1100: 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);
1101: 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);
1102: END IF;
1103: RETURN l_party_contains_str;
1104: END IF;
1097: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1098: ELSE
1099: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1100: 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);
1101: 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);
1102: END IF;
1103: RETURN l_party_contains_str;
1104: END IF;
1105: END;
1099: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1100: 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);
1101: 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);
1102: END IF;
1103: RETURN l_party_contains_str;
1104: END IF;
1105: END;
1106: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1107: l_contains_str VARCHAR2(32000);
1103: RETURN l_party_contains_str;
1104: END IF;
1105: END;
1106: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1107: l_contains_str VARCHAR2(32000);
1108: l_contains_str_temp VARCHAR2(32000);
1109: l_den_contains_str VARCHAR2(32000);
1110: l_den_contains_str_temp VARCHAR2(32000);
1111: l_filter_str VARCHAR2(4000) := null;
1104: END IF;
1105: END;
1106: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1107: l_contains_str VARCHAR2(32000);
1108: l_contains_str_temp VARCHAR2(32000);
1109: l_den_contains_str VARCHAR2(32000);
1110: l_den_contains_str_temp VARCHAR2(32000);
1111: l_filter_str VARCHAR2(4000) := null;
1112: l_prim_temp VARCHAR2(4000) := null;
1105: END;
1106: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1107: l_contains_str VARCHAR2(32000);
1108: l_contains_str_temp VARCHAR2(32000);
1109: l_den_contains_str VARCHAR2(32000);
1110: l_den_contains_str_temp VARCHAR2(32000);
1111: l_filter_str VARCHAR2(4000) := null;
1112: l_prim_temp VARCHAR2(4000) := null;
1113: l_prim_temp_den VARCHAR2(4000) := null;
1106: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1107: l_contains_str VARCHAR2(32000);
1108: l_contains_str_temp VARCHAR2(32000);
1109: l_den_contains_str VARCHAR2(32000);
1110: l_den_contains_str_temp VARCHAR2(32000);
1111: l_filter_str VARCHAR2(4000) := null;
1112: l_prim_temp VARCHAR2(4000) := null;
1113: l_prim_temp_den VARCHAR2(4000) := null;
1114: BEGIN
1119: RETURN NULL;
1120: END;
1121:
1122: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1123: l_contains_str VARCHAR2(32000);
1124: l_contains_str_temp VARCHAR2(32000);
1125: l_den_contains_str VARCHAR2(32000);
1126: l_den_contains_str_temp VARCHAR2(32000);
1127: l_filter_str VARCHAR2(4000) := null;
1120: END;
1121:
1122: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1123: l_contains_str VARCHAR2(32000);
1124: l_contains_str_temp VARCHAR2(32000);
1125: l_den_contains_str VARCHAR2(32000);
1126: l_den_contains_str_temp VARCHAR2(32000);
1127: l_filter_str VARCHAR2(4000) := null;
1128: l_prim_temp VARCHAR2(4000) := null;
1121:
1122: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1123: l_contains_str VARCHAR2(32000);
1124: l_contains_str_temp VARCHAR2(32000);
1125: l_den_contains_str VARCHAR2(32000);
1126: l_den_contains_str_temp VARCHAR2(32000);
1127: l_filter_str VARCHAR2(4000) := null;
1128: l_prim_temp VARCHAR2(4000) := null;
1129: l_prim_temp_den VARCHAR2(4000) := null;
1122: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1123: l_contains_str VARCHAR2(32000);
1124: l_contains_str_temp VARCHAR2(32000);
1125: l_den_contains_str VARCHAR2(32000);
1126: l_den_contains_str_temp VARCHAR2(32000);
1127: l_filter_str VARCHAR2(4000) := null;
1128: l_prim_temp VARCHAR2(4000) := null;
1129: l_prim_temp_den VARCHAR2(4000) := null;
1130: BEGIN
1135: RETURN NULL;
1136: END;
1137:
1138: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1139: l_contains_str VARCHAR2(32000);
1140: l_contains_str_temp VARCHAR2(32000);
1141: l_den_contains_str VARCHAR2(32000);
1142: l_den_contains_str_temp VARCHAR2(32000);
1143: l_filter_str VARCHAR2(4000) := null;
1136: END;
1137:
1138: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1139: l_contains_str VARCHAR2(32000);
1140: l_contains_str_temp VARCHAR2(32000);
1141: l_den_contains_str VARCHAR2(32000);
1142: l_den_contains_str_temp VARCHAR2(32000);
1143: l_filter_str VARCHAR2(4000) := null;
1144: l_prim_temp VARCHAR2(4000) := null;
1137:
1138: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1139: l_contains_str VARCHAR2(32000);
1140: l_contains_str_temp VARCHAR2(32000);
1141: l_den_contains_str VARCHAR2(32000);
1142: l_den_contains_str_temp VARCHAR2(32000);
1143: l_filter_str VARCHAR2(4000) := null;
1144: l_prim_temp VARCHAR2(4000) := null;
1145: l_prim_temp_den VARCHAR2(4000) := null;
1138: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1139: l_contains_str VARCHAR2(32000);
1140: l_contains_str_temp VARCHAR2(32000);
1141: l_den_contains_str VARCHAR2(32000);
1142: l_den_contains_str_temp VARCHAR2(32000);
1143: l_filter_str VARCHAR2(4000) := null;
1144: l_prim_temp VARCHAR2(4000) := null;
1145: l_prim_temp_den VARCHAR2(4000) := null;
1146: TYPE CONTACT_PT_REC_TYPE IS RECORD (
1154: 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);
1155: END IF;
1156: x_denorm_str := NULL;
1157:
1158: -- Dynamic setup of party site contains str
1159: --
1160: -- For each primary transformation add to intermedia query if it
1161: -- is not null
1162: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1159: --
1160: -- For each primary transformation add to intermedia query if it
1161: -- is not null
1162: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1163: l_contains_str_temp := null;
1164: l_den_contains_str_temp := null;
1165: l_filter_str := null;
1166: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1167: if(l_cnt_pt_type.count>0) then
1160: -- For each primary transformation add to intermedia query if it
1161: -- is not null
1162: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1163: l_contains_str_temp := null;
1164: l_den_contains_str_temp := null;
1165: l_filter_str := null;
1166: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1167: if(l_cnt_pt_type.count>0) then
1168: x_modify := 'Y';
1184: l_prim_temp := null;
1185: l_prim_temp_den := null;
1186: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (URL) + WRDOMAIN
1187: g_contact_pt_stage_list(I).TX8,'A49',l_prim_temp);
1188: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1189:
1190: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1191: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1192: IF N>1 THEN
1186: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (URL) + WRDOMAIN
1187: g_contact_pt_stage_list(I).TX8,'A49',l_prim_temp);
1188: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1189:
1190: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1191: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1192: IF N>1 THEN
1193: distinct_search_cpt_types := N-1;
1194: ELSE
1187: g_contact_pt_stage_list(I).TX8,'A49',l_prim_temp);
1188: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1189:
1190: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1191: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1192: IF N>1 THEN
1193: distinct_search_cpt_types := N-1;
1194: ELSE
1195: distinct_search_cpt_types := N;
1198: hz_utility_v2pub.debug(p_message=>'distinct_search_cpt_types is - '||distinct_search_cpt_types,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1199: END IF;
1200: END LOOP;
1201: -- Add the search criteria to query string
1202: IF lengthb(l_contains_str) > 4000 THEN
1203: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1204: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1205: FND_MSG_PUB.ADD;
1206: RAISE FND_API.G_EXC_ERROR;
1204: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1205: FND_MSG_PUB.ADD;
1206: RAISE FND_API.G_EXC_ERROR;
1207: END IF;
1208: x_denorm_str := l_den_contains_str;
1209: RETURN l_contains_str;
1210: END;
1211:
1212: PROCEDURE open_party_cursor_direct (
1205: FND_MSG_PUB.ADD;
1206: RAISE FND_API.G_EXC_ERROR;
1207: END IF;
1208: x_denorm_str := l_den_contains_str;
1209: RETURN l_contains_str;
1210: END;
1211:
1212: PROCEDURE open_party_cursor_direct (
1213: p_dup_party_id NUMBER,
1213: p_dup_party_id NUMBER,
1214: p_restrict_sql VARCHAR2,
1215: p_match_str VARCHAR2,
1216: p_search_merged VARCHAR2,
1217: p_party_contains_str VARCHAR2,
1218: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1219: l_sqlstr VARCHAR2(4000);
1220: l_search_merged VARCHAR2(1);
1221: BEGIN
1230: END IF;
1231: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1232: 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);
1233: END IF;
1234: 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
1235: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1236: 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);
1237: END IF;
1238: OPEN x_cursor FOR
1274: l_sqlstr := l_sqlstr || ' (TX45 like :TX45||'' %'' ';
1275: l_sqlstr := l_sqlstr || ' )) ';
1276: l_sqlstr := l_sqlstr || ' ) ';
1277: END IF;
1278: 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
1279: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1280: 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);
1281: END IF;
1282: null;
1276: l_sqlstr := l_sqlstr || ' ) ';
1277: END IF;
1278: 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
1279: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1280: 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);
1281: END IF;
1282: null;
1283: END IF;
1284: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1295: END IF;
1296: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1297: 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);
1298: END IF;
1299: 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
1300: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1301: 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);
1302: END IF;
1303: 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
1297: 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);
1298: END IF;
1299: 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
1300: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1301: 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);
1302: END IF;
1303: 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
1304: ,g_party_stage_rec.TX34
1305: ,g_party_stage_rec.TX34
1327: ,g_party_stage_rec.TX41
1328: ,g_party_stage_rec.TX45
1329: ,g_party_stage_rec.TX45
1330: ;
1331: 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
1332: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1333: 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);
1334: END IF;
1335: 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
1329: ,g_party_stage_rec.TX45
1330: ;
1331: 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
1332: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1333: 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);
1334: END IF;
1335: 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
1336: ;
1337: ELSE
1344: END;
1345: PROCEDURE open_party_cursor(
1346: p_dup_party_id NUMBER,
1347: p_restrict_sql VARCHAR2,
1348: p_contains_str VARCHAR2,
1349: p_search_ctx_id NUMBER,
1350: p_match_str VARCHAR2,
1351: p_search_merged VARCHAR2,
1352: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1363: l_search_merged := 'N';
1364: ELSE
1365: l_search_merged := p_search_merged;
1366: END IF;
1367: IF p_contains_str IS NULL THEN
1368: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1369: 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);
1370: END IF;
1371: OPEN x_cursor FOR
1365: l_search_merged := p_search_merged;
1366: END IF;
1367: IF p_contains_str IS NULL THEN
1368: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1369: 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);
1370: END IF;
1371: OPEN x_cursor FOR
1372: SELECT PARTY_ID , TX34, TX35, TX39, TX40, lpad(rtrim(TX41),9,chr(48)), TX45
1373: FROM HZ_STAGED_PARTIES stage
1382: END IF;
1383: OPEN x_cursor FOR
1384: SELECT PARTY_ID , TX34, TX35, TX39, TX40, lpad(rtrim(TX41),9,chr(48)), TX45
1385: FROM HZ_STAGED_PARTIES stage
1386: WHERE contains( concat_col, p_contains_str)>0
1387: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1388: AND( (l_search_merged ='Y' )
1389: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1390: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )
1395: END IF;
1396: OPEN x_cursor FOR
1397: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX34, TX35, TX39, TX40, lpad(rtrim(TX41),9,chr(48)), TX45
1398: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1399: WHERE contains( concat_col, p_contains_str)>0
1400: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1401: AND d.party_id = stage.party_id
1402: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1403: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1418: END IF;
1419: IF p_search_ctx_id IS NULL THEN
1420: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX34, TX35, TX39, TX40, lpad(rtrim(TX41),9,chr(48)), TX45'||
1421: ' FROM HZ_STAGED_PARTIES stage'||
1422: ' WHERE contains( concat_col, :cont)>0'||
1423: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1424: ' AND ('||p_restrict_sql||')' ||
1425: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1426: IF l_search_merged = 'Y' THEN
1430: ELSE
1431: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1432: END IF;
1433: output_long_strings('----------------------------------------------------------');
1434: output_long_strings('Party Contains String = '||p_contains_str);
1435: output_long_strings('Restrict Sql = '||p_restrict_sql);
1436: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1437: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1438: ,p_dup_party_id, p_dup_party_id;
1432: END IF;
1433: output_long_strings('----------------------------------------------------------');
1434: output_long_strings('Party Contains String = '||p_contains_str);
1435: output_long_strings('Restrict Sql = '||p_restrict_sql);
1436: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1437: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1438: ,p_dup_party_id, p_dup_party_id;
1439: END IF;
1440: END IF;
1463: PROCEDURE open_party_site_cursor(
1464: p_dup_party_id NUMBER,
1465: p_party_id NUMBER,
1466: p_restrict_sql VARCHAR2,
1467: p_contains_str VARCHAR2,
1468: p_search_ctx_id NUMBER,
1469: p_search_merged VARCHAR2,
1470: p_search_rel_sites VARCHAR2,
1471: p_person_api VARCHAR2,
1495: IF p_search_rel_sites = 'N' THEN
1496: OPEN x_cursor FOR
1497: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID
1498: FROM HZ_STAGED_PARTY_SITES stage
1499: WHERE contains( concat_col, p_contains_str)>0
1500: AND( (l_search_merged ='Y' )
1501: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1502: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1503: AND stage.party_id = p_party_id;
1504: ELSE
1505: OPEN x_cursor FOR
1506: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID
1507: FROM HZ_STAGED_PARTY_SITES stage
1508: WHERE contains( concat_col, p_contains_str)>0
1509: AND( (l_search_merged ='Y' )
1510: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1511: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1512: AND stage.party_id = p_party_id
1512: AND stage.party_id = p_party_id
1513: UNION
1514: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID
1515: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1516: WHERE contains( concat_col, p_contains_str)>0
1517: AND( (l_search_merged ='Y' )
1518: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1519: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1520: AND r.object_id = p_party_id
1534: END IF;
1535: OPEN x_cursor FOR
1536: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID
1537: FROM HZ_STAGED_PARTY_SITES stage
1538: WHERE contains( concat_col, p_contains_str)>0
1539: AND EXISTS (
1540: SELECT 1 FROM HZ_STAGED_PARTIES p
1541: WHERE p.PARTY_ID = stage.PARTY_ID
1542: AND( (l_search_merged ='Y' )
1554: IF p_person_api = 'Y' THEN
1555: OPEN x_cursor FOR
1556: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID
1557: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1558: WHERE contains( concat_col, p_contains_str)>0
1559: AND( (l_search_merged ='Y' )
1560: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1561: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1562: AND d.search_context_id = p_search_ctx_id
1564: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1565: UNION
1566: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID
1567: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1568: WHERE contains( concat_col, p_contains_str)>0
1569: AND( (l_search_merged ='Y' )
1570: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1571: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1572: AND d.search_context_id = p_search_ctx_id
1577: ELSE
1578: OPEN x_cursor FOR
1579: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID
1580: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1581: WHERE contains( concat_col, p_contains_str)>0
1582: AND( (l_search_merged ='Y' )
1583: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1584: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1585: AND d.search_context_id = p_search_ctx_id
1622: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1623: END IF;
1624: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID '||
1625: ' FROM HZ_STAGED_PARTY_SITES stage'||
1626: ' WHERE contains( concat_col, :cont)>0'||
1627: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1628: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1629: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1630: ' AND (ORG_CONTACT_ID IS NULL ' ||
1632: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1633: ' UNION ' ||
1634: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID '||
1635: ' FROM HZ_STAGED_PARTY_SITES stage'||
1636: ' WHERE contains( concat_col, :cont)>0'||
1637: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1638: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1639: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1640: ' AND ORG_CONTACT_ID IN ' ||
1642: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1643: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1644: ' and ('||p_restrict_sql1|| ') )' ||
1645: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1646: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1647: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1648: ELSE
1649: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID '||
1650: ' FROM HZ_STAGED_PARTY_SITES stage'||
1643: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1644: ' and ('||p_restrict_sql1|| ') )' ||
1645: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1646: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1647: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1648: ELSE
1649: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID '||
1650: ' FROM HZ_STAGED_PARTY_SITES stage'||
1651: ' WHERE contains( concat_col, :cont)>0'||
1647: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1648: ELSE
1649: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID '||
1650: ' FROM HZ_STAGED_PARTY_SITES stage'||
1651: ' WHERE contains( concat_col, :cont)>0'||
1652: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1653: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1654: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1655: ' AND EXISTS ('||
1657: ' WHERE p.party_id = stage.party_id ' ||
1658: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1659: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1660: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1661: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1662: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1663: ,p_dup_party_id, p_dup_party_id;
1664: END IF;
1665: ELSE
1664: END IF;
1665: ELSE
1666: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID '||
1667: ' FROM HZ_STAGED_PARTY_SITES stage'||
1668: ' WHERE contains( concat_col, :cont)>0'||
1669: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1670: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1671: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1672: ' AND EXISTS ('||
1674: ' WHERE p.party_id = stage.party_id ' ||
1675: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1676: ' AND ('||p_restrict_sql||')' ||
1677: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1678: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1679: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1680: ,p_dup_party_id, p_dup_party_id;
1681: END IF;
1682: END IF;
1680: ,p_dup_party_id, p_dup_party_id;
1681: END IF;
1682: END IF;
1683: output_long_strings('----------------------------------------------------------');
1684: output_long_strings('Party Site Contains String = '||p_contains_str);
1685: output_long_strings('Restrict Sql = '||p_restrict_sql);
1686: exception
1687: when others then
1688: if (instrb(SQLERRM,'DRG-51030')>0) then
1708: PROCEDURE open_contact_cursor(
1709: p_dup_party_id NUMBER,
1710: p_party_id NUMBER,
1711: p_restrict_sql VARCHAR2,
1712: p_contains_str VARCHAR2,
1713: p_search_ctx_id NUMBER,
1714: p_search_merged VARCHAR2,
1715: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1716: l_sqlstr VARCHAR2(4000);
1736: END IF;
1737: OPEN x_cursor FOR
1738: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
1739: FROM HZ_STAGED_CONTACTS stage
1740: WHERE contains( concat_col, p_contains_str)>0
1741: AND EXISTS (
1742: SELECT 1 FROM HZ_STAGED_PARTIES p
1743: WHERE p.PARTY_ID = stage.PARTY_ID
1744: AND( (l_search_merged ='Y' )
1759: END IF;
1760: OPEN x_cursor FOR
1761: SELECT ORG_CONTACT_ID, PARTY_ID
1762: FROM HZ_STAGED_CONTACTS stage
1763: WHERE contains( concat_col, p_contains_str)>0
1764: AND EXISTS (
1765: SELECT 1 FROM HZ_STAGED_PARTIES p
1766: WHERE p.PARTY_ID = stage.PARTY_ID
1767: AND( (l_search_merged ='Y' )
1778: END IF;
1779: OPEN x_cursor FOR
1780: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
1781: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1782: WHERE contains( concat_col, p_contains_str)>0
1783: AND d.search_context_id = p_search_ctx_id
1784: AND d.party_id = stage.party_id
1785: AND( (l_search_merged ='Y' )
1786: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1812: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
1813: END IF;
1814: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
1815: ' FROM HZ_STAGED_CONTACTS stage'||
1816: ' WHERE contains( concat_col, :cont)>0'||
1817: ' AND EXISTS ('||
1818: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1819: ' WHERE p.party_id = stage.party_id ' ||
1820: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1822: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1823: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1824: ' AND ('||p_restrict_sql||')' ||
1825: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1826: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1827: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1828: ,p_dup_party_id, p_dup_party_id;
1829: END IF;
1830: output_long_strings('----------------------------------------------------------');
1827: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1828: ,p_dup_party_id, p_dup_party_id;
1829: END IF;
1830: output_long_strings('----------------------------------------------------------');
1831: output_long_strings('Contacts Contains String = '||p_contains_str);
1832: output_long_strings('Restrict Sql = '||p_restrict_sql);
1833: exception
1834: when others then
1835: if (instrb(SQLERRM,'DRG-51030')>0) then
1855: PROCEDURE open_contact_pt_cursor(
1856: p_dup_party_id NUMBER,
1857: p_party_id NUMBER,
1858: p_restrict_sql VARCHAR2,
1859: p_contains_str VARCHAR2,
1860: p_search_ctx_id NUMBER,
1861: p_search_merged VARCHAR2,
1862: p_search_rel_cpts VARCHAR2,
1863: p_person_api VARCHAR2,
1885: THEN
1886: OPEN x_cursor FOR
1887: 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 , TX8, TX7
1888: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1889: WHERE contains( concat_col, p_contains_str)>0
1890: AND d.search_context_id = p_search_ctx_id
1891: AND( (l_search_merged ='Y' )
1892: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1893: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1897: THEN
1898: OPEN x_cursor FOR
1899: 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 , TX8, TX7
1900: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1901: WHERE contains( concat_col, p_contains_str)>0
1902: AND d.search_context_id = p_search_ctx_id
1903: AND( (l_search_merged ='Y' )
1904: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1905: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1914: IF p_search_rel_cpts = 'N' THEN
1915: OPEN x_cursor FOR
1916: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX8, TX7
1917: FROM HZ_STAGED_CONTACT_POINTS stage
1918: WHERE contains( concat_col, p_contains_str)>0
1919: AND( (l_search_merged ='Y' )
1920: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1921: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1922: AND stage.party_id = p_party_id;
1923: ELSE
1924: OPEN x_cursor FOR
1925: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX8, TX7
1926: FROM HZ_STAGED_CONTACT_POINTS stage
1927: WHERE contains( concat_col, p_contains_str)>0
1928: AND( (l_search_merged ='Y' )
1929: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1930: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1931: AND stage.party_id = p_party_id
1931: AND stage.party_id = p_party_id
1932: UNION
1933: 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 , TX8, TX7
1934: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1935: WHERE contains( concat_col, p_contains_str)>0
1936: AND r.object_id = p_party_id
1937: AND( (l_search_merged ='Y' )
1938: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1939: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1953: END IF;
1954: OPEN x_cursor FOR
1955: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX8, TX7
1956: FROM HZ_STAGED_CONTACT_POINTS stage
1957: WHERE contains( concat_col, p_contains_str)>0
1958: AND EXISTS (
1959: SELECT 1 FROM HZ_STAGED_PARTIES p
1960: WHERE p.PARTY_ID = stage.PARTY_ID
1961: AND( (l_search_merged ='Y' )
1973: IF p_person_api = 'Y' THEN
1974: OPEN x_cursor FOR
1975: 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 , TX8, TX7
1976: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1977: WHERE contains( concat_col, p_contains_str)>0
1978: AND d.search_context_id = p_search_ctx_id
1979: AND d.party_id = stage.party_id
1980: AND( (l_search_merged ='Y' )
1981: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1983: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1984: UNION
1985: 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 , TX8, TX7
1986: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1987: WHERE contains( concat_col, p_contains_str)>0
1988: AND d.search_context_id = p_search_ctx_id
1989: AND d.party_id = r.subject_id
1990: AND r.relationship_id = oc.party_relationship_id
1991: AND oc.org_contact_id = stage.org_contact_id
1996: ELSE
1997: OPEN x_cursor FOR
1998: 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 , TX8, TX7
1999: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2000: WHERE contains( concat_col, p_contains_str)>0
2001: AND d.search_context_id = p_search_ctx_id
2002: AND d.party_id = stage.party_id
2003: AND( (l_search_merged ='Y' )
2004: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2041: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2042: END IF;
2043: 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 , TX8, TX7'||
2044: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2045: ' WHERE contains( concat_col, :cont)>0 '||
2046: ' AND (stage.org_contact_id is null '||
2047: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2048: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2049: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2051: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2052: ' UNION ' ||
2053: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX8, TX7'||
2054: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2055: ' WHERE contains( concat_col, :cont)>0 '||
2056: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2057: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2058: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2059: ' AND (stage.org_contact_id in ' ||
2061: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2062: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2063: ' and ('||p_restrict_sql1 || ') ) )' ||
2064: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2065: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2066: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2067: ELSE
2068: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX8, TX7'||
2069: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2062: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2063: ' and ('||p_restrict_sql1 || ') ) )' ||
2064: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2065: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2066: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2067: ELSE
2068: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX8, TX7'||
2069: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2070: ' WHERE contains( concat_col, :cont)>0'||
2066: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2067: ELSE
2068: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX8, TX7'||
2069: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2070: ' WHERE contains( concat_col, :cont)>0'||
2071: ' AND EXISTS ('||
2072: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2073: ' WHERE p.party_id = stage.party_id ' ||
2074: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2076: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2077: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2078: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2079: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2080: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2081: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2082: ,p_dup_party_id, p_dup_party_id;
2083: END IF;
2084: ELSE
2083: END IF;
2084: ELSE
2085: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX8, TX7'||
2086: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2087: ' WHERE contains( concat_col, :cont)>0'||
2088: ' AND EXISTS ('||
2089: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2090: ' WHERE p.party_id = stage.party_id ' ||
2091: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2093: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2094: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2095: ' AND ('||p_restrict_sql||')' ||
2096: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2097: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2098: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2099: ,p_dup_party_id, p_dup_party_id;
2100: END IF;
2101: END IF;
2100: END IF;
2101: END IF;
2102: END IF;
2103: output_long_strings('----------------------------------------------------------');
2104: output_long_strings('Contact Points Contains String = '||p_contains_str);
2105: output_long_strings('Restrict Sql = '||p_restrict_sql);
2106: exception
2107: when others then
2108: if (instrb(SQLERRM,'DRG-51030')>0) then
2221: x_num_matches OUT NUMBER
2222: ) IS
2223:
2224: -- Strings to hold the generated Intermedia query strings
2225: l_party_contains_str VARCHAR2(32000);
2226: l_party_site_contains_str VARCHAR2(32000);
2227: l_contact_contains_str VARCHAR2(32000);
2228: l_contact_pt_contains_str VARCHAR2(32000);
2229: l_denorm_str VARCHAR2(32000);
2222: ) IS
2223:
2224: -- Strings to hold the generated Intermedia query strings
2225: l_party_contains_str VARCHAR2(32000);
2226: l_party_site_contains_str VARCHAR2(32000);
2227: l_contact_contains_str VARCHAR2(32000);
2228: l_contact_pt_contains_str VARCHAR2(32000);
2229: l_denorm_str VARCHAR2(32000);
2230: l_ps_denorm_str VARCHAR2(32000);
2223:
2224: -- Strings to hold the generated Intermedia query strings
2225: l_party_contains_str VARCHAR2(32000);
2226: l_party_site_contains_str VARCHAR2(32000);
2227: l_contact_contains_str VARCHAR2(32000);
2228: l_contact_pt_contains_str VARCHAR2(32000);
2229: l_denorm_str VARCHAR2(32000);
2230: l_ps_denorm_str VARCHAR2(32000);
2231: l_ct_denorm_str VARCHAR2(32000);
2224: -- Strings to hold the generated Intermedia query strings
2225: l_party_contains_str VARCHAR2(32000);
2226: l_party_site_contains_str VARCHAR2(32000);
2227: l_contact_contains_str VARCHAR2(32000);
2228: l_contact_pt_contains_str VARCHAR2(32000);
2229: l_denorm_str VARCHAR2(32000);
2230: l_ps_denorm_str VARCHAR2(32000);
2231: l_ct_denorm_str VARCHAR2(32000);
2232: l_cpt_denorm_str VARCHAR2(32000);
2317: call_type(2) := 'OR';
2318: END;
2319:
2320: /** Private procedure to acquire and score at party level ***/
2321: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2322: l_party_id_idx NUMBER:=1;
2323: l_ctx_id NUMBER;
2324: l_precalc_score BOOLEAN := FALSE;
2325: BEGIN
2348: l_search_ctx_id,H_PARTY_ID(I));
2349: l_party_id_idx := H_PARTY_ID.COUNT+1;
2350: ELSE
2351: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2352: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2353: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2354: 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);
2355: END IF;
2356: 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
2356: 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
2357: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2358: 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);
2359: END IF;
2360: IF (l_party_site_contains_str IS NULL AND
2361: l_contact_contains_str IS NULL AND
2362: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2363: 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);
2364: RAISE L_RETURN_IMM_EXC;
2357: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2358: 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);
2359: END IF;
2360: IF (l_party_site_contains_str IS NULL AND
2361: l_contact_contains_str IS NULL AND
2362: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2363: 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);
2364: RAISE L_RETURN_IMM_EXC;
2365: ELSE
2358: 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);
2359: END IF;
2360: IF (l_party_site_contains_str IS NULL AND
2361: l_contact_contains_str IS NULL AND
2362: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2363: 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);
2364: RAISE L_RETURN_IMM_EXC;
2365: ELSE
2366: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2362: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2363: 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);
2364: RAISE L_RETURN_IMM_EXC;
2365: ELSE
2366: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2367: END IF;
2368: ELSE
2369: IF p_index>1 THEN
2370: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2385: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2386: 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);
2387: END IF;
2388: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2389: 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);
2390: END IF;
2391: LOOP
2392: FETCH l_party_cur INTO
2393: l_party_id , l_TX34, l_TX35, l_TX39, l_TX40, l_TX41, l_TX45;
2407: l_party_id_idx:= l_party_id_idx+1;
2408: END IF;
2409: IF (l_party_id_idx-1)>l_max_thresh THEN
2410: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2411: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2412: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2413: AND l_contact_pt_contains_str IS NULL THEN
2414: EXIT;
2415: END IF;
2409: IF (l_party_id_idx-1)>l_max_thresh THEN
2410: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2411: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2412: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2413: AND l_contact_pt_contains_str IS NULL THEN
2414: EXIT;
2415: END IF;
2416: CLOSE l_party_cur;
2417: IF p_index>1 THEN
2438: ROLLBACK to eval_start;
2439: END;
2440:
2441: /** Private procedure to acquire and score at party site level ***/
2442: 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
2443: l_party_id_idx NUMBER:=1;
2444: l_ctx_id NUMBER;
2445: h_ps_id HZ_PARTY_SEARCH.IDList;
2446: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2450: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2451: 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);
2452: 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);
2453: END IF;
2454: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2455: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2456: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2457: l_party_id := H_SCORES.FIRST;
2458: WHILE l_party_id IS NOT NULL LOOP
2451: 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);
2452: 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);
2453: END IF;
2454: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2455: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2456: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2457: l_party_id := H_SCORES.FIRST;
2458: WHILE l_party_id IS NOT NULL LOOP
2459: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2452: 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);
2453: END IF;
2454: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2455: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2456: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2457: l_party_id := H_SCORES.FIRST;
2458: WHILE l_party_id IS NOT NULL LOOP
2459: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2460: l_party_id:=H_SCORES.NEXT(l_party_id);
2482: l_search_ctx_id,H_PARTY_ID(I));
2483: l_party_id_idx := H_PARTY_ID.COUNT+1;
2484: ELSE
2485: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2486: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2487: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2488: 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);
2489: END IF;
2490: IF p_index>1 THEN
2504: END IF;
2505: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2506: 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);
2507: END IF;
2508: 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);
2509: LOOP
2510: FETCH l_party_site_cur INTO
2511: l_party_site_id, l_ps_party_id, l_ps_contact_id ;
2512: EXIT WHEN l_party_site_cur%NOTFOUND;
2560: END IF;
2561: END;
2562:
2563: /** Private procedure to acquire and score at contact point level ***/
2564: 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
2565: l_party_id_idx NUMBER:=1;
2566: l_ctx_id NUMBER;
2567: h_ct_id HZ_PARTY_SEARCH.IDList;
2568: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2594: l_search_ctx_id,H_PARTY_ID(I));
2595: l_party_id_idx := H_PARTY_ID.COUNT+1;
2596: ELSE
2597: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2598: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
2599: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2600: 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);
2601: END IF;
2602: IF p_index>1 THEN
2616: END IF;
2617: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2618: 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);
2619: END IF;
2620: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2621: LOOP
2622: FETCH l_contact_cur INTO
2623: l_org_contact_id, l_ct_party_id ;
2624: EXIT WHEN l_contact_cur%NOTFOUND;
2669: END IF;
2670: END;
2671:
2672: /** Private procedure to acquire and score at contact point level ***/
2673: 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
2674: l_party_id_idx NUMBER:=1;
2675: l_ctx_id NUMBER;
2676: h_cpt_id HZ_PARTY_SEARCH.IDList;
2677: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2705: l_search_ctx_id,H_PARTY_ID(I));
2706: l_party_id_idx := H_PARTY_ID.COUNT+1;
2707: ELSE
2708: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2709: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
2710: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2711: 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);
2712: END IF;
2713: IF p_index>1 THEN
2727: END IF;
2728: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2729: 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);
2730: END IF;
2731: 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);
2732: LOOP
2733: FETCH l_contact_pt_cur INTO
2734: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX8, l_TX7;
2735: EXIT WHEN l_contact_pt_cur%NOTFOUND;
2908: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
2909: l_threshold := round(( l_entered_max_score / 340) * 255);
2910: other_acq_criteria_exists := TRUE ;
2911: --check if acquisition criteria exists for any other entity
2912: IF l_party_contains_str IS NOT NULL THEN
2913: acq_cnt := 1;
2914: END IF;
2915: IF l_party_site_contains_str IS NOT NULL THEN
2916: acq_cnt := acq_cnt+1;
2911: --check if acquisition criteria exists for any other entity
2912: IF l_party_contains_str IS NOT NULL THEN
2913: acq_cnt := 1;
2914: END IF;
2915: IF l_party_site_contains_str IS NOT NULL THEN
2916: acq_cnt := acq_cnt+1;
2917: END IF;
2918: IF l_contact_contains_str IS NOT NULL THEN
2919: acq_cnt := acq_cnt+1;
2914: END IF;
2915: IF l_party_site_contains_str IS NOT NULL THEN
2916: acq_cnt := acq_cnt+1;
2917: END IF;
2918: IF l_contact_contains_str IS NOT NULL THEN
2919: acq_cnt := acq_cnt+1;
2920: END IF;
2921: IF l_contact_pt_contains_str IS NOT NULL THEN
2922: acq_cnt := acq_cnt+1;
2917: END IF;
2918: IF l_contact_contains_str IS NOT NULL THEN
2919: acq_cnt := acq_cnt+1;
2920: END IF;
2921: IF l_contact_pt_contains_str IS NOT NULL THEN
2922: acq_cnt := acq_cnt+1;
2923: END IF;
2924: IF acq_cnt>1 THEN
2925: other_acq_criteria_exists := TRUE;
2951: FND_MSG_PUB.ADD;
2952: RAISE FND_API.G_EXC_ERROR;
2953: END IF;
2954: END IF;
2955: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2956: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2957: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2958: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2959: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2952: RAISE FND_API.G_EXC_ERROR;
2953: END IF;
2954: END IF;
2955: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2956: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2957: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2958: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2959: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2960: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2953: END IF;
2954: END IF;
2955: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2956: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2957: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2958: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2959: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2960: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2961: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2954: END IF;
2955: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2956: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2957: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2958: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2959: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2960: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2961: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2962: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2955: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2956: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2957: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2958: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2959: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2960: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2961: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2962: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2963: END IF;
2956: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2957: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2958: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2959: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2960: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2961: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2962: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2963: END IF;
2964: END;
2957: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2958: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2959: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2960: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2961: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2962: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2963: END IF;
2964: END;
2965: /************ End of find_parties private procedures **********/
2958: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2959: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2960: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2961: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2962: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2963: END IF;
2964: END;
2965: /************ End of find_parties private procedures **********/
2966:
3030: 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);
3031: END IF;
3032: -- ************************************************************
3033: -- STEP 2. Setup of intermedia query strings for Acquisition query
3034: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3035: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3036: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3037: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3038: 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);
3031: END IF;
3032: -- ************************************************************
3033: -- STEP 2. Setup of intermedia query strings for Acquisition query
3034: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3035: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3036: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3037: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3038: 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);
3039: END IF;
3032: -- ************************************************************
3033: -- STEP 2. Setup of intermedia query strings for Acquisition query
3034: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3035: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3036: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3037: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3038: 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);
3039: END IF;
3040: l_denorm_max_score:=0;
3064: END IF;
3065: ELSE
3066: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
3067: END IF;
3068: 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((255/340)*l_entered_max_score));
3069: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3070:
3071: -- Setup Search Context ID
3072: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3074:
3075: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3076: 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);
3077: END IF;
3078: IF l_party_contains_str IS NULL THEN
3079: defpt := 1;
3080: END IF;
3081: IF l_party_site_contains_str IS NULL THEN
3082: defps := 1;
3077: END IF;
3078: IF l_party_contains_str IS NULL THEN
3079: defpt := 1;
3080: END IF;
3081: IF l_party_site_contains_str IS NULL THEN
3082: defps := 1;
3083: END IF;
3084: IF l_contact_contains_str IS NULL THEN
3085: defct := 1;
3080: END IF;
3081: IF l_party_site_contains_str IS NULL THEN
3082: defps := 1;
3083: END IF;
3084: IF l_contact_contains_str IS NULL THEN
3085: defct := 1;
3086: END IF;
3087: IF l_contact_pt_contains_str IS NULL THEN
3088: defcpt := 1;
3083: END IF;
3084: IF l_contact_contains_str IS NULL THEN
3085: defct := 1;
3086: END IF;
3087: IF l_contact_pt_contains_str IS NULL THEN
3088: defcpt := 1;
3089: END IF;
3090:
3091: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3092: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3093: 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);
3094: END IF;
3095: 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);
3096: 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);
3097: 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);
3098: 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);
3099: 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);
3100: 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);
3093: 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);
3094: END IF;
3095: 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);
3096: 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);
3097: 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);
3098: 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);
3099: 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);
3100: 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);
3101: END IF;
3094: END IF;
3095: 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);
3096: 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);
3097: 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);
3098: 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);
3099: 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);
3100: 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);
3101: END IF;
3102: FOR I in 1..5 LOOP
3095: 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);
3096: 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);
3097: 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);
3098: 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);
3099: 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);
3100: 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);
3101: END IF;
3102: FOR I in 1..5 LOOP
3103: do_eval(I);
3177: x_num_matches OUT NUMBER
3178: ) IS
3179:
3180: -- Strings to hold the generated Intermedia query strings
3181: l_party_contains_str VARCHAR2(32000);
3182: l_party_site_contains_str VARCHAR2(32000);
3183: l_contact_contains_str VARCHAR2(32000);
3184: l_contact_pt_contains_str VARCHAR2(32000);
3185: l_denorm_str VARCHAR2(32000);
3178: ) IS
3179:
3180: -- Strings to hold the generated Intermedia query strings
3181: l_party_contains_str VARCHAR2(32000);
3182: l_party_site_contains_str VARCHAR2(32000);
3183: l_contact_contains_str VARCHAR2(32000);
3184: l_contact_pt_contains_str VARCHAR2(32000);
3185: l_denorm_str VARCHAR2(32000);
3186: l_ps_denorm_str VARCHAR2(32000);
3179:
3180: -- Strings to hold the generated Intermedia query strings
3181: l_party_contains_str VARCHAR2(32000);
3182: l_party_site_contains_str VARCHAR2(32000);
3183: l_contact_contains_str VARCHAR2(32000);
3184: l_contact_pt_contains_str VARCHAR2(32000);
3185: l_denorm_str VARCHAR2(32000);
3186: l_ps_denorm_str VARCHAR2(32000);
3187: l_ct_denorm_str VARCHAR2(32000);
3180: -- Strings to hold the generated Intermedia query strings
3181: l_party_contains_str VARCHAR2(32000);
3182: l_party_site_contains_str VARCHAR2(32000);
3183: l_contact_contains_str VARCHAR2(32000);
3184: l_contact_pt_contains_str VARCHAR2(32000);
3185: l_denorm_str VARCHAR2(32000);
3186: l_ps_denorm_str VARCHAR2(32000);
3187: l_ct_denorm_str VARCHAR2(32000);
3188: l_cpt_denorm_str VARCHAR2(32000);
3303: call_type(2) := 'OR';
3304: END;
3305:
3306: /** Private procedure to acquire and score at party level ***/
3307: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3308: l_party_id_idx NUMBER:=1;
3309: l_ctx_id NUMBER;
3310: BEGIN
3311: SAVEPOINT eval_start;
3324: l_search_ctx_id,H_PARTY_ID(I));
3325: l_party_id_idx := H_PARTY_ID.COUNT+1;
3326: ELSE
3327: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3328: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3329: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3330: 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);
3331: END IF;
3332: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3332: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3333: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3334: 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);
3335: END IF;
3336: IF (l_party_site_contains_str IS NULL AND
3337: l_contact_contains_str IS NULL AND
3338: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3339: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3340: RAISE L_RETURN_IMM_EXC;
3333: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3334: 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);
3335: END IF;
3336: IF (l_party_site_contains_str IS NULL AND
3337: l_contact_contains_str IS NULL AND
3338: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3339: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3340: RAISE L_RETURN_IMM_EXC;
3341: ELSE
3334: 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);
3335: END IF;
3336: IF (l_party_site_contains_str IS NULL AND
3337: l_contact_contains_str IS NULL AND
3338: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3339: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3340: RAISE L_RETURN_IMM_EXC;
3341: ELSE
3342: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3338: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3339: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3340: RAISE L_RETURN_IMM_EXC;
3341: ELSE
3342: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3343: END IF;
3344: ELSE
3345: IF p_index>1 THEN
3346: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3358: l_ctx_id := NULL;
3359: l_party_id_idx := H_PARTY_ID.COUNT+1;
3360: END IF;
3361: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3362: 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);
3363: END IF;
3364: LOOP
3365: FETCH l_party_cur INTO
3366: l_party_id , l_TX34, l_TX35, l_TX39, l_TX40, l_TX41, l_TX45;
3380: l_party_id_idx:= l_party_id_idx+1;
3381: END IF;
3382: IF (l_party_id_idx-1)>l_max_thresh THEN
3383: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3384: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3385: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3386: AND l_contact_pt_contains_str IS NULL THEN
3387: H_PARTY_ID.DELETE(l_party_id_idx-1);
3388: H_PARTY_ID_LIST.DELETE(l_index);
3382: IF (l_party_id_idx-1)>l_max_thresh THEN
3383: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3384: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3385: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3386: AND l_contact_pt_contains_str IS NULL THEN
3387: H_PARTY_ID.DELETE(l_party_id_idx-1);
3388: H_PARTY_ID_LIST.DELETE(l_index);
3389: H_SCORES.DELETE(l_index);
3390: EXIT;
3411: CLOSE l_party_cur;
3412: ROLLBACK to eval_start;
3413: END;
3414: PROCEDURE open_person_contact_cursor(
3415: p_contains_str VARCHAR2,
3416: p_search_ctx_id NUMBER,
3417: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3418: BEGIN
3419: OPEN x_cursor FOR
3418: BEGIN
3419: OPEN x_cursor FOR
3420: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
3421: FROM HZ_STAGED_CONTACTS stage
3422: WHERE contains( concat_col, p_contains_str)>0
3423: AND ORG_CONTACT_ID in (
3424: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3425: org_contact_id
3426: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc
3442: end if;
3443: END;
3444:
3445: /** Private procedure to acquire and score at party site level ***/
3446: 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
3447: l_party_id_idx NUMBER:=1;
3448: l_ctx_id NUMBER;
3449: h_ps_id HZ_PARTY_SEARCH.IDList;
3450: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3470: ELSE
3471: l_party_id_idx := H_PARTY_ID.COUNT+1;
3472: l_ctx_id := NULL;
3473: END IF;
3474: 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);
3475: LOOP
3476: FETCH l_party_site_cur INTO
3477: l_party_site_id, l_ps_party_id, l_ps_contact_id ;
3478: EXIT WHEN l_party_site_cur%NOTFOUND;
3541: END IF;
3542: END;
3543:
3544: /** Private procedure to acquire and score at party site level ***/
3545: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3546: l_party_id_idx NUMBER:=1;
3547: l_ctx_id NUMBER;
3548: h_ct_id HZ_PARTY_SEARCH.IDList;
3549: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3557: FORALL I in 1..H_PARTY_ID.COUNT
3558: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
3559: l_search_ctx_id,H_PARTY_ID(I));
3560: l_party_id_idx := H_PARTY_ID.COUNT+1;
3561: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
3562: LOOP
3563: FETCH l_contact_cur INTO
3564: l_org_contact_id, l_ct_party_id ;
3565: EXIT WHEN l_contact_cur%NOTFOUND;
3599: END IF;
3600: END;
3601:
3602: /** Private procedure to acquire and score at contact point level ***/
3603: 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
3604: l_party_id_idx NUMBER:=1;
3605: l_ctx_id NUMBER;
3606: h_cpt_id HZ_PARTY_SEARCH.IDList;
3607: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3644: END IF;
3645: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3646: 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);
3647: END IF;
3648: 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);
3649: LOOP
3650: FETCH l_contact_pt_cur INTO
3651: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX8, l_TX7;
3652: EXIT WHEN l_contact_pt_cur%NOTFOUND;
3847: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3848: l_threshold := round(( l_entered_max_score / 340) * 255);
3849: other_acq_criteria_exists := TRUE ;
3850: --check if acquisition criteria exists for any other entity
3851: IF l_party_contains_str IS NOT NULL THEN
3852: acq_cnt := 1;
3853: END IF;
3854: IF l_party_site_contains_str IS NOT NULL THEN
3855: acq_cnt := acq_cnt+1;
3850: --check if acquisition criteria exists for any other entity
3851: IF l_party_contains_str IS NOT NULL THEN
3852: acq_cnt := 1;
3853: END IF;
3854: IF l_party_site_contains_str IS NOT NULL THEN
3855: acq_cnt := acq_cnt+1;
3856: END IF;
3857: IF l_contact_contains_str IS NOT NULL THEN
3858: acq_cnt := acq_cnt+1;
3853: END IF;
3854: IF l_party_site_contains_str IS NOT NULL THEN
3855: acq_cnt := acq_cnt+1;
3856: END IF;
3857: IF l_contact_contains_str IS NOT NULL THEN
3858: acq_cnt := acq_cnt+1;
3859: END IF;
3860: IF l_contact_pt_contains_str IS NOT NULL THEN
3861: acq_cnt := acq_cnt+1;
3856: END IF;
3857: IF l_contact_contains_str IS NOT NULL THEN
3858: acq_cnt := acq_cnt+1;
3859: END IF;
3860: IF l_contact_pt_contains_str IS NOT NULL THEN
3861: acq_cnt := acq_cnt+1;
3862: END IF;
3863: IF acq_cnt>1 THEN
3864: other_acq_criteria_exists := TRUE;
3890: FND_MSG_PUB.ADD;
3891: RAISE FND_API.G_EXC_ERROR;
3892: END IF;
3893: END IF;
3894: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3895: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3896: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3897: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3898: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3891: RAISE FND_API.G_EXC_ERROR;
3892: END IF;
3893: END IF;
3894: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3895: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3896: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3897: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3898: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3899: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3892: END IF;
3893: END IF;
3894: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3895: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3896: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3897: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3898: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3899: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3900: END IF;
3893: END IF;
3894: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3895: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3896: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3897: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3898: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3899: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3900: END IF;
3901: END;
3894: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3895: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3896: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3897: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3898: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3899: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3900: END IF;
3901: END;
3902: /************ End of find_persons private procedures **********/
3895: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3896: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3897: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3898: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3899: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3900: END IF;
3901: END;
3902: /************ End of find_persons private procedures **********/
3903:
3930: g_score_until_thresh := false;
3931: END IF;
3932: -- ************************************************************
3933: -- STEP 2. Setup of intermedia query strings for Acquisition query
3934: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3935: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3936: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3937: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3938: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3931: END IF;
3932: -- ************************************************************
3933: -- STEP 2. Setup of intermedia query strings for Acquisition query
3934: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3935: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3936: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3937: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3938: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3939:
3932: -- ************************************************************
3933: -- STEP 2. Setup of intermedia query strings for Acquisition query
3934: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3935: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3936: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3937: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3938: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3939:
3940: -- Setup Search Context ID
3933: -- STEP 2. Setup of intermedia query strings for Acquisition query
3934: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3935: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3936: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3937: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3938: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3939:
3940: -- Setup Search Context ID
3941: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3940: -- Setup Search Context ID
3941: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3942: x_search_ctx_id := l_search_ctx_id;
3943:
3944: IF l_party_contains_str IS NULL THEN
3945: defpt := 1;
3946: END IF;
3947: IF l_party_site_contains_str IS NULL THEN
3948: defps := 1;
3943:
3944: IF l_party_contains_str IS NULL THEN
3945: defpt := 1;
3946: END IF;
3947: IF l_party_site_contains_str IS NULL THEN
3948: defps := 1;
3949: END IF;
3950: IF l_contact_contains_str IS NULL THEN
3951: defct := 1;
3946: END IF;
3947: IF l_party_site_contains_str IS NULL THEN
3948: defps := 1;
3949: END IF;
3950: IF l_contact_contains_str IS NULL THEN
3951: defct := 1;
3952: END IF;
3953: IF l_contact_pt_contains_str IS NULL THEN
3954: defcpt := 1;
3949: END IF;
3950: IF l_contact_contains_str IS NULL THEN
3951: defct := 1;
3952: END IF;
3953: IF l_contact_pt_contains_str IS NULL THEN
3954: defcpt := 1;
3955: END IF;
3956:
3957: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3955: END IF;
3956:
3957: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3958: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3959: hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3960: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3961: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3962: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3963: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3956:
3957: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3958: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3959: hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3960: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3961: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3962: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3963: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3964: END IF;
3957: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3958: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3959: hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3960: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3961: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3962: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3963: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3964: END IF;
3965: FOR I in 1..5 LOOP
3958: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3959: hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3960: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3961: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3962: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3963: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3964: END IF;
3965: FOR I in 1..5 LOOP
3966: do_eval(I);
3964: END IF;
3965: FOR I in 1..5 LOOP
3966: do_eval(I);
3967: END LOOP;
3968: IF l_contact_contains_str IS NOT NULL THEN
3969: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3970: END IF;
3971: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3972: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 75 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3965: FOR I in 1..5 LOOP
3966: do_eval(I);
3967: END LOOP;
3968: IF l_contact_contains_str IS NOT NULL THEN
3969: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3970: END IF;
3971: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3972: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 75 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3973: END IF;
4750: x_num_matches OUT NUMBER
4751: ) IS
4752:
4753: -- Strings to hold the generated Intermedia query strings
4754: l_party_contains_str VARCHAR2(32000);
4755: l_match_idx NUMBER;
4756: l_party_site_contains_str VARCHAR2(32000);
4757: l_contact_contains_str VARCHAR2(32000);
4758: l_contact_pt_contains_str VARCHAR2(32000);
4752:
4753: -- Strings to hold the generated Intermedia query strings
4754: l_party_contains_str VARCHAR2(32000);
4755: l_match_idx NUMBER;
4756: l_party_site_contains_str VARCHAR2(32000);
4757: l_contact_contains_str VARCHAR2(32000);
4758: l_contact_pt_contains_str VARCHAR2(32000);
4759: l_tmp VARCHAR2(32000);
4760:
4753: -- Strings to hold the generated Intermedia query strings
4754: l_party_contains_str VARCHAR2(32000);
4755: l_match_idx NUMBER;
4756: l_party_site_contains_str VARCHAR2(32000);
4757: l_contact_contains_str VARCHAR2(32000);
4758: l_contact_pt_contains_str VARCHAR2(32000);
4759: l_tmp VARCHAR2(32000);
4760:
4761: -- Other local variables
4754: l_party_contains_str VARCHAR2(32000);
4755: l_match_idx NUMBER;
4756: l_party_site_contains_str VARCHAR2(32000);
4757: l_contact_contains_str VARCHAR2(32000);
4758: l_contact_pt_contains_str VARCHAR2(32000);
4759: l_tmp VARCHAR2(32000);
4760:
4761: -- Other local variables
4762: l_match_str VARCHAR2(30); -- Match type (AND or OR)
4829: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4830:
4831: -- ************************************************************
4832: -- STEP 2. Setup of intermedia query strings for Acquisition query
4833: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4834: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4835: 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);
4836:
4837: -- Setup Search Context ID
4830:
4831: -- ************************************************************
4832: -- STEP 2. Setup of intermedia query strings for Acquisition query
4833: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4834: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4835: 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);
4836:
4837: -- Setup Search Context ID
4838: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4839: x_search_ctx_id := l_search_ctx_id;
4840:
4841: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4842: 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);
4843: 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);
4844: 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);
4845: 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);
4846: END IF;
4847: IF l_party_site_contains_str IS NULL THEN
4840:
4841: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4842: 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);
4843: 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);
4844: 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);
4845: 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);
4846: END IF;
4847: IF l_party_site_contains_str IS NULL THEN
4848: defps := 1;
4843: 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);
4844: 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);
4845: 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);
4846: END IF;
4847: IF l_party_site_contains_str IS NULL THEN
4848: defps := 1;
4849: END IF;
4850: IF l_contact_pt_contains_str IS NULL THEN
4851: defcpt := 1;
4846: END IF;
4847: IF l_party_site_contains_str IS NULL THEN
4848: defps := 1;
4849: END IF;
4850: IF l_contact_pt_contains_str IS NULL THEN
4851: defcpt := 1;
4852: END IF;
4853:
4854: IF l_party_site_contains_str IS NOT NULL THEN
4850: IF l_contact_pt_contains_str IS NULL THEN
4851: defcpt := 1;
4852: END IF;
4853:
4854: IF l_party_site_contains_str IS NOT NULL THEN
4855: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4856: LOOP
4857: FETCH l_party_site_cur INTO
4858: l_party_site_id, l_ps_party_id, l_ps_contact_id ;
4851: defcpt := 1;
4852: END IF;
4853:
4854: IF l_party_site_contains_str IS NOT NULL THEN
4855: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4856: LOOP
4857: FETCH l_party_site_cur INTO
4858: l_party_site_id, l_ps_party_id, l_ps_contact_id ;
4859: EXIT WHEN l_party_site_cur%NOTFOUND;
4874: END LOOP;
4875: CLOSE l_party_site_cur;
4876: END IF;
4877:
4878: IF l_contact_pt_contains_str IS NOT NULL THEN
4879: SAVEPOINT eval_start;
4880: IF l_match_str = ' AND ' OR (
4881: ((l_cpt_max_score/l_entered_max_score)<(255/340))
4882: ) THEN
4882: ) THEN
4883: FORALL I in 1..l_match_ps_list.COUNT
4884: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4885: values (l_search_ctx_id,l_match_ps_list(I));
4886: 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');
4887: ELSE
4888: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4889: END IF;
4890: LOOP
4884: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4885: values (l_search_ctx_id,l_match_ps_list(I));
4886: 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');
4887: ELSE
4888: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4889: END IF;
4890: LOOP
4891: FETCH l_contact_pt_cur INTO
4892: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX8, l_TX7;
4984: x_num_matches OUT NUMBER
4985: ) IS
4986:
4987: -- Strings to hold the generated Intermedia query strings
4988: l_party_contains_str VARCHAR2(32000);
4989: l_party_site_contains_str VARCHAR2(32000);
4990: l_contact_contains_str VARCHAR2(32000);
4991: l_contact_pt_contains_str VARCHAR2(32000);
4992: l_tmp VARCHAR2(32000);
4985: ) IS
4986:
4987: -- Strings to hold the generated Intermedia query strings
4988: l_party_contains_str VARCHAR2(32000);
4989: l_party_site_contains_str VARCHAR2(32000);
4990: l_contact_contains_str VARCHAR2(32000);
4991: l_contact_pt_contains_str VARCHAR2(32000);
4992: l_tmp VARCHAR2(32000);
4993:
4986:
4987: -- Strings to hold the generated Intermedia query strings
4988: l_party_contains_str VARCHAR2(32000);
4989: l_party_site_contains_str VARCHAR2(32000);
4990: l_contact_contains_str VARCHAR2(32000);
4991: l_contact_pt_contains_str VARCHAR2(32000);
4992: l_tmp VARCHAR2(32000);
4993:
4994: -- Other local variables
4987: -- Strings to hold the generated Intermedia query strings
4988: l_party_contains_str VARCHAR2(32000);
4989: l_party_site_contains_str VARCHAR2(32000);
4990: l_contact_contains_str VARCHAR2(32000);
4991: l_contact_pt_contains_str VARCHAR2(32000);
4992: l_tmp VARCHAR2(32000);
4993:
4994: -- Other local variables
4995: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5060: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5061:
5062: -- ************************************************************
5063: -- STEP 2. Setup of intermedia query strings for Acquisition query
5064: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5065: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5066: 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);
5067:
5068: -- Setup Search Context ID
5061:
5062: -- ************************************************************
5063: -- STEP 2. Setup of intermedia query strings for Acquisition query
5064: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5065: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5066: 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);
5067:
5068: -- Setup Search Context ID
5069: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5070: x_search_ctx_id := l_search_ctx_id;
5071:
5072: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5073: 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);
5074: 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);
5075: 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);
5076: 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);
5077: END IF;
5078: IF l_contact_contains_str IS NULL THEN
5071:
5072: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5073: 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);
5074: 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);
5075: 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);
5076: 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);
5077: END IF;
5078: IF l_contact_contains_str IS NULL THEN
5079: defct := 1;
5074: 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);
5075: 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);
5076: 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);
5077: END IF;
5078: IF l_contact_contains_str IS NULL THEN
5079: defct := 1;
5080: END IF;
5081: IF l_contact_pt_contains_str IS NULL THEN
5082: defcpt := 1;
5077: END IF;
5078: IF l_contact_contains_str IS NULL THEN
5079: defct := 1;
5080: END IF;
5081: IF l_contact_pt_contains_str IS NULL THEN
5082: defcpt := 1;
5083: END IF;
5084:
5085: IF l_contact_contains_str IS NOT NULL THEN
5081: IF l_contact_pt_contains_str IS NULL THEN
5082: defcpt := 1;
5083: END IF;
5084:
5085: IF l_contact_contains_str IS NOT NULL THEN
5086: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5087: LOOP
5088: FETCH l_contact_cur INTO
5089: l_org_contact_id, l_ct_party_id ;
5082: defcpt := 1;
5083: END IF;
5084:
5085: IF l_contact_contains_str IS NOT NULL THEN
5086: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5087: LOOP
5088: FETCH l_contact_cur INTO
5089: l_org_contact_id, l_ct_party_id ;
5090: EXIT WHEN l_contact_cur%NOTFOUND;
5103: END LOOP;
5104: CLOSE l_contact_cur;
5105: END IF;
5106:
5107: IF l_contact_pt_contains_str IS NOT NULL THEN
5108: SAVEPOINT eval_start;
5109: IF l_match_str = ' AND ' OR (
5110: ((l_cpt_max_score/l_entered_max_score)<(255/340))
5111: ) THEN
5111: ) THEN
5112: FORALL I in 1..l_match_contact_list.COUNT
5113: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5114: values (l_search_ctx_id,l_match_contact_list(I));
5115: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur,'CONTACTS');
5116: ELSE
5117: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5118: END IF;
5119: LOOP
5113: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5114: values (l_search_ctx_id,l_match_contact_list(I));
5115: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur,'CONTACTS');
5116: ELSE
5117: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5118: END IF;
5119: LOOP
5120: FETCH l_contact_pt_cur INTO
5121: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX8, l_TX7;
5211: ) IS
5212:
5213:
5214: -- Strings to hold the generated Intermedia query strings
5215: l_contact_pt_contains_str VARCHAR2(32000);
5216: l_tmp VARCHAR2(32000);
5217:
5218: -- Other local variables
5219: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5271: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5272:
5273: -- ************************************************************
5274: -- STEP 2. Setup of intermedia query strings for Acquisition query
5275: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5276: 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);
5277:
5278: -- Setup Search Context ID
5279: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5279: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5280: x_search_ctx_id := l_search_ctx_id;
5281: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5282: 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);
5283: 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);
5284: 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);
5285: END IF;
5286:
5287: IF l_contact_pt_contains_str IS NOT NULL THEN
5283: 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);
5284: 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);
5285: END IF;
5286:
5287: IF l_contact_pt_contains_str IS NOT NULL THEN
5288: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5289: cnt := 1;
5290: LOOP
5291: FETCH l_contact_pt_cur INTO
5284: 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);
5285: END IF;
5286:
5287: IF l_contact_pt_contains_str IS NOT NULL THEN
5288: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5289: cnt := 1;
5290: LOOP
5291: FETCH l_contact_pt_cur INTO
5292: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX8, l_TX7;
5355: x_search_ctx_id IN OUT NUMBER
5356: ) IS
5357:
5358: -- Strings to hold the generated Intermedia query strings
5359: l_party_contains_str VARCHAR2(32000);
5360: l_party_site_contains_str VARCHAR2(32000);
5361: l_contact_contains_str VARCHAR2(32000);
5362: l_contact_pt_contains_str VARCHAR2(32000);
5363: l_tmp VARCHAR2(32000);
5356: ) IS
5357:
5358: -- Strings to hold the generated Intermedia query strings
5359: l_party_contains_str VARCHAR2(32000);
5360: l_party_site_contains_str VARCHAR2(32000);
5361: l_contact_contains_str VARCHAR2(32000);
5362: l_contact_pt_contains_str VARCHAR2(32000);
5363: l_tmp VARCHAR2(32000);
5364:
5357:
5358: -- Strings to hold the generated Intermedia query strings
5359: l_party_contains_str VARCHAR2(32000);
5360: l_party_site_contains_str VARCHAR2(32000);
5361: l_contact_contains_str VARCHAR2(32000);
5362: l_contact_pt_contains_str VARCHAR2(32000);
5363: l_tmp VARCHAR2(32000);
5364:
5365: -- Other local variables
5358: -- Strings to hold the generated Intermedia query strings
5359: l_party_contains_str VARCHAR2(32000);
5360: l_party_site_contains_str VARCHAR2(32000);
5361: l_contact_contains_str VARCHAR2(32000);
5362: l_contact_pt_contains_str VARCHAR2(32000);
5363: l_tmp VARCHAR2(32000);
5364:
5365: -- Other local variables
5366: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5431: g_score_until_thresh := false;
5432: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5433: -- ************************************************************
5434: -- STEP 2. Setup of intermedia query strings for Acquisition query
5435: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5436: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5437: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5438: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5439:
5432: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5433: -- ************************************************************
5434: -- STEP 2. Setup of intermedia query strings for Acquisition query
5435: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5436: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5437: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5438: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5439:
5440: -- Setup Search Context ID
5433: -- ************************************************************
5434: -- STEP 2. Setup of intermedia query strings for Acquisition query
5435: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5436: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5437: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5438: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5439:
5440: -- Setup Search Context ID
5441: IF x_search_ctx_id IS NULL THEN
5453: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX34,l_TX35,l_TX39,l_TX40,l_TX41,l_TX45,1);
5454: END LOOP;
5455: CLOSE l_party_cur;
5456:
5457: IF l_party_site_contains_str IS NOT NULL THEN
5458: l_max_score := 0;
5459: l_max_id := 0;
5460: l_max_idx := 0;
5461: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5458: l_max_score := 0;
5459: l_max_id := 0;
5460: l_max_idx := 0;
5461: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5462: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5463: ELSE
5464: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5465: END IF;
5466: LOOP
5460: l_max_idx := 0;
5461: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5462: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5463: ELSE
5464: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5465: END IF;
5466: LOOP
5467: FETCH l_party_site_cur INTO
5468: l_party_site_id, l_ps_party_id,l_ps_contact_id ;
5479: 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_idx);
5480: END IF;
5481: END IF;
5482:
5483: IF l_contact_contains_str IS NOT NULL THEN
5484: l_max_score := 0;
5485: l_max_id := 0;
5486: l_max_idx := 0;
5487: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5483: IF l_contact_contains_str IS NOT NULL THEN
5484: l_max_score := 0;
5485: l_max_id := 0;
5486: l_max_idx := 0;
5487: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5488: LOOP
5489: FETCH l_contact_cur INTO
5490: l_org_contact_id, l_ct_party_id ;
5491: EXIT WHEN l_contact_cur%NOTFOUND;
5501: 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);
5502: END IF;
5503: END IF;
5504:
5505: IF l_contact_pt_contains_str IS NOT NULL THEN
5506: l_max_score := 0;
5507: l_max_id := 0;
5508: l_max_idx := 0;
5509: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5506: l_max_score := 0;
5507: l_max_id := 0;
5508: l_max_idx := 0;
5509: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5510: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5511: ELSE
5512: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5513: END IF;
5514: LOOP
5508: l_max_idx := 0;
5509: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5510: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5511: ELSE
5512: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5513: END IF;
5514: LOOP
5515: FETCH l_contact_pt_cur INTO
5516: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX8, l_TX7;