|
1
|
USE [MinovaHR_ESS_Mega_Syariah_Production]
|
|
2
|
GO
|
|
3
|
/****** Object: StoredProcedure [dbo].[Rpt_BMS_HR_PY_Bank_Transfer_Content] Script Date: 11/18/2021 9:23:00 AM ******/
|
|
4
|
SET ANSI_NULLS ON
|
|
5
|
GO
|
|
6
|
SET QUOTED_IDENTIFIER ON
|
|
7
|
GO
|
|
8
|
ALTER PROCEDURE [dbo].[Rpt_BMS_HR_PY_Bank_Transfer_Content]
|
|
9
|
@landscape AS VARCHAR(3),
|
|
10
|
@companycode AS VARCHAR(5),
|
|
11
|
@paygroup AS VARCHAR(5),
|
|
12
|
@payperiod AS VARCHAR(6),
|
|
13
|
@bank_id AS VARCHAR(2),
|
|
14
|
@tgltrans AS VARCHAR(8),
|
|
15
|
@key1 AS NVARCHAR(MAX),
|
|
16
|
@key2 AS NVARCHAR(MAX)
|
|
17
|
AS
|
|
18
|
|
|
19
|
--DECLARE @landscape AS VARCHAR(3)
|
|
20
|
--DECLARE @companycode AS VARCHAR(5)
|
|
21
|
--DECLARE @paygroup AS VARCHAR(5)
|
|
22
|
--DECLARE @payperiod AS VARCHAR(6)
|
|
23
|
--DECLARE @bank_id AS VARCHAR(2)
|
|
24
|
--DECLARE @tgltrans AS VARCHAR(8)
|
|
25
|
--DECLARE @emp_type VARCHAR(12)
|
|
26
|
--SET @landscape = '100'
|
|
27
|
--SET @companycode = '1000'
|
|
28
|
--SET @paygroup = '01'
|
|
29
|
--SET @payperiod = '202107'
|
|
30
|
--SET @bank_id = '11'
|
|
31
|
--SET @tgltrans = '20211118'
|
|
32
|
--SET @emp_type = ''
|
|
33
|
|
|
34
|
DECLARE @companyname AS NVARCHAR(50)
|
|
35
|
DECLARE @companylogo AS VARBINARY(MAX)
|
|
36
|
DECLARE @payrollgroup AS NVARCHAR(50)
|
|
37
|
DECLARE @paymonth AS NVARCHAR(2)
|
|
38
|
DECLARE @payyear AS NVARCHAR(4)
|
|
39
|
DECLARE @begda AS NVARCHAR(8)
|
|
40
|
DECLARE @endda AS NVARCHAR(8)
|
|
41
|
DECLARE @enddate AS VARCHAR(8)
|
|
42
|
SET @paymonth = RIGHT(@payperiod,2)
|
|
43
|
SET @payyear = LEFT(@payperiod,4)
|
|
44
|
SET @begda = @payperiod + '01'
|
|
45
|
SET @endda = convert(NVARCHAR(8),dateadd(d,-1, dateadd(m, 1, @begda)), 112)
|
|
46
|
|
|
47
|
DECLARE @last_date AS varchar(12)
|
|
48
|
DECLARE @last_year AS varchar(8)
|
|
49
|
DECLARE @last_month AS varchar(8)
|
|
50
|
SET @last_date = convert(VARCHAR(6), dateadd(d,0, dateadd(m,-1, @begda)), 112)
|
|
51
|
SET @last_year = LEFT(@last_date,4)
|
|
52
|
SET @last_month = RIGHT(@last_date,2)
|
|
53
|
|
|
54
|
-----//----- tabel-tabel internal
|
|
55
|
DECLARE @tbl_emp TABLE (
|
|
56
|
emp_id varchar(8)
|
|
57
|
,emp_name varchar(250)
|
|
58
|
,nick_name varchar(250)
|
|
59
|
,[bank_id] [varchar](2)
|
|
60
|
,[bank_account] [varchar](50)
|
|
61
|
,[pay_grade] [varchar](8)
|
|
62
|
,[tax_status] [varchar](8)
|
|
63
|
,[npwp] [varchar](50)
|
|
64
|
,[jamsostek_type] [varchar](4)
|
|
65
|
,[jamsostek] [varchar](50)
|
|
66
|
,[jamsostek_level] [varchar](5)
|
|
67
|
,bank_acc_name VARCHAR(100)
|
|
68
|
,[bank_id_non] [varchar](20)
|
|
69
|
,[bank_name_non] [varchar](50)
|
|
70
|
,[bank_account_non] [varchar](50)
|
|
71
|
,[cost_center] [varchar](8)
|
|
72
|
,[employee_area] [varchar](4)
|
|
73
|
,[employee_office] [varchar](4)
|
|
74
|
,[employee_status] [varchar](8)
|
|
75
|
,[employee_type] [varchar](8)
|
|
76
|
,[employee_subtype] [varchar](8)
|
|
77
|
,[payroll_group] [varchar](8)
|
|
78
|
,[organization] [varchar](8)
|
|
79
|
,[position] [varchar](8)
|
|
80
|
,[job] [varchar](8)
|
|
81
|
,bank_name varchar(250)
|
|
82
|
)
|
|
83
|
|
|
84
|
DECLARE @tbl_py TABLE (
|
|
85
|
emp_id varchar(8)
|
|
86
|
,this_amount decimal(18,0)
|
|
87
|
,wage_type varchar(8)
|
|
88
|
)
|
|
89
|
|
|
90
|
DECLARE @tbl_py_last TABLE (
|
|
91
|
emp_id varchar(8)
|
|
92
|
,last_amount decimal(18,0)
|
|
93
|
)
|
|
94
|
|
|
95
|
DECLARE @tbl_tr_enc TABLE
|
|
96
|
(landscape varchar (3),
|
|
97
|
employee_id varchar(8),
|
|
98
|
payPeriodMonth varchar(2),
|
|
99
|
payPeriodYear varchar(4),
|
|
100
|
runPeriodMonth varchar(2),
|
|
101
|
runPeriodYear varchar(4),
|
|
102
|
wage_type varchar(4),
|
|
103
|
amount varchar(250),
|
|
104
|
rate varchar(250),
|
|
105
|
flg_retro varchar(3),
|
|
106
|
flg_period VARCHAR(20))
|
|
107
|
|
|
108
|
DECLARE @tbl_tr_dec TABLE
|
|
109
|
(landscape varchar (3),
|
|
110
|
employee_id varchar(8),
|
|
111
|
payPeriodMonth varchar(2),
|
|
112
|
payPeriodYear varchar(4),
|
|
113
|
runPeriodMonth varchar(2),
|
|
114
|
runPeriodYear varchar(4),
|
|
115
|
wage_type varchar(4),
|
|
116
|
amount DECIMAL(20,0),
|
|
117
|
rate DECIMAL(20,0),
|
|
118
|
flg_retro varchar(3),
|
|
119
|
flg_period VARCHAR(20))
|
|
120
|
|
|
121
|
DECLARE @tbl_tr_dec_sum TABLE
|
|
122
|
(landscape varchar (3),
|
|
123
|
employee_id varchar(8),
|
|
124
|
payPeriodMonth varchar(2),
|
|
125
|
payPeriodYear varchar(4),
|
|
126
|
runPeriodMonth varchar(2),
|
|
127
|
runPeriodYear varchar(4),
|
|
128
|
wage_type varchar(4),
|
|
129
|
amount DECIMAL(20,0),
|
|
130
|
rate DECIMAL(20,0),
|
|
131
|
flg_retro varchar(1),
|
|
132
|
flg_period VARCHAR(20))
|
|
133
|
|
|
134
|
DECLARE @t_res TABLE
|
|
135
|
(
|
|
136
|
emp_id varchar(8)
|
|
137
|
,emp_name varchar(250)
|
|
138
|
,nick_name varchar(250)
|
|
139
|
,[bank_account] [varchar](50)
|
|
140
|
,bank_name VARCHAR(200)
|
|
141
|
,bank_acc_name VARCHAR(225)
|
|
142
|
,bank_name_non VARCHAR(200)
|
|
143
|
,bank_acc_non VARCHAR(50)
|
|
144
|
,this_amount DECIMAL(20,0)
|
|
145
|
,last_amount DECIMAL(20,0)
|
|
146
|
,selisih VARCHAR(50)
|
|
147
|
,persen VARCHAR(50)
|
|
148
|
,persenpositif VARCHAR(50)
|
|
149
|
,jum_emp DECIMAL(18,0)
|
|
150
|
)
|
|
151
|
|
|
152
|
DECLARE @t_res_temp TABLE
|
|
153
|
(
|
|
154
|
emp_id varchar(8)
|
|
155
|
,emp_name varchar(250)
|
|
156
|
,nick_name varchar(250)
|
|
157
|
,[bank_account] [varchar](50)
|
|
158
|
,bank_name VARCHAR(200)
|
|
159
|
,bank_acc_name VARCHAR(225)
|
|
160
|
,bank_name_non VARCHAR(200)
|
|
161
|
,bank_acc_non VARCHAR(50)
|
|
162
|
,this_amount DECIMAL(20,0)
|
|
163
|
,last_amount DECIMAL(20,0)
|
|
164
|
,selisih VARCHAR(50)
|
|
165
|
,persen VARCHAR(50)
|
|
166
|
,persenpositif VARCHAR(50)
|
|
167
|
,jum_emp DECIMAL(18,0)
|
|
168
|
)
|
|
169
|
|
|
170
|
SELECT @companyname = description
|
|
171
|
, @companylogo = logo
|
|
172
|
FROM
|
|
173
|
base_cust_ref_companycode
|
|
174
|
WHERE
|
|
175
|
(landscape = @landscape)
|
|
176
|
AND (companycode = @companycode)
|
|
177
|
|
|
178
|
SELECT @payrollgroup = description
|
|
179
|
FROM
|
|
180
|
base_cust_ref_payroll_group
|
|
181
|
WHERE
|
|
182
|
(landscape = @landscape)
|
|
183
|
AND (payroll_group = @paygroup)
|
|
184
|
|
|
185
|
-----//----- pengambilan data employee
|
|
186
|
INSERT INTO @tbl_emp
|
|
187
|
SELECT tr300.employee_id
|
|
188
|
,md1.full_name
|
|
189
|
,md1.nick_name
|
|
190
|
,md3.[bank_id]
|
|
191
|
,CASE WHEN md3.[bank_account] <> '' THEN md3.[bank_account] ELSE '0' END
|
|
192
|
,md3.[pay_grade]
|
|
193
|
,md3.[tax_status]
|
|
194
|
,md3.[npwp]
|
|
195
|
,md3.[jamsostek_type]
|
|
196
|
,md3.[jamsostek]
|
|
197
|
,md3.[jamsostek_level]
|
|
198
|
,md3.bank_account_name
|
|
199
|
,CASE WHEN md3.bank_id_non <> '' THEN md3.bank_id_non ELSE '0' END
|
|
200
|
,bank_non.description AS bank__name_non
|
|
201
|
,md3.bank_account_non
|
|
202
|
,tr300.[cost_center]
|
|
203
|
,tr300.[employee_area]
|
|
204
|
,tr300.[employee_office]
|
|
205
|
,tr300.[employee_status]
|
|
206
|
,tr300.[employee_type]
|
|
207
|
,tr300.[employee_subtype]
|
|
208
|
,tr300.[payroll_group]
|
|
209
|
,tr300.[organization]
|
|
210
|
,tr300.[position]
|
|
211
|
,tr300.[job]
|
|
212
|
,bank.description
|
|
213
|
FROM hr_tr0300 AS tr300
|
|
214
|
LEFT JOIN hr_md_emp_md0001 AS md1
|
|
215
|
ON tr300.landscape = md1.landscape
|
|
216
|
AND tr300.employee_id = md1.emp_id
|
|
217
|
AND md1.start_date <= @endda AND md1.end_date >= @endda
|
|
218
|
LEFT JOIN hr_md_emp_md0003 AS md3
|
|
219
|
ON tr300.landscape = md3.landscape
|
|
220
|
AND tr300.employee_id = md3.emp_id
|
|
221
|
AND md3.start_date <= @endda AND md3.end_date >= @endda
|
|
222
|
LEFT JOIN base_cust_ref_bankid AS bank
|
|
223
|
ON bank.code = md3.bank_id
|
|
224
|
AND bank.landscape = md3.landscape
|
|
225
|
LEFT JOIN dbo.base_cust_ref_bankid AS bank_non
|
|
226
|
ON bank_non.landscape = md3.landscape
|
|
227
|
AND bank_non.code = md3.bank_id_non
|
|
228
|
WHERE tr300.landscape = @landscape
|
|
229
|
AND tr300.payroll_group = @paygroup
|
|
230
|
AND tr300.pay_period_month = @paymonth AND tr300.pay_period_year = @payyear
|
|
231
|
AND tr300.run_period_month = @paymonth AND tr300.run_period_year = @payyear
|
|
232
|
AND (tr300.company_id = @companycode OR @companycode = '')
|
|
233
|
--AND md3.bank_id = @bank_id
|
|
234
|
--AND tr300.employee_id = '00005311'
|
|
235
|
--AND (tr300.employee_type = @emp_type OR @emp_type = '')
|
|
236
|
|
|
237
|
--------------- get data payroll --------------
|
|
238
|
INSERT INTO @tbl_tr_enc
|
|
239
|
SELECT pr.landscape , pr.employee_id,
|
|
240
|
pr.pay_period_month, pr.pay_period_year,
|
|
241
|
pr.run_period_month, pr.run_period_year,
|
|
242
|
pr.wage_type, pr.amount, pr.rate, 'N', 'now'
|
|
243
|
FROM hr_tr0301 pr WITH (NOLOCK)
|
|
244
|
INNER JOIN @tbl_emp emp
|
|
245
|
ON pr.employee_id = emp.emp_id
|
|
246
|
WHERE pr.pay_period_month = @paymonth AND pr.pay_period_year = @payyear
|
|
247
|
AND pr.run_period_month = @paymonth AND pr.run_period_year = @payyear
|
|
248
|
AND pr.wage_type in ('THP','D050')
|
|
249
|
|
|
250
|
INSERT INTO @tbl_tr_enc
|
|
251
|
SELECT pr.landscape, pr.employee_id,
|
|
252
|
pr.pay_period_month, pr.pay_period_year,
|
|
253
|
pr.run_period_month, pr.run_period_year,
|
|
254
|
pr.wage_type, pr.amount, pr.rate, 'R', 'now'
|
|
255
|
FROM hr_tr0301_retro pr WITH (NOLOCK)
|
|
256
|
INNER JOIN @tbl_emp emp
|
|
257
|
ON pr.employee_id = emp.emp_id
|
|
258
|
WHERE pr.pay_period_month = @paymonth AND pr.pay_period_year = @payyear
|
|
259
|
AND pr.run_period_month = @paymonth AND pr.run_period_year = @payyear
|
|
260
|
AND pr.wage_type in ('THP','D050')
|
|
261
|
|
|
262
|
INSERT INTO @tbl_tr_dec
|
|
263
|
SELECT pr.landscape, pr.employee_id,
|
|
264
|
pr.payPeriodMonth, pr.payPeriodYear,
|
|
265
|
pr.runPeriodMonth, pr.runPeriodYear, pr.wage_type,
|
|
266
|
CONVERT(decimal(18,0), dbo.SDE(pr.amount, 'M!N0V@2010')),
|
|
267
|
CONVERT(decimal(18,0), dbo.SDE(pr.rate, 'M!N0V@2010')), pr.flg_retro, pr.flg_period
|
|
268
|
FROM @tbl_tr_enc pr
|
|
269
|
|
|
270
|
INSERT INTO @tbl_tr_dec_sum
|
|
271
|
SELECT tbl_tr_dec.landscape, employee_id,
|
|
272
|
payPeriodMonth, payPeriodYear,
|
|
273
|
runPeriodMonth, runPeriodYear, wage_type,
|
|
274
|
SUM(amount) AS amount,SUM(rate) AS rate,
|
|
275
|
min(flg_retro) AS flg_retro, flg_period
|
|
276
|
FROM @tbl_tr_dec AS tbl_tr_dec
|
|
277
|
GROUP BY tbl_tr_dec.landscape, employee_id,
|
|
278
|
payPeriodMonth, payPeriodYear,
|
|
279
|
runPeriodMonth,runPeriodYear, wage_type, flg_period
|
|
280
|
|
|
281
|
|
|
282
|
-----//----- data payroll bulan ini
|
|
283
|
INSERT INTO @tbl_py
|
|
284
|
SELECT tbl_tr_dec_sum.employee_id
|
|
285
|
,tbl_tr_dec_sum.amount 'amount'
|
|
286
|
,tbl_tr_dec_sum.wage_type
|
|
287
|
FROM @tbl_tr_dec_sum AS tbl_tr_dec_sum WHERE flg_period = 'now'
|
|
288
|
|
|
289
|
|
|
290
|
DECLARE @jum_emp DECIMAL(5,0)
|
|
291
|
SET @jum_emp = ( SELECT COUNT(emp_id) FROM @tbl_py)
|
|
292
|
|
|
293
|
--SELECT * FROM @tbl_emp
|
|
294
|
|
|
295
|
IF ( @bank_id = 'upin') ------ bank mega syariah ------
|
|
296
|
BEGIN
|
|
297
|
INSERT INTO @t_res
|
|
298
|
SELECT DISTINCT tbl_emp.emp_id
|
|
299
|
,tbl_emp.emp_name
|
|
300
|
,tbl_emp.nick_name
|
|
301
|
,tbl_emp.bank_account
|
|
302
|
,tbl_emp.bank_name
|
|
303
|
,ISNULL(tbl_emp.bank_acc_name, 'None') AS bank_acc_name
|
|
304
|
,tbl_emp.bank_name_non
|
|
305
|
,ISNULL(tbl_emp.bank_account_non, 'None') AS bank_acc_non
|
|
306
|
,isnull(tbl_py.this_amount, 0) AS this_amount
|
|
307
|
,isnull(tbl_py_last.last_amount,0) AS last_mount
|
|
308
|
,'' AS selisih
|
|
309
|
,'' AS persen
|
|
310
|
,'' AS persenpositif
|
|
311
|
,1 AS jum_emp
|
|
312
|
FROM @tbl_emp AS tbl_emp
|
|
313
|
INNER JOIN @tbl_py AS tbl_py
|
|
314
|
ON tbl_py.emp_id = tbl_emp.emp_id
|
|
315
|
LEFT JOIN @tbl_py_last AS tbl_py_last
|
|
316
|
ON tbl_py_last.emp_id = tbl_emp.emp_id
|
|
317
|
WHERE tbl_emp.bank_account <> '' AND tbl_emp.bank_account_non <> ''
|
|
318
|
AND tbl_py.wage_type = 'D050' AND this_amount > 0
|
|
319
|
|
|
320
|
INSERT INTO @t_res
|
|
321
|
SELECT DISTINCT tbl_emp.emp_id
|
|
322
|
,tbl_emp.emp_name
|
|
323
|
,tbl_emp.nick_name
|
|
324
|
,tbl_emp.bank_account
|
|
325
|
,tbl_emp.bank_name
|
|
326
|
,ISNULL(tbl_emp.bank_acc_name, 'None') AS bank_acc_name
|
|
327
|
,tbl_emp.bank_name_non
|
|
328
|
,ISNULL(tbl_emp.bank_account_non, 'None') AS bank_acc_non
|
|
329
|
,isnull(tbl_py.this_amount, 0) AS this_amount
|
|
330
|
,isnull(tbl_py_last.last_amount,0) AS last_mount
|
|
331
|
,'' AS selisih
|
|
332
|
,'' AS persen
|
|
333
|
,'' AS persenpositif
|
|
334
|
,1 AS jum_emp
|
|
335
|
FROM @tbl_emp AS tbl_emp
|
|
336
|
INNER JOIN @tbl_py AS tbl_py
|
|
337
|
ON tbl_py.emp_id = tbl_emp.emp_id
|
|
338
|
LEFT JOIN @tbl_py_last AS tbl_py_last
|
|
339
|
ON tbl_py_last.emp_id = tbl_emp.emp_id
|
|
340
|
WHERE tbl_emp.bank_account <> '' AND ( tbl_emp.bank_account_non = NULL OR tbl_emp.bank_account_non = '' OR tbl_emp.bank_account_non = '0')
|
|
341
|
AND tbl_py.wage_type = 'THP'
|
|
342
|
|
|
343
|
INSERT INTO @t_res
|
|
344
|
SELECT DISTINCT tbl_emp.emp_id
|
|
345
|
,tbl_emp.emp_name
|
|
346
|
,tbl_emp.nick_name
|
|
347
|
,tbl_emp.bank_account
|
|
348
|
,tbl_emp.bank_name
|
|
349
|
,ISNULL(tbl_emp.bank_acc_name, 'None') AS bank_acc_name
|
|
350
|
,tbl_emp.bank_name_non
|
|
351
|
,ISNULL(tbl_emp.bank_account_non, 'None') AS bank_acc_non
|
|
352
|
,isnull(tbl_py.this_amount, 0) AS this_amount
|
|
353
|
,isnull(tbl_py_last.last_amount,0) AS last_mount
|
|
354
|
,'' AS selisih
|
|
355
|
,'' AS persen
|
|
356
|
,'' AS persenpositif
|
|
357
|
,1 AS jum_emp
|
|
358
|
FROM @tbl_emp AS tbl_emp
|
|
359
|
INNER JOIN @tbl_py AS tbl_py
|
|
360
|
ON tbl_py.emp_id = tbl_emp.emp_id
|
|
361
|
LEFT JOIN @tbl_py_last AS tbl_py_last
|
|
362
|
ON tbl_py_last.emp_id = tbl_emp.emp_id
|
|
363
|
WHERE tbl_emp.bank_account <> '0' AND tbl_emp.bank_account_non <> '0' AND this_amount < 1000000
|
|
364
|
AND tbl_py.wage_type = 'THP' AND tbl_emp.emp_id NOT IN ( SELECT DISTINCT emp_id FROM @t_res)
|
|
365
|
|
|
366
|
END
|
|
367
|
ELSE IF ( @bank_id <> '') ------ bank mega------
|
|
368
|
BEGIN
|
|
369
|
INSERT INTO @t_res_temp
|
|
370
|
SELECT DISTINCT tbl_emp.emp_id
|
|
371
|
,tbl_emp.emp_name
|
|
372
|
,tbl_emp.nick_name
|
|
373
|
,tbl_emp.bank_account
|
|
374
|
,tbl_emp.bank_name
|
|
375
|
,ISNULL(tbl_emp.bank_acc_name, 'None') AS bank_acc_name
|
|
376
|
,tbl_emp.bank_name_non
|
|
377
|
,ISNULL(tbl_emp.bank_account_non, 'None') AS bank_acc_non
|
|
378
|
,isnull(tbl_py.this_amount, 0) AS this_amount
|
|
379
|
,isnull(tbl_py_last.last_amount,0) AS last_mount
|
|
380
|
,'' AS selisih
|
|
381
|
,'' AS persen
|
|
382
|
,'' AS persenpositif
|
|
383
|
,1 AS jum_emp
|
|
384
|
FROM @tbl_emp AS tbl_emp
|
|
385
|
INNER JOIN @tbl_py AS tbl_py
|
|
386
|
ON tbl_py.emp_id = tbl_emp.emp_id
|
|
387
|
LEFT JOIN @tbl_py_last AS tbl_py_last
|
|
388
|
ON tbl_py_last.emp_id = tbl_emp.emp_id
|
|
389
|
WHERE tbl_emp.bank_account <> '' AND tbl_emp.bank_account_non <> ''
|
|
390
|
AND tbl_py.wage_type = 'D050' AND this_amount > 0
|
|
391
|
|
|
392
|
INSERT INTO @t_res
|
|
393
|
SELECT DISTINCT tbl_emp.emp_id
|
|
394
|
,tbl_emp.emp_name
|
|
395
|
,tbl_emp.nick_name
|
|
396
|
,tbl_emp.bank_account
|
|
397
|
,tbl_emp.bank_name
|
|
398
|
,ISNULL(tbl_emp.bank_acc_name, 'None') AS bank_acc_name
|
|
399
|
,tbl_emp.bank_name_non
|
|
400
|
,ISNULL(tbl_emp.bank_account_non, 'None') AS bank_acc_non
|
|
401
|
,isnull(tbl_py.this_amount, 0) AS this_amount
|
|
402
|
,isnull(tbl_py_last.last_amount,0) AS last_mount
|
|
403
|
,'' AS selisih
|
|
404
|
,'' AS persen
|
|
405
|
,'' AS persenpositif
|
|
406
|
,1 AS jum_emp
|
|
407
|
FROM @tbl_emp AS tbl_emp
|
|
408
|
INNER JOIN @tbl_py AS tbl_py
|
|
409
|
ON tbl_py.emp_id = tbl_emp.emp_id
|
|
410
|
LEFT JOIN @tbl_py_last AS tbl_py_last
|
|
411
|
ON tbl_py_last.emp_id = tbl_emp.emp_id
|
|
412
|
WHERE (tbl_emp.bank_account_non <> '0') --AND ( tbl_emp.bank_account = NULL OR tbl_emp.bank_account = '' OR tbl_emp.bank_account = '0')
|
|
413
|
AND tbl_py.wage_type = 'THP'
|
|
414
|
|
|
415
|
--INSERT INTO @t_res
|
|
416
|
--SELECT DISTINCT tbl_emp.emp_id
|
|
417
|
-- ,tbl_emp.emp_name
|
|
418
|
-- ,tbl_emp.bank_account
|
|
419
|
-- ,tbl_emp.bank_name
|
|
420
|
-- ,ISNULL(tbl_emp.bank_acc_name, 'None') AS bank_acc_name
|
|
421
|
-- ,tbl_emp.bank_name_non
|
|
422
|
-- ,ISNULL(tbl_emp.bank_account_non, 'None') AS bank_acc_non
|
|
423
|
-- ,isnull(tbl_py.this_amount, 0) AS this_amount
|
|
424
|
-- ,isnull(tbl_py_last.last_amount,0) AS last_mount
|
|
425
|
-- ,'' AS selisih
|
|
426
|
-- ,'' AS persen
|
|
427
|
-- ,'' AS persenpositif
|
|
428
|
-- ,1 AS jum_emp
|
|
429
|
--FROM @tbl_emp AS tbl_emp
|
|
430
|
--INNER JOIN @tbl_py AS tbl_py
|
|
431
|
-- ON tbl_py.emp_id = tbl_emp.emp_id
|
|
432
|
--LEFT JOIN @tbl_py_last AS tbl_py_last
|
|
433
|
-- ON tbl_py_last.emp_id = tbl_emp.emp_id
|
|
434
|
--WHERE (tbl_emp.bank_account_non <> '0') AND tbl_emp.bank_account <> '0' --AND this_amount > 1000000
|
|
435
|
-- AND tbl_py.wage_type = 'THP'
|
|
436
|
|
|
437
|
INSERT INTO @t_res
|
|
438
|
SELECT DISTINCT tbl_emp.emp_id
|
|
439
|
,tbl_emp.emp_name
|
|
440
|
,tbl_emp.nick_name
|
|
441
|
,tbl_emp.bank_account
|
|
442
|
,tbl_emp.bank_name
|
|
443
|
,ISNULL(tbl_emp.bank_acc_name, 'None') AS bank_acc_name
|
|
444
|
,tbl_emp.bank_name_non
|
|
445
|
,ISNULL(tbl_emp.bank_account_non, 'None') AS bank_acc_non
|
|
446
|
,isnull(tbl_py.this_amount, 0) AS this_amount
|
|
447
|
,isnull(tbl_py_last.last_amount,0) AS last_mount
|
|
448
|
,'' AS selisih
|
|
449
|
,'' AS persen
|
|
450
|
,'' AS persenpositif
|
|
451
|
,1 AS jum_emp
|
|
452
|
FROM @tbl_emp AS tbl_emp
|
|
453
|
INNER JOIN @tbl_py AS tbl_py
|
|
454
|
ON tbl_py.emp_id = tbl_emp.emp_id
|
|
455
|
LEFT JOIN @tbl_py_last AS tbl_py_last
|
|
456
|
ON tbl_py_last.emp_id = tbl_emp.emp_id
|
|
457
|
WHERE (tbl_emp.bank_account_non <> '0') AND tbl_emp.bank_account <> '0' --AND this_amount > 1000000
|
|
458
|
AND tbl_py.wage_type = 'THP'
|
|
459
|
AND tbl_emp.emp_id IN ( SELECT DISTINCT emp_id FROM @t_res_temp)
|
|
460
|
|
|
461
|
END
|
|
462
|
|
|
463
|
SELECT DISTINCT * FROM @t_res
|
|
464
|
|
