prompt  等待打印的处方队列   VW_PY_DYCFDL
create or replace view VW_PY_DYCFDL (
   dlxh00,
   cflsh0,
   yfbmbh,
   ypje00,
   ghid00,
   ghh000,
   brid00,
   brxm00,
   xb0000,
   csrq00,
   pyckbh,
   fyckbh,
   zt0000,
   dyrq00,
   dysj00,
   pyrq00,
   pysj00,
   fyrq00,
   fysj00,
   check0,
   jzrq00,
   bmmc00,
   brnl00,
   kdys00,
   kdks00,
   fbmc00,
   ybmc00,
   zyts00,
   sfdr00,
   sfy000,
   fydlxh,
   cfzje0,
   CXBZ00,
   LCZD01,
   LCZD02,
   SHBZ00,
   THBZ00,
   ZYDJS0,
   YPDLBH,
   CFZT00,
   FYDLH2,
   PYR000,
   SRRQ00,
   YZYZM0,
   YZJGBZ,
   YPYFMC
   )
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- CSF     2008.03.06      收费、医技开具的零散处方也允许打印
-- CSF     2008.03.26      增加CXBZ00 
-- CSF     2009.02.12      增加临床诊断、是否审核标志,THBZ00
-- CSF     2009.03.09      增加字段zydjs0,YPDLBH,CFZT00
-- chenyw  2010.05.28      增加医保病人PHBZ00大于零才能发药的限制
-- chenyw  2010.06.08      控制参数为空设成默认值
-- laijg   2013.07.22      增加字段FYDLH2，用于替换配药系统FYDLXH前台显示 by YFPY-20130712-001
-- dsm     2017.10.09      增加PYR000 for YFPY-20170718-002
-- dsm     2017.12.15      增加SRRQ00,YZYZM0 for YFPY-20171215-001
-- huangjy 2019.04.25      增加药品用法  for YFPY-20190419-001
-- ---------   ----------  ------------------------------------------- 
AS
select a.DLXH00,c.CFLSH0,a.YFBMBH,a.YPJE00,c.GHID00,c.GHH000,c.BRID00,
       a.BRXM00,a.XB0000,a.CSRQ00,a.PYCKBH,a.FYCKBH,a.ZT0000,a.DYRQ00,
       a.DYSJ00,a.PYRQ00,a.PYSJ00,a.FYRQ00,a.FYSJ00,a.CHECK0,c.jzrq00,
       (select d.BMMC00 from bm_bmbm00 d where a.yfbmbh=d.bmbh00 )bmmc00,
       substr(sf_BQ_csrqtonl(a.csrq00),1,10) as brnl00,
       c.YSXM00 as kdys00,(select e.bmmc00 from bm_bmbm00 e where c.ghks00=e.bmbh00)kdks00,
       (select g.FBMC00 from bm_brxxb0 f,BM_BRFBB0 g where a.BRID00=f.BRID00 and f.FBBH00=g.fbbh00)fbmc00,
       (select h.ybmc00 from bm_brxxb0 f,BM_BRFBB0 g,ic_ybbrlb h where a.BRID00=f.BRID00 and f.FBBH00=g.fbbh00 and f.fbbh00=h.fbbh00 and f.yblb00=h.yblb00) ybmc00,
        c.ZYTS00,decode(c.ysgzh0,c.czy000,'','代录') as sfdr00,
       '收费员:'||(select zwxm00 from sf_brfy00 Y,bm_YGBM00 M where c.djh000=Y.djh000 and y.czy000=m.ygbh00 ) sfy000,
       a.fydlxh,
       c.cfzje0,
       (select cxbz00 from yf_mzcfmx where cflsh0=c.cflsh0 and rownum=1) cxbz00,
       SUBSTR(trim(LPAD('诊断:'||nvl(trim(DECODE(TRIM(SF_YF_GETXYZYZD(c.GHH000,'1')),'','','(西)'||TRIM(SF_YF_GETXYZYZD(c.GHH000,'1')))||DECODE(trim(SF_YF_GETXYZYZD(c.GHH000,'0')),'','','(中)'||TRIM(SF_YF_GETXYZYZD(c.GHH000,'0')))),'待查'),40,' ')),1,40) LCZD01,
       ' ' LCZD02,
       NVL(c.SHBZ00,'N') SHBZ00,
       c.THBZ00,
       c.ZYDJS0,
       c.YPDLBH,
       c.CFZT00,
       (SELECT nvl(MC0000,'') FROM BM_TYZD00 WHERE ZDMC00='发药窗口对应标识' and BH0000=a.FYCKBH and rownum=1)||a.FYDLXH FYDLH2,
       a.PYR000,
       c.SRRQ00,
       c.YZYZM0,
       c.YZJGBZ,
      (SELECT YPYFMC FROM YF_MZCFMX e WHERE e.CFLSH0=a.CFLSH0 AND ROWNUM=1) YPYFMC
from   yf_cfdl00 a,yf_mzcf00 c,bm_brxxb0 b
where  a.cflsh0=c.cflsh0 and a.zt0000='0' and 
       ((CFSRBZ='0') or (CFSRBZ='1') or (CFSRBZ='2') or (CFSRBZ='3') or  (CFSRBZ='4' and (select trim(nvl(max(value0),'Y')) from xt_xtcs00 where name00='MZPY_SFDYDLCF')='Y'))
       and c.brid00=b.brid00 and ((b.FBBH00<>3) or (((select trim(nvl(max(value0),'Y')) from xt_xtcs00 where trim(name00)='MZPY_YBBRJSZHSFZJJRDL')='Y') or (((select trim(nvl(value0,'Y')) from xt_xtcs00 where trim(name00)='MZPY_YBBRJSZHSFZJJRDL')='N') and (b.FBBH00=3  and a.PHBZ00>0))))
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

--modify end.

--modify by 2011.5.27 09:51
--等待发药处方队列
prompt  配发药的处方队列   VW_PY_FYCFDL
create or replace view VW_PY_FYCFDL
 (
 CHECK0, FLAG00, CFZT00, YPDLBH, DLXH00, CFLSH0, YFBMBH, YPJE00, GHID00,
GHH000, BRID00, BRXM00, XB0000, CSRQ00, PYCKBH, FYCKBH, ZT0000, DYRQ00,
DYSJ00, PYRQ00, PYSJ00, FYRQ00, FYSJ00, BMMC00, BRNL00, KDYS00, KDKS00,
FBMC00, YBMC00, ZYTS00, SFDR00, JZRQ00, SFY000, FYDLXH, THBZ00, CXBZ00,
CFDGS0, FYDLH2, PHDYCS, ZDMC00, BZ0000, PYR000, PYRXM0, YPPZSL, FYDLH1,
ZYDJS0,SRRQ00,YPYFMC
 )
  as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- CSF     2008.02.26      增加字段 THBZ00 退回的药品不出现在队列中
-- csf     2008.02.28      去掉thbz00条件
-- csf     2008.03.26      增加cxbz00字段
-- csf     2009.04.29      check0字段增加为空的默认值
-- laijg   2011.05.26      增加处方单格式字段CFDGS0
-- laijg   2013.07.22      增加字段FYDLH2，用于替换配药系统FYDLXH前台显示 by YFPY-20130712-001
-- daihq   2014.10.29      增加PHDYCS for YFPY-20141023-001
-- huangjy 2016.12.09      增加ZDMC00 for YFPY-20161117-001
-- dsm     2017.03.13      增加BZ0000 for YFPY-20170306-001
-- dsm     2017.10.09      增加PYR000 for YFPY-20170718-002
-- huangjy 2018.05.29      修改ZDMC00，增加FYDLH1 类型并归档 for YFPY-20180524-001  
-- huangjy 2019.04.25      增加药品用法,开单日期,中药代煎数  for YFPY-20190419-001    
-- ---------   ----------  -------------------------------------------
select nvl(a.CHECK0,'0'),'N' FLAG00,c.CFZT00,C.YPDLBH,a.DLXH00,a.CFLSH0,a.YFBMBH,
       a.YPJE00,a.GHID00,a.GHH000,a.BRID00,a.BRXM00,a.XB0000,a.CSRQ00,a.PYCKBH,
       a.FYCKBH,a.ZT0000,a.DYRQ00,a.DYSJ00,a.PYRQ00,a.PYSJ00,a.FYRQ00,a.FYSJ00,
       d.BMMC00,substr(sf_bq_csrqtonl(a.CSRQ00),1,10) as BRNL00,c.YSXM00 as KDYS00,
       e.BMMC00 as KDKS00,g.FBMC00 as FBMC00,h.YBMC00 as YBMC00,c.ZYTS00,
       decode(c.YSGZH0,c.CZY000,'','代录') as SFDR00 ,c.JZRQ00,
       '收费员:'||(select ZWXM00 from SF_BRFY00 y,BM_YGBM00 m where c.DJH000=y.DJH000 and y.CZY000=m.YGBH00 ) SFY000,
       a.FYDLXH,c.THBZ00,(select CXBZ00 from YF_MZCFMX where CFLSH0=c.CFLSH0 and rownum=1) CXBZ00,c.CFDGS0,
       (select nvl(MC0000,'') from BM_TYZD00 WHERE ZDMC00='发药窗口对应标识' and BH0000=a.FYCKBH and rownum=1)||a.FYDLXH FYDLH2,
       nvl(a.PHDYCS,1)-1 PHDYCS,
       (select ZDMC00  from YS_BRZDXX k  where ZDXXID=(select  max(ZDXXID)  from YS_BRZDXX k where GHH000=c.GHH000 and ZZDBZ0='3'))
       ||','||(select wmsys.wm_concat(t.ZDMC00)  from YS_BRZDXX t  where t.GHH000=c.GHH000 and t.ZZDBZ0='4' and rownum<=
       (select nvl(VALUE0,'0') from XT_XTCS00 where NAME00='MZPY_ZDXSXXCS'))  ZDMC00,
       a.LZH000 BZ0000,
       nvl(a.PYR000,0) PYR000,
       (select ZWXM00 from BM_YGBM00 where YGBH00=a.PYR000) PYRXM0,
       YPPZSL,
       (select nvl(MC0000,'') from BM_TYZD00 WHERE ZDMC00='发药窗口对应标识' and BH0000=a.FYCKBH and rownum=1)||to_char(a.FYDLXH,'00000') FYDLH1,
       c.ZYDJS0,
       c.SRRQ00,
       (SELECT YPYFMC FROM YF_MZCFMX e WHERE e.CFLSH0=a.CFLSH0 AND ROWNUM=1) YPYFMC
