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_2 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 822: l_party_contains_str VARCHAR2(32000);

818: p_party_max_score NUMBER,
819: p_denorm_max_score NUMBER,
820: p_non_denorm_max_score NUMBER,
821: p_threshold NUMBER) RETURN VARCHAR2 IS
822: l_party_contains_str VARCHAR2(32000);
823: l_party_filter VARCHAR2(1) := null;
824: l_prim_temp VARCHAR2(4000);
825: l_denorm_str VARCHAR2(4000);
826: BEGIN

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

862:
863: IF l_denorm_str IS NOT NULL THEN
864: IF (p_non_denorm_max_score+19)>=p_threshold THEN
865: l_denorm_str := NULL;
866: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
867: ELSIF (p_non_denorm_max_score+p_denorm_max_score+19)>=p_threshold THEN
868: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
869: l_denorm_str := NULL;
870: END IF;

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

864: IF (p_non_denorm_max_score+19)>=p_threshold THEN
865: l_denorm_str := NULL;
866: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
867: ELSIF (p_non_denorm_max_score+p_denorm_max_score+19)>=p_threshold THEN
868: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
869: l_denorm_str := NULL;
870: END IF;
871: ELSE
872: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

868: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
869: l_denorm_str := NULL;
870: END IF;
871: ELSE
872: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
873: END IF;
874:
875: -- Setup query string for DUNS_NUMBER_C
876: l_prim_temp := null;

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

885:
886: IF l_denorm_str IS NOT NULL THEN
887: IF (p_non_denorm_max_score+20)>=p_threshold THEN
888: l_denorm_str := NULL;
889: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
890: ELSIF (p_non_denorm_max_score+p_denorm_max_score+20)>=p_threshold THEN
891: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
892: l_denorm_str := NULL;
893: END IF;

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

887: IF (p_non_denorm_max_score+20)>=p_threshold THEN
888: l_denorm_str := NULL;
889: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
890: ELSIF (p_non_denorm_max_score+p_denorm_max_score+20)>=p_threshold THEN
891: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
892: l_denorm_str := NULL;
893: END IF;
894: ELSE
895: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

891: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
892: l_denorm_str := NULL;
893: END IF;
894: ELSE
895: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
896: END IF;
897:
898: -- Setup query string for TAX_REFERENCE
899: l_prim_temp := null;

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

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

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

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

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

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

Line 914: IF lengthb(l_party_contains_str) > 4000 THEN

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

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

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

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

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

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

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

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

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

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

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

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

Line 936: RETURN l_party_contains_str;

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

Line 940: l_contains_str VARCHAR2(32000);

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

Line 941: l_contains_str_temp VARCHAR2(32000);

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

Line 942: l_den_contains_str VARCHAR2(32000);

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

Line 943: l_den_contains_str_temp VARCHAR2(32000);

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

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

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

Line 958: l_contains_str_temp := null;

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

Line 959: l_den_contains_str_temp := null;

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

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

963: l_prim_temp := null;
964: l_prim_temp_den := null;
965: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL + WRADDRESS
966: g_party_site_stage_list(I).TX3,'A27',l_prim_temp);
967: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
968:
969: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
970: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
971: END LOOP;

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

965: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL + WRADDRESS
966: g_party_site_stage_list(I).TX3,'A27',l_prim_temp);
967: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
968:
969: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
970: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
971: END LOOP;
972: -- Add the search criteria to query string
973: IF lengthb(l_contains_str) > 4000 THEN

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

966: g_party_site_stage_list(I).TX3,'A27',l_prim_temp);
967: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
968:
969: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
970: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
971: END LOOP;
972: -- Add the search criteria to query string
973: IF lengthb(l_contains_str) > 4000 THEN
974: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');

Line 973: IF lengthb(l_contains_str) > 4000 THEN

969: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
970: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
971: END LOOP;
972: -- Add the search criteria to query string
973: IF lengthb(l_contains_str) > 4000 THEN
974: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
975: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
976: FND_MSG_PUB.ADD;
977: RAISE FND_API.G_EXC_ERROR;

Line 979: x_denorm_str := l_den_contains_str;

975: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
976: FND_MSG_PUB.ADD;
977: RAISE FND_API.G_EXC_ERROR;
978: END IF;
979: x_denorm_str := l_den_contains_str;
980: RETURN l_contains_str;
981: END;
982:
983: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS

Line 980: RETURN l_contains_str;

976: FND_MSG_PUB.ADD;
977: RAISE FND_API.G_EXC_ERROR;
978: END IF;
979: x_denorm_str := l_den_contains_str;
980: RETURN l_contains_str;
981: END;
982:
983: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
984: l_contains_str VARCHAR2(32000);

Line 984: l_contains_str VARCHAR2(32000);

980: RETURN l_contains_str;
981: END;
982:
983: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
984: l_contains_str VARCHAR2(32000);
985: l_contains_str_temp VARCHAR2(32000);
986: l_den_contains_str VARCHAR2(32000);
987: l_den_contains_str_temp VARCHAR2(32000);
988: l_filter_str VARCHAR2(4000) := null;

Line 985: l_contains_str_temp VARCHAR2(32000);

981: END;
982:
983: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
984: l_contains_str VARCHAR2(32000);
985: l_contains_str_temp VARCHAR2(32000);
986: l_den_contains_str VARCHAR2(32000);
987: l_den_contains_str_temp VARCHAR2(32000);
988: l_filter_str VARCHAR2(4000) := null;
989: l_prim_temp VARCHAR2(4000) := null;

Line 986: l_den_contains_str VARCHAR2(32000);

982:
983: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
984: l_contains_str VARCHAR2(32000);
985: l_contains_str_temp VARCHAR2(32000);
986: l_den_contains_str VARCHAR2(32000);
987: l_den_contains_str_temp VARCHAR2(32000);
988: l_filter_str VARCHAR2(4000) := null;
989: l_prim_temp VARCHAR2(4000) := null;
990: l_prim_temp_den VARCHAR2(4000) := null;

Line 987: l_den_contains_str_temp VARCHAR2(32000);

983: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
984: l_contains_str VARCHAR2(32000);
985: l_contains_str_temp VARCHAR2(32000);
986: l_den_contains_str VARCHAR2(32000);
987: l_den_contains_str_temp VARCHAR2(32000);
988: l_filter_str VARCHAR2(4000) := null;
989: l_prim_temp VARCHAR2(4000) := null;
990: l_prim_temp_den VARCHAR2(4000) := null;
991: BEGIN

Line 1000: l_contains_str VARCHAR2(32000);

996: RETURN NULL;
997: END;
998:
999: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1000: l_contains_str VARCHAR2(32000);
1001: l_contains_str_temp VARCHAR2(32000);
1002: l_den_contains_str VARCHAR2(32000);
1003: l_den_contains_str_temp VARCHAR2(32000);
1004: l_filter_str VARCHAR2(4000) := null;

Line 1001: l_contains_str_temp VARCHAR2(32000);

997: END;
998:
999: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1000: l_contains_str VARCHAR2(32000);
1001: l_contains_str_temp VARCHAR2(32000);
1002: l_den_contains_str VARCHAR2(32000);
1003: l_den_contains_str_temp VARCHAR2(32000);
1004: l_filter_str VARCHAR2(4000) := null;
1005: l_prim_temp VARCHAR2(4000) := null;

Line 1002: l_den_contains_str VARCHAR2(32000);

998:
999: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1000: l_contains_str VARCHAR2(32000);
1001: l_contains_str_temp VARCHAR2(32000);
1002: l_den_contains_str VARCHAR2(32000);
1003: l_den_contains_str_temp VARCHAR2(32000);
1004: l_filter_str VARCHAR2(4000) := null;
1005: l_prim_temp VARCHAR2(4000) := null;
1006: l_prim_temp_den VARCHAR2(4000) := null;

Line 1003: l_den_contains_str_temp VARCHAR2(32000);

999: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1000: l_contains_str VARCHAR2(32000);
1001: l_contains_str_temp VARCHAR2(32000);
1002: l_den_contains_str VARCHAR2(32000);
1003: l_den_contains_str_temp VARCHAR2(32000);
1004: l_filter_str VARCHAR2(4000) := null;
1005: l_prim_temp VARCHAR2(4000) := null;
1006: l_prim_temp_den VARCHAR2(4000) := null;
1007: BEGIN

Line 1020: p_party_contains_str VARCHAR2,

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

Line 1037: IF p_restrict_sql IS NULL AND NOT g_other_party_level_attribs AND NOT (p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1) THEN

