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_240 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 861: l_party_contains_str VARCHAR2(32000);

857: p_party_max_score NUMBER,
858: p_denorm_max_score NUMBER,
859: p_non_denorm_max_score NUMBER,
860: p_threshold NUMBER) RETURN VARCHAR2 IS
861: l_party_contains_str VARCHAR2(32000);
862: l_party_filter VARCHAR2(1) := null;
863: l_prim_temp VARCHAR2(4000);
864: l_denorm_str VARCHAR2(4000);
865: BEGIN

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

901:
902: IF l_denorm_str IS NOT NULL THEN
903: IF (p_non_denorm_max_score+50)>=p_threshold THEN
904: l_denorm_str := NULL;
905: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
906: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
907: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
908: l_denorm_str := NULL;
909: END IF;

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

903: IF (p_non_denorm_max_score+50)>=p_threshold THEN
904: l_denorm_str := NULL;
905: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
906: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
907: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
908: l_denorm_str := NULL;
909: END IF;
910: ELSE
911: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

907: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
908: l_denorm_str := NULL;
909: END IF;
910: ELSE
911: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
912: END IF;
913: IF lengthb(l_party_contains_str) > 4000 THEN
914: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
915: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');

Line 913: IF lengthb(l_party_contains_str) > 4000 THEN

909: END IF;
910: ELSE
911: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
912: END IF;
913: IF lengthb(l_party_contains_str) > 4000 THEN
914: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
915: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
916: FND_MSG_PUB.ADD;
917: RAISE FND_API.G_EXC_ERROR;

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

915: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
916: FND_MSG_PUB.ADD;
917: RAISE FND_API.G_EXC_ERROR;
918: END IF;
919: IF (p_match_str = ' AND ' OR p_party_max_score 920: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
921: 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);
922: END IF;
923: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

917: RAISE FND_API.G_EXC_ERROR;
918: END IF;
919: IF (p_match_str = ' AND ' OR p_party_max_score 920: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
921: 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);
922: END IF;
923: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
924: 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);
925: END IF;

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

920: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
921: 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);
922: END IF;
923: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
924: 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);
925: END IF;
926: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
927: 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);
928: END IF;

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

925: END IF;
926: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
927: 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);
928: END IF;
929: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
930: ELSE
931: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
932: 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);
933: 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 932: 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);

928: END IF;
929: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
930: ELSE
931: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
932: 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);
933: 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);
934: END IF;
935: RETURN l_party_contains_str;
936: END IF;

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

929: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
930: ELSE
931: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
932: 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);
933: 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);
934: END IF;
935: RETURN l_party_contains_str;
936: END IF;
937: END;

Line 935: RETURN l_party_contains_str;

931: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
932: 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);
933: 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);
934: END IF;
935: RETURN l_party_contains_str;
936: END IF;
937: END;
938: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
939: l_contains_str VARCHAR2(32000);

Line 939: l_contains_str VARCHAR2(32000);

935: RETURN l_party_contains_str;
936: END IF;
937: END;
938: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
939: l_contains_str VARCHAR2(32000);
940: l_contains_str_temp VARCHAR2(32000);
941: l_den_contains_str VARCHAR2(32000);
942: l_den_contains_str_temp VARCHAR2(32000);
943: l_filter_str VARCHAR2(4000) := null;

Line 940: l_contains_str_temp VARCHAR2(32000);

936: END IF;
937: END;
938: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
939: l_contains_str VARCHAR2(32000);
940: l_contains_str_temp VARCHAR2(32000);
941: l_den_contains_str VARCHAR2(32000);
942: l_den_contains_str_temp VARCHAR2(32000);
943: l_filter_str VARCHAR2(4000) := null;
944: l_prim_temp VARCHAR2(4000) := null;

Line 941: l_den_contains_str VARCHAR2(32000);

937: END;
938: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
939: l_contains_str VARCHAR2(32000);
940: l_contains_str_temp VARCHAR2(32000);
941: l_den_contains_str VARCHAR2(32000);
942: l_den_contains_str_temp VARCHAR2(32000);
943: l_filter_str VARCHAR2(4000) := null;
944: l_prim_temp VARCHAR2(4000) := null;
945: l_prim_temp_den VARCHAR2(4000) := null;

Line 942: l_den_contains_str_temp VARCHAR2(32000);

938: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
939: l_contains_str VARCHAR2(32000);
940: l_contains_str_temp VARCHAR2(32000);
941: l_den_contains_str VARCHAR2(32000);
942: l_den_contains_str_temp VARCHAR2(32000);
943: l_filter_str VARCHAR2(4000) := null;
944: l_prim_temp VARCHAR2(4000) := null;
945: l_prim_temp_den VARCHAR2(4000) := null;
946: BEGIN

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

948: hz_utility_v2pub.debug(p_message=>'INIT_PARTY_SITES_QUERY ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
949: END IF;
950: x_denorm_str := NULL;
951:
952: -- Dynamic setup of party site contains str
953: --
954: -- For each primary transformation add to intermedia query if it
955: -- is not null
956: FOR I IN 1..g_party_site_stage_list.COUNT LOOP

Line 957: l_contains_str_temp := null;

953: --
954: -- For each primary transformation add to intermedia query if it
955: -- is not null
956: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
957: l_contains_str_temp := null;
958: l_den_contains_str_temp := null;
959: l_filter_str := null;
960:
961: -- Setup query string for ADDRESS

Line 958: l_den_contains_str_temp := null;

954: -- For each primary transformation add to intermedia query if it
955: -- is not null
956: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
957: l_contains_str_temp := null;
958: l_den_contains_str_temp := null;
959: l_filter_str := null;
960:
961: -- Setup query string for ADDRESS
962: l_prim_temp := null;

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

962: l_prim_temp := null;
963: l_prim_temp_den := null;
964: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRADDRESS
965: g_party_site_stage_list(I).TX4,'A27',l_prim_temp);
966: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
967:
968: -- Setup query string for POSTAL_CODE
969: l_prim_temp := null;
970: l_prim_temp_den := null;

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

971: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
972: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
973: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
974: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
975: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
976: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
977:
978: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
979: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);

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

972: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
973: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
974: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
975: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
976: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
977:
978: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
979: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
980: END LOOP;

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

974: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
975: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
976: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
977:
978: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
979: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
980: END LOOP;
981: -- Add the search criteria to query string
982: IF lengthb(l_contains_str) > 4000 THEN

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

975: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
976: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
977:
978: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
979: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
980: END LOOP;
981: -- Add the search criteria to query string
982: IF lengthb(l_contains_str) > 4000 THEN
983: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');

Line 982: IF lengthb(l_contains_str) > 4000 THEN

978: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
979: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
980: END LOOP;
981: -- Add the search criteria to query string
982: IF lengthb(l_contains_str) > 4000 THEN
983: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
984: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
985: FND_MSG_PUB.ADD;
986: RAISE FND_API.G_EXC_ERROR;

Line 988: x_denorm_str := l_den_contains_str;

984: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
985: FND_MSG_PUB.ADD;
986: RAISE FND_API.G_EXC_ERROR;
987: END IF;
988: x_denorm_str := l_den_contains_str;
989: RETURN l_contains_str;
990: END;
991:
992: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS

Line 989: RETURN l_contains_str;

985: FND_MSG_PUB.ADD;
986: RAISE FND_API.G_EXC_ERROR;
987: END IF;
988: x_denorm_str := l_den_contains_str;
989: RETURN l_contains_str;
990: END;
991:
992: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
993: l_contains_str VARCHAR2(32000);

Line 993: l_contains_str VARCHAR2(32000);

989: RETURN l_contains_str;
990: END;
991:
992: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
993: l_contains_str VARCHAR2(32000);
994: l_contains_str_temp VARCHAR2(32000);
995: l_den_contains_str VARCHAR2(32000);
996: l_den_contains_str_temp VARCHAR2(32000);
997: l_filter_str VARCHAR2(4000) := null;

Line 994: l_contains_str_temp VARCHAR2(32000);

990: END;
991:
992: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
993: l_contains_str VARCHAR2(32000);
994: l_contains_str_temp VARCHAR2(32000);
995: l_den_contains_str VARCHAR2(32000);
996: l_den_contains_str_temp VARCHAR2(32000);
997: l_filter_str VARCHAR2(4000) := null;
998: l_prim_temp VARCHAR2(4000) := null;

Line 995: l_den_contains_str VARCHAR2(32000);

991:
992: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
993: l_contains_str VARCHAR2(32000);
994: l_contains_str_temp VARCHAR2(32000);
995: l_den_contains_str VARCHAR2(32000);
996: l_den_contains_str_temp VARCHAR2(32000);
997: l_filter_str VARCHAR2(4000) := null;
998: l_prim_temp VARCHAR2(4000) := null;
999: l_prim_temp_den VARCHAR2(4000) := null;

Line 996: l_den_contains_str_temp VARCHAR2(32000);

992: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
993: l_contains_str VARCHAR2(32000);
994: l_contains_str_temp VARCHAR2(32000);
995: l_den_contains_str VARCHAR2(32000);
996: l_den_contains_str_temp VARCHAR2(32000);
997: l_filter_str VARCHAR2(4000) := null;
998: l_prim_temp VARCHAR2(4000) := null;
999: l_prim_temp_den VARCHAR2(4000) := null;
1000: BEGIN

Line 1009: l_contains_str VARCHAR2(32000);

