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_98 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 839: l_party_contains_str VARCHAR2(32000);

835: p_party_max_score NUMBER,
836: p_denorm_max_score NUMBER,
837: p_non_denorm_max_score NUMBER,
838: p_threshold NUMBER) RETURN VARCHAR2 IS
839: l_party_contains_str VARCHAR2(32000);
840: l_party_filter VARCHAR2(1) := null;
841: l_prim_temp VARCHAR2(4000);
842: l_denorm_str VARCHAR2(4000);
843: BEGIN

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

879:
880: IF l_denorm_str IS NOT NULL THEN
881: IF (p_non_denorm_max_score+40)>=p_threshold THEN
882: l_denorm_str := NULL;
883: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
884: ELSIF (p_non_denorm_max_score+p_denorm_max_score+40)>=p_threshold THEN
885: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
886: l_denorm_str := NULL;
887: END IF;

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

881: IF (p_non_denorm_max_score+40)>=p_threshold THEN
882: l_denorm_str := NULL;
883: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
884: ELSIF (p_non_denorm_max_score+p_denorm_max_score+40)>=p_threshold THEN
885: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
886: l_denorm_str := NULL;
887: END IF;
888: ELSE
889: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

885: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
886: l_denorm_str := NULL;
887: END IF;
888: ELSE
889: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
890: END IF;
891: IF lengthb(l_party_contains_str) > 4000 THEN
892: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
893: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');

Line 891: IF lengthb(l_party_contains_str) > 4000 THEN

887: END IF;
888: ELSE
889: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
890: END IF;
891: IF lengthb(l_party_contains_str) > 4000 THEN
892: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
893: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
894: FND_MSG_PUB.ADD;
895: RAISE FND_API.G_EXC_ERROR;

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

893: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
894: FND_MSG_PUB.ADD;
895: RAISE FND_API.G_EXC_ERROR;
896: END IF;
897: IF (p_match_str = ' AND ' OR p_party_max_score 898: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
899: 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);
900: END IF;
901: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

895: RAISE FND_API.G_EXC_ERROR;
896: END IF;
897: IF (p_match_str = ' AND ' OR p_party_max_score 898: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
899: 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);
900: END IF;
901: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
902: 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);
903: END IF;

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

898: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
899: 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);
900: END IF;
901: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
902: 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);
903: END IF;
904: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
905: 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);
906: END IF;

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

903: END IF;
904: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
905: 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);
906: END IF;
907: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
908: ELSE
909: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
910: 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);
911: 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 910: 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);

906: END IF;
907: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
908: ELSE
909: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
910: 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);
911: 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);
912: END IF;
913: RETURN l_party_contains_str;
914: END IF;

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

907: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
908: ELSE
909: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
910: 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);
911: 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);
912: END IF;
913: RETURN l_party_contains_str;
914: END IF;
915: END;

Line 913: RETURN l_party_contains_str;

909: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
910: 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);
911: 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);
912: END IF;
913: RETURN l_party_contains_str;
914: END IF;
915: END;
916: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
917: l_contains_str VARCHAR2(32000);

Line 917: l_contains_str VARCHAR2(32000);

913: RETURN l_party_contains_str;
914: END IF;
915: END;
916: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
917: l_contains_str VARCHAR2(32000);
918: l_contains_str_temp VARCHAR2(32000);
919: l_den_contains_str VARCHAR2(32000);
920: l_den_contains_str_temp VARCHAR2(32000);
921: l_filter_str VARCHAR2(4000) := null;

Line 918: l_contains_str_temp VARCHAR2(32000);

914: END IF;
915: END;
916: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
917: l_contains_str VARCHAR2(32000);
918: l_contains_str_temp VARCHAR2(32000);
919: l_den_contains_str VARCHAR2(32000);
920: l_den_contains_str_temp VARCHAR2(32000);
921: l_filter_str VARCHAR2(4000) := null;
922: l_prim_temp VARCHAR2(4000) := null;

Line 919: l_den_contains_str VARCHAR2(32000);

915: END;
916: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
917: l_contains_str VARCHAR2(32000);
918: l_contains_str_temp VARCHAR2(32000);
919: l_den_contains_str VARCHAR2(32000);
920: l_den_contains_str_temp VARCHAR2(32000);
921: l_filter_str VARCHAR2(4000) := null;
922: l_prim_temp VARCHAR2(4000) := null;
923: l_prim_temp_den VARCHAR2(4000) := null;

Line 920: l_den_contains_str_temp VARCHAR2(32000);

916: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
917: l_contains_str VARCHAR2(32000);
918: l_contains_str_temp VARCHAR2(32000);
919: l_den_contains_str VARCHAR2(32000);
920: l_den_contains_str_temp VARCHAR2(32000);
921: l_filter_str VARCHAR2(4000) := null;
922: l_prim_temp VARCHAR2(4000) := null;
923: l_prim_temp_den VARCHAR2(4000) := null;
924: BEGIN

Line 933: l_contains_str VARCHAR2(32000);

929: RETURN NULL;
930: END;
931:
932: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
933: l_contains_str VARCHAR2(32000);
934: l_contains_str_temp VARCHAR2(32000);
935: l_den_contains_str VARCHAR2(32000);
936: l_den_contains_str_temp VARCHAR2(32000);
937: l_filter_str VARCHAR2(4000) := null;

Line 934: l_contains_str_temp VARCHAR2(32000);

930: END;
931:
932: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
933: l_contains_str VARCHAR2(32000);
934: l_contains_str_temp VARCHAR2(32000);
935: l_den_contains_str VARCHAR2(32000);
936: l_den_contains_str_temp VARCHAR2(32000);
937: l_filter_str VARCHAR2(4000) := null;
938: l_prim_temp VARCHAR2(4000) := null;

Line 935: l_den_contains_str VARCHAR2(32000);

931:
932: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
933: l_contains_str VARCHAR2(32000);
934: l_contains_str_temp VARCHAR2(32000);
935: l_den_contains_str VARCHAR2(32000);
936: l_den_contains_str_temp VARCHAR2(32000);
937: l_filter_str VARCHAR2(4000) := null;
938: l_prim_temp VARCHAR2(4000) := null;
939: l_prim_temp_den VARCHAR2(4000) := null;

Line 936: l_den_contains_str_temp VARCHAR2(32000);

932: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
933: l_contains_str VARCHAR2(32000);
934: l_contains_str_temp VARCHAR2(32000);
935: l_den_contains_str VARCHAR2(32000);
936: l_den_contains_str_temp VARCHAR2(32000);
937: l_filter_str VARCHAR2(4000) := null;
938: l_prim_temp VARCHAR2(4000) := null;
939: l_prim_temp_den VARCHAR2(4000) := null;
940: BEGIN

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

942: hz_utility_v2pub.debug(p_message=>'INIT_CONTACTS_QUERY ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
943: END IF;
944: x_denorm_str := NULL;
945:
946: -- Dynamic setup of party site contains str
947: --
948: -- For each primary transformation add to intermedia query if it
949: -- is not null
950: FOR I IN 1..g_contact_stage_list.COUNT LOOP

Line 951: l_contains_str_temp := null;

947: --
948: -- For each primary transformation add to intermedia query if it
949: -- is not null
950: FOR I IN 1..g_contact_stage_list.COUNT LOOP
951: l_contains_str_temp := null;
952: l_den_contains_str_temp := null;
953: l_filter_str := null;
954:
955: -- Setup query string for JOB_TITLE

Line 952: l_den_contains_str_temp := null;

948: -- For each primary transformation add to intermedia query if it
949: -- is not null
950: FOR I IN 1..g_contact_stage_list.COUNT LOOP
951: l_contains_str_temp := null;
952: l_den_contains_str_temp := null;
953: l_filter_str := null;
954:
955: -- Setup query string for JOB_TITLE
956: l_prim_temp := null;

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

958: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
959: g_contact_stage_list(I).TX22,'A43',l_prim_temp);
960: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
961: g_contact_stage_list(I).TX22,'D_CT',l_prim_temp_den);
962: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
963: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
964:
965: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
966: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);

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

959: g_contact_stage_list(I).TX22,'A43',l_prim_temp);
960: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
961: g_contact_stage_list(I).TX22,'D_CT',l_prim_temp_den);
962: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
963: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
964:
965: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
966: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
967: END LOOP;

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

961: g_contact_stage_list(I).TX22,'D_CT',l_prim_temp_den);
962: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
963: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
964:
965: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
966: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
967: END LOOP;
968: -- Add the search criteria to query string
969: IF lengthb(l_contains_str) > 4000 THEN

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

962: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
963: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
964:
965: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
966: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
967: END LOOP;
968: -- Add the search criteria to query string
969: IF lengthb(l_contains_str) > 4000 THEN
970: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');

Line 969: IF lengthb(l_contains_str) > 4000 THEN

965: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
966: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
967: END LOOP;
968: -- Add the search criteria to query string
969: IF lengthb(l_contains_str) > 4000 THEN
970: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
971: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACTS');
972: FND_MSG_PUB.ADD;
973: RAISE FND_API.G_EXC_ERROR;

