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_3 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 786: l_party_contains_str VARCHAR2(32000);

782: p_party_max_score NUMBER,
783: p_denorm_max_score NUMBER,
784: p_non_denorm_max_score NUMBER,
785: p_threshold NUMBER) RETURN VARCHAR2 IS
786: l_party_contains_str VARCHAR2(32000);
787: l_party_filter VARCHAR2(1) := null;
788: l_prim_temp VARCHAR2(4000);
789: l_denorm_str VARCHAR2(4000);
790: BEGIN

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

826:
827: IF l_denorm_str IS NOT NULL THEN
828: IF (p_non_denorm_max_score+20)>=p_threshold THEN
829: l_denorm_str := NULL;
830: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
831: ELSIF (p_non_denorm_max_score+p_denorm_max_score+20)>=p_threshold THEN
832: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
833: l_denorm_str := NULL;
834: END IF;

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

828: IF (p_non_denorm_max_score+20)>=p_threshold THEN
829: l_denorm_str := NULL;
830: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
831: ELSIF (p_non_denorm_max_score+p_denorm_max_score+20)>=p_threshold THEN
832: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
833: l_denorm_str := NULL;
834: END IF;
835: ELSE
836: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

832: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
833: l_denorm_str := NULL;
834: END IF;
835: ELSE
836: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
837: END IF;
838: IF lengthb(l_party_contains_str) > 4000 THEN
839: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
840: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');

Line 838: IF lengthb(l_party_contains_str) > 4000 THEN

834: END IF;
835: ELSE
836: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
837: END IF;
838: IF lengthb(l_party_contains_str) > 4000 THEN
839: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
840: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
841: FND_MSG_PUB.ADD;
842: RAISE FND_API.G_EXC_ERROR;

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

840: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
841: FND_MSG_PUB.ADD;
842: RAISE FND_API.G_EXC_ERROR;
843: END IF;
844: IF (p_match_str = ' AND ' OR p_party_max_score 845: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
846: 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);
847: END IF;
848: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

842: RAISE FND_API.G_EXC_ERROR;
843: END IF;
844: IF (p_match_str = ' AND ' OR p_party_max_score 845: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
846: 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);
847: END IF;
848: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
849: 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);
850: END IF;

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

845: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
846: 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);
847: END IF;
848: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
849: 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);
850: END IF;
851: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
852: 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);
853: END IF;

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

850: END IF;
851: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
852: 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);
853: END IF;
854: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
855: ELSE
856: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
857: 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);
858: 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 857: 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);

853: END IF;
854: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
855: ELSE
856: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
857: 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);
858: 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);
859: END IF;
860: RETURN l_party_contains_str;
861: END IF;

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

854: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
855: ELSE
856: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
857: 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);
858: 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);
859: END IF;
860: RETURN l_party_contains_str;
861: END IF;
862: END;

Line 860: RETURN l_party_contains_str;

856: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
857: 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);
858: 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);
859: END IF;
860: RETURN l_party_contains_str;
861: END IF;
862: END;
863: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
864: l_contains_str VARCHAR2(32000);

Line 864: l_contains_str VARCHAR2(32000);

860: RETURN l_party_contains_str;
861: END IF;
862: END;
863: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
864: l_contains_str VARCHAR2(32000);
865: l_contains_str_temp VARCHAR2(32000);
866: l_den_contains_str VARCHAR2(32000);
867: l_den_contains_str_temp VARCHAR2(32000);
868: l_filter_str VARCHAR2(4000) := null;

Line 865: l_contains_str_temp VARCHAR2(32000);

861: END IF;
862: END;
863: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
864: l_contains_str VARCHAR2(32000);
865: l_contains_str_temp VARCHAR2(32000);
866: l_den_contains_str VARCHAR2(32000);
867: l_den_contains_str_temp VARCHAR2(32000);
868: l_filter_str VARCHAR2(4000) := null;
869: l_prim_temp VARCHAR2(4000) := null;

Line 866: l_den_contains_str VARCHAR2(32000);

862: END;
863: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
864: l_contains_str VARCHAR2(32000);
865: l_contains_str_temp VARCHAR2(32000);
866: l_den_contains_str VARCHAR2(32000);
867: l_den_contains_str_temp VARCHAR2(32000);
868: l_filter_str VARCHAR2(4000) := null;
869: l_prim_temp VARCHAR2(4000) := null;
870: l_prim_temp_den VARCHAR2(4000) := null;

Line 867: l_den_contains_str_temp VARCHAR2(32000);

863: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
864: l_contains_str VARCHAR2(32000);
865: l_contains_str_temp VARCHAR2(32000);
866: l_den_contains_str VARCHAR2(32000);
867: l_den_contains_str_temp VARCHAR2(32000);
868: l_filter_str VARCHAR2(4000) := null;
869: l_prim_temp VARCHAR2(4000) := null;
870: l_prim_temp_den VARCHAR2(4000) := null;
871: BEGIN

Line 880: l_contains_str VARCHAR2(32000);

876: RETURN NULL;
877: END;
878:
879: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
880: l_contains_str VARCHAR2(32000);
881: l_contains_str_temp VARCHAR2(32000);
882: l_den_contains_str VARCHAR2(32000);
883: l_den_contains_str_temp VARCHAR2(32000);
884: l_filter_str VARCHAR2(4000) := null;

Line 881: l_contains_str_temp VARCHAR2(32000);

877: END;
878:
879: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
880: l_contains_str VARCHAR2(32000);
881: l_contains_str_temp VARCHAR2(32000);
882: l_den_contains_str VARCHAR2(32000);
883: l_den_contains_str_temp VARCHAR2(32000);
884: l_filter_str VARCHAR2(4000) := null;
885: l_prim_temp VARCHAR2(4000) := null;

Line 882: l_den_contains_str VARCHAR2(32000);

878:
879: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
880: l_contains_str VARCHAR2(32000);
881: l_contains_str_temp VARCHAR2(32000);
882: l_den_contains_str VARCHAR2(32000);
883: l_den_contains_str_temp VARCHAR2(32000);
884: l_filter_str VARCHAR2(4000) := null;
885: l_prim_temp VARCHAR2(4000) := null;
886: l_prim_temp_den VARCHAR2(4000) := null;

Line 883: l_den_contains_str_temp VARCHAR2(32000);

879: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
880: l_contains_str VARCHAR2(32000);
881: l_contains_str_temp VARCHAR2(32000);
882: l_den_contains_str VARCHAR2(32000);
883: l_den_contains_str_temp VARCHAR2(32000);
884: l_filter_str VARCHAR2(4000) := null;
885: l_prim_temp VARCHAR2(4000) := null;
886: l_prim_temp_den VARCHAR2(4000) := null;
887: BEGIN

Line 896: l_contains_str VARCHAR2(32000);

892: RETURN NULL;
893: END;
894:
895: FUNCTION INIT_CONTACT_POINTS_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_CONTACT_POINTS_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_CONTACT_POINTS_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_CONTACT_POINTS_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 909: -- Dynamic setup of party site contains str

905: hz_utility_v2pub.debug(p_message=>'INIT_CONTACT_POINTS_QUERY ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
906: END IF;
907: x_denorm_str := NULL;
908:
909: -- Dynamic setup of party site contains str
910: --
911: -- For each primary transformation add to intermedia query if it
912: -- is not null
913: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP

Line 914: l_contains_str_temp := null;

910: --
911: -- For each primary transformation add to intermedia query if it
912: -- is not null
913: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
914: l_contains_str_temp := null;
915: l_den_contains_str_temp := null;
916: l_filter_str := null;
917: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
918:

Line 915: l_den_contains_str_temp := null;

911: -- For each primary transformation add to intermedia query if it
912: -- is not null
913: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
914: l_contains_str_temp := null;
915: l_den_contains_str_temp := null;
916: l_filter_str := null;
917: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
918:
919: -- Setup query string for FLEX_FORMAT_PHONE_NUMBER

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

920: l_prim_temp := null;
921: l_prim_temp_den := null;
922: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL CTX
923: g_contact_pt_stage_list(I).TX1,'A44',l_prim_temp);
924: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
925:
926: -- Setup query string for EMAIL_ADDRESS
927: l_prim_temp := null;
928: l_prim_temp_den := null;

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

927: l_prim_temp := null;
928: l_prim_temp_den := null;
929: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (EMAIL) + WRDOMAIN
930: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
931: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
932:
933: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
934: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
935: END LOOP;

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

929: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (EMAIL) + WRDOMAIN
930: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
931: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
932:
933: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
934: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
935: END LOOP;
936: -- Add the search criteria to query string
937: IF lengthb(l_contains_str) > 4000 THEN

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

930: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
931: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
932:
933: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
934: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
935: END LOOP;
936: -- Add the search criteria to query string
937: IF lengthb(l_contains_str) > 4000 THEN
938: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');

