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

APPS.HZ_MATCH_RULE_41 dependencies on CONTAINS

Line 70: p_contains_str VARCHAR2) RETURN NUMBER IS

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

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

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

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

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

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

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

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

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

Line 956: l_party_contains_str VARCHAR2(32000);

952: p_party_max_score NUMBER,
953: p_denorm_max_score NUMBER,
954: p_non_denorm_max_score NUMBER,
955: p_threshold NUMBER) RETURN VARCHAR2 IS
956: l_party_contains_str VARCHAR2(32000);
957: l_party_filter VARCHAR2(1) := null;
958: l_prim_temp VARCHAR2(4000);
959: l_denorm_str VARCHAR2(4000);
960: BEGIN

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

996:
997: IF l_denorm_str IS NOT NULL THEN
998: IF (p_non_denorm_max_score+50)>=p_threshold THEN
999: l_denorm_str := NULL;
1000: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1001: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
1002: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1003: l_denorm_str := NULL;
1004: END IF;

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

998: IF (p_non_denorm_max_score+50)>=p_threshold THEN
999: l_denorm_str := NULL;
1000: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1001: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
1002: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1003: l_denorm_str := NULL;
1004: END IF;
1005: ELSE
1006: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

1002: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1003: l_denorm_str := NULL;
1004: END IF;
1005: ELSE
1006: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1007: END IF;
1008:
1009: -- Setup query string for TAX_REFERENCE
1010: l_prim_temp := null;

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

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

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

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

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

1019: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1020: l_denorm_str := NULL;
1021: END IF;
1022: ELSE
1023: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1024: END IF;
1025: IF lengthb(l_party_contains_str) > 4000 THEN
1026: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1027: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');

Line 1025: IF lengthb(l_party_contains_str) > 4000 THEN

1021: END IF;
1022: ELSE
1023: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1024: END IF;
1025: IF lengthb(l_party_contains_str) > 4000 THEN
1026: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1027: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1028: FND_MSG_PUB.ADD;
1029: RAISE FND_API.G_EXC_ERROR;

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

1027: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1028: FND_MSG_PUB.ADD;
1029: RAISE FND_API.G_EXC_ERROR;
1030: END IF;
1031: IF (p_match_str = ' AND ' OR p_party_max_score 1032: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1033: 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);
1034: END IF;
1035: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

1029: RAISE FND_API.G_EXC_ERROR;
1030: END IF;
1031: IF (p_match_str = ' AND ' OR p_party_max_score 1032: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1033: 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);
1034: END IF;
1035: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1036: 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);
1037: END IF;

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

1032: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1033: 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);
1034: END IF;
1035: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1036: 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);
1037: END IF;
1038: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1039: 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);
1040: END IF;

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

1037: END IF;
1038: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1039: 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);
1040: END IF;
1041: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1042: ELSE
1043: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1044: 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);
1045: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

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

1040: END IF;
1041: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1042: ELSE
1043: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1044: 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);
1045: 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);
1046: END IF;
1047: RETURN l_party_contains_str;
1048: END IF;

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

1041: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1042: ELSE
1043: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1044: 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);
1045: 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);
1046: END IF;
1047: RETURN l_party_contains_str;
1048: END IF;
1049: END;

Line 1047: RETURN l_party_contains_str;

1043: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1044: 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);
1045: 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);
1046: END IF;
1047: RETURN l_party_contains_str;
1048: END IF;
1049: END;
1050: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1051: l_contains_str VARCHAR2(32000);

Line 1051: l_contains_str VARCHAR2(32000);

1047: RETURN l_party_contains_str;
1048: END IF;
1049: END;
1050: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1051: l_contains_str VARCHAR2(32000);
1052: l_contains_str_temp VARCHAR2(32000);
1053: l_den_contains_str VARCHAR2(32000);
1054: l_den_contains_str_temp VARCHAR2(32000);
1055: l_filter_str VARCHAR2(4000) := null;

Line 1052: l_contains_str_temp VARCHAR2(32000);

1048: END IF;
1049: END;
1050: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1051: l_contains_str VARCHAR2(32000);
1052: l_contains_str_temp VARCHAR2(32000);
1053: l_den_contains_str VARCHAR2(32000);
1054: l_den_contains_str_temp VARCHAR2(32000);
1055: l_filter_str VARCHAR2(4000) := null;
1056: l_prim_temp VARCHAR2(4000) := null;

Line 1053: l_den_contains_str VARCHAR2(32000);

1049: END;
1050: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1051: l_contains_str VARCHAR2(32000);
1052: l_contains_str_temp VARCHAR2(32000);
1053: l_den_contains_str VARCHAR2(32000);
1054: l_den_contains_str_temp VARCHAR2(32000);
1055: l_filter_str VARCHAR2(4000) := null;
1056: l_prim_temp VARCHAR2(4000) := null;
1057: l_prim_temp_den VARCHAR2(4000) := null;

Line 1054: l_den_contains_str_temp VARCHAR2(32000);

1050: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1051: l_contains_str VARCHAR2(32000);
1052: l_contains_str_temp VARCHAR2(32000);
1053: l_den_contains_str VARCHAR2(32000);
1054: l_den_contains_str_temp VARCHAR2(32000);
1055: l_filter_str VARCHAR2(4000) := null;
1056: l_prim_temp VARCHAR2(4000) := null;
1057: l_prim_temp_den VARCHAR2(4000) := null;
1058: BEGIN

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

1060: hz_utility_v2pub.debug(p_message=>'INIT_PARTY_SITES_QUERY ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
1061: END IF;
1062: x_denorm_str := NULL;
1063:
1064: -- Dynamic setup of party site contains str
1065: --
1066: -- For each primary transformation add to intermedia query if it
1067: -- is not null
1068: FOR I IN 1..g_party_site_stage_list.COUNT LOOP

Line 1069: l_contains_str_temp := null;

1065: --
1066: -- For each primary transformation add to intermedia query if it
1067: -- is not null
1068: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1069: l_contains_str_temp := null;
1070: l_den_contains_str_temp := null;
1071: l_filter_str := null;
1072:
1073: -- Setup query string for ADDRESS

Line 1070: l_den_contains_str_temp := null;

1066: -- For each primary transformation add to intermedia query if it
1067: -- is not null
1068: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1069: l_contains_str_temp := null;
1070: l_den_contains_str_temp := null;
1071: l_filter_str := null;
1072:
1073: -- Setup query string for ADDRESS
1074: l_prim_temp := null;

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

1074: l_prim_temp := null;
1075: l_prim_temp_den := null;
1076: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRADDRESS
1077: g_party_site_stage_list(I).TX4,'A27',l_prim_temp);
1078: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1079:
1080: -- Setup query string for POSTAL_CODE
1081: l_prim_temp := null;
1082: l_prim_temp_den := null;

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

1083: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1084: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1085: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1086: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1087: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1088: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1089:
1090: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1091: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);

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

1084: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1085: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1086: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1087: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1088: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1089:
1090: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1091: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1092: END LOOP;

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

1086: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1087: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1088: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1089:
1090: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1091: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1092: END LOOP;
1093: -- Add the search criteria to query string
1094: IF lengthb(l_contains_str) > 4000 THEN

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

1087: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1088: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1089:
1090: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1091: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1092: END LOOP;
1093: -- Add the search criteria to query string
1094: IF lengthb(l_contains_str) > 4000 THEN
1095: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');

Line 1094: IF lengthb(l_contains_str) > 4000 THEN

1090: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1091: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1092: END LOOP;
1093: -- Add the search criteria to query string
1094: IF lengthb(l_contains_str) > 4000 THEN
1095: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1096: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1097: FND_MSG_PUB.ADD;
1098: RAISE FND_API.G_EXC_ERROR;

Line 1100: x_denorm_str := l_den_contains_str;

1096: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1097: FND_MSG_PUB.ADD;
1098: RAISE FND_API.G_EXC_ERROR;
1099: END IF;
1100: x_denorm_str := l_den_contains_str;
1101: RETURN l_contains_str;
1102: END;
1103:
1104: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS

Line 1101: RETURN l_contains_str;

1097: FND_MSG_PUB.ADD;
1098: RAISE FND_API.G_EXC_ERROR;
1099: END IF;
1100: x_denorm_str := l_den_contains_str;
1101: RETURN l_contains_str;
1102: END;
1103:
1104: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1105: l_contains_str VARCHAR2(32000);

Line 1105: l_contains_str VARCHAR2(32000);

1101: RETURN l_contains_str;
1102: END;
1103:
1104: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1105: l_contains_str VARCHAR2(32000);
1106: l_contains_str_temp VARCHAR2(32000);
1107: l_den_contains_str VARCHAR2(32000);
1108: l_den_contains_str_temp VARCHAR2(32000);
1109: l_filter_str VARCHAR2(4000) := null;

Line 1106: l_contains_str_temp VARCHAR2(32000);

1102: END;
1103:
1104: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1105: l_contains_str VARCHAR2(32000);
1106: l_contains_str_temp VARCHAR2(32000);
1107: l_den_contains_str VARCHAR2(32000);
1108: l_den_contains_str_temp VARCHAR2(32000);
1109: l_filter_str VARCHAR2(4000) := null;
1110: l_prim_temp VARCHAR2(4000) := null;