Line 975: x_denorm_str := l_den_contains_str;

971: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACTS');
972: FND_MSG_PUB.ADD;
973: RAISE FND_API.G_EXC_ERROR;
974: END IF;
975: x_denorm_str := l_den_contains_str;
976: RETURN l_contains_str;
977: END;
978:
979: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS

Line 976: RETURN l_contains_str;

972: FND_MSG_PUB.ADD;
973: RAISE FND_API.G_EXC_ERROR;
974: END IF;
975: x_denorm_str := l_den_contains_str;
976: RETURN l_contains_str;
977: END;
978:
979: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
980: l_contains_str VARCHAR2(32000);

Line 980: l_contains_str VARCHAR2(32000);

976: RETURN l_contains_str;
977: END;
978:
979: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
980: l_contains_str VARCHAR2(32000);
981: l_contains_str_temp VARCHAR2(32000);
982: l_den_contains_str VARCHAR2(32000);
983: l_den_contains_str_temp VARCHAR2(32000);
984: l_filter_str VARCHAR2(4000) := null;

Line 981: l_contains_str_temp VARCHAR2(32000);

977: END;
978:
979: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
980: l_contains_str VARCHAR2(32000);
981: l_contains_str_temp VARCHAR2(32000);
982: l_den_contains_str VARCHAR2(32000);
983: l_den_contains_str_temp VARCHAR2(32000);
984: l_filter_str VARCHAR2(4000) := null;
985: l_prim_temp VARCHAR2(4000) := null;

Line 982: l_den_contains_str VARCHAR2(32000);

978:
979: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
980: l_contains_str VARCHAR2(32000);
981: l_contains_str_temp VARCHAR2(32000);
982: l_den_contains_str VARCHAR2(32000);
983: l_den_contains_str_temp VARCHAR2(32000);
984: l_filter_str VARCHAR2(4000) := null;
985: l_prim_temp VARCHAR2(4000) := null;
986: l_prim_temp_den VARCHAR2(4000) := null;

Line 983: l_den_contains_str_temp VARCHAR2(32000);

979: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
980: l_contains_str VARCHAR2(32000);
981: l_contains_str_temp VARCHAR2(32000);
982: l_den_contains_str VARCHAR2(32000);
983: l_den_contains_str_temp VARCHAR2(32000);
984: l_filter_str VARCHAR2(4000) := null;
985: l_prim_temp VARCHAR2(4000) := null;
986: l_prim_temp_den VARCHAR2(4000) := null;
987: TYPE CONTACT_PT_REC_TYPE IS RECORD (

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

995: 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);
996: END IF;
997: x_denorm_str := NULL;
998:
999: -- Dynamic setup of party site contains str
1000: --
1001: -- For each primary transformation add to intermedia query if it
1002: -- is not null
1003: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP

Line 1004: l_contains_str_temp := null;

1000: --
1001: -- For each primary transformation add to intermedia query if it
1002: -- is not null
1003: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1004: l_contains_str_temp := null;
1005: l_den_contains_str_temp := null;
1006: l_filter_str := null;
1007: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1008: if(l_cnt_pt_type.count>0) then

Line 1005: l_den_contains_str_temp := null;

1001: -- For each primary transformation add to intermedia query if it
1002: -- is not null
1003: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1004: l_contains_str_temp := null;
1005: l_den_contains_str_temp := null;
1006: l_filter_str := null;
1007: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1008: if(l_cnt_pt_type.count>0) then
1009: x_modify := 'Y';

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

1025: l_prim_temp := null;
1026: l_prim_temp_den := null;
1027: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL CTX
1028: g_contact_pt_stage_list(I).TX1,'A44',l_prim_temp);
1029: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1030:
1031: -- Setup query string for EMAIL_ADDRESS
1032: l_prim_temp := null;
1033: l_prim_temp_den := null;

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

1032: l_prim_temp := null;
1033: l_prim_temp_den := null;
1034: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (EMAIL) + WRDOMAIN
1035: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1036: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1037:
1038: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1039: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1040: IF N>1 THEN

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

1034: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (EMAIL) + WRDOMAIN
1035: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1036: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1037:
1038: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1039: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1040: IF N>1 THEN
1041: distinct_search_cpt_types := N-1;
1042: ELSE

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

1035: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1036: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1037:
1038: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1039: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1040: IF N>1 THEN
1041: distinct_search_cpt_types := N-1;
1042: ELSE
1043: distinct_search_cpt_types := N;

Line 1050: IF lengthb(l_contains_str) > 4000 THEN

1046: 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);
1047: END IF;
1048: END LOOP;
1049: -- Add the search criteria to query string
1050: IF lengthb(l_contains_str) > 4000 THEN
1051: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1052: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1053: FND_MSG_PUB.ADD;
1054: RAISE FND_API.G_EXC_ERROR;

Line 1056: x_denorm_str := l_den_contains_str;

1052: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1053: FND_MSG_PUB.ADD;
1054: RAISE FND_API.G_EXC_ERROR;
1055: END IF;
1056: x_denorm_str := l_den_contains_str;
1057: RETURN l_contains_str;
1058: END;
1059:
1060: PROCEDURE open_party_cursor_direct (

Line 1057: RETURN l_contains_str;

1053: FND_MSG_PUB.ADD;
1054: RAISE FND_API.G_EXC_ERROR;
1055: END IF;
1056: x_denorm_str := l_den_contains_str;
1057: RETURN l_contains_str;
1058: END;
1059:
1060: PROCEDURE open_party_cursor_direct (
1061: p_dup_party_id NUMBER,

Line 1065: p_party_contains_str VARCHAR2,

1061: p_dup_party_id NUMBER,
1062: p_restrict_sql VARCHAR2,
1063: p_match_str VARCHAR2,
1064: p_search_merged VARCHAR2,
1065: p_party_contains_str VARCHAR2,
1066: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1067: l_sqlstr VARCHAR2(4000);
1068: l_search_merged VARCHAR2(1);
1069: BEGIN

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

1078: END IF;
1079: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1080: 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);
1081: END IF;
1082: 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
1083: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1084: 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);
1085: END IF;
1086: OPEN x_cursor FOR

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

1110: l_sqlstr := l_sqlstr || ' (TX40 like :TX40||'' %'' ';
1111: l_sqlstr := l_sqlstr || ' )) ';
1112: l_sqlstr := l_sqlstr || ' ) ';
1113: END IF;
1114: 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
1115: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1116: 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);
1117: END IF;
1118: null;

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

1112: l_sqlstr := l_sqlstr || ' ) ';
1113: END IF;
1114: 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
1115: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1116: 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);
1117: END IF;
1118: null;
1119: END IF;
1120: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

1131: END IF;
1132: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1133: 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);
1134: END IF;
1135: 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
1136: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1137: 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);
1138: END IF;
1139: 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 1137: 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);

1133: 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);
1134: END IF;
1135: 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
1136: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1137: 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);
1138: END IF;
1139: 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
1140: ,g_party_stage_rec.TX40
1141: ,g_party_stage_rec.TX40

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

1147: 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
1148: ,g_party_stage_rec.TX40
1149: ,g_party_stage_rec.TX40
1150: ;
1151: 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
1152: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1153: 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);
1154: END IF;
1155: 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 1153: 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);

1149: ,g_party_stage_rec.TX40
1150: ;
1151: 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
1152: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1153: 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);
1154: END IF;
1155: 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
1156: ;
1157: ELSE

Line 1168: p_contains_str VARCHAR2,

1164: END;
1165: PROCEDURE open_party_cursor(
1166: p_dup_party_id NUMBER,
1167: p_restrict_sql VARCHAR2,
1168: p_contains_str VARCHAR2,
1169: p_search_ctx_id NUMBER,
1170: p_match_str VARCHAR2,
1171: p_search_merged VARCHAR2,
1172: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS

Line 1187: IF p_contains_str IS NULL THEN

1183: l_search_merged := 'N';
1184: ELSE
1185: l_search_merged := p_search_merged;
1186: END IF;
1187: IF p_contains_str IS NULL THEN
1188: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1189: 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);
1190: END IF;
1191: OPEN x_cursor FOR

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

1185: l_search_merged := p_search_merged;
1186: END IF;
1187: IF p_contains_str IS NULL THEN
1188: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1189: 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);
1190: END IF;
1191: OPEN x_cursor FOR
1192: SELECT PARTY_ID , TX40, TX39
1193: FROM HZ_STAGED_PARTIES stage

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

1202: END IF;
1203: OPEN x_cursor FOR
1204: SELECT PARTY_ID , TX40, TX39
1205: FROM HZ_STAGED_PARTIES stage
1206: WHERE contains( concat_col, p_contains_str)>0
1207: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1208: AND( (l_search_merged ='Y' )
1209: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1210: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )

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