1033: END IF;
1034: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1035: hz_utility_v2pub.debug(p_message=>'Search Merged Flag - '||l_search_merged,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1036: END IF;
1037: IF p_restrict_sql IS NULL AND NOT g_other_party_level_attribs AND NOT (p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1) THEN
1038: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1039: hz_utility_v2pub.debug(p_message=>'Restrict SQL is NULL and other conditions met to OPEN x_cursor',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1040: END IF;
1041: OPEN x_cursor FOR

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

1074: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1075: l_sqlstr := l_sqlstr || ' (TX36 like :TX36||'' %'' ';
1076: l_sqlstr := l_sqlstr || ' )) ';
1077: END IF;
1078: 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
1079: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1080: 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);
1081: END IF;
1082: null;

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

1076: l_sqlstr := l_sqlstr || ' )) ';
1077: END IF;
1078: 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
1079: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1080: 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);
1081: END IF;
1082: null;
1083: END IF;
1084: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

1095: END IF;
1096: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1097: 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);
1098: END IF;
1099: 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
1100: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1101: 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);
1102: END IF;
1103: 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 1101: 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);

1097: 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);
1098: END IF;
1099: 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
1100: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1101: 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);
1102: END IF;
1103: 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
1104: ,g_party_stage_rec.TX39
1105: ,g_party_stage_rec.TX39

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

1123: ,g_party_stage_rec.TX44
1124: ,g_party_stage_rec.TX36
1125: ,g_party_stage_rec.TX36
1126: ;
1127: 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
1128: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1129: 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);
1130: END IF;
1131: 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 1129: 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);

1125: ,g_party_stage_rec.TX36
1126: ;
1127: 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
1128: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1129: 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);
1130: END IF;
1131: 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
1132: ;
1133: ELSE

Line 1144: p_contains_str VARCHAR2,

1140: END;
1141: PROCEDURE open_party_cursor(
1142: p_dup_party_id NUMBER,
1143: p_restrict_sql VARCHAR2,
1144: p_contains_str VARCHAR2,
1145: p_search_ctx_id NUMBER,
1146: p_match_str VARCHAR2,
1147: p_search_merged VARCHAR2,
1148: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS

Line 1163: IF p_contains_str IS NULL THEN

1159: l_search_merged := 'N';
1160: ELSE
1161: l_search_merged := p_search_merged;
1162: END IF;
1163: IF p_contains_str IS NULL THEN
1164: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1165: 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);
1166: END IF;
1167: OPEN x_cursor FOR

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

1161: l_search_merged := p_search_merged;
1162: END IF;
1163: IF p_contains_str IS NULL THEN
1164: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1165: 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);
1166: END IF;
1167: OPEN x_cursor FOR
1168: SELECT PARTY_ID , TX39, lpad(rtrim(TX41),9,chr(48)), TX44
1169: FROM HZ_STAGED_PARTIES stage

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

1178: END IF;
1179: OPEN x_cursor FOR
1180: SELECT /*+ INDEX(stage HZ_STAGE_PARTIES_T1) */ PARTY_ID , TX39, lpad(rtrim(TX41),9,chr(48)), TX44
1181: FROM HZ_STAGED_PARTIES stage
1182: WHERE contains( concat_col, p_contains_str)>0
1183: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1184: AND( (l_search_merged ='Y' )
1185: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1186: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )

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

1191: END IF;
1192: OPEN x_cursor FOR
1193: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX39, lpad(rtrim(TX41),9,chr(48)), TX44
1194: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1195: WHERE contains( concat_col, p_contains_str)>0
1196: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1197: AND d.party_id = stage.party_id
1198: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1199: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)

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

1216: END IF;
1217: IF p_search_ctx_id IS NULL THEN
1218: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX39, lpad(rtrim(TX41),9,chr(48)), TX44'||
1219: ' FROM HZ_STAGED_PARTIES stage'||
1220: ' WHERE contains( concat_col, :cont)>0'||
1221: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1222: ' AND ('||p_restrict_sql||')' ||
1223: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1224: IF l_search_merged = 'Y' THEN

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

1228: ELSE
1229: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1230: END IF;
1231: output_long_strings('----------------------------------------------------------');
1232: output_long_strings('Party Contains String = '||p_contains_str);
1233: output_long_strings('Restrict Sql = '||p_restrict_sql);
1234: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1235: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1236: ,p_dup_party_id, p_dup_party_id;

Line 1234: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1230: END IF;
1231: output_long_strings('----------------------------------------------------------');
1232: output_long_strings('Party Contains String = '||p_contains_str);
1233: output_long_strings('Restrict Sql = '||p_restrict_sql);
1234: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1235: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1236: ,p_dup_party_id, p_dup_party_id;
1237: END IF;
1238: END IF;

Line 1265: p_contains_str VARCHAR2,