Line 1107: l_den_contains_str VARCHAR2(32000);

1103:
1104: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1105: l_contains_str VARCHAR2(32000);
1106: l_contains_str_temp VARCHAR2(32000);
1107: l_den_contains_str VARCHAR2(32000);
1108: l_den_contains_str_temp VARCHAR2(32000);
1109: l_filter_str VARCHAR2(4000) := null;
1110: l_prim_temp VARCHAR2(4000) := null;
1111: l_prim_temp_den VARCHAR2(4000) := null;

Line 1108: l_den_contains_str_temp VARCHAR2(32000);

1104: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1105: l_contains_str VARCHAR2(32000);
1106: l_contains_str_temp VARCHAR2(32000);
1107: l_den_contains_str VARCHAR2(32000);
1108: l_den_contains_str_temp VARCHAR2(32000);
1109: l_filter_str VARCHAR2(4000) := null;
1110: l_prim_temp VARCHAR2(4000) := null;
1111: l_prim_temp_den VARCHAR2(4000) := null;
1112: BEGIN

Line 1121: l_contains_str VARCHAR2(32000);

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

Line 1122: l_contains_str_temp VARCHAR2(32000);

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

Line 1123: l_den_contains_str VARCHAR2(32000);

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

Line 1124: l_den_contains_str_temp VARCHAR2(32000);

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

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

1136: 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);
1137: END IF;
1138: x_denorm_str := NULL;
1139:
1140: -- Dynamic setup of party site contains str
1141: --
1142: -- For each primary transformation add to intermedia query if it
1143: -- is not null
1144: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP

Line 1145: l_contains_str_temp := null;

1141: --
1142: -- For each primary transformation add to intermedia query if it
1143: -- is not null
1144: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1145: l_contains_str_temp := null;
1146: l_den_contains_str_temp := null;
1147: l_filter_str := null;
1148: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1149: if(l_cnt_pt_type.count>0) then

Line 1146: l_den_contains_str_temp := null;

1142: -- For each primary transformation add to intermedia query if it
1143: -- is not null
1144: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1145: l_contains_str_temp := null;
1146: l_den_contains_str_temp := null;
1147: l_filter_str := null;
1148: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1149: if(l_cnt_pt_type.count>0) then
1150: x_modify := 'Y';

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

1166: l_prim_temp := null;
1167: l_prim_temp_den := null;
1168: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (EMAIL) + WRDOMAIN
1169: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1170: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1171:
1172: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1173: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1174: IF N>1 THEN

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

1168: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (EMAIL) + WRDOMAIN
1169: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1170: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1171:
1172: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1173: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1174: IF N>1 THEN
1175: distinct_search_cpt_types := N-1;
1176: ELSE

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

1169: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1170: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1171:
1172: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1173: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1174: IF N>1 THEN
1175: distinct_search_cpt_types := N-1;
1176: ELSE
1177: distinct_search_cpt_types := N;

Line 1184: IF lengthb(l_contains_str) > 4000 THEN

1180: 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);
1181: END IF;
1182: END LOOP;
1183: -- Add the search criteria to query string
1184: IF lengthb(l_contains_str) > 4000 THEN
1185: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1186: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1187: FND_MSG_PUB.ADD;
1188: RAISE FND_API.G_EXC_ERROR;

Line 1190: x_denorm_str := l_den_contains_str;

1186: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1187: FND_MSG_PUB.ADD;
1188: RAISE FND_API.G_EXC_ERROR;
1189: END IF;
1190: x_denorm_str := l_den_contains_str;
1191: RETURN l_contains_str;
1192: END;
1193:
1194: PROCEDURE open_party_cursor_direct (

Line 1191: RETURN l_contains_str;

1187: FND_MSG_PUB.ADD;
1188: RAISE FND_API.G_EXC_ERROR;
1189: END IF;
1190: x_denorm_str := l_den_contains_str;
1191: RETURN l_contains_str;
1192: END;
1193:
1194: PROCEDURE open_party_cursor_direct (
1195: p_dup_party_id NUMBER,

Line 1199: p_party_contains_str VARCHAR2,

1195: p_dup_party_id NUMBER,
1196: p_restrict_sql VARCHAR2,
1197: p_match_str VARCHAR2,
1198: p_search_merged VARCHAR2,
1199: p_party_contains_str VARCHAR2,
1200: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1201: l_sqlstr VARCHAR2(4000);
1202: l_search_merged VARCHAR2(1);
1203: BEGIN

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

1212: END IF;
1213: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1214: 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);
1215: END IF;
1216: 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
1217: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1218: 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);
1219: END IF;
1220: OPEN x_cursor FOR

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

1244: l_sqlstr := l_sqlstr || ' (TX44 like :TX44||'' %'' ';
1245: l_sqlstr := l_sqlstr || ' )) ';
1246: l_sqlstr := l_sqlstr || ' ) ';
1247: END IF;
1248: 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
1249: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1250: 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);
1251: END IF;
1252: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';

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

1246: l_sqlstr := l_sqlstr || ' ) ';
1247: END IF;
1248: 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
1249: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1250: 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);
1251: END IF;
1252: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1253: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX11||'' %'' ';
1254: l_sqlstr := l_sqlstr || ' )) ';

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

1267: END IF;
1268: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1269: 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);
1270: END IF;
1271: 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
1272: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1273: 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);
1274: END IF;
1275: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id

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

1269: 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);
1270: END IF;
1271: 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
1272: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1273: 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);
1274: END IF;
1275: 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
1276: ,g_party_stage_rec.TX44
1277: ,g_party_stage_rec.TX44

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

1285: 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
1286: ,g_party_stage_rec.TX44
1287: ,g_party_stage_rec.TX44
1288: ;
1289: 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
1290: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1291: 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);
1292: END IF;
1293: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id

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

1287: ,g_party_stage_rec.TX44
1288: ;
1289: 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
1290: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1291: 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);
1292: END IF;
1293: 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
1294: ,g_party_site_stage_list(1).TX11
1295: ,g_party_site_stage_list(1).TX11

Line 1308: p_contains_str VARCHAR2,

1304: END;
1305: PROCEDURE open_party_cursor(
1306: p_dup_party_id NUMBER,
1307: p_restrict_sql VARCHAR2,
1308: p_contains_str VARCHAR2,
1309: p_search_ctx_id NUMBER,
1310: p_match_str VARCHAR2,
1311: p_search_merged VARCHAR2,
1312: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS

Line 1327: IF p_contains_str IS NULL THEN

1323: l_search_merged := 'N';
1324: ELSE
1325: l_search_merged := p_search_merged;
1326: END IF;
1327: IF p_contains_str IS NULL THEN
1328: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1329: 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);
1330: END IF;
1331: OPEN x_cursor FOR

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

1325: l_search_merged := p_search_merged;
1326: END IF;
1327: IF p_contains_str IS NULL THEN
1328: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1329: 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);
1330: END IF;
1331: OPEN x_cursor FOR
1332: SELECT PARTY_ID , TX2, TX8, TX44
1333: FROM HZ_STAGED_PARTIES stage

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

1342: END IF;
1343: OPEN x_cursor FOR
1344: SELECT PARTY_ID , TX2, TX8, TX44
1345: FROM HZ_STAGED_PARTIES stage
1346: WHERE contains( concat_col, p_contains_str)>0
1347: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1348: AND( (l_search_merged ='Y' )
1349: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1350: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )

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

1355: END IF;
1356: OPEN x_cursor FOR
1357: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX2, TX8, TX44
1358: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1359: WHERE contains( concat_col, p_contains_str)>0
1360: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1361: AND d.party_id = stage.party_id
1362: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1363: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)

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

1378: END IF;
1379: IF p_search_ctx_id IS NULL THEN
1380: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX2, TX8, TX44'||
1381: ' FROM HZ_STAGED_PARTIES stage'||
1382: ' WHERE contains( concat_col, :cont)>0'||
1383: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1384: ' AND ('||p_restrict_sql||')' ||
1385: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1386: IF l_search_merged = 'Y' THEN

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

1390: ELSE
1391: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1392: END IF;
1393: output_long_strings('----------------------------------------------------------');
1394: output_long_strings('Party Contains String = '||p_contains_str);
1395: output_long_strings('Restrict Sql = '||p_restrict_sql);
1396: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1397: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1398: ,p_dup_party_id, p_dup_party_id;

Line 1396: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1392: END IF;
1393: output_long_strings('----------------------------------------------------------');
1394: output_long_strings('Party Contains String = '||p_contains_str);
1395: output_long_strings('Restrict Sql = '||p_restrict_sql);
1396: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1397: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1398: ,p_dup_party_id, p_dup_party_id;
1399: END IF;
1400: END IF;

Line 1427: p_contains_str VARCHAR2,