Line 937: IF lengthb(l_contains_str) > 4000 THEN

933: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
934: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
935: END LOOP;
936: -- Add the search criteria to query string
937: IF lengthb(l_contains_str) > 4000 THEN
938: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
939: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
940: FND_MSG_PUB.ADD;
941: RAISE FND_API.G_EXC_ERROR;

Line 943: x_denorm_str := l_den_contains_str;

939: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
940: FND_MSG_PUB.ADD;
941: RAISE FND_API.G_EXC_ERROR;
942: END IF;
943: x_denorm_str := l_den_contains_str;
944: RETURN l_contains_str;
945: END;
946:
947: PROCEDURE open_party_cursor_direct (

Line 944: RETURN l_contains_str;

940: FND_MSG_PUB.ADD;
941: RAISE FND_API.G_EXC_ERROR;
942: END IF;
943: x_denorm_str := l_den_contains_str;
944: RETURN l_contains_str;
945: END;
946:
947: PROCEDURE open_party_cursor_direct (
948: p_dup_party_id NUMBER,

Line 952: p_party_contains_str VARCHAR2,

948: p_dup_party_id NUMBER,
949: p_restrict_sql VARCHAR2,
950: p_match_str VARCHAR2,
951: p_search_merged VARCHAR2,
952: p_party_contains_str VARCHAR2,
953: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
954: l_sqlstr VARCHAR2(4000);
955: l_search_merged VARCHAR2(1);
956: BEGIN

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

965: END IF;
966: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
967: 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);
968: END IF;
969: 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
970: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
971: 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);
972: END IF;
973: OPEN x_cursor FOR

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

1000: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1001: l_sqlstr := l_sqlstr || ' (TX36 like :TX36||'' %'' ';
1002: l_sqlstr := l_sqlstr || ' )) ';
1003: END IF;
1004: 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
1005: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1006: 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);
1007: END IF;
1008: null;

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

1002: l_sqlstr := l_sqlstr || ' )) ';
1003: END IF;
1004: 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
1005: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1006: 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);
1007: END IF;
1008: null;
1009: END IF;
1010: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

1021: END IF;
1022: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1023: 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);
1024: END IF;
1025: 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
1026: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1027: 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);
1028: END IF;
1029: 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 1027: 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);

1023: 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);
1024: END IF;
1025: 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
1026: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1027: 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);
1028: END IF;
1029: 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
1030: ,g_party_stage_rec.TX44
1031: ,g_party_stage_rec.TX44

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

1041: ,g_party_stage_rec.TX44
1042: ,g_party_stage_rec.TX36
1043: ,g_party_stage_rec.TX36
1044: ;
1045: 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
1046: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1047: 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);
1048: END IF;
1049: 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 1047: 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);

1043: ,g_party_stage_rec.TX36
1044: ;
1045: 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
1046: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1047: 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);
1048: END IF;
1049: 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
1050: ;
1051: ELSE

Line 1062: p_contains_str VARCHAR2,

1058: END;
1059: PROCEDURE open_party_cursor(
1060: p_dup_party_id NUMBER,
1061: p_restrict_sql VARCHAR2,
1062: p_contains_str VARCHAR2,
1063: p_search_ctx_id NUMBER,
1064: p_match_str VARCHAR2,
1065: p_search_merged VARCHAR2,
1066: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS

Line 1081: IF p_contains_str IS NULL THEN

1077: l_search_merged := 'N';
1078: ELSE
1079: l_search_merged := p_search_merged;
1080: END IF;
1081: IF p_contains_str IS NULL THEN
1082: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1083: 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);
1084: END IF;
1085: OPEN x_cursor FOR

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

1079: l_search_merged := p_search_merged;
1080: END IF;
1081: IF p_contains_str IS NULL THEN
1082: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1083: 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);
1084: END IF;
1085: OPEN x_cursor FOR
1086: SELECT PARTY_ID , TX44
1087: FROM HZ_STAGED_PARTIES stage

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

1096: END IF;
1097: OPEN x_cursor FOR
1098: SELECT PARTY_ID , TX44
1099: FROM HZ_STAGED_PARTIES stage
1100: WHERE contains( concat_col, p_contains_str)>0
1101: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1102: AND( (l_search_merged ='Y' )
1103: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1104: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )

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

1109: END IF;
1110: OPEN x_cursor FOR
1111: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX44
1112: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1113: WHERE contains( concat_col, p_contains_str)>0
1114: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1115: AND d.party_id = stage.party_id
1116: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1117: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)

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

1132: END IF;
1133: IF p_search_ctx_id IS NULL THEN
1134: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX44'||
1135: ' FROM HZ_STAGED_PARTIES stage'||
1136: ' WHERE contains( concat_col, :cont)>0'||
1137: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1138: ' AND ('||p_restrict_sql||')' ||
1139: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1140: IF l_search_merged = 'Y' THEN

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

1144: ELSE
1145: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1146: END IF;
1147: output_long_strings('----------------------------------------------------------');
1148: output_long_strings('Party Contains String = '||p_contains_str);
1149: output_long_strings('Restrict Sql = '||p_restrict_sql);
1150: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1151: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1152: ,p_dup_party_id, p_dup_party_id;

Line 1150: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1146: END IF;
1147: output_long_strings('----------------------------------------------------------');
1148: output_long_strings('Party Contains String = '||p_contains_str);
1149: output_long_strings('Restrict Sql = '||p_restrict_sql);
1150: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1151: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1152: ,p_dup_party_id, p_dup_party_id;
1153: END IF;
1154: END IF;

Line 1181: p_contains_str VARCHAR2,

