SET QUOTED_IDENTIFIER OFF
GO

   CREATE VIEW HARPKGFROZENVIEW AS
   SELECT P.PackageObjId
   FROM HarPackage P
   WHERE EXISTS
   (
   SELECT *
   FROM HarApprove AP
   WHERE AP.StateObjId = P.StateObjId
   AND EXISTS
   (
   SELECT *
   FROM HarApproveList AL
   WHERE AL.ProcessObjId = AP.ProcessObjId
   AND
   (
   ( AL.IsGroup = 'N'
   AND EXISTS
   (
   SELECT *
   FROM HarApproveHist AH1
   WHERE AH1.EnvObjId = P.EnvObjId
   AND AH1.StateObjId = P.StateObjId
   AND AH1.PackageObjId = P.PackageObjId
   AND AH1.UsrObjId = AL.UsrObjId
   AND AH1.Action = 'Approved'
   AND AH1.ExecDTime =
   (
   SELECT MAX(AH2.ExecDTime)
   FROM HarApproveHist AH2
   WHERE AH2.EnvObjId = P.EnvObjId
   AND AH2.StateObjId = P.StateObjId
   AND AH2.PackageObjId = P.PackageObjId
   AND AH2.UsrObjId = AL.UsrObjId ) ) )
   OR
   ( AL.IsGroup = 'Y'
   AND EXISTS
   (
   SELECT *
   FROM HarApproveHist AH1
   WHERE AH1.EnvObjId = P.EnvObjId
   AND AH1.StateObjId = P.StateObjId
   AND AH1.PackageObjId = P.PackageObjId
   AND EXISTS
   (SELECT *
   FROM HarUsersInGroup UG
   WHERE UG.UsrObjId = AH1.UsrObjId
   and UG.UsrGrpObjId = AL.UsrGrpObjId)
   AND AH1.Action = 'Approved'
   AND AH1.ExecDTime =
   (
   SELECT MAX(AH2.ExecDTime)
   FROM HarApproveHist AH2
   WHERE AH2.EnvObjId = P.EnvObjId
   AND AH2.StateObjId = P.StateObjId
   AND AH2.PackageObjId = P.PackageObjId
   AND AH2.UsrObjId = AH1.UsrObjId ) ) ) ) )
   AND NOT EXISTS
   (
   SELECT *
   FROM HarApproveList AL
   WHERE AL.ProcessObjId = AP.ProcessObjId
   AND
   (
   ( AL.IsGroup = 'N'
   AND EXISTS
   (
   SELECT *
   FROM HarApproveHist AH1
   WHERE AH1.EnvObjId = P.EnvObjId
   AND AH1.StateObjId = P.StateObjId
   AND AH1.PackageObjId = P.PackageObjId
   AND AH1.UsrObjId = AL.UsrObjId
   AND AH1.Action = 'Rejected'
   AND AH1.ExecDTime =
   (
   SELECT MAX(AH2.ExecDTime)
   FROM HarApproveHist AH2
   WHERE AH2.EnvObjId = P.EnvObjId
   AND AH2.StateObjId = P.StateObjId
   AND AH2.PackageObjId = P.PackageObjId
   AND AH2.UsrObjId = AL.UsrObjId ) ) )
   OR
   ( AL.IsGroup = 'Y'
   AND EXISTS
   (
   SELECT *
   FROM HarApproveHist AH1
   WHERE AH1.EnvObjId = P.EnvObjId
   AND AH1.StateObjId = P.StateObjId
   AND AH1.PackageObjId = P.PackageObjId
   AND EXISTS
   (SELECT *
   FROM HarUsersInGroup UG
   WHERE UG.UsrObjId = AH1.UsrObjId
   and UG.UsrGrpObjId = AL.UsrGrpObjId)
   AND AH1.Action = 'Rejected'
   AND AH1.ExecDTime =
   (
   SELECT MAX(AH2.ExecDTime)
   FROM HarApproveHist AH2
   WHERE AH2.EnvObjId = P.EnvObjId
   AND AH2.StateObjId = P.StateObjId
   AND AH2.PackageObjId = P.PackageObjId
   AND AH2.UsrObjId = AH1.UsrObjId ) ) ) ) ) )  

GO