CREATE OR REPLACE VIEW vw_bq_ybyp00_dzbl (
   ypnm00,
   ypbm00,
   pysm00,
   wbsm00,
   ypmc00,
   ydypmc,
   ypgg00,
   lbbh00,
   lsjxx0,
   sjkcxx,
   yfbmbh,
   fbbh00,
   ybzxlb,
   zfbl00,
   yblb00,
   yblbmc,
   ybbrlb,
   colour,
   cfjb00,
   gzdj00,
   mzdj00,
   dpdj00,
   sjdj00,
   ypsysm,
   bz0000,
   bqkdyf,
   ypmc02,
   ypmclb,
   kssjb0,
   nblbmc,
   nbzfbl,
   dxsybz,
   ypczfl,
   lctxxx,
   ykkcsl,
   ypxzmc )
AS
select    a.YPNM00,
                a.YPBM00,
                a.PYSM00,
                a.WBSM00,
                a.YPMC00,
                a.YPMC00,--药品名         
                a.YPGG00,
                a.LBBH00,
                --DECODE(a.MZDJ00||a.DPDJ00||a.SJDJ00,'000','0','1'),
                decode(substr(to_char(b.LSDJ00/(a.K2J000*a.NSBL00)*a.Z2J000),1,1),'.','0','')
                    ||to_char(round(b.LSDJ00/(a.K2J000*a.NSBL00)*a.Z2J000,4))||'/'||a.ZYFYDW,
                --round(b.SJKCSL/a.Z2J000,4)||a.ZYFYDW,
                   case when ((select Trim(value0) from xt_xtcs00 where name00='XT_SFXSKC')='N') then 
                '/'
                when ((select Trim(value0) from xt_xtcs00 where name00='XT_SFXSKC')='M'and nvl(LCTXXX,0)< round(YKKCSL/ZHL000,4)) then 
                '充足'
                else
                 round(b.YKKCSL/a.Z2J000*a.NSBL00,4)||a.ZYFYDW
                end SJKCXX,
                b.YFBMBH,
                C.FBBH00,   --费别编号
                c.YBZXLB,   --医保类别
                to_char(c.ZFBL00*100)||'%',   --自付比率
                c.YBLB00,   --医保类别
                decode(c.YBLB00,'4','甲类','5','乙类','6','民族','7','非保','8','医保','自付'),
                c.YBBRLB,
                DECODE(C.YBZXLB,'0','0',DECODE(C.ZFBL00,1,'$0000FF',(decode(c.bz0000,'','','16711935'))))COLOUR,
                a.CFJB00,
                b.GZDJ00,
                 a.MZDJ00,
                a.DPDJ00,
                a.SJDJ00,
                decode(c.BZ0000,null,'','医保提示:'||c.BZ0000||chr(13))||a.ypsysm ypsysm,
                c.bz0000,
                b.BQKDYF,
                a.yptym0 ypmc02,
                a.YPMCLB,  -- 0: 通用名 1：商品名 2： 别名
                a.KSSJB0,
--                decode(H.YBLB00,'4','甲类','5','乙类','6','民族','7','自付','8','医保') NBLBMC,  --农保类别名称
--                H.ZFBL00 NBZFBL      --农保自付比例                                                
                decode(c.NBLB00,'4','甲类','5','乙类','6','民族','7','非保','8','医保','自付') NBLBMC,  --农保类别名称
                to_char(c.NBZFBL*100)||'%' NBZFBL ,     --农保自付比例  
                    decode(dxsybz,0,
          decode(
          (select 1 from BM_YDTSSX m, BM_YPSX00 n WHERE m.YPNM00 = a.YPNM00  and m.SXBH00 = n.SXBH00 and n.BZ0000 like '%溶酶%'  and rownum <= 1), 
          null, 0,1), 1) DXSYBZ, --大输液标志 
                a.ypczfl,
                b.LCTXXX, --临床提醒下线
                  b.YKKCSL,
             decode(a.ypxz00,'1','基本用药 ','2','临时用处方药','3','临时用非处方药','4','非基本药物') YPXZMC  -- 药品性质名称
      from BM_YD0000 a,   --药典表
--           BM_YBSFDY H, 
           YF_YPKCXX b,   --药房药品库存信息表
           VW_BM_YBSFDY_ZYDZBL c    --医保收费对应表
      where b.YPNM00 = a.YPNM00
      and c.SFXMID = a.YPNM00
      and c.SFXMID = b.YPNM00
      and c.SFYP00 = 'Y'  --是否药品  Y:药品 N:医疗项目
      and c.YBZXLB <> '0' --医保类别'0'表示非医保
      --and a.TZRQ00 is NULL
      --and (a.MZDJ00 = '0' and a.DPDJ00 = '0' and a.SJDJ00 = '0')
      and b.PDXH00 <> '0' and nvl(b.ZTBZ00,'0')='0' and c.YXBZ00='1'
