CREATE OR REPLACE VIEW vw_yf_ypslsrts (
   ypnm00,
   ykbmbh,
   yfbmbh,
   ypbm00,
   pysm00,
   wbsm00,
   ypmc00,
   spm000,
   ypgg00,
   kcdw00,
   jldw00,
   zyfydw,
   mzfydw,
   k2j000,
   z2j000,
   m2j000,
   lsj000,
   gjj000,
   pfj000,
   cyjl00,
   ykkcsl,
   ykkc00,
   sjkcsl,
   kcsx00,
   kcxx00,
   ykypkc,
   sfkyyy,
   cfjb00,
   gzdj00,
   mzdj00,
   dpdj00,
   sjdj00,
   lbbh00,
   ylbh00,
   jxbh00,
   yfid00,
   pcid00,
   cyl000,
   zqs000,
   pdxh00,
   ypsysm,
   sfztpd,
   zyfydj,
   ztbz00,
   sccjzw,
   ypmlbz,
   pzwh00 )
AS
SELECT
  K.YPNM00,
  K.YKBMBH,
  F.YFBMBH,
  K.YPBM00,
  K.PYSM00,
  K.WBSM00,
  K.YPMC00,
  K.YPMC00,
  K.YPGG00,
  K.KCDW00,
  K.JLDW00,
  K.ZYFYDW,
  K.MZFYDW,
  K.K2J000,
  K.Z2J000,
  K.M2J000,
  round(F.LSDJ00/A.NSBL00,4),
  round(F.GJDJ00/A.NSBL00,4),--K.GJJ000,
  round(K.PFJ000/A.NSBL00,4),
  K.CYJL00,
  decode( (select trim(VALUE0) from XT_XTCS00 where NAME00='XT_SFXSKC'),
          'N',decode(sign(F.YKKCSL*A.NSBL00),1,0,0),
          F.YKKCSL*A.NSBL00),
  --F.YKKCSL,
  TO_CHAR(ROUND(
     decode((select trim(VALUE0) from XT_XTCS00 where NAME00='XT_SFXSKC'),
          'N',decode(sign(F.YKKCSL*A.NSBL00),1,0,0),
          F.YKKCSL*A.NSBL00)
     /K.K2J000,2))||trim(K.KCDW00),
  --TO_CHAR(ROUND(F.YKKCSL/K.K2J000,2))||trim(K.KCDW00),
  ROUND(F.SJKCSL/K.K2J000*A.NSBL00,2)||trim(K.KCDW00),
  F.KCSX00*A.NSBL00,
  F.KCXX00*A.NSBL00,
  TO_CHAR(ROUND(DECODE((SELECT TRIM(VALUE0) FROM XT_XTCS00 WHERE NAME00='YK_SFQYLSYP'),'Y',
  K.KCSL00,K.KCSL00),3))||trim(K.KCDW00),
  --TO_CHAR(ROUND(DECODE((SELECT TRIM(VALUE0) FROM XT_XTCS00 WHERE NAME00='YK_SFQYLSYP'),'Y',
  --K.KCSL00,K.KCSL00)/K.K2J000,3))||trim(K.KCDW00),
  --TO_CHAR(ROUND(K.KCSL00/K.K2J000,2))||trim(K.KCDW00),
  K.SFKYYY,
  K.CFJB00,
  K.GZDJ00,
  K.MZDJ00,
  K.DPDJ00,
  K.SJDJ00,
  K.LBBH00,
  K.YLBH00,
  K.JXBH00,
  K.YFID00,      --药品用法
  K.PCID00,      --服用频次
  K.CYL000,      --次用量
  K.ZQS000,      --周期数
  F.PDXH00,     --盘点序号
  K.YPSYSM,       --药品使用说明
  F.SFZTPD,
  ROUND(K.Z2J000*F.LSDJ00/K.K2J000,3), --单价/住院发药单位
  F.ZTBZ00,
  K.SCCJZW,
  A.YPMLBZ,
  A.PZWH00
  FROM YF_YPKCXX F,VW_YK_YPKCXX K,BM_YD0000 A
  WHERE F.YPNM00=K.YPNM00 and F.ypnm00=A.ypnm00
UNION ALL
  SELECT
  K.YPNM00,
  K.YKBMBH,
  F.YFBMBH,
  K.YPBM00,
  B.PYSM00,
  B.WBSM00,
  B.BMMC00,
  K.YPMC00,
  K.YPGG00,
  K.KCDW00,
  K.JLDW00,
  K.ZYFYDW,
  K.MZFYDW,
  K.K2J000,
  K.Z2J000,
  K.M2J000,
  round(F.LSDJ00/A.NSBL00,4),
  round(F.GJDJ00/A.NSBL00,4),--K.GJJ000,
  round(K.PFJ000/A.NSBL00,4),
  K.CYJL00,
  F.YKKCSL*A.NSBL00,
    TO_CHAR(ROUND(
     decode((select trim(VALUE0) from XT_XTCS00 where NAME00='XT_SFXSKC'),
          'N',decode(sign(F.YKKCSL*A.NSBL00),1,0,0),
          F.YKKCSL*A.NSBL00)
     /K.K2J000,2))||trim(K.KCDW00),
--TO_CHAR(ROUND(F.YKKCSL/K.K2J000,2))||trim(K.KCDW00),
  ROUND(F.SJKCSL/K.K2J000*A.NSBL00,2)||trim(K.KCDW00),
  F.KCSX00*A.NSBL00,
  F.KCXX00*A.NSBL00,
    TO_CHAR(ROUND(DECODE((SELECT TRIM(VALUE0) FROM XT_XTCS00 WHERE NAME00='YK_SFQYLSYP'),'Y',
  K.KCSL00,K.KCSL00),3))||trim(K.KCDW00),
 -- TO_CHAR(ROUND(DECODE((SELECT TRIM(VALUE0) FROM XT_XTCS00 WHERE NAME00='YK_SFQYLSYP'),'Y',
 -- K.KCSL00,K.KCSL00)/K.K2J000,3))||trim(K.KCDW00),
  --  TO_CHAR(ROUND(K.KCSL00/K.K2J000,2))||trim(K.KCDW00),
  K.SFKYYY,
  K.CFJB00,
  K.GZDJ00,
  K.MZDJ00,
  K.DPDJ00,
  K.SJDJ00,
  K.LBBH00,
  K.YLBH00,
  K.JXBH00,
  K.YFID00,      --药品用法
  K.PCID00,      --服用频次
  K.CYL000,      --次用量
  K.ZQS000,      --周期数
  F.PDXH00,     --盘点序号
  K.YPSYSM,       --药品使用说明
  F.SFZTPD,       --是否暂停盘点
  ROUND(K.Z2J000*F.LSDJ00/K.K2J000,3), --单价/住院发药单位
  F.ZTBZ00,
  K.SCCJZW,
  A.YPMLBZ,
  A.PZWH00
  FROM YF_YPKCXX F,BM_YPBM00 B,VW_YK_YPKCXX K,bm_YD0000 A
  WHERE F.YPNM00=K.YPNM00 AND K.YPNM00=B.YPNM00 and K.YPNM00=A.YPNM00
/