1005: RETURN NULL;
1006: END;
1007:
1008: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1009: l_contains_str VARCHAR2(32000);
1010: l_contains_str_temp VARCHAR2(32000);
1011: l_den_contains_str VARCHAR2(32000);
1012: l_den_contains_str_temp VARCHAR2(32000);
1013: l_filter_str VARCHAR2(4000) := null;

Line 1010: l_contains_str_temp VARCHAR2(32000);

1006: END;
1007:
1008: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1009: l_contains_str VARCHAR2(32000);
1010: l_contains_str_temp VARCHAR2(32000);
1011: l_den_contains_str VARCHAR2(32000);
1012: l_den_contains_str_temp VARCHAR2(32000);
1013: l_filter_str VARCHAR2(4000) := null;
1014: l_prim_temp VARCHAR2(4000) := null;

Line 1011: l_den_contains_str VARCHAR2(32000);

1007:
1008: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1009: l_contains_str VARCHAR2(32000);
1010: l_contains_str_temp VARCHAR2(32000);
1011: l_den_contains_str VARCHAR2(32000);
1012: l_den_contains_str_temp VARCHAR2(32000);
1013: l_filter_str VARCHAR2(4000) := null;
1014: l_prim_temp VARCHAR2(4000) := null;
1015: l_prim_temp_den VARCHAR2(4000) := null;

Line 1012: l_den_contains_str_temp VARCHAR2(32000);

1008: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1009: l_contains_str VARCHAR2(32000);
1010: l_contains_str_temp VARCHAR2(32000);
1011: l_den_contains_str VARCHAR2(32000);
1012: l_den_contains_str_temp VARCHAR2(32000);
1013: l_filter_str VARCHAR2(4000) := null;
1014: l_prim_temp VARCHAR2(4000) := null;
1015: l_prim_temp_den VARCHAR2(4000) := null;
1016: TYPE CONTACT_PT_REC_TYPE IS RECORD (

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

1024: 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);
1025: END IF;
1026: x_denorm_str := NULL;
1027:
1028: -- Dynamic setup of party site contains str
1029: --
1030: -- For each primary transformation add to intermedia query if it
1031: -- is not null
1032: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP

Line 1033: l_contains_str_temp := null;

1029: --
1030: -- For each primary transformation add to intermedia query if it
1031: -- is not null
1032: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1033: l_contains_str_temp := null;
1034: l_den_contains_str_temp := null;
1035: l_filter_str := null;
1036: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1037: if(l_cnt_pt_type.count>0) then

Line 1034: l_den_contains_str_temp := null;

1030: -- For each primary transformation add to intermedia query if it
1031: -- is not null
1032: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1033: l_contains_str_temp := null;
1034: l_den_contains_str_temp := null;
1035: l_filter_str := null;
1036: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1037: if(l_cnt_pt_type.count>0) then
1038: x_modify := 'Y';

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

1054: l_prim_temp := null;
1055: l_prim_temp_den := null;
1056: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL
1057: g_contact_pt_stage_list(I).TX2,'A45',l_prim_temp);
1058: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1059:
1060: -- Setup query string for EMAIL_ADDRESS
1061: l_prim_temp := null;
1062: l_prim_temp_den := null;

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

1061: l_prim_temp := null;
1062: l_prim_temp_den := null;
1063: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (EMAIL) + WRDOMAIN
1064: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1065: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1066:
1067: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1068: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1069: IF N>1 THEN

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

1063: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (EMAIL) + WRDOMAIN
1064: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1065: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1066:
1067: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1068: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1069: IF N>1 THEN
1070: distinct_search_cpt_types := N-1;
1071: ELSE

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

1064: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1065: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1066:
1067: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1068: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1069: IF N>1 THEN
1070: distinct_search_cpt_types := N-1;
1071: ELSE
1072: distinct_search_cpt_types := N;

Line 1079: IF lengthb(l_contains_str) > 4000 THEN

1075: 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);
1076: END IF;
1077: END LOOP;
1078: -- Add the search criteria to query string
1079: IF lengthb(l_contains_str) > 4000 THEN
1080: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1081: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1082: FND_MSG_PUB.ADD;
1083: RAISE FND_API.G_EXC_ERROR;

Line 1085: x_denorm_str := l_den_contains_str;

1081: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1082: FND_MSG_PUB.ADD;
1083: RAISE FND_API.G_EXC_ERROR;
1084: END IF;
1085: x_denorm_str := l_den_contains_str;
1086: RETURN l_contains_str;
1087: END;
1088:
1089: PROCEDURE open_party_cursor_direct (

Line 1086: RETURN l_contains_str;

1082: FND_MSG_PUB.ADD;
1083: RAISE FND_API.G_EXC_ERROR;
1084: END IF;
1085: x_denorm_str := l_den_contains_str;
1086: RETURN l_contains_str;
1087: END;
1088:
1089: PROCEDURE open_party_cursor_direct (
1090: p_dup_party_id NUMBER,

Line 1094: p_party_contains_str VARCHAR2,

1090: p_dup_party_id NUMBER,
1091: p_restrict_sql VARCHAR2,
1092: p_match_str VARCHAR2,
1093: p_search_merged VARCHAR2,
1094: p_party_contains_str VARCHAR2,
1095: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1096: l_sqlstr VARCHAR2(4000);
1097: l_search_merged VARCHAR2(1);
1098: BEGIN

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

1107: END IF;
1108: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1109: 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);
1110: END IF;
1111: 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
1112: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1113: 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);
1114: END IF;
1115: OPEN x_cursor FOR

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

1136: l_sqlstr := l_sqlstr || ' AND (:p_dup IS NULL OR party_id <> :p_dup ) ';
1137: IF g_other_party_level_attribs THEN
1138: NULL;
1139: END IF;
1140: 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
1141: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1142: 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);
1143: END IF;
1144: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';

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

1138: NULL;
1139: END IF;
1140: 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
1141: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1142: 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);
1143: END IF;
1144: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1145: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX11||'' %'' ';
1146: l_sqlstr := l_sqlstr || ' )) ';

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

1159: END IF;
1160: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1161: 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);
1162: END IF;
1163: 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
1164: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1165: 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);
1166: END IF;
1167: 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 1165: 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);

1161: 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);
1162: END IF;
1163: 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
1164: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1165: 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);
1166: END IF;
1167: 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
1168: ,g_party_site_stage_list(1).TX11
1169: ,g_party_site_stage_list(1).TX11

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

1173: hz_utility_v2pub.debug(p_message=>'ELSIF g_other_party_level_attribs THEN',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1174: END IF;
1175: 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
1176: ;
1177: 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
1178: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1179: 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);
1180: END IF;
1181: 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 1179: 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);

1175: 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
1176: ;
1177: 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
1178: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1179: 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);
1180: END IF;
1181: 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
1182: ,g_party_site_stage_list(1).TX11
1183: ,g_party_site_stage_list(1).TX11

Line 1196: p_contains_str VARCHAR2,

1192: END;
1193: PROCEDURE open_party_cursor(
1194: p_dup_party_id NUMBER,
1195: p_restrict_sql VARCHAR2,
1196: p_contains_str VARCHAR2,
1197: p_search_ctx_id NUMBER,
1198: p_match_str VARCHAR2,
1199: p_search_merged VARCHAR2,
1200: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS

Line 1215: IF p_contains_str IS NULL THEN

1211: l_search_merged := 'N';
1212: ELSE
1213: l_search_merged := p_search_merged;
1214: END IF;
1215: IF p_contains_str IS NULL THEN
1216: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1217: 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);
1218: END IF;
1219: OPEN x_cursor FOR

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

1213: l_search_merged := p_search_merged;
1214: END IF;
1215: IF p_contains_str IS NULL THEN
1216: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1217: 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);
1218: END IF;
1219: OPEN x_cursor FOR
1220: SELECT PARTY_ID , TX4
1221: FROM HZ_STAGED_PARTIES stage

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

1230: END IF;
1231: OPEN x_cursor FOR
1232: SELECT /*+ INDEX(stage HZ_STAGE_PARTIES_T1) */ PARTY_ID , TX4
1233: FROM HZ_STAGED_PARTIES stage
1234: WHERE contains( concat_col, p_contains_str)>0
1235: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1236: AND( (l_search_merged ='Y' )
1237: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1238: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )

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

1243: END IF;
1244: OPEN x_cursor FOR
1245: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX4
1246: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1247: WHERE contains( concat_col, p_contains_str)>0
1248: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1249: AND d.party_id = stage.party_id
1250: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1251: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)

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

1268: END IF;
1269: IF p_search_ctx_id IS NULL THEN
1270: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX4'||
1271: ' FROM HZ_STAGED_PARTIES stage'||
1272: ' WHERE contains( concat_col, :cont)>0'||
1273: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1274: ' AND ('||p_restrict_sql||')' ||
1275: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1276: IF l_search_merged = 'Y' THEN

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

1280: ELSE
1281: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1282: END IF;
1283: output_long_strings('----------------------------------------------------------');
1284: output_long_strings('Party Contains String = '||p_contains_str);
1285: output_long_strings('Restrict Sql = '||p_restrict_sql);
1286: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1287: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1288: ,p_dup_party_id, p_dup_party_id;

Line 1286: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1282: END IF;
1283: output_long_strings('----------------------------------------------------------');
1284: output_long_strings('Party Contains String = '||p_contains_str);
1285: output_long_strings('Restrict Sql = '||p_restrict_sql);
1286: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1287: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1288: ,p_dup_party_id, p_dup_party_id;
1289: END IF;
1290: END IF;

Line 1317: p_contains_str VARCHAR2,