from   YF_CFDL00 a,BM_CKBM00 b,YF_MZCF00 c,BM_BMBM00 d,BM_BMBM00 e,BM_BRXXB0 f,BM_BRFBB0 g,IC_YBBRLB h
where  a.CFLSH0=c.CFLSH0 and c.GHKS00=e.BMBH00 and a.PYCKBH=b.CKBH00 and a.BRID00=f.BRID00 and f.FBBH00=g.FBBH00
       and f.FBBH00=h.FBBH00 and f.YBLB00=h.YBLB00 and a.YFBMBH=d.BMBH00
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%




--modify end.

--modify by 2007.06.08 15:55
--打印处方明细信息
--打印处方明细信息
CREATE OR REPLACE VIEW SD_HOSPITAL.VW_PY_DYCFXX
 (
 CFLSH0, CFID00, YPJE00, GHH000, BRXM00, XB0000, DYRQ00, DYSJ00, BMMC00, 
BRNL00, KDYS00, KDKS00, FBMC00, BCCFH0, YBMC00, ZYTS00, SFDR00, SFY000, 
LSDJ00, SFZBY0, YYSJ00, YYSJDW, YFSMMC, ZB0000, ZJYFMC1, ZJYFMC, XMYFMC,
BZ0000, ZQCD00, PSLBID, YPDLBH, XJJE00, YPMCGG, SLDW00, JL00DW, XXYF00, 
ZXCS00, FYDLXH, ZCS000, GROUP0, XYZDMC, YPGG00, YBKH00, BRDH00, QWM000, 
XBS000, QWMSX0, FYDW00, FYSL00, FYSLDW, YYJC00, YFBMMC, FYCKMC, JZRQ00, 
HZBZ00, YPPCMC, PDXH00, DLXH00, BZ0001

 )
 AS 
 -- MODIFICATION HISTORY
-- Person      Date        Comments
-- YANGH     2007.06.05    执行次数如果是0，则不显示
-- YANGH     2007.06.05    增加病人电话字段（BM_BRXXB0.BRDH00）
-- YANGH     2007.06.05    增加病人医保卡号（BM_BRXXB0.YBKH00）
-- CSF       2008.06.26    增加区位码
-- CSF       2008.07.04    在bm_brxxb0表中取家庭电话或联系人电话
-- CSF       2008.10.11    增加区位码顺序 
-- CSF       2009.03.03    增加yf_ypkcxx.fydw00 f2j000
-- YANGH     2009.09.30    新增字段YYJC00, YFBMMC, FYCKMC, JZRQ00, HZBZ00, YPPCMC, PDXH00, DLXH00
-- YANGH     2009.10.21    新增字段bz0001
-- YANGH     2009.11.16    医院简称通过部门表关联YYID00
-- CSF       2011.03.09    区位码算法等内容为广东省专用，故加参数MZPY_YYGXH控制。
-- ---------   ----------  -------------------------------------------  
select a.CFLSH0,i.CFID00,a.YPJE00,c.GHH000,
       a.BRXM00,a.XB0000,a.DYRQ00,a.DYSJ00,
       (select d.BMMC00 from bm_bmbm00 d where a.yfbmbh=d.bmbh00 ),
       substr(sf_BQ_csrqtonl(a.csrq00),1,10) as brnl00,
       c.YSXM00 as kdys00,
       (select e.bmmc00 from bm_bmbm00 e where c.ghks00=e.bmbh00 )kdks00,
       (select g.FBMC00 from BM_BRFBB0 g where f.FBBH00=g.fbbh00 ) fbmc00,c.BCCFH0,
       (select h.ybmc00 from ic_ybbrlb h where f.yblb00=h.yblb00 and f.fbbh00=h.fbbh00 )ybmc00,
       c.ZYTS00,decode(c.cfsrbz,'4','(代录)','') as sfdr00,
       '收费员:'||(select zwxm00 from sf_brfy00 Y,bm_YGBM00 M where c.djh000=Y.djh000 and y.czy000=m.ygbh00 ) sfy000,
       i.LSDJ00,i.SFZBY0,i.YYSJ00,i.YYSJDW,i.YFSMMC,
       i.ZB0000,i.ZJYFMC, i.zjyfmc/*decode(i.ZJYFMC,'煎服','',i.ZJYFMC)*/ ,  i.XMYFMC,              
       i.BZ0000,
       i.ZQCD00,
       (SELECT PSMC00 FROM BM_PS0000 WHERE PSID00=I.PSLBID),
       i.YPDLBH,
       i.YPZSL0*i.lsdj00 as xjje00, 
       trim(decode(sfzby0,'Y','(自备)','')||decode(i.YPDLBH,'2',substrb(i.ypmc00,1,12),substrb(i.ypmc00,1,40))) as ypmcgg,
       decode(substr(trim(i.ypzsl0),1,1),'.','0','')||trim(i.ypzsl0)||trim(i.cfdw00) as sldw00,
       --Lpad(trim(i.csl000)||' ',5,' ')||trim(i.JLDW00) as jl00dw,  
       decode(substr(trim(i.csl000),1,1),'.','0','')||Lpad(trim(i.csl000)||' ',5,' ')||trim(i.JLDW00) as jl00dw,--20090603处理0.5剂量中0  不显示问题;
       trim(decode(i.ypyfmc,null,null,i.ypyfmc||'/'))||pcmc00||'/'||--(select trim(sm0000)||'/' from bm_pc0000 m where m.pcmc00=i.pcmc00 and rownum=1)||
           decode(i.csl000,null,null,'每次'||decode(substr(trim(i.csl000),1,1),'.','0','')||trim(i.csl000)|| trim(i.jldw00))as xxyf00,
           --decode(i.bz0000,null,null,'/'||i.bz0000) 
       to_char((decode(SF_PY_PDSFZJ(i.ypnm00),'Y',decode(i.ZXCS00,0,null,i.ZXCS00),NULL))),a.fydlxh,decode(ZB0000,null,null,'共'||YYSJ00*nvl((select ZQCS00 from bm_pc0000 m where m.pcmc00=i.pcmc00 and rownum=1),0)||'次'),
       (select nvl(i.ZB0000,i.CFID00-x.CFID00+60) from yf_mzcfmx x where a.cflsh0=x.cflsh0 and rownum=1),
       substrb(sf_py_brzxx(c.ghid00),1,100),
       Trim(I.ypgg00),decode(f.BRGFZH,'0','',f.BRGFZH||'/')||decode(f.YBKH00,'0','',f.YBKH00),
       decode((select nvl(brdh00,'') from bm_brxxb0 where brid00=c.brid00),'',(select nvl(brlxdh,'') from bm_brxxb0 where brid00=c.brid00),(select nvl(brdh00,'') from bm_brxxb0 where brid00=c.brid00)),
       decode((select trim(VALUE0) from XT_XTCS00 where NAME00='MZPY_YYGXH'),'0',sp_py_getqwm(c.yfbmbh,i.ypnm00,i.csl000),' ') qwm000,
       decode((select trim(VALUE0) from XT_XTCS00 where NAME00='MZPY_YYGXH'),'0',sp_py_getxbs(c.cflsh0),0) xbs000,
       decode((select trim(VALUE0) from XT_XTCS00 where NAME00='MZPY_YYGXH'),'0',sp_py_getqwmsx(c.yfbmbh,i.ypnm00,i.csl000),' ') qwmsx0,
       j.fydw00,
       ROUND(i.ypzsl0*i.zhl000/j.f2j000,3) fysl00,
       decode(substr(trim(ROUND(i.ypzsl0*i.zhl000/j.f2j000,3)),1,1),'.','0','')||trim(ROUND(i.ypzsl0*i.zhl000/j.f2j000,3))||trim(j.fydw00) as fysldw,
       (SELECT YYJC00 FROM XT_YYXX00 a,BM_BMBM00 b WHERE A.YYID00=B.YYID00 AND b.BMBH00=C.YFBMBH AND ROWNUM=1) YYJC00,
       (SELECT BMMC00 FROM BM_BMBM00 WHERE BMBH00=C.YFBMBH AND ROWNUM=1) YFBMMC,
       (SELECT CKMC00 FROM BM_CKBM00 WHERE CKBH00=A.FYCKBH AND ROWNUM=1) FYCKMC,
       TO_CHAR(TO_DATE(C.JZRQ00,'YYYYMMDD'),'YYYY-MM-DD') JZRQ00,
       DECODE((SELECT HZBZ00 FROM BQ_YFLB00 E,BM_YPYF00 F WHERE E.YFLBID=F.YFLBID AND F.YPYFMC=I.YPYFMC),'0','☆',' ') HZBZ00,
       (SELECT SM0000 FROM BM_PC0000 WHERE PCMC00=I.PCMC00 AND ROWNUM=1) YPPCMC,
       (SELECT PDXH00 FROM YF_YPKCXX WHERE YPNM00=I.YPNM00 AND YFBMBH=C.YFBMBH AND ROWNUM=1) PDXH00,
       TO_NUMBER(A.FYDLXH) DLXH00,
       (select bz0000 from bm_yd0000 where ypnm00=i.ypnm00) BZ0001
