BANK WOORI SAUDARA » Phase 2

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