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_5 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 771: l_party_contains_str VARCHAR2(32000);

767: p_party_max_score NUMBER,
768: p_denorm_max_score NUMBER,
769: p_non_denorm_max_score NUMBER,
770: p_threshold NUMBER) RETURN VARCHAR2 IS
771: l_party_contains_str VARCHAR2(32000);
772: l_party_filter VARCHAR2(1) := null;
773: l_prim_temp VARCHAR2(4000);
774: l_denorm_str VARCHAR2(4000);
775: BEGIN

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

811:
812: IF l_denorm_str IS NOT NULL THEN
813: IF (p_non_denorm_max_score+50)>=p_threshold THEN
814: l_denorm_str := NULL;
815: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
816: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
817: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
818: l_denorm_str := NULL;
819: END IF;

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

813: IF (p_non_denorm_max_score+50)>=p_threshold THEN
814: l_denorm_str := NULL;
815: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
816: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
817: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
818: l_denorm_str := NULL;
819: END IF;
820: ELSE
821: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

817: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
818: l_denorm_str := NULL;
819: END IF;
820: ELSE
821: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
822: END IF;
823: IF lengthb(l_party_contains_str) > 4000 THEN
824: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
825: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');

Line 823: IF lengthb(l_party_contains_str) > 4000 THEN

819: END IF;
820: ELSE
821: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
822: END IF;
823: IF lengthb(l_party_contains_str) > 4000 THEN
824: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
825: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
826: FND_MSG_PUB.ADD;
827: RAISE FND_API.G_EXC_ERROR;

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

825: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
826: FND_MSG_PUB.ADD;
827: RAISE FND_API.G_EXC_ERROR;
828: END IF;
829: IF (p_match_str = ' AND ' OR p_party_max_score 830: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
831: 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);
832: END IF;
833: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

827: RAISE FND_API.G_EXC_ERROR;
828: END IF;
829: IF (p_match_str = ' AND ' OR p_party_max_score 830: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
831: 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);
832: END IF;
833: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
834: 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);
835: END IF;

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

830: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
831: 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);
832: END IF;
833: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
834: 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);
835: END IF;
836: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
837: 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);
838: END IF;

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

835: END IF;
836: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
837: 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);
838: END IF;
839: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
840: ELSE
841: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
842: 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);
843: 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 842: 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);

838: END IF;
839: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
840: ELSE
841: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
842: 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);
843: 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);
844: END IF;
845: RETURN l_party_contains_str;
846: END IF;

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

839: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
840: ELSE
841: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
842: 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);
843: 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);
844: END IF;
845: RETURN l_party_contains_str;
846: END IF;
847: END;

Line 845: RETURN l_party_contains_str;

841: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
842: 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);
843: 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);
844: END IF;
845: RETURN l_party_contains_str;
846: END IF;
847: END;
848: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
849: l_contains_str VARCHAR2(32000);

Line 849: l_contains_str VARCHAR2(32000);

845: RETURN l_party_contains_str;
846: END IF;
847: END;
848: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
849: l_contains_str VARCHAR2(32000);
850: l_contains_str_temp VARCHAR2(32000);
851: l_den_contains_str VARCHAR2(32000);
852: l_den_contains_str_temp VARCHAR2(32000);
853: l_filter_str VARCHAR2(4000) := null;

Line 850: l_contains_str_temp VARCHAR2(32000);

846: END IF;
847: END;
848: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
849: l_contains_str VARCHAR2(32000);
850: l_contains_str_temp VARCHAR2(32000);
851: l_den_contains_str VARCHAR2(32000);
852: l_den_contains_str_temp VARCHAR2(32000);
853: l_filter_str VARCHAR2(4000) := null;
854: l_prim_temp VARCHAR2(4000) := null;

Line 851: l_den_contains_str VARCHAR2(32000);

847: END;
848: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
849: l_contains_str VARCHAR2(32000);
850: l_contains_str_temp VARCHAR2(32000);
851: l_den_contains_str VARCHAR2(32000);
852: l_den_contains_str_temp VARCHAR2(32000);
853: l_filter_str VARCHAR2(4000) := null;
854: l_prim_temp VARCHAR2(4000) := null;
855: l_prim_temp_den VARCHAR2(4000) := null;

Line 852: l_den_contains_str_temp VARCHAR2(32000);

848: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
849: l_contains_str VARCHAR2(32000);
850: l_contains_str_temp VARCHAR2(32000);
851: l_den_contains_str VARCHAR2(32000);
852: l_den_contains_str_temp VARCHAR2(32000);
853: l_filter_str VARCHAR2(4000) := null;
854: l_prim_temp VARCHAR2(4000) := null;
855: l_prim_temp_den VARCHAR2(4000) := null;
856: BEGIN

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

858: 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);
859: END IF;
860: x_denorm_str := NULL;
861:
862: -- Dynamic setup of party site contains str
863: --
864: -- For each primary transformation add to intermedia query if it
865: -- is not null
866: FOR I IN 1..g_party_site_stage_list.COUNT LOOP

Line 867: l_contains_str_temp := null;

863: --
864: -- For each primary transformation add to intermedia query if it
865: -- is not null
866: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
867: l_contains_str_temp := null;
868: l_den_contains_str_temp := null;
869: l_filter_str := null;
870:
871: -- Setup query string for POSTAL_CODE

Line 868: l_den_contains_str_temp := null;

864: -- For each primary transformation add to intermedia query if it
865: -- is not null
866: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
867: l_contains_str_temp := null;
868: l_den_contains_str_temp := null;
869: l_filter_str := null;
870:
871: -- Setup query string for POSTAL_CODE
872: l_prim_temp := null;

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

874: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
875: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
876: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
877: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
878: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
879: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
880:
881: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
882: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);

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

875: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
876: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
877: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
878: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
879: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
880:
881: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
882: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
883: END LOOP;

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

877: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
878: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
879: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
880:
881: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
882: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
883: END LOOP;
884: -- Add the search criteria to query string
885: IF lengthb(l_contains_str) > 4000 THEN

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

878: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
879: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
880:
881: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
882: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
883: END LOOP;
884: -- Add the search criteria to query string
885: IF lengthb(l_contains_str) > 4000 THEN
886: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');

Line 885: IF lengthb(l_contains_str) > 4000 THEN

881: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
882: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
883: END LOOP;
884: -- Add the search criteria to query string
885: IF lengthb(l_contains_str) > 4000 THEN
886: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
887: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
888: FND_MSG_PUB.ADD;
889: RAISE FND_API.G_EXC_ERROR;

Line 891: x_denorm_str := l_den_contains_str;

887: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
888: FND_MSG_PUB.ADD;
889: RAISE FND_API.G_EXC_ERROR;
890: END IF;
891: x_denorm_str := l_den_contains_str;
892: RETURN l_contains_str;
893: END;
894:
895: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS

Line 892: RETURN l_contains_str;

888: FND_MSG_PUB.ADD;
889: RAISE FND_API.G_EXC_ERROR;
890: END IF;
891: x_denorm_str := l_den_contains_str;
892: RETURN l_contains_str;
893: END;
894:
895: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
896: l_contains_str VARCHAR2(32000);

Line 896: l_contains_str VARCHAR2(32000);

892: RETURN l_contains_str;
893: END;
894:
895: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
896: l_contains_str VARCHAR2(32000);
897: l_contains_str_temp VARCHAR2(32000);
898: l_den_contains_str VARCHAR2(32000);
899: l_den_contains_str_temp VARCHAR2(32000);
900: l_filter_str VARCHAR2(4000) := null;

Line 897: l_contains_str_temp VARCHAR2(32000);

893: END;
894:
895: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
896: l_contains_str VARCHAR2(32000);
897: l_contains_str_temp VARCHAR2(32000);
898: l_den_contains_str VARCHAR2(32000);
899: l_den_contains_str_temp VARCHAR2(32000);
900: l_filter_str VARCHAR2(4000) := null;
901: l_prim_temp VARCHAR2(4000) := null;

Line 898: l_den_contains_str VARCHAR2(32000);

894:
895: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
896: l_contains_str VARCHAR2(32000);
897: l_contains_str_temp VARCHAR2(32000);
898: l_den_contains_str VARCHAR2(32000);
899: l_den_contains_str_temp VARCHAR2(32000);
900: l_filter_str VARCHAR2(4000) := null;
901: l_prim_temp VARCHAR2(4000) := null;
902: l_prim_temp_den VARCHAR2(4000) := null;

Line 899: l_den_contains_str_temp VARCHAR2(32000);

895: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
896: l_contains_str VARCHAR2(32000);
897: l_contains_str_temp VARCHAR2(32000);
898: l_den_contains_str VARCHAR2(32000);
899: l_den_contains_str_temp VARCHAR2(32000);
900: l_filter_str VARCHAR2(4000) := null;
901: l_prim_temp VARCHAR2(4000) := null;
902: l_prim_temp_den VARCHAR2(4000) := null;
903: BEGIN

Line 912: l_contains_str VARCHAR2(32000);