from   yf_cfdl00 a,yf_mzcf00 c,bm_brxxb0 f,yf_mzcfmx i,yf_ypkcxx j
where  a.cflsh0=c.cflsh0 and a.BRID00=f.BRID00 and 
       c.cflsh0=i.cflsh0 and c.yfbmbh=j.yfbmbh and 
       i.ypnm00=j.ypnm00
     order by i.ZB0000
/     
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
--modify end.

--modify by 2007.06.08 17:35
--注射处方明细信息
CREATE OR REPLACE VIEW SD_HOSPITAL.VW_PY_ZSCFXX
 (
 CFLSH0, CFID00, YPJE00, GHH000, BRXM00, XB0000, DYRQ00, DYSJ00, BMMC00, 
BRNL00, KDYS00, KDKS00, FBMC00, BCCFH0, YBMC00, ZYTS00, SFDR00, SFY000, 
LSDJ00, SFZBY0, YYSJ00, YYSJDW, YFSMMC, ZB0000, ZJYFMC1, ZJYFMC, XMYFMC, 
BZ0000, ZQCD00, PSLBID, YPDLBH, XJJE00, YPMCGG, SLDW00, JL00DW, XXYF00, 
ZXCS00, FYDLXH, ZCS000, GROUP0, XYZDMC, YPGG00, BRDH00, YBKH00,XH0000,
XBS000, QWM000, FYDW00, FYSL00, FYSLDW, YYJC00, YFBMMC, FYCKMC, JZRQ00, 
HZBZ00, YPPCMC, PDXH00, DLXH00, BZ0001, XH0001
 )
 AS 
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- YANGH     2007.06.05    执行次数如果是0，则不显示
-- YANGH     2007.06.05    增加病人电话字段（BM_BRXXB0.BRDH00）
-- YANGH     2007.06.05    增加病人医保卡号（BM_BRXXB0.YBKH00）
-- CSF       2009.03.03    增加yf_ypkcxx.fydw00 f2j000
-- YANGH     2009.09.30    新增字段YYJC00, YFBMMC, FYCKMC, JZRQ00, HZBZ00, YPPCMC, PDXH00, DLXH00
-- YANGH     2009.10.21    新增字段bz0001
-- YANGH     2009.10.21    新增字段XH0001
-- YANGH     2009.11.16    医院简称通过部门表关联YYID00
-- ---------   ----------  ------------------------------------------- 
select a.CFLSH0,i.CFID00,a.YPJE00,c.GHH000,
       a.BRXM00,a.XB0000,a.DYRQ00,a.DYSJ00,
       (select d.BMMC00 from bm_bmbm00 d where a.yfbmbh=d.bmbh00 ),
       substr(sf_BQ_csrqtonl(a.csrq00),1,10) as brnl00,
       c.YSXM00 as kdys00,
       (select e.bmmc00 from bm_bmbm00 e where c.ghks00=e.bmbh00 )kdks00,
       (select g.FBMC00 from BM_BRFBB0 g where f.FBBH00=g.fbbh00 ) fbmc00,c.BCCFH0,
       (select h.ybmc00 from ic_ybbrlb h where f.yblb00=h.yblb00 and f.fbbh00=h.fbbh00 )ybmc00,
       c.ZYTS00,decode(c.cfsrbz,'4','(代录)','') as sfdr00,
       '收费员:'||(select zwxm00 from sf_brfy00 Y,bm_YGBM00 M where c.djh000=Y.djh000 and y.czy000=m.ygbh00 ) sfy000,
       i.LSDJ00,i.SFZBY0,i.YYSJ00,i.YYSJDW,i.YFSMMC,
       i.ZB0000,i.ZJYFMC, i.zjyfmc ,  i.XMYFMC,
       i.BZ0000,
       i.ZQCD00,
       (SELECT PSMC00 FROM BM_PS0000 WHERE PSID00=I.PSLBID),
       i.YPDLBH,
       i.YPZSL0*i.lsdj00 as xjje00,
        trim(decode(sfzby0,'Y','(自备)','')||substrb( trim(i.ypmc00),1,40)  ) as ypmcgg,
       
       decode(substr(trim(i.ypzsl0),1,1),'.','0','')||trim(i.ypzsl0)||trim(i.cfdw00) as sldw00,
       LPAD(trim(i.csl000),5,' ')||' '||trim(i.JLDW00) as jl00dw,
       trim(decode(i.ypyfmc,null,null,i.ypyfmc||'/'))||pcmc00||'/'||--(select trim(sm0000)||'/' from bm_pc0000 m where m.pcmc00=i.pcmc00 and rownum=1)||
           decode(i.csl000,null,null,'每次'||decode(substr(trim(i.csl000),1,1),'.','0','')||trim(i.csl000)|| trim(i.jldw00))as xxyf00,
       to_char(decode(i.ZXCS00,0,null,i.ZXCS00)),a.fydlxh,decode(ZB0000,null,null,'共'||YYSJ00*nvl((select ZQCS00 from bm_pc0000 m where m.pcmc00=i.pcmc00 and rownum=1),0)||'次'),
       (select nvl(i.ZB0000,i.CFID00-x.CFID00+60) from yf_mzcfmx x where a.cflsh0=x.cflsh0 and rownum=1),
       substrb(sf_py_brzxx(c.ghid00),1,100),
       trim(i.YPGG00),
       (select brdh00 from bm_brxxb0 where brid00=c.brid00),
       decode(f.BRGFZH,'0','',f.BRGFZH||'/')||decode(f.YBKH00,'0','',f.YBKH00),
       0 xh0000,0 xbs000,sp_py_getqwm(c.yfbmbh,i.ypnm00,i.csl000) qwm000,
       j.fydw00,
       ROUND(i.ypzsl0*i.zhl000/j.f2j000,3) FYSL00,
       decode(substr(trim(ROUND(i.ypzsl0*i.zhl000/j.f2j000,3)),1,1),'.','0','')||trim(ROUND(i.ypzsl0*i.zhl000/j.f2j000,3))||trim(j.fydw00) as fysldw,
       (SELECT YYJC00 FROM XT_YYXX00 a,BM_BMBM00 b WHERE A.YYID00=B.YYID00 AND b.BMBH00=C.YFBMBH AND ROWNUM=1) YYJC00,
       (SELECT BMMC00 FROM BM_BMBM00 WHERE BMBH00=C.YFBMBH AND ROWNUM=1) YFBMMC,
       (SELECT CKMC00 FROM BM_CKBM00 WHERE CKBH00=A.FYCKBH AND ROWNUM=1) FYCKMC,
       TO_CHAR(TO_DATE(C.JZRQ00,'YYYYMMDD'),'YYYY-MM-DD') JZRQ00,
       DECODE((SELECT HZBZ00 FROM BQ_YFLB00 E,BM_YPYF00 F WHERE E.YFLBID=F.YFLBID AND F.YPYFMC=I.YPYFMC),'0','☆',' ') HZBZ00,
       (SELECT SM0000 FROM BM_PC0000 WHERE PCMC00=I.PCMC00 AND ROWNUM=1) YPPCMC,
       (SELECT PDXH00 FROM YF_YPKCXX WHERE YPNM00=I.YPNM00 AND YFBMBH=C.YFBMBH AND ROWNUM=1) PDXH00,
       TO_NUMBER(A.FYDLXH) DLXH00,
       (select bz0000 from bm_yd0000 where ypnm00=i.ypnm00) BZ0001,
       '0' XH0001
