BANK WOORI SAUDARA » Phase 2

??CREATE PROCEDURE [dbo].[PHRBUDGETIRREGULARPAYROLL]

(

	@EmployeeID VARCHAR(20)

	,@StartDate VARCHAR(20)

	,@EndDate VARCHAR(20)

	,@now 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 @TDataEmp TABLE

(

	StartDate VARCHAR(10)

	,EndDate VARCHAR(10)

	,EmployeeID VARCHAR(20)

	,WageType VARCHAR(20)

	,Valuation VARCHAR(10)

	,Number DECIMAL(18,2)

	,Amount DECIMAL(22,2)

	,Base VARCHAR(20)

)

DECLARE @TWTPayroll TABLE

(

	StartDate VARCHAR(10)

	,EndDate VARCHAR(10)

	,EmployeeID VARCHAR(20)

	,WageType VARCHAR(20)

	,Valuation VARCHAR(10)

	,Number DECIMAL(18,2)

	,Amount DECIMAL(22,2)

)

DECLARE @TWTBase TABLE

(

	EmployeeID VARCHAR(20)

	,WageTypeMD VARCHAR(20)

	,Base VARCHAR(20)

	,WageType VARCHAR(20)

	,Persen DECIMAL(18,10)

)

DECLARE @StartDateLast VARCHAR(10) = CONVERT(NVARCHAR(8), DATEADD(YEAR, -1, @StartDate), 112)

DECLARE @EndDateLast VARCHAR(10) = CONVERT(NVARCHAR(8), DATEADD(YEAR, -1, @EndDate), 112)

INSERT INTO @TDataEmp

SELECT DISTINCT PA5.StartDate ,PA5.EndDate ,PA5.EmployeeID ,PA5.WageType ,cu300.Valuation ,PA5.Number

				,CAST(REPLACE(ISNULL(dbo.GetPEN(PA5.Amount), '0'), ',', '.') AS DECIMAL(22,0)) AS Amount

				,cu300.Base

FROM PHRPA0005 AS PA5

LEFT JOIN dbo.PHRPYCU0300 AS cu300

	ON PA5.WageType = cu300.WageTypeDefinition

WHERE (PA5.StartDate BETWEEN @StartDateLast AND @EndDateLast) 

	AND (PA5.EmployeeID = @EmployeeID OR @EmployeeID = '') 

ORDER BY PA5.EmployeeID

INSERT INTO @TWTPayroll

SELECT DISTINCT TDataEmp.StartDate ,TDataEmp.EndDate ,TDataEmp.EmployeeID ,TDataEmp.WageType ,TDataEmp.Valuation ,TDataEmp.Number ,TDataEmp.Amount

FROM @TDataEmp AS TDataEmp

WHERE TDataEmp.Valuation = 'D'

INSERT INTO @TWTPayroll

SELECT DISTINCT TDataEmp.StartDate ,TDataEmp.EndDate ,TDataEmp.EmployeeID ,TDataEmp.WageType ,TDataEmp.Valuation ,TDataEmp.Number

				,TDataEmp.Number * cu303.Amount AS Amount

FROM @TDataEmp AS TDataEmp

LEFT JOIN PHRPYCU0303 AS cu303

	ON TDataEmp.WageType = cu303.WageType

	AND cu303.StartDate <= TDataEmp.EndDate AND cu303.EndDate >= TDataEmp.EndDate

WHERE TDataEmp.Valuation = 'I1'

INSERT INTO @TWTPayroll

SELECT DISTINCT TDataEmp.StartDate ,TDataEmp.EndDate ,TDataEmp.EmployeeID ,TDataEmp.WageType ,TDataEmp.Valuation ,TDataEmp.Number

				,TDataEmp.Number * cu304.Amount AS Amount

FROM @TDataEmp AS TDataEmp

LEFT JOIN dbo.PHRPA0003 AS PA3

	ON PA3.EmployeeID = TDataEmp.EmployeeID

	AND PA3.StartDate <= '20210101' AND PA3.EndDate >= '20210101'

LEFT JOIN dbo.PHRPYCU0304 AS cu304

	ON cu304.WageType = TDataEmp.WageType

	AND (cu304.PayrollType = '*' OR PA3.PayrollType = cu304.PayrollType)

	AND (cu304.PayrollArea = '*' OR PA3.PayrollArea = cu304.PayrollArea)

	AND (cu304.PayrollClass = '*' OR cu304.PayrollClass = PA3.PayrollClass)

	AND (cu304.PayrollGrade = '*' OR cu304.PayrollGrade = PA3.PayrollGrade)

	AND cu304.StartDate <= TDataEmp.EndDate AND cu304.EndDate >= TDataEmp.EndDate

WHERE TDataEmp.Valuation = 'I2'

INSERT INTO @TWTBase

SELECT DISTINCT TDataEmp.EmployeeID ,TDataEmp.WageType ,TDataEmp.Base, cu305.WageType 

			,CASE WHEN cu300.Percentage >= 100 THEN (cu300.Percentage / 100)  ELSE cu300.Percentage END Percentage

FROM @TDataEmp AS TDataEmp

LEFT JOIN PHRPYCU0305 AS cu305

	ON cu305.WageTypeCum = TDataEmp.Base

	AND cu305.StartDate <= TDataEmp.EndDate AND cu305.EndDate >= TDataEmp.EndDate

LEFT JOIN dbo.PHRPYCU0300 AS cu300

	ON cu300.WageTypeDefinition = TDataEmp.Base	

	AND cu300.StartDate <= TDataEmp.EndDate AND cu300.EndDate >= TDataEmp.EndDate

WHERE TDataEmp.Valuation = 'I3' AND TDataEmp.Base <> ''