SET QUOTED_IDENTIFIER OFF
GO

   CREATE VIEW HARPKGREJECTEDVIEW AS
   SELECT P.PackageObjId
   FROM HarPackage P
   WHERE
   ( EXISTS
   (
   SELECT *
   FROM HarApprove AP
   WHERE AP.StateObjId = P.StateObjId )
   AND NOT EXISTS
   (
   SELECT *
   FROM HarApprove AP
   WHERE AP.StateObjId = P.StateObjId
   AND NOT EXISTS
   (SELECT *
   FROM HarApproveList AL
   WHERE AL.ProcessObjId = AP.ProcessObjId) )
   AND NOT EXISTS
   (
   SELECT AL1.ProcessObjId
   FROM HarApproveList AL1
   WHERE AL1.StateObjId = P.StateObjId
   GROUP BY AL1.ProcessObjId HAVING COUNT(*) =
   (
   SELECT COUNT(*)
   FROM HarApproveList AL2
   WHERE AL2.ProcessObjId = AL1.ProcessObjId
   AND
   (
   ( AL2.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 = AL2.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 = AL2.UsrObjId ) ) )
   OR
   ( AL2.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 = AL2.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 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 = AL2.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 ) ) ) ) ) )
   AND EXISTS
   (
   SELECT *
   FROM HarApproveList AL
   WHERE AL.StateObjId = P.StateObjId
   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