908: RETURN NULL;
909: END;
910:
911: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
912: l_contains_str VARCHAR2(32000);
913: l_contains_str_temp VARCHAR2(32000);
914: l_den_contains_str VARCHAR2(32000);
915: l_den_contains_str_temp VARCHAR2(32000);
916: l_filter_str VARCHAR2(4000) := null;

Line 913: l_contains_str_temp VARCHAR2(32000);

909: END;
910:
911: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
912: l_contains_str VARCHAR2(32000);
913: l_contains_str_temp VARCHAR2(32000);
914: l_den_contains_str VARCHAR2(32000);
915: l_den_contains_str_temp VARCHAR2(32000);
916: l_filter_str VARCHAR2(4000) := null;
917: l_prim_temp VARCHAR2(4000) := null;

Line 914: l_den_contains_str VARCHAR2(32000);

910:
911: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
912: l_contains_str VARCHAR2(32000);
913: l_contains_str_temp VARCHAR2(32000);
914: l_den_contains_str VARCHAR2(32000);
915: l_den_contains_str_temp VARCHAR2(32000);
916: l_filter_str VARCHAR2(4000) := null;
917: l_prim_temp VARCHAR2(4000) := null;
918: l_prim_temp_den VARCHAR2(4000) := null;

Line 915: l_den_contains_str_temp VARCHAR2(32000);

911: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
912: l_contains_str VARCHAR2(32000);
913: l_contains_str_temp VARCHAR2(32000);
914: l_den_contains_str VARCHAR2(32000);
915: l_den_contains_str_temp VARCHAR2(32000);
916: l_filter_str VARCHAR2(4000) := null;
917: l_prim_temp VARCHAR2(4000) := null;
918: l_prim_temp_den VARCHAR2(4000) := null;
919: BEGIN

Line 932: p_party_contains_str VARCHAR2,

928: p_dup_party_id NUMBER,
929: p_restrict_sql VARCHAR2,
930: p_match_str VARCHAR2,
931: p_search_merged VARCHAR2,
932: p_party_contains_str VARCHAR2,
933: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
934: l_sqlstr VARCHAR2(4000);
935: l_search_merged VARCHAR2(1);
936: BEGIN

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

945: END IF;
946: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
947: 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);
948: END IF;
949: 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
950: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
951: 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);
952: END IF;
953: OPEN x_cursor FOR

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

976: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
977: l_sqlstr := l_sqlstr || ' (TX36 like :TX36||'' %'' ';
978: l_sqlstr := l_sqlstr || ' )) ';
979: END IF;
980: 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
981: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
982: 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);
983: END IF;
984: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';

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

978: l_sqlstr := l_sqlstr || ' )) ';
979: END IF;
980: 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
981: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
982: 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);
983: END IF;
984: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
985: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX11||'' %'' ';
986: l_sqlstr := l_sqlstr || ' )) ';

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

999: END IF;
1000: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1001: 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);
1002: END IF;
1003: 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
1004: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1005: 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);
1006: END IF;
1007: 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 1005: 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);

1001: 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);
1002: END IF;
1003: 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
1004: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1005: 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);
1006: END IF;
1007: 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
1008: ,g_party_stage_rec.TX36
1009: ,g_party_stage_rec.TX36

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

1017: 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
1018: ,g_party_stage_rec.TX36
1019: ,g_party_stage_rec.TX36
1020: ;
1021: 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
1022: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1023: 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);
1024: END IF;
1025: 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 1023: 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);

1019: ,g_party_stage_rec.TX36
1020: ;
1021: 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
1022: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1023: 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);
1024: END IF;
1025: 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
1026: ,g_party_site_stage_list(1).TX11
1027: ,g_party_site_stage_list(1).TX11

Line 1040: p_contains_str VARCHAR2,

1036: END;
1037: PROCEDURE open_party_cursor(
1038: p_dup_party_id NUMBER,
1039: p_restrict_sql VARCHAR2,
1040: p_contains_str VARCHAR2,
1041: p_search_ctx_id NUMBER,
1042: p_match_str VARCHAR2,
1043: p_search_merged VARCHAR2,
1044: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS

Line 1059: IF p_contains_str IS NULL THEN

1055: l_search_merged := 'N';
1056: ELSE
1057: l_search_merged := p_search_merged;
1058: END IF;
1059: IF p_contains_str IS NULL THEN
1060: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1061: 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);
1062: END IF;
1063: OPEN x_cursor FOR

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

1057: l_search_merged := p_search_merged;
1058: END IF;
1059: IF p_contains_str IS NULL THEN
1060: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1061: 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);
1062: END IF;
1063: OPEN x_cursor FOR
1064: SELECT PARTY_ID , TX2, TX8, TX4
1065: FROM HZ_STAGED_PARTIES stage

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

1074: END IF;
1075: OPEN x_cursor FOR
1076: SELECT PARTY_ID , TX2, TX8, TX4
1077: FROM HZ_STAGED_PARTIES stage
1078: WHERE contains( concat_col, p_contains_str)>0
1079: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1080: AND( (l_search_merged ='Y' )
1081: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1082: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )

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

1087: END IF;
1088: OPEN x_cursor FOR
1089: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX2, TX8, TX4
1090: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1091: WHERE contains( concat_col, p_contains_str)>0
1092: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1093: AND d.party_id = stage.party_id
1094: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1095: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)

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

1110: END IF;
1111: IF p_search_ctx_id IS NULL THEN
1112: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX2, TX8, TX4'||
1113: ' FROM HZ_STAGED_PARTIES stage'||
1114: ' WHERE contains( concat_col, :cont)>0'||
1115: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1116: ' AND ('||p_restrict_sql||')' ||
1117: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1118: IF l_search_merged = 'Y' THEN

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

1122: ELSE
1123: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1124: END IF;
1125: output_long_strings('----------------------------------------------------------');
1126: output_long_strings('Party Contains String = '||p_contains_str);
1127: output_long_strings('Restrict Sql = '||p_restrict_sql);
1128: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1129: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1130: ,p_dup_party_id, p_dup_party_id;

Line 1128: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1124: END IF;
1125: output_long_strings('----------------------------------------------------------');
1126: output_long_strings('Party Contains String = '||p_contains_str);
1127: output_long_strings('Restrict Sql = '||p_restrict_sql);
1128: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1129: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1130: ,p_dup_party_id, p_dup_party_id;
1131: END IF;
1132: END IF;

Line 1159: p_contains_str VARCHAR2,

