WITH
w_final as (
select
sum_ym
, to_char(add_months(to_date(sum_ym,'YYYYMM'),-1), 'YYYYMM') sum_pym
, substr(sum_ym, 1,4) sum_year
, substr(sum_ym, 5,2) sum_month
, to_char(add_months(to_date(sum_ym,'YYYYMM'),-12), 'YYYY') sum_pyear
, day_month
, day_month - holiday_cnt(sum_ym||'01',1, sum_ym||day_month,1,1) as total_day
, pay_emer
, pay_comm
, pay_intern
, hpay1
, hpay2
, pay_unemins
, #{consultType} search_group
, #{searchNo} search_no
, #{searchNm} search_nm
, #{searchState} search_state
from (
select #{selYear}||#{selMonth} sum_ym
, to_char(last_day(to_date(#{selYear}||#{selMonth},'YYYYMM')),'DD') day_month
from dual
)
, (
select pay_comm, pay_emer, pay_intern
from (select code, trim(cd_desc) cd_desc from tb_cccddatas where cd_classfier = 'C1040')
pivot ( max(cd_desc) for code in ('PY001' as pay_comm
,'PY002' as pay_emer
,'PY003' as pay_intern))
)
, (
select hpay1, hpay2
from (select code, trim(cd_desc) cd_desc from tb_cccddatas where cd_classfier = 'C1041')
pivot ( max(cd_desc) for code in ('PT001' as hpay1
,'PT002' as hpay2))
)
, (
select pay_unemins
from (select code, trim(cd_desc) cd_desc from tb_cccddatas where cd_classfier = 'C1042')
pivot ( max(cd_desc) for code in ('PE001' as pay_unemins))
)
)
, w_nhis AS
(
select
emp_no reg_no
,nm emp_nm
,trim(insurance) insurance
from vi_upload_nh, w_final f
where ins_date like f.sum_ym||'%'
)
, w_nps AS
(
select
substr(pers_contrib, 1,6) reg_no
,nm emp_nm
,base_income
,insur_mon
,trim(empler_contrib) empler_contrib
,substr(pers_contrib, 1,6) pers_contrib
from vi_upload_np, w_final f
where ins_date like f.sum_ym||'%'
)
, w_nt AS
(
select 'Y' data_yn,
pay_mon_lb, pay_mon_ub
, size_fam_deduct01, size_fam_deduct02, size_fam_deduct03, size_fam_deduct04, size_fam_deduct05, size_fam_deduct06, size_fam_deduct07, size_fam_deduct08, size_fam_deduct09, size_fam_deduct10, size_fam_deduct11
from vi_upload_nt, w_final f
where ins_date like f.sum_pyear||'%'
)
, w_incpay AS (
select
ie.emp_no
,ie.grade
,trim(ib.base) inc_base
from vi_upload_ie ie
inner join w_final f on 1=1
inner join vi_upload_ib ib on ib.ins_date like f.sum_ym||'%'and ib.grade=ie.grade
where ie.ins_date like f.sum_ym||'%'
)
, w_agtpay as (
select emp_no, min(confirm_yn) agt_confirm_yn
from tb_cmagtpay, w_final f
where pay_ym = f.sum_ym
and (acdgrp like f.search_group||'%')
and (emp_no like f.search_no||'%')
group by emp_no
)
, w_emp_info as (
select
min(f.total_day) total_day,
nvl(sum(d.cut_day),0) cut_day,
nvl(sum(d.baby_break),0) baby_break,
nvl(sum(d.bear_vac),0) bear_vac,
min(f.total_day)-nvl(sum(d.cut_day),0)-nvl(sum(d.baby_break),0)-nvl(sum(d.bear_vac),0)
+ (SELECT sum(mens)
FROM tb_cmagtduty
WHERE substr(duty_ymd,1,6) = #{selYear}||#{selMonth}
AND emp_no = a.emp_no) real_day_pay_house, -- 가계지원비 계산 시 보건휴가 제외
0 and MIN(substr(f.sum_ym, 5,2)) - MIN(SUBSTR(d.duty_ymd, 5,2)) = '0' -- 첫째달이면
then min(f.total_day) - 0
when NVL(SUM(d.bear_vac),0) <> 0 and MIN(substr(f.sum_ym, 5,2)) - MIN(substr(d.duty_ymd, 5,2)) = '1' -- 둘째달이면
then min(f.total_day) - 0
when NVL(SUM(d.bear_vac),0) <> 0 and MIN(substr(f.sum_ym, 5,2)) - MIN(substr(d.duty_ymd, 5,2)) = '2' -- 세째달이면
then min(f.total_day) - ( (MIN(day_month) - MIN(substr(d.duty_ymd, 7,2))) - holiday_cnt(MIN(f.sum_ym)||MIN(substr(d.duty_ymd, 7,2)),1, MIN(f.sum_ym)||MIN(day_month),1,1) ) -- 휴가시작일부터 이달 마지막날까지 일요일계산해서 뺀다.
when NVL(SUM(d.bear_vac),0) <> 0 and MIN(substr(f.sum_ym, 5,2)) - MIN(substr(d.duty_ymd, 5,2)) = '3' -- 네째달이면
then min(f.total_day) - (MIN(substr(d.duty_ymd, 7,2))) - holiday_cnt(MIN(f.sum_ym)||'01',1, MIN(f.sum_ym)||substr(MIN(d.duty_ymd), 7,2),1,1)
else min(f.total_day)-nvl(sum(d.cut_day),0)-nvl(sum(d.baby_break),0)-nvl(sum(d.bear_vac),0)
end
,
'20',
case
when NVL(SUM(d.baby_break),0) <> 0 and MIN(substr(f.sum_ym, 0,6)) - MIN(SUBSTR(d.duty_ymd, 0,6)) = '0'
then min(f.total_day) - (MIN(substr(d.duty_ymd, 7,2))) - holiday_cnt(MIN(f.sum_ym)||'01',1, MIN(f.sum_ym)||substr(MIN(d.duty_ymd), 7,2),1,1)
when NVL(SUM(d.baby_break),0) <> 0 and MIN(substr(f.sum_ym, 0,6)) - MIN(SUBSTR(ADD_MONTHS(TO_DATE(d.duty_ymd,'YYYYMMDD'), 12), 0,6)) = '0'
then min(f.total_day) - ( (MIN(day_month) - MIN(substr(d.duty_ymd, 7,2))) - holiday_cnt(MIN(f.sum_ym)||MIN(substr(d.duty_ymd, 7,2)),1, MIN(f.sum_ym)||MIN(day_month),1,1) )
else NULL
end
,
'30',
case
when min(a.wk_end_ymd) = min(f.sum_ym||min(SUBSTR(a.wk_end_ymd, 7,2)))
then min(f.total_day) - (MIN(substr(a.wk_end_ymd, 7,2))) - holiday_cnt(MIN(f.sum_ym)||'01',1, MIN(f.sum_ym)||substr(MIN(a.wk_end_ymd)-1, 7,2),1,1)
else NULL
END
,
null
) real_day1,
]]>
min(f.sum_ym) pay_ym,
a.emp_no,
min(a.emp_nm) emp_nm,
min(a.emp_no) reg_no,
min(d.confirm_yn) confirm_yn,
min(b.acd_group) acd_group,
trim(min(b.acd_nm)) acd_nm,
min(a.rule_cd) rule_cd,
min(a.work_emp_type) work_emp_type, -- 고용형태
min(a.wk_status_cd) wk_status_cd, -- 상담원 상태 30 휴직, 20퇴사
min(a.org_yn) org_yn,
min(trim(a.wk_start_ymd)) wk_start_ymd,
min(a.wk_end_ymd) wk_end_ymd,
min(f.day_month) day_month,
nvl(sum(d.absent),0) absent,
nvl(min(inc.inc_base),0) pay_class,
nvl2(min(inc.inc_base),'N','Y') incpay_err,
min(f.total_day) - case when min(trim(a.wk_start_ymd)) is not null and ( to_number(min(trim(a.wk_start_ymd))) - to_number(min(f.sum_ym)||'01') ) > 0
then holiday_cnt(min(f.sum_ym)||'01',1, min(a.wk_start_ymd),0, 1)
else 0 end
- case when min(trim(a.wk_end_ymd)) is not null and ( to_number(min(f.sum_ym)||min(f.day_month)) - to_number(min(trim(a.wk_end_ymd))) +1 ) > 0
then holiday_cnt(min(a.wk_end_ymd),1, min(f.sum_ym)||min(f.day_month),1, 1)
else 0 end
- nvl(sum(d.absent),0)
as day_work,
nvl(sum(late),0)
+ nvl(sum(outting),0)
+ nvl(sum(early),0)
as hpay_minus,
case when min(trim(a.wk_start_ymd)) is null then 0 else floor((min(f.sum_ym)||'31' - min(a.wk_start_ymd)) / 10000) end work_year,
case when min(a.rule_cd) = '62' or min(a.rule_cd) = '10' then 30000 else 0 end pay_assadm,
case when '0' ='1' then 400000 else 0 end pay_tradhol
from tb_caagent a
inner join tb_caacdagt c on a.emp_no = trim(c.emp_no)
inner join tb_ccacdgroup b on b.acd_group = c.acd_group
inner join w_final f on 1=1
left join tb_cmagtduty d on (d.duty_ymd between f.sum_ym||'01' and f.sum_ym||'31' and a.emp_no = d.emp_no and d.confirm_yn = 'Y')
left join w_incpay inc on inc.emp_no = a.emp_no
where --a.org_yn!='Y' and
a.rule_cd in('10','20','70','80','60','55')-- and a.wk_status_cd in ('10', '20', '30')
AND (a.wk_end_ymd IS NULL OR a.wk_end_ymd = f.sum_ym||'%')
group by a.emp_no
)
, w_emp_pay as (
select
a.emp_no,
a.emp_nm,
a.reg_no,
a.acd_group,
a.acd_nm,
a.cut_day,
a.baby_break,
a.bear_vac,
--a.real_day,
a.real_day1,
a.pay_ym,
a.confirm_yn,
a.rule_cd,
a.work_emp_type,
a.wk_status_cd,
a.org_yn,
a.wk_start_ymd,
a.wk_end_ymd,
a.day_month,
a.absent,
a.pay_class,
a.incpay_err,
a.day_work,
a.hpay_minus,
a.work_year,
a.pay_assadm,
a.pay_tradhol,
case when a.work_emp_type = '04' then '0'
when a.acd_group = '1400' then f.hpay2
else f.hpay1 end hpay,
--시용직 기본급(2016.03. 신규 요구사항)
case when a.work_emp_type = '04' then floor(f.pay_intern *a.real_day1 /f.total_day /10)*10
when a.acd_group = '1400' -- 퇴사 '20' 추가
then case when a.wk_status_cd = '30' then 0 else floor(f.pay_emer *a.real_day1 /f.total_day /10)*10 end
else case when a.wk_status_cd = '30' then 0 else floor(f.pay_comm *a.real_day1 /f.total_day /10)*10 end
end pay_basic,
--휴직자 기본급 계산(휴직자 일 경우 가계지원비 차감)
case when a.work_emp_type = '04' then floor(50000 *a.real_day1 /f.total_day /10)*10
else floor(100000 *a.real_day1 /f.total_day /10)*10
end pay_lunch, --중식비(시용직일 경우 절반만 줌)
case when a.work_emp_type = '04' then floor(50000 *a.real_day1 /f.total_day /10)*10
else floor(100000 *a.real_day1 /f.total_day /10)*10
end pay_trans, --교통비(시용직일 경우 절반만 줌)
case when a.work_emp_type = '01' then floor(100000 * a.real_day_pay_house /f.total_day /10)*10 else 0 end pay_house, --가계지원비(01 무기계약직; 2016.03. 신규 요구사항)
case when a.work_year = 0 then 0 else (a.work_year-1) * 10000 + decode(a.work_year,0,0,30000) end pay_senior,
case a.rule_cd
when '70'/*팀장*/ then case when f.total_day = a.day_work then 300000 else floor(300000 *a.day_work /f.day_month /10)*10 end
when '80'/*부팀장*/ then case when f.total_day = a.day_work then 200000 else floor(200000 *a.day_work /f.day_month /10)*10 end
when '20'/*파트장*/ then case when f.total_day = a.day_work then 100000 else floor(100000 *a.day_work /f.day_month /10)*10 end
else 0
end pay_long,
case when a.rule_cd = '11' or a.rule_cd = '61' then floor(50000 *a.day_work /f.day_month /10)*10 else 0 end pay_right -- 야간
from w_emp_info a, w_final f
)
, w_pay_extra as (
select a.emp_no, b.acd_group,
min(a.org_yn) org_yn,
min(a.rule_cd) rule_cd,
min(a.wk_status_cd) wk_status_cd,
min(e.hpay) hpay,
sum(d.extwk) extwk,
sum(d.nightwk) nightwk,
round(case when min(f.sum_month) = '01' then 0 else nvl(sum(d.extwk),0)*1.5*min(e.hpay) end) pay_wk,
round(case when min(f.sum_month) = '01' then 0 else nvl(sum(d.nightwk),0)* 0.5 *min(e.hpay) end) pay_ot
from tb_caagent a
inner join tb_caacdagt c on a.emp_no = trim(c.emp_no)
inner join tb_ccacdgroup b on b.acd_group = c.acd_group
inner join w_emp_pay e on e.emp_no=a.emp_no
inner join w_final f on 1=1
left join tb_cmagtduty d
on ( (
f.sum_month = '12' and d.duty_ymd between f.sum_pym||'01' and f.sum_ym||'31'
or f.sum_month = '01' and d.duty_ymd like f.sum_pym||'%'
or f.sum_month != '12' and f.sum_month != '01' and d.duty_ymd like f.sum_pym||'%'
)
and a.emp_no = d.emp_no and d.confirm_yn = 'Y')
where --a.org_yn!='Y' and
a.rule_cd in('10', '20','70','80','60','55')-- and a.wk_status_cd in ('10', '20', '30')
AND (a.wk_end_ymd IS NULL OR a.wk_end_ymd = f.sum_ym||'%')
and (b.acd_group like f.search_group||'%')
and (a.emp_no like f.search_no||'%')
and (a.emp_nm like f.search_nm||'%')
group by a.emp_no, b.acd_group
)
, w_pay_info as (
select
z.emp_no,
z.emp_nm,
z.reg_no,
z.acd_group,
z.acd_nm,
w.pay_wk,
w.pay_ot,
w.extwk,
w.nightwk,
z.pay_assadm pay_misc,
(z.pay_basic+z.pay_lunch+z.pay_trans+ w.pay_wk+w.pay_ot +z.pay_class+z.pay_long+z.pay_senior+z.pay_assadm+z.pay_house) pay_amt,
z.pay_basic,
z.pay_lunch,
z.pay_trans,
z.pay_class,
z.incpay_err,
z.pay_senior,
z.pay_long,
z.cut_day,
z.baby_break,
z.bear_vac,
--z.real_day,
z.real_day1,
z.pay_ym,
z.confirm_yn,
z.rule_cd,
z.work_emp_type,
z.wk_status_cd,
z.org_yn,
z.wk_start_ymd,
z.wk_end_ymd,
z.day_month,
z.absent,
z.day_work,
z.hpay_minus,
z.work_year,
z.pay_assadm,
z.pay_tradhol,
z.hpay,
z.pay_house,
z.pay_right
from w_emp_pay z, w_pay_extra w
where z.emp_no = w.emp_no
and z.acd_group = w.acd_group
)
, w_deduct as (
select
f.sum_ym,
q.emp_no,
q.reg_no,
nvl2(( select data_yn
from w_nt
where pay_mon_lb * 1000 <=(q.pay_amt-q.pay_lunch) and pay_mon_ub * 1000 >(q.pay_amt-q.pay_lunch)
),'N','Y') nt_err,
nvl(( select size_fam_deduct01
from w_nt
where pay_mon_lb * 1000 <=(q.pay_amt-q.pay_lunch) and pay_mon_ub * 1000 >(q.pay_amt-q.pay_lunch)
),0) tax_income,
floor(nvl(( select size_fam_deduct01
from w_nt
where pay_mon_lb * 1000 <=(q.pay_amt-q.pay_lunch) and pay_mon_ub * 1000 >(q.pay_amt-q.pay_lunch)
),0)/10) tax_jumin,
nvl(w_nps.empler_contrib,0) tax_pension,
nvl2(w_nps.empler_contrib,'N','Y') nps_err,
nvl(w_nhis.insurance, 0) tax_insu,
nvl2(w_nhis.insurance,'N','Y') nhis_err,
floor(NVL(q.pay_amt*f.pay_unemins,0)/10)*10 tax_emp,
q.hpay*q.hpay_minus tax_misc,
q.confirm_yn,
agt_confirm_yn
from w_pay_info q
inner join w_final f on 1=1
left join w_nhis on w_nhis.reg_no = q.reg_no
left join w_nps on w_nps.reg_no = q.reg_no
left join w_agtpay ap on ap.emp_no=q.emp_no
)
, TBL as (
select
r.emp_nm,
case when (trim(d.agt_confirm_yn) is not null and trim(d.agt_confirm_yn) ='Y') then '확정. ' end
|| case when (trim(r.confirm_yn) is null or trim(r.confirm_yn) !='Y') then '근무정보 미확정. ' end
|| case when d.nhis_err='Y' then '건강보험 EXCEL 항목 누락. ' end
|| case when d.nps_err='Y' then '국민연금 EXCEL 항목 누락. ' end
|| case when d.nt_err='Y' then '간이세액표 EXCEL 항목 범위 누락. ' end
|| case when r.incpay_err='Y' then '성과급 EXCEL 항목 누락. ' end
as prc_stat,
r.acd_nm,
r.emp_no,
r.wk_start_ymd,
r.pay_basic,
r.pay_lunch,
r.pay_trans,
r.pay_wk,
r.pay_ot,
r.pay_class,
r.pay_senior,
r.pay_long,
r.pay_house,
r.pay_right,
r.pay_misc,
r.pay_tradhol,
r.pay_amt,
d.tax_pension,
d.tax_insu,
d.tax_emp,
d.tax_income,
d.tax_jumin,
d.tax_misc,
(d.tax_income+d.tax_jumin+d.tax_pension+d.tax_insu+d.tax_emp+d.tax_misc) tax_amt,
nvl((r.pay_amt-(d.tax_income+d.tax_jumin+d.tax_pension+d.tax_insu+d.tax_emp+d.tax_misc)),0) sal,
f.sum_ym,
f.sum_pym,
f.sum_year,
f.sum_month,
f.sum_pyear,
f.day_month,
f.total_day,
f.pay_emer,
f.pay_comm,
f.pay_intern,
f.hpay1,
f.hpay2,
f.pay_unemins,
f.search_no,
f.search_nm,
f.search_state,
r.reg_no,
r.acd_group,
d.nhis_err,
d.nps_err,
d.nt_err,
r.incpay_err,
r.confirm_yn,
d.agt_confirm_yn,
r.cut_day,
r.baby_break,
r.bear_vac,
--r.real_day,
r.real_day1,
r.rule_cd,
r.work_emp_type,
r.wk_status_cd,
r.org_yn,
r.wk_end_ymd,
r.extwk,
r.nightwk,
r.absent,
r.day_work,
r.hpay_minus,
r.work_year,
r.pay_assadm,
r.hpay
from w_pay_info r
inner join w_final f on 1=1
left join w_deduct d on d.emp_no=r.emp_no
where d.nhis_err='Y'
where d.nps_err='Y'
where d.nt_err='Y'
where r.incpay_err='Y'
where (trim(r.confirm_yn) is null or trim(r.confirm_yn) !='Y')
where (trim(d.agt_confirm_yn) is null or trim(d.agt_confirm_yn) !='Y')
)
SELECT * FROM ( SELECT LIST.*, ceil(ROWNUM/#{rows}) AS pageNum, count(ROWNUM) over() - ROWNUM + 1 rnum, CNT.recordNum
FROM (select count(*) AS recordNum from TBL) CNT,
(select * from TBL
order by acd_group, emp_nm
order by ${sidx} ${sord}
) LIST
)
WHERE pageNum=#{page}