-- Start of DDL Script for View SD_HOSPITAL.VW_ZY_ZHXXTJ
-- Generated 16-三月-2012 19:55:22 from SD_HOSPITAL@QZRMYY

CREATE OR REPLACE VIEW vw_zy_zhxxtj (
   dqbq00,
   dqbqmc,
   dqks00,
   dqksmc,
   zyh000,
   ybzyh0,
   mrn000,
   rycwh0,
   brxm00,
   zyzhid,
   jflbid,
   brid00,
   sfdj00,
   yjzje0,
   xjye00,
   zzye00,
   yjsje0,
   wjsje0,
   srfyze,
   yyj000,
   zye000,
   ykzye0,
   bjje00,
   tyje00,
   yxqfje,
   ryrq00,
   rysj00,
   cyrq00,
   cysj00,
   cydjrq,
   cydjsj,
   kl0000,
   yblbmc,
   color0,
   zyid00,
   xb0000,
   fshjje,
   fsgrxj,
   fsgrzh,
   fstcjj,
   fssbgr,
   fssbtc,
   kyje00,
   brzt00,
   czyxm0,
   sjryrq,
   sjcyrq,
   ybzhye,
   ybyjs0,
   ybyzf0,
   yzybje,
   ybtcje,
   ybzfje,
   gryzje )
AS
select
      B.DQBQ00  ,    --当前病区编号
      C.BMMC00  ,    --当前病区名称
      B.DQKS00  ,
      (SELECT BMMC00 from BM_BMBM00 where BMBH00=B.DQKS00),
      B.ZYH000  ,    --住院号
      B.YBZYH0  ,    --医保生成的住院流水号
      B.MRN000  ,    --病案号
      B.RYCWH0  ,    --床号
      B.XM0000  ,    --姓名
        ZYZHID  ,    --住院病人帐户流水号
        JFLBID  ,    --帐户类别ID
      A.BRID00  ,    --病人ID
        SFDJ00  ,    --病人帐户是否冻结标志;'0':正常,'1':冻结该帐户
        (select  NVL(sum(JFJE00),0)  from  zy_brjfb0  where  brid00 =A.BRID00 and JFLBID+0 = 2 and jzdh00 = 0 ),    --病人预交总额
        XJYE00	,    --病人现金余额
        ZZYE00	,    --病人转帐余额
        YJSJE0	,    --已结算金额
--        WJSJE0	,    --未结算金额
        (select nvl(sum(HJJE00),0) from ZY_brfy00 where  ZYID00 =B.ZYID00),  --费用总额
        (select nvl(sum(HJJE00),0) from ZY_brfy00 where  ZYID00 =B.ZYID00 and CZRQ00= to_char(sysdate-1,'yyyymmdd')),
        YYJ000  ,    --优先级,'0':先扣除现金余额,'1':先扣除转帐余额
        ZYE000	,    --帐户实际总余额
        YKZYE0	,    --预扣后的总余额
        BJJE00	,    --报警限额
        TYJE00  ,    --停用限额
        YXQFJE  ,    --允许病人的欠费的最大金额
        RYRQ00	,    --入院日期
        RYSJ00	,    --入院时间
        CYRQ00	,    --出院日期
        CYSJ00	,    --出院时间
        CYDJRQ  ,    --出院登记日期
        CYDJSJ  ,    --出院登记时间
        KL0000	,    --帐户口令
        D.YBMC00,    --医保类别名称
        E.COLOR0,    --颜色
        B.ZYID00,    --ZYID00
        b.XB0000,    --性别  
        (select nvl(sum(HJJE00),0) from ZY_YBFS00 where ZYID00=B.ZYID00 and CXBZ00='0') ,   --医保已发送记录合计金额
        (select nvl(sum(ZFJE00),0) from ZY_YBFS00 where ZYID00=B.ZYID00 and CXBZ00='0') ,   --医保已发送记录个人现金分摊
        (select nvl(sum(GRZHZF),0) from ZY_YBFS00 where ZYID00=B.ZYID00 and CXBZ00='0') ,   --医保已发送记录个人账户分摊
        (select nvl(sum(TCJJZF),0) from ZY_YBFS00 where ZYID00=B.ZYID00 and CXBZ00='0') ,   --医保已发送记录统筹基金分摊
        (select nvl(sum(SBGRZH),0) from ZY_YBFS00 where ZYID00=B.ZYID00 and CXBZ00='0') ,   --医保已发送记录商保个人账户分摊
        (select nvl(sum(SBTCJJ),0) from ZY_YBFS00 where ZYID00=B.ZYID00 and CXBZ00='0') ,   --医保已发送记录商保统凑基金分摊
        SP_SF_KYJE00(0,B.BRID00) KYJE00,
        b.BRZT00,                                                     --可用金额
        B.CZYXM0,
        B.SJRYRQ,
        B.SJCYRQ,
        A.YBZHYE,
        A.YBYJS0,
        A.YBYZF0,
        A.YZYBJE,
        A.YBTCJE,
        A.YBZFJE,
        (nvl(A.YBYJS0,0)-nvl(A.YBYZF0,0)) GRYZJE
FROM  ZY_BRZHXX A,ZY_BRXXB0 B,BM_BMBM00 C,IC_YBBRLB D,BM_YBBRLB E
where A.BRID00 = B.BRID00
  and A.JFLBID = 2         --住院预交金账户
  and B.DQBQ00 = C.BMBH00
  
----####====修改显示出院登记状态的病人
  and b.cyrq00 is null            --B.CYDJRQ is NULL
  and B.BRZT00 NOT IN ('4','5','9')   ---显示出院登记状态的病人
----####====修改显示出院登记状态的病人

  and B.YBLB00=D.YBLB00(+)  --增加医保类别
  and b.FBBH00=D.FBBH00(+)
  and B.FBBH00=E.FBBH00(+)
  and B.YBZXLB=E.YBZXLB(+)
  and B.YBBRLB=E.YBBRLB(+)
/


-- End of DDL Script for View SD_HOSPITAL.VW_ZY_ZHXXTJ