ALTER PROCEDURE [dbo].[PRPTPMSFORMFA]
(		
	@EmployeeID VARCHAR(8),
	@AppraisalType VARCHAR(20),
	@Year VARCHAR(8), 
	@MenuID VARCHAR(10),
    @UserID VARCHAR(10),
	@HeaderSeq VARCHAR(10)
)
AS

	--DECLARE @EmployeeID varchar(8) = '00000143'
	--DECLARE @AppraisalType varchar(8) = 'F'
	--DECLARE @Year varchar(8) = '2025'
	--DECLARE @MenuID VARCHAR(10) = 'PMR01'
	--DECLARE @UserID VARCHAR(10) = 'tri'
	--DECLARE @HeaderSeq VARCHAR(10) = '7'
	
	DECLARE @startdate VARCHAR(12) = @Year + '0101'
	DECLARE @enddate VARCHAR(12) = @Year + '1231'
	
	------------------ // Get Otorisasi //---------------

	DECLARE @TableResult TABLE ( EmployeeID VARCHAR(20) )

	INSERT  INTO @TableResult
    EXEC dbo.GETEMPIDAUTHBYMENU @MenuID = @MenuID, -- varchar(max)
        @UserID = @UserID
 
------------------ // End Get Otorisasi //---------------
	
	SELECT @EmployeeID = CASE WHEN @EmployeeID = '-' THEN NULL ELSE @EmployeeID END	
	
	DECLARE @Organization VARCHAR(8), 
			@Position VARCHAR(8), 
			@SuperVisorName VARCHAR(50), 
			@SupervisorInOrg VARCHAR(50),
			@JabatanOrPangkat VARCHAR(50),
			@DivisiOrCabang VARCHAR(50),
			@Directorate VARCHAR(50);
			
	SELECT TOP 1 @Organization = x.Organization , 
				@Position = x.Position
	FROM dbo.PHRPA0002 AS x 
	WHERE cast(x.StartDate as bigint) <= @startdate and CAST(x.EndDate as bigint) >= @enddate and
	x.EmployeeID = @EmployeeID 
	ORDER BY CAST(x.EndDate AS BIGINT) DESC

	DECLARE @SPVID VARCHAR(50);

	SET @SPVID = dbo.GetEmpSupervisor(@EmployeeID)
	select TOP 1 @SuperVisorName = UPPER(FullName) from dbo.PHRPA0001 where EmployeeID =  @SPVID;
	
	DECLARE @SPVIDINORG VARCHAR(50);
	SET @SPVIDINORG = dbo.GetSupervisorInOrg(@Organization, @startdate)
	
	select TOP 1 @SupervisorInOrg = UPPER(FullName) from dbo.PHRPA0001 where EmployeeID = @SPVIDINORG
	
	SET @JabatanOrPangkat = dbo.Gethr_md_orm_object_description(@Position, @startdate, 'P')
	SET @DivisiOrCabang = dbo.Gethr_md_orm_object_description(@Organization, @startdate, 'O');
	SELECT TOP 1 @Directorate = lv1_descr FROM dbo.GetOrganisasiTreeByLevel(@Organization, @startdate) AS x;

	DECLARE @t_org TABLE(
		org_id VARCHAR(20)
		,org_name VARCHAR(500)
		,dir_id VARCHAR(200)
		,dir_name VARCHAR(500)
		,div_id VARCHAR(20)
		,div_name VARCHAR(500)
		,cab_id VARCHAR(20)
		,cab_name VARCHAR(500)
	)
	INSERT INTO @t_org
	select org_id, org_name, org_id_01, org_name_01, org_id_04, org_name_04, org_id_12, org_name_12 from dbo.CBKT_GetOrgInOrgWide('00000001', @enddate)


	SELECT	DISTINCT _md01.EmployeeID, 
				_md01.FullName, 
				ISNULL(@SuperVisorName,'') AS Supervisor,
				ISNULL(@SupervisorInOrg,'') as SupervisorInOrg,
				--ISNULL(@JabatanOrPangkat,'') AS JabatanOrPangkat, 
				--ISNULL(@DivisiOrCabang,'') AS DivisiOrCabang, 
				posom1.ObjectDescription AS JabatanOrPangkat,
				CASE WHEN torg.div_name <> '' THEN torg.div_name ELSE CASE WHEN torg.cab_name <> '' THEN torg.cab_name ELSE '' END END AS DivisiOrCabang,
				--ISNULL(@Directorate,'') AS Directorate
				torg.dir_name AS Directorate
				,dbo.fn_formatdatetime(@startdate, 'dd/mm/yyyy') AS PeriodeStart
				,dbo.fn_formatdatetime(@enddate, 'dd/mm/yyyy') AS PeriodeEnd
				,CASE WHEN pa48_app1.AppraiserName <> '' THEN pa48_app1.AppraiserName ELSE app1.FullName END AS Appraisal1Name
				,CASE WHEN pa48_app2.AppraiserName <> '' THEN pa48_app2.AppraiserName ELSE app2.FullName END AS Appraisal2Name
				,pa2.Position
				,posom1.ObjectDescription AS PosDesc
				,posom1.PositionHead AS PosHead
				,_47.Sequence AS PeriodSeq
				,_47.PeriodFrom
				,_47.PeriodTo
				,dbo.fn_formatdatetime(@Year+_47.PeriodFrom+'01', 'dd/mm/yyyy') AS PeriodDescDesc
				,dbo.fn_formatdatetime(@Year+_47.PeriodTo+'01', 'dd/mm/yyyy') AS PeriodToDesc
				,_47.AppraisalType
				--,torg.*
	FROM	dbo.PHRPA0001 AS _md01 WITH (NOLOCK)
	INNER JOIN @TableResult AS tr 
		ON tr.EmployeeID = [_md01].EmployeeID
	LEFT JOIN PHRPA0047 AS _47 
		ON _md01.EmployeeID = _47.EmployeeID
	LEFT JOIN PHRPA0048 pa48_app1 
		ON _47.EmployeeID = pa48_app1.EmployeeID
		--AND pa48_app1.AppraiserNo = 'App1' 
		AND pa48_app1.Sequence = '1'
		AND pa48_app1.HeaderSequence = _47.Sequence
	LEFT JOIN dbo.PHRPA0001 AS app1
		ON app1.EmployeeID = pa48_app1.AppraisalD
		AND app1.StartDate <= @enddate AND app1.EndDate >= @enddate
	LEFT JOIN PHRPA0048 pa48_app2 
		ON _47.EmployeeID = pa48_app2.EmployeeID
		--AND pa48_app2.AppraiserNo = 'App2' 
		AND pa48_app2.Sequence = '2'
		AND pa48_app2.HeaderSequence = _47.Sequence
	LEFT JOIN dbo.PHRPA0001 AS app2
		ON app2.EmployeeID = pa48_app2.AppraisalD
		AND app2.StartDate <= @enddate AND app2.EndDate >= @enddate
	LEFT JOIN dbo.PHRPA0002 AS pa2
		ON _md01.EmployeeID = pa2.EmployeeID
		AND pa2.StartDate <= @enddate AND pa2.EndDate >= @enddate
	LEFT JOIN dbo.PHROM0001 AS posom1
		ON posom1.ObjectID = pa2.Position
		AND posom1.ObjectClass = 'P'
		AND posom1.StartDate <= @enddate AND posom1.EndDate >= @enddate
	LEFT JOIN @t_org AS torg
		ON torg.org_id = pa2.Organization
	WHERE _md01.EmployeeID = @EmployeeID		
		AND _md01.StartDate <= @startdate
		AND _md01.EndDate >= @startdate
		AND LEFT(_47.StartDate, 4) = @Year		
		AND _47.AppraisalType = @AppraisalType
		AND _47.Sequence = @HeaderSeq
	ORDER BY 	_md01.EmployeeID
		
	


  

 
