|
1
|
ALTER PROCEDURE [dbo].[rp_trPS00] -- exec rp_trPS00 '100', '201011', '00000324', '0', '', 'scriberion', 'triadblank'
|
|
2
|
-- Add the parameters for the stored procedure here
|
|
3
|
@landscape VARCHAR(3),
|
|
4
|
@payperiod VARCHAR(6),
|
|
5
|
@employee_id VARCHAR(8),
|
|
6
|
@running VARCHAR(2),
|
|
7
|
@paytype VARCHAR(2),
|
|
8
|
@key1 AS NVARCHAR(max),
|
|
9
|
@key2 AS NVARCHAR(max)
|
|
10
|
WITH ENCRYPTION
|
|
11
|
AS
|
|
12
|
BEGIN
|
|
13
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
14
|
-- interfering with SELECT statements.
|
|
15
|
SET NOCOUNT ON;
|
|
16
|
|
|
17
|
--DECLARE @landscape VARCHAR(3) = '100'
|
|
18
|
--DECLARE @payperiod VARCHAR(6) = '202405'
|
|
19
|
--DECLARE @employee_id VARCHAR(8) = '20130636'
|
|
20
|
--DECLARE @running VARCHAR(2) = '1'
|
|
21
|
--DECLARE @paytype VARCHAR(2) = ''
|
|
22
|
--DECLARE @key1 AS NVARCHAR(max) = 'scriberion'
|
|
23
|
--DECLARE @key2 AS NVARCHAR(max) = 'triadblank'
|
|
24
|
|
|
25
|
-- Insert statements for procedure here
|
|
26
|
DECLARE @companycode AS VARCHAR(50)
|
|
27
|
DECLARE @companyname AS NVARCHAR(50)
|
|
28
|
DECLARE @companylogo AS VARBINARY(MAX)
|
|
29
|
DECLARE @paymonth AS NVARCHAR(2)
|
|
30
|
DECLARE @payyear AS NVARCHAR(4)
|
|
31
|
DECLARE @begda AS NVARCHAR(8)
|
|
32
|
DECLARE @endda AS NVARCHAR(8)
|
|
33
|
|
|
34
|
SET @paymonth = RIGHT(@payperiod,2)
|
|
35
|
SET @payyear = LEFT(@payperiod,4)
|
|
36
|
SET @begda = @payperiod + '01'
|
|
37
|
SET @endda = convert(NVARCHAR(8),dateadd(d,-1, dateadd(m, 1, @begda)), 112)
|
|
38
|
|
|
39
|
DECLARE @payslipsigned AS NVARCHAR(50)
|
|
40
|
|
|
41
|
SELECT TOP(1) @payslipsigned = [value]
|
|
42
|
FROM hr_cu0307
|
|
43
|
WHERE (landscape = @landscape) AND (pay_param = 'PAYSLIPSIGNED') AND
|
|
44
|
(start_date <= @endda AND end_date >= @endda)
|
|
45
|
|
|
46
|
IF (@running = '1')
|
|
47
|
BEGIN
|
|
48
|
SELECT TOP(1) @companycode = company_id
|
|
49
|
FROM hr_tr0300
|
|
50
|
WHERE (landscape = @landscape) AND (employee_id = @employee_id) AND
|
|
51
|
(pay_period_month = @paymonth) AND (pay_period_year = @payyear) AND
|
|
52
|
(run_period_month = @paymonth) AND (run_period_year = @payyear)
|
|
53
|
END
|
|
54
|
ELSE
|
|
55
|
BEGIN
|
|
56
|
SELECT TOP(1) @companycode = company_id
|
|
57
|
FROM hr_tr0300_sim
|
|
58
|
WHERE (landscape = @landscape) AND (employee_id = @employee_id) AND
|
|
59
|
(pay_period_month = @paymonth) AND (pay_period_year = @payyear) AND
|
|
60
|
(run_period_month = @paymonth) AND (run_period_year = @payyear)
|
|
61
|
END
|
|
62
|
|
|
63
|
SELECT @companyname = description, @companylogo = logo
|
|
64
|
FROM base_cust_ref_companycode
|
|
65
|
WHERE (landscape = @landscape) AND (companycode= @companycode)
|
|
66
|
|
|
67
|
IF (@running = '1')
|
|
68
|
BEGIN
|
|
69
|
|
|
70
|
DECLARE @TableCA TABLE
|
|
71
|
(
|
|
72
|
employee_id VARCHAR(20)
|
|
73
|
--,wage_type VARCHAR(20)
|
|
74
|
--,rate DECIMAL(22,0)
|
|
75
|
,amount DECIMAL(22,0)
|
|
76
|
,retrotype VARCHAR(20)
|
|
77
|
)
|
|
78
|
|
|
79
|
INSERT INTO @TableCA
|
|
80
|
SELECT DISTINCT sumca.employee_id, SUM(DISTINCT(sumca.amount)) AS amount, MIN(sumca.retrotype) FROM
|
|
81
|
( SELECT DISTINCT ca.employee_id, ca.wage_type, CAST(dbo.GetPEN(@landscape, ca.rate) AS DECIMAL) 'rate' , CAST(dbo.GetPEN(@landscape, ca.amount) AS DECIMAL) 'amount', ca.retrotype
|
|
82
|
FROM (
|
|
83
|
SELECT * ,
|
|
84
|
'N' retrotype
|
|
85
|
FROM hr_tr0301 WHERE hr_tr0301.wage_type = '3000'
|
|
86
|
AND hr_tr0301.employee_id = @employee_id
|
|
87
|
AND hr_tr0301.pay_period_month = @paymonth
|
|
88
|
AND hr_tr0301.pay_period_year = @payyear
|
|
89
|
AND hr_tr0301.run_period_month = @paymonth
|
|
90
|
AND hr_tr0301.run_period_year = @payyear
|
|
91
|
UNION ALL
|
|
92
|
SELECT * ,
|
|
93
|
'R' retrotype
|
|
94
|
FROM hr_tr0301_retro WHERE hr_tr0301_retro.wage_type = '3000'
|
|
95
|
AND hr_tr0301_retro.employee_id = @employee_id
|
|
96
|
AND hr_tr0301_retro.pay_period_month = @paymonth
|
|
97
|
AND hr_tr0301_retro.pay_period_year = @payyear
|
|
98
|
AND hr_tr0301_retro.run_period_month = @paymonth
|
|
99
|
AND hr_tr0301_retro.run_period_year = @payyear
|
|
100
|
) ca ) sumca
|
|
101
|
GROUP BY sumca.employee_id
|
|
102
|
|
|
103
|
SELECT DISTINCT @payslipsigned payslipsigned, c308.landscape, @companyname companyname, @companylogo logo, c308.reportid, c308.groupby, c308P.description parentdescription, c308.sequence, c308.description, c308.paytype, c308.wage_type, c308.isdisplaywage_type, c308.isdisplaynumber, c308.isdisplayrate, c308.isdisplayamount,
|
|
104
|
c308.start_date, c308.end_date, c308.isactive, c308.change_by, c308.change_date, c308.created_by, c308.created_date, hr_tr0301.employee_id,
|
|
105
|
hr_tr0301.pay_period_month, hr_tr0301.pay_period_year, hr_tr0301.run_period_month, hr_tr0301.run_period_year, hr_tr0301.split_indicator,
|
|
106
|
--hr_tr0301.rate, hr_tr0301.amount,
|
|
107
|
CAST(dbo.GetPEN(@landscape, hr_tr0301.rate) AS DECIMAL) 'rate',
|
|
108
|
CASE WHEN c308.paytype = 'T' THEN ((ISNULL(CONVERT(INT, c308.[percent]),100) / 100) * CAST(dbo.GetPEN(@landscape, hr_tr0301.amount) AS DECIMAL)) + ISNULL(tblca.amount,0)
|
|
109
|
ELSE ((ISNULL(CONVERT(INT, c308.[percent]),100) / 100) * CAST(dbo.GetPEN(@landscape, hr_tr0301.amount) AS DECIMAL))
|
|
110
|
END AS 'amount',
|
|
111
|
hr_tr0301.number, hr_tr0301.currency, hr_tr0301.flag, md001.full_name, md003.tax_status, tax_stat.description tax_status_desc,
|
|
112
|
md001.marital_status, ms.description maritaldescription, md003.pay_industry, md003.pay_area, md003.pay_class,
|
|
113
|
md003.pay_grade, grd.description AS grd_desc, md002.job, md002.employee_area, area.description areaname, md002.organization, org.description orgname, md002.position, pos.description posname
|
|
114
|
,md002.employee_office, office.emp_subarea_description AS emp_office_desc
|
|
115
|
FROM hr_cu0308 AS c308
|
|
116
|
LEFT JOIN hr_cu0308 AS c308P ON c308.landscape = c308P.landscape AND c308.groupby = c308P.reportid
|
|
117
|
INNER JOIN
|
|
118
|
(SELECT *, 'N' retrotype
|
|
119
|
FROM hr_tr0301
|
|
120
|
UNION ALL
|
|
121
|
SELECT *, 'R' retrotype
|
|
122
|
FROM hr_tr0301_retro
|
|
123
|
)hr_tr0301 ON c308.landscape = hr_tr0301.landscape AND c308.wage_type = hr_tr0301.wage_type AND c308.retrotype = hr_tr0301.retrotype
|
|
124
|
AND (c308.paytype = @paytype OR @paytype = '')
|
|
125
|
INNER JOIN
|
|
126
|
(
|
|
127
|
SELECT m1.*
|
|
128
|
FROM hr_md_emp_md0001 m1
|
|
129
|
WHERE(m1.start_date <= @endda AND m1.end_date >= @endda)
|
|
130
|
)md001 ON md001.landscape = hr_tr0301.landscape AND md001.emp_id = hr_tr0301.employee_id
|
|
131
|
LEFT JOIN @TableCA AS tblca ON hr_tr0301.employee_id = tblca.employee_id AND c308.retrotype = tblca.retrotype
|
|
132
|
LEFT JOIN
|
|
133
|
(
|
|
134
|
SELECT m1.*
|
|
135
|
FROM hr_md_emp_md0003 m1
|
|
136
|
WHERE(m1.start_date <= @endda AND m1.end_date >= @endda)
|
|
137
|
)md003 ON md001.landscape = md003.landscape AND md001.emp_id = md003.emp_id
|
|
138
|
LEFT JOIN
|
|
139
|
(
|
|
140
|
SELECT m1.*
|
|
141
|
FROM hr_md_emp_md0002 m1
|
|
142
|
WHERE(m1.start_date <= @endda AND m1.end_date >= @endda)
|
|
143
|
)md002 ON hr_tr0301.landscape = md002.landscape AND hr_tr0301.employee_id = md002.emp_id
|
|
144
|
LEFT JOIN base_cust_ref_emp_area area ON md002.landscape = area.landscape and md002.employee_area = area.emp_area
|
|
145
|
LEFT JOIN base_cust_ref_emp_office office ON office.landscape = md002.landscape AND md002.employee_office = office.emp_subarea
|
|
146
|
LEFT JOIN
|
|
147
|
(
|
|
148
|
SELECT m1.*
|
|
149
|
FROM hr_md_orm_object m1
|
|
150
|
WHERE class = 'O' AND (start_date <= @endda AND end_date >= @endda)
|
|
151
|
) org ON md002.landscape = org.landscape and md002.organization = org.object
|
|
152
|
LEFT JOIN
|
|
153
|
(
|
|
154
|
SELECT m1.*
|
|
155
|
FROM hr_md_orm_object m1
|
|
156
|
WHERE class = 'P' AND (start_date <= @endda AND end_date >= @endda)
|
|
157
|
) pos ON md002.landscape = pos.landscape and md002.position = pos.object
|
|
158
|
LEFT JOIN base_cust_ref_pay_grade grd ON md003.landscape = grd.landscape AND md003.pay_grade = grd.pay_grade
|
|
159
|
LEFT JOIN base_cust_ref_marital_status ms ON md001.landscape = ms.landscape and md001.marital_status = ms.code
|
|
160
|
LEFT JOIN hr_cu0302 tax_stat ON tax_stat.landscape = md003.landscape AND md003.tax_status = tax_stat.status
|
|
161
|
AND tax_stat.start_date <= @endda AND tax_stat.end_date >= @endda
|
|
162
|
WHERE c308.landscape = @landscape AND hr_tr0301.employee_id = @employee_id AND
|
|
163
|
hr_tr0301.pay_period_month = @paymonth AND hr_tr0301.pay_period_year = @payyear AND
|
|
164
|
hr_tr0301.run_period_month = @paymonth AND hr_tr0301.run_period_year = @payyear
|
|
165
|
--AND hr_tr0301.amount <> 0
|
|
166
|
AND CAST(dbo.GetPEN(@landscape, hr_tr0301.amount) AS DECIMAL) <> 0
|
|
167
|
AND dbo.CekObjectKey(@landscape, @key1, @key2, 'M!N0V@2010') > 0
|
|
168
|
|
|
169
|
ORDER BY c308.groupby, c308.wage_type
|
|
170
|
END
|
|
171
|
ELSE
|
|
172
|
BEGIN
|
|
173
|
|
|
174
|
SELECT DISTINCT @payslipsigned payslipsigned, c308.landscape, @companyname companyname, @companylogo logo, c308.reportid, c308.groupby, c308P.description parentdescription, c308.sequence, c308.description, c308.paytype, c308.wage_type, c308.isdisplaywage_type, c308.isdisplaynumber, c308.isdisplayrate, c308.isdisplayamount,
|
|
175
|
c308.start_date, c308.end_date, c308.isactive, c308.change_by, c308.change_date, c308.created_by, c308.created_date, hr_tr0301_sim.employee_id,
|
|
176
|
hr_tr0301_sim.pay_period_month, hr_tr0301_sim.pay_period_year, hr_tr0301_sim.run_period_month, hr_tr0301_sim.run_period_year, hr_tr0301_sim.split_indicator,
|
|
177
|
--hr_tr0301_sim.rate, hr_tr0301_sim.amount,
|
|
178
|
CAST(dbo.GetPEN(@landscape, hr_tr0301_sim.rate) AS DECIMAL) 'rate',
|
|
179
|
CAST(dbo.GetPEN(@landscape, hr_tr0301_sim.amount) AS DECIMAL) 'amount',
|
|
180
|
hr_tr0301_sim.number, hr_tr0301_sim.currency, hr_tr0301_sim.flag, md001.full_name, md003.tax_status, tax_stat.description tax_status_desc,
|
|
181
|
md001.marital_status, ms.description maritaldescription, md003.pay_industry, md003.pay_area, md003.pay_class,
|
|
182
|
md003.pay_grade, grd.description AS grd_desc, md002.job, md002.employee_area, area.description areaname, md002.organization, org.description orgname, md002.position, pos.description posname
|
|
183
|
,md002.employee_office, office.emp_subarea_description AS emp_office_desc
|
|
184
|
FROM hr_cu0308 AS c308
|
|
185
|
LEFT JOIN hr_cu0308 AS c308P ON c308.landscape = c308P.landscape AND c308.groupby = c308P.reportid
|
|
186
|
INNER JOIN
|
|
187
|
(SELECT *, 'N' retrotype
|
|
188
|
FROM hr_tr0301_sim
|
|
189
|
UNION ALL
|
|
190
|
SELECT *, 'R' retrotype
|
|
191
|
FROM hr_tr0301_retro_sim
|
|
192
|
)hr_tr0301_sim ON c308.landscape = hr_tr0301_sim.landscape AND c308.wage_type = hr_tr0301_sim.wage_type AND c308.retrotype = hr_tr0301_sim.retrotype
|
|
193
|
AND (c308.paytype = @paytype OR @paytype = '')
|
|
194
|
INNER JOIN
|
|
195
|
(
|
|
196
|
SELECT m1.*
|
|
197
|
FROM hr_md_emp_md0001 m1
|
|
198
|
WHERE(m1.start_date <= @endda AND m1.end_date >= @endda)
|
|
199
|
)md001 ON md001.landscape = hr_tr0301_sim.landscape AND md001.emp_id = hr_tr0301_sim.employee_id
|
|
200
|
LEFT JOIN
|
|
201
|
(
|
|
202
|
SELECT m1.*
|
|
203
|
FROM hr_md_emp_md0003 m1
|
|
204
|
WHERE(m1.start_date <= @endda AND m1.end_date >= @endda)
|
|
205
|
)md003 ON md001.landscape = md003.landscape AND md001.emp_id = md003.emp_id
|
|
206
|
LEFT JOIN
|
|
207
|
(
|
|
208
|
SELECT m1.*
|
|
209
|
FROM hr_md_emp_md0002 m1
|
|
210
|
WHERE(m1.start_date <= @endda AND m1.end_date >= @endda)
|
|
211
|
)md002 ON hr_tr0301_sim.landscape = md002.landscape AND hr_tr0301_sim.employee_id = md002.emp_id
|
|
212
|
LEFT JOIN base_cust_ref_emp_area area ON md002.landscape = area.landscape and md002.employee_area = area.emp_area
|
|
213
|
LEFT JOIN base_cust_ref_emp_office office ON office.landscape = md002.landscape AND md002.employee_office = office.emp_subarea
|
|
214
|
LEFT JOIN
|
|
215
|
(
|
|
216
|
SELECT m1.*
|
|
217
|
FROM hr_md_orm_object m1
|
|
218
|
WHERE class = 'O' AND (start_date <= @endda AND end_date >= @endda)
|
|
219
|
) org ON md002.landscape = org.landscape and md002.organization = org.object
|
|
220
|
LEFT JOIN
|
|
221
|
(
|
|
222
|
SELECT m1.*
|
|
223
|
FROM hr_md_orm_object m1
|
|
224
|
WHERE class = 'P' AND (start_date <= @endda AND end_date >= @endda)
|
|
225
|
) pos ON md002.landscape = pos.landscape and md002.position = pos.object
|
|
226
|
LEFT JOIN base_cust_ref_pay_grade grd ON md003.landscape = grd.landscape AND md003.pay_grade = grd.pay_grade
|
|
227
|
LEFT JOIN base_cust_ref_marital_status ms ON md001.landscape = ms.landscape and md001.marital_status = ms.code
|
|
228
|
LEFT JOIN hr_cu0302 tax_stat ON tax_stat.landscape = md003.landscape AND md003.tax_status = tax_stat.status
|
|
229
|
AND tax_stat.start_date <= @endda AND tax_stat.end_date >= @endda
|
|
230
|
WHERE c308.landscape = @landscape AND hr_tr0301_sim.employee_id = @employee_id AND
|
|
231
|
hr_tr0301_sim.pay_period_month = @paymonth AND hr_tr0301_sim.pay_period_year = @payyear AND
|
|
232
|
hr_tr0301_sim.run_period_month = @paymonth AND hr_tr0301_sim.run_period_year = @payyear
|
|
233
|
--AND hr_tr0301_sim.amount <> 0
|
|
234
|
AND CAST(dbo.GetPEN(@landscape, hr_tr0301_sim.amount) AS DECIMAL) <> 0
|
|
235
|
AND dbo.CekObjectKey(@landscape, @key1, @key2, 'M!N0V@2010') > 0
|
|
236
|
|
|
237
|
ORDER BY c308.groupby, c308.wage_type
|
|
238
|
END
|
|
239
|
END
|
|
240
|
GO
|
