USE [hrmis]
GO
/****** Object: StoredProcedure [dbo].[SelectPersonnelPayOutDetail] Script Date: 09/14/2012 10:55:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
View Code
1 USE [hrmis] 2 GO 3 /****** Object: StoredProcedure [dbo].[SelectPersonnelPayOutDetail] Script Date: 09/14/2012 10:55:55 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 -- ============================================= 9 -- Author: <吴杰> 10 -- Create date: <2012.9.7> 11 -- 大平台接口按明细导出数据汇总 12 -- ============================================= 13 ALTER proc [dbo].[SelectPersonnelPayOutDetail] 14 ( 15 @year int, 16 @Moth int, 17 @unitsid varchar(200) 18 ) 19 as 20 --将payout表数据放入临时表 21 declare @sql varchar(2000) 22 set @sql='select * into ##PersonnelPayout from dbo.PersonnelPayout where Years='+ cast(@year as varchar(20))+' and Months='+cast(@moth as varchar(20))+' and Fk_Units_Id in('+@unitsid+')' 23 exec(@sql) 24 25 --查询临时表数据 26 27 select 28 pp.Fk_Personnel_Id, 29 30 --月份 31 pp.Months, 32 33 --预算单位 34 u.Finance_code, 35 36 --职员姓名 37 pp.name, 38 39 --职员代码 40 (case when pp.Fk_PersonnelType_Code in ('1','2') then pe.Code 41 when pp.Fk_PersonnelType_Code in ('3') then (select p.Code from Personnel as p where p.id=d.Fk_Personnel_Id) 42 when pp.Fk_PersonnelType_Code in ('4','5') then '01' 43 end) as code, 44 45 --在职教师类型 46 '02' as TeacherType, 47 48 --人员变动情况 49 '1004' as PeopleChange, 50 51 --性别 52 (case when pp.Fk_PersonnelType_Code in ('1','2') then (case when pe.Gender=2 then 0 else pe.Gender end) 53 when pp.Fk_PersonnelType_Code in ('3') then (select (case when p.Gender=2 then 0 else p.Gender end) from Personnel as p where p.id=d.Fk_Personnel_Id) 54 when pp.Fk_PersonnelType_Code in ('4') then l.Gender 55 when pp.Fk_PersonnelType_Code in ('5') then t.Gender 56 end) as Sex, 57 58 --民族 59 (case when pp.Fk_PersonnelType_Code in ('4') then '01' 60 when pp.Fk_PersonnelType_Code in ('1','2') then dbo.Minzu(pe.Fk_nation_code) 61 when pp.Fk_PersonnelType_Code in ('3') then (select dbo.Minzu(p.Fk_nation_code) from Personnel as p where p.id=d.Fk_Personnel_Id) 62 when pp.Fk_PersonnelType_Code in ('5') then dbo.Minzu(t.Fk_nation_code) 63 end) as Fk_nation_code, 64 65 --身份证号码 66 pp.IdentityCode as IDCard, 67 68 --出生日期 69 (case when pp.Fk_PersonnelType_Code in ('1','2') then pe.Birthday 70 when pp.Fk_PersonnelType_Code in ('3') then (select p.Birthday from Personnel as p where p.id=d.Fk_Personnel_Id) 71 when pp.Fk_PersonnelType_Code in ('4') then l.Birthday 72 when pp.Fk_PersonnelType_Code in ('5') then t.Birthday 73 end) as Birthday, 74 75 --参加工作日期 76 (case when pp.Fk_PersonnelType_Code in ('1','2') then pe.JobDate 77 when pp.Fk_PersonnelType_Code in ('3') then (select p.JobDate from Personnel as p where p.id=d.Fk_Personnel_Id) 78 when pp.Fk_PersonnelType_Code in ('4') then '1992-08-09' 79 when pp.Fk_PersonnelType_Code in ('5') then t.Start_Date 80 end) as JobDate, 81 82 --离退休值时间 83 (case when pp.Fk_PersonnelType_Code in ('1','2') then pe.RetireDate 84 when pp.Fk_PersonnelType_Code in ('3') then d.RetireDate 85 when pp.Fk_PersonnelType_Code in ('4') then '1992-08-09' 86 when pp.Fk_PersonnelType_Code in ('5') then t.End_Date 87 end) as RetireDate, 88 89 --离退休时年龄 90 '60' as PeoPleAge, 91 92 --连续工龄或工作年限 93 (case when pp.Fk_PersonnelType_Code in ('1','2') then pe.SuccessionWorkYear 94 when pp.Fk_PersonnelType_Code in ('3') then (select p.SuccessionWorkYear from Personnel as p where p.id=d.Fk_Personnel_Id) 95 when pp.Fk_PersonnelType_Code in ('4') then '60' 96 when pp.Fk_PersonnelType_Code in ('5') then '20' 97 end) as SuccessionWorkYear, 98 99 --折算工龄 100 '30' as ZsuGl, 101 102 --连续工龄或工作年限 103 (case when pp.Fk_PersonnelType_Code in ('1','2') then pe.SuccessionWorkYear 104 when pp.Fk_PersonnelType_Code in ('3') then (select p.SuccessionWorkYear from Personnel as p where p.id=d.Fk_Personnel_Id) 105 when pp.Fk_PersonnelType_Code in ('4') then '60' 106 when pp.Fk_PersonnelType_Code in ('5') then '20' 107 end) as SuccessionWorkYearTwo, 108 109 --社会保障号码 110 (case when pp.Fk_PersonnelType_Code in ('1','2') then pe.SocialCode 111 when pp.Fk_PersonnelType_Code in ('3') then (select p.SocialCode from Personnel as p where p.id=d.Fk_Personnel_Id) 112 when pp.Fk_PersonnelType_Code in ('4') then '111' 113 when pp.Fk_PersonnelType_Code in ('5') then '111' 114 end) as SocialCode, 115 116 --学历 117 (case when pp.Fk_PersonnelType_Code in ('1','2') then dbo.eduLevelInfo(pe.Fk_eduLevel_code) 118 when pp.Fk_PersonnelType_Code in ('3') then (select dbo.eduLevelInfo(p.Fk_eduLevel_code) from Personnel as p where p.id=d.Fk_Personnel_Id) 119 when pp.Fk_PersonnelType_Code in ('4') then '08' 120 when pp.Fk_PersonnelType_Code in ('5') then dbo.eduLevelInfo(t.Fk_EduLevel_Code) 121 end) as Fk_eduLevel_code, 122 123 --个人使用编制信息 124 (case when pp.Fk_PersonnelType_Code in ('1','2') then dbo.AuthorizedTwo(pe.Fk_Authorized_2) 125 when pp.Fk_PersonnelType_Code in ('3') then (select dbo.AuthorizedTwo(p.Fk_Authorized_2) from Personnel as p where p.id=d.Fk_Personnel_Id) 126 when pp.Fk_PersonnelType_Code in ('4') then '0101001' 127 when pp.Fk_PersonnelType_Code in ('5') then '0101001' 128 end) as Fk_Authorized_2, 129 130 --人员身份 131 (case when pp.Fk_PersonnelType_Code in ('1','2') then dbo.personalIdentityInfo(pe.Fk_personalIdentity_id) 132 when pp.Fk_PersonnelType_Code in ('3') then (select dbo.personalIdentityInfo(p.Fk_personalIdentity_id) from Personnel as p where p.id=d.Fk_Personnel_Id) 133 when pp.Fk_PersonnelType_Code in ('4') then '0101001' 134 when pp.Fk_PersonnelType_Code in ('5') then '0101001' 135 end) as Fk_personalIdentity_id, 136 137 --职级 138 (case when pp.Fk_PersonnelType_Code in ('1','2') then dbo.positionLevelInfo(pe.Fksa_positionLevel_code) 139 when pp.Fk_PersonnelType_Code in ('3') then (select dbo.positionLevelInfo(p.Fksa_positionLevel_code) from Personnel as p where p.id=d.Fk_Personnel_Id) 140 when pp.Fk_PersonnelType_Code in ('4') then '0101001' 141 when pp.Fk_PersonnelType_Code in ('5') then '0101001' 142 end) as Fksa_positionLevel_code, 143 144 --衔级 145 '144' as Xianji, 146 147 --执行工资制度 148 (case when pp.Fk_PersonnelType_Code in ('1','2') then dbo.personalIdentityMoney(pe.Fk_personalIdentity_id) 149 when pp.Fk_PersonnelType_Code in ('3') then (select dbo.personalIdentityMoney(p.Fk_personalIdentity_id) from Personnel as p where p.id=d.Fk_Personnel_Id) 150 when pp.Fk_PersonnelType_Code in ('4') then '0101001' 151 when pp.Fk_PersonnelType_Code in ('5') then '0101001' 152 end) as personalIdentityMoney, 153 154 --交换文件类型 155 '0' as JHFIleType, 156 157 --排序序号(预算单位编码+人员编号） 158 (case when pp.Fk_PersonnelType_Code in ('1','2') then (u.Finance_code+pe.Code) 159 when pp.Fk_PersonnelType_Code in ('4','5') then (u.Finance_code+'01') 160 when pp.Fk_PersonnelType_Code in ('3') then (select (u.Finance_code+p.Code) from Personnel as p where p.id=d.Fk_Personnel_Id) 161 end) as Fincode, 162 163 --单位内设部门 164 '0' as UnitsBuMen, 165 166 --银行 167 dbo.BankInfo(pp.Fk_bank_code) as Fk_bank_code, 168 169 --开户行 170 b.name as BankName, 171 172 --银行帐号 173 pp.CardCode, 174 175 --自筹资金 176 '0' as ZCMOney, 177 178 --（公务员）职务工资 179 (case when pp.Fk_personalIdentity_id in('1','2') then pp.PaySalary else 0 180 end) as GPaySalary, 181 182 --（公务员）级别工资 183 (case when pp.Fk_personalIdentity_id in('1','2') then pp.RealSalary else 0 184 end) as GRealSalary, 185 186 187 --机关工勤人员岗位工资 188 (case when pp.Fk_personalIdentity_id in('5','6') then pp.PaySalary else 0 189 end) as JPaySalary, 190 191 --机关工勤人员技术等级工资 192 (case when pp.Fk_personalIdentity_id in('5','6') then pp.RealSalary else 0 193 end) as JRealSalary, 194 195 --（事业单位工作人员）岗位工资 196 (case when pp.Fk_personalIdentity_id in('3','4','7','8') then pp.PaySalary else 0 197 end) as SPaySalary, 198 199 --（事业单位工作人员）薪级工资 200 (case when pp.Fk_personalIdentity_id in('3','4','7','8') then pp.RealSalary else 0 201 end) as SRealSalary, 202 203 --（事业单位工作人员）教师护士提高10%工资 204 sum(pp.ImproveSalary) as SYJHTG, 205 206 --运动员基本津贴 207 '0' as YDJBJT, 208 209 --运动员成绩津贴 210 '0' as YDCJJT, 211 212 --月基本离退休费 213 pp.RetirePension, 214 215 --艰苦边远地区津贴 216 pp.RemoteAreaAllowance, 217 218 --中小学教师的教龄津贴 219 case when pp.ImproveType='1' then pp.NurseAllowance else 0 end as ZXXJSJLJT, 220 221 --中小学班主任津贴 222 ((case when pp.Fk1_SpecialAllowance_Code in ('036','037','038','039','040','041') then pp.SpecialAllowanc1 else 0 end)+ 223 (case when pp.Fk2_SpecialAllowance_Code in ('036','037','038','039','040','041') then pp.SpecialAllowanc2 else 0 end)+ 224 (case when pp.Fk3_SpecialAllowance_Code in ('036','037','038','039','040','041') then pp.SpecialAllowanc3 else 0 end)) as ZXXBZRJT, 225 226 --中小学特级教师津贴 227 ((case when pp.Fk1_SpecialAllowance_Code ='042' then pp.SpecialAllowanc1 else 0 end)+ 228 (case when pp.Fk2_SpecialAllowance_Code ='042' then pp.SpecialAllowanc2 else 0 end)+ 229 (case when pp.Fk3_SpecialAllowance_Code ='042' then pp.SpecialAllowanc3 else 0 end)) as ZXXTJJT, 230 231 --特教津贴 232 ((case when pp.Fk1_SpecialAllowance_Code ='043' then pp.SpecialAllowanc1 else 0 end)+ 233 (case when pp.Fk2_SpecialAllowance_Code ='043' then pp.SpecialAllowanc2 else 0 end)+ 234 (case when pp.Fk3_SpecialAllowance_Code ='043' then pp.SpecialAllowanc3 else 0 end)) as TJJT, 235 236 --警衔津贴 237 pp.PoliceAllowance, 238 239 --人民警察值勤岗位津贴 240 ((case when pp.Fk1_SpecialAllowance_Code in ('009','083','084','089','090') then pp.SpecialAllowanc1 else 0 end)+ 241 (case when pp.Fk2_SpecialAllowance_Code in ('009','083','084','089','090') then pp.SpecialAllowanc2 else 0 end)+ 242 (case when pp.Fk3_SpecialAllowance_Code in ('009','083','084','089','090') then pp.SpecialAllowanc3 else 0 end)) as RMJZQJ, 243 244 --检察官检察津贴 245 ((case when pp.Fk1_SpecialAllowance_Code ='012' then pp.SpecialAllowanc1 else 0 end)+ 246 (case when pp.Fk2_SpecialAllowance_Code ='012' then pp.SpecialAllowanc2 else 0 end)+ 247 (case when pp.Fk3_SpecialAllowance_Code ='012' then pp.SpecialAllowanc3 else 0 end)) as JCGJCJ, 248 249 --检察院工作人员办案补贴 250 '0' as JCYGZRYBJ, 251 252 --司法助理人员岗位津贴 253 '0' as SFZYGJ, 254 255 --法官审判津贴 256 '0' as FGSJ, 257 258 --法院工作人员办案补贴 259 ((case when pp.Fk1_SpecialAllowance_Code ='011' then pp.SpecialAllowanc1 else 0 end)+ 260 (case when pp.Fk2_SpecialAllowance_Code ='011' then pp.SpecialAllowanc2 else 0 end)+ 261 (case when pp.Fk3_SpecialAllowance_Code ='011' then pp.SpecialAllowanc3 else 0 end)) as FGRYJ, 262 263 --纪委监察人员办案补贴 264 ((case when pp.Fk1_SpecialAllowance_Code ='013' then pp.SpecialAllowanc1 else 0 end)+ 265 (case when pp.Fk2_SpecialAllowance_Code ='013' then pp.SpecialAllowanc2 else 0 end)+ 266 (case when pp.Fk3_SpecialAllowance_Code ='013' then pp.SpecialAllowanc3 else 0 end)) as JWJDRYJ, 267 268 --审计工作人员办案补贴 269 ((case when pp.Fk1_SpecialAllowance_Code ='010' then pp.SpecialAllowanc1 else 0 end)+ 270 (case when pp.Fk2_SpecialAllowance_Code ='010' then pp.SpecialAllowanc2 else 0 end)+ 271 (case when pp.Fk3_SpecialAllowance_Code ='010' then pp.SpecialAllowanc3 else 0 end)) as SJGRYJ, 272 273 --税务工作人员税务征收津贴 274 '0' as SWGRYZJ, 275 276 --海关工作人员津贴 277 '0' as HGGZRYJ, 278 279 --密码工作人员岗位津贴 280 ((case when pp.Fk1_SpecialAllowance_Code in ('015','016','017') then pp.SpecialAllowanc1 else 0 end)+ 281 (case when pp.Fk2_SpecialAllowance_Code in ('015','016','017') then pp.SpecialAllowanc2 else 0 end)+ 282 (case when pp.Fk3_SpecialAllowance_Code in ('015','016','017') then pp.SpecialAllowanc3 else 0 end)) as MMRYGJ, 283 284 --广播电视天线工岗位津贴 285 ((case when pp.Fk1_SpecialAllowance_Code in ('055','056','058','071') then pp.SpecialAllowanc1 else 0 end)+ 286 (case when pp.Fk2_SpecialAllowance_Code in ('055','056','058','071') then pp.SpecialAllowanc2 else 0 end)+ 287 (case when pp.Fk3_SpecialAllowance_Code in ('055','056','058','071') then pp.SpecialAllowanc3 else 0 end)) as GBDSTGJ, 288 289 --广播电视播音员津贴 290 '0' as GBDSBRYJ, 291 292 --合同制工人10%工资性补贴 293 '0' as HTZRYSGJ, 294 295 --地质测探野外工作津贴 296 '0' as DZCTGJ, 297 298 --艰苦气象台站津贴 299 ((case when pp.Fk1_SpecialAllowance_Code in ('106','107','108','109','110','111') then pp.SpecialAllowanc1 else 0 end)+ 300 (case when pp.Fk2_SpecialAllowance_Code in ('106','107','108','109','110','111') then pp.SpecialAllowanc2 else 0 end)+ 301 (case when pp.Fk3_SpecialAllowance_Code in ('106','107','108','109','110','111') then pp.SpecialAllowanc3 else 0 end)) as JKQXTJ, 302 303 --艰苦广播台站津贴 304 ((case when pp.Fk1_SpecialAllowance_Code in ('065','066','067','068','069','070') then pp.SpecialAllowanc1 else 0 end)+ 305 (case when pp.Fk2_SpecialAllowance_Code in ('065','066','067','068','069','070') then pp.SpecialAllowanc2 else 0 end)+ 306 (case when pp.Fk3_SpecialAllowance_Code in ('065','066','067','068','069','070') then pp.SpecialAllowanc3 else 0 end)) as JKGBTJ, 307 308 --艰苦地震台站津贴 309 ((case when pp.Fk1_SpecialAllowance_Code ='098' then pp.SpecialAllowanc1 else 0 end)+ 310 (case when pp.Fk2_SpecialAllowance_Code ='098' then pp.SpecialAllowanc2 else 0 end)+ 311 (case when pp.Fk3_SpecialAllowance_Code ='098' then pp.SpecialAllowanc3 else 0 end)) as JKDZTZJ, 312 313 --护士护龄津贴 314 (case when pp.ImproveType='2' then pp.NurseAllowance else 0 end) as HSHLJ, 315 316 --医疗卫生津贴 317 '0' as YLWSJT, 318 319 --卫生防疫津贴 320 ((case when pp.Fk1_SpecialAllowance_Code in ('076','077','078','079') then pp.SpecialAllowanc1 else 0 end)+ 321 (case when pp.Fk2_SpecialAllowance_Code in ('076','077','078','079') then pp.SpecialAllowanc2 else 0 end)+ 322 (case when pp.Fk3_SpecialAllowance_Code in ('076','077','078','079') then pp.SpecialAllowanc3 else 0 end)) as WSFYJT, 323 324 --殡葬岗位津贴 325 ((case when pp.Fk1_SpecialAllowance_Code in ('100','101','102','103','104','105') then pp.SpecialAllowanc1 else 0 end)+ 326 (case when pp.Fk2_SpecialAllowance_Code in ('100','101','102','103','104','105') then pp.SpecialAllowanc2 else 0 end)+ 327 (case when pp.Fk3_SpecialAllowance_Code in ('100','101','102','103','104','105') then pp.SpecialAllowanc3 else 0 end)) as BZGWJT, 328 329 --环境保护监测津贴 330 ((case when pp.Fk1_SpecialAllowance_Code in ('019','020','021') then pp.SpecialAllowanc1 else 0 end)+ 331 (case when pp.Fk2_SpecialAllowance_Code in ('019','020','021') then pp.SpecialAllowanc2 else 0 end)+ 332 (case when pp.Fk3_SpecialAllowance_Code in ('019','020','021') then pp.SpecialAllowanc3 else 0 end)) as HJBHJCJT, 333 334 --城市下水道和环卫工人岗位津贴 335 ((case when pp.Fk1_SpecialAllowance_Code in ('023','024','025') then pp.SpecialAllowanc1 else 0 end)+ 336 (case when pp.Fk2_SpecialAllowance_Code in ('023','024','025') then pp.SpecialAllowanc2 else 0 end)+ 337 (case when pp.Fk3_SpecialAllowance_Code in ('023','024','025') then pp.SpecialAllowanc3 else 0 end)) as CSXHGWJT, 338 339 --农业事业单位人员有毒保健津贴 340 ((case when pp.Fk1_SpecialAllowance_Code in ('027','028','029') then pp.SpecialAllowanc1 else 0 end)+ 341 (case when pp.Fk2_SpecialAllowance_Code in ('027','028','029') then pp.SpecialAllowanc2 else 0 end)+ 342 (case when pp.Fk3_SpecialAllowance_Code in ('027','028','029') then pp.SpecialAllowanc3 else 0 end)) as NYDRYDJT, 343 344 --畜牧兽医医疗卫生津贴 345 ((case when pp.Fk1_SpecialAllowance_Code in ('031','032','033') then pp.SpecialAllowanc1 else 0 end)+ 346 (case when pp.Fk2_SpecialAllowance_Code in ('031','032','033') then pp.SpecialAllowanc2 else 0 end)+ 347 (case when pp.Fk3_SpecialAllowance_Code in ('031','032','033') then pp.SpecialAllowanc3 else 0 end)) as XUSYYLJT, 348 349 --独生子女费 350 pp.ChildAllowance, 351 352 --副省级以上干部自用费 353 '0' as FSGBZMoney, 354 355 --于1993年工改保留的津贴补贴 356 pp.RetainAllowance as GGBLJBT, 357 358 359 --于1994年工改保留的超4个月奖金 360 '0' as GGJAJ, 361 362 --于2006年工改工资超过30%的部分 363 '0' as GGGZCBF, 364 365 --于2006年特殊岗位工资提高部分 366 '0' as TSGWGTG, 367 368 --老粮贴 369 '0' as Allowance03, 370 371 --回民补贴 372 pp.MuslimAllowance, 373 374 --因公伤残人员护理补助 375 '0' as YGSCRYBZ, 376 377 --红军生活补助费 378 '0' as HJSHBZF, 379 380 --部分离休干部1-2月的生活补助 381 '0' as BFLXGBSHBZ, 382 383 --生活长期不能自理的离休干部护理费 384 '0' as SHCQLXGHLF, 385 386 --老红军护理费 387 '0' as LHJHLIF, 388 389 --部分离退休专家生活补贴 390 '0' as BFLTUSBJ, 391 392 --其他国家津补贴项目 393 (pp.Allowance01+pp.Allowance02+pp.Allowance03+pp.Allowance04+pp.Allowance05+ 394 pp.AchievementsAllowance+pp.Allowance06+pp.Allowance07+pp.Allowance08+ 395 pp.Allowance09+pp.Allowance19) as Allowance14, 396 397 --公务员规范后的津贴补贴 398 (cast(pp.WorkAllowance as decimal)+ cast(pp.LifeAllowance as decimal)) as GFHJBT, 399 400 --（非公务员）驻蓉生活补贴 401 '0' as FGZRSHBT, 402 403 --（非公务员）适当补贴 404 '0' as FGWYSDBT, 405 406 --于2006年工改中小学教师百分之10补贴 407 '0' as GGZXXJT, 408 409 --非公务员信访工人员岗位工作津贴 410 ((case when pp.Fk1_SpecialAllowance_Code in ('092','093') then pp.SpecialAllowanc1 else 0 end)+ 411 (case when pp.Fk2_SpecialAllowance_Code in ('092','093') then pp.SpecialAllowanc2 else 0 end)+ 412 (case when pp.Fk3_SpecialAllowance_Code in ('092','093') then pp.SpecialAllowanc3 else 0 end)) as GWYXFJT, 413 414 --非公务员老干部工作人员岗位津贴 415 ((case when pp.Fk1_SpecialAllowance_Code in ('002','003') then pp.SpecialAllowanc1 else 0 end)+ 416 (case when pp.Fk2_SpecialAllowance_Code in ('002','003') then pp.SpecialAllowanc2 else 0 end)+ 417 (case when pp.Fk3_SpecialAllowance_Code in ('002','003') then pp.SpecialAllowanc3 else 0 end)) as FGWLGBGJT, 418 419 --非公务员民族地区工作补贴 420 '0' as FGWYMZBT, 421 422 --汽车驾驶员津贴 423 pp.DriveAllowance, 424 425 --非公务员增收节支削峰填谷政策 426 '0' as FZJZXZC, 427 428 --非公务员离退休干部交通费补贴 429 '0' as FGWLTXGJTBT, 430 431 --非公务员无子女孤寡退休人员补贴 432 '0' as FWZNGTBT, 433 434 --非公务员其他地方出台津补贴项目 435 (pp.Allowance11+pp.Allowance12+pp.Allowance13+pp.Allowance14+pp.Allowance15+ 436 pp.Allowance16+pp.Allowance17+pp.Allowance18+pp.Allowance20 437 ) as FGQTDFBTXM, 438 439 --按月住房津贴 440 '0' as AYZFJT, 441 442 --事业单位离退休人员生活补贴 443 pp.RetireAllowance as SYDWLTSBT, 444 445 --特殊教育津贴 446 '0' as TSJYJT, 447 448 --补发工资 449 '0' as BFGZ, 450 451 --目标奖 452 '0' as MBJ, 453 454 --年中一次性补贴 455 pp.OneSalary, 456 457 --住房补贴 458 '0' as ZFBT, 459 460 --其他补贴一 461 (pp.LegataryPay+pp.TempPay+pp.DecedentPay) as OtherSupplyOne, 462 463 --备注其他补贴一 464 '遗嘱临时工死亡抚恤' as BZQTY, 465 466 --其他补贴二 467 (pp.OtherSupplyTwo+pp.OtherSupplyOne) as OtherSupplyTwo, 468 469 --备注其他补贴二 470 '单位补发数' as BZQTE, 471 472 --住房公积金 473 pp.HouseFund, 474 475 --职工医疗保险费 476 pp.MedicalInsurance, 477 478 --职工个人教育费 479 pp.NurseAllowance, 480 481 --个人所得税 482 pp.Tax, 483 484 --扣发工资 485 '0' as KFGZ, 486 487 --扣房租 488 pp.RoomRate, 489 490 --扣水费 491 pp.WaterRate, 492 493 --扣电话费 494 '0' as KDHF, 495 496 --扣电费 497 pp.PowerRate, 498 499 --扣气费 500 pp.GasRate, 501 502 --扣住房贷款 503 pp.HousingLoans, 504 505 --扣养老保险 506 pp.OldInsurance, 507 508 --扣公会会费 509 pp.SociatyCost, 510 511 --扣失业保险 512 pp.JobLessInsurance, 513 514 --其他扣款一 515 pp.OtherCostOne, 516 517 --其他扣款二 518 pp.OtherCostTwo, 519 520 --其他扣款三 521 '0' as QTKKS, 522 523 --应发工资 524 pp.PaySalary, 525 526 --实发工资 527 pp.RealSalary, 528 529 --绩效工资 530 pp.AchievementsAllowance, 531 532 --遗嘱补助 533 pp.LegataryPay, 534 535 --扣款合计 536 (cast(pp.PaySalary as decimal)- cast(pp.RealSalary as decimal)) as KOUMEYHEJI 537 538 from 539 ##PersonnelPayout as pp 540 inner join units as u on u.id=pp.Fk_Units_Id 541 inner join Bank as b on b.code=pp.fk_bank_code 542 left join dbo.Personnel as pe on pe.id=pp.Fk_Personnel_Id 543 left join dbo.Decedent as d on d.id=pp.Fk_Personnel_Id 544 left join dbo.Legatary as l on l.id=pp.Fk_Personnel_Id 545 left join dbo.TemporaryPersonnel as t on t.id=pp.Fk_Personnel_Id 546 547 drop table ##PersonnelPayout 548
扫一扫
1283
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
