create or replace view vw_emr_f_page_oper_syn_his as
select --病案首页  手术信息同步
    A.PAT_MZZYID  as   ZY_ID  ,--  住院流水号(一次住院内部唯一ID)
    ROW_NUMBER()OVER(PARTITION by A.PAT_MZZYID order by A.OPS_DTIME)*10+0 as   SORT  ,--  排序
    to_number(null)  as   MAIN_FLAG  ,--  是否主手术
    A.OPS_CODE  as   OPER_CODE  ,--  手术代码
    A.OPS_STD_NAME  as   OPER_NAME  ,--  手术名称
    A.OPS_DTIME  as   OPER_DTIME  ,--  手术日期
    to_number(null) as   OPER_DURATION  ,--  手术持续时间(小时)
    B.LEVEL_CODE  as   OPER_LEVEL_CODE  ,--  手术级别编码
    ''  as   OPER_TYPE_CODE  ,--  手术-操作类型编码 治疗性操作 诊断性操作 手术
    ''  as   OPER_TYPE_NAME  ,--  手术-操作类型名称 治疗性操作 诊断性操作 手术
    A.OPS_DOCT_CODE  as   OPER_DOCT_CODE  ,--  手术操作人员代码
    A.OPS_DOCT_NAME  as   OPER_DOCT_NAME  ,--  手术操作人员名称
    A.ASSISTANT1_CODE  as   asSISTANT1_CODE  ,--  一助编码
    A.ASSISTANT1_NAME  as   asSISTANT1_NAME  ,--  一助名称
    A.ASSISTANT2_CODE  as   asSISTANT2_CODE  ,--  二助编码
    A.ASSISTANT2_NAME  as   asSISTANT2_NAME  ,--  二助名称
    A.INCISION_HEALING_CODE  as   INCISION_HEALING_CODE  ,--  切口愈合等级代码
    A.INCISION_HEALING_NAME  as   INCISION_HEALING_NAME  ,--  切口愈合等级名称
    A.ANE_METHOD_CODE  as   ANESTHESIA_CODE  ,--  麻醉方式编码
    A.ANE_METHOD_NAME  as   ANESTHESIA_NAME  ,--  麻醉方式名称
    ''  as   ANESTHESIA_LEVEL_CODE  ,--  麻醉分级编码
    A.ANE_DOCT1_CODE  as   ANESTHESIOLOGIST_CODE  ,--  麻醉医师编码
    A.Ane_Doct1_Name  as   ANESTHESIOLOGIST_NAME  ,--  麻醉医师名称
    ''  as   BODY_PART_CODE  --  手术部位编码
from EMR_OPS_APPLY A LEFT JOIN VW_EMR_DICT_OPER_NAME B on A.OPS_CODE = B.OPER_CODE
where 1=1
union all
select --病案首页  手术信息同步
    A.PAT_MZZYID  as   ZY_ID  ,--  住院流水号(一次住院内部唯一ID)
    ROW_NUMBER()OVER(PARTITION by A.PAT_MZZYID order by A.OPS_DTIME)*10+1 as   SORT  ,--  排序
    to_number(null)  as   MAIN_FLAG  ,--  是否主手术
    A.OPS_ASSIST1_CODE  as   OPER_CODE  ,--  手术代码
    A.OPS_ASSIST1_STD_NAME  as   OPER_NAME  ,--  手术名称
    A.OPS_DTIME  as   OPER_DTIME  ,--  手术日期
    to_number(null) as   OPER_DURATION  ,--  手术持续时间(小时)
    B.LEVEL_CODE  as   OPER_LEVEL_CODE  ,--  手术级别编码
    ''  as   OPER_TYPE_CODE  ,--  手术-操作类型编码 治疗性操作 诊断性操作 手术
    ''  as   OPER_TYPE_NAME  ,--  手术-操作类型名称 治疗性操作 诊断性操作 手术
    A.OPS_DOCT_CODE  as   OPER_DOCT_CODE  ,--  手术操作人员代码
    A.OPS_DOCT_NAME  as   OPER_DOCT_NAME  ,--  手术操作人员名称
    A.ASSISTANT1_CODE  as   asSISTANT1_CODE  ,--  一助编码
    A.ASSISTANT1_NAME  as   asSISTANT1_NAME  ,--  一助名称
    A.ASSISTANT2_CODE  as   asSISTANT2_CODE  ,--  二助编码
    A.ASSISTANT2_NAME as   asSISTANT2_NAME  ,--  二助名称
    A.INCISION_HEALING_CODE  as   INCISION_HEALING_CODE  ,--  切口愈合等级代码
    A.INCISION_HEALING_NAME  as   INCISION_HEALING_NAME  ,--  切口愈合等级名称
    A.ANE_METHOD_CODE  as   ANESTHESIA_CODE  ,--  麻醉方式编码
    A.ANE_METHOD_NAME  as   ANESTHESIA_NAME  ,--  麻醉方式名称
    ''  as   ANESTHESIA_LEVEL_CODE  ,--  麻醉分级编码
    A.ANE_DOCT1_CODE  as   ANESTHESIOLOGIST_CODE  ,--  麻醉医师编码
    A.Ane_Doct1_Name  as   ANESTHESIOLOGIST_NAME  ,--  麻醉医师名称
    ''  as   BODY_PART_CODE  --  手术部位编码
