[Closed]Paymaster and HR Master integration Script

   --exec UpdateEmployeeDetailsFromHRMS '1430',''

create procedure [dbo].[UpdateEmployeeDetailsFromHRMS] 

@EmpPayrollNo varchar(50), 

@HRMS_DataBaseName varchar(50), 

@Update_Flag int = 0 output 

as 

 

begin 

 

Declare @Sqlstr Varchar(Max) 

Declare @LinkServer as varchar(100)   

 

Declare @Designation_Name VarChar(100) 

Declare @Department_Name VarChar(100) 

Declare @Branch_Name VarChar(100) 

Declare @Category_Name VarChar(100) 

Declare @Category_Maximum_Employee as int 

Declare @Location_Name VarChar(100) 

Declare @Location_Code VarChar(100) 

Declare @Location_Address VarChar(100) 

Declare @Location_Phone_No VarChar(100) 

Declare @Location_Fax_No VarChar(100) 

Declare @Bank_Name VarChar(100) 

Declare @Bank_Branch_Name VarChar(100) 

Declare @DepartmentID Int 

 

Declare @Bank_No VarChar(100) 

Declare @Bank_Branch_No VarChar(100) 

 

declare @Next_Of_Kin varchar(100) 

 

Declare @EmpCode int 

declare @Emp_Desig_Id int 

declare @Emp_Dept_Id int 

declare @Emp_Branch_Id int 

declare @Emp_Catgegory_Id int 

declare @employmenttype int 

declare @Bank_Id int 

declare @Bank_Branch_Id int 

declare @HRMSCocode int 

declare @Cocode int 

declare @Emp_Weekly_OffType int 

declare @Emp_Weekly_Off int 

declare @Emp_ID int 

 

declare @Emp_Gender VarChar(50) 

declare @Emp_BloodGroup VarChar(50) 

declare @Emp_NationalIDCountry VarChar(50) 

 

Declare @strEmp_ID as varchar(10) 

 

set @Update_Flag = 0 

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

 

 

-- Paymaster CoCode From HRMS database DT:14-Feb-2014 

--set @Cocode = 3 -- Specify the CoCode for the Payroll Database where this Procedure will be Created 

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