1261: PROCEDURE open_party_site_cursor(
1262: p_dup_party_id NUMBER,
1263: p_party_id NUMBER,
1264: p_restrict_sql VARCHAR2,
1265: p_contains_str VARCHAR2,
1266: p_search_ctx_id NUMBER,
1267: p_search_merged VARCHAR2,
1268: p_search_rel_sites VARCHAR2,
1269: p_person_api VARCHAR2,

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

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

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

1302: ELSE
1303: OPEN x_cursor FOR
1304: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3
1305: FROM HZ_STAGED_PARTY_SITES stage
1306: WHERE contains( concat_col, p_contains_str)>0
1307: AND( (l_search_merged ='Y' )
1308: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1309: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1310: AND stage.party_id = p_party_id

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

1310: AND stage.party_id = p_party_id
1311: UNION
1312: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX3
1313: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1314: WHERE contains( concat_col, p_contains_str)>0
1315: AND( (l_search_merged ='Y' )
1316: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1317: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1318: AND r.object_id = p_party_id

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

1332: END IF;
1333: OPEN x_cursor FOR
1334: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3
1335: FROM HZ_STAGED_PARTY_SITES stage
1336: WHERE contains( concat_col, p_contains_str)>0
1337: AND EXISTS (
1338: SELECT 1 FROM HZ_STAGED_PARTIES p
1339: WHERE p.PARTY_ID = stage.PARTY_ID
1340: AND( (l_search_merged ='Y' )

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

1352: IF p_person_api = 'Y' THEN
1353: OPEN x_cursor FOR
1354: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3
1355: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1356: WHERE contains( concat_col, p_contains_str)>0
1357: AND( (l_search_merged ='Y' )
1358: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1359: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1360: AND d.search_context_id = p_search_ctx_id

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

1362: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1363: UNION
1364: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX3
1365: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1366: WHERE contains( concat_col, p_contains_str)>0
1367: AND( (l_search_merged ='Y' )
1368: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1369: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1370: AND d.search_context_id = p_search_ctx_id

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

1375: ELSE
1376: OPEN x_cursor FOR
1377: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3
1378: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1379: WHERE contains( concat_col, p_contains_str)>0
1380: AND( (l_search_merged ='Y' )
1381: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1382: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1383: AND d.search_context_id = p_search_ctx_id

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

1420: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1421: END IF;
1422: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3'||
1423: ' FROM HZ_STAGED_PARTY_SITES stage'||
1424: ' WHERE contains( concat_col, :cont)>0'||
1425: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1426: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1427: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1428: ' AND (ORG_CONTACT_ID IS NULL ' ||

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

1430: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1431: ' UNION ' ||
1432: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3'||
1433: ' FROM HZ_STAGED_PARTY_SITES stage'||
1434: ' WHERE contains( concat_col, :cont)>0'||
1435: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1436: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1437: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1438: ' AND ORG_CONTACT_ID IN ' ||

Line 1444: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1440: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1441: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1442: ' and ('||p_restrict_sql1|| ') )' ||
1443: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1444: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1445: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1446: ELSE
1447: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3'||
1448: ' FROM HZ_STAGED_PARTY_SITES stage'||

Line 1445: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1441: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1442: ' and ('||p_restrict_sql1|| ') )' ||
1443: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1444: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1445: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1446: ELSE
1447: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3'||
1448: ' FROM HZ_STAGED_PARTY_SITES stage'||
1449: ' WHERE contains( concat_col, :cont)>0'||

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

1445: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1446: ELSE
1447: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3'||
1448: ' FROM HZ_STAGED_PARTY_SITES stage'||
1449: ' WHERE contains( concat_col, :cont)>0'||
1450: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1451: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1452: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1453: ' AND EXISTS ('||

Line 1459: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1455: ' WHERE p.party_id = stage.party_id ' ||
1456: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1457: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1458: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1459: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1460: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1461: ,p_dup_party_id, p_dup_party_id;
1462: END IF;
1463: ELSE

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

1462: END IF;
1463: ELSE
1464: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3'||
1465: ' FROM HZ_STAGED_PARTY_SITES stage'||
1466: ' WHERE contains( concat_col, :cont)>0'||
1467: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1468: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1469: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1470: ' AND EXISTS ('||

Line 1476: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1472: ' WHERE p.party_id = stage.party_id ' ||
1473: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1474: ' AND ('||p_restrict_sql||')' ||
1475: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1476: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1477: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1478: ,p_dup_party_id, p_dup_party_id;
1479: END IF;
1480: END IF;

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

1478: ,p_dup_party_id, p_dup_party_id;
1479: END IF;
1480: END IF;
1481: output_long_strings('----------------------------------------------------------');
1482: output_long_strings('Party Site Contains String = '||p_contains_str);
1483: output_long_strings('Restrict Sql = '||p_restrict_sql);
1484: exception
1485: when others then
1486: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1510: p_contains_str VARCHAR2,

1506: PROCEDURE open_contact_cursor(
1507: p_dup_party_id NUMBER,
1508: p_party_id NUMBER,
1509: p_restrict_sql VARCHAR2,
1510: p_contains_str VARCHAR2,
1511: p_search_ctx_id NUMBER,
1512: p_search_merged VARCHAR2,
1513: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1514: l_sqlstr VARCHAR2(4000);

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

1534: END IF;
1535: OPEN x_cursor FOR
1536: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
1537: FROM HZ_STAGED_CONTACTS stage
1538: WHERE contains( concat_col, p_contains_str)>0
1539: AND EXISTS (
1540: SELECT 1 FROM HZ_STAGED_PARTIES p
1541: WHERE p.PARTY_ID = stage.PARTY_ID
1542: AND( (l_search_merged ='Y' )

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

1557: END IF;
1558: OPEN x_cursor FOR
1559: SELECT ORG_CONTACT_ID, PARTY_ID
1560: FROM HZ_STAGED_CONTACTS stage
1561: WHERE contains( concat_col, p_contains_str)>0
1562: AND EXISTS (
1563: SELECT 1 FROM HZ_STAGED_PARTIES p
1564: WHERE p.PARTY_ID = stage.PARTY_ID
1565: AND( (l_search_merged ='Y' )

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

1576: END IF;
1577: OPEN x_cursor FOR
1578: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
1579: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1580: WHERE contains( concat_col, p_contains_str)>0
1581: AND d.search_context_id = p_search_ctx_id
1582: AND d.party_id = stage.party_id
1583: AND( (l_search_merged ='Y' )
1584: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1610: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
1611: END IF;
1612: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
1613: ' FROM HZ_STAGED_CONTACTS stage'||
1614: ' WHERE contains( concat_col, :cont)>0'||
1615: ' AND EXISTS ('||
1616: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1617: ' WHERE p.party_id = stage.party_id ' ||
1618: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1624: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1620: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1621: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1622: ' AND ('||p_restrict_sql||')' ||
1623: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1624: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1625: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1626: ,p_dup_party_id, p_dup_party_id;
1627: END IF;
1628: output_long_strings('----------------------------------------------------------');

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

1625: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1626: ,p_dup_party_id, p_dup_party_id;
1627: END IF;
1628: output_long_strings('----------------------------------------------------------');
1629: output_long_strings('Contacts Contains String = '||p_contains_str);
1630: output_long_strings('Restrict Sql = '||p_restrict_sql);
1631: exception
1632: when others then
1633: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1657: p_contains_str VARCHAR2,

1653: PROCEDURE open_contact_pt_cursor(
1654: p_dup_party_id NUMBER,
1655: p_party_id NUMBER,
1656: p_restrict_sql VARCHAR2,
1657: p_contains_str VARCHAR2,
1658: p_search_ctx_id NUMBER,
1659: p_search_merged VARCHAR2,
1660: p_search_rel_cpts VARCHAR2,
1661: p_person_api VARCHAR2,

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

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

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

1695: THEN
1696: OPEN x_cursor FOR
1697: 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
1698: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1699: WHERE contains( concat_col, p_contains_str)>0
1700: AND d.search_context_id = p_search_ctx_id
1701: AND( (l_search_merged ='Y' )
1702: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1703: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

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

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

1721: ELSE
1722: OPEN x_cursor FOR
1723: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1724: FROM HZ_STAGED_CONTACT_POINTS stage
1725: WHERE contains( concat_col, p_contains_str)>0
1726: AND( (l_search_merged ='Y' )
1727: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1728: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1729: AND stage.party_id = p_party_id

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

1729: AND stage.party_id = p_party_id
1730: UNION
1731: 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
1732: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1733: WHERE contains( concat_col, p_contains_str)>0
1734: AND r.object_id = p_party_id
1735: AND( (l_search_merged ='Y' )
1736: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1737: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1751: END IF;
1752: OPEN x_cursor FOR
1753: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1754: FROM HZ_STAGED_CONTACT_POINTS stage
1755: WHERE contains( concat_col, p_contains_str)>0
1756: AND EXISTS (
1757: SELECT 1 FROM HZ_STAGED_PARTIES p
1758: WHERE p.PARTY_ID = stage.PARTY_ID
1759: AND( (l_search_merged ='Y' )

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

1771: IF p_person_api = 'Y' THEN
1772: OPEN x_cursor FOR
1773: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1774: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1775: WHERE contains( concat_col, p_contains_str)>0
1776: AND d.search_context_id = p_search_ctx_id
1777: AND d.party_id = stage.party_id
1778: AND( (l_search_merged ='Y' )
1779: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1781: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1782: UNION
1783: 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
1784: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1785: WHERE contains( concat_col, p_contains_str)>0
1786: AND d.search_context_id = p_search_ctx_id
1787: AND d.party_id = r.subject_id
1788: AND r.relationship_id = oc.party_relationship_id
1789: AND oc.org_contact_id = stage.org_contact_id

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

1794: ELSE
1795: OPEN x_cursor FOR
1796: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1797: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1798: WHERE contains( concat_col, p_contains_str)>0
1799: AND d.search_context_id = p_search_ctx_id
1800: AND d.party_id = stage.party_id
1801: AND( (l_search_merged ='Y' )
1802: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1839: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1840: END IF;
1841: 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 '||
1842: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1843: ' WHERE contains( concat_col, :cont)>0 '||
1844: ' AND (stage.org_contact_id is null '||
1845: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1846: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1847: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

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

1849: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1850: ' UNION ' ||
1851: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1852: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1853: ' WHERE contains( concat_col, :cont)>0 '||
1854: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1855: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1856: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1857: ' AND (stage.org_contact_id in ' ||

Line 1863: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1859: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1860: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1861: ' and ('||p_restrict_sql1 || ') ) )' ||
1862: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1863: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1864: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1865: ELSE
1866: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1867: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

Line 1864: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1860: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1861: ' and ('||p_restrict_sql1 || ') ) )' ||
1862: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1863: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1864: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1865: ELSE
1866: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1867: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1868: ' WHERE contains( concat_col, :cont)>0'||

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

1864: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1865: ELSE
1866: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1867: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1868: ' WHERE contains( concat_col, :cont)>0'||
1869: ' AND EXISTS ('||
1870: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1871: ' WHERE p.party_id = stage.party_id ' ||
1872: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1878: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1874: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1875: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1876: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1877: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1878: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1879: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1880: ,p_dup_party_id, p_dup_party_id;
1881: END IF;
1882: ELSE

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

1881: END IF;
1882: ELSE
1883: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1884: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1885: ' WHERE contains( concat_col, :cont)>0'||
1886: ' AND EXISTS ('||
1887: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1888: ' WHERE p.party_id = stage.party_id ' ||
1889: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1895: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1891: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1892: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1893: ' AND ('||p_restrict_sql||')' ||
1894: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1895: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1896: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1897: ,p_dup_party_id, p_dup_party_id;
1898: END IF;
1899: END IF;

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

1898: END IF;
1899: END IF;
1900: END IF;
1901: output_long_strings('----------------------------------------------------------');
1902: output_long_strings('Contact Points Contains String = '||p_contains_str);
1903: output_long_strings('Restrict Sql = '||p_restrict_sql);
1904: exception
1905: when others then
1906: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 2023: l_party_contains_str VARCHAR2(32000);

2019: x_num_matches OUT NUMBER
2020: ) IS
2021:
2022: -- Strings to hold the generated Intermedia query strings
2023: l_party_contains_str VARCHAR2(32000);
2024: l_party_site_contains_str VARCHAR2(32000);
2025: l_contact_contains_str VARCHAR2(32000);
2026: l_contact_pt_contains_str VARCHAR2(32000);
2027: l_denorm_str VARCHAR2(32000);

Line 2024: l_party_site_contains_str VARCHAR2(32000);

2020: ) IS
2021:
2022: -- Strings to hold the generated Intermedia query strings
2023: l_party_contains_str VARCHAR2(32000);
2024: l_party_site_contains_str VARCHAR2(32000);
2025: l_contact_contains_str VARCHAR2(32000);
2026: l_contact_pt_contains_str VARCHAR2(32000);
2027: l_denorm_str VARCHAR2(32000);
2028: l_ps_denorm_str VARCHAR2(32000);

Line 2025: l_contact_contains_str VARCHAR2(32000);

2021:
2022: -- Strings to hold the generated Intermedia query strings
2023: l_party_contains_str VARCHAR2(32000);
2024: l_party_site_contains_str VARCHAR2(32000);
2025: l_contact_contains_str VARCHAR2(32000);
2026: l_contact_pt_contains_str VARCHAR2(32000);
2027: l_denorm_str VARCHAR2(32000);
2028: l_ps_denorm_str VARCHAR2(32000);
2029: l_ct_denorm_str VARCHAR2(32000);

Line 2026: l_contact_pt_contains_str VARCHAR2(32000);

2022: -- Strings to hold the generated Intermedia query strings
2023: l_party_contains_str VARCHAR2(32000);
2024: l_party_site_contains_str VARCHAR2(32000);
2025: l_contact_contains_str VARCHAR2(32000);
2026: l_contact_pt_contains_str VARCHAR2(32000);
2027: l_denorm_str VARCHAR2(32000);
2028: l_ps_denorm_str VARCHAR2(32000);
2029: l_ct_denorm_str VARCHAR2(32000);
2030: l_cpt_denorm_str VARCHAR2(32000);

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

2111: call_type(2) := 'OR';
2112: END;
2113:
2114: /** Private procedure to acquire and score at party level ***/
2115: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2116: l_party_id_idx NUMBER:=1;
2117: l_ctx_id NUMBER;
2118: l_precalc_score BOOLEAN := FALSE;
2119: l_TX35_new varchar2(4000);

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

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

2151: 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
2152: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2153: 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);
2154: END IF;
2155: IF (l_party_site_contains_str IS NULL AND
2156: l_contact_contains_str IS NULL AND
2157: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2158: 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);
2159: RAISE L_RETURN_IMM_EXC;

Line 2156: l_contact_contains_str IS NULL AND

2152: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2153: 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);
2154: END IF;
2155: IF (l_party_site_contains_str IS NULL AND
2156: l_contact_contains_str IS NULL AND
2157: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2158: 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);
2159: RAISE L_RETURN_IMM_EXC;
2160: ELSE

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

2153: 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);
2154: END IF;
2155: IF (l_party_site_contains_str IS NULL AND
2156: l_contact_contains_str IS NULL AND
2157: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2158: 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);
2159: RAISE L_RETURN_IMM_EXC;
2160: ELSE
2161: 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 2161: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);

2157: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2158: 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);
2159: RAISE L_RETURN_IMM_EXC;
2160: ELSE
2161: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2162: END IF;
2163: ELSE
2164: IF p_index>1 THEN
2165: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

2180: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2181: 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);
2182: END IF;
2183: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2184: 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);
2185: END IF;
2186: LOOP
2187: FETCH l_party_cur INTO
2188: l_party_id , l_TX39, l_TX41, l_TX44;

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

2202: l_party_id_idx:= l_party_id_idx+1;
2203: END IF;
2204: IF (l_party_id_idx-1)>l_max_thresh THEN
2205: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2206: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2207: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2208: AND l_contact_pt_contains_str IS NULL THEN
2209: EXIT;
2210: END IF;

Line 2208: AND l_contact_pt_contains_str IS NULL THEN

2204: IF (l_party_id_idx-1)>l_max_thresh THEN
2205: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2206: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2207: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2208: AND l_contact_pt_contains_str IS NULL THEN
2209: EXIT;
2210: END IF;
2211: CLOSE l_party_cur;
2212: IF p_index>1 THEN

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

2233: ROLLBACK to eval_start;
2234: END;
2235:
2236: /** Private procedure to acquire and score at party site level ***/
2237: 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
2238: l_party_id_idx NUMBER:=1;
2239: l_ctx_id NUMBER;
2240: h_ps_id HZ_PARTY_SEARCH.IDList;
2241: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

2245: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2246: 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);
2247: 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);
2248: END IF;
2249: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2250: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2251: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2252: l_party_id := H_SCORES.FIRST;
2253: WHILE l_party_id IS NOT NULL LOOP

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

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

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

2247: 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);
2248: END IF;
2249: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2250: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2251: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2252: l_party_id := H_SCORES.FIRST;
2253: WHILE l_party_id IS NOT NULL LOOP
2254: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2255: l_party_id:=H_SCORES.NEXT(l_party_id);

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

2277: l_search_ctx_id,H_PARTY_ID(I));
2278: l_party_id_idx := H_PARTY_ID.COUNT+1;
2279: ELSE
2280: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2281: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2282: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2283: 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);
2284: END IF;
2285: IF p_index>1 THEN

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

2299: END IF;
2300: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2301: 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);
2302: END IF;
2303: 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);
2304: LOOP
2305: FETCH l_party_site_cur INTO
2306: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3;
2307: EXIT WHEN l_party_site_cur%NOTFOUND;

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

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

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

2389: l_search_ctx_id,H_PARTY_ID(I));
2390: l_party_id_idx := H_PARTY_ID.COUNT+1;
2391: ELSE
2392: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2393: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
2394: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2395: 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);
2396: END IF;
2397: IF p_index>1 THEN

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

2411: END IF;
2412: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2413: 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);
2414: END IF;
2415: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2416: LOOP
2417: FETCH l_contact_cur INTO
2418: l_org_contact_id, l_ct_party_id ;
2419: EXIT WHEN l_contact_cur%NOTFOUND;

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

2464: END IF;
2465: END;
2466:
2467: /** Private procedure to acquire and score at contact point level ***/
2468: 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
2469: l_party_id_idx NUMBER:=1;
2470: l_ctx_id NUMBER;
2471: h_cpt_id HZ_PARTY_SEARCH.IDList;
2472: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

2500: l_search_ctx_id,H_PARTY_ID(I));
2501: l_party_id_idx := H_PARTY_ID.COUNT+1;
2502: ELSE
2503: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2504: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
2505: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2506: 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);
2507: END IF;
2508: IF p_index>1 THEN

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

2522: END IF;
2523: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2524: 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);
2525: END IF;
2526: 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);
2527: LOOP
2528: FETCH l_contact_pt_cur INTO
2529: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
2530: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 2626: IF l_party_contains_str IS NOT NULL THEN

