|
1
|
ALTER PROCEDURE [dbo].[CHRPYTPIDUMMYPA0005]
|
|
2
|
(
|
|
3
|
@EmployeeID VARCHAR(20),
|
|
4
|
@StartDate VARCHAR(20),
|
|
5
|
@EndDate VARCHAR(20)
|
|
6
|
)
|
|
7
|
AS
|
|
8
|
|
|
9
|
--DECLARE @EmployeeID VARCHAR(20) = '70000339'
|
|
10
|
--DECLARE @StartDate VARCHAR(20) = '20220601'
|
|
11
|
--DECLARE @EndDate VARCHAR(20) = '20220630'
|
|
12
|
|
|
13
|
DECLARE @TBefore TABLE(
|
|
14
|
[StartDate] [VARCHAR](8) NOT NULL,
|
|
15
|
[EndDate] [VARCHAR](8) NOT NULL,
|
|
16
|
[EmployeeID] [VARCHAR](18) NOT NULL,
|
|
17
|
[WageType] [VARCHAR](4) NOT NULL,
|
|
18
|
[Sequence] [INT] NOT NULL,
|
|
19
|
[Number] [DECIMAL](18, 5) NULL,
|
|
20
|
[Amount] [VARCHAR](500) NULL,
|
|
21
|
[Currency] [VARCHAR](4) NULL,
|
|
22
|
[Flag] [VARCHAR](4) NULL,
|
|
23
|
[Overwrite] [VARCHAR](4) NULL,
|
|
24
|
[Notes] [VARCHAR](500) NULL,
|
|
25
|
[CreateBy] [VARCHAR](18) NULL,
|
|
26
|
[CreateDate] [VARCHAR](14) NULL,
|
|
27
|
[ChangeBy] [VARCHAR](18) NULL,
|
|
28
|
[ChangeDate] [VARCHAR](14) NULL
|
|
29
|
)
|
|
30
|
|
|
31
|
DECLARE @TResult TABLE(
|
|
32
|
[StartDate] [VARCHAR](8) NOT NULL,
|
|
33
|
[EndDate] [VARCHAR](8) NOT NULL,
|
|
34
|
[EmployeeID] [VARCHAR](18) NOT NULL,
|
|
35
|
[WageType] [VARCHAR](4) NOT NULL,
|
|
36
|
[Sequence] [INT] NOT NULL,
|
|
37
|
[Number] [DECIMAL](18, 5) NULL,
|
|
38
|
[Amount] [VARCHAR](500) NULL,
|
|
39
|
[Currency] [VARCHAR](4) NULL,
|
|
40
|
[Flag] [VARCHAR](4) NULL,
|
|
41
|
[Overwrite] [VARCHAR](4) NULL,
|
|
42
|
[Notes] [VARCHAR](500) NULL,
|
|
43
|
[CreateBy] [VARCHAR](18) NULL,
|
|
44
|
[CreateDate] [VARCHAR](14) NULL,
|
|
45
|
[ChangeBy] [VARCHAR](18) NULL,
|
|
46
|
[ChangeDate] [VARCHAR](14) NULL
|
|
47
|
)
|
|
48
|
|
|
49
|
INSERT INTO @TBefore
|
|
50
|
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,
|
|
51
|
a.Currency, 'I', a.Overwrite, a.Notes, 'dummy', @StartDate+'010101', 'dummy', @StartDate+'010101'
|
|
52
|
FROM PHRPA0005 AS a
|
|
53
|
LEFT JOIN PHRPYCU0300 AS b ON a.WageType = b.WageTypeDefinition
|
|
54
|
WHERE a.EmployeeID = @EmployeeID and (a.StartDate BETWEEN LEFT(@StartDate,4) +'0101' AND @StartDate) AND b.TaxSubsidy <> ''
|
|
55
|
|
|
56
|
DECLARE @c_wt varchar(8)
|
|
57
|
DECLARE cur_pa5 CURSOR FOR SELECT DISTINCT WageType FROM @TBefore
|
|
58
|
OPEN cur_pa5
|
|
59
|
FETCH cur_pa5 INTO @c_wt
|
|
60
|
WHILE @@Fetch_Status = 0
|
|
61
|
BEGIN
|
|
62
|
DECLARE @CountEmp DECIMAL(18,0)
|
|
63
|
SELECT @CountEmp = COUNT(z.EmployeeID) FROM dbo.PHRPA0005 AS z
|
|
64
|
WHERE z.EmployeeID = @EmployeeID AND z.StartDate BETWEEN @StartDate AND @EndDate AND z.WageType = @c_wt
|
|
65
|
|
|
66
|
IF(@CountEmp = 0)
|
|
67
|
BEGIN
|
|
68
|
INSERT INTO @TResult
|
|
69
|
SELECT * FROM @TBefore WHERE WageType = @c_wt
|
|
70
|
END
|
|
71
|
FETCH cur_pa5 INTO @c_wt
|
|
72
|
END
|
|
73
|
CLOSE cur_pa5
|
|
74
|
DEALLOCATE cur_pa5
|
|
75
|
|
|
76
|
INSERT INTO @TResult
|
|
77
|
SELECT *
|
|
78
|
FROM dbo.PHRPA0005
|
|
79
|
WHERE EmployeeID = @EmployeeID and (StartDate BETWEEN @StartDate AND @EndDate)
|
|
80
|
|
|
81
|
IF( (SELECT DISTINCT COUNT(EmployeeID) FROM @TResult) = 0)
|
|
82
|
BEGIN
|
|
83
|
INSERT INTO @TResult
|
|
84
|
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,
|
|
85
|
'IDR', 'I', '', '', 'dummy', @StartDate+'010101', 'dummy', @StartDate+'010101'
|
|
86
|
INSERT INTO @TResult
|
|
87
|
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,
|
|
88
|
'IDR', 'D', '', '', 'dummy', @StartDate+'010101', 'dummy', @StartDate+'010101'
|
|
89
|
SELECT * FROM @TResult
|
|
90
|
END
|
|
91
|
ELSE
|
|
92
|
BEGIN
|
|
93
|
INSERT INTO @TResult
|
|
94
|
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,
|
|
95
|
'IDR', 'D', '', '', 'dummy', @StartDate+'010101', 'dummy', @StartDate+'010101'
|
|
96
|
SELECT * FROM @TResult
|
|
97
|
END
|