exec ('select  P.PayMaster_CoCode INTO ##TempPaymaster from ' + @LinkServer + 'tblEmployee as E 

Inner join ' + @LinkServer + 'tblPayMaster_Company As P  on P.Paymaster_Cmp_Id = E.Paymaster_Cmp_Id 

where E.Emp_Payroll_No = ''' + @EmpPayrollNo + '''') 

select @Cocode = PayMaster_CoCode from ##TempPaymaster 

print @Cocode 

DRop table ##TempPaymaster  

-- 

 

 

 

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

exec ('select Top 1 A.Cmp_ID,A.Emp_ID, A.Emp_Payroll_No, a.Emp_First_Name, a.Emp_Middle_Name, a.Emp_Last_Name,a.Emp_Gender,   

 a.Emp_Join_Date,a.Emp_Birth_Date,a.Employment_Type,a.Emp_MaritalStatus_Id,a.Emp_Children,K.Emp_Address ,L.Emp_WorkPhone, 

 M.Emp_P_Address,L.Emp_MobileNo,L.Emp_WorkEmail,Q.Emp_PINNo,Q.Emp_NSSFNo,Q.Emp_NHIFNo,Q.emp_Nationalityid,N.Height,N.weight,N.Blood_Group, 

 O.College_Name,P.Religion_Name,G.Account_No,(R.First_Name + ' + ' ' +  ' + R.Last_Name) as Next_Of_Kin, 

    B.Desig_Name,C.Dept_Name,C.Dept_ID,D.Branch_Name, e.Category_Name ,e.Maximum_Employee as Category_Maximum_Employee, 

 F.Location_Name,F.Location_Code,F.[Address] as Location_Address,F.Phone_No as Location_Phone_No,F.Fax_No as Location_Fax_No, 

 H.Bank_Name,I.Bank_Branch_Name,H.Bank_No,I.Bank_Branch_No,J.Nationality_Name, A.Emp_WeekOff_Type   

 into ##TempEmployee_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 + 'tblBranch AS D ON (A.Branch_Id=D.Branch_Id)  

 LEFT JOIN ' + @LinkServer + 'tblCategory AS E ON (A.Category_Id=E.Category_Id)  

 LEFT JOIN ' + @LinkServer + 'tblLocation AS F ON (A.Emp_Location_Id=F.Location_Id)  

 LEFT JOIN ' + @LinkServer + 'tblEmployee_Bank AS G ON (A.Emp_Id=G.Emp_Id)  

 left join ' + @LinkServer + 'tblBank as H on (G.Bank_Id=H.Bank_Id)  

 left join ' + @LinkServer + 'TblBank_Branch I on (G.BankBranch_Id=I.BankBranch_Id)  

 left join ' + @LinkServer + 'tblNationality J on (A.Emp_Nationality_Id=J.Nationality_Id)  

 left join ' + @LinkServer + 'tblEmployee_Address K on (a.Emp_Id=K.Emp_Id and a.Cmp_Id=K.Cmp_Id ) 

 left join ' + @LinkServer + 'tblEmployee_Contact L on ( a.Emp_Id=L.Emp_Id and a.Cmp_Id=L.cmp_id ) 

 left join ' + @LinkServer + 'tblEmployee_P_Address M on (a.Emp_Id=M.Emp_Id and a.Cmp_Id=M.Cmp_Id ) 

 left join ' + @LinkServer + 'tblEmployee_Medical N on ( a.Emp_Id=N.Emp_Id and a.Cmp_Id=N.cmp_id ) 

 left join ' + @LinkServer + 'tblemployee_Degree O on (a.Emp_Id=O.Emp_Id and a.Cmp_Id=O.Cmp_Id ) 

 left join ' + @LinkServer + 'tblReligion P on (a.Emp_Religion_Id=P.Religion_Id and a.Cmp_Id=P.Cmp_Id) 

 left join ' + @LinkServer + 'tblEmployee_Statutory Q on ( a.Emp_Id=Q.Emp_Id and a.Cmp_Id=Q.cmp_id ) 

 left join ' + @LinkServer + 'tblEmployee_Family R on (a.Emp_Id=R.Emp_Id and a.Cmp_Id=R.cmp_id and R.Next_Of_Kin=1 )  

 WHERE A.Emp_Payroll_No= '''+ @EmpPayrollNo + '''')  

  

    

 --left join (SELECT Emp_Id,Cmp_Id, max(Emp_Weekoff) as Emp_Weekoff FROM ' + @LinkServer + 'tblEmployee_Weekoff  

--      group by Emp_Id,Cmp_Id) as S on (a.Emp_Id=S.Emp_Id and a.Cmp_Id=S.cmp_id)          

   

  

select  @HRMSCocode=Cmp_Id, @Emp_ID = Emp_ID, @Designation_Name =Desig_Name,@Department_Name=Dept_Name,@DepartmentID = Dept_ID, @Branch_Name=Branch_Name,  

@Category_Name=Category_Name, @Category_Maximum_Employee = Category_Maximum_Employee, 

@Location_Name=Location_Name,@Location_Code=Location_Code,@Location_Address=Location_Address, @Location_Phone_No=Location_Phone_No,@Location_Fax_No=Location_Fax_No, 

@Bank_Name=Bank_Name,@Bank_Branch_Name=Bank_Branch_Name,@Bank_No=Bank_No,@Bank_Branch_No = Bank_Branch_No, 

@Emp_NationalIDCountry=Nationality_Name, @Emp_Weekly_OffType=Emp_WeekOff_Type 

 From ##TempEmployee_HRMS 

WHERE Emp_Payroll_No= @EmpPayrollNo  

 

--, @Emp_Weekly_Off =  case when @Emp_Weekly_OffType=1 then Emp_Weekoff else Null End  

 

SET @Emp_Desig_Id = 0             

SELECT @Emp_Desig_Id = DesignationCode FROM tblDesignation WHERE UPPER(DesignationName) = UPPER(@Designation_Name) 

 

SET @Emp_Dept_Id = 0                       

SELECT @Emp_Dept_Id = DepartmentCode FROM tblDepartment WHERE UPPER(DepartmentName) = UPPER(@Department_Name) 

 

SET @Emp_Branch_Id= 0           

SELECT @Emp_Branch_Id= BranchCode FROM tblBranch WHERE UPPER(BranchName) = UPPER(@Location_Name) --@Branch_Name For Nakumatt Location is MAPPED to BRANCH in Payroll/TA 

 

SET @Emp_Catgegory_Id= 0                       

SELECT @Emp_Catgegory_Id= CategoryCode FROM tblCategory WHERE UPPER(CategoryName) = UPPER(@Category_Name) 

 

SET @Bank_Id= 0                       

SELECT @Bank_Id= BankCode FROM tblBankMaster WHERE UPPER(BankNo) = UPPER(@Bank_No) --UPPER(BankName) = UPPER(@Bank_Name) 

 

SET @Bank_Branch_Id= 0 

SELECT @Bank_Branch_Id= BankCode FROM tblBank WHERE UPPER(BankNo) = UPPER(@Bank_No + @Bank_Branch_No) --UPPER(BankBranch) = UPPER(@Bank_Branch_Name) 

 

--IF @Emp_Weekly_OffType=1   -- Pending 

--   BEGIN 

--      SELECT @Emp_Weekly_Off=Emp_Weekoff FROM WebHR.dbo.tblEmployee_Weekoff  

--      where Emp_Id = @Emp_ID and Cmp_Id=@HRMSCocode 

--   END 

 

IF @Emp_Desig_Id = 0  

 BEGIN                                 

     select  @Emp_Desig_Id  = max(Designationcode) +1 from tblDesignation 

     Insert INTO tblDesignation  (DesignationCode,DesignationName,[Status],Director, MinWorkHours, CoCode,UserId,EntryDate)  

     Values(@Emp_Desig_Id ,@Designation_Name,'' , 

     0,'' ,@CoCode,1,GETDATE() ) 

   END      

                     

IF @Emp_Dept_Id = 0   

 BEGIN                           

     select @Emp_Dept_Id= isnull(max(Departmentcode),0)+1 from tblDepartment 

     Insert INTO tblDepartment  (DepartmentCode ,DepartmentName, DepartmentHead, MaximumEmployees, CoCode, UserId, EntryDate,ProcessDay,DepartmentId)  

     Values (@Emp_Dept_Id , @Department_Name, '','', @CoCode ,1,GETDATE(),0,@DepartmentID) 

   END       

 

 

IF @Emp_Branch_Id= 0  

 BEGIN                                

     select @Emp_Branch_Id=max(BranchCode)+1 from tblBranch 

     Insert INTO tblBranch  (BranchCode, BranchID , BranchName,Address1, Address2, Telephone, Fax,  CoCode,UserId,EntryDate)  

     Values (@Emp_Branch_Id ,@Location_Code,@Location_Name,@Location_Address,@Location_Address,@Location_Phone_No,@Location_Fax_No,@CoCode,1,getdate())                     

   END      

 

 IF @Emp_Catgegory_Id= 0  

 BEGIN                                 

     select @Emp_Catgegory_Id=MAX(CategoryCode)+1 from tblCategory 

     Insert INTO tblCategory  (CategoryCode,CategoryName,MaximumEmployees,CoCode,UserId,EntryDate)  

     Values (@Emp_Catgegory_Id,@Category_Name,@Category_Maximum_Employee,@CoCode,1,GETDATE()) 

      

   END   

   IF @Bank_Id= 0  

 BEGIN                                

     select @Bank_Id=MAX(BankCode) + 1 from tblBankMaster 

     Insert INTO tblBankMaster  (BankCode,BankName,BankNo,CoCode)  

     VALUES (@Bank_Id,@Bank_Name,@Bank_No,@CoCode) 

 

                               

   END      

   IF @Bank_Branch_Id= 0  

 BEGIN       

     select @Bank_Branch_Id=MAX(BankCode) + 1 from tblBank 

     Insert INTO tblBank  (BankCode, BankNo,BankName,BankBranch,cocode,BankMasterCode)  

     VALUES (@Bank_Branch_Id,@Bank_No+@Bank_Branch_No,@Bank_Name,@Bank_Branch_Name,@Cocode,@Bank_Id) 

                               

   END 

 

 set @Next_Of_Kin='' 

 set  @strEmp_ID = CONVERT(varchar(10), @Emp_ID) 

 if not exists(select * from tblEmployee where LTRIM(RTRIM(EmpNo)) LIKE LTRIM(rtrim(@EmpPayrollNo) )) 

 begin 

 

  SELECT @EmpCode = isnull(MAX(EMPCODE),0) + 1 FROM tblEmployee 

  -- select WeeklyOffDay,* from tblEmployee  

  Insert Into tblEmployee 

  (Cocode,EmpCode, EmpNo, FamilyName, FatherName, OtherNames, EmpName, Sex, DateOfJoining, DateOfBirth, EmploymentType, 

  MaritialStatus, NoOfChildren, BranchCode, DepartmentCode, CategoryCode, DesignationCode, 

  LocalAddress1, LocalAddress2, LocalTelephone, PermanentAddress1, PermanentAddress2, 

  PermanentTelephone, EmailAddress, PINNo, NSSFNo, NHIFNo, NationalIDPPno, NationalIDCountry, Height, Weight, BloodGroup,ChargeNSSF,ChargeNHIF,UniversityName,Religion,NextOfKin,BankName,BankBranch,BankCode,BankAccountNo) 

  select @CoCode,@EmpCode,emp_payroll_no,Emp_Last_Name,'',Emp_First_Name + ' ' + Emp_Middle_Name,Emp_First_Name + ' ' + Emp_Middle_Name + ' ' + Emp_Last_Name 

  ,CASE Emp_Gender WHEN 1 then 'M' WHEN 2 then 'F' END,Emp_Join_Date,Emp_Birth_Date, 

  case Employment_Type when 1 then 0 when 2 then 1 when 3 then 0 end asemploymenttype, 

  Emp_MaritalStatus_Id,Emp_Children,@Emp_Branch_Id,@Emp_Dept_Id,@Emp_Catgegory_Id,@Emp_Desig_Id,Emp_Address ,'',Emp_WorkPhone, 

  Emp_P_Address,'',Emp_MobileNo,Emp_WorkEmail,Emp_PINNo,Emp_NSSFNo,Emp_NHIFNo,emp_Nationalityid,@Emp_NationalIDCountry,Height,[weight], 

  CASE Blood_Group WHEN 1 Then 'A-' WHEN 2 Then 'A+' WHEN 3 Then 'B-' WHEN 4 Then 'B+' 

       WHEN 5 Then 'AB-' WHEN 6 Then 'AB+' WHEN 7 Then 'O-'WHEN 8 Then 'O+' END  

  ,1,1,College_Name,Religion_Name,@Next_Of_Kin ,@Bank_Name,@Bank_Branch_Name,@Bank_Branch_Id,Account_No 

  from ##TempEmployee_HRMS    

  where ltrim(rtrim(Emp_Payroll_No)) like LTRIM(rtrim(@EmpPayrollNo))  

  --WeeklyOffDay 

  IF @Emp_Weekly_OffType=1 

  Exec ('update tblEmployee set WeeklyOffDay = ( select max(Emp_Weekoff) as Emp_Weekoff FROM ' + @LinkServer + 'tblEmployee_Weekoff where Emp_ID  = ' +  @strEmp_ID + ') where EmpNo = '''+ @EmpPayrollNo + '''') 

--       

   

 end 

else 

 begin 

 SELECT @employmenttype = case Employment_Type when 1 then 0 when 2 then 1 when 3 then 0 end, 

  @Emp_Gender =  Case Emp_Gender WHEN 1 Then 'M' WHEN 2 Then 'F' END , 

  @Emp_BloodGroup = CASE Blood_Group WHEN 1 Then 'A-' WHEN 2 Then 'A+' WHEN 3 Then 'B-' WHEN 4 Then 'B+' 

    WHEN 5 Then 'AB-' WHEN 6 Then 'AB+' WHEN 7 Then 'O-' WHEN 8 Then 'O+' END  

  FROM ##TempEmployee_HRMS   

  WHERE ltrim(rtrim(Emp_Payroll_No)) like LTRIM(rtrim(@EmpPayrollNo))  

   

  update tblemployee 

  set  

  FamilyName=A.Emp_Last_Name, 

  OtherNames=A.Emp_First_Name + ' ' + A.Emp_Middle_Name, 

  EmpName=A.Emp_First_Name + ' ' + A.Emp_Middle_Name + ' ' + A.Emp_Last_Name, 

  Sex=@Emp_Gender, 

  DateOfJoining=A.Emp_Join_Date, 

  DateOfBirth=A.Emp_Birth_Date, 

  EmploymentType=@employmenttype, 

  MaritialStatus=A.Emp_MaritalStatus_Id, 

  NoOfChildren=A.Emp_Children, 

  BranchCode=@Emp_Branch_Id, 

  DepartmentCode=@Emp_Dept_Id, 

  CategoryCode=@Emp_Catgegory_Id, 

  DesignationCode=@Emp_Desig_Id, 

  LocalAddress1=A.Emp_Address, 

  LocalTelephone=A.Emp_WorkPhone, 

  PermanentAddress1=A.Emp_P_Address, 

  PermanentTelephone=A.Emp_MobileNo, 

  EmailAddress=A.Emp_WorkEmail,  

  PINNo=A.Emp_PINNo, 

  NSSFNo=A.Emp_NSSFNo,  

  NHIFNo=A.Emp_NHIFNo,  

  NationalIDPPno=A.Emp_NationalityId,  

  NationalIDCountry=@Emp_NationalIDCountry, 

  Height= A.Height,  

  Weight=A.[weight], 

  BloodGroup=@Emp_BloodGroup, 

  UniversityName=A.College_Name, 

  Religion=A.Religion_Name, 

  NextOfKin=@Next_Of_Kin, 

  BankName=@Bank_Name, 

  BankBranch=@Bank_Branch_Name, 

  BankCode=@Bank_Branch_Id, 

  BankAccountNo=A.Account_No FROM 

  ##TempEmployee_HRMS as A  

  where LTRIM(RTRIM(EMPNO)) =  LTRIM(rtrim(@EmpPayrollNo))--ltrim(rtrim(a.emp_payroll_no)) like LTRIM(rtrim(@EmpPayrollNo)) 

  IF @Emp_Weekly_OffType=1 

  exec ('update tblEmployee set WeeklyOffDay = ( select max(Emp_Weekoff) as Emp_Weekoff FROM ' + @LinkServer + 'tblEmployee_Weekoff where Emp_ID  = ' +  @strEmp_ID + ') where EmpNo = '''+ @EmpPayrollNo + '''') 

   

 End  

 set @Update_Flag = 1 

end 

  

 Return