28: -- ==========================================================================================
29:
30: TYPE vlisttype IS TABLE of VARCHAR2(255) INDEX BY BINARY_INTEGER ;
31: call_order vlisttype;
32: call_max_score HZ_PARTY_SEARCH.IDList;
33: call_type vlisttype;
34: g_party_stage_rec HZ_PARTY_STAGE.party_stage_rec_type;
35: g_party_site_stage_list HZ_PARTY_STAGE.party_site_stage_list;
36: g_contact_stage_list HZ_PARTY_STAGE.contact_stage_list;
34: g_party_stage_rec HZ_PARTY_STAGE.party_stage_rec_type;
35: g_party_site_stage_list HZ_PARTY_STAGE.party_site_stage_list;
36: g_contact_stage_list HZ_PARTY_STAGE.contact_stage_list;
37: g_contact_pt_stage_list HZ_PARTY_STAGE.contact_pt_stage_list;
38: g_mappings HZ_PARTY_SEARCH.IDList;
39: g_max_id NUMBER:=2000000000;
40: g_other_party_level_attribs BOOLEAN;
41:
42: g_debug_count NUMBER := 0;
492: PROCEDURE INSERT_PARTY_SCORE (
493: p_party_id IN NUMBER
494: ,p_record_id IN NUMBER
495: ,p_search_ctx_id IN NUMBER
496: ,p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
497: ,p_stage_rec IN HZ_PARTY_STAGE.party_stage_rec_type
498: ,p_table_TX39 VARCHAR2
499: ,p_table_TX40 VARCHAR2
500: ,p_table_TX41 VARCHAR2
567: PROCEDURE INSERT_PARTY_SITES_SCORE (
568: p_party_id IN NUMBER
569: ,p_record_id IN NUMBER
570: ,p_search_ctx_id IN NUMBER
571: ,p_search_rec IN HZ_PARTY_SEARCH.party_site_search_rec_type
572: ,p_stage_rec IN HZ_PARTY_STAGE.party_site_stage_rec_type
573: ,p_table_TX3 VARCHAR2
574: ,p_table_TX4 VARCHAR2
575: ,p_table_TX9 VARCHAR2
708: PROCEDURE INSERT_CONTACTS_SCORE (
709: p_party_id IN NUMBER
710: ,p_record_id IN NUMBER
711: ,p_search_ctx_id IN NUMBER
712: ,p_search_rec IN HZ_PARTY_SEARCH.contact_search_rec_type
713: ,p_stage_rec IN HZ_PARTY_STAGE.contact_stage_rec_type
714: ,p_table_TX6 VARCHAR2
715: ,p_table_TX5 VARCHAR2
716: ,p_idx IN NUMBER) IS
757: PROCEDURE INSERT_CONTACT_POINTS_SCORE (
758: p_party_id IN NUMBER
759: ,p_record_id IN NUMBER
760: ,p_search_ctx_id IN NUMBER
761: ,p_search_rec IN HZ_PARTY_SEARCH.contact_point_search_rec_type
762: ,p_stage_rec IN HZ_PARTY_STAGE.contact_pt_stage_rec_type
763: ,p_table_TX1 VARCHAR2
764: ,p_table_TX5 VARCHAR2
765: ,p_table_TX6 VARCHAR2
861: for the PARTY Entity
862: ************************************************/
863:
864: PROCEDURE POP_PARTY_COND_REC(
865: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
866: ) IS
867: BEGIN
868: NULL ;
869: EXCEPTION
881: for the PARTY_SITES Entity
882: ************************************************/
883:
884: PROCEDURE POP_PARTY_SITES_COND_REC(
885: p_search_list IN HZ_PARTY_SEARCH.party_site_list
886: ) IS
887: BEGIN
888: NULL ;
889: EXCEPTION
901: for the CONTACTS Entity
902: ************************************************/
903:
904: PROCEDURE POP_CONTACTS_COND_REC(
905: p_search_list IN HZ_PARTY_SEARCH.contact_list
906: ) IS
907: BEGIN
908: NULL ;
909: EXCEPTION
921: for the CONTACT_POINTS Entity
922: ************************************************/
923:
924: PROCEDURE POP_CONTACT_POINTS_COND_REC(
925: p_search_list IN HZ_PARTY_SEARCH.contact_point_list
926: ) IS
927: BEGIN
928: NULL ;
929: EXCEPTION
935: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
936: END;
937:
938: PROCEDURE init_score_context (
939: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
940: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
941: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
942: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
943: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
936: END;
937:
938: PROCEDURE init_score_context (
939: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
940: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
941: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
942: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
943: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
944: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
937:
938: PROCEDURE init_score_context (
939: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
940: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
941: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
942: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
943: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
944: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
945: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
938: PROCEDURE init_score_context (
939: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
940: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
941: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
942: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
943: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
944: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
945: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
946: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
939: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
940: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
941: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
942: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
943: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
944: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
945: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
946: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
947: ) IS
940: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
941: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
942: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
943: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
944: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
945: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
946: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
947: ) IS
948: l_dummy NUMBER;
941: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
942: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
943: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
944: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
945: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
946: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
947: ) IS
948: l_dummy NUMBER;
949: BEGIN
942: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
943: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
944: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
945: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
946: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
947: ) IS
948: l_dummy NUMBER;
949: BEGIN
950: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
962: MAP_CONTACT_POINT_REC(FALSE,p_contact_point_list, l_dummy, g_contact_pt_stage_list);
963:
964: END;
965: FUNCTION init_search(
966: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
967: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
968: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
969: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
970: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
963:
964: END;
965: FUNCTION init_search(
966: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
967: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
968: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
969: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
970: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
971: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
964: END;
965: FUNCTION init_search(
966: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
967: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
968: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
969: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
970: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
971: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
972: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
965: FUNCTION init_search(
966: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
967: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
968: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
969: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
970: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
971: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
972: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
973: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
966: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
967: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
968: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
969: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
970: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
971: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
972: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
973: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
974: p_match_type IN VARCHAR2,
967: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
968: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
969: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
970: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
971: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
972: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
973: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
974: p_match_type IN VARCHAR2,
975: x_party_max_score OUT NUMBER,
968: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
969: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
970: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
971: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
972: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
973: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
974: p_match_type IN VARCHAR2,
975: x_party_max_score OUT NUMBER,
976: x_ps_max_score OUT NUMBER,
969: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
970: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
971: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
972: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
973: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
974: p_match_type IN VARCHAR2,
975: x_party_max_score OUT NUMBER,
976: x_ps_max_score OUT NUMBER,
977: x_contact_max_score OUT NUMBER,
981: l_ps_entered_max_score NUMBER:=0;
982: l_ct_entered_max_score NUMBER:=0;
983: l_cpt_entered_max_score NUMBER:=0;
984: vlist vlisttype;
985: maxscore HZ_PARTY_SEARCH.IDList;
986: l_name VARCHAR2(200);
987: l_idx NUMBER;
988: l_num NUMBER;
989: total NUMBER;
2431: p_party_id NUMBER,
2432: p_party_site_id NUMBER,
2433: p_org_contact_id NUMBER,
2434: p_contact_point_id NUMBER)
2435: RETURN HZ_PARTY_SEARCH.score_rec IS
2436: l_score_rec HZ_PARTY_SEARCH.score_rec;
2437: BEGIN
2438: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2439: 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);
2432: p_party_site_id NUMBER,
2433: p_org_contact_id NUMBER,
2434: p_contact_point_id NUMBER)
2435: RETURN HZ_PARTY_SEARCH.score_rec IS
2436: l_score_rec HZ_PARTY_SEARCH.score_rec;
2437: BEGIN
2438: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2439: 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);
2440: hz_utility_v2pub.debug(p_message=>'calling the function get_new_score_rec to set the l_score_rec structure',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2464: **********************************************************/
2465:
2466: PROCEDURE find_parties (
2467: p_rule_id IN NUMBER,
2468: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2469: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2470: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2471: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2472: p_restrict_sql IN VARCHAR2,
2465:
2466: PROCEDURE find_parties (
2467: p_rule_id IN NUMBER,
2468: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2469: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2470: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2471: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2472: p_restrict_sql IN VARCHAR2,
2473: p_match_type IN VARCHAR2,
2466: PROCEDURE find_parties (
2467: p_rule_id IN NUMBER,
2468: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2469: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2470: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2471: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2472: p_restrict_sql IN VARCHAR2,
2473: p_match_type IN VARCHAR2,
2474: p_search_merged IN VARCHAR2,
2467: p_rule_id IN NUMBER,
2468: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2469: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2470: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2471: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2472: p_restrict_sql IN VARCHAR2,
2473: p_match_type IN VARCHAR2,
2474: p_search_merged IN VARCHAR2,
2475: p_dup_party_id IN NUMBER,
2513: l_TX40 VARCHAR2(2000);
2514: l_TX8 VARCHAR2(2000);
2515: l_TX14 VARCHAR2(2000);
2516: l_TX6 VARCHAR2(2000);
2517: H_SCORES HZ_PARTY_SEARCH.score_list;
2518: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2519: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2520:
2521: l_score NUMBER;
2514: l_TX8 VARCHAR2(2000);
2515: l_TX14 VARCHAR2(2000);
2516: l_TX6 VARCHAR2(2000);
2517: H_SCORES HZ_PARTY_SEARCH.score_list;
2518: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2519: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2520:
2521: l_score NUMBER;
2522: l_idx NUMBER;
2515: l_TX14 VARCHAR2(2000);
2516: l_TX6 VARCHAR2(2000);
2517: H_SCORES HZ_PARTY_SEARCH.score_list;
2518: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2519: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2520:
2521: l_score NUMBER;
2522: l_idx NUMBER;
2523: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
2707: /** Private procedure to acquire and score at party site level ***/
2708: 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
2709: l_party_id_idx NUMBER:=1;
2710: l_ctx_id NUMBER;
2711: h_ps_id HZ_PARTY_SEARCH.IDList;
2712: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2713: h_ps_score HZ_PARTY_SEARCH.IDList;
2714: detcnt NUMBER := 1;
2715: BEGIN
2708: 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
2709: l_party_id_idx NUMBER:=1;
2710: l_ctx_id NUMBER;
2711: h_ps_id HZ_PARTY_SEARCH.IDList;
2712: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2713: h_ps_score HZ_PARTY_SEARCH.IDList;
2714: detcnt NUMBER := 1;
2715: BEGIN
2716: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2709: l_party_id_idx NUMBER:=1;
2710: l_ctx_id NUMBER;
2711: h_ps_id HZ_PARTY_SEARCH.IDList;
2712: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2713: h_ps_score HZ_PARTY_SEARCH.IDList;
2714: detcnt NUMBER := 1;
2715: BEGIN
2716: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2717: 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);
2829: /** Private procedure to acquire and score at contact point level ***/
2830: 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
2831: l_party_id_idx NUMBER:=1;
2832: l_ctx_id NUMBER;
2833: h_ct_id HZ_PARTY_SEARCH.IDList;
2834: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2835: h_ct_score HZ_PARTY_SEARCH.IDList;
2836: detcnt NUMBER := 1;
2837: BEGIN
2830: 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
2831: l_party_id_idx NUMBER:=1;
2832: l_ctx_id NUMBER;
2833: h_ct_id HZ_PARTY_SEARCH.IDList;
2834: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2835: h_ct_score HZ_PARTY_SEARCH.IDList;
2836: detcnt NUMBER := 1;
2837: BEGIN
2838: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2831: l_party_id_idx NUMBER:=1;
2832: l_ctx_id NUMBER;
2833: h_ct_id HZ_PARTY_SEARCH.IDList;
2834: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2835: h_ct_score HZ_PARTY_SEARCH.IDList;
2836: detcnt NUMBER := 1;
2837: BEGIN
2838: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2839: 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);
2938: /** Private procedure to acquire and score at contact point level ***/
2939: 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
2940: l_party_id_idx NUMBER:=1;
2941: l_ctx_id NUMBER;
2942: h_cpt_id HZ_PARTY_SEARCH.IDList;
2943: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2944: h_cpt_score HZ_PARTY_SEARCH.IDList;
2945: detcnt NUMBER := 1;
2946: l_cpt_flag VARCHAR2(1) := 'N';
2939: 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
2940: l_party_id_idx NUMBER:=1;
2941: l_ctx_id NUMBER;
2942: h_cpt_id HZ_PARTY_SEARCH.IDList;
2943: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2944: h_cpt_score HZ_PARTY_SEARCH.IDList;
2945: detcnt NUMBER := 1;
2946: l_cpt_flag VARCHAR2(1) := 'N';
2947: is_a_match VARCHAR2(1) := 'Y';
2940: l_party_id_idx NUMBER:=1;
2941: l_ctx_id NUMBER;
2942: h_cpt_id HZ_PARTY_SEARCH.IDList;
2943: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2944: h_cpt_score HZ_PARTY_SEARCH.IDList;
2945: detcnt NUMBER := 1;
2946: l_cpt_flag VARCHAR2(1) := 'N';
2947: is_a_match VARCHAR2(1) := 'Y';
2948: BEGIN
3427: END find_parties;
3428:
3429: PROCEDURE find_persons (
3430: p_rule_id IN NUMBER,
3431: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3432: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3433: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3434: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3435: p_restrict_sql IN VARCHAR2,
3428:
3429: PROCEDURE find_persons (
3430: p_rule_id IN NUMBER,
3431: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3432: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3433: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3434: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3435: p_restrict_sql IN VARCHAR2,
3436: p_match_type IN VARCHAR2,
3429: PROCEDURE find_persons (
3430: p_rule_id IN NUMBER,
3431: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3432: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3433: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3434: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3435: p_restrict_sql IN VARCHAR2,
3436: p_match_type IN VARCHAR2,
3437: p_search_merged IN VARCHAR2,
3430: p_rule_id IN NUMBER,
3431: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3432: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3433: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3434: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3435: p_restrict_sql IN VARCHAR2,
3436: p_match_type IN VARCHAR2,
3437: p_search_merged IN VARCHAR2,
3438: p_dup_party_id IN NUMBER,
3476: l_TX40 VARCHAR2(2000);
3477: l_TX8 VARCHAR2(2000);
3478: l_TX14 VARCHAR2(2000);
3479: l_TX6 VARCHAR2(2000);
3480: H_SCORES HZ_PARTY_SEARCH.score_list;
3481: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3482: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3483:
3484: l_score NUMBER;
3477: l_TX8 VARCHAR2(2000);
3478: l_TX14 VARCHAR2(2000);
3479: l_TX6 VARCHAR2(2000);
3480: H_SCORES HZ_PARTY_SEARCH.score_list;
3481: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3482: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3483:
3484: l_score NUMBER;
3485: l_idx NUMBER;
3478: l_TX14 VARCHAR2(2000);
3479: l_TX6 VARCHAR2(2000);
3480: H_SCORES HZ_PARTY_SEARCH.score_list;
3481: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3482: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3483:
3484: l_score NUMBER;
3485: l_idx NUMBER;
3486: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
3718: /** Private procedure to acquire and score at party site level ***/
3719: 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
3720: l_party_id_idx NUMBER:=1;
3721: l_ctx_id NUMBER;
3722: h_ps_id HZ_PARTY_SEARCH.IDList;
3723: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3724: h_ps_score HZ_PARTY_SEARCH.IDList;
3725: detcnt NUMBER := 1;
3726: l_person_id NUMBER;
3719: 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
3720: l_party_id_idx NUMBER:=1;
3721: l_ctx_id NUMBER;
3722: h_ps_id HZ_PARTY_SEARCH.IDList;
3723: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3724: h_ps_score HZ_PARTY_SEARCH.IDList;
3725: detcnt NUMBER := 1;
3726: l_person_id NUMBER;
3727: BEGIN
3720: l_party_id_idx NUMBER:=1;
3721: l_ctx_id NUMBER;
3722: h_ps_id HZ_PARTY_SEARCH.IDList;
3723: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3724: h_ps_score HZ_PARTY_SEARCH.IDList;
3725: detcnt NUMBER := 1;
3726: l_person_id NUMBER;
3727: BEGIN
3728: SAVEPOINT eval_start;
3817: /** Private procedure to acquire and score at party site level ***/
3818: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3819: l_party_id_idx NUMBER:=1;
3820: l_ctx_id NUMBER;
3821: h_ct_id HZ_PARTY_SEARCH.IDList;
3822: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3823: h_ct_score HZ_PARTY_SEARCH.IDList;
3824: detcnt NUMBER := 1;
3825: l_person_id NUMBER;
3818: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3819: l_party_id_idx NUMBER:=1;
3820: l_ctx_id NUMBER;
3821: h_ct_id HZ_PARTY_SEARCH.IDList;
3822: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3823: h_ct_score HZ_PARTY_SEARCH.IDList;
3824: detcnt NUMBER := 1;
3825: l_person_id NUMBER;
3826: BEGIN
3819: l_party_id_idx NUMBER:=1;
3820: l_ctx_id NUMBER;
3821: h_ct_id HZ_PARTY_SEARCH.IDList;
3822: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3823: h_ct_score HZ_PARTY_SEARCH.IDList;
3824: detcnt NUMBER := 1;
3825: l_person_id NUMBER;
3826: BEGIN
3827: SAVEPOINT eval_start;
3875: /** Private procedure to acquire and score at contact point level ***/
3876: 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
3877: l_party_id_idx NUMBER:=1;
3878: l_ctx_id NUMBER;
3879: h_cpt_id HZ_PARTY_SEARCH.IDList;
3880: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3881: h_cpt_score HZ_PARTY_SEARCH.IDList;
3882: detcnt NUMBER := 1;
3883: l_person_id NUMBER;
3876: 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
3877: l_party_id_idx NUMBER:=1;
3878: l_ctx_id NUMBER;
3879: h_cpt_id HZ_PARTY_SEARCH.IDList;
3880: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3881: h_cpt_score HZ_PARTY_SEARCH.IDList;
3882: detcnt NUMBER := 1;
3883: l_person_id NUMBER;
3884: is_a_match VARCHAR2(1) := 'Y';
3877: l_party_id_idx NUMBER:=1;
3878: l_ctx_id NUMBER;
3879: h_cpt_id HZ_PARTY_SEARCH.IDList;
3880: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3881: h_cpt_score HZ_PARTY_SEARCH.IDList;
3882: detcnt NUMBER := 1;
3883: l_person_id NUMBER;
3884: is_a_match VARCHAR2(1) := 'Y';
3885: l_cpt_flag VARCHAR2(1) := 'N';
4288: END find_persons;
4289:
4290: PROCEDURE find_persons (
4291: p_rule_id IN NUMBER,
4292: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4293: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4294: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4295: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4296: p_restrict_sql IN VARCHAR2,
4289:
4290: PROCEDURE find_persons (
4291: p_rule_id IN NUMBER,
4292: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4293: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4294: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4295: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4296: p_restrict_sql IN VARCHAR2,
4297: p_match_type IN VARCHAR2,
4290: PROCEDURE find_persons (
4291: p_rule_id IN NUMBER,
4292: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4293: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4294: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4295: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4296: p_restrict_sql IN VARCHAR2,
4297: p_match_type IN VARCHAR2,
4298: p_search_merged IN VARCHAR2,
4291: p_rule_id IN NUMBER,
4292: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4293: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4294: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4295: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4296: p_restrict_sql IN VARCHAR2,
4297: p_match_type IN VARCHAR2,
4298: p_search_merged IN VARCHAR2,
4299: p_ins_details IN VARCHAR2,
4305: find_persons(p_rule_id,p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list,p_restrict_sql,p_match_type,p_search_merged,null,null,null,p_ins_details,x_search_ctx_id,x_num_matches);
4306: END;
4307: PROCEDURE find_party_details (
4308: p_rule_id IN NUMBER,
4309: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4310: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4311: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4312: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4313: p_restrict_sql IN VARCHAR2,
4306: END;
4307: PROCEDURE find_party_details (
4308: p_rule_id IN NUMBER,
4309: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4310: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4311: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4312: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4313: p_restrict_sql IN VARCHAR2,
4314: p_match_type IN VARCHAR2,
4307: PROCEDURE find_party_details (
4308: p_rule_id IN NUMBER,
4309: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4310: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4311: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4312: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4313: p_restrict_sql IN VARCHAR2,
4314: p_match_type IN VARCHAR2,
4315: p_search_merged IN VARCHAR2,
4308: p_rule_id IN NUMBER,
4309: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4310: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4311: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4312: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4313: p_restrict_sql IN VARCHAR2,
4314: p_match_type IN VARCHAR2,
4315: p_search_merged IN VARCHAR2,
4316: x_search_ctx_id OUT NUMBER,
4365: x_dup_set_id OUT NUMBER,
4366: x_search_ctx_id OUT NUMBER,
4367: x_num_matches OUT NUMBER
4368: ) IS
4369: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4370: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4371: l_contact_list HZ_PARTY_SEARCH.contact_list;
4372: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4373: l_match_idx NUMBER;
4366: x_search_ctx_id OUT NUMBER,
4367: x_num_matches OUT NUMBER
4368: ) IS
4369: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4370: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4371: l_contact_list HZ_PARTY_SEARCH.contact_list;
4372: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4373: l_match_idx NUMBER;
4374:
4367: x_num_matches OUT NUMBER
4368: ) IS
4369: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4370: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4371: l_contact_list HZ_PARTY_SEARCH.contact_list;
4372: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4373: l_match_idx NUMBER;
4374:
4375: --Fix for bug 4417124
4368: ) IS
4369: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4370: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4371: l_contact_list HZ_PARTY_SEARCH.contact_list;
4372: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4373: l_match_idx NUMBER;
4374:
4375: --Fix for bug 4417124
4376: l_use_contact_addr_info BOOLEAN := TRUE;
4481: x_search_ctx_id OUT NUMBER,
4482: x_num_matches OUT NUMBER
4483: ) IS
4484:
4485: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4486: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4487: l_contact_list HZ_PARTY_SEARCH.contact_list;
4488: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4489: contact_point_ids HZ_PARTY_SEARCH.IDList;
4482: x_num_matches OUT NUMBER
4483: ) IS
4484:
4485: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4486: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4487: l_contact_list HZ_PARTY_SEARCH.contact_list;
4488: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4489: contact_point_ids HZ_PARTY_SEARCH.IDList;
4490: p_party_site_list HZ_PARTY_SEARCH.IDList;
4483: ) IS
4484:
4485: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4486: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4487: l_contact_list HZ_PARTY_SEARCH.contact_list;
4488: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4489: contact_point_ids HZ_PARTY_SEARCH.IDList;
4490: p_party_site_list HZ_PARTY_SEARCH.IDList;
4491: p_contact_ids HZ_PARTY_SEARCH.IDList;
4484:
4485: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4486: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4487: l_contact_list HZ_PARTY_SEARCH.contact_list;
4488: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4489: contact_point_ids HZ_PARTY_SEARCH.IDList;
4490: p_party_site_list HZ_PARTY_SEARCH.IDList;
4491: p_contact_ids HZ_PARTY_SEARCH.IDList;
4492: l_match_idx NUMBER;
4485: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4486: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4487: l_contact_list HZ_PARTY_SEARCH.contact_list;
4488: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4489: contact_point_ids HZ_PARTY_SEARCH.IDList;
4490: p_party_site_list HZ_PARTY_SEARCH.IDList;
4491: p_contact_ids HZ_PARTY_SEARCH.IDList;
4492: l_match_idx NUMBER;
4493: cursor get_cpts_for_party_sites is select contact_point_id
4486: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4487: l_contact_list HZ_PARTY_SEARCH.contact_list;
4488: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4489: contact_point_ids HZ_PARTY_SEARCH.IDList;
4490: p_party_site_list HZ_PARTY_SEARCH.IDList;
4491: p_contact_ids HZ_PARTY_SEARCH.IDList;
4492: l_match_idx NUMBER;
4493: cursor get_cpts_for_party_sites is select contact_point_id
4494: from hz_contact_points
4487: l_contact_list HZ_PARTY_SEARCH.contact_list;
4488: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4489: contact_point_ids HZ_PARTY_SEARCH.IDList;
4490: p_party_site_list HZ_PARTY_SEARCH.IDList;
4491: p_contact_ids HZ_PARTY_SEARCH.IDList;
4492: l_match_idx NUMBER;
4493: cursor get_cpts_for_party_sites is select contact_point_id
4494: from hz_contact_points
4495: where owner_table_name = 'HZ_PARTY_SITES'
4516:
4517: get_search_criteria (
4518: null,
4519: p_party_site_list,
4520: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4521: contact_point_ids,
4522: l_party_search_rec,
4523: l_party_site_list,
4524: l_contact_list,
4566: x_search_ctx_id OUT NUMBER,
4567: x_num_matches OUT NUMBER
4568: ) IS
4569:
4570: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4571: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4572: l_contact_list HZ_PARTY_SEARCH.contact_list;
4573: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4574: contact_point_ids HZ_PARTY_SEARCH.IDList;
4567: x_num_matches OUT NUMBER
4568: ) IS
4569:
4570: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4571: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4572: l_contact_list HZ_PARTY_SEARCH.contact_list;
4573: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4574: contact_point_ids HZ_PARTY_SEARCH.IDList;
4575: p_party_site_list HZ_PARTY_SEARCH.IDList;
4568: ) IS
4569:
4570: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4571: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4572: l_contact_list HZ_PARTY_SEARCH.contact_list;
4573: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4574: contact_point_ids HZ_PARTY_SEARCH.IDList;
4575: p_party_site_list HZ_PARTY_SEARCH.IDList;
4576: p_contact_ids HZ_PARTY_SEARCH.IDList;
4569:
4570: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4571: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4572: l_contact_list HZ_PARTY_SEARCH.contact_list;
4573: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4574: contact_point_ids HZ_PARTY_SEARCH.IDList;
4575: p_party_site_list HZ_PARTY_SEARCH.IDList;
4576: p_contact_ids HZ_PARTY_SEARCH.IDList;
4577: l_match_idx NUMBER;
4570: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4571: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4572: l_contact_list HZ_PARTY_SEARCH.contact_list;
4573: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4574: contact_point_ids HZ_PARTY_SEARCH.IDList;
4575: p_party_site_list HZ_PARTY_SEARCH.IDList;
4576: p_contact_ids HZ_PARTY_SEARCH.IDList;
4577: l_match_idx NUMBER;
4578: cursor get_cpt_for_contact_id is select contact_point_id
4571: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4572: l_contact_list HZ_PARTY_SEARCH.contact_list;
4573: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4574: contact_point_ids HZ_PARTY_SEARCH.IDList;
4575: p_party_site_list HZ_PARTY_SEARCH.IDList;
4576: p_contact_ids HZ_PARTY_SEARCH.IDList;
4577: l_match_idx NUMBER;
4578: cursor get_cpt_for_contact_id is select contact_point_id
4579: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4572: l_contact_list HZ_PARTY_SEARCH.contact_list;
4573: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4574: contact_point_ids HZ_PARTY_SEARCH.IDList;
4575: p_party_site_list HZ_PARTY_SEARCH.IDList;
4576: p_contact_ids HZ_PARTY_SEARCH.IDList;
4577: l_match_idx NUMBER;
4578: cursor get_cpt_for_contact_id is select contact_point_id
4579: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4580: where a.party_relationship_id = b.relationship_id
4603: CLOSE get_cpt_for_contact_id;
4604:
4605: get_search_criteria (
4606: null,
4607: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4608: p_contact_ids,
4609: contact_point_ids,
4610: l_party_search_rec,
4611: l_party_site_list,
4654: p_match_type IN VARCHAR2,
4655: x_search_ctx_id OUT NUMBER,
4656: x_num_matches OUT NUMBER
4657: ) IS
4658: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4659: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4660: l_contact_list HZ_PARTY_SEARCH.contact_list;
4661: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4662: contact_point_ids HZ_PARTY_SEARCH.IDList;
4655: x_search_ctx_id OUT NUMBER,
4656: x_num_matches OUT NUMBER
4657: ) IS
4658: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4659: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4660: l_contact_list HZ_PARTY_SEARCH.contact_list;
4661: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4662: contact_point_ids HZ_PARTY_SEARCH.IDList;
4663: p_party_site_list HZ_PARTY_SEARCH.IDList;
4656: x_num_matches OUT NUMBER
4657: ) IS
4658: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4659: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4660: l_contact_list HZ_PARTY_SEARCH.contact_list;
4661: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4662: contact_point_ids HZ_PARTY_SEARCH.IDList;
4663: p_party_site_list HZ_PARTY_SEARCH.IDList;
4664: p_contact_ids HZ_PARTY_SEARCH.IDList;
4657: ) IS
4658: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4659: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4660: l_contact_list HZ_PARTY_SEARCH.contact_list;
4661: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4662: contact_point_ids HZ_PARTY_SEARCH.IDList;
4663: p_party_site_list HZ_PARTY_SEARCH.IDList;
4664: p_contact_ids HZ_PARTY_SEARCH.IDList;
4665: l_match_idx NUMBER;
4658: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4659: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4660: l_contact_list HZ_PARTY_SEARCH.contact_list;
4661: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4662: contact_point_ids HZ_PARTY_SEARCH.IDList;
4663: p_party_site_list HZ_PARTY_SEARCH.IDList;
4664: p_contact_ids HZ_PARTY_SEARCH.IDList;
4665: l_match_idx NUMBER;
4666:
4659: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4660: l_contact_list HZ_PARTY_SEARCH.contact_list;
4661: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4662: contact_point_ids HZ_PARTY_SEARCH.IDList;
4663: p_party_site_list HZ_PARTY_SEARCH.IDList;
4664: p_contact_ids HZ_PARTY_SEARCH.IDList;
4665: l_match_idx NUMBER;
4666:
4667: BEGIN
4660: l_contact_list HZ_PARTY_SEARCH.contact_list;
4661: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4662: contact_point_ids HZ_PARTY_SEARCH.IDList;
4663: p_party_site_list HZ_PARTY_SEARCH.IDList;
4664: p_contact_ids HZ_PARTY_SEARCH.IDList;
4665: l_match_idx NUMBER;
4666:
4667: BEGIN
4668: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4677: END IF;
4678: contact_point_ids(1) := p_contact_point_id;
4679: get_search_criteria (
4680: null,
4681: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4682: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4683: contact_point_ids,
4684: l_party_search_rec,
4685: l_party_site_list,
4678: contact_point_ids(1) := p_contact_point_id;
4679: get_search_criteria (
4680: null,
4681: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4682: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4683: contact_point_ids,
4684: l_party_search_rec,
4685: l_party_site_list,
4686: l_contact_list,
4831: ) IS
4832: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4833: l_match_idx NUMBER;
4834: AttrVals AttrList;
4835: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4836: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4837: l_contact_list HZ_PARTY_SEARCH.contact_list;
4838: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4839: l_dup_set_id NUMBER;
4832: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4833: l_match_idx NUMBER;
4834: AttrVals AttrList;
4835: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4836: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4837: l_contact_list HZ_PARTY_SEARCH.contact_list;
4838: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4839: l_dup_set_id NUMBER;
4840: l_idx NUMBER;
4833: l_match_idx NUMBER;
4834: AttrVals AttrList;
4835: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4836: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4837: l_contact_list HZ_PARTY_SEARCH.contact_list;
4838: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4839: l_dup_set_id NUMBER;
4840: l_idx NUMBER;
4841: l_cpt_type VARCHAR2(255);
4834: AttrVals AttrList;
4835: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4836: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4837: l_contact_list HZ_PARTY_SEARCH.contact_list;
4838: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4839: l_dup_set_id NUMBER;
4840: l_idx NUMBER;
4841: l_cpt_type VARCHAR2(255);
4842: FIRST BOOLEAN := TRUE;
5075:
5076: PROCEDURE get_matching_party_sites (
5077: p_rule_id IN NUMBER,
5078: p_party_id IN NUMBER,
5079: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
5080: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5081: p_restrict_sql IN VARCHAR2,
5082: p_match_type IN VARCHAR2,
5083: p_dup_party_site_id IN NUMBER,
5076: PROCEDURE get_matching_party_sites (
5077: p_rule_id IN NUMBER,
5078: p_party_id IN NUMBER,
5079: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
5080: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5081: p_restrict_sql IN VARCHAR2,
5082: p_match_type IN VARCHAR2,
5083: p_dup_party_site_id IN NUMBER,
5084: x_search_ctx_id OUT NUMBER,
5112: l_TX5 VARCHAR2(2000);
5113: l_TX8 VARCHAR2(2000);
5114: l_TX14 VARCHAR2(2000);
5115: l_TX6 VARCHAR2(2000);
5116: H_SCORES HZ_PARTY_SEARCH.score_list;
5117:
5118: l_score NUMBER;
5119: l_idx NUMBER;
5120: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
5143: defps NUMBER :=0;
5144: defct NUMBER :=0;
5145: defcpt NUMBER :=0;
5146: l_index NUMBER;
5147: l_match_ps_list HZ_PARTY_SEARCH.IDList;
5148: l_cnt NUMBER:=1;
5149:
5150:
5151: BEGIN
5167: l_match_str := ' AND ';
5168: ELSIF p_match_type = 'OR' THEN
5169: l_match_str := ' OR ';
5170: END IF;
5171: l_entered_max_score:= init_search( HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC, p_party_site_list, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST, p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5172: g_score_until_thresh := false;
5173: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5174:
5175: -- ************************************************************
5175: -- ************************************************************
5176: -- STEP 2. Setup of intermedia query strings for Acquisition query
5177: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5178: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5179: 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);
5180:
5181: -- Setup Search Context ID
5182: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5183: x_search_ctx_id := l_search_ctx_id;
5318:
5319: PROCEDURE get_matching_contacts (
5320: p_rule_id IN NUMBER,
5321: p_party_id IN NUMBER,
5322: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
5323: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5324: p_restrict_sql IN VARCHAR2,
5325: p_match_type IN VARCHAR2,
5326: p_dup_contact_id IN NUMBER,
5319: PROCEDURE get_matching_contacts (
5320: p_rule_id IN NUMBER,
5321: p_party_id IN NUMBER,
5322: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
5323: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5324: p_restrict_sql IN VARCHAR2,
5325: p_match_type IN VARCHAR2,
5326: p_dup_contact_id IN NUMBER,
5327: x_search_ctx_id OUT NUMBER,
5348: l_TX1 VARCHAR2(2000);
5349: l_TX5 VARCHAR2(2000);
5350: l_TX8 VARCHAR2(2000);
5351: l_TX6 VARCHAR2(2000);
5352: H_SCORES HZ_PARTY_SEARCH.score_list;
5353:
5354: l_score NUMBER;
5355: l_idx NUMBER;
5356: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
5376: l_party_max_score NUMBER;
5377: l_ps_max_score NUMBER;
5378: l_contact_max_score NUMBER;
5379: l_cpt_max_score NUMBER;
5380: l_match_contact_list HZ_PARTY_SEARCH.IDList;
5381: l_cnt NUMBER:=1;
5382:
5383:
5384: BEGIN
5400: l_match_str := ' AND ';
5401: ELSIF p_match_type = 'OR' THEN
5402: l_match_str := ' OR ';
5403: END IF;
5404: l_entered_max_score:= init_search( HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC, HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, p_contact_list, p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5405: g_score_until_thresh := false;
5406: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5407:
5408: -- ************************************************************
5408: -- ************************************************************
5409: -- STEP 2. Setup of intermedia query strings for Acquisition query
5410: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5411: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5412: 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);
5413:
5414: -- Setup Search Context ID
5415: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5416: x_search_ctx_id := l_search_ctx_id;
5547:
5548: PROCEDURE get_matching_contact_points (
5549: p_rule_id IN NUMBER,
5550: p_party_id IN NUMBER,
5551: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5552: p_restrict_sql IN VARCHAR2,
5553: p_match_type IN VARCHAR2,
5554: p_dup_contact_point_id IN NUMBER,
5555: x_search_ctx_id OUT NUMBER,
5580: l_cpt_ps_id NUMBER;
5581: l_cpt_contact_id NUMBER;
5582: l_contact_pt_id NUMBER;
5583: l_cpt_type VARCHAR2(100);
5584: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5585: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5586: H_SCORE HZ_PARTY_SEARCH.IDList;
5587:
5588: cnt NUMBER :=0;
5581: l_cpt_contact_id NUMBER;
5582: l_contact_pt_id NUMBER;
5583: l_cpt_type VARCHAR2(100);
5584: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5585: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5586: H_SCORE HZ_PARTY_SEARCH.IDList;
5587:
5588: cnt NUMBER :=0;
5589: l_party_max_score NUMBER;
5582: l_contact_pt_id NUMBER;
5583: l_cpt_type VARCHAR2(100);
5584: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5585: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5586: H_SCORE HZ_PARTY_SEARCH.IDList;
5587:
5588: cnt NUMBER :=0;
5589: l_party_max_score NUMBER;
5590: l_ps_max_score NUMBER;
5611: l_match_str := ' AND ';
5612: ELSIF p_match_type = 'OR' THEN
5613: l_match_str := ' OR ';
5614: END IF;
5615: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
5616: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
5617: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5618: g_score_until_thresh := false;
5619: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5612: ELSIF p_match_type = 'OR' THEN
5613: l_match_str := ' OR ';
5614: END IF;
5615: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
5616: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
5617: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5618: g_score_until_thresh := false;
5619: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5620:
5620:
5621: -- ************************************************************
5622: -- STEP 2. Setup of intermedia query strings for Acquisition query
5623: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5624: 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);
5625:
5626: -- Setup Search Context ID
5627: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5628: x_search_ctx_id := l_search_ctx_id;
5695:
5696: PROCEDURE get_score_details (
5697: p_rule_id IN NUMBER,
5698: p_party_id IN NUMBER,
5699: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5700: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5701: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5702: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5703: x_search_ctx_id IN OUT NUMBER
5696: PROCEDURE get_score_details (
5697: p_rule_id IN NUMBER,
5698: p_party_id IN NUMBER,
5699: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5700: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5701: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5702: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5703: x_search_ctx_id IN OUT NUMBER
5704: ) IS
5697: p_rule_id IN NUMBER,
5698: p_party_id IN NUMBER,
5699: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5700: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5701: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5702: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5703: x_search_ctx_id IN OUT NUMBER
5704: ) IS
5705:
5698: p_party_id IN NUMBER,
5699: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5700: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5701: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5702: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5703: x_search_ctx_id IN OUT NUMBER
5704: ) IS
5705:
5706: -- Strings to hold the generated Intermedia query strings
5745: l_TX14 VARCHAR2(2000);
5746: l_max_TX14 VARCHAR2(2000);
5747: l_TX6 VARCHAR2(2000);
5748: l_max_TX6 VARCHAR2(2000);
5749: H_SCORES HZ_PARTY_SEARCH.score_list;
5750:
5751: l_score NUMBER;
5752: l_match_idx NUMBER;
5753: l_idx NUMBER;
5924: ************************************************/
5925:
5926: PROCEDURE get_party_for_search (
5927: p_party_id IN NUMBER,
5928: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5929: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5930: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5931: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5932: ) IS
5925:
5926: PROCEDURE get_party_for_search (
5927: p_party_id IN NUMBER,
5928: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5929: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5930: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5931: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5932: ) IS
5933: l_party_id NUMBER;
5926: PROCEDURE get_party_for_search (
5927: p_party_id IN NUMBER,
5928: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5929: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5930: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5931: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5932: ) IS
5933: l_party_id NUMBER;
5934: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5927: p_party_id IN NUMBER,
5928: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5929: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5930: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5931: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5932: ) IS
5933: l_party_id NUMBER;
5934: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5935: l_contact_ids HZ_PARTY_SEARCH.IDList;
5930: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5931: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5932: ) IS
5933: l_party_id NUMBER;
5934: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5935: l_contact_ids HZ_PARTY_SEARCH.IDList;
5936: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5937: ps NUMBER :=1;
5938: cpt NUMBER :=1;
5931: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5932: ) IS
5933: l_party_id NUMBER;
5934: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5935: l_contact_ids HZ_PARTY_SEARCH.IDList;
5936: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5937: ps NUMBER :=1;
5938: cpt NUMBER :=1;
5939: ct NUMBER :=1;
5932: ) IS
5933: l_party_id NUMBER;
5934: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5935: l_contact_ids HZ_PARTY_SEARCH.IDList;
5936: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5937: ps NUMBER :=1;
5938: cpt NUMBER :=1;
5939: ct NUMBER :=1;
5940: l_use_contact_info varchar2(1);
6041: END get_party_for_search;
6042:
6043: PROCEDURE get_search_criteria (
6044: p_party_id IN NUMBER,
6045: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
6046: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6047: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
6048: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
6049: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
6042:
6043: PROCEDURE get_search_criteria (
6044: p_party_id IN NUMBER,
6045: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
6046: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6047: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
6048: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
6049: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
6050: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
6043: PROCEDURE get_search_criteria (
6044: p_party_id IN NUMBER,
6045: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
6046: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6047: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
6048: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
6049: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
6050: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
6051: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6044: p_party_id IN NUMBER,
6045: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
6046: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6047: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
6048: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
6049: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
6050: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
6051: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6052: ) IS
6045: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
6046: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6047: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
6048: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
6049: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
6050: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
6051: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6052: ) IS
6053: BEGIN
6046: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6047: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
6048: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
6049: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
6050: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
6051: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6052: ) IS
6053: BEGIN
6054:
6047: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
6048: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
6049: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
6050: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
6051: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6052: ) IS
6053: BEGIN
6054:
6055: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6099: ************************************************/
6100:
6101: PROCEDURE get_party_rec (
6102: p_party_id IN NUMBER,
6103: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type
6104: ) IS
6105: l_party_type VARCHAR2(255);
6106: BEGIN
6107:
6149: the party site search record structure
6150: ************************************************/
6151:
6152: PROCEDURE get_party_site_rec (
6153: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
6154: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
6155: ) IS
6156: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
6157: SELECT party_site_id
6150: ************************************************/
6151:
6152: PROCEDURE get_party_site_rec (
6153: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
6154: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
6155: ) IS
6156: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
6157: SELECT party_site_id
6158: ,translate(HZ_LOCATIONS.CITY, '%',' ')
6209: the contact search record structure
6210: ************************************************/
6211:
6212: PROCEDURE get_contact_rec (
6213: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6214: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
6215: ) IS
6216: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
6217: SELECT org_contact_id
6210: ************************************************/
6211:
6212: PROCEDURE get_contact_rec (
6213: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6214: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
6215: ) IS
6216: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
6217: SELECT org_contact_id
6218: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
6267: the contact point search record structure
6268: ************************************************/
6269:
6270: PROCEDURE get_contact_point_rec (
6271: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
6272: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6273: ) IS
6274:
6275: -- Cursor to fetch primary contact points for party
6268: ************************************************/
6269:
6270: PROCEDURE get_contact_point_rec (
6271: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
6272: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6273: ) IS
6274:
6275: -- Cursor to fetch primary contact points for party
6276: CURSOR c_cpts(cp_contact_point_id NUMBER) IS
6328: ************************************************/
6329:
6330: PROCEDURE MAP_PARTY_REC(
6331: p_search_ctx IN BOOLEAN,
6332: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6333: x_entered_max_score OUT NUMBER,
6334: x_stage_rec IN OUT NOCOPY HZ_PARTY_STAGE.party_stage_rec_type
6335: ) IS
6336: tmp VARCHAR2(4000);
6447: ************************************************/
6448:
6449: PROCEDURE MAP_PARTY_SITE_REC(
6450: p_search_ctx IN BOOLEAN,
6451: p_search_list IN HZ_PARTY_SEARCH.party_site_list,
6452: x_entered_max_score OUT NUMBER,
6453: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.party_site_stage_list
6454: ) IS
6455: l_current_max_score NUMBER;
6643: ************************************************/
6644:
6645: PROCEDURE MAP_CONTACT_REC(
6646: p_search_ctx IN BOOLEAN,
6647: p_search_list IN HZ_PARTY_SEARCH.contact_list,
6648: x_entered_max_score OUT NUMBER,
6649: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_stage_list
6650: ) IS
6651: l_current_max_score NUMBER;
6733: ************************************************/
6734:
6735: PROCEDURE MAP_CONTACT_POINT_REC(
6736: p_search_ctx IN BOOLEAN,
6737: p_search_list IN HZ_PARTY_SEARCH.contact_point_list,
6738: x_entered_max_score OUT NUMBER,
6739: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_pt_stage_list
6740: ) IS
6741: l_current_max_score NUMBER;
6889: 2. Contact Point Type is not null for each condition
6890: ************************************************/
6891:
6892: FUNCTION check_prim_cond(
6893: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6894: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6895: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6896: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6897: RETURN BOOLEAN IS
6890: ************************************************/
6891:
6892: FUNCTION check_prim_cond(
6893: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6894: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6895: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6896: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6897: RETURN BOOLEAN IS
6898: BEGIN
6891:
6892: FUNCTION check_prim_cond(
6893: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6894: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6895: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6896: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6897: RETURN BOOLEAN IS
6898: BEGIN
6899: FOR I IN 1..p_contact_point_list.COUNT LOOP
6892: FUNCTION check_prim_cond(
6893: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6894: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6895: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6896: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6897: RETURN BOOLEAN IS
6898: BEGIN
6899: FOR I IN 1..p_contact_point_list.COUNT LOOP
6900: IF p_contact_point_list(I).CONTACT_POINT_TYPE IS NULL AND (
6971: has party site criteria.
6972: ************************************************/
6973:
6974: PROCEDURE check_party_site_cond(
6975: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6976: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6977: x_secondary OUT BOOLEAN,
6978: x_primary OUT BOOLEAN
6979: ) IS
6972: ************************************************/
6973:
6974: PROCEDURE check_party_site_cond(
6975: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6976: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6977: x_secondary OUT BOOLEAN,
6978: x_primary OUT BOOLEAN
6979: ) IS
6980: BEGIN
7033: has contact criteria.
7034: ************************************************/
7035:
7036: PROCEDURE check_contact_cond(
7037: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
7038: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
7039: x_secondary OUT BOOLEAN,
7040: x_primary OUT BOOLEAN
7041: ) IS
7034: ************************************************/
7035:
7036: PROCEDURE check_contact_cond(
7037: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
7038: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
7039: x_secondary OUT BOOLEAN,
7040: x_primary OUT BOOLEAN
7041: ) IS
7042: BEGIN
7091: has valid contact point criteria.
7092: ************************************************/
7093:
7094: PROCEDURE check_contact_point_cond(
7095: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
7096: x_secondary OUT BOOLEAN,
7097: x_primary OUT BOOLEAN
7098: ) IS
7099: BEGIN