
--- Script 1

/****** Object:  StoredProcedure [dbo].[Proc_TA_InsertEmployeeDetails]    Script Date: 23-06-2026 16:05:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  PROCEDURE  [dbo].[Proc_TA_InsertEmployeeDetails]   
(  
	@type CHAR(1),
	@EmployeeId INT=0,
	@LevelId INT=0,
	@UserGroupId NVARCHAR(50)='',  
	@DesignationId INT=0,
	@BusRouteId INT=0,
	@EmpFirstName NVARCHAR(50)='',
	@EmpMiddleName NVARCHAR(50)='',  
	@EmpLastName NVARCHAR(50)='',
	@EmpFatherName NVARCHAR(50)='',
	@EmpMotherName NVARCHAR(50)='',
	@dob NVARCHAR(15)=null,  
	@dobrecord NVARCHAR(15)=null,
	@BloodGroup NVARCHAR(10)='',
	@EmailId NVARCHAR(100)='',
	@ContactAddress NVARCHAR(255)='',  
	@PermanentAddress NVARCHAR(255)='',
	@PhoneNo NVARCHAR(20)='',
	@MobileNo NVARCHAR(20)='',
	@Gender NVARCHAR(10)='',  
	@MaritalStatus NVARCHAR(10)='',
	@Nationality NVARCHAR(50)='',
	@Religion INT,  
	@Photo IMAGE,  
	@PersonalEmailId NVARCHAR(100)='',
	@PassportNo NVARCHAR(50)='',
	@PExpiry NVARCHAR(15)=null,
	@VisaNo NVARCHAR(50)='',  
	@VExpiry NVARCHAR(15)=null,
	@VehicleType NVARCHAR(20)='',
	@VehicleNo NVARCHAR(50)='',  
	@AnniversaryDate NVARCHAR(15)=null,  
	@NativePlace INT=0,  
	@District NVARCHAR(50)='',  
	@State INT=0,  
	@Country INT=0,  
	@SpouseNo INT=0,  
	@ChildrenNo INT=0,  
	@EmpCode NVARCHAR(15)='',  	
	@DateOfJoin NVARCHAR(15)=null,  
	@ReportingTo INT=0,   
	@LeaveSancAuthority INT=0,  	  
	@PrevExperience FLOAT=0,  
	@BasicSalary FLOAT,  
	@HostelWorker BIT=0, 
	@Username NVARCHAR(15)='',  
	@Password NVARCHAR(50)='',  
	@Qual_Join NVARCHAR(50)=null,
	@Qual_Employment NVARCHAR(50)=null,  
	@OutDoorJob BIT=0,  
	@CompOffEligible BIT=0,  
	@OTEligible BIT=0,  
	@ESIeligible BIT=0, 
	@PFeligible BIT=0,
	@ReportExclude BIT=0,  
	@EmpStatus BIT=0,  
	@SwipeCardNo NVARCHAR(20),
	@intEmpCategory NVARCHAR(15),
	@intEmpType NVARCHAR(15),  
	@EmpID INT=0,
	@strBasicSalEffDate NVARCHAR(20)=null,
	@strDesigEffDate NVARCHAR(20)=null,
	@intYearSettingsId INT,
	@strLevelEffDate NVARCHAR(20)=null, 
	@strEmpCatEffDate NVARCHAR(20)=null, 
	@strEmpTypeEffDate NVARCHAR(20)=null,
	@intFlexibleTimings int,
	@InActiveDate NVARCHAR(15)=null,	
	@CustomField1 nvarchar(max)=null,
	@CustomField2 nvarchar(max)=null,
	@CustomField3 nvarchar(max)=null,
	@CustomField4 nvarchar(max)=null,
	@CustomField5 nvarchar(max)=null,
	@ApplnMode BIT,
	@penalty bit,
	@MobileClockInOUt bit,
	@SMS bit,
	@IsPassportReminderRequired bit = 0,
	@PassportRemindDays int = 0,
	@IsVisaReminderRequired bit =0,
	@VisaRemindDays int = 0,
	-- payroll config--
	@hra int=0,
	@DA int =0,
	@MA int=0,
	@CA int =0,
	@PFESI int=0,
	@TaxPolicy int=0,
	@CEA int=0,
	@CTC bigint=0,
	-- Contractor Id--
	@contractorId int=0,
	@strContractorEffDate NVARCHAR(20)=null,
	-- Account and GovId--
	@CustomField6 Nvarchar(15),
	@InActiveReason Nvarchar(255)
) 
AS  
	set nocount on 
	DECLARE @Isdeleted INT 
	SET @Isdeleted=0  
	DECLARE @countBasicPay INT
	DECLARE @countDesignation INT  
	DECLARE @countLevel INT
	DECLARE @countContract INT
	DECLARE @countCategory INT
	DECLARE @countType INT
	DECLARE @Inactive_Status BIT
	Declare @Deleted int
	SET @countBasicPay=0  
	SET @countDesignation=0
	SET @countLevel=0
	SET @countContract=0
	SET @countCategory=0
	SET @countType=0
	SET @Inactive_Status = (CASE WHEN ISNULL(@EmpStatus, 0) = 0 or @EmpStatus='' THEN 1 ELSE 0 END)
	if(@Religion=0)
	begin
		select top 1 @Religion=ID from TA_Tbl_Religion where Name='All' and IsDeleted=0
		set @Religion=ISNULL(@Religion,0)
	end
	-- insert  
	IF (@type='I')  
	BEGIN
		if (@Password is null or @Password='') 
		begin
			set @Password='QL5OWbmiorXf+5GMDoaz1w=='
		end
		INSERT INTO TA_Tbl_Employee 
		(
			UserGroup_Id,
			Bus_Id,
			Employee_FirstName,
			Employee_MiddleName,
			Employee_LastName,  
			Father_name,
			Mother_name,
			DateofBirth,
			DateofBirth_Record,
			Blood_Group,
			EmailId,
			Contact_Address,
			Permanent_Address,
			Phone_Number,
			Mobile_Number,
			Gender,
			Marital_status,
			Nationality,
			Religion,
			Personal_mailid,
			Passport_No,  
			Passport_expirydate,
			Visa_No,
			Visa_expirydate,
			Vehicle_Type,
			Vehicle_No,
			Anniversary_Date,
			Native_Place,
			District,
			State,
			Country,
			Spouse_No,
			Children_No,
			Employee_Code_Id,
			DateOfJoining,
			Reporting_To,  
			Leave_Sanc_Authority,
			Prev_Experience,
			Hostel,
			UserName,
			Emp_password,
			Qual_JoiningTime,
			Qual_Employment,
			OutDoorJob,
			CompOff_Eligible,
			OT_Eligible,
			ESI_Eligible,
			PF_Eligible,
			Report_Exclude,
			Employee_Status,
			SwipeCardNo,
			Emp_ID,
			Isdeleted,
			IsFlexible,
			InActiveDate,
			InActive_Status,
			CustomField1,
			CustomField2,
			CustomField3,
			CustomField4,
			CustomField5,
			penalty,
			MobileClockInOUt,
			SMSEnabled,
			Basic_Salary,
			IsPassportReminderRequired,
			PassportRemindDays,
			IsVisaReminderRequired,
			VisaRemindDays,
			HRA,DA,MedicalAlllowance,CEDAllowance,CA,PF_Policy,Tax_Policy,Emp_Ctc,CustomField6,InActiveReason
		)
		VALUES 
		(
			@UserGroupId,
			cast(@BusRouteId as nvarchar(25)),
			@EmpFirstName,
			@EmpMiddleName,
			@EmpLastName,
			@EmpFatherName,
			@EmpMotherName,
			case when isnull(@dob,'')='' then null else @dob end,
			case when isnull(@dobrecord,'')='' then null else @dobrecord end,
			@BloodGroup,
			@EmailId,
			@ContactAddress,
			@PermanentAddress,
			@PhoneNo,
			@MobileNo,
			@Gender,
			@MaritalStatus,
			@Nationality,
			convert(varchar(10),@Religion) ,
			@PersonalEmailId,
			@PassportNo,
			case when isnull(@PExpiry,'')='' then null else @PExpiry end,  
			@VisaNo,
			case when isnull(@VExpiry,'')='' then null else @VExpiry end,
			@VehicleType,
			@VehicleNo,
			case when isnull(@AnniversaryDate,'')='' then null else @AnniversaryDate end,
			cast(@NativePlace as nvarchar(25)),
			@District,
			cast(@State as nvarchar(25)),
			cast(@Country as nvarchar(25)),
			cast(@SpouseNo as nvarchar(25)),
			cast(@ChildrenNo as nvarchar(25)),
			@EmpCode,			
			case when isnull(@DateOfJoin,'')='' then null else @DateOfJoin end,
			cast(@ReportingTo as nvarchar(25)),
			cast(@LeaveSancAuthority as nvarchar(25)),     
			cast(@PrevExperience as nvarchar(25)),
			cast(@HostelWorker as nvarchar(25)),
			@Username,
			@Password,
			@Qual_Join,
			@Qual_Employment,
			cast(@OutDoorJob as nvarchar(25)),
			cast(@CompOffEligible as nvarchar(25)),
			cast(@OTEligible as nvarchar(25)),
			cast(@ESIeligible as nvarchar(25)),
			cast(@PFeligible as nvarchar(25)),
			cast(@ReportExclude as nvarchar(25)),
			cast(@EmpStatus as nvarchar(25)),
			@SwipeCardNo,
			cast(@EmpID as nvarchar(25)),
			cast(@Isdeleted as nvarchar(25)),
			cast(@intFlexibleTimings as nvarchar(25)),
			case when isnull(@InActiveDate,'')='' then null else @InActiveDate end ,
			CAST(@Inactive_Status AS VARCHAR(10)),
			cast(@CustomField1 as nvarchar(1000)),
			cast(@CustomField2 as nvarchar(1000)),
			cast(@CustomField3 as nvarchar(1000)),
			cast(@CustomField4 as nvarchar(1000)),
			cast(@CustomField5 as nvarchar(1000)),
			@penalty,
			@MobileClockInOUt,
			@SMS,
			@BasicSalary,
			@IsPassportReminderRequired,
			@PassportRemindDays,
			@IsVisaReminderRequired,
			@VisaRemindDays,
			@hra,@DA,@MA,@CEA,@CA,@PFESI,@TaxPolicy,@CTC,@CustomField6,@InActiveReason
		)
		--exec(@sql)  
		IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[UpdateTrigTA_Tbl_Employee]'))
		begin
			--DROP TRIGGER [dbo].[UpdateTrigTA_Tbl_Employee]
			DISABLE TRIGGER UpdateTrigTA_Tbl_Employee ON TA_Tbl_Employee
			set @Deleted=1
		end	
		declare @nextId as int    		
		select @nextId =max(employee_id) from ta_tbl_employee	
		INSERT INTO TA_Tbl_EmployeePay (EmployeeId,BasicPay,EffectiveDate,EmpID) VALUES(@nextId,@BasicSalary,@strBasicSalEffDate,@EmpID)
		INSERT INTO TA_Tbl_EmployeeDesignation (EmployeeId,DesignationID,EffectiveDate,EmpID) VALUES(@nextId,@DesignationId,@strDesigEffDate,@EmpID)
		INSERT INTO TA_Tbl_EmployeeLevel (EmployeeId,LevelId,EffectiveDate,EmpID) VALUES(@nextId,@LevelId,@strLevelEffDate,@EmpID)
		-- Below Code Should be added the New ELGI Customization Project --
		--Insert Into TA_tbl_EmployeeContractor (EmployeeId,ContractorId,EffectiveDate,EmpID) VALUES(@EmployeeId,@contractorId,@strContractorEffDate,@EmpID)
		INSERT INTO TA_Tbl_EmployeeCategory (EmployeeId,CategoryId,EffectiveDate,EmpId)VALUES(@nextId,@intEmpCategory,@strEmpCatEffDate,@EmpID)
		INSERT INTO TA_Tbl_EmployeeType (EmployeeId,TypeId,EffectiveDate,EmpId)VALUES(@nextId,@intEmpType,@strEmpTypeEffDate,@EmpID)
		Insert Into TA_Tbl_LevelChanges(EmployeeId,Emp_LevelID,Transfer_FromDate) Values(@EmployeeId,@LevelId,cast(Convert(date,getdate()) as nvarchar(20)))

		Insert Into TA_tbl_EmployeeContractor (EmployeeId,ContractorId,EffectiveDate,EmpID,Timestamp) VALUES(@nextId,@contractorId,@strContractorEffDate,@EmpID,GETDATE())

		--Enhancement II
		update TA_Tbl_Employee set Reporting_To=@contractorId,Leave_Sanc_Authority=@contractorId where Employee_id=@nextId

		-- Type Details Insert Ends here
		if @Deleted=1
		begin
			Enable TRIGGER UpdateTrigTA_Tbl_Employee ON TA_Tbl_Employee
		end
	END
	---update  
	ELSE IF (@type='U')  
	BEGIN  
		IF (@Password is null or @Password='')  
		BEGIN  
			SELECT @Password = Emp_password FROM TA_Tbl_Employee WHERE Employee_Id =@EmployeeId  
			if (@Password is null or @Password='') 
			begin
				set @Password='QL5OWbmiorXf+5GMDoaz1w=='
			end
		END
		--Manual Entry Employee Code Update, while changing the Employee Code Id
		DECLARE @EmployeeCodeId as varchar(15)
		SELECT @EmployeeCodeId=Employee_Code_Id FROM TA_Tbl_Employee WHERE Employee_Id =@EmployeeId
		if(@EmployeeCodeId<>@EmpCode)
		BEGIN
			update TA_Tbl_ManualTimeEntry set ManualTimeEntry_Employee_Id=@EmpCode where ManualTimeEntry_Employee_Id=@EmployeeCodeId
		END
		IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[UpdateTrigTA_Tbl_Employee]'))
		begin
			--DROP TRIGGER [dbo].[UpdateTrigTA_Tbl_Employee]

			DISABLE TRIGGER UpdateTrigTA_Tbl_Employee ON TA_Tbl_Employee
			set @Deleted=1
		end	
		UPDATE TA_Tbl_Employee SET
		MobileClockInOUt = @MobileClockInOUt,
		SMSEnabled=@SMS,
		penalty=@penalty,	
		UserGroup_Id=@UserGroupId,  
		Bus_Id=cast(@BusRouteId as nvarchar(25)),Employee_FirstName=@EmpFirstName,Employee_MiddleName=@EmpMiddleName,Employee_LastName=@EmpLastName,Father_name=@EmpFatherName,Mother_name=@EmpMotherName, 
		Blood_Group=@BloodGroup,EmailId=@EmailId,Contact_Address=@ContactAddress,Permanent_Address=@PermanentAddress,Phone_Number=@PhoneNo,Mobile_Number=@MobileNo,Gender=@Gender,Marital_status=@MaritalStatus,  
		Nationality=@Nationality,Religion=convert(varchar(10),@Religion),Personal_mailid=@PersonalEmailId,Passport_No=@PassportNo,Visa_No=@VisaNo,Vehicle_Type=@VehicleType,  
		Vehicle_No=@VehicleNo,Native_Place=cast(@NativePlace as nvarchar(25)),District=@District,State=cast(@State as nvarchar(25)),Country=cast(@Country as nvarchar(25)),Spouse_No=cast(@SpouseNo as nvarchar(25)),Children_No=cast(@ChildrenNo as nvarchar(25)),Employee_Code_Id=@EmpCode,  
		Reporting_To=cast(@ReportingTo as nvarchar(25)),Leave_Sanc_Authority=cast(@LeaveSancAuthority as nvarchar(25)),Prev_Experience=cast(@PrevExperience as nvarchar(25)),Hostel=cast(@HostelWorker as nvarchar(25)),UserName=@Username,Emp_password=@Password,Qual_JoiningTime=@Qual_Join,  
		Qual_Employment=@Qual_Employment,OutDoorJob=cast(@OutDoorJob as nvarchar(25)),CompOff_Eligible=cast(@CompOffEligible as nvarchar(25)),OT_Eligible=cast(@OTEligible as nvarchar(25)),ESI_Eligible=cast(@ESIeligible as nvarchar(25)),PF_Eligible=cast(@PFeligible as nvarchar(25)),
		Report_Exclude=cast(@ReportExclude as nvarchar(25)),
		Employee_Status=cast(@EmpStatus as nvarchar(25)),
		SwipeCardNo=@SwipeCardNo,
		modify_by=cast(@EmpID as nvarchar(25)),
		modify_date=cast(getdate() as nvarchar(25)),
		IsFlexible=cast(@intFlexibleTimings as nvarchar(25)),
		CustomField1  =cast(@CustomField1 as nvarchar(1000)),
		CustomField2  =cast(@CustomField2 as nvarchar(1000)),
		CustomField3  =cast(@CustomField3 as nvarchar(1000)),
		CustomField4  =cast(@CustomField4 as nvarchar(1000)),
		CustomField5  =cast(@CustomField5 as nvarchar(1000)),
		InActiveDate= case @InActiveDate when '' then null else @InActiveDate end,
		InActive_Status = CAST(@Inactive_Status AS VARCHAR(10)),
		Basic_Salary=@BasicSalary,
		DateofBirth_Record=	case when isnull(@dobrecord,'')=''	then null else @dobrecord end,		
		Anniversary_Date=	case when isnull(@AnniversaryDate,'')='' then null else @AnniversaryDate end,
		DateOfJoining=		case when isnull(@DateOfJoin,'')='' then null else @DateOfJoin end,
		DateofBirth=		case when isnull(@dob,'')=''		then null else @dob end,
		Photo=case when (@Photo is null)then null else @Photo end,
		IsPassportReminderRequired = @IsPassportReminderRequired,
        IsVisaReminderRequired = @IsVisaReminderRequired,
		HRA=@hra,DA=@DA,CA=@CA,CEDAllowance=@CEA,PF_Policy=@PFESI,Tax_Policy=@TaxPolicy,Emp_Ctc=@CTC, CustomField6=@CustomField6,
		InActiveReason=@InActiveReason
		where Employee_Id = @EmployeeId	

		-- Organization Level Changes insert into Table Created By Maria Prasanth 11:05 22-02-2023

		IF((Select Count(*) From TA_Tbl_LevelChanges where Transfer_FromDate>=Cast(Convert(date,GETDATE()) as varchar(20))and Transfer_ToDate IS Null and EmployeeID=@EmployeeId)>0)
		begin
			
			Update TA_Tbl_LevelChanges set Emp_LevelID=@LevelId where EmployeeID=@EmployeeId and Transfer_FromDate>=Cast(Convert(date,GETDATE()) as varchar(20))
		End
		Else
		Begin
			
			Declare @PreviousFromDate DateTime

			Select @PreviousFromDate=Transfer_FromDate  From TA_Tbl_LevelChanges where EmployeeId=@EmployeeId 

			print @PreviousFromDate

			Update TA_Tbl_LevelChanges set Transfer_ToDate=cast(Convert(date,DateAdd(day,-1,getdate())) as nvarchar(20)) where EmployeeID=@EmployeeId and Transfer_ToDate is Null

			Insert Into TA_Tbl_LevelChanges(EmployeeId,Emp_LevelID,Transfer_FromDate) Values(@EmployeeId,@LevelId,cast(Convert(date,getdate()) as nvarchar(20)))
		End

		IF((SELECT CONVERT(datetime,Passport_expirydate) from TA_Tbl_Employee where Employee_Id=@EmployeeId)!=CONVERT(datetime,@PExpiry))
		begin
			update TA_Tbl_Employee set Passport_expirydate=case when isnull(@PExpiry,'')=''	then null else @PExpiry end,
			PassportRemindDays=null where Employee_Id=@EmployeeId
		end
		
		IF((SELECT CONVERT(datetime,Visa_expirydate) from TA_Tbl_Employee where Employee_Id=@EmployeeId)!=CONVERT(datetime,@VExpiry))
		begin
			update TA_Tbl_Employee set Visa_expirydate=	case when isnull(@VExpiry,'')=''	then null else @VExpiry end,
			VisaRemindDays=null where Employee_Id=@EmployeeId
		end				
		if @Deleted=1
			begin
				Enable TRIGGER UpdateTrigTA_Tbl_Employee ON TA_Tbl_Employee
			end			
		-- UPDATE/Insert Employee Basic Pay Details
		BEGIN
			select @countBasicPay=count(EmployeeId) from TA_Tbl_EmployeePay where EffectiveDate=@strBasicSalEffDate and EmployeeId = @EmployeeId
			if(@countBasicPay=0)
			begin
				--Adding Timestamp to getdate() for null insert value error while modifyinh the employee by MAnjula.P on 09-Oct-2013
				INSERT INTO TA_Tbl_EmployeePay (EmployeeId,BasicPay,EffectiveDate,EmpID,Timestamp)VALUES(@EmployeeId,@BasicSalary,@strBasicSalEffDate,@EmpID,GETDATE())
			end
			else
			begin
				UPDATE TA_Tbl_EmployeePay SET BasicPay=@BasicSalary,EmpID=@EmpID where EmployeeId = @EmployeeId  and EffectiveDate=@strBasicSalEffDate
			end
		END
		-- Employee Pay Details UPDATE ends here
		-- Insert/Update Employee Designation Details
		BEGIN
			Select @countDesignation=count(EmployeeId) from TA_Tbl_EmployeeDesignation where EffectiveDate=@strDesigEffDate and EmployeeId = @EmployeeId
			if(@countDesignation=0)
				begin
					INSERT INTO TA_Tbl_EmployeeDesignation (EmployeeId,DesignationID,EffectiveDate,EmpID) VALUES(@EmployeeId,@DesignationId,@strDesigEffDate,@EmpID)
				end
			else
				begin
					UPDATE TA_Tbl_EmployeeDesignation SET DesignationID=@DesignationId,EmpID=@EmpID where EmployeeId = @EmployeeId and EffectiveDate=@strDesigEffDate  
				end
		END
		-- Designation Details Insert Ends here
		-- Insert/Update Employee Level Details
		BEGIN
			Select @countLevel=count(EmployeeId) from TA_Tbl_EmployeeLevel where EffectiveDate=@strLevelEffDate and EmployeeId = @EmployeeId
			if(@countLevel=0)
				begin
					INSERT INTO TA_Tbl_EmployeeLevel (EmployeeId,LevelID,EffectiveDate,EmpID) VALUES(@EmployeeId,@LevelId,@strLevelEffDate,@EmpID)
				end
			else
				begin
					UPDATE TA_Tbl_EmployeeLevel SET LevelId=@LevelId,EmpID=@EmpID where EmployeeId = @EmployeeId and EffectiveDate=@strLevelEffDate  
				end
		END
		-- Level Details Insert/Update Ends here
		-- Contractor Id Update 
		BEGIN
			Select @countContract=count(EmployeeId) from TA_tbl_EmployeeContractor where EffectiveDate=@strContractorEffDate and EmployeeId = @EmployeeId
			if(@countContract=0)
				begin
					Insert Into TA_tbl_EmployeeContractor (EmployeeId,ContractorId,EffectiveDate,EmpID,Timestamp) VALUES(@EmployeeId,@contractorId,@strContractorEffDate,@EmpID,getDate())
				end
			else
				begin
					UPDATE TA_tbl_EmployeeContractor SET ContractorId=@contractorId,EmpID=@EmpID,Timestamp=getDate() where EmployeeId = @EmployeeId and EffectiveDate=@strContractorEffDate  
				end
		END
		-- Insert/Update Employee Category Details
		BEGIN
			Select @countCategory=count(EmployeeId) from TA_Tbl_EmployeeCategory where EffectiveDate=@strEmpCatEffDate and EmployeeId = @EmployeeId
			if(@countCategory=0)
				begin				
					INSERT INTO TA_Tbl_EmployeeCategory (EmployeeId,CategoryId,EffectiveDate,EmpID) VALUES(@EmployeeId,@intEmpCategory,@strEmpCatEffDate,@EmpID)
				end
			else
				begin				
					UPDATE TA_Tbl_EmployeeCategory SET CategoryId=@intEmpCategory,EmpID=@EmpID where EmployeeId = @EmployeeId and EffectiveDate=@strEmpCatEffDate  
				end
		END
		-- Category Details Insert/Update Ends here
		-- Insert/Update Employee Type Details
		BEGIN
			Select @countType=count(EmployeeId) from TA_Tbl_EmployeeType where EffectiveDate=@strEmpTypeEffDate and EmployeeId = @EmployeeId
			if(@countType=0)
				begin
					INSERT INTO TA_Tbl_EmployeeType (EmployeeId,TypeId,EffectiveDate,EmpID) VALUES(@EmployeeId,@intEmpType,@strEmpTypeEffDate,@EmpID)
				end
			else
				begin
					UPDATE TA_Tbl_EmployeeType SET TypeId=@intEmpType,EmpID=@EmpID where EmployeeId = @EmployeeId and EffectiveDate=@strEmpTypeEffDate  
				end
		END
		IF(@ApplnMode = 0)
		BEGIN		
			IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[UpdateTrigTA_Tbl_Employee]'))
			begin
				--DROP TRIGGER [dbo].[UpdateTrigTA_Tbl_Employee]
				DISABLE TRIGGER UpdateTrigTA_Tbl_Employee ON TA_Tbl_Employee
				set @Deleted=1
			end	
			/* Start(07-Apr-2014): Update IsDeleted = 1 When Employee Count Greater Then 5 */
			UPDATE TA_Tbl_Employee SET
				Isdeleted = 1,
				--Report_Exclude = 1,
				Employee_Status = 0,
				InActiveDate = DATEADD(D, 1, DateOfJoining)
			WHERE Employee_Id NOT IN(SELECT TOP 5 Employee_Id FROM TA_Tbl_Employee WHERE Isdeleted = 0 AND Employee_Code_Id <> '18000000' ) AND
				Employee_Code_Id <> '18000000' AND ISNULL(InActive_Status, 0) = 0
			/* End */
			if @Deleted=1
			begin
				Enable TRIGGER UpdateTrigTA_Tbl_Employee ON TA_Tbl_Employee
			end
		END
