1 SELECT t_1.pk_psndoc pk_psndoc, 2 t_1.pk_group pk_group, 3 t_1.pyear pyear, 4 t_1.month month, 5 t_1.code psncode, 6 t_1.name psnname, 7 t_1.glbdef1 glbdef1, 8 t_1.thisyearyial thisyearyial, 9 t_1.lastal lastal, 10 t_1.lastbx lastbx, 11 nvl(t_1.annualleave, 0) annualleave, 12 nvl(t_1.absenceleave, 0) absenceleave, 13 nvl(t_1.sickleave, 0) sickleave, 14 nvl(t_1.compensatedleave, 0) compensatedleave, 15 t_1.yxhunjia yxhunjia, 16 t_1.yxsangjia yxsangjia, 17 t_1.yxgongshang yxgongshang, 18 t_1.yxchanjia yxchanjia, 19 t_1.yxchanjian yxchanjian, 20 t_1.yxpeichan yxpeichan, 21 t_1.yxshijia yxshijia, 22 t_1.yxbingjia yxbingjia, 23 t_1.yxnianjia yxnianjia, 24 t_1.jynianjia jynianjia, 25 t_1.jybuxiu jybuxiu, 26 hi_psnjob.pk_dept pk_dept 27 FROM (SELECT ld.ym ym, 28 psn.pk_group pk_group, 29 psn.pk_psndoc pk_psndoc, 30 psn.code code, 31 psn.name name, 32 psn.glbdef1 glbdef1, 33 bal.thisyearyial thisyearyial, 34 bal.lastal lastal, 35 tbbxv.lastbx lastbx, 36 bal.yxhunjia yxhunjia, 37 bal.yxsangjia yxsangjia, 38 bal.yxgongshang yxgongshang, 39 bal.yxchanjia yxchanjia, 40 bal.yxchanjian yxchanjian, 41 bal.yxpeichan yxpeichan, 42 bal.yxshijia yxshijia, 43 bal.yxbingjia yxbingjia, 44 bal.yxnianjia yxnianjia, 45 bal.jynianjia jynianjia, 46 bal.jybuxiu jybuxiu, 47 ld.annualleave annualleave, 48 ld.absenceleave absenceleave, 49 ld.sickleave sickleave, 50 ld.compensatedleave compensatedleave 51 FROM bd_psndoc psn 52 INNER JOIN leave_daily ld 53 ON ld.pk_psnodc = psn.pk_psndoc 54 AND ld.pk_org = psn.pk_org 55 INNER JOIN (SELECT tbbx.pk_org pk_org, 56 tbbx.pk_psndoc pk_psndoc, 57 tbbx.curyear curyear, 58 sum(CASE 59 WHEN tbbx.pk_timeitem = '10018G1000000000BM2L' THEN 60 tbbx.realdayorhour - tbbx.yidayorhour - 61 tbbx.freezedayorhour 62 ELSE 63 0 64 END) lastbx 65 FROM tbm_leavebalance tbbx 66 GROUP BY tbbx.pk_org, tbbx.pk_psndoc, tbbx.curyear) tbbxv 67 ON psn.pk_psndoc = tbbxv.pk_psndoc 68 AND psn.pk_org = tbbxv.pk_org 69 AND tbbxv.curyear = substr(ld.ym, 0, 4) - 1 70 INNER JOIN (SELECT tb.pk_org pk_org, 71 tb.pk_psndoc pk_psndoc, 72 tb.curyear curyear, 73 sum(CASE 74 WHEN tb.pk_timeitem = '1002Z710000000021ZLJ' THEN 75 tb.curdayorhour 76 ELSE 77 0 78 END) thisyearyial, 79 sum(CASE 80 WHEN tb.pk_timeitem = '1002Z710000000021ZLJ' THEN 81 tb.lastdayorhour 82 ELSE 83 0 84 END) lastal, 85 sum(CASE 86 WHEN tb.pk_timeitem = '1002Z710000000021ZLF' THEN 87 tb.yidayorhour + tb.freezedayorhour 88 ELSE 89 0 90 END) yxhunjia, 91 sum(CASE 92 WHEN tb.pk_timeitem = '1002Z710000000021ZLN' THEN 93 tb.yidayorhour + tb.freezedayorhour 94 ELSE 95 0 96 END) yxsangjia, 97 sum(CASE 98 WHEN tb.pk_timeitem = '1002Z710000000021ZLP' THEN 99 tb.yidayorhour + tb.freezedayorhour 100 ELSE 101 0 102 END) yxgongshang, 103 sum(CASE 104 WHEN tb.pk_timeitem = '1002Z710000000021ZLH' THEN 105 tb.yidayorhour + tb.freezedayorhour 106 ELSE 107 0 108 END) yxchanjia, 109 sum(CASE 110 WHEN tb.pk_timeitem = '10018G1000000000IOLU' THEN 111 tb.yidayorhour + tb.freezedayorhour 112 ELSE 113 0 114 END) yxchanjian, 115 sum(CASE 116 WHEN tb.pk_timeitem = '10018G1000000000BM2X' THEN 117 tb.yidayorhour + tb.freezedayorhour 118 ELSE 119 0 120 END) yxpeichan, 121 sum(CASE 122 WHEN tb.pk_timeitem = '1002Z710000000021ZLB' THEN 123 tb.yidayorhour + tb.freezedayorhour 124 ELSE 125 0 126 END) yxshijia, 127 sum(CASE 128 WHEN tb.pk_timeitem = '1002Z710000000021ZLD' THEN 129 tb.yidayorhour + tb.freezedayorhour 130 ELSE 131 0 132 END) yxbingjia, 133 sum(CASE 134 WHEN tb.pk_timeitem = '1002Z710000000021ZLJ' THEN 135 tb.yidayorhour + tb.freezedayorhour 136 ELSE 137 0 138 END) yxnianjia, 139 sum(CASE 140 WHEN tb.pk_timeitem = '1002Z710000000021ZLJ' THEN 141 tb.curdayorhour - tb.yidayorhour - 142 tb.freezedayorhour 143 ELSE 144 0 145 END) jynianjia, 146 sum(CASE 147 WHEN tb.pk_timeitem = '10018G1000000000BM2L' THEN 148 tb.realdayorhour - tb.yidayorhour - 149 tb.freezedayorhour 150 ELSE 151 0 152 END) jybuxiu 153 FROM tbm_leavebalance tb 154 GROUP BY tb.pk_org, tb.pk_psndoc, tb.curyear) bal 155 ON psn.pk_psndoc = bal.pk_psndoc 156 AND psn.pk_org = bal.pk_org 157 AND bal.curyear = substr(ld.ym, 0, 4) 158 WHERE psn.pk_org = '00016H10000000000BIV') t_1 159 INNER JOIN hi_psnjob hi_psnjob 160 ON (t_1.pk_psndoc = hi_psnjob.pk_psndoc AND hi_psnjob.ismainjob = 'Y' AND 161 hi_psnjob.lastflag = 'Y') 162 INNER JOIN (SELECT * FROM org_dept) org_dept 163 ON (hi_psnjob.pk_dept = org_dept.pk_dept) 164 WHERE org_dept.principal = (SELECT pk_base_doc pk_base_doc FROM cp_user) 165 ORDER BY t_1.ym