2622: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
2623: l_threshold := 20;
2624: other_acq_criteria_exists := TRUE ;
2625: --check if acquisition criteria exists for any other entity
2626: IF l_party_contains_str IS NOT NULL THEN
2627: acq_cnt := 1;
2628: END IF;
2629: IF l_party_site_contains_str IS NOT NULL THEN
2630: acq_cnt := acq_cnt+1;

Line 2629: IF l_party_site_contains_str IS NOT NULL THEN

2625: --check if acquisition criteria exists for any other entity
2626: IF l_party_contains_str IS NOT NULL THEN
2627: acq_cnt := 1;
2628: END IF;
2629: IF l_party_site_contains_str IS NOT NULL THEN
2630: acq_cnt := acq_cnt+1;
2631: END IF;
2632: IF l_contact_contains_str IS NOT NULL THEN
2633: acq_cnt := acq_cnt+1;

Line 2632: IF l_contact_contains_str IS NOT NULL THEN

2628: END IF;
2629: IF l_party_site_contains_str IS NOT NULL THEN
2630: acq_cnt := acq_cnt+1;
2631: END IF;
2632: IF l_contact_contains_str IS NOT NULL THEN
2633: acq_cnt := acq_cnt+1;
2634: END IF;
2635: IF l_contact_pt_contains_str IS NOT NULL THEN
2636: acq_cnt := acq_cnt+1;

Line 2635: IF l_contact_pt_contains_str IS NOT NULL THEN

2631: END IF;
2632: IF l_contact_contains_str IS NOT NULL THEN
2633: acq_cnt := acq_cnt+1;
2634: END IF;
2635: IF l_contact_pt_contains_str IS NOT NULL THEN
2636: acq_cnt := acq_cnt+1;
2637: END IF;
2638: IF acq_cnt>1 THEN
2639: other_acq_criteria_exists := TRUE;

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

