PT ARITA

??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