BANK KALTENG

DECLARE @empid_requester VARCHAR = '10000099' DECLARE @now VARCHAR = dbo.fn_formatdatetime(GETDATE, 'yyyymmdd') DECLARE @pos VARCHAR;
DECLARE @org VARCHAR;
DECLARE @orglvl VARCHAR;
DECLARE @poshead VARCHAR;
DECLARE @emphead VARCHAR;
DECLARE @empheadname VARCHAR;
SET @pos =
(
SELECT ObjectID
FROM dbo.PHROM0002
WHERE ObjectClass = 'P'
AND RelationshipClass = 'E'
AND RelationshipObject = @empid_requester
AND StartDate <= @now
AND EndDate >= @now
);
SET @org =
(
SELECT DISTINCT
ObjectID
FROM dbo.PHROM0002
WHERE ObjectClass = 'O'
AND RelationshipClass = 'P'
AND RelationshipObject = @pos
AND StartDate <= @now
AND EndDate >= @now
);
SET @orglvl =
(
SELECT OrganizationLevel
FROM dbo.PHROM0004
WHERE ObjectID = @org
AND StartDate <= @now
AND EndDate >= @now
);
SET @poshead =
(
SELECT ObjectID
FROM dbo.PHROM0001
WHERE ObjectID IN
(
SELECT RelationshipObject
FROM dbo.PHROM0002
WHERE ObjectID = @org
AND ObjectClass = 'O'
AND RelationshipClass = 'P' /*AND RelationshipType = '002'*/
AND StartDate <= @now
AND EndDate >= @now
)
AND ObjectClass = 'P'
AND PositionHead = '1'
AND StartDate <= @now
AND EndDate >= @now
);
SET @emphead =
(
SELECT RelationshipObject
FROM dbo.PHROM0002
WHERE ObjectID = @poshead
AND ObjectClass = 'P'
AND RelationshipClass = 'E'
AND StartDate <= @now
AND EndDate >= @now
);
SET @empheadname =
(
SELECT FullName
FROM dbo.PHRPA0001
WHERE EmployeeID = @emphead
AND StartDate <= @now
AND EndDate >= @now
);
DECLARE @dataseq INT = 1;
DECLARE @tableapv TABLE
(
apr_emp_id VARCHAR,
apr_start_date VARCHAR,
apr_end_date VARCHAR,
apr_appraisal_type VARCHAR,
apr_header_seq VARCHAR,
apr_seq VARCHAR,
apr_appraiser_id VARCHAR,
apr_appraiser_name VARCHAR,
apr_appraiser_type VARCHAR,
apr_kpi VARCHAR,
apr_qualification VARCHAR,
apr_appraiser_no VARCHAR
);
DECLARE @lvlascount INT = /*CAST;*/ 20;
WHILE @lvlascount > 0
BEGIN
IF @orglvl = '00'
AND @emphead IS NULL
BEGIN
SET @emphead =
(
SELECT TOP (1)
EmployeeID
FROM dbo.PHRPA0055
WHERE Position = @poshead
AND StartDate <= @now
AND EndDate >= @now
);
END;
IF @emphead IS NULL
BEGIN
SET @emphead =
(
SELECT TOP (1)
EmployeeID
FROM dbo.PHRPA0055
WHERE Position = @poshead
AND StartDate <= @now
AND EndDate >= @now
);
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 + 1, 1)
FROM dbo.PHRPA0048
WHERE EmployeeID = @empid_requester
AND AppraisalType = 'G'
) AS apr_header_seq,
CAST) 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) AS apr_appraiser_no;
SET @dataseq = @dataseq + 1;
END;
SET @org =
(
SELECT DISTINCT
ObjectID
FROM dbo.PHROM0002
WHERE RelationshipObject = @org
AND RelationshipClass = 'O'
AND ObjectClass = 'O'
AND RelationshipDirectory = 'A'
AND StartDate <= @now
AND EndDate >= @now
);
SET @orglvl =
(
SELECT OrganizationLevel
FROM dbo.PHROM0004
WHERE ObjectID = @org
AND StartDate <= @now
AND EndDate >= @now
);
SET @poshead =
(
SELECT ObjectID
FROM dbo.PHROM0001
WHERE ObjectID IN
(
SELECT RelationshipObject
FROM dbo.PHROM0002
WHERE ObjectID = @org
AND ObjectClass = 'O'
AND RelationshipClass = 'P' /*AND RelationshipType = '002'*/
AND StartDate <= @now
AND EndDate >= @now
)
AND ObjectClass = 'P'
AND PositionHead = '1'
AND StartDate <= @now
AND EndDate >= @now
);
SET @emphead =
(
SELECT RelationshipObject
FROM dbo.PHROM0002
WHERE ObjectID = @poshead
AND ObjectClass = 'P'
AND RelationshipClass = 'E'
AND StartDate <= @now
AND EndDate >= @now
);
SET @empheadname =
(
SELECT FullName
FROM dbo.PHRPA0001
WHERE EmployeeID = @emphead
AND StartDate <= @now
AND EndDate >= @now
);
SET @lvlascount = @lvlascount - 1;
END;
SELECT apr_appraiser_id AS DirectSupervisor1_EmployeeID
FROM @tableapv
WHERE apr_seq = '1';