PROMPT 49. 病历未提交病人(VW_ZS_WTJBR0)
CREATE OR REPLACE VIEW VW_ZS_WTJBR0(
	ZYID00,BRID00,MRN000,ZYH000,YBZYH0,XM0000,
	XB0000,CSRQ00,FBBH00,LBBH00,RYRQ00,RYSJ00,
	RYCZY0,CZYXM0,RYZD00,RYSQK0,RYLB00,RYBQ00,
	RZKS00,RYCWH0,DQBQ00,DQKS00,RCYFH0,CYRQ00,
	CYSJ00,CYCZY0,CYYGXM,ZZYS00,ZZYSXM,ZRYS00,
	ZRYSXM,SXYS00,SXYSXM,HLJBID,BRRYCS,BRZT00,
	BRHYZK,BRZY00,BRMZ00,BRJTDZ,BRYB00,TYDWMC,
	BRNL00,BRGJ00,YBZXLB,YBBRLB,BRLBJC,KYJE00,
	FBMC00,BJJE00,YKZYE0,YKSH00)
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- YANGY      2008.10.30   增加原科室号
-- qks        2009.11.12   增加条件T2.FBBH00=C.FBBH00
AS
SELECT  T1.ZYID00,T1.BRID00,T1.MRN000,T1.ZYH000,T1.YBZYH0,T1.XM0000,
	T1.XB0000,TO_DATE(T1.CSRQ00,'YYYYMMDD'),T1.FBBH00,T1.LBBH00,TO_DATE(T1.RYRQ00,'YYYYMMDD'),T1.RYSJ00,
	T1.RYCZY0,T1.CZYXM0,T1.RYZD00,T1.RYSQK0,T1.RYLB00,T1.RYBQ00,
	T1.RZKS00,T1.RYCWH0,T1.DQBQ00,T1.DQKS00,T1.RCYFH0,T1.CYRQ00,
	T1.CYSJ00,T1.CYCZY0,T1.CYYGXM,T1.ZZYS00,T1.ZZYSXM,T1.ZRYS00,
	T1.ZRYSXM,T1.SXYS00,T1.SXYSXM,T1.HLJBID,T1.BRRYCS,T1.BRZT00,
	T2.BRHYZK,T2.BRZY00,T2.BRMZ00,T2.BRJTDZ,T2.BRYB00,T2.TYDWMC,
	TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))-TO_NUMBER(SUBSTR(T1.CSRQ00,1,4)),T2.BRGJ00,
	C.YBZXLB,T2.YBBRLB,D.BRLBJC,SF_BQ_KYJE00(0,T2.BRID00),
	(select F.FBMC00 from BM_BRFBB0 F where F.FBBH00 = T2.FBBH00),
	(select E.BJJE00 from ZY_BRZHXX E where E.BRID00 = T2.BRID00 and E.JFLBID = 2),
	(select E.YKZYE0+YBYZF0+YXQFJE from ZY_BRZHXX E where E.BRID00 = T2.BRID00 and E.JFLBID = 2),
	T1.YKSH00  --原科室号
FROM ZY_BRXXB0 T1,BM_BRXXB0 T2,IC_YBBRLB C,BM_YBBRLB D
WHERE T1.BRID00= T2.BRID00 AND C.YBLB00 = T2.YBLB00 AND
      D.YBBRLB = T2.YBBRLB AND D.YBZXLB = C.YBZXLB AND
      T2.FBBH00=C.FBBH00
/