1155: PROCEDURE open_party_site_cursor(
1156: p_dup_party_id NUMBER,
1157: p_party_id NUMBER,
1158: p_restrict_sql VARCHAR2,
1159: p_contains_str VARCHAR2,
1160: p_search_ctx_id NUMBER,
1161: p_search_merged VARCHAR2,
1162: p_search_rel_sites VARCHAR2,
1163: p_person_api VARCHAR2,

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

1187: IF p_search_rel_sites = 'N' THEN
1188: OPEN x_cursor FOR
1189: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX4, TX3
1190: FROM HZ_STAGED_PARTY_SITES stage
1191: WHERE contains( concat_col, p_contains_str)>0
1192: AND( (l_search_merged ='Y' )
1193: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1194: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1195: AND stage.party_id = p_party_id;

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

1196: ELSE
1197: OPEN x_cursor FOR
1198: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX4, TX3
1199: FROM HZ_STAGED_PARTY_SITES stage
1200: WHERE contains( concat_col, p_contains_str)>0
1201: AND( (l_search_merged ='Y' )
1202: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1203: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1204: AND stage.party_id = p_party_id

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

1204: AND stage.party_id = p_party_id
1205: UNION
1206: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX4, TX3
1207: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1208: WHERE contains( concat_col, p_contains_str)>0
1209: AND( (l_search_merged ='Y' )
1210: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1211: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1212: AND r.object_id = p_party_id

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

1226: END IF;
1227: OPEN x_cursor FOR
1228: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX4, TX3
1229: FROM HZ_STAGED_PARTY_SITES stage
1230: WHERE contains( concat_col, p_contains_str)>0
1231: AND EXISTS (
1232: SELECT 1 FROM HZ_STAGED_PARTIES p
1233: WHERE p.PARTY_ID = stage.PARTY_ID
1234: AND( (l_search_merged ='Y' )

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

1246: IF p_person_api = 'Y' THEN
1247: OPEN x_cursor FOR
1248: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX4, TX3
1249: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1250: WHERE contains( concat_col, p_contains_str)>0
1251: AND( (l_search_merged ='Y' )
1252: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1253: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1254: AND d.search_context_id = p_search_ctx_id

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

1256: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1257: UNION
1258: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX4, TX3
1259: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1260: WHERE contains( concat_col, p_contains_str)>0
1261: AND( (l_search_merged ='Y' )
1262: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1263: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1264: AND d.search_context_id = p_search_ctx_id

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

1269: ELSE
1270: OPEN x_cursor FOR
1271: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX4, TX3
1272: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1273: WHERE contains( concat_col, p_contains_str)>0
1274: AND( (l_search_merged ='Y' )
1275: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1276: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1277: AND d.search_context_id = p_search_ctx_id

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

1314: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1315: END IF;
1316: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1317: ' FROM HZ_STAGED_PARTY_SITES stage'||
1318: ' WHERE contains( concat_col, :cont)>0'||
1319: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1320: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1321: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1322: ' AND (ORG_CONTACT_ID IS NULL ' ||

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

1324: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1325: ' UNION ' ||
1326: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1327: ' FROM HZ_STAGED_PARTY_SITES stage'||
1328: ' WHERE contains( concat_col, :cont)>0'||
1329: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1330: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1331: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1332: ' AND ORG_CONTACT_ID IN ' ||

Line 1338: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1334: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1335: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1336: ' and ('||p_restrict_sql1|| ') )' ||
1337: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1338: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1339: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1340: ELSE
1341: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1342: ' FROM HZ_STAGED_PARTY_SITES stage'||

Line 1339: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1335: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1336: ' and ('||p_restrict_sql1|| ') )' ||
1337: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1338: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1339: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1340: ELSE
1341: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1342: ' FROM HZ_STAGED_PARTY_SITES stage'||
1343: ' WHERE contains( concat_col, :cont)>0'||

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

1339: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1340: ELSE
1341: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1342: ' FROM HZ_STAGED_PARTY_SITES stage'||
1343: ' WHERE contains( concat_col, :cont)>0'||
1344: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1345: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1346: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1347: ' AND EXISTS ('||

Line 1353: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1349: ' WHERE p.party_id = stage.party_id ' ||
1350: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1351: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1352: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1353: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1354: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1355: ,p_dup_party_id, p_dup_party_id;
1356: END IF;
1357: ELSE

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

1356: END IF;
1357: ELSE
1358: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1359: ' FROM HZ_STAGED_PARTY_SITES stage'||
1360: ' WHERE contains( concat_col, :cont)>0'||
1361: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1362: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1363: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1364: ' AND EXISTS ('||

Line 1370: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1366: ' WHERE p.party_id = stage.party_id ' ||
1367: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1368: ' AND ('||p_restrict_sql||')' ||
1369: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1370: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1371: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1372: ,p_dup_party_id, p_dup_party_id;
1373: END IF;
1374: END IF;

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

1372: ,p_dup_party_id, p_dup_party_id;
1373: END IF;
1374: END IF;
1375: output_long_strings('----------------------------------------------------------');
1376: output_long_strings('Party Site Contains String = '||p_contains_str);
1377: output_long_strings('Restrict Sql = '||p_restrict_sql);
1378: exception
1379: when others then
1380: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1404: p_contains_str VARCHAR2,

1400: PROCEDURE open_contact_cursor(
1401: p_dup_party_id NUMBER,
1402: p_party_id NUMBER,
1403: p_restrict_sql VARCHAR2,
1404: p_contains_str VARCHAR2,
1405: p_search_ctx_id NUMBER,
1406: p_search_merged VARCHAR2,
1407: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1408: l_sqlstr VARCHAR2(4000);

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

1428: END IF;
1429: OPEN x_cursor FOR
1430: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
1431: FROM HZ_STAGED_CONTACTS stage
1432: WHERE contains( concat_col, p_contains_str)>0
1433: AND EXISTS (
1434: SELECT 1 FROM HZ_STAGED_PARTIES p
1435: WHERE p.PARTY_ID = stage.PARTY_ID
1436: AND( (l_search_merged ='Y' )

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

1451: END IF;
1452: OPEN x_cursor FOR
1453: SELECT ORG_CONTACT_ID, PARTY_ID
1454: FROM HZ_STAGED_CONTACTS stage
1455: WHERE contains( concat_col, p_contains_str)>0
1456: AND EXISTS (
1457: SELECT 1 FROM HZ_STAGED_PARTIES p
1458: WHERE p.PARTY_ID = stage.PARTY_ID
1459: AND( (l_search_merged ='Y' )

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

1470: END IF;
1471: OPEN x_cursor FOR
1472: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
1473: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1474: WHERE contains( concat_col, p_contains_str)>0
1475: AND d.search_context_id = p_search_ctx_id
1476: AND d.party_id = stage.party_id
1477: AND( (l_search_merged ='Y' )
1478: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1504: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
1505: END IF;
1506: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
1507: ' FROM HZ_STAGED_CONTACTS stage'||
1508: ' WHERE contains( concat_col, :cont)>0'||
1509: ' AND EXISTS ('||
1510: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1511: ' WHERE p.party_id = stage.party_id ' ||
1512: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1518: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1514: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1515: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1516: ' AND ('||p_restrict_sql||')' ||
1517: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1518: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1519: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1520: ,p_dup_party_id, p_dup_party_id;
1521: END IF;
1522: output_long_strings('----------------------------------------------------------');

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

1519: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1520: ,p_dup_party_id, p_dup_party_id;
1521: END IF;
1522: output_long_strings('----------------------------------------------------------');
1523: output_long_strings('Contacts Contains String = '||p_contains_str);
1524: output_long_strings('Restrict Sql = '||p_restrict_sql);
1525: exception
1526: when others then
1527: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1551: p_contains_str VARCHAR2,

1547: PROCEDURE open_contact_pt_cursor(
1548: p_dup_party_id NUMBER,
1549: p_party_id NUMBER,
1550: p_restrict_sql VARCHAR2,
1551: p_contains_str VARCHAR2,
1552: p_search_ctx_id NUMBER,
1553: p_search_merged VARCHAR2,
1554: p_search_rel_cpts VARCHAR2,
1555: p_person_api VARCHAR2,

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

1577: THEN
1578: OPEN x_cursor FOR
1579: 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
1580: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1581: WHERE contains( concat_col, p_contains_str)>0
1582: AND d.search_context_id = p_search_ctx_id
1583: AND( (l_search_merged ='Y' )
1584: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1585: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1589: THEN
1590: OPEN x_cursor FOR
1591: 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
1592: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1593: WHERE contains( concat_col, p_contains_str)>0
1594: AND d.search_context_id = p_search_ctx_id
1595: AND( (l_search_merged ='Y' )
1596: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1597: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1606: IF p_search_rel_cpts = 'N' THEN
1607: OPEN x_cursor FOR
1608: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1609: FROM HZ_STAGED_CONTACT_POINTS stage
1610: WHERE contains( concat_col, p_contains_str)>0
1611: AND( (l_search_merged ='Y' )
1612: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1613: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1614: AND stage.party_id = p_party_id;

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

1615: ELSE
1616: OPEN x_cursor FOR
1617: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1618: FROM HZ_STAGED_CONTACT_POINTS stage
1619: WHERE contains( concat_col, p_contains_str)>0
1620: AND( (l_search_merged ='Y' )
1621: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1622: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1623: AND stage.party_id = p_party_id

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

1623: AND stage.party_id = p_party_id
1624: UNION
1625: 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
1626: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1627: WHERE contains( concat_col, p_contains_str)>0
1628: AND r.object_id = p_party_id
1629: AND( (l_search_merged ='Y' )
1630: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1631: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1645: END IF;
1646: OPEN x_cursor FOR
1647: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1648: FROM HZ_STAGED_CONTACT_POINTS stage
1649: WHERE contains( concat_col, p_contains_str)>0
1650: AND EXISTS (
1651: SELECT 1 FROM HZ_STAGED_PARTIES p
1652: WHERE p.PARTY_ID = stage.PARTY_ID
1653: AND( (l_search_merged ='Y' )

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

1665: IF p_person_api = 'Y' THEN
1666: OPEN x_cursor FOR
1667: 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
1668: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1669: WHERE contains( concat_col, p_contains_str)>0
1670: AND d.search_context_id = p_search_ctx_id
1671: AND d.party_id = stage.party_id
1672: AND( (l_search_merged ='Y' )
1673: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1675: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1676: UNION
1677: 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
1678: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1679: WHERE contains( concat_col, p_contains_str)>0
1680: AND d.search_context_id = p_search_ctx_id
1681: AND d.party_id = r.subject_id
1682: AND r.relationship_id = oc.party_relationship_id
1683: AND oc.org_contact_id = stage.org_contact_id

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

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

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

1733: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1734: END IF;
1735: 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 '||
1736: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1737: ' WHERE contains( concat_col, :cont)>0 '||
1738: ' AND (stage.org_contact_id is null '||
1739: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1740: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1741: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

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

1743: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1744: ' UNION ' ||
1745: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1746: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1747: ' WHERE contains( concat_col, :cont)>0 '||
1748: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1749: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1750: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1751: ' AND (stage.org_contact_id in ' ||

Line 1757: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1753: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1754: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1755: ' and ('||p_restrict_sql1 || ') ) )' ||
1756: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1757: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1758: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1759: ELSE
1760: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1761: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

Line 1758: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1754: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1755: ' and ('||p_restrict_sql1 || ') ) )' ||
1756: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1757: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1758: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1759: ELSE
1760: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1761: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1762: ' WHERE contains( concat_col, :cont)>0'||

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

1758: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1759: ELSE
1760: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1761: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1762: ' WHERE contains( concat_col, :cont)>0'||
1763: ' AND EXISTS ('||
1764: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1765: ' WHERE p.party_id = stage.party_id ' ||
1766: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1772: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1768: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1769: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1770: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1771: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1772: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1773: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1774: ,p_dup_party_id, p_dup_party_id;
1775: END IF;
1776: ELSE

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

1775: END IF;
1776: ELSE
1777: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1778: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1779: ' WHERE contains( concat_col, :cont)>0'||
1780: ' AND EXISTS ('||
1781: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1782: ' WHERE p.party_id = stage.party_id ' ||
1783: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1789: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

1792: END IF;
1793: END IF;
1794: END IF;
1795: output_long_strings('----------------------------------------------------------');
1796: output_long_strings('Contact Points Contains String = '||p_contains_str);
1797: output_long_strings('Restrict Sql = '||p_restrict_sql);
1798: exception
1799: when others then
1800: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1917: l_party_contains_str VARCHAR2(32000);

1913: x_num_matches OUT NUMBER
1914: ) IS
1915:
1916: -- Strings to hold the generated Intermedia query strings
1917: l_party_contains_str VARCHAR2(32000);
1918: l_party_site_contains_str VARCHAR2(32000);
1919: l_contact_contains_str VARCHAR2(32000);
1920: l_contact_pt_contains_str VARCHAR2(32000);
1921: l_denorm_str VARCHAR2(32000);

Line 1918: l_party_site_contains_str VARCHAR2(32000);

1914: ) IS
1915:
1916: -- Strings to hold the generated Intermedia query strings
1917: l_party_contains_str VARCHAR2(32000);
1918: l_party_site_contains_str VARCHAR2(32000);
1919: l_contact_contains_str VARCHAR2(32000);
1920: l_contact_pt_contains_str VARCHAR2(32000);
1921: l_denorm_str VARCHAR2(32000);
1922: l_ps_denorm_str VARCHAR2(32000);

Line 1919: l_contact_contains_str VARCHAR2(32000);

1915:
1916: -- Strings to hold the generated Intermedia query strings
1917: l_party_contains_str VARCHAR2(32000);
1918: l_party_site_contains_str VARCHAR2(32000);
1919: l_contact_contains_str VARCHAR2(32000);
1920: l_contact_pt_contains_str VARCHAR2(32000);
1921: l_denorm_str VARCHAR2(32000);
1922: l_ps_denorm_str VARCHAR2(32000);
1923: l_ct_denorm_str VARCHAR2(32000);

Line 1920: l_contact_pt_contains_str VARCHAR2(32000);

1916: -- Strings to hold the generated Intermedia query strings
1917: l_party_contains_str VARCHAR2(32000);
1918: l_party_site_contains_str VARCHAR2(32000);
1919: l_contact_contains_str VARCHAR2(32000);
1920: l_contact_pt_contains_str VARCHAR2(32000);
1921: l_denorm_str VARCHAR2(32000);
1922: l_ps_denorm_str VARCHAR2(32000);
1923: l_ct_denorm_str VARCHAR2(32000);
1924: l_cpt_denorm_str VARCHAR2(32000);

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

2005: call_type(2) := 'OR';
2006: END;
2007:
2008: /** Private procedure to acquire and score at party level ***/
2009: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2010: l_party_id_idx NUMBER:=1;
2011: l_ctx_id NUMBER;
2012: l_precalc_score BOOLEAN := FALSE;
2013: BEGIN

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

2036: l_search_ctx_id,H_PARTY_ID(I));
2037: l_party_id_idx := H_PARTY_ID.COUNT+1;
2038: ELSE
2039: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2040: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2041: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2042: 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);
2043: END IF;
2044: 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 2048: IF (l_party_site_contains_str IS NULL AND

2044: 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
2045: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2046: 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);
2047: END IF;
2048: IF (l_party_site_contains_str IS NULL AND
2049: l_contact_contains_str IS NULL AND
2050: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2051: 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);
2052: RAISE L_RETURN_IMM_EXC;

Line 2049: l_contact_contains_str IS NULL AND

2045: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2046: 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);
2047: END IF;
2048: IF (l_party_site_contains_str IS NULL AND
2049: l_contact_contains_str IS NULL AND
2050: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2051: 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);
2052: RAISE L_RETURN_IMM_EXC;
2053: ELSE

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

2046: 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);
2047: END IF;
2048: IF (l_party_site_contains_str IS NULL AND
2049: l_contact_contains_str IS NULL AND
2050: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2051: 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);
2052: RAISE L_RETURN_IMM_EXC;
2053: ELSE
2054: 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 2054: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);

2050: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2051: 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);
2052: RAISE L_RETURN_IMM_EXC;
2053: ELSE
2054: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2055: END IF;
2056: ELSE
2057: IF p_index>1 THEN
2058: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

2073: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2074: 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);
2075: END IF;
2076: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2077: 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);
2078: END IF;
2079: LOOP
2080: FETCH l_party_cur INTO
2081: l_party_id , l_TX2, l_TX8, l_TX4;

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

2095: l_party_id_idx:= l_party_id_idx+1;
2096: END IF;
2097: IF (l_party_id_idx-1)>l_max_thresh THEN
2098: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2099: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2100: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2101: AND l_contact_pt_contains_str IS NULL THEN
2102: EXIT;
2103: END IF;

Line 2101: AND l_contact_pt_contains_str IS NULL THEN

2097: IF (l_party_id_idx-1)>l_max_thresh THEN
2098: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2099: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2100: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2101: AND l_contact_pt_contains_str IS NULL THEN
2102: EXIT;
2103: END IF;
2104: CLOSE l_party_cur;
2105: IF p_index>1 THEN

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

2126: ROLLBACK to eval_start;
2127: END;
2128:
2129: /** Private procedure to acquire and score at party site level ***/
2130: 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
2131: l_party_id_idx NUMBER:=1;
2132: l_ctx_id NUMBER;
2133: h_ps_id HZ_PARTY_SEARCH.IDList;
2134: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

2138: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2139: 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);
2140: 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);
2141: END IF;
2142: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2143: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2144: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2145: l_party_id := H_SCORES.FIRST;
2146: WHILE l_party_id IS NOT NULL LOOP

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

2139: 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);
2140: 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);
2141: END IF;
2142: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2143: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2144: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2145: l_party_id := H_SCORES.FIRST;
2146: WHILE l_party_id IS NOT NULL LOOP
2147: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;

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

2140: 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);
2141: END IF;
2142: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2143: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2144: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2145: l_party_id := H_SCORES.FIRST;
2146: WHILE l_party_id IS NOT NULL LOOP
2147: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2148: l_party_id:=H_SCORES.NEXT(l_party_id);

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

2170: l_search_ctx_id,H_PARTY_ID(I));
2171: l_party_id_idx := H_PARTY_ID.COUNT+1;
2172: ELSE
2173: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2174: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2175: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2176: 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);
2177: END IF;
2178: IF p_index>1 THEN

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

2192: END IF;
2193: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2194: 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);
2195: END IF;
2196: 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);
2197: LOOP
2198: FETCH l_party_site_cur INTO
2199: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX3;
2200: EXIT WHEN l_party_site_cur%NOTFOUND;

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

2248: END IF;
2249: END;
2250:
2251: /** Private procedure to acquire and score at contact point level ***/
2252: 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
2253: l_party_id_idx NUMBER:=1;
2254: l_ctx_id NUMBER;
2255: h_ct_id HZ_PARTY_SEARCH.IDList;
2256: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

2282: l_search_ctx_id,H_PARTY_ID(I));
2283: l_party_id_idx := H_PARTY_ID.COUNT+1;
2284: ELSE
2285: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2286: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
2287: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2288: 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);
2289: END IF;
2290: IF p_index>1 THEN

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

2304: END IF;
2305: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2306: 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);
2307: END IF;
2308: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2309: LOOP
2310: FETCH l_contact_cur INTO
2311: l_org_contact_id, l_ct_party_id ;
2312: EXIT WHEN l_contact_cur%NOTFOUND;

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

2357: END IF;
2358: END;
2359:
2360: /** Private procedure to acquire and score at contact point level ***/
2361: 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
2362: l_party_id_idx NUMBER:=1;
2363: l_ctx_id NUMBER;
2364: h_cpt_id HZ_PARTY_SEARCH.IDList;
2365: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

2393: l_search_ctx_id,H_PARTY_ID(I));
2394: l_party_id_idx := H_PARTY_ID.COUNT+1;
2395: ELSE
2396: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2397: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
2398: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2399: 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);
2400: END IF;
2401: IF p_index>1 THEN

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

