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_39 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 853: l_party_contains_str VARCHAR2(32000);

849: p_party_max_score NUMBER,
850: p_denorm_max_score NUMBER,
851: p_non_denorm_max_score NUMBER,
852: p_threshold NUMBER) RETURN VARCHAR2 IS
853: l_party_contains_str VARCHAR2(32000);
854: l_party_filter VARCHAR2(1) := null;
855: l_prim_temp VARCHAR2(4000);
856: l_denorm_str VARCHAR2(4000);
857: BEGIN

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

893:
894: IF l_denorm_str IS NOT NULL THEN
895: IF (p_non_denorm_max_score+50)>=p_threshold THEN
896: l_denorm_str := NULL;
897: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
898: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
899: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
900: l_denorm_str := NULL;
901: END IF;

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

895: IF (p_non_denorm_max_score+50)>=p_threshold THEN
896: l_denorm_str := NULL;
897: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
898: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
899: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
900: l_denorm_str := NULL;
901: END IF;
902: ELSE
903: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

899: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
900: l_denorm_str := NULL;
901: END IF;
902: ELSE
903: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
904: END IF;
905: IF lengthb(l_party_contains_str) > 4000 THEN
906: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
907: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');

Line 905: IF lengthb(l_party_contains_str) > 4000 THEN

901: END IF;
902: ELSE
903: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
904: END IF;
905: IF lengthb(l_party_contains_str) > 4000 THEN
906: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
907: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
908: FND_MSG_PUB.ADD;
909: RAISE FND_API.G_EXC_ERROR;

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

907: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
908: FND_MSG_PUB.ADD;
909: RAISE FND_API.G_EXC_ERROR;
910: END IF;
911: IF (p_match_str = ' AND ' OR p_party_max_score 912: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
913: 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);
914: END IF;
915: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

909: RAISE FND_API.G_EXC_ERROR;
910: END IF;
911: IF (p_match_str = ' AND ' OR p_party_max_score 912: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
913: 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);
914: END IF;
915: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
916: 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);
917: END IF;

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

912: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
913: 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);
914: END IF;
915: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
916: 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);
917: END IF;
918: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
919: 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);
920: END IF;

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

917: END IF;
918: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
919: 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);
920: END IF;
921: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
922: ELSE
923: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
924: 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);
925: 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 924: 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);

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

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

921: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
922: ELSE
923: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
924: 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);
925: 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);
926: END IF;
927: RETURN l_party_contains_str;
928: END IF;
929: END;

Line 927: RETURN l_party_contains_str;

923: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
924: 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);
925: 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);
926: END IF;
927: RETURN l_party_contains_str;
928: END IF;
929: END;
930: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
931: l_contains_str VARCHAR2(32000);

Line 931: l_contains_str VARCHAR2(32000);

927: RETURN l_party_contains_str;
928: END IF;
929: END;
930: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
931: l_contains_str VARCHAR2(32000);
932: l_contains_str_temp VARCHAR2(32000);
933: l_den_contains_str VARCHAR2(32000);
934: l_den_contains_str_temp VARCHAR2(32000);
935: l_filter_str VARCHAR2(4000) := null;

Line 932: l_contains_str_temp VARCHAR2(32000);

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

Line 933: l_den_contains_str VARCHAR2(32000);

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

Line 934: l_den_contains_str_temp VARCHAR2(32000);

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

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

940: 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);
941: END IF;
942: x_denorm_str := NULL;
943:
944: -- Dynamic setup of party site contains str
945: --
946: -- For each primary transformation add to intermedia query if it
947: -- is not null
948: FOR I IN 1..g_party_site_stage_list.COUNT LOOP

Line 949: l_contains_str_temp := null;

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

Line 950: l_den_contains_str_temp := null;

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

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

956: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
957: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
958: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
959: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
960: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
961: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
962:
963: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
964: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);

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

957: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
958: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
959: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
960: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
961: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
962:
963: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
964: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
965: END LOOP;

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

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

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

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

Line 967: IF lengthb(l_contains_str) > 4000 THEN

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

Line 973: x_denorm_str := l_den_contains_str;

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

Line 974: RETURN l_contains_str;

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

Line 978: l_contains_str VARCHAR2(32000);

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

Line 979: l_contains_str_temp VARCHAR2(32000);

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

Line 980: l_den_contains_str VARCHAR2(32000);

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

Line 981: l_den_contains_str_temp VARCHAR2(32000);

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

Line 994: l_contains_str VARCHAR2(32000);

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

Line 995: l_contains_str_temp VARCHAR2(32000);

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

Line 996: l_den_contains_str VARCHAR2(32000);

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

Line 997: l_den_contains_str_temp VARCHAR2(32000);

993: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
994: l_contains_str VARCHAR2(32000);
995: l_contains_str_temp VARCHAR2(32000);
996: l_den_contains_str VARCHAR2(32000);
997: l_den_contains_str_temp VARCHAR2(32000);
998: l_filter_str VARCHAR2(4000) := null;
999: l_prim_temp VARCHAR2(4000) := null;
1000: l_prim_temp_den VARCHAR2(4000) := null;
1001: TYPE CONTACT_PT_REC_TYPE IS RECORD (

Line 1020: p_party_contains_str VARCHAR2,

1016: p_dup_party_id NUMBER,
1017: p_restrict_sql VARCHAR2,
1018: p_match_str VARCHAR2,
1019: p_search_merged VARCHAR2,
1020: p_party_contains_str VARCHAR2,
1021: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1022: l_sqlstr VARCHAR2(4000);
1023: l_search_merged VARCHAR2(1);
1024: BEGIN

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

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

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

1068: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1069: l_sqlstr := l_sqlstr || ' (TX36 like :TX36||'' %'' ';
1070: l_sqlstr := l_sqlstr || ' )) ';
1071: END IF;
1072: 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
1073: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1074: 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);
1075: END IF;
1076: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';

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

1070: l_sqlstr := l_sqlstr || ' )) ';
1071: END IF;
1072: 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
1073: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1074: 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);
1075: END IF;
1076: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1077: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX11||'' %'' ';
1078: l_sqlstr := l_sqlstr || ' )) ';

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

1091: END IF;
1092: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1093: 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);
1094: END IF;
1095: 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
1096: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1097: 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);
1098: END IF;
1099: 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 1097: 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);

1093: 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);
1094: END IF;
1095: 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
1096: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1097: 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);
1098: END IF;
1099: 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
1100: ,g_party_stage_rec.TX40
1101: ,g_party_stage_rec.TX40

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

1113: ,g_party_stage_rec.TX40
1114: ,g_party_stage_rec.TX36
1115: ,g_party_stage_rec.TX36
1116: ;
1117: 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
1118: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1119: 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);
1120: END IF;
1121: 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 1119: 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);

1115: ,g_party_stage_rec.TX36
1116: ;
1117: 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
1118: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1119: 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);
1120: END IF;
1121: 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
1122: ,g_party_site_stage_list(1).TX11
1123: ,g_party_site_stage_list(1).TX11

Line 1136: p_contains_str VARCHAR2,

1132: END;
1133: PROCEDURE open_party_cursor(
1134: p_dup_party_id NUMBER,
1135: p_restrict_sql VARCHAR2,
1136: p_contains_str VARCHAR2,
1137: p_search_ctx_id NUMBER,
1138: p_match_str VARCHAR2,
1139: p_search_merged VARCHAR2,
1140: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS

Line 1155: IF p_contains_str IS NULL THEN

1151: l_search_merged := 'N';
1152: ELSE
1153: l_search_merged := p_search_merged;
1154: END IF;
1155: IF p_contains_str IS NULL THEN
1156: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1157: 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);
1158: END IF;
1159: OPEN x_cursor FOR

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

1153: l_search_merged := p_search_merged;
1154: END IF;
1155: IF p_contains_str IS NULL THEN
1156: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1157: 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);
1158: END IF;
1159: OPEN x_cursor FOR
1160: SELECT PARTY_ID , TX39, TX40, lpad(rtrim(TX41),9,chr(48)), TX44
1161: FROM HZ_STAGED_PARTIES stage

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

1170: END IF;
1171: OPEN x_cursor FOR
1172: SELECT /*+ INDEX(stage HZ_STAGE_PARTIES_T1) */ PARTY_ID , TX39, TX40, lpad(rtrim(TX41),9,chr(48)), TX44
1173: FROM HZ_STAGED_PARTIES stage
1174: WHERE contains( concat_col, p_contains_str)>0
1175: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1176: AND( (l_search_merged ='Y' )
1177: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1178: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )

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