1177: PROCEDURE open_party_site_cursor(
1178: p_dup_party_id NUMBER,
1179: p_party_id NUMBER,
1180: p_restrict_sql VARCHAR2,
1181: p_contains_str VARCHAR2,
1182: p_search_ctx_id NUMBER,
1183: p_search_merged VARCHAR2,
1184: p_search_rel_sites VARCHAR2,
1185: p_person_api VARCHAR2,

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

1209: IF p_search_rel_sites = 'N' THEN
1210: OPEN x_cursor FOR
1211: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID
1212: FROM HZ_STAGED_PARTY_SITES stage
1213: WHERE contains( concat_col, p_contains_str)>0
1214: AND( (l_search_merged ='Y' )
1215: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1216: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1217: AND stage.party_id = p_party_id;

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

1218: ELSE
1219: OPEN x_cursor FOR
1220: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID
1221: FROM HZ_STAGED_PARTY_SITES stage
1222: WHERE contains( concat_col, p_contains_str)>0
1223: AND( (l_search_merged ='Y' )
1224: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1225: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1226: AND stage.party_id = p_party_id

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

1226: AND stage.party_id = p_party_id
1227: UNION
1228: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID
1229: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1230: WHERE contains( concat_col, p_contains_str)>0
1231: AND( (l_search_merged ='Y' )
1232: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1233: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1234: AND r.object_id = p_party_id

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

1248: END IF;
1249: OPEN x_cursor FOR
1250: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID
1251: FROM HZ_STAGED_PARTY_SITES stage
1252: WHERE contains( concat_col, p_contains_str)>0
1253: AND EXISTS (
1254: SELECT 1 FROM HZ_STAGED_PARTIES p
1255: WHERE p.PARTY_ID = stage.PARTY_ID
1256: AND( (l_search_merged ='Y' )

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

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

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

1278: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1279: UNION
1280: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID
1281: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1282: WHERE contains( concat_col, p_contains_str)>0
1283: AND( (l_search_merged ='Y' )
1284: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1285: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1286: AND d.search_context_id = p_search_ctx_id

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

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

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

1336: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1337: END IF;
1338: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID '||
1339: ' FROM HZ_STAGED_PARTY_SITES stage'||
1340: ' WHERE contains( concat_col, :cont)>0'||
1341: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1342: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1343: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1344: ' AND (ORG_CONTACT_ID IS NULL ' ||

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

1346: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1347: ' UNION ' ||
1348: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID '||
1349: ' FROM HZ_STAGED_PARTY_SITES stage'||
1350: ' WHERE contains( concat_col, :cont)>0'||
1351: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1352: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1353: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1354: ' AND ORG_CONTACT_ID IN ' ||

Line 1360: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1356: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1357: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1358: ' and ('||p_restrict_sql1|| ') )' ||
1359: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1360: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1361: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1362: ELSE
1363: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID '||
1364: ' FROM HZ_STAGED_PARTY_SITES stage'||

Line 1361: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1357: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1358: ' and ('||p_restrict_sql1|| ') )' ||
1359: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1360: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1361: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1362: ELSE
1363: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID '||
1364: ' FROM HZ_STAGED_PARTY_SITES stage'||
1365: ' WHERE contains( concat_col, :cont)>0'||

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

1361: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1362: ELSE
1363: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID '||
1364: ' FROM HZ_STAGED_PARTY_SITES stage'||
1365: ' WHERE contains( concat_col, :cont)>0'||
1366: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1367: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1368: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1369: ' AND EXISTS ('||

Line 1375: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1371: ' WHERE p.party_id = stage.party_id ' ||
1372: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1373: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1374: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1375: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1376: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1377: ,p_dup_party_id, p_dup_party_id;
1378: END IF;
1379: ELSE

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

1378: END IF;
1379: ELSE
1380: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID '||
1381: ' FROM HZ_STAGED_PARTY_SITES stage'||
1382: ' WHERE contains( concat_col, :cont)>0'||
1383: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1384: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1385: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1386: ' AND EXISTS ('||

Line 1392: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1388: ' WHERE p.party_id = stage.party_id ' ||
1389: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1390: ' AND ('||p_restrict_sql||')' ||
1391: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1392: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1393: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1394: ,p_dup_party_id, p_dup_party_id;
1395: END IF;
1396: END IF;

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

1394: ,p_dup_party_id, p_dup_party_id;
1395: END IF;
1396: END IF;
1397: output_long_strings('----------------------------------------------------------');
1398: output_long_strings('Party Site Contains String = '||p_contains_str);
1399: output_long_strings('Restrict Sql = '||p_restrict_sql);
1400: exception
1401: when others then
1402: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1426: p_contains_str VARCHAR2,

1422: PROCEDURE open_contact_cursor(
1423: p_dup_party_id NUMBER,
1424: p_party_id NUMBER,
1425: p_restrict_sql VARCHAR2,
1426: p_contains_str VARCHAR2,
1427: p_search_ctx_id NUMBER,
1428: p_search_merged VARCHAR2,
1429: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1430: l_sqlstr VARCHAR2(4000);

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

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

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

1473: END IF;
1474: OPEN x_cursor FOR
1475: SELECT ORG_CONTACT_ID, PARTY_ID
1476: FROM HZ_STAGED_CONTACTS stage
1477: WHERE contains( concat_col, p_contains_str)>0
1478: AND EXISTS (
1479: SELECT 1 FROM HZ_STAGED_PARTIES p
1480: WHERE p.PARTY_ID = stage.PARTY_ID
1481: AND( (l_search_merged ='Y' )

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

1492: END IF;
1493: OPEN x_cursor FOR
1494: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
1495: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1496: WHERE contains( concat_col, p_contains_str)>0
1497: AND d.search_context_id = p_search_ctx_id
1498: AND d.party_id = stage.party_id
1499: AND( (l_search_merged ='Y' )
1500: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1526: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
1527: END IF;
1528: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
1529: ' FROM HZ_STAGED_CONTACTS stage'||
1530: ' WHERE contains( concat_col, :cont)>0'||
1531: ' AND EXISTS ('||
1532: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1533: ' WHERE p.party_id = stage.party_id ' ||
1534: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1540: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1536: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1537: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1538: ' AND ('||p_restrict_sql||')' ||
1539: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1540: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1541: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1542: ,p_dup_party_id, p_dup_party_id;
1543: END IF;
1544: output_long_strings('----------------------------------------------------------');

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

1541: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1542: ,p_dup_party_id, p_dup_party_id;
1543: END IF;
1544: output_long_strings('----------------------------------------------------------');
1545: output_long_strings('Contacts Contains String = '||p_contains_str);
1546: output_long_strings('Restrict Sql = '||p_restrict_sql);
1547: exception
1548: when others then
1549: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1573: p_contains_str VARCHAR2,

1569: PROCEDURE open_contact_pt_cursor(
1570: p_dup_party_id NUMBER,
1571: p_party_id NUMBER,
1572: p_restrict_sql VARCHAR2,
1573: p_contains_str VARCHAR2,
1574: p_search_ctx_id NUMBER,
1575: p_search_merged VARCHAR2,
1576: p_search_rel_cpts VARCHAR2,
1577: p_person_api VARCHAR2,

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

1599: THEN
1600: OPEN x_cursor FOR
1601: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1602: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1603: WHERE contains( concat_col, p_contains_str)>0
1604: AND d.search_context_id = p_search_ctx_id
1605: AND( (l_search_merged ='Y' )
1606: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1607: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1611: THEN
1612: OPEN x_cursor FOR
1613: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N3) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1614: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1615: WHERE contains( concat_col, p_contains_str)>0
1616: AND d.search_context_id = p_search_ctx_id
1617: AND( (l_search_merged ='Y' )
1618: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1619: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1628: IF p_search_rel_cpts = 'N' THEN
1629: OPEN x_cursor FOR
1630: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1631: FROM HZ_STAGED_CONTACT_POINTS stage
1632: WHERE contains( concat_col, p_contains_str)>0
1633: AND( (l_search_merged ='Y' )
1634: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1635: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1636: AND stage.party_id = p_party_id;

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

1637: ELSE
1638: OPEN x_cursor FOR
1639: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1640: FROM HZ_STAGED_CONTACT_POINTS stage
1641: WHERE contains( concat_col, p_contains_str)>0
1642: AND( (l_search_merged ='Y' )
1643: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1644: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1645: AND stage.party_id = p_party_id

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

1645: AND stage.party_id = p_party_id
1646: UNION
1647: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ stage.CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , TX1, TX6, TX5
1648: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1649: WHERE contains( concat_col, p_contains_str)>0
1650: AND r.object_id = p_party_id
1651: AND( (l_search_merged ='Y' )
1652: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1653: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1667: END IF;
1668: OPEN x_cursor FOR
1669: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1670: FROM HZ_STAGED_CONTACT_POINTS stage
1671: WHERE contains( concat_col, p_contains_str)>0
1672: AND EXISTS (
1673: SELECT 1 FROM HZ_STAGED_PARTIES p
1674: WHERE p.PARTY_ID = stage.PARTY_ID
1675: AND( (l_search_merged ='Y' )

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

1687: IF p_person_api = 'Y' THEN
1688: OPEN x_cursor FOR
1689: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1690: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1691: WHERE contains( concat_col, p_contains_str)>0
1692: AND d.search_context_id = p_search_ctx_id
1693: AND d.party_id = stage.party_id
1694: AND( (l_search_merged ='Y' )
1695: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1697: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1698: UNION
1699: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, r.subject_id, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , TX1, TX6, TX5
1700: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1701: WHERE contains( concat_col, p_contains_str)>0
1702: AND d.search_context_id = p_search_ctx_id
1703: AND d.party_id = r.subject_id
1704: AND r.relationship_id = oc.party_relationship_id
1705: AND oc.org_contact_id = stage.org_contact_id

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

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

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

1755: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1756: END IF;
1757: l_sqlstr := ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
1758: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1759: ' WHERE contains( concat_col, :cont)>0 '||
1760: ' AND (stage.org_contact_id is null '||
1761: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1762: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1763: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

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

1765: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1766: ' UNION ' ||
1767: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
1768: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1769: ' WHERE contains( concat_col, :cont)>0 '||
1770: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1771: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1772: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1773: ' AND (stage.org_contact_id in ' ||

Line 1779: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1775: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1776: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1777: ' and ('||p_restrict_sql1 || ') ) )' ||
1778: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1779: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1780: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1781: ELSE
1782: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
1783: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

Line 1780: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1776: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1777: ' and ('||p_restrict_sql1 || ') ) )' ||
1778: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1779: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1780: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1781: ELSE
1782: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
1783: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1784: ' WHERE contains( concat_col, :cont)>0'||

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

1780: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1781: ELSE
1782: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
1783: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1784: ' WHERE contains( concat_col, :cont)>0'||
1785: ' AND EXISTS ('||
1786: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1787: ' WHERE p.party_id = stage.party_id ' ||
1788: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1794: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1790: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1791: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1792: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1793: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1794: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1795: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1796: ,p_dup_party_id, p_dup_party_id;
1797: END IF;
1798: ELSE

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

1797: END IF;
1798: ELSE
1799: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
1800: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1801: ' WHERE contains( concat_col, :cont)>0'||
1802: ' AND EXISTS ('||
1803: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1804: ' WHERE p.party_id = stage.party_id ' ||
1805: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1811: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1807: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1808: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1809: ' AND ('||p_restrict_sql||')' ||
1810: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1811: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1812: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1813: ,p_dup_party_id, p_dup_party_id;
1814: END IF;
1815: END IF;

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

1814: END IF;
1815: END IF;
1816: END IF;
1817: output_long_strings('----------------------------------------------------------');
1818: output_long_strings('Contact Points Contains String = '||p_contains_str);
1819: output_long_strings('Restrict Sql = '||p_restrict_sql);
1820: exception
1821: when others then
1822: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1939: l_party_contains_str VARCHAR2(32000);

1935: x_num_matches OUT NUMBER
1936: ) IS
1937:
1938: -- Strings to hold the generated Intermedia query strings
1939: l_party_contains_str VARCHAR2(32000);
1940: l_party_site_contains_str VARCHAR2(32000);
1941: l_contact_contains_str VARCHAR2(32000);
1942: l_contact_pt_contains_str VARCHAR2(32000);
1943: l_denorm_str VARCHAR2(32000);

Line 1940: l_party_site_contains_str VARCHAR2(32000);

1936: ) IS
1937:
1938: -- Strings to hold the generated Intermedia query strings
1939: l_party_contains_str VARCHAR2(32000);
1940: l_party_site_contains_str VARCHAR2(32000);
1941: l_contact_contains_str VARCHAR2(32000);
1942: l_contact_pt_contains_str VARCHAR2(32000);
1943: l_denorm_str VARCHAR2(32000);
1944: l_ps_denorm_str VARCHAR2(32000);

Line 1941: l_contact_contains_str VARCHAR2(32000);

1937:
1938: -- Strings to hold the generated Intermedia query strings
1939: l_party_contains_str VARCHAR2(32000);
1940: l_party_site_contains_str VARCHAR2(32000);
1941: l_contact_contains_str VARCHAR2(32000);
1942: l_contact_pt_contains_str VARCHAR2(32000);
1943: l_denorm_str VARCHAR2(32000);
1944: l_ps_denorm_str VARCHAR2(32000);
1945: l_ct_denorm_str VARCHAR2(32000);

Line 1942: l_contact_pt_contains_str VARCHAR2(32000);

1938: -- Strings to hold the generated Intermedia query strings
1939: l_party_contains_str VARCHAR2(32000);
1940: l_party_site_contains_str VARCHAR2(32000);
1941: l_contact_contains_str VARCHAR2(32000);
1942: l_contact_pt_contains_str VARCHAR2(32000);
1943: l_denorm_str VARCHAR2(32000);
1944: l_ps_denorm_str VARCHAR2(32000);
1945: l_ct_denorm_str VARCHAR2(32000);
1946: l_cpt_denorm_str VARCHAR2(32000);

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

2027: call_type(2) := 'OR';
2028: END;
2029:
2030: /** Private procedure to acquire and score at party level ***/
2031: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2032: l_party_id_idx NUMBER:=1;
2033: l_ctx_id NUMBER;
2034: l_precalc_score BOOLEAN := FALSE;
2035: BEGIN

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

2058: l_search_ctx_id,H_PARTY_ID(I));
2059: l_party_id_idx := H_PARTY_ID.COUNT+1;
2060: ELSE
2061: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2062: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2063: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2064: 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);
2065: END IF;
2066: 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 2070: IF (l_party_site_contains_str IS NULL AND

2066: 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
2067: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2068: 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);
2069: END IF;
2070: IF (l_party_site_contains_str IS NULL AND
2071: l_contact_contains_str IS NULL AND
2072: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2073: 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);
2074: RAISE L_RETURN_IMM_EXC;

Line 2071: l_contact_contains_str IS NULL AND

2067: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2068: 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);
2069: END IF;
2070: IF (l_party_site_contains_str IS NULL AND
2071: l_contact_contains_str IS NULL AND
2072: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2073: 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);
2074: RAISE L_RETURN_IMM_EXC;
2075: ELSE

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

2068: 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);
2069: END IF;
2070: IF (l_party_site_contains_str IS NULL AND
2071: l_contact_contains_str IS NULL AND
2072: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2073: 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);
2074: RAISE L_RETURN_IMM_EXC;
2075: ELSE
2076: 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 2076: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);

2072: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2073: 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);
2074: RAISE L_RETURN_IMM_EXC;
2075: ELSE
2076: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2077: END IF;
2078: ELSE
2079: IF p_index>1 THEN
2080: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

2095: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2096: 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);
2097: END IF;
2098: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2099: 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);
2100: END IF;
2101: LOOP
2102: FETCH l_party_cur INTO
2103: l_party_id , l_TX44;

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

2117: l_party_id_idx:= l_party_id_idx+1;
2118: END IF;
2119: IF (l_party_id_idx-1)>l_max_thresh THEN
2120: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2121: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2122: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2123: AND l_contact_pt_contains_str IS NULL THEN
2124: EXIT;
2125: END IF;

Line 2123: AND l_contact_pt_contains_str IS NULL THEN

2119: IF (l_party_id_idx-1)>l_max_thresh THEN
2120: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2121: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2122: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2123: AND l_contact_pt_contains_str IS NULL THEN
2124: EXIT;
2125: END IF;
2126: CLOSE l_party_cur;
2127: IF p_index>1 THEN

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

2148: ROLLBACK to eval_start;
2149: END;
2150:
2151: /** Private procedure to acquire and score at party site level ***/
2152: 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
2153: l_party_id_idx NUMBER:=1;
2154: l_ctx_id NUMBER;
2155: h_ps_id HZ_PARTY_SEARCH.IDList;
2156: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

2160: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2161: 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);
2162: 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);
2163: END IF;
2164: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2165: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2166: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2167: l_party_id := H_SCORES.FIRST;
2168: WHILE l_party_id IS NOT NULL LOOP

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

2161: 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);
2162: 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);
2163: END IF;
2164: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2165: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2166: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2167: l_party_id := H_SCORES.FIRST;
2168: WHILE l_party_id IS NOT NULL LOOP
2169: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;

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

2162: 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);
2163: END IF;
2164: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2165: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2166: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2167: l_party_id := H_SCORES.FIRST;
2168: WHILE l_party_id IS NOT NULL LOOP
2169: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2170: l_party_id:=H_SCORES.NEXT(l_party_id);

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

2192: l_search_ctx_id,H_PARTY_ID(I));
2193: l_party_id_idx := H_PARTY_ID.COUNT+1;
2194: ELSE
2195: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2196: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2197: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2198: 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);
2199: END IF;
2200: IF p_index>1 THEN

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

2214: END IF;
2215: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2216: 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);
2217: END IF;
2218: 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);
2219: LOOP
2220: FETCH l_party_site_cur INTO
2221: l_party_site_id, l_ps_party_id, l_ps_contact_id ;
2222: EXIT WHEN l_party_site_cur%NOTFOUND;

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