2415: END IF;
2416: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2417: 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);
2418: END IF;
2419: 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);
2420: LOOP
2421: FETCH l_contact_pt_cur INTO
2422: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
2423: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 2519: IF l_party_contains_str IS NOT NULL THEN

2515: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
2516: l_threshold := 65;
2517: other_acq_criteria_exists := TRUE ;
2518: --check if acquisition criteria exists for any other entity
2519: IF l_party_contains_str IS NOT NULL THEN
2520: acq_cnt := 1;
2521: END IF;
2522: IF l_party_site_contains_str IS NOT NULL THEN
2523: acq_cnt := acq_cnt+1;

Line 2522: IF l_party_site_contains_str IS NOT NULL THEN

2518: --check if acquisition criteria exists for any other entity
2519: IF l_party_contains_str IS NOT NULL THEN
2520: acq_cnt := 1;
2521: END IF;
2522: IF l_party_site_contains_str IS NOT NULL THEN
2523: acq_cnt := acq_cnt+1;
2524: END IF;
2525: IF l_contact_contains_str IS NOT NULL THEN
2526: acq_cnt := acq_cnt+1;

Line 2525: IF l_contact_contains_str IS NOT NULL THEN

2521: END IF;
2522: IF l_party_site_contains_str IS NOT NULL THEN
2523: acq_cnt := acq_cnt+1;
2524: END IF;
2525: IF l_contact_contains_str IS NOT NULL THEN
2526: acq_cnt := acq_cnt+1;
2527: END IF;
2528: IF l_contact_pt_contains_str IS NOT NULL THEN
2529: acq_cnt := acq_cnt+1;

