-- Delete new and duplicate data created out of wrong on clause join
DELETE FROM DIM_MR_TIME_VERTEX 
WHERE ROWID IN (SELECT RID FROM (SELECT ROWID RID,ROW_NUMBER() OVER (PARTITION BY N_TIME_VERTEX,V_TIME_VERTEX_DESC ORDER BY N_TIME_VERTEX_SKEY ASC) RN
				FROM DIM_MR_TIME_VERTEX
				) WHERE RN <> 1
				)
/
-- Update Spot and NA vertices with the functionally relevant vertex values
MERGE INTO DIM_MR_TIME_VERTEX T
USING (
SELECT '0.000001' N_TIME_VERTEX,'Spot' V_TIME_VERTEX_DESC,'Y' F_LATEST_RECORD_INDICATOR FROM DUAL
UNION
SELECT '-1.000001' N_TIME_VERTEX,'NA' V_TIME_VERTEX_DESC,'Y' F_LATEST_RECORD_INDICATOR FROM DUAL
) S 
ON (T.V_TIME_VERTEX_DESC = S.V_TIME_VERTEX_DESC	AND T.F_LATEST_RECORD_INDICATOR = S.F_LATEST_RECORD_INDICATOR)
WHEN MATCHED THEN 
UPDATE
SET T.N_TIME_VERTEX = S.N_TIME_VERTEX
/
-- Update the existing old records with the corrected SD/ED
UPDATE DIM_MR_TIME_VERTEX SET D_RECORD_END_DATE = '31-DEC-9999',D_RECORD_START_DATE = '01-JAN-1900'
WHERE F_LATEST_RECORD_INDICATOR = 'Y'
AND D_RECORD_START_DATE <> '01-JAN-1900'
AND D_RECORD_END_DATE <> '31-DEC-9999'
/
COMMIT
/