create or replace view VW_HLYY_YZMXCX
--Person    date         Comments
--huangzw   2020-11-13   合理用药接口获取药品医嘱信息视图   for ZYDZBL-20201111-006
--huangzw   2020-12-16   新增给药时机字段    for  ZYDZBL-20201215-004
as
select KDKSBH,
       (select BMMC00 from BM_BMBM00 where BMBH00 = A.KDKSBH) KDKSMC,
       KZYS00,
       B.ZYFYDW,
       (select decode(YSLB00, '5', '231', '4', '232', '3', '233', '234')
          from BM_YGBM00
         where YGBH00 = A.KZYS00) YSLB00,
       (select ZWXM00 from BM_YGBM00 where YGBH00 = A.KZYS00) KZYSMC,
       to_char(to_date(A.QYRQ00, 'yyyy-MM-dd'), 'yyyy-MM-dd') || ' ' ||
       A.QYSJ00 QYRQ00,
       to_char(to_date(A.TZRQ00 || A.TZSJ00, 'yyyy-MM-dd hh24:mi:ss'),
               'yyyy-MM-dd hh24:mi:ss') TZRQ00,
       B.MCSL00 MCSL00,
       B.JLDW00 JLDW00,
       round(D.LSDJ00 / (C.K2J000 * C.NSBL00) * C.Z2J000, 4) LSDJ00,
       A.YFBMBH,
       C.SCCJBH,
       (select SCCJZW
          from BM_YPSCCJ x
         where C.SCCJBH = x.SCCJBH
           and rownum = 1) SCCJZW,
       A.YFID00,
       case when instr(A.YPYFMC,'餐前')>0 then '餐前' 
       when  instr(A.YPYFMC,'餐前')>0 then '餐后'
       when instr(A.YPYFMC,'空腹')>0  then '空腹'
       when instr(A.YPYFMC,'外用')>0  then '外用'
       else A.YPYFMC end as GYSJ00,
       A.YPYFMC  YPYFMC,
       A.PCID00,
       A.PCMC00,
       B.YPGG00,
       (select JXMC00
          from BM_YPJX00 y
         where y.LBBH00 = c.LBBH00
           and y.JXBH00 = c.JXBH00) JXMC00,
       B.MCSL00 * round(D.LSDJ00 / (C.K2J000 * C.NSBL00) * C.Z2J000, 4) ZJ0000,
       decode(nvl(B.PSID00, 0), 0, '0', '1') PSBZ00,
       LYL000,
       to_char(to_date(A.LRRQ00, 'yyyy-MM-dd'), 'yyyy-MM-dd') || ' ' ||
       A.LRSJ00 LRRQ00,
       A.YYZF00,
       B.YZMXID,
       B.XMYFMC,
       B.YZCLLB
  from BQ_YPYZ00 A, BQ_YPYZMX B, BM_YD0000 C, YF_YPKCXX D
 where A.YZID00 = B.YZID00
   and B.YPNM00 = C.YPNM00
   and C.YPNM00 = D.YPNM00;