Line 2528: IF l_contact_pt_contains_str IS NOT NULL THEN

2524: END IF;
2525: IF l_contact_contains_str IS NOT NULL THEN
2526: acq_cnt := acq_cnt+1;
2527: END IF;
2528: IF l_contact_pt_contains_str IS NOT NULL THEN
2529: acq_cnt := acq_cnt+1;
2530: END IF;
2531: IF acq_cnt>1 THEN
2532: other_acq_criteria_exists := TRUE;

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

2558: FND_MSG_PUB.ADD;
2559: RAISE FND_API.G_EXC_ERROR;
2560: END IF;
2561: END IF;
2562: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2563: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2564: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2565: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2566: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN

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

2559: RAISE FND_API.G_EXC_ERROR;
2560: END IF;
2561: END IF;
2562: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2563: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2564: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2565: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2566: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2567: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

2560: END IF;
2561: END IF;
2562: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2563: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2564: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2565: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2566: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2567: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2568: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

2561: END IF;
2562: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2563: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2564: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2565: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2566: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2567: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2568: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2569: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

2562: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2563: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2564: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2565: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2566: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2567: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2568: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2569: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2570: END IF;

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

2563: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2564: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2565: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2566: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2567: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2568: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2569: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2570: END IF;
2571: END;

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

2564: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2565: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2566: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2567: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2568: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2569: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2570: END IF;
2571: END;
2572: /************ End of find_parties private procedures **********/

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

2565: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2566: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2567: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2568: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2569: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2570: END IF;
2571: END;
2572: /************ End of find_parties private procedures **********/
2573:

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

2648: 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);
2649: END IF;
2650: -- ************************************************************
2651: -- STEP 2. Setup of intermedia query strings for Acquisition query
2652: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2653: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2654: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2655: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2656: 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 2653: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);

2649: END IF;
2650: -- ************************************************************
2651: -- STEP 2. Setup of intermedia query strings for Acquisition query
2652: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2653: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2654: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2655: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2656: 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);
2657: END IF;

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

2650: -- ************************************************************
2651: -- STEP 2. Setup of intermedia query strings for Acquisition query
2652: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2653: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2654: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2655: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2656: 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);
2657: END IF;
2658: l_denorm_max_score:=0;

Line 2686: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, l_denorm_str, l_party_max_score, l_denorm_max_score, l_non_denorm_max_score, round((65/110)*l_entered_max_score));

2682: END IF;
2683: ELSE
2684: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
2685: END IF;
2686: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, l_denorm_str, l_party_max_score, l_denorm_max_score, l_non_denorm_max_score, round((65/110)*l_entered_max_score));
2687: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
2688:
2689: -- Setup Search Context ID
2690: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 2696: IF l_party_contains_str IS NULL THEN

2692:
2693: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2694: 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);
2695: END IF;
2696: IF l_party_contains_str IS NULL THEN
2697: defpt := 1;
2698: END IF;
2699: IF l_party_site_contains_str IS NULL THEN
2700: defps := 1;

Line 2699: IF l_party_site_contains_str IS NULL THEN

2695: END IF;
2696: IF l_party_contains_str IS NULL THEN
2697: defpt := 1;
2698: END IF;
2699: IF l_party_site_contains_str IS NULL THEN
2700: defps := 1;
2701: END IF;
2702: IF l_contact_contains_str IS NULL THEN
2703: defct := 1;

Line 2702: IF l_contact_contains_str IS NULL THEN

2698: END IF;
2699: IF l_party_site_contains_str IS NULL THEN
2700: defps := 1;
2701: END IF;
2702: IF l_contact_contains_str IS NULL THEN
2703: defct := 1;
2704: END IF;
2705: IF l_contact_pt_contains_str IS NULL THEN
2706: defcpt := 1;

Line 2705: IF l_contact_pt_contains_str IS NULL THEN

2701: END IF;
2702: IF l_contact_contains_str IS NULL THEN
2703: defct := 1;
2704: END IF;
2705: IF l_contact_pt_contains_str IS NULL THEN
2706: defcpt := 1;
2707: END IF;
2708:
2709: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

2710: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2711: 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);
2712: END IF;
2713: 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);
2714: 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);
2715: 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);
2716: 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);
2717: 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);
2718: 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 2715: 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);

2711: 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);
2712: END IF;
2713: 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);
2714: 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);
2715: 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);
2716: 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);
2717: 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);
2718: 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);
2719: END IF;

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

2712: END IF;
2713: 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);
2714: 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);
2715: 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);
2716: 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);
2717: 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);
2718: 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);
2719: END IF;
2720: FOR I in 1..5 LOOP

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

2713: 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);
2714: 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);
2715: 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);
2716: 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);
2717: 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);
2718: 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);
2719: END IF;
2720: FOR I in 1..5 LOOP
2721: do_eval(I);

Line 2831: l_party_contains_str VARCHAR2(32000);

2827: x_num_matches OUT NUMBER
2828: ) IS
2829:
2830: -- Strings to hold the generated Intermedia query strings
2831: l_party_contains_str VARCHAR2(32000);
2832: l_party_site_contains_str VARCHAR2(32000);
2833: l_contact_contains_str VARCHAR2(32000);
2834: l_contact_pt_contains_str VARCHAR2(32000);
2835: l_denorm_str VARCHAR2(32000);

Line 2832: l_party_site_contains_str VARCHAR2(32000);

2828: ) IS
2829:
2830: -- Strings to hold the generated Intermedia query strings
2831: l_party_contains_str VARCHAR2(32000);
2832: l_party_site_contains_str VARCHAR2(32000);
2833: l_contact_contains_str VARCHAR2(32000);
2834: l_contact_pt_contains_str VARCHAR2(32000);
2835: l_denorm_str VARCHAR2(32000);
2836: l_ps_denorm_str VARCHAR2(32000);

Line 2833: l_contact_contains_str VARCHAR2(32000);

2829:
2830: -- Strings to hold the generated Intermedia query strings
2831: l_party_contains_str VARCHAR2(32000);
2832: l_party_site_contains_str VARCHAR2(32000);
2833: l_contact_contains_str VARCHAR2(32000);
2834: l_contact_pt_contains_str VARCHAR2(32000);
2835: l_denorm_str VARCHAR2(32000);
2836: l_ps_denorm_str VARCHAR2(32000);
2837: l_ct_denorm_str VARCHAR2(32000);

Line 2834: l_contact_pt_contains_str VARCHAR2(32000);

2830: -- Strings to hold the generated Intermedia query strings
2831: l_party_contains_str VARCHAR2(32000);
2832: l_party_site_contains_str VARCHAR2(32000);
2833: l_contact_contains_str VARCHAR2(32000);
2834: l_contact_pt_contains_str VARCHAR2(32000);
2835: l_denorm_str VARCHAR2(32000);
2836: l_ps_denorm_str VARCHAR2(32000);
2837: l_ct_denorm_str VARCHAR2(32000);
2838: l_cpt_denorm_str VARCHAR2(32000);

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

2949: call_type(2) := 'OR';
2950: END;
2951:
2952: /** Private procedure to acquire and score at party level ***/
2953: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2954: l_party_id_idx NUMBER:=1;
2955: l_ctx_id NUMBER;
2956: BEGIN
2957: SAVEPOINT eval_start;

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

2970: l_search_ctx_id,H_PARTY_ID(I));
2971: l_party_id_idx := H_PARTY_ID.COUNT+1;
2972: ELSE
2973: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2974: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2975: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2976: 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);
2977: END IF;
2978: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN

