create or replace view vw_zy_dbzczb as
select 
  decode(a.DBZBM0,null,'2','3') as BZZT00,  --病种进入状态  1进入待确认 2退出待确认 3已进入 4已退出
  decode(a.DBZBM0,null,'退出待确认','已进入') as BZZTMC,   --病种进入状态名称 
  b.CZBID0,     --操作表ID    
  a.ZYID00,     --住院ID
  a.ZYH000,
  a.BRRYCS,
  a.BRID00,
  a.XM0000,
  a.XB0000,
  a.BRNL00,
  a.FBBH00,
  a.FBMC00,
  a.YBZXLB,
  a.YBLB00,
  a.YBBRLB,
  a.RYRQ00,
  a.RYBQ00,
  a.RYBQMC,
  a.RZKS00,
  a.RZKSMC,
  a.RYCWH0,
  a.PXCWH0,  
  a.DQBQ00,
  a.DQBQMC,
  a.BQYYID,
  a.DQKS00,
  a.DQKSMC,
  a.BRZT00,
  a.BRZTMC,
  a.FBXX00,
  a.DBZBM0,
  b.FFBZBM,     --付费病种编码
  b.JRRQ00,     --单病种进入日期
  b.JRSJ00,     --单病种进入时间
  b.JRCZY0,     --单病种进入操作员
  (select ZWXM00 from BM_YGBM00 where YGBH00 = b.JRCZY0) as JRCZXM,
  b.JRCZKS,     --单病种进入操作科室
  (select BMMC00 from BM_BMBM00 where BMBH00 = b.JRCZKS) as JRKSMC,
  b.TCRQ00,     --单病种退出日期
  b.TCSJ00,     --单病种退出时间
  b.TCCZY0,     --单病种退出操作员
  (select ZWXM00 from BM_YGBM00 where YGBH00 = b.TCCZY0) as TCCZXM,  
  b.TCCZKS,     --单病种退出操作科室
  (select BMMC00 from BM_BMBM00 where BMBH00 = b.TCCZKS) as TCKSMC,
  c.SFBZID,     --病种收费标准ID
  c.BZMC00,     --病种（主诊断）名称
  c.ZZDBM0,     --主诊断编码
  c.ZCZMC0,     --主操作名称
  c.SSCZBM,     --手术（操作）码  
  c.CWNR00,     --除外内容
  substr(c.BZ0000,0,100) BZ0000,     --备注说明
  d.YJSRQ0,     --最后预结算日期
  d.YJSSJ0,     --最后预结算时间
  d.YBYJS0,     --医保预结算金额
  d.YBYZF0,     --医保预支付总额
 -- d.YJZFJE,     --医保预结算--自付金额
  decode(a.FBBH00, 1, c.BZSFJE, d.YJZFJE) YJZFJE, --预结自付金额
  d.YJZHJE,     --医保预结算--账户支付
  d.YJTCJE,     --医保预结算--统筹支付
  d.YJQTJE,     --医保预结算--其它支付 
  d.DBZCWF,     --单病种超标床位费
  d.DBZHCF,     --单病种可另收材料
  d.ZYE000,     --账户预交总余额
  d.WJSJE0,     --未结算金额(当前总费用=病种内总费用+病种外总费用)  
  c.BZSFJE,     --单病种定额  
  d.BZWZFY,     --单病种外费用,
  d.BZNZFY,     --单病种内费用
  SF_ZY_GETDBZNKYYE(a.ZYID00) as BZNKYE,             --单病种内可用余额
  SF_ZY_GETDBZWKYYE(a.ZYID00) as BZWKYE,             --单病种外可用余额    
  nvl(c.BZSFJE,0) - d.BZNZFY as BZNCE0,  --单病种内费用差额 = 单病种定额-病种内总费用
  round(d.BZNZFY/nvl(c.BZSFJE,0),2)*100 as BZNBL0,  --单病种内费用比例 = 病种内总费用/单病种定额*100%
  (select nvl(sum(JFJE00),0) from ZY_BRJFB0 where ZYID00 = a.ZYID00 and JZDH00 +0 = 0 and PLBH00 in (4,8)) as WJYJZE,  --未结算预交金总额
  case when (round(d.BZNZFY/nvl(c.BZSFJE,0),2)*100-100)>=0 then '2'
       when (round(d.BZNZFY/nvl(c.BZSFJE,0),2)*100-80)>=0 then '1'
     else '0' end as  YJQK00,
  case when (round(d.BZNZFY/nvl(c.BZSFJE,0),2)*100-100)>=0 then '超额'
       when (round(d.BZNZFY/nvl(c.BZSFJE,0),2)*100-80)>=0 then '预警'
     else '正常' end as  YJQKMC,   
   SF_ZY_GETDQBJXJ(a.ZYID00, c.BZSFJE) as DQBJXJ --当前补交现金
