DROP VIEW PROJECTIONS_STATE;

CREATE VIEW PROJECTIONS_State
 (PROJECTION_ID, PROJECTION_NAME, PROJECTION_TYPE, SPHERIOD_CODE, PROJECTION_UOM,
  ZONE_CODE, Start_Date, Stop_Date)
AS(
 SELECT P.PROJECTION_ID, P.PROJECTION_NAME, P.PROJECTION_TYPE, P.SPHEROID_CODE, 
        P.PROJECTION_UOM, P.ZONE_CODE, When_Changed,  Sysdate
 FROM P_Audit A1, PROJECTIONS P
 WHERE A1.PROJECTION_ID = P.PROJECTION_ID AND
       NOT EXISTS (SELECT * FROM P_Audit A2
                   WHERE A1.PROJECTION_ID = A2.PROJECTION_ID AND
                         A1.When_Changed < A2.When_Changed)
 UNION
 SELECT PROJECTION_ID, PROJECTION_NAME, PROJECTION_TYPE, SPHEROID_CODE, PROJECTION_UOM,
        ZONE_CODE, TO_DATE('01/01/1996','MM/DD/YYYY'), Sysdate
 FROM PROJECTIONS
 WHERE NOT EXISTS (SELECT * FROM P_Audit
                   WHERE P_Audit.PROJECTION_ID = PROJECTIONS.PROJECTION_ID)
 UNION
 SELECT PROJECTION_ID, PROJECTION_NAME, PROJECTION_TYPE, SPHEROID_CODE, PROJECTION_UOM,
        ZONE_CODE, TO_DATE('01/01/1996','MM/DD/YYYY'), When_Changed
 FROM P_Audit A1
 WHERE NOT EXISTS (SELECT * FROM P_Audit A2
                   WHERE A1.PROJECTION_ID = A2.PROJECTION_ID AND
                         A1.When_Changed > A2.When_Changed)
 UNION
 SELECT A1.PROJECTION_ID, A1.PROJECTION_NAME, A1.PROJECTION_TYPE, A1.SPHEROID_CODE, 
        A1.PROJECTION_UOM, A1.ZONE_CODE, A0.When_Changed, A1.When_Changed
 FROM P_Audit A0, P_Audit A1
 WHERE A0.PROJECTION_ID = A1.PROJECTION_ID AND
       A0.When_Changed < A1.When_Changed AND
       NOT EXISTS (SELECT * FROM P_Audit M
                   WHERE M.PROJECTION_ID = A1.PROJECTION_ID AND
                         M.When_Changed < A1.When_Changed AND
                         M.When_Changed > A0.When_Changed)
);
/