2665: FND_MSG_PUB.ADD;
2666: RAISE FND_API.G_EXC_ERROR;
2667: END IF;
2668: END IF;
2669: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2670: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2671: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2672: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2673: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN

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

2666: RAISE FND_API.G_EXC_ERROR;
2667: END IF;
2668: END IF;
2669: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2670: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2671: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2672: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2673: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2674: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

2667: END IF;
2668: END IF;
2669: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2670: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2671: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2672: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2673: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2674: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2675: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

2668: END IF;
2669: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2670: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2671: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2672: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2673: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2674: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2675: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2676: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

2669: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2670: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2671: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2672: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2673: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2674: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2675: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2676: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2677: END IF;

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

2670: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2671: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2672: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2673: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2674: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2675: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2676: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2677: END IF;
2678: END;

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

2671: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2672: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2673: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2674: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2675: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2676: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2677: END IF;
2678: END;
2679: /************ End of find_parties private procedures **********/

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

2672: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2673: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2674: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2675: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2676: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2677: END IF;
2678: END;
2679: /************ End of find_parties private procedures **********/
2680:

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

2755: 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);
2756: END IF;
2757: -- ************************************************************
2758: -- STEP 2. Setup of intermedia query strings for Acquisition query
2759: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2760: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2761: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2762: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2763: 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 2760: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);

2756: END IF;
2757: -- ************************************************************
2758: -- STEP 2. Setup of intermedia query strings for Acquisition query
2759: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2760: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2761: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2762: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2763: 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);
2764: END IF;

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

2757: -- ************************************************************
2758: -- STEP 2. Setup of intermedia query strings for Acquisition query
2759: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2760: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2761: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2762: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2763: 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);
2764: END IF;
2765: l_denorm_max_score:=0;

Line 2793: 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((20/78)*l_entered_max_score));

2789: END IF;
2790: ELSE
2791: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
2792: END IF;
2793: 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((20/78)*l_entered_max_score));
2794: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
2795:
2796: -- Setup Search Context ID
2797: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 2803: IF l_party_contains_str IS NULL THEN

2799:
2800: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2801: 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);
2802: END IF;
2803: IF l_party_contains_str IS NULL THEN
2804: defpt := 1;
2805: END IF;
2806: IF l_party_site_contains_str IS NULL THEN
2807: defps := 1;

Line 2806: IF l_party_site_contains_str IS NULL THEN

2802: END IF;
2803: IF l_party_contains_str IS NULL THEN
2804: defpt := 1;
2805: END IF;
2806: IF l_party_site_contains_str IS NULL THEN
2807: defps := 1;
2808: END IF;
2809: IF l_contact_contains_str IS NULL THEN
2810: defct := 1;

Line 2809: IF l_contact_contains_str IS NULL THEN

2805: END IF;
2806: IF l_party_site_contains_str IS NULL THEN
2807: defps := 1;
2808: END IF;
2809: IF l_contact_contains_str IS NULL THEN
2810: defct := 1;
2811: END IF;
2812: IF l_contact_pt_contains_str IS NULL THEN
2813: defcpt := 1;

Line 2812: IF l_contact_pt_contains_str IS NULL THEN

2808: END IF;
2809: IF l_contact_contains_str IS NULL THEN
2810: defct := 1;
2811: END IF;
2812: IF l_contact_pt_contains_str IS NULL THEN
2813: defcpt := 1;
2814: END IF;
2815:
2816: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

2817: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2818: 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);
2819: END IF;
2820: 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);
2821: 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);
2822: 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);
2823: 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);
2824: 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);
2825: 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 2822: 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);

2818: 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);
2819: END IF;
2820: 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);
2821: 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);
2822: 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);
2823: 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);
2824: 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);
2825: 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);
2826: END IF;

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

2819: END IF;
2820: 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);
2821: 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);
2822: 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);
2823: 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);
2824: 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);
2825: 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);
2826: END IF;
2827: FOR I in 1..5 LOOP

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

2820: 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);
2821: 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);
2822: 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);
2823: 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);
2824: 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);
2825: 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);
2826: END IF;
2827: FOR I in 1..5 LOOP
2828: do_eval(I);

Line 2938: l_party_contains_str VARCHAR2(32000);

2934: x_num_matches OUT NUMBER
2935: ) IS
2936:
2937: -- Strings to hold the generated Intermedia query strings
2938: l_party_contains_str VARCHAR2(32000);
2939: l_party_site_contains_str VARCHAR2(32000);
2940: l_contact_contains_str VARCHAR2(32000);
2941: l_contact_pt_contains_str VARCHAR2(32000);
2942: l_denorm_str VARCHAR2(32000);

Line 2939: l_party_site_contains_str VARCHAR2(32000);

2935: ) IS
2936:
2937: -- Strings to hold the generated Intermedia query strings
2938: l_party_contains_str VARCHAR2(32000);
2939: l_party_site_contains_str VARCHAR2(32000);
2940: l_contact_contains_str VARCHAR2(32000);
2941: l_contact_pt_contains_str VARCHAR2(32000);
2942: l_denorm_str VARCHAR2(32000);
2943: l_ps_denorm_str VARCHAR2(32000);

Line 2940: l_contact_contains_str VARCHAR2(32000);

2936:
2937: -- Strings to hold the generated Intermedia query strings
2938: l_party_contains_str VARCHAR2(32000);
2939: l_party_site_contains_str VARCHAR2(32000);
2940: l_contact_contains_str VARCHAR2(32000);
2941: l_contact_pt_contains_str VARCHAR2(32000);
2942: l_denorm_str VARCHAR2(32000);
2943: l_ps_denorm_str VARCHAR2(32000);
2944: l_ct_denorm_str VARCHAR2(32000);

Line 2941: l_contact_pt_contains_str VARCHAR2(32000);

2937: -- Strings to hold the generated Intermedia query strings
2938: l_party_contains_str VARCHAR2(32000);
2939: l_party_site_contains_str VARCHAR2(32000);
2940: l_contact_contains_str VARCHAR2(32000);
2941: l_contact_pt_contains_str VARCHAR2(32000);
2942: l_denorm_str VARCHAR2(32000);
2943: l_ps_denorm_str VARCHAR2(32000);
2944: l_ct_denorm_str VARCHAR2(32000);
2945: l_cpt_denorm_str VARCHAR2(32000);

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

3056: call_type(2) := 'OR';
3057: END;
3058:
3059: /** Private procedure to acquire and score at party level ***/
3060: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3061: l_party_id_idx NUMBER:=1;
3062: l_ctx_id NUMBER;
3063: l_TX35_new varchar2(4000);
3064: BEGIN

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

3078: l_search_ctx_id,H_PARTY_ID(I));
3079: l_party_id_idx := H_PARTY_ID.COUNT+1;
3080: ELSE
3081: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3082: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3083: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3084: 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);
3085: END IF;
3086: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN

Line 3090: IF (l_party_site_contains_str IS NULL AND

3086: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3087: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3088: 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);
3089: END IF;
3090: IF (l_party_site_contains_str IS NULL AND
3091: l_contact_contains_str IS NULL AND
3092: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3093: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3094: RAISE L_RETURN_IMM_EXC;

Line 3091: l_contact_contains_str IS NULL AND

3087: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3088: 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);
3089: END IF;
3090: IF (l_party_site_contains_str IS NULL AND
3091: l_contact_contains_str IS NULL AND
3092: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3093: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3094: RAISE L_RETURN_IMM_EXC;
3095: ELSE

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

3088: 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);
3089: END IF;
3090: IF (l_party_site_contains_str IS NULL AND
3091: l_contact_contains_str IS NULL AND
3092: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3093: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3094: RAISE L_RETURN_IMM_EXC;
3095: ELSE
3096: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);

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

3092: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3093: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3094: RAISE L_RETURN_IMM_EXC;
3095: ELSE
3096: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3097: END IF;
3098: ELSE
3099: IF p_index>1 THEN
3100: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3112: l_ctx_id := NULL;
3113: l_party_id_idx := H_PARTY_ID.COUNT+1;
3114: END IF;
3115: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3116: 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);
3117: END IF;
3118: LOOP
3119: FETCH l_party_cur INTO
3120: l_party_id , l_TX39, l_TX41, l_TX44;

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