1313: PROCEDURE open_party_site_cursor(
1314: p_dup_party_id NUMBER,
1315: p_party_id NUMBER,
1316: p_restrict_sql VARCHAR2,
1317: p_contains_str VARCHAR2,
1318: p_search_ctx_id NUMBER,
1319: p_search_merged VARCHAR2,
1320: p_search_rel_sites VARCHAR2,
1321: p_person_api VARCHAR2,

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

1345: IF p_search_rel_sites = 'N' THEN
1346: OPEN x_cursor FOR
1347: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX4, TX11
1348: FROM HZ_STAGED_PARTY_SITES stage
1349: WHERE contains( concat_col, p_contains_str)>0
1350: AND( (l_search_merged ='Y' )
1351: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1352: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1353: AND stage.party_id = p_party_id;

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

1354: ELSE
1355: OPEN x_cursor FOR
1356: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX4, TX11
1357: FROM HZ_STAGED_PARTY_SITES stage
1358: WHERE contains( concat_col, p_contains_str)>0
1359: AND( (l_search_merged ='Y' )
1360: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1361: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1362: AND stage.party_id = p_party_id

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

1362: AND stage.party_id = p_party_id
1363: UNION
1364: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX4, TX11
1365: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1366: WHERE contains( concat_col, p_contains_str)>0
1367: AND( (l_search_merged ='Y' )
1368: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1369: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1370: AND r.object_id = p_party_id

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

1384: END IF;
1385: OPEN x_cursor FOR
1386: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX4, TX11
1387: FROM HZ_STAGED_PARTY_SITES stage
1388: WHERE contains( concat_col, p_contains_str)>0
1389: AND EXISTS (
1390: SELECT 1 FROM HZ_STAGED_PARTIES p
1391: WHERE p.PARTY_ID = stage.PARTY_ID
1392: AND( (l_search_merged ='Y' )

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

1404: IF p_person_api = 'Y' THEN
1405: OPEN x_cursor FOR
1406: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX4, TX11
1407: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1408: WHERE contains( concat_col, p_contains_str)>0
1409: AND( (l_search_merged ='Y' )
1410: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1411: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1412: AND d.search_context_id = p_search_ctx_id

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

1414: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1415: UNION
1416: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX4, TX11
1417: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1418: WHERE contains( concat_col, p_contains_str)>0
1419: AND( (l_search_merged ='Y' )
1420: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1421: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1422: AND d.search_context_id = p_search_ctx_id

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

1427: ELSE
1428: OPEN x_cursor FOR
1429: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX4, TX11
1430: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1431: WHERE contains( concat_col, p_contains_str)>0
1432: AND( (l_search_merged ='Y' )
1433: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1434: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1435: AND d.search_context_id = p_search_ctx_id

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

1472: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1473: END IF;
1474: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX11'||
1475: ' FROM HZ_STAGED_PARTY_SITES stage'||
1476: ' WHERE contains( concat_col, :cont)>0'||
1477: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1478: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1479: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1480: ' AND (ORG_CONTACT_ID IS NULL ' ||

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

1482: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1483: ' UNION ' ||
1484: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX11'||
1485: ' FROM HZ_STAGED_PARTY_SITES stage'||
1486: ' WHERE contains( concat_col, :cont)>0'||
1487: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1488: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1489: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1490: ' AND ORG_CONTACT_ID IN ' ||

Line 1496: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1492: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1493: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1494: ' and ('||p_restrict_sql1|| ') )' ||
1495: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1496: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1497: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1498: ELSE
1499: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX11'||
1500: ' FROM HZ_STAGED_PARTY_SITES stage'||

Line 1497: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1493: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1494: ' and ('||p_restrict_sql1|| ') )' ||
1495: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1496: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1497: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1498: ELSE
1499: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX11'||
1500: ' FROM HZ_STAGED_PARTY_SITES stage'||
1501: ' WHERE contains( concat_col, :cont)>0'||

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

1497: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1498: ELSE
1499: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX11'||
1500: ' FROM HZ_STAGED_PARTY_SITES stage'||
1501: ' WHERE contains( concat_col, :cont)>0'||
1502: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1503: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1504: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1505: ' AND EXISTS ('||

Line 1511: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1507: ' WHERE p.party_id = stage.party_id ' ||
1508: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1509: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1510: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1511: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1512: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1513: ,p_dup_party_id, p_dup_party_id;
1514: END IF;
1515: ELSE

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

1514: END IF;
1515: ELSE
1516: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX11'||
1517: ' FROM HZ_STAGED_PARTY_SITES stage'||
1518: ' WHERE contains( concat_col, :cont)>0'||
1519: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1520: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1521: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1522: ' AND EXISTS ('||

Line 1528: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1524: ' WHERE p.party_id = stage.party_id ' ||
1525: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1526: ' AND ('||p_restrict_sql||')' ||
1527: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1528: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1529: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1530: ,p_dup_party_id, p_dup_party_id;
1531: END IF;
1532: END IF;

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

1530: ,p_dup_party_id, p_dup_party_id;
1531: END IF;
1532: END IF;
1533: output_long_strings('----------------------------------------------------------');
1534: output_long_strings('Party Site Contains String = '||p_contains_str);
1535: output_long_strings('Restrict Sql = '||p_restrict_sql);
1536: exception
1537: when others then
1538: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1562: p_contains_str VARCHAR2,

1558: PROCEDURE open_contact_cursor(
1559: p_dup_party_id NUMBER,
1560: p_party_id NUMBER,
1561: p_restrict_sql VARCHAR2,
1562: p_contains_str VARCHAR2,
1563: p_search_ctx_id NUMBER,
1564: p_search_merged VARCHAR2,
1565: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1566: l_sqlstr VARCHAR2(4000);

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

1586: END IF;
1587: OPEN x_cursor FOR
1588: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
1589: FROM HZ_STAGED_CONTACTS stage
1590: WHERE contains( concat_col, p_contains_str)>0
1591: AND EXISTS (
1592: SELECT 1 FROM HZ_STAGED_PARTIES p
1593: WHERE p.PARTY_ID = stage.PARTY_ID
1594: AND( (l_search_merged ='Y' )

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

1609: END IF;
1610: OPEN x_cursor FOR
1611: SELECT ORG_CONTACT_ID, PARTY_ID
1612: FROM HZ_STAGED_CONTACTS stage
1613: WHERE contains( concat_col, p_contains_str)>0
1614: AND EXISTS (
1615: SELECT 1 FROM HZ_STAGED_PARTIES p
1616: WHERE p.PARTY_ID = stage.PARTY_ID
1617: AND( (l_search_merged ='Y' )

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

1628: END IF;
1629: OPEN x_cursor FOR
1630: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
1631: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1632: WHERE contains( concat_col, p_contains_str)>0
1633: AND d.search_context_id = p_search_ctx_id
1634: AND d.party_id = stage.party_id
1635: AND( (l_search_merged ='Y' )
1636: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1662: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
1663: END IF;
1664: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
1665: ' FROM HZ_STAGED_CONTACTS stage'||
1666: ' WHERE contains( concat_col, :cont)>0'||
1667: ' AND EXISTS ('||
1668: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1669: ' WHERE p.party_id = stage.party_id ' ||
1670: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1676: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1672: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1673: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1674: ' AND ('||p_restrict_sql||')' ||
1675: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1676: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1677: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1678: ,p_dup_party_id, p_dup_party_id;
1679: END IF;
1680: output_long_strings('----------------------------------------------------------');

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

1677: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1678: ,p_dup_party_id, p_dup_party_id;
1679: END IF;
1680: output_long_strings('----------------------------------------------------------');
1681: output_long_strings('Contacts Contains String = '||p_contains_str);
1682: output_long_strings('Restrict Sql = '||p_restrict_sql);
1683: exception
1684: when others then
1685: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1709: p_contains_str VARCHAR2,

1705: PROCEDURE open_contact_pt_cursor(
1706: p_dup_party_id NUMBER,
1707: p_party_id NUMBER,
1708: p_restrict_sql VARCHAR2,
1709: p_contains_str VARCHAR2,
1710: p_search_ctx_id NUMBER,
1711: p_search_merged VARCHAR2,
1712: p_search_rel_cpts VARCHAR2,
1713: p_person_api VARCHAR2,

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

1735: THEN
1736: OPEN x_cursor FOR
1737: 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 , TX2, TX6
1738: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1739: WHERE contains( concat_col, p_contains_str)>0
1740: AND d.search_context_id = p_search_ctx_id
1741: AND( (l_search_merged ='Y' )
1742: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1743: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1747: THEN
1748: OPEN x_cursor FOR
1749: 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 , TX2, TX6
1750: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1751: WHERE contains( concat_col, p_contains_str)>0
1752: AND d.search_context_id = p_search_ctx_id
1753: AND( (l_search_merged ='Y' )
1754: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1755: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1764: IF p_search_rel_cpts = 'N' THEN
1765: OPEN x_cursor FOR
1766: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6
1767: FROM HZ_STAGED_CONTACT_POINTS stage
1768: WHERE contains( concat_col, p_contains_str)>0
1769: AND( (l_search_merged ='Y' )
1770: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1771: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1772: AND stage.party_id = p_party_id;

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

1773: ELSE
1774: OPEN x_cursor FOR
1775: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6
1776: FROM HZ_STAGED_CONTACT_POINTS stage
1777: WHERE contains( concat_col, p_contains_str)>0
1778: AND( (l_search_merged ='Y' )
1779: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1780: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1781: AND stage.party_id = p_party_id

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

1781: AND stage.party_id = p_party_id
1782: UNION
1783: 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 , TX2, TX6
1784: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1785: WHERE contains( concat_col, p_contains_str)>0
1786: AND r.object_id = p_party_id
1787: AND( (l_search_merged ='Y' )
1788: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1789: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1803: END IF;
1804: OPEN x_cursor FOR
1805: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6
1806: FROM HZ_STAGED_CONTACT_POINTS stage
1807: WHERE contains( concat_col, p_contains_str)>0
1808: AND EXISTS (
1809: SELECT 1 FROM HZ_STAGED_PARTIES p
1810: WHERE p.PARTY_ID = stage.PARTY_ID
1811: AND( (l_search_merged ='Y' )

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

1823: IF p_person_api = 'Y' THEN
1824: OPEN x_cursor FOR
1825: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6
1826: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1827: WHERE contains( concat_col, p_contains_str)>0
1828: AND d.search_context_id = p_search_ctx_id
1829: AND d.party_id = stage.party_id
1830: AND( (l_search_merged ='Y' )
1831: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1833: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1834: UNION
1835: 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 , TX2, TX6
1836: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1837: WHERE contains( concat_col, p_contains_str)>0
1838: AND d.search_context_id = p_search_ctx_id
1839: AND d.party_id = r.subject_id
1840: AND r.relationship_id = oc.party_relationship_id
1841: AND oc.org_contact_id = stage.org_contact_id

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

1846: ELSE
1847: OPEN x_cursor FOR
1848: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6
1849: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1850: WHERE contains( concat_col, p_contains_str)>0
1851: AND d.search_context_id = p_search_ctx_id
1852: AND d.party_id = stage.party_id
1853: AND( (l_search_merged ='Y' )
1854: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1891: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1892: END IF;
1893: 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 , TX2, TX6'||
1894: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1895: ' WHERE contains( concat_col, :cont)>0 '||
1896: ' AND (stage.org_contact_id is null '||
1897: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1898: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1899: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

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

1901: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1902: ' UNION ' ||
1903: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6'||
1904: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1905: ' WHERE contains( concat_col, :cont)>0 '||
1906: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1907: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1908: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1909: ' AND (stage.org_contact_id in ' ||

Line 1915: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1911: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1912: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1913: ' and ('||p_restrict_sql1 || ') ) )' ||
1914: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1915: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1916: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1917: ELSE
1918: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6'||
1919: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

Line 1916: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1912: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1913: ' and ('||p_restrict_sql1 || ') ) )' ||
1914: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1915: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1916: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1917: ELSE
1918: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6'||
1919: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1920: ' WHERE contains( concat_col, :cont)>0'||

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

1916: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1917: ELSE
1918: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6'||
1919: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1920: ' WHERE contains( concat_col, :cont)>0'||
1921: ' AND EXISTS ('||
1922: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1923: ' WHERE p.party_id = stage.party_id ' ||
1924: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1930: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1926: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1927: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1928: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1929: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1930: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1931: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1932: ,p_dup_party_id, p_dup_party_id;
1933: END IF;
1934: ELSE

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

1933: END IF;
1934: ELSE
1935: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX2, TX6'||
1936: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1937: ' WHERE contains( concat_col, :cont)>0'||
1938: ' AND EXISTS ('||
1939: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1940: ' WHERE p.party_id = stage.party_id ' ||
1941: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1947: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1943: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1944: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1945: ' AND ('||p_restrict_sql||')' ||
1946: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1947: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1948: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1949: ,p_dup_party_id, p_dup_party_id;
1950: END IF;
1951: END IF;

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

1950: END IF;
1951: END IF;
1952: END IF;
1953: output_long_strings('----------------------------------------------------------');
1954: output_long_strings('Contact Points Contains String = '||p_contains_str);
1955: output_long_strings('Restrict Sql = '||p_restrict_sql);
1956: exception
1957: when others then
1958: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 2075: l_party_contains_str VARCHAR2(32000);

2071: x_num_matches OUT NUMBER
2072: ) IS
2073:
2074: -- Strings to hold the generated Intermedia query strings
2075: l_party_contains_str VARCHAR2(32000);
2076: l_party_site_contains_str VARCHAR2(32000);
2077: l_contact_contains_str VARCHAR2(32000);
2078: l_contact_pt_contains_str VARCHAR2(32000);
2079: l_denorm_str VARCHAR2(32000);

Line 2076: l_party_site_contains_str VARCHAR2(32000);

2072: ) IS
2073:
2074: -- Strings to hold the generated Intermedia query strings
2075: l_party_contains_str VARCHAR2(32000);
2076: l_party_site_contains_str VARCHAR2(32000);
2077: l_contact_contains_str VARCHAR2(32000);
2078: l_contact_pt_contains_str VARCHAR2(32000);
2079: l_denorm_str VARCHAR2(32000);
2080: l_ps_denorm_str VARCHAR2(32000);

Line 2077: l_contact_contains_str VARCHAR2(32000);

2073:
2074: -- Strings to hold the generated Intermedia query strings
2075: l_party_contains_str VARCHAR2(32000);
2076: l_party_site_contains_str VARCHAR2(32000);
2077: l_contact_contains_str VARCHAR2(32000);
2078: l_contact_pt_contains_str VARCHAR2(32000);
2079: l_denorm_str VARCHAR2(32000);
2080: l_ps_denorm_str VARCHAR2(32000);
2081: l_ct_denorm_str VARCHAR2(32000);

Line 2078: l_contact_pt_contains_str VARCHAR2(32000);

2074: -- Strings to hold the generated Intermedia query strings
2075: l_party_contains_str VARCHAR2(32000);
2076: l_party_site_contains_str VARCHAR2(32000);
2077: l_contact_contains_str VARCHAR2(32000);
2078: l_contact_pt_contains_str VARCHAR2(32000);
2079: l_denorm_str VARCHAR2(32000);
2080: l_ps_denorm_str VARCHAR2(32000);
2081: l_ct_denorm_str VARCHAR2(32000);
2082: l_cpt_denorm_str VARCHAR2(32000);

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

2163: call_type(2) := 'OR';
2164: END;
2165:
2166: /** Private procedure to acquire and score at party level ***/
2167: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2168: l_party_id_idx NUMBER:=1;
2169: l_ctx_id NUMBER;
2170: l_precalc_score BOOLEAN := FALSE;
2171: l_TX35_new varchar2(4000);

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

2195: l_search_ctx_id,H_PARTY_ID(I));
2196: l_party_id_idx := H_PARTY_ID.COUNT+1;
2197: ELSE
2198: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2199: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2200: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2201: 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);
2202: END IF;
2203: 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 2207: IF (l_party_site_contains_str IS NULL AND

2203: 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
2204: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2205: 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);
2206: END IF;
2207: IF (l_party_site_contains_str IS NULL AND
2208: l_contact_contains_str IS NULL AND
2209: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2210: 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);
2211: RAISE L_RETURN_IMM_EXC;

Line 2208: l_contact_contains_str IS NULL AND

2204: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2205: 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);
2206: END IF;
2207: IF (l_party_site_contains_str IS NULL AND
2208: l_contact_contains_str IS NULL AND
2209: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2210: 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);
2211: RAISE L_RETURN_IMM_EXC;
2212: ELSE

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

2205: 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);
2206: END IF;
2207: IF (l_party_site_contains_str IS NULL AND
2208: l_contact_contains_str IS NULL AND
2209: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2210: 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);
2211: RAISE L_RETURN_IMM_EXC;
2212: ELSE
2213: 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 2213: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);

2209: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2210: 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);
2211: RAISE L_RETURN_IMM_EXC;
2212: ELSE
2213: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2214: END IF;
2215: ELSE
2216: IF p_index>1 THEN
2217: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

2232: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2233: 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);
2234: END IF;
2235: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2236: 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);
2237: END IF;
2238: LOOP
2239: FETCH l_party_cur INTO
2240: l_party_id , l_TX4;

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

2254: l_party_id_idx:= l_party_id_idx+1;
2255: END IF;
2256: IF (l_party_id_idx-1)>l_max_thresh THEN
2257: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2258: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2259: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2260: AND l_contact_pt_contains_str IS NULL THEN
2261: EXIT;
2262: END IF;

Line 2260: AND l_contact_pt_contains_str IS NULL THEN

2256: IF (l_party_id_idx-1)>l_max_thresh THEN
2257: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2258: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2259: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2260: AND l_contact_pt_contains_str IS NULL THEN
2261: EXIT;
2262: END IF;
2263: CLOSE l_party_cur;
2264: IF p_index>1 THEN

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

2285: ROLLBACK to eval_start;
2286: END;
2287:
2288: /** Private procedure to acquire and score at party site level ***/
2289: 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
2290: l_party_id_idx NUMBER:=1;
2291: l_ctx_id NUMBER;
2292: h_ps_id HZ_PARTY_SEARCH.IDList;
2293: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

2297: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2298: 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);
2299: 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);
2300: END IF;
2301: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2302: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2303: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2304: l_party_id := H_SCORES.FIRST;
2305: WHILE l_party_id IS NOT NULL LOOP

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

2298: 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);
2299: 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);
2300: END IF;
2301: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2302: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2303: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2304: l_party_id := H_SCORES.FIRST;
2305: WHILE l_party_id IS NOT NULL LOOP
2306: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;

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

2299: 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);
2300: END IF;
2301: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2302: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2303: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2304: l_party_id := H_SCORES.FIRST;
2305: WHILE l_party_id IS NOT NULL LOOP
2306: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2307: l_party_id:=H_SCORES.NEXT(l_party_id);

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

2329: l_search_ctx_id,H_PARTY_ID(I));
2330: l_party_id_idx := H_PARTY_ID.COUNT+1;
2331: ELSE
2332: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2333: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2334: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2335: 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);
2336: END IF;
2337: IF p_index>1 THEN

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

2351: END IF;
2352: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2353: 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);
2354: END IF;
2355: 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);
2356: LOOP
2357: FETCH l_party_site_cur INTO
2358: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX11;
2359: EXIT WHEN l_party_site_cur%NOTFOUND;

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

2407: END IF;
2408: END;
2409:
2410: /** Private procedure to acquire and score at contact point level ***/
2411: 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
2412: l_party_id_idx NUMBER:=1;
2413: l_ctx_id NUMBER;
2414: h_ct_id HZ_PARTY_SEARCH.IDList;
2415: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

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

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

2463: END IF;
2464: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2465: 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);
2466: END IF;
2467: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2468: LOOP
2469: FETCH l_contact_cur INTO
2470: l_org_contact_id, l_ct_party_id ;
2471: EXIT WHEN l_contact_cur%NOTFOUND;

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

2516: END IF;
2517: END;
2518:
2519: /** Private procedure to acquire and score at contact point level ***/
2520: 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
2521: l_party_id_idx NUMBER:=1;
2522: l_ctx_id NUMBER;
2523: h_cpt_id HZ_PARTY_SEARCH.IDList;
2524: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

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

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

2574: END IF;
2575: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2576: 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);
2577: END IF;
2578: 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);
2579: LOOP
2580: FETCH l_contact_pt_cur INTO
2581: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX2, l_TX6;
2582: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 2759: IF l_party_contains_str IS NOT NULL THEN

2755: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
2756: l_threshold := 65;
2757: other_acq_criteria_exists := TRUE ;
2758: --check if acquisition criteria exists for any other entity
2759: IF l_party_contains_str IS NOT NULL THEN
2760: acq_cnt := 1;
2761: END IF;
2762: IF l_party_site_contains_str IS NOT NULL THEN
2763: acq_cnt := acq_cnt+1;

Line 2762: IF l_party_site_contains_str IS NOT NULL THEN

2758: --check if acquisition criteria exists for any other entity
2759: IF l_party_contains_str IS NOT NULL THEN
2760: acq_cnt := 1;
2761: END IF;
2762: IF l_party_site_contains_str IS NOT NULL THEN
2763: acq_cnt := acq_cnt+1;
2764: END IF;
2765: IF l_contact_contains_str IS NOT NULL THEN
2766: acq_cnt := acq_cnt+1;

Line 2765: IF l_contact_contains_str IS NOT NULL THEN

2761: END IF;
2762: IF l_party_site_contains_str IS NOT NULL THEN
2763: acq_cnt := acq_cnt+1;
2764: END IF;
2765: IF l_contact_contains_str IS NOT NULL THEN
2766: acq_cnt := acq_cnt+1;
2767: END IF;
2768: IF l_contact_pt_contains_str IS NOT NULL THEN
2769: acq_cnt := acq_cnt+1;

Line 2768: IF l_contact_pt_contains_str IS NOT NULL THEN

2764: END IF;
2765: IF l_contact_contains_str IS NOT NULL THEN
2766: acq_cnt := acq_cnt+1;
2767: END IF;
2768: IF l_contact_pt_contains_str IS NOT NULL THEN
2769: acq_cnt := acq_cnt+1;
2770: END IF;
2771: IF acq_cnt>1 THEN
2772: other_acq_criteria_exists := TRUE;

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

2798: FND_MSG_PUB.ADD;
2799: RAISE FND_API.G_EXC_ERROR;
2800: END IF;
2801: END IF;
2802: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2803: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2804: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2805: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2806: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN

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

2799: RAISE FND_API.G_EXC_ERROR;
2800: END IF;
2801: END IF;
2802: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2803: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2804: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2805: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2806: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2807: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

2800: END IF;
2801: END IF;
2802: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2803: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2804: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2805: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2806: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2807: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2808: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

2801: END IF;
2802: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2803: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2804: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2805: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2806: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2807: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2808: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2809: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

2802: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2803: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2804: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2805: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2806: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2807: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2808: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2809: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2810: END IF;

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

2803: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2804: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2805: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2806: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2807: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2808: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2809: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2810: END IF;
2811: END;

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

2804: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2805: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2806: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2807: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2808: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2809: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2810: END IF;
2811: END;
2812: /************ End of find_parties private procedures **********/

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

2805: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2806: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2807: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2808: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2809: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2810: END IF;
2811: END;
2812: /************ End of find_parties private procedures **********/
2813:

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

2877: 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);
2878: END IF;
2879: -- ************************************************************
2880: -- STEP 2. Setup of intermedia query strings for Acquisition query
2881: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2882: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2883: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2884: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2885: 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 2882: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);

2878: END IF;
2879: -- ************************************************************
2880: -- STEP 2. Setup of intermedia query strings for Acquisition query
2881: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2882: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2883: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2884: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2885: 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);
2886: END IF;

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