1423: PROCEDURE open_party_site_cursor(
1424: p_dup_party_id NUMBER,
1425: p_party_id NUMBER,
1426: p_restrict_sql VARCHAR2,
1427: p_contains_str VARCHAR2,
1428: p_search_ctx_id NUMBER,
1429: p_search_merged VARCHAR2,
1430: p_search_rel_sites VARCHAR2,
1431: p_person_api VARCHAR2,

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

1455: IF p_search_rel_sites = 'N' THEN
1456: OPEN x_cursor FOR
1457: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1458: FROM HZ_STAGED_PARTY_SITES stage
1459: WHERE contains( concat_col, p_contains_str)>0
1460: AND( (l_search_merged ='Y' )
1461: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1462: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1463: AND stage.party_id = p_party_id;

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

1464: ELSE
1465: OPEN x_cursor FOR
1466: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1467: FROM HZ_STAGED_PARTY_SITES stage
1468: WHERE contains( concat_col, p_contains_str)>0
1469: AND( (l_search_merged ='Y' )
1470: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1471: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1472: AND stage.party_id = p_party_id

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

1472: AND stage.party_id = p_party_id
1473: UNION
1474: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX3, TX4, TX11
1475: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1476: WHERE contains( concat_col, p_contains_str)>0
1477: AND( (l_search_merged ='Y' )
1478: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1479: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1480: AND r.object_id = p_party_id

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

1494: END IF;
1495: OPEN x_cursor FOR
1496: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1497: FROM HZ_STAGED_PARTY_SITES stage
1498: WHERE contains( concat_col, p_contains_str)>0
1499: AND EXISTS (
1500: SELECT 1 FROM HZ_STAGED_PARTIES p
1501: WHERE p.PARTY_ID = stage.PARTY_ID
1502: AND( (l_search_merged ='Y' )

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

1514: IF p_person_api = 'Y' THEN
1515: OPEN x_cursor FOR
1516: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1517: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1518: WHERE contains( concat_col, p_contains_str)>0
1519: AND( (l_search_merged ='Y' )
1520: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1521: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1522: AND d.search_context_id = p_search_ctx_id

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

1524: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1525: UNION
1526: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX3, TX4, TX11
1527: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1528: WHERE contains( concat_col, p_contains_str)>0
1529: AND( (l_search_merged ='Y' )
1530: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1531: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1532: AND d.search_context_id = p_search_ctx_id

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

1537: ELSE
1538: OPEN x_cursor FOR
1539: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1540: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1541: WHERE contains( concat_col, p_contains_str)>0
1542: AND( (l_search_merged ='Y' )
1543: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1544: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1545: AND d.search_context_id = p_search_ctx_id

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

1582: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1583: END IF;
1584: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1585: ' FROM HZ_STAGED_PARTY_SITES stage'||
1586: ' WHERE contains( concat_col, :cont)>0'||
1587: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1588: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1589: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1590: ' AND (ORG_CONTACT_ID IS NULL ' ||

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

1592: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1593: ' UNION ' ||
1594: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1595: ' FROM HZ_STAGED_PARTY_SITES stage'||
1596: ' WHERE contains( concat_col, :cont)>0'||
1597: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1598: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1599: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1600: ' AND ORG_CONTACT_ID IN ' ||

Line 1606: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1602: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1603: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1604: ' and ('||p_restrict_sql1|| ') )' ||
1605: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1606: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1607: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1608: ELSE
1609: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1610: ' FROM HZ_STAGED_PARTY_SITES stage'||

Line 1607: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1603: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1604: ' and ('||p_restrict_sql1|| ') )' ||
1605: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1606: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1607: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1608: ELSE
1609: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1610: ' FROM HZ_STAGED_PARTY_SITES stage'||
1611: ' WHERE contains( concat_col, :cont)>0'||

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

1607: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1608: ELSE
1609: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1610: ' FROM HZ_STAGED_PARTY_SITES stage'||
1611: ' WHERE contains( concat_col, :cont)>0'||
1612: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1613: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1614: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1615: ' AND EXISTS ('||

Line 1621: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1617: ' WHERE p.party_id = stage.party_id ' ||
1618: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1619: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1620: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1621: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1622: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1623: ,p_dup_party_id, p_dup_party_id;
1624: END IF;
1625: ELSE

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

1624: END IF;
1625: ELSE
1626: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1627: ' FROM HZ_STAGED_PARTY_SITES stage'||
1628: ' WHERE contains( concat_col, :cont)>0'||
1629: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1630: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1631: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1632: ' AND EXISTS ('||

Line 1638: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1634: ' WHERE p.party_id = stage.party_id ' ||
1635: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1636: ' AND ('||p_restrict_sql||')' ||
1637: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1638: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1639: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1640: ,p_dup_party_id, p_dup_party_id;
1641: END IF;
1642: END IF;

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

1640: ,p_dup_party_id, p_dup_party_id;
1641: END IF;
1642: END IF;
1643: output_long_strings('----------------------------------------------------------');
1644: output_long_strings('Party Site Contains String = '||p_contains_str);
1645: output_long_strings('Restrict Sql = '||p_restrict_sql);
1646: exception
1647: when others then
1648: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1672: p_contains_str VARCHAR2,

1668: PROCEDURE open_contact_cursor(
1669: p_dup_party_id NUMBER,
1670: p_party_id NUMBER,
1671: p_restrict_sql VARCHAR2,
1672: p_contains_str VARCHAR2,
1673: p_search_ctx_id NUMBER,
1674: p_search_merged VARCHAR2,
1675: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1676: l_sqlstr VARCHAR2(4000);

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

1696: END IF;
1697: OPEN x_cursor FOR
1698: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
1699: FROM HZ_STAGED_CONTACTS stage
1700: WHERE contains( concat_col, p_contains_str)>0
1701: AND EXISTS (
1702: SELECT 1 FROM HZ_STAGED_PARTIES p
1703: WHERE p.PARTY_ID = stage.PARTY_ID
1704: AND( (l_search_merged ='Y' )

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

1719: END IF;
1720: OPEN x_cursor FOR
1721: SELECT ORG_CONTACT_ID, PARTY_ID
1722: FROM HZ_STAGED_CONTACTS stage
1723: WHERE contains( concat_col, p_contains_str)>0
1724: AND EXISTS (
1725: SELECT 1 FROM HZ_STAGED_PARTIES p
1726: WHERE p.PARTY_ID = stage.PARTY_ID
1727: AND( (l_search_merged ='Y' )

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

1738: END IF;
1739: OPEN x_cursor FOR
1740: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
1741: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1742: WHERE contains( concat_col, p_contains_str)>0
1743: AND d.search_context_id = p_search_ctx_id
1744: AND d.party_id = stage.party_id
1745: AND( (l_search_merged ='Y' )
1746: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1772: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
1773: END IF;
1774: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
1775: ' FROM HZ_STAGED_CONTACTS stage'||
1776: ' WHERE contains( concat_col, :cont)>0'||
1777: ' AND EXISTS ('||
1778: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1779: ' WHERE p.party_id = stage.party_id ' ||
1780: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1786: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1782: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1783: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1784: ' AND ('||p_restrict_sql||')' ||
1785: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1786: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1787: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1788: ,p_dup_party_id, p_dup_party_id;
1789: END IF;
1790: output_long_strings('----------------------------------------------------------');

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

1787: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1788: ,p_dup_party_id, p_dup_party_id;
1789: END IF;
1790: output_long_strings('----------------------------------------------------------');
1791: output_long_strings('Contacts Contains String = '||p_contains_str);
1792: output_long_strings('Restrict Sql = '||p_restrict_sql);
1793: exception
1794: when others then
1795: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1819: p_contains_str VARCHAR2,

1815: PROCEDURE open_contact_pt_cursor(
1816: p_dup_party_id NUMBER,
1817: p_party_id NUMBER,
1818: p_restrict_sql VARCHAR2,
1819: p_contains_str VARCHAR2,
1820: p_search_ctx_id NUMBER,
1821: p_search_merged VARCHAR2,
1822: p_search_rel_cpts VARCHAR2,
1823: p_person_api VARCHAR2,

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

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

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

1857: THEN
1858: OPEN x_cursor FOR
1859: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N3) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1860: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1861: WHERE contains( concat_col, p_contains_str)>0
1862: AND d.search_context_id = p_search_ctx_id
1863: AND( (l_search_merged ='Y' )
1864: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1865: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1874: IF p_search_rel_cpts = 'N' THEN
1875: OPEN x_cursor FOR
1876: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1877: FROM HZ_STAGED_CONTACT_POINTS stage
1878: WHERE contains( concat_col, p_contains_str)>0
1879: AND( (l_search_merged ='Y' )
1880: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1881: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1882: AND stage.party_id = p_party_id;

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

1883: ELSE
1884: OPEN x_cursor FOR
1885: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1886: FROM HZ_STAGED_CONTACT_POINTS stage
1887: WHERE contains( concat_col, p_contains_str)>0
1888: AND( (l_search_merged ='Y' )
1889: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1890: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1891: AND stage.party_id = p_party_id

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

1891: AND stage.party_id = p_party_id
1892: UNION
1893: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ stage.CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , TX1, TX6, TX5
1894: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1895: WHERE contains( concat_col, p_contains_str)>0
1896: AND r.object_id = p_party_id
1897: AND( (l_search_merged ='Y' )
1898: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1899: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1913: END IF;
1914: OPEN x_cursor FOR
1915: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1916: FROM HZ_STAGED_CONTACT_POINTS stage
1917: WHERE contains( concat_col, p_contains_str)>0
1918: AND EXISTS (
1919: SELECT 1 FROM HZ_STAGED_PARTIES p
1920: WHERE p.PARTY_ID = stage.PARTY_ID
1921: AND( (l_search_merged ='Y' )

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

1933: IF p_person_api = 'Y' THEN
1934: OPEN x_cursor FOR
1935: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1936: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1937: WHERE contains( concat_col, p_contains_str)>0
1938: AND d.search_context_id = p_search_ctx_id
1939: AND d.party_id = stage.party_id
1940: AND( (l_search_merged ='Y' )
1941: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1943: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1944: UNION
1945: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, r.subject_id, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , TX1, TX6, TX5
1946: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1947: WHERE contains( concat_col, p_contains_str)>0
1948: AND d.search_context_id = p_search_ctx_id
1949: AND d.party_id = r.subject_id
1950: AND r.relationship_id = oc.party_relationship_id
1951: AND oc.org_contact_id = stage.org_contact_id

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

1956: ELSE
1957: OPEN x_cursor FOR
1958: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1959: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1960: WHERE contains( concat_col, p_contains_str)>0
1961: AND d.search_context_id = p_search_ctx_id
1962: AND d.party_id = stage.party_id
1963: AND( (l_search_merged ='Y' )
1964: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2001: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2002: END IF;
2003: l_sqlstr := ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2004: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2005: ' WHERE contains( concat_col, :cont)>0 '||
2006: ' AND (stage.org_contact_id is null '||
2007: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2008: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2009: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

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

2011: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2012: ' UNION ' ||
2013: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2014: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2015: ' WHERE contains( concat_col, :cont)>0 '||
2016: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2017: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2018: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2019: ' AND (stage.org_contact_id in ' ||

Line 2025: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

2021: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2022: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2023: ' and ('||p_restrict_sql1 || ') ) )' ||
2024: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2025: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2026: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2027: ELSE
2028: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2029: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

Line 2026: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

2022: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2023: ' and ('||p_restrict_sql1 || ') ) )' ||
2024: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2025: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2026: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2027: ELSE
2028: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2029: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2030: ' WHERE contains( concat_col, :cont)>0'||

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

2026: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2027: ELSE
2028: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2029: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2030: ' WHERE contains( concat_col, :cont)>0'||
2031: ' AND EXISTS ('||
2032: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2033: ' WHERE p.party_id = stage.party_id ' ||
2034: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 2040: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

2043: END IF;
2044: ELSE
2045: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2046: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2047: ' WHERE contains( concat_col, :cont)>0'||
2048: ' AND EXISTS ('||
2049: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2050: ' WHERE p.party_id = stage.party_id ' ||
2051: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 2057: OPEN x_cursor FOR l_sqlstr USING p_contains_str

2053: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2054: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2055: ' AND ('||p_restrict_sql||')' ||
2056: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2057: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2058: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2059: ,p_dup_party_id, p_dup_party_id;
2060: END IF;
2061: END IF;

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

2060: END IF;
2061: END IF;
2062: END IF;
2063: output_long_strings('----------------------------------------------------------');
2064: output_long_strings('Contact Points Contains String = '||p_contains_str);
2065: output_long_strings('Restrict Sql = '||p_restrict_sql);
2066: exception
2067: when others then
2068: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 2185: l_party_contains_str VARCHAR2(32000);

2181: x_num_matches OUT NUMBER
2182: ) IS
2183:
2184: -- Strings to hold the generated Intermedia query strings
2185: l_party_contains_str VARCHAR2(32000);
2186: l_party_site_contains_str VARCHAR2(32000);
2187: l_contact_contains_str VARCHAR2(32000);
2188: l_contact_pt_contains_str VARCHAR2(32000);
2189: l_denorm_str VARCHAR2(32000);

Line 2186: l_party_site_contains_str VARCHAR2(32000);

2182: ) IS
2183:
2184: -- Strings to hold the generated Intermedia query strings
2185: l_party_contains_str VARCHAR2(32000);
2186: l_party_site_contains_str VARCHAR2(32000);
2187: l_contact_contains_str VARCHAR2(32000);
2188: l_contact_pt_contains_str VARCHAR2(32000);
2189: l_denorm_str VARCHAR2(32000);
2190: l_ps_denorm_str VARCHAR2(32000);

Line 2187: l_contact_contains_str VARCHAR2(32000);

2183:
2184: -- Strings to hold the generated Intermedia query strings
2185: l_party_contains_str VARCHAR2(32000);
2186: l_party_site_contains_str VARCHAR2(32000);
2187: l_contact_contains_str VARCHAR2(32000);
2188: l_contact_pt_contains_str VARCHAR2(32000);
2189: l_denorm_str VARCHAR2(32000);
2190: l_ps_denorm_str VARCHAR2(32000);
2191: l_ct_denorm_str VARCHAR2(32000);

Line 2188: l_contact_pt_contains_str VARCHAR2(32000);

2184: -- Strings to hold the generated Intermedia query strings
2185: l_party_contains_str VARCHAR2(32000);
2186: l_party_site_contains_str VARCHAR2(32000);
2187: l_contact_contains_str VARCHAR2(32000);
2188: l_contact_pt_contains_str VARCHAR2(32000);
2189: l_denorm_str VARCHAR2(32000);
2190: l_ps_denorm_str VARCHAR2(32000);
2191: l_ct_denorm_str VARCHAR2(32000);
2192: l_cpt_denorm_str VARCHAR2(32000);

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

2278: call_type(2) := 'OR';
2279: END;
2280:
2281: /** Private procedure to acquire and score at party level ***/
2282: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2283: l_party_id_idx NUMBER:=1;
2284: l_ctx_id NUMBER;
2285: l_precalc_score BOOLEAN := FALSE;
2286: BEGIN

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

2309: l_search_ctx_id,H_PARTY_ID(I));
2310: l_party_id_idx := H_PARTY_ID.COUNT+1;
2311: ELSE
2312: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2313: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2314: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2315: 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);
2316: END IF;
2317: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' AND p_dup_batch_id IS NULL THEN

Line 2321: IF (l_party_site_contains_str IS NULL AND

2317: 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
2318: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2319: 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);
2320: END IF;
2321: IF (l_party_site_contains_str IS NULL AND
2322: l_contact_contains_str IS NULL AND
2323: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2324: 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);
2325: RAISE L_RETURN_IMM_EXC;

Line 2322: l_contact_contains_str IS NULL AND

2318: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2319: 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);
2320: END IF;
2321: IF (l_party_site_contains_str IS NULL AND
2322: l_contact_contains_str IS NULL AND
2323: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2324: 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);
2325: RAISE L_RETURN_IMM_EXC;
2326: ELSE

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

2319: 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);
2320: END IF;
2321: IF (l_party_site_contains_str IS NULL AND
2322: l_contact_contains_str IS NULL AND
2323: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2324: 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);
2325: RAISE L_RETURN_IMM_EXC;
2326: ELSE
2327: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);

Line 2327: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);

2323: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2324: 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);
2325: RAISE L_RETURN_IMM_EXC;
2326: ELSE
2327: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2328: END IF;
2329: ELSE
2330: IF p_index>1 THEN
2331: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

2346: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2347: 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);
2348: END IF;
2349: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2350: 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);
2351: END IF;
2352: LOOP
2353: FETCH l_party_cur INTO
2354: l_party_id , l_TX2, l_TX8, l_TX44;

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

2368: l_party_id_idx:= l_party_id_idx+1;
2369: END IF;
2370: IF (l_party_id_idx-1)>l_max_thresh THEN
2371: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2372: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2373: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2374: AND l_contact_pt_contains_str IS NULL THEN
2375: EXIT;
2376: END IF;

Line 2374: AND l_contact_pt_contains_str IS NULL THEN

2370: IF (l_party_id_idx-1)>l_max_thresh THEN
2371: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2372: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2373: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2374: AND l_contact_pt_contains_str IS NULL THEN
2375: EXIT;
2376: END IF;
2377: CLOSE l_party_cur;
2378: IF p_index>1 THEN

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

2399: ROLLBACK to eval_start;
2400: END;
2401:
2402: /** Private procedure to acquire and score at party site level ***/
2403: 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
2404: l_party_id_idx NUMBER:=1;
2405: l_ctx_id NUMBER;
2406: h_ps_id HZ_PARTY_SEARCH.IDList;
2407: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

2411: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2412: 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);
2413: 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);
2414: END IF;
2415: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2416: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2417: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2418: l_party_id := H_SCORES.FIRST;
2419: WHILE l_party_id IS NOT NULL LOOP

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

2412: 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);
2413: 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);
2414: END IF;
2415: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2416: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2417: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2418: l_party_id := H_SCORES.FIRST;
2419: WHILE l_party_id IS NOT NULL LOOP
2420: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;

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

2413: 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);
2414: END IF;
2415: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2416: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2417: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2418: l_party_id := H_SCORES.FIRST;
2419: WHILE l_party_id IS NOT NULL LOOP
2420: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2421: l_party_id:=H_SCORES.NEXT(l_party_id);

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

2443: l_search_ctx_id,H_PARTY_ID(I));
2444: l_party_id_idx := H_PARTY_ID.COUNT+1;
2445: ELSE
2446: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2447: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2448: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2449: 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);
2450: END IF;
2451: IF p_index>1 THEN

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

2465: END IF;
2466: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2467: 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);
2468: END IF;
2469: 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);
2470: LOOP
2471: FETCH l_party_site_cur INTO
2472: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX11;
2473: EXIT WHEN l_party_site_cur%NOTFOUND;

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

2521: END IF;
2522: END;
2523:
2524: /** Private procedure to acquire and score at contact point level ***/
2525: 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
2526: l_party_id_idx NUMBER:=1;
2527: l_ctx_id NUMBER;
2528: h_ct_id HZ_PARTY_SEARCH.IDList;
2529: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

2555: l_search_ctx_id,H_PARTY_ID(I));
2556: l_party_id_idx := H_PARTY_ID.COUNT+1;
2557: ELSE
2558: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2559: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
2560: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2561: 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);
2562: END IF;
2563: IF p_index>1 THEN

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

2577: END IF;
2578: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2579: 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);
2580: END IF;
2581: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2582: LOOP
2583: FETCH l_contact_cur INTO
2584: l_org_contact_id, l_ct_party_id ;
2585: EXIT WHEN l_contact_cur%NOTFOUND;

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

