-- Start of DDL Script for View SD_HOSPITAL.VW_ZY_BRXXB0
-- Generated 24-2月-2017 19:13:23 from SD_HOSPITAL@ZHZYYGL

CREATE OR REPLACE VIEW vw_zy_brxxb0 (
   zyid00,
   brid00,
   brblh0,
   mrn000,
   zyh000,
   brrycs,
   ybzyh0,
   ybzycs,
   xm0000,
   xb0000,
   csrq00,
   brnl00,
   fbbh00,
   fbmc00,
   lbbh00,
   ryrq00,
   rysj00,
   ryrqsj,
   ryczy0,
   czyxm0,
   ryzd00,
   ryzdmc,
   rysqk0,
   rylb00,
   zymd00,
   ryfs00,
   rybq00,
   rybqmc,
   rzks00,
   rzksmc,
   rycwh0,
   pxcwh0,
   dqbq00,
   dqbqmc,
   dqks00,
   dqksmc,
   rcyfh0,
   cyrq00,
   cysj00,
   cyrqsj,
   cyzdmc,
   cyczy0,
   cyygxm,
   zzys00,
   zzysxm,
   zrys00,
   zrysxm,
   sxys00,
   sxysxm,
   hljbid,
   hljbmc,
   brzt00,
   brztmc,
   cydjrq,
   cydjsj,
   szys00,
   szysxm,
   yblb00,
   ybmc00,
   ybbhsb,
   sfxnh0,
   ybzxlb,
   ybbrlb,
   ssmc00,
   shjg00,
   cyxj00,
   gsdjh0,
   ybkh00,
   ybqh00,
   yksh00,
   zkjcbz,
   sjcyrq,
   cyjsrq,
   cyjssj,
   sjryrq,
   sjrysj,
   jzrqsj,
   cslx00,
   cslxmc,
   shbzh0,
   fyhjje,
   yszid0,
   dqljid,
   yylbid,
   yykbrq,
   qkdjbh,
   dblb00,
   dblbmc,
   blcjrq,
   cyczrq,
   sjcysj,
   zghs00,
   zghsxm,
   zrhlz0,
   ybid00,
   zhzye0,
   brlbmc,
   fbxx00,
   pysm00,
   wbsm00,
   sfyxjs,
   sqid00,
   sqmc00,
   cwzgbz,
   ewm001,
   fmbqbh,
   zrzybz,
   rylbbz,
   dbzbm0,
   dbzmc0,
   zfy000,
   yjj000 )
