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 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 1210: ' WHERE contains( concat_col, :cont)>0'||

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

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

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

Line 1224: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

Line 1255: p_contains_str VARCHAR2,

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

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

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

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

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

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

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

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

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

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

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

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

1352: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1353: UNION
1354: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX4, TX3
1355: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1356: WHERE contains( concat_col, p_contains_str)>0
1357: AND( (l_search_merged ='Y' )
1358: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1359: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1360: AND d.search_context_id = p_search_ctx_id

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

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

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

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

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

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

Line 1434: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

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

Line 1435: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

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

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

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

Line 1449: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 1466: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 1500: p_contains_str VARCHAR2,

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

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

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

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

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

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

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

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

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

Line 1614: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 1647: p_contains_str VARCHAR2,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 1853: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

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

Line 1854: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

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

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

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

Line 1868: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 1885: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 2013: l_party_contains_str VARCHAR2(32000);

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

Line 2014: l_party_site_contains_str VARCHAR2(32000);

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

Line 2015: l_contact_contains_str VARCHAR2(32000);

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

Line 2016: l_contact_pt_contains_str VARCHAR2(32000);

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

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

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

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

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

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

Line 2147: l_contact_contains_str IS NULL AND

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

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

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

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

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

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

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

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

Line 2199: AND l_contact_pt_contains_str IS NULL THEN

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 2698: IF l_party_contains_str IS NOT NULL THEN

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

Line 2701: IF l_party_site_contains_str IS NOT NULL THEN

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

Line 2704: IF l_contact_contains_str IS NOT NULL THEN

2700: END IF;
2701: IF l_party_site_contains_str IS NOT NULL THEN
2702: acq_cnt := acq_cnt+1;
2703: END IF;
2704: IF l_contact_contains_str IS NOT NULL THEN
2705: acq_cnt := acq_cnt+1;
2706: END IF;
2707: IF l_contact_pt_contains_str IS NOT NULL THEN
2708: acq_cnt := acq_cnt+1;

Line 2707: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2850: END IF;
2851: ELSE
2852: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
2853: END IF;
2854: 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));
2855: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
2856:
2857: -- Setup Search Context ID
2858: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 2864: IF l_party_contains_str IS NULL THEN

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

Line 2867: IF l_party_site_contains_str IS NULL THEN

2863: END IF;
2864: IF l_party_contains_str IS NULL THEN
2865: defpt := 1;
2866: END IF;
2867: IF l_party_site_contains_str IS NULL THEN
2868: defps := 1;
2869: END IF;
2870: IF l_contact_contains_str IS NULL THEN
2871: defct := 1;

Line 2870: IF l_contact_contains_str IS NULL THEN

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

Line 2873: IF l_contact_pt_contains_str IS NULL THEN

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

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

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

2879: 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);
2880: END IF;
2881: 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);
2882: 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);
2883: 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);
2884: 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);
2885: 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);
2886: 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);
2887: END IF;

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

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

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

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

Line 2967: l_party_contains_str VARCHAR2(32000);

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

Line 2968: l_party_site_contains_str VARCHAR2(32000);

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

Line 2969: l_contact_contains_str VARCHAR2(32000);

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

Line 2970: l_contact_pt_contains_str VARCHAR2(32000);

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

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

3087: call_type(2) := 'OR';
3088: END;
3089:
3090: /** Private procedure to acquire and score at party level ***/
3091: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3092: l_party_id_idx NUMBER:=1;
3093: l_ctx_id NUMBER;
3094: BEGIN
3095: SAVEPOINT eval_start;

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

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

