APPS.HZ_MATCH_RULE_52 dependencies on HZ_STAGED_PARTIES

Line 1166: FROM HZ_STAGED_PARTIES stage

1162: BEGIN
1163: IF p_contains_str IS NULL THEN
1164: OPEN x_cursor FOR
1165: SELECT PARTY_ID , TX2, TX59, lpad(rtrim(TX41),9,chr(48)), TX45
1166: FROM HZ_STAGED_PARTIES stage
1167: WHERE PARTY_ID = p_dup_party_id;
1168: ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN
1169: IF p_search_ctx_id IS NULL THEN
1170: OPEN x_cursor FOR

Line 1172: FROM HZ_SRCH_PARTIES srch, HZ_STAGED_PARTIES stage

1168: ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN
1169: IF p_search_ctx_id IS NULL THEN
1170: OPEN x_cursor FOR
1171: SELECT /*+ ORDERED */ stage.PARTY_ID , stage.TX2, stage.TX59, lpad(rtrim(stage.TX41),9,chr(48)), stage.TX45
1172: FROM HZ_SRCH_PARTIES srch, HZ_STAGED_PARTIES stage
1173: WHERE
1174: srch.batch_id = -1
1175: AND
1176: (

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

1194: AND (nvl(p_search_merged,'N')='Y' OR nvl(stage.status,'A') in ('A'))
1195: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
1196: ELSE
1197: OPEN x_cursor FOR
1198: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , stage.TX2, stage.TX59, lpad(rtrim(stage.TX41),9,chr(48)), stage.TX45
1199: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage, HZ_SRCH_PARTIES srch
1200: WHERE
1201: srch.batch_id = -1
1202: AND

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

1195: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
1196: ELSE
1197: OPEN x_cursor FOR
1198: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , stage.TX2, stage.TX59, lpad(rtrim(stage.TX41),9,chr(48)), stage.TX45
1199: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage, HZ_SRCH_PARTIES srch
1200: WHERE
1201: srch.batch_id = -1
1202: AND
1203: (

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

1225: END IF;
1226: ELSE
1227: IF p_search_ctx_id IS NULL THEN
1228: l_sqlstr := 'SELECT /*+ ORDERED */ stage.PARTY_ID , stage.TX2, stage.TX59, lpad(rtrim(stage.TX41),9,chr(48)), stage.TX45'||
1229: ' FROM HZ_SRCH_PARTIES srch, HZ_STAGED_PARTIES stage'||
1230: ' WHERE'||
1231: 'srch.batch_id = -1'||
1232: 'AND'||
1233: '('||

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

1293: (srch.TX26 is not null and stage.TX26 like srch.TX26 || '%')
1294: )
1295: )
1296: AND EXISTS (
1297: SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p
1298: WHERE p.PARTY_ID = stage.PARTY_ID
1299: 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)))
1300: AND stage.party_id = p_party_id;
1301: ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN

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

1301: ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN
1302: IF p_search_ctx_id IS NULL THEN
1303: OPEN x_cursor FOR
1304: 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
1305: FROM HZ_SRCH_PSITES srch, HZ_STAGED_PARTY_SITES stage, HZ_STAGED_PARTIES p
1306: WHERE
1307: srch.batch_id = -1
1308: AND
1309: (

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

1335: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
1336: END IF;
1337: ELSE
1338: 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'||
1339: ' FROM HZ_SRCH_PSITES srch, HZ_STAGED_PARTY_SITES stage, HZ_STAGED_PARTIES p'||
1340: ' WHERE' ||
1341: 'srch.batch_id = -1'||
1342: 'AND'||
1343: '('||

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

1390: (srch.TX23 is not null and stage.TX23 like srch.TX23 || '%')
1391: )
1392: )
1393: AND EXISTS (
1394: SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p
1395: WHERE p.PARTY_ID = stage.PARTY_ID
1396: 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)))
1397: AND stage.party_id = p_party_id;
1398: ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN

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

1410: (srch.TX23 is not null and stage.TX23 like srch.TX23 || '%')
1411: )
1412: )
1413: AND EXISTS (
1414: SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p
1415: WHERE p.PARTY_ID = stage.PARTY_ID
1416: 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)))
1417: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
1418: ELSE

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

1446: '(srch.TX23 is not null and stage.TX23 like srch.TX23 || ''%'')'||
1447: ')'||
1448: ')'||
1449: ' AND EXISTS ('||
1450: ' SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p ' ||
1451: ' WHERE p.party_id = stage.party_id ' ||
1452: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36) AND (:TX46 IS NULL OR :TX46||'' '' = p.TX46))) ' ||
1453: ' AND ('||p_restrict_sql||')' ||
1454: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';

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

1501: (srch.TX7 is not null and stage.TX7 like srch.TX7 || '%')
1502: )
1503: )
1504: AND EXISTS (
1505: SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p
1506: WHERE p.PARTY_ID = stage.PARTY_ID
1507: 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)))
1508: AND stage.party_id = p_party_id;
1509: ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN

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

1531: (srch.TX7 is not null and stage.TX7 like srch.TX7 || '%')
1532: )
1533: )
1534: AND EXISTS (
1535: SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p
1536: WHERE p.PARTY_ID = stage.PARTY_ID
1537: 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)))
1538: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
1539: ELSE

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

1587: '(srch.TX7 is not null and stage.TX7 like srch.TX7 || ''%'')'||
1588: ')'||
1589: ')'||
1590: ' AND EXISTS ('||
1591: ' SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p ' ||
1592: ' WHERE p.party_id = stage.party_id ' ||
1593: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36) AND (:TX46 IS NULL OR :TX46||'' '' = p.TX46))) ' ||
1594: ' AND ('||p_restrict_sql||')' ||
1595: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';