笨宝宝啦啦啦~
t distinct nvl((select c1.attribute_valuefrom ess_type c1 where c1.type_id=e.join_company_type),e.join_company_type||'')
joincompanytype,
nvl(e.exercitation,' ') exercitation,
(nvl(e.glide_num,0)+1) glide_num,
nvl(e.emp_count,'0') empCount,
nvl(e.paper_number,' ') papernumber,
nvl(e.chinese_name,' ') chinesename,
nvl(c.compact_id,' ') compactid,
nvl(c.company_info_id,' ') companyinfoid,
decode(e.sex,'F','女','M','男',' ')sex,
nvl((select e.attribute_value from ess_type e wheree.type_id=c.work_content),' ') workcontent,
c.start_date startdate,
case when (select e.attribute_value from ess_type e wheree.type_id=c.term_type)='无固定期限'then '/年/月/日'
when (select e.attribute_value from ess_type e wheree.type_id=c.term_type)='以完成一定任务为期限'then '/年/月/日'
else to_char(c.end_date,'yyyy-MM-dd') end enddate,
nvl((select e.attribute_value from ess_type e where e.type_id=c.term_type),'') termtype,
nvl((select sp.service_point_name from ess_tache et,ess_service_pointsp,ess_require r where et.require_id=r.require_id andr.Server_Id=sp.service_point_id and et.tache_id=#tacheID# and rownum=1),'')visaside,
decode(c.is_probationership,'Y','有试用期','无试用期') isprobationership,
decode(c.is_probationership,'Y',',本合同的试用期从'||to_char(c.start_date,'yyyy-MM-dd')||'起至'||to_char(c.probationership_close_date,'yyyy-MM-dd')||'止。试用期包含在合同期内。' ,'。')probationershipclosedate,
(select vc.payday from vw_compact_re_data vcwhere vc.emp_id=c.emp_id and rownum=1) payday,
nvl((select vc.extra_faith from vw_compact_re_data vc wherevc.emp_id=c.emp_id and rownum=1),' ') extrafaith,
nvl(c.practice_subsidy,' ') practicesubsidy,
nvl(h.department_name,' ') departmentname,
decode(c.recruit_protocol,
'无',
(select t1.attribute_value
from ess_compact_protocol t,ess_type t1
where t.makeup_protocol =t1.type_id
and t.work_content =c.work_content
and t.manage_principal_id =h.manage_principal_id
and t.company_info_id =c.company_info_id),
null,
(select t1.attribute_value
from ess_compact_protocol t,ess_type t1
where t.makeup_protocol =t1.type_id
and t.work_content =c.work_content
and t.manage_principal_id =h.manage_principal_id
and t.company_info_id =c.company_info_id),
' ',
(select t1.attribute_value
from ess_compact_protocol t,ess_type t1
where t.makeup_protocol =t1.type_id
and t.work_content =c.work_content
and t.manage_principal_id =h.manage_principal_id
and t.company_info_id =c.company_info_id),
(select t1.attribute_value from ess_type t1 wheret1.type_id=c.recruit_protocol)) recruitprotocol,
nvl(h.work_side,' ') workside,
case when c.END_DATE=c.fact_end_date then '终止'else '解除' endtType,
nvl(h.station,' ') station,
(select t.attribute_value from ess_type t wheret.type_id=c.compact_end_matter) compantEndMatter,
trunc(c.fact_end_date) fact_end_date,
trunc(e.ps_forecast_different_date) joinCompnyDate,
nvl(floor(months_between((trunc(c.fact_end_date) + 1),trunc(e.ps_forecast_different_date))),0) yearmonth,
nvl(c.compact_no,' ') compactNo,
(select u.user_name from ess_tache et,ess_um_user u,ess_require r whereet.require_id=r.require_id and r.initiate_employee=u.user_id andet.tache_id=#tacheID# and rownum=1) umName,
nvl(c.compact_end_faith,' ') compactEndFaith
fromvw_ess_employee_data e,
ess_compact c,
vw_heresy_move_data h
where e.EMP_ID=c.EMP_ID
and e.EMP_ID=h.EMP_ID
and c.compact_id=#COMPACT_ID#
and rownum=1