create or replace view vw_ys_cfzl00 as
select '1' CFBZ00,  --处方标识
       A.CFLSH0 CFLSH0,  --处方流水号
       A.YPDLBH CFLB00,  --处方类别
       decode(A.YPDLBH,'0','西药','1','成药','2','中药') CFLBMC,  --处方类别名称
       (select YPMC00 from YF_MZCFMX M where A.CFLSH0=M.CFLSH0 and rownum=1 ) CFMC00,  --处方名称
       decode(A.YPDLBH,'2',ZYTS00||'剂','') SL0000,  --数量
       nvl((select round(sum(YPZSL0*LSDJ00),2) from YF_MZCFMX where CFLSH0=A.CFLSH0 and SFZBY0<>'Y'),0) CFZJE0,  --处方总金额
       A.YSXM00 KDYSXM,  --开单医生姓名
       B.BMMC00 KDKSMC,  --开单科室名称
       C.BMMC00 ZXKSMC,  --执行科室名称
       decode(A.CFZT00,'0',decode(sign(A.CFZJE0),-1,'申请退','未收费'),'1','已收费','2','已发药','3','作废','4','未审核','5','已退药','6','已退费','未知') CFZT00,  --处方状态
       A.BCCFH0 CXCFH0,  --冲销处方号
       A.GHH000 GHH000,  --挂号号
       A.GHID00 GHID00,  --挂号id
       A.BRID00 BRID00,  --病人id
       SRRQ00||SRSJ00 KDRQSJ,  --开单日期时间
       to_date(SRRQ00,'yyyymmdd')KDRQ00,  --开单日期
       decode(A.DJH000,0,null,A.DJH000)DJH000,  --单据号
       BZ0000 BZ0000,  --备注
       YSGZH0 KDYS00,  --开单医生
       CFZT00 ZT0000,  --状态
       0 YJFZH0,  --医技分组号
       (select F.ZWXM00 from BM_YGBM00 F where F.YGBH00=A.CZY000) LRYXM0,  --录入员姓名
         (select ICKH00 FROM IC_YHXX00 WHERE BRID00=A.BRID00 and ZT0000='1' and rownum=1) ICKH00,  --IC卡号
         trunc(sysdate-to_date(SRRQ00||SRSJ00,'YYYYMMDDHH24:MI:SS')) KDJGTS,  --开单间隔天数
       (select MC0000 from BM_TSBZB0 where BH0000=A.TSBZBH and rownum=1) TSBZMC,  --特殊病种
       (select  round(sum(ZFJE00),2) from YF_MZCFMX where CFLSH0=A.CFLSH0 )ZFJE00,  --自费金额
       0 ZLXMID,  --诊疗项目ID
       ' ' ZFFSBZ    --自费发送标志
  from YF_MZCF00 A,BM_BMBM00 B,BM_BMBM00 C
 where A.YFBMBH=C.BMBH00 and A.GHKS00=B.BMBH00
 union all
select '2' CFBZ00,  --处方标识
       to_char(YJDJH0) CFLSH0,  --处方流水号
       to_char(B.LBBH00) CFLB00,  --处方类别
       C.LBMC00 CFLBMC,  --处方类别名称
       decode(nvl((select decode(B.LBBH00,6,VALUE0,'0') from XT_XTCS00 where NAME00='YS_SSLZLXMXSCSFXMMC'),'0'),'1',(select XMMC00 from BM_YYSFXM where SFXMID=(select max(SFXMID) from YJ_YWJJ00 where YJDJH0=A.YJDJH0)),A.ZLXMJC) CFMC00,  --处方名称
       A.SL0000||A.DW0000 SL0000,  --数量
       ZJE000 CFZJE0,  --处方总金额
       A.KDYSXM KDYSXM,  --开单医生姓名
       D.BMMC00 KDKSMC,  --开单科室名称
       E.BMMC00 ZXKSMC,  --执行科室名称
       decode(A.XMZT00,'0','新开','1','已定价','2','已收费','3','已执行','4','已报告','5','疑退','6','作废') CFZT00,  --处方状态
       to_char(A.CXDJH0) CXCFH0,  --冲销处方号
       A.ZYHGHH GHH000,  --挂号号
       A.ZYGHID GHID00,  --挂号id
       A.BRID00 BRID00,  --病人id
       KDRQ00||KDSJ00 KDRQSJ,  --开单日期时间
       to_date(KDRQ00,'yyyymmdd') KDRQ00,  --开单日期
       decode(A.SFDJH0,0,null,A.SFDJH0) DJH000,  --单据号
       A.BZ0000 BZ0000,  --备注
       KDYS00 KDYS00,  --开单医生
       XMZT00 ZT0000,  --状态
       YJFZH0 YJFZH0,  --医技分组号
       A.LRXM00 LRYXM0,  --录入员姓名
       (select ICKH00 FROM IC_YHXX00 WHERE BRID00=A.BRID00 and ZT0000='1' and rownum=1) ICKH00,  --IC卡号
       trunc(sysdate-to_date(KDRQ00||KDSJ00,'YYYYMMDDHH24:MI:SS')) KDJGTS,  --开单间隔天数
       (select MC0000 from BM_TSBZB0 where BH0000=A.BH0000 and rownum=1) TSBZMC,  --特殊病种
       (select round(sum(ZFJE00),2) from YJ_YWJJ00 where YJDJH0=A.YJDJH0) ZFJE00,  --自费金额
       A.ZLXMID ZLXMID,  --诊疗项目ID
       nvl((select ZFFSBZ from YJ_YWJJ00 G where YJDJH0=A.YJDJH0 and nvl(ZFFSBZ,'N')='Y'and rownum=1),'N') ZFFSBZ    --自费发送标志
  from YJ_YW0000 A,BM_ZLZD00 B,BM_ZLXMLB C,BM_BMBM00 D,BM_BMBM00 E
 where A.ZLXMID=B.ZLXMID and B.LBBH00=C.LBBH00 and A.KDKSBH=D.BMBH00 and A.YJKSBH=E.BMBH00 and A.MZZYBZ='0'
;