Line 2982: IF (l_party_site_contains_str IS NULL AND

2978: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
2979: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2980: 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);
2981: END IF;
2982: IF (l_party_site_contains_str IS NULL AND
2983: l_contact_contains_str IS NULL AND
2984: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
2985: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
2986: RAISE L_RETURN_IMM_EXC;

Line 2983: l_contact_contains_str IS NULL AND

2979: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2980: 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);
2981: END IF;
2982: IF (l_party_site_contains_str IS NULL AND
2983: l_contact_contains_str IS NULL AND
2984: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
2985: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
2986: RAISE L_RETURN_IMM_EXC;
2987: ELSE

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

2980: 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);
2981: END IF;
2982: IF (l_party_site_contains_str IS NULL AND
2983: l_contact_contains_str IS NULL AND
2984: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
2985: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
2986: RAISE L_RETURN_IMM_EXC;
2987: ELSE
2988: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);

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

2984: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
2985: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
2986: RAISE L_RETURN_IMM_EXC;
2987: ELSE
2988: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
2989: END IF;
2990: ELSE
2991: IF p_index>1 THEN
2992: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3004: l_ctx_id := NULL;
3005: l_party_id_idx := H_PARTY_ID.COUNT+1;
3006: END IF;
3007: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3008: 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);
3009: END IF;
3010: LOOP
3011: FETCH l_party_cur INTO
3012: l_party_id , l_TX2, l_TX8, l_TX4;

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

3026: l_party_id_idx:= l_party_id_idx+1;
3027: END IF;
3028: IF (l_party_id_idx-1)>l_max_thresh THEN
3029: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3030: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3031: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3032: AND l_contact_pt_contains_str IS NULL THEN
3033: H_PARTY_ID.DELETE(l_party_id_idx-1);
3034: H_PARTY_ID_LIST.DELETE(l_index);

Line 3032: AND l_contact_pt_contains_str IS NULL THEN

3028: IF (l_party_id_idx-1)>l_max_thresh THEN
3029: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3030: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3031: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3032: AND l_contact_pt_contains_str IS NULL THEN
3033: H_PARTY_ID.DELETE(l_party_id_idx-1);
3034: H_PARTY_ID_LIST.DELETE(l_index);
3035: H_SCORES.DELETE(l_index);
3036: EXIT;

Line 3061: p_contains_str VARCHAR2,

3057: CLOSE l_party_cur;
3058: ROLLBACK to eval_start;
3059: END;
3060: PROCEDURE open_person_contact_cursor(
3061: p_contains_str VARCHAR2,
3062: p_search_ctx_id NUMBER,
3063: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3064: BEGIN
3065: OPEN x_cursor FOR

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

3064: BEGIN
3065: OPEN x_cursor FOR
3066: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
3067: FROM HZ_STAGED_CONTACTS stage
3068: WHERE contains( concat_col, p_contains_str)>0
3069: AND ORG_CONTACT_ID in (
3070: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3071: org_contact_id
3072: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc

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

3088: end if;
3089: END;
3090:
3091: /** Private procedure to acquire and score at party site level ***/
3092: 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
3093: l_party_id_idx NUMBER:=1;
3094: l_ctx_id NUMBER;
3095: h_ps_id HZ_PARTY_SEARCH.IDList;
3096: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

3116: ELSE
3117: l_party_id_idx := H_PARTY_ID.COUNT+1;
3118: l_ctx_id := NULL;
3119: END IF;
3120: 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);
3121: LOOP
3122: FETCH l_party_site_cur INTO
3123: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX3;
3124: EXIT WHEN l_party_site_cur%NOTFOUND;

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

3187: END IF;
3188: END;
3189:
3190: /** Private procedure to acquire and score at party site level ***/
3191: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3192: l_party_id_idx NUMBER:=1;
3193: l_ctx_id NUMBER;
3194: h_ct_id HZ_PARTY_SEARCH.IDList;
3195: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

3203: FORALL I in 1..H_PARTY_ID.COUNT
3204: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
3205: l_search_ctx_id,H_PARTY_ID(I));
3206: l_party_id_idx := H_PARTY_ID.COUNT+1;
3207: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
3208: LOOP
3209: FETCH l_contact_cur INTO
3210: l_org_contact_id, l_ct_party_id ;
3211: EXIT WHEN l_contact_cur%NOTFOUND;

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

3245: END IF;
3246: END;
3247:
3248: /** Private procedure to acquire and score at contact point level ***/
3249: 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
3250: l_party_id_idx NUMBER:=1;
3251: l_ctx_id NUMBER;
3252: h_cpt_id HZ_PARTY_SEARCH.IDList;
3253: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

3290: END IF;
3291: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3292: 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);
3293: END IF;
3294: 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);
3295: LOOP
3296: FETCH l_contact_pt_cur INTO
3297: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
3298: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 3402: IF l_party_contains_str IS NOT NULL THEN

3398: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3399: l_threshold := 65;
3400: other_acq_criteria_exists := TRUE ;
3401: --check if acquisition criteria exists for any other entity
3402: IF l_party_contains_str IS NOT NULL THEN
3403: acq_cnt := 1;
3404: END IF;
3405: IF l_party_site_contains_str IS NOT NULL THEN
3406: acq_cnt := acq_cnt+1;

Line 3405: IF l_party_site_contains_str IS NOT NULL THEN

3401: --check if acquisition criteria exists for any other entity
3402: IF l_party_contains_str IS NOT NULL THEN
3403: acq_cnt := 1;
3404: END IF;
3405: IF l_party_site_contains_str IS NOT NULL THEN
3406: acq_cnt := acq_cnt+1;
3407: END IF;
3408: IF l_contact_contains_str IS NOT NULL THEN
3409: acq_cnt := acq_cnt+1;

Line 3408: IF l_contact_contains_str IS NOT NULL THEN

3404: END IF;
3405: IF l_party_site_contains_str IS NOT NULL THEN
3406: acq_cnt := acq_cnt+1;
3407: END IF;
3408: IF l_contact_contains_str IS NOT NULL THEN
3409: acq_cnt := acq_cnt+1;
3410: END IF;
3411: IF l_contact_pt_contains_str IS NOT NULL THEN
3412: acq_cnt := acq_cnt+1;

Line 3411: IF l_contact_pt_contains_str IS NOT NULL THEN

3407: END IF;
3408: IF l_contact_contains_str IS NOT NULL THEN
3409: acq_cnt := acq_cnt+1;
3410: END IF;
3411: IF l_contact_pt_contains_str IS NOT NULL THEN
3412: acq_cnt := acq_cnt+1;
3413: END IF;
3414: IF acq_cnt>1 THEN
3415: other_acq_criteria_exists := TRUE;

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

3441: FND_MSG_PUB.ADD;
3442: RAISE FND_API.G_EXC_ERROR;
3443: END IF;
3444: END IF;
3445: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3446: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3447: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3448: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3449: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

3442: RAISE FND_API.G_EXC_ERROR;
3443: END IF;
3444: END IF;
3445: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3446: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3447: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3448: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3449: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3450: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

3443: END IF;
3444: END IF;
3445: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3446: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3447: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3448: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3449: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3450: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3451: END IF;

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

3444: END IF;
3445: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3446: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3447: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3448: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3449: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3450: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3451: END IF;
3452: END;

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

3445: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3446: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3447: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3448: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3449: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3450: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3451: END IF;
3452: END;
3453: /************ End of find_persons private procedures **********/

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

3446: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3447: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3448: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3449: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3450: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3451: END IF;
3452: END;
3453: /************ End of find_persons private procedures **********/
3454:

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

3481: g_score_until_thresh := false;
3482: END IF;
3483: -- ************************************************************
3484: -- STEP 2. Setup of intermedia query strings for Acquisition query
3485: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3486: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3487: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3488: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3489: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);

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

3482: END IF;
3483: -- ************************************************************
3484: -- STEP 2. Setup of intermedia query strings for Acquisition query
3485: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3486: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3487: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3488: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3489: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3490:

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

3483: -- ************************************************************
3484: -- STEP 2. Setup of intermedia query strings for Acquisition query
3485: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3486: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3487: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3488: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3489: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3490:
3491: -- Setup Search Context ID

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

3484: -- STEP 2. Setup of intermedia query strings for Acquisition query
3485: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3486: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3487: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3488: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3489: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3490:
3491: -- Setup Search Context ID
3492: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 3495: IF l_party_contains_str IS NULL THEN

3491: -- Setup Search Context ID
3492: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3493: x_search_ctx_id := l_search_ctx_id;
3494:
3495: IF l_party_contains_str IS NULL THEN
3496: defpt := 1;
3497: END IF;
3498: IF l_party_site_contains_str IS NULL THEN
3499: defps := 1;

Line 3498: IF l_party_site_contains_str IS NULL THEN

3494:
3495: IF l_party_contains_str IS NULL THEN
3496: defpt := 1;
3497: END IF;
3498: IF l_party_site_contains_str IS NULL THEN
3499: defps := 1;
3500: END IF;
3501: IF l_contact_contains_str IS NULL THEN
3502: defct := 1;