--      and a.YPNM00=H.SFXMID(+) and H.SFYP00='Y' 
--      and to_char(H.FBBH00)||','||H.YBZXLB=nvl((select substr(value0,1,instr(trim(value0),',',1,2)-1) from XT_XTCS00 where name00='ZS_KJYZSLRXXSFXSNBMLXX' and rownum=1),'1,0')
      UNION ALL
      select    a.YPNM00,
                a.YPBM00,
                d.PYSM00,
                d.WBSM00,
                d.BMMC00,--Trim(d.BMMC00)||'('||Trim(a.YPMC00)||')' BMMC00,          
                a.YPMC00,--药品通用名      
                a.YPGG00,
                a.LBBH00,
                --DECODE(a.MZDJ00||a.DPDJ00||a.SJDJ00,'000','0','1'),
                decode(substr(to_char(b.LSDJ00/(a.K2J000*a.NSBL00)*a.Z2J000),1,1),'.','0','')
                    ||to_char(round(b.LSDJ00/(a.K2J000*a.NSBL00)*a.Z2J000,4))||'/'||a.ZYFYDW,
                --round(b.SJKCSL/a.Z2J000,4)||a.ZYFYDW,
                   case when ((select Trim(value0) from xt_xtcs00 where name00='XT_SFXSKC')='N') then 
                '/'
                when ((select Trim(value0) from xt_xtcs00 where name00='XT_SFXSKC')='M'and nvl(LCTXXX,0)< round(YKKCSL/ZHL000,4)) then 
                '充足'
                else
                 round(b.YKKCSL/a.Z2J000*a.NSBL00,4)||a.ZYFYDW
                end SJKCXX,
                b.YFBMBH,
                C.FBBH00,   --费别编号
                c.YBZXLB,   --医保类别
                to_char(c.ZFBL00*100)||'%',   --自付比率
                c.YBLB00,   --医保类别
                decode(c.YBLB00,'4','甲类','5','乙类','6','民族','7','非保','8','医保','自付'),
                c.YBBRLB,
                DECODE(C.YBZXLB,'0','0',DECODE(C.ZFBL00,1,'$0000FF',(decode(c.bz0000,'','','16711935'))))COLOUR,
                a.CFJB00,
                b.GZDJ00,
                a.MZDJ00,
                a.DPDJ00,
                a.SJDJ00,
                decode(c.BZ0000,null,'','医保提示:'||c.BZ0000||chr(13))||a.ypsysm ypsysm,
                c.bz0000,
                b.BQKDYF,                
                DECODE(d.ypmclb,'0',a.ypmc00,'1',a.ypmc00,'2',decode(a.ypmclb,'0',a.yptym0,'1',a.ypmc00)) ypmc02,                 
                d.YPMCLB,  --'0'商品名'1'通用名'2'别名
                a.KSSJB0,
--                decode(H.YBLB00,'4','甲类','5','乙类','6','民族','7','自付','8','医保') NBLBMC,  --农保类别名称
--                H.ZFBL00 NBZFBL      --农保自付比例                                                                
                decode(c.NBLB00,'4','甲类','5','乙类','6','民族','7','非保','8','医保','自付') NBLBMC,  --农保类别名称
                to_char(c.NBZFBL*100)||'%' NBZFBL ,     --农保自付比例  
                     decode(DXSYBZ,0,
          decode(
          (select 1 from BM_YDTSSX m, BM_YPSX00 n WHERE m.YPNM00 = a.YPNM00  and m.SXBH00 = n.SXBH00 and n.BZ0000 like '%溶酶%'  and rownum <= 1), 
          null, 0,1), 1) DXSYBZ, --大输液标志 
                 a.ypczfl,
                 b.LCTXXX, --临床提醒下线
                 b.YKKCSL,
               decode(a.ypxz00,'1','基本用药 ','2','临时用处方药','3','临时用非处方药','4','非基本药物') YPXZMC  -- 药品性质名称
      from BM_YD0000 a,    --药典表
--           BM_YBSFDY H, 
           YF_YPKCXX b,    --药房药品库存信息表
           VW_BM_YBSFDY_ZYDZBL c,    --医保收费对应表
           BM_YPBM00 d     --药典表和药品别名表
      where b.YPNM00 = a.YPNM00
      and c.SFXMID = a.YPNM00
      and d.YPNM00 = a.YPNM00
      and c.SFXMID = b.YPNM00
      and d.YPNM00 = b.YPNM00
      and d.YPNM00 = c.SFXMID
      and c.SFYP00 = 'Y'  --是否药品  Y:药品 N:医疗项目
      and c.YBZXLB <> '0' --医保类别'0'表示非医保
      --and a.TZRQ00 is NULL
      --and a.MZDJ00 = '0' and a.DPDJ00 = '0' and a.SJDJ00 = '0'
      and b.PDXH00 <> '0' and nvl(b.ZTBZ00,'0')='0' and c.YXBZ00='1'
/


-- End of DDL Script for View SD_HOSPITAL.VW_BQ_YBYP00_DZBL