1183: END IF;
1184: OPEN x_cursor FOR
1185: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX39, TX40, lpad(rtrim(TX41),9,chr(48)), TX44
1186: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1187: WHERE contains( concat_col, p_contains_str)>0
1188: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1189: AND d.party_id = stage.party_id
1190: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1191: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)

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

1208: END IF;
1209: IF p_search_ctx_id IS NULL THEN
1210: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX39, TX40, lpad(rtrim(TX41),9,chr(48)), TX44'||
1211: ' FROM HZ_STAGED_PARTIES stage'||
1212: ' WHERE contains( concat_col, :cont)>0'||
1213: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1214: ' AND ('||p_restrict_sql||')' ||
1215: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1216: IF l_search_merged = 'Y' THEN

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

1220: ELSE
1221: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1222: END IF;
1223: output_long_strings('----------------------------------------------------------');
1224: output_long_strings('Party Contains String = '||p_contains_str);
1225: output_long_strings('Restrict Sql = '||p_restrict_sql);
1226: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1227: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1228: ,p_dup_party_id, p_dup_party_id;

Line 1226: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1222: END IF;
1223: output_long_strings('----------------------------------------------------------');
1224: output_long_strings('Party Contains String = '||p_contains_str);
1225: output_long_strings('Restrict Sql = '||p_restrict_sql);
1226: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1227: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1228: ,p_dup_party_id, p_dup_party_id;
1229: END IF;
1230: END IF;

Line 1257: p_contains_str VARCHAR2,