from EMR_OPS_APPLY A LEFT JOIN VW_EMR_DICT_OPER_NAME B on A.OPS_ASSIST1_CODE = B.OPER_CODE
where 1=1 and A.OPS_ASSIST1_CODE is not null
union all
select --病案首页  手术信息同步
    A.PAT_MZZYID  as   ZY_ID  ,--  住院流水号(一次住院内部唯一ID)
    ROW_NUMBER()OVER(PARTITION by A.PAT_MZZYID order by A.OPS_DTIME)*10+2 as   SORT  ,--  排序
    to_number(null)  as   MAIN_FLAG  ,--  是否主手术
    A.OPS_ASSIST2_CODE  as   OPER_CODE  ,--  手术代码
    A.OPS_ASSIST2_STD_NAME  as   OPER_NAME  ,--  手术名称
    A.OPS_DTIME  as   OPER_DTIME  ,--  手术日期
    to_number(null) as   OPER_DURATION  ,--  手术持续时间(小时)
    B.LEVEL_CODE  as   OPER_LEVEL_CODE  ,--  手术级别编码
    ''  as   OPER_TYPE_CODE  ,--  手术-操作类型编码 治疗性操作 诊断性操作 手术
    ''  as   OPER_TYPE_NAME  ,--  手术-操作类型名称 治疗性操作 诊断性操作 手术
    A.OPS_DOCT_CODE  as   OPER_DOCT_CODE  ,--  手术操作人员代码
    A.OPS_DOCT_NAME  as   OPER_DOCT_NAME  ,--  手术操作人员名称
    A.ASSISTANT1_CODE  as   asSISTANT1_CODE  ,--  一助编码
    A.ASSISTANT1_NAME  as   asSISTANT1_NAME  ,--  一助名称
    A.ASSISTANT2_CODE  as   asSISTANT2_CODE  ,--  二助编码
    A.ASSISTANT2_NAME as   asSISTANT2_NAME  ,--  二助名称
    A.INCISION_HEALING_CODE  as   INCISION_HEALING_CODE  ,--  切口愈合等级代码
    A.INCISION_HEALING_NAME  as   INCISION_HEALING_NAME  ,--  切口愈合等级名称
    A.ANE_METHOD_CODE  as   ANESTHESIA_CODE  ,--  麻醉方式编码
    A.ANE_METHOD_NAME  as   ANESTHESIA_NAME  ,--  麻醉方式名称
    ''  as   ANESTHESIA_LEVEL_CODE  ,--  麻醉分级编码
    A.ANE_DOCT1_CODE  as   ANESTHESIOLOGIST_CODE  ,--  麻醉医师编码
    A.Ane_Doct1_Name  as   ANESTHESIOLOGIST_NAME  ,--  麻醉医师名称
    ''  as   BODY_PART_CODE  --  手术部位编码