from   yf_cfdl00 a,yf_mzcf00 c,bm_brxxb0 f,yf_mzcfmx i,yf_ypkcxx j
where  a.cflsh0=c.cflsh0 and a.BRID00=f.BRID00 and i.ypyfmc in ('ivd','im','iv','h','ivd(1)','ivd(2)','ivd(3)','接滴','接滴(1)','接滴(2)','ivd(*)','ivd(*2)','ivd(*1)') and 
       c.cflsh0=i.cflsh0  and c.yfbmbh=j.yfbmbh and i.ypnm00=j.ypnm00
       order by i.ZB0000
/
--modify end.

--modify by 2005.07.21 10:16
--注射处方药品个数
CREATE OR REPLACE VIEW SD_HOSPITAL.VW_PY_ZSCFGS
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- YANGH     2009.11.16    用法增加了几种
-- ---------   ----------  ------------------------------------------- 
select b.cflsh0 from ys_cfmx00 b,bm_ypyf00 c 
where b.yfid00=c.yfid00 and c.ypyfmc in ('ivd','im','iv','h','ivd(1)','ivd(2)','ivd(3)','接滴','接滴(1)','接滴(2)','ivd(*)','ivd(*2)','ivd(*1)') and nvl(b.zxcs00,0)>=0
/
--modify end.

prompt 创建处方药品用法打印试图 vw_mzpy_ypyfPrint
create or replace view vw_mzpy_ypyfPrint
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- csf         2008.05.05  create view
-- huangjy     2020.04.13  add YPZSL0 by YFPY-20200402-001
-- ---------   ----------  -------------------------------------------
select a.cflsh0,b.cfid00,
a.brxm00,
b.ypnm00,
substr(Trim(SF_YF_GETYPMC(b.YPNM00)),1,15) ypmc00,
b.ypgg00,
b.YPZSL0||b.CFDW00 YPZSL0,
TRIM(nvl(b.YFSMMC,' '))||TRIM(nvl(b.YPYFMC,' ')) ypyfmc,
(select sm0000 from bm_pc0000 where trim(pcmc00)=trim(b.pcmc00) and rownum=1) pcmc00,
'每次'||Trim(DECODE(round(b.CSL000/b.zhl000,3),NULL,'',0,'',DECODE(SUBSTR(ABS(round(b.CSL000/b.zhl000,3)),1,1),'.','0','')||ABS(round(b.CSL000/b.zhl000,3))||trim(b.cfDW00))) mcyf00
from yf_mzcf00 a,yf_mzcfmx b where  a.cflsh0=b.cflsh0
/

/*
--此处注释掉，请从药房系统自定义视图处查找相关脚本
--2009.03.11 从药房视图中移过来 csf
create or replace view SD_HOSPITAL.VW_YF_PDPRINT
 (
 CFLSH0, BRID00, BRXM00, YFBMBH, YFMC00, FYCKBH, PYCKBH, FYDLXH, DDRS00, 
 ZXH000, YE0000, CFJE00, FYCK00, CKMC00,PHBZ00,DLXH00,ZYDJS0,
 ZYMRCS,--中药每日服用次数
 BS0000,--总包数=代煎数*中药每日服用次数
 DJFY00, --代煎费用
 QYSJ00,  --病人取药时间
 PHDYCS  --排号打印次数

 )
 AS 
 -- Person      Date        Comments
 -- xzw      2007.12.11     增加字段PHBZ00,DLXH00
 -- csf      2009.03.02     增加字段ZYDJS0
 -- csf      2010.05.20     增加字段，BS0000,ZYMRCS、DJFY00、QYSJ00、PHDYCS
 -- csf      2010.08.12     取病人余额，加条JFLBID=1
 -- csf      2010.08.12     自助取号机，打印次数限制
 -- csf      2010.08.18     取消2010.08.12的修改记录，因为自助取号系统独立视图来处理
SELECT  a.CFLSH0,
        a.BRID00,
        a.BRXM00,
        a.YFBMBH,
        (SELECT BMMC00 from BM_BMBM00 where BMBH00=A.YFBMBH) YFMC00,
        a.PYCKBH AS FYCKBH,
        a.PYCKBH,
        a.FYDLXH,
        (SELECT COUNT(DISTINCT FYDLXH) FROM YF_CFDL00 WHERE FYDLXH<A.FYDLXH AND PHRQ00=A.PHRQ00 AND ZT0000 IN ('0','1') AND YFBMBH=A.YFBMBH AND YPJE00>=0) DDRS00,
        a.PHZXH0,
        (SELECT XJYE00 FROM SF_BRZHXX WHERE BRID00=A.BRID00 and JFLBID=1) AS YE0000,
        --(SELECT CFZJE0 FROM YF_MZCF00 WHERE CFLSH0=A.CFLSH0) AS CFJE00,
        b.CFZJE0 as CFJE00,
        a.FYCKBH,
        (SELECT TRIM(CKMC00) FROM BM_CKBM00 WHERE CKBH00=A.FYCKBH AND ROWNUM=1),
        a.PHBZ00,
        a.DLXH00,
        --(SELECT ZYDJS0 FROM YF_MZCF00 B WHERE B.CFLSH0=A.CFLSH0) AS ZYDJS0,
        b.ZYDJS0,
        b.ZYMRCS,
        b.ZYDJS0*b.ZYMRCS as BS0000,
        (select HJJE00 from SF_BRFY00 where DJH000=b.FJFDJH) DJFY00,
        (select to_date(QYRQ00||QYSJ00,'YYYYMMDDHH24:MI:SS') from YF_JYXXB0 where CFLSH0=a.CFLSH0) QYSJ00,
        a.PHDYCS
    from YF_CFDL00 a,YF_MZCF00 b where a.CFLSH0=b.CFLSH0 
*/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

/*AS
select a.CFLSH0,-1,rpad('挂号号:'||Trim(a.GHH000),16,' ')||
       rpad('姓名:'||Trim(a.BRXM00),16,' ')||rpad('性别:'||Trim(a.XB0000),16,' ')||
       rpad('年龄:'||Trim(SF_YF_CSRQTONL(a.CSRQ00,a.SRRQ00)),15,' ')||
       rpad('处方号:'||Trim(a.CFLSH0),16,' ')
from YF_MZCF00 a
union all-- CFID00=-1~-10的为页头
select a.CFLSH0,-2,rpad('代煎剂数:'||Trim(a.ZYDJS0)||'服药'||Trim(a.ZYMRCS)||'次/日',16,' ')||
       rpad('代煎费:'||(Select to_char(sum(F.HJJE00),'999990.00') HJJE00 from SF_FYMX00 F where F.DJH000=A.FJFDJH and F.XMBH00=(select Trim(VALUE0) from xt_xtcs00 where name00='YF_ZYJYF_SFDM')),20,' ')||
       rpad('用法:'||(Select Trim(X.YPYFMC)||Trim(X.YFSMMC) YYFF00 from YF_MZCFMX X where a.CFLSH0=x.CFLSH0 and rownum=1),16,' ')
from YF_MZCF00 a
union all
SELECT a.CFLSH0,-3,'送方时间:   月   日   时   分        取药时间:   月   日   时   分'
FROM  YF_MZCF00 a
union all
SELECT a.CFLSH0,-4,'中药房联系电话：87878206'
FROM  YF_MZCF00 a
union all
SELECT a.CFLSH0,-5,rpad('医师:'||Trim(a.YSXM00),15,' ')||
        rpad('调剂:',15,' ')||rpad('煎药:',15,' ')||
        '打印日期:'||to_char(sysdate,'YYYY-MM-DD')
FROM  YF_MZCF00 a
union all
SELECT a.CFLSH0,-6,'门诊取药地点：中药房5号窗口  取药时间：8:00-20:00      收方人签名_______'
FROM YF_MZCF00 a
union all
SELECT a.CFLSH0,-7,'备注：'
FROM YF_MZCF00 a
*/
/

-- End of DDL script for VW_YF_DJDDY
prompt 新增住院中药代煎单格式视图 VW_YF_ZYZYDJDFR3

CREATE OR REPLACE VIEW sd_hospital.VW_YF_ZYZYDJDFR3 (
 CFLSH0,
 GHH000,
 BRXM00,
 XB0000,    
 NL0000,
 ZYDJS0,
 DJFY00,
 YFSMMC,
 YSXM00,
 TITLE0
)
AS
SELECT a.CFLSH0,a.ZYH000,a.BRXM00,
       a.XB0000,SF_YF_CSRQTONL(a.CSRQ00,a.SRRQ00) as NL0000,
       a.ZYDJS0,
       (Select to_char(sum(F.HJJE00),'999990.00') HJJE00 from SF_FYMX00 F 
         where F.DJH000=A.FJFDJH and F.XMBH00=(select Trim(VALUE0) from xt_xtcs00 where name00='YF_ZYJYF_SFDM')) as DJFY00,
       (Select Trim(X.YPYFMC)||Trim(X.YFSMMC) YYFF00 from YF_ZYCFMX X where a.CFLSH0=x.CFLSH0 and rownum=1) as YFSMMC,
       a.YSXM00,sf_xt_yymc00(a.YFBMBH)||'中药代煎单' title0 
