|
1
|
DECLARE @landscape VARCHAR(10)='100'
|
|
2
|
DECLARE @companyid VARCHAR(20)='1000'
|
|
3
|
DECLARE @emp_subst VARCHAR(10)='200'
|
|
4
|
DECLARE @emp_suben VARCHAR(10)='200'
|
|
5
|
DECLARE @emp_statst VARCHAR(10)='01'
|
|
6
|
DECLARE @emp_staten VARCHAR(10)='01'
|
|
7
|
DECLARE @emp_areast VARCHAR(10)=''
|
|
8
|
DECLARE @emp_areaen VARCHAR(10)=''
|
|
9
|
DECLARE @pygrst VARCHAR(10)=''
|
|
10
|
DECLARE @pygren VARCHAR(10)=''
|
|
11
|
DECLARE @emp_idst VARCHAR(10)=''
|
|
12
|
DECLARE @emp_iden VARCHAR(10)=''
|
|
13
|
DECLARE @emp_typest VARCHAR(10)=''
|
|
14
|
DECLARE @emp_typeen VARCHAR(10)=''
|
|
15
|
DECLARE @organisasi VARCHAR(20)=''
|
|
16
|
DECLARE @movtype_st VARCHAR(10)=''
|
|
17
|
DECLARE @movtype_en VARCHAR(10)=''
|
|
18
|
DECLARE @reason_st VARCHAR(10)=''
|
|
19
|
DECLARE @reason_en VARCHAR(10)=''
|
|
20
|
DECLARE @subtype_st VARCHAR(10)=''
|
|
21
|
DECLARE @subtype_en VARCHAR(10)=''
|
|
22
|
DECLARE @startdate VARCHAR(8)=''
|
|
23
|
DECLARE @enddate VARCHAR(8)=''
|
|
24
|
DECLARE @datatype_st VARCHAR(5)=''
|
|
25
|
DECLARE @datatype_en VARCHAR(5)=''
|
|
26
|
|
|
27
|
--SET @landscape = '100'
|
|
28
|
--SET @companyid = '1000'
|
|
29
|
--SET @emp_subst = ''
|
|
30
|
--SET @emp_suben = ''
|
|
31
|
--SET @emp_statst = ''
|
|
32
|
--SET @emp_staten = ''
|
|
33
|
--SET @emp_areast = ''
|
|
34
|
--SET @emp_areaen = ''
|
|
35
|
--SET @pygrst = ''
|
|
36
|
--SET @pygren = ''
|
|
37
|
--SET @emp_idst = '20170077'--00000002'
|
|
38
|
--SET @emp_iden = '20170077'--00000006'
|
|
39
|
--SET @emp_typest = ''
|
|
40
|
--SET @emp_typeen = ''
|
|
41
|
--SET @organisasi = ''
|
|
42
|
--SET @movtype_st = ''
|
|
43
|
--SET @movtype_en = ''
|
|
44
|
--SET @reason_st = ''
|
|
45
|
--SET @reason_en = ''
|
|
46
|
--SET @subtype_st = ''
|
|
47
|
--SET @subtype_en = ''
|
|
48
|
--SET @startdate = ''--'20100803'
|
|
49
|
--SET @enddate = ''--'20101212'
|
|
50
|
--SET @datatype_en = ''
|
|
51
|
--SET @datatype_st = ''
|
|
52
|
|
|
53
|
DECLARE @now VARCHAR(10)
|
|
54
|
|
|
55
|
SET @now = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd')
|
|
56
|
SELECT DISTINCT
|
|
57
|
m1.landscape ,
|
|
58
|
DENSE_RANK() OVER ( ORDER BY m1.emp_id ) AS GroupSeq ,
|
|
59
|
m1.emp_id ,
|
|
60
|
m10.start_date ,
|
|
61
|
m10.end_date ,
|
|
62
|
ROW_NUMBER() OVER ( PARTITION BY m1.emp_id ORDER BY m1.emp_id ASC ) AS seq ,
|
|
63
|
--m10.seq ,
|
|
64
|
m10.data_type ,
|
|
65
|
m10.description ,
|
|
66
|
m10.responsible ,
|
|
67
|
m10.amount ,
|
|
68
|
d1.description AS datatype_desc ,
|
|
69
|
m1.full_name ,
|
|
70
|
o1.emp_subarea_description ,
|
|
71
|
org.description AS organization ,
|
|
72
|
pos.description AS position
|
|
73
|
FROM hr_md_emp_md0001 m1
|
|
74
|
INNER JOIN ( SELECT *
|
|
75
|
FROM hr_md_emp_md0010
|
|
76
|
WHERE ( landscape = @landscape )
|
|
77
|
) AS m10 ON ( m1.landscape = m10.landscape )
|
|
78
|
AND ( m1.emp_id = m10.emp_id )
|
|
79
|
LEFT OUTER JOIN ( SELECT *
|
|
80
|
FROM hr_md_emp_md0002
|
|
81
|
WHERE ( landscape = @landscape )
|
|
82
|
AND ( start_date <= @now )
|
|
83
|
AND ( end_date >= @now )
|
|
84
|
) AS m2 ON ( m1.landscape = m2.landscape )
|
|
85
|
AND ( m1.emp_id = m2.emp_id )
|
|
86
|
LEFT OUTER JOIN base_cust_ref_emp_office AS o1 ON ( m2.landscape = o1.landscape )
|
|
87
|
AND ( m2.employee_office = o1.emp_subarea )
|
|
88
|
LEFT OUTER JOIN dbo.base_cust_ref_data_type AS d1 ON ( m2.landscape = d1.landscape )
|
|
89
|
AND ( m10.data_type = d1.code )
|
|
90
|
LEFT OUTER JOIN hr_md_orm_object AS org ON ( org.landscape = m2.landscape )
|
|
91
|
AND ( org.object = m2.organization )
|
|
92
|
AND ( org.class = 'O' )
|
|
93
|
AND ( org.start_date <= @now )
|
|
94
|
AND ( org.end_date >= @now )
|
|
95
|
LEFT OUTER JOIN hr_md_orm_object AS pos ON ( pos.landscape = m2.landscape )
|
|
96
|
AND ( pos.object = m2.position )
|
|
97
|
AND ( pos.class = 'P' )
|
|
98
|
AND ( pos.start_date <= @now )
|
|
99
|
AND ( pos.end_date >= @now )
|
|
100
|
WHERE ( m1.landscape = @landscape )
|
|
101
|
AND ( m1.start_date <= @now )
|
|
102
|
AND ( m1.end_date >= @now )
|
|
103
|
AND ( m2.emp_id >= @emp_idst
|
|
104
|
OR @emp_idst = ''
|
|
105
|
)
|
|
106
|
AND ( m2.emp_id <= @emp_iden
|
|
107
|
OR @emp_iden = ''
|
|
108
|
)
|
|
109
|
AND ( m2.company_id = @companyid
|
|
110
|
OR @companyid = ''
|
|
111
|
)
|
|
112
|
AND m2.employee_status IN (
|
|
113
|
CASE WHEN ISNULL(@emp_statst, '') = '' THEN m2.employee_status
|
|
114
|
ELSE @emp_statst
|
|
115
|
END, CASE WHEN ISNULL(@emp_staten, '') = '' THEN m2.employee_status
|
|
116
|
ELSE @emp_staten
|
|
117
|
END )
|
|
118
|
AND m2.employee_area IN (
|
|
119
|
CASE WHEN ISNULL(@emp_areast, '') = '' THEN m2.employee_area
|
|
120
|
ELSE @emp_areast
|
|
121
|
END, CASE WHEN ISNULL(@emp_areaen, '') = '' THEN m2.employee_area
|
|
122
|
ELSE @emp_areaen
|
|
123
|
END )
|
|
124
|
AND m2.employee_office IN (
|
|
125
|
CASE WHEN ISNULL(@emp_subst, '') = '' THEN m2.employee_office
|
|
126
|
ELSE @emp_subst
|
|
127
|
END, CASE WHEN ISNULL(@emp_suben, '') = '' THEN m2.employee_office
|
|
128
|
ELSE @emp_suben
|
|
129
|
END )
|
|
130
|
AND m2.employee_type IN (
|
|
131
|
CASE WHEN ISNULL(@emp_typest, '') = '' THEN m2.employee_type
|
|
132
|
ELSE @emp_typest
|
|
133
|
END, CASE WHEN ISNULL(@emp_typeen, '') = '' THEN m2.employee_type
|
|
134
|
ELSE @emp_typeen
|
|
135
|
END )
|
|
136
|
AND m2.payroll_group IN (
|
|
137
|
CASE WHEN ISNULL(@pygrst, '') = '' THEN m2.payroll_group
|
|
138
|
ELSE @pygrst
|
|
139
|
END, CASE WHEN ISNULL(@pygren, '') = '' THEN m2.payroll_group
|
|
140
|
ELSE @pygren
|
|
141
|
END )
|
|
142
|
AND m10.data_type IN (
|
|
143
|
CASE WHEN ISNULL(@datatype_st, '') = '' THEN m10.data_type
|
|
144
|
ELSE @datatype_st
|
|
145
|
END, CASE WHEN ISNULL(@datatype_en, '') = '' THEN m10.data_type
|
|
146
|
ELSE @datatype_en
|
|
147
|
END )
|
|
148
|
AND ( m10.start_date >= @startdate
|
|
149
|
OR @startdate = ''
|
|
150
|
)
|
|
151
|
AND ( m10.start_date <= @enddate
|
|
152
|
OR @enddate = ''
|
|
153
|
)
|
