prompt 1.XXX 门诊药房发药视图                      VW_YF_MZCFFY
create or replace view VW_YF_MZCFFY 
as
-- MODIFICATION HISTORY                                
-- Person      Date           Comments                 
-- jlg         2017.10.10     Create    YF9-20170510-002 门诊药房发药视图
-- gzj         2018.05.14     增加DJFSMC,GHID00字段 for YF9-20180302-002
-- linshu      2020.01.02     调整了字段SRSJ00 by   YF9-20200101-001
-- linshu      2020.01.02     增加字段 DYCS00 by YF9-20200102-001
-- luowf       2020.12.22     增加字段 HLYYZT,SQSFZT by YF9-20201217-001
-- luowf       2022.06.26     增加字段FYCKBH.YPHJH0,CFSM00 by YF13X-20220626-001
-- luowf       2022.07.07     增加字段KFPC00 by YF13X-20220707-001
select 
  'Y' NX, 
  a.YFBMBH,
  (select BMMC00 from BM_BMBM00 where BMBH00 = a.YFBMBH) as YFBMMC,
  a.CFLSH0,
  a.GHH000,
  a.BRID00,
  a.BRXM00,
  a.XB0000,
  a.CSRQ00,
  substr(SF_XT_CSRQTONL(a.CSRQ00, null),1,10) as BRNL00,
  b.BRBLH0,
  b.BRLXDH,
  nvl(a.TSYS00,0)  as TSYS00,
  nvl(a.SHBZ00,'N') as  SHBZ00,
  decode(nvl(a.SHBZ00,'N'),'Y','审核','M','退回','未审核') as SHQK00,  
  nvl(a.CFDGS0,'5') CFDGS0,
  a.YSGZH0,
  a.YSXM00,
  (select BMMC00 from BM_BMBM00 where BMBH00 = a.GHKS00) as GHKS00,
  a.ZYTS00,
  a.ZYDJS0,
  a.GHKS00 as GHKSBM,
  a.SRRQ00, 
  a.SRSJ00,
  a.JZRQ00,
  a.CFZJE0,
  a.CFZT00,
  decode(a.CFZT00,'0','未记账','1','已记账','2','已发药','3','作废','4','需要审核','5','退药(负方)','6','退费(负方)') as CFZTMC,      
  a.YPDLBH,
  (select ZWXM00 from BM_YGBM00 where YGBH00 = a.CZY000) as LRY000,
  substr(SF_XT_GETMZZD(a.GHH000),1,150) as ZZDMC0,
  a.BZ0000,
  a.FYRQ00,
  a.FYSJ00,
  decode(substr(SF_PY_ZYPYLB(a.CFLSH0),1,10),'2','中草药','6','中草药','4','全成分','5','全成分','0','西药','1','成药') as PYLBBH,
  decode(a.PSZT00,'1','需皮试','2','皮试未通过不能发药','3','皮试通过可发药','0','非皮试') as PSJG00,
  a.SFKSLY,
  a.CFXZ00,
  a.BCCFH0,
  a.GHID00,
  (select DJFSMC from YS_CFXXB0 where CFLSH0=a.CFLSH0 and rownum=1) as DJFSMC,
  nvl(a.DYCS00,0) DYCS00,
  decode(a.SQSFZT,'0','不需审','1','待审','2','已审','3','干预中','4','双签中','') HLYYZT,  --合理用药状态名称

  a.SQSFZT,  --事前审方状态(合理用药状态)
  a.FYCKBH,  --发药窗口编号（芜湖不上配药系统，用手工发药界面和发药机对接）
  a.YPHJH0,  --药品货架号
  a.CFSM00,   --处方说明
  a.KFPC00    --扣费批次
 from YF_MZCF00 a,BM_BRXXB0 b
 where a.BRID00 = b.BRID00
 /

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%