-- Glue PO_History and PO_Archive together as the full bitemporal
-- state in a view when history deletions are allowed
CREATE VIEW Prop_Owner
 ( customer_number, property_nubmer, VT_Begin, VT_End, TT_Start, TT_Stop )
 AS
 ( SELECT customer_number, property_number, 
          VT_Begin, VT_End, TT_Start, SYSDATE
   FROM PO_History
 UNION
   SELECT P1.customer_number, P1.property_number, P1.VT_Begin, P1.VT_End,
          P1.TT_Start, P2.TT_Start
   FROM PO_Archive P1, PO_Archive P2
   WHERE P1.customber_number = P2.customer_number AND
         P1.property_number = P2.property_number AND
         P1.TT_Start < P2.TT_Start AND
         NOT EXISTS
         ( SELECT * FROM PO_Archive P3
           WHERE P1.customer_number = P3.customer_number AND
                 P1.property_number = P3.property_number AND
                 P1.TT_Start < P3.TT_Start AND P3.TT_Start < P2.TT_Start
         )
         AND NOT EXISTS
         ( SELECT * FROM PO_Deleted P3
           WHERE P1.customer_number = P3.customer_number AND
                 P1.property_number = P3.property_number AND
                 P1.TT_Start < P3.TT_Start AND P3.TT_Start < P2.TT_Start
         )
 UNION
  SELECT PA.customer_number, PA.property_number, PA.VT_Begin, PA.VT_End,
         PA.TT_Start, PH.TT_Start
  FROM PO_Archive PA, PO_History PH
  WHERE PA.customer_number = PH.customer_number AND
        PA.property_number = PH.property_number AND
        PA.TT_Start < PH.TT_Start AND
        NOT EXISTS
        ( SELECT * FROM PO_Archive P3
          WHERE PA.customer_number = P3.customer_number AND
                PA.property_number = P3.property_number AND
                PA.TT_Start < P3.TT_Start )
        AND NOT EXISTS
        ( SELECT * FROM PO_Deleted P3
          WHERE P1.customer_number = P3.customer_number AND
                P1.property_number = P3.property_number AND
                P1.TT_Start < P3.TT_Start AND P3.TT_Start < P2.TT_Start
        )
 UNION
  SELECT PA.customer_number, PA.property_number, PA.VT_Begin, PA.VT_End,
         PA.TT_Start, PH.TT_Start
  FROM PO_Archive PA, PO_Deleted PD
  WHERE PA.customer_number = PD.customer_number AND
        PA.property_number = PD.property_number AND
        NOT EXISTS
        ( SELECT * FROM PO_Archive P3
          WHERE PA.customer_number = P3.customer_number AND
                PA.property_number = P3.property_number AND
                PA.TT_Start < P3.TT_Start AND P3.TT_Start < PD.TT_Start
        )
 );




