CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YS_CFXXB0_HTBL
 (
 ORDER_SN, REGISTER_NO, PAT_INDEX_NO, ORDER_TYPE, ORDER_CLASS, PARENT_ORDER_SN, 
IS_SUB_ORDER, ROUTE_CY_CODE, ROUTE_CY_NAME, OUT_ORDER_STATUS, DEPT_CODE, 
DEPT_NAME, GROSS_FEE, DOCTOR_MEMO, PRESCRIPTION_SN, PRESCRIPTION_COUNT

 )
 AS 
select
-- person      date            comments
-- ruanbh      2023.01.06      create 海泰电子病历接口_门诊处方视图
-- cyf          2023.02.09     集成平台调整了总量，总量单位，总量单位代码，总价的字段名
  A.CFLSH0 ORDER_SN,--记录唯一号必填key
  --A.CFLSH0||C.CFID00 ORDER_SN_EMR,--记录唯一号必填key
  A.GHH000 REGISTER_NO,--挂号流水号必填
  A.BRID00 PAT_INDEX_NO,--病人档案唯一号
  decode(A.YPDLBH,'0','XY','1','ZCY','2','CY') ORDER_TYPE,--医嘱类型,（XY-西药，ZCY-中成药，CY-草药,JY-检验，JC-检查，ZT-嘱托，ZL-治疗，SS-手术，QT-其他等）
  nvl((select decode(instr(X.LBMC00,'急'),0,'OUT','JZ') from BM_GHLBB0 X where X.LBBH00=B.GHLB00 and rownum=1),'OUT') ORDER_CLASS,--医嘱分类【JZ：急诊；OUT：门诊】
  A.CFLSH0 PARENT_ORDER_SN,--主医嘱号(同组医嘱该字段相同)
  'N' IS_SUB_ORDER,--是否子医嘱(Y-是，N-否)
  decode(A.YPDLBH,'2',(select X.YFID00 from YS_CFMX00 X where X.CFLSH0=A.CFLSH0 and rownum=1)) ROUTE_CY_CODE,--草药用法代码（草药类型必填）
  decode(A.YPDLBH,'2',(select Y.YPYFMC from YS_CFMX00 X,BM_YPYF00 Y where X.CFLSH0=A.CFLSH0 and X.YFID00=Y.YFID00 and rownum=1)) ROUTE_CY_NAME,--草药用法名称（草药类型必填）
  '050' OUT_ORDER_STATUS,--医嘱状态【050：编辑；055：已提交；060：已收费；065：退费中；070：已退费；080：已发药；085：退药中；090：已退药；130：作废医嘱】
  A.GHKS00 DEPT_CODE,--下达科室代码
  (select X.BMMC00 from BM_BMBM00 X where X.BMBH00=A.GHKS00) DEPT_NAME,--下达科室名称
  (select sum(X.HJJE00) from YF_MZCFMX X where X.CFLSH0=A.CFLSH0) GROSS_FEE ,--总价  TOTAL_PRICE
  A.CFSM00 DOCTOR_MEMO,--医生说明
  A.CFLSH0 PRESCRIPTION_SN,--处方号
  decode(A.YPDLBH,'2',A.ZYTS00) PRESCRIPTION_COUNT--剂数（草药）
  from YS_CFXXB0 A,SF_BRXXB0 B
  where A.GHID00=B.GHID00
/