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

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

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

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

Line 1232: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

Line 1263: p_contains_str VARCHAR2,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 1442: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

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

Line 1443: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

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

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

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

Line 1457: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 1474: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 1508: p_contains_str VARCHAR2,

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

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

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

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

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

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

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

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

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

Line 1622: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 1655: p_contains_str VARCHAR2,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 1861: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

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

Line 1862: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

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

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

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

Line 1876: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 1893: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 2021: l_party_contains_str VARCHAR2(32000);

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

Line 2022: l_party_site_contains_str VARCHAR2(32000);

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

Line 2023: l_contact_contains_str VARCHAR2(32000);

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

Line 2024: l_contact_pt_contains_str VARCHAR2(32000);

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

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

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

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

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

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

Line 2153: l_contact_contains_str IS NULL AND

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

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

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

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

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

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

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

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

Line 2205: AND l_contact_pt_contains_str IS NULL THEN

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 2623: IF l_party_contains_str IS NOT NULL THEN

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

Line 2626: IF l_party_site_contains_str IS NOT NULL THEN

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

Line 2629: IF l_contact_contains_str IS NOT NULL THEN

2625: END IF;
2626: IF l_party_site_contains_str IS NOT NULL THEN
2627: acq_cnt := acq_cnt+1;
2628: END IF;
2629: IF l_contact_contains_str IS NOT NULL THEN
2630: acq_cnt := acq_cnt+1;
2631: END IF;
2632: IF l_contact_pt_contains_str IS NOT NULL THEN
2633: acq_cnt := acq_cnt+1;

Line 2632: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2786: END IF;
2787: ELSE
2788: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
2789: END IF;
2790: 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));
2791: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
2792:
2793: -- Setup Search Context ID
2794: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 2800: IF l_party_contains_str IS NULL THEN

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

Line 2803: IF l_party_site_contains_str IS NULL THEN

2799: END IF;
2800: IF l_party_contains_str IS NULL THEN
2801: defpt := 1;
2802: END IF;
2803: IF l_party_site_contains_str IS NULL THEN
2804: defps := 1;
2805: END IF;
2806: IF l_contact_contains_str IS NULL THEN
2807: defct := 1;

Line 2806: IF l_contact_contains_str IS NULL THEN

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

Line 2809: IF l_contact_pt_contains_str IS NULL THEN

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

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

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

2815: 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);
2816: END IF;
2817: 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);
2818: 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);
2819: 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);
2820: 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);
2821: 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);
2822: 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);
2823: END IF;

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

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

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

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

Line 2935: l_party_contains_str VARCHAR2(32000);

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

Line 2936: l_party_site_contains_str VARCHAR2(32000);

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

Line 2937: l_contact_contains_str VARCHAR2(32000);

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

Line 2938: l_contact_pt_contains_str VARCHAR2(32000);

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

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

3053: call_type(2) := 'OR';
3054: END;
3055:
3056: /** Private procedure to acquire and score at party level ***/
3057: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3058: l_party_id_idx NUMBER:=1;
3059: l_ctx_id NUMBER;
3060: BEGIN
3061: SAVEPOINT eval_start;

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

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

