120 lines
4.2 KiB
Transact-SQL
120 lines
4.2 KiB
Transact-SQL
USE [EnVisage]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[sp_AddBulkPeopleResource] Script Date: 08/28/2015 11:01:19 AM ******/
|
|
DROP PROCEDURE [dbo].[sp_AddBulkPeopleResource]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[sp_AddBulkPeopleResource] Script Date: 08/28/2015 11:01:19 AM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
|
|
CREATE PROCEDURE [dbo].[sp_AddBulkPeopleResource] (
|
|
@FirstName nvarchar(250),
|
|
@LastName nvarchar(250),
|
|
@Title nvarchar(200),
|
|
@CreditNumber nvarchar(100),
|
|
@StartDate datetime=null,
|
|
@EndDate datetime=null,
|
|
@isActive bit,
|
|
@EmployeeID nvarchar(100),
|
|
@ProcessID nvarchar(100),
|
|
@RecordNumber int
|
|
|
|
) AS
|
|
BEGIN
|
|
|
|
BEGIN TRANSACTION
|
|
-- declare @EndDate datetime
|
|
--using title and creditnumber determine if we have matching records to get an EC
|
|
declare @ECID uniqueidentifier
|
|
declare @CreditDepertmentID uniqueidentifier
|
|
declare @EXID uniqueidentifier
|
|
-- get credit department id
|
|
select @CreditDepertmentID=Id from CreditDepartment where CreditNumber=@CreditNumber
|
|
--get expenditure id based on title
|
|
select @EXID=Id from Expenditure where Name=@Title
|
|
|
|
if @EXID is null
|
|
begin
|
|
insert into supt_ImportErrors (ProcessID,RecordNbr,Message,DateTimeProcessed) values (@ProcessID,@RecordNumber,'NO Expenditure for "'+@Title+'" Record skipped',GETDATE())
|
|
goto exit_commit
|
|
end
|
|
if @CreditDepertmentID is null
|
|
begin
|
|
insert into supt_ImportErrors (ProcessID,RecordNbr,Message,DateTimeProcessed) values (@ProcessID,@RecordNumber,'NO CreditCode for "'+@CreditNumber+'" Record skipped',GETDATE())
|
|
goto exit_commit
|
|
end
|
|
--if we have a credit department id and expenditure id we can get
|
|
--excpediture category id used to tie to the people resourse
|
|
select @ECID=Id from ExpenditureCategory where ExpenditureId=@EXID and CreditId=@CreditDepertmentID
|
|
if @ECID is null
|
|
begin
|
|
insert into supt_ImportErrors (ProcessID,RecordNbr,Message,DateTimeProcessed) values (@ProcessID,@RecordNumber,'NO Expenditure category found for Expenditure:"'+@title+'" Credit Code:"'+@CreditNumber+'" Record skipped',GETDATE())
|
|
goto exit_commit
|
|
end
|
|
--see if we have an existing people resource record.
|
|
--first search is by first name and employeeid, second is by first and last name
|
|
declare @CurPeopleResourceRecID uniqueidentifier
|
|
select @CurPeopleResourceRecID= Id FROM PeopleResource where FirstName=@FirstName and EmployeeID=@EmployeeID
|
|
if @CurPeopleResourceRecID is null
|
|
begin
|
|
select @CurPeopleResourceRecID= Id FROM PeopleResource where FirstName=@FirstName and LastName=@LastName
|
|
end
|
|
-- if we do not have an existing record create a new one
|
|
if @CurPeopleResourceRecID is null
|
|
begin
|
|
if isnull(@EndDate,'')=''
|
|
set @EndDate=DATEADD (YEAR , 50 , getdate() )
|
|
if isnull(@StartDate,'')=''
|
|
set @StartDate=getdate()
|
|
|
|
--inserting a new record, the start date should be today, the end date will be today +50 years.
|
|
insert into PeopleResource ( FirstName,LastName,IsActiveEmployee,ExpenditureCategoryId,StartDate,EndDate,EmployeeID) values (@FirstName,@LastName,@isActive,@ECID,@StartDate,@EndDate,@EmployeeID)
|
|
end
|
|
--we have an existing record determine if we have to set the start/end dates based on isActive value
|
|
else
|
|
begin
|
|
declare @curECID uniqueidentifier
|
|
declare @StartDateCur datetime = GetDate();
|
|
declare @EndDateCur datetime = GetDate();
|
|
declare @isActiveCur bit
|
|
select @curECID=ExpenditureCategoryId,@isActiveCur=IsActiveEmployee,@StartDateCur=StartDate,@EndDateCur=EndDate FROM PeopleResource where FirstName=@FirstName and LastName=@LastName
|
|
if @isActiveCur <> @isActive
|
|
begin
|
|
if @isActive = 0
|
|
begin
|
|
if isnull(@EndDate ,'') = ''
|
|
set @EndDate=getdate();
|
|
end
|
|
else
|
|
begin
|
|
if @StartDate is null
|
|
set @StartDate=DATEADD(DAY,-1,getdate());
|
|
if isnull(@EndDate ,'') = ''
|
|
set @EndDate=DATEADD (YEAR , 50 , getdate() );
|
|
end
|
|
end
|
|
else
|
|
begin
|
|
set @StartDate=@StartDateCur
|
|
set @EndDate=@EndDateCur
|
|
end
|
|
update PeopleResource set StartDate=@StartDate,EndDate=@EndDate, IsActiveEmployee=@isActive, ExpenditureCategoryId=@ECID ,LastName=@LastName, EmployeeID=@EmployeeID where ID=@CurPeopleResourceRecID
|
|
end
|
|
|
|
exit_commit:
|
|
COMMIT TRANSACTION
|
|
END
|
|
|
|
|
|
|
|
GO
|
|
|
|
|