1253: PROCEDURE open_party_site_cursor(
1254: p_dup_party_id NUMBER,
1255: p_party_id NUMBER,
1256: p_restrict_sql VARCHAR2,
1257: p_contains_str VARCHAR2,
1258: p_search_ctx_id NUMBER,
1259: p_search_merged VARCHAR2,
1260: p_search_rel_sites VARCHAR2,
1261: p_person_api VARCHAR2,

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

1285: IF p_search_rel_sites = 'N' THEN
1286: OPEN x_cursor FOR
1287: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX4, TX3
1288: FROM HZ_STAGED_PARTY_SITES stage
1289: WHERE contains( concat_col, p_contains_str)>0
1290: AND( (l_search_merged ='Y' )
1291: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1292: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1293: AND stage.party_id = p_party_id;

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

1294: ELSE
1295: OPEN x_cursor FOR
1296: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX4, TX3
1297: FROM HZ_STAGED_PARTY_SITES stage
1298: WHERE contains( concat_col, p_contains_str)>0
1299: AND( (l_search_merged ='Y' )
1300: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1301: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1302: AND stage.party_id = p_party_id

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

1302: AND stage.party_id = p_party_id
1303: UNION
1304: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX4, TX3
1305: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1306: WHERE contains( concat_col, p_contains_str)>0
1307: AND( (l_search_merged ='Y' )
1308: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1309: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1310: AND r.object_id = p_party_id

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

1324: END IF;
1325: OPEN x_cursor FOR
1326: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX4, TX3
1327: FROM HZ_STAGED_PARTY_SITES stage
1328: WHERE contains( concat_col, p_contains_str)>0
1329: AND EXISTS (
1330: SELECT 1 FROM HZ_STAGED_PARTIES p
1331: WHERE p.PARTY_ID = stage.PARTY_ID
1332: AND( (l_search_merged ='Y' )

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

1344: IF p_person_api = 'Y' THEN
1345: OPEN x_cursor FOR
1346: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX4, TX3
1347: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1348: WHERE contains( concat_col, p_contains_str)>0
1349: AND( (l_search_merged ='Y' )
1350: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1351: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1352: AND d.search_context_id = p_search_ctx_id

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

1354: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1355: UNION
1356: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX4, TX3
1357: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, 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 d.search_context_id = p_search_ctx_id

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

1367: ELSE
1368: OPEN x_cursor FOR
1369: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX4, TX3
1370: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1371: WHERE contains( concat_col, p_contains_str)>0
1372: AND( (l_search_merged ='Y' )
1373: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1374: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1375: AND d.search_context_id = p_search_ctx_id

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

1412: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1413: END IF;
1414: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1415: ' FROM HZ_STAGED_PARTY_SITES stage'||
1416: ' WHERE contains( concat_col, :cont)>0'||
1417: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1418: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1419: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1420: ' AND (ORG_CONTACT_ID IS NULL ' ||

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

1422: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1423: ' UNION ' ||
1424: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1425: ' FROM HZ_STAGED_PARTY_SITES stage'||
1426: ' WHERE contains( concat_col, :cont)>0'||
1427: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1428: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1429: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1430: ' AND ORG_CONTACT_ID IN ' ||

Line 1436: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1432: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1433: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1434: ' and ('||p_restrict_sql1|| ') )' ||
1435: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1436: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1437: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1438: ELSE
1439: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1440: ' FROM HZ_STAGED_PARTY_SITES stage'||

Line 1437: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1433: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1434: ' and ('||p_restrict_sql1|| ') )' ||
1435: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1436: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1437: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1438: ELSE
1439: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1440: ' FROM HZ_STAGED_PARTY_SITES stage'||
1441: ' WHERE contains( concat_col, :cont)>0'||

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

1437: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1438: ELSE
1439: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1440: ' FROM HZ_STAGED_PARTY_SITES stage'||
1441: ' WHERE contains( concat_col, :cont)>0'||
1442: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1443: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1444: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1445: ' AND EXISTS ('||

Line 1451: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1447: ' WHERE p.party_id = stage.party_id ' ||
1448: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1449: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1450: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1451: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1452: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1453: ,p_dup_party_id, p_dup_party_id;
1454: END IF;
1455: ELSE

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

1454: END IF;
1455: ELSE
1456: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1457: ' FROM HZ_STAGED_PARTY_SITES stage'||
1458: ' WHERE contains( concat_col, :cont)>0'||
1459: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1460: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1461: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1462: ' AND EXISTS ('||

Line 1468: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1464: ' WHERE p.party_id = stage.party_id ' ||
1465: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1466: ' AND ('||p_restrict_sql||')' ||
1467: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1468: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1469: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1470: ,p_dup_party_id, p_dup_party_id;
1471: END IF;
1472: END IF;

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

1470: ,p_dup_party_id, p_dup_party_id;
1471: END IF;
1472: END IF;
1473: output_long_strings('----------------------------------------------------------');
1474: output_long_strings('Party Site Contains String = '||p_contains_str);
1475: output_long_strings('Restrict Sql = '||p_restrict_sql);
1476: exception
1477: when others then
1478: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1502: p_contains_str VARCHAR2,

1498: PROCEDURE open_contact_cursor(
1499: p_dup_party_id NUMBER,
1500: p_party_id NUMBER,
1501: p_restrict_sql VARCHAR2,
1502: p_contains_str VARCHAR2,
1503: p_search_ctx_id NUMBER,
1504: p_search_merged VARCHAR2,
1505: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1506: l_sqlstr VARCHAR2(4000);

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

1526: END IF;
1527: OPEN x_cursor FOR
1528: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
1529: FROM HZ_STAGED_CONTACTS stage
1530: WHERE contains( concat_col, p_contains_str)>0
1531: AND EXISTS (
1532: SELECT 1 FROM HZ_STAGED_PARTIES p
1533: WHERE p.PARTY_ID = stage.PARTY_ID
1534: AND( (l_search_merged ='Y' )

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

1549: END IF;
1550: OPEN x_cursor FOR
1551: SELECT ORG_CONTACT_ID, PARTY_ID
1552: FROM HZ_STAGED_CONTACTS stage
1553: WHERE contains( concat_col, p_contains_str)>0
1554: AND EXISTS (
1555: SELECT 1 FROM HZ_STAGED_PARTIES p
1556: WHERE p.PARTY_ID = stage.PARTY_ID
1557: AND( (l_search_merged ='Y' )

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

1568: END IF;
1569: OPEN x_cursor FOR
1570: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
1571: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1572: WHERE contains( concat_col, p_contains_str)>0
1573: AND d.search_context_id = p_search_ctx_id
1574: AND d.party_id = stage.party_id
1575: AND( (l_search_merged ='Y' )
1576: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1602: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
1603: END IF;
1604: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
1605: ' FROM HZ_STAGED_CONTACTS stage'||
1606: ' WHERE contains( concat_col, :cont)>0'||
1607: ' AND EXISTS ('||
1608: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1609: ' WHERE p.party_id = stage.party_id ' ||
1610: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1616: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1612: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1613: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1614: ' AND ('||p_restrict_sql||')' ||
1615: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1616: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1617: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1618: ,p_dup_party_id, p_dup_party_id;
1619: END IF;
1620: output_long_strings('----------------------------------------------------------');

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

1617: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1618: ,p_dup_party_id, p_dup_party_id;
1619: END IF;
1620: output_long_strings('----------------------------------------------------------');
1621: output_long_strings('Contacts Contains String = '||p_contains_str);
1622: output_long_strings('Restrict Sql = '||p_restrict_sql);
1623: exception
1624: when others then
1625: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1649: p_contains_str VARCHAR2,

1645: PROCEDURE open_contact_pt_cursor(
1646: p_dup_party_id NUMBER,
1647: p_party_id NUMBER,
1648: p_restrict_sql VARCHAR2,
1649: p_contains_str VARCHAR2,
1650: p_search_ctx_id NUMBER,
1651: p_search_merged VARCHAR2,
1652: p_search_rel_cpts VARCHAR2,
1653: p_person_api VARCHAR2,

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

1675: THEN
1676: OPEN x_cursor FOR
1677: 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
1678: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1679: WHERE contains( concat_col, p_contains_str)>0
1680: AND d.search_context_id = p_search_ctx_id
1681: AND( (l_search_merged ='Y' )
1682: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1683: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1687: THEN
1688: OPEN x_cursor FOR
1689: 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
1690: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1691: WHERE contains( concat_col, p_contains_str)>0
1692: AND d.search_context_id = p_search_ctx_id
1693: AND( (l_search_merged ='Y' )
1694: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1695: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1704: IF p_search_rel_cpts = 'N' THEN
1705: OPEN x_cursor FOR
1706: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1707: FROM HZ_STAGED_CONTACT_POINTS stage
1708: WHERE contains( concat_col, p_contains_str)>0
1709: AND( (l_search_merged ='Y' )
1710: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1711: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1712: AND stage.party_id = p_party_id;

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

1713: ELSE
1714: OPEN x_cursor FOR
1715: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1716: FROM HZ_STAGED_CONTACT_POINTS stage
1717: WHERE contains( concat_col, p_contains_str)>0
1718: AND( (l_search_merged ='Y' )
1719: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1720: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1721: AND stage.party_id = p_party_id

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

1721: AND stage.party_id = p_party_id
1722: UNION
1723: 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
1724: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1725: WHERE contains( concat_col, p_contains_str)>0
1726: AND r.object_id = p_party_id
1727: AND( (l_search_merged ='Y' )
1728: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1729: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1743: END IF;
1744: OPEN x_cursor FOR
1745: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1746: FROM HZ_STAGED_CONTACT_POINTS stage
1747: WHERE contains( concat_col, p_contains_str)>0
1748: AND EXISTS (
1749: SELECT 1 FROM HZ_STAGED_PARTIES p
1750: WHERE p.PARTY_ID = stage.PARTY_ID
1751: AND( (l_search_merged ='Y' )

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

1763: IF p_person_api = 'Y' THEN
1764: OPEN x_cursor FOR
1765: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1766: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1767: WHERE contains( concat_col, p_contains_str)>0
1768: AND d.search_context_id = p_search_ctx_id
1769: AND d.party_id = stage.party_id
1770: AND( (l_search_merged ='Y' )
1771: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1773: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1774: UNION
1775: 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
1776: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1777: WHERE contains( concat_col, p_contains_str)>0
1778: AND d.search_context_id = p_search_ctx_id
1779: AND d.party_id = r.subject_id
1780: AND r.relationship_id = oc.party_relationship_id
1781: AND oc.org_contact_id = stage.org_contact_id

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

1786: ELSE
1787: OPEN x_cursor FOR
1788: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1789: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1790: WHERE contains( concat_col, p_contains_str)>0
1791: AND d.search_context_id = p_search_ctx_id
1792: AND d.party_id = stage.party_id
1793: AND( (l_search_merged ='Y' )
1794: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1831: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1832: END IF;
1833: 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 '||
1834: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1835: ' WHERE contains( concat_col, :cont)>0 '||
1836: ' AND (stage.org_contact_id is null '||
1837: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1838: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1839: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

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

1841: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1842: ' UNION ' ||
1843: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1844: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1845: ' WHERE contains( concat_col, :cont)>0 '||
1846: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1847: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1848: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1849: ' AND (stage.org_contact_id in ' ||

Line 1855: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1851: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1852: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1853: ' and ('||p_restrict_sql1 || ') ) )' ||
1854: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1855: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1856: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1857: ELSE
1858: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1859: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

Line 1856: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1852: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1853: ' and ('||p_restrict_sql1 || ') ) )' ||
1854: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1855: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1856: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1857: ELSE
1858: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1859: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1860: ' WHERE contains( concat_col, :cont)>0'||

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

1856: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1857: ELSE
1858: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1859: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1860: ' WHERE contains( concat_col, :cont)>0'||
1861: ' AND EXISTS ('||
1862: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1863: ' WHERE p.party_id = stage.party_id ' ||
1864: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1870: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1866: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1867: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1868: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1869: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1870: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1871: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1872: ,p_dup_party_id, p_dup_party_id;
1873: END IF;
1874: ELSE

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

1873: END IF;
1874: ELSE
1875: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1876: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1877: ' WHERE contains( concat_col, :cont)>0'||
1878: ' AND EXISTS ('||
1879: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1880: ' WHERE p.party_id = stage.party_id ' ||
1881: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1887: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1883: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1884: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1885: ' AND ('||p_restrict_sql||')' ||
1886: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1887: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1888: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1889: ,p_dup_party_id, p_dup_party_id;
1890: END IF;
1891: END IF;

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

1890: END IF;
1891: END IF;
1892: END IF;
1893: output_long_strings('----------------------------------------------------------');
1894: output_long_strings('Contact Points Contains String = '||p_contains_str);
1895: output_long_strings('Restrict Sql = '||p_restrict_sql);
1896: exception
1897: when others then
1898: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 2015: l_party_contains_str VARCHAR2(32000);

2011: x_num_matches OUT NUMBER
2012: ) IS
2013:
2014: -- Strings to hold the generated Intermedia query strings
2015: l_party_contains_str VARCHAR2(32000);
2016: l_party_site_contains_str VARCHAR2(32000);
2017: l_contact_contains_str VARCHAR2(32000);
2018: l_contact_pt_contains_str VARCHAR2(32000);
2019: l_denorm_str VARCHAR2(32000);

Line 2016: l_party_site_contains_str VARCHAR2(32000);

2012: ) IS
2013:
2014: -- Strings to hold the generated Intermedia query strings
2015: l_party_contains_str VARCHAR2(32000);
2016: l_party_site_contains_str VARCHAR2(32000);
2017: l_contact_contains_str VARCHAR2(32000);
2018: l_contact_pt_contains_str VARCHAR2(32000);
2019: l_denorm_str VARCHAR2(32000);
2020: l_ps_denorm_str VARCHAR2(32000);

Line 2017: l_contact_contains_str VARCHAR2(32000);

2013:
2014: -- Strings to hold the generated Intermedia query strings
2015: l_party_contains_str VARCHAR2(32000);
2016: l_party_site_contains_str VARCHAR2(32000);
2017: l_contact_contains_str VARCHAR2(32000);
2018: l_contact_pt_contains_str VARCHAR2(32000);
2019: l_denorm_str VARCHAR2(32000);
2020: l_ps_denorm_str VARCHAR2(32000);
2021: l_ct_denorm_str VARCHAR2(32000);

Line 2018: l_contact_pt_contains_str VARCHAR2(32000);

2014: -- Strings to hold the generated Intermedia query strings
2015: l_party_contains_str VARCHAR2(32000);
2016: l_party_site_contains_str VARCHAR2(32000);
2017: l_contact_contains_str VARCHAR2(32000);
2018: l_contact_pt_contains_str VARCHAR2(32000);
2019: l_denorm_str VARCHAR2(32000);
2020: l_ps_denorm_str VARCHAR2(32000);
2021: l_ct_denorm_str VARCHAR2(32000);
2022: l_cpt_denorm_str VARCHAR2(32000);

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

2105: call_type(2) := 'OR';
2106: END;
2107:
2108: /** Private procedure to acquire and score at party level ***/
2109: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2110: l_party_id_idx NUMBER:=1;
2111: l_ctx_id NUMBER;
2112: l_precalc_score BOOLEAN := FALSE;
2113: l_TX35_new varchar2(4000);

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

2137: l_search_ctx_id,H_PARTY_ID(I));
2138: l_party_id_idx := H_PARTY_ID.COUNT+1;
2139: ELSE
2140: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2141: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2142: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2143: 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);
2144: END IF;
2145: 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 2149: IF (l_party_site_contains_str IS NULL AND

2145: 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
2146: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2147: 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);
2148: END IF;
2149: IF (l_party_site_contains_str IS NULL AND
2150: l_contact_contains_str IS NULL AND
2151: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2152: 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);
2153: RAISE L_RETURN_IMM_EXC;

Line 2150: l_contact_contains_str IS NULL AND

2146: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2147: 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);
2148: END IF;
2149: IF (l_party_site_contains_str IS NULL AND
2150: l_contact_contains_str IS NULL AND
2151: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2152: 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);
2153: RAISE L_RETURN_IMM_EXC;
2154: ELSE

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

2147: 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);
2148: END IF;
2149: IF (l_party_site_contains_str IS NULL AND
2150: l_contact_contains_str IS NULL AND
2151: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2152: 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);
2153: RAISE L_RETURN_IMM_EXC;
2154: ELSE
2155: 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 2155: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);

2151: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2152: 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);
2153: RAISE L_RETURN_IMM_EXC;
2154: ELSE
2155: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2156: END IF;
2157: ELSE
2158: IF p_index>1 THEN
2159: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

2174: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2175: 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);
2176: END IF;
2177: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2178: 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);
2179: END IF;
2180: LOOP
2181: FETCH l_party_cur INTO
2182: l_party_id , l_TX39, l_TX40, l_TX41, l_TX44;

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

2196: l_party_id_idx:= l_party_id_idx+1;
2197: END IF;
2198: IF (l_party_id_idx-1)>l_max_thresh THEN
2199: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2200: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2201: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2202: AND l_contact_pt_contains_str IS NULL THEN
2203: EXIT;
2204: END IF;

Line 2202: AND l_contact_pt_contains_str IS NULL THEN

2198: IF (l_party_id_idx-1)>l_max_thresh THEN
2199: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2200: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2201: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2202: AND l_contact_pt_contains_str IS NULL THEN
2203: EXIT;
2204: END IF;
2205: CLOSE l_party_cur;
2206: IF p_index>1 THEN

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

2227: ROLLBACK to eval_start;
2228: END;
2229:
2230: /** Private procedure to acquire and score at party site level ***/
2231: 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
2232: l_party_id_idx NUMBER:=1;
2233: l_ctx_id NUMBER;
2234: h_ps_id HZ_PARTY_SEARCH.IDList;
2235: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

2239: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2240: 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);
2241: 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);
2242: END IF;
2243: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2244: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2245: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2246: l_party_id := H_SCORES.FIRST;
2247: WHILE l_party_id IS NOT NULL LOOP

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

2240: 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);
2241: 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);
2242: END IF;
2243: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2244: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2245: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2246: l_party_id := H_SCORES.FIRST;
2247: WHILE l_party_id IS NOT NULL LOOP
2248: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;

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

2241: 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);
2242: END IF;
2243: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2244: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2245: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2246: l_party_id := H_SCORES.FIRST;
2247: WHILE l_party_id IS NOT NULL LOOP
2248: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2249: l_party_id:=H_SCORES.NEXT(l_party_id);

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

2271: l_search_ctx_id,H_PARTY_ID(I));
2272: l_party_id_idx := H_PARTY_ID.COUNT+1;
2273: ELSE
2274: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2275: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2276: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2277: 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);
2278: END IF;
2279: IF p_index>1 THEN

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

2293: END IF;
2294: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2295: 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);
2296: END IF;
2297: 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);
2298: LOOP
2299: FETCH l_party_site_cur INTO
2300: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX3;
2301: EXIT WHEN l_party_site_cur%NOTFOUND;

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

2349: END IF;
2350: END;
2351:
2352: /** Private procedure to acquire and score at contact point level ***/
2353: 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
2354: l_party_id_idx NUMBER:=1;
2355: l_ctx_id NUMBER;
2356: h_ct_id HZ_PARTY_SEARCH.IDList;
2357: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

2383: l_search_ctx_id,H_PARTY_ID(I));
2384: l_party_id_idx := H_PARTY_ID.COUNT+1;
2385: ELSE
2386: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2387: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
2388: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2389: 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);
2390: END IF;
2391: IF p_index>1 THEN

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

2405: END IF;
2406: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2407: 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);
2408: END IF;
2409: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2410: LOOP
2411: FETCH l_contact_cur INTO
2412: l_org_contact_id, l_ct_party_id ;
2413: EXIT WHEN l_contact_cur%NOTFOUND;

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

2458: END IF;
2459: END;
2460:
2461: /** Private procedure to acquire and score at contact point level ***/
2462: 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
2463: l_party_id_idx NUMBER:=1;
2464: l_ctx_id NUMBER;
2465: h_cpt_id HZ_PARTY_SEARCH.IDList;
2466: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

2494: l_search_ctx_id,H_PARTY_ID(I));
2495: l_party_id_idx := H_PARTY_ID.COUNT+1;
2496: ELSE
2497: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2498: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
2499: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2500: 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);
2501: END IF;
2502: IF p_index>1 THEN

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