2630: END IF;
2631: END;
2632:
2633: /** Private procedure to acquire and score at contact point level ***/
2634: 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
2635: l_party_id_idx NUMBER:=1;
2636: l_ctx_id NUMBER;
2637: h_cpt_id HZ_PARTY_SEARCH.IDList;
2638: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

2666: l_search_ctx_id,H_PARTY_ID(I));
2667: l_party_id_idx := H_PARTY_ID.COUNT+1;
2668: ELSE
2669: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2670: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
2671: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2672: 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);
2673: END IF;
2674: IF p_index>1 THEN

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

2688: END IF;
2689: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2690: 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);
2691: END IF;
2692: 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);
2693: LOOP
2694: FETCH l_contact_pt_cur INTO
2695: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;
2696: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 2873: IF l_party_contains_str IS NOT NULL THEN

2869: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
2870: l_threshold := round(( l_entered_max_score / 360) * 101);
2871: other_acq_criteria_exists := TRUE ;
2872: --check if acquisition criteria exists for any other entity
2873: IF l_party_contains_str IS NOT NULL THEN
2874: acq_cnt := 1;
2875: END IF;
2876: IF l_party_site_contains_str IS NOT NULL THEN
2877: acq_cnt := acq_cnt+1;

Line 2876: IF l_party_site_contains_str IS NOT NULL THEN