END


--- Script 2


/****** Object:  Table [dbo].[TA_Tbl_ContractorMaster]    Script Date: 23-06-2026 17:44:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TA_Tbl_ContractorMaster](
	[Contractor_Id] [int] IDENTITY(1,1) NOT NULL,
	[Contractor_Code] [nvarchar](20) NOT NULL,
	[Contractor_Name] [nvarchar](100) NOT NULL,
	[ContactPerson] [nvarchar](100) NULL,
	[EmailId] [nvarchar](150) NULL,
	[Phone_Number] [nvarchar](50) NULL,
	[UserName] [nvarchar](50) NOT NULL,
	[Emp_password] [nvarchar](255) NOT NULL,
	[Employee_Status] [bit] NOT NULL,
	[Company_Id] [int] NULL,
	[Created_By] [int] NULL,
	[CreatedDate] [datetime] NULL,
	[ModifyDate] [datetime] NULL,
	[ModifyBy] [bigint] NULL,
	[LastLoginOn] [datetime] NULL,
	[UserGroup_Id] [int] NULL,
	[Isdeleted] [bit] NULL,
 CONSTRAINT [PK_TA_Tbl_ContractorMaster] PRIMARY KEY CLUSTERED 
(
	[Contractor_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [UQ_ContractorMaster_Code] UNIQUE NONCLUSTERED 
(
	[Contractor_Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [UQ_ContractorMaster_UserName] UNIQUE NONCLUSTERED 
(
	[UserName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TA_Tbl_ContractorMaster] ADD  DEFAULT ((1)) FOR [Employee_Status]
GO

ALTER TABLE [dbo].[TA_Tbl_ContractorMaster] ADD  DEFAULT (getdate()) FOR [CreatedDate]
GO

ALTER TABLE [dbo].[TA_Tbl_ContractorMaster] ADD  DEFAULT ((0)) FOR [Isdeleted]
GO


--- Script 3





GO
/****** Object:  StoredProcedure [dbo].[sp_ELGI_Contractor_Insert]    Script Date: 23-06-2026 17:40:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
exec sp_ELGI_Contractor_Insert @Contractor_Code=N'CO0003',@Contractor_Name=N'ABC Contract',@ContactPerson=N'',@EmailId=N'',@Phone_Number=N'521569851222220',@UserName=N'CO0003',@PwdHash=N'280d44ab1e9f79b5cce2dd4f58f5fe91f0fbacdac9f7447dffc318ceb79f2d02',@Employee_Status=1,@UserGroup_Id=0,@Created_By=1


*/

