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;
2429: p_party_id NUMBER,
2430: p_party_site_id NUMBER,
2431: p_org_contact_id NUMBER,
2432: p_contact_point_id NUMBER)
2433: RETURN HZ_PARTY_SEARCH.score_rec IS
2434: l_score_rec HZ_PARTY_SEARCH.score_rec;
2435: BEGIN
2436: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2437: 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);
2430: p_party_site_id NUMBER,
2431: p_org_contact_id NUMBER,
2432: p_contact_point_id NUMBER)
2433: RETURN HZ_PARTY_SEARCH.score_rec IS
2434: l_score_rec HZ_PARTY_SEARCH.score_rec;
2435: BEGIN
2436: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2437: 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);
2438: 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);
2462: **********************************************************/
2463:
2464: PROCEDURE find_parties (
2465: p_rule_id IN NUMBER,
2466: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2467: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2468: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2469: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2470: p_restrict_sql IN VARCHAR2,
2463:
2464: PROCEDURE find_parties (
2465: p_rule_id IN NUMBER,
2466: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2467: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2468: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2469: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2470: p_restrict_sql IN VARCHAR2,
2471: p_match_type IN VARCHAR2,
2464: PROCEDURE find_parties (
2465: p_rule_id IN NUMBER,
2466: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2467: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2468: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2469: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2470: p_restrict_sql IN VARCHAR2,
2471: p_match_type IN VARCHAR2,
2472: p_search_merged IN VARCHAR2,
2465: p_rule_id IN NUMBER,
2466: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2467: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2468: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2469: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2470: p_restrict_sql IN VARCHAR2,
2471: p_match_type IN VARCHAR2,
2472: p_search_merged IN VARCHAR2,
2473: p_dup_party_id IN NUMBER,
2511: l_TX40 VARCHAR2(2000);
2512: l_TX8 VARCHAR2(2000);
2513: l_TX14 VARCHAR2(2000);
2514: l_TX6 VARCHAR2(2000);
2515: H_SCORES HZ_PARTY_SEARCH.score_list;
2516: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2517: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2518:
2519: l_score NUMBER;
2512: l_TX8 VARCHAR2(2000);
2513: l_TX14 VARCHAR2(2000);
2514: l_TX6 VARCHAR2(2000);
2515: H_SCORES HZ_PARTY_SEARCH.score_list;
2516: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2517: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2518:
2519: l_score NUMBER;
2520: l_idx NUMBER;
2513: l_TX14 VARCHAR2(2000);
2514: l_TX6 VARCHAR2(2000);
2515: H_SCORES HZ_PARTY_SEARCH.score_list;
2516: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2517: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2518:
2519: l_score NUMBER;
2520: l_idx NUMBER;
2521: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
2704: /** Private procedure to acquire and score at party site level ***/
2705: 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
2706: l_party_id_idx NUMBER:=1;
2707: l_ctx_id NUMBER;
2708: h_ps_id HZ_PARTY_SEARCH.IDList;
2709: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2710: h_ps_score HZ_PARTY_SEARCH.IDList;
2711: detcnt NUMBER := 1;
2712: BEGIN
2705: 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
2706: l_party_id_idx NUMBER:=1;
2707: l_ctx_id NUMBER;
2708: h_ps_id HZ_PARTY_SEARCH.IDList;
2709: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2710: h_ps_score HZ_PARTY_SEARCH.IDList;
2711: detcnt NUMBER := 1;
2712: BEGIN
2713: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2706: l_party_id_idx NUMBER:=1;
2707: l_ctx_id NUMBER;
2708: h_ps_id HZ_PARTY_SEARCH.IDList;
2709: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2710: h_ps_score HZ_PARTY_SEARCH.IDList;
2711: detcnt NUMBER := 1;
2712: BEGIN
2713: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2714: 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);
2826: /** Private procedure to acquire and score at contact point level ***/
2827: 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
2828: l_party_id_idx NUMBER:=1;
2829: l_ctx_id NUMBER;
2830: h_ct_id HZ_PARTY_SEARCH.IDList;
2831: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2832: h_ct_score HZ_PARTY_SEARCH.IDList;
2833: detcnt NUMBER := 1;
2834: BEGIN
2827: 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
2828: l_party_id_idx NUMBER:=1;
2829: l_ctx_id NUMBER;
2830: h_ct_id HZ_PARTY_SEARCH.IDList;
2831: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2832: h_ct_score HZ_PARTY_SEARCH.IDList;
2833: detcnt NUMBER := 1;
2834: BEGIN
2835: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2828: l_party_id_idx NUMBER:=1;
2829: l_ctx_id NUMBER;
2830: h_ct_id HZ_PARTY_SEARCH.IDList;
2831: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2832: h_ct_score HZ_PARTY_SEARCH.IDList;
2833: detcnt NUMBER := 1;
2834: BEGIN
2835: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2836: 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);
2935: /** Private procedure to acquire and score at contact point level ***/
2936: 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
2937: l_party_id_idx NUMBER:=1;
2938: l_ctx_id NUMBER;
2939: h_cpt_id HZ_PARTY_SEARCH.IDList;
2940: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2941: h_cpt_score HZ_PARTY_SEARCH.IDList;
2942: detcnt NUMBER := 1;
2943: l_cpt_flag VARCHAR2(1) := 'N';
2936: 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
2937: l_party_id_idx NUMBER:=1;
2938: l_ctx_id NUMBER;
2939: h_cpt_id HZ_PARTY_SEARCH.IDList;
2940: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2941: h_cpt_score HZ_PARTY_SEARCH.IDList;
2942: detcnt NUMBER := 1;
2943: l_cpt_flag VARCHAR2(1) := 'N';
2944: is_a_match VARCHAR2(1) := 'Y';
2937: l_party_id_idx NUMBER:=1;
2938: l_ctx_id NUMBER;
2939: h_cpt_id HZ_PARTY_SEARCH.IDList;
2940: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2941: h_cpt_score HZ_PARTY_SEARCH.IDList;
2942: detcnt NUMBER := 1;
2943: l_cpt_flag VARCHAR2(1) := 'N';
2944: is_a_match VARCHAR2(1) := 'Y';
2945: BEGIN
3424: END find_parties;
3425:
3426: PROCEDURE find_persons (
3427: p_rule_id IN NUMBER,
3428: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3429: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3430: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3431: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3432: p_restrict_sql IN VARCHAR2,
3425:
3426: PROCEDURE find_persons (
3427: p_rule_id IN NUMBER,
3428: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3429: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3430: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3431: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3432: p_restrict_sql IN VARCHAR2,
3433: p_match_type IN VARCHAR2,
3426: PROCEDURE find_persons (
3427: p_rule_id IN NUMBER,
3428: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3429: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3430: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3431: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3432: p_restrict_sql IN VARCHAR2,
3433: p_match_type IN VARCHAR2,
3434: p_search_merged IN VARCHAR2,
3427: p_rule_id IN NUMBER,
3428: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3429: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3430: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3431: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3432: p_restrict_sql IN VARCHAR2,
3433: p_match_type IN VARCHAR2,
3434: p_search_merged IN VARCHAR2,
3435: p_dup_party_id IN NUMBER,
3473: l_TX40 VARCHAR2(2000);
3474: l_TX8 VARCHAR2(2000);
3475: l_TX14 VARCHAR2(2000);
3476: l_TX6 VARCHAR2(2000);
3477: H_SCORES HZ_PARTY_SEARCH.score_list;
3478: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3479: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3480:
3481: l_score NUMBER;
3474: l_TX8 VARCHAR2(2000);
3475: l_TX14 VARCHAR2(2000);
3476: l_TX6 VARCHAR2(2000);
3477: H_SCORES HZ_PARTY_SEARCH.score_list;
3478: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3479: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3480:
3481: l_score NUMBER;
3482: l_idx NUMBER;
3475: l_TX14 VARCHAR2(2000);
3476: l_TX6 VARCHAR2(2000);
3477: H_SCORES HZ_PARTY_SEARCH.score_list;
3478: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3479: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3480:
3481: l_score NUMBER;
3482: l_idx NUMBER;
3483: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
3714: /** Private procedure to acquire and score at party site level ***/
3715: 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
3716: l_party_id_idx NUMBER:=1;
3717: l_ctx_id NUMBER;
3718: h_ps_id HZ_PARTY_SEARCH.IDList;
3719: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3720: h_ps_score HZ_PARTY_SEARCH.IDList;
3721: detcnt NUMBER := 1;
3722: l_person_id NUMBER;
3715: 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
3716: l_party_id_idx NUMBER:=1;
3717: l_ctx_id NUMBER;
3718: h_ps_id HZ_PARTY_SEARCH.IDList;
3719: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3720: h_ps_score HZ_PARTY_SEARCH.IDList;
3721: detcnt NUMBER := 1;
3722: l_person_id NUMBER;
3723: BEGIN
3716: l_party_id_idx NUMBER:=1;
3717: l_ctx_id NUMBER;
3718: h_ps_id HZ_PARTY_SEARCH.IDList;
3719: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3720: h_ps_score HZ_PARTY_SEARCH.IDList;
3721: detcnt NUMBER := 1;
3722: l_person_id NUMBER;
3723: BEGIN
3724: SAVEPOINT eval_start;
3813: /** Private procedure to acquire and score at party site level ***/
3814: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3815: l_party_id_idx NUMBER:=1;
3816: l_ctx_id NUMBER;
3817: h_ct_id HZ_PARTY_SEARCH.IDList;
3818: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3819: h_ct_score HZ_PARTY_SEARCH.IDList;
3820: detcnt NUMBER := 1;
3821: l_person_id NUMBER;
3814: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3815: l_party_id_idx NUMBER:=1;
3816: l_ctx_id NUMBER;
3817: h_ct_id HZ_PARTY_SEARCH.IDList;
3818: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3819: h_ct_score HZ_PARTY_SEARCH.IDList;
3820: detcnt NUMBER := 1;
3821: l_person_id NUMBER;
3822: BEGIN
3815: l_party_id_idx NUMBER:=1;
3816: l_ctx_id NUMBER;
3817: h_ct_id HZ_PARTY_SEARCH.IDList;
3818: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3819: h_ct_score HZ_PARTY_SEARCH.IDList;
3820: detcnt NUMBER := 1;
3821: l_person_id NUMBER;
3822: BEGIN
3823: SAVEPOINT eval_start;
3871: /** Private procedure to acquire and score at contact point level ***/
3872: 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
3873: l_party_id_idx NUMBER:=1;
3874: l_ctx_id NUMBER;
3875: h_cpt_id HZ_PARTY_SEARCH.IDList;
3876: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3877: h_cpt_score HZ_PARTY_SEARCH.IDList;
3878: detcnt NUMBER := 1;
3879: l_person_id NUMBER;
3872: 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
3873: l_party_id_idx NUMBER:=1;
3874: l_ctx_id NUMBER;
3875: h_cpt_id HZ_PARTY_SEARCH.IDList;
3876: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3877: h_cpt_score HZ_PARTY_SEARCH.IDList;
3878: detcnt NUMBER := 1;
3879: l_person_id NUMBER;
3880: is_a_match VARCHAR2(1) := 'Y';
3873: l_party_id_idx NUMBER:=1;
3874: l_ctx_id NUMBER;
3875: h_cpt_id HZ_PARTY_SEARCH.IDList;
3876: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3877: h_cpt_score HZ_PARTY_SEARCH.IDList;
3878: detcnt NUMBER := 1;
3879: l_person_id NUMBER;
3880: is_a_match VARCHAR2(1) := 'Y';
3881: l_cpt_flag VARCHAR2(1) := 'N';
4284: END find_persons;
4285:
4286: PROCEDURE find_persons (
4287: p_rule_id IN NUMBER,
4288: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4289: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4290: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4291: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4292: p_restrict_sql IN VARCHAR2,
4285:
4286: PROCEDURE find_persons (
4287: p_rule_id IN NUMBER,
4288: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4289: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4290: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4291: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4292: p_restrict_sql IN VARCHAR2,
4293: p_match_type IN VARCHAR2,
4286: PROCEDURE find_persons (
4287: p_rule_id IN NUMBER,
4288: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4289: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4290: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4291: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4292: p_restrict_sql IN VARCHAR2,
4293: p_match_type IN VARCHAR2,
4294: p_search_merged IN VARCHAR2,
4287: p_rule_id IN NUMBER,
4288: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4289: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4290: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4291: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4292: p_restrict_sql IN VARCHAR2,
4293: p_match_type IN VARCHAR2,
4294: p_search_merged IN VARCHAR2,
4295: p_ins_details IN VARCHAR2,
4301: 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);
4302: END;
4303: PROCEDURE find_party_details (
4304: p_rule_id IN NUMBER,
4305: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4306: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4307: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4308: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4309: p_restrict_sql IN VARCHAR2,
4302: END;
4303: PROCEDURE find_party_details (
4304: p_rule_id IN NUMBER,
4305: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4306: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4307: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4308: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4309: p_restrict_sql IN VARCHAR2,
4310: p_match_type IN VARCHAR2,
4303: PROCEDURE find_party_details (
4304: p_rule_id IN NUMBER,
4305: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4306: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4307: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4308: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4309: p_restrict_sql IN VARCHAR2,
4310: p_match_type IN VARCHAR2,
4311: p_search_merged IN VARCHAR2,
4304: p_rule_id IN NUMBER,
4305: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4306: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4307: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4308: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4309: p_restrict_sql IN VARCHAR2,
4310: p_match_type IN VARCHAR2,
4311: p_search_merged IN VARCHAR2,
4312: x_search_ctx_id OUT NUMBER,
4361: x_dup_set_id OUT NUMBER,
4362: x_search_ctx_id OUT NUMBER,
4363: x_num_matches OUT NUMBER
4364: ) IS
4365: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4366: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4367: l_contact_list HZ_PARTY_SEARCH.contact_list;
4368: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4369: l_match_idx NUMBER;
4362: x_search_ctx_id OUT NUMBER,
4363: x_num_matches OUT NUMBER
4364: ) IS
4365: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4366: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4367: l_contact_list HZ_PARTY_SEARCH.contact_list;
4368: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4369: l_match_idx NUMBER;
4370:
4363: x_num_matches OUT NUMBER
4364: ) IS
4365: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4366: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4367: l_contact_list HZ_PARTY_SEARCH.contact_list;
4368: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4369: l_match_idx NUMBER;
4370:
4371: --Fix for bug 4417124
4364: ) IS
4365: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4366: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4367: l_contact_list HZ_PARTY_SEARCH.contact_list;
4368: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4369: l_match_idx NUMBER;
4370:
4371: --Fix for bug 4417124
4372: l_use_contact_addr_info BOOLEAN := TRUE;
4477: x_search_ctx_id OUT NUMBER,
4478: x_num_matches OUT NUMBER
4479: ) IS
4480:
4481: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4482: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4483: l_contact_list HZ_PARTY_SEARCH.contact_list;
4484: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4485: contact_point_ids HZ_PARTY_SEARCH.IDList;
4478: x_num_matches OUT NUMBER
4479: ) IS
4480:
4481: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4482: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4483: l_contact_list HZ_PARTY_SEARCH.contact_list;
4484: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4485: contact_point_ids HZ_PARTY_SEARCH.IDList;
4486: p_party_site_list HZ_PARTY_SEARCH.IDList;
4479: ) IS
4480:
4481: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4482: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4483: l_contact_list HZ_PARTY_SEARCH.contact_list;
4484: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4485: contact_point_ids HZ_PARTY_SEARCH.IDList;
4486: p_party_site_list HZ_PARTY_SEARCH.IDList;
4487: p_contact_ids HZ_PARTY_SEARCH.IDList;
4480:
4481: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4482: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4483: l_contact_list HZ_PARTY_SEARCH.contact_list;
4484: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4485: contact_point_ids HZ_PARTY_SEARCH.IDList;
4486: p_party_site_list HZ_PARTY_SEARCH.IDList;
4487: p_contact_ids HZ_PARTY_SEARCH.IDList;
4488: l_match_idx NUMBER;
4481: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4482: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4483: l_contact_list HZ_PARTY_SEARCH.contact_list;
4484: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4485: contact_point_ids HZ_PARTY_SEARCH.IDList;
4486: p_party_site_list HZ_PARTY_SEARCH.IDList;
4487: p_contact_ids HZ_PARTY_SEARCH.IDList;
4488: l_match_idx NUMBER;
4489: cursor get_cpts_for_party_sites is select contact_point_id
4482: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4483: l_contact_list HZ_PARTY_SEARCH.contact_list;
4484: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4485: contact_point_ids HZ_PARTY_SEARCH.IDList;
4486: p_party_site_list HZ_PARTY_SEARCH.IDList;
4487: p_contact_ids HZ_PARTY_SEARCH.IDList;
4488: l_match_idx NUMBER;
4489: cursor get_cpts_for_party_sites is select contact_point_id
4490: from hz_contact_points
4483: l_contact_list HZ_PARTY_SEARCH.contact_list;
4484: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4485: contact_point_ids HZ_PARTY_SEARCH.IDList;
4486: p_party_site_list HZ_PARTY_SEARCH.IDList;
4487: p_contact_ids HZ_PARTY_SEARCH.IDList;
4488: l_match_idx NUMBER;
4489: cursor get_cpts_for_party_sites is select contact_point_id
4490: from hz_contact_points
4491: where owner_table_name = 'HZ_PARTY_SITES'
4512:
4513: get_search_criteria (
4514: null,
4515: p_party_site_list,
4516: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4517: contact_point_ids,
4518: l_party_search_rec,
4519: l_party_site_list,
4520: l_contact_list,
4562: x_search_ctx_id OUT NUMBER,
4563: x_num_matches OUT NUMBER
4564: ) IS
4565:
4566: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4567: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4568: l_contact_list HZ_PARTY_SEARCH.contact_list;
4569: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4570: contact_point_ids HZ_PARTY_SEARCH.IDList;
4563: x_num_matches OUT NUMBER
4564: ) IS
4565:
4566: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4567: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4568: l_contact_list HZ_PARTY_SEARCH.contact_list;
4569: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4570: contact_point_ids HZ_PARTY_SEARCH.IDList;
4571: p_party_site_list HZ_PARTY_SEARCH.IDList;
4564: ) IS
4565:
4566: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4567: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4568: l_contact_list HZ_PARTY_SEARCH.contact_list;
4569: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4570: contact_point_ids HZ_PARTY_SEARCH.IDList;
4571: p_party_site_list HZ_PARTY_SEARCH.IDList;
4572: p_contact_ids HZ_PARTY_SEARCH.IDList;
4565:
4566: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4567: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4568: l_contact_list HZ_PARTY_SEARCH.contact_list;
4569: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4570: contact_point_ids HZ_PARTY_SEARCH.IDList;
4571: p_party_site_list HZ_PARTY_SEARCH.IDList;
4572: p_contact_ids HZ_PARTY_SEARCH.IDList;
4573: l_match_idx NUMBER;
4566: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4567: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4568: l_contact_list HZ_PARTY_SEARCH.contact_list;
4569: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4570: contact_point_ids HZ_PARTY_SEARCH.IDList;
4571: p_party_site_list HZ_PARTY_SEARCH.IDList;
4572: p_contact_ids HZ_PARTY_SEARCH.IDList;
4573: l_match_idx NUMBER;
4574: cursor get_cpt_for_contact_id is select contact_point_id
4567: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4568: l_contact_list HZ_PARTY_SEARCH.contact_list;
4569: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4570: contact_point_ids HZ_PARTY_SEARCH.IDList;
4571: p_party_site_list HZ_PARTY_SEARCH.IDList;
4572: p_contact_ids HZ_PARTY_SEARCH.IDList;
4573: l_match_idx NUMBER;
4574: cursor get_cpt_for_contact_id is select contact_point_id
4575: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4568: l_contact_list HZ_PARTY_SEARCH.contact_list;
4569: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4570: contact_point_ids HZ_PARTY_SEARCH.IDList;
4571: p_party_site_list HZ_PARTY_SEARCH.IDList;
4572: p_contact_ids HZ_PARTY_SEARCH.IDList;
4573: l_match_idx NUMBER;
4574: cursor get_cpt_for_contact_id is select contact_point_id
4575: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4576: where a.party_relationship_id = b.relationship_id
4599: CLOSE get_cpt_for_contact_id;
4600:
4601: get_search_criteria (
4602: null,
4603: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4604: p_contact_ids,
4605: contact_point_ids,
4606: l_party_search_rec,
4607: l_party_site_list,
4650: p_match_type IN VARCHAR2,
4651: x_search_ctx_id OUT NUMBER,
4652: x_num_matches OUT NUMBER
4653: ) IS
4654: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4655: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4656: l_contact_list HZ_PARTY_SEARCH.contact_list;
4657: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4658: contact_point_ids HZ_PARTY_SEARCH.IDList;
4651: x_search_ctx_id OUT NUMBER,
4652: x_num_matches OUT NUMBER
4653: ) IS
4654: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4655: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4656: l_contact_list HZ_PARTY_SEARCH.contact_list;
4657: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4658: contact_point_ids HZ_PARTY_SEARCH.IDList;
4659: p_party_site_list HZ_PARTY_SEARCH.IDList;
4652: x_num_matches OUT NUMBER
4653: ) IS
4654: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4655: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4656: l_contact_list HZ_PARTY_SEARCH.contact_list;
4657: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4658: contact_point_ids HZ_PARTY_SEARCH.IDList;
4659: p_party_site_list HZ_PARTY_SEARCH.IDList;
4660: p_contact_ids HZ_PARTY_SEARCH.IDList;
4653: ) IS
4654: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4655: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4656: l_contact_list HZ_PARTY_SEARCH.contact_list;
4657: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4658: contact_point_ids HZ_PARTY_SEARCH.IDList;
4659: p_party_site_list HZ_PARTY_SEARCH.IDList;
4660: p_contact_ids HZ_PARTY_SEARCH.IDList;
4661: l_match_idx NUMBER;
4654: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4655: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4656: l_contact_list HZ_PARTY_SEARCH.contact_list;
4657: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4658: contact_point_ids HZ_PARTY_SEARCH.IDList;
4659: p_party_site_list HZ_PARTY_SEARCH.IDList;
4660: p_contact_ids HZ_PARTY_SEARCH.IDList;
4661: l_match_idx NUMBER;
4662:
4655: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4656: l_contact_list HZ_PARTY_SEARCH.contact_list;
4657: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4658: contact_point_ids HZ_PARTY_SEARCH.IDList;
4659: p_party_site_list HZ_PARTY_SEARCH.IDList;
4660: p_contact_ids HZ_PARTY_SEARCH.IDList;
4661: l_match_idx NUMBER;
4662:
4663: BEGIN
4656: l_contact_list HZ_PARTY_SEARCH.contact_list;
4657: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4658: contact_point_ids HZ_PARTY_SEARCH.IDList;
4659: p_party_site_list HZ_PARTY_SEARCH.IDList;
4660: p_contact_ids HZ_PARTY_SEARCH.IDList;
4661: l_match_idx NUMBER;
4662:
4663: BEGIN
4664: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4673: END IF;
4674: contact_point_ids(1) := p_contact_point_id;
4675: get_search_criteria (
4676: null,
4677: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4678: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4679: contact_point_ids,
4680: l_party_search_rec,
4681: l_party_site_list,
4674: contact_point_ids(1) := p_contact_point_id;
4675: get_search_criteria (
4676: null,
4677: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4678: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4679: contact_point_ids,
4680: l_party_search_rec,
4681: l_party_site_list,
4682: l_contact_list,
4827: ) IS
4828: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4829: l_match_idx NUMBER;
4830: AttrVals AttrList;
4831: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4832: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4833: l_contact_list HZ_PARTY_SEARCH.contact_list;
4834: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4835: l_dup_set_id NUMBER;
4828: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4829: l_match_idx NUMBER;
4830: AttrVals AttrList;
4831: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4832: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4833: l_contact_list HZ_PARTY_SEARCH.contact_list;
4834: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4835: l_dup_set_id NUMBER;
4836: l_idx NUMBER;
4829: l_match_idx NUMBER;
4830: AttrVals AttrList;
4831: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4832: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4833: l_contact_list HZ_PARTY_SEARCH.contact_list;
4834: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4835: l_dup_set_id NUMBER;
4836: l_idx NUMBER;
4837: l_cpt_type VARCHAR2(255);
4830: AttrVals AttrList;
4831: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4832: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4833: l_contact_list HZ_PARTY_SEARCH.contact_list;
4834: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4835: l_dup_set_id NUMBER;
4836: l_idx NUMBER;
4837: l_cpt_type VARCHAR2(255);
4838: FIRST BOOLEAN := TRUE;
5071:
5072: PROCEDURE get_matching_party_sites (
5073: p_rule_id IN NUMBER,
5074: p_party_id IN NUMBER,
5075: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
5076: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5077: p_restrict_sql IN VARCHAR2,
5078: p_match_type IN VARCHAR2,
5079: p_dup_party_site_id IN NUMBER,
5072: PROCEDURE get_matching_party_sites (
5073: p_rule_id IN NUMBER,
5074: p_party_id IN NUMBER,
5075: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
5076: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5077: p_restrict_sql IN VARCHAR2,
5078: p_match_type IN VARCHAR2,
5079: p_dup_party_site_id IN NUMBER,
5080: x_search_ctx_id OUT NUMBER,
5108: l_TX5 VARCHAR2(2000);
5109: l_TX8 VARCHAR2(2000);
5110: l_TX14 VARCHAR2(2000);
5111: l_TX6 VARCHAR2(2000);
5112: H_SCORES HZ_PARTY_SEARCH.score_list;
5113:
5114: l_score NUMBER;
5115: l_idx NUMBER;
5116: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
5139: defps NUMBER :=0;
5140: defct NUMBER :=0;
5141: defcpt NUMBER :=0;
5142: l_index NUMBER;
5143: l_match_ps_list HZ_PARTY_SEARCH.IDList;
5144: l_cnt NUMBER:=1;
5145:
5146:
5147: BEGIN
5163: l_match_str := ' AND ';
5164: ELSIF p_match_type = 'OR' THEN
5165: l_match_str := ' OR ';
5166: END IF;
5167: 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);
5168: g_score_until_thresh := false;
5169: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5170:
5171: -- ************************************************************
5171: -- ************************************************************
5172: -- STEP 2. Setup of intermedia query strings for Acquisition query
5173: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5174: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5175: 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);
5176:
5177: -- Setup Search Context ID
5178: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5179: x_search_ctx_id := l_search_ctx_id;
5314:
5315: PROCEDURE get_matching_contacts (
5316: p_rule_id IN NUMBER,
5317: p_party_id IN NUMBER,
5318: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
5319: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5320: p_restrict_sql IN VARCHAR2,
5321: p_match_type IN VARCHAR2,
5322: p_dup_contact_id IN NUMBER,
5315: PROCEDURE get_matching_contacts (
5316: p_rule_id IN NUMBER,
5317: p_party_id IN NUMBER,
5318: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
5319: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5320: p_restrict_sql IN VARCHAR2,
5321: p_match_type IN VARCHAR2,
5322: p_dup_contact_id IN NUMBER,
5323: x_search_ctx_id OUT NUMBER,
5344: l_TX1 VARCHAR2(2000);
5345: l_TX5 VARCHAR2(2000);
5346: l_TX8 VARCHAR2(2000);
5347: l_TX6 VARCHAR2(2000);
5348: H_SCORES HZ_PARTY_SEARCH.score_list;
5349:
5350: l_score NUMBER;
5351: l_idx NUMBER;
5352: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
5372: l_party_max_score NUMBER;
5373: l_ps_max_score NUMBER;
5374: l_contact_max_score NUMBER;
5375: l_cpt_max_score NUMBER;
5376: l_match_contact_list HZ_PARTY_SEARCH.IDList;
5377: l_cnt NUMBER:=1;
5378:
5379:
5380: BEGIN
5396: l_match_str := ' AND ';
5397: ELSIF p_match_type = 'OR' THEN
5398: l_match_str := ' OR ';
5399: END IF;
5400: 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);
5401: g_score_until_thresh := false;
5402: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5403:
5404: -- ************************************************************
5404: -- ************************************************************
5405: -- STEP 2. Setup of intermedia query strings for Acquisition query
5406: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5407: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5408: 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);
5409:
5410: -- Setup Search Context ID
5411: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5412: x_search_ctx_id := l_search_ctx_id;
5543:
5544: PROCEDURE get_matching_contact_points (
5545: p_rule_id IN NUMBER,
5546: p_party_id IN NUMBER,
5547: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5548: p_restrict_sql IN VARCHAR2,
5549: p_match_type IN VARCHAR2,
5550: p_dup_contact_point_id IN NUMBER,
5551: x_search_ctx_id OUT NUMBER,
5576: l_cpt_ps_id NUMBER;
5577: l_cpt_contact_id NUMBER;
5578: l_contact_pt_id NUMBER;
5579: l_cpt_type VARCHAR2(100);
5580: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5581: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5582: H_SCORE HZ_PARTY_SEARCH.IDList;
5583:
5584: cnt NUMBER :=0;
5577: l_cpt_contact_id NUMBER;
5578: l_contact_pt_id NUMBER;
5579: l_cpt_type VARCHAR2(100);
5580: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5581: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5582: H_SCORE HZ_PARTY_SEARCH.IDList;
5583:
5584: cnt NUMBER :=0;
5585: l_party_max_score NUMBER;
5578: l_contact_pt_id NUMBER;
5579: l_cpt_type VARCHAR2(100);
5580: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5581: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5582: H_SCORE HZ_PARTY_SEARCH.IDList;
5583:
5584: cnt NUMBER :=0;
5585: l_party_max_score NUMBER;
5586: l_ps_max_score NUMBER;
5607: l_match_str := ' AND ';
5608: ELSIF p_match_type = 'OR' THEN
5609: l_match_str := ' OR ';
5610: END IF;
5611: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
5612: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
5613: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5614: g_score_until_thresh := false;
5615: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5608: ELSIF p_match_type = 'OR' THEN
5609: l_match_str := ' OR ';
5610: END IF;
5611: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
5612: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
5613: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5614: g_score_until_thresh := false;
5615: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5616:
5616:
5617: -- ************************************************************
5618: -- STEP 2. Setup of intermedia query strings for Acquisition query
5619: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5620: 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);
5621:
5622: -- Setup Search Context ID
5623: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5624: x_search_ctx_id := l_search_ctx_id;
5691:
5692: PROCEDURE get_score_details (
5693: p_rule_id IN NUMBER,
5694: p_party_id IN NUMBER,
5695: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5696: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5697: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5698: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5699: x_search_ctx_id IN OUT NUMBER
5692: PROCEDURE get_score_details (
5693: p_rule_id IN NUMBER,
5694: p_party_id IN NUMBER,
5695: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5696: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5697: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5698: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5699: x_search_ctx_id IN OUT NUMBER
5700: ) IS
5693: p_rule_id IN NUMBER,
5694: p_party_id IN NUMBER,
5695: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5696: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5697: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5698: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5699: x_search_ctx_id IN OUT NUMBER
5700: ) IS
5701:
5694: p_party_id IN NUMBER,
5695: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5696: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5697: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5698: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5699: x_search_ctx_id IN OUT NUMBER
5700: ) IS
5701:
5702: -- Strings to hold the generated Intermedia query strings
5741: l_TX14 VARCHAR2(2000);
5742: l_max_TX14 VARCHAR2(2000);
5743: l_TX6 VARCHAR2(2000);
5744: l_max_TX6 VARCHAR2(2000);
5745: H_SCORES HZ_PARTY_SEARCH.score_list;
5746:
5747: l_score NUMBER;
5748: l_match_idx NUMBER;
5749: l_idx NUMBER;
5919: ************************************************/
5920:
5921: PROCEDURE get_party_for_search (
5922: p_party_id IN NUMBER,
5923: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5924: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5925: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5926: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5927: ) IS
5920:
5921: PROCEDURE get_party_for_search (
5922: p_party_id IN NUMBER,
5923: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5924: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5925: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5926: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5927: ) IS
5928: l_party_id NUMBER;
5921: PROCEDURE get_party_for_search (
5922: p_party_id IN NUMBER,
5923: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5924: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5925: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5926: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5927: ) IS
5928: l_party_id NUMBER;
5929: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5922: p_party_id IN NUMBER,
5923: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5924: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5925: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5926: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5927: ) IS
5928: l_party_id NUMBER;
5929: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5930: l_contact_ids HZ_PARTY_SEARCH.IDList;
5925: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5926: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5927: ) IS
5928: l_party_id NUMBER;
5929: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5930: l_contact_ids HZ_PARTY_SEARCH.IDList;
5931: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5932: ps NUMBER :=1;
5933: cpt NUMBER :=1;
5926: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5927: ) IS
5928: l_party_id NUMBER;
5929: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5930: l_contact_ids HZ_PARTY_SEARCH.IDList;
5931: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5932: ps NUMBER :=1;
5933: cpt NUMBER :=1;
5934: ct NUMBER :=1;
5927: ) IS
5928: l_party_id NUMBER;
5929: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5930: l_contact_ids HZ_PARTY_SEARCH.IDList;
5931: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5932: ps NUMBER :=1;
5933: cpt NUMBER :=1;
5934: ct NUMBER :=1;
5935: l_use_contact_info varchar2(1);
6036: END get_party_for_search;
6037:
6038: PROCEDURE get_search_criteria (
6039: p_party_id IN NUMBER,
6040: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
6041: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6042: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
6043: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
6044: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
6037:
6038: PROCEDURE get_search_criteria (
6039: p_party_id IN NUMBER,
6040: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
6041: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6042: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
6043: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
6044: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
6045: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
6038: PROCEDURE get_search_criteria (
6039: p_party_id IN NUMBER,
6040: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
6041: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6042: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
6043: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
6044: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
6045: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
6046: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6039: p_party_id IN NUMBER,
6040: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
6041: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6042: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
6043: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
6044: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
6045: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
6046: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6047: ) IS
6040: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
6041: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6042: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
6043: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
6044: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
6045: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
6046: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6047: ) IS
6048: BEGIN
6041: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6042: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
6043: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
6044: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
6045: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
6046: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6047: ) IS
6048: BEGIN
6049:
6042: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
6043: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
6044: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
6045: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
6046: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6047: ) IS
6048: BEGIN
6049:
6050: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6094: ************************************************/
6095:
6096: PROCEDURE get_party_rec (
6097: p_party_id IN NUMBER,
6098: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type
6099: ) IS
6100: l_party_type VARCHAR2(255);
6101: BEGIN
6102:
6144: the party site search record structure
6145: ************************************************/
6146:
6147: PROCEDURE get_party_site_rec (
6148: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
6149: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
6150: ) IS
6151: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
6152: SELECT party_site_id
6145: ************************************************/
6146:
6147: PROCEDURE get_party_site_rec (
6148: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
6149: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
6150: ) IS
6151: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
6152: SELECT party_site_id
6153: ,translate(HZ_LOCATIONS.CITY, '%',' ')
6204: the contact search record structure
6205: ************************************************/
6206:
6207: PROCEDURE get_contact_rec (
6208: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6209: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
6210: ) IS
6211: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
6212: SELECT org_contact_id
6205: ************************************************/
6206:
6207: PROCEDURE get_contact_rec (
6208: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6209: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
6210: ) IS
6211: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
6212: SELECT org_contact_id
6213: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
6262: the contact point search record structure
6263: ************************************************/
6264:
6265: PROCEDURE get_contact_point_rec (
6266: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
6267: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6268: ) IS
6269:
6270: -- Cursor to fetch primary contact points for party
6263: ************************************************/
6264:
6265: PROCEDURE get_contact_point_rec (
6266: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
6267: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6268: ) IS
6269:
6270: -- Cursor to fetch primary contact points for party
6271: CURSOR c_cpts(cp_contact_point_id NUMBER) IS
6323: ************************************************/
6324:
6325: PROCEDURE MAP_PARTY_REC(
6326: p_search_ctx IN BOOLEAN,
6327: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6328: x_entered_max_score OUT NUMBER,
6329: x_stage_rec IN OUT NOCOPY HZ_PARTY_STAGE.party_stage_rec_type
6330: ) IS
6331: tmp VARCHAR2(4000);
6442: ************************************************/
6443:
6444: PROCEDURE MAP_PARTY_SITE_REC(
6445: p_search_ctx IN BOOLEAN,
6446: p_search_list IN HZ_PARTY_SEARCH.party_site_list,
6447: x_entered_max_score OUT NUMBER,
6448: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.party_site_stage_list
6449: ) IS
6450: l_current_max_score NUMBER;
6638: ************************************************/
6639:
6640: PROCEDURE MAP_CONTACT_REC(
6641: p_search_ctx IN BOOLEAN,
6642: p_search_list IN HZ_PARTY_SEARCH.contact_list,
6643: x_entered_max_score OUT NUMBER,
6644: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_stage_list
6645: ) IS
6646: l_current_max_score NUMBER;
6728: ************************************************/
6729:
6730: PROCEDURE MAP_CONTACT_POINT_REC(
6731: p_search_ctx IN BOOLEAN,
6732: p_search_list IN HZ_PARTY_SEARCH.contact_point_list,
6733: x_entered_max_score OUT NUMBER,
6734: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_pt_stage_list
6735: ) IS
6736: l_current_max_score NUMBER;
6884: 2. Contact Point Type is not null for each condition
6885: ************************************************/
6886:
6887: FUNCTION check_prim_cond(
6888: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6889: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6890: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6891: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6892: RETURN BOOLEAN IS
6885: ************************************************/
6886:
6887: FUNCTION check_prim_cond(
6888: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6889: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6890: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6891: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6892: RETURN BOOLEAN IS
6893: BEGIN
6886:
6887: FUNCTION check_prim_cond(
6888: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6889: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6890: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6891: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6892: RETURN BOOLEAN IS
6893: BEGIN
6894: FOR I IN 1..p_contact_point_list.COUNT LOOP
6887: FUNCTION check_prim_cond(
6888: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6889: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6890: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6891: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6892: RETURN BOOLEAN IS
6893: BEGIN
6894: FOR I IN 1..p_contact_point_list.COUNT LOOP
6895: IF p_contact_point_list(I).CONTACT_POINT_TYPE IS NULL AND (
6966: has party site criteria.
6967: ************************************************/
6968:
6969: PROCEDURE check_party_site_cond(
6970: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6971: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6972: x_secondary OUT BOOLEAN,
6973: x_primary OUT BOOLEAN
6974: ) IS
6967: ************************************************/
6968:
6969: PROCEDURE check_party_site_cond(
6970: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6971: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6972: x_secondary OUT BOOLEAN,
6973: x_primary OUT BOOLEAN
6974: ) IS
6975: BEGIN
7028: has contact criteria.
7029: ************************************************/
7030:
7031: PROCEDURE check_contact_cond(
7032: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
7033: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
7034: x_secondary OUT BOOLEAN,
7035: x_primary OUT BOOLEAN
7036: ) IS
7029: ************************************************/
7030:
7031: PROCEDURE check_contact_cond(
7032: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
7033: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
7034: x_secondary OUT BOOLEAN,
7035: x_primary OUT BOOLEAN
7036: ) IS
7037: BEGIN
7086: has valid contact point criteria.
7087: ************************************************/
7088:
7089: PROCEDURE check_contact_point_cond(
7090: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
7091: x_secondary OUT BOOLEAN,
7092: x_primary OUT BOOLEAN
7093: ) IS
7094: BEGIN