2516: END IF;
2517: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2518: 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);
2519: END IF;
2520: 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);
2521: LOOP
2522: FETCH l_contact_pt_cur INTO
2523: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
2524: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 2701: IF l_party_contains_str IS NOT NULL THEN

2697: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
2698: l_threshold := round(( l_entered_max_score / 250) * 80);
2699: other_acq_criteria_exists := TRUE ;
2700: --check if acquisition criteria exists for any other entity
2701: IF l_party_contains_str IS NOT NULL THEN
2702: acq_cnt := 1;
2703: END IF;
2704: IF l_party_site_contains_str IS NOT NULL THEN
2705: acq_cnt := acq_cnt+1;

Line 2704: IF l_party_site_contains_str IS NOT NULL THEN

2700: --check if acquisition criteria exists for any other entity
2701: IF l_party_contains_str IS NOT NULL THEN
2702: acq_cnt := 1;
2703: END IF;
2704: IF l_party_site_contains_str IS NOT NULL THEN
2705: acq_cnt := acq_cnt+1;
2706: END IF;
2707: IF l_contact_contains_str IS NOT NULL THEN
2708: acq_cnt := acq_cnt+1;

Line 2707: IF l_contact_contains_str IS NOT NULL THEN

2703: END IF;
2704: IF l_party_site_contains_str IS NOT NULL THEN
2705: acq_cnt := acq_cnt+1;
2706: END IF;
2707: IF l_contact_contains_str IS NOT NULL THEN
2708: acq_cnt := acq_cnt+1;
2709: END IF;
2710: IF l_contact_pt_contains_str IS NOT NULL THEN
2711: acq_cnt := acq_cnt+1;

Line 2710: IF l_contact_pt_contains_str IS NOT NULL THEN

2706: END IF;
2707: IF l_contact_contains_str IS NOT NULL THEN
2708: acq_cnt := acq_cnt+1;
2709: END IF;
2710: IF l_contact_pt_contains_str IS NOT NULL THEN
2711: acq_cnt := acq_cnt+1;
2712: END IF;
2713: IF acq_cnt>1 THEN
2714: other_acq_criteria_exists := TRUE;

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

2740: FND_MSG_PUB.ADD;
2741: RAISE FND_API.G_EXC_ERROR;
2742: END IF;
2743: END IF;
2744: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2745: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2746: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2747: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2748: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN

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

2741: RAISE FND_API.G_EXC_ERROR;
2742: END IF;
2743: END IF;
2744: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2745: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2746: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2747: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2748: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2749: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

2742: END IF;
2743: END IF;
2744: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2745: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2746: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2747: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2748: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2749: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2750: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

2743: END IF;
2744: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2745: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2746: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2747: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2748: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2749: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2750: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2751: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

2744: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2745: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2746: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2747: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2748: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2749: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2750: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2751: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2752: END IF;

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

