APPS.HZ_MATCH_RULE_51 dependencies on HZ_STAGED_PARTIES

Line 955: FROM HZ_STAGED_PARTIES stage

951: BEGIN
952: IF p_contains_str IS NULL THEN
953: OPEN x_cursor FOR
954: SELECT PARTY_ID , TX2, TX59, TX45
955: FROM HZ_STAGED_PARTIES stage
956: WHERE PARTY_ID = p_dup_party_id;
957: ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN
958: IF p_search_ctx_id IS NULL THEN
959: OPEN x_cursor FOR

Line 961: FROM HZ_SRCH_PARTIES srch, HZ_STAGED_PARTIES stage

957: ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN
958: IF p_search_ctx_id IS NULL THEN
959: OPEN x_cursor FOR
960: SELECT /*+ ORDERED */ stage.PARTY_ID , stage.TX2, stage.TX59, stage.TX45
961: FROM HZ_SRCH_PARTIES srch, HZ_STAGED_PARTIES stage
962: WHERE
963: srch.batch_id = -1
964: AND
965: (

Line 982: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , stage.TX2, stage.TX59, stage.TX45

978: AND (nvl(p_search_merged,'N')='Y' OR nvl(stage.status,'A') in ('A'))
979: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
980: ELSE
981: OPEN x_cursor FOR
982: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , stage.TX2, stage.TX59, stage.TX45
983: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage, HZ_SRCH_PARTIES srch
984: WHERE
985: srch.batch_id = -1
986: AND

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

979: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
980: ELSE
981: OPEN x_cursor FOR
982: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , stage.TX2, stage.TX59, stage.TX45
983: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage, HZ_SRCH_PARTIES srch
984: WHERE
985: srch.batch_id = -1
986: AND
987: (

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

1004: END IF;
1005: ELSE
1006: IF p_search_ctx_id IS NULL THEN
1007: l_sqlstr := 'SELECT /*+ ORDERED */ stage.PARTY_ID , stage.TX2, stage.TX59, stage.TX45'||
1008: ' FROM HZ_SRCH_PARTIES srch, HZ_STAGED_PARTIES stage'||
1009: ' WHERE'||
1010: 'srch.batch_id = -1'||
1011: 'AND'||
1012: '('||

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

1066: (srch.TX26 is not null and stage.TX26 like srch.TX26 || '%')
1067: )
1068: )
1069: AND EXISTS (
1070: SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p
1071: WHERE p.PARTY_ID = stage.PARTY_ID
1072: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = p.TX36)))
1073: AND stage.party_id = p_party_id;
1074: ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN

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

1074: ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN
1075: IF p_search_ctx_id IS NULL THEN
1076: OPEN x_cursor FOR
1077: 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
1078: FROM HZ_SRCH_PSITES srch, HZ_STAGED_PARTY_SITES stage, HZ_STAGED_PARTIES p
1079: WHERE
1080: srch.batch_id = -1
1081: AND
1082: (

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

1108: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
1109: END IF;
1110: ELSE
1111: 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'||
1112: ' FROM HZ_SRCH_PSITES srch, HZ_STAGED_PARTY_SITES stage, HZ_STAGED_PARTIES p'||
1113: ' WHERE' ||
1114: 'srch.batch_id = -1'||
1115: 'AND'||
1116: '('||

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

1179: (srch.TX158 is not null and stage.TX158 like srch.TX158 || '%')
1180: )
1181: )
1182: AND EXISTS (
1183: SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p
1184: WHERE p.PARTY_ID = stage.PARTY_ID
1185: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = p.TX36)))
1186: AND stage.party_id = p_party_id;
1187: ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN

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

1204: (srch.TX158 is not null and stage.TX158 like srch.TX158 || '%')
1205: )
1206: )
1207: AND EXISTS (
1208: SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p
1209: WHERE p.PARTY_ID = stage.PARTY_ID
1210: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = p.TX36)))
1211: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
1212: ELSE

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

1250: '(srch.TX158 is not null and stage.TX158 like srch.TX158 || ''%'')'||
1251: ')'||
1252: ')'||
1253: ' AND EXISTS ('||
1254: ' SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p ' ||
1255: ' WHERE p.party_id = stage.party_id ' ||
1256: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36))) ' ||
1257: ' AND ('||p_restrict_sql||')' ||
1258: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';