-- Start of DDL Script for View SD_HOSPITAL.VW_EMR_MEDICAL_DICT
-- Generated 2023/7/31 11:36:04 from SD_HOSPITAL@CLFY

CREATE OR REPLACE VIEW vw_emr_medical_dict (
   orderdictcode,
   orderdictno,
   orderdicttypecode,
   orderdictclasscode,
   sexcontrol,
   orderdictclassname,
   orderdictname,
   wb,
   py,
   deptcode,
   deptname,
   tplid,
   tplname,
   doseunit,
   doseunitcode,
   price,
   classcode,
   userflag,
   deptnames,
   deptcodes,
   excludetype,
   excludeteamid,
   excludeteamname,
   labbillcode,
   examcode,
   hospitalcode,
   ordinal,
   medinsurcountrycode,
   medinsurcountryname )
AS
select A.ORDER_CODE orderDictCode,  --诊疗项目id号 <90000000
          A.ORDER_NO orderDictNo,    --诊疗项目编号,以'Z'打头,最多允许三层，每级三位
          A.ORDER_TYPE_CODE orderDictTypeCode,
          A.EXAM_BILL_CODE orderDictClassCode,
          A.SEX_CONTROL SexControl,--医嘱性别限制
          (select EXAMNAME from VW_EMR_EXAM_APPLY where EXAMCODE=A.EXAM_BILL_CODE and USE_FLAG in ('1','2')) orderDictClassName,
           A.ORDER_NAME orderDictName,  --诊疗项目简称
          A.WB WB,                  --五笔码
          A.PY PY,                  --拼音码
          --nvl(A.ZXKS00,decode(A.LBBH00,'4',420,'3',418)) deptCode,
          null deptCode,
          null deptName,
          '0' tplId,  --模板ID
          '检查申请单' tplName,--模板名称
          trim(A.DOSE_UNIT) doseUnit,
          trim(A.DOSE_UNIT_CODE) doseUnitCode,
          (select sum(nvl(c.TCJE00,d.SFJE00)*c.SFCS00) from XT_ZLSFGX c, BM_YYSFXM d where C.ZLXMID = to_number(A.ORDER_CODE) and d.sfxmid = c.sfxmid) price,--诊疗项目金额
          A.ORDER_CLASS classCode,      --是否类别标志‘1’子项目(子项目不可以再有子项目),
          A.USE_FLAG userFlag,          --住院门诊使用标志
          null deptNames,  --执行科室名称列表
          null deptCodes,  --执行科室代码列表
          case   when  A.ORDER_NAME  = '术后医嘱' or A.ORDER_NAME  = '产后医嘱' or A.ORDER_NAME  = '重整医嘱'  then  'Q'
          else
           (select EXCLUDETYPE from VW_EMR_EXCLUDE_ORDER where ZLXMID=to_number(A.ORDER_CODE))
          end  ,                                            --排斥医嘱类型
          (select EXCLUDETEAMID from VW_EMR_EXCLUDE_ORDER where ZLXMID=to_number(A.ORDER_CODE)) EXCLUDETEAMID,                                            --排斥医嘱号
          (select EXCLUDETEAMNAME from VW_EMR_EXCLUDE_ORDER where ZLXMID=to_number(A.ORDER_CODE)) EXCLUDETEAMNAME,                                         --排斥医嘱名称
          --nvl(A.ZYZBID,'0') LABBILLCODE,
          A.LAB_BILL_CODE LABBILLCODE,
          (select examCode from VW_EMR_EXAM_X_ITEM where examItemClassCode=A.EXAM_BILL_CODE and USE_FLAG in ('1','2')) EXAMCODE,
          nvl(A.HOSPITAL_CODE,'0') HOSPITALCODE,
      nvl(A.ORDINAL,9999) ORDINAL,
      MEDINSURCOUNTRYCODE,
      MEDINSURCOUNTRYNAME
      from EMR_MEDICAL_DICT A where AVAILABLE='Y' and A.USE_FLAG in ('1','2') and A.IS_VALID_APPLY='Y'
      and exists(select 1 from BM_ZLZD00 where ZLXMID=A.ORDER_CODE and SFYXSQ='Y'
      and QYRQ00 IS NOT NULL and QYRQ00<=TO_CHAR(SYSDATE,'YYYYMMDD')
      and ((TZRQ00 IS NULL) OR (TZRQ00>TO_CHAR(SYSDATE,'YYYYMMDD'))))
/

-- Grants for View
GRANT SELECT ON vw_emr_medical_dict TO sd_dzbljk
/

-- End of DDL Script for View SD_HOSPITAL.VW_EMR_MEDICAL_DICT