SELECT t_1.pk_psndoc pk_psndoc,
t_1.pk_group pk_group,
t_1.pyear pyear,
t_1.month month,
t_1.code psncode,
t_1.name psnname,
t_1.glbdef1 glbdef1,
t_1.thisyearyial thisyearyial,
t_1.lastal lastal,
t_1.lastbx lastbx,
nvl(t_1.annualleave, ) annualleave,
nvl(t_1.absenceleave, ) absenceleave,
nvl(t_1.sickleave, ) sickleave,
nvl(t_1.compensatedleave, ) compensatedleave,
t_1.yxhunjia yxhunjia,
t_1.yxsangjia yxsangjia,
t_1.yxgongshang yxgongshang,
t_1.yxchanjia yxchanjia,
t_1.yxchanjian yxchanjian,
t_1.yxpeichan yxpeichan,
t_1.yxshijia yxshijia,
t_1.yxbingjia yxbingjia,
t_1.yxnianjia yxnianjia,
t_1.jynianjia jynianjia,
t_1.jybuxiu jybuxiu,
hi_psnjob.pk_dept pk_dept
FROM (SELECT ld.ym ym,
psn.pk_group pk_group,
psn.pk_psndoc pk_psndoc,
psn.code code,
psn.name name,
psn.glbdef1 glbdef1,
bal.thisyearyial thisyearyial,
bal.lastal lastal,
tbbxv.lastbx lastbx,
bal.yxhunjia yxhunjia,
bal.yxsangjia yxsangjia,
bal.yxgongshang yxgongshang,
bal.yxchanjia yxchanjia,
bal.yxchanjian yxchanjian,
bal.yxpeichan yxpeichan,
bal.yxshijia yxshijia,
bal.yxbingjia yxbingjia,
bal.yxnianjia yxnianjia,
bal.jynianjia jynianjia,
bal.jybuxiu jybuxiu,
ld.annualleave annualleave,
ld.absenceleave absenceleave,
ld.sickleave sickleave,
ld.compensatedleave compensatedleave
FROM bd_psndoc psn
INNER JOIN leave_daily ld
ON ld.pk_psnodc = psn.pk_psndoc
AND ld.pk_org = psn.pk_org
INNER JOIN (SELECT tbbx.pk_org pk_org,
tbbx.pk_psndoc pk_psndoc,
tbbx.curyear curyear,
sum(CASE
WHEN tbbx.pk_timeitem = '10018G1000000000BM2L' THEN
tbbx.realdayorhour - tbbx.yidayorhour -
tbbx.freezedayorhour
ELSE END) lastbx
FROM tbm_leavebalance tbbx
GROUP BY tbbx.pk_org, tbbx.pk_psndoc, tbbx.curyear) tbbxv
ON psn.pk_psndoc = tbbxv.pk_psndoc
AND psn.pk_org = tbbxv.pk_org
AND tbbxv.curyear = substr(ld.ym, , ) -
INNER JOIN (SELECT tb.pk_org pk_org,
tb.pk_psndoc pk_psndoc,
tb.curyear curyear,
sum(CASE
WHEN tb.pk_timeitem = '1002Z710000000021ZLJ' THEN
tb.curdayorhour
ELSE END) thisyearyial,
sum(CASE
WHEN tb.pk_timeitem = '1002Z710000000021ZLJ' THEN
tb.lastdayorhour
ELSE END) lastal,
sum(CASE
WHEN tb.pk_timeitem = '1002Z710000000021ZLF' THEN
tb.yidayorhour + tb.freezedayorhour
ELSE END) yxhunjia,
sum(CASE
WHEN tb.pk_timeitem = '1002Z710000000021ZLN' THEN
tb.yidayorhour + tb.freezedayorhour
ELSE END) yxsangjia,
sum(CASE
WHEN tb.pk_timeitem = '1002Z710000000021ZLP' THEN
tb.yidayorhour + tb.freezedayorhour
ELSE END) yxgongshang,
sum(CASE
WHEN tb.pk_timeitem = '1002Z710000000021ZLH' THEN
tb.yidayorhour + tb.freezedayorhour
ELSE END) yxchanjia,
sum(CASE
WHEN tb.pk_timeitem = '10018G1000000000IOLU' THEN
tb.yidayorhour + tb.freezedayorhour
ELSE END) yxchanjian,
sum(CASE
WHEN tb.pk_timeitem = '10018G1000000000BM2X' THEN
tb.yidayorhour + tb.freezedayorhour
ELSE END) yxpeichan,
sum(CASE
WHEN tb.pk_timeitem = '1002Z710000000021ZLB' THEN
tb.yidayorhour + tb.freezedayorhour
ELSE END) yxshijia,
sum(CASE
WHEN tb.pk_timeitem = '1002Z710000000021ZLD' THEN
tb.yidayorhour + tb.freezedayorhour
ELSE END) yxbingjia,
sum(CASE
WHEN tb.pk_timeitem = '1002Z710000000021ZLJ' THEN
tb.yidayorhour + tb.freezedayorhour
ELSE END) yxnianjia,
sum(CASE
WHEN tb.pk_timeitem = '1002Z710000000021ZLJ' THEN
tb.curdayorhour - tb.yidayorhour -
tb.freezedayorhour
ELSE END) jynianjia,
sum(CASE
WHEN tb.pk_timeitem = '10018G1000000000BM2L' THEN
tb.realdayorhour - tb.yidayorhour -
tb.freezedayorhour
ELSE END) jybuxiu
FROM tbm_leavebalance tb
GROUP BY tb.pk_org, tb.pk_psndoc, tb.curyear) bal
ON psn.pk_psndoc = bal.pk_psndoc
AND psn.pk_org = bal.pk_org
AND bal.curyear = substr(ld.ym, , )
WHERE psn.pk_org = '00016H10000000000BIV') t_1
INNER JOIN hi_psnjob hi_psnjob
ON (t_1.pk_psndoc = hi_psnjob.pk_psndoc AND hi_psnjob.ismainjob = 'Y' AND
hi_psnjob.lastflag = 'Y')
INNER JOIN (SELECT * FROM org_dept) org_dept
ON (hi_psnjob.pk_dept = org_dept.pk_dept)
WHERE org_dept.principal = (SELECT pk_base_doc pk_base_doc FROM cp_user)
ORDER BY t_1.ym