1215: END IF;
1216: OPEN x_cursor FOR
1217: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX40, TX39
1218: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1219: WHERE contains( concat_col, p_contains_str)>0
1220: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1221: AND d.party_id = stage.party_id
1222: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1223: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)

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

1238: END IF;
1239: IF p_search_ctx_id IS NULL THEN
1240: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX40, TX39'||
1241: ' FROM HZ_STAGED_PARTIES stage'||
1242: ' WHERE contains( concat_col, :cont)>0'||
1243: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1244: ' AND ('||p_restrict_sql||')' ||
1245: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1246: IF l_search_merged = 'Y' THEN

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

1250: ELSE
1251: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1252: END IF;
1253: output_long_strings('----------------------------------------------------------');
1254: output_long_strings('Party Contains String = '||p_contains_str);
1255: output_long_strings('Restrict Sql = '||p_restrict_sql);
1256: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1257: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1258: ,p_dup_party_id, p_dup_party_id;

Line 1256: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1252: END IF;
1253: output_long_strings('----------------------------------------------------------');
1254: output_long_strings('Party Contains String = '||p_contains_str);
1255: output_long_strings('Restrict Sql = '||p_restrict_sql);
1256: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1257: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1258: ,p_dup_party_id, p_dup_party_id;
1259: END IF;
1260: END IF;

Line 1287: p_contains_str VARCHAR2,