Line 3120: IF (l_party_site_contains_str IS NULL AND

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

Line 3121: l_contact_contains_str IS NULL AND

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

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

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

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

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

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

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

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

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

Line 3170: AND l_contact_pt_contains_str IS NULL THEN

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

Line 3199: p_contains_str VARCHAR2,

3195: CLOSE l_party_cur;
3196: ROLLBACK to eval_start;
3197: END;
3198: PROCEDURE open_person_contact_cursor(
3199: p_contains_str VARCHAR2,
3200: p_search_ctx_id NUMBER,
3201: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3202: BEGIN
3203: OPEN x_cursor FOR

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 3635: IF l_party_contains_str IS NOT NULL THEN

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

Line 3638: IF l_party_site_contains_str IS NOT NULL THEN

3634: --check if acquisition criteria exists for any other entity
3635: IF l_party_contains_str IS NOT NULL THEN
3636: acq_cnt := 1;
3637: END IF;
3638: IF l_party_site_contains_str IS NOT NULL THEN
3639: acq_cnt := acq_cnt+1;
3640: END IF;
3641: IF l_contact_contains_str IS NOT NULL THEN
3642: acq_cnt := acq_cnt+1;

Line 3641: IF l_contact_contains_str IS NOT NULL THEN

3637: END IF;
3638: IF l_party_site_contains_str IS NOT NULL THEN
3639: acq_cnt := acq_cnt+1;
3640: END IF;
3641: IF l_contact_contains_str IS NOT NULL THEN
3642: acq_cnt := acq_cnt+1;
3643: END IF;
3644: IF l_contact_pt_contains_str IS NOT NULL THEN
3645: acq_cnt := acq_cnt+1;

Line 3644: IF l_contact_pt_contains_str IS NOT NULL THEN

3640: END IF;
3641: IF l_contact_contains_str IS NOT NULL THEN
3642: acq_cnt := acq_cnt+1;
3643: END IF;
3644: IF l_contact_pt_contains_str IS NOT NULL THEN
3645: acq_cnt := acq_cnt+1;
3646: END IF;
3647: IF acq_cnt>1 THEN
3648: other_acq_criteria_exists := TRUE;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 3728: IF l_party_contains_str IS NULL THEN

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

Line 3731: IF l_party_site_contains_str IS NULL THEN

3727:
3728: IF l_party_contains_str IS NULL THEN
3729: defpt := 1;
3730: END IF;
3731: IF l_party_site_contains_str IS NULL THEN
3732: defps := 1;
3733: END IF;
3734: IF l_contact_contains_str IS NULL THEN
3735: defct := 1;

Line 3734: IF l_contact_contains_str IS NULL THEN

3730: END IF;
3731: IF l_party_site_contains_str IS NULL THEN
3732: defps := 1;
3733: END IF;
3734: IF l_contact_contains_str IS NULL THEN
3735: defct := 1;
3736: END IF;
3737: IF l_contact_pt_contains_str IS NULL THEN
3738: defcpt := 1;

Line 3737: IF l_contact_pt_contains_str IS NULL THEN

3733: END IF;
3734: IF l_contact_contains_str IS NULL THEN
3735: defct := 1;
3736: END IF;
3737: IF l_contact_pt_contains_str IS NULL THEN
3738: defcpt := 1;
3739: END IF;
3740:
3741: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3739: END IF;
3740:
3741: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3742: 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);
3743: 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);
3744: 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);
3745: 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);
3746: 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);
3747: 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 3744: 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);

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

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

3741: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3742: 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);
3743: 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);
3744: 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);
3745: 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);
3746: 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);
3747: 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);
3748: END IF;
3749: FOR I in 1..5 LOOP

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

3742: 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);
3743: 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);
3744: 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);
3745: 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);
3746: 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);
3747: 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);
3748: END IF;
3749: FOR I in 1..5 LOOP
3750: do_eval(I);

Line 3752: IF l_contact_contains_str IS NOT NULL THEN

3748: END IF;
3749: FOR I in 1..5 LOOP
3750: do_eval(I);
3751: END LOOP;
3752: IF l_contact_contains_str IS NOT NULL THEN
3753: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3754: END IF;
3755: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3756: 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 3753: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);

3749: FOR I in 1..5 LOOP
3750: do_eval(I);
3751: END LOOP;
3752: IF l_contact_contains_str IS NOT NULL THEN
3753: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3754: END IF;
3755: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3756: 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);
3757: END IF;

Line 4521: l_party_contains_str VARCHAR2(32000);

4517: x_num_matches OUT NUMBER
4518: ) IS
4519:
4520: -- Strings to hold the generated Intermedia query strings
4521: l_party_contains_str VARCHAR2(32000);
4522: l_match_idx NUMBER;
4523: l_party_site_contains_str VARCHAR2(32000);
4524: l_contact_contains_str VARCHAR2(32000);
4525: l_contact_pt_contains_str VARCHAR2(32000);

Line 4523: l_party_site_contains_str VARCHAR2(32000);

4519:
4520: -- Strings to hold the generated Intermedia query strings
4521: l_party_contains_str VARCHAR2(32000);
4522: l_match_idx NUMBER;
4523: l_party_site_contains_str VARCHAR2(32000);
4524: l_contact_contains_str VARCHAR2(32000);
4525: l_contact_pt_contains_str VARCHAR2(32000);
4526: l_tmp VARCHAR2(32000);
4527:

Line 4524: l_contact_contains_str VARCHAR2(32000);

4520: -- Strings to hold the generated Intermedia query strings
4521: l_party_contains_str VARCHAR2(32000);
4522: l_match_idx NUMBER;
4523: l_party_site_contains_str VARCHAR2(32000);
4524: l_contact_contains_str VARCHAR2(32000);
4525: l_contact_pt_contains_str VARCHAR2(32000);
4526: l_tmp VARCHAR2(32000);
4527:
4528: -- Other local variables

