CREATE OR REPLACE VIEW SD_HOSPITAL.VW_BJJHMK_BRLDXX
 (
 PATIENT_ID, ZYID00, VISIT_ID, ORIGIN_DEPT_CODE, ORIGIN_WARD_CODE, ORIGIN_BED_NO, 
CURR_DEPT_CODE, CURR_WARD_CODE, CURR_BED_NO, LOG_DATE_TIME

 )
 AS 
select b.BRID00       ,       
       b.ZYID00       ,
       b.BRRYCS       ,
       a.KSH000       ,           --转出科室
       a.BQH000       ,           --转出病区
       a.CH0000       ,           --转出床号
       --(select KSH000 from BQ_BRLDXX where ZYID00=a.ZYID00 and (JSZT00 <>'请假' or jszt00 is null) and QSRQ00||QSSJ00>=a.QSRQ00||a.QSSJ00 and ROWnum=1),   --转入科室
       --(select BQH000 from BQ_BRLDXX where ZYID00=a.ZYID00 and (JSZT00 <>'请假' or jszt00 is null) and QSRQ00||QSSJ00>=a.QSRQ00||a.QSSJ00 and ROWnum=1),   --转入病区
       --(select CH0000 from BQ_BRLDXX where ZYID00=a.ZYID00 and (JSZT00 <>'请假' or jszt00 is null) and QSRQ00||QSSJ00>=a.QSRQ00||a.QSSJ00 and ROWnum=1),   --转入床号
      (select KSH000 from (select * from BQ_BRLDXX where (JSZT00 <>'请假' or jszt00 is null) order by QSRQ00||QSSJ00 ) x
       where  x.ZYID00=a.ZYID00 and x.QSRQ00||x.QSSJ00>=a.jSRQ00||a.jSSJ00 and  ROWnum=1), --转入科室
       (select BQH000 from (select * from BQ_BRLDXX where (JSZT00 <>'请假' or jszt00 is null) order by QSRQ00||QSSJ00 ) x
      where  x.ZYID00=a.ZYID00 and x.QSRQ00||x.QSSJ00>=a.jSRQ00||a.jSSJ00 and  ROWnum=1), --转入病区
      (select CH0000 from (select * from BQ_BRLDXX where (JSZT00 <>'请假' or jszt00 is null) order by QSRQ00||QSSJ00  ) x
       where  x.ZYID00=a.ZYID00 and x.QSRQ00||x.QSSJ00>=a.jSRQ00||a.jSSJ00 and  ROWnum=1), --转入床号
       --2012.10.11 
      to_date(a.JSRQ00||a.JSSJ00,'YYYYMMDD HH24:MI:SS')    --转科时间
        from BQ_BRLDXX a,ZY_BRXXB0 b where a.ZYID00=b.ZYID00 and  (a.JSZT00 ='转出' or a.JSZT00 ='换出' or a.JSZT00 ='出科' )
       --2012.10.11 '出科' 为增加区内转科记录。
        order by QSRQ00||QSSJ00
/