--------------------------------------
prompt 52.药品分类树    VW_BM_YPFLTREEVIEW
create or replace view VW_BM_YPFLTREEVIEW 
-- MODIFICATION HISTORY
-- Person    Date          Comments
-- JXQ       2012.08.06    归档
-- JLG       2012.09.17    增加拼音首码PYSM00
-- chenyw    2014.02.21    增加物资属性WZSX00
as
select 
  a.LBBH00, --类别编号 药品: 0:西药 1:成药 2:中药 3:材料 4:药械 9:物资
  a.SJYLBH FLBBH0, --上级药理编号
  a.YLBH00 YPFLBH, --药理编号
  a.YLMC00 YPFLMC, --药理名称 
  -1  YPNM00,
  a.PYSM00,
  '0' FLAG00 ,
  WZSX00
from BM_YPYL00 a where SFYX00 = '1';

----------------------------


create or replace view vw_bm_ybsfdy1
(sfxmid, sfyp00, fbbh00, ybzxlb, ybxmbh, ybzyfp, ybmzfp, xmje00, yyje00, zfbl00, zfje00, jmbl00, jmje00, sfybxm, ybbrlb, sfsx00, yxbz00, yblb00, bz0000, nblb00, nbzfbl, ybjsdj, zyzfbl, xmdw00, ybjsdj1, xmdw01, mzzybz, bkeh83)
as
select
       a.SFXMID,
       a.SFYP00,
       a.FBBH00,
       a.YBZXLB,
       a.YBXMBH,
       a.YBZYFP,
       a.YBMZFP,
       SF_GETXMJE(a.SFYP00,a.SFXMID,a.YYDJ00,0,'0','0'),
       SF_GETXMJE(a.SFYP00,a.SFXMID,'A',0,'0','0'),
       decode(replace(decode(a.YBBRLB,'Z',b.YBBRLB,a.YBBRLB),b.YBBRLB),a.YBBRLB,1,a.ZFBL00)*b.SFMLXZ,
       decode(replace(decode(a.YBBRLB,'Z',b.YBBRLB,a.YBBRLB),b.YBBRLB),a.YBBRLB,0,a.ZFJE00)*b.SFMLXZ,
       decode(replace(decode(a.YBBRLB,'Z',b.YBBRLB,a.YBBRLB),b.YBBRLB),a.YBBRLB,0,a.JMBL00)*b.SFMLXZ,
       decode(replace(decode(a.YBBRLB,'Z',b.YBBRLB,a.YBBRLB),b.YBBRLB),a.YBBRLB,0,a.JMJE00)*b.SFMLXZ,
       a.SFYBXM,
       --decode(b.SFMLXZ,0,'Y',decode(replace(a.YBBRLB,b.YBBRLB),a.YBBRLB,'N',a.SFYBXM)),
       b.YBBRLB,
       a.SFSX00,
       a.YXBZ00,
       decode(b.SFMLXZ,0,decode(a.SFYP00,'Y','8','0'),decode(replace(decode(a.YBBRLB,'Z',b.YBBRLB,a.YBBRLB),b.YBBRLB),a.YBBRLB,decode(a.SFYP00,'Y','7','2'),a.YBLB00)),
       a.bz0000,
       a.NBLB00,
       a.NBZFBL,
       (select YBJSDJ from BM_YBSFXM where XMBH00=a.YBXMBH and YBZXLB=a.YBZXLB and FBBH00=a.FBBH00 and rownum=1) YBJSDJ,
       decode(a.YBBRLB,'Z',a.ZYZFBL,SF_XT_ZFBL00(b.YBBRLB,a.YBBRLB,a.ZYZFBL)),
       (select XMDW00 from BM_YBSFXM where XMBH00=YBXMBH and YBZXLB=YBZXLB and rownum=1) XMDW00,
       (select YBJSDJ1 from BM_YBSFXM where XMBH00=YBXMBH and YBZXLB=YBZXLB and rownum=1) YBJSDJ1,
       (select XMDW01 from BM_YBSFXM where XMBH00=YBXMBH and YBZXLB=YBZXLB and rownum=1) XMDW01,
       nvl(a.MZZYBZ,'2') MZZYBZ,--门诊住院标志  0门诊，1住院，2门诊住院都可用
       a.BKEH83  --国家医保编码
from BM_YBSFDY a, BM_YBBRLB b,BM_BRFBB0 c
where a.YBZXLB=b.YBZXLB and a.FBBH00=b.FBBH00 and a.FBBH00=c.FBBH00 and a.YBBRLB<>'-' and
      exists (select 1 from IC_YBBRLB where YBLB00=YBZXLB and YBLB00=a.YBZXLB)
;


---------------------------------



create or replace view vw_zs_ybsfdy_dzbl
(sfxmid, sfyp00, fbbh00, ybzxlb, ybxmbh, ybzyfp, ybmzfp, xmje00, zfbl00, sczfje, zfje00, jmbl00, jmje00, sfybxm, ybbrlb, sfsx00, yxbz00, yblb00, yblbmc, bz0000, nblb00, nbzfbl, zyzfbl, ybjsdj, xmdw00, ybjsdj1, xmdw01)
as
select SFXMID, SFYP00, FBBH00, YBZXLB, YBXMBH, YBZYFP, YBMZFP, XMJE00, ZFBL00,
SCZFJE, ZFJE00, JMBL00, JMJE00, SFYBXM, YBBRLB, (select SFSX00 from BM_YBSFXM where YBZXLB=a.YBZXLB and FBBH00=a.FBBH00 and XMBH00=a.YBXMBH and rownum=1) SFSX00, YXBZ00, YBLB00,
        decode(YBLB00,'4','甲类','5','乙类','6','民族','7','自付','8','医保'),   --医保类别名称
 BZ0000, NBLB00, nvl(NBZFBL,1) NBZFBL,nvl(ZYZFBL,1) ZYZFBL, YBJSDJ,
        XMDW00,
       nvl(YBJSDJ1,1),
        XMDW01
        from VW_BM_YBSFDY a
;

----------------------