APPS.HZ_MATCH_RULE_39 dependencies on HZ_STAGED_CONTACT_POINTS

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

1671: END IF;
1672: IF p_restrict_entity = 'CONTACTS'
1673: THEN
1674: OPEN x_cursor FOR
1675: 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
1676: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1677: WHERE contains( concat_col, p_contains_str)>0
1678: AND d.search_context_id = p_search_ctx_id
1679: AND( (l_search_merged ='Y' )

Line 1676: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1672: IF p_restrict_entity = 'CONTACTS'
1673: THEN
1674: OPEN x_cursor FOR
1675: 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
1676: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1677: WHERE contains( concat_col, p_contains_str)>0
1678: AND d.search_context_id = p_search_ctx_id
1679: AND( (l_search_merged ='Y' )
1680: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1683: END IF;
1684: IF p_restrict_entity = 'PARTY_SITES'
1685: THEN
1686: OPEN x_cursor FOR
1687: 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
1688: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1689: WHERE contains( concat_col, p_contains_str)>0
1690: AND d.search_context_id = p_search_ctx_id
1691: AND( (l_search_merged ='Y' )

Line 1688: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1684: IF p_restrict_entity = 'PARTY_SITES'
1685: THEN
1686: OPEN x_cursor FOR
1687: 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
1688: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1689: WHERE contains( concat_col, p_contains_str)>0
1690: AND d.search_context_id = p_search_ctx_id
1691: AND( (l_search_merged ='Y' )
1692: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1700: 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);
1701: END IF;
1702: IF p_search_rel_cpts = 'N' THEN
1703: OPEN x_cursor FOR
1704: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1705: FROM HZ_STAGED_CONTACT_POINTS stage
1706: WHERE contains( concat_col, p_contains_str)>0
1707: AND( (l_search_merged ='Y' )
1708: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1705: FROM HZ_STAGED_CONTACT_POINTS stage

1701: END IF;
1702: IF p_search_rel_cpts = 'N' THEN
1703: OPEN x_cursor FOR
1704: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1705: FROM HZ_STAGED_CONTACT_POINTS stage
1706: WHERE contains( concat_col, p_contains_str)>0
1707: AND( (l_search_merged ='Y' )
1708: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1709: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1709: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1710: AND stage.party_id = p_party_id;
1711: ELSE
1712: OPEN x_cursor FOR
1713: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1714: FROM HZ_STAGED_CONTACT_POINTS stage
1715: WHERE contains( concat_col, p_contains_str)>0
1716: AND( (l_search_merged ='Y' )
1717: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1714: FROM HZ_STAGED_CONTACT_POINTS stage

1710: AND stage.party_id = p_party_id;
1711: ELSE
1712: OPEN x_cursor FOR
1713: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1714: FROM HZ_STAGED_CONTACT_POINTS stage
1715: WHERE contains( concat_col, p_contains_str)>0
1716: AND( (l_search_merged ='Y' )
1717: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1718: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1717: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1718: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1719: AND stage.party_id = p_party_id
1720: UNION
1721: 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
1722: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1723: WHERE contains( concat_col, p_contains_str)>0
1724: AND r.object_id = p_party_id
1725: AND( (l_search_merged ='Y' )

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

1718: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1719: AND stage.party_id = p_party_id
1720: UNION
1721: 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
1722: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1723: WHERE contains( concat_col, p_contains_str)>0
1724: AND r.object_id = p_party_id
1725: AND( (l_search_merged ='Y' )
1726: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1744: FROM HZ_STAGED_CONTACT_POINTS stage

1740: 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);
1741: END IF;
1742: OPEN x_cursor FOR
1743: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1744: FROM HZ_STAGED_CONTACT_POINTS stage
1745: WHERE contains( concat_col, p_contains_str)>0
1746: AND EXISTS (
1747: SELECT 1 FROM HZ_STAGED_PARTIES p
1748: WHERE p.PARTY_ID = stage.PARTY_ID

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

1759: 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);
1760: END IF;
1761: IF p_person_api = 'Y' THEN
1762: OPEN x_cursor FOR
1763: 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
1764: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1765: WHERE contains( concat_col, p_contains_str)>0
1766: AND d.search_context_id = p_search_ctx_id
1767: AND d.party_id = stage.party_id

Line 1764: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1760: END IF;
1761: IF p_person_api = 'Y' THEN
1762: OPEN x_cursor FOR
1763: 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
1764: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1765: WHERE contains( concat_col, p_contains_str)>0
1766: AND d.search_context_id = p_search_ctx_id
1767: AND d.party_id = stage.party_id
1768: AND( (l_search_merged ='Y' )

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

1769: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1770: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1771: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1772: UNION
1773: 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
1774: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1775: WHERE contains( concat_col, p_contains_str)>0
1776: AND d.search_context_id = p_search_ctx_id
1777: AND d.party_id = r.subject_id

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

1770: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1771: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1772: UNION
1773: 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
1774: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1775: WHERE contains( concat_col, p_contains_str)>0
1776: AND d.search_context_id = p_search_ctx_id
1777: AND d.party_id = r.subject_id
1778: AND r.relationship_id = oc.party_relationship_id

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

1782: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1783: AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
1784: ELSE
1785: OPEN x_cursor FOR
1786: 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
1787: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1788: WHERE contains( concat_col, p_contains_str)>0
1789: AND d.search_context_id = p_search_ctx_id
1790: AND d.party_id = stage.party_id

Line 1787: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage

1783: AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
1784: ELSE
1785: OPEN x_cursor FOR
1786: 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
1787: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1788: WHERE contains( concat_col, p_contains_str)>0
1789: AND d.search_context_id = p_search_ctx_id
1790: AND d.party_id = stage.party_id
1791: AND( (l_search_merged ='Y' )

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

1803: IF (l_check_dt > 0 ) THEN
1804: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1805: 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);
1806: END IF;
1807: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_U1) */';
1808: ELSIF (l_check > 0 ) THEN
1809: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1810: 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);
1811: END IF;

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

1808: ELSIF (l_check > 0 ) THEN
1809: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1810: 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);
1811: END IF;
1812: l_hint := '/*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */';
1813: END IF;
1814: IF l_search_merged = 'Y' THEN
1815: l_status_sql := ' ' ;
1816: ELSIF l_search_merged = 'I' THEN

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

1827: p_restrict_sql1 := replace( p_restrict_sql, 'stage.', 'stage1.');
1828: ELSE
1829: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1830: END IF;
1831: 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 '||
1832: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1833: ' WHERE contains( concat_col, :cont)>0 '||
1834: ' AND (stage.org_contact_id is null '||
1835: ' AND( ('''||l_search_merged||''' =''Y'' ) '||

Line 1832: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

1828: ELSE
1829: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1830: END IF;
1831: 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 '||
1832: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1833: ' WHERE contains( concat_col, :cont)>0 '||
1834: ' AND (stage.org_contact_id is null '||
1835: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1836: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

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

1837: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1838: ' AND ('||p_restrict_sql||'))' ||
1839: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1840: ' UNION ' ||
1841: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1842: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1843: ' WHERE contains( concat_col, :cont)>0 '||
1844: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1845: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||

Line 1842: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

1838: ' AND ('||p_restrict_sql||'))' ||
1839: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1840: ' UNION ' ||
1841: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1842: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1843: ' WHERE contains( concat_col, :cont)>0 '||
1844: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1845: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1846: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

Line 1857: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

1853: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1854: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1855: ELSE
1856: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1857: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1858: ' WHERE contains( concat_col, :cont)>0'||
1859: ' AND EXISTS ('||
1860: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1861: ' WHERE p.party_id = stage.party_id ' ||

Line 1874: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

1870: ,p_dup_party_id, p_dup_party_id;
1871: END IF;
1872: ELSE
1873: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1874: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1875: ' WHERE contains( concat_col, :cont)>0'||
1876: ' AND EXISTS ('||
1877: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1878: ' WHERE p.party_id = stage.party_id ' ||