SQL

 -- =============================================================================      

-- Author:  Jaipal      

-- Create date: 18/12/2020      

-- Modifications:      

--       

-- =============================================================================    

-- SELECT * FROM PROFILES WHERE NETWORKID ='dbiondaro'  

-- EXEC MidYearAppraisalArchiveProcess 30038, 30166, 21, 'HR Close'  

CREATE PROCEDURE  [dbo].[MidYearAppraisalArchiveProcess]      

 @ProfileId int,      

 @AppraisalID int,  

 @AppraisalTypeID int,  

 @ModifiedBy nvarchar(100)      

AS  

BEGIN      

 --SET NOCOUNT ON;        

    BEGIN TRANSACTION      

 BEGIN TRY     

  --IF (@ModifiedBy = 'HR Close')  

  --BEGIN  

  

  --END  

    

  INSERT INTO Archive.MidYear      

  (  

   OriginalMidYearId, ArchivedProfileId, AppraisalTypeId, EmpObjective, MgrObjective, EmpValueBehavior,   

   MgrValueBehavior, EmpDevelopmentPlan, MgrDevelopmentPlan, ReviewDate, EmpSignDate, EmployeeName,  

   MgrSignDate, ManagerName, CreatedBy, Created, ModifiedBy, ModifiedOn, ObjectiveID  

  )      

  SELECT   

   MidYearId, @ProfileID,  AppraisalTypeID, EmpObjective, MgrObjective, EmpValueBehavior,   

   MgrValueBehavior, EmpDevelopmentPlan, MgrDevelopmentPlan,   

   CASE WHEN @ModifiedBy = 'HR Close' THEN  GETDATE() ELSE ReviewDate END AS ReviewDate,  

   CASE WHEN @ModifiedBy = 'HR Close' THEN  ISNULL(EmpSignDate, GETDATE()) ELSE EmpSignDate END AS EmpSignDate,  

   CASE WHEN @ModifiedBy = 'HR Close' THEN  ISNULL(EmployeeName, 'HR Close') ELSE EmployeeName END AS EmployeeName,  

   CASE WHEN @ModifiedBy = 'HR Close' THEN  ISNULL(MgrSignDate, GETDATE()) ELSE MgrSignDate END AS MgrSignDate,  

   CASE WHEN @ModifiedBy = 'HR Close' THEN  ISNULL(ManagerName, 'HR Close') ELSE ManagerName END AS ManagerName,  

   --ReviewDate, EmpSignDate, EmployeeName, MgrSignDate, ManagerName,   

   CreatedBy, CreatedOn, @ModifiedBy, GETDATE(), ObjectiveID      

  FROM MidYear       

  WHERE MidYear.ProfileID = @ProfileID   

    

  INSERT INTO Archive.MidYear_EmpMgrValues      

  (  

   OriginalMidYearEmpValuesID,ProfileID,AppraisalTypeID,EnterpriseCompetencyID,EmpValueBehavior,  

   MgrValueBehavior,ReviewDate,EmployeeName,ManagerName,CreatedBy,CreatedOn,ModifiedBy,ModifiedOn  

  )      

  SELECT   

   MidYearEmpValuesID,ProfileID,AppraisalTypeID,EnterpriseCompetencyID,EmpValueBehavior,MgrValueBehavior,  

   ReviewDate,EmployeeName,ManagerName,CreatedBy,CreatedOn,ModifiedBy,GETDATE()  

  FROM dbo.MidYear_EmpMgrValues    

  WHERE ProfileID = @ProfileId  

  AND AppraisalTypeID = @AppraisalTypeID  

  

  INSERT INTO Archive.MidYear_EmpMgrDevPlan      

  (  

   OriginalMidYearEmpMgrDevID,ProfileID,AppraisalTypeID,EmpDevPlan,MgrDevPlan,ReviewDate,EmployeeName,  

   ManagerName,CreatedBy,CreatedOn,ModifiedBy,ModifiedOn,DevPlanDetailId  

  

  )      

  SELECT   

   MidYearEmpMgrDevID,ProfileID,AppraisalTypeID,EmpDevPlan,MgrDevPlan,ReviewDate,EmployeeName,  

   ManagerName,CreatedBy,CreatedOn,ModifiedBy,GETDATE(),DevPlanDetailId  

  FROM dbo.MidYear_EmpMgrDevPlan    

  WHERE ProfileID = @ProfileId  

  AND AppraisalTypeID = @AppraisalTypeID  

         

  DELETE FROM [dbo].[MidYear] WHERE MidYear.ProfileID = @ProfileID  

  DELETE FROM [dbo].[MidYear_EmpMgrValues] where ProfileID  = @ProfileID  

  

        

  UPDATE Appraisals SET   

   AppraisalTypeID = @AppraisalTypeID + 1,      

   COCViolation = '',      

   ManagerMeeting = '',      

   PerformanceRating = NULL,      

   CompetencyRating = NULL,      

   OverallRating = NULL,      

   ObjectiveComment = NULL,      

   CompetencyComment = NULL,      

   SkillComment = NULL,      

   ReviewDate = NULL,      

   SelfAssessmentComplete = 0,      

   ManagerStepComplete = 0,      

   EmployeeComment = NULL,      

   ManagerComment = NULL,      

   EmployeeName = NULL,      

   EmployeeSignDate = NULL,      

   ManagerName = NULL,      

   ManagerSignDate = NULL,      

   ManagersManagerName = NULL,      

   ManagersManagerSignDate = NULL,      

   ModifiedBy = 'Archive Process'      

 WHERE AppraisalID = @AppraisalID      

      

 COMMIT TRANSACTION      

  END TRY      

    BEGIN CATCH      

  INSERT INTO tbl_Errors(ErrorNumber,ErrorState,ErrorSeverity,ErrorLine,ErrorProcedure,ErrorMessage,ErrorDateTime )  

  SELECT    

   ERROR_NUMBER() AS ErrorNumber, ERROR_STATE() AS ErrorState, ERROR_SEVERITY() AS ErrorSeverity   

   ,ERROR_LINE() AS ErrorLine, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_MESSAGE() AS ErrorMessage  

   ,GETDATE()  

        DECLARE @ErrorMessage NVARCHAR(4000),      

            @ErrorNumber INT,      

            @ErrorSeverity INT,      

            @ErrorState INT,      

            @ErrorLine INT,      

            @ErrorProcedure NVARCHAR(200)      

      

            -- Assign variables to error-handling functions that      

            -- capture information for RAISERROR.      

        SELECT  @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(),      

                @ErrorState = ERROR_STATE(), @ErrorLine = ERROR_LINE(),      

                @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-') ;      

      

            -- Building the message string that will contain original      

            -- error information.      

        SELECT  @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +      

                'Message: ' + ERROR_MESSAGE() ;      

      

            -- Raise an error: msg_str parameter of RAISERROR will contain      

            -- the original error information.      

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber, -- parameter: original error number.      

          @ErrorSeverity, -- parameter: original error severity.      

          @ErrorState, -- parameter: original error state.      

          @ErrorProcedure, -- parameter: original error procedure name.      

          @ErrorLine-- parameter: original error line number.      

                ) ;     

    

  

  

        ROLLBACK TRANSACTION      

        RETURN 50001      

    END CATCH      

END