BANK KALTENG

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