1283: PROCEDURE open_party_site_cursor(
1284: p_dup_party_id NUMBER,
1285: p_party_id NUMBER,
1286: p_restrict_sql VARCHAR2,
1287: p_contains_str VARCHAR2,
1288: p_search_ctx_id NUMBER,
1289: p_search_merged VARCHAR2,
1290: p_search_rel_sites VARCHAR2,
1291: p_person_api VARCHAR2,

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

1315: IF p_search_rel_sites = 'N' THEN
1316: OPEN x_cursor FOR
1317: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID
1318: FROM HZ_STAGED_PARTY_SITES stage
1319: WHERE contains( concat_col, p_contains_str)>0
1320: AND( (l_search_merged ='Y' )
1321: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1322: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1323: AND stage.party_id = p_party_id;

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

1324: ELSE
1325: OPEN x_cursor FOR
1326: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID
1327: FROM HZ_STAGED_PARTY_SITES stage
1328: WHERE contains( concat_col, p_contains_str)>0
1329: AND( (l_search_merged ='Y' )
1330: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1331: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1332: AND stage.party_id = p_party_id

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

1332: AND stage.party_id = p_party_id
1333: UNION
1334: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID
1335: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1336: WHERE contains( concat_col, p_contains_str)>0
1337: AND( (l_search_merged ='Y' )
1338: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1339: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1340: AND r.object_id = p_party_id

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

1354: END IF;
1355: OPEN x_cursor FOR
1356: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID
1357: FROM HZ_STAGED_PARTY_SITES stage
1358: WHERE contains( concat_col, p_contains_str)>0
1359: AND EXISTS (
1360: SELECT 1 FROM HZ_STAGED_PARTIES p
1361: WHERE p.PARTY_ID = stage.PARTY_ID
1362: AND( (l_search_merged ='Y' )

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

1374: IF p_person_api = 'Y' THEN
1375: OPEN x_cursor FOR
1376: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID
1377: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1378: WHERE contains( concat_col, p_contains_str)>0
1379: AND( (l_search_merged ='Y' )
1380: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1381: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1382: AND d.search_context_id = p_search_ctx_id

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

1384: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1385: UNION
1386: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID
1387: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1388: WHERE contains( concat_col, p_contains_str)>0
1389: AND( (l_search_merged ='Y' )
1390: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1391: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1392: AND d.search_context_id = p_search_ctx_id

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

1397: ELSE
1398: OPEN x_cursor FOR
1399: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID
1400: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1401: WHERE contains( concat_col, p_contains_str)>0
1402: AND( (l_search_merged ='Y' )
1403: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1404: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1405: AND d.search_context_id = p_search_ctx_id

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

1442: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1443: END IF;
1444: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID '||
1445: ' FROM HZ_STAGED_PARTY_SITES stage'||
1446: ' WHERE contains( concat_col, :cont)>0'||
1447: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1448: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1449: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1450: ' AND (ORG_CONTACT_ID IS NULL ' ||

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

1452: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1453: ' UNION ' ||
1454: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID '||
1455: ' FROM HZ_STAGED_PARTY_SITES stage'||
1456: ' WHERE contains( concat_col, :cont)>0'||
1457: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1458: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1459: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1460: ' AND ORG_CONTACT_ID IN ' ||

Line 1466: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1462: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1463: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1464: ' and ('||p_restrict_sql1|| ') )' ||
1465: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1466: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1467: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1468: ELSE
1469: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID '||
1470: ' FROM HZ_STAGED_PARTY_SITES stage'||

Line 1467: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1463: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1464: ' and ('||p_restrict_sql1|| ') )' ||
1465: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1466: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1467: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1468: ELSE
1469: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID '||
1470: ' FROM HZ_STAGED_PARTY_SITES stage'||
1471: ' WHERE contains( concat_col, :cont)>0'||

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

1467: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1468: ELSE
1469: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID '||
1470: ' FROM HZ_STAGED_PARTY_SITES stage'||
1471: ' WHERE contains( concat_col, :cont)>0'||
1472: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1473: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1474: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1475: ' AND EXISTS ('||

Line 1481: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1477: ' WHERE p.party_id = stage.party_id ' ||
1478: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1479: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1480: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1481: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1482: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1483: ,p_dup_party_id, p_dup_party_id;
1484: END IF;
1485: ELSE

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

1484: END IF;
1485: ELSE
1486: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID '||
1487: ' FROM HZ_STAGED_PARTY_SITES stage'||
1488: ' WHERE contains( concat_col, :cont)>0'||
1489: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1490: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1491: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1492: ' AND EXISTS ('||

Line 1498: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1494: ' WHERE p.party_id = stage.party_id ' ||
1495: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1496: ' AND ('||p_restrict_sql||')' ||
1497: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1498: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1499: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1500: ,p_dup_party_id, p_dup_party_id;
1501: END IF;
1502: END IF;

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

1500: ,p_dup_party_id, p_dup_party_id;
1501: END IF;
1502: END IF;
1503: output_long_strings('----------------------------------------------------------');
1504: output_long_strings('Party Site Contains String = '||p_contains_str);
1505: output_long_strings('Restrict Sql = '||p_restrict_sql);
1506: exception
1507: when others then
1508: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1532: p_contains_str VARCHAR2,

1528: PROCEDURE open_contact_cursor(
1529: p_dup_party_id NUMBER,
1530: p_party_id NUMBER,
1531: p_restrict_sql VARCHAR2,
1532: p_contains_str VARCHAR2,
1533: p_search_ctx_id NUMBER,
1534: p_search_merged VARCHAR2,
1535: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1536: l_sqlstr VARCHAR2(4000);

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

1556: END IF;
1557: OPEN x_cursor FOR
1558: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID, TX22
1559: FROM HZ_STAGED_CONTACTS stage
1560: WHERE contains( concat_col, p_contains_str)>0
1561: AND EXISTS (
1562: SELECT 1 FROM HZ_STAGED_PARTIES p
1563: WHERE p.PARTY_ID = stage.PARTY_ID
1564: AND( (l_search_merged ='Y' )

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

1579: END IF;
1580: OPEN x_cursor FOR
1581: SELECT ORG_CONTACT_ID, PARTY_ID, TX22
1582: FROM HZ_STAGED_CONTACTS stage
1583: WHERE contains( concat_col, p_contains_str)>0
1584: AND EXISTS (
1585: SELECT 1 FROM HZ_STAGED_PARTIES p
1586: WHERE p.PARTY_ID = stage.PARTY_ID
1587: AND( (l_search_merged ='Y' )

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

1598: END IF;
1599: OPEN x_cursor FOR
1600: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID, TX22
1601: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1602: WHERE contains( concat_col, p_contains_str)>0
1603: AND d.search_context_id = p_search_ctx_id
1604: AND d.party_id = stage.party_id
1605: AND( (l_search_merged ='Y' )
1606: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1632: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
1633: END IF;
1634: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID , TX22'||
1635: ' FROM HZ_STAGED_CONTACTS stage'||
1636: ' WHERE contains( concat_col, :cont)>0'||
1637: ' AND EXISTS ('||
1638: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1639: ' WHERE p.party_id = stage.party_id ' ||
1640: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1646: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1642: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1643: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1644: ' AND ('||p_restrict_sql||')' ||
1645: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1646: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1647: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1648: ,p_dup_party_id, p_dup_party_id;
1649: END IF;
1650: output_long_strings('----------------------------------------------------------');

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

1647: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1648: ,p_dup_party_id, p_dup_party_id;
1649: END IF;
1650: output_long_strings('----------------------------------------------------------');
1651: output_long_strings('Contacts Contains String = '||p_contains_str);
1652: output_long_strings('Restrict Sql = '||p_restrict_sql);
1653: exception
1654: when others then
1655: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1679: p_contains_str VARCHAR2,

1675: PROCEDURE open_contact_pt_cursor(
1676: p_dup_party_id NUMBER,
1677: p_party_id NUMBER,
1678: p_restrict_sql VARCHAR2,
1679: p_contains_str VARCHAR2,
1680: p_search_ctx_id NUMBER,
1681: p_search_merged VARCHAR2,
1682: p_search_rel_cpts VARCHAR2,
1683: p_person_api VARCHAR2,

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

1705: THEN
1706: OPEN x_cursor FOR
1707: 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
1708: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1709: WHERE contains( concat_col, p_contains_str)>0
1710: AND d.search_context_id = p_search_ctx_id
1711: AND( (l_search_merged ='Y' )
1712: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1713: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1717: THEN
1718: OPEN x_cursor FOR
1719: 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
1720: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1721: WHERE contains( concat_col, p_contains_str)>0
1722: AND d.search_context_id = p_search_ctx_id
1723: AND( (l_search_merged ='Y' )
1724: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1725: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1734: IF p_search_rel_cpts = 'N' THEN
1735: OPEN x_cursor FOR
1736: 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
1737: FROM HZ_STAGED_CONTACT_POINTS stage
1738: WHERE contains( concat_col, p_contains_str)>0
1739: AND( (l_search_merged ='Y' )
1740: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1741: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1742: AND stage.party_id = p_party_id;

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

1743: ELSE
1744: OPEN x_cursor FOR
1745: 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
1746: FROM HZ_STAGED_CONTACT_POINTS stage
1747: WHERE contains( concat_col, p_contains_str)>0
1748: AND( (l_search_merged ='Y' )
1749: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1750: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1751: AND stage.party_id = p_party_id

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

1751: AND stage.party_id = p_party_id
1752: UNION
1753: 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
1754: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1755: WHERE contains( concat_col, p_contains_str)>0
1756: AND r.object_id = p_party_id
1757: AND( (l_search_merged ='Y' )
1758: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1759: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1773: END IF;
1774: OPEN x_cursor FOR
1775: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1776: FROM HZ_STAGED_CONTACT_POINTS stage
1777: WHERE contains( concat_col, p_contains_str)>0
1778: AND EXISTS (
1779: SELECT 1 FROM HZ_STAGED_PARTIES p
1780: WHERE p.PARTY_ID = stage.PARTY_ID
1781: AND( (l_search_merged ='Y' )

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

1793: IF p_person_api = 'Y' THEN
1794: OPEN x_cursor FOR
1795: 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
1796: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1797: WHERE contains( concat_col, p_contains_str)>0
1798: AND d.search_context_id = p_search_ctx_id
1799: AND d.party_id = stage.party_id
1800: AND( (l_search_merged ='Y' )
1801: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1803: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1804: UNION
1805: 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
1806: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1807: WHERE contains( concat_col, p_contains_str)>0
1808: AND d.search_context_id = p_search_ctx_id
1809: AND d.party_id = r.subject_id
1810: AND r.relationship_id = oc.party_relationship_id
1811: AND oc.org_contact_id = stage.org_contact_id

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

1816: ELSE
1817: OPEN x_cursor FOR
1818: 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
1819: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1820: WHERE contains( concat_col, p_contains_str)>0
1821: AND d.search_context_id = p_search_ctx_id
1822: AND d.party_id = stage.party_id
1823: AND( (l_search_merged ='Y' )
1824: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1861: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1862: END IF;
1863: 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'||
1864: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1865: ' WHERE contains( concat_col, :cont)>0 '||
1866: ' AND (stage.org_contact_id is null '||
1867: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1868: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1869: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

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

1871: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1872: ' UNION ' ||
1873: ' 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'||
1874: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1875: ' WHERE contains( concat_col, :cont)>0 '||
1876: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1877: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1878: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1879: ' AND (stage.org_contact_id in ' ||

Line 1885: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1881: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1882: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1883: ' and ('||p_restrict_sql1 || ') ) )' ||
1884: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1885: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1886: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1887: ELSE
1888: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
1889: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

Line 1886: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1882: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1883: ' and ('||p_restrict_sql1 || ') ) )' ||
1884: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1885: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1886: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1887: ELSE
1888: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
1889: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1890: ' WHERE contains( concat_col, :cont)>0'||

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

1886: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1887: ELSE
1888: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
1889: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1890: ' WHERE contains( concat_col, :cont)>0'||
1891: ' AND EXISTS ('||
1892: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1893: ' WHERE p.party_id = stage.party_id ' ||
1894: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1900: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1896: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1897: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1898: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1899: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1900: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1901: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1902: ,p_dup_party_id, p_dup_party_id;
1903: END IF;
1904: ELSE

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

1903: END IF;
1904: ELSE
1905: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
1906: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1907: ' WHERE contains( concat_col, :cont)>0'||
1908: ' AND EXISTS ('||
1909: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1910: ' WHERE p.party_id = stage.party_id ' ||
1911: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1917: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1913: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1914: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1915: ' AND ('||p_restrict_sql||')' ||
1916: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1917: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1918: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1919: ,p_dup_party_id, p_dup_party_id;
1920: END IF;
1921: END IF;

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

1920: END IF;
1921: END IF;
1922: END IF;
1923: output_long_strings('----------------------------------------------------------');
1924: output_long_strings('Contact Points Contains String = '||p_contains_str);
1925: output_long_strings('Restrict Sql = '||p_restrict_sql);
1926: exception
1927: when others then
1928: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 2045: l_party_contains_str VARCHAR2(32000);

2041: x_num_matches OUT NUMBER
2042: ) IS
2043:
2044: -- Strings to hold the generated Intermedia query strings
2045: l_party_contains_str VARCHAR2(32000);
2046: l_party_site_contains_str VARCHAR2(32000);
2047: l_contact_contains_str VARCHAR2(32000);
2048: l_contact_pt_contains_str VARCHAR2(32000);
2049: l_denorm_str VARCHAR2(32000);

Line 2046: l_party_site_contains_str VARCHAR2(32000);

2042: ) IS
2043:
2044: -- Strings to hold the generated Intermedia query strings
2045: l_party_contains_str VARCHAR2(32000);
2046: l_party_site_contains_str VARCHAR2(32000);
2047: l_contact_contains_str VARCHAR2(32000);
2048: l_contact_pt_contains_str VARCHAR2(32000);
2049: l_denorm_str VARCHAR2(32000);
2050: l_ps_denorm_str VARCHAR2(32000);

Line 2047: l_contact_contains_str VARCHAR2(32000);

2043:
2044: -- Strings to hold the generated Intermedia query strings
2045: l_party_contains_str VARCHAR2(32000);
2046: l_party_site_contains_str VARCHAR2(32000);
2047: l_contact_contains_str VARCHAR2(32000);
2048: l_contact_pt_contains_str VARCHAR2(32000);
2049: l_denorm_str VARCHAR2(32000);
2050: l_ps_denorm_str VARCHAR2(32000);
2051: l_ct_denorm_str VARCHAR2(32000);

Line 2048: l_contact_pt_contains_str VARCHAR2(32000);

2044: -- Strings to hold the generated Intermedia query strings
2045: l_party_contains_str VARCHAR2(32000);
2046: l_party_site_contains_str VARCHAR2(32000);
2047: l_contact_contains_str VARCHAR2(32000);
2048: l_contact_pt_contains_str VARCHAR2(32000);
2049: l_denorm_str VARCHAR2(32000);
2050: l_ps_denorm_str VARCHAR2(32000);
2051: l_ct_denorm_str VARCHAR2(32000);
2052: l_cpt_denorm_str VARCHAR2(32000);

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

2135: call_type(2) := 'OR';
2136: END;
2137:
2138: /** Private procedure to acquire and score at party level ***/
2139: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2140: l_party_id_idx NUMBER:=1;
2141: l_ctx_id NUMBER;
2142: l_precalc_score BOOLEAN := FALSE;
2143: BEGIN

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

2166: l_search_ctx_id,H_PARTY_ID(I));
2167: l_party_id_idx := H_PARTY_ID.COUNT+1;
2168: ELSE
2169: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2170: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2171: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2172: 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);
2173: END IF;
2174: 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 2178: IF (l_party_site_contains_str IS NULL AND

2174: 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
2175: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2176: 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);
2177: END IF;
2178: IF (l_party_site_contains_str IS NULL AND
2179: l_contact_contains_str IS NULL AND
2180: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2181: 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);
2182: RAISE L_RETURN_IMM_EXC;

Line 2179: l_contact_contains_str IS NULL AND

2175: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2176: 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);
2177: END IF;
2178: IF (l_party_site_contains_str IS NULL AND
2179: l_contact_contains_str IS NULL AND
2180: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2181: 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);
2182: RAISE L_RETURN_IMM_EXC;
2183: ELSE

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

2176: 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);
2177: END IF;
2178: IF (l_party_site_contains_str IS NULL AND
2179: l_contact_contains_str IS NULL AND
2180: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2181: 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);
2182: RAISE L_RETURN_IMM_EXC;
2183: ELSE
2184: 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 2184: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);

2180: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2181: 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);
2182: RAISE L_RETURN_IMM_EXC;
2183: ELSE
2184: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2185: END IF;
2186: ELSE
2187: IF p_index>1 THEN
2188: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

2203: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2204: 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);
2205: END IF;
2206: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2207: 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);
2208: END IF;
2209: LOOP
2210: FETCH l_party_cur INTO
2211: l_party_id , l_TX40, l_TX39;

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

2225: l_party_id_idx:= l_party_id_idx+1;
2226: END IF;
2227: IF (l_party_id_idx-1)>l_max_thresh THEN
2228: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2229: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2230: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2231: AND l_contact_pt_contains_str IS NULL THEN
2232: EXIT;
2233: END IF;

Line 2231: AND l_contact_pt_contains_str IS NULL THEN

2227: IF (l_party_id_idx-1)>l_max_thresh THEN
2228: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2229: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2230: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2231: AND l_contact_pt_contains_str IS NULL THEN
2232: EXIT;
2233: END IF;
2234: CLOSE l_party_cur;
2235: IF p_index>1 THEN

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

2256: ROLLBACK to eval_start;
2257: END;
2258:
2259: /** Private procedure to acquire and score at party site level ***/
2260: 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
2261: l_party_id_idx NUMBER:=1;
2262: l_ctx_id NUMBER;
2263: h_ps_id HZ_PARTY_SEARCH.IDList;
2264: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

2268: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2269: 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);
2270: 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);
2271: END IF;
2272: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2273: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2274: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2275: l_party_id := H_SCORES.FIRST;
2276: WHILE l_party_id IS NOT NULL LOOP

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

2269: 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);
2270: 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);
2271: END IF;
2272: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2273: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2274: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2275: l_party_id := H_SCORES.FIRST;
2276: WHILE l_party_id IS NOT NULL LOOP
2277: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;

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

2270: 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);
2271: END IF;
2272: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2273: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2274: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2275: l_party_id := H_SCORES.FIRST;
2276: WHILE l_party_id IS NOT NULL LOOP
2277: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2278: l_party_id:=H_SCORES.NEXT(l_party_id);

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

2300: l_search_ctx_id,H_PARTY_ID(I));
2301: l_party_id_idx := H_PARTY_ID.COUNT+1;
2302: ELSE
2303: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2304: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2305: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2306: 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);
2307: END IF;
2308: IF p_index>1 THEN

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

2322: END IF;
2323: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2324: 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);
2325: END IF;
2326: 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);
2327: LOOP
2328: FETCH l_party_site_cur INTO
2329: l_party_site_id, l_ps_party_id, l_ps_contact_id ;
2330: EXIT WHEN l_party_site_cur%NOTFOUND;

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

2378: END IF;
2379: END;
2380:
2381: /** Private procedure to acquire and score at contact point level ***/
2382: 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
2383: l_party_id_idx NUMBER:=1;
2384: l_ctx_id NUMBER;
2385: h_ct_id HZ_PARTY_SEARCH.IDList;
2386: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

2412: l_search_ctx_id,H_PARTY_ID(I));
2413: l_party_id_idx := H_PARTY_ID.COUNT+1;
2414: ELSE
2415: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2416: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
2417: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2418: 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);
2419: END IF;
2420: IF p_index>1 THEN

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

2434: END IF;
2435: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2436: 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);
2437: END IF;
2438: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2439: LOOP
2440: FETCH l_contact_cur INTO
2441: l_org_contact_id, l_ct_party_id , l_TX22;
2442: EXIT WHEN l_contact_cur%NOTFOUND;

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

2487: END IF;
2488: END;
2489:
2490: /** Private procedure to acquire and score at contact point level ***/
2491: 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
2492: l_party_id_idx NUMBER:=1;
2493: l_ctx_id NUMBER;
2494: h_cpt_id HZ_PARTY_SEARCH.IDList;
2495: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

2523: l_search_ctx_id,H_PARTY_ID(I));
2524: l_party_id_idx := H_PARTY_ID.COUNT+1;
2525: ELSE
2526: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2527: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
2528: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2529: 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);
2530: END IF;
2531: IF p_index>1 THEN

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

2545: END IF;
2546: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2547: 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);
2548: END IF;
2549: 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);
2550: LOOP
2551: FETCH l_contact_pt_cur INTO
2552: 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;
2553: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 2730: IF l_party_contains_str IS NOT NULL THEN