2872: --check if acquisition criteria exists for any other entity
2873: IF l_party_contains_str IS NOT NULL THEN
2874: acq_cnt := 1;
2875: END IF;
2876: IF l_party_site_contains_str IS NOT NULL THEN
2877: acq_cnt := acq_cnt+1;
2878: END IF;
2879: IF l_contact_contains_str IS NOT NULL THEN
2880: acq_cnt := acq_cnt+1;

Line 2879: IF l_contact_contains_str IS NOT NULL THEN

2875: END IF;
2876: IF l_party_site_contains_str IS NOT NULL THEN
2877: acq_cnt := acq_cnt+1;
2878: END IF;
2879: IF l_contact_contains_str IS NOT NULL THEN
2880: acq_cnt := acq_cnt+1;
2881: END IF;
2882: IF l_contact_pt_contains_str IS NOT NULL THEN
2883: acq_cnt := acq_cnt+1;

Line 2882: IF l_contact_pt_contains_str IS NOT NULL THEN

2878: END IF;
2879: IF l_contact_contains_str IS NOT NULL THEN
2880: acq_cnt := acq_cnt+1;
2881: END IF;
2882: IF l_contact_pt_contains_str IS NOT NULL THEN
2883: acq_cnt := acq_cnt+1;
2884: END IF;
2885: IF acq_cnt>1 THEN
2886: other_acq_criteria_exists := TRUE;

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

2912: FND_MSG_PUB.ADD;
2913: RAISE FND_API.G_EXC_ERROR;
2914: END IF;
2915: END IF;
2916: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2917: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2918: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2919: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2920: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN

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

2913: RAISE FND_API.G_EXC_ERROR;
2914: END IF;
2915: END IF;
2916: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2917: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2918: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2919: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2920: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2921: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

2914: END IF;
2915: END IF;
2916: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2917: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2918: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2919: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2920: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2921: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2922: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

2915: END IF;
2916: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2917: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2918: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2919: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2920: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2921: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2922: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2923: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

2916: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2917: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2918: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2919: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2920: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2921: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2922: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2923: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2924: END IF;

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

2917: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2918: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2919: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2920: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2921: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2922: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2923: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2924: END IF;
2925: END;

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

2918: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2919: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2920: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2921: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2922: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2923: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2924: END IF;
2925: END;
2926: /************ End of find_parties private procedures **********/

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

2919: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2920: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2921: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2922: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2923: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2924: END IF;
2925: END;
2926: /************ End of find_parties private procedures **********/
2927:

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

2991: 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);
2992: END IF;
2993: -- ************************************************************
2994: -- STEP 2. Setup of intermedia query strings for Acquisition query
2995: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2996: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2997: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2998: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2999: hz_utility_v2pub.debug(p_message=>'Commencing the DENORM LOGIC in find_parties',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

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

2992: END IF;
2993: -- ************************************************************
2994: -- STEP 2. Setup of intermedia query strings for Acquisition query
2995: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2996: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2997: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2998: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2999: 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);
3000: END IF;

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

2993: -- ************************************************************
2994: -- STEP 2. Setup of intermedia query strings for Acquisition query
2995: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2996: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2997: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2998: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2999: 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);
3000: END IF;
3001: l_denorm_max_score:=0;

Line 3029: 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((101/360)*l_entered_max_score));

3025: END IF;
3026: ELSE
3027: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
3028: END IF;
3029: 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((101/360)*l_entered_max_score));
3030: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3031:
3032: -- Setup Search Context ID
3033: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 3039: IF l_party_contains_str IS NULL THEN

3035:
3036: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3037: 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);
3038: END IF;
3039: IF l_party_contains_str IS NULL THEN
3040: defpt := 1;
3041: END IF;
3042: IF l_party_site_contains_str IS NULL THEN
3043: defps := 1;

Line 3042: IF l_party_site_contains_str IS NULL THEN

3038: END IF;
3039: IF l_party_contains_str IS NULL THEN
3040: defpt := 1;
3041: END IF;
3042: IF l_party_site_contains_str IS NULL THEN
3043: defps := 1;
3044: END IF;
3045: IF l_contact_contains_str IS NULL THEN
3046: defct := 1;

Line 3045: IF l_contact_contains_str IS NULL THEN

3041: END IF;
3042: IF l_party_site_contains_str IS NULL THEN
3043: defps := 1;
3044: END IF;
3045: IF l_contact_contains_str IS NULL THEN
3046: defct := 1;
3047: END IF;
3048: IF l_contact_pt_contains_str IS NULL THEN
3049: defcpt := 1;

Line 3048: IF l_contact_pt_contains_str IS NULL THEN

3044: END IF;
3045: IF l_contact_contains_str IS NULL THEN
3046: defct := 1;
3047: END IF;
3048: IF l_contact_pt_contains_str IS NULL THEN
3049: defcpt := 1;
3050: END IF;
3051:
3052: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3053: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3054: 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);
3055: END IF;
3056: 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);
3057: 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);
3058: 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);
3059: 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);
3060: 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);
3061: hz_utility_v2pub.debug(p_message=>'In find_parties l_search_ctx_id = '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

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

3054: 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);
3055: END IF;
3056: 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);
3057: 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);
3058: 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);
3059: 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);
3060: 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);
3061: 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);
3062: END IF;

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

3055: END IF;
3056: 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);
3057: 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);
3058: 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);
3059: 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);
3060: 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);
3061: 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);
3062: END IF;
3063: FOR I in 1..5 LOOP

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

3056: 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);
3057: 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);
3058: 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);
3059: 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);
3060: 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);
3061: 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);
3062: END IF;
3063: FOR I in 1..5 LOOP
3064: do_eval(I);

Line 3142: l_party_contains_str VARCHAR2(32000);

3138: x_num_matches OUT NUMBER
3139: ) IS
3140:
3141: -- Strings to hold the generated Intermedia query strings
3142: l_party_contains_str VARCHAR2(32000);
3143: l_party_site_contains_str VARCHAR2(32000);
3144: l_contact_contains_str VARCHAR2(32000);
3145: l_contact_pt_contains_str VARCHAR2(32000);
3146: l_denorm_str VARCHAR2(32000);

Line 3143: l_party_site_contains_str VARCHAR2(32000);

3139: ) IS
3140:
3141: -- Strings to hold the generated Intermedia query strings
3142: l_party_contains_str VARCHAR2(32000);
3143: l_party_site_contains_str VARCHAR2(32000);
3144: l_contact_contains_str VARCHAR2(32000);
3145: l_contact_pt_contains_str VARCHAR2(32000);
3146: l_denorm_str VARCHAR2(32000);
3147: l_ps_denorm_str VARCHAR2(32000);

Line 3144: l_contact_contains_str VARCHAR2(32000);

3140:
3141: -- Strings to hold the generated Intermedia query strings
3142: l_party_contains_str VARCHAR2(32000);
3143: l_party_site_contains_str VARCHAR2(32000);
3144: l_contact_contains_str VARCHAR2(32000);
3145: l_contact_pt_contains_str VARCHAR2(32000);
3146: l_denorm_str VARCHAR2(32000);
3147: l_ps_denorm_str VARCHAR2(32000);
3148: l_ct_denorm_str VARCHAR2(32000);