2879: -- ************************************************************
2880: -- STEP 2. Setup of intermedia query strings for Acquisition query
2881: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2882: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2883: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2884: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2885: 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);
2886: END IF;
2887: l_denorm_max_score:=0;

Line 2915: 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((65/100)*l_entered_max_score));

2911: END IF;
2912: ELSE
2913: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
2914: END IF;
2915: 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((65/100)*l_entered_max_score));
2916: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
2917:
2918: -- Setup Search Context ID
2919: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 2925: IF l_party_contains_str IS NULL THEN

2921:
2922: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2923: 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);
2924: END IF;
2925: IF l_party_contains_str IS NULL THEN
2926: defpt := 1;
2927: END IF;
2928: IF l_party_site_contains_str IS NULL THEN
2929: defps := 1;

Line 2928: IF l_party_site_contains_str IS NULL THEN

2924: END IF;
2925: IF l_party_contains_str IS NULL THEN
2926: defpt := 1;
2927: END IF;
2928: IF l_party_site_contains_str IS NULL THEN
2929: defps := 1;
2930: END IF;
2931: IF l_contact_contains_str IS NULL THEN
2932: defct := 1;

Line 2931: IF l_contact_contains_str IS NULL THEN

2927: END IF;
2928: IF l_party_site_contains_str IS NULL THEN
2929: defps := 1;
2930: END IF;
2931: IF l_contact_contains_str IS NULL THEN
2932: defct := 1;
2933: END IF;
2934: IF l_contact_pt_contains_str IS NULL THEN
2935: defcpt := 1;