From YF_ZYCF00 a

/*AS
select a.CFLSH0,-1,rpad('挂号号:'||Trim(a.GHH000),16,' ')||
       rpad('姓名:'||Trim(a.BRXM00),16,' ')||rpad('性别:'||Trim(a.XB0000),16,' ')||
       rpad('年龄:'||Trim(SF_YF_CSRQTONL(a.CSRQ00,a.SRRQ00)),15,' ')||
       rpad('处方号:'||Trim(a.CFLSH0),16,' ')
from YF_MZCF00 a
union all-- CFID00=-1~-10的为页头
select a.CFLSH0,-2,rpad('代煎剂数:'||Trim(a.ZYDJS0)||'服药'||Trim(a.ZYMRCS)||'次/日',16,' ')||
       rpad('代煎费:'||(Select to_char(sum(F.HJJE00),'999990.00') HJJE00 from SF_FYMX00 F where F.DJH000=A.FJFDJH and F.XMBH00=(select Trim(VALUE0) from xt_xtcs00 where name00='YF_ZYJYF_SFDM')),20,' ')||
       rpad('用法:'||(Select Trim(X.YPYFMC)||Trim(X.YFSMMC) YYFF00 from YF_MZCFMX X where a.CFLSH0=x.CFLSH0 and rownum=1),16,' ')
from YF_MZCF00 a
union all
SELECT a.CFLSH0,-3,'送方时间:   月   日   时   分        取药时间:   月   日   时   分'
FROM  YF_MZCF00 a
union all
SELECT a.CFLSH0,-4,'中药房联系电话：87878206'
FROM  YF_MZCF00 a
union all
SELECT a.CFLSH0,-5,rpad('医师:'||Trim(a.YSXM00),15,' ')||
        rpad('调剂:',15,' ')||rpad('煎药:',15,' ')||
        '打印日期:'||to_char(sysdate,'YYYY-MM-DD')
FROM  YF_MZCF00 a
union all
SELECT a.CFLSH0,-6,'门诊取药地点：中药房5号窗口  取药时间：8:00-20:00      收方人签名_______'
FROM YF_MZCF00 a
union all
SELECT a.CFLSH0,-7,'备注：'
FROM YF_MZCF00 a
*/
/

-- End of DDL script for VW_YF_DJDDY



prompt 创建视图用于自助取号的排号单打印VW_PY_ZZQHPRINT
create or replace view SD_HOSPITAL.VW_PY_ZZQHPRINT
 (
 CFLSH0, --处方流水号
 BRID00, --病人id
 BRXM00, --病人姓名
 YFBMBH, --药房部门编号
 YFMC00, --用法名称
 FYCKBH, --发药窗口编号
 PYCKBH, --配药窗口编号
 FYDLXH, --发药队列序号
 DDRS00, --等待人数
 ZXH000, --执行号
 YE0000, --余额
 CFJE00, --处方金额
 FYCK00, --发药窗口编号
 CKMC00,--窗口名称
 PHBZ00,--排号标志
 DLXH00,--队列序号
 ZYDJS0,--中药代煎数
 ZYMRCS,--中药每日服用次数
 BS0000,--总包数=代煎数*中药每日服用次数
 DJFY00,--代煎费用
 QYSJ00,--病人取药时间
 PHDYCS, --排号打印次数
 BDBZ00, --补打标志
 SRRQ00,  --输入日期
 FYDLH2,   --日期+发药队列序号 
 FYDLH3, --字母+发药队列序号
 DDRS02  --等待人数(字母) 
 )
 as 
 -- Person      Date        Comments
 -- xzw      2007.12.11     增加字段PHBZ00,DLXH00
 -- csf      2009.03.02     增加字段ZYDJS0
 -- csf      2010.05.20     增加字段，BS0000,ZYMRCS、DJFY00、QYSJ00、PHDYCS
 -- csf      2010.08.12     取病人余额，加条JFLBID=1
 -- csf      2010.08.12     自助取号机，打印次数限制
 -- csf      2010.08.18     该视图复制VW_YF_PDPRINT
 -- csf      2010.12.24     为了提高效率，yf_mzcf00表加时间条件
 -- csf      2011.06.01     加补打标志BDBZ00
 -- csf      2011.11.23     加输入日期，同时去除输入日期的限制
 -- laijg    2012.02.24     增加字段FYDLH2 by YFPY-20120223-001  
 -- laijg    2013.04.22     增加字段FYDLH3,DDRS02  by YFPY-20130128-001 
 -- laijg    2013.07.24     替换FYDLH3，从通用字段取，便于维护 by YFPY-20130712-001
 --------   -----------     ------------------------------------------------ 
select  a.CFLSH0,
        a.BRID00,
        a.BRXM00,
        a.YFBMBH,
        (select BMMC00 from BM_BMBM00 where BMBH00=A.YFBMBH) YFMC00,
        a.PYCKBH as FYCKBH,
        a.PYCKBH,
        a.FYDLXH,
        (select COUNT(distinct FYDLXH) from YF_CFDL00 where FYDLXH<A.FYDLXH and PHRQ00=A.PHRQ00 and ZT0000 in ('0','1') and YFBMBH=A.YFBMBH and YPJE00>=0) DDRS00,
        a.PHZXH0,
        (select XJYE00 from SF_BRZHXX where BRID00=A.BRID00 and JFLBID=1) as YE0000,
        --(SELECT CFZJE0 FROM YF_MZCF00 WHERE CFLSH0=A.CFLSH0) AS CFJE00,
        b.CFZJE0 as CFJE00,
        a.FYCKBH,
        (select TRIM(CKMC00) from BM_CKBM00 where CKBH00=A.FYCKBH and rownum=1),
        a.PHBZ00,
        a.DLXH00,
        --(SELECT ZYDJS0 FROM YF_MZCF00 B WHERE B.CFLSH0=A.CFLSH0) AS ZYDJS0,
        b.ZYDJS0,
        b.ZYMRCS,
        b.ZYDJS0*b.ZYMRCS as BS0000,
        (select HJJE00 from SF_BRFY00 where DJH000=b.FJFDJH) DJFY00,
        (select to_date(QYRQ00||QYSJ00,'YYYYMMDDHH24:MI:SS') from YF_JYXXB0 where CFLSH0=a.CFLSH0) QYSJ00,
        a.PHDYCS,
        decode(NVL(a.PHDYCS,1),1,' ','补打') BDBZ00,
        b.SRRQ00,
        substr(to_char(sysdate,'YYYYMMDD'),5,4)||a.FYDLXH,
      --  decode(A.FYCKBH,1,'A',2,'B',3,'C',4,'D',5,'E',6,'F',7,'G',8,'H',9,'I',10,'J','')||a.FYDLXH,
        (SELECT nvl(MC0000,'') FROM BM_TYZD00 WHERE ZDMC00='发药窗口对应标识' and BH0000=a.FYCKBH and rownum=1)||a.FYDLXH,
        (select COUNT(distinct FYDLXH) from YF_CFDL00 where FYDLXH<A.FYDLXH and PHRQ00=A.PHRQ00 and ZT0000 in ('0','1') and YFBMBH=A.YFBMBH and YPJE00>=0 AND FYCKBH=A.FYCKBH) DDRS02
    from YF_CFDL00 a,YF_MZCF00 b where a.CFLSH0=b.CFLSH0 
    and (    (select  to_number(NVL(max(trim(value0)),'0')) from XT_XTCS00 where NAME00='ZZQH_QHCSXZ')=0 
          or ( nvl(a.QHDDY0,0)<(select  to_number(NVL(max(trim(value0)),'0')) from XT_XTCS00 where NAME00='ZZQH_QHCSXZ')) )
/
show error;
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt 排号打印日志表视图 VW_YF_PHDYRZ
create or replace view VW_YF_PHDYRZ(
  ID0000,
  XTMC00,
  CFLSH0,
  BRXM00,
  CZY000,
  CZRQ00,
  CZSJ00,
  PHDYCS,
  IP0000
)
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- daihq     2014.11.06    create for YFPY-20141023-001
select
  ID0000,
  XTMC00,
  CFLSH0,
  BRXM00,
  CZY000,
  CZRQ00,
  CZSJ00,
  PHDYCS-1,
  IP0000
  from YF_PHDYRZ
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