Line 3145: l_contact_pt_contains_str VARCHAR2(32000);

3141: -- Strings to hold the generated Intermedia query strings
3142: l_party_contains_str VARCHAR2(32000);
3143: l_party_site_contains_str VARCHAR2(32000);
3144: l_contact_contains_str VARCHAR2(32000);
3145: l_contact_pt_contains_str VARCHAR2(32000);
3146: l_denorm_str VARCHAR2(32000);
3147: l_ps_denorm_str VARCHAR2(32000);
3148: l_ct_denorm_str VARCHAR2(32000);
3149: l_cpt_denorm_str VARCHAR2(32000);

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

3265: call_type(2) := 'OR';
3266: END;
3267:
3268: /** Private procedure to acquire and score at party level ***/
3269: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3270: l_party_id_idx NUMBER:=1;
3271: l_ctx_id NUMBER;
3272: BEGIN
3273: SAVEPOINT eval_start;

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

3286: l_search_ctx_id,H_PARTY_ID(I));
3287: l_party_id_idx := H_PARTY_ID.COUNT+1;
3288: ELSE
3289: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3290: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3291: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3292: 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);
3293: END IF;
3294: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN

Line 3298: IF (l_party_site_contains_str IS NULL AND

3294: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3295: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3296: 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);
3297: END IF;
3298: IF (l_party_site_contains_str IS NULL AND
3299: l_contact_contains_str IS NULL AND
3300: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3301: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3302: RAISE L_RETURN_IMM_EXC;

Line 3299: l_contact_contains_str IS NULL AND

3295: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3296: 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);
3297: END IF;
3298: IF (l_party_site_contains_str IS NULL AND
3299: l_contact_contains_str IS NULL AND
3300: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3301: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3302: RAISE L_RETURN_IMM_EXC;
3303: ELSE

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

3296: 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);
3297: END IF;
3298: IF (l_party_site_contains_str IS NULL AND
3299: l_contact_contains_str IS NULL AND
3300: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3301: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3302: RAISE L_RETURN_IMM_EXC;
3303: ELSE
3304: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);

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

3300: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3301: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3302: RAISE L_RETURN_IMM_EXC;
3303: ELSE
3304: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3305: END IF;
3306: ELSE
3307: IF p_index>1 THEN
3308: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3320: l_ctx_id := NULL;
3321: l_party_id_idx := H_PARTY_ID.COUNT+1;
3322: END IF;
3323: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3324: 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);
3325: END IF;
3326: LOOP
3327: FETCH l_party_cur INTO
3328: l_party_id , l_TX2, l_TX8, l_TX44;

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

3342: l_party_id_idx:= l_party_id_idx+1;
3343: END IF;
3344: IF (l_party_id_idx-1)>l_max_thresh THEN
3345: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3346: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3347: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3348: AND l_contact_pt_contains_str IS NULL THEN
3349: H_PARTY_ID.DELETE(l_party_id_idx-1);
3350: H_PARTY_ID_LIST.DELETE(l_index);

Line 3348: AND l_contact_pt_contains_str IS NULL THEN

3344: IF (l_party_id_idx-1)>l_max_thresh THEN
3345: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3346: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3347: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3348: AND l_contact_pt_contains_str IS NULL THEN
3349: H_PARTY_ID.DELETE(l_party_id_idx-1);
3350: H_PARTY_ID_LIST.DELETE(l_index);
3351: H_SCORES.DELETE(l_index);
3352: EXIT;

Line 3377: p_contains_str VARCHAR2,

3373: CLOSE l_party_cur;
3374: ROLLBACK to eval_start;
3375: END;
3376: PROCEDURE open_person_contact_cursor(
3377: p_contains_str VARCHAR2,
3378: p_search_ctx_id NUMBER,
3379: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3380: BEGIN
3381: OPEN x_cursor FOR

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

3380: BEGIN
3381: OPEN x_cursor FOR
3382: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
3383: FROM HZ_STAGED_CONTACTS stage
3384: WHERE contains( concat_col, p_contains_str)>0
3385: AND ORG_CONTACT_ID in (
3386: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3387: org_contact_id
3388: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc

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

3404: end if;
3405: END;
3406:
3407: /** Private procedure to acquire and score at party site level ***/
3408: 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
3409: l_party_id_idx NUMBER:=1;
3410: l_ctx_id NUMBER;
3411: h_ps_id HZ_PARTY_SEARCH.IDList;
3412: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

3432: ELSE
3433: l_party_id_idx := H_PARTY_ID.COUNT+1;
3434: l_ctx_id := NULL;
3435: END IF;
3436: 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);
3437: LOOP
3438: FETCH l_party_site_cur INTO
3439: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX11;
3440: EXIT WHEN l_party_site_cur%NOTFOUND;

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

3503: END IF;
3504: END;
3505:
3506: /** Private procedure to acquire and score at party site level ***/
3507: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3508: l_party_id_idx NUMBER:=1;
3509: l_ctx_id NUMBER;
3510: h_ct_id HZ_PARTY_SEARCH.IDList;
3511: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

3519: FORALL I in 1..H_PARTY_ID.COUNT
3520: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
3521: l_search_ctx_id,H_PARTY_ID(I));
3522: l_party_id_idx := H_PARTY_ID.COUNT+1;
3523: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
3524: LOOP
3525: FETCH l_contact_cur INTO
3526: l_org_contact_id, l_ct_party_id ;
3527: EXIT WHEN l_contact_cur%NOTFOUND;

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

3561: END IF;
3562: END;
3563:
3564: /** Private procedure to acquire and score at contact point level ***/
3565: 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
3566: l_party_id_idx NUMBER:=1;
3567: l_ctx_id NUMBER;
3568: h_cpt_id HZ_PARTY_SEARCH.IDList;
3569: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

3606: END IF;
3607: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3608: 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);
3609: END IF;
3610: 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);
3611: LOOP
3612: FETCH l_contact_pt_cur INTO
3613: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;
3614: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 3813: IF l_party_contains_str IS NOT NULL THEN

3809: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3810: l_threshold := round(( l_entered_max_score / 360) * 101);
3811: other_acq_criteria_exists := TRUE ;
3812: --check if acquisition criteria exists for any other entity
3813: IF l_party_contains_str IS NOT NULL THEN
3814: acq_cnt := 1;
3815: END IF;
3816: IF l_party_site_contains_str IS NOT NULL THEN
3817: acq_cnt := acq_cnt+1;

Line 3816: IF l_party_site_contains_str IS NOT NULL THEN

3812: --check if acquisition criteria exists for any other entity
3813: IF l_party_contains_str IS NOT NULL THEN
3814: acq_cnt := 1;
3815: END IF;
3816: IF l_party_site_contains_str IS NOT NULL THEN
3817: acq_cnt := acq_cnt+1;
3818: END IF;
3819: IF l_contact_contains_str IS NOT NULL THEN
3820: acq_cnt := acq_cnt+1;

Line 3819: IF l_contact_contains_str IS NOT NULL THEN

3815: END IF;
3816: IF l_party_site_contains_str IS NOT NULL THEN
3817: acq_cnt := acq_cnt+1;
3818: END IF;
3819: IF l_contact_contains_str IS NOT NULL THEN
3820: acq_cnt := acq_cnt+1;
3821: END IF;
3822: IF l_contact_pt_contains_str IS NOT NULL THEN
3823: acq_cnt := acq_cnt+1;

Line 3822: IF l_contact_pt_contains_str IS NOT NULL THEN

3818: END IF;
3819: IF l_contact_contains_str IS NOT NULL THEN
3820: acq_cnt := acq_cnt+1;
3821: END IF;
3822: IF l_contact_pt_contains_str IS NOT NULL THEN
3823: acq_cnt := acq_cnt+1;
3824: END IF;
3825: IF acq_cnt>1 THEN
3826: other_acq_criteria_exists := TRUE;

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

3852: FND_MSG_PUB.ADD;
3853: RAISE FND_API.G_EXC_ERROR;
3854: END IF;
3855: END IF;
3856: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3857: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3858: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3859: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3860: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

3853: RAISE FND_API.G_EXC_ERROR;
3854: END IF;
3855: END IF;
3856: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3857: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3858: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3859: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3860: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3861: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

3854: END IF;
3855: END IF;
3856: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3857: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3858: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3859: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3860: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3861: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3862: END IF;

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

3855: END IF;
3856: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3857: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3858: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3859: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3860: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3861: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3862: END IF;
3863: END;

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

3856: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3857: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3858: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3859: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3860: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3861: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3862: END IF;
3863: END;
3864: /************ End of find_persons private procedures **********/

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

3857: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3858: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3859: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3860: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3861: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3862: END IF;
3863: END;
3864: /************ End of find_persons private procedures **********/
3865:

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

3892: g_score_until_thresh := false;
3893: END IF;
3894: -- ************************************************************
3895: -- STEP 2. Setup of intermedia query strings for Acquisition query
3896: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3897: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3898: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3899: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3900: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);

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

3893: END IF;
3894: -- ************************************************************
3895: -- STEP 2. Setup of intermedia query strings for Acquisition query
3896: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3897: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3898: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3899: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3900: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3901:

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

