APPS.HZ_MATCH_RULE_61 dependencies on HZ_STAGED_CONTACT_POINTS

Line 1887: 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 , TX8, TX7

1883: END IF;
1884: IF p_restrict_entity = 'CONTACTS'
1885: THEN
1886: OPEN x_cursor FOR
1887: 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 , TX8, TX7
1888: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1889: WHERE contains( concat_col, p_contains_str)>0
1890: AND d.search_context_id = p_search_ctx_id
1891: AND( (l_search_merged ='Y' )

Line 1888: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1884: IF p_restrict_entity = 'CONTACTS'
1885: THEN
1886: OPEN x_cursor FOR
1887: 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 , TX8, TX7
1888: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1889: WHERE contains( concat_col, p_contains_str)>0
1890: AND d.search_context_id = p_search_ctx_id
1891: AND( (l_search_merged ='Y' )
1892: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1899: 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 , TX8, TX7

1895: END IF;
1896: IF p_restrict_entity = 'PARTY_SITES'
1897: THEN
1898: OPEN x_cursor FOR
1899: 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 , TX8, TX7
1900: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1901: WHERE contains( concat_col, p_contains_str)>0
1902: AND d.search_context_id = p_search_ctx_id
1903: AND( (l_search_merged ='Y' )

Line 1900: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1896: IF p_restrict_entity = 'PARTY_SITES'
1897: THEN
1898: OPEN x_cursor FOR
1899: 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 , TX8, TX7
1900: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1901: WHERE contains( concat_col, p_contains_str)>0
1902: AND d.search_context_id = p_search_ctx_id
1903: AND( (l_search_merged ='Y' )
1904: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1912: 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);
1913: END IF;
1914: IF p_search_rel_cpts = 'N' THEN
1915: OPEN x_cursor FOR
1916: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX8, TX7
1917: FROM HZ_STAGED_CONTACT_POINTS stage
1918: WHERE contains( concat_col, p_contains_str)>0
1919: AND( (l_search_merged ='Y' )
1920: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1917: FROM HZ_STAGED_CONTACT_POINTS stage

1913: END IF;
1914: IF p_search_rel_cpts = 'N' THEN
1915: OPEN x_cursor FOR
1916: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX8, TX7
1917: FROM HZ_STAGED_CONTACT_POINTS stage
1918: WHERE contains( concat_col, p_contains_str)>0
1919: AND( (l_search_merged ='Y' )
1920: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1921: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1921: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1922: AND stage.party_id = p_party_id;
1923: ELSE
1924: OPEN x_cursor FOR
1925: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX8, TX7
1926: FROM HZ_STAGED_CONTACT_POINTS stage
1927: WHERE contains( concat_col, p_contains_str)>0
1928: AND( (l_search_merged ='Y' )
1929: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1926: FROM HZ_STAGED_CONTACT_POINTS stage

1922: AND stage.party_id = p_party_id;
1923: ELSE
1924: OPEN x_cursor FOR
1925: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX8, TX7
1926: FROM HZ_STAGED_CONTACT_POINTS stage
1927: WHERE contains( concat_col, p_contains_str)>0
1928: AND( (l_search_merged ='Y' )
1929: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1930: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1933: 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 , TX8, TX7

1929: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1930: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1931: AND stage.party_id = p_party_id
1932: UNION
1933: 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 , TX8, TX7
1934: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1935: WHERE contains( concat_col, p_contains_str)>0
1936: AND r.object_id = p_party_id
1937: AND( (l_search_merged ='Y' )

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

1930: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1931: AND stage.party_id = p_party_id
1932: UNION
1933: 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 , TX8, TX7
1934: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1935: WHERE contains( concat_col, p_contains_str)>0
1936: AND r.object_id = p_party_id
1937: AND( (l_search_merged ='Y' )
1938: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1956: FROM HZ_STAGED_CONTACT_POINTS stage

1952: 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);
1953: END IF;
1954: OPEN x_cursor FOR
1955: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX8, TX7
1956: FROM HZ_STAGED_CONTACT_POINTS stage
1957: WHERE contains( concat_col, p_contains_str)>0
1958: AND EXISTS (
1959: SELECT 1 FROM HZ_STAGED_PARTIES p
1960: WHERE p.PARTY_ID = stage.PARTY_ID

Line 1975: 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 , TX8, TX7

1971: 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);
1972: END IF;
1973: IF p_person_api = 'Y' THEN
1974: OPEN x_cursor FOR
1975: 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 , TX8, TX7
1976: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1977: WHERE contains( concat_col, p_contains_str)>0
1978: AND d.search_context_id = p_search_ctx_id
1979: AND d.party_id = stage.party_id

Line 1976: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1972: END IF;
1973: IF p_person_api = 'Y' THEN
1974: OPEN x_cursor FOR
1975: 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 , TX8, TX7
1976: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1977: WHERE contains( concat_col, p_contains_str)>0
1978: AND d.search_context_id = p_search_ctx_id
1979: AND d.party_id = stage.party_id
1980: AND( (l_search_merged ='Y' )

Line 1985: 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 , TX8, TX7

1981: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1982: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1983: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1984: UNION
1985: 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 , TX8, TX7
1986: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1987: WHERE contains( concat_col, p_contains_str)>0
1988: AND d.search_context_id = p_search_ctx_id
1989: AND d.party_id = r.subject_id

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

1982: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1983: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1984: UNION
1985: 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 , TX8, TX7
1986: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1987: WHERE contains( concat_col, p_contains_str)>0
1988: AND d.search_context_id = p_search_ctx_id
1989: AND d.party_id = r.subject_id
1990: AND r.relationship_id = oc.party_relationship_id

Line 1998: 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 , TX8, TX7

1994: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1995: AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
1996: ELSE
1997: OPEN x_cursor FOR
1998: 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 , TX8, TX7
1999: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2000: WHERE contains( concat_col, p_contains_str)>0
2001: AND d.search_context_id = p_search_ctx_id
2002: AND d.party_id = stage.party_id

Line 1999: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1995: AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
1996: ELSE
1997: OPEN x_cursor FOR
1998: 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 , TX8, TX7
1999: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2000: WHERE contains( concat_col, p_contains_str)>0
2001: AND d.search_context_id = p_search_ctx_id
2002: AND d.party_id = stage.party_id
2003: AND( (l_search_merged ='Y' )

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

2015: IF (l_check_dt > 0 ) THEN
2016: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2017: 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);
2018: END IF;
2019: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_U1) */';
2020: ELSIF (l_check > 0 ) THEN
2021: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2022: 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);
2023: END IF;

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

2020: ELSIF (l_check > 0 ) THEN
2021: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2022: 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);
2023: END IF;
2024: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */';
2025: END IF;
2026: IF l_search_merged = 'Y' THEN
2027: l_status_sql := ' ' ;
2028: ELSIF l_search_merged = 'I' THEN

Line 2043: 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 , TX8, TX7'||

2039: p_restrict_sql1 := replace( p_restrict_sql, 'stage.', 'stage1.');
2040: ELSE
2041: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2042: END IF;
2043: 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 , TX8, TX7'||
2044: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2045: ' WHERE contains( concat_col, :cont)>0 '||
2046: ' AND (stage.org_contact_id is null '||
2047: ' AND( ('''||l_search_merged||''' =''Y'' ) '||

Line 2044: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2040: ELSE
2041: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2042: END IF;
2043: 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 , TX8, TX7'||
2044: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2045: ' WHERE contains( concat_col, :cont)>0 '||
2046: ' AND (stage.org_contact_id is null '||
2047: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2048: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

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

2049: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2050: ' AND ('||p_restrict_sql||'))' ||
2051: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2052: ' UNION ' ||
2053: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX8, TX7'||
2054: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2055: ' WHERE contains( concat_col, :cont)>0 '||
2056: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2057: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 2054: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2050: ' AND ('||p_restrict_sql||'))' ||
2051: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2052: ' UNION ' ||
2053: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX8, TX7'||
2054: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2055: ' WHERE contains( concat_col, :cont)>0 '||
2056: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2057: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2058: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

Line 2069: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2065: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2066: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2067: ELSE
2068: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX8, TX7'||
2069: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2070: ' WHERE contains( concat_col, :cont)>0'||
2071: ' AND EXISTS ('||
2072: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2073: ' WHERE p.party_id = stage.party_id ' ||

Line 2086: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2082: ,p_dup_party_id, p_dup_party_id;
2083: END IF;
2084: ELSE
2085: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX8, TX7'||
2086: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2087: ' WHERE contains( concat_col, :cont)>0'||
2088: ' AND EXISTS ('||
2089: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2090: ' WHERE p.party_id = stage.party_id ' ||