CREATE OR REPLACE VIEW VW_BM_CWK000
(ch0000, bqh000, bqmc00, ksh000, ksmc00, cwzt00, cwblrq, zyid00, cwdj00, fjh000, zch000, cwxz00, cwyj00, 
zyys00, zyysxm, zzys00, zzysxm, zrys00, zrysxm, zrhs00, zrhsxm, cwf000, cwdjmc, cwztmc, xm0000, xb0000, 
color, zyh000, sfmrkt,cwxh00)
AS
SELECT
  C.CH0000, C.BQH000, Q.BMMC00 BQMC00, C.KSH000, M.BMMC00 "KSMC00", CWZT00, CWBLRQ, C.ZYID00, C.CWDJ00,
  FJH000, ZCH000, CWXZ00, CWYJ00, P.SXYS00, Y.ZWXM00 "ZYYSXM", P.ZZYS00, Z.ZWXM00 "ZZYSXM", P.ZRYS00,
  R.ZWXM00 "ZRYSXM",NVL(P.ZGHS00,C.ZRHS00) ZRHS00,NVL((SELECT ZWXM00 FROM BM_YGBM00 WHERE YGBH00=P.ZGHS00 AND ROWNUM=1),H.ZWXM00)  "ZRHSXM", J.CWF000, D.CWDJMC,
  DECODE(CWZT00,'0','空床','1','占用','2','包床','3','预定','4','停用')CWZTMC, P.XM0000, P.XB0000,'0' color,
  P.ZYH000,c.sfmrkt,c.cwxh00
FROM BM_CWK000 C,
    (SELECT CWDJ00,SUM(NVL(S.SFJE00,0)*J.SFCS00) "CWF000",S.SFBZDJ FROM VW_BQ_CWJM00 J, VW_BQ_ZYSFXM S
      WHERE J.SFXMID=S.SFXMID GROUP BY CWDJ00,SFBZDJ) J, BM_CWDJ00 D, BM_YGBM00 H, BM_YGBM00 Y,
         BM_YGBM00 Z, BM_YGBM00 R, BM_BMBM00 M, BM_BMBM00 Q, ZY_BRXXB0 P
WHERE C.ZYID00=P.ZYID00(+) AND C.CWDJ00=D.CWDJ00 AND C.CWDJ00=J.CWDJ00(+)
  AND C.ZRHS00=H.YGBH00(+) AND P.SXYS00=Y.YGBH00(+)
  AND P.ZZYS00=Z.YGBH00(+) AND P.ZRYS00=R.YGBH00(+) AND C.KSH000=M.BMBH00(+)
  AND C.BQH000=Q.BMBH00(+) and J.SFBZDJ=SF_XT_GETSFDJBYBMBH(C.BQH000);