Bug #2329
Updated by Muhammad Bintar about 1 year ago
dear tim developer mohon support nya untuk perbaikan pada service om recap karena atasan nya tidak terupdate dengan atasan baru dan masih membaca atasan yang lama.
contoh employee max enteng 07018591
!clipboard-202309160146-l9rtg.png!
terlampir SP untuk membaca om recap nya.
========
ALTER FUNCTION [dbo].[fncGetDSId_byNip]
(
@inNIP varchar(10), @inSEQ varchar(1)
)
RETURNS varchar(100)
AS
BEGIN
-- Declare the return variable here
DECLARE @outNIP varchar(100)
-- Add the T-SQL statements to compute the return value here
DECLARE @empid_requester VARCHAR(10) = @inNIP
DECLARE @now VARCHAR(8) = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd')
DECLARE @pos VARCHAR(8)
DECLARE @posIsHead VARCHAR(8)
DECLARE @org VARCHAR(8)
DECLARE @orglvl VARCHAR(8)
DECLARE @poshead VARCHAR(8)
DECLARE @emphead VARCHAR(8)
DECLARE @empheadname VARCHAR(200)
SET @pos = ( SELECT TOP 1 object
FROM dbo.hr_md_orm_relationship
WHERE class = 'P'
AND rel_class = 'E'
AND rel_object = @empid_requester
AND start_date <= @now
AND end_date >= @now
ORDER BY last_change DESC
)
SET @posIsHead = ( SELECT dbo.GetPositionIsHead(100,@pos,@now) )
SET @org = ( SELECT TOP 1 --DISTINCT
object
FROM dbo.hr_md_orm_relationship
WHERE class = 'O'
AND rel_class = 'P'
AND rel_object = @pos
AND start_date <= @now
AND end_date >= @now
ORDER BY last_change DESC
)
SET @orglvl = ( SELECT org_level
FROM dbo.hr_md_orm_o_o
WHERE object = @org
AND start_date <= @now
AND end_date >= @now
)
SET @poshead = ( SELECT rel_object
FROM dbo.hr_md_orm_relationship
WHERE object = @org
AND class = 'O'
AND rel_class = 'P'
AND rel_type = '002'
AND start_date <= @now
AND end_date >= @now
)
SET @emphead = ( SELECT TOP 1 rel_object
FROM dbo.hr_md_orm_relationship
WHERE object = @poshead
AND class = 'P'
AND rel_class = 'E'
AND start_date <= @now
AND end_date >= @now
ORDER BY last_change DESC
)
SET @empheadname = ( SELECT full_name
FROM dbo.hr_md_emp_md0001
WHERE emp_id = @emphead
AND start_date <= @now
AND end_date >= @now
)
DECLARE @dataseq INT = 1
DECLARE @tableapv TABLE
(
apr_emp_id VARCHAR(8) ,
apr_start_date VARCHAR(8) ,
apr_end_date VARCHAR(8) ,
apr_appraisal_type VARCHAR(4) ,
apr_header_seq VARCHAR(4) ,
apr_seq VARCHAR(4) ,
apr_appraiser_id VARCHAR(8) ,
apr_appraiser_name VARCHAR(200) ,
apr_appraiser_type VARCHAR(200) ,
apr_kpi VARCHAR(8) ,
apr_qualification VARCHAR(8) ,
apr_appraiser_no VARCHAR(50)
)
DECLARE @lvlascount INT = /*CAST(@orglvl AS INT)*/7
WHILE @lvlascount > 0
BEGIN
IF @orglvl < '01'
BEGIN
BREAK
END
IF @orglvl = '01'
AND @emphead IS NULL
BEGIN
SET @emphead = ( SELECT TOP ( 1 )
emp_id
FROM dbo.hr_md_emp_md0055
WHERE position = @poshead
AND start_date <= @now
AND end_date >= @now
AND emp_id IN (SELECT a.emp_id
FROM [MinovaHR_ESS_BANK_MEGA_Production].[dbo].[hr_md_emp_md0001] AS a WITH (NOLOCK)
LEFT JOIN [MinovaHR_ESS_BANK_MEGA_Production].[dbo].[hr_md_emp_md0002] AS b WITH (NOLOCK) ON (a.emp_id = b.emp_id)
WHERE 1=1
AND GETDATE() BETWEEN a.[start_date] and a.[end_date]
AND ((b.employee_status = '01' AND GETDATE() BETWEEN b.[start_date] and b.[end_date]) OR (b.employee_status = '04' AND b.[start_date] >= GETDATE()))
AND a.external_id NOT IN ('X','x','....','','11111111','22222222','33333333'))
)
END
IF @emphead IS NULL
BEGIN
SET @emphead = ( SELECT TOP ( 1 )
emp_id
FROM dbo.hr_md_emp_md0055
WHERE position = @poshead
AND start_date <= @now
AND end_date >= @now
AND emp_id IN (SELECT a.emp_id
FROM [MinovaHR_ESS_BANK_MEGA_Production].[dbo].[hr_md_emp_md0001] AS a WITH (NOLOCK)
LEFT JOIN [MinovaHR_ESS_BANK_MEGA_Production].[dbo].[hr_md_emp_md0002] AS b WITH (NOLOCK) ON (a.emp_id = b.emp_id)
WHERE 1=1
AND GETDATE() BETWEEN a.[start_date] and a.[end_date]
AND ((b.employee_status = '01' AND GETDATE() BETWEEN b.[start_date] and b.[end_date]) OR (b.employee_status = '04' AND b.[start_date] >= GETDATE()))
AND a.external_id NOT IN ('X','x','....','','11111111','22222222','33333333'))
)
END
IF @poshead IS NOT NULL
AND @emphead IS NOT NULL
AND @empid_requester <> @emphead
BEGIN
INSERT INTO @tableapv
SELECT @empid_requester ,
'' AS apr_start_date ,
'' AS apr_end_date ,
'G' AS apr_appraisal_type ,
( SELECT ISNULL(MAX(header_seq) + 1, 1)
FROM dbo.hr_md_emp_md0048_performance_appraiser
WHERE emp_id = @empid_requester
AND appraisal_type = 'G'
) AS apr_header_seq ,
CAST(@dataseq AS VARCHAR(4)) AS apr_seq ,
@emphead AS apr_appraiser_id ,
@empheadname AS apr_appraiser_name ,
CASE WHEN @dataseq = '1'
THEN 'Direct Supervisor'
ELSE 'Indirect Supervisor'
END apr_appraiser_type ,
'' AS apr_kpi ,
'' AS apr_qualification ,
'Appraiser ' + CAST(@dataseq AS VARCHAR(4)) AS apr_appraiser_no
SET @dataseq = @dataseq + 1
END
SET @org = ( SELECT DISTINCT
object
FROM dbo.hr_md_orm_relationship
WHERE rel_object = @org
AND end_date >= @now
AND rel_class = 'O'
AND class = 'O'
AND rel_dir = 'A'
AND start_date <= @now
)
SET @orglvl = ( SELECT org_level
FROM dbo.hr_md_orm_o_o
WHERE object = @org
AND start_date <= @now
AND end_date >= @now
)
SET @poshead = ( SELECT rel_object
FROM dbo.hr_md_orm_relationship
WHERE object = @org
AND class = 'O'
AND rel_class = 'P'
AND rel_type = '002'
AND start_date <= @now
AND end_date >= @now
)
SET @emphead = ( SELECT TOP 1 rel_object
FROM dbo.hr_md_orm_relationship
WHERE object = @poshead
AND class = 'P'
AND rel_class = 'E'
AND start_date <= @now
AND end_date >= @now
ORDER BY last_change DESC
)
SET @empheadname = ( SELECT full_name
FROM dbo.hr_md_emp_md0001
WHERE emp_id = @emphead
AND start_date <= @now
AND end_date >= @now
)
SET @lvlascount = @lvlascount - 1
END
--SET @outNIP = (SELECT apr_appraiser_id AS ds_id FROM @tableapv WHERE apr_seq = @inSEQ )
SET @outNIP = (SELECT CASE WHEN ((@inSEQ = 2) AND (@posIsHead = 0) and (apr_appraiser_id IN ('99110831','15071252','13045207','18059656','03033597','14079035','04044547'))) THEN NULL ELSE apr_appraiser_id END AS ds_id FROM @tableapv WHERE apr_seq = @inSEQ )
-- Return the result of the function
RETURN @outNIP
END
remote.minovais.com:31140
database sql server 2014
remote.minovais.com, 1438
MinovaHRBaseServerMegaConven