-- Start of DDL Script for View SD_HOSPITAL.VW_HLYY_YZMXCX
-- Generated 15-十二月-2020 16:56:28 from SD_HOSPITAL@46

CREATE OR REPLACE VIEW vw_hlyy_yzmxcx (
   kdksbh,
   kdksmc,
   kzys00,
   zyfydw,
   yslb00,
   kzysmc,
   qyrq00,
   tzrq00,
   mcsl00,
   jldw00,
   lsdj00,
   yfbmbh,
   sccjbh,
   sccjzw,
   yfid00,
   gysj00,
   ypyfmc,
   pcid00,
   pcmc00,
   ypgg00,
   jxmc00,
   zj0000,
   psbz00,
   lyl000,
   lrrq00,
   yyzf00,
   yzmxid,
   xmyfmc )
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
  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
/


-- End of DDL Script for View SD_HOSPITAL.VW_HLYY_YZMXCX