ALTER PROC [dbo].[InsertPromotions]
@Employee_ID         int,
@NotificaitonDate    date,
@FromBPS             int,
@ToBPS               int,
@FromDesignation_ID  int,
@ToDesignation_ID    int,
@Remarks             varchar(1000),
@User_ID             int,
@Organization_ID     int,
@EntryDateTime       datetime      = NULL,
@IsPromoted          bit,
@RecentPK            int OUTPUT,
@ReturnStatus        bit OUTPUT,
@ReturnStatusMessage varchar(max) OUTPUT,
@PageName varchar(50),
@IP varchar(20)
AS
declare @recordID int;

BEGIN

    BEGIN TRY
        BEGIN TRANSACTION tran1;

        SET @EntryDateTime =(SELECT GETDATE());
        ----
        DECLARE @DistrictID int;

        SET @DistrictID =ISNULL((SELECT orgs.District_ID
                           FROM MC_MemberShip.Membership.Organization orgs
                           WHERE orgs.OrganizationID = @Organization_ID
                          ),0);

        CREATE TABLE #EmployeeServiceInfo
        (
        BPS             tinyint,
        Designation_ID  int,
        Branch_ID       int,
        Office_ID       int,
        Cadre           varchar(100),
        Remarks         varchar(1000),
        ServiceType     tinyint,
        ServiceDistrict int
        );

        INSERT INTO #EmployeeServiceInfo
        EXEC SelectServiceBPSDesignationFromPersonalInfoByID @Employee_ID;

        DECLARE @Designation_ID int;
        SET @Designation_ID = CASE WHEN @IsPromoted = 0 THEN
                                                            (
                                                             SELECT TempServiceInfo.Designation_ID
                                                             FROM #EmployeeServiceInfo TempServiceInfo
                                                            ) WHEN @IsPromoted = 1 AND
                                                                   @ToDesignation_ID = -1 THEN
                                                                                              (
                                                                                               SELECT TempServiceInfo.Designation_ID
                                                                                               FROM #EmployeeServiceInfo TempServiceInfo
                                                                                              ) WHEN @IsPromoted = 1 AND
                                                                                                     @ToDesignation_ID != -1 THEN @ToDesignation_ID END;

                                                                                                     --Audit Entry
                                                                                                     set @recordID = SCOPE_IDENTITY();
                                                                                                    -- exec MC_MemberShip.dbo.AuditEntry @PageName , @IP , @User_ID , @recordID , null , 2
                                                                                                     --Audit Entry


        IF(@FromDesignation_ID = -1 AND
           @FromBPS = 0 AND
           @IsPromoted = 1
          ) -- In case if we are promoting all employees
        BEGIN
            SET @FromDesignation_ID =
                                      (
                                       SELECT TempServiceInfo.Designation_ID
                                       FROM #EmployeeServiceInfo TempServiceInfo
                                      );
            SET @FromBPS =
                           (
                            SELECT TempServiceInfo.BPS
                            FROM #EmployeeServiceInfo TempServiceInfo
                           );
        END;

        DECLARE @BPS int;
        SET @BPS = CASE WHEN @ToBPS = 0 THEN
                                            (
                                             SELECT BPS
                                             FROM #EmployeeServiceInfo
                                            ) ELSE @ToBPS END;

        DECLARE @Office_ID int;
        SET @Office_ID =
                         (
                          SELECT Office_ID
                          FROM #EmployeeServiceInfo
                         );

        DECLARE @ServiceDistrict int;
        SET @ServiceDistrict =
                               (
                                SELECT ServiceDistrict
                                FROM #EmployeeServiceInfo
                               );

        DECLARE @ServiceType tinyint;
        SET @ServiceType =
                           (
                            SELECT ServiceType
                            FROM #EmployeeServiceInfo
                           );

        DECLARE @Department_ID int;
        SET @Department_ID =
                             (
                              SELECT Branch_ID
                              FROM #EmployeeServiceInfo
                             );

        DECLARE @Cadre varchar;
        SET @Cadre =
                     (
                      SELECT Cadre
                      FROM #EmployeeServiceInfo
                     );
        ----
        IF(@IsPromoted = 0)
        BEGIN

            INSERT INTO Employee.Promotions(Employee_ID, NotificaitonDate, FromBPS, ToBPS, FromDesignation_ID, ToDesignation_ID, Remarks, User_ID, Organization_ID, EntryDateTime, IsPromoted)
            VALUES(@Employee_ID, @NotificaitonDate, @FromBPS, @BPS, @Designation_ID, @Designation_ID, @Remarks, @User_ID, @Organization_ID, @EntryDateTime, @IsPromoted);

        --Audit Entry
     set @recordID = SCOPE_IDENTITY();
     --exec MC_MemberShip.dbo.AuditEntry @PageName , @IP , @User_ID , @recordID , null , 2
 --Audit Entry
        END;
        ELSE
        BEGIN
            INSERT INTO Employee.Promotions(Employee_ID, NotificaitonDate, FromBPS, ToBPS, FromDesignation_ID, ToDesignation_ID, Remarks, User_ID, Organization_ID, EntryDateTime, IsPromoted)
            VALUES(@Employee_ID, @NotificaitonDate, @FromBPS, @BPS, @FromDesignation_ID, @Designation_ID, @Remarks, @User_ID, @Organization_ID, @EntryDateTime, @IsPromoted);
    --Audit Entry
     set @recordID = SCOPE_IDENTITY();
     --exec MC_MemberShip.dbo.AuditEntry @PageName , @IP , @User_ID , @recordID , null , 2
 --Audit Entry
        END;




        EXEC InsertServiceInfo @Designation_ID, @BPS, @ServiceDistrict, 3, @NotificaitonDate, NULL, '', @Employee_ID, @ServiceType, '', @Cadre, @User_ID, @Organization_ID, @DistrictID, NULL, @Office_ID, @Department_ID, @Remarks, 1, 1, '';

        UPDATE PersonalInfo
          SET Designation_ID = @Designation_ID, BPS = @BPS
        WHERE pk_PersonalInfo_ID = @Employee_ID;

        SET @RecentPK = 0 --IDENT_CURRENT('Employee.Promotions');
        --Audit Entry
     --set @recordID = IDENT_CURRENT('Employee.Promotions')
     --exec MC_MemberShip.dbo.AuditEntry @PageName , @IP , @User_ID , @recordID , null , 3
 --Audit Entry
        --Calculating new basic salary

        IF(@ToBPS > 0)         --Calculate new salary only if BPS is mentioned otherwise no new saalry will be calculated
        BEGIN

            CREATE TABLE #SalaryChart   --Temp table to store salary chart details
            (
            YearID        int,
            InitialSalary money,
            Increment     money
            );

            INSERT INTO #SalaryChart(YearID, InitialSalary, Increment)
                   SELECT sc.Year_ID, sc.InitialSalary, sc.Increment
                   FROM Setup.SalaryChart sc
                   WHERE sc.BPS = @BPS AND
                         IsActive = 1;

            DECLARE @EmpBasicSalary money;
            DECLARE @EmployeeMinSalary money;
            DECLARE @YearlyIncrement money;
            DECLARE @NewBasicSalary money;
            DECLARE @Year_ID int;


            SET @Year_ID =
                           (
                            SELECT sc.YearID
                            FROM #SalaryChart sc
                           );

            SET @EmpBasicSalary =
                                  (
                                   SELECT pi.BasicSalary 'EmployeeBasicSalary'
                                   FROM PersonalInfo pi
                                   WHERE bps = @BPS AND
                                         pk_PersonalInfo_ID = @Employee_ID
                                  );

            SET @EmployeeMinSalary =
                                     (
                                      SELECT sc.InitialSalary
                                      FROM #SalaryChart sc
                                     );

            SET @YearlyIncrement =
                                   (
                                    SELECT sc.Increment
                                    FROM #SalaryChart sc
                                   );

            WHILE(@EmployeeMinSalary < @EmpBasicSalary)
            BEGIN
                SET @EmployeeMinSalary = @EmployeeMinSalary + @YearlyIncrement;
            END;

            SET @NewBasicSalary = @EmployeeMinSalary + @YearlyIncrement;

            set @YearlyIncrement=@NewBasicSalary-@EmpBasicSalary;

            INSERT INTO Employee.SalaryDetails(NotificationDate, Employee_ID, Year_ID, BasicSalary, IncrementAmount, NewBasicSalary, SystemUser_ID, Organization_ID, District_ID, EntryDateTime)
            VALUES(@NotificaitonDate, @Employee_ID, @Year_ID, @EmpBasicSalary, @YearlyIncrement, @NewBasicSalary, @User_ID, @Organization_ID, @DistrictID, @EntryDateTime);
            --Audit Entry
     set @recordID = SCOPE_IDENTITY();
     --exec MC_MemberShip.dbo.AuditEntry @PageName , @IP , @User_ID , @recordID , null , 2
 --Audit Entry

            UPDATE PersonalInfo
              SET BasicSalary = @NewBasicSalary
            WHERE pk_PersonalInfo_ID = @Employee_ID;
            --Audit Entry
     set @recordID = @Employee_ID;
     --exec MC_MemberShip.dbo.AuditEntry @PageName , @IP , @User_ID , @recordID , null , 3
 --Audit Entry
        END;
        ------ Salary's Part
        SET @ReturnStatus = 1;

        COMMIT TRANSACTION tran1;
    END TRY
    BEGIN CATCH

        SET @ReturnStatus = 0;
        SET @ReturnStatusMessage =
                                   (
                                    SELECT ERROR_MESSAGE()
                                   );
                                   Set @RecentPK=0
                                   SEt @recordID=0

        ROLLBACK TRANSACTION tran1;
    END CATCH;


END;