3894: -- ************************************************************
3895: -- STEP 2. Setup of intermedia query strings for Acquisition query
3896: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3897: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3898: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3899: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3900: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3901:
3902: -- Setup Search Context ID

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

3895: -- STEP 2. Setup of intermedia query strings for Acquisition query
3896: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3897: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3898: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3899: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3900: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3901:
3902: -- Setup Search Context ID
3903: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 3906: IF l_party_contains_str IS NULL THEN

3902: -- Setup Search Context ID
3903: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3904: x_search_ctx_id := l_search_ctx_id;
3905:
3906: IF l_party_contains_str IS NULL THEN
3907: defpt := 1;
3908: END IF;
3909: IF l_party_site_contains_str IS NULL THEN
3910: defps := 1;

Line 3909: IF l_party_site_contains_str IS NULL THEN

3905:
3906: IF l_party_contains_str IS NULL THEN
3907: defpt := 1;
3908: END IF;
3909: IF l_party_site_contains_str IS NULL THEN
3910: defps := 1;
3911: END IF;
3912: IF l_contact_contains_str IS NULL THEN
3913: defct := 1;

Line 3912: IF l_contact_contains_str IS NULL THEN

3908: END IF;
3909: IF l_party_site_contains_str IS NULL THEN
3910: defps := 1;
3911: END IF;
3912: IF l_contact_contains_str IS NULL THEN
3913: defct := 1;
3914: END IF;
3915: IF l_contact_pt_contains_str IS NULL THEN
3916: defcpt := 1;

Line 3915: IF l_contact_pt_contains_str IS NULL THEN

3911: END IF;
3912: IF l_contact_contains_str IS NULL THEN
3913: defct := 1;
3914: END IF;
3915: IF l_contact_pt_contains_str IS NULL THEN
3916: defcpt := 1;
3917: END IF;
3918:
3919: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3917: END IF;
3918:
3919: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3920: 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);
3921: 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);
3922: 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);
3923: 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);
3924: 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);
3925: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

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

3918:
3919: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3920: 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);
3921: 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);
3922: 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);
3923: 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);
3924: 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);
3925: 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);
3926: END IF;

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

3919: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3920: 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);
3921: 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);
3922: 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);
3923: 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);
3924: 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);
3925: 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);
3926: END IF;
3927: FOR I in 1..5 LOOP

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

3920: 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);
3921: 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);
3922: 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);
3923: 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);
3924: 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);
3925: 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);
3926: END IF;
3927: FOR I in 1..5 LOOP
3928: do_eval(I);

Line 3930: IF l_contact_contains_str IS NOT NULL THEN

3926: END IF;
3927: FOR I in 1..5 LOOP
3928: do_eval(I);
3929: END LOOP;
3930: IF l_contact_contains_str IS NOT NULL THEN
3931: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3932: END IF;
3933: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3934: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 28 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

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

3927: FOR I in 1..5 LOOP
3928: do_eval(I);
3929: END LOOP;
3930: IF l_contact_contains_str IS NOT NULL THEN
3931: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3932: END IF;
3933: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3934: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 28 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3935: END IF;

Line 4733: l_party_contains_str VARCHAR2(32000);

4729: x_num_matches OUT NUMBER
4730: ) IS
4731:
4732: -- Strings to hold the generated Intermedia query strings
4733: l_party_contains_str VARCHAR2(32000);
4734: l_match_idx NUMBER;
4735: l_party_site_contains_str VARCHAR2(32000);
4736: l_contact_contains_str VARCHAR2(32000);
4737: l_contact_pt_contains_str VARCHAR2(32000);

Line 4735: l_party_site_contains_str VARCHAR2(32000);

4731:
4732: -- Strings to hold the generated Intermedia query strings
4733: l_party_contains_str VARCHAR2(32000);
4734: l_match_idx NUMBER;
4735: l_party_site_contains_str VARCHAR2(32000);
4736: l_contact_contains_str VARCHAR2(32000);
4737: l_contact_pt_contains_str VARCHAR2(32000);
4738: l_tmp VARCHAR2(32000);
4739:

Line 4736: l_contact_contains_str VARCHAR2(32000);

4732: -- Strings to hold the generated Intermedia query strings
4733: l_party_contains_str VARCHAR2(32000);
4734: l_match_idx NUMBER;
4735: l_party_site_contains_str VARCHAR2(32000);
4736: l_contact_contains_str VARCHAR2(32000);
4737: l_contact_pt_contains_str VARCHAR2(32000);
4738: l_tmp VARCHAR2(32000);
4739:
4740: -- Other local variables

Line 4737: l_contact_pt_contains_str VARCHAR2(32000);

4733: l_party_contains_str VARCHAR2(32000);
4734: l_match_idx NUMBER;
4735: l_party_site_contains_str VARCHAR2(32000);
4736: l_contact_contains_str VARCHAR2(32000);
4737: l_contact_pt_contains_str VARCHAR2(32000);
4738: l_tmp VARCHAR2(32000);
4739:
4740: -- Other local variables
4741: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4812: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4813:
4814: -- ************************************************************
4815: -- STEP 2. Setup of intermedia query strings for Acquisition query
4816: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4817: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4818: 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);
4819:
4820: -- Setup Search Context ID

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

4813:
4814: -- ************************************************************
4815: -- STEP 2. Setup of intermedia query strings for Acquisition query
4816: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4817: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4818: 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);
4819:
4820: -- Setup Search Context ID
4821: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4822: x_search_ctx_id := l_search_ctx_id;
4823:
4824: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4825: 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);
4826: 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);
4827: 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);
4828: 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);
4829: END IF;
4830: IF l_party_site_contains_str IS NULL THEN

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

4823:
4824: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4825: 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);
4826: 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);
4827: 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);
4828: 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);
4829: END IF;
4830: IF l_party_site_contains_str IS NULL THEN
4831: defps := 1;

Line 4830: IF l_party_site_contains_str IS NULL THEN

4826: 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);
4827: 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);
4828: 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);
4829: END IF;
4830: IF l_party_site_contains_str IS NULL THEN
4831: defps := 1;
4832: END IF;
4833: IF l_contact_pt_contains_str IS NULL THEN
4834: defcpt := 1;

Line 4833: IF l_contact_pt_contains_str IS NULL THEN

4829: END IF;
4830: IF l_party_site_contains_str IS NULL THEN
4831: defps := 1;
4832: END IF;
4833: IF l_contact_pt_contains_str IS NULL THEN
4834: defcpt := 1;
4835: END IF;
4836:
4837: IF l_party_site_contains_str IS NOT NULL THEN

Line 4837: IF l_party_site_contains_str IS NOT NULL THEN

4833: IF l_contact_pt_contains_str IS NULL THEN
4834: defcpt := 1;
4835: END IF;
4836:
4837: IF l_party_site_contains_str IS NOT NULL THEN
4838: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4839: LOOP
4840: FETCH l_party_site_cur INTO
4841: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX11;

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

4834: defcpt := 1;
4835: END IF;
4836:
4837: IF l_party_site_contains_str IS NOT NULL THEN
4838: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4839: LOOP
4840: FETCH l_party_site_cur INTO
4841: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX11;
4842: EXIT WHEN l_party_site_cur%NOTFOUND;

Line 4861: IF l_contact_pt_contains_str IS NOT NULL THEN

4857: END LOOP;
4858: CLOSE l_party_site_cur;
4859: END IF;
4860:
4861: IF l_contact_pt_contains_str IS NOT NULL THEN
4862: SAVEPOINT eval_start;
4863: IF l_match_str = ' AND ' OR (
4864: ((l_cpt_max_score/l_entered_max_score)<(101/360))
4865: ) THEN

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

4865: ) THEN
4866: FORALL I in 1..l_match_ps_list.COUNT
4867: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4868: values (l_search_ctx_id,l_match_ps_list(I));
4869: 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');
4870: ELSE
4871: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4872: END IF;
4873: LOOP

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

4867: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4868: values (l_search_ctx_id,l_match_ps_list(I));
4869: 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');
4870: ELSE
4871: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4872: END IF;
4873: LOOP
4874: FETCH l_contact_pt_cur INTO
4875: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;

Line 4971: l_party_contains_str VARCHAR2(32000);

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

Line 4972: l_party_site_contains_str VARCHAR2(32000);

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

Line 4973: l_contact_contains_str VARCHAR2(32000);

4969:
4970: -- Strings to hold the generated Intermedia query strings
4971: l_party_contains_str VARCHAR2(32000);
4972: l_party_site_contains_str VARCHAR2(32000);
4973: l_contact_contains_str VARCHAR2(32000);
4974: l_contact_pt_contains_str VARCHAR2(32000);
4975: l_tmp VARCHAR2(32000);
4976:
4977: -- Other local variables

Line 4974: l_contact_pt_contains_str VARCHAR2(32000);

4970: -- Strings to hold the generated Intermedia query strings
4971: l_party_contains_str VARCHAR2(32000);
4972: l_party_site_contains_str VARCHAR2(32000);
4973: l_contact_contains_str VARCHAR2(32000);
4974: l_contact_pt_contains_str VARCHAR2(32000);
4975: l_tmp VARCHAR2(32000);
4976:
4977: -- Other local variables
4978: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5044: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5045:
5046: -- ************************************************************
5047: -- STEP 2. Setup of intermedia query strings for Acquisition query
5048: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5049: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5050: 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);
5051:
5052: -- Setup Search Context ID

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

