USE [AGSBIM_APPS_DEV]
GO
/****** Object: StoredProcedure [AGS_BIM_GLL].[USP_SAVE_LOANOUT_REQUEST] Script Date: 3/28/2023 3:43:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [AGS_BIM_GLL].[USP_SAVE_LOANOUT_REQUEST](
@requestedEmployeeId NVARCHAR(10),
@team NVARCHAR(50),
@requestObj VARCHAR(MAX)
)
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
DECLARE @requestType NVARCHAR(10) = 'LOAN_OUT';
DECLARE @count NVARCHAR(10);
SET @count = (SELECT COUNT(*) FROM [AGS_BIM_GLL].[request]) + 1
DECLARE @requestId NVARCHAR(10);
SET @requestId = CONCAT('REQ', REPLICATE('0',7-LEN(@count)) + @count);
-- status id 1: pending for approval, status id 2: approved
DECLARE @defaultStatusId INT;
SET @defaultStatusId = CASE
WHEN ((SELECT COUNT(*) FROM [AGS_BIM_GLL].[team_configuration] WHERE team = @team AND request_type = @requestType AND auto_approve = 1)) > 0 THEN 2
ELSE 1
END
INSERT INTO [AGS_BIM_GLL].[request] (request_id, request_type, status_id, created_by, created_at, updated_at ) VALUES
(@requestId, @requestType, @defaultStatusId, @requestedEmployeeId, GETUTCDATE(), GETUTCDATE());
INSERT INTO [AGS_BIM_GLL].[available_resource_pool] (request_id, team, employee_id, justification, start_date, end_date, status_id , created_at, updated_at)
SELECT @requestId, team, employee_id, justification, start_date, end_date, @defaultStatusId, GETUTCDATE(), GETUTCDATE() FROM OPENJSON(@requestObj) WITH
(
team NVARCHAR(50) '$.team',
employee_id NVARCHAR(10) '$.employeeId',
justification NVARCHAR(MAX) '$.justification',
start_date DATE '$.startDate',
end_date DATE '$.endDate'
)
DECLARE @listOfEmployeeIds table (employeeId NVARCHAR(10));
INSERT INTO @listOfEmployeeIds
SELECT employee_id FROM OPENJSON(@requestObj) WITH
(
employee_id NVARCHAR(10) '$.employeeId'
)
-- Skills
DELETE FROM [AGS_BIM_GLL].[employee_skills_mapping] WHERE employee_id IN (SELECT employeeId FROM @listOfEmployeeIds)
INSERT INTO [AGS_BIM_GLL].[employee_skills_mapping] (employee_id, skill_name, created_at, updated_at)
SELECT employee_id, skill_name, GETUTCDATE(), GETUTCDATE() FROM OPENJSON(@requestObj) WITH
(
employee_id NVARCHAR(10) '$.employeeId',
skills NVARCHAR(MAX) '$.skills' as JSON
)
CROSS APPLY OPENJSON (skills) WITH
(
id INT '$.id',
skill_name NVARCHAR(255) '$.name'
)
-- Languages
DELETE FROM [AGS_BIM_GLL].[employee_languages_mapping] WHERE employee_id IN (SELECT employeeId FROM @listOfEmployeeIds)
INSERT INTO [AGS_BIM_GLL].[employee_languages_mapping] (employee_id, language_name, created_at, updated_at)
SELECT employee_id, language_name, GETUTCDATE(), GETUTCDATE() FROM OPENJSON(@requestObj) WITH
(
employee_id NVARCHAR(10) '$.employeeId',
languages NVARCHAR(MAX) '$.languages' as JSON
)
CROSS APPLY OPENJSON (languages) WITH
(
id INT '$.id',
language_name NVARCHAR(255) '$.name'
)
-- Certifications
DELETE FROM [AGS_BIM_GLL].[employee_certifications_mapping] WHERE employee_id IN (SELECT employeeId FROM @listOfEmployeeIds)
INSERT INTO [AGS_BIM_GLL].[employee_certifications_mapping] (employee_id, certification_name, created_at, updated_at)
SELECT employee_id, certification_name, GETUTCDATE(), GETUTCDATE() FROM OPENJSON(@requestObj) WITH
(
employee_id NVARCHAR(10) '$.employeeId',
certifications NVARCHAR(MAX) '$.certifications' as JSON
)
CROSS APPLY OPENJSON (certifications) WITH
(
id INT '$.id',
certification_name NVARCHAR(255) '$.name'
)
-- Passport Details
DELETE FROM [AGS_BIM_GLL].[employee_passport_details] WHERE employee_id IN (SELECT employeeId FROM @listOfEmployeeIds)
INSERT INTO [AGS_BIM_GLL].[employee_passport_details] (employee_id, issued_by, valid_from, valid_to, created_at, updated_at)
SELECT employee_id, issued_by, valid_from, valid_to, GETUTCDATE(), GETUTCDATE() FROM OPENJSON(@requestObj) WITH
(
employee_id NVARCHAR(10) '$.employeeId',
passportDetails NVARCHAR(MAX) '$.passportDetails' as JSON
)
CROSS APPLY OPENJSON (passportDetails) WITH
(
id INT '$.id',
issued_by INT '$.issued_by',
valid_from DATE '$.valid_from',
valid_to DATE '$.valid_to'
)
-- Visa Details
DELETE FROM [AGS_BIM_GLL].[employee_visa_details] WHERE employee_id IN (SELECT employeeId FROM @listOfEmployeeIds)
INSERT INTO [AGS_BIM_GLL].[employee_visa_details] (employee_id, country_id, visa_type_id, valid_from, valid_to, created_at, updated_at)
SELECT employee_id, country_id, visa_type_id, valid_from, valid_to, GETUTCDATE(), GETUTCDATE() FROM OPENJSON(@requestObj) WITH
(
employee_id NVARCHAR(10) '$.employeeId',
visaDetails NVARCHAR(MAX) '$.visaDetails' as JSON
)
CROSS APPLY OPENJSON (visaDetails) WITH
(
id INT '$.id',
country_id INT '$.country_id',
visa_type_id INT '$.visa_type_id',
valid_from DATE '$.valid_from',
valid_to DATE '$.valid_to'
)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
THROW(SELECT ERROR_MESSAGE());
END CATCH
END
