-- Start of DDL Script for View SD_HOSPITAL.VW_JY_YW0000
-- Generated 23-5月-2024 15:47:11 from SD_HOSPITAL@his

CREATE OR REPLACE VIEW vw_jy_yw0000 (
   jyywid,
   yjdjh0,
   yjfzh0,
   zlxmid,
   zlxmzh,
   zlxmjc,
   xxms00,
   zyzbid,
   zyghid,
   zyhghh,
   mzzybz,
   sftj00,
   brlx00,
   szks00,
   brid00,
   ickh00,
   brblh0,
   fbbh00,
   fbmc00,
   brxm00,
   yexm00,
   xb0000,
   xbmc00,
   csrq00,
   nl0000,
   nldw00,
   nldwmc,
   ch0000,
   ybh000,
   yxbz00,
   zdid00,
   zdmc00,
   sljddm,
   sljdmc,
   bbid00,
   bbmc00,
   bbsm00,
   bbcjl0,
   bbcjr0,
   cjrxm0,
   bbsdr0,
   sdrxm0,
   bbsdrq,
   bbsdsj,
   sdrqsj,
   bbcyrq,
   cyrq00,
   bbcysj,
   cyrqsj,
   sqrq00,
   sq0000,
   sqsj00,
   sqks00,
   sqksmc,
   sqbq00,
   sqbqmc,
   sqys00,
   sqysxm,
   pyrq00,
   py0000,
   pysj00,
   wyybh0,
   jgzt00,
   jgztmc,
   thyy00,
   cxshyy,
   cbgrq0,
   bg0000,
   cbgsj0,
   bgr000,
   bgrxm0,
   bgjyr0,
   jyrxm0,
   dzqm00,
   zje000,
   sfbz00,
   sfbzmc,
   bgdybz,
   txm000,
   txm001,
   txmqz0,
   txmysz,
   yblx00,
   jydly0,
   dybz00,
   ybhbbz,
   mzkid0,
   yqdh00,
   tydwmc,
   bzdwmc,
   lry000,
   lryxm0,
   bgckr0,
   ckrxm0,
   bgcksj,
   sgcjqz,
   sftmwk,
   kwh000,
   cjdyrq,
   cjbz00,
   bbbz00,
   sfje00,
   qdxh00,
   sj0000,
   tdjttm,
   sfjm00,
   sfyb00,
   czy000,
   czyxm0,
   yjksbh,
   brzt00,
   bgysz0,
   qzmc00,
   sfky00,
   lcpy00,
   tmsfsf,
   dycs00,
   bgrqm0,
   czyqm0,
   fhzqm0,
   qsrqsj,
   hgsdrq,
   hgsdsj,
   hgscrq,
   hgscsj,
   hgxm00,
   cjksbh,
   yqmc00,
   xjall0,
   cfzbz0,
   sflstd,
   dyrqsj,
   bgdyr0,
   syzdmc,
   sfbd00,
   qsbz00,
   jyjs00,
   bdxx00,
   zjjyjs,
   brdh00,
   zzjfs0,
   sfxwfs,
   zzbgrs,
   sfctat,
   jyqsj0,
   jyzsj0,
   jyhsj0,
   sfjz00,
   sjrq00,
   sjsj00,
   sjcjrq,
   sjcjsj,
   cjksmc,
   zyzbmc,
   ckfknr,
   wjzwck,
   sftbjs,
   dxsffs,
   tmdyr0,
   jtdah0,
   xmlx00,
   tmdyxm,
   sfyc00,
   fcybh0,
   sffs02,
   sfwjz0,
   brzjbh,
   bh0000,
   zxyyid,
   xbjs00,
   yyid00,
   brjtdz,
   brlxdz,
   wljgbm,
   wljgmc,
   sfwj00,
   sfxwr0,
   sfdcny,
   sffny0,
   xmpysm,
   xmwbsm,
   brzy00,
   jjbz00,
   sfybwr )
