prompt 53.药品字典分类  VW_BM_YPZDFL
create or replace view SD_HOSPITAL.VW_BM_YPZDFL
-- MODIFICATION HISTORY
-- Person    Date          Comments
-- JXQ       2012.08.06    归档
-- JLG       2012.09.17    增加拼音首码PYSM00
-- JLG       2012.09.29    增加药品状态(0未启用1正常使用-1停用)
-- chenyw    2014.02.21    增加物资属性
-- liwm      2019.01.16    增加YKBMBH YK9-20190111-001
-- luowf     2020.06.10    增加药品状态(0未启用 1正常使用 -1、-2停用 2注册证过期)   KCWZ9-20200525-001
-- luowf     2020.11.04    处理ZCSXRQ异常数据     KCWZ9-20201104-001
-- luowf     2022.07.12    避免新增药品获取的LBBH00是substr(YXFLM0, 1, 1) 关联BM_YPYL00获取正确的LBBH00
as 
select 
  --substr(YXFLM0, 1, 1) LBBH00, --类别编号
  A.LBBH00,
  B.YXFLM0 FLBBH0, 
  B.TYMBH0 YPFLBH, 
  B.TYMMC0 YPFLMC, 
  -1 as YPNM00, 
  B.PYSM00,
  '1' as FLAG00, 
  1 as ZT0000,
  (select WZSX00 from bm_ypyl00 where b.yxflm0=YLBH00) WZSX00,
  0 as YKBMBH
from BM_YPTYM0 B,BM_YPYL00 A 
  where B.YXFLM0=A.YLBH00 and SFYX00= 'Y' 
union all
select
  C.LBBH00, --类别编号
  C.TYMBH0 FLBBH0,
  C.YPBM00,
  C.YPMC00,
  C.YPNM00,
  C.PYSM00,
  '2' as FLAG00,
  decode(QYRQ00,null,0,1) * decode(TZRQ00,null,1,-1) ZT0000,
  (select WZSX00 from BM_YPYL00 where YLBH00=c.YLBH00) WZSX00,
  C.YKBMBH as YKBMBH
from BM_YD0000 C
where c.lbbh00<>'9' 
union all
select 
  C.LBBH00, --类别编号
  C.TYMBH0 FLBBH0,
  C.YPBM00,
  C.YPMC00,
  C.YPNM00,
  C.PYSM00,
  '2' as FLAG00,
  --decode(QYRQ00,null,0,1) * decode(TZRQ00,null,1,-1) * (case when (to_char(sysdate,'YYYYMMDD')-D.ZCSXRQ)>0 then 2 else 1 end) ZT0000,   --0未启用，-1，-2 已停用,2注册证过期，1正常
  decode(QYRQ00,null,0,1) * decode(TZRQ00,null,1,-1) * (case when (sysdate-SF_XT_STRTODATEVALUE(D.ZCSXRQ,sysdate)-1)>0 then 2 else 1 end) ZT0000,--0未启用，-1，-2 已停用,2注册证过期，1正常
  (select WZSX00 from BM_YPYL00 where YLBH00=c.YLBH00) WZSX00,
  C.YKBMBH as YKBMBH
from BM_YD0000 C,BM_WZQTXX D
where C.YPNM00=D.YPNM00 and C.LBBH00='9'
/
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%