2745: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2746: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2747: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2748: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2749: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2750: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2751: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2752: END IF;
2753: END;

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

2746: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2747: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2748: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2749: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2750: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2751: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2752: END IF;
2753: END;
2754: /************ End of find_parties private procedures **********/

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

2747: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2748: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2749: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2750: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2751: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2752: END IF;
2753: END;
2754: /************ End of find_parties private procedures **********/
2755:

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

2819: 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);
2820: END IF;
2821: -- ************************************************************
2822: -- STEP 2. Setup of intermedia query strings for Acquisition query
2823: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2824: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2825: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2826: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2827: 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 2824: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);

2820: END IF;
2821: -- ************************************************************
2822: -- STEP 2. Setup of intermedia query strings for Acquisition query
2823: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2824: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2825: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2826: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2827: 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);
2828: END IF;

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

2821: -- ************************************************************
2822: -- STEP 2. Setup of intermedia query strings for Acquisition query
2823: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2824: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2825: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2826: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2827: 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);
2828: END IF;
2829: l_denorm_max_score:=0;

Line 2857: 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((80/250)*l_entered_max_score));

2853: END IF;
2854: ELSE
2855: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
2856: END IF;
2857: 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((80/250)*l_entered_max_score));
2858: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
2859:
2860: -- Setup Search Context ID
2861: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 2867: IF l_party_contains_str IS NULL THEN

2863:
2864: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2865: 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);
2866: END IF;
2867: IF l_party_contains_str IS NULL THEN
2868: defpt := 1;
2869: END IF;
2870: IF l_party_site_contains_str IS NULL THEN
2871: defps := 1;

Line 2870: IF l_party_site_contains_str IS NULL THEN

2866: END IF;
2867: IF l_party_contains_str IS NULL THEN
2868: defpt := 1;
2869: END IF;
2870: IF l_party_site_contains_str IS NULL THEN
2871: defps := 1;
2872: END IF;
2873: IF l_contact_contains_str IS NULL THEN
2874: defct := 1;

Line 2873: IF l_contact_contains_str IS NULL THEN

2869: END IF;
2870: IF l_party_site_contains_str IS NULL THEN
2871: defps := 1;
2872: END IF;
2873: IF l_contact_contains_str IS NULL THEN
2874: defct := 1;
2875: END IF;
2876: IF l_contact_pt_contains_str IS NULL THEN
2877: defcpt := 1;

Line 2876: IF l_contact_pt_contains_str IS NULL THEN

2872: END IF;
2873: IF l_contact_contains_str IS NULL THEN
2874: defct := 1;
2875: END IF;
2876: IF l_contact_pt_contains_str IS NULL THEN
2877: defcpt := 1;
2878: END IF;
2879:
2880: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

2881: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2882: 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);
2883: END IF;
2884: 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);
2885: 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);
2886: 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);
2887: 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);
2888: 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);
2889: 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 2886: 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);

2882: 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);
2883: END IF;
2884: 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);
2885: 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);
2886: 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);
2887: 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);
2888: 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);
2889: 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);
2890: END IF;

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

2883: END IF;
2884: 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);
2885: 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);
2886: 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);
2887: 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);
2888: 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);
2889: 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);
2890: END IF;
2891: FOR I in 1..5 LOOP

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

2884: 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);
2885: 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);
2886: 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);
2887: 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);
2888: 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);
2889: 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);
2890: END IF;
2891: FOR I in 1..5 LOOP
2892: do_eval(I);

Line 2970: l_party_contains_str VARCHAR2(32000);

2966: x_num_matches OUT NUMBER
2967: ) IS
2968:
2969: -- Strings to hold the generated Intermedia query strings
2970: l_party_contains_str VARCHAR2(32000);
2971: l_party_site_contains_str VARCHAR2(32000);
2972: l_contact_contains_str VARCHAR2(32000);
2973: l_contact_pt_contains_str VARCHAR2(32000);
2974: l_denorm_str VARCHAR2(32000);

Line 2971: l_party_site_contains_str VARCHAR2(32000);

2967: ) IS
2968:
2969: -- Strings to hold the generated Intermedia query strings
2970: l_party_contains_str VARCHAR2(32000);
2971: l_party_site_contains_str VARCHAR2(32000);
2972: l_contact_contains_str VARCHAR2(32000);
2973: l_contact_pt_contains_str VARCHAR2(32000);
2974: l_denorm_str VARCHAR2(32000);
2975: l_ps_denorm_str VARCHAR2(32000);

Line 2972: l_contact_contains_str VARCHAR2(32000);

2968:
2969: -- Strings to hold the generated Intermedia query strings
2970: l_party_contains_str VARCHAR2(32000);
2971: l_party_site_contains_str VARCHAR2(32000);
2972: l_contact_contains_str VARCHAR2(32000);
2973: l_contact_pt_contains_str VARCHAR2(32000);
2974: l_denorm_str VARCHAR2(32000);
2975: l_ps_denorm_str VARCHAR2(32000);
2976: l_ct_denorm_str VARCHAR2(32000);

Line 2973: l_contact_pt_contains_str VARCHAR2(32000);

2969: -- Strings to hold the generated Intermedia query strings
2970: l_party_contains_str VARCHAR2(32000);
2971: l_party_site_contains_str VARCHAR2(32000);
2972: l_contact_contains_str VARCHAR2(32000);
2973: l_contact_pt_contains_str VARCHAR2(32000);
2974: l_denorm_str VARCHAR2(32000);
2975: l_ps_denorm_str VARCHAR2(32000);
2976: l_ct_denorm_str VARCHAR2(32000);
2977: l_cpt_denorm_str VARCHAR2(32000);

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

3090: call_type(2) := 'OR';
3091: END;
3092:
3093: /** Private procedure to acquire and score at party level ***/
3094: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3095: l_party_id_idx NUMBER:=1;
3096: l_ctx_id NUMBER;
3097: l_TX35_new varchar2(4000);
3098: BEGIN

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

3112: l_search_ctx_id,H_PARTY_ID(I));
3113: l_party_id_idx := H_PARTY_ID.COUNT+1;
3114: ELSE
3115: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3116: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3117: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3118: 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);
3119: END IF;
3120: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN

Line 3124: IF (l_party_site_contains_str IS NULL AND

3120: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3121: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3122: 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);
3123: END IF;
3124: IF (l_party_site_contains_str IS NULL AND
3125: l_contact_contains_str IS NULL AND
3126: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3127: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3128: RAISE L_RETURN_IMM_EXC;

Line 3125: l_contact_contains_str IS NULL AND

3121: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3122: 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);
3123: END IF;
3124: IF (l_party_site_contains_str IS NULL AND
3125: l_contact_contains_str IS NULL AND
3126: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3127: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3128: RAISE L_RETURN_IMM_EXC;
3129: ELSE

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

3122: 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);
3123: END IF;
3124: IF (l_party_site_contains_str IS NULL AND
3125: l_contact_contains_str IS NULL AND
3126: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3127: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3128: RAISE L_RETURN_IMM_EXC;
3129: ELSE
3130: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);

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

3126: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3127: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3128: RAISE L_RETURN_IMM_EXC;
3129: ELSE
3130: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3131: END IF;
3132: ELSE
3133: IF p_index>1 THEN
3134: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3146: l_ctx_id := NULL;
3147: l_party_id_idx := H_PARTY_ID.COUNT+1;
3148: END IF;
3149: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3150: 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);
3151: END IF;
3152: LOOP
3153: FETCH l_party_cur INTO
3154: l_party_id , l_TX39, l_TX40, l_TX41, l_TX44;

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

3168: l_party_id_idx:= l_party_id_idx+1;
3169: END IF;
3170: IF (l_party_id_idx-1)>l_max_thresh THEN
3171: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3172: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3173: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3174: AND l_contact_pt_contains_str IS NULL THEN
3175: H_PARTY_ID.DELETE(l_party_id_idx-1);
3176: H_PARTY_ID_LIST.DELETE(l_index);

Line 3174: AND l_contact_pt_contains_str IS NULL THEN

3170: IF (l_party_id_idx-1)>l_max_thresh THEN
3171: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3172: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3173: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3174: AND l_contact_pt_contains_str IS NULL THEN
3175: H_PARTY_ID.DELETE(l_party_id_idx-1);
3176: H_PARTY_ID_LIST.DELETE(l_index);
3177: H_SCORES.DELETE(l_index);
3178: EXIT;

