APPS.HZ_MATCH_RULE_37 dependencies on HZ_STAGED_CONTACT_POINTS

Line 1908: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID

1904: END IF;
1905: IF p_restrict_entity = 'CONTACTS'
1906: THEN
1907: OPEN x_cursor FOR
1908: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1909: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1910: WHERE contains( concat_col, p_contains_str)>0
1911: AND d.search_context_id = p_search_ctx_id
1912: AND( (l_search_merged ='Y' )

Line 1909: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1905: IF p_restrict_entity = 'CONTACTS'
1906: THEN
1907: OPEN x_cursor FOR
1908: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1909: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1910: WHERE contains( concat_col, p_contains_str)>0
1911: AND d.search_context_id = p_search_ctx_id
1912: AND( (l_search_merged ='Y' )
1913: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1920: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N3) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID

1916: END IF;
1917: IF p_restrict_entity = 'PARTY_SITES'
1918: THEN
1919: OPEN x_cursor FOR
1920: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N3) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1921: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1922: WHERE contains( concat_col, p_contains_str)>0
1923: AND d.search_context_id = p_search_ctx_id
1924: AND( (l_search_merged ='Y' )

Line 1921: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1917: IF p_restrict_entity = 'PARTY_SITES'
1918: THEN
1919: OPEN x_cursor FOR
1920: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N3) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1921: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1922: WHERE contains( concat_col, p_contains_str)>0
1923: AND d.search_context_id = p_search_ctx_id
1924: AND( (l_search_merged ='Y' )
1925: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1937: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID

1933: hz_utility_v2pub.debug(p_message=>'Single Party Scenario',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1934: END IF;
1935: IF p_search_rel_cpts = 'N' THEN
1936: OPEN x_cursor FOR
1937: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1938: FROM HZ_STAGED_CONTACT_POINTS stage
1939: WHERE contains( concat_col, p_contains_str)>0
1940: AND( (l_search_merged ='Y' )
1941: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1938: FROM HZ_STAGED_CONTACT_POINTS stage

1934: END IF;
1935: IF p_search_rel_cpts = 'N' THEN
1936: OPEN x_cursor FOR
1937: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1938: FROM HZ_STAGED_CONTACT_POINTS stage
1939: WHERE contains( concat_col, p_contains_str)>0
1940: AND( (l_search_merged ='Y' )
1941: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1942: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1946: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID

1942: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1943: AND stage.party_id = p_party_id;
1944: ELSE
1945: OPEN x_cursor FOR
1946: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1947: FROM HZ_STAGED_CONTACT_POINTS stage
1948: WHERE contains( concat_col, p_contains_str)>0
1949: AND( (l_search_merged ='Y' )
1950: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1947: FROM HZ_STAGED_CONTACT_POINTS stage

1943: AND stage.party_id = p_party_id;
1944: ELSE
1945: OPEN x_cursor FOR
1946: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1947: FROM HZ_STAGED_CONTACT_POINTS stage
1948: WHERE contains( concat_col, p_contains_str)>0
1949: AND( (l_search_merged ='Y' )
1950: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1951: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1954: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ stage.CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID

1950: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1951: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1952: AND stage.party_id = p_party_id
1953: UNION
1954: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ stage.CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID
1955: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1956: WHERE contains( concat_col, p_contains_str)>0
1957: AND r.object_id = p_party_id
1958: AND( (l_search_merged ='Y' )

Line 1955: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc

1951: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1952: AND stage.party_id = p_party_id
1953: UNION
1954: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ stage.CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID
1955: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1956: WHERE contains( concat_col, p_contains_str)>0
1957: AND r.object_id = p_party_id
1958: AND( (l_search_merged ='Y' )
1959: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1977: FROM HZ_STAGED_CONTACT_POINTS stage

1973: hz_utility_v2pub.debug(p_message=>'Either Search context id is null',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1974: END IF;
1975: OPEN x_cursor FOR
1976: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1977: FROM HZ_STAGED_CONTACT_POINTS stage
1978: WHERE contains( concat_col, p_contains_str)>0
1979: AND EXISTS (
1980: SELECT 1 FROM HZ_STAGED_PARTIES p
1981: WHERE p.PARTY_ID = stage.PARTY_ID

Line 1996: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID

1992: hz_utility_v2pub.debug(p_message=>'Search_context_id is not null',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1993: END IF;
1994: IF p_person_api = 'Y' THEN
1995: OPEN x_cursor FOR
1996: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1997: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1998: WHERE contains( concat_col, p_contains_str)>0
1999: AND d.search_context_id = p_search_ctx_id
2000: AND d.party_id = stage.party_id

Line 1997: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1993: END IF;
1994: IF p_person_api = 'Y' THEN
1995: OPEN x_cursor FOR
1996: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1997: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1998: WHERE contains( concat_col, p_contains_str)>0
1999: AND d.search_context_id = p_search_ctx_id
2000: AND d.party_id = stage.party_id
2001: AND( (l_search_merged ='Y' )

Line 2006: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, r.subject_id, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID

2002: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2003: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2004: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2005: UNION
2006: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, r.subject_id, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID
2007: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2008: WHERE contains( concat_col, p_contains_str)>0
2009: AND d.search_context_id = p_search_ctx_id
2010: AND d.party_id = r.subject_id

Line 2007: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage

2003: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2004: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2005: UNION
2006: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, r.subject_id, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID
2007: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2008: WHERE contains( concat_col, p_contains_str)>0
2009: AND d.search_context_id = p_search_ctx_id
2010: AND d.party_id = r.subject_id
2011: AND r.relationship_id = oc.party_relationship_id

Line 2019: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID

2015: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2016: AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
2017: ELSE
2018: OPEN x_cursor FOR
2019: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2020: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2021: WHERE contains( concat_col, p_contains_str)>0
2022: AND d.search_context_id = p_search_ctx_id
2023: AND d.party_id = stage.party_id

Line 2020: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2016: AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
2017: ELSE
2018: OPEN x_cursor FOR
2019: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2020: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2021: WHERE contains( concat_col, p_contains_str)>0
2022: AND d.search_context_id = p_search_ctx_id
2023: AND d.party_id = stage.party_id
2024: AND( (l_search_merged ='Y' )

Line 2040: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_U1) */';

2036: IF (l_check_dt > 0 ) THEN
2037: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2038: hz_utility_v2pub.debug(p_message=>'Restrict Sql has the selective_cpt hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2039: END IF;
2040: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_U1) */';
2041: ELSIF (l_check > 0 ) THEN
2042: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2043: hz_utility_v2pub.debug(p_message=>'Restrict Sql has the selective hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2044: END IF;

Line 2045: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */';

2041: ELSIF (l_check > 0 ) THEN
2042: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2043: hz_utility_v2pub.debug(p_message=>'Restrict Sql has the selective hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2044: END IF;
2045: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */';
2046: END IF;
2047: IF l_search_merged = 'Y' THEN
2048: l_status_sql := ' ' ;
2049: ELSIF l_search_merged = 'I' THEN

Line 2064: l_sqlstr := ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||

2060: p_restrict_sql1 := replace( p_restrict_sql, 'stage.', 'stage1.');
2061: ELSE
2062: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2063: END IF;
2064: l_sqlstr := ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2065: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2066: ' WHERE contains( concat_col, :cont)>0 '||
2067: ' AND (stage.org_contact_id is null '||
2068: ' AND( ('''||l_search_merged||''' =''Y'' ) '||

Line 2065: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2061: ELSE
2062: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2063: END IF;
2064: l_sqlstr := ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2065: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2066: ' WHERE contains( concat_col, :cont)>0 '||
2067: ' AND (stage.org_contact_id is null '||
2068: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2069: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 2074: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||

2070: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2071: ' AND ('||p_restrict_sql||'))' ||
2072: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2073: ' UNION ' ||
2074: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2075: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2076: ' WHERE contains( concat_col, :cont)>0 '||
2077: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2078: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 2075: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2071: ' AND ('||p_restrict_sql||'))' ||
2072: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2073: ' UNION ' ||
2074: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2075: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2076: ' WHERE contains( concat_col, :cont)>0 '||
2077: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2078: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2079: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

Line 2090: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2086: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2087: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2088: ELSE
2089: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2090: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2091: ' WHERE contains( concat_col, :cont)>0'||
2092: ' AND EXISTS ('||
2093: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2094: ' WHERE p.party_id = stage.party_id ' ||

Line 2107: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2103: ,p_dup_party_id, p_dup_party_id;
2104: END IF;
2105: ELSE
2106: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2107: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2108: ' WHERE contains( concat_col, :cont)>0'||
2109: ' AND EXISTS ('||
2110: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2111: ' WHERE p.party_id = stage.party_id ' ||