Line 3086: IF (l_party_site_contains_str IS NULL AND

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

Line 3087: l_contact_contains_str IS NULL AND

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

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

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

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

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

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

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

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

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

Line 3136: AND l_contact_pt_contains_str IS NULL THEN

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

Line 3165: p_contains_str VARCHAR2,

3161: CLOSE l_party_cur;
3162: ROLLBACK to eval_start;
3163: END;
3164: PROCEDURE open_person_contact_cursor(
3165: p_contains_str VARCHAR2,
3166: p_search_ctx_id NUMBER,
3167: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3168: BEGIN
3169: OPEN x_cursor FOR

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 3506: IF l_party_contains_str IS NOT NULL THEN

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

Line 3509: IF l_party_site_contains_str IS NOT NULL THEN

3505: --check if acquisition criteria exists for any other entity
3506: IF l_party_contains_str IS NOT NULL THEN
3507: acq_cnt := 1;
3508: END IF;
3509: IF l_party_site_contains_str IS NOT NULL THEN
3510: acq_cnt := acq_cnt+1;
3511: END IF;
3512: IF l_contact_contains_str IS NOT NULL THEN
3513: acq_cnt := acq_cnt+1;

Line 3512: IF l_contact_contains_str IS NOT NULL THEN

3508: END IF;
3509: IF l_party_site_contains_str IS NOT NULL THEN
3510: acq_cnt := acq_cnt+1;
3511: END IF;
3512: IF l_contact_contains_str IS NOT NULL THEN
3513: acq_cnt := acq_cnt+1;
3514: END IF;
3515: IF l_contact_pt_contains_str IS NOT NULL THEN
3516: acq_cnt := acq_cnt+1;

Line 3515: IF l_contact_pt_contains_str IS NOT NULL THEN

3511: END IF;
3512: IF l_contact_contains_str IS NOT NULL THEN
3513: acq_cnt := acq_cnt+1;
3514: END IF;
3515: IF l_contact_pt_contains_str IS NOT NULL THEN
3516: acq_cnt := acq_cnt+1;
3517: END IF;
3518: IF acq_cnt>1 THEN
3519: other_acq_criteria_exists := TRUE;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 3599: IF l_party_contains_str IS NULL THEN

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

Line 3602: IF l_party_site_contains_str IS NULL THEN

3598:
3599: IF l_party_contains_str IS NULL THEN
3600: defpt := 1;
3601: END IF;
3602: IF l_party_site_contains_str IS NULL THEN
3603: defps := 1;
3604: END IF;
3605: IF l_contact_contains_str IS NULL THEN
3606: defct := 1;

Line 3605: IF l_contact_contains_str IS NULL THEN

3601: END IF;
3602: IF l_party_site_contains_str IS NULL THEN
3603: defps := 1;
3604: END IF;
3605: IF l_contact_contains_str IS NULL THEN
3606: defct := 1;
3607: END IF;
3608: IF l_contact_pt_contains_str IS NULL THEN
3609: defcpt := 1;

Line 3608: IF l_contact_pt_contains_str IS NULL THEN

3604: END IF;
3605: IF l_contact_contains_str IS NULL THEN
3606: defct := 1;
3607: END IF;
3608: IF l_contact_pt_contains_str IS NULL THEN
3609: defcpt := 1;
3610: END IF;
3611:
3612: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3610: END IF;
3611:
3612: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3613: 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);
3614: 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);
3615: 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);
3616: 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);
3617: 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);
3618: 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 3615: 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);

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

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

3612: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3613: 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);
3614: 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);
3615: 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);
3616: 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);
3617: 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);
3618: 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);
3619: END IF;
3620: FOR I in 1..5 LOOP

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

3613: 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);
3614: 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);
3615: 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);
3616: 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);
3617: 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);
3618: 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);
3619: END IF;
3620: FOR I in 1..5 LOOP
3621: do_eval(I);

Line 3623: IF l_contact_contains_str IS NOT NULL THEN

3619: END IF;
3620: FOR I in 1..5 LOOP
3621: do_eval(I);
3622: END LOOP;
3623: IF l_contact_contains_str IS NOT NULL THEN
3624: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3625: END IF;
3626: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3627: 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 3624: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);

3620: FOR I in 1..5 LOOP
3621: do_eval(I);
3622: END LOOP;
3623: IF l_contact_contains_str IS NOT NULL THEN
3624: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3625: END IF;
3626: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3627: 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);
3628: END IF;

Line 4411: l_party_contains_str VARCHAR2(32000);

4407: x_num_matches OUT NUMBER
4408: ) IS
4409:
4410: -- Strings to hold the generated Intermedia query strings
4411: l_party_contains_str VARCHAR2(32000);
4412: l_match_idx NUMBER;
4413: l_party_site_contains_str VARCHAR2(32000);
4414: l_contact_contains_str VARCHAR2(32000);
4415: l_contact_pt_contains_str VARCHAR2(32000);

Line 4413: l_party_site_contains_str VARCHAR2(32000);

4409:
4410: -- Strings to hold the generated Intermedia query strings
4411: l_party_contains_str VARCHAR2(32000);
4412: l_match_idx NUMBER;
4413: l_party_site_contains_str VARCHAR2(32000);
4414: l_contact_contains_str VARCHAR2(32000);
4415: l_contact_pt_contains_str VARCHAR2(32000);
4416: l_tmp VARCHAR2(32000);
4417:

Line 4414: l_contact_contains_str VARCHAR2(32000);

