[Home] [Help]
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: (
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: (
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: '('||
4432: END IF;
4433: CLOSE l_contact_pt_cur;
4434: END IF;
4435: --------------- DELETE FROM ALL SRCH TABLES ---------------------
4436: DELETE FROM HZ_SRCH_PARTIES WHERE batch_id = -1 ;
4437: DELETE FROM HZ_SRCH_PSITES WHERE batch_id = -1 ;
4438: DELETE FROM HZ_SRCH_CONTACTS WHERE batch_id = -1 ;
4439: DELETE FROM HZ_SRCH_CPTS WHERE batch_id = -1 ;
4440: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4442: END IF;
4443: EXCEPTION
4444: WHEN FND_API.G_EXC_ERROR THEN
4445: --------------- DELETE FROM ALL SRCH TABLES ---------------------
4446: DELETE FROM HZ_SRCH_PARTIES WHERE batch_id = -1 ;
4447: DELETE FROM HZ_SRCH_PSITES WHERE batch_id = -1 ;
4448: DELETE FROM HZ_SRCH_CONTACTS WHERE batch_id = -1 ;
4449: DELETE FROM HZ_SRCH_CPTS WHERE batch_id = -1 ;
4450: RAISE FND_API.G_EXC_ERROR;
4449: DELETE FROM HZ_SRCH_CPTS WHERE batch_id = -1 ;
4450: RAISE FND_API.G_EXC_ERROR;
4451: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4452: --------------- DELETE FROM ALL SRCH TABLES ---------------------
4453: DELETE FROM HZ_SRCH_PARTIES WHERE batch_id = -1 ;
4454: DELETE FROM HZ_SRCH_PSITES WHERE batch_id = -1 ;
4455: DELETE FROM HZ_SRCH_CONTACTS WHERE batch_id = -1 ;
4456: DELETE FROM HZ_SRCH_CPTS WHERE batch_id = -1 ;
4457: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4456: DELETE FROM HZ_SRCH_CPTS WHERE batch_id = -1 ;
4457: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4458: WHEN OTHERS THEN
4459: --------------- DELETE FROM ALL SRCH TABLES ---------------------
4460: DELETE FROM HZ_SRCH_PARTIES WHERE batch_id = -1 ;
4461: DELETE FROM HZ_SRCH_PSITES WHERE batch_id = -1 ;
4462: DELETE FROM HZ_SRCH_CONTACTS WHERE batch_id = -1 ;
4463: DELETE FROM HZ_SRCH_CPTS WHERE batch_id = -1 ;
4464: FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
5002: x_stage_rec.TX46 := '';
5003: END IF;
5004: END IF;
5005: IF p_search_ctx THEN
5006: insert into HZ_SRCH_PARTIES(batch_id,party_id, party_osr,party_os,TX41,TX45,TX59,TX2,TX36,TX46) values(-1,-1,-1,-1,x_stage_rec.TX41,x_stage_rec.TX45,x_stage_rec.TX59,x_stage_rec.TX2,x_stage_rec.TX36,x_stage_rec.TX46);
5007: END IF;
5008: EXCEPTION
5009: WHEN OTHERS THEN
5010: FND_MESSAGE.SET_NAME('AR', 'HZ_TRANSFORM_PROC_ERROR');