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