prompt 创建视图 处方处方条型码打印明细记录 VW_PY_CFPYTXM
create or replace view VW_PY_CFPYTXM(
       BRXM00,--姓名
       XB0000,--性别
       BRNL00,
       ZB0000,--组别
       CFID00,--处方id
       YPNM00,--药品内码
       CFLSH0,--处方号
       YPMC00,--药品品名
       YPGG00,--药品规格
       PCMC00,--频次
       YPZSL0,--药品总数量
       CFDW00,--处方单位
       CSL000,--次数量
       JLDW00,-- 次数量单位
       YYSJ00,--用药时间
       YYSJDW,--用药时间单位
       YPYFMC,--药品用法
       YSGZH0,--医生工号
       YSXM00,--医生姓名
       GHH000,--挂号号
       GHID00, --挂号id
       SRRQ00,--输入日期
       SRSJ00,--输入时间
       BRID00,--病人id
       CSRQ00, --出生日期
       GHKS00,--挂号科室    
       PXH000,   
       YYMC00, --医院名称
       CZSL00,  --次数量
       PSMC00,
       ZYCFDW
)
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- dsm         2016.01.12  create for YF-20151219-001
-- huangjy     2018.08.03  modify by YFPY-20160401-001
-- ----------  ----------  -------------------------------------------------
select
       b.BRXM00,--姓名
       b.XB0000,--性别
       substr(SF_BQ_CSRQTONL(b.CSRQ00),1,10) as BRNL00,
       a.ZB0000,--组别
       a.CFID00,--处方id
       a.YPNM00,--药品内码
       b.CFLSH0,--处方号
       substr(a.YPMC00,1,15) as YPMC00,
       --a.YPMC00,--药品品名
       a.YPGG00,--药品规格
       --a.PCMC00,
       (select distinct SM0000 from BM_PC0000 where PCMC00=a.PCMC00 and YXBZ00='Y') PCMC00,
       a.YPZSL0,
       a.CFDW00,
       a.CSL000,--次数量
       a.JLDW00,-- 次数量单位
       a.YYSJ00,--用药时间
       a.YYSJDW,--用药时间单位
       a.YPYFMC,--药品用法
       b.YSGZH0,--医生工号
       b.YSXM00,--医生姓名
       b.GHH000,--挂号号
       b.GHID00, --挂号id
       b.SRRQ00,--输入日期
       b.SRSJ00,--输入时间
       b.BRID00,--病人id
       b.CSRQ00, --出生日期
       b.GHKS00,--挂号科室
       CFID00 PXH000,
       (select YYJC00 from XT_YYXX00 where rownum=1) YYMC00,
       (select round(a.CSL000*F2J000/Z2J000,3) from BM_YD0000 where YPNM00=a.YPNM00) CZSL00,
       (select PSMC00 from BM_PS0000 where PSID00=a.PSLBID ) PSMC00,
       (select ZYFYDW from BM_YD0000 where YPNM00=a.YPNM00 ) ZYFYDW          
 from YF_MZCF00 b,YF_MZCFMX a where a.CFLSH0=b.CFLSH0 and b.CFZT00 in ('0','1','2','4') --AND a.CXBZ00='Z' and b.SRRQ00=to_char(sysdate,'YYYYMMDD')
 /
 --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt 创建视图 处方处方条型码打印明细记录 VW_PY_CFPYTXM_HZ
create or replace view VW_PY_CFPYTXM_HZ(
       BRXM00,--姓名
       XB0000,--性别
       BRNL00,--年龄
       CFLSH0,--处方号
       YSXM00,--医生姓名
       GHH000,--挂号号
       GHID00, --挂号id
       SRRQ00,--输入日期
       SRSJ00,--输入时间
       BRID00,--病人id
       CSRQ00, --出生日期
       GHKS00,--挂号科室       
       KSMC00,--科室名称
       ZDMC00,--诊断名称
       PYRXM0,--配药人姓名
       YPZJE0,--药品总金额
       FYRXM0,--发药人姓名
       YPPAGE,--药品数
       YYMC00, --医院名称
       CKBH00,
       CKMC00,
       ZJE000,
       FYDLXH
)
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- dsm         2016.01.12  create for YF-20151219-001
-- huangjy     2018.08.03  modify by YFPY-20160401-001

select
       a.BRXM00,--姓名
       a.XB0000,--性别
       substr(SF_BQ_CSRQTONL(a.CSRQ00),1,10) BRNL00,--年龄
       a.CFLSH0,--处方号
       a.YSXM00,--医生姓名
       a.GHH000,--挂号号
       a.GHID00, --挂号id
       a.SRRQ00,--输入日期
       a.SRSJ00,--输入时间
       a.BRID00,--病人id
       a.CSRQ00, --出生日期
       a.GHKS00,--挂号科室
       (select BMMC00 from BM_BMBM00 where BMBH00=a.GHKS00) KSMC00,--科室名称
       (select ZDMC00 from YS_BRZDXX where GHID00=a.GHID00 and ZZDBZ0='3' and rownum=1) ZDMC00,--诊断名称
       (select ZWXM00 from BM_YGBM00 where YGBH00=a.PFR000) PYRXM0,--配药人姓名
       a.CFZJE0 YPZJE0,--药品总金额
       (select ZWXM00 from BM_YGBM00 where YGBH00=a.FYR000) FYRXM0,--发药人姓名
       (select count(*) from YF_MZCFMX where CFLSH0=a.CFLSH0) YPPAGE,
       (select YYJC00 from XT_YYXX00 where rownum=1)  YYMC00, --医院名称
       (select FYDLXH from YF_CFDL00 where CFLSH0=a.CFLSH0) CKBH00,
      (select w.CKMC00 from YF_CFDL00 q,BM_CKBM00 w where q.CFLSH0=a.CFLSH0 and q.FYCKBH=w.CKBH00) CKMC00,
       CFZJE0 ZJE000,
       (select  FYDLXH  from YF_CFDL00 where CFLSH0 =a.CFLSH0) FYDLXH
       from YF_MZCF00 a
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

prompt 创建视图 处方处方条型码打印明细记录 VW_PY_MZCFDY_SEYY
create or replace view VW_PY_MZCFDY_SEYY
( cflsh0, --处方流水号
  cfid00, --处方ID
  cfxh00, --处方序号
  cfmx00, --处方明细
  btxx00, --表头信息
  je0000, --金额
  xmyfmc, --项目用法名称
  ypmc00  --药品名称
)
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- huangjy     2018.01.18  create for YFPY-20180111-001   
  /*药房名称*/
   select a.CFLSH0,-1,3,(select BMMC00 from BM_BMBM00 where BMBH00=a.YFBMBH),'',0,'',''  from YF_MZCF00 a
 union all/*医院名称*/
   select CFLSH0,-2,3,sf_xt_yymc00(YFBMBH),'',0,'','' from YF_MZCF00
 union all /*科别  费别*/
   select a.CFLSH0,-3,3,rpad('科别:'||(select trim(b.BMMC00) from BM_BMBM00 b where BMBH00=a.GHKS00),24,' ')||
   rpad('费别:'||(select Trim(d.YBMC00) from IC_YBBRLB d where  F.YBLB00=d.YBLB00 and F.FBBH00=d.FBBH00),24,' ')||
   rpad('药房:'||(select Trim(e.BMMC00) from BM_BMBM00 e where  a.YFBMBH=e.BMBH00),32,' '),'',0,'',''
   from YF_MZCF00 a,BM_BRXXB0 f where a.BRID00=f.BRID00
 union all/*卡号*/
   select a.CFLSH0,-4,3,rpad('卡号:'||Trim(b.KMH000),25,' '),'',0,'',''
   from YF_MZCF00 a,IC_YHXX00 b where a.BRID00=b.BRID00  and b.ZT0000='1'
 union all/*姓名*/
   select a.CFLSH0,-5,3,rpad('姓名:'||Trim(a.BRXM00),25,' '),'',0,'',''   from YF_MZCF00 a
 union all/*性别*/
   select a.CFLSH0,-6,3,rpad('性别:'||Trim(a.XB0000),23,' '),'',0,'','' from YF_MZCF00 a
 union all/* 年龄*/
   select a.CFLSH0,-7,3,rpad('年龄:'||decode(SF_YF_GETCFDGS0(a.CFLSH0,'0'),'2',Trim(SF_YF_CSRQTONYR(a.CSRQ00,a.SRRQ00)),'3',Trim(SF_YF_CSRQTONYR   (a.CSRQ00,a.SRRQ00)),Trim(SF_YF_CSRQTONL(CSRQ00,SRRQ00))),20,' '),'',0,'','' from YF_MZCF00 a
 union all /*字段标题*/
   select a.CFLSH0,-8,3,' 药 名                数量                 金额','',0,'','' from YF_MZCF00 a
 union all/*合  计*/
   select a.CFLSH0,-9,3,rpad('药费合计:'||Trim(to_char(ROUND(a.CFZJE0,2),'999990.00'))||'元',20,' '),'',0,'','' from YF_MZCF00 a
 union all/*日  期*/
   select a.CFLSH0,-10,3,rpad('日    期:'||Trim(to_char(sysdate,'YYYY/MM/DD')),28,' '),'',0,'','' from YF_MZCF00 a
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt 创建视图 处方处方条型码打印明细记录 VW_PY_MZCFMXDY_SEYY
create or replace view VW_PY_MZCFMXDY_SEYY
( cflsh0,--处方流水号
  cfid00,--处方ID 
  ypmc00,--药品名称 
  je0000,--金额 
  ypzsl0,--总数
  YPDLBH --药品类别
)
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- huangjy     2018.01.18  create for YFPY-20180111-001   
-- huangjy     2019.06.05  增加ypdlbh for YFPY-20190605-001
select 
  x.CFLSH0 cflsh0,
  x.CFID00 cfid00,
  x.YPMC00 ypmc00,
  to_char(trim(round(YPZSL0*LSDJ00,2)),'999990.99') je0000,
  lpad((decode(substr(x.YPZSL0,1,1),'.','0','')||x.YPZSL0||trim(x.CFDW00)),7,' ') ypzsl0,
  x.YPDLBH YPDLBH