Line 4525: l_contact_pt_contains_str VARCHAR2(32000);

4521: l_party_contains_str VARCHAR2(32000);
4522: l_match_idx NUMBER;
4523: l_party_site_contains_str VARCHAR2(32000);
4524: l_contact_contains_str VARCHAR2(32000);
4525: l_contact_pt_contains_str VARCHAR2(32000);
4526: l_tmp VARCHAR2(32000);
4527:
4528: -- Other local variables
4529: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

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

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

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

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

4606: x_search_ctx_id := l_search_ctx_id;
4607:
4608: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4609: 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);
4610: 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);
4611: 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);
4612: 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);
4613: END IF;
4614: IF l_party_site_contains_str IS NULL THEN

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

4607:
4608: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4609: 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);
4610: 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);
4611: 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);
4612: 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);
4613: END IF;
4614: IF l_party_site_contains_str IS NULL THEN
4615: defps := 1;

Line 4614: IF l_party_site_contains_str IS NULL THEN

4610: 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);
4611: 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);
4612: 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);
4613: END IF;
4614: IF l_party_site_contains_str IS NULL THEN
4615: defps := 1;
4616: END IF;
4617: IF l_contact_pt_contains_str IS NULL THEN
4618: defcpt := 1;

Line 4617: IF l_contact_pt_contains_str IS NULL THEN

4613: END IF;
4614: IF l_party_site_contains_str IS NULL THEN
4615: defps := 1;
4616: END IF;
4617: IF l_contact_pt_contains_str IS NULL THEN
4618: defcpt := 1;
4619: END IF;
4620:
4621: IF l_party_site_contains_str IS NOT NULL THEN

Line 4621: IF l_party_site_contains_str IS NOT NULL THEN

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

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

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

Line 4645: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

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

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

Line 4755: l_party_contains_str VARCHAR2(32000);

4751: x_num_matches OUT NUMBER
4752: ) IS
4753:
4754: -- Strings to hold the generated Intermedia query strings
4755: l_party_contains_str VARCHAR2(32000);
4756: l_party_site_contains_str VARCHAR2(32000);
4757: l_contact_contains_str VARCHAR2(32000);
4758: l_contact_pt_contains_str VARCHAR2(32000);
4759: l_tmp VARCHAR2(32000);

Line 4756: l_party_site_contains_str VARCHAR2(32000);

4752: ) IS
4753:
4754: -- Strings to hold the generated Intermedia query strings
4755: l_party_contains_str VARCHAR2(32000);
4756: l_party_site_contains_str VARCHAR2(32000);
4757: l_contact_contains_str VARCHAR2(32000);
4758: l_contact_pt_contains_str VARCHAR2(32000);
4759: l_tmp VARCHAR2(32000);
4760:

Line 4757: l_contact_contains_str VARCHAR2(32000);

4753:
4754: -- Strings to hold the generated Intermedia query strings
4755: l_party_contains_str VARCHAR2(32000);
4756: l_party_site_contains_str VARCHAR2(32000);
4757: l_contact_contains_str VARCHAR2(32000);
4758: l_contact_pt_contains_str VARCHAR2(32000);
4759: l_tmp VARCHAR2(32000);
4760:
4761: -- Other local variables

Line 4758: l_contact_pt_contains_str VARCHAR2(32000);

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

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

4825: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4826:
4827: -- ************************************************************
4828: -- STEP 2. Setup of intermedia query strings for Acquisition query
4829: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4830: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4831: 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);
4832:
4833: -- Setup Search Context ID

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

4826:
4827: -- ************************************************************
4828: -- STEP 2. Setup of intermedia query strings for Acquisition query
4829: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4830: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4831: 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);
4832:
4833: -- Setup Search Context ID
4834: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4835: x_search_ctx_id := l_search_ctx_id;
4836:
4837: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4838: 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);
4839: 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);
4840: 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);
4841: 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);
4842: END IF;
4843: IF l_contact_contains_str IS NULL THEN

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

4836:
4837: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4838: 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);
4839: 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);
4840: 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);
4841: 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);
4842: END IF;
4843: IF l_contact_contains_str IS NULL THEN
4844: defct := 1;

Line 4843: IF l_contact_contains_str IS NULL THEN

4839: 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);
4840: 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);
4841: 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);
4842: END IF;
4843: IF l_contact_contains_str IS NULL THEN
4844: defct := 1;
4845: END IF;
4846: IF l_contact_pt_contains_str IS NULL THEN
4847: defcpt := 1;

