CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YF_ZDYKFLYD_DSQL
(ckdh00, zyid00, bqbmbh, yfbmbh, qlzt00, hzbz00, fybz00, thbz00, byfl00, ypmc00, yptym0, ypgg00, ypmcgg, lsdj00, ypzsl0, qldw00, pcmc00, djl000, yyff00, syff00, je0000, ysgzh0, ypdlbh, cxbz00, yfsmmc, srrq00, zwxm00, zyts00)
AS
SELECT Y.CKDH00,Y.ZYID00,Y.BQBMBH,Y.YFBMBH,Y.QLZT00,Y.HZBZ00,Y.FYBZ00,Y.THBZ00,NVL(E.BYFL00,' '),
   Y.YPMC00,E.YPTYM0,Y.YPGG00,Y.YPMC00||'/'||Y.YPGG00 YPMCGG,
   Y.LSDJ00,
   Decode((SELECT Trim(value0) from xt_xtcs00 where name00='YF_ZJKFDDDYGS'),
           '17',
           ABS(Y.YPZSL0),
           sum(Y.YPZSL0)),
   Y.QLDW00,Y.PCMC00,
   Decode((SELECT Trim(value0) from xt_xtcs00 where name00='YF_ZJKFDDDYGS'),
           '17',
           DECODE(SUBSTR(Y.CSL000,1,1),'.','0','')||Round(Y.CSL000/ZHL000,2)||Y.QLDW00,
           DECODE(SUBSTR(Y.CSL000,1,1),'.','0','')||Y.CSL000||Trim(Y.JLDW00))||'/',
   TRIM(Y.YPYFMC)||TRIM(Y.XMYFMC) as YYFF00,
   Decode((SELECT Trim(value0) from xt_xtcs00 where name00='YF_ZJKFDDDYGS'),
           '17',
          DECODE(SUBSTR(Y.CSL000,1,1),'.','0','')||Round(Y.CSL000/ZHL000,2)||Y.QLDW00,
           DECODE(SUBSTR(Y.CSL000,1,1),'.','0','')||Y.CSL000||Trim(Y.JLDW00))||'/'||
         --  Y.PCMC00||'/'||Y.YPYFMC||'/'||Y.YFSMMC as syff00,
        (SELECT Z.SM0000 FROM bm_pc0000 Z WHERE Z.PCMC00=Y.PCMC00 and z.yxbz00='Y')||'/'||Y.YPYFMC||'/'||Y.YFSMMC as syff00,
   DECODE((SELECT Trim(value0) from xt_xtcs00 where name00='YF_ZJKFDDDYGS'),'17',abs(round(Y.YPZSL0*Y.LSDJ00,2)),round(Y.YPZSL0*Y.LSDJ00,2)) JE0000,
   DECODE(Y.YSGZH0,'9999','1','0')YSGZH0,
   --Y.YSGZH0,
   DECODE(Y.YPDLBH,'2','2','1'),
   Y.CXBZ00,
   Y.YFSMMC,
   Y.SRRQ00,
   D.ZWXM00,
   Y.ZYTS00

 FROM YF_YZYPSQ Y,BM_YD0000 E,BM_YGBM00 D
 WHERE Y.YPNM00=E.YPNM00 AND Y.ysgzh0=D.ygbh00
 group by Y.CKDH00,Y.ZYID00,Y.BQBMBH,Y.YFBMBH,Y.QLZT00,Y.HZBZ00,Y.FYBZ00,Y.THBZ00,E.BYFL00,
   Y.YPMC00,E.YPTYM0,Y.YPGG00,Y.LSDJ00,Y.YPZSL0,Y.QLDW00,Y.PCMC00,Y.CSL000,Y.QLDW00,Y.JLDW00,
   Y.YPYFMC,Y.XMYFMC,Y.YSGZH0,ZHL000,
   Y.YPDLBH,
   Y.CXBZ00,
   Y.YFSMMC,
   Y.SRRQ00,
   D.ZWXM00,Y.ZYTS00,Y.ch0000,Y.BRXM00

  ORDER BY Y.YPDLBH,Y.SRRQ00,Y.YPMC00
  /*select SZ0000, ZB0000, CKDH00, ZYID00, BQBMBH, YFBMBH, QLZT00, HZBZ00, FYBZ00, THBZ00, BYFL00,
YPMC00, YPTYM0, YPGG00, YPMCGG, LSDJ00, YPZSL0, QLDW00, PCMC00, DJL000,
YYFF00, SYFF00, JE0000, YSGZH0, YPDLBH, CXBZ00, YFSMMC, SRRQ00, ZWXM00,
ZYTS00, BQBMMC, CH0000, BRXM00 from
(
select * from ycvw_yf_zdykflyd_dsql01zs
union all
select * from ycvw_yf_zdykflyd_dsql01zw
union all
select * from ycvw_yf_zdykflyd_dsql01ws
union all
select * from ycvw_yf_zdykflyd_dsql01sq
union all
select * from ycvw_yf_zdykflyd_dsql01dc
) where PCMC00 not like '%备%' and SRRQ00>='20251101'
)*/;