Line 3501: IF l_contact_contains_str IS NULL THEN

3497: END IF;
3498: IF l_party_site_contains_str IS NULL THEN
3499: defps := 1;
3500: END IF;
3501: IF l_contact_contains_str IS NULL THEN
3502: defct := 1;
3503: END IF;
3504: IF l_contact_pt_contains_str IS NULL THEN
3505: defcpt := 1;

Line 3504: IF l_contact_pt_contains_str IS NULL THEN

3500: END IF;
3501: IF l_contact_contains_str IS NULL THEN
3502: defct := 1;
3503: END IF;
3504: IF l_contact_pt_contains_str IS NULL THEN
3505: defcpt := 1;
3506: END IF;
3507:
3508: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3506: END IF;
3507:
3508: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3509: 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);
3510: 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);
3511: 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);
3512: 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);
3513: 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);
3514: 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 3511: 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);

3507:
3508: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3509: 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);
3510: 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);
3511: 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);
3512: 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);
3513: 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);
3514: 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);
3515: END IF;

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

3508: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3509: 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);
3510: 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);
3511: 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);
3512: 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);
3513: 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);
3514: 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);
3515: END IF;
3516: FOR I in 1..5 LOOP

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

3509: 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);
3510: 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);
3511: 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);
3512: 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);
3513: 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);
3514: 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);
3515: END IF;
3516: FOR I in 1..5 LOOP
3517: do_eval(I);

Line 3519: IF l_contact_contains_str IS NOT NULL THEN

3515: END IF;
3516: FOR I in 1..5 LOOP
3517: do_eval(I);
3518: END LOOP;
3519: IF l_contact_contains_str IS NOT NULL THEN
3520: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3521: END IF;
3522: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3523: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 59 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

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

3516: FOR I in 1..5 LOOP
3517: do_eval(I);
3518: END LOOP;
3519: IF l_contact_contains_str IS NOT NULL THEN
3520: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3521: END IF;
3522: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3523: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 59 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3524: END IF;

Line 4300: l_party_contains_str VARCHAR2(32000);

4296: x_num_matches OUT NUMBER
4297: ) IS
4298:
4299: -- Strings to hold the generated Intermedia query strings
4300: l_party_contains_str VARCHAR2(32000);
4301: l_match_idx NUMBER;
4302: l_party_site_contains_str VARCHAR2(32000);
4303: l_contact_contains_str VARCHAR2(32000);
4304: l_contact_pt_contains_str VARCHAR2(32000);

Line 4302: l_party_site_contains_str VARCHAR2(32000);

4298:
4299: -- Strings to hold the generated Intermedia query strings
4300: l_party_contains_str VARCHAR2(32000);
4301: l_match_idx NUMBER;
4302: l_party_site_contains_str VARCHAR2(32000);
4303: l_contact_contains_str VARCHAR2(32000);
4304: l_contact_pt_contains_str VARCHAR2(32000);
4305: l_tmp VARCHAR2(32000);
4306:

Line 4303: l_contact_contains_str VARCHAR2(32000);

4299: -- Strings to hold the generated Intermedia query strings
4300: l_party_contains_str VARCHAR2(32000);
4301: l_match_idx NUMBER;
4302: l_party_site_contains_str VARCHAR2(32000);
4303: l_contact_contains_str VARCHAR2(32000);
4304: l_contact_pt_contains_str VARCHAR2(32000);
4305: l_tmp VARCHAR2(32000);
4306:
4307: -- Other local variables

Line 4304: l_contact_pt_contains_str VARCHAR2(32000);

4300: l_party_contains_str VARCHAR2(32000);
4301: l_match_idx NUMBER;
4302: l_party_site_contains_str VARCHAR2(32000);
4303: l_contact_contains_str VARCHAR2(32000);
4304: l_contact_pt_contains_str VARCHAR2(32000);
4305: l_tmp VARCHAR2(32000);
4306:
4307: -- Other local variables
4308: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4375: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4376:
4377: -- ************************************************************
4378: -- STEP 2. Setup of intermedia query strings for Acquisition query
4379: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4380: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4381: 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);
4382:
4383: -- Setup Search Context ID

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

4376:
4377: -- ************************************************************
4378: -- STEP 2. Setup of intermedia query strings for Acquisition query
4379: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4380: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4381: 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);
4382:
4383: -- Setup Search Context ID
4384: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4385: x_search_ctx_id := l_search_ctx_id;
4386:
4387: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4388: 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);
4389: 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);
4390: 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);
4391: 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);
4392: END IF;
4393: IF l_party_site_contains_str IS NULL THEN

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

4386:
4387: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4388: 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);
4389: 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);
4390: 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);
4391: 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);
4392: END IF;
4393: IF l_party_site_contains_str IS NULL THEN
4394: defps := 1;

Line 4393: IF l_party_site_contains_str IS NULL THEN

4389: 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);
4390: 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);
4391: 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);
4392: END IF;
4393: IF l_party_site_contains_str IS NULL THEN
4394: defps := 1;
4395: END IF;
4396: IF l_contact_pt_contains_str IS NULL THEN
4397: defcpt := 1;

Line 4396: IF l_contact_pt_contains_str IS NULL THEN

4392: END IF;
4393: IF l_party_site_contains_str IS NULL THEN
4394: defps := 1;
4395: END IF;
4396: IF l_contact_pt_contains_str IS NULL THEN
4397: defcpt := 1;
4398: END IF;
4399:
4400: IF l_party_site_contains_str IS NOT NULL THEN

Line 4400: IF l_party_site_contains_str IS NOT NULL THEN

4396: IF l_contact_pt_contains_str IS NULL THEN
4397: defcpt := 1;
4398: END IF;
4399:
4400: IF l_party_site_contains_str IS NOT NULL THEN
4401: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4402: LOOP
4403: FETCH l_party_site_cur INTO
4404: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX3;

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

4397: defcpt := 1;
4398: END IF;
4399:
4400: IF l_party_site_contains_str IS NOT NULL THEN
4401: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4402: LOOP
4403: FETCH l_party_site_cur INTO
4404: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX3;
4405: EXIT WHEN l_party_site_cur%NOTFOUND;

Line 4424: IF l_contact_pt_contains_str IS NOT NULL THEN

4420: END LOOP;
4421: CLOSE l_party_site_cur;
4422: END IF;
4423:
4424: IF l_contact_pt_contains_str IS NOT NULL THEN
4425: SAVEPOINT eval_start;
4426: IF l_match_str = ' AND ' OR (
4427: ((l_cpt_max_score/l_entered_max_score)<(65/110))
4428: ) THEN

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

4428: ) THEN
4429: FORALL I in 1..l_match_ps_list.COUNT
4430: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4431: values (l_search_ctx_id,l_match_ps_list(I));
4432: 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');
4433: ELSE
4434: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4435: END IF;
4436: LOOP

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

4430: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4431: values (l_search_ctx_id,l_match_ps_list(I));
4432: 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');
4433: ELSE
4434: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4435: END IF;
4436: LOOP
4437: FETCH l_contact_pt_cur INTO
4438: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 4534: l_party_contains_str VARCHAR2(32000);

4530: x_num_matches OUT NUMBER
4531: ) IS
4532:
4533: -- Strings to hold the generated Intermedia query strings
4534: l_party_contains_str VARCHAR2(32000);
4535: l_party_site_contains_str VARCHAR2(32000);
4536: l_contact_contains_str VARCHAR2(32000);
4537: l_contact_pt_contains_str VARCHAR2(32000);
4538: l_tmp VARCHAR2(32000);

Line 4535: l_party_site_contains_str VARCHAR2(32000);

4531: ) IS
4532:
4533: -- Strings to hold the generated Intermedia query strings
4534: l_party_contains_str VARCHAR2(32000);
4535: l_party_site_contains_str VARCHAR2(32000);
4536: l_contact_contains_str VARCHAR2(32000);
4537: l_contact_pt_contains_str VARCHAR2(32000);
4538: l_tmp VARCHAR2(32000);
4539:

Line 4536: l_contact_contains_str VARCHAR2(32000);

4532:
4533: -- Strings to hold the generated Intermedia query strings
4534: l_party_contains_str VARCHAR2(32000);
4535: l_party_site_contains_str VARCHAR2(32000);
4536: l_contact_contains_str VARCHAR2(32000);
4537: l_contact_pt_contains_str VARCHAR2(32000);
4538: l_tmp VARCHAR2(32000);
4539:
4540: -- Other local variables

Line 4537: l_contact_pt_contains_str VARCHAR2(32000);

4533: -- Strings to hold the generated Intermedia query strings
4534: l_party_contains_str VARCHAR2(32000);
4535: l_party_site_contains_str VARCHAR2(32000);
4536: l_contact_contains_str VARCHAR2(32000);
4537: l_contact_pt_contains_str VARCHAR2(32000);
4538: l_tmp VARCHAR2(32000);
4539:
4540: -- Other local variables
4541: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4604: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4605:
4606: -- ************************************************************
4607: -- STEP 2. Setup of intermedia query strings for Acquisition query
4608: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4609: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4610: 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);
4611:
4612: -- Setup Search Context ID

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