Line 3203: p_contains_str VARCHAR2,

3199: CLOSE l_party_cur;
3200: ROLLBACK to eval_start;
3201: END;
3202: PROCEDURE open_person_contact_cursor(
3203: p_contains_str VARCHAR2,
3204: p_search_ctx_id NUMBER,
3205: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3206: BEGIN
3207: OPEN x_cursor FOR

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

3206: BEGIN
3207: OPEN x_cursor FOR
3208: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
3209: FROM HZ_STAGED_CONTACTS stage
3210: WHERE contains( concat_col, p_contains_str)>0
3211: AND ORG_CONTACT_ID in (
3212: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3213: org_contact_id
3214: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc

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

3230: end if;
3231: END;
3232:
3233: /** Private procedure to acquire and score at party site level ***/
3234: 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
3235: l_party_id_idx NUMBER:=1;
3236: l_ctx_id NUMBER;
3237: h_ps_id HZ_PARTY_SEARCH.IDList;
3238: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

3258: ELSE
3259: l_party_id_idx := H_PARTY_ID.COUNT+1;
3260: l_ctx_id := NULL;
3261: END IF;
3262: 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);
3263: LOOP
3264: FETCH l_party_site_cur INTO
3265: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX3;
3266: EXIT WHEN l_party_site_cur%NOTFOUND;

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

3329: END IF;
3330: END;
3331:
3332: /** Private procedure to acquire and score at party site level ***/
3333: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3334: l_party_id_idx NUMBER:=1;
3335: l_ctx_id NUMBER;
3336: h_ct_id HZ_PARTY_SEARCH.IDList;
3337: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

3345: FORALL I in 1..H_PARTY_ID.COUNT
3346: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
3347: l_search_ctx_id,H_PARTY_ID(I));
3348: l_party_id_idx := H_PARTY_ID.COUNT+1;
3349: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
3350: LOOP
3351: FETCH l_contact_cur INTO
3352: l_org_contact_id, l_ct_party_id ;
3353: EXIT WHEN l_contact_cur%NOTFOUND;

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

3387: END IF;
3388: END;
3389:
3390: /** Private procedure to acquire and score at contact point level ***/
3391: 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
3392: l_party_id_idx NUMBER:=1;
3393: l_ctx_id NUMBER;
3394: h_cpt_id HZ_PARTY_SEARCH.IDList;
3395: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

3432: END IF;
3433: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3434: 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);
3435: END IF;
3436: 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);
3437: LOOP
3438: FETCH l_contact_pt_cur INTO
3439: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
3440: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 3639: IF l_party_contains_str IS NOT NULL THEN

3635: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3636: l_threshold := round(( l_entered_max_score / 250) * 80);
3637: other_acq_criteria_exists := TRUE ;
3638: --check if acquisition criteria exists for any other entity
3639: IF l_party_contains_str IS NOT NULL THEN
3640: acq_cnt := 1;
3641: END IF;
3642: IF l_party_site_contains_str IS NOT NULL THEN
3643: acq_cnt := acq_cnt+1;

Line 3642: IF l_party_site_contains_str IS NOT NULL THEN

3638: --check if acquisition criteria exists for any other entity
3639: IF l_party_contains_str IS NOT NULL THEN
3640: acq_cnt := 1;
3641: END IF;
3642: IF l_party_site_contains_str IS NOT NULL THEN
3643: acq_cnt := acq_cnt+1;
3644: END IF;
3645: IF l_contact_contains_str IS NOT NULL THEN
3646: acq_cnt := acq_cnt+1;

Line 3645: IF l_contact_contains_str IS NOT NULL THEN

3641: END IF;
3642: IF l_party_site_contains_str IS NOT NULL THEN
3643: acq_cnt := acq_cnt+1;
3644: END IF;
3645: IF l_contact_contains_str IS NOT NULL THEN
3646: acq_cnt := acq_cnt+1;
3647: END IF;
3648: IF l_contact_pt_contains_str IS NOT NULL THEN
3649: acq_cnt := acq_cnt+1;

Line 3648: IF l_contact_pt_contains_str IS NOT NULL THEN

3644: END IF;
3645: IF l_contact_contains_str IS NOT NULL THEN
3646: acq_cnt := acq_cnt+1;
3647: END IF;
3648: IF l_contact_pt_contains_str IS NOT NULL THEN
3649: acq_cnt := acq_cnt+1;
3650: END IF;
3651: IF acq_cnt>1 THEN
3652: other_acq_criteria_exists := TRUE;

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

3678: FND_MSG_PUB.ADD;
3679: RAISE FND_API.G_EXC_ERROR;
3680: END IF;
3681: END IF;
3682: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3683: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3684: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3685: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3686: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

3679: RAISE FND_API.G_EXC_ERROR;
3680: END IF;
3681: END IF;
3682: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3683: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3684: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3685: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3686: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3687: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

3680: END IF;
3681: END IF;
3682: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3683: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3684: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3685: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3686: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3687: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3688: END IF;

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

3681: END IF;
3682: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3683: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3684: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3685: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3686: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3687: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3688: END IF;
3689: END;

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

3682: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3683: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3684: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3685: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3686: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3687: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3688: END IF;
3689: END;
3690: /************ End of find_persons private procedures **********/

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

3683: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3684: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3685: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3686: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3687: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3688: END IF;
3689: END;
3690: /************ End of find_persons private procedures **********/
3691:

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

3718: g_score_until_thresh := false;
3719: END IF;
3720: -- ************************************************************
3721: -- STEP 2. Setup of intermedia query strings for Acquisition query
3722: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3723: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3724: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3725: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3726: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);

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

3719: END IF;
3720: -- ************************************************************
3721: -- STEP 2. Setup of intermedia query strings for Acquisition query
3722: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3723: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3724: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3725: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3726: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3727:

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

3720: -- ************************************************************
3721: -- STEP 2. Setup of intermedia query strings for Acquisition query
3722: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3723: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3724: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3725: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3726: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3727:
3728: -- Setup Search Context ID

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

3721: -- STEP 2. Setup of intermedia query strings for Acquisition query
3722: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3723: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3724: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3725: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3726: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3727:
3728: -- Setup Search Context ID
3729: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 3732: IF l_party_contains_str IS NULL THEN

3728: -- Setup Search Context ID
3729: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3730: x_search_ctx_id := l_search_ctx_id;
3731:
3732: IF l_party_contains_str IS NULL THEN
3733: defpt := 1;
3734: END IF;
3735: IF l_party_site_contains_str IS NULL THEN
3736: defps := 1;

Line 3735: IF l_party_site_contains_str IS NULL THEN

3731:
3732: IF l_party_contains_str IS NULL THEN
3733: defpt := 1;
3734: END IF;
3735: IF l_party_site_contains_str IS NULL THEN
3736: defps := 1;
3737: END IF;
3738: IF l_contact_contains_str IS NULL THEN
3739: defct := 1;

Line 3738: IF l_contact_contains_str IS NULL THEN

3734: END IF;
3735: IF l_party_site_contains_str IS NULL THEN
3736: defps := 1;
3737: END IF;
3738: IF l_contact_contains_str IS NULL THEN
3739: defct := 1;
3740: END IF;
3741: IF l_contact_pt_contains_str IS NULL THEN
3742: defcpt := 1;

Line 3741: IF l_contact_pt_contains_str IS NULL THEN

3737: END IF;
3738: IF l_contact_contains_str IS NULL THEN
3739: defct := 1;
3740: END IF;
3741: IF l_contact_pt_contains_str IS NULL THEN
3742: defcpt := 1;
3743: END IF;
3744:
3745: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3743: END IF;
3744:
3745: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3746: 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);
3747: 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);
3748: 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);
3749: 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);
3750: 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);
3751: 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 3748: 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);

3744:
3745: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3746: 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);
3747: 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);
3748: 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);
3749: 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);
3750: 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);
3751: 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);
3752: END IF;

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

3745: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3746: 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);
3747: 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);
3748: 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);
3749: 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);
3750: 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);
3751: 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);
3752: END IF;
3753: FOR I in 1..5 LOOP

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

3746: 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);
3747: 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);
3748: 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);
3749: 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);
3750: 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);
3751: 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);
3752: END IF;
3753: FOR I in 1..5 LOOP
3754: do_eval(I);

Line 3756: IF l_contact_contains_str IS NOT NULL THEN