2726: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
2727: l_threshold := round(( l_entered_max_score / 180) * 144);
2728: other_acq_criteria_exists := TRUE ;
2729: --check if acquisition criteria exists for any other entity
2730: IF l_party_contains_str IS NOT NULL THEN
2731: acq_cnt := 1;
2732: END IF;
2733: IF l_party_site_contains_str IS NOT NULL THEN
2734: acq_cnt := acq_cnt+1;

Line 2733: IF l_party_site_contains_str IS NOT NULL THEN

2729: --check if acquisition criteria exists for any other entity
2730: IF l_party_contains_str IS NOT NULL THEN
2731: acq_cnt := 1;
2732: END IF;
2733: IF l_party_site_contains_str IS NOT NULL THEN
2734: acq_cnt := acq_cnt+1;
2735: END IF;
2736: IF l_contact_contains_str IS NOT NULL THEN
2737: acq_cnt := acq_cnt+1;

Line 2736: IF l_contact_contains_str IS NOT NULL THEN

2732: END IF;
2733: IF l_party_site_contains_str IS NOT NULL THEN
2734: acq_cnt := acq_cnt+1;
2735: END IF;
2736: IF l_contact_contains_str IS NOT NULL THEN
2737: acq_cnt := acq_cnt+1;
2738: END IF;
2739: IF l_contact_pt_contains_str IS NOT NULL THEN
2740: acq_cnt := acq_cnt+1;

Line 2739: IF l_contact_pt_contains_str IS NOT NULL THEN

2735: END IF;
2736: IF l_contact_contains_str IS NOT NULL THEN
2737: acq_cnt := acq_cnt+1;
2738: END IF;
2739: IF l_contact_pt_contains_str IS NOT NULL THEN
2740: acq_cnt := acq_cnt+1;
2741: END IF;
2742: IF acq_cnt>1 THEN
2743: other_acq_criteria_exists := TRUE;

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

2769: FND_MSG_PUB.ADD;
2770: RAISE FND_API.G_EXC_ERROR;
2771: END IF;
2772: END IF;
2773: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2774: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2775: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2776: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2777: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN

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

2770: RAISE FND_API.G_EXC_ERROR;
2771: END IF;
2772: END IF;
2773: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2774: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2775: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2776: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2777: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2778: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

2771: END IF;
2772: END IF;
2773: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2774: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2775: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2776: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2777: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2778: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2779: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

2772: END IF;
2773: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2774: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2775: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2776: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2777: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2778: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2779: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2780: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

2773: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2774: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2775: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2776: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2777: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2778: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2779: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2780: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2781: END IF;

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

2774: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2775: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2776: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2777: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2778: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2779: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2780: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2781: END IF;
2782: END;

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

2775: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2776: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2777: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2778: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2779: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2780: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2781: END IF;
2782: END;
2783: /************ End of find_parties private procedures **********/

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

2776: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2777: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2778: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2779: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2780: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2781: END IF;
2782: END;
2783: /************ End of find_parties private procedures **********/
2784:

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

2848: 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);
2849: END IF;
2850: -- ************************************************************
2851: -- STEP 2. Setup of intermedia query strings for Acquisition query
2852: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2853: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2854: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2855: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2856: 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 2853: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);

2849: END IF;
2850: -- ************************************************************
2851: -- STEP 2. Setup of intermedia query strings for Acquisition query
2852: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2853: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2854: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2855: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2856: 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);
2857: END IF;

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

2850: -- ************************************************************
2851: -- STEP 2. Setup of intermedia query strings for Acquisition query
2852: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2853: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2854: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2855: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2856: 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);
2857: END IF;
2858: l_denorm_max_score:=0;

Line 2886: 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((144/180)*l_entered_max_score));

2882: END IF;
2883: ELSE
2884: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
2885: END IF;
2886: 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((144/180)*l_entered_max_score));
2887: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
2888:
2889: -- Setup Search Context ID
2890: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 2896: IF l_party_contains_str IS NULL THEN

2892:
2893: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2894: 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);
2895: END IF;
2896: IF l_party_contains_str IS NULL THEN
2897: defpt := 1;
2898: END IF;
2899: IF l_party_site_contains_str IS NULL THEN
2900: defps := 1;

Line 2899: IF l_party_site_contains_str IS NULL THEN