3134: l_party_id_idx:= l_party_id_idx+1;
3135: END IF;
3136: IF (l_party_id_idx-1)>l_max_thresh THEN
3137: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3138: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3139: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3140: AND l_contact_pt_contains_str IS NULL THEN
3141: H_PARTY_ID.DELETE(l_party_id_idx-1);
3142: H_PARTY_ID_LIST.DELETE(l_index);

Line 3140: AND l_contact_pt_contains_str IS NULL THEN

3136: IF (l_party_id_idx-1)>l_max_thresh THEN
3137: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3138: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3139: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3140: AND l_contact_pt_contains_str IS NULL THEN
3141: H_PARTY_ID.DELETE(l_party_id_idx-1);
3142: H_PARTY_ID_LIST.DELETE(l_index);
3143: H_SCORES.DELETE(l_index);
3144: EXIT;

Line 3169: p_contains_str VARCHAR2,

3165: CLOSE l_party_cur;
3166: ROLLBACK to eval_start;
3167: END;
3168: PROCEDURE open_person_contact_cursor(
3169: p_contains_str VARCHAR2,
3170: p_search_ctx_id NUMBER,
3171: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3172: BEGIN
3173: OPEN x_cursor FOR

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

3172: BEGIN
3173: OPEN x_cursor FOR
3174: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
3175: FROM HZ_STAGED_CONTACTS stage
3176: WHERE contains( concat_col, p_contains_str)>0
3177: AND ORG_CONTACT_ID in (
3178: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3179: org_contact_id
3180: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc

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

3196: end if;
3197: END;
3198:
3199: /** Private procedure to acquire and score at party site level ***/
3200: 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
3201: l_party_id_idx NUMBER:=1;
3202: l_ctx_id NUMBER;
3203: h_ps_id HZ_PARTY_SEARCH.IDList;
3204: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

3224: ELSE
3225: l_party_id_idx := H_PARTY_ID.COUNT+1;
3226: l_ctx_id := NULL;
3227: END IF;
3228: 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);
3229: LOOP
3230: FETCH l_party_site_cur INTO
3231: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3;
3232: EXIT WHEN l_party_site_cur%NOTFOUND;

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

3295: END IF;
3296: END;
3297:
3298: /** Private procedure to acquire and score at party site level ***/
3299: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3300: l_party_id_idx NUMBER:=1;
3301: l_ctx_id NUMBER;
3302: h_ct_id HZ_PARTY_SEARCH.IDList;
3303: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

3311: FORALL I in 1..H_PARTY_ID.COUNT
3312: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
3313: l_search_ctx_id,H_PARTY_ID(I));
3314: l_party_id_idx := H_PARTY_ID.COUNT+1;
3315: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
3316: LOOP
3317: FETCH l_contact_cur INTO
3318: l_org_contact_id, l_ct_party_id ;
3319: EXIT WHEN l_contact_cur%NOTFOUND;

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

3353: END IF;
3354: END;
3355:
3356: /** Private procedure to acquire and score at contact point level ***/
3357: 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
3358: l_party_id_idx NUMBER:=1;
3359: l_ctx_id NUMBER;
3360: h_cpt_id HZ_PARTY_SEARCH.IDList;
3361: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

3398: END IF;
3399: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3400: 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);
3401: END IF;
3402: 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);
3403: LOOP
3404: FETCH l_contact_pt_cur INTO
3405: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
3406: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 3510: IF l_party_contains_str IS NOT NULL THEN

3506: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3507: l_threshold := 20;
3508: other_acq_criteria_exists := TRUE ;
3509: --check if acquisition criteria exists for any other entity
3510: IF l_party_contains_str IS NOT NULL THEN
3511: acq_cnt := 1;
3512: END IF;
3513: IF l_party_site_contains_str IS NOT NULL THEN
3514: acq_cnt := acq_cnt+1;

Line 3513: IF l_party_site_contains_str IS NOT NULL THEN

3509: --check if acquisition criteria exists for any other entity
3510: IF l_party_contains_str IS NOT NULL THEN
3511: acq_cnt := 1;
3512: END IF;
3513: IF l_party_site_contains_str IS NOT NULL THEN
3514: acq_cnt := acq_cnt+1;
3515: END IF;
3516: IF l_contact_contains_str IS NOT NULL THEN
3517: acq_cnt := acq_cnt+1;

Line 3516: IF l_contact_contains_str IS NOT NULL THEN

3512: END IF;
3513: IF l_party_site_contains_str IS NOT NULL THEN
3514: acq_cnt := acq_cnt+1;
3515: END IF;
3516: IF l_contact_contains_str IS NOT NULL THEN
3517: acq_cnt := acq_cnt+1;
3518: END IF;
3519: IF l_contact_pt_contains_str IS NOT NULL THEN
3520: acq_cnt := acq_cnt+1;

Line 3519: IF l_contact_pt_contains_str IS NOT NULL THEN

3515: END IF;
3516: IF l_contact_contains_str IS NOT NULL THEN
3517: acq_cnt := acq_cnt+1;
3518: END IF;
3519: IF l_contact_pt_contains_str IS NOT NULL THEN
3520: acq_cnt := acq_cnt+1;
3521: END IF;
3522: IF acq_cnt>1 THEN
3523: other_acq_criteria_exists := TRUE;

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

3549: FND_MSG_PUB.ADD;
3550: RAISE FND_API.G_EXC_ERROR;
3551: END IF;
3552: END IF;
3553: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3554: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3555: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3556: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3557: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

3550: RAISE FND_API.G_EXC_ERROR;
3551: END IF;
3552: END IF;
3553: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3554: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3555: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3556: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3557: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3558: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

3551: END IF;
3552: END IF;
3553: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3554: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3555: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3556: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3557: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3558: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3559: END IF;

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

3552: END IF;
3553: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3554: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3555: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3556: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3557: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3558: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3559: END IF;
3560: END;

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

3553: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3554: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3555: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3556: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3557: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3558: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3559: END IF;
3560: END;
3561: /************ End of find_persons private procedures **********/

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

3554: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3555: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3556: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3557: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3558: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3559: END IF;
3560: END;
3561: /************ End of find_persons private procedures **********/
3562:

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

3589: g_score_until_thresh := false;
3590: END IF;
3591: -- ************************************************************
3592: -- STEP 2. Setup of intermedia query strings for Acquisition query
3593: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3594: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3595: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3596: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3597: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);

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

3590: END IF;
3591: -- ************************************************************
3592: -- STEP 2. Setup of intermedia query strings for Acquisition query
3593: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3594: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3595: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3596: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3597: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3598:

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

3591: -- ************************************************************
3592: -- STEP 2. Setup of intermedia query strings for Acquisition query
3593: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3594: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3595: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3596: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3597: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3598:
3599: -- Setup Search Context ID

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

3592: -- STEP 2. Setup of intermedia query strings for Acquisition query
3593: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3594: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3595: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3596: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3597: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3598:
3599: -- Setup Search Context ID
3600: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 3603: IF l_party_contains_str IS NULL THEN

3599: -- Setup Search Context ID
3600: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3601: x_search_ctx_id := l_search_ctx_id;
3602:
3603: IF l_party_contains_str IS NULL THEN
3604: defpt := 1;
3605: END IF;
3606: IF l_party_site_contains_str IS NULL THEN
3607: defps := 1;

Line 3606: IF l_party_site_contains_str IS NULL THEN

3602:
3603: IF l_party_contains_str IS NULL THEN
3604: defpt := 1;
3605: END IF;
3606: IF l_party_site_contains_str IS NULL THEN
3607: defps := 1;
3608: END IF;
3609: IF l_contact_contains_str IS NULL THEN
3610: defct := 1;

Line 3609: IF l_contact_contains_str IS NULL THEN

3605: END IF;
3606: IF l_party_site_contains_str IS NULL THEN
3607: defps := 1;
3608: END IF;
3609: IF l_contact_contains_str IS NULL THEN
3610: defct := 1;
3611: END IF;
3612: IF l_contact_pt_contains_str IS NULL THEN
3613: defcpt := 1;

Line 3612: IF l_contact_pt_contains_str IS NULL THEN

3608: END IF;
3609: IF l_contact_contains_str IS NULL THEN
3610: defct := 1;
3611: END IF;
3612: IF l_contact_pt_contains_str IS NULL THEN
3613: defcpt := 1;
3614: END IF;
3615:
3616: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3614: END IF;
3615:
3616: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3617: 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);
3618: 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);
3619: 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);
3620: 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);
3621: 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);
3622: 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 3619: 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);

3615:
3616: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3617: 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);
3618: 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);
3619: 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);
3620: 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);
3621: 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);
3622: 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);
3623: END IF;

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