2270: END IF;
2271: END;
2272:
2273: /** Private procedure to acquire and score at contact point level ***/
2274: 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
2275: l_party_id_idx NUMBER:=1;
2276: l_ctx_id NUMBER;
2277: h_ct_id HZ_PARTY_SEARCH.IDList;
2278: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

2304: l_search_ctx_id,H_PARTY_ID(I));
2305: l_party_id_idx := H_PARTY_ID.COUNT+1;
2306: ELSE
2307: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2308: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
2309: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2310: 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);
2311: END IF;
2312: IF p_index>1 THEN

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

2326: END IF;
2327: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2328: 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);
2329: END IF;
2330: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2331: LOOP
2332: FETCH l_contact_cur INTO
2333: l_org_contact_id, l_ct_party_id ;
2334: EXIT WHEN l_contact_cur%NOTFOUND;

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

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

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

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

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

2437: END IF;
2438: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2439: 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);
2440: END IF;
2441: 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);
2442: LOOP
2443: FETCH l_contact_pt_cur INTO
2444: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;
2445: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 2541: IF l_party_contains_str IS NOT NULL THEN

2537: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
2538: l_threshold := 15;
2539: other_acq_criteria_exists := TRUE ;
2540: --check if acquisition criteria exists for any other entity
2541: IF l_party_contains_str IS NOT NULL THEN
2542: acq_cnt := 1;
2543: END IF;
2544: IF l_party_site_contains_str IS NOT NULL THEN
2545: acq_cnt := acq_cnt+1;

Line 2544: IF l_party_site_contains_str IS NOT NULL THEN

2540: --check if acquisition criteria exists for any other entity
2541: IF l_party_contains_str IS NOT NULL THEN
2542: acq_cnt := 1;
2543: END IF;
2544: IF l_party_site_contains_str IS NOT NULL THEN
2545: acq_cnt := acq_cnt+1;
2546: END IF;
2547: IF l_contact_contains_str IS NOT NULL THEN
2548: acq_cnt := acq_cnt+1;

Line 2547: IF l_contact_contains_str IS NOT NULL THEN

2543: END IF;
2544: IF l_party_site_contains_str IS NOT NULL THEN
2545: acq_cnt := acq_cnt+1;
2546: END IF;
2547: IF l_contact_contains_str IS NOT NULL THEN
2548: acq_cnt := acq_cnt+1;
2549: END IF;
2550: IF l_contact_pt_contains_str IS NOT NULL THEN
2551: acq_cnt := acq_cnt+1;