Line 2934: IF l_contact_pt_contains_str IS NULL THEN

2930: END IF;
2931: IF l_contact_contains_str IS NULL THEN
2932: defct := 1;
2933: END IF;
2934: IF l_contact_pt_contains_str IS NULL THEN
2935: defcpt := 1;
2936: END IF;
2937:
2938: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

2939: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2940: 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);
2941: END IF;
2942: 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);
2943: 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);
2944: 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);
2945: 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);
2946: 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);
2947: 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 2944: 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);

2940: 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);
2941: END IF;
2942: 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);
2943: 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);
2944: 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);
2945: 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);
2946: 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);
2947: 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);
2948: END IF;

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

2941: END IF;
2942: 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);
2943: 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);
2944: 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);
2945: 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);
2946: 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);
2947: 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);
2948: END IF;
2949: FOR I in 1..5 LOOP

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

2942: 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);
2943: 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);
2944: 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);
2945: 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);
2946: 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);
2947: 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);
2948: END IF;
2949: FOR I in 1..5 LOOP
2950: do_eval(I);

Line 3028: l_party_contains_str VARCHAR2(32000);

3024: x_num_matches OUT NUMBER
3025: ) IS
3026:
3027: -- Strings to hold the generated Intermedia query strings
3028: l_party_contains_str VARCHAR2(32000);
3029: l_party_site_contains_str VARCHAR2(32000);
3030: l_contact_contains_str VARCHAR2(32000);
3031: l_contact_pt_contains_str VARCHAR2(32000);
3032: l_denorm_str VARCHAR2(32000);

Line 3029: l_party_site_contains_str VARCHAR2(32000);

3025: ) IS
3026:
3027: -- Strings to hold the generated Intermedia query strings
3028: l_party_contains_str VARCHAR2(32000);
3029: l_party_site_contains_str VARCHAR2(32000);
3030: l_contact_contains_str VARCHAR2(32000);
3031: l_contact_pt_contains_str VARCHAR2(32000);
3032: l_denorm_str VARCHAR2(32000);
3033: l_ps_denorm_str VARCHAR2(32000);

Line 3030: l_contact_contains_str VARCHAR2(32000);

3026:
3027: -- Strings to hold the generated Intermedia query strings
3028: l_party_contains_str VARCHAR2(32000);
3029: l_party_site_contains_str VARCHAR2(32000);
3030: l_contact_contains_str VARCHAR2(32000);
3031: l_contact_pt_contains_str VARCHAR2(32000);
3032: l_denorm_str VARCHAR2(32000);
3033: l_ps_denorm_str VARCHAR2(32000);
3034: l_ct_denorm_str VARCHAR2(32000);

Line 3031: l_contact_pt_contains_str VARCHAR2(32000);