3616: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3617: 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);
3618: 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);
3619: 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);
3620: 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);
3621: 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);
3622: 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);
3623: END IF;
3624: FOR I in 1..5 LOOP

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

3617: 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);
3618: 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);
3619: 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);
3620: 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);
3621: 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);
3622: 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);
3623: END IF;
3624: FOR I in 1..5 LOOP
3625: do_eval(I);

Line 3627: IF l_contact_contains_str IS NOT NULL THEN

3623: END IF;
3624: FOR I in 1..5 LOOP
3625: do_eval(I);
3626: END LOOP;
3627: IF l_contact_contains_str IS NOT NULL THEN
3628: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3629: END IF;
3630: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3631: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 26 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

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

3624: FOR I in 1..5 LOOP
3625: do_eval(I);
3626: END LOOP;
3627: IF l_contact_contains_str IS NOT NULL THEN
3628: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3629: END IF;
3630: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3631: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 26 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3632: END IF;

Line 4415: l_party_contains_str VARCHAR2(32000);

4411: x_num_matches OUT NUMBER
4412: ) IS
4413:
4414: -- Strings to hold the generated Intermedia query strings
4415: l_party_contains_str VARCHAR2(32000);
4416: l_match_idx NUMBER;
4417: l_party_site_contains_str VARCHAR2(32000);
4418: l_contact_contains_str VARCHAR2(32000);
4419: l_contact_pt_contains_str VARCHAR2(32000);

Line 4417: l_party_site_contains_str VARCHAR2(32000);

4413:
4414: -- Strings to hold the generated Intermedia query strings
4415: l_party_contains_str VARCHAR2(32000);
4416: l_match_idx NUMBER;
4417: l_party_site_contains_str VARCHAR2(32000);
4418: l_contact_contains_str VARCHAR2(32000);
4419: l_contact_pt_contains_str VARCHAR2(32000);
4420: l_tmp VARCHAR2(32000);
4421:

Line 4418: l_contact_contains_str VARCHAR2(32000);

4414: -- Strings to hold the generated Intermedia query strings
4415: l_party_contains_str VARCHAR2(32000);
4416: l_match_idx NUMBER;
4417: l_party_site_contains_str VARCHAR2(32000);
4418: l_contact_contains_str VARCHAR2(32000);
4419: l_contact_pt_contains_str VARCHAR2(32000);
4420: l_tmp VARCHAR2(32000);
4421:
4422: -- Other local variables

Line 4419: l_contact_pt_contains_str VARCHAR2(32000);

4415: l_party_contains_str VARCHAR2(32000);
4416: l_match_idx NUMBER;
4417: l_party_site_contains_str VARCHAR2(32000);
4418: l_contact_contains_str VARCHAR2(32000);
4419: l_contact_pt_contains_str VARCHAR2(32000);
4420: l_tmp VARCHAR2(32000);
4421:
4422: -- Other local variables
4423: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4489: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4490:
4491: -- ************************************************************
4492: -- STEP 2. Setup of intermedia query strings for Acquisition query
4493: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4494: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4495: 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);
4496:
4497: -- Setup Search Context ID

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

4490:
4491: -- ************************************************************
4492: -- STEP 2. Setup of intermedia query strings for Acquisition query
4493: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4494: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4495: 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);
4496:
4497: -- Setup Search Context ID
4498: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4499: x_search_ctx_id := l_search_ctx_id;
4500:
4501: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4502: 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);
4503: 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);
4504: 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);
4505: 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);
4506: END IF;
4507: IF l_party_site_contains_str IS NULL THEN

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

4500:
4501: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4502: 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);
4503: 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);
4504: 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);
4505: 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);
4506: END IF;
4507: IF l_party_site_contains_str IS NULL THEN
4508: defps := 1;

Line 4507: IF l_party_site_contains_str IS NULL THEN

4503: 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);
4504: 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);
4505: 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);
4506: END IF;
4507: IF l_party_site_contains_str IS NULL THEN
4508: defps := 1;
4509: END IF;
4510: IF l_contact_pt_contains_str IS NULL THEN
4511: defcpt := 1;

Line 4510: IF l_contact_pt_contains_str IS NULL THEN

4506: END IF;
4507: IF l_party_site_contains_str IS NULL THEN
4508: defps := 1;
4509: END IF;
4510: IF l_contact_pt_contains_str IS NULL THEN
4511: defcpt := 1;
4512: END IF;
4513:
4514: IF l_party_site_contains_str IS NOT NULL THEN

Line 4514: IF l_party_site_contains_str IS NOT NULL THEN

4510: IF l_contact_pt_contains_str IS NULL THEN
4511: defcpt := 1;
4512: END IF;
4513:
4514: IF l_party_site_contains_str IS NOT NULL THEN
4515: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4516: LOOP
4517: FETCH l_party_site_cur INTO
4518: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3;

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

4511: defcpt := 1;
4512: END IF;
4513:
4514: IF l_party_site_contains_str IS NOT NULL THEN
4515: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4516: LOOP
4517: FETCH l_party_site_cur INTO
4518: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3;
4519: EXIT WHEN l_party_site_cur%NOTFOUND;

Line 4538: IF l_contact_pt_contains_str IS NOT NULL THEN

4534: END LOOP;
4535: CLOSE l_party_site_cur;
4536: END IF;
4537:
4538: IF l_contact_pt_contains_str IS NOT NULL THEN
4539: SAVEPOINT eval_start;
4540: IF l_match_str = ' AND ' OR (
4541: ((l_cpt_max_score/l_entered_max_score)<(20/78))
4542: ) THEN

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

4542: ) THEN
4543: FORALL I in 1..l_match_ps_list.COUNT
4544: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4545: values (l_search_ctx_id,l_match_ps_list(I));
4546: 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');
4547: ELSE
4548: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4549: END IF;
4550: LOOP

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

4544: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4545: values (l_search_ctx_id,l_match_ps_list(I));
4546: 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');
4547: ELSE
4548: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4549: END IF;
4550: LOOP
4551: FETCH l_contact_pt_cur INTO
4552: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 4648: l_party_contains_str VARCHAR2(32000);

4644: x_num_matches OUT NUMBER
4645: ) IS
4646:
4647: -- Strings to hold the generated Intermedia query strings
4648: l_party_contains_str VARCHAR2(32000);
4649: l_party_site_contains_str VARCHAR2(32000);
4650: l_contact_contains_str VARCHAR2(32000);
4651: l_contact_pt_contains_str VARCHAR2(32000);
4652: l_tmp VARCHAR2(32000);

Line 4649: l_party_site_contains_str VARCHAR2(32000);

4645: ) IS
4646:
4647: -- Strings to hold the generated Intermedia query strings
4648: l_party_contains_str VARCHAR2(32000);
4649: l_party_site_contains_str VARCHAR2(32000);
4650: l_contact_contains_str VARCHAR2(32000);
4651: l_contact_pt_contains_str VARCHAR2(32000);
4652: l_tmp VARCHAR2(32000);
4653:

Line 4650: l_contact_contains_str VARCHAR2(32000);

4646:
4647: -- Strings to hold the generated Intermedia query strings
4648: l_party_contains_str VARCHAR2(32000);
4649: l_party_site_contains_str VARCHAR2(32000);
4650: l_contact_contains_str VARCHAR2(32000);
4651: l_contact_pt_contains_str VARCHAR2(32000);
4652: l_tmp VARCHAR2(32000);
4653:
4654: -- Other local variables

Line 4651: l_contact_pt_contains_str VARCHAR2(32000);

4647: -- Strings to hold the generated Intermedia query strings
4648: l_party_contains_str VARCHAR2(32000);
4649: l_party_site_contains_str VARCHAR2(32000);
4650: l_contact_contains_str VARCHAR2(32000);
4651: l_contact_pt_contains_str VARCHAR2(32000);
4652: l_tmp VARCHAR2(32000);
4653:
4654: -- Other local variables
4655: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4718: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4719:
4720: -- ************************************************************
4721: -- STEP 2. Setup of intermedia query strings for Acquisition query
4722: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4723: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4724: 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);
4725:
4726: -- Setup Search Context ID

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

4719:
4720: -- ************************************************************
4721: -- STEP 2. Setup of intermedia query strings for Acquisition query
4722: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4723: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4724: 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);
4725:
4726: -- Setup Search Context ID
4727: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4728: x_search_ctx_id := l_search_ctx_id;
4729:
4730: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4731: 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);
4732: 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);
4733: 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);
4734: 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);
4735: END IF;
4736: IF l_contact_contains_str IS NULL THEN

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