from EMR_OPS_APPLY A LEFT JOIN VW_EMR_DICT_OPER_NAME B on A.OPS_ASSIST2_CODE = B.OPER_CODE
where 1=1 and A.OPS_ASSIST2_CODE is not null
union all
select --病案首页  手术信息同步
    A.PAT_MZZYID  as   ZY_ID  ,--  住院流水号(一次住院内部唯一ID)
    ROW_NUMBER()OVER(PARTITION by A.PAT_MZZYID order by A.OPS_DTIME)*10+3 as   SORT  ,--  排序
    to_number(null)  as   MAIN_FLAG  ,--  是否主手术
    A.OPS_ASSIST3_CODE  as   OPER_CODE  ,--  手术代码
    A.OPS_ASSIST3_STD_NAME  as   OPER_NAME  ,--  手术名称
    A.OPS_DTIME  as   OPER_DTIME  ,--  手术日期
    to_number(null) as   OPER_DURATION  ,--  手术持续时间(小时)
    B.LEVEL_CODE  as   OPER_LEVEL_CODE  ,--  手术级别编码
    ''  as   OPER_TYPE_CODE  ,--  手术-操作类型编码 治疗性操作 诊断性操作 手术
    ''  as   OPER_TYPE_NAME  ,--  手术-操作类型名称 治疗性操作 诊断性操作 手术
    A.OPS_DOCT_CODE  as   OPER_DOCT_CODE  ,--  手术操作人员代码
    A.OPS_DOCT_NAME  as   OPER_DOCT_NAME  ,--  手术操作人员名称
    A.ASSISTANT1_CODE  as   asSISTANT1_CODE  ,--  一助编码
    A.ASSISTANT1_NAME  as   asSISTANT1_NAME  ,--  一助名称
    A.ASSISTANT2_CODE  as   asSISTANT2_CODE  ,--  二助编码
    A.ASSISTANT2_NAME as   asSISTANT2_NAME  ,--  二助名称
    A.INCISION_HEALING_CODE  as   INCISION_HEALING_CODE  ,--  切口愈合等级代码
    A.INCISION_HEALING_NAME  as   INCISION_HEALING_NAME  ,--  切口愈合等级名称
    A.ANE_METHOD_CODE  as   ANESTHESIA_CODE  ,--  麻醉方式编码
    A.ANE_METHOD_NAME  as   ANESTHESIA_NAME  ,--  麻醉方式名称
    ''  as   ANESTHESIA_LEVEL_CODE  ,--  麻醉分级编码
    A.ANE_DOCT1_CODE  as   ANESTHESIOLOGIST_CODE  ,--  麻醉医师编码
    A.Ane_Doct1_Name  as   ANESTHESIOLOGIST_NAME  ,--  麻醉医师名称
    ''  as   BODY_PART_CODE  --  手术部位编码