3027: -- Strings to hold the generated Intermedia query strings
3028: l_party_contains_str VARCHAR2(32000);
3029: l_party_site_contains_str VARCHAR2(32000);
3030: l_contact_contains_str VARCHAR2(32000);
3031: l_contact_pt_contains_str VARCHAR2(32000);
3032: l_denorm_str VARCHAR2(32000);
3033: l_ps_denorm_str VARCHAR2(32000);
3034: l_ct_denorm_str VARCHAR2(32000);
3035: l_cpt_denorm_str VARCHAR2(32000);

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

3146: call_type(2) := 'OR';
3147: END;
3148:
3149: /** Private procedure to acquire and score at party level ***/
3150: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3151: l_party_id_idx NUMBER:=1;
3152: l_ctx_id NUMBER;
3153: l_TX35_new varchar2(4000);
3154: BEGIN

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

3168: l_search_ctx_id,H_PARTY_ID(I));
3169: l_party_id_idx := H_PARTY_ID.COUNT+1;
3170: ELSE
3171: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3172: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3173: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3174: 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);
3175: END IF;
3176: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN

Line 3180: IF (l_party_site_contains_str IS NULL AND

3176: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3177: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3178: 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);
3179: END IF;
3180: IF (l_party_site_contains_str IS NULL AND
3181: l_contact_contains_str IS NULL AND
3182: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3183: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3184: RAISE L_RETURN_IMM_EXC;

Line 3181: l_contact_contains_str IS NULL AND

3177: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3178: 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);
3179: END IF;
3180: IF (l_party_site_contains_str IS NULL AND
3181: l_contact_contains_str IS NULL AND
3182: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3183: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3184: RAISE L_RETURN_IMM_EXC;
3185: ELSE

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

3178: 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);
3179: END IF;
3180: IF (l_party_site_contains_str IS NULL AND
3181: l_contact_contains_str IS NULL AND
3182: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3183: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3184: RAISE L_RETURN_IMM_EXC;
3185: ELSE
3186: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);

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

3182: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3183: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3184: RAISE L_RETURN_IMM_EXC;
3185: ELSE
3186: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3187: END IF;
3188: ELSE
3189: IF p_index>1 THEN
3190: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3202: l_ctx_id := NULL;
3203: l_party_id_idx := H_PARTY_ID.COUNT+1;
3204: END IF;
3205: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3206: 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);
3207: END IF;
3208: LOOP
3209: FETCH l_party_cur INTO
3210: l_party_id , l_TX4;

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

3224: l_party_id_idx:= l_party_id_idx+1;
3225: END IF;
3226: IF (l_party_id_idx-1)>l_max_thresh THEN
3227: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3228: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3229: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3230: AND l_contact_pt_contains_str IS NULL THEN
3231: H_PARTY_ID.DELETE(l_party_id_idx-1);
3232: H_PARTY_ID_LIST.DELETE(l_index);

Line 3230: AND l_contact_pt_contains_str IS NULL THEN

3226: IF (l_party_id_idx-1)>l_max_thresh THEN
3227: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3228: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3229: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3230: AND l_contact_pt_contains_str IS NULL THEN
3231: H_PARTY_ID.DELETE(l_party_id_idx-1);
3232: H_PARTY_ID_LIST.DELETE(l_index);
3233: H_SCORES.DELETE(l_index);
3234: EXIT;

Line 3259: p_contains_str VARCHAR2,

3255: CLOSE l_party_cur;
3256: ROLLBACK to eval_start;
3257: END;
3258: PROCEDURE open_person_contact_cursor(
3259: p_contains_str VARCHAR2,
3260: p_search_ctx_id NUMBER,
3261: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3262: BEGIN
3263: OPEN x_cursor FOR

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

3262: BEGIN
3263: OPEN x_cursor FOR
3264: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
3265: FROM HZ_STAGED_CONTACTS stage
3266: WHERE contains( concat_col, p_contains_str)>0
3267: AND ORG_CONTACT_ID in (
3268: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3269: org_contact_id
3270: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc

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

3286: end if;
3287: END;
3288:
3289: /** Private procedure to acquire and score at party site level ***/
3290: 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
3291: l_party_id_idx NUMBER:=1;
3292: l_ctx_id NUMBER;
3293: h_ps_id HZ_PARTY_SEARCH.IDList;
3294: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

3314: ELSE
3315: l_party_id_idx := H_PARTY_ID.COUNT+1;
3316: l_ctx_id := NULL;
3317: END IF;
3318: 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);
3319: LOOP
3320: FETCH l_party_site_cur INTO
3321: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX11;
3322: EXIT WHEN l_party_site_cur%NOTFOUND;

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

3385: END IF;
3386: END;
3387:
3388: /** Private procedure to acquire and score at party site level ***/
3389: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3390: l_party_id_idx NUMBER:=1;
3391: l_ctx_id NUMBER;
3392: h_ct_id HZ_PARTY_SEARCH.IDList;
3393: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

3401: FORALL I in 1..H_PARTY_ID.COUNT
3402: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
3403: l_search_ctx_id,H_PARTY_ID(I));
3404: l_party_id_idx := H_PARTY_ID.COUNT+1;
3405: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
3406: LOOP
3407: FETCH l_contact_cur INTO
3408: l_org_contact_id, l_ct_party_id ;
3409: EXIT WHEN l_contact_cur%NOTFOUND;

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

3443: END IF;
3444: END;
3445:
3446: /** Private procedure to acquire and score at contact point level ***/
3447: 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
3448: l_party_id_idx NUMBER:=1;
3449: l_ctx_id NUMBER;
3450: h_cpt_id HZ_PARTY_SEARCH.IDList;
3451: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

3488: END IF;
3489: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3490: 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);
3491: END IF;
3492: 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);
3493: LOOP
3494: FETCH l_contact_pt_cur INTO
3495: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX2, l_TX6;
3496: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 3695: IF l_party_contains_str IS NOT NULL THEN

3691: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3692: l_threshold := 65;
3693: other_acq_criteria_exists := TRUE ;
3694: --check if acquisition criteria exists for any other entity
3695: IF l_party_contains_str IS NOT NULL THEN
3696: acq_cnt := 1;
3697: END IF;
3698: IF l_party_site_contains_str IS NOT NULL THEN
3699: acq_cnt := acq_cnt+1;

Line 3698: IF l_party_site_contains_str IS NOT NULL THEN

3694: --check if acquisition criteria exists for any other entity
3695: IF l_party_contains_str IS NOT NULL THEN
3696: acq_cnt := 1;
3697: END IF;
3698: IF l_party_site_contains_str IS NOT NULL THEN
3699: acq_cnt := acq_cnt+1;
3700: END IF;
3701: IF l_contact_contains_str IS NOT NULL THEN
3702: acq_cnt := acq_cnt+1;

Line 3701: IF l_contact_contains_str IS NOT NULL THEN

3697: END IF;
3698: IF l_party_site_contains_str IS NOT NULL THEN
3699: acq_cnt := acq_cnt+1;
3700: END IF;
3701: IF l_contact_contains_str IS NOT NULL THEN
3702: acq_cnt := acq_cnt+1;
3703: END IF;
3704: IF l_contact_pt_contains_str IS NOT NULL THEN
3705: acq_cnt := acq_cnt+1;

Line 3704: IF l_contact_pt_contains_str IS NOT NULL THEN

3700: END IF;
3701: IF l_contact_contains_str IS NOT NULL THEN
3702: acq_cnt := acq_cnt+1;
3703: END IF;
3704: IF l_contact_pt_contains_str IS NOT NULL THEN
3705: acq_cnt := acq_cnt+1;
3706: END IF;
3707: IF acq_cnt>1 THEN
3708: other_acq_criteria_exists := TRUE;

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

3734: FND_MSG_PUB.ADD;
3735: RAISE FND_API.G_EXC_ERROR;
3736: END IF;
3737: END IF;
3738: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3739: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3740: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3741: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3742: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

3735: RAISE FND_API.G_EXC_ERROR;
3736: END IF;
3737: END IF;
3738: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3739: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3740: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3741: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3742: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3743: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

3736: END IF;
3737: END IF;
3738: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3739: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3740: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3741: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3742: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3743: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3744: END IF;

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

3737: END IF;
3738: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3739: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3740: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3741: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3742: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3743: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3744: END IF;
3745: END;

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

3738: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3739: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3740: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3741: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3742: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3743: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3744: END IF;
3745: END;
3746: /************ End of find_persons private procedures **********/

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

3739: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3740: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3741: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3742: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3743: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3744: END IF;
3745: END;
3746: /************ End of find_persons private procedures **********/
3747:

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

3774: g_score_until_thresh := false;
3775: END IF;
3776: -- ************************************************************
3777: -- STEP 2. Setup of intermedia query strings for Acquisition query
3778: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3779: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3780: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3781: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3782: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);

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

3775: END IF;
3776: -- ************************************************************
3777: -- STEP 2. Setup of intermedia query strings for Acquisition query
3778: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3779: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3780: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3781: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3782: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3783:

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

3776: -- ************************************************************
3777: -- STEP 2. Setup of intermedia query strings for Acquisition query
3778: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3779: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3780: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3781: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3782: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3783:
3784: -- Setup Search Context ID

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

3777: -- STEP 2. Setup of intermedia query strings for Acquisition query
3778: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3779: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3780: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3781: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3782: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3783:
3784: -- Setup Search Context ID
3785: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 3788: IF l_party_contains_str IS NULL THEN

