APPS.HZ_MATCH_RULE_40 dependencies on HZ_STAGED_CONTACT_POINTS

Line 1954: 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 , TX1

1950: END IF;
1951: IF p_restrict_entity = 'CONTACTS'
1952: THEN
1953: OPEN x_cursor FOR
1954: 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 , TX1
1955: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1956: WHERE contains( concat_col, p_contains_str)>0
1957: AND d.search_context_id = p_search_ctx_id
1958: AND( (l_search_merged ='Y' )

Line 1955: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1951: IF p_restrict_entity = 'CONTACTS'
1952: THEN
1953: OPEN x_cursor FOR
1954: 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 , TX1
1955: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1956: WHERE contains( concat_col, p_contains_str)>0
1957: AND d.search_context_id = p_search_ctx_id
1958: AND( (l_search_merged ='Y' )
1959: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1966: 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 , TX1

1962: END IF;
1963: IF p_restrict_entity = 'PARTY_SITES'
1964: THEN
1965: OPEN x_cursor FOR
1966: 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 , TX1
1967: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1968: WHERE contains( concat_col, p_contains_str)>0
1969: AND d.search_context_id = p_search_ctx_id
1970: AND( (l_search_merged ='Y' )

Line 1967: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1963: IF p_restrict_entity = 'PARTY_SITES'
1964: THEN
1965: OPEN x_cursor FOR
1966: 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 , TX1
1967: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1968: WHERE contains( concat_col, p_contains_str)>0
1969: AND d.search_context_id = p_search_ctx_id
1970: AND( (l_search_merged ='Y' )
1971: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1979: 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);
1980: END IF;
1981: IF p_search_rel_cpts = 'N' THEN
1982: OPEN x_cursor FOR
1983: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1
1984: FROM HZ_STAGED_CONTACT_POINTS stage
1985: WHERE contains( concat_col, p_contains_str)>0
1986: AND( (l_search_merged ='Y' )
1987: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1984: FROM HZ_STAGED_CONTACT_POINTS stage

1980: END IF;
1981: IF p_search_rel_cpts = 'N' THEN
1982: OPEN x_cursor FOR
1983: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1
1984: FROM HZ_STAGED_CONTACT_POINTS stage
1985: WHERE contains( concat_col, p_contains_str)>0
1986: AND( (l_search_merged ='Y' )
1987: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1988: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1988: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1989: AND stage.party_id = p_party_id;
1990: ELSE
1991: OPEN x_cursor FOR
1992: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1
1993: FROM HZ_STAGED_CONTACT_POINTS stage
1994: WHERE contains( concat_col, p_contains_str)>0
1995: AND( (l_search_merged ='Y' )
1996: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1993: FROM HZ_STAGED_CONTACT_POINTS stage

1989: AND stage.party_id = p_party_id;
1990: ELSE
1991: OPEN x_cursor FOR
1992: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1
1993: FROM HZ_STAGED_CONTACT_POINTS stage
1994: WHERE contains( concat_col, p_contains_str)>0
1995: AND( (l_search_merged ='Y' )
1996: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1997: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 2000: 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 , TX1

1996: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1997: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1998: AND stage.party_id = p_party_id
1999: UNION
2000: 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 , TX1
2001: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2002: WHERE contains( concat_col, p_contains_str)>0
2003: AND r.object_id = p_party_id
2004: AND( (l_search_merged ='Y' )

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

1997: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1998: AND stage.party_id = p_party_id
1999: UNION
2000: 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 , TX1
2001: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2002: WHERE contains( concat_col, p_contains_str)>0
2003: AND r.object_id = p_party_id
2004: AND( (l_search_merged ='Y' )
2005: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 2023: FROM HZ_STAGED_CONTACT_POINTS stage

2019: 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);
2020: END IF;
2021: OPEN x_cursor FOR
2022: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1
2023: FROM HZ_STAGED_CONTACT_POINTS stage
2024: WHERE contains( concat_col, p_contains_str)>0
2025: AND EXISTS (
2026: SELECT 1 FROM HZ_STAGED_PARTIES p
2027: WHERE p.PARTY_ID = stage.PARTY_ID

Line 2042: 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 , TX1

2038: 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);
2039: END IF;
2040: IF p_person_api = 'Y' THEN
2041: OPEN x_cursor FOR
2042: 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 , TX1
2043: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2044: WHERE contains( concat_col, p_contains_str)>0
2045: AND d.search_context_id = p_search_ctx_id
2046: AND d.party_id = stage.party_id

Line 2043: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2039: END IF;
2040: IF p_person_api = 'Y' THEN
2041: OPEN x_cursor FOR
2042: 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 , TX1
2043: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2044: WHERE contains( concat_col, p_contains_str)>0
2045: AND d.search_context_id = p_search_ctx_id
2046: AND d.party_id = stage.party_id
2047: AND( (l_search_merged ='Y' )

Line 2052: 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 , TX1

2048: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2049: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2050: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2051: UNION
2052: 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 , TX1
2053: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2054: WHERE contains( concat_col, p_contains_str)>0
2055: AND d.search_context_id = p_search_ctx_id
2056: AND d.party_id = r.subject_id

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

2049: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2050: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2051: UNION
2052: 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 , TX1
2053: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2054: WHERE contains( concat_col, p_contains_str)>0
2055: AND d.search_context_id = p_search_ctx_id
2056: AND d.party_id = r.subject_id
2057: AND r.relationship_id = oc.party_relationship_id

Line 2065: 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 , TX1

2061: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2062: AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
2063: ELSE
2064: OPEN x_cursor FOR
2065: 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 , TX1
2066: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2067: WHERE contains( concat_col, p_contains_str)>0
2068: AND d.search_context_id = p_search_ctx_id
2069: AND d.party_id = stage.party_id

Line 2066: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

2062: AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
2063: ELSE
2064: OPEN x_cursor FOR
2065: 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 , TX1
2066: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2067: WHERE contains( concat_col, p_contains_str)>0
2068: AND d.search_context_id = p_search_ctx_id
2069: AND d.party_id = stage.party_id
2070: AND( (l_search_merged ='Y' )

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

2082: IF (l_check_dt > 0 ) THEN
2083: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2084: 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);
2085: END IF;
2086: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_U1) */';
2087: ELSIF (l_check > 0 ) THEN
2088: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2089: 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);
2090: END IF;

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

2087: ELSIF (l_check > 0 ) THEN
2088: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2089: 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);
2090: END IF;
2091: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */';
2092: END IF;
2093: IF l_search_merged = 'Y' THEN
2094: l_status_sql := ' ' ;
2095: ELSIF l_search_merged = 'I' THEN

Line 2110: 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 , TX1'||

2106: p_restrict_sql1 := replace( p_restrict_sql, 'stage.', 'stage1.');
2107: ELSE
2108: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2109: END IF;
2110: 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 , TX1'||
2111: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2112: ' WHERE contains( concat_col, :cont)>0 '||
2113: ' AND (stage.org_contact_id is null '||
2114: ' AND( ('''||l_search_merged||''' =''Y'' ) '||

Line 2111: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2107: ELSE
2108: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2109: END IF;
2110: 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 , TX1'||
2111: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2112: ' WHERE contains( concat_col, :cont)>0 '||
2113: ' AND (stage.org_contact_id is null '||
2114: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2115: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

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

2116: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2117: ' AND ('||p_restrict_sql||'))' ||
2118: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2119: ' UNION ' ||
2120: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1'||
2121: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2122: ' WHERE contains( concat_col, :cont)>0 '||
2123: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2124: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 2121: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2117: ' AND ('||p_restrict_sql||'))' ||
2118: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2119: ' UNION ' ||
2120: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1'||
2121: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2122: ' WHERE contains( concat_col, :cont)>0 '||
2123: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2124: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2125: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

Line 2136: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2132: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2133: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2134: ELSE
2135: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1'||
2136: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2137: ' WHERE contains( concat_col, :cont)>0'||
2138: ' AND EXISTS ('||
2139: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2140: ' WHERE p.party_id = stage.party_id ' ||

Line 2153: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

2149: ,p_dup_party_id, p_dup_party_id;
2150: END IF;
2151: ELSE
2152: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1'||
2153: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2154: ' WHERE contains( concat_col, :cont)>0'||
2155: ' AND EXISTS ('||
2156: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2157: ' WHERE p.party_id = stage.party_id ' ||