4605:
4606: -- ************************************************************
4607: -- STEP 2. Setup of intermedia query strings for Acquisition query
4608: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4609: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4610: 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);
4611:
4612: -- Setup Search Context ID
4613: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4614: x_search_ctx_id := l_search_ctx_id;
4615:
4616: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4617: 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);
4618: 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);
4619: 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);
4620: 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);
4621: END IF;
4622: IF l_contact_contains_str IS NULL THEN

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

4615:
4616: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4617: 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);
4618: 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);
4619: 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);
4620: 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);
4621: END IF;
4622: IF l_contact_contains_str IS NULL THEN
4623: defct := 1;

Line 4622: IF l_contact_contains_str IS NULL THEN

4618: 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);
4619: 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);
4620: 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);
4621: END IF;
4622: IF l_contact_contains_str IS NULL THEN
4623: defct := 1;
4624: END IF;
4625: IF l_contact_pt_contains_str IS NULL THEN
4626: defcpt := 1;

Line 4625: IF l_contact_pt_contains_str IS NULL THEN

4621: END IF;
4622: IF l_contact_contains_str IS NULL THEN
4623: defct := 1;
4624: END IF;
4625: IF l_contact_pt_contains_str IS NULL THEN
4626: defcpt := 1;
4627: END IF;
4628:
4629: IF l_contact_contains_str IS NOT NULL THEN

Line 4629: IF l_contact_contains_str IS NOT NULL THEN

4625: IF l_contact_pt_contains_str IS NULL THEN
4626: defcpt := 1;
4627: END IF;
4628:
4629: IF l_contact_contains_str IS NOT NULL THEN
4630: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
4631: LOOP
4632: FETCH l_contact_cur INTO
4633: l_org_contact_id, l_ct_party_id ;

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

4626: defcpt := 1;
4627: END IF;
4628:
4629: IF l_contact_contains_str IS NOT NULL THEN
4630: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
4631: LOOP
4632: FETCH l_contact_cur INTO
4633: l_org_contact_id, l_ct_party_id ;
4634: EXIT WHEN l_contact_cur%NOTFOUND;

Line 4651: IF l_contact_pt_contains_str IS NOT NULL THEN

4647: END LOOP;
4648: CLOSE l_contact_cur;
4649: END IF;
4650:
4651: IF l_contact_pt_contains_str IS NOT NULL THEN
4652: SAVEPOINT eval_start;
4653: IF l_match_str = ' AND ' OR (
4654: ((l_cpt_max_score/l_entered_max_score)<(65/110))
4655: ) THEN

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

4655: ) THEN
4656: FORALL I in 1..l_match_contact_list.COUNT
4657: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4658: values (l_search_ctx_id,l_match_contact_list(I));
4659: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur,'CONTACTS');
4660: ELSE
4661: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4662: END IF;
4663: LOOP

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

4657: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4658: values (l_search_ctx_id,l_match_contact_list(I));
4659: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur,'CONTACTS');
4660: ELSE
4661: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4662: END IF;
4663: LOOP
4664: FETCH l_contact_pt_cur INTO
4665: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 4759: l_contact_pt_contains_str VARCHAR2(32000);

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

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

4813: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4814:
4815: -- ************************************************************
4816: -- STEP 2. Setup of intermedia query strings for Acquisition query
4817: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4818: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,p_contact_point_list);
4819:
4820: -- Setup Search Context ID
4821: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4821: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4822: x_search_ctx_id := l_search_ctx_id;
4823: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4824: 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);
4825: 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);
4826: 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);
4827: END IF;
4828:
4829: IF l_contact_pt_contains_str IS NOT NULL THEN

Line 4829: IF l_contact_pt_contains_str IS NOT NULL THEN

4825: 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);
4826: 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);
4827: END IF;
4828:
4829: IF l_contact_pt_contains_str IS NOT NULL THEN
4830: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4831: cnt := 1;
4832: LOOP
4833: FETCH l_contact_pt_cur INTO

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

4826: 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);
4827: END IF;
4828:
4829: IF l_contact_pt_contains_str IS NOT NULL THEN
4830: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4831: cnt := 1;
4832: LOOP
4833: FETCH l_contact_pt_cur INTO
4834: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 4901: l_party_contains_str VARCHAR2(32000);

4897: x_search_ctx_id IN OUT NUMBER
4898: ) IS
4899:
4900: -- Strings to hold the generated Intermedia query strings
4901: l_party_contains_str VARCHAR2(32000);
4902: l_party_site_contains_str VARCHAR2(32000);
4903: l_contact_contains_str VARCHAR2(32000);
4904: l_contact_pt_contains_str VARCHAR2(32000);
4905: l_tmp VARCHAR2(32000);

Line 4902: l_party_site_contains_str VARCHAR2(32000);

4898: ) IS
4899:
4900: -- Strings to hold the generated Intermedia query strings
4901: l_party_contains_str VARCHAR2(32000);
4902: l_party_site_contains_str VARCHAR2(32000);
4903: l_contact_contains_str VARCHAR2(32000);
4904: l_contact_pt_contains_str VARCHAR2(32000);
4905: l_tmp VARCHAR2(32000);
4906:

Line 4903: l_contact_contains_str VARCHAR2(32000);

4899:
4900: -- Strings to hold the generated Intermedia query strings
4901: l_party_contains_str VARCHAR2(32000);
4902: l_party_site_contains_str VARCHAR2(32000);
4903: l_contact_contains_str VARCHAR2(32000);
4904: l_contact_pt_contains_str VARCHAR2(32000);
4905: l_tmp VARCHAR2(32000);
4906:
4907: -- Other local variables

Line 4904: l_contact_pt_contains_str VARCHAR2(32000);

4900: -- Strings to hold the generated Intermedia query strings
4901: l_party_contains_str VARCHAR2(32000);
4902: l_party_site_contains_str VARCHAR2(32000);
4903: l_contact_contains_str VARCHAR2(32000);
4904: l_contact_pt_contains_str VARCHAR2(32000);
4905: l_tmp VARCHAR2(32000);
4906:
4907: -- Other local variables
4908: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4965: g_score_until_thresh := false;
4966: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4967: -- ************************************************************
4968: -- STEP 2. Setup of intermedia query strings for Acquisition query
4969: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4970: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4971: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4972: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4973:

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

4966: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4967: -- ************************************************************
4968: -- STEP 2. Setup of intermedia query strings for Acquisition query
4969: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4970: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4971: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4972: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4973:
4974: -- Setup Search Context ID

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

4967: -- ************************************************************
4968: -- STEP 2. Setup of intermedia query strings for Acquisition query
4969: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4970: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4971: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4972: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4973:
4974: -- Setup Search Context ID
4975: IF x_search_ctx_id IS NULL THEN

Line 4991: IF l_party_site_contains_str IS NOT NULL THEN

4987: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX2,l_TX8,l_TX4,1);
4988: END LOOP;
4989: CLOSE l_party_cur;
4990:
4991: IF l_party_site_contains_str IS NOT NULL THEN
4992: l_max_score := 0;
4993: l_max_id := 0;
4994: l_max_idx := 0;
4995: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN

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

4992: l_max_score := 0;
4993: l_max_id := 0;
4994: l_max_idx := 0;
4995: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
4996: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
4997: ELSE
4998: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
4999: END IF;
5000: LOOP

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

4994: l_max_idx := 0;
4995: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
4996: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
4997: ELSE
4998: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
4999: END IF;
5000: LOOP
5001: FETCH l_party_site_cur INTO
5002: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX4, l_TX3;

Line 5019: IF l_contact_contains_str IS NOT NULL THEN

5015: 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);
5016: END IF;
5017: END IF;
5018:
5019: IF l_contact_contains_str IS NOT NULL THEN
5020: l_max_score := 0;
5021: l_max_id := 0;
5022: l_max_idx := 0;
5023: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

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

5019: IF l_contact_contains_str IS NOT NULL THEN
5020: l_max_score := 0;
5021: l_max_id := 0;
5022: l_max_idx := 0;
5023: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5024: LOOP
5025: FETCH l_contact_cur INTO
5026: l_org_contact_id, l_ct_party_id ;
5027: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5041: IF l_contact_pt_contains_str IS NOT NULL THEN

5037: 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);
5038: END IF;
5039: END IF;
5040:
5041: IF l_contact_pt_contains_str IS NOT NULL THEN
5042: l_max_score := 0;
5043: l_max_id := 0;
5044: l_max_idx := 0;
5045: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN

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

5042: l_max_score := 0;
5043: l_max_id := 0;
5044: l_max_idx := 0;
5045: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5046: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5047: ELSE
5048: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5049: END IF;
5050: LOOP

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

5044: l_max_idx := 0;
5045: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5046: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5047: ELSE
5048: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5049: END IF;
5050: LOOP
5051: FETCH l_contact_pt_cur INTO
5052: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;