3752: END IF;
3753: FOR I in 1..5 LOOP
3754: do_eval(I);
3755: END LOOP;
3756: IF l_contact_contains_str IS NOT NULL THEN
3757: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3758: END IF;
3759: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3760: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 32 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

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

3753: FOR I in 1..5 LOOP
3754: do_eval(I);
3755: END LOOP;
3756: IF l_contact_contains_str IS NOT NULL THEN
3757: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3758: END IF;
3759: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3760: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 32 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3761: END IF;

Line 4525: l_party_contains_str VARCHAR2(32000);

4521: x_num_matches OUT NUMBER
4522: ) IS
4523:
4524: -- Strings to hold the generated Intermedia query strings
4525: l_party_contains_str VARCHAR2(32000);
4526: l_match_idx NUMBER;
4527: l_party_site_contains_str VARCHAR2(32000);
4528: l_contact_contains_str VARCHAR2(32000);
4529: l_contact_pt_contains_str VARCHAR2(32000);

Line 4527: l_party_site_contains_str VARCHAR2(32000);

4523:
4524: -- Strings to hold the generated Intermedia query strings
4525: l_party_contains_str VARCHAR2(32000);
4526: l_match_idx NUMBER;
4527: l_party_site_contains_str VARCHAR2(32000);
4528: l_contact_contains_str VARCHAR2(32000);
4529: l_contact_pt_contains_str VARCHAR2(32000);
4530: l_tmp VARCHAR2(32000);
4531:

Line 4528: l_contact_contains_str VARCHAR2(32000);

4524: -- Strings to hold the generated Intermedia query strings
4525: l_party_contains_str VARCHAR2(32000);
4526: l_match_idx NUMBER;
4527: l_party_site_contains_str VARCHAR2(32000);
4528: l_contact_contains_str VARCHAR2(32000);
4529: l_contact_pt_contains_str VARCHAR2(32000);
4530: l_tmp VARCHAR2(32000);
4531:
4532: -- Other local variables

Line 4529: l_contact_pt_contains_str VARCHAR2(32000);

4525: l_party_contains_str VARCHAR2(32000);
4526: l_match_idx NUMBER;
4527: l_party_site_contains_str VARCHAR2(32000);
4528: l_contact_contains_str VARCHAR2(32000);
4529: l_contact_pt_contains_str VARCHAR2(32000);
4530: l_tmp VARCHAR2(32000);
4531:
4532: -- Other local variables
4533: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4600: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4601:
4602: -- ************************************************************
4603: -- STEP 2. Setup of intermedia query strings for Acquisition query
4604: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4605: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4606: 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);
4607:
4608: -- Setup Search Context ID

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

4601:
4602: -- ************************************************************
4603: -- STEP 2. Setup of intermedia query strings for Acquisition query
4604: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4605: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4606: 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);
4607:
4608: -- Setup Search Context ID
4609: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4610: x_search_ctx_id := l_search_ctx_id;
4611:
4612: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4613: 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);
4614: 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);
4615: 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);
4616: 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);
4617: END IF;
4618: IF l_party_site_contains_str IS NULL THEN

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

4611:
4612: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4613: 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);
4614: 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);
4615: 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);
4616: 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);
4617: END IF;
4618: IF l_party_site_contains_str IS NULL THEN
4619: defps := 1;

Line 4618: IF l_party_site_contains_str IS NULL THEN

4614: 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);
4615: 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);
4616: 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);
4617: END IF;
4618: IF l_party_site_contains_str IS NULL THEN
4619: defps := 1;
4620: END IF;
4621: IF l_contact_pt_contains_str IS NULL THEN
4622: defcpt := 1;

Line 4621: IF l_contact_pt_contains_str IS NULL THEN

4617: END IF;
4618: IF l_party_site_contains_str IS NULL THEN
4619: defps := 1;
4620: END IF;
4621: IF l_contact_pt_contains_str IS NULL THEN
4622: defcpt := 1;
4623: END IF;
4624:
4625: IF l_party_site_contains_str IS NOT NULL THEN

Line 4625: IF l_party_site_contains_str IS NOT NULL THEN

4621: IF l_contact_pt_contains_str IS NULL THEN
4622: defcpt := 1;
4623: END IF;
4624:
4625: IF l_party_site_contains_str IS NOT NULL THEN
4626: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4627: LOOP
4628: FETCH l_party_site_cur INTO
4629: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX3;

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

4622: defcpt := 1;
4623: END IF;
4624:
4625: IF l_party_site_contains_str IS NOT NULL THEN
4626: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4627: LOOP
4628: FETCH l_party_site_cur INTO
4629: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX3;
4630: EXIT WHEN l_party_site_cur%NOTFOUND;

Line 4649: IF l_contact_pt_contains_str IS NOT NULL THEN

4645: END LOOP;
4646: CLOSE l_party_site_cur;
4647: END IF;
4648:
4649: IF l_contact_pt_contains_str IS NOT NULL THEN
4650: SAVEPOINT eval_start;
4651: IF l_match_str = ' AND ' OR (
4652: ((l_cpt_max_score/l_entered_max_score)<(80/250))
4653: ) THEN

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

4653: ) THEN
4654: FORALL I in 1..l_match_ps_list.COUNT
4655: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4656: values (l_search_ctx_id,l_match_ps_list(I));
4657: 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');
4658: ELSE
4659: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4660: END IF;
4661: LOOP

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

4655: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4656: values (l_search_ctx_id,l_match_ps_list(I));
4657: 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');
4658: ELSE
4659: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4660: END IF;
4661: LOOP
4662: FETCH l_contact_pt_cur INTO
4663: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 4759: l_party_contains_str VARCHAR2(32000);

4755: x_num_matches OUT NUMBER
4756: ) IS
4757:
4758: -- Strings to hold the generated Intermedia query strings
4759: l_party_contains_str VARCHAR2(32000);
4760: l_party_site_contains_str VARCHAR2(32000);
4761: l_contact_contains_str VARCHAR2(32000);
4762: l_contact_pt_contains_str VARCHAR2(32000);
4763: l_tmp VARCHAR2(32000);

Line 4760: l_party_site_contains_str VARCHAR2(32000);

4756: ) IS
4757:
4758: -- Strings to hold the generated Intermedia query strings
4759: l_party_contains_str VARCHAR2(32000);
4760: l_party_site_contains_str VARCHAR2(32000);
4761: l_contact_contains_str VARCHAR2(32000);
4762: l_contact_pt_contains_str VARCHAR2(32000);
4763: l_tmp VARCHAR2(32000);
4764:

Line 4761: l_contact_contains_str VARCHAR2(32000);

4757:
4758: -- Strings to hold the generated Intermedia query strings
4759: l_party_contains_str VARCHAR2(32000);
4760: l_party_site_contains_str VARCHAR2(32000);
4761: l_contact_contains_str VARCHAR2(32000);
4762: l_contact_pt_contains_str VARCHAR2(32000);
4763: l_tmp VARCHAR2(32000);
4764:
4765: -- Other local variables

Line 4762: l_contact_pt_contains_str VARCHAR2(32000);

4758: -- Strings to hold the generated Intermedia query strings
4759: l_party_contains_str VARCHAR2(32000);
4760: l_party_site_contains_str VARCHAR2(32000);
4761: l_contact_contains_str VARCHAR2(32000);
4762: l_contact_pt_contains_str VARCHAR2(32000);
4763: l_tmp VARCHAR2(32000);
4764:
4765: -- Other local variables
4766: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4829: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4830:
4831: -- ************************************************************
4832: -- STEP 2. Setup of intermedia query strings for Acquisition query
4833: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4834: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4835: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,p_contact_list,p_contact_point_list);
4836:
4837: -- Setup Search Context ID

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