from EMR_OPS_APPLY A LEFT JOIN VW_EMR_DICT_OPER_NAME B on A.OPS_ASSIST3_CODE = B.OPER_CODE
where 1=1 and A.OPS_ASSIST3_CODE is not null
union all
select --病案首页  手术信息同步
    A.PAT_MZZYID  as   ZY_ID  ,--  住院流水号(一次住院内部唯一ID)
    ROW_NUMBER()OVER(PARTITION by A.PAT_MZZYID order by A.OPS_DTIME)*10+4 as   SORT  ,--  排序
    to_number(null)  as   MAIN_FLAG  ,--  是否主手术
    A.OPS_ASSIST4_CODE  as   OPER_CODE  ,--  手术代码
    A.OPS_ASSIST4_STD_NAME  as   OPER_NAME  ,--  手术名称
    A.OPS_DTIME  as   OPER_DTIME  ,--  手术日期
    to_number(null) as   OPER_DURATION  ,--  手术持续时间(小时)
    B.LEVEL_CODE  as   OPER_LEVEL_CODE  ,--  手术级别编码
    ''  as   OPER_TYPE_CODE  ,--  手术-操作类型编码 治疗性操作 诊断性操作 手术
    ''  as   OPER_TYPE_NAME  ,--  手术-操作类型名称 治疗性操作 诊断性操作 手术
    A.OPS_DOCT_CODE  as   OPER_DOCT_CODE  ,--  手术操作人员代码
    A.OPS_DOCT_NAME  as   OPER_DOCT_NAME  ,--  手术操作人员名称
    A.ASSISTANT1_CODE  as   asSISTANT1_CODE  ,--  一助编码
    A.ASSISTANT1_NAME  as   asSISTANT1_NAME  ,--  一助名称
    A.ASSISTANT2_CODE  as   asSISTANT2_CODE  ,--  二助编码
    A.ASSISTANT2_NAME as   asSISTANT2_NAME  ,--  二助名称
    A.INCISION_HEALING_CODE  as   INCISION_HEALING_CODE  ,--  切口愈合等级代码
    A.INCISION_HEALING_NAME  as   INCISION_HEALING_NAME  ,--  切口愈合等级名称
    A.ANE_METHOD_CODE  as   ANESTHESIA_CODE  ,--  麻醉方式编码
    A.ANE_METHOD_NAME  as   ANESTHESIA_NAME  ,--  麻醉方式名称
    ''  as   ANESTHESIA_LEVEL_CODE  ,--  麻醉分级编码
    A.ANE_DOCT1_CODE  as   ANESTHESIOLOGIST_CODE  ,--  麻醉医师编码
    A.Ane_Doct1_Name  as   ANESTHESIOLOGIST_NAME  ,--  麻醉医师名称
    ''  as   BODY_PART_CODE  --  手术部位编码
from EMR_OPS_APPLY A LEFT JOIN VW_EMR_DICT_OPER_NAME B on A.OPS_ASSIST4_CODE = B.OPER_CODE
where 1=1 and A.OPS_ASSIST4_CODE is not null
union all
select --病案首页  手术信息同步
    A.PAT_MZZYID  as   ZY_ID  ,--  住院流水号(一次住院内部唯一ID)
    ROW_NUMBER()OVER(PARTITION by A.PAT_MZZYID order by A.OPS_DTIME)*10+5 as   SORT  ,--  排序
    to_number(null)  as   MAIN_FLAG  ,--  是否主手术
    A.OPS_ASSIST5_CODE  as   OPER_CODE  ,--  手术代码
    A.OPS_ASSIST5_STD_NAME  as   OPER_NAME  ,--  手术名称
    A.OPS_DTIME  as   OPER_DTIME  ,--  手术日期
    to_number(null) as   OPER_DURATION  ,--  手术持续时间(小时)
    B.LEVEL_CODE  as   OPER_LEVEL_CODE  ,--  手术级别编码
    ''  as   OPER_TYPE_CODE  ,--  手术-操作类型编码 治疗性操作 诊断性操作 手术
    ''  as   OPER_TYPE_NAME  ,--  手术-操作类型名称 治疗性操作 诊断性操作 手术
    A.OPS_DOCT_CODE  as   OPER_DOCT_CODE  ,--  手术操作人员代码
    A.OPS_DOCT_NAME  as   OPER_DOCT_NAME  ,--  手术操作人员名称
    A.ASSISTANT1_CODE  as   asSISTANT1_CODE  ,--  一助编码
    A.ASSISTANT1_NAME  as   asSISTANT1_NAME  ,--  一助名称
    A.ASSISTANT2_CODE  as   asSISTANT2_CODE  ,--  二助编码
    A.ASSISTANT2_NAME as   asSISTANT2_NAME  ,--  二助名称
    A.INCISION_HEALING_CODE  as   INCISION_HEALING_CODE  ,--  切口愈合等级代码
    A.INCISION_HEALING_NAME  as   INCISION_HEALING_NAME  ,--  切口愈合等级名称
    A.ANE_METHOD_CODE  as   ANESTHESIA_CODE  ,--  麻醉方式编码
    A.ANE_METHOD_NAME  as   ANESTHESIA_NAME  ,--  麻醉方式名称
    ''  as   ANESTHESIA_LEVEL_CODE  ,--  麻醉分级编码
    A.ANE_DOCT1_CODE  as   ANESTHESIOLOGIST_CODE  ,--  麻醉医师编码
    A.Ane_Doct1_Name  as   ANESTHESIOLOGIST_NAME  ,--  麻醉医师名称
    ''  as   BODY_PART_CODE  --  手术部位编码
