--Code Fragment 9.13
DROP VIEW PROJECTIONS_State;

CREATE VIEW PROJECTIONS_State 
 (PROJECTION_ID, PROJECTION_NAME, PROJECTION_TYPE, SPHEROID_CODE, PROJECTION_UOM, 
  ZONE_CODE, START_DATE, STOP_DATE) 
AS (
 SELECT A.PROJECTION_ID, A.PROJECTION_NAME, A.PROJECTION_TYPE, A.SPHEROID_CODE, 
        A.PROJECTION_UOM, A.ZONE_CODE, A.When_Changed, A2.When_Changed 
 FROM P_Audit A, P_Audit A2 
 WHERE A.PROJECTION_ID = A2.PROJECTION_ID AND 
       A.When_Changed < A2.When_Changed AND 
       A.Operation <> 'D' AND 
       NOT EXISTS (SELECT * FROM P_Audit A3 
                   WHERE A.PROJECTION_ID = A3.PROJECTION_ID AND 
                         A.When_Changed < A3.When_Changed AND 
                         A3.When_Changed < A2.When_Changed) 
UNION 
 SELECT PROJECTION_ID, PROJECTION_NAME, PROJECTION_TYPE, SPHEROID_CODE, 
        PROJECTION_UOM, ZONE_CODE, When_Changed, SYSDATE 
 FROM P_Audit A 
 WHERE A.Operation <> 'D' AND 
       NOT EXISTS (SELECT * FROM P_Audit A3 
           WHERE A.PROJECTION_ID = A3.PROJECTION_ID AND 
           A.When_Changed < A3.When_Changed)
);
/
