PROMPT 1 .检验单信息(VW_JY_YW0000)
create or replace view VW_JY_YW0000
as
select
	JYYWID   ,	--流水ID:SEQ_JY_YW0000_LSID00
	YJDJH0   ,	--医技单据号 
	YJFZH0   ,	--
	ZLXMID   ,	--
	(SELECT ZLXMJC FROM BM_ZLZD00 WHERE ZLXMID=A.ZLXMID) ZLXMJC	,
	(select zyzbid from bm_zlzd00 where zlxmid=A.ZLXMID) zyzbid	, 
 	ZYGHID   ,	--住院挂号ID  
 	ZYHGHH   , --住院(ZYH000)或门诊(GHH000)号(冗余)
  MZZYBZ   , --'0'为门诊'1'为住院
  SFTJ00   , --是否体检
  DECODE(MZZYBZ,'0','门诊','住院') BRLX00	,	--病人类型
	(SELECT BMMC00 FROM SF_BRXXB0 T1,BM_BMBM00 T2 WHERE A.ZYGHID=T1.GHID00 AND A.MZZYBZ='0' AND T1.GHKS00=T2.BMBH00)||
	(SELECT BMMC00 FROM ZY_BRXXB0 T1,BM_BMBM00 T2 WHERE A.ZYGHID=T1.ZYID00 AND A.MZZYBZ='1' AND T1.DQKS00=T2.BMBH00) SZKS00,   
 	A.BRID00  ,       --病人ID (冗余) 
 	(SELECT SUBSTR(ICKH00,1,100) FROM IC_YHXX00 WHERE BRID00=A.BRID00 AND ZT0000='1' AND ROWNUM = 1) ICKH00,
 	(SELECT BRBLH0 FROM BM_BRXXB0 WHERE BRID00=A.BRID00) BRBLH0	,	--病人病历号
 	(SELECT FBBH00 FROM BM_BRXXB0 WHERE BRID00=A.BRID00) FBBH00	,	--
 	(SELECT FBMC00 from BM_BRFBB0 B,BM_BRXXB0 C where B.FBBH00=C.FBBH00 AND C.BRID00=A.BRID00) FBMC00	,	--
	A.BRXM00  , 	--病人姓名(冗余)
	XB0000   	, 	--性别(冗余)
	DECODE(XB0000,'M','男','F','女','') XBMC00	,	--性别名称
	CSRQ00   	, 	--出生日期(冗余) 	
	--NL0000 , 	--年龄(冗余)
	TO_NUMBER(SUBSTR(SUBSTR(SF_JY_CSRQTONL(CSRQ00),1,LENGTH(SF_JY_CSRQTONL(CSRQ00)) -1),1,5)) NL0000 ,  --年龄
	NLDW00		, 	--年龄单位       	
	DECODE(NLDW00,'Y','岁','M','月','D','天','') NLDWMC,	
	CH0000		, 	--*床号(冗余)  
 	YBH000    , 	--检验样本号
 	YXBZ00		,   --优先标志 0-普通 1-紧急  	
	ZDID00   	, 	--诊断内部码(对应XT_ICD900.ICD900)
	SUBSTR(SF_JY_GET_ZDMC00(A.YJDJH0,A.ZDID00),1,50) ZDMC00	,	--诊断名称
	SLJDDM    ,   --病人状态 PK:JY_SLJDZD.SLJDDM
	(SELECT SLJDMC FROM JY_SLJDZD WHERE SLJDDM=A.SLJDDM) SLJDMC	,	--生理阶段名称
	BBID00		,	  --标本ID
	(SELECT BBMC00 FROM BM_BBZD00 WHERE BBID00=A.BBID00) BBMC00	,	--标本名称
	BBSM00		,	  --标本说明(标本采集的条件说明，如饭前、饭后一小时等)        
	BBCJL0    ,   --标本采集量
	BBCJR0    ,   --标本采集人
	(SELECT ZWXM00 FROM BM_YGBM00 WHERE YGBH00=A.BBCJR0) CJRXM0	,	--采集人姓名
	BBSDR0    ,   --标本采集人
	(SELECT ZWXM00 FROM BM_YGBM00 WHERE YGBH00=A.BBSDR0) SDRXM0	,	--收到人姓名
	BBSDRQ		,	  --标本收到日期(即样本签收日期)
  BBSDSJ		,   --标本收到时间
	substr(nvl(BBSDRQ,To_char(sysdate,'YYYYMMDD')),1,4)||'.'||
	substr(nvl(BBSDRQ,To_char(sysdate,'YYYYMMDD')),5,2)||'.'||
	substr(nvl(BBSDRQ,To_char(sysdate,'YYYYMMDD')),7,2)||' '||SUBSTR(BBSDSJ,1,5) SDRQSJ	,
	BBCYRQ		,	  --标本采样日期
	substr(nvl(BBCYRQ,To_char(sysdate,'YYYYMMDD')),1,4)||'.'||
	substr(nvl(BBCYRQ,To_char(sysdate,'YYYYMMDD')),5,2)||'.'||
	substr(nvl(BBCYRQ,To_char(sysdate,'YYYYMMDD')),7,2) CYRQ00,
  BBCYSJ		,   --标本采样时间
	substr(nvl(BBCYRQ,To_char(sysdate,'YYYYMMDD')),1,4)||'.'||
	substr(nvl(BBCYRQ,To_char(sysdate,'YYYYMMDD')),5,2)||'.'||
	substr(nvl(BBCYRQ,To_char(sysdate,'YYYYMMDD')),7,2)||' '||BBCYSJ CYRQSJ	,	--采样日期时间
  SQRQ00    ,   --申请日期
  substr(SQRQ00,1,4)||'.'||substr(SQRQ00,5,2)||'.'||substr(SQRQ00,7,2) SQ0000,
  SQSJ00    ,   --申请时间
  SQKS00    ,   --申请科室 
  (SELECT BMMC00 FROM BM_BMBM00 WHERE BMBH00=A.SQKS00) SQKSMC,	--申请科室名称
  SQBQ00    ,   --申请病区
  (SELECT BMMC00 FROM BM_BMBM00 WHERE BMBH00=A.SQBQ00) SQBQMC,  --申请病区名称 
	SQYS00    ,   --申请医生
	DECODE(SQYS00,0,NULL,(SELECT ZWXM00 FROM BM_YGBM00 WHERE YGBH00=A.SQYS00)) SQYSXM	,	--申请医生姓名
	PYRQ00   	, 	--排样日期
	DECODE(PYRQ00,null,null,substr(PYRQ00,1,4)||'.'||substr(PYRQ00,5,2)||'.'||substr(PYRQ00,7,2)) PY0000,  	
	PYSJ00   	, 	--排样时间	
	BBSDRQ||YBH000 WYYBH0   ,    
	JGZT00 		,	  --结果状态          
  DECODE(JGZT00,'0','已申请','1','已采集','2','已签收','3','已退回','4','已排样','5','已处理','6','已审核','7','已删除','8','已打印','9','已取单','未知') JGZTMC,
	THYY00		,	  --退回原因
	CXSHYY		,	  --撤销审核原因
	CBGRQ0    ,   --出报告日期
	DECODE(CBGRQ0,null,null,substr(CBGRQ0,1,4)||'.'||substr(CBGRQ0,5,2)||'.'||substr(CBGRQ0,7,2)||' '||CBGSJ0) BG0000,  	
	CBGSJ0    ,   --出报告时间	
	BGR000    ,   --报告人(报告签发者或检验操作者)
	(SELECT ZWXM00 FROM BM_YGBM00 WHERE YGBH00=A.BGR000) BGRXM0	,	--报告人姓名		
	BGJYR0    ,   --报告人结果校对者
	(SELECT ZWXM00 FROM BM_YGBM00 WHERE YGBH00=A.BGJYR0) JYRXM0	,	--校对者姓名
	(SELECT DZQM00 FROM BM_ZJ0000 WHERE YGBH00=A.BGJYR0 AND A.MZZYBZ='0') DZQM00	,	--电子签名
	SF_JY_GET_ZJE000(A.YJDJH0,A.ZLXMID) ZJE000	,
	SFBZ00    ,	  --计价标志(0-未计价 1-已计价，收费程序使用)          	
	DECODE(SFBZ00,'0','未计价','1','已计价','2','已收费','3','已执行','未知') SFBZMC,  
	BGDYBZ 		,	  --报告打印标志 0-未打印 1-已打印
	TXM000 		,	  --条形码 
	DECODE(TXM000,'','0000000000',TXM000) TXM001 ,--报告条形码
	SUBSTR(TXM000,1,2) TXMQZ0	,	--条形码前缀
	(SELECT TXMYSZ FROM JY_TXMLB0 WHERE TXMQZ0=SUBSTR(TXM000,1,2)) TXMYSZ	,	--条形码颜色值
  YBLX00		,
	JYDLY0    ,   --检验单来源 (0 :医生开具, 1:检验科开... )(目的:检验单,是否允许修改)
	DYBZ00		,
	YBHBBZ    ,   --样本合并标志'0'表示此次的检验结果,不参与合并,'1':表示可以参与合并
	MZKID0		,	
	YQDH00		,
	(SELECT TYDWMC FROM BM_BRXXB0 WHERE BRID00=A.BRID00) TYDWMC		,
	DYBZ00||DECODE((SELECT TRIM(VALUE0) FROM XT_XTCS00 WHERE NAME00='JY_SFDYDWMC'),'N',NULL,
  DECODE((SELECT TYDWMC FROM BM_BRXXB0 WHERE BRID00=A.BRID00),NULL,NULL,'单位: '||(SELECT TYDWMC||BRDH00 FROM BM_BRXXB0 WHERE BRID00=A.BRID00))) BZDWMC,
	LRY000    ,       --录入医生
	(SELECT ZWXM00 FROM BM_YGBM00 WHERE YGBH00=A.LRY000) LRYXM0,--录入医生姓名	
	BGCKR0		,
	(SELECT ZWXM00 FROM BM_YGBM00 WHERE YGBH00=A.BGCKR0) CKRXM0,--
	BGCKSJ		,
	SGCJQZ    ,
	( SELECT TMSFWK FROM BM_ZLZD00 WHERE ZLXMID=A.ZLXMID) SFTMWK,--采集时条码是否为空		
	KWH000	 ,
	CJDYRQ   ,
	CJBZ00   ,
	BBBZ00   , --标本备注  --jvs
	(SELECT ZJE000 FROM YJ_YW0000 WHERE YJDJH0=A.YJDJH0) SFJE00,
	QDXH00,
	(SELECT SJ0000 FROM JY_JYYQZD WHERE YQDH00 = A.YQDH00) SJ0000,--试剂
	TDJTTM	 ,
	SFJM00   ,
	SFYB00   ,
	CZY000   , --操作员
	(SELECT ZWXM00 FROM BM_YGBM00 WHERE YGBH00=A.CZY000) CZYXM0,	--操作员姓名
	(SELECT YJKSBH FROM YJ_YW0000 WHERE YJDJH0=A.YJDJH0) YJKSBH,
	DECODE(MZZYBZ,'0','2',(SELECT SUBSTR(BRZT00,1,1) FROM ZY_BRXXB0 WHERE ZYID00=A.ZYGHID)) BRZT00,
	(SELECT BGYSZ0 FROM JY_JYYQZD WHERE YQDH00=A.YQDH00) BGYSZ0,  --报告颜色值
  (SELECT QZMC00 FROM JY_QDXHQZ WHERE CJXQ00 = (SELECT to_char(to_date(B.BBCYRQ,'yyyymmdd'),'day') FROM JY_YW0000 B WHERE B.YJDJH0 = A.YJDJH0 AND ROWNUM = 1)) QZMC00	,  --取单前缀
  SFKY00,    --是否科研
  (select LCPY00 from JY_XJJGPY WHERE YBH000=A.YBH000 and PYRQ00=A.PYRQ00) LCPY00,  --临床评语,细菌	
  TMSFSF,
  NVL(DYCS00, 0) DYCS00,   --打印次数
  JJBZ00
from JY_YW0000 A;