USE [MinovaES_Arita_Dev]
GO
/****** Object:  StoredProcedure [dbo].[PMSAPPRAISER1]    Script Date: 02/02/2023 11.26.05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PMSAPPRAISER1] 
(
	@EmployeeID VARCHAr(20)
	,@EndDate VARCHAR(20)
)
AS 

--DECLARE @EmployeeID VARCHAr(20) = '00000074'
--DECLARE @EndDate VARCHAR(20) = '20221231'

DECLARE @Pos VARCHAR(20)
DECLARE @Org VARCHAR(20)
DECLARE @now VARCHAR(20) = CONVERT(VARCHAR, GETDATE(), 112) 
SELECT DISTINCT @Org = Organization, @Pos = Position FROM dbo.PHRPA0002 WHERE EmployeeID = @EmployeeID
SELECT DISTINCT @EmployeeID AS EmployeeID, '' AS AppraisalType, '' AS HeaderSequence, '' AS Sequence,
				pa2.EmployeeID AS AppraisalD, 'DS' AS AppraiserType, 100 AS KPIWeight, 100 AS QualificationWeight, 'App1' AS AppraiserNo,
				pa1.FullName AS AppraiserName
FROM dbo.PHROM0002  AS om2
LEFT JOIN dbo.PHROM0001 AS om1
	ON om1.ObjectClass = 'P'
	AND om1.ObjectID = om2.RelationshipObject
	AND om1.StartDate <= @now AND om1.EndDate >= @now 
LEFT JOIN dbo.PHRPA0002 AS pa2
	ON pa2.Position = om2.RelationshipObject
	AND pa2.StartDate <= @now AND pa2.EndDate >= @now 
LEFT JOIN PHRPA0001 AS pa1
	On pa1.EmployeeID = pa2.EmployeeID
	AND pa1.StartDate <= @now AND pa1.EndDate >= @now 
WHERE om2.ObjectClass = 'O' AND om2.RelationshipClass = 'P' AND om2.ObjectID = @Org AND om1.PositionHead = '1'
	AND om2.StartDate <= @now AND om2.EndDate >= @now