2895: END IF;
2896: IF l_party_contains_str IS NULL THEN
2897: defpt := 1;
2898: END IF;
2899: IF l_party_site_contains_str IS NULL THEN
2900: defps := 1;
2901: END IF;
2902: IF l_contact_contains_str IS NULL THEN
2903: defct := 1;

Line 2902: IF l_contact_contains_str IS NULL THEN

2898: END IF;
2899: IF l_party_site_contains_str IS NULL THEN
2900: defps := 1;
2901: END IF;
2902: IF l_contact_contains_str IS NULL THEN
2903: defct := 1;
2904: END IF;
2905: IF l_contact_pt_contains_str IS NULL THEN
2906: defcpt := 1;

Line 2905: IF l_contact_pt_contains_str IS NULL THEN

2901: END IF;
2902: IF l_contact_contains_str IS NULL THEN
2903: defct := 1;
2904: END IF;
2905: IF l_contact_pt_contains_str IS NULL THEN
2906: defcpt := 1;
2907: END IF;
2908:
2909: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

2910: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2911: 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);
2912: END IF;
2913: 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);
2914: 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);
2915: 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);
2916: 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);
2917: 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);
2918: 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 2915: 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);

2911: 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);
2912: END IF;
2913: 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);
2914: 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);
2915: 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);
2916: 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);
2917: 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);
2918: 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);
2919: END IF;

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

2912: END IF;
2913: 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);
2914: 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);
2915: 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);
2916: 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);
2917: 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);
2918: 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);
2919: END IF;
2920: FOR I in 1..5 LOOP

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

2913: 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);
2914: 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);
2915: 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);
2916: 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);
2917: 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);
2918: 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);
2919: END IF;
2920: FOR I in 1..5 LOOP
2921: do_eval(I);

Line 2999: l_party_contains_str VARCHAR2(32000);

2995: x_num_matches OUT NUMBER
2996: ) IS
2997:
2998: -- Strings to hold the generated Intermedia query strings
2999: l_party_contains_str VARCHAR2(32000);
3000: l_party_site_contains_str VARCHAR2(32000);
3001: l_contact_contains_str VARCHAR2(32000);
3002: l_contact_pt_contains_str VARCHAR2(32000);
3003: l_denorm_str VARCHAR2(32000);

Line 3000: l_party_site_contains_str VARCHAR2(32000);

2996: ) IS
2997:
2998: -- Strings to hold the generated Intermedia query strings
2999: l_party_contains_str VARCHAR2(32000);
3000: l_party_site_contains_str VARCHAR2(32000);
3001: l_contact_contains_str VARCHAR2(32000);
3002: l_contact_pt_contains_str VARCHAR2(32000);
3003: l_denorm_str VARCHAR2(32000);
3004: l_ps_denorm_str VARCHAR2(32000);

Line 3001: l_contact_contains_str VARCHAR2(32000);

2997:
2998: -- Strings to hold the generated Intermedia query strings
2999: l_party_contains_str VARCHAR2(32000);
3000: l_party_site_contains_str VARCHAR2(32000);
3001: l_contact_contains_str VARCHAR2(32000);
3002: l_contact_pt_contains_str VARCHAR2(32000);
3003: l_denorm_str VARCHAR2(32000);
3004: l_ps_denorm_str VARCHAR2(32000);
3005: l_ct_denorm_str VARCHAR2(32000);

Line 3002: l_contact_pt_contains_str VARCHAR2(32000);

2998: -- Strings to hold the generated Intermedia query strings
2999: l_party_contains_str VARCHAR2(32000);
3000: l_party_site_contains_str VARCHAR2(32000);
3001: l_contact_contains_str VARCHAR2(32000);
3002: l_contact_pt_contains_str VARCHAR2(32000);
3003: l_denorm_str VARCHAR2(32000);
3004: l_ps_denorm_str VARCHAR2(32000);
3005: l_ct_denorm_str VARCHAR2(32000);
3006: l_cpt_denorm_str VARCHAR2(32000);

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

3119: call_type(2) := 'OR';
3120: END;
3121:
3122: /** Private procedure to acquire and score at party level ***/
3123: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3124: l_party_id_idx NUMBER:=1;
3125: l_ctx_id NUMBER;
3126: BEGIN
3127: SAVEPOINT eval_start;

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

3140: l_search_ctx_id,H_PARTY_ID(I));
3141: l_party_id_idx := H_PARTY_ID.COUNT+1;
3142: ELSE
3143: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3144: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3145: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3146: 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);
3147: END IF;
3148: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN

Line 3152: IF (l_party_site_contains_str IS NULL AND

3148: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3149: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3150: 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);
3151: END IF;
3152: IF (l_party_site_contains_str IS NULL AND
3153: l_contact_contains_str IS NULL AND
3154: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3155: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3156: RAISE L_RETURN_IMM_EXC;

Line 3153: l_contact_contains_str IS NULL AND

3149: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3150: 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);
3151: END IF;
3152: IF (l_party_site_contains_str IS NULL AND
3153: l_contact_contains_str IS NULL AND
3154: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3155: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3156: RAISE L_RETURN_IMM_EXC;
3157: ELSE

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

3150: 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);
3151: END IF;
3152: IF (l_party_site_contains_str IS NULL AND
3153: l_contact_contains_str IS NULL AND
3154: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3155: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3156: RAISE L_RETURN_IMM_EXC;
3157: ELSE
3158: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);

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

3154: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3155: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3156: RAISE L_RETURN_IMM_EXC;
3157: ELSE
3158: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3159: END IF;
3160: ELSE
3161: IF p_index>1 THEN
3162: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3174: l_ctx_id := NULL;
3175: l_party_id_idx := H_PARTY_ID.COUNT+1;
3176: END IF;
3177: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3178: 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);
3179: END IF;
3180: LOOP
3181: FETCH l_party_cur INTO
3182: l_party_id , l_TX40, l_TX39;

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

3196: l_party_id_idx:= l_party_id_idx+1;
3197: END IF;
3198: IF (l_party_id_idx-1)>l_max_thresh THEN
3199: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3200: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3201: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3202: AND l_contact_pt_contains_str IS NULL THEN
3203: H_PARTY_ID.DELETE(l_party_id_idx-1);
3204: H_PARTY_ID_LIST.DELETE(l_index);

Line 3202: AND l_contact_pt_contains_str IS NULL THEN

3198: IF (l_party_id_idx-1)>l_max_thresh THEN
3199: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3200: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3201: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3202: AND l_contact_pt_contains_str IS NULL THEN
3203: H_PARTY_ID.DELETE(l_party_id_idx-1);
3204: H_PARTY_ID_LIST.DELETE(l_index);
3205: H_SCORES.DELETE(l_index);
3206: EXIT;

Line 3231: p_contains_str VARCHAR2,

3227: CLOSE l_party_cur;
3228: ROLLBACK to eval_start;
3229: END;
3230: PROCEDURE open_person_contact_cursor(
3231: p_contains_str VARCHAR2,
3232: p_search_ctx_id NUMBER,
3233: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3234: BEGIN
3235: OPEN x_cursor FOR

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

3234: BEGIN
3235: OPEN x_cursor FOR
3236: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID, TX22
3237: FROM HZ_STAGED_CONTACTS stage
3238: WHERE contains( concat_col, p_contains_str)>0
3239: AND ORG_CONTACT_ID in (
3240: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3241: org_contact_id
3242: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc

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

3258: end if;
3259: END;
3260:
3261: /** Private procedure to acquire and score at party site level ***/
3262: 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
3263: l_party_id_idx NUMBER:=1;
3264: l_ctx_id NUMBER;
3265: h_ps_id HZ_PARTY_SEARCH.IDList;
3266: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

3286: ELSE
3287: l_party_id_idx := H_PARTY_ID.COUNT+1;
3288: l_ctx_id := NULL;
3289: END IF;
3290: 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);
3291: LOOP
3292: FETCH l_party_site_cur INTO
3293: l_party_site_id, l_ps_party_id, l_ps_contact_id ;
3294: EXIT WHEN l_party_site_cur%NOTFOUND;

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

3357: END IF;
3358: END;
3359:
3360: /** Private procedure to acquire and score at party site level ***/
3361: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3362: l_party_id_idx NUMBER:=1;
3363: l_ctx_id NUMBER;
3364: h_ct_id HZ_PARTY_SEARCH.IDList;
3365: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

3373: FORALL I in 1..H_PARTY_ID.COUNT
3374: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
3375: l_search_ctx_id,H_PARTY_ID(I));
3376: l_party_id_idx := H_PARTY_ID.COUNT+1;
3377: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
3378: LOOP
3379: FETCH l_contact_cur INTO
3380: l_org_contact_id, l_ct_party_id , l_TX22;
3381: EXIT WHEN l_contact_cur%NOTFOUND;

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

3415: END IF;
3416: END;
3417:
3418: /** Private procedure to acquire and score at contact point level ***/
3419: 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
3420: l_party_id_idx NUMBER:=1;
3421: l_ctx_id NUMBER;
3422: h_cpt_id HZ_PARTY_SEARCH.IDList;
3423: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