Line 2550: IF l_contact_pt_contains_str IS NOT NULL THEN

2546: END IF;
2547: IF l_contact_contains_str IS NOT NULL THEN
2548: acq_cnt := acq_cnt+1;
2549: END IF;
2550: IF l_contact_pt_contains_str IS NOT NULL THEN
2551: acq_cnt := acq_cnt+1;
2552: END IF;
2553: IF acq_cnt>1 THEN
2554: other_acq_criteria_exists := TRUE;

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

2580: FND_MSG_PUB.ADD;
2581: RAISE FND_API.G_EXC_ERROR;
2582: END IF;
2583: END IF;
2584: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2585: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2586: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2587: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2588: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN

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

2581: RAISE FND_API.G_EXC_ERROR;
2582: END IF;
2583: END IF;
2584: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2585: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2586: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2587: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2588: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2589: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

2582: END IF;
2583: END IF;
2584: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2585: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2586: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2587: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2588: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2589: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2590: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

2583: END IF;
2584: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2585: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2586: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2587: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2588: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2589: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2590: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2591: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

2584: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2585: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2586: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2587: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2588: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2589: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2590: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2591: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2592: END IF;

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

2585: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2586: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2587: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2588: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2589: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2590: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2591: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2592: END IF;
2593: END;

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

2586: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2587: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2588: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2589: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2590: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2591: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2592: END IF;
2593: END;
2594: /************ End of find_parties private procedures **********/

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

2587: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2588: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2589: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2590: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2591: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2592: END IF;
2593: END;
2594: /************ End of find_parties private procedures **********/
2595:

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

2670: 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);
2671: END IF;
2672: -- ************************************************************
2673: -- STEP 2. Setup of intermedia query strings for Acquisition query
2674: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2675: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2676: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2677: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2678: 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 2675: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);

2671: END IF;
2672: -- ************************************************************
2673: -- STEP 2. Setup of intermedia query strings for Acquisition query
2674: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2675: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2676: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2677: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2678: 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);
2679: END IF;

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

2672: -- ************************************************************
2673: -- STEP 2. Setup of intermedia query strings for Acquisition query
2674: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2675: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2676: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2677: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2678: 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);
2679: END IF;
2680: l_denorm_max_score:=0;

Line 2708: 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((15/60)*l_entered_max_score));

2704: END IF;
2705: ELSE
2706: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
2707: END IF;
2708: 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((15/60)*l_entered_max_score));
2709: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
2710:
2711: -- Setup Search Context ID
2712: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 2718: IF l_party_contains_str IS NULL THEN

2714:
2715: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2716: 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);
2717: END IF;
2718: IF l_party_contains_str IS NULL THEN
2719: defpt := 1;
2720: END IF;
2721: IF l_party_site_contains_str IS NULL THEN
2722: defps := 1;

Line 2721: IF l_party_site_contains_str IS NULL THEN

2717: END IF;
2718: IF l_party_contains_str IS NULL THEN
2719: defpt := 1;
2720: END IF;
2721: IF l_party_site_contains_str IS NULL THEN
2722: defps := 1;
2723: END IF;
2724: IF l_contact_contains_str IS NULL THEN
2725: defct := 1;

Line 2724: IF l_contact_contains_str IS NULL THEN

2720: END IF;
2721: IF l_party_site_contains_str IS NULL THEN
2722: defps := 1;
2723: END IF;
2724: IF l_contact_contains_str IS NULL THEN
2725: defct := 1;
2726: END IF;
2727: IF l_contact_pt_contains_str IS NULL THEN
2728: defcpt := 1;

Line 2727: IF l_contact_pt_contains_str IS NULL THEN

2723: END IF;
2724: IF l_contact_contains_str IS NULL THEN
2725: defct := 1;
2726: END IF;
2727: IF l_contact_pt_contains_str IS NULL THEN
2728: defcpt := 1;
2729: END IF;
2730:
2731: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

2732: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2733: 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);
2734: END IF;
2735: 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);
2736: 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);
2737: 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);
2738: 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);
2739: 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);
2740: 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 2737: 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);

2733: 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);
2734: END IF;
2735: 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);
2736: 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);
2737: 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);
2738: 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);
2739: 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);
2740: 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);
2741: END IF;

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

2734: END IF;
2735: 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);
2736: 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);
2737: 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);
2738: 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);
2739: 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);
2740: 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);
2741: END IF;
2742: FOR I in 1..5 LOOP

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

2735: 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);
2736: 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);
2737: 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);
2738: 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);
2739: 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);
2740: 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);
2741: END IF;
2742: FOR I in 1..5 LOOP
2743: do_eval(I);

Line 2853: l_party_contains_str VARCHAR2(32000);

2849: x_num_matches OUT NUMBER
2850: ) IS
2851:
2852: -- Strings to hold the generated Intermedia query strings
2853: l_party_contains_str VARCHAR2(32000);
2854: l_party_site_contains_str VARCHAR2(32000);
2855: l_contact_contains_str VARCHAR2(32000);
2856: l_contact_pt_contains_str VARCHAR2(32000);
2857: l_denorm_str VARCHAR2(32000);

Line 2854: l_party_site_contains_str VARCHAR2(32000);

2850: ) IS
2851:
2852: -- Strings to hold the generated Intermedia query strings
2853: l_party_contains_str VARCHAR2(32000);
2854: l_party_site_contains_str VARCHAR2(32000);
2855: l_contact_contains_str VARCHAR2(32000);
2856: l_contact_pt_contains_str VARCHAR2(32000);
2857: l_denorm_str VARCHAR2(32000);
2858: l_ps_denorm_str VARCHAR2(32000);

Line 2855: l_contact_contains_str VARCHAR2(32000);

2851:
2852: -- Strings to hold the generated Intermedia query strings
2853: l_party_contains_str VARCHAR2(32000);
2854: l_party_site_contains_str VARCHAR2(32000);
2855: l_contact_contains_str VARCHAR2(32000);
2856: l_contact_pt_contains_str VARCHAR2(32000);
2857: l_denorm_str VARCHAR2(32000);
2858: l_ps_denorm_str VARCHAR2(32000);
2859: l_ct_denorm_str VARCHAR2(32000);

Line 2856: l_contact_pt_contains_str VARCHAR2(32000);

2852: -- Strings to hold the generated Intermedia query strings
2853: l_party_contains_str VARCHAR2(32000);
2854: l_party_site_contains_str VARCHAR2(32000);
2855: l_contact_contains_str VARCHAR2(32000);
2856: l_contact_pt_contains_str VARCHAR2(32000);
2857: l_denorm_str VARCHAR2(32000);
2858: l_ps_denorm_str VARCHAR2(32000);
2859: l_ct_denorm_str VARCHAR2(32000);
2860: l_cpt_denorm_str VARCHAR2(32000);

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

2971: call_type(2) := 'OR';
2972: END;
2973:
2974: /** Private procedure to acquire and score at party level ***/
2975: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2976: l_party_id_idx NUMBER:=1;
2977: l_ctx_id NUMBER;
2978: BEGIN
2979: SAVEPOINT eval_start;

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

2992: l_search_ctx_id,H_PARTY_ID(I));
2993: l_party_id_idx := H_PARTY_ID.COUNT+1;
2994: ELSE
2995: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2996: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2997: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2998: 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);
2999: END IF;
3000: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN

Line 3004: IF (l_party_site_contains_str IS NULL AND

3000: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3001: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3002: 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);
3003: END IF;
3004: IF (l_party_site_contains_str IS NULL AND
3005: l_contact_contains_str IS NULL AND
3006: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3007: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3008: RAISE L_RETURN_IMM_EXC;

Line 3005: l_contact_contains_str IS NULL AND

3001: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3002: 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);
3003: END IF;
3004: IF (l_party_site_contains_str IS NULL AND
3005: l_contact_contains_str IS NULL AND
3006: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3007: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3008: RAISE L_RETURN_IMM_EXC;
3009: ELSE

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

3002: 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);
3003: END IF;
3004: IF (l_party_site_contains_str IS NULL AND
3005: l_contact_contains_str IS NULL AND
3006: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3007: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3008: RAISE L_RETURN_IMM_EXC;
3009: ELSE
3010: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);

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

3006: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3007: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3008: RAISE L_RETURN_IMM_EXC;
3009: ELSE
3010: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3011: END IF;
3012: ELSE
3013: IF p_index>1 THEN
3014: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3026: l_ctx_id := NULL;
3027: l_party_id_idx := H_PARTY_ID.COUNT+1;
3028: END IF;
3029: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3030: 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);
3031: END IF;
3032: LOOP
3033: FETCH l_party_cur INTO
3034: l_party_id , l_TX44;

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

