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;
328: PROCEDURE INSERT_PARTY_SCORE (
329: p_party_id IN NUMBER
330: ,p_record_id IN NUMBER
331: ,p_search_ctx_id IN NUMBER
332: ,p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
333: ,p_stage_rec IN HZ_PARTY_STAGE.party_stage_rec_type
334: ,p_table_TX2 VARCHAR2
335: ,p_table_TX8 VARCHAR2
336: ,p_table_TX4 VARCHAR2
384: PROCEDURE INSERT_PARTY_SITES_SCORE (
385: p_party_id IN NUMBER
386: ,p_record_id IN NUMBER
387: ,p_search_ctx_id IN NUMBER
388: ,p_search_rec IN HZ_PARTY_SEARCH.party_site_search_rec_type
389: ,p_stage_rec IN HZ_PARTY_STAGE.party_site_stage_rec_type
390: ,p_table_TX4 VARCHAR2
391: ,p_table_TX3 VARCHAR2
392: ,p_idx IN NUMBER) IS
433: PROCEDURE INSERT_CONTACTS_SCORE (
434: p_party_id IN NUMBER
435: ,p_record_id IN NUMBER
436: ,p_search_ctx_id IN NUMBER
437: ,p_search_rec IN HZ_PARTY_SEARCH.contact_search_rec_type
438: ,p_stage_rec IN HZ_PARTY_STAGE.contact_stage_rec_type
439: ,p_idx IN NUMBER) IS
440: l_current_score NUMBER:=0;
441: l_score NUMBER;
449: PROCEDURE INSERT_CONTACT_POINTS_SCORE (
450: p_party_id IN NUMBER
451: ,p_record_id IN NUMBER
452: ,p_search_ctx_id IN NUMBER
453: ,p_search_rec IN HZ_PARTY_SEARCH.contact_point_search_rec_type
454: ,p_stage_rec IN HZ_PARTY_STAGE.contact_pt_stage_rec_type
455: ,p_idx IN NUMBER) IS
456: l_current_score NUMBER:=0;
457: l_score NUMBER;
468: for the PARTY Entity
469: ************************************************/
470:
471: PROCEDURE POP_PARTY_COND_REC(
472: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
473: ) IS
474: BEGIN
475: NULL ;
476: EXCEPTION
488: for the PARTY_SITES Entity
489: ************************************************/
490:
491: PROCEDURE POP_PARTY_SITES_COND_REC(
492: p_search_list IN HZ_PARTY_SEARCH.party_site_list
493: ) IS
494: BEGIN
495: NULL ;
496: EXCEPTION
508: for the CONTACTS Entity
509: ************************************************/
510:
511: PROCEDURE POP_CONTACTS_COND_REC(
512: p_search_list IN HZ_PARTY_SEARCH.contact_list
513: ) IS
514: BEGIN
515: NULL ;
516: EXCEPTION
528: for the CONTACT_POINTS Entity
529: ************************************************/
530:
531: PROCEDURE POP_CONTACT_POINTS_COND_REC(
532: p_search_list IN HZ_PARTY_SEARCH.contact_point_list
533: ) IS
534: BEGIN
535: NULL ;
536: EXCEPTION
542: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
543: END;
544:
545: PROCEDURE init_score_context (
546: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
547: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
548: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
549: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
550: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
543: END;
544:
545: PROCEDURE init_score_context (
546: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
547: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
548: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
549: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
550: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
551: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
544:
545: PROCEDURE init_score_context (
546: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
547: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
548: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
549: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
550: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
551: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
552: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
545: PROCEDURE init_score_context (
546: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
547: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
548: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
549: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
550: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
551: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
552: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
553: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
546: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
547: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
548: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
549: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
550: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
551: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
552: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
553: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
554: ) IS
547: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
548: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
549: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
550: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
551: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
552: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
553: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
554: ) IS
555: l_dummy NUMBER;
548: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
549: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
550: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
551: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
552: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
553: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
554: ) IS
555: l_dummy NUMBER;
556: BEGIN
549: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
550: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
551: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
552: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
553: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
554: ) IS
555: l_dummy NUMBER;
556: BEGIN
557: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
569: MAP_CONTACT_POINT_REC(FALSE,p_contact_point_list, l_dummy, g_contact_pt_stage_list);
570:
571: END;
572: FUNCTION init_search(
573: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
574: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
575: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
576: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
577: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
570:
571: END;
572: FUNCTION init_search(
573: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
574: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
575: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
576: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
577: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
578: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
571: END;
572: FUNCTION init_search(
573: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
574: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
575: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
576: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
577: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
578: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
579: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
572: FUNCTION init_search(
573: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
574: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
575: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
576: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
577: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
578: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
579: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
580: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
573: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
574: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
575: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
576: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
577: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
578: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
579: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
580: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
581: p_match_type IN VARCHAR2,
574: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
575: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
576: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
577: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
578: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
579: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
580: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
581: p_match_type IN VARCHAR2,
582: x_party_max_score OUT NUMBER,
575: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
576: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
577: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
578: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
579: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
580: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
581: p_match_type IN VARCHAR2,
582: x_party_max_score OUT NUMBER,
583: x_ps_max_score OUT NUMBER,
576: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
577: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
578: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
579: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
580: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
581: p_match_type IN VARCHAR2,
582: x_party_max_score OUT NUMBER,
583: x_ps_max_score OUT NUMBER,
584: x_contact_max_score OUT NUMBER,
588: l_ps_entered_max_score NUMBER:=0;
589: l_ct_entered_max_score NUMBER:=0;
590: l_cpt_entered_max_score NUMBER:=0;
591: vlist vlisttype;
592: maxscore HZ_PARTY_SEARCH.IDList;
593: l_name VARCHAR2(200);
594: l_idx NUMBER;
595: l_num NUMBER;
596: total NUMBER;
1864: p_party_id NUMBER,
1865: p_party_site_id NUMBER,
1866: p_org_contact_id NUMBER,
1867: p_contact_point_id NUMBER)
1868: RETURN HZ_PARTY_SEARCH.score_rec IS
1869: l_score_rec HZ_PARTY_SEARCH.score_rec;
1870: BEGIN
1871: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1872: 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);
1865: p_party_site_id NUMBER,
1866: p_org_contact_id NUMBER,
1867: p_contact_point_id NUMBER)
1868: RETURN HZ_PARTY_SEARCH.score_rec IS
1869: l_score_rec HZ_PARTY_SEARCH.score_rec;
1870: BEGIN
1871: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1872: 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);
1873: 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);
1897: **********************************************************/
1898:
1899: PROCEDURE find_parties (
1900: p_rule_id IN NUMBER,
1901: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
1902: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
1903: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
1904: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
1905: p_restrict_sql IN VARCHAR2,
1898:
1899: PROCEDURE find_parties (
1900: p_rule_id IN NUMBER,
1901: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
1902: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
1903: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
1904: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
1905: p_restrict_sql IN VARCHAR2,
1906: p_match_type IN VARCHAR2,
1899: PROCEDURE find_parties (
1900: p_rule_id IN NUMBER,
1901: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
1902: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
1903: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
1904: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
1905: p_restrict_sql IN VARCHAR2,
1906: p_match_type IN VARCHAR2,
1907: p_search_merged IN VARCHAR2,
1900: p_rule_id IN NUMBER,
1901: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
1902: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
1903: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
1904: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
1905: p_restrict_sql IN VARCHAR2,
1906: p_match_type IN VARCHAR2,
1907: p_search_merged IN VARCHAR2,
1908: p_dup_party_id IN NUMBER,
1936: l_TX2 VARCHAR2(2000);
1937: l_TX3 VARCHAR2(2000);
1938: l_TX4 VARCHAR2(2000);
1939: l_TX8 VARCHAR2(2000);
1940: H_SCORES HZ_PARTY_SEARCH.score_list;
1941: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
1942: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
1943:
1944: l_score NUMBER;
1937: l_TX3 VARCHAR2(2000);
1938: l_TX4 VARCHAR2(2000);
1939: l_TX8 VARCHAR2(2000);
1940: H_SCORES HZ_PARTY_SEARCH.score_list;
1941: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
1942: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
1943:
1944: l_score NUMBER;
1945: l_idx NUMBER;
1938: l_TX4 VARCHAR2(2000);
1939: l_TX8 VARCHAR2(2000);
1940: H_SCORES HZ_PARTY_SEARCH.score_list;
1941: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
1942: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
1943:
1944: l_score NUMBER;
1945: l_idx NUMBER;
1946: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
2129: /** Private procedure to acquire and score at party site level ***/
2130: 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
2131: l_party_id_idx NUMBER:=1;
2132: l_ctx_id NUMBER;
2133: h_ps_id HZ_PARTY_SEARCH.IDList;
2134: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2135: h_ps_score HZ_PARTY_SEARCH.IDList;
2136: detcnt NUMBER := 1;
2137: BEGIN
2130: 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
2131: l_party_id_idx NUMBER:=1;
2132: l_ctx_id NUMBER;
2133: h_ps_id HZ_PARTY_SEARCH.IDList;
2134: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2135: h_ps_score HZ_PARTY_SEARCH.IDList;
2136: detcnt NUMBER := 1;
2137: BEGIN
2138: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2131: l_party_id_idx NUMBER:=1;
2132: l_ctx_id NUMBER;
2133: h_ps_id HZ_PARTY_SEARCH.IDList;
2134: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2135: h_ps_score HZ_PARTY_SEARCH.IDList;
2136: detcnt NUMBER := 1;
2137: BEGIN
2138: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2139: 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);
2251: /** Private procedure to acquire and score at contact point level ***/
2252: 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
2253: l_party_id_idx NUMBER:=1;
2254: l_ctx_id NUMBER;
2255: h_ct_id HZ_PARTY_SEARCH.IDList;
2256: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2257: h_ct_score HZ_PARTY_SEARCH.IDList;
2258: detcnt NUMBER := 1;
2259: BEGIN
2252: 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
2253: l_party_id_idx NUMBER:=1;
2254: l_ctx_id NUMBER;
2255: h_ct_id HZ_PARTY_SEARCH.IDList;
2256: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2257: h_ct_score HZ_PARTY_SEARCH.IDList;
2258: detcnt NUMBER := 1;
2259: BEGIN
2260: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2253: l_party_id_idx NUMBER:=1;
2254: l_ctx_id NUMBER;
2255: h_ct_id HZ_PARTY_SEARCH.IDList;
2256: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2257: h_ct_score HZ_PARTY_SEARCH.IDList;
2258: detcnt NUMBER := 1;
2259: BEGIN
2260: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2261: 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);
2360: /** Private procedure to acquire and score at contact point level ***/
2361: 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
2362: l_party_id_idx NUMBER:=1;
2363: l_ctx_id NUMBER;
2364: h_cpt_id HZ_PARTY_SEARCH.IDList;
2365: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2366: h_cpt_score HZ_PARTY_SEARCH.IDList;
2367: detcnt NUMBER := 1;
2368: l_cpt_flag VARCHAR2(1) := 'N';
2361: 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
2362: l_party_id_idx NUMBER:=1;
2363: l_ctx_id NUMBER;
2364: h_cpt_id HZ_PARTY_SEARCH.IDList;
2365: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2366: h_cpt_score HZ_PARTY_SEARCH.IDList;
2367: detcnt NUMBER := 1;
2368: l_cpt_flag VARCHAR2(1) := 'N';
2369: is_a_match VARCHAR2(1) := 'Y';
2362: l_party_id_idx NUMBER:=1;
2363: l_ctx_id NUMBER;
2364: h_cpt_id HZ_PARTY_SEARCH.IDList;
2365: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2366: h_cpt_score HZ_PARTY_SEARCH.IDList;
2367: detcnt NUMBER := 1;
2368: l_cpt_flag VARCHAR2(1) := 'N';
2369: is_a_match VARCHAR2(1) := 'Y';
2370: BEGIN
2811: END find_parties;
2812:
2813: PROCEDURE find_persons (
2814: p_rule_id IN NUMBER,
2815: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2816: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2817: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2818: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2819: p_restrict_sql IN VARCHAR2,
2812:
2813: PROCEDURE find_persons (
2814: p_rule_id IN NUMBER,
2815: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2816: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2817: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2818: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2819: p_restrict_sql IN VARCHAR2,
2820: p_match_type IN VARCHAR2,
2813: PROCEDURE find_persons (
2814: p_rule_id IN NUMBER,
2815: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2816: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2817: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2818: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2819: p_restrict_sql IN VARCHAR2,
2820: p_match_type IN VARCHAR2,
2821: p_search_merged IN VARCHAR2,
2814: p_rule_id IN NUMBER,
2815: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2816: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2817: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2818: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2819: p_restrict_sql IN VARCHAR2,
2820: p_match_type IN VARCHAR2,
2821: p_search_merged IN VARCHAR2,
2822: p_dup_party_id IN NUMBER,
2850: l_TX2 VARCHAR2(2000);
2851: l_TX3 VARCHAR2(2000);
2852: l_TX4 VARCHAR2(2000);
2853: l_TX8 VARCHAR2(2000);
2854: H_SCORES HZ_PARTY_SEARCH.score_list;
2855: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2856: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2857:
2858: l_score NUMBER;
2851: l_TX3 VARCHAR2(2000);
2852: l_TX4 VARCHAR2(2000);
2853: l_TX8 VARCHAR2(2000);
2854: H_SCORES HZ_PARTY_SEARCH.score_list;
2855: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2856: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2857:
2858: l_score NUMBER;
2859: l_idx NUMBER;
2852: l_TX4 VARCHAR2(2000);
2853: l_TX8 VARCHAR2(2000);
2854: H_SCORES HZ_PARTY_SEARCH.score_list;
2855: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2856: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2857:
2858: l_score NUMBER;
2859: l_idx NUMBER;
2860: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
3091: /** Private procedure to acquire and score at party site level ***/
3092: 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
3093: l_party_id_idx NUMBER:=1;
3094: l_ctx_id NUMBER;
3095: h_ps_id HZ_PARTY_SEARCH.IDList;
3096: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3097: h_ps_score HZ_PARTY_SEARCH.IDList;
3098: detcnt NUMBER := 1;
3099: l_person_id NUMBER;
3092: 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
3093: l_party_id_idx NUMBER:=1;
3094: l_ctx_id NUMBER;
3095: h_ps_id HZ_PARTY_SEARCH.IDList;
3096: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3097: h_ps_score HZ_PARTY_SEARCH.IDList;
3098: detcnt NUMBER := 1;
3099: l_person_id NUMBER;
3100: BEGIN
3093: l_party_id_idx NUMBER:=1;
3094: l_ctx_id NUMBER;
3095: h_ps_id HZ_PARTY_SEARCH.IDList;
3096: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3097: h_ps_score HZ_PARTY_SEARCH.IDList;
3098: detcnt NUMBER := 1;
3099: l_person_id NUMBER;
3100: BEGIN
3101: SAVEPOINT eval_start;
3190: /** Private procedure to acquire and score at party site level ***/
3191: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3192: l_party_id_idx NUMBER:=1;
3193: l_ctx_id NUMBER;
3194: h_ct_id HZ_PARTY_SEARCH.IDList;
3195: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3196: h_ct_score HZ_PARTY_SEARCH.IDList;
3197: detcnt NUMBER := 1;
3198: l_person_id NUMBER;
3191: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3192: l_party_id_idx NUMBER:=1;
3193: l_ctx_id NUMBER;
3194: h_ct_id HZ_PARTY_SEARCH.IDList;
3195: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3196: h_ct_score HZ_PARTY_SEARCH.IDList;
3197: detcnt NUMBER := 1;
3198: l_person_id NUMBER;
3199: BEGIN
3192: l_party_id_idx NUMBER:=1;
3193: l_ctx_id NUMBER;
3194: h_ct_id HZ_PARTY_SEARCH.IDList;
3195: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3196: h_ct_score HZ_PARTY_SEARCH.IDList;
3197: detcnt NUMBER := 1;
3198: l_person_id NUMBER;
3199: BEGIN
3200: SAVEPOINT eval_start;
3248: /** Private procedure to acquire and score at contact point level ***/
3249: 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
3250: l_party_id_idx NUMBER:=1;
3251: l_ctx_id NUMBER;
3252: h_cpt_id HZ_PARTY_SEARCH.IDList;
3253: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3254: h_cpt_score HZ_PARTY_SEARCH.IDList;
3255: detcnt NUMBER := 1;
3256: l_person_id NUMBER;
3249: 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
3250: l_party_id_idx NUMBER:=1;
3251: l_ctx_id NUMBER;
3252: h_cpt_id HZ_PARTY_SEARCH.IDList;
3253: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3254: h_cpt_score HZ_PARTY_SEARCH.IDList;
3255: detcnt NUMBER := 1;
3256: l_person_id NUMBER;
3257: is_a_match VARCHAR2(1) := 'Y';
3250: l_party_id_idx NUMBER:=1;
3251: l_ctx_id NUMBER;
3252: h_cpt_id HZ_PARTY_SEARCH.IDList;
3253: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3254: h_cpt_score HZ_PARTY_SEARCH.IDList;
3255: detcnt NUMBER := 1;
3256: l_person_id NUMBER;
3257: is_a_match VARCHAR2(1) := 'Y';
3258: l_cpt_flag VARCHAR2(1) := 'N';
3592: END find_persons;
3593:
3594: PROCEDURE find_persons (
3595: p_rule_id IN NUMBER,
3596: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3597: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3598: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3599: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3600: p_restrict_sql IN VARCHAR2,
3593:
3594: PROCEDURE find_persons (
3595: p_rule_id IN NUMBER,
3596: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3597: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3598: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3599: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3600: p_restrict_sql IN VARCHAR2,
3601: p_match_type IN VARCHAR2,
3594: PROCEDURE find_persons (
3595: p_rule_id IN NUMBER,
3596: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3597: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3598: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3599: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3600: p_restrict_sql IN VARCHAR2,
3601: p_match_type IN VARCHAR2,
3602: p_search_merged IN VARCHAR2,
3595: p_rule_id IN NUMBER,
3596: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3597: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3598: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3599: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3600: p_restrict_sql IN VARCHAR2,
3601: p_match_type IN VARCHAR2,
3602: p_search_merged IN VARCHAR2,
3603: p_ins_details IN VARCHAR2,
3609: 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);
3610: END;
3611: PROCEDURE find_party_details (
3612: p_rule_id IN NUMBER,
3613: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3614: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3615: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3616: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3617: p_restrict_sql IN VARCHAR2,
3610: END;
3611: PROCEDURE find_party_details (
3612: p_rule_id IN NUMBER,
3613: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3614: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3615: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3616: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3617: p_restrict_sql IN VARCHAR2,
3618: p_match_type IN VARCHAR2,
3611: PROCEDURE find_party_details (
3612: p_rule_id IN NUMBER,
3613: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3614: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3615: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3616: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3617: p_restrict_sql IN VARCHAR2,
3618: p_match_type IN VARCHAR2,
3619: p_search_merged IN VARCHAR2,
3612: p_rule_id IN NUMBER,
3613: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3614: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3615: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3616: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3617: p_restrict_sql IN VARCHAR2,
3618: p_match_type IN VARCHAR2,
3619: p_search_merged IN VARCHAR2,
3620: x_search_ctx_id OUT NUMBER,
3669: x_dup_set_id OUT NUMBER,
3670: x_search_ctx_id OUT NUMBER,
3671: x_num_matches OUT NUMBER
3672: ) IS
3673: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3674: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3675: l_contact_list HZ_PARTY_SEARCH.contact_list;
3676: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3677: l_match_idx NUMBER;
3670: x_search_ctx_id OUT NUMBER,
3671: x_num_matches OUT NUMBER
3672: ) IS
3673: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3674: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3675: l_contact_list HZ_PARTY_SEARCH.contact_list;
3676: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3677: l_match_idx NUMBER;
3678:
3671: x_num_matches OUT NUMBER
3672: ) IS
3673: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3674: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3675: l_contact_list HZ_PARTY_SEARCH.contact_list;
3676: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3677: l_match_idx NUMBER;
3678:
3679: --Fix for bug 4417124
3672: ) IS
3673: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3674: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3675: l_contact_list HZ_PARTY_SEARCH.contact_list;
3676: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3677: l_match_idx NUMBER;
3678:
3679: --Fix for bug 4417124
3680: l_use_contact_addr_info BOOLEAN := TRUE;
3785: x_search_ctx_id OUT NUMBER,
3786: x_num_matches OUT NUMBER
3787: ) IS
3788:
3789: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3790: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3791: l_contact_list HZ_PARTY_SEARCH.contact_list;
3792: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3793: contact_point_ids HZ_PARTY_SEARCH.IDList;
3786: x_num_matches OUT NUMBER
3787: ) IS
3788:
3789: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3790: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3791: l_contact_list HZ_PARTY_SEARCH.contact_list;
3792: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3793: contact_point_ids HZ_PARTY_SEARCH.IDList;
3794: p_party_site_list HZ_PARTY_SEARCH.IDList;
3787: ) IS
3788:
3789: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3790: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3791: l_contact_list HZ_PARTY_SEARCH.contact_list;
3792: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3793: contact_point_ids HZ_PARTY_SEARCH.IDList;
3794: p_party_site_list HZ_PARTY_SEARCH.IDList;
3795: p_contact_ids HZ_PARTY_SEARCH.IDList;
3788:
3789: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3790: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3791: l_contact_list HZ_PARTY_SEARCH.contact_list;
3792: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3793: contact_point_ids HZ_PARTY_SEARCH.IDList;
3794: p_party_site_list HZ_PARTY_SEARCH.IDList;
3795: p_contact_ids HZ_PARTY_SEARCH.IDList;
3796: l_match_idx NUMBER;
3789: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3790: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3791: l_contact_list HZ_PARTY_SEARCH.contact_list;
3792: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3793: contact_point_ids HZ_PARTY_SEARCH.IDList;
3794: p_party_site_list HZ_PARTY_SEARCH.IDList;
3795: p_contact_ids HZ_PARTY_SEARCH.IDList;
3796: l_match_idx NUMBER;
3797: cursor get_cpts_for_party_sites is select contact_point_id
3790: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3791: l_contact_list HZ_PARTY_SEARCH.contact_list;
3792: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3793: contact_point_ids HZ_PARTY_SEARCH.IDList;
3794: p_party_site_list HZ_PARTY_SEARCH.IDList;
3795: p_contact_ids HZ_PARTY_SEARCH.IDList;
3796: l_match_idx NUMBER;
3797: cursor get_cpts_for_party_sites is select contact_point_id
3798: from hz_contact_points
3791: l_contact_list HZ_PARTY_SEARCH.contact_list;
3792: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3793: contact_point_ids HZ_PARTY_SEARCH.IDList;
3794: p_party_site_list HZ_PARTY_SEARCH.IDList;
3795: p_contact_ids HZ_PARTY_SEARCH.IDList;
3796: l_match_idx NUMBER;
3797: cursor get_cpts_for_party_sites is select contact_point_id
3798: from hz_contact_points
3799: where owner_table_name = 'HZ_PARTY_SITES'
3820:
3821: get_search_criteria (
3822: null,
3823: p_party_site_list,
3824: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
3825: contact_point_ids,
3826: l_party_search_rec,
3827: l_party_site_list,
3828: l_contact_list,
3870: x_search_ctx_id OUT NUMBER,
3871: x_num_matches OUT NUMBER
3872: ) IS
3873:
3874: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3875: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3876: l_contact_list HZ_PARTY_SEARCH.contact_list;
3877: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3878: contact_point_ids HZ_PARTY_SEARCH.IDList;
3871: x_num_matches OUT NUMBER
3872: ) IS
3873:
3874: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3875: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3876: l_contact_list HZ_PARTY_SEARCH.contact_list;
3877: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3878: contact_point_ids HZ_PARTY_SEARCH.IDList;
3879: p_party_site_list HZ_PARTY_SEARCH.IDList;
3872: ) IS
3873:
3874: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3875: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3876: l_contact_list HZ_PARTY_SEARCH.contact_list;
3877: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3878: contact_point_ids HZ_PARTY_SEARCH.IDList;
3879: p_party_site_list HZ_PARTY_SEARCH.IDList;
3880: p_contact_ids HZ_PARTY_SEARCH.IDList;
3873:
3874: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3875: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3876: l_contact_list HZ_PARTY_SEARCH.contact_list;
3877: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3878: contact_point_ids HZ_PARTY_SEARCH.IDList;
3879: p_party_site_list HZ_PARTY_SEARCH.IDList;
3880: p_contact_ids HZ_PARTY_SEARCH.IDList;
3881: l_match_idx NUMBER;
3874: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3875: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3876: l_contact_list HZ_PARTY_SEARCH.contact_list;
3877: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3878: contact_point_ids HZ_PARTY_SEARCH.IDList;
3879: p_party_site_list HZ_PARTY_SEARCH.IDList;
3880: p_contact_ids HZ_PARTY_SEARCH.IDList;
3881: l_match_idx NUMBER;
3882: cursor get_cpt_for_contact_id is select contact_point_id
3875: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3876: l_contact_list HZ_PARTY_SEARCH.contact_list;
3877: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3878: contact_point_ids HZ_PARTY_SEARCH.IDList;
3879: p_party_site_list HZ_PARTY_SEARCH.IDList;
3880: p_contact_ids HZ_PARTY_SEARCH.IDList;
3881: l_match_idx NUMBER;
3882: cursor get_cpt_for_contact_id is select contact_point_id
3883: from hz_org_contacts a, hz_relationships b, hz_contact_points c
3876: l_contact_list HZ_PARTY_SEARCH.contact_list;
3877: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3878: contact_point_ids HZ_PARTY_SEARCH.IDList;
3879: p_party_site_list HZ_PARTY_SEARCH.IDList;
3880: p_contact_ids HZ_PARTY_SEARCH.IDList;
3881: l_match_idx NUMBER;
3882: cursor get_cpt_for_contact_id is select contact_point_id
3883: from hz_org_contacts a, hz_relationships b, hz_contact_points c
3884: where a.party_relationship_id = b.relationship_id
3907: CLOSE get_cpt_for_contact_id;
3908:
3909: get_search_criteria (
3910: null,
3911: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
3912: p_contact_ids,
3913: contact_point_ids,
3914: l_party_search_rec,
3915: l_party_site_list,
3958: p_match_type IN VARCHAR2,
3959: x_search_ctx_id OUT NUMBER,
3960: x_num_matches OUT NUMBER
3961: ) IS
3962: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3963: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3964: l_contact_list HZ_PARTY_SEARCH.contact_list;
3965: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3966: contact_point_ids HZ_PARTY_SEARCH.IDList;
3959: x_search_ctx_id OUT NUMBER,
3960: x_num_matches OUT NUMBER
3961: ) IS
3962: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3963: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3964: l_contact_list HZ_PARTY_SEARCH.contact_list;
3965: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3966: contact_point_ids HZ_PARTY_SEARCH.IDList;
3967: p_party_site_list HZ_PARTY_SEARCH.IDList;
3960: x_num_matches OUT NUMBER
3961: ) IS
3962: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3963: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3964: l_contact_list HZ_PARTY_SEARCH.contact_list;
3965: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3966: contact_point_ids HZ_PARTY_SEARCH.IDList;
3967: p_party_site_list HZ_PARTY_SEARCH.IDList;
3968: p_contact_ids HZ_PARTY_SEARCH.IDList;
3961: ) IS
3962: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3963: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3964: l_contact_list HZ_PARTY_SEARCH.contact_list;
3965: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3966: contact_point_ids HZ_PARTY_SEARCH.IDList;
3967: p_party_site_list HZ_PARTY_SEARCH.IDList;
3968: p_contact_ids HZ_PARTY_SEARCH.IDList;
3969: l_match_idx NUMBER;
3962: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3963: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3964: l_contact_list HZ_PARTY_SEARCH.contact_list;
3965: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3966: contact_point_ids HZ_PARTY_SEARCH.IDList;
3967: p_party_site_list HZ_PARTY_SEARCH.IDList;
3968: p_contact_ids HZ_PARTY_SEARCH.IDList;
3969: l_match_idx NUMBER;
3970:
3963: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3964: l_contact_list HZ_PARTY_SEARCH.contact_list;
3965: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3966: contact_point_ids HZ_PARTY_SEARCH.IDList;
3967: p_party_site_list HZ_PARTY_SEARCH.IDList;
3968: p_contact_ids HZ_PARTY_SEARCH.IDList;
3969: l_match_idx NUMBER;
3970:
3971: BEGIN
3964: l_contact_list HZ_PARTY_SEARCH.contact_list;
3965: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3966: contact_point_ids HZ_PARTY_SEARCH.IDList;
3967: p_party_site_list HZ_PARTY_SEARCH.IDList;
3968: p_contact_ids HZ_PARTY_SEARCH.IDList;
3969: l_match_idx NUMBER;
3970:
3971: BEGIN
3972: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3981: END IF;
3982: contact_point_ids(1) := p_contact_point_id;
3983: get_search_criteria (
3984: null,
3985: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
3986: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
3987: contact_point_ids,
3988: l_party_search_rec,
3989: l_party_site_list,
3982: contact_point_ids(1) := p_contact_point_id;
3983: get_search_criteria (
3984: null,
3985: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
3986: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
3987: contact_point_ids,
3988: l_party_search_rec,
3989: l_party_site_list,
3990: l_contact_list,
4135: ) IS
4136: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4137: l_match_idx NUMBER;
4138: AttrVals AttrList;
4139: l_party_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_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4143: l_dup_set_id NUMBER;
4136: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4137: l_match_idx NUMBER;
4138: AttrVals AttrList;
4139: l_party_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_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4143: l_dup_set_id NUMBER;
4144: l_idx NUMBER;
4137: l_match_idx NUMBER;
4138: AttrVals AttrList;
4139: l_party_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_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4143: l_dup_set_id NUMBER;
4144: l_idx NUMBER;
4145: l_cpt_type VARCHAR2(255);
4138: AttrVals AttrList;
4139: l_party_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_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4143: l_dup_set_id NUMBER;
4144: l_idx NUMBER;
4145: l_cpt_type VARCHAR2(255);
4146: FIRST BOOLEAN := TRUE;
4286:
4287: PROCEDURE get_matching_party_sites (
4288: p_rule_id IN NUMBER,
4289: p_party_id IN NUMBER,
4290: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4291: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4292: p_restrict_sql IN VARCHAR2,
4293: p_match_type IN VARCHAR2,
4294: p_dup_party_site_id IN NUMBER,
4287: PROCEDURE get_matching_party_sites (
4288: p_rule_id IN NUMBER,
4289: p_party_id IN NUMBER,
4290: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4291: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4292: p_restrict_sql IN VARCHAR2,
4293: p_match_type IN VARCHAR2,
4294: p_dup_party_site_id IN NUMBER,
4295: x_search_ctx_id OUT NUMBER,
4314: l_search_ctx_id NUMBER; -- Generated Search Context ID
4315:
4316: l_TX3 VARCHAR2(2000);
4317: l_TX4 VARCHAR2(2000);
4318: H_SCORES HZ_PARTY_SEARCH.score_list;
4319:
4320: l_score NUMBER;
4321: l_idx NUMBER;
4322: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
4345: defps NUMBER :=0;
4346: defct NUMBER :=0;
4347: defcpt NUMBER :=0;
4348: l_index NUMBER;
4349: l_match_ps_list HZ_PARTY_SEARCH.IDList;
4350: l_cnt NUMBER:=1;
4351:
4352:
4353: BEGIN
4369: l_match_str := ' AND ';
4370: ELSIF p_match_type = 'OR' THEN
4371: l_match_str := ' OR ';
4372: END IF;
4373: 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);
4374: g_score_until_thresh := false;
4375: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4376:
4377: -- ************************************************************
4377: -- ************************************************************
4378: -- STEP 2. Setup of intermedia query strings for Acquisition query
4379: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4380: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4381: 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);
4382:
4383: -- Setup Search Context ID
4384: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4385: x_search_ctx_id := l_search_ctx_id;
4520:
4521: PROCEDURE get_matching_contacts (
4522: p_rule_id IN NUMBER,
4523: p_party_id IN NUMBER,
4524: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
4525: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4526: p_restrict_sql IN VARCHAR2,
4527: p_match_type IN VARCHAR2,
4528: p_dup_contact_id IN NUMBER,
4521: PROCEDURE get_matching_contacts (
4522: p_rule_id IN NUMBER,
4523: p_party_id IN NUMBER,
4524: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
4525: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4526: p_restrict_sql IN VARCHAR2,
4527: p_match_type IN VARCHAR2,
4528: p_dup_contact_id IN NUMBER,
4529: x_search_ctx_id OUT NUMBER,
4546: l_entered_max_score NUMBER;
4547: FIRST BOOLEAN;
4548: l_search_ctx_id NUMBER; -- Generated Search Context ID
4549:
4550: H_SCORES HZ_PARTY_SEARCH.score_list;
4551:
4552: l_score NUMBER;
4553: l_idx NUMBER;
4554: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
4574: l_party_max_score NUMBER;
4575: l_ps_max_score NUMBER;
4576: l_contact_max_score NUMBER;
4577: l_cpt_max_score NUMBER;
4578: l_match_contact_list HZ_PARTY_SEARCH.IDList;
4579: l_cnt NUMBER:=1;
4580:
4581:
4582: BEGIN
4598: l_match_str := ' AND ';
4599: ELSIF p_match_type = 'OR' THEN
4600: l_match_str := ' OR ';
4601: END IF;
4602: 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);
4603: g_score_until_thresh := false;
4604: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4605:
4606: -- ************************************************************
4606: -- ************************************************************
4607: -- STEP 2. Setup of intermedia query strings for Acquisition query
4608: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4609: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4610: 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);
4611:
4612: -- Setup Search Context ID
4613: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4614: x_search_ctx_id := l_search_ctx_id;
4745:
4746: PROCEDURE get_matching_contact_points (
4747: p_rule_id IN NUMBER,
4748: p_party_id IN NUMBER,
4749: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4750: p_restrict_sql IN VARCHAR2,
4751: p_match_type IN VARCHAR2,
4752: p_dup_contact_point_id IN NUMBER,
4753: x_search_ctx_id OUT NUMBER,
4774: l_cpt_ps_id NUMBER;
4775: l_cpt_contact_id NUMBER;
4776: l_contact_pt_id NUMBER;
4777: l_cpt_type VARCHAR2(100);
4778: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
4779: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
4780: H_SCORE HZ_PARTY_SEARCH.IDList;
4781:
4782: cnt NUMBER :=0;
4775: l_cpt_contact_id NUMBER;
4776: l_contact_pt_id NUMBER;
4777: l_cpt_type VARCHAR2(100);
4778: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
4779: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
4780: H_SCORE HZ_PARTY_SEARCH.IDList;
4781:
4782: cnt NUMBER :=0;
4783: l_party_max_score NUMBER;
4776: l_contact_pt_id NUMBER;
4777: l_cpt_type VARCHAR2(100);
4778: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
4779: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
4780: H_SCORE HZ_PARTY_SEARCH.IDList;
4781:
4782: cnt NUMBER :=0;
4783: l_party_max_score NUMBER;
4784: l_ps_max_score NUMBER;
4805: l_match_str := ' AND ';
4806: ELSIF p_match_type = 'OR' THEN
4807: l_match_str := ' OR ';
4808: END IF;
4809: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
4810: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
4811: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
4812: g_score_until_thresh := false;
4813: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4806: ELSIF p_match_type = 'OR' THEN
4807: l_match_str := ' OR ';
4808: END IF;
4809: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
4810: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
4811: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
4812: g_score_until_thresh := false;
4813: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4814:
4814:
4815: -- ************************************************************
4816: -- STEP 2. Setup of intermedia query strings for Acquisition query
4817: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4818: 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);
4819:
4820: -- Setup Search Context ID
4821: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4822: x_search_ctx_id := l_search_ctx_id;
4889:
4890: PROCEDURE get_score_details (
4891: p_rule_id IN NUMBER,
4892: p_party_id IN NUMBER,
4893: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4894: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4895: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4896: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4897: x_search_ctx_id IN OUT NUMBER
4890: PROCEDURE get_score_details (
4891: p_rule_id IN NUMBER,
4892: p_party_id IN NUMBER,
4893: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4894: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4895: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4896: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4897: x_search_ctx_id IN OUT NUMBER
4898: ) IS
4891: p_rule_id IN NUMBER,
4892: p_party_id IN NUMBER,
4893: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4894: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4895: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4896: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4897: x_search_ctx_id IN OUT NUMBER
4898: ) IS
4899:
4892: p_party_id IN NUMBER,
4893: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4894: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4895: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4896: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4897: x_search_ctx_id IN OUT NUMBER
4898: ) IS
4899:
4900: -- Strings to hold the generated Intermedia query strings
4919: l_TX4 VARCHAR2(2000);
4920: l_max_TX4 VARCHAR2(2000);
4921: l_TX8 VARCHAR2(2000);
4922: l_max_TX8 VARCHAR2(2000);
4923: H_SCORES HZ_PARTY_SEARCH.score_list;
4924:
4925: l_score NUMBER;
4926: l_match_idx NUMBER;
4927: l_idx NUMBER;
5086: ************************************************/
5087:
5088: PROCEDURE get_party_for_search (
5089: p_party_id IN NUMBER,
5090: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5091: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5092: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5093: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5094: ) IS
5087:
5088: PROCEDURE get_party_for_search (
5089: p_party_id IN NUMBER,
5090: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5091: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5092: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5093: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5094: ) IS
5095: l_party_id NUMBER;
5088: PROCEDURE get_party_for_search (
5089: p_party_id IN NUMBER,
5090: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5091: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5092: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5093: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5094: ) IS
5095: l_party_id NUMBER;
5096: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5089: p_party_id IN NUMBER,
5090: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5091: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5092: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5093: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5094: ) IS
5095: l_party_id NUMBER;
5096: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5097: l_contact_ids HZ_PARTY_SEARCH.IDList;
5092: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5093: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5094: ) IS
5095: l_party_id NUMBER;
5096: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5097: l_contact_ids HZ_PARTY_SEARCH.IDList;
5098: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5099: ps NUMBER :=1;
5100: cpt NUMBER :=1;
5093: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5094: ) IS
5095: l_party_id NUMBER;
5096: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5097: l_contact_ids HZ_PARTY_SEARCH.IDList;
5098: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5099: ps NUMBER :=1;
5100: cpt NUMBER :=1;
5101: ct NUMBER :=1;
5094: ) IS
5095: l_party_id NUMBER;
5096: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5097: l_contact_ids HZ_PARTY_SEARCH.IDList;
5098: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5099: ps NUMBER :=1;
5100: cpt NUMBER :=1;
5101: ct NUMBER :=1;
5102: l_use_contact_info varchar2(1);
5158: END get_party_for_search;
5159:
5160: PROCEDURE get_search_criteria (
5161: p_party_id IN NUMBER,
5162: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5163: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5164: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5165: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5166: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5159:
5160: PROCEDURE get_search_criteria (
5161: p_party_id IN NUMBER,
5162: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5163: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5164: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5165: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5166: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5167: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5160: PROCEDURE get_search_criteria (
5161: p_party_id IN NUMBER,
5162: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5163: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5164: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5165: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5166: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5167: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5168: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5161: p_party_id IN NUMBER,
5162: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5163: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5164: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5165: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5166: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5167: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5168: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5169: ) IS
5162: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5163: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5164: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5165: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5166: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5167: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5168: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5169: ) IS
5170: BEGIN
5163: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5164: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5165: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5166: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5167: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5168: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5169: ) IS
5170: BEGIN
5171:
5164: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5165: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5166: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5167: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5168: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5169: ) IS
5170: BEGIN
5171:
5172: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5204: ************************************************/
5205:
5206: PROCEDURE get_party_rec (
5207: p_party_id IN NUMBER,
5208: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type
5209: ) IS
5210: l_party_type VARCHAR2(255);
5211: BEGIN
5212:
5263: the party site search record structure
5264: ************************************************/
5265:
5266: PROCEDURE get_party_site_rec (
5267: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5268: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5269: ) IS
5270: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5271: SELECT party_site_id
5264: ************************************************/
5265:
5266: PROCEDURE get_party_site_rec (
5267: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5268: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5269: ) IS
5270: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5271: SELECT party_site_id
5272: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
5319: the contact search record structure
5320: ************************************************/
5321:
5322: PROCEDURE get_contact_rec (
5323: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5324: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5325: ) IS
5326: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5327: SELECT org_contact_id
5320: ************************************************/
5321:
5322: PROCEDURE get_contact_rec (
5323: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5324: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5325: ) IS
5326: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5327: SELECT org_contact_id
5328: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
5375: the contact point search record structure
5376: ************************************************/
5377:
5378: PROCEDURE get_contact_point_rec (
5379: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
5380: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5381: ) IS
5382:
5383: -- Cursor to fetch primary contact points for party
5376: ************************************************/
5377:
5378: PROCEDURE get_contact_point_rec (
5379: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
5380: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5381: ) IS
5382:
5383: -- Cursor to fetch primary contact points for party
5384: CURSOR c_cpts(cp_contact_point_id NUMBER) IS
5430: ************************************************/
5431:
5432: PROCEDURE MAP_PARTY_REC(
5433: p_search_ctx IN BOOLEAN,
5434: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5435: x_entered_max_score OUT NUMBER,
5436: x_stage_rec IN OUT NOCOPY HZ_PARTY_STAGE.party_stage_rec_type
5437: ) IS
5438: tmp VARCHAR2(4000);
5536: ************************************************/
5537:
5538: PROCEDURE MAP_PARTY_SITE_REC(
5539: p_search_ctx IN BOOLEAN,
5540: p_search_list IN HZ_PARTY_SEARCH.party_site_list,
5541: x_entered_max_score OUT NUMBER,
5542: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.party_site_stage_list
5543: ) IS
5544: l_current_max_score NUMBER;
5651: ************************************************/
5652:
5653: PROCEDURE MAP_CONTACT_REC(
5654: p_search_ctx IN BOOLEAN,
5655: p_search_list IN HZ_PARTY_SEARCH.contact_list,
5656: x_entered_max_score OUT NUMBER,
5657: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_stage_list
5658: ) IS
5659: l_current_max_score NUMBER;
5693: ************************************************/
5694:
5695: PROCEDURE MAP_CONTACT_POINT_REC(
5696: p_search_ctx IN BOOLEAN,
5697: p_search_list IN HZ_PARTY_SEARCH.contact_point_list,
5698: x_entered_max_score OUT NUMBER,
5699: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_pt_stage_list
5700: ) IS
5701: l_current_max_score NUMBER;
5735: 2. Contact Point Type is not null for each condition
5736: ************************************************/
5737:
5738: FUNCTION check_prim_cond(
5739: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5740: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5741: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5742: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
5743: RETURN BOOLEAN IS
5736: ************************************************/
5737:
5738: FUNCTION check_prim_cond(
5739: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5740: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5741: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5742: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
5743: RETURN BOOLEAN IS
5744: BEGIN
5737:
5738: FUNCTION check_prim_cond(
5739: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5740: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5741: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5742: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
5743: RETURN BOOLEAN IS
5744: BEGIN
5745: IF p_party_search_rec.PARTY_NAME IS NOT NULL THEN
5738: FUNCTION check_prim_cond(
5739: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5740: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5741: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5742: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
5743: RETURN BOOLEAN IS
5744: BEGIN
5745: IF p_party_search_rec.PARTY_NAME IS NOT NULL THEN
5746: RETURN TRUE;
5767: has party site criteria.
5768: ************************************************/
5769:
5770: PROCEDURE check_party_site_cond(
5771: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5772: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5773: x_secondary OUT BOOLEAN,
5774: x_primary OUT BOOLEAN
5775: ) IS
5768: ************************************************/
5769:
5770: PROCEDURE check_party_site_cond(
5771: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5772: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5773: x_secondary OUT BOOLEAN,
5774: x_primary OUT BOOLEAN
5775: ) IS
5776: BEGIN
5811: has contact criteria.
5812: ************************************************/
5813:
5814: PROCEDURE check_contact_cond(
5815: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5816: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5817: x_secondary OUT BOOLEAN,
5818: x_primary OUT BOOLEAN
5819: ) IS
5812: ************************************************/
5813:
5814: PROCEDURE check_contact_cond(
5815: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5816: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5817: x_secondary OUT BOOLEAN,
5818: x_primary OUT BOOLEAN
5819: ) IS
5820: BEGIN
5849: has valid contact point criteria.
5850: ************************************************/
5851:
5852: PROCEDURE check_contact_point_cond(
5853: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5854: x_secondary OUT BOOLEAN,
5855: x_primary OUT BOOLEAN
5856: ) IS
5857: BEGIN