ALTER PROCEDURE [dbo].[sp_ELGI_Contractor_Insert]
    @Contractor_Code NVARCHAR(20),
    @Contractor_Name NVARCHAR(100),
    @ContactPerson   NVARCHAR(100),
    @EmailId         NVARCHAR(150),
    @Phone_Number    NVARCHAR(50),
    @UserName        NVARCHAR(50),
    @PwdHash         NVARCHAR(255),
    @Employee_Status BIT,
    @UserGroup_Id    INT,
    @Created_By      INT
AS
BEGIN
    SET NOCOUNT ON

    IF EXISTS (SELECT 1 FROM [dbo].[TA_Tbl_ContractorMaster]
               WHERE [Contractor_Code] = @Contractor_Code AND ISNULL([Isdeleted],0) = 0)
    BEGIN
        RAISERROR('Duplicate Contractor Code.',16,1); RETURN
    END

    IF EXISTS (SELECT 1 FROM [dbo].[TA_Tbl_ContractorMaster]
               WHERE [UserName] = @UserName AND ISNULL([Isdeleted],0) = 0)
    BEGIN
        RAISERROR('Username already exists.',16,1); RETURN
    END

    INSERT INTO [dbo].[TA_Tbl_ContractorMaster]
        ([Contractor_Code],[Contractor_Name],[ContactPerson],[EmailId],[Phone_Number],
         [UserName],[Emp_password],[Employee_Status],[UserGroup_Id],[Created_By],[CreatedDate],[Isdeleted])
    VALUES
        (@Contractor_Code, @Contractor_Name, @ContactPerson, @EmailId, @Phone_Number,
         @UserName, @PwdHash, @Employee_Status, @UserGroup_Id, @Created_By, GETDATE(), 0)

         Declare @contractorAutoId int

         Select @contractorAutoId=Contractor_Id From TA_Tbl_ContractorMaster where Contractor_Code=@Contractor_Code


         INSERT INTO TA_tbl_Employee