3048: l_party_id_idx:= l_party_id_idx+1;
3049: END IF;
3050: IF (l_party_id_idx-1)>l_max_thresh THEN
3051: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3052: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3053: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3054: AND l_contact_pt_contains_str IS NULL THEN
3055: H_PARTY_ID.DELETE(l_party_id_idx-1);
3056: H_PARTY_ID_LIST.DELETE(l_index);

Line 3054: AND l_contact_pt_contains_str IS NULL THEN

3050: IF (l_party_id_idx-1)>l_max_thresh THEN
3051: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3052: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3053: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3054: AND l_contact_pt_contains_str IS NULL THEN
3055: H_PARTY_ID.DELETE(l_party_id_idx-1);
3056: H_PARTY_ID_LIST.DELETE(l_index);
3057: H_SCORES.DELETE(l_index);
3058: EXIT;

Line 3083: p_contains_str VARCHAR2,

3079: CLOSE l_party_cur;
3080: ROLLBACK to eval_start;
3081: END;
3082: PROCEDURE open_person_contact_cursor(
3083: p_contains_str VARCHAR2,
3084: p_search_ctx_id NUMBER,
3085: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3086: BEGIN
3087: OPEN x_cursor FOR

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

3086: BEGIN
3087: OPEN x_cursor FOR
3088: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
3089: FROM HZ_STAGED_CONTACTS stage
3090: WHERE contains( concat_col, p_contains_str)>0
3091: AND ORG_CONTACT_ID in (
3092: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3093: org_contact_id
3094: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc

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

3110: end if;
3111: END;
3112:
3113: /** Private procedure to acquire and score at party site level ***/
3114: 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
3115: l_party_id_idx NUMBER:=1;
3116: l_ctx_id NUMBER;
3117: h_ps_id HZ_PARTY_SEARCH.IDList;
3118: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

3138: ELSE
3139: l_party_id_idx := H_PARTY_ID.COUNT+1;
3140: l_ctx_id := NULL;
3141: END IF;
3142: 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);
3143: LOOP
3144: FETCH l_party_site_cur INTO
3145: l_party_site_id, l_ps_party_id, l_ps_contact_id ;
3146: EXIT WHEN l_party_site_cur%NOTFOUND;

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

3209: END IF;
3210: END;
3211:
3212: /** Private procedure to acquire and score at party site level ***/
3213: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3214: l_party_id_idx NUMBER:=1;
3215: l_ctx_id NUMBER;
3216: h_ct_id HZ_PARTY_SEARCH.IDList;
3217: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

3225: FORALL I in 1..H_PARTY_ID.COUNT
3226: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
3227: l_search_ctx_id,H_PARTY_ID(I));
3228: l_party_id_idx := H_PARTY_ID.COUNT+1;
3229: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
3230: LOOP
3231: FETCH l_contact_cur INTO
3232: l_org_contact_id, l_ct_party_id ;
3233: EXIT WHEN l_contact_cur%NOTFOUND;

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

3267: END IF;
3268: END;
3269:
3270: /** Private procedure to acquire and score at contact point level ***/
3271: 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
3272: l_party_id_idx NUMBER:=1;
3273: l_ctx_id NUMBER;
3274: h_cpt_id HZ_PARTY_SEARCH.IDList;
3275: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

3312: END IF;
3313: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3314: 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);
3315: END IF;
3316: 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);
3317: LOOP
3318: FETCH l_contact_pt_cur INTO
3319: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;
3320: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 3424: IF l_party_contains_str IS NOT NULL THEN

3420: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3421: l_threshold := 15;
3422: other_acq_criteria_exists := TRUE ;
3423: --check if acquisition criteria exists for any other entity
3424: IF l_party_contains_str IS NOT NULL THEN
3425: acq_cnt := 1;
3426: END IF;
3427: IF l_party_site_contains_str IS NOT NULL THEN
3428: acq_cnt := acq_cnt+1;

Line 3427: IF l_party_site_contains_str IS NOT NULL THEN

3423: --check if acquisition criteria exists for any other entity
3424: IF l_party_contains_str IS NOT NULL THEN
3425: acq_cnt := 1;
3426: END IF;
3427: IF l_party_site_contains_str IS NOT NULL THEN
3428: acq_cnt := acq_cnt+1;
3429: END IF;
3430: IF l_contact_contains_str IS NOT NULL THEN
3431: acq_cnt := acq_cnt+1;

Line 3430: IF l_contact_contains_str IS NOT NULL THEN

3426: END IF;
3427: IF l_party_site_contains_str IS NOT NULL THEN
3428: acq_cnt := acq_cnt+1;
3429: END IF;
3430: IF l_contact_contains_str IS NOT NULL THEN
3431: acq_cnt := acq_cnt+1;
3432: END IF;
3433: IF l_contact_pt_contains_str IS NOT NULL THEN
3434: acq_cnt := acq_cnt+1;

Line 3433: IF l_contact_pt_contains_str IS NOT NULL THEN

3429: END IF;
3430: IF l_contact_contains_str IS NOT NULL THEN
3431: acq_cnt := acq_cnt+1;
3432: END IF;
3433: IF l_contact_pt_contains_str IS NOT NULL THEN
3434: acq_cnt := acq_cnt+1;
3435: END IF;
3436: IF acq_cnt>1 THEN
3437: other_acq_criteria_exists := TRUE;

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

3463: FND_MSG_PUB.ADD;
3464: RAISE FND_API.G_EXC_ERROR;
3465: END IF;
3466: END IF;
3467: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3468: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3469: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3470: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3471: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

3464: RAISE FND_API.G_EXC_ERROR;
3465: END IF;
3466: END IF;
3467: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3468: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3469: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3470: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3471: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3472: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

3465: END IF;
3466: END IF;
3467: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3468: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3469: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3470: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3471: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3472: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3473: END IF;

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

3466: END IF;
3467: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3468: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3469: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3470: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3471: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3472: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3473: END IF;
3474: END;

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

3467: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3468: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3469: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3470: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3471: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3472: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3473: END IF;
3474: END;
3475: /************ End of find_persons private procedures **********/

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

3468: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3469: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3470: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3471: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3472: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3473: END IF;
3474: END;
3475: /************ End of find_persons private procedures **********/
3476:

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

3503: g_score_until_thresh := false;
3504: END IF;
3505: -- ************************************************************
3506: -- STEP 2. Setup of intermedia query strings for Acquisition query
3507: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3508: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3509: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3510: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3511: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);

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

3504: END IF;
3505: -- ************************************************************
3506: -- STEP 2. Setup of intermedia query strings for Acquisition query
3507: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3508: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3509: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3510: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3511: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3512:

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

3505: -- ************************************************************
3506: -- STEP 2. Setup of intermedia query strings for Acquisition query
3507: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3508: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3509: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3510: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3511: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3512:
3513: -- Setup Search Context ID

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

3506: -- STEP 2. Setup of intermedia query strings for Acquisition query
3507: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3508: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3509: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3510: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3511: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3512:
3513: -- Setup Search Context ID
3514: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 3517: IF l_party_contains_str IS NULL THEN

3513: -- Setup Search Context ID
3514: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3515: x_search_ctx_id := l_search_ctx_id;
3516:
3517: IF l_party_contains_str IS NULL THEN
3518: defpt := 1;
3519: END IF;
3520: IF l_party_site_contains_str IS NULL THEN
3521: defps := 1;

Line 3520: IF l_party_site_contains_str IS NULL THEN

3516:
3517: IF l_party_contains_str IS NULL THEN
3518: defpt := 1;
3519: END IF;
3520: IF l_party_site_contains_str IS NULL THEN
3521: defps := 1;
3522: END IF;
3523: IF l_contact_contains_str IS NULL THEN
3524: defct := 1;

Line 3523: IF l_contact_contains_str IS NULL THEN

3519: END IF;
3520: IF l_party_site_contains_str IS NULL THEN
3521: defps := 1;
3522: END IF;
3523: IF l_contact_contains_str IS NULL THEN
3524: defct := 1;
3525: END IF;
3526: IF l_contact_pt_contains_str IS NULL THEN
3527: defcpt := 1;

Line 3526: IF l_contact_pt_contains_str IS NULL THEN

3522: END IF;
3523: IF l_contact_contains_str IS NULL THEN
3524: defct := 1;
3525: END IF;
3526: IF l_contact_pt_contains_str IS NULL THEN
3527: defcpt := 1;
3528: END IF;
3529:
3530: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3528: END IF;
3529:
3530: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3531: 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);
3532: 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);
3533: 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);
3534: 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);
3535: 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);
3536: 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 3533: 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);

3529:
3530: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3531: 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);
3532: 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);
3533: 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);
3534: 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);
3535: 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);
3536: 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);
3537: END IF;

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

