ALTER PROCEDURE [dbo].[CHRPYTPIDUMMYPA0005]
(
	@EmployeeID VARCHAR(20),
	@StartDate VARCHAR(20),
	@EndDate VARCHAR(20)
)
AS

--DECLARE @EmployeeID VARCHAR(20) = '70000339'
--DECLARE @StartDate VARCHAR(20) = '20220601'
--DECLARE @EndDate VARCHAR(20) = '20220630'

DECLARE @TBefore TABLE(
	[StartDate] [VARCHAR](8) NOT NULL,
	[EndDate] [VARCHAR](8) NOT NULL,
	[EmployeeID] [VARCHAR](18) NOT NULL,
	[WageType] [VARCHAR](4) NOT NULL,
	[Sequence] [INT] NOT NULL,
	[Number] [DECIMAL](18, 5) NULL,
	[Amount] [VARCHAR](500) NULL,
	[Currency] [VARCHAR](4) NULL,
	[Flag] [VARCHAR](4) NULL,
	[Overwrite] [VARCHAR](4) NULL,
	[Notes] [VARCHAR](500) NULL,
	[CreateBy] [VARCHAR](18) NULL,
	[CreateDate] [VARCHAR](14) NULL,
	[ChangeBy] [VARCHAR](18) NULL,
	[ChangeDate] [VARCHAR](14) NULL
)

DECLARE @TResult TABLE(
	[StartDate] [VARCHAR](8) NOT NULL,
	[EndDate] [VARCHAR](8) NOT NULL,
	[EmployeeID] [VARCHAR](18) NOT NULL,
	[WageType] [VARCHAR](4) NOT NULL,
	[Sequence] [INT] NOT NULL,
	[Number] [DECIMAL](18, 5) NULL,
	[Amount] [VARCHAR](500) NULL,
	[Currency] [VARCHAR](4) NULL,
	[Flag] [VARCHAR](4) NULL,
	[Overwrite] [VARCHAR](4) NULL,
	[Notes] [VARCHAR](500) NULL,
	[CreateBy] [VARCHAR](18) NULL,
	[CreateDate] [VARCHAR](14) NULL,
	[ChangeBy] [VARCHAR](18) NULL,
	[ChangeDate] [VARCHAR](14) NULL
)

INSERT INTO @TBefore
SELECT DISTINCT @StartDate, @StartDate, a.EmployeeID, a.WageType, 1, 0, '74-246-71-170-194-200-53-180-242-222-105-222-98-60-120-253' AS Amount,
				a.Currency, 'I', a.Overwrite, a.Notes, 'dummy', @StartDate+'010101', 'dummy', @StartDate+'010101'
FROM PHRPA0005 AS a
LEFT JOIN PHRPYCU0300 AS b ON a.WageType = b.WageTypeDefinition 
WHERE a.EmployeeID = @EmployeeID and (a.StartDate BETWEEN LEFT(@StartDate,4) +'0101' AND @StartDate) AND b.TaxSubsidy <> ''

DECLARE @c_wt varchar(8)
DECLARE cur_pa5 CURSOR FOR SELECT DISTINCT WageType FROM @TBefore
OPEN cur_pa5
FETCH cur_pa5 INTO @c_wt
WHILE @@Fetch_Status = 0
	BEGIN
		DECLARE @CountEmp DECIMAL(18,0)
		SELECT @CountEmp = COUNT(z.EmployeeID) FROM dbo.PHRPA0005 AS z
		WHERE z.EmployeeID = @EmployeeID AND z.StartDate BETWEEN @StartDate AND @EndDate AND z.WageType = @c_wt

		IF(@CountEmp = 0)
		BEGIN
			INSERT INTO	@TResult
			SELECT * FROM @TBefore WHERE WageType = @c_wt
		END
	FETCH cur_pa5 INTO @c_wt  
	END
CLOSE cur_pa5
DEALLOCATE cur_pa5

INSERT INTO	@TResult
SELECT * 
FROM dbo.PHRPA0005 
WHERE EmployeeID = @EmployeeID and (StartDate BETWEEN @StartDate AND @EndDate)

IF( (SELECT DISTINCT COUNT(EmployeeID) FROM @TResult) = 0)
BEGIN
	INSERT INTO @TResult
	SELECT DISTINCT @StartDate, @StartDate, @EmployeeID, '1507', 1, 0, '74-246-71-170-194-200-53-180-242-222-105-222-98-60-120-253' AS Amount,
				'IDR', 'I', '', '', 'dummy', @StartDate+'010101', 'dummy', @StartDate+'010101'
	INSERT INTO @TResult	
	SELECT DISTINCT @StartDate, @StartDate, @EmployeeID, '1510', 1, 0, '74-246-71-170-194-200-53-180-242-222-105-222-98-60-120-253' AS Amount,
				'IDR', 'D', '', '', 'dummy', @StartDate+'010101', 'dummy', @StartDate+'010101'
	SELECT * FROM @TResult
END
ELSE
BEGIN
	INSERT INTO @TResult
	SELECT DISTINCT @StartDate, @StartDate, @EmployeeID, '1510', 1, 0, '74-246-71-170-194-200-53-180-242-222-105-222-98-60-120-253' AS Amount,
				'IDR', 'D', '', '', 'dummy', @StartDate+'010101', 'dummy', @StartDate+'010101'
	SELECT * FROM @TResult
END