from EMR_OPS_APPLY A LEFT JOIN VW_EMR_DICT_OPER_NAME B on A.OPS_ASSIST5_CODE = B.OPER_CODE
where 1=1 and A.OPS_ASSIST5_CODE is not null
union all
select --病案首页  手术信息同步
    A.PAT_MZZYID  as   ZY_ID  ,--  住院流水号(一次住院内部唯一ID)
    ROW_NUMBER()OVER(PARTITION by A.PAT_MZZYID order by A.OPS_DTIME)*10+6 as   SORT  ,--  排序
    to_number(null)  as   MAIN_FLAG  ,--  是否主手术
    A.OPS_ASSIST6_CODE  as   OPER_CODE  ,--  手术代码
    A.OPS_ASSIST6_STD_NAME  as   OPER_NAME  ,--  手术名称
    A.OPS_DTIME  as   OPER_DTIME  ,--  手术日期
    to_number(null) as   OPER_DURATION  ,--  手术持续时间(小时)
    B.LEVEL_CODE  as   OPER_LEVEL_CODE  ,--  手术级别编码
    ''  as   OPER_TYPE_CODE  ,--  手术-操作类型编码 治疗性操作 诊断性操作 手术
    ''  as   OPER_TYPE_NAME  ,--  手术-操作类型名称 治疗性操作 诊断性操作 手术
    A.OPS_DOCT_CODE  as   OPER_DOCT_CODE  ,--  手术操作人员代码
    A.OPS_DOCT_NAME  as   OPER_DOCT_NAME  ,--  手术操作人员名称
    A.ASSISTANT1_CODE  as   asSISTANT1_CODE  ,--  一助编码
    A.ASSISTANT1_NAME  as   asSISTANT1_NAME  ,--  一助名称
    A.ASSISTANT2_CODE  as   asSISTANT2_CODE  ,--  二助编码
    A.ASSISTANT2_NAME as   asSISTANT2_NAME  ,--  二助名称
    A.INCISION_HEALING_CODE  as   INCISION_HEALING_CODE  ,--  切口愈合等级代码
    A.INCISION_HEALING_NAME  as   INCISION_HEALING_NAME  ,--  切口愈合等级名称
    A.ANE_METHOD_CODE  as   ANESTHESIA_CODE  ,--  麻醉方式编码
    A.ANE_METHOD_NAME  as   ANESTHESIA_NAME  ,--  麻醉方式名称
    ''  as   ANESTHESIA_LEVEL_CODE  ,--  麻醉分级编码
    A.ANE_DOCT1_CODE  as   ANESTHESIOLOGIST_CODE  ,--  麻醉医师编码
    A.Ane_Doct1_Name  as   ANESTHESIOLOGIST_NAME  ,--  麻醉医师名称
    ''  as   BODY_PART_CODE  --  手术部位编码
