Support Search

[Closed]Paymaster Cloud and HR Master intergration Script




If Exists (Select * From dbo.sysobjects where id = object_id(N'[dbo].[prcClassInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                Drop Procedure [prcClassInsert]

GO---

Create Procedure [dbo].[prcClassInsert]         
@HRMS_DataBaseName varchar(50),   
@OldValue nvarchar(100),   
@NewValue nvarchar(100),   
@Cmp_Id int,   
@TenantId int,   
@Login_Id int, 
@Class_Id int output 
AS             
Begin          
   
Declare @LinkServer as varchar(100)     
Declare @Class_Name NVarChar(200)     
Declare @Class_Desc NVarChar(600)     
 
set @LinkServer= '[' + @HRMS_DataBaseName + '].dbo.'         
   
  IF OBJECT_ID('tempdb.dbo.##TempClass_HRMS') IS NOT NULL Drop table  ##TempClass_HRMS 
   
Exec ('Select Top 1   
    Class_Name,Class_Desc   
       into ##TempClass_HRMS From ' + @LinkServer + 'tblClass     
    Where Class_Code='''+@NewValue+'''')    
   
 Select @Class_Name= Class_Name,@Class_Desc = Class_Desc From ##TempClass_HRMS   
    
 If Exists(select 1 from tblClass Where Cmp_Id = @Cmp_ID And TenantId = @TenantId And Is_Deleted = 0  And Class_Code = @OldValue)   
 Begin    
  Select @Class_Id = Class_Id From tblClass Where Cmp_Id = @Cmp_ID And TenantId= @TenantId And Class_Code = @OldValue And Is_Deleted = 0   
    
  If Exists (Select 1 From tblClass Where Cmp_Id = @Cmp_ID And TenantId = @TenantId And Is_Deleted = 0 And Class_Id <> @Class_Id And Upper(Class_Name) = @Class_Name )           
  Begin                 
   Raiserror('Bitplus:Duplicate Class Name',14,2)                 
   Return -1                 
  End                      
  If Exists (Select Class_Name From tblClass Where Cmp_Id = @Cmp_ID  And TenantId = @TenantId And Is_Deleted = 0 and Class_Id <> @Class_Id And Upper(Class_Code) = @NewValue )       
  Begin             
   Raiserror('Bitplus:Duplicate Class Code',14,2)             
   Select @@Error           
   Return -1             
  End      
     
  Update tblClass Set   Class_Code = @NewValue, Class_Name = @Class_Name , Class_Desc= @Class_Desc, Login_U_Id = @Login_Id, USysDate = GetDate()              
  Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Class_Id= @Class_Id     
 
 End   
 Else   
 Begin   
  Set @Class_Id = 0   
  If Exists (Select 1 From tblClass Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Is_Deleted = 0 And Upper(Class_Name) = @Class_Name )           
  Begin                 
   Raiserror('Bitplus:Duplicate Class Name',14,2)                 
   Return -1                 
  End                      
  If Exists (Select Class_Name From tblClass Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Is_Deleted = 0  And UPPER(Class_Code) = @NewValue)       
  Begin             
   Raiserror('Bitplus:Duplicate Class Code',14,2)             
   Select @@Error           
   Return -1             
  End      
     
  Insert into tblClass (TenantId,Cmp_Id,Class_Code,Class_Name,Class_Desc,Login_C_Id,CSysDate)   
  Values (@TenantId,@Cmp_Id,@NewValue,@Class_Name,@Class_Desc,@Login_Id,GETDATE())   
 
 Set @Class_Id = Scope_Identity()     
     
 End   
    
 IF OBJECT_ID('tempdb.dbo.##TempClass_HRMS') IS NOT NULL Drop table  ##TempClass_HRMS        
End   
Return

GO---

If Exists (Select * From dbo.sysobjects where id = object_id(N'[dbo].[prcDepartmentInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                Drop Procedure [prcDepartmentInsert]

GO---

Create Procedure [dbo].[prcDepartmentInsert]       
@HRMS_DataBaseName varchar(50), 
@OldValue nvarchar(100), 
@NewValue nvarchar(100), 
@Cmp_Id int, 
@TenantId int, 
@Login_Id int, 
@Dept_Id int output 
AS           
Begin        
 
Declare @LinkServer as varchar(100)   
Declare @Dept_Name NVarChar(200)   
Declare @Dept_Desc NVarChar(600)   
Declare @Max_No_Of_Emp int 

set @LinkServer= '[' + @HRMS_DataBaseName + '].dbo.'       
 
IF OBJECT_ID('tempdb.dbo.##TempDept_HRMS') IS NOT NULL Drop table  ##TempDept_HRMS
 
Exec ('select Dept_Name,Max_No_Of_Emp,Dept_Desc  
       into ##TempDept_HRMS From ' + @LinkServer + 'TblDepartment   
    Where Dept_Code='''+@NewValue+'''')  
         
 Select @Dept_Name= Dept_Name,@Max_No_Of_Emp= Max_No_Of_Emp, @Dept_Desc = Dept_Desc     
 From ##TempDept_HRMS 
  
 If exists(select 1 from TblDepartment where Cmp_Id = @Cmp_ID And TenantId= @TenantId And Is_Deleted = 0 And Dept_Code = @OldValue ) 
 Begin  
  Select @Dept_Id = Dept_Id From TblDepartment where Cmp_Id = @Cmp_ID And TenantId= @TenantId And Is_Deleted = 0  And Dept_Code = @OldValue  
  
  If Exists (Select 1 From TblDepartment Where Cmp_Id = @Cmp_ID And Is_Deleted = 0  And TenantId = @TenantId And Upper(Dept_Name) = @Dept_Name and Dept_Id <> @Dept_Id )         
  Begin               
   Raiserror('Bitplus:Duplicate Department Name',14,2)               
   Return -1               
  End                    
  If Exists (Select Dept_Name From TblDepartment Where Cmp_Id = @Cmp_ID And Is_Deleted = 0  And TenantId = @TenantId  And Upper(Dept_Code) = @NewValue And Dept_Id <> @Dept_Id )     
  Begin           
   Raiserror('Bitplus:Duplicate Department Code',14,2)           
   Select @@Error         
   Return -1           
  End    
   
  Update TblDepartment Set   Dept_Code = @NewValue, Dept_Name = @Dept_Name , Max_No_Of_Emp= @Max_No_Of_Emp,Dept_Desc= @Dept_Desc, Login_U_Id = @Login_Id, USysDate = GetDate()            
  Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Dept_Id= @Dept_Id   
 
 End 
 Else 
 Begin 
  Set @Dept_Id = 0 
  If Exists (Select 1 From TblDepartment Where Cmp_Id = @Cmp_ID And Is_Deleted = 0 and  TenantId = @TenantId And Upper(Dept_Name) = @Dept_Name)         
  Begin               
   Raiserror('Bitplus:Duplicate Department Name',14,2)                
   Return -1               
  End                    
  If Exists (Select Dept_Name From TblDepartment Where Cmp_Id = @Cmp_ID And Is_Deleted = 0 And TenantId = @TenantId And Upper(Dept_Code) = @NewValue)     
  Begin           
   Raiserror('Bitplus:Duplicate Department Code',14,2)           
   Select @@Error         
   Return -1           
  End    
   
  Insert into TblDepartment (TenantId,Cmp_Id,Dept_Code,Dept_Name,Max_No_Of_Emp,Dept_Desc,Login_C_Id,CSysDate) 
  Values (@TenantId,@Cmp_Id,@NewValue,@Dept_Name,@Max_No_Of_Emp,@Dept_Desc,@Login_Id,GETDATE()) 
               
  Set @Dept_Id = SCOPE_IDENTITY()
                  
 End  
  
    IF OBJECT_ID('tempdb.dbo.##TempDept_HRMS') IS NOT NULL Drop table  ##TempDept_HRMS
End 
Return

GO---

If Exists (Select * From dbo.sysobjects where id = object_id(N'[dbo].[prcSubDepartmentInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                Drop Procedure [prcSubDepartmentInsert]

GO---

Create Procedure [dbo].[prcSubDepartmentInsert]       
@HRMS_DataBaseName varchar(50), 
@OldValue nvarchar(100), 
@NewValue nvarchar(100), 
@Cmp_Id int, 
@TenantId int, 
@Login_Id int,
@SubDepartment_Id int output
AS           
Begin        
 
Declare @LinkServer as varchar(100)   
Declare @Dept_Name NVarChar(200)  
Declare @Dept_Code NVarChar(200)  
Declare @SubDepartment_Name NVarChar(200)  
Declare @SubDepartment_Desc NVarChar(600)   

Set @LinkServer= '[' + @HRMS_DataBaseName + '].dbo.'       
 
IF OBJECT_ID('tempdb.dbo.##TempSubdept_HRMS') IS NOT NULL Drop table  ##TempSubdept_HRMS
 
Exec ('Select Top 1 
    S.SubDepartment_Name,S.SubDepartment_Desc ,D.Dept_Code,D.Dept_Name
       into ##TempSubdept_HRMS From ' + @LinkServer + 'tblSubdepartment S
       inner join ' + @LinkServer +  'tblDepartment D on D.Dept_Id = S.Dept_Id And D.Cmp_ID = S.Cmp_Id
    Where SubDepartment_Code='''+@NewValue+'''')  
 
 Select @SubDepartment_Name= SubDepartment_Name, @SubDepartment_Desc = SubDepartment_Desc,
                                @Dept_Name= Dept_Name, @Dept_Code = Dept_Code
 From ##TempSubdept_HRMS 

Declare @Dept_Id  int
Select @Dept_Id = Dept_Id From tblDepartment Where Cmp_Id = @Cmp_ID And TenantId= @TenantId And Is_Deleted = 0 And Dept_Code = @Dept_Code

If(isnull(@Dept_Id,0)=0)
Begin
                Exec prcDepartmentInsert @HRMS_DataBaseName,@Dept_Code,@Dept_Code,@Cmp_Id,@TenantId,@Login_Id,@Dept_Id output
End

If(isnull(@Dept_Id,0)=0)
Begin
Raiserror('Bitplus:Enter Proper Department data',14,2);Select @@Error;Return -1           
End

  
If Exists(select 1 from tblSubdepartment Where Cmp_Id = @Cmp_ID And TenantId= @TenantId And IS_Deleted = 0 And SubDepartment_Code = @OldValue) 
Begin  
Select @SubDepartment_Id = SubDepartment_Id From tblSubdepartment Where Cmp_Id = @Cmp_ID And TenantId= @TenantId And SubDepartment_Code = @OldValue And Is_Deleted = 0 
  
  If Exists (Select 1 From tblSubdepartment Where Cmp_Id = @Cmp_ID And TenantId = @TenantId And Is_Deleted = 0 And SubDepartment_Id <> @SubDepartment_Id And Upper(SubDepartment_Name) = @SubDepartment_Name)         
  Begin               
   Raiserror('Bitplus:Duplicate Subdepartment Name',14,2)               
   Select @@Error         
   Return -1               
  End                    
  If Exists (Select SubDepartment_Name From tblSubdepartment Where Cmp_Id = @Cmp_ID  And TenantId = @TenantId And Is_Deleted = 0 and SubDepartment_Id <> @SubDepartment_Id And Upper(SubDepartment_Code) = @NewValue )     
  Begin           
   Raiserror('Bitplus:Duplicate Subdepartment Code',14,2)            
   Select @@Error         
   Return -1           
  End    
   
  Update tblSubdepartment Set  SubDepartment_Code = @NewValue, SubDepartment_Name = @SubDepartment_Name, Dept_Id = @Dept_Id, SubDepartment_Desc= @SubDepartment_Desc,
                                                                                 Login_U_Id = @Login_Id, USysDate = GetDate()            
  Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And SubDepartment_Id= @SubDepartment_Id   
 
 End 
 Else 
 Begin 
  Set @SubDepartment_Id = 0 
  If Exists (Select 1 From tblSubdepartment Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Is_Deleted = 0 And Upper(SubDepartment_Name) = @SubDepartment_Name)         
  Begin               
   Raiserror('Bitplus:Duplicate Subdepartment Name',14,2)                
   Return -1               
  End                    
  If Exists (Select SubDepartment_Name From tblSubdepartment Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Is_Deleted = 0  And UPPER(SubDepartment_Code) = @NewValue)     
  Begin           
   Raiserror('Bitplus:Duplicate Subdepartment Code',14,2)           
   Select @@Error         
   Return -1           
  End    
   
  Insert into tblSubdepartment (TenantId,Cmp_Id,SubDepartment_Code,SubDepartment_Name,Dept_Id,SubDepartment_Desc,Login_C_Id,CSysDate) 
  Values (@TenantId,@Cmp_Id,@NewValue,@SubDepartment_Name,@Dept_Id,@SubDepartment_Desc,@Login_Id,GETDATE()) 

                Set @SubDepartment_Id = Scope_Identity()   
   
 End 
  
 IF OBJECT_ID('tempdb.dbo.##TempSubdept_HRMS') IS NOT NULL Drop table  ##TempSubdept_HRMS      

End 
Return

GO---

If Exists (Select * From dbo.sysobjects where id = object_id(N'[dbo].[prcDesignationInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                Drop Procedure [prcDesignationInsert]

GO---

Create Procedure [dbo].[prcDesignationInsert]     
@HRMS_DataBaseName nvarchar(50),
@OldValue nvarchar(100),
@NewValue nvarchar(100),
@Cmp_Id int,
@TenantId int,
@Login_Id int,
@Desig_Id  int output
AS         
Begin      

Declare @LinkServer as nvarchar(100) 
Declare @Desig_Name NVarChar(200) 
Declare @Desig_Desc NVarChar(600) 

set @LinkServer= '[' + @HRMS_DataBaseName + '].dbo.'       

IF OBJECT_ID('tempdb.dbo.##TempDesig_HRMS ') IS NOT NULL Drop table  ##TempDesig_HRMS

Exec ('select Top 1
                   Desig_Name,Desig_Desc
       into ##TempDesig_HRMS From ' + @LinkServer + 'TblDesignation    
                   Where Desig_Code='''+@NewValue+'''')          
                  
                  Select @Desig_Name= Desig_Name,@Desig_Desc = Desig_Desc From ##TempDesig_HRMS
                 
                If exists(select 1 from TblDesignation where Desig_Code = @OldValue And Cmp_Id = @Cmp_ID And TenantId= @TenantId And Is_Deleted = 0 )
                Begin
                                Select @Desig_Id = Desig_Id From TblDesignation where Cmp_Id = @Cmp_ID And TenantId= @TenantId And Desig_Code = @OldValue And Is_Deleted = 0
               
                                If Exists (Select 1 From TblDesignation Where Cmp_Id = @Cmp_ID And TenantId = @TenantId And Is_Deleted = 0 And Upper(Desig_Name) = @Desig_Name And Desig_Id <> @Desig_Id)       
                                Begin             
                                                Raiserror('Bitplus:Duplicate Designation Name',14,2)             
                                                Return -1             
                                End                  
                                If Exists (Select Desig_Name From TblDesignation Where Cmp_Id = @Cmp_ID And TenantId = @TenantId And Is_Deleted = 0  And Upper(Desig_Code) = @NewValue And Desig_Id <> @Desig_Id )    
                                Begin         
                                                Raiserror('Bitplus:Duplicate Designation Code',14,2)         
                                                Select @@Error       
                                                Return -1         
                                End  
                               
                                Update TblDesignation Set   Desig_Code = @NewValue, Desig_Name = @Desig_Name , Desig_Desc= @Desig_Desc, Login_U_Id = @Login_Id, USysDate = GetDate()          
                                Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Desig_Id= @Desig_Id                         
                               
                End
                Else
                Begin
                                Set @Desig_Id = 0
                                If Exists (Select 1 From TblDesignation Where Cmp_Id = @Cmp_ID And Is_Deleted = 0 and  TenantId = @TenantId And Upper(Desig_Name) = @Desig_Name )       
                                Begin             
                                                Raiserror('Bitplus:Duplicate Designation Name',14,2)             
                                                Return -1             
                                End                  
                                If Exists (Select Desig_Name From TblDesignation Where Cmp_Id = @Cmp_ID And Is_Deleted = 0 And TenantId = @TenantId And Upper(Desig_Code) = @NewValue )   
                                Begin         
                                                Raiserror('Bitplus:Duplicate Designation Code',14,2)         
                                                Select @@Error       
                                                Return -1         
                                End  
                               
                                Insert into TblDesignation (TenantId,Cmp_Id,Desig_Code,Desig_Name,Desig_Desc,Login_C_Id,CSysDate)
                                Values (@TenantId,@Cmp_Id,@NewValue,@Desig_Name,@Desig_Desc,@Login_Id,GETDATE())
                               
                                Set @Desig_Id = Scope_Identity()
                End
                                               
                IF OBJECT_ID('tempdb.dbo.##TempDesig_HRMS') IS NOT NULL Drop table  ##TempDesig_HRMS   
                  
End
Return

GO---

If Exists (Select * From dbo.sysobjects where id = object_id(N'[dbo].[prcCategoryInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                Drop Procedure [prcCategoryInsert]

GO---

Create Procedure [dbo].[prcCategoryInsert]     
@HRMS_DataBaseName varchar(50),
@OldValue nvarchar(100),
@NewValue nvarchar(100),
@Cmp_Id int,
@TenantId int,
@Login_Id int,
@Category_Id int output
AS         
Begin      

Declare @LinkServer as varchar(100) 
Declare @Category_Name NVarChar(200) 
Declare @Maximum_Employee int

set @LinkServer= '[' + @HRMS_DataBaseName + '].dbo.'       

IF OBJECT_ID('tempdb.dbo.##TempCategoty_HRMS') IS NOT NULL Drop table  ##TempCategoty_HRMS

Exec ('Select Category_Name,Maximum_Employee
       into ##TempCategoty_HRMS From ' + @LinkServer + 'TblCategory   
                   Where Category_Code='''+@NewValue+'''')   

                Select @Category_Name= Category_Name,@Maximum_Employee = Maximum_Employee From ##TempCategoty_HRMS
               
                If Exists(select 1 from TblCategory Where Cmp_Id = @Cmp_ID And TenantId= @TenantId And Category_Code = @OldValue)
                Begin
                                Select @Category_Id = Category_Id From TblCategory Where Cmp_Id = @Cmp_ID And TenantId= @TenantId And Category_Code = @OldValue And Is_Deleted = 0
               
                                If Exists (Select 1 From TblCategory Where Cmp_Id = @Cmp_ID And TenantId = @TenantId And Is_Deleted = 0 And Category_Id <> @Category_Id And Upper(Category_Name) = @Category_Name )       
                                Begin              
                                                Raiserror('Bitplus:Duplicate Category Name',14,2)             
                                                Return -1             
                                End                  
                                If Exists (Select Category_Name From TblCategory Where Cmp_Id = @Cmp_ID  And TenantId = @TenantId And Is_Deleted = 0 and Category_Id <> @Category_Id And Upper(Category_Code) = @NewValue )   
                                Begin         
                                                Raiserror('Bitplus:Duplicate Category Code',14,2)         
                                                Select @@Error       
                                                Return -1         
                                End  
                               
                                Update TblCategory Set   Category_Code = @NewValue, Category_Name = @Category_Name , Maximum_Employee= @Maximum_Employee, Login_U_Id = @Login_Id, USysDate = GetDate()           
                                Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Category_Id= @Category_Id                                                           
                               
                End
                Else
                Begin
                                Set @Category_Id = 0
                                If Exists (Select 1 From TblCategory Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Is_Deleted = 0 And Upper(Category_Name) = @Category_Name )       
                                Begin             
                                                Raiserror('Bitplus:Duplicate Category Name',14,2)             
                                                Return -1             
                                End                  
                                If Exists (Select Category_Name From TblCategory Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Is_Deleted = 0  And UPPER(Category_Code) = @NewValue)   
                                Begin         
                                                Raiserror('Bitplus:Duplicate Category Code',14,2)         
                                                Select @@Error       
                                                Return -1         
                                End  
                               
                                Insert into TblCategory (TenantId,Cmp_Id,Category_Code,Category_Name,Maximum_Employee,Login_C_Id,CSysDate)
                                Values (@TenantId,@Cmp_Id,@NewValue,@Category_Name,@Maximum_Employee,@Login_Id,GETDATE())
                               
                                Set @Category_ID = Scope_Identity()
                               
                End
               
                IF OBJECT_ID('tempdb.dbo.##TempCategoty_HRMS') IS NOT NULL Drop table  ##TempCategoty_HRMS             
End
Return

GO---

If Exists (Select * From dbo.sysobjects where id = object_id(N'[dbo].[prcLocationInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                Drop Procedure [prcLocationInsert]

GO---

Create Procedure [dbo].[prcLocationInsert]     
@HRMS_DataBaseName varchar(50),
@OldValue nvarchar(100),
@NewValue nvarchar(100),
@Cmp_Id int,
@TenantId int,
@Login_Id int,
@Location_Id Int output
AS         
Begin       

                Declare @LinkServer as varchar(100)     
                Declare @City_id int
                Declare @State_Id int
                Declare @Country_Id int
                Declare @Location_Name NVarChar(200)   
                --Declare @Location_Code NVarChar(200)   
                Declare @Location_Short_Name NVarChar(100)   
                Declare @Location_Desc nvarchar(600)
                Declare @Address nVarChar(500)   
                Declare @Phone_No VarChar(40)   
                Declare @Fax_No nVarChar(40)   
                Declare @Max_No_Of_Emp int               
                Declare @Country_Name NVarChar(100)   
                Declare @State_Name NVarChar(100)   
                Declare @City_Name nVarChar(100)
                Declare @PO_Box nvarchar(20)
               
                set @LinkServer= '[' + @HRMS_DataBaseName + '].dbo.'       
               
                IF OBJECT_ID('tempdb.dbo.##Loc_HRMS') IS NOT NULL Drop table  ##Loc_HRMS
               
                exec ('select L.Location_Code,L.Location_Short_Name,L.Location_Desc,L.Location_Name,
                L.[Address] as Location_Address,L.PO_Box,L.Phone_No as Location_Phone_No,
                L.Fax_No as Location_Fax_No,L.Max_No_Of_Emp,FCou.Country_Name,FSta.State_Name,FCit.City_Name
                into ##Loc_HRMS From ' + @LinkServer + 'tblLocation L
                LEFT JOIN ' + @LinkServer + 'tblCountry AS FCou ON (L.Country_Id=FCou.Country_Id)      
                LEFT JOIN ' + @LinkServer + 'tblState AS FSta ON (L.State_Id=FSta.State_Id)      
                LEFT JOIN ' + @LinkServer + 'tblCity AS FCit ON (L.City_Id=FCit.City_Id)      
                where L.Location_Code='''+@NewValue+'''')

                select @Location_Name = Location_Name,@Location_Short_Name = Location_Short_Name,@Location_Desc=Location_Desc,
                @Address=Location_Address, @City_Name = City_Name, @PO_Box=PO_Box, @State_Name = State_Name, @Country_Name =Country_Name, @Phone_No=Location_Phone_No,
    @Fax_No=Location_Fax_No,@Max_No_Of_Emp= Max_No_Of_Emp
                From ##Loc_HRMS        

                SET @Country_Id = 0                         
                SELECT @Country_Id = isnull(Country_Id,0) FROM tblCountry WHERE Cmp_Id = @Cmp_Id And TenantId = @TenantId And Is_Deleted = 0 And UPPER(Country_Name) = UPPER(@Country_Name)

                SET @State_Id = 0
                If(@Country_Id <> 0)
                Begin
                                Select @State_Id = ISNULL(State_Id,0) From tblState Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Is_Deleted = 0  And UPPER(State_Name) = UPPER(@State_Name) And Country_Id = @Country_Id
                End

                SET @City_Id = 0
                If(@Country_Id <> 0 And @State_Id <> 0)
                Begin
                                Select @City_Id = ISNULL(City_Id,0) From tblCity Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Is_Deleted = 0  And  UPPER(City_Name) = UPPER(@City_Name) And State_Id = @State_Id
                End
               
                If(@Country_Id = 0 or @State_Id = 0 or @City_Id = 0)
                Begin
                                Set @Country_Id  = null;Set @State_Id  = null;Set @City_Id  = null
                End

                If exists(select 1 from tblLocation where Cmp_Id = @Cmp_ID And TenantId= @TenantId And Is_Deleted = 0 And Location_Code = @OldValue )
                Begin                                    
                                Select @Location_Id = Location_Id From tblLocation where Cmp_Id = @Cmp_ID And TenantId= @TenantId And Is_Deleted = 0 And Location_Code = @OldValue 
                               
                                If Exists (Select Location_Name From tblLocation Where  Cmp_Id = @Cmp_ID And TenantId= @TenantId And Is_Deleted = 0 
                                                                                                                                                And Upper(Location_Name) = @Location_Name And Location_Id <> @Location_Id)       
                                Begin             
                                                Raiserror('Bitplus:Duplicate Location Name',14,2)             
                                                Return -1             
                                End                  
                                If Exists (Select Location_Name From tblLocation Where Cmp_Id = @Cmp_ID And TenantId= @TenantId And Is_Deleted = 0 
                                                                                                                And Upper(Location_Code) = @NewValue And Location_ID <> @Location_ID)
                                Begin         
                                                Raiserror('Bitplus:Duplicate Location Code',14,2)         
                                                Select @@Error       
                                                Return -1         
                                End  
                               
                                Update tblLocation Set       
                                Location_Code = @NewValue, Location_Short_Name = @Location_Short_Name, Location_Name = @Location_Name, 
                                Location_Desc = @Location_Desc,Address = @Address, City_Id = @City_Id, PO_Box = @PO_Box, State_Id = @State_Id,  
                                Country_Id = @Country_Id,Phone_No = @Phone_No, Fax_No = @Fax_No, Max_No_Of_Emp=@Max_No_Of_Emp,   
                                Login_U_Id = @Login_Id, USysDate = GetDate()          
                                Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Location_Id = @Location_Id                              
                                                               
                End
                Else
                Begin
                                Set @Location_Id = 0   
                                If Exists (Select Location_Name from tblLocation Where Cmp_Id = @Cmp_ID And TenantId = @TenantId And Is_Deleted = 0 And Upper(Location_Name) = @Location_Name)       
                                Begin             
                                                Raiserror('Bitplus:Duplicate Location Name',14,2)             
                                                Select @@Error             
                                                Return -1             
                                End             
                                If Exists (Select Location_Name from tblLocation Where Cmp_Id = @Cmp_ID And TenantId = @TenantId And Is_Deleted = 0  And Upper(Location_Code) = @NewValue)   
                                Begin         
                                                Raiserror('Bitplus:Duplicate Location Code',14,2)         
                                                Select @@Error         
                                                Return -1   
                                End             
                               
                                Insert Into tblLocation          
                                (Cmp_Id, TenantId, Location_Code, Location_Short_Name, Location_Name,Max_No_Of_Emp,Location_Desc, Address,        
                                City_Id, PO_Box, State_Id, Country_Id, Phone_No, Fax_No, Login_C_Id, CSysDate)  
                                Values       
                                (@Cmp_Id, @TenantId, @NewValue, @Location_Short_Name, @Location_Name,@Max_No_Of_Emp,@Location_Desc, @Address,        
                                @City_Id, @PO_Box, @State_Id, @Country_Id, @Phone_No, @Fax_No, @Login_Id, GetDate())  
                               
                                Set @Location_Id = SCOPE_IDENTITY()
                End
                                               
 
  IF OBJECT_ID('tempdb.dbo.##Loc_HRMS') IS NOT NULL Drop table  ##Loc_HRMS
End   
RETURN

GO---

If Exists (Select * From dbo.sysobjects where id = object_id(N'[dbo].[prcBankInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                Drop Procedure [prcBankInsert]

GO---

Create Procedure [dbo].[prcBankInsert]     
@HRMS_DataBaseName nvarchar(50),
@OldValue nvarchar(100),
@NewValue nvarchar(100),
@Cmp_Id int,
@TenantId int,
@Login_Id int,
@Bank_Id int output
AS         
Begin      

Declare @LinkServer as nvarchar(100) 
Declare @Bank_Name NVarChar(200) 
Declare @Bank_No NVarChar(60) 

set @LinkServer= '[' + @HRMS_DataBaseName + '].dbo.'       

IF OBJECT_ID('tempdb.dbo.##TempBank_HRMS') IS NOT NULL Drop table  ##TempBank_HRMS

Exec ('select Bank_Name,Bank_No
       into ##TempBank_HRMS From ' + @LinkServer + 'TblBank
                   Where Bank_Code='''+@NewValue+'''')           

                Select @Bank_Name= Bank_Name,@Bank_No= Bank_No From ##TempBank_HRMS
               
                If Exists(select 1 from TblBank where  Cmp_Id = @Cmp_ID And TenantId= @TenantId And Is_Deleted = 0  And Bank_Code = @OldValue)
                Begin
                               
                                Select @Bank_Id = Bank_Id From TblBank where Cmp_Id = @Cmp_ID And TenantId= @TenantId And Is_Deleted = 0 ANd Bank_Code = @OldValue
               
                                If Exists (Select 1 From TblBank Where Cmp_Id = @Cmp_ID And Is_Deleted = 0 And TenantId = @TenantId And Upper(Bank_Name) = @Bank_Name And Bank_Id <> @Bank_Id )       
                                Begin             
                                                Raiserror('Bitplus:Duplicate Bank Name',14,2)             
                                                Return -1             
                                End                  
                                If Exists (Select Bank_Name From TblBank Where Cmp_Id = @Cmp_ID And Is_Deleted = 0 And TenantId = @TenantId and  Upper(Bank_Code) = @NewValue And Bank_Id <> @Bank_Id )   
                                Begin         
                                                Raiserror('Bitplus:Duplicate Bank Code',14,2)         
                                                Select @@Error       
                                                Return -1         
                                End  
                               
                                Update TblBank Set   Bank_Code = @NewValue, Bank_Name = @Bank_Name ,Bank_No =@Bank_No,  Login_U_Id = @Login_Id, USysDate = GetDate()          
                                Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Bank_Id= @Bank_Id 
                End
                Else
                Begin
                                Set @Bank_Id = 0
                                If Exists (Select 1 From TblBank Where Cmp_Id = @Cmp_ID And TenantId = @TenantId  And Is_Deleted = 0 And Upper(Bank_Name) = @Bank_Name )       
                                Begin             
                                                Raiserror('Bitplus:Duplicate Bank Name',14,2)             
                                                Return -1             
                                End                   
                                If Exists (Select Bank_Name From TblBank Where Cmp_Id = @Cmp_ID And TenantId = @TenantId And Is_Deleted = 0  And Upper(Bank_Code) = @NewValue)   
                                Begin         
                                                Raiserror('Bitplus:Duplicate Bank Code',14,2)         
                                                Select @@Error       
                                                Return -1         
                                End  
                               
                                Insert into TblBank (TenantId,Cmp_Id,Bank_Code,Bank_Name,Bank_No,Login_C_Id,CSysDate)
                                Values (@TenantId,@Cmp_Id,@NewValue,@Bank_Name,@Bank_No,@Login_Id,GETDATE())
                               
                                select @Bank_Id = Scope_identity()
                End
               
                IF OBJECT_ID('tempdb.dbo.##TempBank_HRMS') IS NOT NULL Drop table  ##TempBank_HRMS
End
Return

GO---

If Exists (Select * From dbo.sysobjects where id = object_id(N'[dbo].[prcBankBranchInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                Drop Procedure [prcBankBranchInsert]

GO---

Create  Procedure [dbo].[prcBankBranchInsert]     
@HRMS_DataBaseName nvarchar(50),
@OldValue nvarchar(100),
@NewValue nvarchar(100),
@Cmp_Id int,
@TenantId int,
@Login_Id int,
@Bank_Branch_Id Int output
AS         
Begin      


Declare @LinkServer as nvarchar(100) 
Declare @Bank_Id int
Declare @Bank_Code NVarChar(200) 
Declare @Bank_Name NVarChar(200) 
Declare @Bank_No NVarChar(60) 
Declare @Bank_Branch_Name NVarChar(200) 
Declare @Bank_Branch_No NVarChar(60) 

set @LinkServer= '[' + @HRMS_DataBaseName + '].dbo.'    

IF OBJECT_ID('tempdb.dbo.##TempBankBranch_HRMS') IS NOT NULL Drop table  ##TempBankBranch_HRMS

Exec ('select Top 1
                   BB.Bank_Branch_Name,BB.Bank_Branch_No,B.Bank_Code,B.Bank_Name,B.Bank_No
       into ##TempBankBranch_HRMS From ' + @LinkServer + 'TblBank_Branch BB
       Inner Join ' + @LinkServer + 'tblBank B On  (B.Bank_Id = BB.Bank_Id)
                   Where BB.Bank_Branch_Code='''+@NewValue+'''')    

                Select @Bank_Branch_Name=Bank_Branch_Name,@Bank_Branch_No= Bank_Branch_No ,@Bank_Code= Bank_Code,
                @Bank_Name= Bank_Name, @Bank_No = Bank_No     From ##TempBankBranch_HRMS
               
                Select @Bank_Id = isnull(Bank_Id,0) from tblBank Where Cmp_Id = @Cmp_ID And TenantId= @TenantId And Is_Deleted = 0 And Bank_Code = @Bank_Code
               
                If isnull(@Bank_Id,0) = 0
                Begin
                                Exec [dbo].[prcBankInsert] @HRMS_DataBaseName ,@Bank_Code,@Bank_Code,@Cmp_Id,@TenantId,@Login_Id,@Bank_id output
                End       
               
                If Exists(select 1 from TblBank_Branch where Cmp_Id = @Cmp_ID And TenantId= @TenantId And Is_Deleted = 0 And Bank_Branch_Code = @OldValue)
                Begin
                                Select @Bank_Branch_Id = Bank_Branch_Id From TblBank_Branch where Cmp_Id = @Cmp_ID And TenantId= @TenantId And Bank_Branch_Code = @OldValue
               
                                If Exists (Select 1 From TblBank_Branch Where Cmp_Id = @Cmp_ID And TenantId = @TenantId And Is_Deleted = 0 And Upper(Bank_Branch_Name) = @Bank_Branch_Name
                                                                                                                                                                                                And Bank_Branch_Id <> @Bank_Branch_Id)       
                                Begin             
                                                Raiserror('Bitplus:Duplicate BankBranch Name',14,2)              
                                                Return -1             
                                End                  
                                If Exists (Select Bank_Branch_Name From TblBank_Branch Where Cmp_Id = @Cmp_ID And TenantId = @TenantId And Is_Deleted = 0 and Upper(Bank_Branch_Code) = @NewValue
                                                                                                                                                                                                                                                                And  Bank_Branch_Id <> @Bank_Branch_Id )   
                                Begin         
                                                Raiserror('Bitplus:Duplicate BankBranch Code',14,2)         
                                                Select @@Error       
                                                Return -1         
                                End  
                               
                                Update TblBank_Branch Set   Bank_Branch_Code = @NewValue, Bank_Branch_Name = @Bank_Branch_Name , Bank_Branch_No= @Bank_Branch_No, Bank_Id = @Bank_Id , Login_U_Id = @Login_Id, USysDate = GetDate()          
                                Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Bank_Branch_Id = @Bank_Branch_Id
                               
                End
                Else
                Begin
                                Set @Bank_Branch_Id = 0
                                If Exists (Select 1 From TblBank_Branch Where Cmp_Id = @Cmp_ID And TenantId = @TenantId  And Is_Deleted = 0 And Upper(Bank_Branch_Name) = @Bank_Branch_Name )       
                                Begin             
                                                Raiserror('Bitplus:Duplicate BankBranch Name',14,2)             
                                                Return -1             
                                End                  
                                If Exists (Select Bank_Branch_Name From TblBank_Branch Where Cmp_Id = @Cmp_ID And Is_Deleted = 0 And TenantId = @TenantId And Upper(Bank_Branch_Code) = @NewValue )   
                                Begin         
                                                Raiserror('Bitplus:Duplicate BankBranch Code',14,2)         
                                                Select @@Error       
                                                Return -1         
                                End  
                               
                                Insert into TblBank_Branch (TenantId,Cmp_Id,Bank_Id,Bank_Branch_Code,Bank_Branch_Name,Bank_Branch_No,Login_C_Id,CSysDate)
                                Values (@TenantId,@Cmp_Id,@Bank_Id,@NewValue,@Bank_Branch_Name,@Bank_Branch_No,@Login_Id,GETDATE())
                               
                                Select @Bank_Branch_Id = scope_identity()
                End
               
                IF OBJECT_ID('tempdb.dbo.##TempBankBranch_HRMS') IS NOT NULL Drop table  ##TempBankBranch_HRMS End
Return

GO---

If Exists (Select * From dbo.sysobjects where id = object_id(N'[dbo].[prcPensionSchemeInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                Drop Procedure [prcPensionSchemeInsert]

GO---

Create Procedure [dbo].[prcPensionSchemeInsert]           
@Scheme_Name nvarchar(200),
@Cmp_Id int,     
@TenantId int,     
@Login_Id int,   
@Scheme_Id int output   
AS               
Begin            
    
     if isnull(@Scheme_Name,'')=''
     return    

 If Exists(select 1 from tblPensionScheme Where Cmp_Id = @Cmp_ID And TenantId= @TenantId And Is_Deleted = 0 And Scheme_Name = @Scheme_Name)     
 Begin      
  Select @Scheme_Id = Scheme_Id From tblPensionScheme Where Cmp_Id = @Cmp_ID And TenantId= @TenantId And Is_Deleted = 0 And Scheme_Name = @Scheme_Name
      
  If Exists (Select 1 From tblPensionScheme Where Cmp_Id = @Cmp_ID And TenantId = @TenantId And Is_Deleted = 0 And Scheme_Id <> @Scheme_Id And Upper(Scheme_Name) = @Scheme_Name )             
  Begin                   
   Raiserror('Bitplus:Duplicate Pension Scheme Name',14,2)                   
   Return -1                   
  End            
                Update tblPensionScheme Set   Scheme_Name = @Scheme_Name, Login_U_Id = @Login_Id, USysDate = GetDate()                
                Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Scheme_Id = @Scheme_Id       
               
 End     
 Else     
 Begin     
  Set @Scheme_Id = 0     
  If Exists (Select 1 From tblPensionScheme Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Is_Deleted = 0 And Upper(Scheme_Name) = @Scheme_Name )             
  Begin                   
   Raiserror('Bitplus:Duplicate Pension Scheme Name',14,2)                   
   Return -1                   
  End                        
       
  Insert into tblPensionScheme (TenantId,Cmp_Id,Scheme_ShortName,Scheme_Name,Scheme_Desc,Login_C_Id,CSysDate,Is_Deleted)     
  Values (@TenantId,@Cmp_Id,@Scheme_Name,@Scheme_Name,'',@Login_Id,GETDATE(),0)     
    
 Set @Scheme_Id = Scope_Identity()       
       
 End     

End     
Return 

GO---

If Exists (Select * From dbo.sysobjects where id = object_id(N'[dbo].[prcMaritalStatusInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                Drop Procedure [prcMaritalStatusInsert]

GO---

Create Procedure [dbo].[prcMaritalStatusInsert]       
@HRMS_DataBaseName nvarchar(50), 
@OldValue nvarchar(100), 
@NewValue nvarchar(100), 
@Cmp_Id bigint, 
@TenantId bigint, 
@Login_Id bigint,
@MaritalStatus_Id bigint output
AS           
Begin        
 
Declare @LinkServer as nvarchar(100)   
Declare @MaritalStatus_Name NVarChar(200)   
Declare @MaritalStatus_Desc NVarChar(600)   

set @LinkServer= '[' + @HRMS_DataBaseName + '].dbo.'       
 
IF OBJECT_ID('tempdb.dbo.##TempMaritalStatus_HRMS') IS NOT NULL Drop table  ##TempMaritalStatus_HRMS    
 
Exec ('Select Top 1 
    MaritalStatus_Name,MaritalStatus_Desc
       into ##TempMaritalStatus_HRMS From ' + @LinkServer + 'tblMaritalStatus   
    Where MaritalStatus_Code='''+@NewValue+'''')  
 
 Select @MaritalStatus_Name= MaritalStatus_Name, @MaritalStatus_Desc = MaritalStatus_Desc From ##TempMaritalStatus_HRMS 
  
 If Exists(select 1 from tblMaritalStatus Where Cmp_Id = @Cmp_ID And TenantId= @TenantId And MaritalStatus_Code = @OldValue) 
 Begin  
  Select @MaritalStatus_Id = MaritalStatus_Id From tblMaritalStatus Where Cmp_Id = @Cmp_ID And TenantId= @TenantId And MaritalStatus_Code = @OldValue And Is_Deleted = 0 
  
  If Exists (Select 1 From tblMaritalStatus Where Cmp_Id = @Cmp_ID And TenantId = @TenantId And Is_Deleted = 0 And MaritalStatus_Id  <> @MaritalStatus_Id
                                                                                And Upper(MaritalStatus_Name) = @MaritalStatus_Name )         
  Begin               
   Raiserror('Bitplus:Duplicate Marital Status Name',14,2)               
   Return -1               
  End                    
  If Exists (Select MaritalStatus_Name From tblMaritalStatus Where Cmp_Id = @Cmp_ID  And TenantId = @TenantId And Is_Deleted = 0 and MaritalStatus_Id <> @MaritalStatus_Id And Upper(MaritalStatus_Code) = @NewValue )     
  Begin           
   Raiserror('Bitplus:Duplicate Marital Status Code',14,2)           
   Select @@Error         
   Return -1           
  End    
   
  Update tblMaritalStatus Set MaritalStatus_Code = @NewValue, MaritalStatus_Name = @MaritalStatus_Name, MaritalStatus_Desc = @MaritalStatus_Desc,
                                Login_U_Id = @Login_Id, USysDate = GetDate()            
  Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And MaritalStatus_Id= @MaritalStatus_Id   
   
 End 
 Else 
 Begin 
  Set @MaritalStatus_Id = 0 
  If Exists (Select 1 From tblMaritalStatus Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Is_Deleted = 0 And Upper(MaritalStatus_Name) = @MaritalStatus_Name )         
  Begin               
   Raiserror('Bitplus:Duplicate Marital Status Name',14,2)               
   Return -1               
  End                    
  If Exists (Select MaritalStatus_Name From tblMaritalStatus Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Is_Deleted = 0  And UPPER(MaritalStatus_Code) = @NewValue)     
  Begin           
   Raiserror('Bitplus:Marital Status Code',14,2)           
   Select @@Error         
   Return -1           
  End    
   
  Insert into tblMaritalStatus (TenantId,Cmp_Id,MaritalStatus_Code,MaritalStatus_Name,MaritalStatus_Desc,Login_C_Id,CSysDate) 
  Values (@TenantId,@Cmp_Id,@NewValue,@MaritalStatus_Name,@MaritalStatus_Desc,@Login_Id,GETDATE()) 

                Set @MaritalStatus_Id = Scope_Identity()   
   
 End 

End 
Return

GO---

If Exists (Select * From dbo.sysobjects where id = object_id(N'[dbo].[prcSalaryGradeInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                Drop Procedure [prcSalaryGradeInsert]

GO---

Create Procedure [dbo].[prcSalaryGradeInsert]             
@HRMS_DataBaseName varchar(50), 
@OldValue nvarchar(100), 
@NewValue nvarchar(100), 
@Cmp_Id bigint,       
@TenantId bigint,       
@Login_Id bigint,     
@SalaryGrade_Id bigint output      
AS                 
Begin              
      
Declare @LinkServer as varchar(100)   
Declare @SalaryGrade_Name NVarChar(200)   
Declare @SalaryGrade_Code NVarChar(600)   

Set @LinkServer= '[' + @HRMS_DataBaseName + '].dbo.'       

IF OBJECT_ID('tempdb.dbo.##TempSalary_HRMS') IS NOT NULL Drop table  ##TempSalary_HRMS

Exec ('Select  SalaryGrade_Code,SalaryGrade_Name
       Into ##TempSalary_HRMS From ' + @LinkServer + 'tblSalaryGrade   
       Where SalaryGrade_Code='''+@NewValue+'''')  
   
Select @SalaryGrade_Name= SalaryGrade_Name,@SalaryGrade_Code= SalaryGrade_Code From ##TempSalary_HRMS 
 
If Exists(select 1 from tblSalaryGrade Where Cmp_Id = @Cmp_ID And TenantId= @TenantId And SalaryGrade_Code = @OldValue)       
Begin         
                Select @SalaryGrade_Id = SalaryGrade_Id From tblSalaryGrade Where Cmp_Id = @Cmp_ID And TenantId= @TenantId And SalaryGrade_Code = @OldValue  
        
                If Exists (Select 1 From tblSalaryGrade Where Cmp_Id = @Cmp_ID And TenantId = @TenantId And SalaryGrade_Id <> @SalaryGrade_Id And Upper(SalaryGrade_Name) = @SalaryGrade_Name )               
                Begin                     
                                Raiserror('Bitplus:Duplicate Salary Grade Name',14,2)                     
                                Return -1                     
                End       
                If Exists (Select 1 From tblSalaryGrade Where Cmp_Id = @Cmp_ID And TenantId = @TenantId And SalaryGrade_Id <> @SalaryGrade_Id And Upper(SalaryGrade_Code) = @NewValue )               
                Begin                     
                                Raiserror('Bitplus:Duplicate Salary Grade Code',14,2)                     
                                Return -1                     
                End              
                                Update tblSalaryGrade Set  SalaryGrade_Code = @NewValue ,SalaryGrade_Name = @SalaryGrade_Name, Login_U_Id = @Login_Id, USysDate = GetDate()                  
                                Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And SalaryGrade_Id = @SalaryGrade_Id            
                End       
                Else       
                Begin       
                                Set @SalaryGrade_Id = 0       
                                If Exists (Select 1 From tblSalaryGrade Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Upper(SalaryGrade_Name) = @SalaryGrade_Name )               
                                Begin                     
                                                Raiserror('Bitplus:Duplicate Salary Grade Name',14,2)                      
                                                Return -1                     
                                End 
                                If Exists (Select 1 From tblSalaryGrade Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Upper(SalaryGrade_Code) = @NewValue )               
                                Begin                     
                                                Raiserror('Bitplus:Duplicate Salary Grade Code',14,2)                     
                                                Return -1                     
                                End                          
         
                                Insert into tblSalaryGrade (TenantId,Cmp_Id,SalaryGrade_Code,SalaryGrade_Name,SalaryGrade_Type,SalaryGrade_Basic,Login_C_Id,CSysDate,HousingAllowanceApplicable,Pension,PF,Rowguid)       
                                Values (@TenantId,@Cmp_Id,@SalaryGrade_Code,@SalaryGrade_Name,1,0,@Login_Id,GETDATE(),0,0,0,NEWID())                                
     
                                Set @SalaryGrade_Id = Scope_Identity()          
         
 End       
   IF OBJECT_ID('tempdb.dbo.##TempSalary_HRMS') IS NOT NULL Drop table  ##TempSalary_HRMS
End       
Return

GO---

If Exists (Select * From dbo.sysobjects where id = object_id(N'[dbo].[prcUpdateMasters]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                Drop Procedure [prcUpdateMasters]

GO---

Create Procedure [dbo].[prcUpdateMasters]   
@HRMS_DataBaseName varchar(50),   
@MasterType int,   
@OldValue nvarchar(100),   
@NewValue nvarchar(100),   
@Cmp_Id int,   
@TenantId int   
As   
Begin   
   
   
Declare @Login_Id int   
Select Top 1 @Login_Id = Login_Id From tblLogin Where Cmp_Id = @Cmp_Id  And TenantId= @TenantId   
   
   
If @MasterType=0 -- Location   
Begin      
 declare @Loc_Id int 
 Exec dbo.prcLocationInsert @HRMS_DataBaseName,@OldValue,@NewValue,@Cmp_Id,@TenantId,@Login_Id,@Loc_Id  output    
End    
Else If @MasterType=1 -- Department   
Begin   
                declare @Dept int 
 Exec dbo.prcDepartmentInsert @HRMS_DataBaseName,@OldValue,@NewValue,@Cmp_Id,@TenantId,@Login_Id,@Dept output    
End    
Else If @MasterType=2 -- Designation   
Begin   
                declare @Desig int 
                Exec dbo.prcDesignationInsert @HRMS_DataBaseName,@OldValue,@NewValue,@Cmp_Id,@TenantId,@Login_Id,@Desig output
End    
Else If @MasterType=3 -- Category   
Begin   
                declare @Cat_Id int 
 Exec dbo.prcCategoryInsert @HRMS_DataBaseName,@OldValue,@NewValue,@Cmp_Id,@TenantId,@Login_Id,@Cat_Id output    
End    
Else If @MasterType=4 -- Bank   
Begin   
 declare @Bank_Id int 
 Exec dbo.prcBankInsert @HRMS_DataBaseName,@OldValue,@NewValue,@Cmp_Id,@TenantId,@Login_Id,@Bank_Id output    
End   
Else If @MasterType=5 -- Bank Branch   
Begin   
 declare @BankBranch_Id int 
 Exec dbo.prcBankBranchInsert @HRMS_DataBaseName,@OldValue,@NewValue,@Cmp_Id,@TenantId,@Login_Id,@BankBranch_Id output    
End    
Else If @MasterType=6 -- Class   
Begin   
 declare @Class_Id int 
 Exec dbo.prcClassInsert @HRMS_DataBaseName,@OldValue,@NewValue,@Cmp_Id,@TenantId,@Login_Id,@Class_Id  output   
End    
Else If @MasterType= 7 -- Subdepartment
Begin   
 declare @Subdepartment_Id int 
 Exec dbo.prcSubDepartmentInsert @HRMS_DataBaseName,@OldValue,@NewValue,@Cmp_Id,@TenantId,@Login_Id,@Subdepartment_Id  output   
End    
   
End

GO---

If Exists (Select * From dbo.sysobjects where id = object_id(N'[dbo].[UpdateEmployeeDetailsFromHRMS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                Drop Procedure [UpdateEmployeeDetailsFromHRMS]

GO---

Create procedure [dbo].[UpdateEmployeeDetailsFromHRMS]   
@EmpPayrollNo varchar(50),   
@HRMS_DataBaseName varchar(50),   
@EmpPayrollNo_Old varchar(50),   
@Update_Flag int = 0 output, 
@Cmp_Id int = 1,
@TenantId int = 1
As       
Begin   
   
Declare @Sqlstr Varchar(Max)   
Declare @LinkServer as varchar(100)     
   
Declare @Desig_Code nVarChar(200)   
Declare @Dept_Code nVarChar(200)   
Declare @Category_Code nVarChar(200)   
Declare @Location_Code nVarChar(200)   
Declare @SubDepartment_Code nVarChar(200)   
Declare @Nationality_Code nVarChar(200)   
Declare @Religion_Code nVarChar(200)
Declare @Class_Code nVarChar(200)

declare @EmpCode int   
declare @Emp_Desig_Id int   
declare @Emp_Dept_Id int   
declare @Emp_Location_Id int   
declare @Emp_Category_Id int  
declare @Emp_Class_Id int
declare @Emp_SubDepartment_Id int
declare @Emp_Scheme_Id int
Declare @Emp_SalaryGrade_Id int
Declare @Emp_MaritalStatus_Id int
declare @HRMSCocode int   
declare @Emp_WeekOffType int   

Declare @Emp_Code nvarchar(200)
Declare @Emp_Payroll_No  nvarchar(200)
Declare @Emp_Title int
Declare @Emp_Title_Name nvarchar(200)
Declare @Emp_First_Name nvarchar(200)
Declare @Emp_Middle_Name nvarchar(200)
Declare @Emp_Last_Name nvarchar(200)
Declare @Emp_Name nvarchar(600)
Declare @Emp_Gender int
Declare @Emp_Join_Date datetime
Declare @Employment_Type int
Declare @Emp_Children bit
Declare @Emp_Dependents bit
Declare @Is_Active Bit 
Declare @PensionPin Nvarchar(200)
Declare @PensionScheme Nvarchar(200)
Declare @Emp_SalaryGrade_Code nvarchar(200)
Declare @MaritalStatus_Code nvarchar(200)
   
set @Update_Flag = 0   
set @LinkServer= '[' + @HRMS_DataBaseName + '].dbo.'       
   
IF OBJECT_ID('tempdb.dbo.##TempPayEmployee_HRMS') IS NOT NULL Drop table  ##TempPayEmployee_HRMS     

exec ('select Top 1 A.Cmp_ID,A.Emp_ID,A.Emp_Code, A.Emp_Payroll_No, A.Emp_First_Name, A.Emp_Middle_Name, A.Emp_Last_Name,
 A.Emp_Name,
 A.Emp_Gender, A.Emp_Join_Date,A.Emp_Birth_Date,A.Employment_Type,A.Emp_Children,A.Emp_WeekOff_Type,A.Is_Separate,
 B.Desig_Code,C.Dept_Code,D.Category_Code, E.Location_Code,F.Nationality_Code, G.Religion_Code,H.Class_Code,J.Subdepartment_Code,
 A.Is_Active,A.Emp_Dependents,I.LookUp_Data_Value
 into ##TempPayEmployee_HRMS From ' + @LinkServer + 'tblEmployee AS A    
 LEFT JOIN ' + @LinkServer + 'tblDesignation AS B ON (A.Emp_Desig_Id = B.Desig_Id)    
 LEFT JOIN ' + @LinkServer + 'tblDepartment AS C ON (A.Emp_Dept_Id=C.Dept_Id)     
 LEFT JOIN ' + @LinkServer + 'tblCategory AS D ON (A.Category_Id = D.Category_Id)    
 LEFT JOIN ' + @LinkServer + 'tblLocation AS E ON (A.Emp_Location_Id=E.Location_Id)       
 left join ' + @LinkServer + 'tblNationality F on (A.Emp_Nationality_Id=F.Nationality_Id)      
 left join ' + @LinkServer + 'tblReligion G on (a.Emp_Religion_Id = G.Religion_Id and a.Cmp_Id = G.Cmp_Id)     
 left join ' + @LinkServer + 'tblClass H on (a.Emp_Class_Id = H.Class_Id and a.Cmp_Id = H.Cmp_Id)     
 left join ' + @LinkServer + 'tblSubDepartment J on (a.Emp_SubDepartment_Id = J.SubDepartment_Id and a.Cmp_Id = J.Cmp_Id)     
 left join ' + @LinkServer + 'tblLookUp_Data I on (a.Emp_Title = I.Lookup_Data_Id and a.Cmp_Id = I.Cmp_Id And Lookup_Type_Id = 1)      
 WHERE A.Emp_Payroll_No= '''+ @EmpPayrollNo + '''')    
    
    
select  @HRMSCocode= Cmp_Id, @Emp_Code = Emp_Code, @Emp_Payroll_No = Emp_Payroll_No, @Emp_Name = Emp_Name,
        @Emp_First_Name= Emp_First_Name, @Emp_Middle_Name= Emp_Middle_Name, @Emp_Last_Name = Emp_Last_Name, @Emp_Gender = Emp_Gender,     
        @Emp_Join_Date = Emp_Join_Date , @Employment_Type= Employment_Type, @Emp_WeekOffType = Emp_WeekOff_Type,
                                @Desig_Code = Desig_Code, @Dept_Code=Dept_Code, @Category_Code = Category_Code, @Location_Code = Location_Code,
        @Nationality_Code = Nationality_Code, @Class_Code = Class_Code, @SubDepartment_Code = Subdepartment_Code,
        @Emp_WeekOffType = Emp_WeekOff_Type , @Emp_Children = Emp_Children,
        @Emp_Dependents = Emp_Dependents, @Is_Active = Is_Active, @Emp_Title_Name=LookUp_Data_Value
         From ##TempPayEmployee_HRMS   
WHERE Emp_Payroll_No= @EmpPayrollNo    
 
   
SET @Emp_Desig_Id = 0               
SELECT @Emp_Desig_Id = Desig_Id FROM tblDesignation WHERE Cmp_Id = @Cmp_Id And TenantId = @TenantId And UPPER(Desig_Code) = UPPER(@Desig_Code)  And Is_deleted = 0
   
SET @Emp_Dept_Id = 0                         
SELECT @Emp_Dept_Id = Dept_Id FROM tblDepartment WHERE Cmp_Id = @Cmp_Id And TenantId = @TenantId And  UPPER(Dept_Code) = UPPER(@Dept_Code)  And Is_deleted = 0
   
SET @Emp_Location_Id= 0             
SELECT @Emp_Location_Id= Location_Id FROM tblLocation WHERE Cmp_Id = @Cmp_Id And TenantId = @TenantId And  UPPER(Location_Code) = UPPER(@Location_Code) And Is_deleted = 0
   
SET @Emp_Category_Id= 0                         
SELECT @Emp_Category_Id= Category_Id FROM tblCategory WHERE Cmp_Id = @Cmp_Id And TenantId = @TenantId And  UPPER(Category_Code) = UPPER(@Category_Code) And Is_deleted = 0 

Set @Emp_Class_Id = 0
SELECT @Emp_Class_Id= Class_Id FROM tblClass WHERE Cmp_Id = @Cmp_Id And TenantId = @TenantId And  UPPER(Class_Code) = UPPER(@Class_Code) And Is_deleted = 0 

Set @Emp_SubDepartment_Id = 0
SELECT @Emp_SubDepartment_Id= SubDepartment_Id FROM tblSubDepartment WHERE Cmp_Id = @Cmp_Id And TenantId = @TenantId And  UPPER(SubDepartment_Code) = UPPER(@SubDepartment_Code) And Is_deleted = 0 

Declare @Login_Id int
Select Top 1  @Login_Id =Login_Id From tblLogin Where Cmp_Id = @Cmp_Id And TenantId = @TenantId

Begin Try
                IF isnull(@Emp_Location_Id,0)= 0    
                BEGIN                                       
                                 Exec dbo.prcLocationInsert @HRMS_DataBaseName,@Location_Code,@Location_Code,@Cmp_Id,@TenantId,@Login_Id,@Emp_Location_Id output
                END     
                IF isnull(@Emp_Dept_Id,0) = 0     
                BEGIN                                                  
                                Exec dbo.prcDepartmentInsert @HRMS_DataBaseName,@Dept_Code,@Dept_Code,@Cmp_Id,@TenantId,@Login_Id,@Emp_Dept_Id output                                             
                END
                IF isnull(@Emp_Desig_Id,0) = 0    
                BEGIN                  
                                Exec dbo.prcDesignationInsert @HRMS_DataBaseName,@Desig_Code,@Desig_Code,@Cmp_Id,@TenantId,@Login_Id,@Emp_Desig_Id output     
                END                    
                IF isnull(@Emp_Category_Id,0)= 0    
                BEGIN                                       
                                 Exec dbo.prcCategoryInsert @HRMS_DataBaseName,@Category_Code,@Category_Code,@Cmp_Id,@TenantId,@Login_Id,@Emp_Category_Id output      
                END  
                IF isnull(@Emp_SubDepartment_Id,0)= 0                
                BEGIN
                                If isnull(@SubDepartment_Code,'') <>''
                                                Exec dbo.prcSubDepartmentInsert @HRMS_DataBaseName,@SubDepartment_Code,@SubDepartment_Code,@Cmp_Id,@TenantId,@Login_Id,@Emp_SubDepartment_Id output      
                END  
                IF isnull(@Emp_Class_Id,0)= 0    
                BEGIN                                     
                                 If isnull(@Class_Code,'')<>'' 
                                                Exec dbo.prcClassInsert @HRMS_DataBaseName,@Class_Code,@Class_Code,@Cmp_Id,@TenantId,@Login_Id,@Emp_Class_Id output                  
                END  
                IF isnull(@Emp_Scheme_Id,0)= 0    
                BEGIN                             
                                 if(ISNULL(@PensionScheme,'') <> '')                                                                                                                                              
                                                Exec dbo.prcPensionSchemeInsert @PensionScheme,@Cmp_Id,@TenantId,@Login_Id,@Emp_Scheme_Id output                                              
                END
                IF isnull(@Emp_SalaryGrade_Id,0)= 0    
                BEGIN                             
                                 if(ISNULL(@Emp_SalaryGrade_Code,'') <> '')                                                                                                                                             
                                                Exec dbo.prcSalaryGradeInsert @HRMS_DataBaseName,@Emp_SalaryGrade_Code,@Emp_SalaryGrade_Code,@Cmp_Id,@TenantId,@Login_Id,@Emp_SalaryGrade_Id  output     
                END     
                IF isnull(@Emp_MaritalStatus_Id,0)= 0    
                BEGIN                             
                                 if(ISNULL(@MaritalStatus_Code,'') <> '')                                                                                                                                                      
                                                Exec dbo.prcMaritalStatusInsert @HRMS_DataBaseName,@MaritalStatus_Code,@MaritalStatus_Code,@Cmp_Id,@TenantId,@Login_Id,@Emp_MaritalStatus_Id  output     
                End
End Try
Begin Catch
                  Declare @Error as nvarchar(1000)
                  Set @Error  =  ERROR_MESSAGE()
                  Raiserror(@Error  ,14,2);
                  Return
End Catch

If Not Exists(Select 1 from tblLookUp_Type Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And LookUp_Type_Key In (1,2,3,16))
Begin
                                Raiserror('Bitplus:Set Lookup Data For EmploymentType/WeekOff/Gender/Title',14,2);Select @@Error;Return
End

If(@Emp_WeekOffType= 2)
Begin
                Raiserror('Bitplus:Datewise weekoff not allow in paymaster, please select day wise weekoff',14,2);Select @@Error;Return
End
Declare @Gender_Name nVarchar(100)
Declare @WeekOffType_Name nVarchar(100)
Declare @WeekDay int

SELECT @Employment_Type = case @Employment_Type when 1 then 1 when 2 then 2 when 3 then 1 end
Select @Gender_Name  = Case @Emp_Gender when 1 then 'male' when 2 then 'female' end
Select @WeekOffType_Name = Case @Emp_WeekOffType when 1 then 'day wise' when 2 then 'date wise' end

Select @Emp_Gender = isnull(LookUp_Data_Id ,0)from tblLookUp_Data Where Cmp_Id = @Cmp_Id And TenantId = @TenantId  And lower(Lookup_Data_Value) = lower(@Gender_Name)
Select @Emp_WeekOffType = isnull(LookUp_Data_Def_Id ,0)from tblLookUp_Data Where Cmp_Id = @Cmp_Id And TenantId = @TenantId  And lower(Lookup_Data_Value) = lower(@WeekOffType_Name)
Select @Emp_Title = isnull(LookUp_Data_Id ,0)from tblLookUp_Data Where Cmp_Id = @Cmp_Id And TenantId = @TenantId  And lower(Lookup_Data_Value) = lower(@Emp_Title_Name)


If(isnull(@Emp_Gender,0) = 0)
Begin
                Raiserror('Bitplus:Set Proper Lookup Data for Gender',14,2);Select @@Error;Return
                return
End
If(isnull(@Emp_WeekOffType,0) = 0)
Begin
                Raiserror('Bitplus:Set Proper Lookup Data for Weekoff Type',14,2);Select @@Error;Return
                return
End
If(isnull(@Emp_Title,0) = 0)
Begin
                Raiserror('Bitplus:Set Proper Lookup Data for Title',14,2);Select @@Error;Return
                return
End

Set @WeekDay = null

If(@WeekOffType_Name = 'day wise')
Begin
                Select @WeekDay = isnull(LookUp_Data_Def_Id ,0) From tblLookUp_Data Where Cmp_Id = @Cmp_Id And TenantId = @TenantId  And lower(Lookup_Data_Value) = lower('sunday')
                If(@WeekDay = 0)
                Begin
                                Raiserror('Bitplus:Set Proper Lookup Data for Weekday',14,2);Select @@Error;Return
                End
End

If @Emp_SubDepartment_Id = 0
                set @Emp_SubDepartment_Id = null
                               
If @Emp_Class_Id = 0
                set @Emp_Class_Id = null
                               
If @Emp_Scheme_Id = 0
                Set @Emp_Scheme_Id=null
               
If @Emp_SalaryGrade_Id = 0
                Set @Emp_SalaryGrade_Id=null

If @Emp_MaritalStatus_Id = 0
                Set @Emp_MaritalStatus_Id = null

If Not Exists(Select * From tblEmployee Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Payroll_No = @EmpPayrollNo_Old And Is_Deleted = 0 And Is_Separate = 0) 
Begin
               
                If Exists (Select 1 From tblEmployee Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Payroll_No = @EmpPayrollNo_Old And Is_Deleted = 0
                                                                                                                                                                And Is_Separate = 0  And Emp_Payroll_No = @EmpPayrollNo)
                Begin
                                Raiserror('Bitplus:Duplicate Payroll No',14,2)         
                                Select @@Error
                                Return
                End                       
               
                Insert Into tblEmployee (TenantId,Cmp_Id,Emp_Code,Emp_Payroll_No,Emp_Title,Emp_First_Name,Emp_Middle_Name,Emp_Last_Name,
                                                                                                                  Emp_Name,Emp_Gender,Emp_Join_Date,Emp_Dept_Id,Emp_Location_Id,Emp_Desig_Id,Category_Id,
                                                                                                                Emp_SubDepartment_Id,Emp_Class_Id,
                                                                                                                Employment_Type,Emp_Children,Emp_Dependents,Login_C_Id,CSysDate,Is_Active,Emp_WeekOff_Type,
                                                                                                                IS_First_Job_in_Kenya,WeekDay,Company_Calendar_Id)
                                                                                Values (@TenantId,@Cmp_Id,@Emp_Code,@Emp_Payroll_No,@Emp_Title,@Emp_First_Name,@Emp_Middle_Name,@Emp_Last_Name,
                                                                                                  @Emp_Name,@Emp_Gender,@Emp_Join_Date,@Emp_Dept_Id,@Emp_Location_Id,@Emp_Desig_Id,@Emp_Category_Id,
                                                                                                                @Emp_SubDepartment_Id,@Emp_Class_Id,
                                                                                                @Employment_Type,@Emp_Children,@Emp_Dependents,@Login_Id,GETDATE(),@Is_Active,@Emp_WeekOffType,1,@WeekDay,null)
                               
                                Declare @NewEmp_Id Bigint
                                Set @NewEmp_Id  = SCOPE_IDENTITY()
                               
                                Begin Try
                                                Exec EmployeeEffectAfterInsertFromHRMS @Cmp_Id,@TenantId,@Login_Id,@NewEmp_Id
                                End Try
                                Begin Catch
                                                                Declare @Err as nvarchar(1000)
                                                                Set @Err  =  ERROR_MESSAGE()
                                                                Raiserror(@Err  ,14,2);
                                                                Return
                                End Catch
               
End
Else
Begin
                Declare @Emp_Id int
                Select @Emp_Id = Emp_Id From tblEmployee Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Is_Deleted = 0 And Is_Separate = 0  And Emp_Payroll_No = @EmpPayrollNo_Old
                                                                               
                If Exists (Select 1 From tblEmployee Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Is_Deleted = 0 And Is_Separate = 0  And Emp_Payroll_No = @EmpPayrollNo And Emp_Id <> @Emp_Id )
                Begin
                                Raiserror('Bitplus:Duplicate Payroll No',14,2)         
                                Select @@Error
                                Return
                End       
               
                Update tblEmployee
                Set
                Emp_Payroll_No = @Emp_Payroll_No, Emp_Code = @Emp_Code, Emp_Title = @Emp_Title, Emp_First_Name = @Emp_First_Name, Emp_Middle_Name= @Emp_Middle_Name,
                Emp_Last_Name = @Emp_Last_Name, Emp_Name = @Emp_Name, Emp_Gender = @Emp_Gender, Emp_Join_Date = Emp_Join_Date, Emp_Dept_Id= @Emp_Dept_Id,
                Emp_SubDepartment_Id = @Emp_SubDepartment_Id, Emp_Class_Id = @Emp_Class_Id,
                Emp_Location_Id= @Emp_Location_Id, Emp_Desig_Id = @Emp_Desig_Id,  Employment_Type = @Employment_Type , Emp_Children = @Emp_Children,
                Emp_Dependents = @Emp_Dependents, Login_U_Id = @Login_Id, USysDate = GETDATE(), Is_Active = @Is_Active, Category_Id = @Emp_Category_Id,
                Emp_WeekOff_Type = @Emp_WeekOffType, WeekDay  = @WeekDay    
                Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id
               
                If Not Exists(Select 1 From tblEmployee_Salary Where Cmp_Id = @Cmp_Id And TenantId =@TenantId  And Emp_Id = @Emp_Id)   
                Begin
                                Declare @PaymentMode bigint     
                                Select @PaymentMode  = LD.LookUp_Data_Def_Id From tbllookup_Type lt    
                                Inner join tbllookup_data ld on lt.Cmp_Id = ld.Cmp_Id And lt.TenantId = LD.TenantId And lt.Lookup_Type_Id = ld.Lookup_Type_Id   
                                Where LT.LookUp_Type_Key = 14 And LT.Cmp_Id = @Cmp_ID And LT.TenantId = @TenantId And LookUp_Data_Value ='cash'   
   
                                Insert Into tblEmployee_Salary(TenantId,Cmp_Id,Emp_Id,Use_salary_DailyBasic,Use_Prorated_Salary,Use_DOJ,SalaryGrade_Id,WagesRate_Id,Payment_Mode,Login_C_Id,CSysDate)   
                                Values (@TenantId, @Cmp_Id,@Emp_Id,0,0,0,@Emp_SalaryGrade_id,null,@PaymentMode,@Login_Id,getdate())    
                End
                Else
                Begin
                                Declare @SalaryGrade_Id int                     
                                Select @SalaryGrade_Id = SalaryGrade_Id From tblEmployee_Salary Where Cmp_Id = @Cmp_Id And TenantId = @TenantId  And Emp_Id = @Emp_Id
                               
                                If @SalaryGrade_Id <> @Emp_SalaryGrade_Id
                                Begin
                                                Update tblEmployee_Salary Set SalaryGrade_Id = @Emp_SalaryGrade_Id Where Cmp_Id = @Cmp_Id And TenantId = @TenantId  And Emp_Id = @Emp_Id
                                End                       
                End

End


 Declare @S_Compliance_For As TinyInt
  Select @S_Compliance_For = S_Compliance_For From tblCompany Where Cmp_Id = @Cmp_Id And TenantId = @TenantId
 
  If isnull(@PensionPin,'') <> ''
  Begin
                                Declare @Emp_NSSFNo As NVarchar(200)
                                Declare @Emp_NHIFNo As NVarchar(200)
                                Declare @Emp_PINNo As NVarchar(200)
                               
                                If Exists(Select 1 From tblEmployee_Statutory Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And  Emp_Id = @Emp_Id)
                                Begin
                                               
                                                 
                                                If @S_Compliance_For = 1  -- Kenya
                                                Begin
                                                                                Select @Emp_NSSFNo = Emp_NSSFNo ,@Emp_NHIFNo = Emp_NHIFNo,@Emp_PINNo = Emp_PINNo From tblEmployee_Statutory Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And  Emp_Id = @Emp_Id
                                                                               
                                                                                Update tblEmployee_Statutory Set Emp_NSSFNo = @Emp_NSSFNo,Emp_NHIFNo = @Emp_NHIFNo,Emp_PINNo = @Emp_PINNo, Emp_PFPINNo = @PensionPin,                                              
                                                                                                                                Login_U_Id = @Login_Id , UsysDate = GETDATE()
                                                                                Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id   
                                                End
                                                Else if @S_Compliance_For = 5 --Nigeria
                                                Begin                    
                                                                                Select @Emp_NSSFNo = Emp_NHFNo ,@Emp_NHIFNo = Emp_NHISNo,@Emp_PINNo = Emp_PINNo From tblEmployee_Statutory Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And  Emp_Id = @Emp_Id
                                                                                Update tblEmployee_Statutory Set Emp_NHFNo = @Emp_NSSFNo,Emp_NHISNo = @Emp_NHIFNo,Emp_PINNo = @Emp_PINNo,Emp_PFPINNo =                 @PensionPin,                                   
                                                                                                                Login_U_Id = @Login_Id , UsysDate = GETDATE()
                                                                                Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id   
                                                End       
                                End       
                                Else
                                Begin
                                                If @S_Compliance_For = 1  -- Kenya
                                                Begin
                                                                Insert into tblEmployee_Statutory
                                                                                (TenantId,Cmp_Id,Emp_Id,Emp_NSSFNo,Emp_NHIFNo,Emp_PINNo, Emp_PFPINNo, Login_C_Id,CsysDate)
                                                                Values
                                                                                (@TenantId, @Cmp_Id, @Emp_Id, @Emp_NSSFNo,@Emp_NHIFNo,@Emp_PINNo,@PensionPin,@Login_Id,GETDATE())  
                                                End
                                                Else If @S_Compliance_For = 5  -- Nigeria
                                                Begin
                                                                Insert into tblEmployee_Statutory
                                                                                (TenantId,Cmp_Id,Emp_Id,Emp_NHFNo,Emp_NHISNo,Emp_PINNo,Emp_PFPINNo, Login_C_Id,CsysDate)
                                                                Values
                                                                                (@TenantId, @Cmp_Id, @Emp_Id, @Emp_NSSFNo,@Emp_NHIFNo,@Emp_PINNo,@PensionPin,@Login_Id,GETDATE())
                                                End
                                End                                       
  End
 
                If Exists(Select 1 From tblEmployee_Contribution Where Cmp_Id = @Cmp_Id And TenantId =@TenantId  And Emp_Id = @Emp_Id) 
                Begin 
                                Update tblEmployee_Contribution Set PensionScheme_Id = @Emp_Scheme_Id  Where Cmp_Id = @Cmp_Id And TenantId =@TenantId  And Emp_Id = @Emp_Id                
                End
                               
Set @Update_Flag = 1   

End        
Return 

GO---

If Exists (Select * From dbo.sysobjects where id = object_id(N'[dbo].[EmployeeEffectAfterInsertFromHRMS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                Drop Procedure [EmployeeEffectAfterInsertFromHRMS]

GO---

Create procedure [dbo].[EmployeeEffectAfterInsertFromHRMS]   
@Cmp_Id bigint,
@TenantId bigint ,
@Login_Id bigint ,
@Emp_Id bigint
As       
Begin

                If Not Exists(Select 1 From tblEmployee_Statutory Where Cmp_Id = @Cmp_Id And TenantId =@TenantId  And Emp_Id = @Emp_Id)
                Begin
                                Insert Into tblEmployee_Statutory(TenantId, Cmp_Id, Emp_Id, Tax_Paid_By_Employer, Is_Handicap, Login_C_Id, CSysDate)
                                Values (@TenantId, @Cmp_Id,@Emp_Id,0,0,@Login_Id,getdate())
                End
                If Not Exists(Select 1 From tblEmployee_Housing Where Cmp_Id = @Cmp_Id And TenantId =@TenantId  And Emp_Id = @Emp_Id)
                Begin
                                Insert Into tblEmployee_Housing(TenantId, Cmp_Id, Emp_Id, Allowance_applicable, Login_C_Id, CSysDate)
                                Values (@TenantId, @Cmp_Id,@Emp_Id,0,@Login_Id,getdate())           
                End
                If Not Exists(Select 1 From tblEmployee_Contribution Where Cmp_Id = @Cmp_Id And TenantId =@TenantId  And Emp_Id = @Emp_Id)
                Begin
                                Declare @Currency_Id bigint
                                Select Top 1 @Currency_Id = Currency_Id from tblCurrency Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Is_Default= 1

                                Insert Into tblEmployee_Contribution(TenantId, Cmp_Id, Emp_Id, ProvidentFund, PF_DeductionType, PF_CalculateOn, PF_Employee, PF_Employer,
                                                                                                                                                                                 PF_FC_Employee, PF_FC_Employer, PF_Exchange_Rate, PF_Currency_Id, Pension, Pension_DeductionType,
                                                                                                                                                                                 Pension_CalculateOn, Pension_Employee, Pension_Employer, Pension_FC_Employee, Pension_FC_Employer,
                                                                                                                                                                                 Pension_Exchange_rate, Pension_currency_Id, Owner_OccupiedInterest, Login_C_Id, CSysDate)
                                                               
                                                                                                                                                Values (@TenantId, @Cmp_Id, @Emp_Id, 0, null, null, 0, 0, 0, 0, 1, @Currency_Id,
                                                                                                                                                        0, null, null, 0, 0, 0, 0, 1, @Currency_Id, 0, @Login_Id, getdate())  
                End
                If Not Exists(Select 1 From tblEmployee_Salary Where Cmp_Id = @Cmp_Id And TenantId =@TenantId  And Emp_Id = @Emp_Id)
                Begin                    
                               
                                Declare @PaymentMode bigint                               
                                Select @PaymentMode  = LD.LookUp_Data_Def_Id From tbllookup_Type lt
                                Inner join tbllookup_data ld on lt.Cmp_Id = ld.Cmp_Id And lt.TenantId = LD.TenantId And lt.Lookup_Type_Id = ld.Lookup_Type_Id
                                Where LT.LookUp_Type_Key = 14 And LT.Cmp_Id = @Cmp_ID And LT.TenantId = @TenantId And LookUp_Data_Value ='cash'

                                Insert Into tblEmployee_Salary(TenantId,Cmp_Id,Emp_Id,Use_salary_DailyBasic,Use_Prorated_Salary,Use_DOJ,SalaryGrade_Id,WagesRate_Id,Payment_Mode,Login_C_Id,CSysDate)
                                Values (@TenantId, @Cmp_Id,@Emp_Id,0,0,0,null,null,@PaymentMode,@Login_Id,getdate())              
                End
                If Not Exists(Select 1 From tblEmployee_Insurance_Relief Where Cmp_Id = @Cmp_Id And TenantId =@TenantId  And Emp_Id = @Emp_Id)
                Begin
                                Insert Into tblEmployee_Insurance_Relief(TenantId, Cmp_Id, Emp_Id,Insurance_Relief,Deduct_Relief_From_Salary,Login_C_Id,CSysDate)
                                Values (@TenantId, @Cmp_Id,@Emp_Id,0,0,@Login_Id,getdate())       
                End
                If Not Exists(Select 1 From tblEmployee_Joining_History Where Cmp_Id = @Cmp_Id And TenantId =@TenantId  And Emp_Id = @Emp_Id)
                Begin
                                Declare @DateofJOining datetime
                                Select @DateofJOining = Emp_Join_Date From TblEMployee Where Cmp_Id = @Cmp_Id And TenantId =@TenantId  And Emp_Id = @Emp_Id
                                Insert Into tblEmployee_Joining_History(TenantId, Cmp_Id, Emp_Id,DateOfJoining,Login_C_Id,CSysDate)
                                Values (@TenantId, @Cmp_Id,@Emp_Id,@DateofJOining,@Login_Id,getdate())            
                End
End
Return

GO---

If Exists (Select * From dbo.sysobjects where id = object_id(N'[dbo].[UpdateEmployeeContactDetailsFromHRMS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                Drop Procedure [UpdateEmployeeContactDetailsFromHRMS]

GO---

Create Procedure [dbo].[UpdateEmployeeContactDetailsFromHRMS]
@EmpPayrollNo nvarchar(50),   
@WorkEmail nvarchar(100),
@HomeEmail nvarchar(100),
@WorkExt nvarchar(100),
@WorkNo nvarchar(100),
@MObileNo nvarchar(100),
@Cmp_Id int = 1,
@TenantId int = 1
As       
Begin   

  
Declare @Emp_Payroll_No  nvarchar(200)
Declare @Error nvarchar(400)
Declare @Emp_Id int
Declare @Login_Id int   

Select @Emp_Id  = Emp_Id From tblemployee Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Is_Separate = 0 And Emp_Payroll_No = @EmpPayrollNo
If (isnull(@Emp_Id,0)= 0)
Begin    
                Set @Error  ='Bitplus:Payroll no : ' + @Emp_Payroll_No + ' is not available in Paymaster Database.'
                Raiserror(@Error ,14,2)
                Return
End

Select Top 1 @Login_Id = Login_Id From tblLogin Where Cmp_Id = @Cmp_Id  And TenantId= @TenantId   

If Not Exists(Select 1 from tblLookUp_Type Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And LookUp_Type_Key In (9,8))
Begin
                Raiserror('Bitplus:Set Lookup Data For Email/Phone',14,2);Select @@Error;Return
End

Declare @EmailTypeName nVarchar(100)
Declare @Email_Type bigint
Declare @Email_Detail_Id bigint
Set @EmailTypeName= 'office'

Select @Email_Type = isnull(LookUp_Data_Id ,0)from tblLookUp_Data LD
Inner join tblLookUp_Type LT on LD.Cmp_Id = LT.Cmp_Id  And LD.TenantId = LT.TenantId And LD.Lookup_Type_Id = LT.Lookup_Type_Id
Where LD.Cmp_Id = @Cmp_Id And LD.TenantId = @TenantId  And lower(LD.Lookup_Data_Value) = lower(@EmailTypeName) And LT.LookUp_Type_Key = 9

If isnull(@Email_Type,0)=0
Begin
                Raiserror('Bitplus:Set Lookup Data For Email',14,2);Select @@Error;Return
End


If Exists(SElect 1 from tblEmployee_ContactEmail Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id And Email_Type = @Email_Type)
Begin
                Select @Email_Detail_Id = Email_Detail_Id From tblEmployee_ContactEmail Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id And Email_Type = @Email_Type
               
                Update tblEmployee_ContactEmail  Set Email_Address = @WorkEmail,Login_U_Id= @Login_Id,UsysDate=GETDATE()
    Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id And Email_Type = @Email_Type
               
End
Else
Begin
                Insert into tblEmployee_ContactEmail (TenantId,Cmp_Id,Emp_Id,Email_Type,Email_Address,Login_C_Id,CsysDate)
                Values (@TenantId, @Cmp_Id, @Emp_Id, @Email_Type, @WorkEmail, @Login_Id, GETDATE())
End

Set @EmailTypeName= 'personal'
Select @Email_Type = isnull(LookUp_Data_Id ,0)from tblLookUp_Data LD
Inner join tblLookUp_Type LT on LD.Cmp_Id = LT.Cmp_Id  And LD.TenantId = LT.TenantId And LD.Lookup_Type_Id = LT.Lookup_Type_Id
Where LD.Cmp_Id = @Cmp_Id And LD.TenantId = @TenantId  And lower(LD.Lookup_Data_Value) = lower(@EmailTypeName) And LT.LookUp_Type_Key = 9

If isnull(@Email_Type,0)=0
Begin
                Raiserror('Bitplus:Set Lookup Data For Email',14,2);Select @@Error;Return
End

If(isnull(@HomeEmail,'') <>'')
Begin
                If Exists(SElect 1 from tblEmployee_ContactEmail Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id And Email_Type = @Email_Type)
                Begin
                                Select @Email_Detail_Id = Email_Detail_Id From tblEmployee_ContactEmail Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id And Email_Type = @Email_Type
                               
                                Update tblEmployee_ContactEmail  Set Email_Address = @HomeEmail,Login_U_Id= @Login_Id,UsysDate=GETDATE()
                                Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id And Email_Type = @Email_Type
                End
                Else
                Begin
                                Insert into tblEmployee_ContactEmail (TenantId,Cmp_Id,Emp_Id,Email_Type,Email_Address,Login_C_Id,CsysDate)
                                Values (@TenantId, @Cmp_Id, @Emp_Id, @Email_Type, @HomeEmail, @Login_Id, GETDATE())
                ENd
End
Else
Begin
                Select @Email_Detail_Id = Email_Detail_Id From tblEmployee_ContactEmail Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id And Email_Type = @Email_Type
                Delete From tblEmployee_ContactEmail Where  Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id And Email_Type = @Email_Type And Email_Detail_Id = @Email_Detail_Id
End
----
Declare @PhoneTypeName nVarchar(100)
Declare @Phone_Type bigint
Declare @Phone_Detail_Id bigint
Set @PhoneTypeName= 'Office Phone'

Select @Phone_Type = isnull(LookUp_Data_Id ,0)from tblLookUp_Data LD
Inner join tblLookUp_Type LT on LD.Cmp_Id = LT.Cmp_Id  And LD.TenantId = LT.TenantId And LD.Lookup_Type_Id = LT.Lookup_Type_Id
Where LD.Cmp_Id = @Cmp_Id And LD.TenantId = @TenantId  And lower(LD.Lookup_Data_Value) = lower(@PhoneTypeName) And LT.LookUp_Type_Key = 8

If isnull(@Phone_Type,0)=0
Begin
                Raiserror('Bitplus:Set Lookup Data For Phone',14,2);Select @@Error;Return
End

If(isnull(@WorkNo,'')<>'')
Begin
                If Exists(SElect 1 from tblEmployee_ContactPhone Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id And Phone_Type = @Phone_Type)
                Begin
                                Select @Phone_Detail_Id = Phone_Detail_Id From tblEmployee_ContactPhone Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id And Phone_Type = @Phone_Type
                               
                                Update tblEmployee_ContactPhone  Set  Phone_No = @WorkNo, Phone_Ext =@WorkExt ,Login_U_Id= @Login_Id,UsysDate=GETDATE()
                                Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id And Phone_Type = @Phone_Type
                End
                Else
                Begin
                                Insert into tblEmployee_ContactPhone (TenantId,Cmp_Id,Emp_Id,Phone_type,Phone_No,Phone_Ext,Login_C_Id,CsysDate)
                                Values (@TenantId, @Cmp_Id, @Emp_Id, @Phone_type,@WorkNo,@WorkExt,  @Login_Id, GETDATE())
                End
End
Else
Begin
                Select @Phone_Detail_Id = Phone_Detail_Id From tblEmployee_ContactPhone Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id And Phone_type = @Phone_type
                Delete From tblEmployee_ContactPhone Where  Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id And Phone_type = @Phone_type And Phone_Detail_Id = @Phone_Detail_Id
End

Set @PhoneTypeName= 'Mobile'

Select @Phone_Type = isnull(LookUp_Data_Id ,0)from tblLookUp_Data LD
Inner join tblLookUp_Type LT on LD.Cmp_Id = LT.Cmp_Id  And LD.TenantId = LT.TenantId And LD.Lookup_Type_Id = LT.Lookup_Type_Id
Where LD.Cmp_Id = @Cmp_Id And LD.TenantId = @TenantId  And lower(LD.Lookup_Data_Value) = lower(@PhoneTypeName) And LT.LookUp_Type_Key = 8

If isnull(@Phone_Type,0)=0
Begin
                Raiserror('Bitplus:Set Lookup Data For Phone',14,2);Select @@Error;Return
End

If(isnull(@MobileNo,'')<>'')
Begin
                If Exists(SElect 1 from tblEmployee_ContactPhone Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id And Phone_Type = @Phone_Type)
                Begin
                                Select @Phone_Detail_Id = Phone_Detail_Id From tblEmployee_ContactPhone Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id And Phone_Type = @Phone_Type
                               
                                Update tblEmployee_ContactPhone  Set  Phone_No = @MobileNo, Login_U_Id= @Login_Id,UsysDate=GETDATE()
                                Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id And Phone_Type = @Phone_Type
                End
                Else
                Begin
                                Insert into tblEmployee_ContactPhone (TenantId,Cmp_Id,Emp_Id,Phone_type,Phone_No,Phone_Ext,Login_C_Id,CsysDate)
                                Values (@TenantId, @Cmp_Id, @Emp_Id, @Phone_type,@MobileNo,null,  @Login_Id, GETDATE())
                End
End
Else
Begin
                Select @Phone_Detail_Id = Phone_Detail_Id From tblEmployee_ContactPhone Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id And Phone_type = @Phone_Type
                Delete From tblEmployee_ContactPhone Where  Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id And Phone_type = @Phone_Type And Phone_Detail_Id = @Phone_Detail_Id
End

End
Return

GO---

If Exists (Select * From dbo.sysobjects where id = object_id(N'[dbo].[UpdateEmployeeBankDetailsFromHRMS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                Drop Procedure [UpdateEmployeeBankDetailsFromHRMS]

GO---

Create PROCEDURE [dbo].[UpdateEmployeeBankDetailsFromHRMS]

@EmpPayrollNo Nvarchar(200),   
@Bank_Code nvarchar(200),
@Bank_Branch_Code nvarchar(200),
@Account_No nvarchar(200),
@Account_NoOld nvarchar(200),

@Account_Type nvarchar(100),
@Nett_Pay_Share Decimal(18,6),
@Cmp_Id int ,
@TenantId int

As

Begin   
    
Declare @Emp_Id int
Declare @Login_Id int   

Declare @Lookup_Type_Id As Int
Declare @LookUp_Data_Id As Int

Declare @Bank_Id As Int
Declare @Bank_Branch_Id As Int

Declare @Bank_Detail_Id As Int
Declare @TotalNett_Pay_Share Decimal(18,6)
Declare @Error nvarchar(400)

Select Top 1 @Login_Id = Login_Id From tblLogin Where Cmp_Id = @Cmp_Id  And TenantId= @TenantId
Select @Emp_Id  = Emp_Id From tblemployee Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Is_Separate = 0 And Emp_Payroll_No = @EmpPayrollNo

Select @Lookup_Type_Id = Lookup_Type_Id from tblLookUp_Type Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And LookUp_Type_Key = 5
Select @Bank_Id = Bank_Id From tblBank Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Bank_Code = @Bank_Code
Select @Bank_Branch_Id = Bank_Branch_Id From tblBank_Branch Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Bank_Branch_Code = @Bank_Branch_Code

If ISNULL(@Emp_Id ,0) = 0
Begin    
                Set @Error  ='Bitplus:Payroll no : ' + Isnull(@EmpPayrollNo,'') + ' is not available in Paymaster Database.'
                Raiserror(@Error ,14,2)
                Select @@ERROR
                Return -1
End
If ISNULL(@Bank_Id ,0) = 0
Begin    
                Set @Error  ='Bitplus:Bank not available in Paymaster Database.'
                Raiserror(@Error ,14,2)
                Select @@ERROR
                Return -1
End
If ISNULL(@Bank_Branch_Id ,0) = 0
Begin    
                Set @Error  ='Bitplus:Bank Branch not available in Paymaster Database.'
                Raiserror(@Error ,14,2)
                Select @@ERROR
                Return -1
End
If ISNULL(@Lookup_Type_Id,0) = 0
Begin
                                Raiserror('Bitplus:Set Lookup Data For Account Type',14,2);Select @@Error;Return -1
End

--Get Account Type
Select @LookUp_Data_Id = LookUp_Data_Id from tblLookUp_Data Where Cmp_Id = @Cmp_Id And TenantId = @TenantId  And Lookup_Type_Id = @Lookup_Type_Id And LookUp_Data_Value = @Account_Type

If ISNULL(@LookUp_Data_Id,0) = 0
Begin
                                Raiserror('Bitplus:Account Type Not Found in Paymaster Database',14,2);Select @@Error;Return -1
End

Select @Bank_Detail_Id =  Bank_Detail_Id From tblEmployee_Bank Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id And Account_No = @Account_NoOld
Select @TotalNett_Pay_Share = SUM(@Nett_Pay_Share) From tblEmployee_Bank  Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id And Bank_Detail_Id <> Isnull(@Bank_Detail_Id,0)
Set @TotalNett_Pay_Share = @Nett_Pay_Share + Isnull(@TotalNett_Pay_Share,0)

If @TotalNett_Pay_Share > 100
                Begin
                                Raiserror('Bitplus:Total Net Pay Share must not exceed 100 Percent.',14,2);Select @@Error;Return -1
                End

If Isnull(@Bank_Detail_Id,0) <> 0
Begin
               
                Update tblEmployee_Bank  Set Bank_Id = @Bank_Id,Bank_Branch_Id = @Bank_Branch_Id,Account_No = @Account_No,Account_Type = @LookUp_Data_Id,
                                Nett_Pay_Share = @Nett_Pay_Share ,Login_U_Id= @Login_Id , UsysDate = GETDATE()
    Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id And Account_No = @Account_NoOld
End
Else
Begin
                Insert into tblEmployee_Bank
    (TenantId,Cmp_Id,Emp_Id,Account_No,Account_Type,Login_C_Id,CsysDate,Bank_Id,Bank_Branch_Id,Nett_Pay_Share)
                Values
                (@TenantId, @Cmp_Id, @Emp_Id, @Account_No, @LookUp_Data_Id, @Login_Id, GETDATE(),@Bank_Id,@Bank_Branch_Id,@Nett_Pay_Share)
End
End
Return

GO---

If Exists (Select * From dbo.sysobjects where id = object_id(N'[dbo].[UpdateEmployeeAddressDetailsFromHRMS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                Drop Procedure [UpdateEmployeeAddressDetailsFromHRMS]

GO---

Create PROCEDURE [dbo].[UpdateEmployeeAddressDetailsFromHRMS]
@EmpPayrollNo Nvarchar(200),   
@AddressType As Tinyint,
@Emp_Address As Nvarchar(600),
@City_Code Nvarchar(200),
@State_Code Nvarchar(200),
@Country_Code Nvarchar(200),
@Emp_POBox Nvarchar(20),
@Cmp_Id int ,
@TenantId int
As
Begin       
Declare @Emp_Id int
Declare @Login_Id int   

Declare @Lookup_Type_Id As Int
Declare @LookUp_Data_Id As Int

Declare @Country_Id As Int
Declare @State_Id As Int
Declare @City_Id As Int

Declare @Bank_Detail_Id As Int
Declare @TotalNett_Pay_Share Decimal(18,6)
Declare @Error nvarchar(400)

Select Top 1 @Login_Id = Login_Id From tblLogin Where Cmp_Id = @Cmp_Id  And TenantId= @TenantId
Select @Emp_Id  = Emp_Id From tblemployee Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Is_Separate = 0 And Emp_Payroll_No = @EmpPayrollNo

Select @Lookup_Type_Id = Lookup_Type_Id from tblLookUp_Type Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And LookUp_Type_Key = 10
Select @Country_Id = Country_Id From tblCountry Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Country_Code = @Country_Code
Select @State_Id = State_Id From tblState Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Country_Id  = @Country_Id And State_Code = @State_Code
Select @City_Id = City_Id From tblCity Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And State_Id = @State_Id And City_Code = @City_Code

If ISNULL(@Emp_Id ,0) = 0
Begin    
                Set @Error  ='Bitplus:Payroll no : ' + Isnull(@EmpPayrollNo,'') + ' is not available in Paymaster Database.'
                Raiserror(@Error ,14,2)
                Select @@ERROR
                Return -1
End

If ISNULL(@Country_Id ,0) = 0
Begin    
                Set @Error  ='Bitplus:Country not available in Paymaster Database.'
                Raiserror(@Error ,14,2)
                Select @@ERROR
                Return -1
End
If ISNULL(@State_Id ,0) = 0
Begin    
                Set @Error  ='Bitplus:State not available in Paymaster Database.'
                Raiserror(@Error ,14,2)
                Select @@ERROR
                Return -1
End
If ISNULL(@City_Id ,0) = 0
Begin    
                Set @Error  ='Bitplus:City not available in Paymaster Database.'
                Raiserror(@Error ,14,2)
                Select @@ERROR
                Return -1
End
If ISNULL(@Lookup_Type_Id,0) = 0
Begin
                                Raiserror('Bitplus:Set Lookup Data For Address Type',14,2);Select @@Error;Return -1
End
--Get Account Type
If @AddressType = 1
                Select @LookUp_Data_Id = LookUp_Data_Id from tblLookUp_Data Where Cmp_Id = @Cmp_Id And TenantId = @TenantId  And Lookup_Type_Id = @Lookup_Type_Id And LookUp_Data_Value = 'Permanent'
Else if @AddressType = 2
                Select @LookUp_Data_Id = LookUp_Data_Id from tblLookUp_Data Where Cmp_Id = @Cmp_Id And TenantId = @TenantId  And Lookup_Type_Id = @Lookup_Type_Id And LookUp_Data_Value = 'Local'

If ISNULL(@LookUp_Data_Id,0) = 0
Begin
                                Raiserror('Bitplus:Address Type Not Found in Paymaster Database',14,2);Select @@Error;Return -1
End

If Exists (Select 1 From tblEmployee_Address Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And  Emp_Id = @Emp_Id And Address_Type = @LookUp_Data_Id)
Begin    
                Update tblEmployee_Address  Set Emp_Address = @Emp_Address,Emp_City_Id = @City_Id,Emp_POBox = @Emp_POBox, Emp_State_Id = @State_Id, Emp_Country_Id = @Country_Id,
                                Login_U_Id= @Login_Id , UsysDate = GETDATE()
    Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id And Address_Type = @LookUp_Data_Id
End
Else
Begin    
                Insert into tblEmployee_Address
                (TenantId,Cmp_Id,Emp_Id,Address_Type,Emp_Address,Emp_City_Id,Emp_POBox, Emp_State_Id, Emp_Country_Id, Login_C_Id,CsysDate)
                Values
                (@TenantId, @Cmp_Id, @Emp_Id, @LookUp_Data_Id,@Emp_Address,@City_Id,@Emp_POBox,@State_Id,@Country_Id,@Login_Id,GETDATE())
End
End
Return

GO---

If Exists (Select * From dbo.sysobjects where id = object_id(N'[dbo].[UpdateEmployeeStatutoryDetailsFromHRMS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                Drop Procedure [UpdateEmployeeStatutoryDetailsFromHRMS]

GO---

Create PROCEDURE [dbo].[UpdateEmployeeStatutoryDetailsFromHRMS]
@EmpPayrollNo Nvarchar(200),   
@Emp_NSSFNo As NVarchar(200),
@Emp_NHIFNo As NVarchar(200),
@Emp_PINNo As NVarchar(200),
@Cmp_Id int ,
@TenantId int

As

Begin   


Declare @Emp_Id int
Declare @Login_Id int  
Declare @S_Compliance_For As TinyInt

Declare @Error nvarchar(400)

Select Top 1 @Login_Id = Login_Id From tblLogin Where Cmp_Id = @Cmp_Id  And TenantId= @TenantId
Select @Emp_Id  = Emp_Id From tblemployee Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Is_Separate = 0 And Emp_Payroll_No = @EmpPayrollNo
Select @S_Compliance_For = S_Compliance_For From tblCompany Where Cmp_Id = @Cmp_Id And TenantId = @TenantId


If ISNULL(@Emp_Id ,0) = 0
Begin    
                Set @Error  ='Bitplus:Payroll no : ' + Isnull(@EmpPayrollNo,'') + ' is not available in Paymaster Database.'
                Raiserror(@Error ,14,2)
                Select @@ERROR
                Return -1
End


If Exists (Select 1 From tblEmployee_Statutory Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And  Emp_Id = @Emp_Id)
Begin
                If @S_Compliance_For = 1  -- Kenya
                                Begin
                                                Update tblEmployee_Statutory Set Emp_NSSFNo = @Emp_NSSFNo,Emp_NHIFNo = @Emp_NHIFNo,Emp_PINNo = @Emp_PINNo,                                                 
                                                                Login_U_Id = @Login_Id , UsysDate = GETDATE()
                                                Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id   
                                End
                Else if @S_Compliance_For = 5 --Nigeria
                                Begin                    
                                Update tblEmployee_Statutory Set Emp_NHFNo = @Emp_NSSFNo,Emp_NHISNo = @Emp_NHIFNo,Emp_PINNo = @Emp_PINNo,                                                 
                                                                Login_U_Id = @Login_Id , UsysDate = GETDATE()
                                                Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Id = @Emp_Id   
                                End       
End
Else
Begin    
                If @S_Compliance_For = 1  -- Kenya
                                Begin
                                                Insert into tblEmployee_Statutory
                                                                (TenantId,Cmp_Id,Emp_Id,Emp_NSSFNo,Emp_NHIFNo,Emp_PINNo, Login_C_Id,CsysDate)
                                                Values
                                                                (@TenantId, @Cmp_Id, @Emp_Id, @Emp_NSSFNo,@Emp_NHIFNo,@Emp_PINNo,@Login_Id,GETDATE())               
                                End
                Else If @S_Compliance_For = 5  -- Nigeria
                                Begin
                                                Insert into tblEmployee_Statutory
                                                                (TenantId,Cmp_Id,Emp_Id,Emp_NHFNo,Emp_NHISNo,Emp_PINNo, Login_C_Id,CsysDate)
                                                Values
                                                                (@TenantId, @Cmp_Id, @Emp_Id, @Emp_NSSFNo,@Emp_NHIFNo,@Emp_PINNo,@Login_Id,GETDATE())
                                End
End
End
Return

GO---

If Exists (Select * From dbo.sysobjects where id = object_id(N'[dbo].[PrcEmployeeRejoinSeparate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                Drop Procedure [PrcEmployeeRejoinSeparate]

GO---


Create procedure [dbo].[PrcEmployeeRejoinSeparate]
@Cmp_Id bigint,   
@TenantId bigint ,   
@EmpPayrollNo nvarchar(200),   
@Type tinyint,
@SeparateRejoinDate datetime,
@Remarks as nvarchar(400),
@Flag as int = 0 output
As           
Begin   

Declare @Emp_Id bigint
Declare @EmpType as smallint
Declare @Is_Separate as tinyint
Declare @Emp_Joining_History_Id as bigint
Declare @BlackListDate  datetime

Select @Emp_Id = isnull(Emp_Id,0),@Is_Separate = isnull(Is_Separate,0),@EmpType = Employment_Type From tblEmployee Where Cmp_ID = @Cmp_Id And TenantId = @TenantId And Emp_Payroll_No = @EmpPayrollNo

Declare @Login_Id int
Select Top 1  @Login_Id = Login_Id From tblLogin Where Cmp_Id = @Cmp_Id And TenantId = @TenantId

If @Emp_Id = 0
Begin
                Raiserror('Bitplus:Employee Payroll No is not Exists',14,2)         
    Select @@Error         
    Return -1   
End

If @Login_Id = 0
Begin
                Raiserror('Bitplus:Company name is not found, Please check Paymaster comapny Setting',14,2)         
    Select @@Error         
    Return -1   
End

If @Type = 1 ---- Rejoin
Begin
                IF Exists (Select 1 From tblEmployee Where Cmp_Id = @Cmp_Id And TenantId = @TenantId  And EMp_Id = @Emp_Id And Is_Separate = 0)
                Begin
                                Raiserror('Bitplus:Employee Already active',14,2)         
                                Select @@Error         
                                Return -1
                End
                IF Exists (Select 1 From tblEmployee Where Cmp_Id = @Cmp_Id And TenantId = @TenantId  And EMp_Id = @Emp_Id And IsOnHold = 1)
                Begin
                                Select @BlackListDate = HoldDate From tblEmployee Where Cmp_Id = @Cmp_Id And TenantId = @TenantId  And EMp_Id = @Emp_Id
                                If @BlackListDate is not null And   @SeparateRejoinDate < @BlackListDate
                                Begin
                                                Raiserror('Bitplus:Employee Joining Date Must Be Less Than From Hold Date.',14,2)         
                                                Select @@Error         
                                                Return -1
                                End
                End
                IF Exists (Select 1 From tblEmployee Where Cmp_Id = @Cmp_Id And TenantId = @TenantId  And EMp_Id = @Emp_Id And Emp_IsBlackList = 1)
                Begin                    
                                SElect @BlackListDate = Emp_BlackListDate From tblEmployee Where Cmp_Id = @Cmp_Id And TenantId = @TenantId  And EMp_Id = @Emp_Id
                                If @BlackListDate is not null And   @BlackListDate < @SeparateRejoinDate
                                Begin
                                                Raiserror('Bitplus:Employee Joining Date Must Be Less Than From Black List Date.',14,2)         
                                                Select @@Error         
                                                Return -1
                                End
                End
                If Exists(Select 1 From tblEmployee_LeavingDetails Where Cmp_ID = @Cmp_Id And TenantId = @TenantId  And EMp_Id = @Emp_Id And  LeavingDate >  @SeparateRejoinDate)
                Begin    
                                Raiserror('Bitplus:Joining date is overlapped with existing in Paymaster',14,2)         
                                Select @@Error          
                                Return -1
                End
                If Exists(Select Top 1 Emp_Joining_History_Id  From tblEmployee_Joining_History Where Cmp_ID = @Cmp_Id And TenantId = @TenantId  And Emp_Id = @Emp_Id
                                                                And LeavingId is not null               Order by Emp_Joining_History_Id desc)
                Begin                    
                                                Insert Into tblEmployee_Joining_History(TenantId, Cmp_Id, Emp_Id, DateOfJoining, Joining_Remarks, Login_C_Id, CSysDate)   
                                                Values (@TenantId, @Cmp_Id, @Emp_Id, @SeparateRejoinDate, @Remarks, @Login_Id,GetDate())

                                                Update tblEmployee  Set Is_Separate = 0, Emp_Join_Date = @SeparateRejoinDate  Where Cmp_ID = @Cmp_Id And TenantId = @TenantId  And EMp_Id = @Emp_Id
                End
End
Else    -- 2 ---- Separate
Begin
               
                IF Exists (Select 1 From tblEmployee Where Cmp_Id = @Cmp_Id And TenantId = @TenantId  And EMp_Id = @Emp_Id And Is_Separate = 1)
                Begin
                                Raiserror('Bitplus:Employee Already Separated ',14,2)         
                                Select @@Error         
                                Return -1
                End
                IF Exists (Select 1 From tblEmployee Where Cmp_Id = @Cmp_Id And TenantId = @TenantId  And EMp_Id = @Emp_Id And IsOnHold = 1)
                Begin
                                Raiserror('Bitplus:OnHold Employee Can not seperate From paymaster',14,2)         
                                Select @@Error         
                                Return -1
                End
                IF Exists (Select 1 From tblEmployee Where Cmp_Id = @Cmp_Id And TenantId = @TenantId  And EMp_Id = @Emp_Id And Emp_IsBlackList = 1)
                Begin                    
                                SElect @BlackListDate = Emp_BlackListDate From tblEmployee Where Cmp_Id = @Cmp_Id And TenantId = @TenantId  And EMp_Id = @Emp_Id
                                If @BlackListDate is not null And  @SeparateRejoinDate < @BlackListDate
                                Begin
                                                Raiserror('Bitplus:Employee Separate Date should be greater than Black list Date in Paymaster',14,2)         
                                                Select @@Error         
                                                Return -1
                                End
                End
                If Exists(Select 1 From tblEmployee_Joining_History Where Cmp_ID = @Cmp_Id And TenantId = @TenantId  And EMp_Id = @Emp_Id And  DateOfJoining >  @SeparateRejoinDate)
                Begin    
                                Raiserror('Bitplus:Separation date is overlapped with existing in Paymaster',14,2)         
                                Select @@Error          
                                Return -1
                End
               
                if Exists(Select 1 From Employee_PaySlip EP Inner join
                            tblPeriod P on p.Cmp_Id = EP.Cmp_Id And p.TenantId = EP.TenantId ANd P.Period_Id = EP.Period_Id
                                                                Where P.From_Date > @SeparateRejoinDate And P.To_Date > @SeparateRejoinDate And P.EmpType = @EmpType
                                                                And EP.Cmp_Id = @Cmp_Id ANd EP.TenantId = @TenantId And EP.Emp_Id = @Emp_Id)
                Begin
                                Raiserror('Bitplus:PaySlip Exists For Future Period Cannot Separate Employee in Paymaster',14,2)         
                                Select @@Error         
                                Return -1
                End
               
                Declare @SettingValue nvarchar(100)
                Declare @IsLoanRestrict bit
                Select @SettingValue  = Isnull(Setting_Value,'False') from tblGeneralSetting Where Cmp_Id = @Cmp_Id And  TenantId = @TenantId  And Setting_Key = 101
                Set @IsLoanRestrict = Cast(@SettingValue as bit)

                If @IsLoanRestrict  = 1
                Begin
                                If Exists(Select 1 From tblLoanVoucher LV Inner Join tblLoanVoucherDetail LVD On LV.Cmp_Id = LVD.Cmp_Id And LV.TenantId = LVD.TenantId
                                                                  And LV.Loan_Voucher_Id = LVD.Loan_Voucher_Id
                                                                  Where LV.Cmp_Id = @Cmp_Id And LV.TenantId = @TenantId And LV.Emp_ID = @Emp_Id And LVD.Installment_Date >= @SeparateRejoinDate
                                                                  And LVD.Year_Id is null And LVD.Month_Id is null And LVD.Period_Id is null)
                                Begin
                                                Raiserror('Bitplus:Loan Dues Are Pending Employee Cannot Be Separated in Paymaster',14,2)         
                                                Select @@Error         
                                                Return -1
                                End                                       
                End

                If Exists(Select Top 1 Emp_Joining_History_Id  From tblEmployee_Joining_History Where Cmp_ID = @Cmp_Id And TenantId = @TenantId  And EMp_Id = @Emp_Id
                                                                And LeavingId is null       Order by Emp_Joining_History_Id desc)
                Begin
                               
                                Select Top 1 @Emp_Joining_History_Id = isnull(Emp_Joining_History_Id,0)  From tblEmployee_Joining_History Where Cmp_ID = @Cmp_Id And TenantId = @TenantId  And EMp_Id = @Emp_Id
                                                                And LeavingId is null       Order by Emp_Joining_History_Id desc

                                If(@Emp_Joining_History_Id > 0)
                                Begin                                    
                                                Declare @LeavingId bigint

                                                Update tblEmployee  Set Is_Separate = 1  Where Cmp_ID = @Cmp_Id And TenantId = @TenantId  And EMp_Id = @Emp_Id

                                                Set @Remarks = substring(@Remarks,1,200)
                                               
                                                Insert into tblEmployee_LeavingDetails (LeavingDate,Remarks,Emp_Id,Cmp_Id,TenantId,Login_C_Id,CSysDate)
                                                values (@SeparateRejoinDate,@Remarks,@Emp_Id,@Cmp_Id,@TenantId,@Login_Id,Getdate())
                                                                                               
                                                Select @LeavingId  = Scope_Identity()

                                                Update tblEmployee_Joining_History Set LeavingId = @LeavingId , Login_U_Id = @Login_Id, USysDate = GetDate()

                                                Where Cmp_Id = @Cmp_Id And TenantId = @TenantId And Emp_Joining_History_Id = @Emp_Joining_History_Id                                         
                                End

                End
End

Set @Flag = 1

End


No comments:

Post a Comment

Kindly Comment and Thanks in Advance for Commenting

SupportCertify Updates

Recent Posts Widget