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