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;
418: PROCEDURE INSERT_PARTY_SCORE (
419: p_party_id IN NUMBER
420: ,p_record_id IN NUMBER
421: ,p_search_ctx_id IN NUMBER
422: ,p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
423: ,p_stage_rec IN HZ_PARTY_STAGE.party_stage_rec_type
424: ,p_table_TX2 VARCHAR2
425: ,p_table_TX8 VARCHAR2
426: ,p_table_TX4 VARCHAR2
500: PROCEDURE INSERT_PARTY_SITES_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_site_search_rec_type
505: ,p_stage_rec IN HZ_PARTY_STAGE.party_site_stage_rec_type
506: ,p_table_TX3 VARCHAR2
507: ,p_table_TX4 VARCHAR2
508: ,p_table_TX11 VARCHAR2
575: PROCEDURE INSERT_CONTACTS_SCORE (
576: p_party_id IN NUMBER
577: ,p_record_id IN NUMBER
578: ,p_search_ctx_id IN NUMBER
579: ,p_search_rec IN HZ_PARTY_SEARCH.contact_search_rec_type
580: ,p_stage_rec IN HZ_PARTY_STAGE.contact_stage_rec_type
581: ,p_idx IN NUMBER) IS
582: l_current_score NUMBER:=0;
583: l_score NUMBER;
591: PROCEDURE INSERT_CONTACT_POINTS_SCORE (
592: p_party_id IN NUMBER
593: ,p_record_id IN NUMBER
594: ,p_search_ctx_id IN NUMBER
595: ,p_search_rec IN HZ_PARTY_SEARCH.contact_point_search_rec_type
596: ,p_stage_rec IN HZ_PARTY_STAGE.contact_pt_stage_rec_type
597: ,p_table_TX1 VARCHAR2
598: ,p_table_TX6 VARCHAR2
599: ,p_table_TX5 VARCHAR2
669: for the PARTY Entity
670: ************************************************/
671:
672: PROCEDURE POP_PARTY_COND_REC(
673: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
674: ) IS
675: BEGIN
676: NULL ;
677: EXCEPTION
689: for the PARTY_SITES Entity
690: ************************************************/
691:
692: PROCEDURE POP_PARTY_SITES_COND_REC(
693: p_search_list IN HZ_PARTY_SEARCH.party_site_list
694: ) IS
695: BEGIN
696: NULL ;
697: EXCEPTION
709: for the CONTACTS Entity
710: ************************************************/
711:
712: PROCEDURE POP_CONTACTS_COND_REC(
713: p_search_list IN HZ_PARTY_SEARCH.contact_list
714: ) IS
715: BEGIN
716: NULL ;
717: EXCEPTION
729: for the CONTACT_POINTS Entity
730: ************************************************/
731:
732: PROCEDURE POP_CONTACT_POINTS_COND_REC(
733: p_search_list IN HZ_PARTY_SEARCH.contact_point_list
734: ) IS
735: BEGIN
736: NULL ;
737: EXCEPTION
743: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
744: END;
745:
746: PROCEDURE init_score_context (
747: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
748: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
749: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
750: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
751: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
744: END;
745:
746: PROCEDURE init_score_context (
747: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
748: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
749: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
750: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
751: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
752: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
745:
746: PROCEDURE init_score_context (
747: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
748: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
749: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
750: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
751: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
752: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
753: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
746: PROCEDURE init_score_context (
747: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
748: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
749: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
750: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
751: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
752: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
753: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
754: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
747: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
748: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
749: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
750: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
751: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
752: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
753: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
754: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
755: ) IS
748: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
749: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
750: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
751: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
752: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
753: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
754: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
755: ) IS
756: l_dummy NUMBER;
749: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
750: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
751: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
752: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
753: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
754: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
755: ) IS
756: l_dummy NUMBER;
757: BEGIN
750: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
751: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
752: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
753: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
754: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
755: ) IS
756: l_dummy NUMBER;
757: BEGIN
758: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
770: MAP_CONTACT_POINT_REC(FALSE,p_contact_point_list, l_dummy, g_contact_pt_stage_list);
771:
772: END;
773: FUNCTION init_search(
774: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
775: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
776: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
777: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
778: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
771:
772: END;
773: FUNCTION init_search(
774: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
775: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
776: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
777: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
778: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
779: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
772: END;
773: FUNCTION init_search(
774: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
775: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
776: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
777: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
778: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
779: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
780: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
773: FUNCTION init_search(
774: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
775: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
776: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
777: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
778: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
779: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
780: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
781: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
774: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
775: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
776: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
777: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
778: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
779: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
780: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
781: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
782: p_match_type IN VARCHAR2,
775: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
776: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
777: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
778: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
779: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
780: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
781: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
782: p_match_type IN VARCHAR2,
783: x_party_max_score OUT NUMBER,
776: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
777: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
778: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
779: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
780: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
781: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
782: p_match_type IN VARCHAR2,
783: x_party_max_score OUT NUMBER,
784: x_ps_max_score OUT NUMBER,
777: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
778: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
779: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
780: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
781: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
782: p_match_type IN VARCHAR2,
783: x_party_max_score OUT NUMBER,
784: x_ps_max_score OUT NUMBER,
785: x_contact_max_score OUT NUMBER,
789: l_ps_entered_max_score NUMBER:=0;
790: l_ct_entered_max_score NUMBER:=0;
791: l_cpt_entered_max_score NUMBER:=0;
792: vlist vlisttype;
793: maxscore HZ_PARTY_SEARCH.IDList;
794: l_name VARCHAR2(200);
795: l_idx NUMBER;
796: l_num NUMBER;
797: total NUMBER;
2119: p_party_id NUMBER,
2120: p_party_site_id NUMBER,
2121: p_org_contact_id NUMBER,
2122: p_contact_point_id NUMBER)
2123: RETURN HZ_PARTY_SEARCH.score_rec IS
2124: l_score_rec HZ_PARTY_SEARCH.score_rec;
2125: BEGIN
2126: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2127: 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);
2120: p_party_site_id NUMBER,
2121: p_org_contact_id NUMBER,
2122: p_contact_point_id NUMBER)
2123: RETURN HZ_PARTY_SEARCH.score_rec IS
2124: l_score_rec HZ_PARTY_SEARCH.score_rec;
2125: BEGIN
2126: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2127: 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);
2128: 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);
2152: **********************************************************/
2153:
2154: PROCEDURE find_parties (
2155: p_rule_id IN NUMBER,
2156: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2157: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2158: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2159: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2160: p_restrict_sql IN VARCHAR2,
2153:
2154: PROCEDURE find_parties (
2155: p_rule_id IN NUMBER,
2156: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2157: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2158: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2159: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2160: p_restrict_sql IN VARCHAR2,
2161: p_match_type IN VARCHAR2,
2154: PROCEDURE find_parties (
2155: p_rule_id IN NUMBER,
2156: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2157: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2158: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2159: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2160: p_restrict_sql IN VARCHAR2,
2161: p_match_type IN VARCHAR2,
2162: p_search_merged IN VARCHAR2,
2155: p_rule_id IN NUMBER,
2156: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2157: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2158: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2159: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2160: p_restrict_sql IN VARCHAR2,
2161: p_match_type IN VARCHAR2,
2162: p_search_merged IN VARCHAR2,
2163: p_dup_party_id IN NUMBER,
2196: l_TX11 VARCHAR2(2000);
2197: l_TX5 VARCHAR2(2000);
2198: l_TX8 VARCHAR2(2000);
2199: l_TX6 VARCHAR2(2000);
2200: H_SCORES HZ_PARTY_SEARCH.score_list;
2201: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2202: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2203:
2204: l_score NUMBER;
2197: l_TX5 VARCHAR2(2000);
2198: l_TX8 VARCHAR2(2000);
2199: l_TX6 VARCHAR2(2000);
2200: H_SCORES HZ_PARTY_SEARCH.score_list;
2201: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2202: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2203:
2204: l_score NUMBER;
2205: l_idx NUMBER;
2198: l_TX8 VARCHAR2(2000);
2199: l_TX6 VARCHAR2(2000);
2200: H_SCORES HZ_PARTY_SEARCH.score_list;
2201: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2202: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2203:
2204: l_score NUMBER;
2205: l_idx NUMBER;
2206: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
2389: /** Private procedure to acquire and score at party site level ***/
2390: 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
2391: l_party_id_idx NUMBER:=1;
2392: l_ctx_id NUMBER;
2393: h_ps_id HZ_PARTY_SEARCH.IDList;
2394: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2395: h_ps_score HZ_PARTY_SEARCH.IDList;
2396: detcnt NUMBER := 1;
2397: BEGIN
2390: 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
2391: l_party_id_idx NUMBER:=1;
2392: l_ctx_id NUMBER;
2393: h_ps_id HZ_PARTY_SEARCH.IDList;
2394: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2395: h_ps_score HZ_PARTY_SEARCH.IDList;
2396: detcnt NUMBER := 1;
2397: BEGIN
2398: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2391: l_party_id_idx NUMBER:=1;
2392: l_ctx_id NUMBER;
2393: h_ps_id HZ_PARTY_SEARCH.IDList;
2394: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2395: h_ps_score HZ_PARTY_SEARCH.IDList;
2396: detcnt NUMBER := 1;
2397: BEGIN
2398: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2399: 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);
2511: /** Private procedure to acquire and score at contact point level ***/
2512: 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
2513: l_party_id_idx NUMBER:=1;
2514: l_ctx_id NUMBER;
2515: h_ct_id HZ_PARTY_SEARCH.IDList;
2516: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2517: h_ct_score HZ_PARTY_SEARCH.IDList;
2518: detcnt NUMBER := 1;
2519: BEGIN
2512: 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
2513: l_party_id_idx NUMBER:=1;
2514: l_ctx_id NUMBER;
2515: h_ct_id HZ_PARTY_SEARCH.IDList;
2516: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2517: h_ct_score HZ_PARTY_SEARCH.IDList;
2518: detcnt NUMBER := 1;
2519: BEGIN
2520: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2513: l_party_id_idx NUMBER:=1;
2514: l_ctx_id NUMBER;
2515: h_ct_id HZ_PARTY_SEARCH.IDList;
2516: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2517: h_ct_score HZ_PARTY_SEARCH.IDList;
2518: detcnt NUMBER := 1;
2519: BEGIN
2520: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2521: 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);
2620: /** Private procedure to acquire and score at contact point level ***/
2621: 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
2622: l_party_id_idx NUMBER:=1;
2623: l_ctx_id NUMBER;
2624: h_cpt_id HZ_PARTY_SEARCH.IDList;
2625: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2626: h_cpt_score HZ_PARTY_SEARCH.IDList;
2627: detcnt NUMBER := 1;
2628: l_cpt_flag VARCHAR2(1) := 'N';
2621: 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
2622: l_party_id_idx NUMBER:=1;
2623: l_ctx_id NUMBER;
2624: h_cpt_id HZ_PARTY_SEARCH.IDList;
2625: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2626: h_cpt_score HZ_PARTY_SEARCH.IDList;
2627: detcnt NUMBER := 1;
2628: l_cpt_flag VARCHAR2(1) := 'N';
2629: is_a_match VARCHAR2(1) := 'Y';
2622: l_party_id_idx NUMBER:=1;
2623: l_ctx_id NUMBER;
2624: h_cpt_id HZ_PARTY_SEARCH.IDList;
2625: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2626: h_cpt_score HZ_PARTY_SEARCH.IDList;
2627: detcnt NUMBER := 1;
2628: l_cpt_flag VARCHAR2(1) := 'N';
2629: is_a_match VARCHAR2(1) := 'Y';
2630: BEGIN
3071: END find_parties;
3072:
3073: PROCEDURE find_persons (
3074: p_rule_id IN NUMBER,
3075: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3076: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3077: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3078: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3079: p_restrict_sql IN VARCHAR2,
3072:
3073: PROCEDURE find_persons (
3074: p_rule_id IN NUMBER,
3075: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3076: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3077: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3078: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3079: p_restrict_sql IN VARCHAR2,
3080: p_match_type IN VARCHAR2,
3073: PROCEDURE find_persons (
3074: p_rule_id IN NUMBER,
3075: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3076: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3077: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3078: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3079: p_restrict_sql IN VARCHAR2,
3080: p_match_type IN VARCHAR2,
3081: p_search_merged IN VARCHAR2,
3074: p_rule_id IN NUMBER,
3075: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3076: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3077: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3078: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3079: p_restrict_sql IN VARCHAR2,
3080: p_match_type IN VARCHAR2,
3081: p_search_merged IN VARCHAR2,
3082: p_dup_party_id IN NUMBER,
3115: l_TX11 VARCHAR2(2000);
3116: l_TX5 VARCHAR2(2000);
3117: l_TX8 VARCHAR2(2000);
3118: l_TX6 VARCHAR2(2000);
3119: H_SCORES HZ_PARTY_SEARCH.score_list;
3120: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3121: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3122:
3123: l_score NUMBER;
3116: l_TX5 VARCHAR2(2000);
3117: l_TX8 VARCHAR2(2000);
3118: l_TX6 VARCHAR2(2000);
3119: H_SCORES HZ_PARTY_SEARCH.score_list;
3120: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3121: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3122:
3123: l_score NUMBER;
3124: l_idx NUMBER;
3117: l_TX8 VARCHAR2(2000);
3118: l_TX6 VARCHAR2(2000);
3119: H_SCORES HZ_PARTY_SEARCH.score_list;
3120: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3121: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3122:
3123: l_score NUMBER;
3124: l_idx NUMBER;
3125: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
3356: /** Private procedure to acquire and score at party site level ***/
3357: 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
3358: l_party_id_idx NUMBER:=1;
3359: l_ctx_id NUMBER;
3360: h_ps_id HZ_PARTY_SEARCH.IDList;
3361: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3362: h_ps_score HZ_PARTY_SEARCH.IDList;
3363: detcnt NUMBER := 1;
3364: l_person_id NUMBER;
3357: 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
3358: l_party_id_idx NUMBER:=1;
3359: l_ctx_id NUMBER;
3360: h_ps_id HZ_PARTY_SEARCH.IDList;
3361: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3362: h_ps_score HZ_PARTY_SEARCH.IDList;
3363: detcnt NUMBER := 1;
3364: l_person_id NUMBER;
3365: BEGIN
3358: l_party_id_idx NUMBER:=1;
3359: l_ctx_id NUMBER;
3360: h_ps_id HZ_PARTY_SEARCH.IDList;
3361: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3362: h_ps_score HZ_PARTY_SEARCH.IDList;
3363: detcnt NUMBER := 1;
3364: l_person_id NUMBER;
3365: BEGIN
3366: SAVEPOINT eval_start;
3455: /** Private procedure to acquire and score at party site level ***/
3456: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3457: l_party_id_idx NUMBER:=1;
3458: l_ctx_id NUMBER;
3459: h_ct_id HZ_PARTY_SEARCH.IDList;
3460: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3461: h_ct_score HZ_PARTY_SEARCH.IDList;
3462: detcnt NUMBER := 1;
3463: l_person_id NUMBER;
3456: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3457: l_party_id_idx NUMBER:=1;
3458: l_ctx_id NUMBER;
3459: h_ct_id HZ_PARTY_SEARCH.IDList;
3460: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3461: h_ct_score HZ_PARTY_SEARCH.IDList;
3462: detcnt NUMBER := 1;
3463: l_person_id NUMBER;
3464: BEGIN
3457: l_party_id_idx NUMBER:=1;
3458: l_ctx_id NUMBER;
3459: h_ct_id HZ_PARTY_SEARCH.IDList;
3460: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3461: h_ct_score HZ_PARTY_SEARCH.IDList;
3462: detcnt NUMBER := 1;
3463: l_person_id NUMBER;
3464: BEGIN
3465: SAVEPOINT eval_start;
3513: /** Private procedure to acquire and score at contact point level ***/
3514: 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
3515: l_party_id_idx NUMBER:=1;
3516: l_ctx_id NUMBER;
3517: h_cpt_id HZ_PARTY_SEARCH.IDList;
3518: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3519: h_cpt_score HZ_PARTY_SEARCH.IDList;
3520: detcnt NUMBER := 1;
3521: l_person_id NUMBER;
3514: 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
3515: l_party_id_idx NUMBER:=1;
3516: l_ctx_id NUMBER;
3517: h_cpt_id HZ_PARTY_SEARCH.IDList;
3518: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3519: h_cpt_score HZ_PARTY_SEARCH.IDList;
3520: detcnt NUMBER := 1;
3521: l_person_id NUMBER;
3522: is_a_match VARCHAR2(1) := 'Y';
3515: l_party_id_idx NUMBER:=1;
3516: l_ctx_id NUMBER;
3517: h_cpt_id HZ_PARTY_SEARCH.IDList;
3518: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3519: h_cpt_score HZ_PARTY_SEARCH.IDList;
3520: detcnt NUMBER := 1;
3521: l_person_id NUMBER;
3522: is_a_match VARCHAR2(1) := 'Y';
3523: l_cpt_flag VARCHAR2(1) := 'N';
3857: END find_persons;
3858:
3859: PROCEDURE find_persons (
3860: p_rule_id IN NUMBER,
3861: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3862: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3863: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3864: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3865: p_restrict_sql IN VARCHAR2,
3858:
3859: PROCEDURE find_persons (
3860: p_rule_id IN NUMBER,
3861: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3862: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3863: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3864: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3865: p_restrict_sql IN VARCHAR2,
3866: p_match_type IN VARCHAR2,
3859: PROCEDURE find_persons (
3860: p_rule_id IN NUMBER,
3861: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3862: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3863: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3864: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3865: p_restrict_sql IN VARCHAR2,
3866: p_match_type IN VARCHAR2,
3867: p_search_merged IN VARCHAR2,
3860: p_rule_id IN NUMBER,
3861: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3862: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3863: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3864: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3865: p_restrict_sql IN VARCHAR2,
3866: p_match_type IN VARCHAR2,
3867: p_search_merged IN VARCHAR2,
3868: p_ins_details IN VARCHAR2,
3874: 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);
3875: END;
3876: PROCEDURE find_party_details (
3877: p_rule_id IN NUMBER,
3878: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3879: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3880: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3881: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3882: p_restrict_sql IN VARCHAR2,
3875: END;
3876: PROCEDURE find_party_details (
3877: p_rule_id IN NUMBER,
3878: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3879: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3880: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3881: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3882: p_restrict_sql IN VARCHAR2,
3883: p_match_type IN VARCHAR2,
3876: PROCEDURE find_party_details (
3877: p_rule_id IN NUMBER,
3878: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3879: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3880: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3881: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3882: p_restrict_sql IN VARCHAR2,
3883: p_match_type IN VARCHAR2,
3884: p_search_merged IN VARCHAR2,
3877: p_rule_id IN NUMBER,
3878: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3879: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3880: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3881: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3882: p_restrict_sql IN VARCHAR2,
3883: p_match_type IN VARCHAR2,
3884: p_search_merged IN VARCHAR2,
3885: x_search_ctx_id OUT NUMBER,
3934: x_dup_set_id OUT NUMBER,
3935: x_search_ctx_id OUT NUMBER,
3936: x_num_matches OUT NUMBER
3937: ) IS
3938: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3939: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3940: l_contact_list HZ_PARTY_SEARCH.contact_list;
3941: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3942: l_match_idx NUMBER;
3935: x_search_ctx_id OUT NUMBER,
3936: x_num_matches OUT NUMBER
3937: ) IS
3938: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3939: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3940: l_contact_list HZ_PARTY_SEARCH.contact_list;
3941: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3942: l_match_idx NUMBER;
3943:
3936: x_num_matches OUT NUMBER
3937: ) IS
3938: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3939: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3940: l_contact_list HZ_PARTY_SEARCH.contact_list;
3941: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3942: l_match_idx NUMBER;
3943:
3944: --Fix for bug 4417124
3937: ) IS
3938: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3939: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3940: l_contact_list HZ_PARTY_SEARCH.contact_list;
3941: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3942: l_match_idx NUMBER;
3943:
3944: --Fix for bug 4417124
3945: l_use_contact_addr_info BOOLEAN := TRUE;
4050: x_search_ctx_id OUT NUMBER,
4051: x_num_matches OUT NUMBER
4052: ) IS
4053:
4054: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4055: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4056: l_contact_list HZ_PARTY_SEARCH.contact_list;
4057: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4058: contact_point_ids HZ_PARTY_SEARCH.IDList;
4051: x_num_matches OUT NUMBER
4052: ) IS
4053:
4054: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4055: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4056: l_contact_list HZ_PARTY_SEARCH.contact_list;
4057: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4058: contact_point_ids HZ_PARTY_SEARCH.IDList;
4059: p_party_site_list HZ_PARTY_SEARCH.IDList;
4052: ) IS
4053:
4054: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4055: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4056: l_contact_list HZ_PARTY_SEARCH.contact_list;
4057: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4058: contact_point_ids HZ_PARTY_SEARCH.IDList;
4059: p_party_site_list HZ_PARTY_SEARCH.IDList;
4060: p_contact_ids HZ_PARTY_SEARCH.IDList;
4053:
4054: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4055: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4056: l_contact_list HZ_PARTY_SEARCH.contact_list;
4057: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4058: contact_point_ids HZ_PARTY_SEARCH.IDList;
4059: p_party_site_list HZ_PARTY_SEARCH.IDList;
4060: p_contact_ids HZ_PARTY_SEARCH.IDList;
4061: l_match_idx NUMBER;
4054: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4055: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4056: l_contact_list HZ_PARTY_SEARCH.contact_list;
4057: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4058: contact_point_ids HZ_PARTY_SEARCH.IDList;
4059: p_party_site_list HZ_PARTY_SEARCH.IDList;
4060: p_contact_ids HZ_PARTY_SEARCH.IDList;
4061: l_match_idx NUMBER;
4062: cursor get_cpts_for_party_sites is select contact_point_id
4055: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4056: l_contact_list HZ_PARTY_SEARCH.contact_list;
4057: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4058: contact_point_ids HZ_PARTY_SEARCH.IDList;
4059: p_party_site_list HZ_PARTY_SEARCH.IDList;
4060: p_contact_ids HZ_PARTY_SEARCH.IDList;
4061: l_match_idx NUMBER;
4062: cursor get_cpts_for_party_sites is select contact_point_id
4063: from hz_contact_points
4056: l_contact_list HZ_PARTY_SEARCH.contact_list;
4057: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4058: contact_point_ids HZ_PARTY_SEARCH.IDList;
4059: p_party_site_list HZ_PARTY_SEARCH.IDList;
4060: p_contact_ids HZ_PARTY_SEARCH.IDList;
4061: l_match_idx NUMBER;
4062: cursor get_cpts_for_party_sites is select contact_point_id
4063: from hz_contact_points
4064: where owner_table_name = 'HZ_PARTY_SITES'
4085:
4086: get_search_criteria (
4087: null,
4088: p_party_site_list,
4089: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4090: contact_point_ids,
4091: l_party_search_rec,
4092: l_party_site_list,
4093: l_contact_list,
4135: x_search_ctx_id OUT NUMBER,
4136: x_num_matches OUT NUMBER
4137: ) IS
4138:
4139: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4140: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4141: l_contact_list HZ_PARTY_SEARCH.contact_list;
4142: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4143: contact_point_ids HZ_PARTY_SEARCH.IDList;
4136: x_num_matches OUT NUMBER
4137: ) IS
4138:
4139: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4140: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4141: l_contact_list HZ_PARTY_SEARCH.contact_list;
4142: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4143: contact_point_ids HZ_PARTY_SEARCH.IDList;
4144: p_party_site_list HZ_PARTY_SEARCH.IDList;
4137: ) IS
4138:
4139: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4140: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4141: l_contact_list HZ_PARTY_SEARCH.contact_list;
4142: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4143: contact_point_ids HZ_PARTY_SEARCH.IDList;
4144: p_party_site_list HZ_PARTY_SEARCH.IDList;
4145: p_contact_ids HZ_PARTY_SEARCH.IDList;
4138:
4139: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4140: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4141: l_contact_list HZ_PARTY_SEARCH.contact_list;
4142: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4143: contact_point_ids HZ_PARTY_SEARCH.IDList;
4144: p_party_site_list HZ_PARTY_SEARCH.IDList;
4145: p_contact_ids HZ_PARTY_SEARCH.IDList;
4146: l_match_idx NUMBER;
4139: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4140: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4141: l_contact_list HZ_PARTY_SEARCH.contact_list;
4142: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4143: contact_point_ids HZ_PARTY_SEARCH.IDList;
4144: p_party_site_list HZ_PARTY_SEARCH.IDList;
4145: p_contact_ids HZ_PARTY_SEARCH.IDList;
4146: l_match_idx NUMBER;
4147: cursor get_cpt_for_contact_id is select contact_point_id
4140: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4141: l_contact_list HZ_PARTY_SEARCH.contact_list;
4142: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4143: contact_point_ids HZ_PARTY_SEARCH.IDList;
4144: p_party_site_list HZ_PARTY_SEARCH.IDList;
4145: p_contact_ids HZ_PARTY_SEARCH.IDList;
4146: l_match_idx NUMBER;
4147: cursor get_cpt_for_contact_id is select contact_point_id
4148: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4141: l_contact_list HZ_PARTY_SEARCH.contact_list;
4142: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4143: contact_point_ids HZ_PARTY_SEARCH.IDList;
4144: p_party_site_list HZ_PARTY_SEARCH.IDList;
4145: p_contact_ids HZ_PARTY_SEARCH.IDList;
4146: l_match_idx NUMBER;
4147: cursor get_cpt_for_contact_id is select contact_point_id
4148: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4149: where a.party_relationship_id = b.relationship_id
4172: CLOSE get_cpt_for_contact_id;
4173:
4174: get_search_criteria (
4175: null,
4176: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4177: p_contact_ids,
4178: contact_point_ids,
4179: l_party_search_rec,
4180: l_party_site_list,
4223: p_match_type IN VARCHAR2,
4224: x_search_ctx_id OUT NUMBER,
4225: x_num_matches OUT NUMBER
4226: ) IS
4227: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4228: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4229: l_contact_list HZ_PARTY_SEARCH.contact_list;
4230: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4231: contact_point_ids HZ_PARTY_SEARCH.IDList;
4224: x_search_ctx_id OUT NUMBER,
4225: x_num_matches OUT NUMBER
4226: ) IS
4227: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4228: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4229: l_contact_list HZ_PARTY_SEARCH.contact_list;
4230: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4231: contact_point_ids HZ_PARTY_SEARCH.IDList;
4232: p_party_site_list HZ_PARTY_SEARCH.IDList;
4225: x_num_matches OUT NUMBER
4226: ) IS
4227: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4228: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4229: l_contact_list HZ_PARTY_SEARCH.contact_list;
4230: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4231: contact_point_ids HZ_PARTY_SEARCH.IDList;
4232: p_party_site_list HZ_PARTY_SEARCH.IDList;
4233: p_contact_ids HZ_PARTY_SEARCH.IDList;
4226: ) IS
4227: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4228: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4229: l_contact_list HZ_PARTY_SEARCH.contact_list;
4230: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4231: contact_point_ids HZ_PARTY_SEARCH.IDList;
4232: p_party_site_list HZ_PARTY_SEARCH.IDList;
4233: p_contact_ids HZ_PARTY_SEARCH.IDList;
4234: l_match_idx NUMBER;
4227: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4228: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4229: l_contact_list HZ_PARTY_SEARCH.contact_list;
4230: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4231: contact_point_ids HZ_PARTY_SEARCH.IDList;
4232: p_party_site_list HZ_PARTY_SEARCH.IDList;
4233: p_contact_ids HZ_PARTY_SEARCH.IDList;
4234: l_match_idx NUMBER;
4235:
4228: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4229: l_contact_list HZ_PARTY_SEARCH.contact_list;
4230: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4231: contact_point_ids HZ_PARTY_SEARCH.IDList;
4232: p_party_site_list HZ_PARTY_SEARCH.IDList;
4233: p_contact_ids HZ_PARTY_SEARCH.IDList;
4234: l_match_idx NUMBER;
4235:
4236: BEGIN
4229: l_contact_list HZ_PARTY_SEARCH.contact_list;
4230: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4231: contact_point_ids HZ_PARTY_SEARCH.IDList;
4232: p_party_site_list HZ_PARTY_SEARCH.IDList;
4233: p_contact_ids HZ_PARTY_SEARCH.IDList;
4234: l_match_idx NUMBER;
4235:
4236: BEGIN
4237: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4246: END IF;
4247: contact_point_ids(1) := p_contact_point_id;
4248: get_search_criteria (
4249: null,
4250: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4251: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4252: contact_point_ids,
4253: l_party_search_rec,
4254: l_party_site_list,
4247: contact_point_ids(1) := p_contact_point_id;
4248: get_search_criteria (
4249: null,
4250: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4251: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4252: contact_point_ids,
4253: l_party_search_rec,
4254: l_party_site_list,
4255: l_contact_list,
4400: ) IS
4401: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4402: l_match_idx NUMBER;
4403: AttrVals AttrList;
4404: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4405: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4406: l_contact_list HZ_PARTY_SEARCH.contact_list;
4407: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4408: l_dup_set_id NUMBER;
4401: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4402: l_match_idx NUMBER;
4403: AttrVals AttrList;
4404: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4405: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4406: l_contact_list HZ_PARTY_SEARCH.contact_list;
4407: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4408: l_dup_set_id NUMBER;
4409: l_idx NUMBER;
4402: l_match_idx NUMBER;
4403: AttrVals AttrList;
4404: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4405: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4406: l_contact_list HZ_PARTY_SEARCH.contact_list;
4407: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4408: l_dup_set_id NUMBER;
4409: l_idx NUMBER;
4410: l_cpt_type VARCHAR2(255);
4403: AttrVals AttrList;
4404: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4405: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4406: l_contact_list HZ_PARTY_SEARCH.contact_list;
4407: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4408: l_dup_set_id NUMBER;
4409: l_idx NUMBER;
4410: l_cpt_type VARCHAR2(255);
4411: FIRST BOOLEAN := TRUE;
4599:
4600: PROCEDURE get_matching_party_sites (
4601: p_rule_id IN NUMBER,
4602: p_party_id IN NUMBER,
4603: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4604: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4605: p_restrict_sql IN VARCHAR2,
4606: p_match_type IN VARCHAR2,
4607: p_dup_party_site_id IN NUMBER,
4600: PROCEDURE get_matching_party_sites (
4601: p_rule_id IN NUMBER,
4602: p_party_id IN NUMBER,
4603: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4604: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4605: p_restrict_sql IN VARCHAR2,
4606: p_match_type IN VARCHAR2,
4607: p_dup_party_site_id IN NUMBER,
4608: x_search_ctx_id OUT NUMBER,
4631: l_TX4 VARCHAR2(2000);
4632: l_TX11 VARCHAR2(2000);
4633: l_TX5 VARCHAR2(2000);
4634: l_TX6 VARCHAR2(2000);
4635: H_SCORES HZ_PARTY_SEARCH.score_list;
4636:
4637: l_score NUMBER;
4638: l_idx NUMBER;
4639: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
4662: defps NUMBER :=0;
4663: defct NUMBER :=0;
4664: defcpt NUMBER :=0;
4665: l_index NUMBER;
4666: l_match_ps_list HZ_PARTY_SEARCH.IDList;
4667: l_cnt NUMBER:=1;
4668:
4669:
4670: BEGIN
4686: l_match_str := ' AND ';
4687: ELSIF p_match_type = 'OR' THEN
4688: l_match_str := ' OR ';
4689: END IF;
4690: 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);
4691: g_score_until_thresh := false;
4692: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4693:
4694: -- ************************************************************
4694: -- ************************************************************
4695: -- STEP 2. Setup of intermedia query strings for Acquisition query
4696: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4697: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4698: 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);
4699:
4700: -- Setup Search Context ID
4701: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4702: x_search_ctx_id := l_search_ctx_id;
4837:
4838: PROCEDURE get_matching_contacts (
4839: p_rule_id IN NUMBER,
4840: p_party_id IN NUMBER,
4841: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
4842: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4843: p_restrict_sql IN VARCHAR2,
4844: p_match_type IN VARCHAR2,
4845: p_dup_contact_id IN NUMBER,
4838: PROCEDURE get_matching_contacts (
4839: p_rule_id IN NUMBER,
4840: p_party_id IN NUMBER,
4841: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
4842: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4843: p_restrict_sql IN VARCHAR2,
4844: p_match_type IN VARCHAR2,
4845: p_dup_contact_id IN NUMBER,
4846: x_search_ctx_id OUT NUMBER,
4866:
4867: l_TX1 VARCHAR2(2000);
4868: l_TX5 VARCHAR2(2000);
4869: l_TX6 VARCHAR2(2000);
4870: H_SCORES HZ_PARTY_SEARCH.score_list;
4871:
4872: l_score NUMBER;
4873: l_idx NUMBER;
4874: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
4894: l_party_max_score NUMBER;
4895: l_ps_max_score NUMBER;
4896: l_contact_max_score NUMBER;
4897: l_cpt_max_score NUMBER;
4898: l_match_contact_list HZ_PARTY_SEARCH.IDList;
4899: l_cnt NUMBER:=1;
4900:
4901:
4902: BEGIN
4918: l_match_str := ' AND ';
4919: ELSIF p_match_type = 'OR' THEN
4920: l_match_str := ' OR ';
4921: END IF;
4922: 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);
4923: g_score_until_thresh := false;
4924: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4925:
4926: -- ************************************************************
4926: -- ************************************************************
4927: -- STEP 2. Setup of intermedia query strings for Acquisition query
4928: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4929: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4930: 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);
4931:
4932: -- Setup Search Context ID
4933: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4934: x_search_ctx_id := l_search_ctx_id;
5065:
5066: PROCEDURE get_matching_contact_points (
5067: p_rule_id IN NUMBER,
5068: p_party_id IN NUMBER,
5069: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5070: p_restrict_sql IN VARCHAR2,
5071: p_match_type IN VARCHAR2,
5072: p_dup_contact_point_id IN NUMBER,
5073: x_search_ctx_id OUT NUMBER,
5097: l_cpt_ps_id NUMBER;
5098: l_cpt_contact_id NUMBER;
5099: l_contact_pt_id NUMBER;
5100: l_cpt_type VARCHAR2(100);
5101: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5102: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5103: H_SCORE HZ_PARTY_SEARCH.IDList;
5104:
5105: cnt NUMBER :=0;
5098: l_cpt_contact_id NUMBER;
5099: l_contact_pt_id NUMBER;
5100: l_cpt_type VARCHAR2(100);
5101: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5102: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5103: H_SCORE HZ_PARTY_SEARCH.IDList;
5104:
5105: cnt NUMBER :=0;
5106: l_party_max_score NUMBER;
5099: l_contact_pt_id NUMBER;
5100: l_cpt_type VARCHAR2(100);
5101: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5102: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5103: H_SCORE HZ_PARTY_SEARCH.IDList;
5104:
5105: cnt NUMBER :=0;
5106: l_party_max_score NUMBER;
5107: l_ps_max_score NUMBER;
5128: l_match_str := ' AND ';
5129: ELSIF p_match_type = 'OR' THEN
5130: l_match_str := ' OR ';
5131: END IF;
5132: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
5133: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
5134: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5135: g_score_until_thresh := false;
5136: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5129: ELSIF p_match_type = 'OR' THEN
5130: l_match_str := ' OR ';
5131: END IF;
5132: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
5133: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
5134: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5135: g_score_until_thresh := false;
5136: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5137:
5137:
5138: -- ************************************************************
5139: -- STEP 2. Setup of intermedia query strings for Acquisition query
5140: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5141: 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);
5142:
5143: -- Setup Search Context ID
5144: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5145: x_search_ctx_id := l_search_ctx_id;
5212:
5213: PROCEDURE get_score_details (
5214: p_rule_id IN NUMBER,
5215: p_party_id IN NUMBER,
5216: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5217: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5218: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5219: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5220: x_search_ctx_id IN OUT NUMBER
5213: PROCEDURE get_score_details (
5214: p_rule_id IN NUMBER,
5215: p_party_id IN NUMBER,
5216: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5217: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5218: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5219: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5220: x_search_ctx_id IN OUT NUMBER
5221: ) IS
5214: p_rule_id IN NUMBER,
5215: p_party_id IN NUMBER,
5216: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5217: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5218: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5219: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5220: x_search_ctx_id IN OUT NUMBER
5221: ) IS
5222:
5215: p_party_id IN NUMBER,
5216: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5217: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5218: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5219: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5220: x_search_ctx_id IN OUT NUMBER
5221: ) IS
5222:
5223: -- Strings to hold the generated Intermedia query strings
5252: l_TX8 VARCHAR2(2000);
5253: l_max_TX8 VARCHAR2(2000);
5254: l_TX6 VARCHAR2(2000);
5255: l_max_TX6 VARCHAR2(2000);
5256: H_SCORES HZ_PARTY_SEARCH.score_list;
5257:
5258: l_score NUMBER;
5259: l_match_idx NUMBER;
5260: l_idx NUMBER;
5423: ************************************************/
5424:
5425: PROCEDURE get_party_for_search (
5426: p_party_id IN NUMBER,
5427: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5428: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5429: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5430: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5431: ) IS
5424:
5425: PROCEDURE get_party_for_search (
5426: p_party_id IN NUMBER,
5427: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5428: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5429: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5430: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5431: ) IS
5432: l_party_id NUMBER;
5425: PROCEDURE get_party_for_search (
5426: p_party_id IN NUMBER,
5427: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5428: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5429: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5430: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5431: ) IS
5432: l_party_id NUMBER;
5433: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5426: p_party_id IN NUMBER,
5427: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5428: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5429: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5430: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5431: ) IS
5432: l_party_id NUMBER;
5433: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5434: l_contact_ids HZ_PARTY_SEARCH.IDList;
5429: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5430: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5431: ) IS
5432: l_party_id NUMBER;
5433: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5434: l_contact_ids HZ_PARTY_SEARCH.IDList;
5435: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5436: ps NUMBER :=1;
5437: cpt NUMBER :=1;
5430: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5431: ) IS
5432: l_party_id NUMBER;
5433: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5434: l_contact_ids HZ_PARTY_SEARCH.IDList;
5435: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5436: ps NUMBER :=1;
5437: cpt NUMBER :=1;
5438: ct NUMBER :=1;
5431: ) IS
5432: l_party_id NUMBER;
5433: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5434: l_contact_ids HZ_PARTY_SEARCH.IDList;
5435: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5436: ps NUMBER :=1;
5437: cpt NUMBER :=1;
5438: ct NUMBER :=1;
5439: l_use_contact_info varchar2(1);
5527: END get_party_for_search;
5528:
5529: PROCEDURE get_search_criteria (
5530: p_party_id IN NUMBER,
5531: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5532: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5533: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5534: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5535: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5528:
5529: PROCEDURE get_search_criteria (
5530: p_party_id IN NUMBER,
5531: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5532: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5533: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5534: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5535: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5536: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5529: PROCEDURE get_search_criteria (
5530: p_party_id IN NUMBER,
5531: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5532: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5533: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5534: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5535: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5536: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5537: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5530: p_party_id IN NUMBER,
5531: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5532: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5533: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5534: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5535: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5536: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5537: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5538: ) IS
5531: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5532: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5533: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5534: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5535: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5536: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5537: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5538: ) IS
5539: BEGIN
5532: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5533: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5534: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5535: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5536: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5537: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5538: ) IS
5539: BEGIN
5540:
5533: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5534: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5535: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5536: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5537: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5538: ) IS
5539: BEGIN
5540:
5541: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5579: ************************************************/
5580:
5581: PROCEDURE get_party_rec (
5582: p_party_id IN NUMBER,
5583: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type
5584: ) IS
5585: l_party_type VARCHAR2(255);
5586: BEGIN
5587:
5638: the party site search record structure
5639: ************************************************/
5640:
5641: PROCEDURE get_party_site_rec (
5642: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5643: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5644: ) IS
5645: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5646: SELECT party_site_id
5639: ************************************************/
5640:
5641: PROCEDURE get_party_site_rec (
5642: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5643: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5644: ) IS
5645: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5646: SELECT party_site_id
5647: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
5694: the contact search record structure
5695: ************************************************/
5696:
5697: PROCEDURE get_contact_rec (
5698: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5699: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5700: ) IS
5701: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5702: SELECT org_contact_id
5695: ************************************************/
5696:
5697: PROCEDURE get_contact_rec (
5698: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5699: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5700: ) IS
5701: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5702: SELECT org_contact_id
5703: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
5750: the contact point search record structure
5751: ************************************************/
5752:
5753: PROCEDURE get_contact_point_rec (
5754: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
5755: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5756: ) IS
5757:
5758: -- Cursor to fetch primary contact points for party
5751: ************************************************/
5752:
5753: PROCEDURE get_contact_point_rec (
5754: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
5755: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5756: ) IS
5757:
5758: -- Cursor to fetch primary contact points for party
5759: CURSOR c_cpts(cp_contact_point_id NUMBER) IS
5809: ************************************************/
5810:
5811: PROCEDURE MAP_PARTY_REC(
5812: p_search_ctx IN BOOLEAN,
5813: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5814: x_entered_max_score OUT NUMBER,
5815: x_stage_rec IN OUT NOCOPY HZ_PARTY_STAGE.party_stage_rec_type
5816: ) IS
5817: tmp VARCHAR2(4000);
5945: ************************************************/
5946:
5947: PROCEDURE MAP_PARTY_SITE_REC(
5948: p_search_ctx IN BOOLEAN,
5949: p_search_list IN HZ_PARTY_SEARCH.party_site_list,
5950: x_entered_max_score OUT NUMBER,
5951: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.party_site_stage_list
5952: ) IS
5953: l_current_max_score NUMBER;
6063: ************************************************/
6064:
6065: PROCEDURE MAP_CONTACT_REC(
6066: p_search_ctx IN BOOLEAN,
6067: p_search_list IN HZ_PARTY_SEARCH.contact_list,
6068: x_entered_max_score OUT NUMBER,
6069: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_stage_list
6070: ) IS
6071: l_current_max_score NUMBER;
6105: ************************************************/
6106:
6107: PROCEDURE MAP_CONTACT_POINT_REC(
6108: p_search_ctx IN BOOLEAN,
6109: p_search_list IN HZ_PARTY_SEARCH.contact_point_list,
6110: x_entered_max_score OUT NUMBER,
6111: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_pt_stage_list
6112: ) IS
6113: l_current_max_score NUMBER;
6211: 2. Contact Point Type is not null for each condition
6212: ************************************************/
6213:
6214: FUNCTION check_prim_cond(
6215: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6216: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6217: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6218: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6219: RETURN BOOLEAN IS
6212: ************************************************/
6213:
6214: FUNCTION check_prim_cond(
6215: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6216: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6217: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6218: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6219: RETURN BOOLEAN IS
6220: BEGIN
6213:
6214: FUNCTION check_prim_cond(
6215: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6216: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6217: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6218: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6219: RETURN BOOLEAN IS
6220: BEGIN
6221: FOR I IN 1..p_contact_point_list.COUNT LOOP
6214: FUNCTION check_prim_cond(
6215: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6216: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6217: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6218: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6219: RETURN BOOLEAN IS
6220: BEGIN
6221: FOR I IN 1..p_contact_point_list.COUNT LOOP
6222: IF p_contact_point_list(I).CONTACT_POINT_TYPE IS NULL AND (
6266: has party site criteria.
6267: ************************************************/
6268:
6269: PROCEDURE check_party_site_cond(
6270: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6271: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6272: x_secondary OUT BOOLEAN,
6273: x_primary OUT BOOLEAN
6274: ) IS
6267: ************************************************/
6268:
6269: PROCEDURE check_party_site_cond(
6270: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6271: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6272: x_secondary OUT BOOLEAN,
6273: x_primary OUT BOOLEAN
6274: ) IS
6275: BEGIN
6321: has contact criteria.
6322: ************************************************/
6323:
6324: PROCEDURE check_contact_cond(
6325: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6326: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6327: x_secondary OUT BOOLEAN,
6328: x_primary OUT BOOLEAN
6329: ) IS
6322: ************************************************/
6323:
6324: PROCEDURE check_contact_cond(
6325: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6326: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6327: x_secondary OUT BOOLEAN,
6328: x_primary OUT BOOLEAN
6329: ) IS
6330: BEGIN
6368: has valid contact point criteria.
6369: ************************************************/
6370:
6371: PROCEDURE check_contact_point_cond(
6372: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6373: x_secondary OUT BOOLEAN,
6374: x_primary OUT BOOLEAN
6375: ) IS
6376: BEGIN