4410: -- Strings to hold the generated Intermedia query strings
4411: l_party_contains_str VARCHAR2(32000);
4412: l_match_idx NUMBER;
4413: l_party_site_contains_str VARCHAR2(32000);
4414: l_contact_contains_str VARCHAR2(32000);
4415: l_contact_pt_contains_str VARCHAR2(32000);
4416: l_tmp VARCHAR2(32000);
4417:
4418: -- Other local variables

Line 4415: l_contact_pt_contains_str VARCHAR2(32000);

4411: l_party_contains_str VARCHAR2(32000);
4412: l_match_idx NUMBER;
4413: l_party_site_contains_str VARCHAR2(32000);
4414: l_contact_contains_str VARCHAR2(32000);
4415: l_contact_pt_contains_str VARCHAR2(32000);
4416: l_tmp VARCHAR2(32000);
4417:
4418: -- Other local variables
4419: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

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

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

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

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

4495: x_search_ctx_id := l_search_ctx_id;
4496:
4497: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4498: 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);
4499: 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);
4500: 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);
4501: 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);
4502: END IF;
4503: IF l_party_site_contains_str IS NULL THEN

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

4496:
4497: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4498: 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);
4499: 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);
4500: 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);
4501: 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);
4502: END IF;
4503: IF l_party_site_contains_str IS NULL THEN
4504: defps := 1;

Line 4503: IF l_party_site_contains_str IS NULL THEN

4499: 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);
4500: 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);
4501: 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);
4502: END IF;
4503: IF l_party_site_contains_str IS NULL THEN
4504: defps := 1;
4505: END IF;
4506: IF l_contact_pt_contains_str IS NULL THEN
4507: defcpt := 1;

Line 4506: IF l_contact_pt_contains_str IS NULL THEN

4502: END IF;
4503: IF l_party_site_contains_str IS NULL THEN
4504: defps := 1;
4505: END IF;
4506: IF l_contact_pt_contains_str IS NULL THEN
4507: defcpt := 1;
4508: END IF;
4509:
4510: IF l_party_site_contains_str IS NOT NULL THEN

Line 4510: IF l_party_site_contains_str IS NOT NULL THEN

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

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

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

Line 4534: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

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

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

Line 4644: l_party_contains_str VARCHAR2(32000);

4640: x_num_matches OUT NUMBER
4641: ) IS
4642:
4643: -- Strings to hold the generated Intermedia query strings
4644: l_party_contains_str VARCHAR2(32000);
4645: l_party_site_contains_str VARCHAR2(32000);
4646: l_contact_contains_str VARCHAR2(32000);
4647: l_contact_pt_contains_str VARCHAR2(32000);
4648: l_tmp VARCHAR2(32000);

Line 4645: l_party_site_contains_str VARCHAR2(32000);

4641: ) IS
4642:
4643: -- Strings to hold the generated Intermedia query strings
4644: l_party_contains_str VARCHAR2(32000);
4645: l_party_site_contains_str VARCHAR2(32000);
4646: l_contact_contains_str VARCHAR2(32000);
4647: l_contact_pt_contains_str VARCHAR2(32000);
4648: l_tmp VARCHAR2(32000);
4649:

Line 4646: l_contact_contains_str VARCHAR2(32000);

4642:
4643: -- Strings to hold the generated Intermedia query strings
4644: l_party_contains_str VARCHAR2(32000);
4645: l_party_site_contains_str VARCHAR2(32000);
4646: l_contact_contains_str VARCHAR2(32000);
4647: l_contact_pt_contains_str VARCHAR2(32000);
4648: l_tmp VARCHAR2(32000);
4649:
4650: -- Other local variables

Line 4647: l_contact_pt_contains_str VARCHAR2(32000);

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

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

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

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

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

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

4724: x_search_ctx_id := l_search_ctx_id;
4725:
4726: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4727: 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);
4728: 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);
4729: 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);
4730: 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);
4731: END IF;
4732: IF l_contact_contains_str IS NULL THEN

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

4725:
4726: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4727: 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);
4728: 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);
4729: 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);
4730: 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);
4731: END IF;
4732: IF l_contact_contains_str IS NULL THEN
4733: defct := 1;

Line 4732: IF l_contact_contains_str IS NULL THEN

4728: 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);
4729: 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);
4730: 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);
4731: END IF;
4732: IF l_contact_contains_str IS NULL THEN
4733: defct := 1;
4734: END IF;
4735: IF l_contact_pt_contains_str IS NULL THEN
4736: defcpt := 1;

