CREATE OR REPLACE VIEW VW_JY_BBCJXX AS select a.JYYWID , --流水ID:SEQ_JY_YW0000_LSID00 a.YJDJH0 , --医技单据流水号 a.YJFZH0 , -- a.ZYGHID , --住院挂号ID a.MZZYBZ , -- a.BRLX00 , -- a.ZYHGHH , -- a.BRXM00 , -- a.YEXM00 , --婴儿姓名 a.CH0000 , -- a.SZKS00 , -- a.XB0000 , -- a.XBMC00 , --性别名称 a.BRID00 , -- a.ZDID00 , --诊断正文 a.ZDMC00 , a.BBID00 , --标本ID a.BBMC00 , -- a.BBSDRQ , --标本收到日期 a.BBSDSJ , --标本收到时间 a.BBCYRQ , --标本采样日期 a.BBCYSJ , --标本采样时间 a.SQRQ00 , --申请日期 a.SQSJ00 , --申请时间 a.SQKS00 , --申请科室 a.SQKSMC , --申请科室名称 a.SQBQ00 , a.SQBQMC , a.SQYS00 , --申请医生 a.SQYSXM , --申请医生名称 a.PYRQ00 , --执行日期 a.PYSJ00 , --执行时间 a.JGZT00 , --结果状态 a.THYY00 , --退回原因 a.CBGRQ0 , --出报告日期 a.CBGSJ0 , --出报告时间 a.BGR000 , --报告人(报告签发者或检验操作者) a.BGRXM0 , --报告人姓名 a.BGJYR0 , --报告人结果校对者 a.JYRXM0 , --报告结果校对者姓名 a.BGDYBZ , --报告打印标志 0-未打印 1-已打印 a.JYDLY0 , --检验单来源 (0 :医生开具, 1:检验科开... )(目的:检验单,是否允许修改) a.YBH000 , --检验样本号(主要和检验仪器相对应)如:LA0011 --同一天里的同一台机器的样本号不能相同 a.YBHBBZ , --样本合并标志'0'表示此次的检验结果 --不参与合并,'1':表示可以参与合并 a.SFBZ00 , a.ZJE000 , a.TXM000 , --条形码 a.MZKID0 , (select JYMXID from JY_YWMX00 where JYYWID=a.JYYWID and ZLXMID=A.ZLXMID and rownum=1) JYMXID , a.ZLXMID , (select ZLXMJC from BM_ZLZD00 where ZLXMID=a.ZLXMID) ZLXMJC , DECODE((select TDTXM0 from VM_JY_TXMDY0 where ZLXMID=A.ZLXMID and ((A.MZZYBZ=QZSYFW) or (QZSYFW='3')) and rownum=1),null, (select YBSM00 from VM_JY_TXMDY0 where ZLXMID=A.ZLXMID and ((A.MZZYBZ=QZSYFW) or (QZSYFW='3')) and rownum=1), (select B.YBSM00 from JY_TXMLB0 B,VM_JY_TXMDY0 C where B.TXMQZ0=C.TDTXM0 and c.zlxmid=A.zlxmid and rownum=1)) BBSM00 , --标本说明(标本采集的条件说明，如饭前、饭后一小时等) DECODE((select TDTXM0 from VM_JY_TXMDY0 where ZLXMID=A.ZLXMID and ((A.MZZYBZ=QZSYFW) or (QZSYFW='3')) and rownum=1),null, (select YBTJ00 from VM_JY_TXMDY0 where ZLXMID=A.ZLXMID and ((A.MZZYBZ=QZSYFW) or (QZSYFW='3')) and rownum=1 ), (select B.YBTJ00 from JY_TXMLB0 B,VM_JY_TXMDY0 C where B.TXMQZ0=C.TDTXM0 and c.zlxmid=A.zlxmid and rownum=1)) bbcjl0, DECODE((select TDTXM0 from VM_JY_TXMDY0 where ZLXMID=A.ZLXMID and ((A.MZZYBZ=QZSYFW) or (QZSYFW='3')) and rownum=1),null, (select TXMQZ0 from VM_JY_TXMDY0 where ZLXMID=A.ZLXMID and ((A.MZZYBZ=QZSYFW) or (QZSYFW='3')) and rownum=1), (select B.TXMQZ0 from JY_TXMLB0 B,VM_JY_TXMDY0 C where B.TXMQZ0=C.TDTXM0 and c.zlxmid=A.zlxmid and rownum=1)) TXMQZ0, DECODE((select TDTXM0 from VM_JY_TXMDY0 where ZLXMID=A.ZLXMID and ((A.MZZYBZ=QZSYFW) or (QZSYFW='3')) and rownum=1),null, (select TXMMC0 from VM_JY_TXMDY0 where ZLXMID=A.ZLXMID and ((A.MZZYBZ=QZSYFW) or (QZSYFW='3')) and rownum=1), (select B.TXMMC0 from JY_TXMLB0 B,VM_JY_TXMDY0 C where B.TXMQZ0=C.TDTXM0 and c.zlxmid=A.zlxmid and rownum=1)) TXMMC0, DECODE((select TDTXM0 from VM_JY_TXMDY0 where ZLXMID=A.ZLXMID and ((A.MZZYBZ=QZSYFW) or (QZSYFW='3')) and rownum=1),null, (select TXMMS0 from VM_JY_TXMDY0 where ZLXMID=A.ZLXMID and ((A.MZZYBZ=QZSYFW) or (QZSYFW='3')) and rownum=1), (select B.TXMMS0 from JY_TXMLB0 B,VM_JY_TXMDY0 C where B.TXMQZ0=C.TDTXM0 and c.zlxmid=A.zlxmid and rownum=1)) TXMMS0, DECODE((select TDTXM0 from VM_JY_TXMDY0 where ZLXMID=A.ZLXMID and ((A.MZZYBZ=QZSYFW) or (QZSYFW='3')) and rownum=1),null, (select TXMYSZ from VM_JY_TXMDY0 where ZLXMID=A.ZLXMID and ((A.MZZYBZ=QZSYFW) or (QZSYFW='3')) and rownum=1), (SELECT B.TXMYSZ FROM JY_TXMLB0 B,VM_JY_TXMDY0 C where B.TXMQZ0=C.TDTXM0 and c.zlxmid=A.zlxmid and rownum=1)) TXMYSZ, DECODE((select TDTXM0 from VM_JY_TXMDY0 where ZLXMID=A.ZLXMID and ((A.MZZYBZ=QZSYFW) or (QZSYFW='3')) and rownum=1),NULL, (select SFYTM0 from VM_JY_TXMDY0 where ZLXMID=A.ZLXMID and ((A.MZZYBZ=QZSYFW) or (QZSYFW='3')) and rownum=1), (select B.SFYTM0 from JY_TXMLB0 B,VM_JY_TXMDY0 C where B.TXMQZ0=C.TDTXM0 and c.zlxmid=A.zlxmid and rownum=1)) SFYTM0, a.SFTMWK , a.YXBZ00 , a.CJBZ00 , a.SFTJ00 , a.QDXH00 , a.TDJTTM , a.QZMC00 , a.TMSFSF, a.YJKSBH, --医技科室编号 a.SFXWFS --是否向外发送 from VW_JY_YW0000 a where a.zlxmid<>99999999 and NVL(A.TXM000,'0')='0' and (((not exists (select 'y' from yj_yw0000 d where d.cxdjh0=a.yjdjh0 and d.zyghid=a.ZYGHID )) and exists (select 'y' from yj_yw0000 e where e.yjdjh0=a.yjdjh0 and ((e.cxdjh0=0) or (e.cxdjh0 is null) or e.sl0000>0))) or (exists (select 1 from JY_yw0000 e where a.jyywid=e.jyywid and JJBZ00='GGTJ'))) union all select a.JYYWID , --流水ID:SEQ_JY_YW0000_LSID00 a.YJDJH0 , --医技单据流水号 a.YJFZH0 , -- a.ZYGHID , --住院挂号ID a.MZZYBZ , -- a.BRLX00 , -- a.ZYHGHH , -- a.BRXM00 , -- a.YEXM00 , --婴儿姓名 a.CH0000 , -- a.SZKS00 , -- a.XB0000 , -- a.XBMC00 , --性别名称 a.BRID00 , -- a.ZDID00 , --诊断正文 a.ZDMC00 , a.BBID00 , --标本ID a.BBMC00 , -- a.BBSDRQ , --标本收到日期 a.BBSDSJ , --标本收到时间 a.BBCYRQ , --标本采样日期 a.BBCYSJ , --标本采样时间 a.SQRQ00 , --申请日期 a.SQSJ00 , --申请时间 a.SQKS00 , --申请科室 a.SQKSMC , --申请科室名称 a.SQBQ00 , a.SQBQMC , a.SQYS00 , --申请医生 a.SQYSXM , --申请医生名称 a.PYRQ00 , --执行日期 a.PYSJ00 , --执行时间 a.JGZT00 , --结果状态 a.THYY00 , --退回原因 a.CBGRQ0 , --出报告日期 a.CBGSJ0 , --出报告时间 a.BGR000 , --报告人(报告签发者或检验操作者) a.BGRXM0 , --报告人姓名 a.BGJYR0 , --报告人结果校对者 a.JYRXM0 , --报告结果校对者姓名 a.BGDYBZ , --报告打印标志 0-未打印 1-已打印 a.JYDLY0 , --检验单来源 (0 :医生开具, 1:检验科开... )(目的:检验单,是否允许修改) a.YBH000 , --检验样本号(主要和检验仪器相对应)如:LA0011 --同一天里的同一台机器的样本号不能相同 a.YBHBBZ , --样本合并标志'0'表示此次的检验结果 --不参与合并,'1':表示可以参与合并 a.SFBZ00 , a.ZJE000 , a.TXM000 , --条形码 a.MZKID0 , (select JYMXID from JY_YWMX00 where JYYWID=a.JYYWID and ZLXMID=A.ZLXMID and rownum=1) JYMXID , a.ZLXMID , (select ZLXMJC from BM_ZLZD00 where ZLXMID=a.ZLXMID) ZLXMJC , (select YBSM00 from JY_TXMLB0 where TXMQZ0=substr(A.TXM000,1,2)) BBSM00 , --标本说明(标本采集的条件说明，如饭前、饭后一小时等) (select YBTJ00 from JY_TXMLB0 where TXMQZ0=substr(A.TXM000,1,2)) bbcjl0 , --标本采集量 (select TXMQZ0 from JY_TXMLB0 where TXMQZ0=substr(A.TXM000,1,2)) TXMQZ0 , (select TXMMC0 from JY_TXMLB0 where TXMQZ0=substr(A.TXM000,1,2)) TXMMC0 , (select TXMMS0 from JY_TXMLB0 where TXMQZ0=substr(A.TXM000,1,2)) TXMMS0 , (select TXMYSZ from JY_TXMLB0 where TXMQZ0=substr(A.TXM000,1,2)) TXMYSZ , (select SFYTM0 from JY_TXMLB0 where TXMQZ0=substr(A.TXM000,1,2)) SFYTM0 , a.SFTMWK , a.YXBZ00 , a.CJBZ00 , a.SFTJ00 , a.QDXH00 , a.TDJTTM , a.QZMC00 , a.TMSFSF, a.YJKSBH, --医技科室编号 a.SFXWFS --是否向外发送 from VW_JY_YW0000 a WHERE a.zlxmid<>99999999 and NVL(A.TXM000,'0')<>'0' and (((not exists (select 'y' from yj_yw0000 d where d.cxdjh0=a.yjdjh0 and d.zyghid=a.ZYGHID)) and exists (select 'y' from yj_yw0000 e where e.yjdjh0=a.yjdjh0 and ((e.cxdjh0=0) or (e.cxdjh0 is null) or e.sl0000>0))) or (exists (select 1 from JY_yw0000 e where a.jyywid=e.jyywid and JJBZ00='GGTJ')));