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_3 dependencies on HZ_STAGED_CONTACT_POINTS

Line 1603: 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

1599: END IF;
1600: IF p_restrict_entity = 'CONTACTS'
1601: THEN
1602: OPEN x_cursor FOR
1603: 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
1604: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1605: WHERE contains( concat_col, p_contains_str)>0
1606: AND d.search_context_id = p_search_ctx_id
1607: AND( (l_search_merged ='Y' )

Line 1604: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1600: IF p_restrict_entity = 'CONTACTS'
1601: THEN
1602: OPEN x_cursor FOR
1603: 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
1604: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1605: WHERE contains( concat_col, p_contains_str)>0
1606: AND d.search_context_id = p_search_ctx_id
1607: AND( (l_search_merged ='Y' )
1608: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1615: 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

1611: END IF;
1612: IF p_restrict_entity = 'PARTY_SITES'
1613: THEN
1614: OPEN x_cursor FOR
1615: 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
1616: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1617: WHERE contains( concat_col, p_contains_str)>0
1618: AND d.search_context_id = p_search_ctx_id
1619: AND( (l_search_merged ='Y' )

Line 1616: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1612: IF p_restrict_entity = 'PARTY_SITES'
1613: THEN
1614: OPEN x_cursor FOR
1615: 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
1616: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1617: WHERE contains( concat_col, p_contains_str)>0
1618: AND d.search_context_id = p_search_ctx_id
1619: AND( (l_search_merged ='Y' )
1620: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1632: 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

1628: 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);
1629: END IF;
1630: IF p_search_rel_cpts = 'N' THEN
1631: OPEN x_cursor FOR
1632: 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
1633: FROM HZ_STAGED_CONTACT_POINTS stage
1634: WHERE contains( concat_col, p_contains_str)>0
1635: AND( (l_search_merged ='Y' )
1636: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1633: FROM HZ_STAGED_CONTACT_POINTS stage

1629: END IF;
1630: IF p_search_rel_cpts = 'N' THEN
1631: OPEN x_cursor FOR
1632: 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
1633: FROM HZ_STAGED_CONTACT_POINTS stage
1634: WHERE contains( concat_col, p_contains_str)>0
1635: AND( (l_search_merged ='Y' )
1636: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1637: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1641: 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

1637: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1638: AND stage.party_id = p_party_id;
1639: ELSE
1640: OPEN x_cursor FOR
1641: 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
1642: FROM HZ_STAGED_CONTACT_POINTS stage
1643: WHERE contains( concat_col, p_contains_str)>0
1644: AND( (l_search_merged ='Y' )
1645: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1642: FROM HZ_STAGED_CONTACT_POINTS stage

1638: AND stage.party_id = p_party_id;
1639: ELSE
1640: OPEN x_cursor FOR
1641: 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
1642: FROM HZ_STAGED_CONTACT_POINTS stage
1643: WHERE contains( concat_col, p_contains_str)>0
1644: AND( (l_search_merged ='Y' )
1645: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1646: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1649: 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

1645: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1646: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1647: AND stage.party_id = p_party_id
1648: UNION
1649: 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
1650: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1651: WHERE contains( concat_col, p_contains_str)>0
1652: AND r.object_id = p_party_id
1653: AND( (l_search_merged ='Y' )

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

1646: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1647: AND stage.party_id = p_party_id
1648: UNION
1649: 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
1650: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1651: WHERE contains( concat_col, p_contains_str)>0
1652: AND r.object_id = p_party_id
1653: AND( (l_search_merged ='Y' )
1654: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1672: FROM HZ_STAGED_CONTACT_POINTS stage

1668: 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);
1669: END IF;
1670: OPEN x_cursor FOR
1671: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1672: FROM HZ_STAGED_CONTACT_POINTS stage
1673: WHERE contains( concat_col, p_contains_str)>0
1674: AND EXISTS (
1675: SELECT 1 FROM HZ_STAGED_PARTIES p
1676: WHERE p.PARTY_ID = stage.PARTY_ID

Line 1692: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1688: END IF;
1689: IF p_person_api = 'Y' THEN
1690: OPEN x_cursor FOR
1691: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1692: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1693: WHERE contains( concat_col, p_contains_str)>0
1694: AND d.search_context_id = p_search_ctx_id
1695: AND d.party_id = stage.party_id
1696: AND( (l_search_merged ='Y' )

Line 1701: 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

1697: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1698: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1699: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1700: UNION
1701: 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
1702: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1703: WHERE contains( concat_col, p_contains_str)>0
1704: AND d.search_context_id = p_search_ctx_id
1705: AND d.party_id = r.subject_id

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

1698: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1699: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1700: UNION
1701: 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
1702: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1703: WHERE contains( concat_col, p_contains_str)>0
1704: AND d.search_context_id = p_search_ctx_id
1705: AND d.party_id = r.subject_id
1706: AND r.relationship_id = oc.party_relationship_id

Line 1715: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1711: AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
1712: ELSE
1713: OPEN x_cursor FOR
1714: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1715: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1716: WHERE contains( concat_col, p_contains_str)>0
1717: AND d.search_context_id = p_search_ctx_id
1718: AND d.party_id = stage.party_id
1719: AND( (l_search_merged ='Y' )

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

1731: IF (l_check_dt > 0 ) THEN
1732: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1733: 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);
1734: END IF;
1735: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_U1) */';
1736: ELSIF (l_check > 0 ) THEN
1737: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1738: 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);
1739: END IF;

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

1736: ELSIF (l_check > 0 ) THEN
1737: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1738: 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);
1739: END IF;
1740: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */';
1741: END IF;
1742: IF l_search_merged = 'Y' THEN
1743: l_status_sql := ' ' ;
1744: ELSIF l_search_merged = 'I' THEN

Line 1759: 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'||

1755: p_restrict_sql1 := replace( p_restrict_sql, 'stage.', 'stage1.');
1756: ELSE
1757: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1758: END IF;
1759: 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'||
1760: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1761: ' WHERE contains( concat_col, :cont)>0 '||
1762: ' AND (stage.org_contact_id is null '||
1763: ' AND( ('''||l_search_merged||''' =''Y'' ) '||

Line 1760: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

1756: ELSE
1757: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1758: END IF;
1759: 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'||
1760: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1761: ' WHERE contains( concat_col, :cont)>0 '||
1762: ' AND (stage.org_contact_id is null '||
1763: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1764: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 1769: ' 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'||

1765: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1766: ' AND ('||p_restrict_sql||'))' ||
1767: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1768: ' UNION ' ||
1769: ' 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'||
1770: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1771: ' WHERE contains( concat_col, :cont)>0 '||
1772: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1773: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 1770: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

1766: ' AND ('||p_restrict_sql||'))' ||
1767: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1768: ' UNION ' ||
1769: ' 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'||
1770: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1771: ' WHERE contains( concat_col, :cont)>0 '||
1772: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1773: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1774: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

Line 1785: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

1781: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1782: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1783: ELSE
1784: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
1785: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1786: ' WHERE contains( concat_col, :cont)>0'||
1787: ' AND EXISTS ('||
1788: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1789: ' WHERE p.party_id = stage.party_id ' ||

Line 1802: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

1798: ,p_dup_party_id, p_dup_party_id;
1799: END IF;
1800: ELSE
1801: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
1802: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1803: ' WHERE contains( concat_col, :cont)>0'||
1804: ' AND EXISTS ('||
1805: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1806: ' WHERE p.party_id = stage.party_id ' ||