3784: -- Setup Search Context ID
3785: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3786: x_search_ctx_id := l_search_ctx_id;
3787:
3788: IF l_party_contains_str IS NULL THEN
3789: defpt := 1;
3790: END IF;
3791: IF l_party_site_contains_str IS NULL THEN
3792: defps := 1;

Line 3791: IF l_party_site_contains_str IS NULL THEN

3787:
3788: IF l_party_contains_str IS NULL THEN
3789: defpt := 1;
3790: END IF;
3791: IF l_party_site_contains_str IS NULL THEN
3792: defps := 1;
3793: END IF;
3794: IF l_contact_contains_str IS NULL THEN
3795: defct := 1;

Line 3794: IF l_contact_contains_str IS NULL THEN

3790: END IF;
3791: IF l_party_site_contains_str IS NULL THEN
3792: defps := 1;
3793: END IF;
3794: IF l_contact_contains_str IS NULL THEN
3795: defct := 1;
3796: END IF;
3797: IF l_contact_pt_contains_str IS NULL THEN
3798: defcpt := 1;

Line 3797: IF l_contact_pt_contains_str IS NULL THEN

3793: END IF;
3794: IF l_contact_contains_str IS NULL THEN
3795: defct := 1;
3796: END IF;
3797: IF l_contact_pt_contains_str IS NULL THEN
3798: defcpt := 1;
3799: END IF;
3800:
3801: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3799: END IF;
3800:
3801: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3802: 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);
3803: 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);
3804: 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);
3805: 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);
3806: 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);
3807: 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 3804: 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);

3800:
3801: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3802: 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);
3803: 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);
3804: 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);
3805: 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);
3806: 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);
3807: 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);
3808: END IF;

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

3801: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3802: 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);
3803: 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);
3804: 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);
3805: 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);
3806: 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);
3807: 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);
3808: END IF;
3809: FOR I in 1..5 LOOP

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

3802: 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);
3803: 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);
3804: 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);
3805: 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);
3806: 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);
3807: 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);
3808: END IF;
3809: FOR I in 1..5 LOOP
3810: do_eval(I);

Line 3812: IF l_contact_contains_str IS NOT NULL THEN

3808: END IF;
3809: FOR I in 1..5 LOOP
3810: do_eval(I);
3811: END LOOP;
3812: IF l_contact_contains_str IS NOT NULL THEN
3813: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3814: END IF;
3815: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3816: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 65 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

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

3809: FOR I in 1..5 LOOP
3810: do_eval(I);
3811: END LOOP;
3812: IF l_contact_contains_str IS NOT NULL THEN
3813: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3814: END IF;
3815: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3816: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 65 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3817: END IF;

Line 4608: l_party_contains_str VARCHAR2(32000);

4604: x_num_matches OUT NUMBER
4605: ) IS
4606:
4607: -- Strings to hold the generated Intermedia query strings
4608: l_party_contains_str VARCHAR2(32000);
4609: l_match_idx NUMBER;
4610: l_party_site_contains_str VARCHAR2(32000);
4611: l_contact_contains_str VARCHAR2(32000);
4612: l_contact_pt_contains_str VARCHAR2(32000);

Line 4610: l_party_site_contains_str VARCHAR2(32000);

4606:
4607: -- Strings to hold the generated Intermedia query strings
4608: l_party_contains_str VARCHAR2(32000);
4609: l_match_idx NUMBER;
4610: l_party_site_contains_str VARCHAR2(32000);
4611: l_contact_contains_str VARCHAR2(32000);
4612: l_contact_pt_contains_str VARCHAR2(32000);
4613: l_tmp VARCHAR2(32000);
4614:

Line 4611: l_contact_contains_str VARCHAR2(32000);

4607: -- Strings to hold the generated Intermedia query strings
4608: l_party_contains_str VARCHAR2(32000);
4609: l_match_idx NUMBER;
4610: l_party_site_contains_str VARCHAR2(32000);
4611: l_contact_contains_str VARCHAR2(32000);
4612: l_contact_pt_contains_str VARCHAR2(32000);
4613: l_tmp VARCHAR2(32000);
4614:
4615: -- Other local variables

Line 4612: l_contact_pt_contains_str VARCHAR2(32000);

4608: l_party_contains_str VARCHAR2(32000);
4609: l_match_idx NUMBER;
4610: l_party_site_contains_str VARCHAR2(32000);
4611: l_contact_contains_str VARCHAR2(32000);
4612: l_contact_pt_contains_str VARCHAR2(32000);
4613: l_tmp VARCHAR2(32000);
4614:
4615: -- Other local variables
4616: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4685: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4686:
4687: -- ************************************************************
4688: -- STEP 2. Setup of intermedia query strings for Acquisition query
4689: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4690: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4691: 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);
4692:
4693: -- Setup Search Context ID

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

4686:
4687: -- ************************************************************
4688: -- STEP 2. Setup of intermedia query strings for Acquisition query
4689: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4690: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4691: 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);
4692:
4693: -- Setup Search Context ID
4694: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4695: x_search_ctx_id := l_search_ctx_id;
4696:
4697: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4698: 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);
4699: 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);
4700: 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);
4701: 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);
4702: END IF;
4703: IF l_party_site_contains_str IS NULL THEN

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

4696:
4697: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4698: 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);
4699: 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);
4700: 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);
4701: 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);
4702: END IF;
4703: IF l_party_site_contains_str IS NULL THEN
4704: defps := 1;

Line 4703: IF l_party_site_contains_str IS NULL THEN

4699: 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);
4700: 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);
4701: 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);
4702: END IF;
4703: IF l_party_site_contains_str IS NULL THEN
4704: defps := 1;
4705: END IF;
4706: IF l_contact_pt_contains_str IS NULL THEN
4707: defcpt := 1;

Line 4706: IF l_contact_pt_contains_str IS NULL THEN

4702: END IF;
4703: IF l_party_site_contains_str IS NULL THEN
4704: defps := 1;
4705: END IF;
4706: IF l_contact_pt_contains_str IS NULL THEN
4707: defcpt := 1;
4708: END IF;
4709:
4710: IF l_party_site_contains_str IS NOT NULL THEN

Line 4710: IF l_party_site_contains_str IS NOT NULL THEN

4706: IF l_contact_pt_contains_str IS NULL THEN
4707: defcpt := 1;
4708: END IF;
4709:
4710: IF l_party_site_contains_str IS NOT NULL THEN
4711: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4712: LOOP
4713: FETCH l_party_site_cur INTO
4714: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX11;

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

4707: defcpt := 1;
4708: END IF;
4709:
4710: IF l_party_site_contains_str IS NOT NULL THEN
4711: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4712: LOOP
4713: FETCH l_party_site_cur INTO
4714: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX11;
4715: EXIT WHEN l_party_site_cur%NOTFOUND;

Line 4734: IF l_contact_pt_contains_str IS NOT NULL THEN

4730: END LOOP;
4731: CLOSE l_party_site_cur;
4732: END IF;
4733:
4734: IF l_contact_pt_contains_str IS NOT NULL THEN
4735: SAVEPOINT eval_start;
4736: IF l_match_str = ' AND ' OR (
4737: ((l_cpt_max_score/l_entered_max_score)<(65/100))
4738: ) THEN

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

4738: ) THEN
4739: FORALL I in 1..l_match_ps_list.COUNT
4740: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4741: values (l_search_ctx_id,l_match_ps_list(I));
4742: 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');
4743: ELSE
4744: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4745: END IF;
4746: LOOP

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

4740: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4741: values (l_search_ctx_id,l_match_ps_list(I));
4742: 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');
4743: ELSE
4744: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4745: END IF;
4746: LOOP
4747: FETCH l_contact_pt_cur INTO
4748: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX2, l_TX6;

Line 4844: l_party_contains_str VARCHAR2(32000);

4840: x_num_matches OUT NUMBER
4841: ) IS
4842:
4843: -- Strings to hold the generated Intermedia query strings
4844: l_party_contains_str VARCHAR2(32000);
4845: l_party_site_contains_str VARCHAR2(32000);
4846: l_contact_contains_str VARCHAR2(32000);
4847: l_contact_pt_contains_str VARCHAR2(32000);
4848: l_tmp VARCHAR2(32000);

Line 4845: l_party_site_contains_str VARCHAR2(32000);

4841: ) IS
4842:
4843: -- Strings to hold the generated Intermedia query strings
4844: l_party_contains_str VARCHAR2(32000);
4845: l_party_site_contains_str VARCHAR2(32000);
4846: l_contact_contains_str VARCHAR2(32000);
4847: l_contact_pt_contains_str VARCHAR2(32000);
4848: l_tmp VARCHAR2(32000);
4849:

Line 4846: l_contact_contains_str VARCHAR2(32000);

4842:
4843: -- Strings to hold the generated Intermedia query strings
4844: l_party_contains_str VARCHAR2(32000);
4845: l_party_site_contains_str VARCHAR2(32000);
4846: l_contact_contains_str VARCHAR2(32000);
4847: l_contact_pt_contains_str VARCHAR2(32000);
4848: l_tmp VARCHAR2(32000);
4849:
4850: -- Other local variables

Line 4847: l_contact_pt_contains_str VARCHAR2(32000);

4843: -- Strings to hold the generated Intermedia query strings
4844: l_party_contains_str VARCHAR2(32000);
4845: l_party_site_contains_str VARCHAR2(32000);
4846: l_contact_contains_str VARCHAR2(32000);
4847: l_contact_pt_contains_str VARCHAR2(32000);
4848: l_tmp VARCHAR2(32000);
4849:
4850: -- Other local variables
4851: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4916: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4917:
4918: -- ************************************************************
4919: -- STEP 2. Setup of intermedia query strings for Acquisition query
4920: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4921: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4922: 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);
4923:
4924: -- Setup Search Context ID

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