5045:
5046: -- ************************************************************
5047: -- STEP 2. Setup of intermedia query strings for Acquisition query
5048: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5049: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5050: 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);
5051:
5052: -- Setup Search Context ID
5053: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

5054: x_search_ctx_id := l_search_ctx_id;
5055:
5056: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5057: 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);
5058: 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);
5059: 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);
5060: 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);
5061: END IF;
5062: IF l_contact_contains_str IS NULL THEN

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

5055:
5056: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5057: 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);
5058: 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);
5059: 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);
5060: 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);
5061: END IF;
5062: IF l_contact_contains_str IS NULL THEN
5063: defct := 1;

Line 5062: IF l_contact_contains_str IS NULL THEN

5058: 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);
5059: 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);
5060: 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);
5061: END IF;
5062: IF l_contact_contains_str IS NULL THEN
5063: defct := 1;
5064: END IF;
5065: IF l_contact_pt_contains_str IS NULL THEN
5066: defcpt := 1;

Line 5065: IF l_contact_pt_contains_str IS NULL THEN

5061: END IF;
5062: IF l_contact_contains_str IS NULL THEN
5063: defct := 1;
5064: END IF;
5065: IF l_contact_pt_contains_str IS NULL THEN
5066: defcpt := 1;
5067: END IF;
5068:
5069: IF l_contact_contains_str IS NOT NULL THEN

Line 5069: IF l_contact_contains_str IS NOT NULL THEN

5065: IF l_contact_pt_contains_str IS NULL THEN
5066: defcpt := 1;
5067: END IF;
5068:
5069: IF l_contact_contains_str IS NOT NULL THEN
5070: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5071: LOOP
5072: FETCH l_contact_cur INTO
5073: l_org_contact_id, l_ct_party_id ;

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

5066: defcpt := 1;
5067: END IF;
5068:
5069: IF l_contact_contains_str IS NOT NULL THEN
5070: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5071: LOOP
5072: FETCH l_contact_cur INTO
5073: l_org_contact_id, l_ct_party_id ;
5074: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5091: IF l_contact_pt_contains_str IS NOT NULL THEN

5087: END LOOP;
5088: CLOSE l_contact_cur;
5089: END IF;
5090:
5091: IF l_contact_pt_contains_str IS NOT NULL THEN
5092: SAVEPOINT eval_start;
5093: IF l_match_str = ' AND ' OR (
5094: ((l_cpt_max_score/l_entered_max_score)<(101/360))
5095: ) THEN

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

5095: ) THEN
5096: FORALL I in 1..l_match_contact_list.COUNT
5097: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5098: values (l_search_ctx_id,l_match_contact_list(I));
5099: 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');
5100: ELSE
5101: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5102: END IF;
5103: LOOP

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

5097: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5098: values (l_search_ctx_id,l_match_contact_list(I));
5099: 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');
5100: ELSE
5101: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5102: END IF;
5103: LOOP
5104: FETCH l_contact_pt_cur INTO
5105: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;

Line 5199: l_contact_pt_contains_str VARCHAR2(32000);

5195: ) IS
5196:
5197:
5198: -- Strings to hold the generated Intermedia query strings
5199: l_contact_pt_contains_str VARCHAR2(32000);
5200: l_tmp VARCHAR2(32000);
5201:
5202: -- Other local variables
5203: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5256: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5257:
5258: -- ************************************************************
5259: -- STEP 2. Setup of intermedia query strings for Acquisition query
5260: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5261: 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);
5262:
5263: -- Setup Search Context ID
5264: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

5264: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5265: x_search_ctx_id := l_search_ctx_id;
5266: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5267: 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);
5268: 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);
5269: 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);
5270: END IF;
5271:
5272: IF l_contact_pt_contains_str IS NOT NULL THEN

Line 5272: IF l_contact_pt_contains_str IS NOT NULL THEN

5268: 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);
5269: 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);
5270: END IF;
5271:
5272: IF l_contact_pt_contains_str IS NOT NULL THEN
5273: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5274: cnt := 1;
5275: LOOP
5276: FETCH l_contact_pt_cur INTO

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

5269: 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);
5270: END IF;
5271:
5272: IF l_contact_pt_contains_str IS NOT NULL THEN
5273: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5274: cnt := 1;
5275: LOOP
5276: FETCH l_contact_pt_cur INTO
5277: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;

Line 5344: l_party_contains_str VARCHAR2(32000);

5340: x_search_ctx_id IN OUT NUMBER
5341: ) IS
5342:
5343: -- Strings to hold the generated Intermedia query strings
5344: l_party_contains_str VARCHAR2(32000);
5345: l_party_site_contains_str VARCHAR2(32000);
5346: l_contact_contains_str VARCHAR2(32000);
5347: l_contact_pt_contains_str VARCHAR2(32000);
5348: l_tmp VARCHAR2(32000);

Line 5345: l_party_site_contains_str VARCHAR2(32000);

5341: ) IS
5342:
5343: -- Strings to hold the generated Intermedia query strings
5344: l_party_contains_str VARCHAR2(32000);
5345: l_party_site_contains_str VARCHAR2(32000);
5346: l_contact_contains_str VARCHAR2(32000);
5347: l_contact_pt_contains_str VARCHAR2(32000);
5348: l_tmp VARCHAR2(32000);
5349:

Line 5346: l_contact_contains_str VARCHAR2(32000);

5342:
5343: -- Strings to hold the generated Intermedia query strings
5344: l_party_contains_str VARCHAR2(32000);
5345: l_party_site_contains_str VARCHAR2(32000);
5346: l_contact_contains_str VARCHAR2(32000);
5347: l_contact_pt_contains_str VARCHAR2(32000);
5348: l_tmp VARCHAR2(32000);
5349:
5350: -- Other local variables

Line 5347: l_contact_pt_contains_str VARCHAR2(32000);

5343: -- Strings to hold the generated Intermedia query strings
5344: l_party_contains_str VARCHAR2(32000);
5345: l_party_site_contains_str VARCHAR2(32000);
5346: l_contact_contains_str VARCHAR2(32000);
5347: l_contact_pt_contains_str VARCHAR2(32000);
5348: l_tmp VARCHAR2(32000);
5349:
5350: -- Other local variables
5351: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5418: g_score_until_thresh := false;
5419: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5420: -- ************************************************************
5421: -- STEP 2. Setup of intermedia query strings for Acquisition query
5422: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5423: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5424: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5425: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5426:

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

5419: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5420: -- ************************************************************
5421: -- STEP 2. Setup of intermedia query strings for Acquisition query
5422: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5423: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5424: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5425: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5426:
5427: -- Setup Search Context ID

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

5420: -- ************************************************************
5421: -- STEP 2. Setup of intermedia query strings for Acquisition query
5422: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5423: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5424: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5425: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5426:
5427: -- Setup Search Context ID
5428: IF x_search_ctx_id IS NULL THEN

Line 5444: IF l_party_site_contains_str IS NOT NULL THEN

5440: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX2,l_TX8,l_TX44,1);
5441: END LOOP;
5442: CLOSE l_party_cur;
5443:
5444: IF l_party_site_contains_str IS NOT NULL THEN
5445: l_max_score := 0;
5446: l_max_id := 0;
5447: l_max_idx := 0;
5448: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN

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

5445: l_max_score := 0;
5446: l_max_id := 0;
5447: l_max_idx := 0;
5448: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5449: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5450: ELSE
5451: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5452: END IF;
5453: LOOP

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

5447: l_max_idx := 0;
5448: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5449: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5450: ELSE
5451: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5452: END IF;
5453: LOOP
5454: FETCH l_party_site_cur INTO
5455: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX3, l_TX4, l_TX11;

Line 5473: IF l_contact_contains_str IS NOT NULL THEN

5469: INSERT_PARTY_SITES_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_party_site_list(l_max_idx), g_party_site_stage_list(l_max_idx) ,l_max_TX3,l_max_TX4,l_max_TX11,l_max_idx);
5470: END IF;
5471: END IF;
5472:
5473: IF l_contact_contains_str IS NOT NULL THEN
5474: l_max_score := 0;
5475: l_max_id := 0;
5476: l_max_idx := 0;
5477: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

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

5473: IF l_contact_contains_str IS NOT NULL THEN
5474: l_max_score := 0;
5475: l_max_id := 0;
5476: l_max_idx := 0;
5477: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5478: LOOP
5479: FETCH l_contact_cur INTO
5480: l_org_contact_id, l_ct_party_id ;
5481: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5495: IF l_contact_pt_contains_str IS NOT NULL THEN

5491: 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);
5492: END IF;
5493: END IF;
5494:
5495: IF l_contact_pt_contains_str IS NOT NULL THEN
5496: l_max_score := 0;
5497: l_max_id := 0;
5498: l_max_idx := 0;
5499: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN

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

5496: l_max_score := 0;
5497: l_max_id := 0;
5498: l_max_idx := 0;
5499: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5500: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5501: ELSE
5502: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5503: END IF;
5504: LOOP

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

5498: l_max_idx := 0;
5499: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5500: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5501: ELSE
5502: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5503: END IF;
5504: LOOP
5505: FETCH l_contact_pt_cur INTO
5506: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;