from YF_MZCFMX X,BM_YD0000 y where x.YPNM00=y.YPNM00 and X.SFZBY0='N'
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt 创建视图 中药处方处方条型码打印明细记录 VW_PY_MZCFDY_SEYY_ZY
create or replace view VW_PY_MZCFDY_SEYY_ZY
( cflsh0, --处方流水号
  cfid00, --处方ID
  cfxh00, --处方序号
  cfmx00, --处方明细
  btxx00, --表头信息
  je0000, --金额
  xmyfmc, --项目用法名称
  ypmc00  --药品名称
)
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- dsm        2018.03.07  create for YFPY-20180228-002  
  /*药房名称*/
   select a.CFLSH0,-1,3,(select BMMC00 from BM_BMBM00 where BMBH00=a.YFBMBH),'',0,'',''  from YF_MZCF00 a
 union all/*医院名称*/
   select CFLSH0,-2,3,sf_xt_yymc00(YFBMBH),'',0,'','' from YF_MZCF00
 union all /*科别  费别*/
   select a.CFLSH0,-3,3,rpad('科别:'||(select trim(b.BMMC00) from BM_BMBM00 b where BMBH00=a.GHKS00),24,' ')||
   rpad('费别:'||(select Trim(d.YBMC00) from IC_YBBRLB d where  F.YBLB00=d.YBLB00 and F.FBBH00=d.FBBH00),24,' ')||
   rpad('药房:'||(select Trim(e.BMMC00) from BM_BMBM00 e where  a.YFBMBH=e.BMBH00),32,' '),'',0,'',''
   from YF_MZCF00 a,BM_BRXXB0 f where a.BRID00=f.BRID00
 union all/*卡号*/
   select a.CFLSH0,-4,3,rpad('卡号:'||Trim(b.KMH000),25,' '),'',0,'',''
   from YF_MZCF00 a,IC_YHXX00 b where a.BRID00=b.BRID00  and b.ZT0000='1'
 union all/*姓名*/
   select a.CFLSH0,-5,3,rpad('姓名:'||Trim(a.BRXM00),25,' '),'',0,'',''   from YF_MZCF00 a
 union all/*性别*/
   select a.CFLSH0,-6,3,rpad('性别:'||Trim(a.XB0000),23,' '),'',0,'','' from YF_MZCF00 a
 union all/* 年龄*/
   select a.CFLSH0,-7,3,rpad('年龄:'||decode(SF_YF_GETCFDGS0(a.CFLSH0,'0'),'2',Trim(SF_YF_CSRQTONYR(a.CSRQ00,a.SRRQ00)),'3',Trim(SF_YF_CSRQTONYR   (a.CSRQ00,a.SRRQ00)),Trim(SF_YF_CSRQTONL(CSRQ00,SRRQ00))),20,' '),'',0,'','' from YF_MZCF00 a
 union all /*字段标题*/
   select a.CFLSH0,-8,3,' 药 名                数量                 金额','',0,'','' from YF_MZCF00 a
 union all/*合  计*/
   select a.CFLSH0,-9,3,rpad('药费合计:'||Trim(to_char(ROUND(a.CFZJE0,2),'999990.00'))||'元',20,' '),'',0,'','' from YF_MZCF00 a
 union all/*日  期*/
   select a.CFLSH0,-10,3,rpad('日    期:'||Trim(to_char(sysdate,'YYYY/MM/DD')),28,' '),'',0,'','' from YF_MZCF00 a
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
prompt 创建视图 中药处方处方条型码打印明细记录 VW_PY_MZCFMXDY_SEYY_ZY
create or replace view VW_PY_MZCFMXDY_SEYY_ZY
( cflsh0,--处方流水号
  cfid00,--处方ID 
  ypmc00,--药品名称 
  je0000,--金额 
  ypzsl0,--总数
  YPDLBH --药品类别
)
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- dsm        2018.03.07  create for YFPY-20180228-002   
-- huangjy    2019.06.05  增加ypdlbh for YFPY-20190605-001
select 
  x.CFLSH0 cflsh0,
  x.CFID00 cfid00,
  x.YPMC00 ypmc00,
  to_char(trim(round(YPZSL0*LSDJ00,2)),'999990.99') je0000,
  lpad((decode(substr(x.YPZSL0,1,1),'.','0','')||x.YPZSL0||trim(x.CFDW00)),7,' ') ypzsl0,
  x.YPDLBH YPDLBH
from YF_MZCFMX X,BM_YD0000 y where x.YPNM00=y.YPNM00 and X.SFZBY0='N'
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


prompt 收费窗口与药房配药窗口的对应视图  VW_SF_SFPYDY 
create or replace view VW_SF_SFPYDY 
( sfbmbh,sfckbh,sfckmc,yfbmbh,pyckbh,pyckmc,zt0000,ztmc00,ckxxdz,dqxqbz,dqxqmc,yyid00,flag01)
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- huangjy     2018.08.06  create for YF-20180728-002
-- huangjy     2018.08.21   更新 by YFPY-20180821-001
select a.BMBH00 sfbmbh,
       a.CKBH00 sfckbh,
       a.CKMC00 sfckmc,
       b.BMBH00 yfbmbh,
       b.CKBH00 pyckbh,
       b.CKMC00 pyckmc,
       b.ZT0000,
       decode(b.ZT0000, '0', '工作中', '1', '暂停工作', '2', '停止工作') ztmc00,
       b.CKXXDZ,
       (select DQXQBZ from BM_YDCKDY where CKBH00 = c.DCKBH0) dqxqbz,
       (select decode(DQXQBZ, '1', '东区', '2', '西区', '0', '全区')
          from BM_YDCKDY
         where CKBH00 = c.DCKBH0) dqxqmc,
       d.YYID00,
       (select FLAG00 from BM_YDCKDY where CKBH00 = c.DCKBH0) flag01
  from BM_CKBM00 a, BM_CKBM00 b, BM_CKDY00 c, BM_BMBM00 d
 where a.CKBH00 = c.SCKBH0
   and b.CKBH00 = c.DCKBH0
   and a.FLAG00 = '1'
   and b.FLAG00 = '2'
   and b.BMBH00 = d.BMBH00
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

prompt 摆药处方信息 VW_YF_MZCF00_BYJ
create or replace  view VW_YF_MZCF00_BYJ (
   PRESC_DATE,PRESC_NO,DISPENSARY,PATIENT_ID,PATIENT_NAME,INVOICE_NO,PATIENT_TYPE,DATE_OF_BIRTH,SEX,
   PRESC_IDENTITY,CHARGE_TYPE,PRESC_ATTR,PRESC_INFO,RCPT_INFO,RCPT_REMARK,REPETITION,COSTS,PAYMENTS,
   ORDERED_BY,ORDERED_BY_NAME,PRESCRIBED_BY,ENTERED_BY,DISPENSE_PRI,DJH000,GHH000,CFLSH0,BCCFH0,HBH000,DQXQMC
 )
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- huangjy     2018.08.06  create for YF-20180728-002
-- huangjy     2018.08.21   更新 by YFPY-20180821-001
select 
to_char(to_date(A.SRRQ00,'YYYYMMDD'),'YYYY-MM-DD') PRESC_DATE,
A.CFLSH0 PRESC_NO,
A.YFBMBH DISPENSARY,
--B.BRBLH0 PATIENT_ID,
B.BRID00 PATIENT_ID,
A.BRXM00 PATIENT_NAME,
0 INVOICE_NO,
'00' PATIENT_TYPE,
to_char(to_date(A.CSRQ00,'YYYYMMDD'),'YYYY-MM-DD') DATE_OF_BIRTH,
A.XB0000 SEX,
' ' PRESC_IDENTITY,
' ' CHARGE_TYPE,
' ' PRESC_ATTR,
' ' PRESC_INFO,
' ' RCPT_INFO,
' ' RCPT_REMARK,
ZYTS00 REPETITION,
A.CFZJE0 COSTS,
A.CFZJE0 PAYMENTS,
A.GHKS00 ORDERED_BY,
(select BMMC00 from BM_BMBM00 where BMBH00=A.GHKS00 and rownum=1) ORDERED_BY_NAME,
YSXM00 PRESCRIBED_BY,
YSXM00 ENTERED_BY,
'1' DISPENSE_PRI,  
--decode(b.GHKS00,'186','2',decode((select nvl(c.SFJZLB,'0') from BM_GHLBB0 c where b.GHLB00=c.LBBH00 and rownum=1),'1','2','1')) DISPENSE_PRI,  --186急诊科和--挂急诊号反馈值为2
A.DJH000,
A.GHH000,
A.CFLSH0,
A.BCCFH0,
(select HBH000 from SF_CFBYXX where CFLSH0=A.CFLSH0 and rownum=1) HBH000,
(select F.DQXQMC from SF_CFBYXX e,VW_SF_SFPYDY f where e.CFLSH0=A.CFLSH0 and nvl(e.SFCKBH,0)>0 and e.SFCKBH=f.SFCKBH and rownum=1) DQXQMC
from YF_MZCF00 A,SF_BRXXB0 B where A.GHH000=B.GHH000 and A.CFZT00='1'
 and A.SFKSLY='N' and A.YPDLBH in ('0','1') and nvl(A.DJH000,0)>0
 and trim(a.PFR000) is null
 and not exists(select 1 from YF_CFDL00 where CFLSH0=A.CFLSH0)
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%