from EMR_OPS_APPLY A LEFT JOIN VW_EMR_DICT_OPER_NAME B on A.OPS_ASSIST6_CODE = B.OPER_CODE
where 1=1 and A.OPS_ASSIST6_CODE is not null
union all
select --病案首页  手术信息同步
    A.PAT_MZZYID  as   ZY_ID  ,--  住院流水号(一次住院内部唯一ID)
    ROW_NUMBER()OVER(PARTITION by A.PAT_MZZYID order by A.OPS_DTIME)*10+7 as   SORT  ,--  排序
    to_number(null)  as   MAIN_FLAG  ,--  是否主手术
    A.OPS_ASSIST7_CODE  as   OPER_CODE  ,--  手术代码
    A.OPS_ASSIST7_STD_NAME  as   OPER_NAME  ,--  手术名称
    A.OPS_DTIME  as   OPER_DTIME  ,--  手术日期
    to_number(null) as   OPER_DURATION  ,--  手术持续时间(小时)
    B.LEVEL_CODE  as   OPER_LEVEL_CODE  ,--  手术级别编码
    ''  as   OPER_TYPE_CODE  ,--  手术-操作类型编码 治疗性操作 诊断性操作 手术
    ''  as   OPER_TYPE_NAME  ,--  手术-操作类型名称 治疗性操作 诊断性操作 手术
    A.OPS_DOCT_CODE  as   OPER_DOCT_CODE  ,--  手术操作人员代码
    A.OPS_DOCT_NAME  as   OPER_DOCT_NAME  ,--  手术操作人员名称
    A.ASSISTANT1_CODE  as   asSISTANT1_CODE  ,--  一助编码
    A.ASSISTANT1_NAME  as   asSISTANT1_NAME  ,--  一助名称
    A.ASSISTANT2_CODE  as   asSISTANT2_CODE  ,--  二助编码
    A.ASSISTANT2_NAME as   asSISTANT2_NAME  ,--  二助名称
    A.INCISION_HEALING_CODE  as   INCISION_HEALING_CODE  ,--  切口愈合等级代码
    A.INCISION_HEALING_NAME  as   INCISION_HEALING_NAME  ,--  切口愈合等级名称
    A.ANE_METHOD_CODE  as   ANESTHESIA_CODE  ,--  麻醉方式编码
    A.ANE_METHOD_NAME  as   ANESTHESIA_NAME  ,--  麻醉方式名称
    ''  as   ANESTHESIA_LEVEL_CODE  ,--  麻醉分级编码
    A.ANE_DOCT1_CODE  as   ANESTHESIOLOGIST_CODE  ,--  麻醉医师编码
    A.Ane_Doct1_Name  as   ANESTHESIOLOGIST_NAME  ,--  麻醉医师名称
    ''  as   BODY_PART_CODE  --  手术部位编码
from EMR_OPS_APPLY A LEFT JOIN VW_EMR_DICT_OPER_NAME B on A.OPS_ASSIST7_CODE = B.OPER_CODE
where 1=1 and A.OPS_ASSIST7_CODE is not null
union all
select --病案首页  手术信息同步
    A.PAT_MZZYID  as   ZY_ID  ,--  住院流水号(一次住院内部唯一ID)
    ROW_NUMBER()OVER(PARTITION by A.PAT_MZZYID order by A.OPS_DTIME)*10+8 as   SORT  ,--  排序
    to_number(null)  as   MAIN_FLAG  ,--  是否主手术
    A.OPS_ASSIST8_CODE  as   OPER_CODE  ,--  手术代码
    A.OPS_ASSIST8_STD_NAME  as   OPER_NAME  ,--  手术名称
    A.OPS_DTIME  as   OPER_DTIME  ,--  手术日期
    to_number(null) as   OPER_DURATION  ,--  手术持续时间(小时)
    B.LEVEL_CODE  as   OPER_LEVEL_CODE  ,--  手术级别编码
    ''  as   OPER_TYPE_CODE  ,--  手术-操作类型编码 治疗性操作 诊断性操作 手术
    ''  as   OPER_TYPE_NAME  ,--  手术-操作类型名称 治疗性操作 诊断性操作 手术
    A.OPS_DOCT_CODE  as   OPER_DOCT_CODE  ,--  手术操作人员代码
    A.OPS_DOCT_NAME  as   OPER_DOCT_NAME  ,--  手术操作人员名称
    A.ASSISTANT1_CODE  as   asSISTANT1_CODE  ,--  一助编码
    A.ASSISTANT1_NAME  as   asSISTANT1_NAME  ,--  一助名称
    A.ASSISTANT2_CODE  as   asSISTANT2_CODE  ,--  二助编码
    A.ASSISTANT2_NAME as   asSISTANT2_NAME  ,--  二助名称
    A.INCISION_HEALING_CODE  as   INCISION_HEALING_CODE  ,--  切口愈合等级代码
    A.INCISION_HEALING_NAME  as   INCISION_HEALING_NAME  ,--  切口愈合等级名称
    A.ANE_METHOD_CODE  as   ANESTHESIA_CODE  ,--  麻醉方式编码
    A.ANE_METHOD_NAME  as   ANESTHESIA_NAME  ,--  麻醉方式名称
    ''  as   ANESTHESIA_LEVEL_CODE  ,--  麻醉分级编码
    A.ANE_DOCT1_CODE  as   ANESTHESIOLOGIST_CODE  ,--  麻醉医师编码
    A.Ane_Doct1_Name  as   ANESTHESIOLOGIST_NAME  ,--  麻醉医师名称
    ''  as   BODY_PART_CODE  --  手术部位编码