Line 4846: IF l_contact_pt_contains_str IS NULL THEN

4842: END IF;
4843: IF l_contact_contains_str IS NULL THEN
4844: defct := 1;
4845: END IF;
4846: IF l_contact_pt_contains_str IS NULL THEN
4847: defcpt := 1;
4848: END IF;
4849:
4850: IF l_contact_contains_str IS NOT NULL THEN

Line 4850: IF l_contact_contains_str IS NOT NULL THEN

4846: IF l_contact_pt_contains_str IS NULL THEN
4847: defcpt := 1;
4848: END IF;
4849:
4850: IF l_contact_contains_str IS NOT NULL THEN
4851: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
4852: LOOP
4853: FETCH l_contact_cur INTO
4854: l_org_contact_id, l_ct_party_id ;

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

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

Line 4872: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

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

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

Line 4980: l_contact_pt_contains_str VARCHAR2(32000);

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

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

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

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

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

Line 5050: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

Line 5122: l_party_contains_str VARCHAR2(32000);

5118: x_search_ctx_id IN OUT NUMBER
5119: ) IS
5120:
5121: -- Strings to hold the generated Intermedia query strings
5122: l_party_contains_str VARCHAR2(32000);
5123: l_party_site_contains_str VARCHAR2(32000);
5124: l_contact_contains_str VARCHAR2(32000);
5125: l_contact_pt_contains_str VARCHAR2(32000);
5126: l_tmp VARCHAR2(32000);

Line 5123: l_party_site_contains_str VARCHAR2(32000);

5119: ) IS
5120:
5121: -- Strings to hold the generated Intermedia query strings
5122: l_party_contains_str VARCHAR2(32000);
5123: l_party_site_contains_str VARCHAR2(32000);
5124: l_contact_contains_str VARCHAR2(32000);
5125: l_contact_pt_contains_str VARCHAR2(32000);
5126: l_tmp VARCHAR2(32000);
5127:

Line 5124: l_contact_contains_str VARCHAR2(32000);

5120:
5121: -- Strings to hold the generated Intermedia query strings
5122: l_party_contains_str VARCHAR2(32000);
5123: l_party_site_contains_str VARCHAR2(32000);
5124: l_contact_contains_str VARCHAR2(32000);
5125: l_contact_pt_contains_str VARCHAR2(32000);
5126: l_tmp VARCHAR2(32000);
5127:
5128: -- Other local variables

Line 5125: l_contact_pt_contains_str VARCHAR2(32000);

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

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

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

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

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

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

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

Line 5216: IF l_party_site_contains_str IS NOT NULL THEN

5212: 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);
5213: END LOOP;
5214: CLOSE l_party_cur;
5215:
5216: IF l_party_site_contains_str IS NOT NULL THEN
5217: l_max_score := 0;
5218: l_max_id := 0;
5219: l_max_idx := 0;
5220: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN

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

5217: l_max_score := 0;
5218: l_max_id := 0;
5219: l_max_idx := 0;
5220: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5221: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5222: ELSE
5223: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5224: END IF;
5225: LOOP

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

5219: l_max_idx := 0;
5220: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5221: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5222: ELSE
5223: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5224: END IF;
5225: LOOP
5226: FETCH l_party_site_cur INTO
5227: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX4, l_TX3;

Line 5244: IF l_contact_contains_str IS NOT NULL THEN

5240: 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);
5241: END IF;
5242: END IF;
5243:
5244: IF l_contact_contains_str IS NOT NULL THEN
5245: l_max_score := 0;
5246: l_max_id := 0;
5247: l_max_idx := 0;
5248: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

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

5244: IF l_contact_contains_str IS NOT NULL THEN
5245: l_max_score := 0;
5246: l_max_id := 0;
5247: l_max_idx := 0;
5248: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5249: LOOP
5250: FETCH l_contact_cur INTO
5251: l_org_contact_id, l_ct_party_id ;
5252: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5266: IF l_contact_pt_contains_str IS NOT NULL THEN

5262: 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);
5263: END IF;
5264: END IF;
5265:
5266: IF l_contact_pt_contains_str IS NOT NULL THEN
5267: l_max_score := 0;
5268: l_max_id := 0;
5269: l_max_idx := 0;
5270: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN

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

5267: l_max_score := 0;
5268: l_max_id := 0;
5269: l_max_idx := 0;
5270: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5271: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5272: ELSE
5273: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5274: END IF;
5275: LOOP

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

5269: l_max_idx := 0;
5270: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5271: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5272: ELSE
5273: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5274: END IF;
5275: LOOP
5276: FETCH l_contact_pt_cur INTO
5277: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;