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;
500: PROCEDURE INSERT_PARTY_SCORE (
501: p_party_id IN NUMBER
502: ,p_record_id IN NUMBER
503: ,p_search_ctx_id IN NUMBER
504: ,p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
505: ,p_stage_rec IN HZ_PARTY_STAGE.party_stage_rec_type
506: ,p_table_TX40 VARCHAR2
507: ,p_table_TX39 VARCHAR2
508: ,p_idx IN NUMBER) IS
549: PROCEDURE INSERT_PARTY_SITES_SCORE (
550: p_party_id IN NUMBER
551: ,p_record_id IN NUMBER
552: ,p_search_ctx_id IN NUMBER
553: ,p_search_rec IN HZ_PARTY_SEARCH.party_site_search_rec_type
554: ,p_stage_rec IN HZ_PARTY_STAGE.party_site_stage_rec_type
555: ,p_table_TX3 VARCHAR2
556: ,p_table_TX4 VARCHAR2
557: ,p_table_TX9 VARCHAR2
749: PROCEDURE INSERT_CONTACTS_SCORE (
750: p_party_id IN NUMBER
751: ,p_record_id IN NUMBER
752: ,p_search_ctx_id IN NUMBER
753: ,p_search_rec IN HZ_PARTY_SEARCH.contact_search_rec_type
754: ,p_stage_rec IN HZ_PARTY_STAGE.contact_stage_rec_type
755: ,p_table_TX22 VARCHAR2
756: ,p_idx IN NUMBER) IS
757: l_current_score NUMBER:=0;
791: PROCEDURE INSERT_CONTACT_POINTS_SCORE (
792: p_party_id IN NUMBER
793: ,p_record_id IN NUMBER
794: ,p_search_ctx_id IN NUMBER
795: ,p_search_rec IN HZ_PARTY_SEARCH.contact_point_search_rec_type
796: ,p_stage_rec IN HZ_PARTY_STAGE.contact_pt_stage_rec_type
797: ,p_table_TX1 VARCHAR2
798: ,p_table_TX6 VARCHAR2
799: ,p_table_TX5 VARCHAR2
869: for the PARTY Entity
870: ************************************************/
871:
872: PROCEDURE POP_PARTY_COND_REC(
873: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
874: ) IS
875: BEGIN
876: NULL ;
877: EXCEPTION
889: for the PARTY_SITES Entity
890: ************************************************/
891:
892: PROCEDURE POP_PARTY_SITES_COND_REC(
893: p_search_list IN HZ_PARTY_SEARCH.party_site_list
894: ) IS
895: BEGIN
896: ---------POPULATE THE GLOBAL WORD CONDITION REC FOR PARTY_SITES-------------
897: ------ Populate global condition record only if search list is not empty -----------
914: for the CONTACTS Entity
915: ************************************************/
916:
917: PROCEDURE POP_CONTACTS_COND_REC(
918: p_search_list IN HZ_PARTY_SEARCH.contact_list
919: ) IS
920: BEGIN
921: NULL ;
922: EXCEPTION
934: for the CONTACT_POINTS Entity
935: ************************************************/
936:
937: PROCEDURE POP_CONTACT_POINTS_COND_REC(
938: p_search_list IN HZ_PARTY_SEARCH.contact_point_list
939: ) IS
940: BEGIN
941: NULL ;
942: EXCEPTION
948: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
949: END;
950:
951: PROCEDURE init_score_context (
952: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
953: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
954: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
955: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
956: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
949: END;
950:
951: PROCEDURE init_score_context (
952: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
953: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
954: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
955: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
956: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
957: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
950:
951: PROCEDURE init_score_context (
952: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
953: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
954: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
955: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
956: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
957: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
958: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
951: PROCEDURE init_score_context (
952: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
953: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
954: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
955: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
956: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
957: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
958: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
959: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
952: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
953: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
954: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
955: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
956: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
957: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
958: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
959: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
960: ) IS
953: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
954: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
955: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
956: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
957: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
958: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
959: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
960: ) IS
961: l_dummy NUMBER;
954: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
955: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
956: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
957: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
958: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
959: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
960: ) IS
961: l_dummy NUMBER;
962: BEGIN
955: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
956: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
957: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
958: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
959: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
960: ) IS
961: l_dummy NUMBER;
962: BEGIN
963: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
975: MAP_CONTACT_POINT_REC(FALSE,p_contact_point_list, l_dummy, g_contact_pt_stage_list);
976:
977: END;
978: FUNCTION init_search(
979: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
980: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
981: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
982: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
983: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
976:
977: END;
978: FUNCTION init_search(
979: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
980: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
981: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
982: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
983: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
984: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
977: END;
978: FUNCTION init_search(
979: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
980: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
981: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
982: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
983: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
984: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
985: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
978: FUNCTION init_search(
979: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
980: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
981: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
982: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
983: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
984: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
985: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
986: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
979: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
980: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
981: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
982: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
983: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
984: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
985: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
986: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
987: p_match_type IN VARCHAR2,
980: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
981: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
982: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
983: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
984: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
985: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
986: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
987: p_match_type IN VARCHAR2,
988: x_party_max_score OUT NUMBER,
981: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
982: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
983: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
984: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
985: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
986: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
987: p_match_type IN VARCHAR2,
988: x_party_max_score OUT NUMBER,
989: x_ps_max_score OUT NUMBER,
982: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
983: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
984: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
985: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
986: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
987: p_match_type IN VARCHAR2,
988: x_party_max_score OUT NUMBER,
989: x_ps_max_score OUT NUMBER,
990: x_contact_max_score OUT NUMBER,
994: l_ps_entered_max_score NUMBER:=0;
995: l_ct_entered_max_score NUMBER:=0;
996: l_cpt_entered_max_score NUMBER:=0;
997: vlist vlisttype;
998: maxscore HZ_PARTY_SEARCH.IDList;
999: l_name VARCHAR2(200);
1000: l_idx NUMBER;
1001: l_num NUMBER;
1002: total NUMBER;
2442: p_party_id NUMBER,
2443: p_party_site_id NUMBER,
2444: p_org_contact_id NUMBER,
2445: p_contact_point_id NUMBER)
2446: RETURN HZ_PARTY_SEARCH.score_rec IS
2447: l_score_rec HZ_PARTY_SEARCH.score_rec;
2448: BEGIN
2449: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2450: 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);
2443: p_party_site_id NUMBER,
2444: p_org_contact_id NUMBER,
2445: p_contact_point_id NUMBER)
2446: RETURN HZ_PARTY_SEARCH.score_rec IS
2447: l_score_rec HZ_PARTY_SEARCH.score_rec;
2448: BEGIN
2449: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2450: 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);
2451: 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);
2475: **********************************************************/
2476:
2477: PROCEDURE find_parties (
2478: p_rule_id IN NUMBER,
2479: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2480: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2481: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2482: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2483: p_restrict_sql IN VARCHAR2,
2476:
2477: PROCEDURE find_parties (
2478: p_rule_id IN NUMBER,
2479: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2480: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2481: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2482: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2483: p_restrict_sql IN VARCHAR2,
2484: p_match_type IN VARCHAR2,
2477: PROCEDURE find_parties (
2478: p_rule_id IN NUMBER,
2479: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2480: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2481: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2482: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2483: p_restrict_sql IN VARCHAR2,
2484: p_match_type IN VARCHAR2,
2485: p_search_merged IN VARCHAR2,
2478: p_rule_id IN NUMBER,
2479: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2480: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2481: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2482: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2483: p_restrict_sql IN VARCHAR2,
2484: p_match_type IN VARCHAR2,
2485: p_search_merged IN VARCHAR2,
2486: p_dup_party_id IN NUMBER,
2525: l_TX5 VARCHAR2(2000);
2526: l_TX40 VARCHAR2(2000);
2527: l_TX14 VARCHAR2(2000);
2528: l_TX6 VARCHAR2(2000);
2529: H_SCORES HZ_PARTY_SEARCH.score_list;
2530: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2531: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2532:
2533: l_score NUMBER;
2526: l_TX40 VARCHAR2(2000);
2527: l_TX14 VARCHAR2(2000);
2528: l_TX6 VARCHAR2(2000);
2529: H_SCORES HZ_PARTY_SEARCH.score_list;
2530: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2531: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2532:
2533: l_score NUMBER;
2534: l_idx NUMBER;
2527: l_TX14 VARCHAR2(2000);
2528: l_TX6 VARCHAR2(2000);
2529: H_SCORES HZ_PARTY_SEARCH.score_list;
2530: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2531: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2532:
2533: l_score NUMBER;
2534: l_idx NUMBER;
2535: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
2718: /** Private procedure to acquire and score at party site level ***/
2719: 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
2720: l_party_id_idx NUMBER:=1;
2721: l_ctx_id NUMBER;
2722: h_ps_id HZ_PARTY_SEARCH.IDList;
2723: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2724: h_ps_score HZ_PARTY_SEARCH.IDList;
2725: detcnt NUMBER := 1;
2726: BEGIN
2719: 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
2720: l_party_id_idx NUMBER:=1;
2721: l_ctx_id NUMBER;
2722: h_ps_id HZ_PARTY_SEARCH.IDList;
2723: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2724: h_ps_score HZ_PARTY_SEARCH.IDList;
2725: detcnt NUMBER := 1;
2726: BEGIN
2727: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2720: l_party_id_idx NUMBER:=1;
2721: l_ctx_id NUMBER;
2722: h_ps_id HZ_PARTY_SEARCH.IDList;
2723: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2724: h_ps_score HZ_PARTY_SEARCH.IDList;
2725: detcnt NUMBER := 1;
2726: BEGIN
2727: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2728: 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);
2840: /** Private procedure to acquire and score at contact point level ***/
2841: 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
2842: l_party_id_idx NUMBER:=1;
2843: l_ctx_id NUMBER;
2844: h_ct_id HZ_PARTY_SEARCH.IDList;
2845: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2846: h_ct_score HZ_PARTY_SEARCH.IDList;
2847: detcnt NUMBER := 1;
2848: BEGIN
2841: 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
2842: l_party_id_idx NUMBER:=1;
2843: l_ctx_id NUMBER;
2844: h_ct_id HZ_PARTY_SEARCH.IDList;
2845: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2846: h_ct_score HZ_PARTY_SEARCH.IDList;
2847: detcnt NUMBER := 1;
2848: BEGIN
2849: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2842: l_party_id_idx NUMBER:=1;
2843: l_ctx_id NUMBER;
2844: h_ct_id HZ_PARTY_SEARCH.IDList;
2845: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2846: h_ct_score HZ_PARTY_SEARCH.IDList;
2847: detcnt NUMBER := 1;
2848: BEGIN
2849: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2850: 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);
2949: /** Private procedure to acquire and score at contact point level ***/
2950: 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
2951: l_party_id_idx NUMBER:=1;
2952: l_ctx_id NUMBER;
2953: h_cpt_id HZ_PARTY_SEARCH.IDList;
2954: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2955: h_cpt_score HZ_PARTY_SEARCH.IDList;
2956: detcnt NUMBER := 1;
2957: l_cpt_flag VARCHAR2(1) := 'N';
2950: 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
2951: l_party_id_idx NUMBER:=1;
2952: l_ctx_id NUMBER;
2953: h_cpt_id HZ_PARTY_SEARCH.IDList;
2954: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2955: h_cpt_score HZ_PARTY_SEARCH.IDList;
2956: detcnt NUMBER := 1;
2957: l_cpt_flag VARCHAR2(1) := 'N';
2958: is_a_match VARCHAR2(1) := 'Y';
2951: l_party_id_idx NUMBER:=1;
2952: l_ctx_id NUMBER;
2953: h_cpt_id HZ_PARTY_SEARCH.IDList;
2954: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2955: h_cpt_score HZ_PARTY_SEARCH.IDList;
2956: detcnt NUMBER := 1;
2957: l_cpt_flag VARCHAR2(1) := 'N';
2958: is_a_match VARCHAR2(1) := 'Y';
2959: BEGIN
3438: END find_parties;
3439:
3440: PROCEDURE find_persons (
3441: p_rule_id IN NUMBER,
3442: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3443: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3444: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3445: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3446: p_restrict_sql IN VARCHAR2,
3439:
3440: PROCEDURE find_persons (
3441: p_rule_id IN NUMBER,
3442: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3443: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3444: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3445: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3446: p_restrict_sql IN VARCHAR2,
3447: p_match_type IN VARCHAR2,
3440: PROCEDURE find_persons (
3441: p_rule_id IN NUMBER,
3442: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3443: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3444: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3445: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3446: p_restrict_sql IN VARCHAR2,
3447: p_match_type IN VARCHAR2,
3448: p_search_merged IN VARCHAR2,
3441: p_rule_id IN NUMBER,
3442: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3443: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3444: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3445: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3446: p_restrict_sql IN VARCHAR2,
3447: p_match_type IN VARCHAR2,
3448: p_search_merged IN VARCHAR2,
3449: p_dup_party_id IN NUMBER,
3488: l_TX5 VARCHAR2(2000);
3489: l_TX40 VARCHAR2(2000);
3490: l_TX14 VARCHAR2(2000);
3491: l_TX6 VARCHAR2(2000);
3492: H_SCORES HZ_PARTY_SEARCH.score_list;
3493: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3494: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3495:
3496: l_score NUMBER;
3489: l_TX40 VARCHAR2(2000);
3490: l_TX14 VARCHAR2(2000);
3491: l_TX6 VARCHAR2(2000);
3492: H_SCORES HZ_PARTY_SEARCH.score_list;
3493: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3494: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3495:
3496: l_score NUMBER;
3497: l_idx NUMBER;
3490: l_TX14 VARCHAR2(2000);
3491: l_TX6 VARCHAR2(2000);
3492: H_SCORES HZ_PARTY_SEARCH.score_list;
3493: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3494: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3495:
3496: l_score NUMBER;
3497: l_idx NUMBER;
3498: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
3729: /** Private procedure to acquire and score at party site level ***/
3730: 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
3731: l_party_id_idx NUMBER:=1;
3732: l_ctx_id NUMBER;
3733: h_ps_id HZ_PARTY_SEARCH.IDList;
3734: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3735: h_ps_score HZ_PARTY_SEARCH.IDList;
3736: detcnt NUMBER := 1;
3737: l_person_id NUMBER;
3730: 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
3731: l_party_id_idx NUMBER:=1;
3732: l_ctx_id NUMBER;
3733: h_ps_id HZ_PARTY_SEARCH.IDList;
3734: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3735: h_ps_score HZ_PARTY_SEARCH.IDList;
3736: detcnt NUMBER := 1;
3737: l_person_id NUMBER;
3738: BEGIN
3731: l_party_id_idx NUMBER:=1;
3732: l_ctx_id NUMBER;
3733: h_ps_id HZ_PARTY_SEARCH.IDList;
3734: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3735: h_ps_score HZ_PARTY_SEARCH.IDList;
3736: detcnt NUMBER := 1;
3737: l_person_id NUMBER;
3738: BEGIN
3739: SAVEPOINT eval_start;
3828: /** Private procedure to acquire and score at party site level ***/
3829: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3830: l_party_id_idx NUMBER:=1;
3831: l_ctx_id NUMBER;
3832: h_ct_id HZ_PARTY_SEARCH.IDList;
3833: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3834: h_ct_score HZ_PARTY_SEARCH.IDList;
3835: detcnt NUMBER := 1;
3836: l_person_id NUMBER;
3829: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3830: l_party_id_idx NUMBER:=1;
3831: l_ctx_id NUMBER;
3832: h_ct_id HZ_PARTY_SEARCH.IDList;
3833: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3834: h_ct_score HZ_PARTY_SEARCH.IDList;
3835: detcnt NUMBER := 1;
3836: l_person_id NUMBER;
3837: BEGIN
3830: l_party_id_idx NUMBER:=1;
3831: l_ctx_id NUMBER;
3832: h_ct_id HZ_PARTY_SEARCH.IDList;
3833: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3834: h_ct_score HZ_PARTY_SEARCH.IDList;
3835: detcnt NUMBER := 1;
3836: l_person_id NUMBER;
3837: BEGIN
3838: SAVEPOINT eval_start;
3886: /** Private procedure to acquire and score at contact point level ***/
3887: 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
3888: l_party_id_idx NUMBER:=1;
3889: l_ctx_id NUMBER;
3890: h_cpt_id HZ_PARTY_SEARCH.IDList;
3891: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3892: h_cpt_score HZ_PARTY_SEARCH.IDList;
3893: detcnt NUMBER := 1;
3894: l_person_id NUMBER;
3887: 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
3888: l_party_id_idx NUMBER:=1;
3889: l_ctx_id NUMBER;
3890: h_cpt_id HZ_PARTY_SEARCH.IDList;
3891: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3892: h_cpt_score HZ_PARTY_SEARCH.IDList;
3893: detcnt NUMBER := 1;
3894: l_person_id NUMBER;
3895: is_a_match VARCHAR2(1) := 'Y';
3888: l_party_id_idx NUMBER:=1;
3889: l_ctx_id NUMBER;
3890: h_cpt_id HZ_PARTY_SEARCH.IDList;
3891: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3892: h_cpt_score HZ_PARTY_SEARCH.IDList;
3893: detcnt NUMBER := 1;
3894: l_person_id NUMBER;
3895: is_a_match VARCHAR2(1) := 'Y';
3896: l_cpt_flag VARCHAR2(1) := 'N';
4299: END find_persons;
4300:
4301: PROCEDURE find_persons (
4302: p_rule_id IN NUMBER,
4303: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4304: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4305: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4306: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4307: p_restrict_sql IN VARCHAR2,
4300:
4301: PROCEDURE find_persons (
4302: p_rule_id IN NUMBER,
4303: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4304: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4305: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4306: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4307: p_restrict_sql IN VARCHAR2,
4308: p_match_type IN VARCHAR2,
4301: PROCEDURE find_persons (
4302: p_rule_id IN NUMBER,
4303: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4304: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4305: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4306: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4307: p_restrict_sql IN VARCHAR2,
4308: p_match_type IN VARCHAR2,
4309: p_search_merged IN VARCHAR2,
4302: p_rule_id IN NUMBER,
4303: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4304: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4305: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4306: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4307: p_restrict_sql IN VARCHAR2,
4308: p_match_type IN VARCHAR2,
4309: p_search_merged IN VARCHAR2,
4310: p_ins_details IN VARCHAR2,
4316: 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);
4317: END;
4318: PROCEDURE find_party_details (
4319: p_rule_id IN NUMBER,
4320: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4321: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4322: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4323: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4324: p_restrict_sql IN VARCHAR2,
4317: END;
4318: PROCEDURE find_party_details (
4319: p_rule_id IN NUMBER,
4320: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4321: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4322: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4323: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4324: p_restrict_sql IN VARCHAR2,
4325: p_match_type IN VARCHAR2,
4318: PROCEDURE find_party_details (
4319: p_rule_id IN NUMBER,
4320: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4321: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4322: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4323: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4324: p_restrict_sql IN VARCHAR2,
4325: p_match_type IN VARCHAR2,
4326: p_search_merged IN VARCHAR2,
4319: p_rule_id IN NUMBER,
4320: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4321: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4322: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4323: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4324: p_restrict_sql IN VARCHAR2,
4325: p_match_type IN VARCHAR2,
4326: p_search_merged IN VARCHAR2,
4327: x_search_ctx_id OUT NUMBER,
4376: x_dup_set_id OUT NUMBER,
4377: x_search_ctx_id OUT NUMBER,
4378: x_num_matches OUT NUMBER
4379: ) IS
4380: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4381: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4382: l_contact_list HZ_PARTY_SEARCH.contact_list;
4383: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4384: l_match_idx NUMBER;
4377: x_search_ctx_id OUT NUMBER,
4378: x_num_matches OUT NUMBER
4379: ) IS
4380: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4381: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4382: l_contact_list HZ_PARTY_SEARCH.contact_list;
4383: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4384: l_match_idx NUMBER;
4385:
4378: x_num_matches OUT NUMBER
4379: ) IS
4380: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4381: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4382: l_contact_list HZ_PARTY_SEARCH.contact_list;
4383: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4384: l_match_idx NUMBER;
4385:
4386: --Fix for bug 4417124
4379: ) IS
4380: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4381: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4382: l_contact_list HZ_PARTY_SEARCH.contact_list;
4383: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4384: l_match_idx NUMBER;
4385:
4386: --Fix for bug 4417124
4387: l_use_contact_addr_info BOOLEAN := TRUE;
4492: x_search_ctx_id OUT NUMBER,
4493: x_num_matches OUT NUMBER
4494: ) IS
4495:
4496: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4497: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4498: l_contact_list HZ_PARTY_SEARCH.contact_list;
4499: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4500: contact_point_ids HZ_PARTY_SEARCH.IDList;
4493: x_num_matches OUT NUMBER
4494: ) IS
4495:
4496: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4497: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4498: l_contact_list HZ_PARTY_SEARCH.contact_list;
4499: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4500: contact_point_ids HZ_PARTY_SEARCH.IDList;
4501: p_party_site_list HZ_PARTY_SEARCH.IDList;
4494: ) IS
4495:
4496: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4497: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4498: l_contact_list HZ_PARTY_SEARCH.contact_list;
4499: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4500: contact_point_ids HZ_PARTY_SEARCH.IDList;
4501: p_party_site_list HZ_PARTY_SEARCH.IDList;
4502: p_contact_ids HZ_PARTY_SEARCH.IDList;
4495:
4496: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4497: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4498: l_contact_list HZ_PARTY_SEARCH.contact_list;
4499: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4500: contact_point_ids HZ_PARTY_SEARCH.IDList;
4501: p_party_site_list HZ_PARTY_SEARCH.IDList;
4502: p_contact_ids HZ_PARTY_SEARCH.IDList;
4503: l_match_idx NUMBER;
4496: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4497: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4498: l_contact_list HZ_PARTY_SEARCH.contact_list;
4499: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4500: contact_point_ids HZ_PARTY_SEARCH.IDList;
4501: p_party_site_list HZ_PARTY_SEARCH.IDList;
4502: p_contact_ids HZ_PARTY_SEARCH.IDList;
4503: l_match_idx NUMBER;
4504: cursor get_cpts_for_party_sites is select contact_point_id
4497: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4498: l_contact_list HZ_PARTY_SEARCH.contact_list;
4499: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4500: contact_point_ids HZ_PARTY_SEARCH.IDList;
4501: p_party_site_list HZ_PARTY_SEARCH.IDList;
4502: p_contact_ids HZ_PARTY_SEARCH.IDList;
4503: l_match_idx NUMBER;
4504: cursor get_cpts_for_party_sites is select contact_point_id
4505: from hz_contact_points
4498: l_contact_list HZ_PARTY_SEARCH.contact_list;
4499: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4500: contact_point_ids HZ_PARTY_SEARCH.IDList;
4501: p_party_site_list HZ_PARTY_SEARCH.IDList;
4502: p_contact_ids HZ_PARTY_SEARCH.IDList;
4503: l_match_idx NUMBER;
4504: cursor get_cpts_for_party_sites is select contact_point_id
4505: from hz_contact_points
4506: where owner_table_name = 'HZ_PARTY_SITES'
4527:
4528: get_search_criteria (
4529: null,
4530: p_party_site_list,
4531: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4532: contact_point_ids,
4533: l_party_search_rec,
4534: l_party_site_list,
4535: l_contact_list,
4577: x_search_ctx_id OUT NUMBER,
4578: x_num_matches OUT NUMBER
4579: ) IS
4580:
4581: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4582: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4583: l_contact_list HZ_PARTY_SEARCH.contact_list;
4584: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4585: contact_point_ids HZ_PARTY_SEARCH.IDList;
4578: x_num_matches OUT NUMBER
4579: ) IS
4580:
4581: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4582: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4583: l_contact_list HZ_PARTY_SEARCH.contact_list;
4584: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4585: contact_point_ids HZ_PARTY_SEARCH.IDList;
4586: p_party_site_list HZ_PARTY_SEARCH.IDList;
4579: ) IS
4580:
4581: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4582: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4583: l_contact_list HZ_PARTY_SEARCH.contact_list;
4584: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4585: contact_point_ids HZ_PARTY_SEARCH.IDList;
4586: p_party_site_list HZ_PARTY_SEARCH.IDList;
4587: p_contact_ids HZ_PARTY_SEARCH.IDList;
4580:
4581: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4582: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4583: l_contact_list HZ_PARTY_SEARCH.contact_list;
4584: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4585: contact_point_ids HZ_PARTY_SEARCH.IDList;
4586: p_party_site_list HZ_PARTY_SEARCH.IDList;
4587: p_contact_ids HZ_PARTY_SEARCH.IDList;
4588: l_match_idx NUMBER;
4581: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4582: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4583: l_contact_list HZ_PARTY_SEARCH.contact_list;
4584: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4585: contact_point_ids HZ_PARTY_SEARCH.IDList;
4586: p_party_site_list HZ_PARTY_SEARCH.IDList;
4587: p_contact_ids HZ_PARTY_SEARCH.IDList;
4588: l_match_idx NUMBER;
4589: cursor get_cpt_for_contact_id is select contact_point_id
4582: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4583: l_contact_list HZ_PARTY_SEARCH.contact_list;
4584: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4585: contact_point_ids HZ_PARTY_SEARCH.IDList;
4586: p_party_site_list HZ_PARTY_SEARCH.IDList;
4587: p_contact_ids HZ_PARTY_SEARCH.IDList;
4588: l_match_idx NUMBER;
4589: cursor get_cpt_for_contact_id is select contact_point_id
4590: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4583: l_contact_list HZ_PARTY_SEARCH.contact_list;
4584: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4585: contact_point_ids HZ_PARTY_SEARCH.IDList;
4586: p_party_site_list HZ_PARTY_SEARCH.IDList;
4587: p_contact_ids HZ_PARTY_SEARCH.IDList;
4588: l_match_idx NUMBER;
4589: cursor get_cpt_for_contact_id is select contact_point_id
4590: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4591: where a.party_relationship_id = b.relationship_id
4614: CLOSE get_cpt_for_contact_id;
4615:
4616: get_search_criteria (
4617: null,
4618: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4619: p_contact_ids,
4620: contact_point_ids,
4621: l_party_search_rec,
4622: l_party_site_list,
4665: p_match_type IN VARCHAR2,
4666: x_search_ctx_id OUT NUMBER,
4667: x_num_matches OUT NUMBER
4668: ) IS
4669: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4670: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4671: l_contact_list HZ_PARTY_SEARCH.contact_list;
4672: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4673: contact_point_ids HZ_PARTY_SEARCH.IDList;
4666: x_search_ctx_id OUT NUMBER,
4667: x_num_matches OUT NUMBER
4668: ) IS
4669: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4670: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4671: l_contact_list HZ_PARTY_SEARCH.contact_list;
4672: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4673: contact_point_ids HZ_PARTY_SEARCH.IDList;
4674: p_party_site_list HZ_PARTY_SEARCH.IDList;
4667: x_num_matches OUT NUMBER
4668: ) IS
4669: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4670: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4671: l_contact_list HZ_PARTY_SEARCH.contact_list;
4672: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4673: contact_point_ids HZ_PARTY_SEARCH.IDList;
4674: p_party_site_list HZ_PARTY_SEARCH.IDList;
4675: p_contact_ids HZ_PARTY_SEARCH.IDList;
4668: ) IS
4669: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4670: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4671: l_contact_list HZ_PARTY_SEARCH.contact_list;
4672: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4673: contact_point_ids HZ_PARTY_SEARCH.IDList;
4674: p_party_site_list HZ_PARTY_SEARCH.IDList;
4675: p_contact_ids HZ_PARTY_SEARCH.IDList;
4676: l_match_idx NUMBER;
4669: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4670: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4671: l_contact_list HZ_PARTY_SEARCH.contact_list;
4672: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4673: contact_point_ids HZ_PARTY_SEARCH.IDList;
4674: p_party_site_list HZ_PARTY_SEARCH.IDList;
4675: p_contact_ids HZ_PARTY_SEARCH.IDList;
4676: l_match_idx NUMBER;
4677:
4670: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4671: l_contact_list HZ_PARTY_SEARCH.contact_list;
4672: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4673: contact_point_ids HZ_PARTY_SEARCH.IDList;
4674: p_party_site_list HZ_PARTY_SEARCH.IDList;
4675: p_contact_ids HZ_PARTY_SEARCH.IDList;
4676: l_match_idx NUMBER;
4677:
4678: BEGIN
4671: l_contact_list HZ_PARTY_SEARCH.contact_list;
4672: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4673: contact_point_ids HZ_PARTY_SEARCH.IDList;
4674: p_party_site_list HZ_PARTY_SEARCH.IDList;
4675: p_contact_ids HZ_PARTY_SEARCH.IDList;
4676: l_match_idx NUMBER;
4677:
4678: BEGIN
4679: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4688: END IF;
4689: contact_point_ids(1) := p_contact_point_id;
4690: get_search_criteria (
4691: null,
4692: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4693: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4694: contact_point_ids,
4695: l_party_search_rec,
4696: l_party_site_list,
4689: contact_point_ids(1) := p_contact_point_id;
4690: get_search_criteria (
4691: null,
4692: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4693: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4694: contact_point_ids,
4695: l_party_search_rec,
4696: l_party_site_list,
4697: l_contact_list,
4842: ) IS
4843: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4844: l_match_idx NUMBER;
4845: AttrVals AttrList;
4846: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4847: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4848: l_contact_list HZ_PARTY_SEARCH.contact_list;
4849: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4850: l_dup_set_id NUMBER;
4843: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4844: l_match_idx NUMBER;
4845: AttrVals AttrList;
4846: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4847: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4848: l_contact_list HZ_PARTY_SEARCH.contact_list;
4849: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4850: l_dup_set_id NUMBER;
4851: l_idx NUMBER;
4844: l_match_idx NUMBER;
4845: AttrVals AttrList;
4846: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4847: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4848: l_contact_list HZ_PARTY_SEARCH.contact_list;
4849: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4850: l_dup_set_id NUMBER;
4851: l_idx NUMBER;
4852: l_cpt_type VARCHAR2(255);
4845: AttrVals AttrList;
4846: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4847: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4848: l_contact_list HZ_PARTY_SEARCH.contact_list;
4849: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4850: l_dup_set_id NUMBER;
4851: l_idx NUMBER;
4852: l_cpt_type VARCHAR2(255);
4853: FIRST BOOLEAN := TRUE;
5069:
5070: PROCEDURE get_matching_party_sites (
5071: p_rule_id IN NUMBER,
5072: p_party_id IN NUMBER,
5073: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
5074: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5075: p_restrict_sql IN VARCHAR2,
5076: p_match_type IN VARCHAR2,
5077: p_dup_party_site_id IN NUMBER,
5070: PROCEDURE get_matching_party_sites (
5071: p_rule_id IN NUMBER,
5072: p_party_id IN NUMBER,
5073: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
5074: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5075: p_restrict_sql IN VARCHAR2,
5076: p_match_type IN VARCHAR2,
5077: p_dup_party_site_id IN NUMBER,
5078: x_search_ctx_id OUT NUMBER,
5108: l_TX11 VARCHAR2(2000);
5109: l_TX5 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_TX22 VARCHAR2(2000);
5345: l_TX1 VARCHAR2(2000);
5346: l_TX5 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,
5575: l_cpt_ps_id NUMBER;
5576: l_cpt_contact_id NUMBER;
5577: l_contact_pt_id NUMBER;
5578: l_cpt_type VARCHAR2(100);
5579: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5580: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5581: H_SCORE HZ_PARTY_SEARCH.IDList;
5582:
5583: cnt NUMBER :=0;
5576: l_cpt_contact_id NUMBER;
5577: l_contact_pt_id NUMBER;
5578: l_cpt_type VARCHAR2(100);
5579: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5580: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5581: H_SCORE HZ_PARTY_SEARCH.IDList;
5582:
5583: cnt NUMBER :=0;
5584: l_party_max_score NUMBER;
5577: l_contact_pt_id NUMBER;
5578: l_cpt_type VARCHAR2(100);
5579: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5580: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5581: H_SCORE HZ_PARTY_SEARCH.IDList;
5582:
5583: cnt NUMBER :=0;
5584: l_party_max_score NUMBER;
5585: l_ps_max_score NUMBER;
5606: l_match_str := ' AND ';
5607: ELSIF p_match_type = 'OR' THEN
5608: l_match_str := ' OR ';
5609: END IF;
5610: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
5611: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
5612: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5613: g_score_until_thresh := false;
5614: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5607: ELSIF p_match_type = 'OR' THEN
5608: l_match_str := ' OR ';
5609: END IF;
5610: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
5611: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
5612: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5613: g_score_until_thresh := false;
5614: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5615:
5615:
5616: -- ************************************************************
5617: -- STEP 2. Setup of intermedia query strings for Acquisition query
5618: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5619: 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);
5620:
5621: -- Setup Search Context ID
5622: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5623: x_search_ctx_id := l_search_ctx_id;
5690:
5691: PROCEDURE get_score_details (
5692: p_rule_id IN NUMBER,
5693: p_party_id IN NUMBER,
5694: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5695: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5696: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5697: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5698: x_search_ctx_id IN OUT NUMBER
5691: PROCEDURE get_score_details (
5692: p_rule_id IN NUMBER,
5693: p_party_id IN NUMBER,
5694: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5695: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5696: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5697: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5698: x_search_ctx_id IN OUT NUMBER
5699: ) IS
5692: p_rule_id IN NUMBER,
5693: p_party_id IN NUMBER,
5694: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5695: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5696: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5697: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5698: x_search_ctx_id IN OUT NUMBER
5699: ) IS
5700:
5693: p_party_id IN NUMBER,
5694: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5695: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5696: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5697: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5698: x_search_ctx_id IN OUT NUMBER
5699: ) IS
5700:
5701: -- Strings to hold the generated Intermedia query strings
5742: l_TX14 VARCHAR2(2000);
5743: l_max_TX14 VARCHAR2(2000);
5744: l_TX6 VARCHAR2(2000);
5745: l_max_TX6 VARCHAR2(2000);
5746: H_SCORES HZ_PARTY_SEARCH.score_list;
5747:
5748: l_score NUMBER;
5749: l_match_idx NUMBER;
5750: l_idx NUMBER;
5921: ************************************************/
5922:
5923: PROCEDURE get_party_for_search (
5924: p_party_id IN NUMBER,
5925: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5926: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5927: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5928: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5929: ) IS
5922:
5923: PROCEDURE get_party_for_search (
5924: p_party_id IN NUMBER,
5925: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5926: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5927: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5928: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5929: ) IS
5930: l_party_id NUMBER;
5923: PROCEDURE get_party_for_search (
5924: p_party_id IN NUMBER,
5925: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5926: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5927: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5928: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5929: ) IS
5930: l_party_id NUMBER;
5931: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5924: p_party_id IN NUMBER,
5925: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5926: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5927: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5928: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5929: ) IS
5930: l_party_id NUMBER;
5931: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5932: l_contact_ids HZ_PARTY_SEARCH.IDList;
5927: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5928: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5929: ) IS
5930: l_party_id NUMBER;
5931: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5932: l_contact_ids HZ_PARTY_SEARCH.IDList;
5933: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5934: ps NUMBER :=1;
5935: cpt NUMBER :=1;
5928: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5929: ) IS
5930: l_party_id NUMBER;
5931: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5932: l_contact_ids HZ_PARTY_SEARCH.IDList;
5933: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5934: ps NUMBER :=1;
5935: cpt NUMBER :=1;
5936: ct NUMBER :=1;
5929: ) IS
5930: l_party_id NUMBER;
5931: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5932: l_contact_ids HZ_PARTY_SEARCH.IDList;
5933: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5934: ps NUMBER :=1;
5935: cpt NUMBER :=1;
5936: ct NUMBER :=1;
5937: l_use_contact_info varchar2(1);
6038: END get_party_for_search;
6039:
6040: PROCEDURE get_search_criteria (
6041: p_party_id IN NUMBER,
6042: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
6043: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6044: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
6045: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
6046: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
6039:
6040: PROCEDURE get_search_criteria (
6041: p_party_id IN NUMBER,
6042: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
6043: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6044: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
6045: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
6046: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
6047: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
6040: PROCEDURE get_search_criteria (
6041: p_party_id IN NUMBER,
6042: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
6043: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6044: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
6045: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
6046: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
6047: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
6048: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6041: p_party_id IN NUMBER,
6042: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
6043: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6044: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
6045: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
6046: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
6047: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
6048: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6049: ) IS
6042: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
6043: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6044: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
6045: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
6046: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
6047: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
6048: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6049: ) IS
6050: BEGIN
6043: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6044: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
6045: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
6046: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
6047: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
6048: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6049: ) IS
6050: BEGIN
6051:
6044: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
6045: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
6046: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
6047: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
6048: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6049: ) IS
6050: BEGIN
6051:
6052: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6096: ************************************************/
6097:
6098: PROCEDURE get_party_rec (
6099: p_party_id IN NUMBER,
6100: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type
6101: ) IS
6102: l_party_type VARCHAR2(255);
6103: BEGIN
6104:
6139: the party site search record structure
6140: ************************************************/
6141:
6142: PROCEDURE get_party_site_rec (
6143: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
6144: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
6145: ) IS
6146: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
6147: SELECT party_site_id
6140: ************************************************/
6141:
6142: PROCEDURE get_party_site_rec (
6143: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
6144: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
6145: ) IS
6146: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
6147: SELECT party_site_id
6148: ,translate(HZ_LOCATIONS.CITY, '%',' ')
6203: the contact search record structure
6204: ************************************************/
6205:
6206: PROCEDURE get_contact_rec (
6207: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6208: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
6209: ) IS
6210: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
6211: SELECT org_contact_id
6204: ************************************************/
6205:
6206: PROCEDURE get_contact_rec (
6207: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
6208: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
6209: ) IS
6210: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
6211: SELECT org_contact_id
6212: ,translate(HZ_ORG_CONTACTS.JOB_TITLE, '%',' ')
6261: the contact point search record structure
6262: ************************************************/
6263:
6264: PROCEDURE get_contact_point_rec (
6265: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
6266: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6267: ) IS
6268:
6269: -- Cursor to fetch primary contact points for party
6262: ************************************************/
6263:
6264: PROCEDURE get_contact_point_rec (
6265: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
6266: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6267: ) IS
6268:
6269: -- Cursor to fetch primary contact points for party
6270: CURSOR c_cpts(cp_contact_point_id NUMBER) IS
6320: ************************************************/
6321:
6322: PROCEDURE MAP_PARTY_REC(
6323: p_search_ctx IN BOOLEAN,
6324: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6325: x_entered_max_score OUT NUMBER,
6326: x_stage_rec IN OUT NOCOPY HZ_PARTY_STAGE.party_stage_rec_type
6327: ) IS
6328: tmp VARCHAR2(4000);
6417: ************************************************/
6418:
6419: PROCEDURE MAP_PARTY_SITE_REC(
6420: p_search_ctx IN BOOLEAN,
6421: p_search_list IN HZ_PARTY_SEARCH.party_site_list,
6422: x_entered_max_score OUT NUMBER,
6423: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.party_site_stage_list
6424: ) IS
6425: l_current_max_score NUMBER;
6658: ************************************************/
6659:
6660: PROCEDURE MAP_CONTACT_REC(
6661: p_search_ctx IN BOOLEAN,
6662: p_search_list IN HZ_PARTY_SEARCH.contact_list,
6663: x_entered_max_score OUT NUMBER,
6664: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_stage_list
6665: ) IS
6666: l_current_max_score NUMBER;
6715: ************************************************/
6716:
6717: PROCEDURE MAP_CONTACT_POINT_REC(
6718: p_search_ctx IN BOOLEAN,
6719: p_search_list IN HZ_PARTY_SEARCH.contact_point_list,
6720: x_entered_max_score OUT NUMBER,
6721: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_pt_stage_list
6722: ) IS
6723: l_current_max_score NUMBER;
6845: 2. Contact Point Type is not null for each condition
6846: ************************************************/
6847:
6848: FUNCTION check_prim_cond(
6849: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6850: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6851: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6852: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6853: RETURN BOOLEAN IS
6846: ************************************************/
6847:
6848: FUNCTION check_prim_cond(
6849: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6850: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6851: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6852: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6853: RETURN BOOLEAN IS
6854: BEGIN
6847:
6848: FUNCTION check_prim_cond(
6849: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6850: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6851: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6852: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6853: RETURN BOOLEAN IS
6854: BEGIN
6855: FOR I IN 1..p_contact_point_list.COUNT LOOP
6848: FUNCTION check_prim_cond(
6849: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6850: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6851: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6852: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6853: RETURN BOOLEAN IS
6854: BEGIN
6855: FOR I IN 1..p_contact_point_list.COUNT LOOP
6856: IF p_contact_point_list(I).CONTACT_POINT_TYPE IS NULL AND (
6928: has party site criteria.
6929: ************************************************/
6930:
6931: PROCEDURE check_party_site_cond(
6932: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6933: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6934: x_secondary OUT BOOLEAN,
6935: x_primary OUT BOOLEAN
6936: ) IS
6929: ************************************************/
6930:
6931: PROCEDURE check_party_site_cond(
6932: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6933: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6934: x_secondary OUT BOOLEAN,
6935: x_primary OUT BOOLEAN
6936: ) IS
6937: BEGIN
6992: has contact criteria.
6993: ************************************************/
6994:
6995: PROCEDURE check_contact_cond(
6996: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6997: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6998: x_secondary OUT BOOLEAN,
6999: x_primary OUT BOOLEAN
7000: ) IS
6993: ************************************************/
6994:
6995: PROCEDURE check_contact_cond(
6996: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6997: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6998: x_secondary OUT BOOLEAN,
6999: x_primary OUT BOOLEAN
7000: ) IS
7001: BEGIN
7048: has valid contact point criteria.
7049: ************************************************/
7050:
7051: PROCEDURE check_contact_point_cond(
7052: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
7053: x_secondary OUT BOOLEAN,
7054: x_primary OUT BOOLEAN
7055: ) IS
7056: BEGIN