from EMR_OPS_APPLY A LEFT JOIN VW_EMR_DICT_OPER_NAME B on A.OPS_ASSIST8_CODE = B.OPER_CODE
where 1=1 and A.OPS_ASSIST8_CODE is not null
union all
select --病案首页  手术信息同步
    A.PAT_MZZYID  as   ZY_ID  ,--  住院流水号(一次住院内部唯一ID)
    ROW_NUMBER()OVER(PARTITION by A.PAT_MZZYID order by A.OPS_DTIME)*10+9 as   SORT  ,--  排序
    to_number(null)  as   MAIN_FLAG  ,--  是否主手术
    A.OPS_ASSIST9_CODE  as   OPER_CODE  ,--  手术代码
    A.OPS_ASSIST9_STD_NAME  as   OPER_NAME  ,--  手术名称
    A.OPS_DTIME  as   OPER_DTIME  ,--  手术日期
    to_number(null) as   OPER_DURATION  ,--  手术持续时间(小时)
    B.LEVEL_CODE  as   OPER_LEVEL_CODE  ,--  手术级别编码
    ''  as   OPER_TYPE_CODE  ,--  手术-操作类型编码 治疗性操作 诊断性操作 手术
    ''  as   OPER_TYPE_NAME  ,--  手术-操作类型名称 治疗性操作 诊断性操作 手术
    A.OPS_DOCT_CODE  as   OPER_DOCT_CODE  ,--  手术操作人员代码
    A.OPS_DOCT_NAME  as   OPER_DOCT_NAME  ,--  手术操作人员名称
    A.ASSISTANT1_CODE  as   asSISTANT1_CODE  ,--  一助编码
    A.ASSISTANT1_NAME  as   asSISTANT1_NAME  ,--  一助名称
    A.ASSISTANT2_CODE  as   asSISTANT2_CODE  ,--  二助编码
    A.ASSISTANT2_NAME as   asSISTANT2_NAME  ,--  二助名称
    A.INCISION_HEALING_CODE  as   INCISION_HEALING_CODE  ,--  切口愈合等级代码
    A.INCISION_HEALING_NAME  as   INCISION_HEALING_NAME  ,--  切口愈合等级名称
    A.ANE_METHOD_CODE  as   ANESTHESIA_CODE  ,--  麻醉方式编码
    A.ANE_METHOD_NAME  as   ANESTHESIA_NAME  ,--  麻醉方式名称
    ''  as   ANESTHESIA_LEVEL_CODE  ,--  麻醉分级编码
    A.ANE_DOCT1_CODE  as   ANESTHESIOLOGIST_CODE  ,--  麻醉医师编码
    A.Ane_Doct1_Name  as   ANESTHESIOLOGIST_NAME  ,--  麻醉医师名称
    ''  as   BODY_PART_CODE  --  手术部位编码
