select a.*
   from VW_LIS_YJYW00 a
  where a.KDRQ00 between to_char(to_date('20171001', 'YYYYMMDD') - 14, 'YYYYMMDD') and
      to_char(to_date('20171030', 'YYYYMMDD'), 'YYYYMMDD')
    and a.QSRQ00 between '20171001' and '20171030'
    and a.QSRQ00 || a.QSSJ00 >= '2017100100:00:00'
    and a.QSRQ00 || a.QSSJ00 <= '2017103023:59:59'
    and a.QSR000 = 2940
  order by a.QSRQ00 || a.QSSJ00



create or replace view vw_lis_yjyw00 as
select 
  A.ZYGHID, --
  A.BRID00, --
  A.BRXM00, --病人姓名                           
  A.XB0000, --性别  
  substr(SF_BQ_CSRQTONL(A.CSRQ00),1,10) NL0000, --年龄
  A.MZZYBZ, --门诊住院标志
  decode(A.MZZYBZ,'0','门诊','住院') BRLX00, --门诊住院名称
  A.ZYHGHH, --
  A.YJDJH0, --
  A.TXM000 DZTXM0, --
  A.DYRQTM, --
  decode(a.DYRQTM,null,'0','1') DYBZ00, --
  b.REQUISITION_ID TXM000, --条码  nvl(a.TXM000,b.REQUISITION_ID)
  A.ZLXMID, --诊疗项目ID
  nvl(decode(b.CHARGE_ITEM_ID_LIS,'LIS0082','胰岛素释放0','LIS0082A','胰岛素释放30','LIS0082B','胰岛素释放60','LIS0082C','胰岛素释放120','LIS0082D','胰岛素释放180',
         'LIS0056','葡萄糖0','LIS0036','葡萄糖测定','LIS0036A','葡萄糖30','LIS0036B','葡萄糖60','LIS0036C','葡萄糖120','LIS0036D','葡萄糖180',b.CHARGE_NAME),ZLXMJC) ZLXMJC,
  (select BBMC00 from BM_BBZD00 where BBID00=a.BBID00) BBMC00, --
  b.REQUISITION_STATE BBZT00, --
  decode(b.REQUISITION_STATE,'reported','报告完成','preinspect','对照完成','printed',
                 decode(A.DYRQTM,null,'条码生成','条码打印'),'canceled','条码撤销','state','声明','deleted','条码删除',
                 'returned','标本退回','inspecting','检验确认','samplinged','采集确认',
                 'received','接收确认','appended','项目追加','未定义')  BBZTMC, --
  A.ZXRQ00, --
  substr(A.ZXRQ00,3,2)||'-'||substr(A.ZXRQ00,5,2)||'-'||substr(A.ZXRQ00,7,2)||' '||A.ZXSJ00 ZXRQSJ,
  A.YJKSBH,
  (select BMMC00 from BM_BMBM00 where BMBH00=a.YJKSBH) YJKSMC,
  A.XMZT00,
  decode(A.XMZT00,'0','开单','1','计价','2','收费','3','执行','其它') XMZTMC,
  A.ZXR000,
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.ZXR000) ZXRXM0,
  A.KDKSBH, 
  (select BMMC00 from BM_BMBM00 where BMBH00=a.KDKSBH) KDKSMC, --开单科室
  A.KDYSXM, --开单医生姓名
  A.KDRQ00, --开单日期
  decode(A.KDRQ00, null, null,                                                                              
    substr(A.KDRQ00,3,2)||'-'||substr(A.KDRQ00,5,2)||'-'||substr(A.KDRQ00,7,2))||' '||substr(A.KDSJ00,1,5) KDRQSJ, --开单日期时间
  nvl(B.SAMPLING_POSITION,A.QSR000) QSR000, --签收人ID
  (select ZWXM00 from BM_YGBM00 where YGBH00 = nvl(B.SAMPLING_POSITION,A.QSR000)) QSRXM0, --签收人姓名
  decode(B.SAMPLING_TIME,null,A.QSRQ00,to_char(SAMPLING_TIME,'YYYYMMDD')) QSRQ00, --签收日期
  decode(B.SAMPLING_TIME,null,A.QSSJ00,to_char(SAMPLING_TIME,'HH24:MI:SS')) QSSJ00, --签收时间
  decode(B.SAMPLING_TIME,null, decode(A.QSRQ00, null, null,                                                                              
                     substr(A.QSRQ00,3,2)||'-'||substr(A.QSRQ00,5,2)||'-'||substr(A.QSRQ00,7,2))||' '||substr(A.QSSJ00,1,5),
         to_char(B.SAMPLING_TIME,'YYYY-MM-DD HH24:MI:SS')) QSRQSJ, --签收日期时间
  B.Remark JTXM00,  --旧条码
  (select XSXH00 from BM_JYZYZB t,BM_ZLZD00 y where t.ZYZBID=y.ZYZBID and y.ZLXMID=A.ZLXMID) XSXH00,
  A.QSPC00,
  A.ZJE000,
  A.SL0000,
  A.KDBQ00,
  (select BMMC00 from BM_BMBM00 where BMBH00=a.KDBQ00) KDBQMC,
  B.PATIENT_TYPE BRTYPE,
  A.BRPIC0,                    --病人照片相对路径
  A.JZBZ00                     --急诊标志
from YJ_YW0000 A, HIS_REQUISITION B --, BM_ZLZD00 C
where to_char(A.YJDJH0) = B.HIS_ID(+) --and A.ZLXMID = C.ZLXMID
  and A.ZJE000>0 and A.SL0000-A.CXSL00>0 --nvl(CXDJH0,0)=0 and not exists(select 1 from YJ_YW0000 where BRID00=a.BRID00 and CXDJH0=a.YJDJH0)
  and exists (select 1 from BM_ZLZD00 where ZLXMID=a.ZLXMID and LBBH00=4);