(select '销假申请' type,tbm_leaveoff.pk_psndoc pk_psndoc,'销假'timeitemname,
tbm_leaveoff.leavebegintime,
tbm_leaveoff.leaveendtime,
tbm_leaveoff.differencehour leavehour,
(CASE
WHEN tbm_timeitemcopy.timeitemunit = 0 THEN
'天'
ELSE
'小时'
END) timeitemunit,
tbm_leaveoff.approver,
tbm_leaveoff.approve_time,
(CASE
WHEN tbm_leaveoff.approve_state = 1 THEN
'审批通过'
WHEN tbm_leaveoff.approve_state = 3 THEN
'提交'
WHEN tbm_leaveoff.approve_state = -1 THEN
'未提交'
ELSE
'审批进行中'
END) approve_state,
tbm_leaveoff.pk_group,
substr(to_char((sysdate), 'yyyy-mm-dd'), 1, 4) dddddd,
'原休假时间:'||tbm_leaveoff.regbegintimecopy||' 到 '||tbm_leaveoff.regendtimecopy beizhu
from tbm_leaveoff
INNER JOIN tbm_timeitemcopy tbm_timeitemcopy
ON tbm_leaveoff.pk_leavetypecopy = tbm_timeitemcopy.pk_timeitemcopy)
union all(
select '休假申请' type,
tbm_leaveh.pk_psndoc,
tbm_timeitem.timeitemname timeitemname,
tbm_leaveb.leavebegintime,
tbm_leaveb.leaveendtime,
tbm_leaveb.leavehour leavehour,
(CASE
WHEN tbm_timeitemcopy.timeitemunit = 0 THEN
'天'
ELSE
'小时'
END) timeitemunit,
tbm_leaveh.approver,
tbm_leaveh.approve_time approve_time,
(CASE
WHEN tbm_leaveh.approve_state = 1 THEN
'审批通过'
WHEN tbm_leaveh.approve_state = 3 THEN
'提交'
WHEN tbm_leaveh.approve_state = -1 THEN
'未提交'
ELSE
'审批进行中'
END) approve_state,
tbm_leaveb.pk_group pk_group,
substr(to_char((sysdate), 'yyyy-mm-dd'), 1, 4) dddddd,
(case when tbm_leavereg.isleaveoff='Y' then '有销假' else '未销假' end) beizhu
FROM tbm_leaveb tbm_leaveb
INNER JOIN tbm_leaveh tbm_leaveh
ON tbm_leaveb.pk_leaveh = tbm_leaveh.pk_leaveh
INNER JOIN (SELECT * FROM tbm_timeitem) tbm_timeitem
ON tbm_leaveh.pk_leavetype = tbm_timeitem.pk_timeitem
INNER JOIN tbm_timeitemcopy tbm_timeitemcopy
ON tbm_leaveh.pk_leavetypecopy = tbm_timeitemcopy.pk_timeitemcopy
LEFT JOIN tbm_leavereg tbm_leavereg
ON tbm_leaveb.pk_leaveb = tbm_leavereg.pk_billsourceb)
union all (
select '加班申请' type,
tbm_overtimeh.pk_psndoc,
tbm_timeitem.timeitemname timeitemname,
tbm_overtimeb.overtimebegintime,
tbm_overtimeb.overtimeendtime ,
tbm_overtimeb.overtimehour leavehour,
(CASE
WHEN tbm_timeitemcopy.timeitemunit = 0 THEN
'天'
ELSE
'小时'
END) timeitemunit,
tbm_overtimeh.approver,
tbm_overtimeh.approve_time approve_time,
(CASE
WHEN tbm_overtimeh.approve_state = 1 THEN
'审批通过'
WHEN tbm_overtimeh.approve_state = 3 THEN
'提交'
WHEN tbm_overtimeh.approve_state = -1 THEN
'未提交'
ELSE
'审批进行中'
END) approve_state,
tbm_overtimeb.pk_group pk_group,
substr(to_char((sysdate), 'yyyy-mm-dd'), 1, 4) dddddd,
'转'||bd_defdoc.name beizhu
FROM tbm_overtimeb tbm_overtimeb
INNER JOIN tbm_overtimeh tbm_overtimeh
ON tbm_overtimeb.pk_overtimeh = tbm_overtimeh.pk_overtimeh
INNER JOIN (SELECT * FROM tbm_timeitem) tbm_timeitem
ON tbm_overtimeh.pk_overtimetype = tbm_timeitem.pk_timeitem
INNER JOIN tbm_timeitemcopy tbm_timeitemcopy
ON tbm_overtimeh.pk_overtimetypecopy = tbm_timeitemcopy.pk_timeitemcopy
inner join bd_defdoc on tbm_overtimeh.def4=bd_defdoc.pk_defdoc)
tbm_leaveoff.leavebegintime,
tbm_leaveoff.leaveendtime,
tbm_leaveoff.differencehour leavehour,
(CASE
WHEN tbm_timeitemcopy.timeitemunit = 0 THEN
'天'
ELSE
'小时'
END) timeitemunit,
tbm_leaveoff.approver,
tbm_leaveoff.approve_time,
(CASE
WHEN tbm_leaveoff.approve_state = 1 THEN
'审批通过'
WHEN tbm_leaveoff.approve_state = 3 THEN
'提交'
WHEN tbm_leaveoff.approve_state = -1 THEN
'未提交'
ELSE
'审批进行中'
END) approve_state,
tbm_leaveoff.pk_group,
substr(to_char((sysdate), 'yyyy-mm-dd'), 1, 4) dddddd,
'原休假时间:'||tbm_leaveoff.regbegintimecopy||' 到 '||tbm_leaveoff.regendtimecopy beizhu
from tbm_leaveoff
INNER JOIN tbm_timeitemcopy tbm_timeitemcopy
ON tbm_leaveoff.pk_leavetypecopy = tbm_timeitemcopy.pk_timeitemcopy)
union all(
select '休假申请' type,
tbm_leaveh.pk_psndoc,
tbm_timeitem.timeitemname timeitemname,
tbm_leaveb.leavebegintime,
tbm_leaveb.leaveendtime,
tbm_leaveb.leavehour leavehour,
(CASE
WHEN tbm_timeitemcopy.timeitemunit = 0 THEN
'天'
ELSE
'小时'
END) timeitemunit,
tbm_leaveh.approver,
tbm_leaveh.approve_time approve_time,
(CASE
WHEN tbm_leaveh.approve_state = 1 THEN
'审批通过'
WHEN tbm_leaveh.approve_state = 3 THEN
'提交'
WHEN tbm_leaveh.approve_state = -1 THEN
'未提交'
ELSE
'审批进行中'
END) approve_state,
tbm_leaveb.pk_group pk_group,
substr(to_char((sysdate), 'yyyy-mm-dd'), 1, 4) dddddd,
(case when tbm_leavereg.isleaveoff='Y' then '有销假' else '未销假' end) beizhu
FROM tbm_leaveb tbm_leaveb
INNER JOIN tbm_leaveh tbm_leaveh
ON tbm_leaveb.pk_leaveh = tbm_leaveh.pk_leaveh
INNER JOIN (SELECT * FROM tbm_timeitem) tbm_timeitem
ON tbm_leaveh.pk_leavetype = tbm_timeitem.pk_timeitem
INNER JOIN tbm_timeitemcopy tbm_timeitemcopy
ON tbm_leaveh.pk_leavetypecopy = tbm_timeitemcopy.pk_timeitemcopy
LEFT JOIN tbm_leavereg tbm_leavereg
ON tbm_leaveb.pk_leaveb = tbm_leavereg.pk_billsourceb)
union all (
select '加班申请' type,
tbm_overtimeh.pk_psndoc,
tbm_timeitem.timeitemname timeitemname,
tbm_overtimeb.overtimebegintime,
tbm_overtimeb.overtimeendtime ,
tbm_overtimeb.overtimehour leavehour,
(CASE
WHEN tbm_timeitemcopy.timeitemunit = 0 THEN
'天'
ELSE
'小时'
END) timeitemunit,
tbm_overtimeh.approver,
tbm_overtimeh.approve_time approve_time,
(CASE
WHEN tbm_overtimeh.approve_state = 1 THEN
'审批通过'
WHEN tbm_overtimeh.approve_state = 3 THEN
'提交'
WHEN tbm_overtimeh.approve_state = -1 THEN
'未提交'
ELSE
'审批进行中'
END) approve_state,
tbm_overtimeb.pk_group pk_group,
substr(to_char((sysdate), 'yyyy-mm-dd'), 1, 4) dddddd,
'转'||bd_defdoc.name beizhu
FROM tbm_overtimeb tbm_overtimeb
INNER JOIN tbm_overtimeh tbm_overtimeh
ON tbm_overtimeb.pk_overtimeh = tbm_overtimeh.pk_overtimeh
INNER JOIN (SELECT * FROM tbm_timeitem) tbm_timeitem
ON tbm_overtimeh.pk_overtimetype = tbm_timeitem.pk_timeitem
INNER JOIN tbm_timeitemcopy tbm_timeitemcopy
ON tbm_overtimeh.pk_overtimetypecopy = tbm_timeitemcopy.pk_timeitemcopy
inner join bd_defdoc on tbm_overtimeh.def4=bd_defdoc.pk_defdoc)