AS
select
  a.ZYID00,
  a.BRID00,
  b.BRBLH0,
  a.MRN000,
  a.ZYH000,
  a.BRRYCS,
  a.YBZYH0,
  a.YBZYCS,
  a.XM0000,
  a.XB0000,
  a.CSRQ00,
  substr(SF_BQ_CSRQTONL(a.CSRQ00),1,10) as BRNL00 ,  
  a.FBBH00,
  (select FBMC00 from BM_BRFBB0 where FBBH00 = a.FBBH00) as FBMC00,   --费别名称
  a.LBBH00,
  a.RYRQ00,
  a.RYSJ00,
  a.RYRQ00||' '||a.RYSJ00 as RYRQSJ,
  a.RYCZY0,
  a.CZYXM0,
  a.RYZD00,
  a.RYZDMC,
  a.RYSQK0,
  a.RYLB00,
  a.ZYMD00,
  a.RYFS00,
  a.RYBQ00,
  (select BMMC00 from BM_BMBM00 where BMBH00 = a.RYBQ00) as RYBQMC,  --入院病区名称
  a.RZKS00,
  (select BMMC00 from BM_BMBM00 where BMBH00 = a.RZKS00) as RZKSMC,  --入住科室名称
  a.RYCWH0,
  substr(SF_XT_GETPXCWH(a.RYCWH0),1,10) as PXCWH0 ,                  --排序用床位号  
  a.DQBQ00,
  (select BMMC00 from BM_BMBM00 where BMBH00 = a.DQBQ00) as DQBQMC,  --当前病区名称
  a.DQKS00,
  (select BMMC00 from BM_BMBM00 where BMBH00 = a.DQKS00) as DQKSMC,  --当前科室名称
  a.RCYFH0,
  a.CYRQ00,
  a.CYSJ00,
  a.CYRQ00||' '||a.CYSJ00 as CYRQSJ,
  (select ZDMC00 from BQ_BRZDXX where ZYID00=a.ZYID00 and ZDLB00='3' and rownum=1) as CYZDMC, --出院诊断
  a.CYCZY0,
  a.CYYGXM,
  a.ZZYS00,
  a.ZZYSXM,
  a.ZRYS00,
  a.ZRYSXM,
  a.SXYS00,
  a.SXYSXM,
  a.HLJBID,
  (select ZLXMJC from BM_ZLZD00 where ZLXMID = a.HLJBID and rownum = 1) as HLJBMC,  --护理级别名称
  a.BRZT00,
  decode(substr(a.BRZT00,1,1),'0','预约病人','1','入院登记','2','在院病人','3','出院登记','4','正常出院','5','出院欠费','6','医保年度结算病人','8','出院已审批','9','取消入院') as BRZTMC,
  a.CYDJRQ,
  a.CYDJSJ,
  a.SZYS00,
  (select ZWXM00 from BM_YGBM00 where YGBH00 = a.SZYS00 and rownum = 1) as SZYSXM,  --收治医生姓名
  a.YBLB00,
  c.YBMC00,  --医保名称
  nvl(c.YBBHSB,'N') as YBBHSB,  --医保包含商保
  nvl(c.SFXNH0,'0') as SFXNH0,  --是否新农合
  a.YBZXLB,
  a.YBBRLB,
  a.SSMC00,
  a.SHJG00,
  a.CYXJ00,
  a.GSDJH0,
  a.YBKH00,
  a.YBQH00,
  a.YKSH00,
  a.ZKJCBZ,
  a.SJCYRQ,
  a.CYJSRQ,
  a.CYJSSJ,
  a.SJRYRQ,
  a.SJRYSJ,
  a.SJRYRQ||' '||a.SJRYSJ as JZRQSJ,
  a.CSLX00,                                                   --农合传送类型,1实时结算,0传明细
  decode(a.CSLX00,'0','传明细','1','实时结算','') as CSLXMC, 
  a.SHBZH0,
  a.FYHJJE,
  a.YSZID0,
  a.DQLJID,
  a.YYLBID,
  a.YYKBRQ,
  a.QKDJBH,
  a.DBLB00,
  a.DBLBMC,
  a.BLCJRQ,
  a.CYCZRQ,
  a.SJCYSJ,
  a.ZGHS00,
  (select ZWXM00 from BM_YGBM00 where YGBH00 = a.ZGHS00) as ZGHSXM,  --主管护士姓名
  a.ZRHLZ0,  --责任护理组
  b.YBID00,
  (select nvl(sum(ZYE000-TYJE00),0) from ZY_BRZHXX  where ZYID00=a.ZYID00 and SFDJ00='0' ) as ZHZYE0, --账户余额
  d.BRLBMC,
  decode(c.YBJGJC,d.BRLBJC,c.YBJGJC,c.YBJGJC||'('||d.BRLBJC||')') FBXX00,  --费别信息
  substr(SF_XT_GETPYSM(a.XM0000),1,10) as PYSM00,   --拼音首码
  substr(SF_XT_GETWBSM(a.XM0000),1,10) as WBSM00,   --五笔首码  
  nvl(SFYXJS,'1') as SFYXJS,                        --是否允许结算,‘0’护士不允许 ‘1’允许 ‘2’允许患者提前结算
  a.SQID00,                                         --社区服务中心ID
  (select SQMC00 from BM_SQZD00 where SQID00=a.SQID00) SQMC00,  --社区服务中心名称
  d.CWZGBZ,   --床位最高标准
  a.ZYID00 EWM001,   --碗带二维码
  a.FMBQBH,   --生育分娩病情编号
  a.ZRZYBZ,   --择日住院标志: 0非择日,1择日住院,2择日转正式住院
  c.RYLBBZ,   --入院类别标志：1：传值 01，02，03..;2：传普通，家庭病床 3和空:人民医院模式，默认为3
  a.DBZBM0,   --单病种编码
  a.DBZMC0,   --单病种名称,
      (select wjsje0 from ZY_BRZHXX  where ZYID00=a.ZYID00 and SFDJ00='0' ) as ZFY000,  --总费用
 (select nvl(sum(ZYE000-TYJE00+wjsje0),0) from ZY_BRZHXX  where ZYID00=a.ZYID00 and SFDJ00='0' ) as YJJ000 --预交金总额
  from ZY_BRXXB0 a,BM_BRXXB0 b,IC_YBBRLB c,BM_YBBRLB d
  where  a.BRID00+0 = b.BRID00
    and  a.YBLB00 = c.YBLB00
    and  a.FBBH00 = c.FBBH00  
    and  a.FBBH00 = d.FBBH00
    and  a.YBBRLB = d.YBBRLB
    and  c.YBZXLB = d.YBZXLB
    and  a.RYRQ00 >= '19000101'
/


-- End of DDL Script for View SD_HOSPITAL.VW_ZY_BRXXB0