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  ,--  手术名称
   -- nvl(to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss'),A.OPS_DTIME)  as   OPER_DTIME  ,--  手术日期
     -- nvl((select  course_dtime from emr_bui_index where a.apply_order_id=business_id and TPL_TYPE_CODE='EM0703' ),A.OPS_DTIME) as   OPER_DTIME  ,--  手术日期
  nvl(to_date(SF_EMR_GET_FILE_TEXT_BY_ELEID((select  ID from emr_bui_index where a.apply_order_id=business_id and TPL_TYPE_CODE='EM0703' ),'_nX9hpo2Ce_AWhtLPhVpo'),'yyyy-MM-dd hh24:mi:ss'),A.OPS_DTIME) as   OPER_DTIME  ,--  手术日期

    to_number((case when C.SSJSRQ is not null then round((to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss')-to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss')) * 24, 2) else null end )) 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  ,--  麻醉医师名称
    A.OPS_BODY_PART_NAME  as   BODY_PART_CODE,  --  手术部位编码
    to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss') as OPER_BEGIN_DTIME,
  --  to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss') as OPER_END_DTIME, --手术结束日期
    nvl(to_date(SF_EMR_GET_FILE_TEXT_BY_ELEID((select  ID from emr_bui_index where a.apply_order_id=business_id and TPL_TYPE_CODE='EM0703' ),'ttqUzTLazTwwhaMxRDxR-'),'yyyy-MM-dd hh24:mi:ss'),   to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss'))  as OPER_END_DTIME, --手术结束日期

    to_date(C.MZKSRQ||C.MZKSSJ,'yyyyMMddHH24:mi:ss') as ANESTHESIA_BEGIN_DTIME, --麻醉起始时间
    to_date(C.MZJSRQ||C.MZJSSJ,'yyyyMMddHH24:mi:ss') as ANESTHESIA_END_DTIME, --麻醉结束时间
    '' as BODY_DIRECTION_CODE,  --  手术方位编码
    '' as ADDITION_NOTE  --补充说明
from EMR_OPS_APPLY A left join VW_EMR_DICT_OPER_NAME B on A.OPS_CODE = B.OPER_CODE left join SS_YW0000 C on C.SSDH00 = to_number(A.APPLY_ORDER_ID)
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  ,--  手术名称
    --nvl(to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss'),A.OPS_DTIME)  as   OPER_DTIME  ,--  手术日期
      nvl(to_date(SF_EMR_GET_FILE_TEXT_BY_ELEID((select  ID from emr_bui_index where a.apply_order_id=business_id and TPL_TYPE_CODE='EM0703' ),'_nX9hpo2Ce_AWhtLPhVpo'),'yyyy-MM-dd hh24:mi:ss'),A.OPS_DTIME) as   OPER_DTIME  ,--  手术日期
    to_number((case when C.SSJSRQ is not null then round((to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss')-to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss')) * 24, 2) else null end )) 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  ,--  麻醉医师名称
    A.OPS_BODY_PART_NAME  as   BODY_PART_CODE,  --  手术部位编码
    to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss') as OPER_BEGIN_DTIME,
   -- to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss') as OPER_END_DTIME, --手术结束日期
     nvl(to_date(SF_EMR_GET_FILE_TEXT_BY_ELEID((select  ID from emr_bui_index where a.apply_order_id=business_id and TPL_TYPE_CODE='EM0703' ),'ttqUzTLazTwwhaMxRDxR-'),'yyyy-MM-dd hh24:mi:ss'),   to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss'))  as OPER_END_DTIME, --手术结束日期
    to_date(C.MZKSRQ||C.MZKSSJ,'yyyyMMddHH24:mi:ss') as ANESTHESIA_BEGIN_DTIME, --麻醉起始时间
    to_date(C.MZJSRQ||C.MZJSSJ,'yyyyMMddHH24:mi:ss') as ANESTHESIA_END_DTIME, --麻醉结束时间
    '' as BODY_DIRECTION_CODE,  --  手术方位编码
    '' as ADDITION_NOTE  --补充说明
from EMR_OPS_APPLY A left join VW_EMR_DICT_OPER_NAME B on A.OPS_ASSIST1_CODE = B.OPER_CODE left join SS_YW0000 C on C.SSDH00 = to_number(A.APPLY_ORDER_ID)
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  ,--  手术名称
    --nvl(to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss'),A.OPS_DTIME)  as   OPER_DTIME  ,--  手术日期
      nvl(to_date(SF_EMR_GET_FILE_TEXT_BY_ELEID((select  ID from emr_bui_index where a.apply_order_id=business_id and TPL_TYPE_CODE='EM0703' ),'_nX9hpo2Ce_AWhtLPhVpo'),'yyyy-MM-dd hh24:mi:ss'),A.OPS_DTIME) as   OPER_DTIME  ,--  手术日期
    to_number((case when C.SSJSRQ is not null then round((to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss')-to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss')) * 24, 2) else null end )) 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  ,--  麻醉医师名称
    A.OPS_BODY_PART_NAME  as   BODY_PART_CODE,  --  手术部位编码
    to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss') as OPER_BEGIN_DTIME,
   -- to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss') as OPER_END_DTIME, --手术结束日期
      nvl(to_date(SF_EMR_GET_FILE_TEXT_BY_ELEID((select  ID from emr_bui_index where a.apply_order_id=business_id and TPL_TYPE_CODE='EM0703' ),'ttqUzTLazTwwhaMxRDxR-'),'yyyy-MM-dd hh24:mi:ss'),   to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss'))  as OPER_END_DTIME, --手术结束日期

    to_date(C.MZKSRQ||C.MZKSSJ,'yyyyMMddHH24:mi:ss') as ANESTHESIA_BEGIN_DTIME, --麻醉起始时间
    to_date(C.MZJSRQ||C.MZJSSJ,'yyyyMMddHH24:mi:ss') as ANESTHESIA_END_DTIME, --麻醉结束时间
    '' as BODY_DIRECTION_CODE,  --  手术方位编码
    '' as ADDITION_NOTE  --补充说明
from EMR_OPS_APPLY A left join VW_EMR_DICT_OPER_NAME B on A.OPS_ASSIST2_CODE = B.OPER_CODE left join SS_YW0000 C on C.SSDH00 = to_number(A.APPLY_ORDER_ID)
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  ,--  手术名称
    --nvl(to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss'),A.OPS_DTIME)  as   OPER_DTIME  ,--  手术日期
      nvl(to_date(SF_EMR_GET_FILE_TEXT_BY_ELEID((select  ID from emr_bui_index where a.apply_order_id=business_id and TPL_TYPE_CODE='EM0703' ),'_nX9hpo2Ce_AWhtLPhVpo'),'yyyy-MM-dd hh24:mi:ss'),A.OPS_DTIME) as   OPER_DTIME  ,--  手术日期
    to_number((case when C.SSJSRQ is not null then round((to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss')-to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss')) * 24, 2) else null end )) 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  ,--  麻醉医师名称
    A.OPS_BODY_PART_NAME  as   BODY_PART_CODE,  --  手术部位编码
    to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss') as OPER_BEGIN_DTIME,
   -- to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss') as OPER_END_DTIME, --手术结束日期
  nvl(to_date(SF_EMR_GET_FILE_TEXT_BY_ELEID((select  ID from emr_bui_index where a.apply_order_id=business_id and TPL_TYPE_CODE='EM0703' ),'ttqUzTLazTwwhaMxRDxR-'),'yyyy-MM-dd hh24:mi:ss'),   to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss'))  as OPER_END_DTIME, --手术结束日期

    to_date(C.MZKSRQ||C.MZKSSJ,'yyyyMMddHH24:mi:ss') as ANESTHESIA_BEGIN_DTIME, --麻醉起始时间
    to_date(C.MZJSRQ||C.MZJSSJ,'yyyyMMddHH24:mi:ss') as ANESTHESIA_END_DTIME, --麻醉结束时间
    '' as BODY_DIRECTION_CODE,  --  手术方位编码
    '' as ADDITION_NOTE  --补充说明
from EMR_OPS_APPLY A left join VW_EMR_DICT_OPER_NAME B on A.OPS_ASSIST3_CODE = B.OPER_CODE left join SS_YW0000 C on C.SSDH00 = to_number(A.APPLY_ORDER_ID)
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  ,--  手术名称
    --nvl(to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss'),A.OPS_DTIME)  as   OPER_DTIME  ,--  手术日期
    nvl(to_date(SF_EMR_GET_FILE_TEXT_BY_ELEID((select  ID from emr_bui_index where a.apply_order_id=business_id and TPL_TYPE_CODE='EM0703' ),'_nX9hpo2Ce_AWhtLPhVpo'),'yyyy-MM-dd hh24:mi:ss'),A.OPS_DTIME) as   OPER_DTIME  ,--  手术日期
    to_number((case when C.SSJSRQ is not null then round((to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss')-to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss')) * 24, 2) else null end )) 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  ,--  麻醉医师名称
    A.OPS_BODY_PART_NAME  as   BODY_PART_CODE,  --  手术部位编码
    to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss') as OPER_BEGIN_DTIME,
   -- to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss') as OPER_END_DTIME, --手术结束日期
  nvl(to_date(SF_EMR_GET_FILE_TEXT_BY_ELEID((select  ID from emr_bui_index where a.apply_order_id=business_id and TPL_TYPE_CODE='EM0703' ),'ttqUzTLazTwwhaMxRDxR-'),'yyyy-MM-dd hh24:mi:ss'),   to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss'))  as OPER_END_DTIME, --手术结束日期

    to_date(C.MZKSRQ||C.MZKSSJ,'yyyyMMddHH24:mi:ss') as ANESTHESIA_BEGIN_DTIME, --麻醉起始时间
    to_date(C.MZJSRQ||C.MZJSSJ,'yyyyMMddHH24:mi:ss') as ANESTHESIA_END_DTIME, --麻醉结束时间
    '' as BODY_DIRECTION_CODE,  --  手术方位编码
    '' as ADDITION_NOTE  --补充说明
from EMR_OPS_APPLY A left join VW_EMR_DICT_OPER_NAME B on A.OPS_ASSIST4_CODE = B.OPER_CODE left join SS_YW0000 C on C.SSDH00 = to_number(A.APPLY_ORDER_ID)
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  ,--  手术名称
    --nvl(to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss'),A.OPS_DTIME)  as   OPER_DTIME  ,--  手术日期
    nvl(to_date(SF_EMR_GET_FILE_TEXT_BY_ELEID((select  ID from emr_bui_index where a.apply_order_id=business_id and TPL_TYPE_CODE='EM0703' ),'_nX9hpo2Ce_AWhtLPhVpo'),'yyyy-MM-dd hh24:mi:ss'),A.OPS_DTIME) as   OPER_DTIME  ,--  手术日期
    to_number((case when C.SSJSRQ is not null then round((to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss')-to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss')) * 24, 2) else null end )) 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  ,--  麻醉医师名称
    A.OPS_BODY_PART_NAME  as   BODY_PART_CODE,  --  手术部位编码
    to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss') as OPER_BEGIN_DTIME,
   -- to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss') as OPER_END_DTIME, --手术结束日期
  nvl(to_date(SF_EMR_GET_FILE_TEXT_BY_ELEID((select  ID from emr_bui_index where a.apply_order_id=business_id and TPL_TYPE_CODE='EM0703' ),'ttqUzTLazTwwhaMxRDxR-'),'yyyy-MM-dd hh24:mi:ss'),   to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss'))  as OPER_END_DTIME, --手术结束日期

    to_date(C.MZKSRQ||C.MZKSSJ,'yyyyMMddHH24:mi:ss') as ANESTHESIA_BEGIN_DTIME, --麻醉起始时间
    to_date(C.MZJSRQ||C.MZJSSJ,'yyyyMMddHH24:mi:ss') as ANESTHESIA_END_DTIME, --麻醉结束时间
    '' as BODY_DIRECTION_CODE,  --  手术方位编码
    '' as ADDITION_NOTE  --补充说明
from EMR_OPS_APPLY A left join VW_EMR_DICT_OPER_NAME B on A.OPS_ASSIST5_CODE = B.OPER_CODE left join SS_YW0000 C on C.SSDH00 = to_number(A.APPLY_ORDER_ID)
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  ,--  手术名称
    --nvl(to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss'),A.OPS_DTIME)  as   OPER_DTIME  ,--  手术日期
    nvl(to_date(SF_EMR_GET_FILE_TEXT_BY_ELEID((select  ID from emr_bui_index where a.apply_order_id=business_id and TPL_TYPE_CODE='EM0703' ),'_nX9hpo2Ce_AWhtLPhVpo'),'yyyy-MM-dd hh24:mi:ss'),A.OPS_DTIME) as   OPER_DTIME  ,--  手术日期
    to_number((case when C.SSJSRQ is not null then round((to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss')-to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss')) * 24, 2) else null end )) 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  ,--  麻醉医师名称
    A.OPS_BODY_PART_NAME  as   BODY_PART_CODE,  --  手术部位编码
    to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss') as OPER_BEGIN_DTIME,
   -- to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss') as OPER_END_DTIME, --手术结束日期
  nvl(to_date(SF_EMR_GET_FILE_TEXT_BY_ELEID((select  ID from emr_bui_index where a.apply_order_id=business_id and TPL_TYPE_CODE='EM0703' ),'ttqUzTLazTwwhaMxRDxR-'),'yyyy-MM-dd hh24:mi:ss'),   to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss'))  as OPER_END_DTIME, --手术结束日期

    to_date(C.MZKSRQ||C.MZKSSJ,'yyyyMMddHH24:mi:ss') as ANESTHESIA_BEGIN_DTIME, --麻醉起始时间
    to_date(C.MZJSRQ||C.MZJSSJ,'yyyyMMddHH24:mi:ss') as ANESTHESIA_END_DTIME, --麻醉结束时间
    '' as BODY_DIRECTION_CODE,  --  手术方位编码
    '' as ADDITION_NOTE  --补充说明
from EMR_OPS_APPLY A left join VW_EMR_DICT_OPER_NAME B on A.OPS_ASSIST6_CODE = B.OPER_CODE left join SS_YW0000 C on C.SSDH00 = to_number(A.APPLY_ORDER_ID)
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  ,--  手术名称
   -- nvl(to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss'),A.OPS_DTIME)  as   OPER_DTIME  ,--  手术日期
    nvl(to_date(SF_EMR_GET_FILE_TEXT_BY_ELEID((select  ID from emr_bui_index where a.apply_order_id=business_id and TPL_TYPE_CODE='EM0703' ),'_nX9hpo2Ce_AWhtLPhVpo'),'yyyy-MM-dd hh24:mi:ss'),A.OPS_DTIME) as   OPER_DTIME  ,--  手术日期
    to_number((case when C.SSJSRQ is not null then round((to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss')-to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss')) * 24, 2) else null end )) 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  ,--  麻醉医师名称
    A.OPS_BODY_PART_NAME  as   BODY_PART_CODE,  --  手术部位编码
    to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss') as OPER_BEGIN_DTIME,
   -- to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss') as OPER_END_DTIME, --手术结束日期
  nvl(to_date(SF_EMR_GET_FILE_TEXT_BY_ELEID((select  ID from emr_bui_index where a.apply_order_id=business_id and TPL_TYPE_CODE='EM0703' ),'ttqUzTLazTwwhaMxRDxR-'),'yyyy-MM-dd hh24:mi:ss'),   to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss'))  as OPER_END_DTIME, --手术结束日期

    to_date(C.MZKSRQ||C.MZKSSJ,'yyyyMMddHH24:mi:ss') as ANESTHESIA_BEGIN_DTIME, --麻醉起始时间
    to_date(C.MZJSRQ||C.MZJSSJ,'yyyyMMddHH24:mi:ss') as ANESTHESIA_END_DTIME, --麻醉结束时间
    '' as BODY_DIRECTION_CODE,  --  手术方位编码
    '' as ADDITION_NOTE  --补充说明
from EMR_OPS_APPLY A left join VW_EMR_DICT_OPER_NAME B on A.OPS_ASSIST7_CODE = B.OPER_CODE left join SS_YW0000 C on C.SSDH00 = to_number(A.APPLY_ORDER_ID)
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  ,--  手术名称
    --nvl(to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss'),A.OPS_DTIME)  as   OPER_DTIME  ,--  手术日期
    nvl(to_date(SF_EMR_GET_FILE_TEXT_BY_ELEID((select  ID from emr_bui_index where a.apply_order_id=business_id and TPL_TYPE_CODE='EM0703' ),'_nX9hpo2Ce_AWhtLPhVpo'),'yyyy-MM-dd hh24:mi:ss'),A.OPS_DTIME) as   OPER_DTIME  ,--  手术日期
    to_number((case when C.SSJSRQ is not null then round((to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss')-to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss')) * 24, 2) else null end )) 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  ,--  麻醉医师名称
    A.OPS_BODY_PART_NAME  as   BODY_PART_CODE,  --  手术部位编码
    to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss') as OPER_BEGIN_DTIME,
   -- to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss') as OPER_END_DTIME, --手术结束日期
  nvl(to_date(SF_EMR_GET_FILE_TEXT_BY_ELEID((select  ID from emr_bui_index where a.apply_order_id=business_id and TPL_TYPE_CODE='EM0703' ),'ttqUzTLazTwwhaMxRDxR-'),'yyyy-MM-dd hh24:mi:ss'),   to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss'))  as OPER_END_DTIME, --手术结束日期

    to_date(C.MZKSRQ||C.MZKSSJ,'yyyyMMddHH24:mi:ss') as ANESTHESIA_BEGIN_DTIME, --麻醉起始时间
    to_date(C.MZJSRQ||C.MZJSSJ,'yyyyMMddHH24:mi:ss') as ANESTHESIA_END_DTIME, --麻醉结束时间
    '' as BODY_DIRECTION_CODE,  --  手术方位编码
    '' as ADDITION_NOTE  --补充说明
from EMR_OPS_APPLY A left join VW_EMR_DICT_OPER_NAME B on A.OPS_ASSIST8_CODE = B.OPER_CODE left join SS_YW0000 C on C.SSDH00 = to_number(A.APPLY_ORDER_ID)
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  ,--  手术名称
    --nvl(to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss'),A.OPS_DTIME)  as   OPER_DTIME  ,--  手术日期
    nvl(to_date(SF_EMR_GET_FILE_TEXT_BY_ELEID((select  ID from emr_bui_index where a.apply_order_id=business_id and TPL_TYPE_CODE='EM0703' ),'_nX9hpo2Ce_AWhtLPhVpo'),'yyyy-MM-dd hh24:mi:ss'),A.OPS_DTIME) as   OPER_DTIME  ,--  手术日期
    to_number((case when C.SSJSRQ is not null then round((to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss')-to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss')) * 24, 2) else null end )) 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  ,--  麻醉医师名称
    A.OPS_BODY_PART_NAME  as   BODY_PART_CODE,  --  手术部位编码
    to_date(C.YYSSRQ||C.YYSSSJ,'yyyyMMddHH24:mi:ss') as OPER_BEGIN_DTIME,
   -- to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss') as OPER_END_DTIME, --手术结束日期
  nvl(to_date(SF_EMR_GET_FILE_TEXT_BY_ELEID((select  ID from emr_bui_index where a.apply_order_id=business_id and TPL_TYPE_CODE='EM0703' ),'ttqUzTLazTwwhaMxRDxR-'),'yyyy-MM-dd hh24:mi:ss'),   to_date(C.SSJSRQ||C.SSJSSJ,'yyyyMMddHH24:mi:ss'))  as OPER_END_DTIME, --手术结束日期

    to_date(C.MZKSRQ||C.MZKSSJ,'yyyyMMddHH24:mi:ss') as ANESTHESIA_BEGIN_DTIME, --麻醉起始时间
    to_date(C.MZJSRQ||C.MZJSSJ,'yyyyMMddHH24:mi:ss') as ANESTHESIA_END_DTIME, --麻醉结束时间
    '' as BODY_DIRECTION_CODE,  --  手术方位编码
    '' as ADDITION_NOTE  --补充说明
from EMR_OPS_APPLY A left join VW_EMR_DICT_OPER_NAME B on A.OPS_ASSIST9_CODE = B.OPER_CODE left join SS_YW0000 C on C.SSDH00 = to_number(A.APPLY_ORDER_ID)
where 1=1 and A.OPS_ASSIST9_CODE is not null
union all
select  --病案同步诊疗操作
    B.ZYID00 as   ZY_ID  ,--  住院流水号(一次住院内部唯一ID)
    9000  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  ,--  手术级别编码
    '1'  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  ,--  二助名称
    nvl((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),(select T.DICT_CODE from VW_EMR_DICT_COMMON_CODE T where T.TYPE_CODE = 'BM_QKDJ00' and T.DICT_NAME = '无切口' and rownum=1))  as   INCISION_HEALING_CODE  ,--  切口愈合等级代码
    nvl((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,  --  手术部位编码
    null as OPER_BEGIN_DTIME,
    null as OPER_END_DTIME, --手术结束日期
    null as ANESTHESIA_BEGIN_DTIME, --麻醉起始时间
    null as ANESTHESIA_END_DTIME, --麻醉结束时间
    A.FWMC00 as BODY_DIRECTION_CODE,  --  手术方位编码
    '' as ADDITION_NOTE  --补充说明
  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.FWMC00,A.BWMC00
union all
select  --病案据创呼吸机使用时间，生成对应的操作
    A.ZY_ID as   ZY_ID  ,--  住院流水号(一次住院内部唯一ID)
    9000 as   SORT  ,--  排序
    to_number(null)  as   MAIN_FLAG  ,--  是否主手术
    '96.0400'  as   OPER_CODE  ,--  手术代码
    (select SSMC00 from XT_CM3000  where CM3000 = '96.0400' and ROWNUM = 1)  as   OPER_NAME  ,--  手术名称
    nvl(to_date((select min(CZRQ00||CZSJ00) from  ZY_FYMX00 where XMMC00 like '%气管插管术(经口插管)%' and ZYID00=A.ZY_ID),'yyyyMMddHH24:mi:ss'),sysdate) as   OPER_DTIME  ,--  手术日期
    to_number(null) as   OPER_DURATION  ,--  手术持续时间(小时)
    '-'  as   OPER_LEVEL_CODE  ,--  手术级别编码
    '1'  as   OPER_TYPE_CODE  ,--  手术-操作类型编码 治疗性操作 诊断性操作 手术
    '治疗性操作'  as   OPER_TYPE_NAME  ,--  手术-操作类型名称 治疗性操作 诊断性操作 手术
    to_number(null)  as   OPER_DOCT_CODE  ,--  手术操作人员代码
    null  as   OPER_DOCT_NAME  ,--  手术操作人员名称
    to_number(null)  as   asSISTANT1_CODE  ,--  一助编码
    null  as   asSISTANT1_NAME  ,--  一助名称
    to_number(null)  as   asSISTANT2_CODE  ,--  二助编码
    null  as   asSISTANT2_NAME  ,--  二助名称
    (select T.DICT_CODE from VW_EMR_DICT_COMMON_CODE T where T.TYPE_CODE = 'BM_QKDJ00' and T.DICT_NAME = '无切口' and rownum=1)  as   INCISION_HEALING_CODE  ,--  切口愈合等级代码
    '无切口'  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 = '无麻醉' and rownum=1)  as   ANESTHESIA_CODE  ,--  麻醉方式编码
    '无麻醉' as   ANESTHESIA_NAME  ,--  麻醉方式名称
    null  as   ANESTHESIA_LEVEL_CODE  ,--  麻醉分级编码
    to_number(null)  as   ANESTHESIOLOGIST_CODE  ,--  麻醉医师编码
    null as   ANESTHESIOLOGIST_NAME  ,--  麻醉医师名称
    null as   BODY_PART_CODE,  --  手术部位编码
    null as OPER_BEGIN_DTIME,
    null as OPER_END_DTIME, --手术结束日期
    null as ANESTHESIA_BEGIN_DTIME, --麻醉起始时间
    null as ANESTHESIA_END_DTIME, --麻醉结束时间
    null as BODY_DIRECTION_CODE,  --  手术方位编码
    '' as ADDITION_NOTE  --补充说明
  from EMR_FIRST_PAGE A
 where A.LIFT_SUPPORT_HOUR > 0
   and exists(select 1 from ZY_FYMX00 B where XMMC00 like '%气管插管术(经口插管)%' and A.ZY_ID = B.ZYID00 )
   and exists(select 1 from XT_XTCS00 T where T.NAME00 = 'ZS_ZYSFGJHXJSJSSCZ' and T.VALUE0 = 'Y')
union all
select  --病案据创呼吸机使用时间，生成对应的操作
    A.ZY_ID as   ZY_ID  ,--  住院流水号(一次住院内部唯一ID)
    9000 as   SORT  ,--  排序
    to_number(null)  as   MAIN_FLAG  ,--  是否主手术
    '31.1x00x005'  as   OPER_CODE  ,--  手术代码
    (select SSMC00 from XT_CM3000  where CM3000 = '31.1x00x005' and ROWNUM = 1)  as   OPER_NAME  ,--  手术名称
    nvl(to_date((select min(CZRQ00||CZSJ00) from  ZY_FYMX00 where XMMC00 like '%气管切开术%' and ZYID00=A.ZY_ID),'yyyyMMddHH24:mi:ss'),sysdate) as   OPER_DTIME  ,--  手术日期
    to_number(null) as   OPER_DURATION  ,--  手术持续时间(小时)
    '-'  as   OPER_LEVEL_CODE  ,--  手术级别编码
    '1'  as   OPER_TYPE_CODE  ,--  手术-操作类型编码 治疗性操作 诊断性操作 手术
    '治疗性操作'  as   OPER_TYPE_NAME  ,--  手术-操作类型名称 治疗性操作 诊断性操作 手术
    to_number(null)  as   OPER_DOCT_CODE  ,--  手术操作人员代码
    null  as   OPER_DOCT_NAME  ,--  手术操作人员名称
    to_number(null)  as   asSISTANT1_CODE  ,--  一助编码
    null  as   asSISTANT1_NAME  ,--  一助名称
    to_number(null)  as   asSISTANT2_CODE  ,--  二助编码
    null  as   asSISTANT2_NAME  ,--  二助名称
    (select T.DICT_CODE from VW_EMR_DICT_COMMON_CODE T where T.TYPE_CODE = 'BM_QKDJ00' and T.DICT_NAME = '无切口' and rownum=1)  as   INCISION_HEALING_CODE  ,--  切口愈合等级代码
    '无切口'  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 = '无麻醉' and rownum=1)  as   ANESTHESIA_CODE  ,--  麻醉方式编码
    '无麻醉' as   ANESTHESIA_NAME  ,--  麻醉方式名称
    null  as   ANESTHESIA_LEVEL_CODE  ,--  麻醉分级编码
    to_number(null)  as   ANESTHESIOLOGIST_CODE  ,--  麻醉医师编码
    null as  ANESTHESIOLOGIST_NAME  ,--  麻醉医师名称
    null as   BODY_PART_CODE,  --  手术部位编码
    null as OPER_BEGIN_DTIME,
    null as OPER_END_DTIME, --手术结束日期
    null as ANESTHESIA_BEGIN_DTIME, --麻醉起始时间
    null as ANESTHESIA_END_DTIME, --麻醉结束时间
    null as BODY_DIRECTION_CODE,  --  手术方位编码
    '' as ADDITION_NOTE  --补充说明
  from EMR_FIRST_PAGE A
 where A.LIFT_SUPPORT_HOUR > 0
   and exists(select 1 from ZY_FYMX00 B where XMMC00 like '%气管切开术%' and A.ZY_ID = B.ZYID00 )
   and exists(select 1 from XT_XTCS00 T where T.NAME00 = 'ZS_ZYSFGJHXJSJSSCZ' and T.VALUE0 = 'Y')
union all
select  --病案据创呼吸机使用时间，生成对应的操作
    A.ZY_ID as   ZY_ID  ,--  住院流水号(一次住院内部唯一ID)
    9000 as   SORT  ,--  排序
    to_number(null)  as   MAIN_FLAG  ,--  是否主手术
    '96.7201'  as   OPER_CODE  ,--  手术代码
    (select SSMC00 from XT_CM3000  where CM3000 = '96.7201' and ROWNUM = 1)  as   OPER_NAME  ,--  手术名称
    nvl(to_date((select min(CZRQ00||CZSJ00) from  ZY_FYMX00 where XMMC00 like '%气管切开术%' and ZYID00=A.ZY_ID),'yyyyMMddHH24:mi:ss'),sysdate) as   OPER_DTIME  ,--  手术日期
    to_number(null) as   OPER_DURATION  ,--  手术持续时间(小时)
    '-'  as   OPER_LEVEL_CODE  ,--  手术级别编码
    '1'  as   OPER_TYPE_CODE  ,--  手术-操作类型编码 治疗性操作 诊断性操作 手术
    '治疗性操作'  as   OPER_TYPE_NAME  ,--  手术-操作类型名称 治疗性操作 诊断性操作 手术
    to_number(null)  as   OPER_DOCT_CODE  ,--  手术操作人员代码
    null  as   OPER_DOCT_NAME  ,--  手术操作人员名称
    to_number(null)  as   asSISTANT1_CODE  ,--  一助编码
    null  as   asSISTANT1_NAME  ,--  一助名称
    to_number(null)  as   asSISTANT2_CODE  ,--  二助编码
    null  as   asSISTANT2_NAME  ,--  二助名称
    (select T.DICT_CODE from VW_EMR_DICT_COMMON_CODE T where T.TYPE_CODE = 'BM_QKDJ00' and T.DICT_NAME = '无切口' and rownum=1)  as   INCISION_HEALING_CODE  ,--  切口愈合等级代码
    '无切口'  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 = '无麻醉' and rownum=1)  as   ANESTHESIA_CODE  ,--  麻醉方式编码
    '无麻醉' as   ANESTHESIA_NAME  ,--  麻醉方式名称
    null  as   ANESTHESIA_LEVEL_CODE  ,--  麻醉分级编码
    to_number(null)  as   ANESTHESIOLOGIST_CODE  ,--  麻醉医师编码
    null as   ANESTHESIOLOGIST_NAME  ,--  麻醉医师名称
    null as   BODY_PART_CODE,  --  手术部位编码
    null as OPER_BEGIN_DTIME,
    null as OPER_END_DTIME, --手术结束日期
    null as ANESTHESIA_BEGIN_DTIME, --麻醉起始时间
    null as ANESTHESIA_END_DTIME, --麻醉结束时间
    null as BODY_DIRECTION_CODE,  --  手术方位编码
    '' as ADDITION_NOTE  --补充说明
  from EMR_FIRST_PAGE A
 where A.LIFT_SUPPORT_HOUR >= 96
   and exists(select 1 from XT_XTCS00 T where T.NAME00 = 'ZS_ZYSFGJHXJSJSSCZ' and T.VALUE0 = 'Y')
order by ZY_ID,SORT,OPER_DTIME;