USE [MinovaES_Bank_Kalteng_Dev]
GO
/****** Object:  StoredProcedure [dbo].[PHRBUDGETTRANSPAYROLL]    Script Date: 07/07/2022 11.39.51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PHRBUDGETTRANSPAYROLL]
(
	@EmployeeID VARCHAR(20)
	,@StartDate VARCHAR(20)
	,@EndDate VARCHAR(20)
	,@now VARCHAR(20)
	,@WageType VARCHAR(20)
)
AS

--DECLARE @EmployeeID VARCHAR(20) = '10000192' ---- I1, I2 BBGProd 1438 - 00000276 - 2020 ;  I3 TPIDev 1442 - 00000474 - 2021; BKTDev 103 - 10000164 - 2021; BKTDev 103 - 10000192 - 2023
--DECLARE @StartDate VARCHAR(20) = '20230101' 
--DECLARE @EndDate VARCHAR(20) = '20231231'
--DECLARE @now  VARCHAR(20) = '20220616'
--DECLARE @WageType VARCHAR(20) = 'MT'

DECLARE @StartDateLast VARCHAR(20) = CONVERT(NVARCHAR(8), DATEADD(YEAR, -1, @StartDate), 112)
DECLARE @EndDateLast VARCHAR(20) = CONVERT(NVARCHAR(8), DATEADD(YEAR, -1, @EndDate), 112)
DECLARE @RunPeriodYear VARCHAR(20) = LEFT(@EndDateLast,4)
DECLARE @RunPeriodMonth VARCHAR(20) = SUBSTRING(@EndDateLast,5,2)

DECLARE @TWTPayroll TABLE
(
	EmployeeID VARCHAR(20)
	,WageType VARCHAR(20)
	,Number DECIMAL(22,0)
	,Amount DECIMAL(22,2)
)

--DECLARE @TWTResult TABLE
--(
--	EmployeeID VARCHAR(20)
--	,WageType VARCHAR(20)
--	,Number DECIMAL(22,0)
--	,Amount DECIMAL(22,2)
--	,Valuation VARCHAR(20)
--)

INSERT INTO @TWTPayroll
SELECT DISTINCT tr301.EmployeeID ,tr301.WageType ,tr301.Number
				,CONVERT(DECIMAL(22,2),SUM(CAST(REPLACE(ISNULL(dbo.GetPEN(tr301.Amount), '0'), ',', '.') AS DECIMAL(22,0))/12)) AS Amount
FROM dbo.PHRPYTR0301 AS tr301
WHERE (tr301.EmployeeID = @EmployeeID OR @EmployeeID = '')
	AND (tr301.RunPeriodYear = tr301.PayPeriodYear) AND (tr301.RunPeriodMonth = tr301.PayPeriodMonth)
	AND tr301.RunPeriodYear = @RunPeriodYear
	AND tr301.WageType = @WageType
GROUP BY tr301.EmployeeID ,tr301.WageType ,tr301.Number

SELECT DISTINCT TWTPayroll.* ,'D' AS Valuation
FROM @TWTPayroll AS TWTPayroll
LEFT JOIN dbo.PHRPYCU0300 AS cu300
	ON TWTPayroll.WageType = cu300.WageTypeDefinition