server/ses-manage/sql/2019-07/2019-07-29.sql

-- Add/modify columns 
alter table PL_MAJOR_OLD_NEW add old_major_status number(2);
-- Add comments to the columns 
comment on column PL_MAJOR_OLD_NEW.old_major_status
  is '旧专业的状态：转专业、并专业、停考专业';

  
insert into frame_dict (DICT_NAME, DICT_VALUE, DICT_TEXT, DICT_ORDER)
values ('old_major_status', '0', '转专业', '0');

insert into frame_dict (DICT_NAME, DICT_VALUE, DICT_TEXT, DICT_ORDER)
values ('old_major_status', '1', '并专业', '1');

insert into frame_dict (DICT_NAME, DICT_VALUE, DICT_TEXT, DICT_ORDER)
values ('old_major_status', '2', '停考专业', '2');

create sequence SEQ_BY_APPLY_AUDIT;
create sequence SEQ_BY_DIAPLMA;

-- Add/modify columns 
alter table BY_APPLY modify by_photo VARCHAR2(150);

-- Add/modify columns 
alter table KJ_UNIFIED_SCORE_DETAIL modify cert_no VARCHAR2(50);

alter table kj_college_score_detail add major_code varchar2(10);



create or replace view unified_layout_view as
select c.id course_id,
       c.course_code,
       c.course_name,
       c.special_flag,
       reg.cert_no,
       reg.id reg_id,
       reg.std_name,
       1 num,
       t.exam_first_day,
       t.exam_second_day,
       t.exam_third_day,
       enrol.enrol_agent_id exam_area_id,
       org3.org_name exam_area_name,
       kwgx.exam_point_id,
       reg.school_id,
       org1.org_code school_code,
       org1.org_name school_name,
       reg.assist_id,
       org2.org_code help_code,
       org2.org_name help_name,
       enrol.exam_time_id,
       enrol.exam_batch,
       enrol.id enrol_id,
       enrol.ticket_no,
       enrol.status
 from std_enrol enrol
  inner join (
  select r.exam_area_id,r.exam_time_id,r.exam_point_id from kw_exam_relation r,kw_exam_point p
  where r.exam_point_id=p.id and r.exam_time_id=p.exam_time_id
  ) kwgx on kwgx.exam_time_id=enrol.exam_time_id and kwgx.exam_area_id=enrol.enrol_agent_id
  left join pl_course c on enrol.course_id = c.id
  left join std_reg_info reg on reg.id = enrol.std_reg_id
  left join pl_exam_time t on enrol.exam_time_id = t.id
  left join cf_organization org1 on org1.id = reg.school_id
  left join cf_organization org2 on org2.id = reg.assist_id
  left join cf_organization org3 on org3.id = enrol.enrol_agent_id
 where enrol.exam_time_id = (select exam_time_id from cf_operate_time where oper_type = 16
                and start_time<=sysdate and end_time>=sysdate)
   and enrol.status >= 1;