3530: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3531: 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);
3532: 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);
3533: 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);
3534: 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);
3535: 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);
3536: 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);
3537: END IF;
3538: FOR I in 1..5 LOOP

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

3531: 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);
3532: 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);
3533: 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);
3534: 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);
3535: 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);
3536: 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);
3537: END IF;
3538: FOR I in 1..5 LOOP
3539: do_eval(I);

Line 3541: IF l_contact_contains_str IS NOT NULL THEN

3537: END IF;
3538: FOR I in 1..5 LOOP
3539: do_eval(I);
3540: END LOOP;
3541: IF l_contact_contains_str IS NOT NULL THEN
3542: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3543: END IF;
3544: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3545: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 25 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

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

3538: FOR I in 1..5 LOOP
3539: do_eval(I);
3540: END LOOP;
3541: IF l_contact_contains_str IS NOT NULL THEN
3542: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3543: END IF;
3544: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3545: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 25 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3546: END IF;

Line 4356: l_party_contains_str VARCHAR2(32000);

4352: x_num_matches OUT NUMBER
4353: ) IS
4354:
4355: -- Strings to hold the generated Intermedia query strings
4356: l_party_contains_str VARCHAR2(32000);
4357: l_match_idx NUMBER;
4358: l_party_site_contains_str VARCHAR2(32000);
4359: l_contact_contains_str VARCHAR2(32000);
4360: l_contact_pt_contains_str VARCHAR2(32000);

Line 4358: l_party_site_contains_str VARCHAR2(32000);

4354:
4355: -- Strings to hold the generated Intermedia query strings
4356: l_party_contains_str VARCHAR2(32000);
4357: l_match_idx NUMBER;
4358: l_party_site_contains_str VARCHAR2(32000);
4359: l_contact_contains_str VARCHAR2(32000);
4360: l_contact_pt_contains_str VARCHAR2(32000);
4361: l_tmp VARCHAR2(32000);
4362:

Line 4359: l_contact_contains_str VARCHAR2(32000);

4355: -- Strings to hold the generated Intermedia query strings
4356: l_party_contains_str VARCHAR2(32000);
4357: l_match_idx NUMBER;
4358: l_party_site_contains_str VARCHAR2(32000);
4359: l_contact_contains_str VARCHAR2(32000);
4360: l_contact_pt_contains_str VARCHAR2(32000);
4361: l_tmp VARCHAR2(32000);
4362:
4363: -- Other local variables

Line 4360: l_contact_pt_contains_str VARCHAR2(32000);

4356: l_party_contains_str VARCHAR2(32000);
4357: l_match_idx NUMBER;
4358: l_party_site_contains_str VARCHAR2(32000);
4359: l_contact_contains_str VARCHAR2(32000);
4360: l_contact_pt_contains_str VARCHAR2(32000);
4361: l_tmp VARCHAR2(32000);
4362:
4363: -- Other local variables
4364: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4432: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4433:
4434: -- ************************************************************
4435: -- STEP 2. Setup of intermedia query strings for Acquisition query
4436: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4437: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4438: 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);
4439:
4440: -- Setup Search Context ID

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

4433:
4434: -- ************************************************************
4435: -- STEP 2. Setup of intermedia query strings for Acquisition query
4436: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4437: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4438: 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);
4439:
4440: -- Setup Search Context ID
4441: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4442: x_search_ctx_id := l_search_ctx_id;
4443:
4444: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4445: 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);
4446: 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);
4447: 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);
4448: 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);
4449: END IF;
4450: IF l_party_site_contains_str IS NULL THEN

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

4443:
4444: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4445: 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);
4446: 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);
4447: 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);
4448: 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);
4449: END IF;
4450: IF l_party_site_contains_str IS NULL THEN
4451: defps := 1;

Line 4450: IF l_party_site_contains_str IS NULL THEN

4446: 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);
4447: 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);
4448: 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);
4449: END IF;
4450: IF l_party_site_contains_str IS NULL THEN
4451: defps := 1;
4452: END IF;
4453: IF l_contact_pt_contains_str IS NULL THEN
4454: defcpt := 1;

Line 4453: IF l_contact_pt_contains_str IS NULL THEN

4449: END IF;
4450: IF l_party_site_contains_str IS NULL THEN
4451: defps := 1;
4452: END IF;
4453: IF l_contact_pt_contains_str IS NULL THEN
4454: defcpt := 1;
4455: END IF;
4456:
4457: IF l_party_site_contains_str IS NOT NULL THEN

Line 4457: IF l_party_site_contains_str IS NOT NULL THEN

4453: IF l_contact_pt_contains_str IS NULL THEN
4454: defcpt := 1;
4455: END IF;
4456:
4457: IF l_party_site_contains_str IS NOT NULL THEN
4458: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4459: LOOP
4460: FETCH l_party_site_cur INTO
4461: l_party_site_id, l_ps_party_id, l_ps_contact_id ;

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

4454: defcpt := 1;
4455: END IF;
4456:
4457: IF l_party_site_contains_str IS NOT NULL THEN
4458: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4459: LOOP
4460: FETCH l_party_site_cur INTO
4461: l_party_site_id, l_ps_party_id, l_ps_contact_id ;
4462: EXIT WHEN l_party_site_cur%NOTFOUND;

Line 4481: IF l_contact_pt_contains_str IS NOT NULL THEN

4477: END LOOP;
4478: CLOSE l_party_site_cur;
4479: END IF;
4480:
4481: IF l_contact_pt_contains_str IS NOT NULL THEN
4482: SAVEPOINT eval_start;
4483: IF l_match_str = ' AND ' OR (
4484: ((l_cpt_max_score/l_entered_max_score)<(15/60))
4485: ) THEN

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

4485: ) THEN
4486: FORALL I in 1..l_match_ps_list.COUNT
4487: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4488: values (l_search_ctx_id,l_match_ps_list(I));
4489: 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');
4490: ELSE
4491: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4492: END IF;
4493: LOOP

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

4487: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4488: values (l_search_ctx_id,l_match_ps_list(I));
4489: 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');
4490: ELSE
4491: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4492: END IF;
4493: LOOP
4494: FETCH l_contact_pt_cur INTO
4495: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;

Line 4591: l_party_contains_str VARCHAR2(32000);

4587: x_num_matches OUT NUMBER
4588: ) IS
4589:
4590: -- Strings to hold the generated Intermedia query strings
4591: l_party_contains_str VARCHAR2(32000);
4592: l_party_site_contains_str VARCHAR2(32000);
4593: l_contact_contains_str VARCHAR2(32000);
4594: l_contact_pt_contains_str VARCHAR2(32000);
4595: l_tmp VARCHAR2(32000);

Line 4592: l_party_site_contains_str VARCHAR2(32000);

4588: ) IS
4589:
4590: -- Strings to hold the generated Intermedia query strings
4591: l_party_contains_str VARCHAR2(32000);
4592: l_party_site_contains_str VARCHAR2(32000);
4593: l_contact_contains_str VARCHAR2(32000);
4594: l_contact_pt_contains_str VARCHAR2(32000);
4595: l_tmp VARCHAR2(32000);
4596:

Line 4593: l_contact_contains_str VARCHAR2(32000);

4589:
4590: -- Strings to hold the generated Intermedia query strings
4591: l_party_contains_str VARCHAR2(32000);
4592: l_party_site_contains_str VARCHAR2(32000);
4593: l_contact_contains_str VARCHAR2(32000);
4594: l_contact_pt_contains_str VARCHAR2(32000);
4595: l_tmp VARCHAR2(32000);
4596:
4597: -- Other local variables

Line 4594: l_contact_pt_contains_str VARCHAR2(32000);

4590: -- Strings to hold the generated Intermedia query strings
4591: l_party_contains_str VARCHAR2(32000);
4592: l_party_site_contains_str VARCHAR2(32000);
4593: l_contact_contains_str VARCHAR2(32000);
4594: l_contact_pt_contains_str VARCHAR2(32000);
4595: l_tmp VARCHAR2(32000);
4596:
4597: -- Other local variables
4598: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4664: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4665:
4666: -- ************************************************************
4667: -- STEP 2. Setup of intermedia query strings for Acquisition query
4668: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4669: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4670: 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);
4671:
4672: -- Setup Search Context ID

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

4665:
4666: -- ************************************************************
4667: -- STEP 2. Setup of intermedia query strings for Acquisition query
4668: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4669: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4670: 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);
4671:
4672: -- Setup Search Context ID
4673: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4674: x_search_ctx_id := l_search_ctx_id;
4675:
4676: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4677: 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);
4678: 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);
4679: 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);
4680: 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);
4681: END IF;
4682: IF l_contact_contains_str IS NULL THEN

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