4729:
4730: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4731: 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);
4732: 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);
4733: 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);
4734: 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);
4735: END IF;
4736: IF l_contact_contains_str IS NULL THEN
4737: defct := 1;

Line 4736: IF l_contact_contains_str IS NULL THEN

4732: 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);
4733: 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);
4734: 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);
4735: END IF;
4736: IF l_contact_contains_str IS NULL THEN
4737: defct := 1;
4738: END IF;
4739: IF l_contact_pt_contains_str IS NULL THEN
4740: defcpt := 1;

Line 4739: IF l_contact_pt_contains_str IS NULL THEN

4735: END IF;
4736: IF l_contact_contains_str IS NULL THEN
4737: defct := 1;
4738: END IF;
4739: IF l_contact_pt_contains_str IS NULL THEN
4740: defcpt := 1;
4741: END IF;
4742:
4743: IF l_contact_contains_str IS NOT NULL THEN

Line 4743: IF l_contact_contains_str IS NOT NULL THEN

4739: IF l_contact_pt_contains_str IS NULL THEN
4740: defcpt := 1;
4741: END IF;
4742:
4743: IF l_contact_contains_str IS NOT NULL THEN
4744: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
4745: LOOP
4746: FETCH l_contact_cur INTO
4747: l_org_contact_id, l_ct_party_id ;

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

4740: defcpt := 1;
4741: END IF;
4742:
4743: IF l_contact_contains_str IS NOT NULL THEN
4744: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
4745: LOOP
4746: FETCH l_contact_cur INTO
4747: l_org_contact_id, l_ct_party_id ;
4748: EXIT WHEN l_contact_cur%NOTFOUND;

Line 4765: IF l_contact_pt_contains_str IS NOT NULL THEN

4761: END LOOP;
4762: CLOSE l_contact_cur;
4763: END IF;
4764:
4765: IF l_contact_pt_contains_str IS NOT NULL THEN
4766: SAVEPOINT eval_start;
4767: IF l_match_str = ' AND ' OR (
4768: ((l_cpt_max_score/l_entered_max_score)<(20/78))
4769: ) THEN

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

4769: ) THEN
4770: FORALL I in 1..l_match_contact_list.COUNT
4771: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4772: values (l_search_ctx_id,l_match_contact_list(I));
4773: 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');
4774: ELSE
4775: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4776: END IF;
4777: LOOP

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

4771: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4772: values (l_search_ctx_id,l_match_contact_list(I));
4773: 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');
4774: ELSE
4775: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4776: END IF;
4777: LOOP
4778: FETCH l_contact_pt_cur INTO
4779: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 4873: l_contact_pt_contains_str VARCHAR2(32000);

4869: ) IS
4870:
4871:
4872: -- Strings to hold the generated Intermedia query strings
4873: l_contact_pt_contains_str VARCHAR2(32000);
4874: l_tmp VARCHAR2(32000);
4875:
4876: -- Other local variables
4877: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4927: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4928:
4929: -- ************************************************************
4930: -- STEP 2. Setup of intermedia query strings for Acquisition query
4931: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4932: 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);
4933:
4934: -- Setup Search Context ID
4935: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4935: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4936: x_search_ctx_id := l_search_ctx_id;
4937: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4938: 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);
4939: 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);
4940: 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);
4941: END IF;
4942:
4943: IF l_contact_pt_contains_str IS NOT NULL THEN

Line 4943: IF l_contact_pt_contains_str IS NOT NULL THEN

4939: 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);
4940: 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);
4941: END IF;
4942:
4943: IF l_contact_pt_contains_str IS NOT NULL THEN
4944: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4945: cnt := 1;
4946: LOOP
4947: FETCH l_contact_pt_cur INTO

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

4940: 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);
4941: END IF;
4942:
4943: IF l_contact_pt_contains_str IS NOT NULL THEN
4944: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4945: cnt := 1;
4946: LOOP
4947: FETCH l_contact_pt_cur INTO
4948: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 5015: l_party_contains_str VARCHAR2(32000);

5011: x_search_ctx_id IN OUT NUMBER
5012: ) IS
5013:
5014: -- Strings to hold the generated Intermedia query strings
5015: l_party_contains_str VARCHAR2(32000);
5016: l_party_site_contains_str VARCHAR2(32000);
5017: l_contact_contains_str VARCHAR2(32000);
5018: l_contact_pt_contains_str VARCHAR2(32000);
5019: l_tmp VARCHAR2(32000);

Line 5016: l_party_site_contains_str VARCHAR2(32000);

5012: ) IS
5013:
5014: -- Strings to hold the generated Intermedia query strings
5015: l_party_contains_str VARCHAR2(32000);
5016: l_party_site_contains_str VARCHAR2(32000);
5017: l_contact_contains_str VARCHAR2(32000);
5018: l_contact_pt_contains_str VARCHAR2(32000);
5019: l_tmp VARCHAR2(32000);
5020:

Line 5017: l_contact_contains_str VARCHAR2(32000);

5013:
5014: -- Strings to hold the generated Intermedia query strings
5015: l_party_contains_str VARCHAR2(32000);
5016: l_party_site_contains_str VARCHAR2(32000);
5017: l_contact_contains_str VARCHAR2(32000);
5018: l_contact_pt_contains_str VARCHAR2(32000);
5019: l_tmp VARCHAR2(32000);
5020:
5021: -- Other local variables

Line 5018: l_contact_pt_contains_str VARCHAR2(32000);

5014: -- Strings to hold the generated Intermedia query strings
5015: l_party_contains_str VARCHAR2(32000);
5016: l_party_site_contains_str VARCHAR2(32000);
5017: l_contact_contains_str VARCHAR2(32000);
5018: l_contact_pt_contains_str VARCHAR2(32000);
5019: l_tmp VARCHAR2(32000);
5020:
5021: -- Other local variables
5022: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5080: g_score_until_thresh := false;
5081: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5082: -- ************************************************************
5083: -- STEP 2. Setup of intermedia query strings for Acquisition query
5084: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5085: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5086: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5087: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5088:

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

5081: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5082: -- ************************************************************
5083: -- STEP 2. Setup of intermedia query strings for Acquisition query
5084: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5085: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5086: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5087: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5088:
5089: -- Setup Search Context ID

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

5082: -- ************************************************************
5083: -- STEP 2. Setup of intermedia query strings for Acquisition query
5084: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5085: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5086: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5087: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5088:
5089: -- Setup Search Context ID
5090: IF x_search_ctx_id IS NULL THEN

Line 5106: IF l_party_site_contains_str IS NOT NULL THEN

5102: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX39,l_TX41,l_TX44,1);
5103: END LOOP;
5104: CLOSE l_party_cur;
5105:
5106: IF l_party_site_contains_str IS NOT NULL THEN
5107: l_max_score := 0;
5108: l_max_id := 0;
5109: l_max_idx := 0;
5110: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN

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

5107: l_max_score := 0;
5108: l_max_id := 0;
5109: l_max_idx := 0;
5110: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5111: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5112: ELSE
5113: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5114: END IF;
5115: LOOP

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

5109: l_max_idx := 0;
5110: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5111: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5112: ELSE
5113: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5114: END IF;
5115: LOOP
5116: FETCH l_party_site_cur INTO
5117: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX3;

Line 5133: IF l_contact_contains_str IS NOT NULL THEN

5129: 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_TX3,l_max_idx);
5130: END IF;
5131: END IF;
5132:
5133: IF l_contact_contains_str IS NOT NULL THEN
5134: l_max_score := 0;
5135: l_max_id := 0;
5136: l_max_idx := 0;
5137: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

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

5133: IF l_contact_contains_str IS NOT NULL THEN
5134: l_max_score := 0;
5135: l_max_id := 0;
5136: l_max_idx := 0;
5137: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5138: LOOP
5139: FETCH l_contact_cur INTO
5140: l_org_contact_id, l_ct_party_id ;
5141: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5155: IF l_contact_pt_contains_str IS NOT NULL THEN

5151: 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);
5152: END IF;
5153: END IF;
5154:
5155: IF l_contact_pt_contains_str IS NOT NULL THEN
5156: l_max_score := 0;
5157: l_max_id := 0;
5158: l_max_idx := 0;
5159: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN

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

5156: l_max_score := 0;
5157: l_max_id := 0;
5158: l_max_idx := 0;
5159: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5160: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5161: ELSE
5162: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5163: END IF;
5164: LOOP

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

5158: l_max_idx := 0;
5159: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5160: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5161: ELSE
5162: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5163: END IF;
5164: LOOP
5165: FETCH l_contact_pt_cur INTO
5166: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;