CREATE OR REPLACE VIEW SD_HOSPITAL.VW_ZY_JKXJRJZ
 (
 PJXH00, PLMC00, MRN000, XM0000, BMMC00, JFRQ00, JFSJ00, JFCZY0, JFJE00, 
CZYXM0, ZFFSMC, ZPH000, ZPKHYH, JGMC00, JFJE01, JZDH00, DJH000, ZFFS00

 )
 AS 
select  B.PJXH00, --票据序号
          B.PLMC00,  --票类名称
          C.MRN000,  --病案号
          B.XM0000,  --姓名
          D.BMMC00,  --当前病区
          A.JFRQ00,  --交费日期
          A.JFSJ00,  --交费时间
          A.JFCZY0,  --交费操作员
          decode(A.ZFFS00,1,
          decode((select nvl(max(trim(VALUE0)),'N') from XT_XTCS00 where NAME00='ZY_JSTKAYJJFS'),'Y',A.JFJE00,
                 'N',decode(a.JGMC00,'重新结算',(select -XJYE00 from ZY_JZB000 where JZDH00=a.JZDH00),A.JFJE00)
                 ),A.JFJE00) JFJE00,  --交费金额
          A.CZYXM0,  --操作员姓名
          A.ZFFSMC,  --支付方式名称
          A.ZPH000,  --支票号
          A.ZPKHYH,  --银行名称
          A.JGMC00,
          A.JFJE00 JFJE01 ,--原交费金额
          a.JZDH00,   --结账单号
          A.DJH000,    --单据号
          A.ZFFS00
          from  zy_brjfb0 A,ZY_PJSYQK B,BM_BRXXB0 C,BM_BMBM00 D
      where  A.BRID00 = C.BRID00 and A.PJH000 = B.PJH000 and A.DQBQ00=D.BMBH00 and a.jfje00<>0
/