3460: END IF;
3461: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3462: 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);
3463: END IF;
3464: 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);
3465: LOOP
3466: FETCH l_contact_pt_cur INTO
3467: 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;
3468: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 3667: IF l_party_contains_str IS NOT NULL THEN

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

Line 3670: IF l_party_site_contains_str IS NOT NULL THEN

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

Line 3673: IF l_contact_contains_str IS NOT NULL THEN

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

Line 3676: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 3760: IF l_party_contains_str IS NULL THEN

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

Line 3763: IF l_party_site_contains_str IS NULL THEN

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

Line 3766: IF l_contact_contains_str IS NULL THEN

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

Line 3769: IF l_contact_pt_contains_str IS NULL THEN

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

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

3771: END IF;
3772:
3773: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3774: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3775: hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3776: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3777: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3778: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3779: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

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

3772:
3773: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3774: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3775: hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3776: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3777: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3778: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3779: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3780: END IF;

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

3773: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3774: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3775: hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3776: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3777: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3778: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3779: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3780: END IF;
3781: FOR I in 1..5 LOOP

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

3774: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3775: hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3776: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3777: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3778: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3779: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3780: END IF;
3781: FOR I in 1..5 LOOP
3782: do_eval(I);

Line 3784: IF l_contact_contains_str IS NOT NULL THEN

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

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

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

Line 4573: l_party_contains_str VARCHAR2(32000);

4569: x_num_matches OUT NUMBER
4570: ) IS
4571:
4572: -- Strings to hold the generated Intermedia query strings
4573: l_party_contains_str VARCHAR2(32000);
4574: l_match_idx NUMBER;
4575: l_party_site_contains_str VARCHAR2(32000);
4576: l_contact_contains_str VARCHAR2(32000);
4577: l_contact_pt_contains_str VARCHAR2(32000);

Line 4575: l_party_site_contains_str VARCHAR2(32000);

4571:
4572: -- Strings to hold the generated Intermedia query strings
4573: l_party_contains_str VARCHAR2(32000);
4574: l_match_idx NUMBER;
4575: l_party_site_contains_str VARCHAR2(32000);
4576: l_contact_contains_str VARCHAR2(32000);
4577: l_contact_pt_contains_str VARCHAR2(32000);
4578: l_tmp VARCHAR2(32000);
4579:

Line 4576: l_contact_contains_str VARCHAR2(32000);

4572: -- Strings to hold the generated Intermedia query strings
4573: l_party_contains_str VARCHAR2(32000);
4574: l_match_idx NUMBER;
4575: l_party_site_contains_str VARCHAR2(32000);
4576: l_contact_contains_str VARCHAR2(32000);
4577: l_contact_pt_contains_str VARCHAR2(32000);
4578: l_tmp VARCHAR2(32000);
4579:
4580: -- Other local variables

Line 4577: l_contact_pt_contains_str VARCHAR2(32000);

4573: l_party_contains_str VARCHAR2(32000);
4574: l_match_idx NUMBER;
4575: l_party_site_contains_str VARCHAR2(32000);
4576: l_contact_contains_str VARCHAR2(32000);
4577: l_contact_pt_contains_str VARCHAR2(32000);
4578: l_tmp VARCHAR2(32000);
4579:
4580: -- Other local variables
4581: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4649: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4650:
4651: -- ************************************************************
4652: -- STEP 2. Setup of intermedia query strings for Acquisition query
4653: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4654: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4655: 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);
4656:
4657: -- Setup Search Context ID

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

4650:
4651: -- ************************************************************
4652: -- STEP 2. Setup of intermedia query strings for Acquisition query
4653: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4654: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4655: 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);
4656:
4657: -- Setup Search Context ID
4658: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4659: x_search_ctx_id := l_search_ctx_id;
4660:
4661: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4662: 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);
4663: 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);
4664: 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);
4665: 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);
4666: END IF;
4667: IF l_party_site_contains_str IS NULL THEN

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

4660:
4661: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4662: 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);
4663: 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);
4664: 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);
4665: 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);
4666: END IF;
4667: IF l_party_site_contains_str IS NULL THEN
4668: defps := 1;

Line 4667: IF l_party_site_contains_str IS NULL THEN

4663: 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);
4664: 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);
4665: 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);
4666: END IF;
4667: IF l_party_site_contains_str IS NULL THEN
4668: defps := 1;
4669: END IF;
4670: IF l_contact_pt_contains_str IS NULL THEN
4671: defcpt := 1;

Line 4670: IF l_contact_pt_contains_str IS NULL THEN

4666: END IF;
4667: IF l_party_site_contains_str IS NULL THEN
4668: defps := 1;
4669: END IF;
4670: IF l_contact_pt_contains_str IS NULL THEN
4671: defcpt := 1;
4672: END IF;
4673:
4674: IF l_party_site_contains_str IS NOT NULL THEN

Line 4674: IF l_party_site_contains_str IS NOT NULL THEN

4670: IF l_contact_pt_contains_str IS NULL THEN
4671: defcpt := 1;
4672: END IF;
4673:
4674: IF l_party_site_contains_str IS NOT NULL THEN
4675: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4676: LOOP
4677: FETCH l_party_site_cur INTO
4678: l_party_site_id, l_ps_party_id, l_ps_contact_id ;

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

4671: defcpt := 1;
4672: END IF;
4673:
4674: IF l_party_site_contains_str IS NOT NULL THEN
4675: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4676: LOOP
4677: FETCH l_party_site_cur INTO
4678: l_party_site_id, l_ps_party_id, l_ps_contact_id ;
4679: EXIT WHEN l_party_site_cur%NOTFOUND;

Line 4698: IF l_contact_pt_contains_str IS NOT NULL THEN

4694: END LOOP;
4695: CLOSE l_party_site_cur;
4696: END IF;
4697:
4698: IF l_contact_pt_contains_str IS NOT NULL THEN
4699: SAVEPOINT eval_start;
4700: IF l_match_str = ' AND ' OR (
4701: ((l_cpt_max_score/l_entered_max_score)<(144/180))
4702: ) THEN

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

4702: ) THEN
4703: FORALL I in 1..l_match_ps_list.COUNT
4704: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4705: values (l_search_ctx_id,l_match_ps_list(I));
4706: 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');
4707: ELSE
4708: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4709: END IF;
4710: LOOP

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

4704: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4705: values (l_search_ctx_id,l_match_ps_list(I));
4706: 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');
4707: ELSE
4708: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4709: END IF;
4710: LOOP
4711: FETCH l_contact_pt_cur INTO
4712: 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 4808: l_party_contains_str VARCHAR2(32000);

4804: x_num_matches OUT NUMBER
4805: ) IS
4806:
4807: -- Strings to hold the generated Intermedia query strings
4808: l_party_contains_str VARCHAR2(32000);
4809: l_party_site_contains_str VARCHAR2(32000);
4810: l_contact_contains_str VARCHAR2(32000);
4811: l_contact_pt_contains_str VARCHAR2(32000);
4812: l_tmp VARCHAR2(32000);

Line 4809: l_party_site_contains_str VARCHAR2(32000);

4805: ) IS
4806:
4807: -- Strings to hold the generated Intermedia query strings
4808: l_party_contains_str VARCHAR2(32000);
4809: l_party_site_contains_str VARCHAR2(32000);
4810: l_contact_contains_str VARCHAR2(32000);
4811: l_contact_pt_contains_str VARCHAR2(32000);
4812: l_tmp VARCHAR2(32000);
4813:

Line 4810: l_contact_contains_str VARCHAR2(32000);

4806:
4807: -- Strings to hold the generated Intermedia query strings
4808: l_party_contains_str VARCHAR2(32000);
4809: l_party_site_contains_str VARCHAR2(32000);
4810: l_contact_contains_str VARCHAR2(32000);
4811: l_contact_pt_contains_str VARCHAR2(32000);
4812: l_tmp VARCHAR2(32000);
4813:
4814: -- Other local variables

Line 4811: l_contact_pt_contains_str VARCHAR2(32000);

4807: -- Strings to hold the generated Intermedia query strings
4808: l_party_contains_str VARCHAR2(32000);
4809: l_party_site_contains_str VARCHAR2(32000);
4810: l_contact_contains_str VARCHAR2(32000);
4811: l_contact_pt_contains_str VARCHAR2(32000);
4812: l_tmp VARCHAR2(32000);
4813:
4814: -- Other local variables
4815: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4882: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4883:
4884: -- ************************************************************
4885: -- STEP 2. Setup of intermedia query strings for Acquisition query
4886: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4887: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4888: 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);
4889:
4890: -- Setup Search Context ID

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

4883:
4884: -- ************************************************************
4885: -- STEP 2. Setup of intermedia query strings for Acquisition query
4886: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4887: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4888: 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);
4889:
4890: -- Setup Search Context ID
4891: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4892: x_search_ctx_id := l_search_ctx_id;
4893:
4894: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4895: 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);
4896: 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);
4897: 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);
4898: 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);
4899: END IF;
4900: IF l_contact_contains_str IS NULL THEN

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