4830:
4831: -- ************************************************************
4832: -- STEP 2. Setup of intermedia query strings for Acquisition query
4833: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4834: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4835: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,p_contact_list,p_contact_point_list);
4836:
4837: -- Setup Search Context ID
4838: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4839: x_search_ctx_id := l_search_ctx_id;
4840:
4841: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4842: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4843: hz_utility_v2pub.debug(p_message=>'l_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);
4844: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4845: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4846: END IF;
4847: IF l_contact_contains_str IS NULL THEN

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

4840:
4841: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4842: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4843: hz_utility_v2pub.debug(p_message=>'l_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);
4844: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4845: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4846: END IF;
4847: IF l_contact_contains_str IS NULL THEN
4848: defct := 1;

Line 4847: IF l_contact_contains_str IS NULL THEN

4843: 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);
4844: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4845: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4846: END IF;
4847: IF l_contact_contains_str IS NULL THEN
4848: defct := 1;
4849: END IF;
4850: IF l_contact_pt_contains_str IS NULL THEN
4851: defcpt := 1;

Line 4850: IF l_contact_pt_contains_str IS NULL THEN

4846: END IF;
4847: IF l_contact_contains_str IS NULL THEN
4848: defct := 1;
4849: END IF;
4850: IF l_contact_pt_contains_str IS NULL THEN
4851: defcpt := 1;
4852: END IF;
4853:
4854: IF l_contact_contains_str IS NOT NULL THEN

Line 4854: IF l_contact_contains_str IS NOT NULL THEN

4850: IF l_contact_pt_contains_str IS NULL THEN
4851: defcpt := 1;
4852: END IF;
4853:
4854: IF l_contact_contains_str IS NOT NULL THEN
4855: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
4856: LOOP
4857: FETCH l_contact_cur INTO
4858: l_org_contact_id, l_ct_party_id ;

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

4851: defcpt := 1;
4852: END IF;
4853:
4854: IF l_contact_contains_str IS NOT NULL THEN
4855: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
4856: LOOP
4857: FETCH l_contact_cur INTO
4858: l_org_contact_id, l_ct_party_id ;
4859: EXIT WHEN l_contact_cur%NOTFOUND;

Line 4876: IF l_contact_pt_contains_str IS NOT NULL THEN

4872: END LOOP;
4873: CLOSE l_contact_cur;
4874: END IF;
4875:
4876: IF l_contact_pt_contains_str IS NOT NULL THEN
4877: SAVEPOINT eval_start;
4878: IF l_match_str = ' AND ' OR (
4879: ((l_cpt_max_score/l_entered_max_score)<(80/250))
4880: ) THEN

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

4880: ) THEN
4881: FORALL I in 1..l_match_contact_list.COUNT
4882: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4883: values (l_search_ctx_id,l_match_contact_list(I));
4884: 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');
4885: ELSE
4886: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4887: END IF;
4888: LOOP

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

4882: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4883: values (l_search_ctx_id,l_match_contact_list(I));
4884: 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');
4885: ELSE
4886: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4887: END IF;
4888: LOOP
4889: FETCH l_contact_pt_cur INTO
4890: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 4984: l_contact_pt_contains_str VARCHAR2(32000);

4980: ) IS
4981:
4982:
4983: -- Strings to hold the generated Intermedia query strings
4984: l_contact_pt_contains_str VARCHAR2(32000);
4985: l_tmp VARCHAR2(32000);
4986:
4987: -- Other local variables
4988: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5038: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5039:
5040: -- ************************************************************
5041: -- STEP 2. Setup of intermedia query strings for Acquisition query
5042: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5043: 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);
5044:
5045: -- Setup Search Context ID
5046: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

5046: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5047: x_search_ctx_id := l_search_ctx_id;
5048: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5049: 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);
5050: 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);
5051: 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);
5052: END IF;
5053:
5054: IF l_contact_pt_contains_str IS NOT NULL THEN

Line 5054: IF l_contact_pt_contains_str IS NOT NULL THEN

5050: 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);
5051: 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);
5052: END IF;
5053:
5054: IF l_contact_pt_contains_str IS NOT NULL THEN
5055: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5056: cnt := 1;
5057: LOOP
5058: FETCH l_contact_pt_cur INTO

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

5051: 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);
5052: END IF;
5053:
5054: IF l_contact_pt_contains_str IS NOT NULL THEN
5055: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5056: cnt := 1;
5057: LOOP
5058: FETCH l_contact_pt_cur INTO
5059: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 5126: l_party_contains_str VARCHAR2(32000);

5122: x_search_ctx_id IN OUT NUMBER
5123: ) IS
5124:
5125: -- Strings to hold the generated Intermedia query strings
5126: l_party_contains_str VARCHAR2(32000);
5127: l_party_site_contains_str VARCHAR2(32000);
5128: l_contact_contains_str VARCHAR2(32000);
5129: l_contact_pt_contains_str VARCHAR2(32000);
5130: l_tmp VARCHAR2(32000);

Line 5127: l_party_site_contains_str VARCHAR2(32000);

5123: ) IS
5124:
5125: -- Strings to hold the generated Intermedia query strings
5126: l_party_contains_str VARCHAR2(32000);
5127: l_party_site_contains_str VARCHAR2(32000);
5128: l_contact_contains_str VARCHAR2(32000);
5129: l_contact_pt_contains_str VARCHAR2(32000);
5130: l_tmp VARCHAR2(32000);
5131:

Line 5128: l_contact_contains_str VARCHAR2(32000);

5124:
5125: -- Strings to hold the generated Intermedia query strings
5126: l_party_contains_str VARCHAR2(32000);
5127: l_party_site_contains_str VARCHAR2(32000);
5128: l_contact_contains_str VARCHAR2(32000);
5129: l_contact_pt_contains_str VARCHAR2(32000);
5130: l_tmp VARCHAR2(32000);
5131:
5132: -- Other local variables

Line 5129: l_contact_pt_contains_str VARCHAR2(32000);

5125: -- Strings to hold the generated Intermedia query strings
5126: l_party_contains_str VARCHAR2(32000);
5127: l_party_site_contains_str VARCHAR2(32000);
5128: l_contact_contains_str VARCHAR2(32000);
5129: l_contact_pt_contains_str VARCHAR2(32000);
5130: l_tmp VARCHAR2(32000);
5131:
5132: -- Other local variables
5133: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5195: g_score_until_thresh := false;
5196: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5197: -- ************************************************************
5198: -- STEP 2. Setup of intermedia query strings for Acquisition query
5199: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5200: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5201: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5202: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5203:

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

5196: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5197: -- ************************************************************
5198: -- STEP 2. Setup of intermedia query strings for Acquisition query
5199: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5200: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5201: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5202: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5203:
5204: -- Setup Search Context ID

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

5197: -- ************************************************************
5198: -- STEP 2. Setup of intermedia query strings for Acquisition query
5199: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5200: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5201: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5202: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5203:
5204: -- Setup Search Context ID
5205: IF x_search_ctx_id IS NULL THEN

Line 5221: IF l_party_site_contains_str IS NOT NULL THEN

5217: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX39,l_TX40,l_TX41,l_TX44,1);
5218: END LOOP;
5219: CLOSE l_party_cur;
5220:
5221: IF l_party_site_contains_str IS NOT NULL THEN
5222: l_max_score := 0;
5223: l_max_id := 0;
5224: l_max_idx := 0;
5225: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN

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

5222: l_max_score := 0;
5223: l_max_id := 0;
5224: l_max_idx := 0;
5225: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5226: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5227: ELSE
5228: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5229: END IF;
5230: LOOP

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

5224: l_max_idx := 0;
5225: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5226: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5227: ELSE
5228: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5229: END IF;
5230: LOOP
5231: FETCH l_party_site_cur INTO
5232: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX4, l_TX3;

Line 5249: IF l_contact_contains_str IS NOT NULL THEN

5245: 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_TX3,l_max_idx);
5246: END IF;
5247: END IF;
5248:
5249: IF l_contact_contains_str IS NOT NULL THEN
5250: l_max_score := 0;
5251: l_max_id := 0;
5252: l_max_idx := 0;
5253: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

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

5249: IF l_contact_contains_str IS NOT NULL THEN
5250: l_max_score := 0;
5251: l_max_id := 0;
5252: l_max_idx := 0;
5253: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5254: LOOP
5255: FETCH l_contact_cur INTO
5256: l_org_contact_id, l_ct_party_id ;
5257: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5271: IF l_contact_pt_contains_str IS NOT NULL THEN

5267: 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);
5268: END IF;
5269: END IF;
5270:
5271: IF l_contact_pt_contains_str IS NOT NULL THEN
5272: l_max_score := 0;
5273: l_max_id := 0;
5274: l_max_idx := 0;
5275: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN

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

5272: l_max_score := 0;
5273: l_max_id := 0;
5274: l_max_idx := 0;
5275: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5276: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5277: ELSE
5278: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5279: END IF;
5280: LOOP

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

5274: l_max_idx := 0;
5275: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5276: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5277: ELSE
5278: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5279: END IF;
5280: LOOP
5281: FETCH l_contact_pt_cur INTO
5282: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;