from VW_ZY_BRXXB0 a,ZY_DBZCZB b,BM_BZSFBZ c,VW_ZY_DBZZHXX d 
where a.ZYID00 = b.ZYID00 
  and a.ZYID00 = d.ZYID00
  and a.FBBH00 = c.FBBH00
  and a.YBZXLB = c.YBZXLB
  and b.FFBZBM = c.FFBZBM
  and b.TCRQ00 is null 
union all
select 
  '1' as BZZT00,  --病种进入状态  1进入待确认 2退出待确认 3已进入 4已退出
  '进入待确认' as BZZTMC0,  --病种进入状态名称 
  -1 as CZBID0,  --操作表ID    
  a.ZYID00,      --住院ID
  a.ZYH000,
  a.BRRYCS,
  a.BRID00,
  a.XM0000,
  a.XB0000,
  a.BRNL00,
  a.FBBH00,
  a.FBMC00,
  a.YBZXLB,
  a.YBLB00,
  a.YBBRLB,
  a.RYRQ00,
  a.RYBQ00,
  a.RYBQMC,
  a.RZKS00,
  a.RZKSMC,
  a.RYCWH0,
  a.PXCWH0,  
  a.DQBQ00,
  a.DQBQMC,
  a.BQYYID,
  a.DQKS00,
  a.DQKSMC,
  a.BRZT00,
  a.BRZTMC,
  a.FBXX00,
  a.DBZBM0,
  a.DBZBM0 as FFBZBM,     --付费病种编码
  null as JRRQ00,         --单病种进入日期
  null as JRSJ00,         --单病种进入时间
  null as JRCZY0,         --单病种进入操作员
  null as JRCZXM,         
  null as JRCZKS,         --单病种进入操作科室
  null as JRKSMC,         
  null as TCRQ00,         --单病种退出日期
  null as TCSJ00,         --单病种退出时间
  null as TCCZY0,         --单病种退出操作员
  null as TCCZXM,         
  null as TCCZKS,         --单病种退出操作科室
  null as TCKSMC,
  c.SFBZID,               --病种收费标准ID
  c.BZMC00,               --病种（主诊断）名称
  c.ZZDBM0,               --主诊断编码
  c.ZCZMC0,               --主操作名称
  c.SSCZBM,               --手术（操作）码  
  c.CWNR00,               --除外内容
  substr(c.BZ0000,0,100) BZ0000,--c.BZ0000,               --备注说明
  d.YJSRQ0,               --最后预结算日期
  d.YJSSJ0,               --最后预结算时间
  d.YBYJS0,               --医保预结算金额
  d.YBYZF0,               --医保预支付总额
  --d.YJZFJE,               --医保预结算--自付金额
  decode(a.FBBH00, 1, c.BZSFJE, d.YJZFJE) YJZFJE, --预结自付金额
  d.YJZHJE,               --医保预结算--账户支付
  d.YJTCJE,               --医保预结算--统筹支付
  d.YJQTJE,               --医保预结算--其它支付 
  d.DBZCWF,               --单病种超标床位费
  d.DBZHCF,               --单病种可另收材料
  d.ZYE000,               --账户预交总余额
  d.WJSJE0,               --未结算金额(当前总费用=病种内总费用+病种外总费用)  
  c.BZSFJE,               --单病种定额  
  0 as BZWZFY,            --单病种外费用,
  0 as BZNZFY,            --单病种内费用
  0 as BZNKYE,            --单病种内可用余额
  0 as BZWKYE,            --单病种外可用余额    
  0 as BZNCE0,            --单病种内费用差额= 单病种定额-病种内总费用
  0 as BZNBL0,            --单病种内费用比例 = 病种内总费用/单病种定额*100%
  (select nvl(sum(JFJE00),0) from ZY_BRJFB0 where ZYID00 = a.ZYID00 and JZDH00 +0 = 0 and PLBH00 in (4,8)) as WJYJZE,  --未结算预交金总额
  '0' as YJQK00,
  '正常' as YJQKMC,   
   SF_ZY_GETDQBJXJ(a.ZYID00, c.BZSFJE) as DQBJXJ --当前补交现金
from VW_ZY_BRXXB0 a,BM_BZSFBZ c,VW_ZY_DBZZHXX d 
where a.ZYID00 = d.ZYID00
  and a.FBBH00 = c.FBBH00
  and a.YBZXLB = c.YBZXLB
  and a.DBZBM0 = c.FFBZBM
  and a.FFBZBM is null    --收费处未确认进入
  and a.DBZBM0 is not null  --医生已选择病种;