4893:
4894: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4895: 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);
4896: 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);
4897: 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);
4898: 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);
4899: END IF;
4900: IF l_contact_contains_str IS NULL THEN
4901: defct := 1;

Line 4900: IF l_contact_contains_str IS NULL THEN

4896: 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);
4897: 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);
4898: 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);
4899: END IF;
4900: IF l_contact_contains_str IS NULL THEN
4901: defct := 1;
4902: END IF;
4903: IF l_contact_pt_contains_str IS NULL THEN
4904: defcpt := 1;

Line 4903: IF l_contact_pt_contains_str IS NULL THEN

4899: END IF;
4900: IF l_contact_contains_str IS NULL THEN
4901: defct := 1;
4902: END IF;
4903: IF l_contact_pt_contains_str IS NULL THEN
4904: defcpt := 1;
4905: END IF;
4906:
4907: IF l_contact_contains_str IS NOT NULL THEN

Line 4907: IF l_contact_contains_str IS NOT NULL THEN

4903: IF l_contact_pt_contains_str IS NULL THEN
4904: defcpt := 1;
4905: END IF;
4906:
4907: IF l_contact_contains_str IS NOT NULL THEN
4908: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
4909: LOOP
4910: FETCH l_contact_cur INTO
4911: l_org_contact_id, l_ct_party_id , l_TX22;

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

4904: defcpt := 1;
4905: END IF;
4906:
4907: IF l_contact_contains_str IS NOT NULL THEN
4908: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
4909: LOOP
4910: FETCH l_contact_cur INTO
4911: l_org_contact_id, l_ct_party_id , l_TX22;
4912: EXIT WHEN l_contact_cur%NOTFOUND;

Line 4929: IF l_contact_pt_contains_str IS NOT NULL THEN

4925: END LOOP;
4926: CLOSE l_contact_cur;
4927: END IF;
4928:
4929: IF l_contact_pt_contains_str IS NOT NULL THEN
4930: SAVEPOINT eval_start;
4931: IF l_match_str = ' AND ' OR (
4932: ((l_cpt_max_score/l_entered_max_score)<(144/180))
4933: ) THEN

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

4933: ) THEN
4934: FORALL I in 1..l_match_contact_list.COUNT
4935: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4936: values (l_search_ctx_id,l_match_contact_list(I));
4937: 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');
4938: ELSE
4939: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4940: END IF;
4941: LOOP

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

4935: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4936: values (l_search_ctx_id,l_match_contact_list(I));
4937: 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');
4938: ELSE
4939: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4940: END IF;
4941: LOOP
4942: FETCH l_contact_pt_cur INTO
4943: 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 5037: l_contact_pt_contains_str VARCHAR2(32000);

5033: ) IS
5034:
5035:
5036: -- Strings to hold the generated Intermedia query strings
5037: l_contact_pt_contains_str VARCHAR2(32000);
5038: l_tmp VARCHAR2(32000);
5039:
5040: -- Other local variables
5041: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5094: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5095:
5096: -- ************************************************************
5097: -- STEP 2. Setup of intermedia query strings for Acquisition query
5098: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5099: 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);
5100:
5101: -- Setup Search Context ID
5102: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

5102: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5103: x_search_ctx_id := l_search_ctx_id;
5104: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5105: 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);
5106: 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);
5107: 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);
5108: END IF;
5109:
5110: IF l_contact_pt_contains_str IS NOT NULL THEN

Line 5110: IF l_contact_pt_contains_str IS NOT NULL THEN

5106: 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);
5107: 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);
5108: END IF;
5109:
5110: IF l_contact_pt_contains_str IS NOT NULL THEN
5111: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5112: cnt := 1;
5113: LOOP
5114: FETCH l_contact_pt_cur INTO

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

5107: 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);
5108: END IF;
5109:
5110: IF l_contact_pt_contains_str IS NOT NULL THEN
5111: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5112: cnt := 1;
5113: LOOP
5114: FETCH l_contact_pt_cur INTO
5115: 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 5182: l_party_contains_str VARCHAR2(32000);

5178: x_search_ctx_id IN OUT NUMBER
5179: ) IS
5180:
5181: -- Strings to hold the generated Intermedia query strings
5182: l_party_contains_str VARCHAR2(32000);
5183: l_party_site_contains_str VARCHAR2(32000);
5184: l_contact_contains_str VARCHAR2(32000);
5185: l_contact_pt_contains_str VARCHAR2(32000);
5186: l_tmp VARCHAR2(32000);

Line 5183: l_party_site_contains_str VARCHAR2(32000);

5179: ) IS
5180:
5181: -- Strings to hold the generated Intermedia query strings
5182: l_party_contains_str VARCHAR2(32000);
5183: l_party_site_contains_str VARCHAR2(32000);
5184: l_contact_contains_str VARCHAR2(32000);
5185: l_contact_pt_contains_str VARCHAR2(32000);
5186: l_tmp VARCHAR2(32000);
5187:

Line 5184: l_contact_contains_str VARCHAR2(32000);

5180:
5181: -- Strings to hold the generated Intermedia query strings
5182: l_party_contains_str VARCHAR2(32000);
5183: l_party_site_contains_str VARCHAR2(32000);
5184: l_contact_contains_str VARCHAR2(32000);
5185: l_contact_pt_contains_str VARCHAR2(32000);
5186: l_tmp VARCHAR2(32000);
5187:
5188: -- Other local variables

Line 5185: l_contact_pt_contains_str VARCHAR2(32000);

5181: -- Strings to hold the generated Intermedia query strings
5182: l_party_contains_str VARCHAR2(32000);
5183: l_party_site_contains_str VARCHAR2(32000);
5184: l_contact_contains_str VARCHAR2(32000);
5185: l_contact_pt_contains_str VARCHAR2(32000);
5186: l_tmp VARCHAR2(32000);
5187:
5188: -- Other local variables
5189: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5250: g_score_until_thresh := false;
5251: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5252: -- ************************************************************
5253: -- STEP 2. Setup of intermedia query strings for Acquisition query
5254: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5255: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5256: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5257: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5258:

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

5251: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5252: -- ************************************************************
5253: -- STEP 2. Setup of intermedia query strings for Acquisition query
5254: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5255: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5256: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5257: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5258:
5259: -- Setup Search Context ID

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

5252: -- ************************************************************
5253: -- STEP 2. Setup of intermedia query strings for Acquisition query
5254: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5255: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5256: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5257: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5258:
5259: -- Setup Search Context ID
5260: IF x_search_ctx_id IS NULL THEN

Line 5276: IF l_party_site_contains_str IS NOT NULL THEN

5272: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX40,l_TX39,1);
5273: END LOOP;
5274: CLOSE l_party_cur;
5275:
5276: IF l_party_site_contains_str IS NOT NULL THEN
5277: l_max_score := 0;
5278: l_max_id := 0;
5279: l_max_idx := 0;
5280: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN

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

5277: l_max_score := 0;
5278: l_max_id := 0;
5279: l_max_idx := 0;
5280: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5281: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5282: ELSE
5283: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5284: END IF;
5285: LOOP

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

5279: l_max_idx := 0;
5280: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5281: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5282: ELSE
5283: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5284: END IF;
5285: LOOP
5286: FETCH l_party_site_cur INTO
5287: l_party_site_id, l_ps_party_id,l_ps_contact_id ;

Line 5302: IF l_contact_contains_str IS NOT NULL THEN

5298: INSERT_PARTY_SITES_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_party_site_list(l_max_idx), g_party_site_stage_list(l_max_idx) ,l_max_idx);
5299: END IF;
5300: END IF;
5301:
5302: IF l_contact_contains_str IS NOT NULL THEN
5303: l_max_score := 0;
5304: l_max_id := 0;
5305: l_max_idx := 0;
5306: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

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

5302: IF l_contact_contains_str IS NOT NULL THEN
5303: l_max_score := 0;
5304: l_max_id := 0;
5305: l_max_idx := 0;
5306: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5307: LOOP
5308: FETCH l_contact_cur INTO
5309: l_org_contact_id, l_ct_party_id , l_TX22;
5310: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5325: IF l_contact_pt_contains_str IS NOT NULL THEN

5321: 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_TX22,l_max_idx);
5322: END IF;
5323: END IF;
5324:
5325: IF l_contact_pt_contains_str IS NOT NULL THEN
5326: l_max_score := 0;
5327: l_max_id := 0;
5328: l_max_idx := 0;
5329: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN

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

5326: l_max_score := 0;
5327: l_max_id := 0;
5328: l_max_idx := 0;
5329: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5330: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5331: ELSE
5332: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5333: END IF;
5334: LOOP

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

5328: l_max_idx := 0;
5329: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5330: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5331: ELSE
5332: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5333: END IF;
5334: LOOP
5335: FETCH l_contact_pt_cur INTO
5336: 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;