AS
select
    JYYWID,  --流水ID:SEQ_JY_YW0000_LSID00
    YJDJH0,  --医技单据号 
    YJFZH0,  --
    ZLXMID,  --
    substr(SF_JY_UNION_TXM000(TXM000),1,50) ZLXMZH,
    (select ZLXMJC from BM_ZLZD00 where ZLXMID=A.ZLXMID) ZLXMJC,  
    --(select XXMS00 from BM_ZLZD00 where ZLXMID=A.ZLXMID) XXMS00,
    substr(SF_JY_UNION_XXMS00(TXM000),1,50) XXMS00,
    --(select zyzbid from JY_JYYQZD where yqdh00 in (select YQDH00 from JY_zlbgdy where zlxmid=A.zlxmid) and rownum=1) zyzbid,
    (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, --
    substr(SF_JY_GETYEORBRXM(A.YEXM00,A.BRXM00),1,40) BRXM00, --病人姓名(冗余)
    A.YEXM00, --婴儿姓名
    XB0000,   --性别(冗余)
    decode(XB0000,'M','男','F','女','') XBMC00, --性别名称
    CSRQ00,   --出生日期(冗余)  
    --NL0000 ,  --年龄(冗余)
    --trim(substr(SF_JY_CSRQTONL_NYXS(CSRQ00),1,10)) NL0000, --年龄
    trim(substr(NL0000||decode(NLDW00,'Y','岁','D','天','M','月','W','周'),1,10)) NL0000,
    ' ' NLDW00,   --年龄单位        
    ' ' 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, --采样日期时间
    substr(sf_jy_getBBCYRQSJ(PYRQ00,PYSJ00,BBCYRQ,BBCYSJ,TXM000,BBSDRQ,BBSDSJ,MZZYBZ),1,19)  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','已取单','A','已初审','未知') JGZTMC,
    THYY00,  --退回原因
    CXSHYY,  --撤销审核原因
    CBGRQ0,  --出报告日期
    decode(CBGRQ0,null,null,substr(CBGRQ0,1,4)||'.'||substr(CBGRQ0,5,2)||'.'||substr(CBGRQ0,7,2)||' '||CBGSJ0) BG0000,    
    CBGSJ0,  --出报告时间  
    BGR000,  --报告人(报告签发者或检验操作者)
    nvl(BGRXM0,(select ZWXM00 from BM_YGBM00 where YGBH00=A.BGR000)) BGRXM0,  --报告人姓名   
    BGJYR0,  --报告人结果校对者
    nvl(BGJYXM,(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',decode(jgzt00,'7','已退费','已收费'),'3',decode(jgzt00,'7','已冲销','已执行'),'6','已作废','未知') 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,
    SF_JY_GET_ZJE000(A.YJDJH0,A.ZLXMID) SFJE00,
    QDXH00,
    (select SJ0000 from JY_JYYQZD where YQDH00 = A.YQDH00) SJ0000, --试剂
    TDJTTM,
    SFJM00,
    SFYB00,
    CZY000,  --操作员
    nvl(CZYXM0,(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,  --打印次数 
    (select DZQM00 from BM_ZJ0000 where YGBH00=A.BGR000) BGRQM0,  --报告人签名
    (select DZQM00 from BM_ZJ0000 where YGBH00=A.CZY000) CZYQM0,  --操作员签名
    (select DZQM00 from BM_ZJ0000 where YGBH00=A.BGJYR0) FHZQM0,  --复核者签名  
    SF_JY_GETQSRQSJ(BBCYRQ,BBCYSJ,BBSDRQ,BBSDSJ) QSRQSJ,
    (select SDRQ00 from JY_HGSJJL where TXM000=A.TXM000 and JJLB00='1'and rownum=1)HGSDRQ,  --护工收到标本日期
    (select SDSJ00 from JY_HGSJJL where TXM000=A.TXM000 and JJLB00='1'and rownum=1)HGSDSJ,  --护工收到标本时间
    (select SDRQ00 from JY_HGSJJL where TXM000=A.TXM000 and JJLB00='2'and rownum=1) HGSCRQ,   --护工标本送到检验科日期
    (select SDSJ00 from JY_HGSJJL where TXM000=A.TXM000 and JJLB00='2'and rownum=1) HGSCSJ,   --护工标本送到检验科时间
    (select ZWXM00 from BM_YGBM00 where YGBH00=(select SCR000 from JY_HGSJJL where TXM000=A.TXM000 and JJLB00='1'and rownum=1)) HGXM00, --护工姓名
    A.CJKSBH ,   --采集科室编号
    SF_JY_GETYQMC(YBH000,PYRQ00) YQMC00,
    SF_JY_GETXJALL(A.PYRQ00,A.YBH000) XJALL0,
    decode(MZZYBZ,'0',nvl((select decode(SFCZ00,'0','初诊','复诊') from sf_brxxb0 where ghid00=a.zyghid) ,''),'') CFZBZ0,
    SFLSTD,
    nvl(JYBGRS,ZZBGRS) DYRQSJ,
    nvl((select ZWXM00 from BM_YGBM00 where YGBH00=A.JYBGDY),ZZBGDY) BGDYR0,
    SF_JY_UNION_ZDMC00(ZYGHID) SYZDMC ,      --病人所有诊断
    decode(JGZT00,'8','补打','') SFBD00,   --是否补打
    QSBZ00 ,          --签收备注
    JYJS00 ,          --建议解释
    decode(nvl(DYCS00,0),0,'','补打') BDXX00,
    SF_JY_GETJYJS(PYRQ00,YBH000) ZJJYJS,
    (select decode(lengthb(trim(BRLXDH)),11,BRLXDH,decode(BRDH00,'0',null,BRDH00)) from BM_BRXXB0 where BRID00=A.BRID00) BRDH00 ,
    ZZJFS0, --自助机发送
    SFXWFS,  --是否向外发送
    ZZBGRS,
    SFCTAT, --是否超出TAT设置时间
    JYQSJ0, --检验前时间
    JYZSJ0, --检验中时间
    JYHSJ0,  --检验后时间
    decode(A.YXBZ00,'N','否','Y','是','否') SFJZ00,  --是否急诊
    SJRQ00,--送检日期
    SJSJ00, --送检时间
    A.SJCJRQ,--实际采集日期
    A.SJCJSJ,--实际采集时间
    (select BMMC00 from BM_BMBM00 where BMBH00=A.CJKSBH and rownum=1) CJKSMC,  --采集科室名称
    --(select e.zyzbmc from bm_jyzyzb e,jy_jyyqzd d where a.yqdh00=d.yqdh00 and e.zyzbid=d.zyzbid and rownum=1) ZYZBMC,
    (select ZYZBMC from BM_JYZYZB where ZYZBID=(select ZYZBID from BM_ZLZD00 where zlxmid=A.ZLXMID)) ZYZBMC,
    A.CKFKNR,  --查看反馈内容
    A.WJZWCK,  --危急值未提示标志
    A.SFTBJS,  --是否通报及时
    A.DXSFFS,  --短信是否发送
    A.TMDYR0,  --条码打印人
    A.JTDAH0,  --雷奥家庭档案号  
    A.XMLX00,  --婚检项目类型ID 
    (select ZWXM00 from BM_YGBM00 where YGBH00=A.TMDYR0) TMDYXM, --条码打印人姓名  
    SFYC00,
    FCYBH0,
    SFFS02,
    SFWJZ0,
    (select BRZJBH from BM_BRXXB0 where BRID00=A.BRID00) BRZJBH,--身份证号
    (select BH0000 from BM_BMBM00 where BMBH00=A.SQKS00) BH0000,--部门编号
    decode(nvl(trim(A.YYID00),'0'),'0',(select YYID00 from BM_BMBM00 where BMBH00=A.SQKS00),A.YYID00) ZXYYID,
    XBJS00, --骨髓细菌计数
    A.YYID00,
    (select BRJTDZ from BM_BRXXB0 where BRID00=A.BRID00) BRJTDZ,--病人家庭地址
    (select BRLXDZ from BM_BRXXB0 where BRID00=A.BRID00) BRLXDZ,--病人联系地址 
    A.WLJGBM,                                                   --外来机构编码
    (select WLJGMC from JY_WLJG00 where WLJGBM=A.WLJGBM) WLJGMC,--外来机构名称
    SFWJ00,
    SFXWR0,
    SFDCNY,
    SFFNY0,    
    SF_JY_GETPYSM(nvl(A.YEXM00,A.BRXM00)) XMPYSM,
    SF_JY_GETWBSM(nvl(A.YEXM00,A.BRXM00)) XMWBSM,
    (select BRZY00 from BM_BRXXB0 where BRID00=A.BRID00) BRZY00, --病人职业  
    JJBZ00,
    SFYBWR 
  from JY_YW0000 A
/

-- Grants for View
GRANT SELECT ON vw_jy_yw0000 TO palmhospitalv3
/

-- End of DDL Script for View SD_HOSPITAL.VW_JY_YW0000