create or replace view v_operation_schedule
(patient_id, visit_id, schedule_id, dept_stayed, bed_no, scheduled_date_time, operating_room, operating_room_no, sequence, diag_before_operation, patient_condition, operation_scale, emergencyin_dicator, isolation_indicator, operating_dept, surgeon, first_assistant, second_assistant, third_assistant, fourth_assistant, anesthesia_method, anesthesia_doctor, second_anesthesia_doctor, third_anesthesia_doctor, anesthesia_assistant, second_anesthesia_assistant, third_anesthesia_assistant, fourth_anesthesia_assistant, blood_tran_doctor, first_operation_nurse, second_operation_nurse, first_supply_nurse, second_supply_nurse, third_supply_nurse, notes_on_operation, req_date_time, entered_by, status, emergency_indicator, sfgr00)
as
select
  to_char(a.BRID00),      --to_char(a.BRID00),杨青梅修改
  b.BRRYCS,
  a.SSDH00,--手术申请标识
  to_char( nvl(b.DQKS00,a.SQKS00)),
  to_char(b.RYCWH0),
  nvl2(a.YYSSRQ,to_date(a.YYSSRQ||a.YYSSSJ,'YYYYMMDDHH24:MI:SS'),
  to_date(a.ssqsRQ||a.ssqsSJ,'YYYYMMDDHH24:MI:SS')),
  to_char(a.SSKS00),
  Trim(a.SSJH00),
  SSTC00,
  a.SQZD00,
  '',--病情说明
  a.SSDJMC,
  decode(a.JZBZ00,'Y','是','否'),
  decode(a.GLDJMC,'正常',1,'隔离',2,'放射',3),
  to_char(a.SQKS00),
  a.SSYS00,
  SF_ZY_FHCF00(a.SSZS00,';',1),
  SF_ZY_FHCF00(a.SSZS00,';',2),
  SF_ZY_FHCF00(a.SSZS00,';',3),
  SF_ZY_FHCF00(a.SSZS00,';',4),
  SF_ZY_FHCF00(a.MZMC00,';',1),--麻醉方法
  SF_ZY_FHCF00(a.MZYS00,';',1),
  SF_ZY_FHCF00(a.MZYS00,';',2),
  SF_ZY_FHCF00(a.MZYS00,';',3),
  '',--麻醉助手一
  '',--麻醉助手二
  '',--麻醉助手三
  '',--麻醉助手四
  SF_ZY_FHCF00(a.SXYSXM,';',1),
  SF_ZY_FHCF00(a.TSHSXM,';',1),
  SF_ZY_FHCF00(a.TSHSXM,';',2),
  SF_ZY_FHCF00(a.GYHSXM,';',1),
  SF_ZY_FHCF00(a.GYHSXM,';',2),
  SF_ZY_FHCF00(a.GYHSXM,';',3),
  a.BZ0000,
  to_date(a.SQRQ00||a.SQSJ00,'YYYYMMDDHH24:MI:SS') ,
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.SQYS00),--录入者
  decode(a.SSZT00,'0','N','1','G','7','N','4','F','3','C','N'),--'0'申请'1'退回'2'审批(安排)'3'取消'4'已做(术后登记)'6'未发送'7'相关医嘱被撤消
  decode(a.jzbz00,'Y',1,'N',0,A.JZBZ00),
  A.SFGR00
from SS_YW0000 a,ZY_BRXXB0 b
where a.ZYGHID=b.ZYID00(+) and b.brzt00='20' and
      a.SSQSRQ > '20121201' and
      b.BRRYCS is not null;