APPS.HZ_MATCH_RULE_50 dependencies on HZ_STAGED_PARTIES

Line 1120: FROM HZ_STAGED_PARTIES stage

1116: BEGIN
1117: IF p_contains_str IS NULL THEN
1118: OPEN x_cursor FOR
1119: SELECT PARTY_ID , TX2, TX59, lpad(rtrim(TX41),9,chr(48)), TX45
1120: FROM HZ_STAGED_PARTIES stage
1121: WHERE PARTY_ID = p_dup_party_id;
1122: ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN
1123: IF p_search_ctx_id IS NULL THEN
1124: OPEN x_cursor FOR

Line 1126: FROM HZ_SRCH_PARTIES srch, HZ_STAGED_PARTIES stage

1122: ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN
1123: IF p_search_ctx_id IS NULL THEN
1124: OPEN x_cursor FOR
1125: SELECT /*+ ORDERED */ stage.PARTY_ID , stage.TX2, stage.TX59, lpad(rtrim(stage.TX41),9,chr(48)), stage.TX45
1126: FROM HZ_SRCH_PARTIES srch, HZ_STAGED_PARTIES stage
1127: WHERE
1128: srch.batch_id = -1
1129: AND
1130: (

Line 1152: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , stage.TX2, stage.TX59, lpad(rtrim(stage.TX41),9,chr(48)), stage.TX45

1148: AND (nvl(p_search_merged,'N')='Y' OR nvl(stage.status,'A') in ('A'))
1149: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
1150: ELSE
1151: OPEN x_cursor FOR
1152: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , stage.TX2, stage.TX59, lpad(rtrim(stage.TX41),9,chr(48)), stage.TX45
1153: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage, HZ_SRCH_PARTIES srch
1154: WHERE
1155: srch.batch_id = -1
1156: AND

Line 1153: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage, HZ_SRCH_PARTIES srch

1149: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
1150: ELSE
1151: OPEN x_cursor FOR
1152: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , stage.TX2, stage.TX59, lpad(rtrim(stage.TX41),9,chr(48)), stage.TX45
1153: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage, HZ_SRCH_PARTIES srch
1154: WHERE
1155: srch.batch_id = -1
1156: AND
1157: (

Line 1183: ' FROM HZ_SRCH_PARTIES srch, HZ_STAGED_PARTIES stage'||

1179: END IF;
1180: ELSE
1181: IF p_search_ctx_id IS NULL THEN
1182: l_sqlstr := 'SELECT /*+ ORDERED */ stage.PARTY_ID , stage.TX2, stage.TX59, lpad(rtrim(stage.TX41),9,chr(48)), stage.TX45'||
1183: ' FROM HZ_SRCH_PARTIES srch, HZ_STAGED_PARTIES stage'||
1184: ' WHERE'||
1185: 'srch.batch_id = -1'||
1186: 'AND'||
1187: '('||

Line 1251: SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p

1247: (srch.TX26 is not null and stage.TX26 like srch.TX26 || '%')
1248: )
1249: )
1250: AND EXISTS (
1251: SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p
1252: WHERE p.PARTY_ID = stage.PARTY_ID
1253: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = p.TX36) AND (g_party_stage_rec.TX46 IS NULL OR g_party_stage_rec.TX46||' ' = p.TX46)))
1254: AND stage.party_id = p_party_id;
1255: ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN

Line 1259: FROM HZ_SRCH_PSITES srch, HZ_STAGED_PARTY_SITES stage, HZ_STAGED_PARTIES p

1255: ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN
1256: IF p_search_ctx_id IS NULL THEN
1257: OPEN x_cursor FOR
1258: SELECT /*+ ORDERED USE_NL(srch stage p) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, stage.TX26, stage.TX9, stage.TX14, stage.TX22
1259: FROM HZ_SRCH_PSITES srch, HZ_STAGED_PARTY_SITES stage, HZ_STAGED_PARTIES p
1260: WHERE
1261: srch.batch_id = -1
1262: AND
1263: (

Line 1293: ' FROM HZ_SRCH_PSITES srch, HZ_STAGED_PARTY_SITES stage, HZ_STAGED_PARTIES p'||

1289: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
1290: END IF;
1291: ELSE
1292: l_sqlstr := 'SELECT /*+ ORDERED USE_NL(srch stage p) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID , stage.TX26, stage.TX9, stage.TX14, stage.TX22'||
1293: ' FROM HZ_SRCH_PSITES srch, HZ_STAGED_PARTY_SITES stage, HZ_STAGED_PARTIES p'||
1294: ' WHERE' ||
1295: 'srch.batch_id = -1'||
1296: 'AND'||
1297: '('||

Line 1348: SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p

1344: (srch.TX23 is not null and stage.TX23 like srch.TX23 || '%')
1345: )
1346: )
1347: AND EXISTS (
1348: SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p
1349: WHERE p.PARTY_ID = stage.PARTY_ID
1350: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = p.TX36) AND (g_party_stage_rec.TX46 IS NULL OR g_party_stage_rec.TX46||' ' = p.TX46)))
1351: AND stage.party_id = p_party_id;
1352: ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN

Line 1368: SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p

1364: (srch.TX23 is not null and stage.TX23 like srch.TX23 || '%')
1365: )
1366: )
1367: AND EXISTS (
1368: SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p
1369: WHERE p.PARTY_ID = stage.PARTY_ID
1370: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = p.TX36) AND (g_party_stage_rec.TX46 IS NULL OR g_party_stage_rec.TX46||' ' = p.TX46)))
1371: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
1372: ELSE

Line 1404: ' SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p ' ||

1400: '(srch.TX23 is not null and stage.TX23 like srch.TX23 || ''%'')'||
1401: ')'||
1402: ')'||
1403: ' AND EXISTS ('||
1404: ' SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p ' ||
1405: ' WHERE p.party_id = stage.party_id ' ||
1406: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36) AND (:TX46 IS NULL OR :TX46||'' '' = p.TX46))) ' ||
1407: ' AND ('||p_restrict_sql||')' ||
1408: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';

Line 1454: SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p

1450: (srch.TX158 is not null and stage.TX158 like srch.TX158 || '%')
1451: )
1452: )
1453: AND EXISTS (
1454: SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p
1455: WHERE p.PARTY_ID = stage.PARTY_ID
1456: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = p.TX36) AND (g_party_stage_rec.TX46 IS NULL OR g_party_stage_rec.TX46||' ' = p.TX46)))
1457: AND stage.party_id = p_party_id;
1458: ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN

Line 1479: SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p

1475: (srch.TX158 is not null and stage.TX158 like srch.TX158 || '%')
1476: )
1477: )
1478: AND EXISTS (
1479: SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p
1480: WHERE p.PARTY_ID = stage.PARTY_ID
1481: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = p.TX36) AND (g_party_stage_rec.TX46 IS NULL OR g_party_stage_rec.TX46||' ' = p.TX46)))
1482: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
1483: ELSE

Line 1525: ' SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p ' ||

1521: '(srch.TX158 is not null and stage.TX158 like srch.TX158 || ''%'')'||
1522: ')'||
1523: ')'||
1524: ' AND EXISTS ('||
1525: ' SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p ' ||
1526: ' WHERE p.party_id = stage.party_id ' ||
1527: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36) AND (:TX46 IS NULL OR :TX46||'' '' = p.TX46))) ' ||
1528: ' AND ('||p_restrict_sql||')' ||
1529: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';