prompt 摆药处方明细 VW_YF_MZCFMX_BYJ 201
create or replace view VW_YF_MZCFMX_BYJ   --201
(
PRESC_NO, ITEM_NO, ADVICE_CODE, DRUG_CODE, DRUG_SPEC, DRUG_NAME, FIRM_ID, 
FIRM_NAME, PACKAGE_SPEC, PACKAGE_UNITS, QUANTITY, UNIT, COSTS, PAYMENTS, 
DOSAGE, DOSAGE_UNITS, ADMINISTRATION, FREQUENCY, ADDITIONUSAGE, RCPT_REMARK, 
CFLSH0
)
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- huangjy     2018.08.06  create for YF-20180728-002
-- huangjy     2018.08.21   更新 by YFPY-20180821-001
select
A.CFLSH0 PRESC_NO,
1 ITEM_NO,
A.ZB0000 ADVICE_CODE,
A.YPNM00 DRUG_CODE,
A.YPGG00 DRUG_SPEC,
A.YPMC00 DRUG_NAME,
' ' FIRM_ID,
' ' FIRM_NAME,
' ' PACKAGE_SPEC,
' ' PACKAGE_UNITS,
--(A.YPZSL0*A.ZHL000)/nvl(B.YYZHL0,B.M2J000) QUANTITY,
--(A.YPZSL0*A.ZHL000)/b.Z2J000 QUANTITY,
A.YPZSL0 QUANTITY,
--B.YYDW00 UNIT,
--a.JLDW00 UNIT,
a.CFDW00 UNIT,
A.ZFJE00 COSTS,
A.ZFJE00 PAYMENTS,
A.CSL000 DOSAGE,
A.JLDW00 DOSAGE_UNITS,
(select SM0000 from BM_PC0000 where trim(PCMC00)=trim(a.PCMC00) and rownum=1)||
' 每次'||TRIM(nvl(a.YFSMMC,' '))||TRIM(nvl(a.YPYFMC,' '))||
--Trim(DECODE(a.CSL000,NULL,'',0,'',DECODE(SUBSTR(ABS(a.CSL000),1,1),'.','0','')||ABS(a.CSL000)||trim(a.JLDW00)))
 to_char(round(((a.CSL000/b.YYZHL0)),2),'fm9999990.99999')||trim(b.YYDW00) ADMINISTRATION,
A.PCMC00 FREQUENCY,
A.XMYFMC ADDITIONUSAGE,
a.YYSJ00||a.YYSJDW||' '
--||A.BZ0000||' '
||(select c.ZDMC00 from YS_BRZDXX c,YF_MZCF00 d where c.GHH000=d.GHH000 and d.CFLSH0=a.CFLSH0 and c.ZZDBZ0='3' and rownum=1 ) RCPT_REMARK,
A.CFLSH0
from YF_MZCFMX A,BM_YD0000 B
where
A.CXBZ00='Z' and A.SFZBY0='N' and A.YPNM00=B.YPNM00
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


prompt 发送退费处方信息 VW_YF_MZCF00_BYTF 205
create or replace view VW_YF_MZCF00_BYTF(
  PRESC_DATE,PRESC_NO,INVOICE_NO,DISPENSARY,CFLSH0,GHH000,JZDH00
)
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- huangjy     2018.08.06  create for YF-20180728-002
-- huangjy     2018.08.21   更新 by YFPY-20180821-001
select  
to_char(to_date(A.SRRQ00,'YYYYMMDD'),'YYYY-MM-DD')||' '||A.SRSJ00 PRESC_DATE,
A.CFLSH0 PRESC_NO,
' ' INVOICE_NO,
A.YFBMBH DISPENSARY,
A.CFLSH0,
A.GHH000,
C.JZDH00
from YF_MZCF00 A,SF_BRFY00 B,SF_JZB000 C
where 
--A.SFKSLY='N' and A.YPDLBH in ('0','1') and A.CFZT00='6' and A.CFZJE0>0 
  A.SFKSLY='N' and A.YPDLBH in ('0','1') and A.CFZT00='2' and A.CFZJE0>0 
  and A.DJH000=B.DJH000 and C.JZDH00=B.JZDH00 and A.BCCFH0 is null
  and not exists(select 1 from SF_CFBYXX where CFLSH0=A.CFLSH0 and TFFSZT='1')
  and exists (select 1 from YF_MZCF00 where BCCFH0=a.CFLSH0 and CFZT00='6' )
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

prompt 网络处方 VW_PY_WLCFXX
create or replace view VW_PY_WLCFXX
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- huangjy     2019.08.12  网络处方 create for YFPY-20190812-001
as
select C.CFLSH0,C.GHH000,c.GHID00,c.BRID00,C.BRXM00,C.XB0000,C.CFXZ00,
   C.CSRQ00,C.YSXM00,M.BMMC00 as GHKS00,C.ZYTS00,c.YFBMBH,
   C.SRRQ00,C.JZRQ00,C.CFZJE0,C.CFZT00,C.YPDLBH,
   decode(C.THBZ00,'0',D.ZT0000,'4') ZT0000,D.FYCKBH,
   (select CKMC00 from BM_CKBM00 where CKBH00=d.FYCKBH) FYCKMC,D.PYCKBH,
   (select CKMC00 from BM_CKBM00 where CKBH00=d.PYCKBH) PYCKMC,
   D.DLXH00,D.FYDLXH,D.DYRQ00,D.DYSJ00,D.PYRQ00,D.PYSJ00,
   C.DJH000,C.FYRQ00,C.FYSJ00,G.ZWXM00 as LRY000,C.ZYDJS0,
   C.BCCFH0||trim(C.BZ0000) as BZ0000,H.ZWXM00 as FYR000,c.THBZ00,
   (select CXBZ00 from YF_MZCFMX where CFLSH0=c.CFLSH0 and rownum=1) CXBZ00,
   (select CZYXM0  from SF_BRFY00 X where X.DJH000=C.DJH000) JZR000,'1' CFDLB0
from YF_MZCF00 C,BM_BMBM00 M,BM_YGBM00 G,BM_YGBM00 H,YF_CFDL01 D
where 1=1 and C.GHKS00=M.BMBH00
   and D.CFLSH0=c.CFLSH0
   --and c.CFZT00 in ('1','0')
   and exists (select 1 from SF_BRXXB0 where GHID00=c.GHID00 and SJLY00='2')
   and C.CZY000=G.YGBH00
   and C.FYR000=H.YGBH00(+)
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%  
prompt 历史处方队列信息 VW_PY_LSCFDLXX
create or replace view VW_PY_LSCFDLXX
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- huangjy     2019.09.23  create for YFPY-20190919-001
as
select C.CFLSH0,C.GHH000,c.GHID00,c.BRID00,C.BRXM00,C.XB0000,C.CFXZ00,
   C.CSRQ00,C.YSXM00,M.BMMC00 as GHKS00,C.ZYTS00,c.YFBMBH,
   C.SRRQ00,C.JZRQ00,C.CFZJE0,C.CFZT00,C.YPDLBH,
   decode(C.THBZ00,'0',D.ZT0000,'4') ZT0000,D.FYCKBH,
   (select CKMC00 from BM_CKBM00 where CKBH00=d.FYCKBH) FYCKMC,D.PYCKBH,
   (select CKMC00 from BM_CKBM00 where CKBH00=d.PYCKBH) PYCKMC,
   D.DLXH00,D.FYDLXH,D.DYRQ00,D.DYSJ00,D.PYRQ00,D.PYSJ00,
   C.DJH000,C.FYRQ00,C.FYSJ00,G.ZWXM00 as LRY000,C.ZYDJS0,
   C.BCCFH0||trim(C.BZ0000) as BZ0000,H.ZWXM00 as FYR000,c.THBZ00,
   (select CXBZ00 from YF_MZCFMX where CFLSH0=c.CFLSH0 and rownum=1) CXBZ00,
   (select CZYXM0  from SF_BRFY00 X where X.DJH000=C.DJH000) JZR000,'1' CFDLB0
from YF_MZCF00 C,BM_BMBM00 M,BM_YGBM00 G,BM_YGBM00 H,YF_CFDL01 D
where 1=1 and C.GHKS00=M.BMBH00
   and D.CFLSH0=c.CFLSH0
   and C.CZY000=G.YGBH00
   and C.FYR000=H.YGBH00(+)
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%