eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.HZ_MATCH_RULE_98 dependencies on HZ_STAGED_CONTACT_POINTS

Line 1709: 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, TX6, TX5

1705: END IF;
1706: IF p_restrict_entity = 'CONTACTS'
1707: THEN
1708: OPEN x_cursor FOR
1709: 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, TX6, TX5
1710: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1711: WHERE contains( concat_col, p_contains_str)>0
1712: AND d.search_context_id = p_search_ctx_id
1713: AND( (l_search_merged ='Y' )

Line 1710: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1706: IF p_restrict_entity = 'CONTACTS'
1707: THEN
1708: OPEN x_cursor FOR
1709: 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, TX6, TX5
1710: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1711: WHERE contains( concat_col, p_contains_str)>0
1712: AND d.search_context_id = p_search_ctx_id
1713: AND( (l_search_merged ='Y' )
1714: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1721: 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, TX6, TX5

1717: END IF;
1718: IF p_restrict_entity = 'PARTY_SITES'
1719: THEN
1720: OPEN x_cursor FOR
1721: 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, TX6, TX5
1722: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1723: WHERE contains( concat_col, p_contains_str)>0
1724: AND d.search_context_id = p_search_ctx_id
1725: AND( (l_search_merged ='Y' )

Line 1722: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1718: IF p_restrict_entity = 'PARTY_SITES'
1719: THEN
1720: OPEN x_cursor FOR
1721: 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, TX6, TX5
1722: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1723: WHERE contains( concat_col, p_contains_str)>0
1724: AND d.search_context_id = p_search_ctx_id
1725: AND( (l_search_merged ='Y' )
1726: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1738: SELECT /+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) / CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5

1734: 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);
1735: END IF;
1736: IF p_search_rel_cpts = 'N' THEN
1737: OPEN x_cursor FOR
1738: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1739: FROM HZ_STAGED_CONTACT_POINTS stage
1740: WHERE contains( concat_col, p_contains_str)>0
1741: AND( (l_search_merged ='Y' )
1742: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1739: FROM HZ_STAGED_CONTACT_POINTS stage

1735: END IF;
1736: IF p_search_rel_cpts = 'N' THEN
1737: OPEN x_cursor FOR
1738: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1739: FROM HZ_STAGED_CONTACT_POINTS stage
1740: WHERE contains( concat_col, p_contains_str)>0
1741: AND( (l_search_merged ='Y' )
1742: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1743: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1747: SELECT /+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) / CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5

1743: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1744: AND stage.party_id = p_party_id;
1745: ELSE
1746: OPEN x_cursor FOR
1747: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1748: FROM HZ_STAGED_CONTACT_POINTS stage
1749: WHERE contains( concat_col, p_contains_str)>0
1750: AND( (l_search_merged ='Y' )
1751: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1748: FROM HZ_STAGED_CONTACT_POINTS stage

1744: AND stage.party_id = p_party_id;
1745: ELSE
1746: OPEN x_cursor FOR
1747: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1748: FROM HZ_STAGED_CONTACT_POINTS stage
1749: WHERE contains( concat_col, p_contains_str)>0
1750: AND( (l_search_merged ='Y' )
1751: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1752: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1755: 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, TX6, TX5

1751: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1752: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1753: AND stage.party_id = p_party_id
1754: UNION
1755: 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, TX6, TX5
1756: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1757: WHERE contains( concat_col, p_contains_str)>0
1758: AND r.object_id = p_party_id
1759: AND( (l_search_merged ='Y' )

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

1752: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1753: AND stage.party_id = p_party_id
1754: UNION
1755: 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, TX6, TX5
1756: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1757: WHERE contains( concat_col, p_contains_str)>0
1758: AND r.object_id = p_party_id
1759: AND( (l_search_merged ='Y' )
1760: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1778: FROM HZ_STAGED_CONTACT_POINTS stage

1774: 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);
1775: END IF;
1776: OPEN x_cursor FOR
1777: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1778: FROM HZ_STAGED_CONTACT_POINTS stage
1779: WHERE contains( concat_col, p_contains_str)>0
1780: AND EXISTS (
1781: SELECT 1 FROM HZ_STAGED_PARTIES p
1782: WHERE p.PARTY_ID = stage.PARTY_ID

Line 1798: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1794: END IF;
1795: IF p_person_api = 'Y' THEN
1796: OPEN x_cursor FOR
1797: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1798: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1799: WHERE contains( concat_col, p_contains_str)>0
1800: AND d.search_context_id = p_search_ctx_id
1801: AND d.party_id = stage.party_id
1802: AND( (l_search_merged ='Y' )

Line 1807: 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, TX6, TX5

1803: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1804: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1805: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1806: UNION
1807: 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, TX6, TX5
1808: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1809: WHERE contains( concat_col, p_contains_str)>0
1810: AND d.search_context_id = p_search_ctx_id
1811: AND d.party_id = r.subject_id

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

1804: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1805: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1806: UNION
1807: 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, TX6, TX5
1808: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1809: WHERE contains( concat_col, p_contains_str)>0
1810: AND d.search_context_id = p_search_ctx_id
1811: AND d.party_id = r.subject_id
1812: AND r.relationship_id = oc.party_relationship_id

Line 1821: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1817: AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
1818: ELSE
1819: OPEN x_cursor FOR
1820: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1821: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1822: WHERE contains( concat_col, p_contains_str)>0
1823: AND d.search_context_id = p_search_ctx_id
1824: AND d.party_id = stage.party_id
1825: AND( (l_search_merged ='Y' )

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

1837: IF (l_check_dt > 0 ) THEN
1838: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1839: 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);
1840: END IF;
1841: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_U1) */';
1842: ELSIF (l_check > 0 ) THEN
1843: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1844: 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);
1845: END IF;

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

1842: ELSIF (l_check > 0 ) THEN
1843: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1844: 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);
1845: END IF;
1846: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */';
1847: END IF;
1848: IF l_search_merged = 'Y' THEN
1849: l_status_sql := ' ' ;
1850: ELSIF l_search_merged = 'I' THEN

Line 1865: 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, TX6, TX5'||

1861: p_restrict_sql1 := replace( p_restrict_sql, 'stage.', 'stage1.');
1862: ELSE
1863: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1864: END IF;
1865: 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, TX6, TX5'||
1866: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1867: ' WHERE contains( concat_col, :cont)>0 '||
1868: ' AND (stage.org_contact_id is null '||
1869: ' AND( ('''||l_search_merged||''' =''Y'' ) '||

Line 1866: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

1862: ELSE
1863: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1864: END IF;
1865: 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, TX6, TX5'||
1866: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1867: ' WHERE contains( concat_col, :cont)>0 '||
1868: ' AND (stage.org_contact_id is null '||
1869: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1870: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

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

1871: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1872: ' AND ('||p_restrict_sql||'))' ||
1873: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1874: ' UNION ' ||
1875: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
1876: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1877: ' WHERE contains( concat_col, :cont)>0 '||
1878: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1879: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 1876: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

1872: ' AND ('||p_restrict_sql||'))' ||
1873: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1874: ' UNION ' ||
1875: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
1876: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1877: ' WHERE contains( concat_col, :cont)>0 '||
1878: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1879: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1880: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

Line 1891: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

1887: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1888: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1889: ELSE
1890: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
1891: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1892: ' WHERE contains( concat_col, :cont)>0'||
1893: ' AND EXISTS ('||
1894: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1895: ' WHERE p.party_id = stage.party_id ' ||

Line 1908: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

1904: ,p_dup_party_id, p_dup_party_id;
1905: END IF;
1906: ELSE
1907: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
1908: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1909: ' WHERE contains( concat_col, :cont)>0'||
1910: ' AND EXISTS ('||
1911: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1912: ' WHERE p.party_id = stage.party_id ' ||