Line 4735: IF l_contact_pt_contains_str IS NULL THEN

4731: END IF;
4732: IF l_contact_contains_str IS NULL THEN
4733: defct := 1;
4734: END IF;
4735: IF l_contact_pt_contains_str IS NULL THEN
4736: defcpt := 1;
4737: END IF;
4738:
4739: IF l_contact_contains_str IS NOT NULL THEN

Line 4739: IF l_contact_contains_str IS NOT NULL THEN

4735: IF l_contact_pt_contains_str IS NULL THEN
4736: defcpt := 1;
4737: END IF;
4738:
4739: IF l_contact_contains_str IS NOT NULL THEN
4740: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
4741: LOOP
4742: FETCH l_contact_cur INTO
4743: l_org_contact_id, l_ct_party_id ;

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

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

Line 4761: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

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

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

Line 4869: l_contact_pt_contains_str VARCHAR2(32000);

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

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

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

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

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

Line 4939: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

Line 5011: l_party_contains_str VARCHAR2(32000);

5007: x_search_ctx_id IN OUT NUMBER
5008: ) IS
5009:
5010: -- Strings to hold the generated Intermedia query strings
5011: l_party_contains_str VARCHAR2(32000);
5012: l_party_site_contains_str VARCHAR2(32000);
5013: l_contact_contains_str VARCHAR2(32000);
5014: l_contact_pt_contains_str VARCHAR2(32000);
5015: l_tmp VARCHAR2(32000);

Line 5012: l_party_site_contains_str VARCHAR2(32000);

5008: ) IS
5009:
5010: -- Strings to hold the generated Intermedia query strings
5011: l_party_contains_str VARCHAR2(32000);
5012: l_party_site_contains_str VARCHAR2(32000);
5013: l_contact_contains_str VARCHAR2(32000);
5014: l_contact_pt_contains_str VARCHAR2(32000);
5015: l_tmp VARCHAR2(32000);
5016:

Line 5013: l_contact_contains_str VARCHAR2(32000);

5009:
5010: -- Strings to hold the generated Intermedia query strings
5011: l_party_contains_str VARCHAR2(32000);
5012: l_party_site_contains_str VARCHAR2(32000);
5013: l_contact_contains_str VARCHAR2(32000);
5014: l_contact_pt_contains_str VARCHAR2(32000);
5015: l_tmp VARCHAR2(32000);
5016:
5017: -- Other local variables

Line 5014: l_contact_pt_contains_str VARCHAR2(32000);

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

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

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

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

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

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

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

Line 5101: IF l_party_site_contains_str IS NOT NULL THEN

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

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

5102: l_max_score := 0;
5103: l_max_id := 0;
5104: l_max_idx := 0;
5105: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5106: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5107: ELSE
5108: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5109: END IF;
5110: LOOP

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

5104: l_max_idx := 0;
5105: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5106: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5107: ELSE
5108: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5109: END IF;
5110: LOOP
5111: FETCH l_party_site_cur INTO
5112: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX3;

Line 5128: IF l_contact_contains_str IS NOT NULL THEN

5124: 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);
5125: END IF;
5126: END IF;
5127:
5128: IF l_contact_contains_str IS NOT NULL THEN
5129: l_max_score := 0;
5130: l_max_id := 0;
5131: l_max_idx := 0;
5132: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

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

5128: IF l_contact_contains_str IS NOT NULL THEN
5129: l_max_score := 0;
5130: l_max_id := 0;
5131: l_max_idx := 0;
5132: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5133: LOOP
5134: FETCH l_contact_cur INTO
5135: l_org_contact_id, l_ct_party_id ;
5136: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5150: IF l_contact_pt_contains_str IS NOT NULL THEN

5146: 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);
5147: END IF;
5148: END IF;
5149:
5150: IF l_contact_pt_contains_str IS NOT NULL THEN
5151: l_max_score := 0;
5152: l_max_id := 0;
5153: l_max_idx := 0;
5154: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN

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

5151: l_max_score := 0;
5152: l_max_id := 0;
5153: l_max_idx := 0;
5154: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5155: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5156: ELSE
5157: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5158: END IF;
5159: LOOP

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

5153: l_max_idx := 0;
5154: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5155: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5156: ELSE
5157: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5158: END IF;
5159: LOOP
5160: FETCH l_contact_pt_cur INTO
5161: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;