CREATE OR REPLACE VIEW SD_HOSPITAL.VW_WZ_WZMXZ0
 (
 QRN000, QRY000, QRR000, MXZLSH, KCBMBH, RCKDH0, RCKLB0, RCKLX0, RCLXMC, 
SHBMBH, FHBMBH, SHBMMC, FHBMMC, RCKMXH, KCID00, WZMC00, GG0000, WZXH00, 
WZSXMC, BZDW00, JLDW00, ZHL000, CGJ000, BZDJ00, PFJ000, LSJ000, CRKSL0, 
GJJE00, LSJE00, QRRQ00, QRSJ00, QRJCS0, QRJCJE, SHRQ00, SHSJ00, SHJCS0, 
SHJCJE, ZY0000, FPH000, FPRQ00, PH0000, YXQ000, SXRQ00, CJID00, CJMC00, 
PZWH00, ZBQNM0, ZBXH00, ZBJ000, MJPH00, TXM000, MJRQ00, WZID00, DJ0000, 
RKJE00, CKJE00, RKSL00, CKSL00, JCS000, JCJE00, ZTBZ00, ZTBZMC, QRPFJE, 
RKPFJE, CKPFJE, WZDMBM, WZDMID

 )
 AS 
select
substr(a.QRRQ00,1,4) QRN000,
substr(substr(a.QRRQ00,1,6),5,6) QRY000,
substr(a.QRRQ00,7,8) QRR000,
MXZLSH, KCBMBH, RCKDH0, RCKLB0, RCKLX0, decode(RCKLB0,'1','入-','2','出-')||RCLXMC RCLXMC,
SHBMBH, FHBMBH, SHBMMC, FHBMMC, RCKMXH, KCID00, WZMC00, GG0000, WZXH00,
WZSXMC, BZDW00, JLDW00, ZHL000, CGJ000, BZDJ00, PFJ000, LSJ000, CRKSL0,
GJJE00, round(LSJ000*QRJCS0,nvl((select trim(VALUE0) from XT_XTCS00 where NAME00='WZ_SYDJJEBLSWXS'),2)) LSJE00, 
QRRQ00, QRSJ00, QRJCS0, QRJCJE, SHRQ00, SHSJ00, SHJCS0,
SHJCJE, ZY0000, FPH000, FPRQ00, PH0000, YXQ000, SXRQ00, CJID00, CJMC00,
PZWH00, ZBQNM0, ZBXH00, ZBJ000, MJPH00, TXM000, MJRQ00, WZID00,
--to_char(CGJ000) DJ0000,
trim(replace(to_char(CGJ000,'FM9999990.00')||' ','. ','')) DJ0000,
decode(RCKLB0,'1',round(GJJE00,nvl((select trim(VALUE0) from XT_XTCS00 where NAME00='WZ_SYDJJEBLSWXS'),2)),'') RKJE00,
decode(RCKLB0,'2',round(GJJE00,nvl((select trim(VALUE0) from XT_XTCS00 where NAME00='WZ_SYDJJEBLSWXS'),2)),'') CKJE00,
decode(RCKLB0,'1',CRKSL0,'') RKSL00,
decode(RCKLB0,'2',CRKSL0,'') CKSL00,
QRJCS0 JCS000,
round(QRJCJE,nvl((select trim(VALUE0) from XT_XTCS00 where NAME00='WZ_SYDJJEBLSWXS'),2)) JCJE00,
ZTBZ00,
decode(ZTBZ00,'1','已开单','2','已确认','3','已审核','4','已作废','5','已更正') ZTBZMC,
QRPFJE,
decode(RCKLB0,'1',round(PFJ000*CRKSL0,nvl((select trim(VALUE0) from XT_XTCS00 where NAME00='WZ_SYDJJEBLSWXS'),2)),'') RKPFJE,
decode(RCKLB0,'2',round(PFJ000*CRKSL0,nvl((select trim(VALUE0) from XT_XTCS00 where NAME00='WZ_SYDJJEBLSWXS'),2)),'') CKPFJE,
(select WZDMBM from WZ_WZXX00 B,BM_WZDM00 C where B.WZDMID=C.WZDMID and B.WZID00=A.WZID00) WZDMBM,
(select WZDMID from WZ_WZXX00 d where a.WZID00=d.WZID00) WZDMID
from WZ_WZMXZ0 a
/