4675:
4676: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4677: 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);
4678: 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);
4679: 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);
4680: 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);
4681: END IF;
4682: IF l_contact_contains_str IS NULL THEN
4683: defct := 1;

Line 4682: IF l_contact_contains_str IS NULL THEN

4678: 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);
4679: 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);
4680: 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);
4681: END IF;
4682: IF l_contact_contains_str IS NULL THEN
4683: defct := 1;
4684: END IF;
4685: IF l_contact_pt_contains_str IS NULL THEN
4686: defcpt := 1;

Line 4685: IF l_contact_pt_contains_str IS NULL THEN

4681: END IF;
4682: IF l_contact_contains_str IS NULL THEN
4683: defct := 1;
4684: END IF;
4685: IF l_contact_pt_contains_str IS NULL THEN
4686: defcpt := 1;
4687: END IF;
4688:
4689: IF l_contact_contains_str IS NOT NULL THEN

Line 4689: IF l_contact_contains_str IS NOT NULL THEN

4685: IF l_contact_pt_contains_str IS NULL THEN
4686: defcpt := 1;
4687: END IF;
4688:
4689: IF l_contact_contains_str IS NOT NULL THEN
4690: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
4691: LOOP
4692: FETCH l_contact_cur INTO
4693: l_org_contact_id, l_ct_party_id ;

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

4686: defcpt := 1;
4687: END IF;
4688:
4689: IF l_contact_contains_str IS NOT NULL THEN
4690: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
4691: LOOP
4692: FETCH l_contact_cur INTO
4693: l_org_contact_id, l_ct_party_id ;
4694: EXIT WHEN l_contact_cur%NOTFOUND;

Line 4711: IF l_contact_pt_contains_str IS NOT NULL THEN

4707: END LOOP;
4708: CLOSE l_contact_cur;
4709: END IF;
4710:
4711: IF l_contact_pt_contains_str IS NOT NULL THEN
4712: SAVEPOINT eval_start;
4713: IF l_match_str = ' AND ' OR (
4714: ((l_cpt_max_score/l_entered_max_score)<(15/60))
4715: ) THEN

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

4715: ) THEN
4716: FORALL I in 1..l_match_contact_list.COUNT
4717: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4718: values (l_search_ctx_id,l_match_contact_list(I));
4719: 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');
4720: ELSE
4721: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4722: END IF;
4723: LOOP

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

4717: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4718: values (l_search_ctx_id,l_match_contact_list(I));
4719: 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');
4720: ELSE
4721: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4722: END IF;
4723: LOOP
4724: FETCH l_contact_pt_cur INTO
4725: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;

Line 4819: l_contact_pt_contains_str VARCHAR2(32000);

4815: ) IS
4816:
4817:
4818: -- Strings to hold the generated Intermedia query strings
4819: l_contact_pt_contains_str VARCHAR2(32000);
4820: l_tmp VARCHAR2(32000);
4821:
4822: -- Other local variables
4823: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4876: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4877:
4878: -- ************************************************************
4879: -- STEP 2. Setup of intermedia query strings for Acquisition query
4880: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4881: 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);
4882:
4883: -- Setup Search Context ID
4884: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4884: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4885: x_search_ctx_id := l_search_ctx_id;
4886: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4887: 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);
4888: 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);
4889: 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);
4890: END IF;
4891:
4892: IF l_contact_pt_contains_str IS NOT NULL THEN

Line 4892: IF l_contact_pt_contains_str IS NOT NULL THEN

4888: 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);
4889: 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);
4890: END IF;
4891:
4892: IF l_contact_pt_contains_str IS NOT NULL THEN
4893: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4894: cnt := 1;
4895: LOOP
4896: FETCH l_contact_pt_cur INTO

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

4889: 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);
4890: END IF;
4891:
4892: IF l_contact_pt_contains_str IS NOT NULL THEN
4893: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4894: cnt := 1;
4895: LOOP
4896: FETCH l_contact_pt_cur INTO
4897: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;

Line 4964: l_party_contains_str VARCHAR2(32000);

4960: x_search_ctx_id IN OUT NUMBER
4961: ) IS
4962:
4963: -- Strings to hold the generated Intermedia query strings
4964: l_party_contains_str VARCHAR2(32000);
4965: l_party_site_contains_str VARCHAR2(32000);
4966: l_contact_contains_str VARCHAR2(32000);
4967: l_contact_pt_contains_str VARCHAR2(32000);
4968: l_tmp VARCHAR2(32000);

Line 4965: l_party_site_contains_str VARCHAR2(32000);

4961: ) IS
4962:
4963: -- Strings to hold the generated Intermedia query strings
4964: l_party_contains_str VARCHAR2(32000);
4965: l_party_site_contains_str VARCHAR2(32000);
4966: l_contact_contains_str VARCHAR2(32000);
4967: l_contact_pt_contains_str VARCHAR2(32000);
4968: l_tmp VARCHAR2(32000);
4969:

Line 4966: l_contact_contains_str VARCHAR2(32000);

4962:
4963: -- Strings to hold the generated Intermedia query strings
4964: l_party_contains_str VARCHAR2(32000);
4965: l_party_site_contains_str VARCHAR2(32000);
4966: l_contact_contains_str VARCHAR2(32000);
4967: l_contact_pt_contains_str VARCHAR2(32000);
4968: l_tmp VARCHAR2(32000);
4969:
4970: -- Other local variables

Line 4967: l_contact_pt_contains_str VARCHAR2(32000);

4963: -- Strings to hold the generated Intermedia query strings
4964: l_party_contains_str VARCHAR2(32000);
4965: l_party_site_contains_str VARCHAR2(32000);
4966: l_contact_contains_str VARCHAR2(32000);
4967: l_contact_pt_contains_str VARCHAR2(32000);
4968: l_tmp VARCHAR2(32000);
4969:
4970: -- Other local variables
4971: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5028: g_score_until_thresh := false;
5029: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5030: -- ************************************************************
5031: -- STEP 2. Setup of intermedia query strings for Acquisition query
5032: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5033: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5034: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5035: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5036:

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

5029: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5030: -- ************************************************************
5031: -- STEP 2. Setup of intermedia query strings for Acquisition query
5032: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5033: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5034: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5035: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5036:
5037: -- Setup Search Context ID

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

5030: -- ************************************************************
5031: -- STEP 2. Setup of intermedia query strings for Acquisition query
5032: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5033: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5034: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5035: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5036:
5037: -- Setup Search Context ID
5038: IF x_search_ctx_id IS NULL THEN

Line 5054: IF l_party_site_contains_str IS NOT NULL THEN

5050: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX44,1);
5051: END LOOP;
5052: CLOSE l_party_cur;
5053:
5054: IF l_party_site_contains_str IS NOT NULL THEN
5055: l_max_score := 0;
5056: l_max_id := 0;
5057: l_max_idx := 0;
5058: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN

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

5055: l_max_score := 0;
5056: l_max_id := 0;
5057: l_max_idx := 0;
5058: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5059: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5060: ELSE
5061: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5062: END IF;
5063: LOOP

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

5057: l_max_idx := 0;
5058: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5059: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5060: ELSE
5061: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5062: END IF;
5063: LOOP
5064: FETCH l_party_site_cur INTO
5065: l_party_site_id, l_ps_party_id,l_ps_contact_id ;

Line 5080: IF l_contact_contains_str IS NOT NULL THEN

5076: INSERT_PARTY_SITES_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_party_site_list(l_max_idx), g_party_site_stage_list(l_max_idx) ,l_max_idx);
5077: END IF;
5078: END IF;
5079:
5080: IF l_contact_contains_str IS NOT NULL THEN
5081: l_max_score := 0;
5082: l_max_id := 0;
5083: l_max_idx := 0;
5084: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

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

5080: IF l_contact_contains_str IS NOT NULL THEN
5081: l_max_score := 0;
5082: l_max_id := 0;
5083: l_max_idx := 0;
5084: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5085: LOOP
5086: FETCH l_contact_cur INTO
5087: l_org_contact_id, l_ct_party_id ;
5088: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5102: IF l_contact_pt_contains_str IS NOT NULL THEN

5098: 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);
5099: END IF;
5100: END IF;
5101:
5102: IF l_contact_pt_contains_str IS NOT NULL THEN
5103: l_max_score := 0;
5104: l_max_id := 0;
5105: l_max_idx := 0;
5106: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN

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

5103: l_max_score := 0;
5104: l_max_id := 0;
5105: l_max_idx := 0;
5106: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5107: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5108: ELSE
5109: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5110: END IF;
5111: LOOP

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

5105: l_max_idx := 0;
5106: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5107: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5108: ELSE
5109: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5110: END IF;
5111: LOOP
5112: FETCH l_contact_pt_cur INTO
5113: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;