Support Certify: Database

SQL Server 2008 setup may fail with the following error even though you are running the setup as administrator. You might get these kind of errors in Detail.txt after you cleaned up an failed setup (from Add/Remove Programs) and try to run the setup again.

Error 1402. Could not open key: UNKNOWN\Components\493032C95B52CBD448DD2B5A52C50E9A\3EC761FD7E06AE4499CE52705CF173EA.

System error 5. Verify that you have sufficient access to that key, or contact your support personnel.

Action ended 20:37:24: InstallFinalize. Return value 3.

Note: The above GUID may be different in each case

When you search for the GUID that you see in the error in Registry, it maps to the following key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Components\<GUID>

Though you might be running the setup as Administrator you may not be able to access the registry key and it sub keys

Even trying to delete the registry key might fail with the access denied errors

In our case the GUID in the error was pointing to MSXML which was already removed from Add/Remove Programs

There are multiple ways to approach this:

Close 1

· Download and install psexec utility from the following link

http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx

· Open a command prompt (on Vista/Windows 7/2008) using 'run as administrator' option, for Windows (2000 /XP/2003) Hold down the shift key and right click on command prompt and choose run as.

· In the command prompt Navigate to the folder where PSEXEC is installed.

· Execute the following from the command prompt.

§ psexec -i -s cmd.exe

· This will invoke another command prompt running under ‘Local System’

· Using this command prompt open Registry Editor

Command prompt> regedit

· In registry editor navigate to the following hive

HKLM\Software\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Components\

· Add the user account used to run the installation under permissions for the above hive and grant ‘Full control’ for that user.

o Click on Advanced

o Under the Advanced security settings for Components choose the user and check the “Replace all existing inheritable permissions…………” check box as shown below and click OK

· Re-run the installation.

Close 2

As a workaround for the problem you can run the following command at the command prompt to reset the permissions in registry rather than rebuilding the box

SECEDIT /CONFIGURE /CFG %WINDIR%\INF\DEFLTBASE.INF /DB DEFLTBASE.SDB /VERBOSE /AREAS REGKEYS

Close 3

Another possible solution is for the ‘Administrator’ account to take ownership of HKEY_LOCAL_MACHINE registry hive. Owner can be found under Permissions->Advanced

[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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Set @Flag = 1

End

Pages

Support Search

[Closed]SQL Database Optmization Script

[Close]Query for creating fresh leave module that has no leave transactions in HR Master

[Closed]SQL Server 2008 Setup fails to Install with Error 1402

[Closed]Paymaster Cloud and HR Master intergration Script

SupportCertify Updates

SupportCertify Latest

[Closed]How to Change Tax band in Paymaster 2020

Quick Links

Comments

Old Posts

Support Certfieds

Most Visited Post

Content

Support Certify

Contact Us