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;
415: PROCEDURE INSERT_PARTY_SCORE (
416: p_party_id IN NUMBER
417: ,p_record_id IN NUMBER
418: ,p_search_ctx_id IN NUMBER
419: ,p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
420: ,p_stage_rec IN HZ_PARTY_STAGE.party_stage_rec_type
421: ,p_table_TX34 VARCHAR2
422: ,p_table_TX35 VARCHAR2
423: ,p_table_TX39 VARCHAR2
568: PROCEDURE INSERT_PARTY_SITES_SCORE (
569: p_party_id IN NUMBER
570: ,p_record_id IN NUMBER
571: ,p_search_ctx_id IN NUMBER
572: ,p_search_rec IN HZ_PARTY_SEARCH.party_site_search_rec_type
573: ,p_stage_rec IN HZ_PARTY_STAGE.party_site_stage_rec_type
574: ,p_idx IN NUMBER) IS
575: l_current_score NUMBER:=0;
576: l_score NUMBER;
584: PROCEDURE INSERT_CONTACTS_SCORE (
585: p_party_id IN NUMBER
586: ,p_record_id IN NUMBER
587: ,p_search_ctx_id IN NUMBER
588: ,p_search_rec IN HZ_PARTY_SEARCH.contact_search_rec_type
589: ,p_stage_rec IN HZ_PARTY_STAGE.contact_stage_rec_type
590: ,p_idx IN NUMBER) IS
591: l_current_score NUMBER:=0;
592: l_score NUMBER;
600: PROCEDURE INSERT_CONTACT_POINTS_SCORE (
601: p_party_id IN NUMBER
602: ,p_record_id IN NUMBER
603: ,p_search_ctx_id IN NUMBER
604: ,p_search_rec IN HZ_PARTY_SEARCH.contact_point_search_rec_type
605: ,p_stage_rec IN HZ_PARTY_STAGE.contact_pt_stage_rec_type
606: ,p_table_TX8 VARCHAR2
607: ,p_table_TX7 VARCHAR2
608: ,p_idx IN NUMBER) IS
652: for the PARTY Entity
653: ************************************************/
654:
655: PROCEDURE POP_PARTY_COND_REC(
656: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
657: ) IS
658: BEGIN
659: NULL ;
660: EXCEPTION
672: for the PARTY_SITES Entity
673: ************************************************/
674:
675: PROCEDURE POP_PARTY_SITES_COND_REC(
676: p_search_list IN HZ_PARTY_SEARCH.party_site_list
677: ) IS
678: BEGIN
679: NULL ;
680: EXCEPTION
692: for the CONTACTS Entity
693: ************************************************/
694:
695: PROCEDURE POP_CONTACTS_COND_REC(
696: p_search_list IN HZ_PARTY_SEARCH.contact_list
697: ) IS
698: BEGIN
699: NULL ;
700: EXCEPTION
712: for the CONTACT_POINTS Entity
713: ************************************************/
714:
715: PROCEDURE POP_CONTACT_POINTS_COND_REC(
716: p_search_list IN HZ_PARTY_SEARCH.contact_point_list
717: ) IS
718: BEGIN
719: NULL ;
720: EXCEPTION
726: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
727: END;
728:
729: PROCEDURE init_score_context (
730: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
731: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
732: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
733: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
734: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
727: END;
728:
729: PROCEDURE init_score_context (
730: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
731: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
732: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
733: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
734: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
735: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
728:
729: PROCEDURE init_score_context (
730: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
731: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
732: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
733: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
734: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
735: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
736: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
729: PROCEDURE init_score_context (
730: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
731: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
732: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
733: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
734: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
735: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
736: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
737: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
730: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
731: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
732: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
733: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
734: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
735: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
736: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
737: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
738: ) IS
731: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
732: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
733: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
734: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
735: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
736: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
737: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
738: ) IS
739: l_dummy NUMBER;
732: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
733: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
734: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
735: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
736: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
737: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
738: ) IS
739: l_dummy NUMBER;
740: BEGIN
733: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
734: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
735: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
736: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
737: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
738: ) IS
739: l_dummy NUMBER;
740: BEGIN
741: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
753: MAP_CONTACT_POINT_REC(FALSE,p_contact_point_list, l_dummy, g_contact_pt_stage_list);
754:
755: END;
756: FUNCTION init_search(
757: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
758: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
759: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
760: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
761: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
754:
755: END;
756: FUNCTION init_search(
757: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
758: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
759: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
760: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
761: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
762: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
755: END;
756: FUNCTION init_search(
757: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
758: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
759: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
760: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
761: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
762: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
763: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
756: FUNCTION init_search(
757: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
758: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
759: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
760: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
761: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
762: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
763: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
764: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
757: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
758: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
759: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
760: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
761: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
762: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
763: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
764: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
765: p_match_type IN VARCHAR2,
758: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
759: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
760: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
761: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
762: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
763: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
764: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
765: p_match_type IN VARCHAR2,
766: x_party_max_score OUT NUMBER,
759: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
760: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
761: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
762: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
763: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
764: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
765: p_match_type IN VARCHAR2,
766: x_party_max_score OUT NUMBER,
767: x_ps_max_score OUT NUMBER,
760: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
761: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
762: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
763: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
764: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
765: p_match_type IN VARCHAR2,
766: x_party_max_score OUT NUMBER,
767: x_ps_max_score OUT NUMBER,
768: x_contact_max_score OUT NUMBER,
772: l_ps_entered_max_score NUMBER:=0;
773: l_ct_entered_max_score NUMBER:=0;
774: l_cpt_entered_max_score NUMBER:=0;
775: vlist vlisttype;
776: maxscore HZ_PARTY_SEARCH.IDList;
777: l_name VARCHAR2(200);
778: l_idx NUMBER;
779: l_num NUMBER;
780: total NUMBER;
2174: p_party_id NUMBER,
2175: p_party_site_id NUMBER,
2176: p_org_contact_id NUMBER,
2177: p_contact_point_id NUMBER)
2178: RETURN HZ_PARTY_SEARCH.score_rec IS
2179: l_score_rec HZ_PARTY_SEARCH.score_rec;
2180: BEGIN
2181: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2182: 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);
2175: p_party_site_id NUMBER,
2176: p_org_contact_id NUMBER,
2177: p_contact_point_id NUMBER)
2178: RETURN HZ_PARTY_SEARCH.score_rec IS
2179: l_score_rec HZ_PARTY_SEARCH.score_rec;
2180: BEGIN
2181: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2182: 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);
2183: 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);
2207: **********************************************************/
2208:
2209: PROCEDURE find_parties (
2210: p_rule_id IN NUMBER,
2211: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2212: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2213: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2214: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2215: p_restrict_sql IN VARCHAR2,
2208:
2209: PROCEDURE find_parties (
2210: p_rule_id IN NUMBER,
2211: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2212: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2213: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2214: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2215: p_restrict_sql IN VARCHAR2,
2216: p_match_type IN VARCHAR2,
2209: PROCEDURE find_parties (
2210: p_rule_id IN NUMBER,
2211: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2212: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2213: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2214: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2215: p_restrict_sql IN VARCHAR2,
2216: p_match_type IN VARCHAR2,
2217: p_search_merged IN VARCHAR2,
2210: p_rule_id IN NUMBER,
2211: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2212: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2213: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2214: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2215: p_restrict_sql IN VARCHAR2,
2216: p_match_type IN VARCHAR2,
2217: p_search_merged IN VARCHAR2,
2218: p_dup_party_id IN NUMBER,
2250: l_TX34 VARCHAR2(2000);
2251: l_TX45 VARCHAR2(2000);
2252: l_TX8 VARCHAR2(2000);
2253: l_TX7 VARCHAR2(2000);
2254: H_SCORES HZ_PARTY_SEARCH.score_list;
2255: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2256: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2257:
2258: l_score NUMBER;
2251: l_TX45 VARCHAR2(2000);
2252: l_TX8 VARCHAR2(2000);
2253: l_TX7 VARCHAR2(2000);
2254: H_SCORES HZ_PARTY_SEARCH.score_list;
2255: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2256: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2257:
2258: l_score NUMBER;
2259: l_idx NUMBER;
2252: l_TX8 VARCHAR2(2000);
2253: l_TX7 VARCHAR2(2000);
2254: H_SCORES HZ_PARTY_SEARCH.score_list;
2255: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2256: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2257:
2258: l_score NUMBER;
2259: l_idx NUMBER;
2260: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
2446: /** Private procedure to acquire and score at party site level ***/
2447: 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
2448: l_party_id_idx NUMBER:=1;
2449: l_ctx_id NUMBER;
2450: h_ps_id HZ_PARTY_SEARCH.IDList;
2451: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2452: h_ps_score HZ_PARTY_SEARCH.IDList;
2453: detcnt NUMBER := 1;
2454: BEGIN
2447: 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
2448: l_party_id_idx NUMBER:=1;
2449: l_ctx_id NUMBER;
2450: h_ps_id HZ_PARTY_SEARCH.IDList;
2451: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2452: h_ps_score HZ_PARTY_SEARCH.IDList;
2453: detcnt NUMBER := 1;
2454: BEGIN
2455: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2448: l_party_id_idx NUMBER:=1;
2449: l_ctx_id NUMBER;
2450: h_ps_id HZ_PARTY_SEARCH.IDList;
2451: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2452: h_ps_score HZ_PARTY_SEARCH.IDList;
2453: detcnt NUMBER := 1;
2454: BEGIN
2455: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2456: 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);
2568: /** Private procedure to acquire and score at contact point level ***/
2569: 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
2570: l_party_id_idx NUMBER:=1;
2571: l_ctx_id NUMBER;
2572: h_ct_id HZ_PARTY_SEARCH.IDList;
2573: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2574: h_ct_score HZ_PARTY_SEARCH.IDList;
2575: detcnt NUMBER := 1;
2576: BEGIN
2569: 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
2570: l_party_id_idx NUMBER:=1;
2571: l_ctx_id NUMBER;
2572: h_ct_id HZ_PARTY_SEARCH.IDList;
2573: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2574: h_ct_score HZ_PARTY_SEARCH.IDList;
2575: detcnt NUMBER := 1;
2576: BEGIN
2577: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2570: l_party_id_idx NUMBER:=1;
2571: l_ctx_id NUMBER;
2572: h_ct_id HZ_PARTY_SEARCH.IDList;
2573: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2574: h_ct_score HZ_PARTY_SEARCH.IDList;
2575: detcnt NUMBER := 1;
2576: BEGIN
2577: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2578: 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);
2677: /** Private procedure to acquire and score at contact point level ***/
2678: 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
2679: l_party_id_idx NUMBER:=1;
2680: l_ctx_id NUMBER;
2681: h_cpt_id HZ_PARTY_SEARCH.IDList;
2682: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2683: h_cpt_score HZ_PARTY_SEARCH.IDList;
2684: detcnt NUMBER := 1;
2685: l_cpt_flag VARCHAR2(1) := 'N';
2678: 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
2679: l_party_id_idx NUMBER:=1;
2680: l_ctx_id NUMBER;
2681: h_cpt_id HZ_PARTY_SEARCH.IDList;
2682: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2683: h_cpt_score HZ_PARTY_SEARCH.IDList;
2684: detcnt NUMBER := 1;
2685: l_cpt_flag VARCHAR2(1) := 'N';
2686: is_a_match VARCHAR2(1) := 'Y';
2679: l_party_id_idx NUMBER:=1;
2680: l_ctx_id NUMBER;
2681: h_cpt_id HZ_PARTY_SEARCH.IDList;
2682: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2683: h_cpt_score HZ_PARTY_SEARCH.IDList;
2684: detcnt NUMBER := 1;
2685: l_cpt_flag VARCHAR2(1) := 'N';
2686: is_a_match VARCHAR2(1) := 'Y';
2687: BEGIN
3166: END find_parties;
3167:
3168: PROCEDURE find_persons (
3169: p_rule_id IN NUMBER,
3170: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3171: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3172: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3173: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3174: p_restrict_sql IN VARCHAR2,
3167:
3168: PROCEDURE find_persons (
3169: p_rule_id IN NUMBER,
3170: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3171: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3172: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3173: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3174: p_restrict_sql IN VARCHAR2,
3175: p_match_type IN VARCHAR2,
3168: PROCEDURE find_persons (
3169: p_rule_id IN NUMBER,
3170: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3171: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3172: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3173: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3174: p_restrict_sql IN VARCHAR2,
3175: p_match_type IN VARCHAR2,
3176: p_search_merged IN VARCHAR2,
3169: p_rule_id IN NUMBER,
3170: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3171: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3172: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3173: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3174: p_restrict_sql IN VARCHAR2,
3175: p_match_type IN VARCHAR2,
3176: p_search_merged IN VARCHAR2,
3177: p_dup_party_id IN NUMBER,
3209: l_TX34 VARCHAR2(2000);
3210: l_TX45 VARCHAR2(2000);
3211: l_TX8 VARCHAR2(2000);
3212: l_TX7 VARCHAR2(2000);
3213: H_SCORES HZ_PARTY_SEARCH.score_list;
3214: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3215: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3216:
3217: l_score NUMBER;
3210: l_TX45 VARCHAR2(2000);
3211: l_TX8 VARCHAR2(2000);
3212: l_TX7 VARCHAR2(2000);
3213: H_SCORES HZ_PARTY_SEARCH.score_list;
3214: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3215: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3216:
3217: l_score NUMBER;
3218: l_idx NUMBER;
3211: l_TX8 VARCHAR2(2000);
3212: l_TX7 VARCHAR2(2000);
3213: H_SCORES HZ_PARTY_SEARCH.score_list;
3214: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3215: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3216:
3217: l_score NUMBER;
3218: l_idx NUMBER;
3219: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
3453: /** Private procedure to acquire and score at party site level ***/
3454: 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
3455: l_party_id_idx NUMBER:=1;
3456: l_ctx_id NUMBER;
3457: h_ps_id HZ_PARTY_SEARCH.IDList;
3458: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3459: h_ps_score HZ_PARTY_SEARCH.IDList;
3460: detcnt NUMBER := 1;
3461: l_person_id NUMBER;
3454: 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
3455: l_party_id_idx NUMBER:=1;
3456: l_ctx_id NUMBER;
3457: h_ps_id HZ_PARTY_SEARCH.IDList;
3458: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3459: h_ps_score HZ_PARTY_SEARCH.IDList;
3460: detcnt NUMBER := 1;
3461: l_person_id NUMBER;
3462: BEGIN
3455: l_party_id_idx NUMBER:=1;
3456: l_ctx_id NUMBER;
3457: h_ps_id HZ_PARTY_SEARCH.IDList;
3458: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3459: h_ps_score HZ_PARTY_SEARCH.IDList;
3460: detcnt NUMBER := 1;
3461: l_person_id NUMBER;
3462: BEGIN
3463: SAVEPOINT eval_start;
3552: /** Private procedure to acquire and score at party site level ***/
3553: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3554: l_party_id_idx NUMBER:=1;
3555: l_ctx_id NUMBER;
3556: h_ct_id HZ_PARTY_SEARCH.IDList;
3557: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3558: h_ct_score HZ_PARTY_SEARCH.IDList;
3559: detcnt NUMBER := 1;
3560: l_person_id NUMBER;
3553: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3554: l_party_id_idx NUMBER:=1;
3555: l_ctx_id NUMBER;
3556: h_ct_id HZ_PARTY_SEARCH.IDList;
3557: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3558: h_ct_score HZ_PARTY_SEARCH.IDList;
3559: detcnt NUMBER := 1;
3560: l_person_id NUMBER;
3561: BEGIN
3554: l_party_id_idx NUMBER:=1;
3555: l_ctx_id NUMBER;
3556: h_ct_id HZ_PARTY_SEARCH.IDList;
3557: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3558: h_ct_score HZ_PARTY_SEARCH.IDList;
3559: detcnt NUMBER := 1;
3560: l_person_id NUMBER;
3561: BEGIN
3562: SAVEPOINT eval_start;
3610: /** Private procedure to acquire and score at contact point level ***/
3611: 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
3612: l_party_id_idx NUMBER:=1;
3613: l_ctx_id NUMBER;
3614: h_cpt_id HZ_PARTY_SEARCH.IDList;
3615: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3616: h_cpt_score HZ_PARTY_SEARCH.IDList;
3617: detcnt NUMBER := 1;
3618: l_person_id NUMBER;
3611: 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
3612: l_party_id_idx NUMBER:=1;
3613: l_ctx_id NUMBER;
3614: h_cpt_id HZ_PARTY_SEARCH.IDList;
3615: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3616: h_cpt_score HZ_PARTY_SEARCH.IDList;
3617: detcnt NUMBER := 1;
3618: l_person_id NUMBER;
3619: is_a_match VARCHAR2(1) := 'Y';
3612: l_party_id_idx NUMBER:=1;
3613: l_ctx_id NUMBER;
3614: h_cpt_id HZ_PARTY_SEARCH.IDList;
3615: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3616: h_cpt_score HZ_PARTY_SEARCH.IDList;
3617: detcnt NUMBER := 1;
3618: l_person_id NUMBER;
3619: is_a_match VARCHAR2(1) := 'Y';
3620: l_cpt_flag VARCHAR2(1) := 'N';
4023: END find_persons;
4024:
4025: PROCEDURE find_persons (
4026: p_rule_id IN NUMBER,
4027: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4028: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4029: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4030: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4031: p_restrict_sql IN VARCHAR2,
4024:
4025: PROCEDURE find_persons (
4026: p_rule_id IN NUMBER,
4027: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4028: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4029: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4030: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4031: p_restrict_sql IN VARCHAR2,
4032: p_match_type IN VARCHAR2,
4025: PROCEDURE find_persons (
4026: p_rule_id IN NUMBER,
4027: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4028: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4029: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4030: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4031: p_restrict_sql IN VARCHAR2,
4032: p_match_type IN VARCHAR2,
4033: p_search_merged IN VARCHAR2,
4026: p_rule_id IN NUMBER,
4027: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4028: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4029: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4030: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4031: p_restrict_sql IN VARCHAR2,
4032: p_match_type IN VARCHAR2,
4033: p_search_merged IN VARCHAR2,
4034: p_ins_details IN VARCHAR2,
4040: 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);
4041: END;
4042: PROCEDURE find_party_details (
4043: p_rule_id IN NUMBER,
4044: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4045: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4046: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4047: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4048: p_restrict_sql IN VARCHAR2,
4041: END;
4042: PROCEDURE find_party_details (
4043: p_rule_id IN NUMBER,
4044: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4045: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4046: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4047: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4048: p_restrict_sql IN VARCHAR2,
4049: p_match_type IN VARCHAR2,
4042: PROCEDURE find_party_details (
4043: p_rule_id IN NUMBER,
4044: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4045: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4046: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4047: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4048: p_restrict_sql IN VARCHAR2,
4049: p_match_type IN VARCHAR2,
4050: p_search_merged IN VARCHAR2,
4043: p_rule_id IN NUMBER,
4044: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4045: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4046: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4047: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4048: p_restrict_sql IN VARCHAR2,
4049: p_match_type IN VARCHAR2,
4050: p_search_merged IN VARCHAR2,
4051: x_search_ctx_id OUT NUMBER,
4100: x_dup_set_id OUT NUMBER,
4101: x_search_ctx_id OUT NUMBER,
4102: x_num_matches OUT NUMBER
4103: ) IS
4104: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4105: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4106: l_contact_list HZ_PARTY_SEARCH.contact_list;
4107: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4108: l_match_idx NUMBER;
4101: x_search_ctx_id OUT NUMBER,
4102: x_num_matches OUT NUMBER
4103: ) IS
4104: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4105: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4106: l_contact_list HZ_PARTY_SEARCH.contact_list;
4107: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4108: l_match_idx NUMBER;
4109:
4102: x_num_matches OUT NUMBER
4103: ) IS
4104: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4105: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4106: l_contact_list HZ_PARTY_SEARCH.contact_list;
4107: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4108: l_match_idx NUMBER;
4109:
4110: --Fix for bug 4417124
4103: ) IS
4104: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4105: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4106: l_contact_list HZ_PARTY_SEARCH.contact_list;
4107: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4108: l_match_idx NUMBER;
4109:
4110: --Fix for bug 4417124
4111: l_use_contact_addr_info BOOLEAN := TRUE;
4216: x_search_ctx_id OUT NUMBER,
4217: x_num_matches OUT NUMBER
4218: ) IS
4219:
4220: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4221: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4222: l_contact_list HZ_PARTY_SEARCH.contact_list;
4223: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4224: contact_point_ids HZ_PARTY_SEARCH.IDList;
4217: x_num_matches OUT NUMBER
4218: ) IS
4219:
4220: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4221: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4222: l_contact_list HZ_PARTY_SEARCH.contact_list;
4223: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4224: contact_point_ids HZ_PARTY_SEARCH.IDList;
4225: p_party_site_list HZ_PARTY_SEARCH.IDList;
4218: ) IS
4219:
4220: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4221: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4222: l_contact_list HZ_PARTY_SEARCH.contact_list;
4223: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4224: contact_point_ids HZ_PARTY_SEARCH.IDList;
4225: p_party_site_list HZ_PARTY_SEARCH.IDList;
4226: p_contact_ids HZ_PARTY_SEARCH.IDList;
4219:
4220: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4221: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4222: l_contact_list HZ_PARTY_SEARCH.contact_list;
4223: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4224: contact_point_ids HZ_PARTY_SEARCH.IDList;
4225: p_party_site_list HZ_PARTY_SEARCH.IDList;
4226: p_contact_ids HZ_PARTY_SEARCH.IDList;
4227: l_match_idx NUMBER;
4220: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4221: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4222: l_contact_list HZ_PARTY_SEARCH.contact_list;
4223: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4224: contact_point_ids HZ_PARTY_SEARCH.IDList;
4225: p_party_site_list HZ_PARTY_SEARCH.IDList;
4226: p_contact_ids HZ_PARTY_SEARCH.IDList;
4227: l_match_idx NUMBER;
4228: cursor get_cpts_for_party_sites is select contact_point_id
4221: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4222: l_contact_list HZ_PARTY_SEARCH.contact_list;
4223: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4224: contact_point_ids HZ_PARTY_SEARCH.IDList;
4225: p_party_site_list HZ_PARTY_SEARCH.IDList;
4226: p_contact_ids HZ_PARTY_SEARCH.IDList;
4227: l_match_idx NUMBER;
4228: cursor get_cpts_for_party_sites is select contact_point_id
4229: from hz_contact_points
4222: l_contact_list HZ_PARTY_SEARCH.contact_list;
4223: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4224: contact_point_ids HZ_PARTY_SEARCH.IDList;
4225: p_party_site_list HZ_PARTY_SEARCH.IDList;
4226: p_contact_ids HZ_PARTY_SEARCH.IDList;
4227: l_match_idx NUMBER;
4228: cursor get_cpts_for_party_sites is select contact_point_id
4229: from hz_contact_points
4230: where owner_table_name = 'HZ_PARTY_SITES'
4251:
4252: get_search_criteria (
4253: null,
4254: p_party_site_list,
4255: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4256: contact_point_ids,
4257: l_party_search_rec,
4258: l_party_site_list,
4259: l_contact_list,
4301: x_search_ctx_id OUT NUMBER,
4302: x_num_matches OUT NUMBER
4303: ) IS
4304:
4305: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4306: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4307: l_contact_list HZ_PARTY_SEARCH.contact_list;
4308: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4309: contact_point_ids HZ_PARTY_SEARCH.IDList;
4302: x_num_matches OUT NUMBER
4303: ) IS
4304:
4305: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4306: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4307: l_contact_list HZ_PARTY_SEARCH.contact_list;
4308: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4309: contact_point_ids HZ_PARTY_SEARCH.IDList;
4310: p_party_site_list HZ_PARTY_SEARCH.IDList;
4303: ) IS
4304:
4305: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4306: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4307: l_contact_list HZ_PARTY_SEARCH.contact_list;
4308: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4309: contact_point_ids HZ_PARTY_SEARCH.IDList;
4310: p_party_site_list HZ_PARTY_SEARCH.IDList;
4311: p_contact_ids HZ_PARTY_SEARCH.IDList;
4304:
4305: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4306: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4307: l_contact_list HZ_PARTY_SEARCH.contact_list;
4308: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4309: contact_point_ids HZ_PARTY_SEARCH.IDList;
4310: p_party_site_list HZ_PARTY_SEARCH.IDList;
4311: p_contact_ids HZ_PARTY_SEARCH.IDList;
4312: l_match_idx NUMBER;
4305: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4306: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4307: l_contact_list HZ_PARTY_SEARCH.contact_list;
4308: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4309: contact_point_ids HZ_PARTY_SEARCH.IDList;
4310: p_party_site_list HZ_PARTY_SEARCH.IDList;
4311: p_contact_ids HZ_PARTY_SEARCH.IDList;
4312: l_match_idx NUMBER;
4313: cursor get_cpt_for_contact_id is select contact_point_id
4306: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4307: l_contact_list HZ_PARTY_SEARCH.contact_list;
4308: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4309: contact_point_ids HZ_PARTY_SEARCH.IDList;
4310: p_party_site_list HZ_PARTY_SEARCH.IDList;
4311: p_contact_ids HZ_PARTY_SEARCH.IDList;
4312: l_match_idx NUMBER;
4313: cursor get_cpt_for_contact_id is select contact_point_id
4314: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4307: l_contact_list HZ_PARTY_SEARCH.contact_list;
4308: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4309: contact_point_ids HZ_PARTY_SEARCH.IDList;
4310: p_party_site_list HZ_PARTY_SEARCH.IDList;
4311: p_contact_ids HZ_PARTY_SEARCH.IDList;
4312: l_match_idx NUMBER;
4313: cursor get_cpt_for_contact_id is select contact_point_id
4314: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4315: where a.party_relationship_id = b.relationship_id
4338: CLOSE get_cpt_for_contact_id;
4339:
4340: get_search_criteria (
4341: null,
4342: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4343: p_contact_ids,
4344: contact_point_ids,
4345: l_party_search_rec,
4346: l_party_site_list,
4389: p_match_type IN VARCHAR2,
4390: x_search_ctx_id OUT NUMBER,
4391: x_num_matches OUT NUMBER
4392: ) IS
4393: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4394: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4395: l_contact_list HZ_PARTY_SEARCH.contact_list;
4396: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4397: contact_point_ids HZ_PARTY_SEARCH.IDList;
4390: x_search_ctx_id OUT NUMBER,
4391: x_num_matches OUT NUMBER
4392: ) IS
4393: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4394: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4395: l_contact_list HZ_PARTY_SEARCH.contact_list;
4396: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4397: contact_point_ids HZ_PARTY_SEARCH.IDList;
4398: p_party_site_list HZ_PARTY_SEARCH.IDList;
4391: x_num_matches OUT NUMBER
4392: ) IS
4393: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4394: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4395: l_contact_list HZ_PARTY_SEARCH.contact_list;
4396: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4397: contact_point_ids HZ_PARTY_SEARCH.IDList;
4398: p_party_site_list HZ_PARTY_SEARCH.IDList;
4399: p_contact_ids HZ_PARTY_SEARCH.IDList;
4392: ) IS
4393: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4394: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4395: l_contact_list HZ_PARTY_SEARCH.contact_list;
4396: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4397: contact_point_ids HZ_PARTY_SEARCH.IDList;
4398: p_party_site_list HZ_PARTY_SEARCH.IDList;
4399: p_contact_ids HZ_PARTY_SEARCH.IDList;
4400: l_match_idx NUMBER;
4393: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4394: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4395: l_contact_list HZ_PARTY_SEARCH.contact_list;
4396: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4397: contact_point_ids HZ_PARTY_SEARCH.IDList;
4398: p_party_site_list HZ_PARTY_SEARCH.IDList;
4399: p_contact_ids HZ_PARTY_SEARCH.IDList;
4400: l_match_idx NUMBER;
4401:
4394: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4395: l_contact_list HZ_PARTY_SEARCH.contact_list;
4396: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4397: contact_point_ids HZ_PARTY_SEARCH.IDList;
4398: p_party_site_list HZ_PARTY_SEARCH.IDList;
4399: p_contact_ids HZ_PARTY_SEARCH.IDList;
4400: l_match_idx NUMBER;
4401:
4402: BEGIN
4395: l_contact_list HZ_PARTY_SEARCH.contact_list;
4396: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4397: contact_point_ids HZ_PARTY_SEARCH.IDList;
4398: p_party_site_list HZ_PARTY_SEARCH.IDList;
4399: p_contact_ids HZ_PARTY_SEARCH.IDList;
4400: l_match_idx NUMBER;
4401:
4402: BEGIN
4403: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4412: END IF;
4413: contact_point_ids(1) := p_contact_point_id;
4414: get_search_criteria (
4415: null,
4416: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4417: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4418: contact_point_ids,
4419: l_party_search_rec,
4420: l_party_site_list,
4413: contact_point_ids(1) := p_contact_point_id;
4414: get_search_criteria (
4415: null,
4416: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4417: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4418: contact_point_ids,
4419: l_party_search_rec,
4420: l_party_site_list,
4421: l_contact_list,
4566: ) IS
4567: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4568: l_match_idx NUMBER;
4569: AttrVals AttrList;
4570: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4571: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4572: l_contact_list HZ_PARTY_SEARCH.contact_list;
4573: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4574: l_dup_set_id NUMBER;
4567: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4568: l_match_idx NUMBER;
4569: AttrVals AttrList;
4570: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4571: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4572: l_contact_list HZ_PARTY_SEARCH.contact_list;
4573: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4574: l_dup_set_id NUMBER;
4575: l_idx NUMBER;
4568: l_match_idx NUMBER;
4569: AttrVals AttrList;
4570: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4571: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4572: l_contact_list HZ_PARTY_SEARCH.contact_list;
4573: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4574: l_dup_set_id NUMBER;
4575: l_idx NUMBER;
4576: l_cpt_type VARCHAR2(255);
4569: AttrVals AttrList;
4570: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4571: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4572: l_contact_list HZ_PARTY_SEARCH.contact_list;
4573: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4574: l_dup_set_id NUMBER;
4575: l_idx NUMBER;
4576: l_cpt_type VARCHAR2(255);
4577: FIRST BOOLEAN := TRUE;
4748:
4749: PROCEDURE get_matching_party_sites (
4750: p_rule_id IN NUMBER,
4751: p_party_id IN NUMBER,
4752: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4753: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4754: p_restrict_sql IN VARCHAR2,
4755: p_match_type IN VARCHAR2,
4756: p_dup_party_site_id IN NUMBER,
4749: PROCEDURE get_matching_party_sites (
4750: p_rule_id IN NUMBER,
4751: p_party_id IN NUMBER,
4752: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4753: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4754: p_restrict_sql IN VARCHAR2,
4755: p_match_type IN VARCHAR2,
4756: p_dup_party_site_id IN NUMBER,
4757: x_search_ctx_id OUT NUMBER,
4776: l_search_ctx_id NUMBER; -- Generated Search Context ID
4777:
4778: l_TX8 VARCHAR2(2000);
4779: l_TX7 VARCHAR2(2000);
4780: H_SCORES HZ_PARTY_SEARCH.score_list;
4781:
4782: l_score NUMBER;
4783: l_idx NUMBER;
4784: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
4807: defps NUMBER :=0;
4808: defct NUMBER :=0;
4809: defcpt NUMBER :=0;
4810: l_index NUMBER;
4811: l_match_ps_list HZ_PARTY_SEARCH.IDList;
4812: l_cnt NUMBER:=1;
4813:
4814:
4815: BEGIN
4831: l_match_str := ' AND ';
4832: ELSIF p_match_type = 'OR' THEN
4833: l_match_str := ' OR ';
4834: END IF;
4835: 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);
4836: g_score_until_thresh := false;
4837: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4838:
4839: -- ************************************************************
4839: -- ************************************************************
4840: -- STEP 2. Setup of intermedia query strings for Acquisition query
4841: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4842: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4843: 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);
4844:
4845: -- Setup Search Context ID
4846: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4847: x_search_ctx_id := l_search_ctx_id;
4982:
4983: PROCEDURE get_matching_contacts (
4984: p_rule_id IN NUMBER,
4985: p_party_id IN NUMBER,
4986: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
4987: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4988: p_restrict_sql IN VARCHAR2,
4989: p_match_type IN VARCHAR2,
4990: p_dup_contact_id IN NUMBER,
4983: PROCEDURE get_matching_contacts (
4984: p_rule_id IN NUMBER,
4985: p_party_id IN NUMBER,
4986: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
4987: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4988: p_restrict_sql IN VARCHAR2,
4989: p_match_type IN VARCHAR2,
4990: p_dup_contact_id IN NUMBER,
4991: x_search_ctx_id OUT NUMBER,
5010: l_search_ctx_id NUMBER; -- Generated Search Context ID
5011:
5012: l_TX8 VARCHAR2(2000);
5013: l_TX7 VARCHAR2(2000);
5014: H_SCORES HZ_PARTY_SEARCH.score_list;
5015:
5016: l_score NUMBER;
5017: l_idx NUMBER;
5018: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
5038: l_party_max_score NUMBER;
5039: l_ps_max_score NUMBER;
5040: l_contact_max_score NUMBER;
5041: l_cpt_max_score NUMBER;
5042: l_match_contact_list HZ_PARTY_SEARCH.IDList;
5043: l_cnt NUMBER:=1;
5044:
5045:
5046: BEGIN
5062: l_match_str := ' AND ';
5063: ELSIF p_match_type = 'OR' THEN
5064: l_match_str := ' OR ';
5065: END IF;
5066: 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);
5067: g_score_until_thresh := false;
5068: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5069:
5070: -- ************************************************************
5070: -- ************************************************************
5071: -- STEP 2. Setup of intermedia query strings for Acquisition query
5072: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5073: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5074: 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);
5075:
5076: -- Setup Search Context ID
5077: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5078: x_search_ctx_id := l_search_ctx_id;
5209:
5210: PROCEDURE get_matching_contact_points (
5211: p_rule_id IN NUMBER,
5212: p_party_id IN NUMBER,
5213: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5214: p_restrict_sql IN VARCHAR2,
5215: p_match_type IN VARCHAR2,
5216: p_dup_contact_point_id IN NUMBER,
5217: x_search_ctx_id OUT NUMBER,
5240: l_cpt_ps_id NUMBER;
5241: l_cpt_contact_id NUMBER;
5242: l_contact_pt_id NUMBER;
5243: l_cpt_type VARCHAR2(100);
5244: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5245: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5246: H_SCORE HZ_PARTY_SEARCH.IDList;
5247:
5248: cnt NUMBER :=0;
5241: l_cpt_contact_id NUMBER;
5242: l_contact_pt_id NUMBER;
5243: l_cpt_type VARCHAR2(100);
5244: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5245: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5246: H_SCORE HZ_PARTY_SEARCH.IDList;
5247:
5248: cnt NUMBER :=0;
5249: l_party_max_score NUMBER;
5242: l_contact_pt_id NUMBER;
5243: l_cpt_type VARCHAR2(100);
5244: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5245: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5246: H_SCORE HZ_PARTY_SEARCH.IDList;
5247:
5248: cnt NUMBER :=0;
5249: l_party_max_score NUMBER;
5250: l_ps_max_score NUMBER;
5271: l_match_str := ' AND ';
5272: ELSIF p_match_type = 'OR' THEN
5273: l_match_str := ' OR ';
5274: END IF;
5275: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
5276: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
5277: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5278: g_score_until_thresh := false;
5279: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5272: ELSIF p_match_type = 'OR' THEN
5273: l_match_str := ' OR ';
5274: END IF;
5275: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
5276: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
5277: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5278: g_score_until_thresh := false;
5279: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5280:
5280:
5281: -- ************************************************************
5282: -- STEP 2. Setup of intermedia query strings for Acquisition query
5283: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5284: 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);
5285:
5286: -- Setup Search Context ID
5287: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5288: x_search_ctx_id := l_search_ctx_id;
5355:
5356: PROCEDURE get_score_details (
5357: p_rule_id IN NUMBER,
5358: p_party_id IN NUMBER,
5359: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5360: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5361: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5362: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5363: x_search_ctx_id IN OUT NUMBER
5356: PROCEDURE get_score_details (
5357: p_rule_id IN NUMBER,
5358: p_party_id IN NUMBER,
5359: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5360: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5361: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5362: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5363: x_search_ctx_id IN OUT NUMBER
5364: ) IS
5357: p_rule_id IN NUMBER,
5358: p_party_id IN NUMBER,
5359: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5360: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5361: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5362: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5363: x_search_ctx_id IN OUT NUMBER
5364: ) IS
5365:
5358: p_party_id IN NUMBER,
5359: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5360: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5361: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5362: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5363: x_search_ctx_id IN OUT NUMBER
5364: ) IS
5365:
5366: -- Strings to hold the generated Intermedia query strings
5393: l_TX8 VARCHAR2(2000);
5394: l_max_TX8 VARCHAR2(2000);
5395: l_TX7 VARCHAR2(2000);
5396: l_max_TX7 VARCHAR2(2000);
5397: H_SCORES HZ_PARTY_SEARCH.score_list;
5398:
5399: l_score NUMBER;
5400: l_match_idx NUMBER;
5401: l_idx NUMBER;
5563: ************************************************/
5564:
5565: PROCEDURE get_party_for_search (
5566: p_party_id IN NUMBER,
5567: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5568: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5569: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5570: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5571: ) IS
5564:
5565: PROCEDURE get_party_for_search (
5566: p_party_id IN NUMBER,
5567: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5568: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5569: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5570: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5571: ) IS
5572: l_party_id NUMBER;
5565: PROCEDURE get_party_for_search (
5566: p_party_id IN NUMBER,
5567: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5568: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5569: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5570: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5571: ) IS
5572: l_party_id NUMBER;
5573: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5566: p_party_id IN NUMBER,
5567: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5568: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5569: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5570: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5571: ) IS
5572: l_party_id NUMBER;
5573: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5574: l_contact_ids HZ_PARTY_SEARCH.IDList;
5569: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5570: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5571: ) IS
5572: l_party_id NUMBER;
5573: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5574: l_contact_ids HZ_PARTY_SEARCH.IDList;
5575: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5576: ps NUMBER :=1;
5577: cpt NUMBER :=1;
5570: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5571: ) IS
5572: l_party_id NUMBER;
5573: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5574: l_contact_ids HZ_PARTY_SEARCH.IDList;
5575: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5576: ps NUMBER :=1;
5577: cpt NUMBER :=1;
5578: ct NUMBER :=1;
5571: ) IS
5572: l_party_id NUMBER;
5573: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5574: l_contact_ids HZ_PARTY_SEARCH.IDList;
5575: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5576: ps NUMBER :=1;
5577: cpt NUMBER :=1;
5578: ct NUMBER :=1;
5579: l_use_contact_info varchar2(1);
5632: END get_party_for_search;
5633:
5634: PROCEDURE get_search_criteria (
5635: p_party_id IN NUMBER,
5636: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5637: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5638: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5639: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5640: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5633:
5634: PROCEDURE get_search_criteria (
5635: p_party_id IN NUMBER,
5636: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5637: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5638: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5639: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5640: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5641: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5634: PROCEDURE get_search_criteria (
5635: p_party_id IN NUMBER,
5636: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5637: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5638: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5639: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5640: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5641: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5642: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5635: p_party_id IN NUMBER,
5636: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5637: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5638: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5639: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5640: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5641: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5642: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5643: ) IS
5636: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5637: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5638: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5639: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5640: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5641: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5642: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5643: ) IS
5644: BEGIN
5637: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5638: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5639: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5640: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5641: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5642: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5643: ) IS
5644: BEGIN
5645:
5638: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5639: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5640: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5641: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5642: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5643: ) IS
5644: BEGIN
5645:
5646: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5678: ************************************************/
5679:
5680: PROCEDURE get_party_rec (
5681: p_party_id IN NUMBER,
5682: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type
5683: ) IS
5684: l_party_type VARCHAR2(255);
5685: BEGIN
5686:
5743: the party site search record structure
5744: ************************************************/
5745:
5746: PROCEDURE get_party_site_rec (
5747: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5748: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5749: ) IS
5750: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5751: SELECT party_site_id
5744: ************************************************/
5745:
5746: PROCEDURE get_party_site_rec (
5747: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5748: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5749: ) IS
5750: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5751: SELECT party_site_id
5752: FROM HZ_PARTY_SITES, HZ_LOCATIONS
5795: the contact search record structure
5796: ************************************************/
5797:
5798: PROCEDURE get_contact_rec (
5799: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5800: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5801: ) IS
5802: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5803: SELECT org_contact_id
5796: ************************************************/
5797:
5798: PROCEDURE get_contact_rec (
5799: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5800: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5801: ) IS
5802: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5803: SELECT org_contact_id
5804: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
5851: the contact point search record structure
5852: ************************************************/
5853:
5854: PROCEDURE get_contact_point_rec (
5855: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
5856: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5857: ) IS
5858:
5859: -- Cursor to fetch primary contact points for party
5852: ************************************************/
5853:
5854: PROCEDURE get_contact_point_rec (
5855: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
5856: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5857: ) IS
5858:
5859: -- Cursor to fetch primary contact points for party
5860: CURSOR c_cpts(cp_contact_point_id NUMBER) IS
5908: ************************************************/
5909:
5910: PROCEDURE MAP_PARTY_REC(
5911: p_search_ctx IN BOOLEAN,
5912: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5913: x_entered_max_score OUT NUMBER,
5914: x_stage_rec IN OUT NOCOPY HZ_PARTY_STAGE.party_stage_rec_type
5915: ) IS
5916: tmp VARCHAR2(4000);
6102: ************************************************/
6103:
6104: PROCEDURE MAP_PARTY_SITE_REC(
6105: p_search_ctx IN BOOLEAN,
6106: p_search_list IN HZ_PARTY_SEARCH.party_site_list,
6107: x_entered_max_score OUT NUMBER,
6108: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.party_site_stage_list
6109: ) IS
6110: l_current_max_score NUMBER;
6145: ************************************************/
6146:
6147: PROCEDURE MAP_CONTACT_REC(
6148: p_search_ctx IN BOOLEAN,
6149: p_search_list IN HZ_PARTY_SEARCH.contact_list,
6150: x_entered_max_score OUT NUMBER,
6151: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_stage_list
6152: ) IS
6153: l_current_max_score NUMBER;
6187: ************************************************/
6188:
6189: PROCEDURE MAP_CONTACT_POINT_REC(
6190: p_search_ctx IN BOOLEAN,
6191: p_search_list IN HZ_PARTY_SEARCH.contact_point_list,
6192: x_entered_max_score OUT NUMBER,
6193: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_pt_stage_list
6194: ) IS
6195: l_current_max_score NUMBER;
6291: 2. Contact Point Type is not null for each condition
6292: ************************************************/
6293:
6294: FUNCTION check_prim_cond(
6295: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6296: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6297: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6298: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6299: RETURN BOOLEAN IS
6292: ************************************************/
6293:
6294: FUNCTION check_prim_cond(
6295: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6296: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6297: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6298: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6299: RETURN BOOLEAN IS
6300: BEGIN
6293:
6294: FUNCTION check_prim_cond(
6295: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6296: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6297: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6298: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6299: RETURN BOOLEAN IS
6300: BEGIN
6301: FOR I IN 1..p_contact_point_list.COUNT LOOP
6294: FUNCTION check_prim_cond(
6295: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6296: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6297: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6298: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6299: RETURN BOOLEAN IS
6300: BEGIN
6301: FOR I IN 1..p_contact_point_list.COUNT LOOP
6302: IF p_contact_point_list(I).CONTACT_POINT_TYPE IS NULL AND (
6345: has party site criteria.
6346: ************************************************/
6347:
6348: PROCEDURE check_party_site_cond(
6349: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6350: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6351: x_secondary OUT BOOLEAN,
6352: x_primary OUT BOOLEAN
6353: ) IS
6346: ************************************************/
6347:
6348: PROCEDURE check_party_site_cond(
6349: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6350: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6351: x_secondary OUT BOOLEAN,
6352: x_primary OUT BOOLEAN
6353: ) IS
6354: BEGIN
6389: has contact criteria.
6390: ************************************************/
6391:
6392: PROCEDURE check_contact_cond(
6393: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6394: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6395: x_secondary OUT BOOLEAN,
6396: x_primary OUT BOOLEAN
6397: ) IS
6390: ************************************************/
6391:
6392: PROCEDURE check_contact_cond(
6393: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6394: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6395: x_secondary OUT BOOLEAN,
6396: x_primary OUT BOOLEAN
6397: ) IS
6398: BEGIN
6435: has valid contact point criteria.
6436: ************************************************/
6437:
6438: PROCEDURE check_contact_point_cond(
6439: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6440: x_secondary OUT BOOLEAN,
6441: x_primary OUT BOOLEAN
6442: ) IS
6443: BEGIN