CREATE OR REPLACE VIEW SD_HOSPITAL.VW_ZY_KSJSDDY
 (
 ZYID00, JZDH00, BRXM00, BRZJBH, BRJTDZ, BRDH00, YYJBR0, YYJGKS, BRNL00, 
YLJGMC, ZYH000, ZDMC00, RYRQ00, SJCYRQ, ZYTS00, JZRQ00, CWF000, ZCF000, 
JCF000, HYF000, ZLF000, SSF000, HLF000, WSCLF0, XYF000, ZCYF00, CYF000, 
YSFWF0, YBZLF0, QTZYF0, HJJE00, BCFYZE, KBFY00, BKBC00, BCLJ00, DBZKB0, 
DBZBCE, DBKBFY, DBBCFY, ZBJE00, ZBJEDX, QFX000, ZFJE00, ZFJEDX, FDX000, 
CXJZDH, CZYXM0, JBNHBC, NDLJZE

 )
 AS 
select
      a.ZYID00,   --住院ID
      a.JZDH00,   --结帐单号
      b.BRXM00,   --病人姓名
      b.BRZJBH,   --病人身份证
      trim(SF_SF_RETURNINDEXEH(C.CHSFXX,'|', 2))||trim(SF_SF_RETURNINDEXEH(C.CHSQXX,'|', 2))||trim(SF_SF_RETURNINDEXEH(C.CHXQXX,'|', 2)) as BRJTDZ,   --病人地址
      --b.BRJTDZ,   --病人地址
      nvl(b.BRDH00,B.BRLXDH) as BRDH00,   --病人电话
      '' as YYJBR0,--医院经办人
      SF_SF_TYZD00('新农合跨省结报参数','HospName')||'医保办'  as YYJGKS,--医疗机构经办科室
      SF_BQ_CSRQTONL(b.BRCSRQ) BRNL00,   --年龄
      (select MC0000 from BM_TYZD00 where ZDMC00='新农合跨省结报参数' and BH0000='HospName' and rownum=1) YLJGMC,   --医疗机构名称
      trim(C.ZYH000) as  ZYH000,   --住院号
      nvl(SF_ZY_ZDDBZMC(C.ZYID00,2),(select ZDMC00 from BQ_BRZDXX where ZYID00=a.ZYID00 and ZDLB00 in ('2') and rownum=1)) as  ZDMC00,   --诊断名称
       to_char(to_date(C.RYRQ00,'YYYYMMDD'),'YYYY-MM-DD') as  RYRQ00,   --入院日期
       to_char(to_date(C.SJCYRQ,'YYYYMMDD'),'YYYY-MM-DD') as  SJCYRQ,   --出院日期
       to_date(nvl(C.CYDJRQ,to_char(sysdate,'YYYYMMDD')),'YYYYMMDD')-to_date(C.RYRQ00,'YYYYMMDD') as ZYTS00,   --住院天数+1
      to_char(to_date(a.JZRQ00,'YYYYMMDD'),'YYYY-MM-DD') JZRQ00,   --结帐日期
      trim(to_char(SF_ZY_FPXMJE('0',A.ZYID00,A.JZDH00,'1'),'99999990.00')) as CWF000,   --床位费
      trim(to_char(SF_ZY_FPXMJE('0',A.ZYID00,A.JZDH00,'2'),'99999990.00')) as ZCF000,   --诊察费
      trim(to_char(SF_ZY_FPXMJE('0',A.ZYID00,A.JZDH00,'3'),'99999990.00')) as JCF000,   --检查费
      trim(to_char(SF_ZY_FPXMJE('0',A.ZYID00,A.JZDH00,'4'),'99999990.00')) as HYF000,   --化验费
      trim(to_char(SF_ZY_FPXMJE('0',A.ZYID00,A.JZDH00,'5'),'99999990.00')) as ZLF000,   --治疗费
      trim(to_char(SF_ZY_FPXMJE('0',A.ZYID00,A.JZDH00,'6'),'99999990.00')) as SSF000,   --手术费
      trim(to_char(SF_ZY_FPXMJE('0',A.ZYID00,A.JZDH00,'7'),'99999990.00')) as HLF000,   --护理费
      trim(to_char(SF_ZY_FPXMJE('0',A.ZYID00,A.JZDH00,'8'),'99999990.00')) as WSCLF0,   --卫生材料费
      trim(to_char(SF_ZY_FPXMJE('0',A.ZYID00,A.JZDH00,'9'),'99999990.00')) as XYF000,   --西药费
      trim(to_char(SF_ZY_FPXMJE('0',A.ZYID00,A.JZDH00,'10'),'99999990.00')) as ZCYF00,   --中成药费
      trim(to_char(SF_ZY_FPXMJE('0',A.ZYID00,A.JZDH00,'11'),'99999990.00')) as CYF000,   --草药费
      trim(to_char(SF_ZY_FPXMJE('0',A.ZYID00,A.JZDH00,'12'),'99999990.00')) as YSFWF0,   --药事服务费
      trim(to_char(SF_ZY_FPXMJE('0',A.ZYID00,A.JZDH00,'13'),'99999990.00')) as YBZLF0,   --一般诊疗费
      trim(to_char(SF_ZY_FPXMJE('0',A.ZYID00,A.JZDH00,'14'),'99999990.00')) as QTZYF0,   --其他住院费
      trim(to_char(A.HJJE00,'99999990.00')) as HJJE00,   --总费用
      A.YBYL35 as BCFYZE,--本次费用总额
      --SF_ZY_GETZZDVALUE(A.DDLSH0,'N708-33') as KBFY00,   --可补费用
      --SF_ZY_GETZZDVALUE(A.DDLSH0,'N708-31') as BKBC00,   --不可补费用
      --SF_ZY_GETZZDVALUE(A.DDLSH0,'N708-39') as  BCLJ00,   --补偿累计
      trim(to_char(nvl(A.YBYL09,0),'99999990.00')) as KBFY00,   --可补费用
      trim(to_char(nvl(A.YBYL35,A.HJJE00)-nvl(A.YBYL09,0),'99999990.00')) as BKBC00,   --不可补费用
      trim(to_char(nvl(A.YBYL36,0),'99999990.00')) as  BCLJ00,   --补偿累计
      trim(to_char(0,'99999990.00')) as DBZKB0,   --单病种可补费
      trim(to_char(0,'99999990.00')) as DBZBCE,   --单病种补偿额
      --SF_ZY_GETZZDVALUE(A.DDLSH0,'N708-35') as  DBKBFY,   --大病可补费用
      --SF_ZY_GETZZDVALUE(A.DDLSH0,'N708-36') as DBKBFY,   --大病补偿费用
      trim(to_char(nvl(A.YBYL24,0),'99999990.00')) as  DBKBFY,   --大病可补费用 
      trim(to_char(nvl(A.YBYL26,0),'99999990.00')) as DBKBFY,   --大病补偿费用     
      trim(to_char(nvl(A.TCJJZF,0),'99999990.00')) as  ZBJE00,   --直报金额
      SF_ZY_MONEY(nvl(A.TCJJZF,0)) as ZBJEDX,   --直报金额大写   
      --SF_ZY_GETZZDVALUE(A.DDLSH0,'N708-41') QFX000,   --起付线
      trim(to_char(nvl(A.YBQFJE,0),'99999990.00')) as  QFX000,   --起付线
      trim(to_char(nvl(a.ZFJE00,0),'99999990.00')) as  ZFJE00,   --自费金额
      SF_ZY_MONEY(nvl(a.ZFJE00,0)) as ZFJEDX,   --自费金额大写 
      --SF_ZY_GETZZDVALUE(A.DDLSH0,'N708-42')  as FDX000,   --封顶线
      trim(to_char(nvl(A.CFDXJE,0),'99999990.00')) as FDX000,   --封顶线
      a.CXJZDH,
      (select ZWXM00 from BM_YGBM00 where YGBH00=a.JZY000) CZYXM0,
      trim(to_char(SF_ZY_GETZZDVALUE(A.DDLSH0,'N708-31'),'99999990.00')) as JBNHBC,  --(本次)基本农合补偿费用(元)
      trim(to_char(SF_ZY_GETZZDVALUE(A.DDLSH0,'N708-39'),'99999990.00')) as NDLJZE   --(年度)基本农合累计补偿(元)
      from ZY_JZB000 a,BM_BRXXB0 b ,ZY_BRXXB0 C
      where a.BRID00=b.BRID00 and b.BRID00=C.BRID00
      and a.ZYID00=C.ZYID00
/