4917:
4918: -- ************************************************************
4919: -- STEP 2. Setup of intermedia query strings for Acquisition query
4920: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4921: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4922: 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);
4923:
4924: -- Setup Search Context ID
4925: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4926: x_search_ctx_id := l_search_ctx_id;
4927:
4928: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4929: 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);
4930: 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);
4931: 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);
4932: 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);
4933: END IF;
4934: IF l_contact_contains_str IS NULL THEN

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

4927:
4928: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4929: 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);
4930: 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);
4931: 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);
4932: 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);
4933: END IF;
4934: IF l_contact_contains_str IS NULL THEN
4935: defct := 1;

Line 4934: IF l_contact_contains_str IS NULL THEN

4930: 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);
4931: 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);
4932: 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);
4933: END IF;
4934: IF l_contact_contains_str IS NULL THEN
4935: defct := 1;
4936: END IF;
4937: IF l_contact_pt_contains_str IS NULL THEN
4938: defcpt := 1;

Line 4937: IF l_contact_pt_contains_str IS NULL THEN

4933: END IF;
4934: IF l_contact_contains_str IS NULL THEN
4935: defct := 1;
4936: END IF;
4937: IF l_contact_pt_contains_str IS NULL THEN
4938: defcpt := 1;
4939: END IF;
4940:
4941: IF l_contact_contains_str IS NOT NULL THEN

Line 4941: IF l_contact_contains_str IS NOT NULL THEN

4937: IF l_contact_pt_contains_str IS NULL THEN
4938: defcpt := 1;
4939: END IF;
4940:
4941: IF l_contact_contains_str IS NOT NULL THEN
4942: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
4943: LOOP
4944: FETCH l_contact_cur INTO
4945: l_org_contact_id, l_ct_party_id ;

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

4938: defcpt := 1;
4939: END IF;
4940:
4941: IF l_contact_contains_str IS NOT NULL THEN
4942: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
4943: LOOP
4944: FETCH l_contact_cur INTO
4945: l_org_contact_id, l_ct_party_id ;
4946: EXIT WHEN l_contact_cur%NOTFOUND;

Line 4963: IF l_contact_pt_contains_str IS NOT NULL THEN

4959: END LOOP;
4960: CLOSE l_contact_cur;
4961: END IF;
4962:
4963: IF l_contact_pt_contains_str IS NOT NULL THEN
4964: SAVEPOINT eval_start;
4965: IF l_match_str = ' AND ' OR (
4966: ((l_cpt_max_score/l_entered_max_score)<(65/100))
4967: ) THEN

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

4967: ) THEN
4968: FORALL I in 1..l_match_contact_list.COUNT
4969: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4970: values (l_search_ctx_id,l_match_contact_list(I));
4971: 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');
4972: ELSE
4973: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4974: END IF;
4975: LOOP

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

4969: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4970: values (l_search_ctx_id,l_match_contact_list(I));
4971: 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');
4972: ELSE
4973: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4974: END IF;
4975: LOOP
4976: FETCH l_contact_pt_cur INTO
4977: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX2, l_TX6;

Line 5071: l_contact_pt_contains_str VARCHAR2(32000);

5067: ) IS
5068:
5069:
5070: -- Strings to hold the generated Intermedia query strings
5071: l_contact_pt_contains_str VARCHAR2(32000);
5072: l_tmp VARCHAR2(32000);
5073:
5074: -- Other local variables
5075: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5127: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5128:
5129: -- ************************************************************
5130: -- STEP 2. Setup of intermedia query strings for Acquisition query
5131: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5132: 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);
5133:
5134: -- Setup Search Context ID
5135: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

5135: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5136: x_search_ctx_id := l_search_ctx_id;
5137: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5138: 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);
5139: 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);
5140: 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);
5141: END IF;
5142:
5143: IF l_contact_pt_contains_str IS NOT NULL THEN

Line 5143: IF l_contact_pt_contains_str IS NOT NULL THEN

5139: 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);
5140: 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);
5141: END IF;
5142:
5143: IF l_contact_pt_contains_str IS NOT NULL THEN
5144: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5145: cnt := 1;
5146: LOOP
5147: FETCH l_contact_pt_cur INTO

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

5140: 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);
5141: END IF;
5142:
5143: IF l_contact_pt_contains_str IS NOT NULL THEN
5144: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5145: cnt := 1;
5146: LOOP
5147: FETCH l_contact_pt_cur INTO
5148: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX2, l_TX6;

Line 5215: l_party_contains_str VARCHAR2(32000);

5211: x_search_ctx_id IN OUT NUMBER
5212: ) IS
5213:
5214: -- Strings to hold the generated Intermedia query strings
5215: l_party_contains_str VARCHAR2(32000);
5216: l_party_site_contains_str VARCHAR2(32000);
5217: l_contact_contains_str VARCHAR2(32000);
5218: l_contact_pt_contains_str VARCHAR2(32000);
5219: l_tmp VARCHAR2(32000);

Line 5216: l_party_site_contains_str VARCHAR2(32000);

5212: ) IS
5213:
5214: -- Strings to hold the generated Intermedia query strings
5215: l_party_contains_str VARCHAR2(32000);
5216: l_party_site_contains_str VARCHAR2(32000);
5217: l_contact_contains_str VARCHAR2(32000);
5218: l_contact_pt_contains_str VARCHAR2(32000);
5219: l_tmp VARCHAR2(32000);
5220:

Line 5217: l_contact_contains_str VARCHAR2(32000);

5213:
5214: -- Strings to hold the generated Intermedia query strings
5215: l_party_contains_str VARCHAR2(32000);
5216: l_party_site_contains_str VARCHAR2(32000);
5217: l_contact_contains_str VARCHAR2(32000);
5218: l_contact_pt_contains_str VARCHAR2(32000);
5219: l_tmp VARCHAR2(32000);
5220:
5221: -- Other local variables

Line 5218: l_contact_pt_contains_str VARCHAR2(32000);

5214: -- Strings to hold the generated Intermedia query strings
5215: l_party_contains_str VARCHAR2(32000);
5216: l_party_site_contains_str VARCHAR2(32000);
5217: l_contact_contains_str VARCHAR2(32000);
5218: l_contact_pt_contains_str VARCHAR2(32000);
5219: l_tmp VARCHAR2(32000);
5220:
5221: -- Other local variables
5222: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5280: g_score_until_thresh := false;
5281: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5282: -- ************************************************************
5283: -- STEP 2. Setup of intermedia query strings for Acquisition query
5284: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5285: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5286: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5287: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5288:

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

5281: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5282: -- ************************************************************
5283: -- STEP 2. Setup of intermedia query strings for Acquisition query
5284: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5285: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5286: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5287: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5288:
5289: -- Setup Search Context ID

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

5282: -- ************************************************************
5283: -- STEP 2. Setup of intermedia query strings for Acquisition query
5284: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5285: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5286: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5287: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5288:
5289: -- Setup Search Context ID
5290: IF x_search_ctx_id IS NULL THEN

Line 5306: IF l_party_site_contains_str IS NOT NULL THEN

5302: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX4,1);
5303: END LOOP;
5304: CLOSE l_party_cur;
5305:
5306: IF l_party_site_contains_str IS NOT NULL THEN
5307: l_max_score := 0;
5308: l_max_id := 0;
5309: l_max_idx := 0;
5310: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN

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

5307: l_max_score := 0;
5308: l_max_id := 0;
5309: l_max_idx := 0;
5310: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5311: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5312: ELSE
5313: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5314: END IF;
5315: LOOP

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

5309: l_max_idx := 0;
5310: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5311: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5312: ELSE
5313: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5314: END IF;
5315: LOOP
5316: FETCH l_party_site_cur INTO
5317: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX4, l_TX11;

Line 5334: IF l_contact_contains_str IS NOT NULL THEN

5330: 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_TX4,l_max_TX11,l_max_idx);
5331: END IF;
5332: END IF;
5333:
5334: IF l_contact_contains_str IS NOT NULL THEN
5335: l_max_score := 0;
5336: l_max_id := 0;
5337: l_max_idx := 0;
5338: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

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

5334: IF l_contact_contains_str IS NOT NULL THEN
5335: l_max_score := 0;
5336: l_max_id := 0;
5337: l_max_idx := 0;
5338: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5339: LOOP
5340: FETCH l_contact_cur INTO
5341: l_org_contact_id, l_ct_party_id ;
5342: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5356: IF l_contact_pt_contains_str IS NOT NULL THEN

5352: INSERT_CONTACTS_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_contact_list(l_max_idx), g_contact_stage_list(l_max_idx) ,l_max_idx);
5353: END IF;
5354: END IF;
5355:
5356: IF l_contact_pt_contains_str IS NOT NULL THEN
5357: l_max_score := 0;
5358: l_max_id := 0;
5359: l_max_idx := 0;
5360: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN

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

5357: l_max_score := 0;
5358: l_max_id := 0;
5359: l_max_idx := 0;
5360: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5361: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5362: ELSE
5363: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5364: END IF;
5365: LOOP

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

5359: l_max_idx := 0;
5360: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5361: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5362: ELSE
5363: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5364: END IF;
5365: LOOP
5366: FETCH l_contact_pt_cur INTO
5367: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX2, l_TX6;