from EMR_OPS_APPLY A LEFT JOIN VW_EMR_DICT_OPER_NAME B on A.OPS_ASSIST9_CODE = B.OPER_CODE
where 1=1 and A.OPS_ASSIST9_CODE is not null
union all
select  --病案同步诊疗操作
    B.ZYID00 as   ZY_ID  ,--  住院流水号(一次住院内部唯一ID)
    to_number(null)  as   SORT  ,--  排序
    to_number(null)  as   MAIN_FLAG  ,--  是否主手术
    A.SSM000  as   OPER_CODE  ,--  手术代码
    (select SSMC00 from XT_CM3000  where CM3000 = A.SSM000 and ROWNUM = 1)  as   OPER_NAME  ,--  手术名称
    min(to_date(B.QYRQ00||  SUBSTR(B.QYSJ00, 0, 5) || ':00','yyyyMMddHH24:mi:ss')) as   OPER_DTIME  ,--  手术日期
    to_number(null) as   OPER_DURATION  ,--  手术持续时间(小时)
    to_char(nvl((select DICT_CODE from  EMR_DICT_COMMON_CODE a where a.TYPE_CODE='F_OPER_LEVEL_CODE'and DICT_NAME = A.SSDJ00 and rownum=1),'-'))  as   OPER_LEVEL_CODE  ,--  手术级别编码
    ''  as   OPER_TYPE_CODE  ,--  手术-操作类型编码 治疗性操作 诊断性操作 手术
    ''  as   OPER_TYPE_NAME  ,--  手术-操作类型名称 治疗性操作 诊断性操作 手术
    to_number(null)  as   OPER_DOCT_CODE  ,--  手术操作人员代码
    null  as   OPER_DOCT_NAME  ,--  手术操作人员名称
    A.IYS000  as   asSISTANT1_CODE  ,--  一助编码
    A.IYSMC0  as   asSISTANT1_NAME  ,--  一助名称
    A.IIYS00  as   asSISTANT2_CODE  ,--  二助编码
    A.IIYSMC  as   asSISTANT2_NAME  ,--  二助名称
    (select T.DICT_CODE from VW_EMR_DICT_COMMON_CODE T where T.TYPE_CODE = 'BM_QKDJ00' and A.QKYHDJ like '%'||T.DICT_NAME||'%' and rownum=1)  as   INCISION_HEALING_CODE  ,--  切口愈合等级代码
    (select T.DICT_NAME from VW_EMR_DICT_COMMON_CODE T where T.TYPE_CODE = 'BM_QKDJ00' and A.QKYHDJ like '%'||T.DICT_NAME||'%' and rownum=1)  as   INCISION_HEALING_NAME  ,--  切口愈合等级名称
    (select T.DICT_CODE from VW_EMR_DICT_COMMON_CODE T where T.TYPE_CODE = 'BM_MZZD00' and T.DICT_NAME = A.MZFS00 and rownum=1)  as   ANESTHESIA_CODE  ,--  麻醉方式编码
    A.MZFS00  as   ANESTHESIA_NAME  ,--  麻醉方式名称
    null  as   ANESTHESIA_LEVEL_CODE  ,--  麻醉分级编码
    to_number(A.MZYS00)  as   ANESTHESIOLOGIST_CODE  ,--  麻醉医师编码
    A.MZYSMC  as   ANESTHESIOLOGIST_NAME  ,--  麻醉医师名称
    A.BWMC00  as   BODY_PART_CODE  --  手术部位编码
  from ZS_ZLZDSS A, BQ_YJYZ00 B, YJ_YW0000 C
 where A.ZLXMID = B.ZLXMID and B.YZMXID = C.YZCFID  and B.ZLXMID = C.ZLXMID and C.ZYGHID = B.ZYID00
   and ZT0000 = '1'
   and C.XMZT00 in ('1', '2', '3', '4') and C.CXDJH0 is null
   and MZZYBZ = '1' and YZZT00 <> '4'
   and not exists(select 1 from EMR_OPS_APPLY t where t.PAT_MZZYID = B.ZYID00 and t.OPS_CODE = A.SSM000)
group by B.ZYID00,A.SSM000,A.SSDJ00,A.IYS000,A.IYSMC0,A.IIYS00,A.IIYSMC,A.QKYHDJ,A.MZFS00,A.MZYS00,A.MZYSMC,A.BWMC00
order by ZY_ID,OPER_DTIME
;