(
    
    UserGroup_Id,
    Designation_Id,
    ShiftPattern_Id,
    Bus_Id,
    Employee_FirstName,
    Employee_MiddleName,
    Employee_LastName,
    Father_name,
    Mother_name,
    DateofBirth,
    DateofBirth_Record,
    Blood_Group,
    EmailId,
    Contact_Address,
    Permanent_Address,
    Phone_Number,
    Mobile_Number,
    Gender,
    Marital_status,
    Nationality,
    Religion,
    Photo,
    Personal_mailid,
    Passport_No,
    Passport_expirydate,
    Visa_No,
    Visa_expirydate,
    Vehicle_Type,
    Vehicle_No,
    Anniversary_Date,
    Native_Place,
    District,
    State,
    Country,
    Spouse_No,
    Children_No,
    Employee_Code_Id,
    DateOfJoining,
    Reporting_To,
    Leave_Sanc_Authority,
    Prev_Experience,
    Basic_Salary,
    Allowances,
    UserName,
    Emp_password,
    Qual_JoiningTime,
    Qual_Employment,
    OutDoorJob,
    CompOff_Eligible,
    OT_Eligible,
    ESI_Eligible,
    PF_Eligible,
    Report_Exclude,
    Employee_Status,
    SwipeCardNo,
    Time_Stamp,
    Emp_ID,
    Isdeleted,
    Hostel,
    IsFlexible,
    InActiveDate,
    CustomField1,
    CustomField2,
    CustomField3,
    CustomField4,
    CustomField5,
    IsComplete,
    Isdeleted_Status,
    InActive_Status,
    IsImported,
    modify_date,
    modify_by,
    penalty,
    MobileClockInOUt,
    SMSEnabled,
    IsPassportReminderRequired,
    PassportRemindDays,
    IsVisaReminderRequired,
    VisaRemindDays,
    IsMailSentForPassport,
    IsMailSentForVisa,
    IsPass_LastUpdatedOn,
    IsVisa_LastUpdatedOn,
    IsMyProfileRqrd,
    Emp_Ctc,
    HRA,
    DA,
    PF_Policy,
    CA,
    MedicalAlllowance,
    CEDAllowance,
    Tax_Policy,
    CustomField6,
    INActiveReason,
    Contractor_Id
)
VALUES
(
    3,
    NULL,
    NULL,
    0,
    @Contractor_Name,
    '',
    '',
    '',
    '',
    '1997-04-05',
    '1997-04-05',
    '',
    '',
    '',
    '',
    '',
    '',
    'Male',
    'Single',
    '',
    1,
    NULL,
    '',
    '',
    NULL,
    '',
    NULL,
    '-Select-',
    '',
    NULL,
    '',
    '',
    '',
    '',
    0,
    0,
    @Contractor_Code,
    '2026-01-01',
    0,
    0,
    0,
    0,
    NULL,
    @Contractor_Code,
    'QL5OWbmiorXf+5GMDoaz1w==',
    '',
    '',
    0,
    0,
    0,
    0,
    0,
    0,
    1,
    @Contractor_Code,
    '2026-06-17 10:13:23.170',
    1,
    0,
    1,
    0,
    NULL,
    '000000000000',
    '000000000000',
    '000000000000',
    '',
    '',
    NULL,
    NULL,
    0,
    NULL,
    NULL,
    NULL,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    NULL,
    NULL,
    NULL,
    NULL,
    0,
    0,
    0,
    1,
    0,
    0,
    0,
    0,
    0,
    '',
    '',
    @contractorAutoId
);


    SELECT SCOPE_IDENTITY() AS NewContractorId
END
