prompt 1.016 VW_YJ_YW0000 医技业务
create or replace view VW_YJ_YW0000
as
-- MODIFICATION HISTORY
-- Person     Date          Comments
-- lintj      2013.05.08    重新整理
-- lintj      2013.09.29    加 JCZID0, YYID00
-- zhangwz    2013.11.15    加 SQID00, YYBZ00
-- qks        2014.08.18    加 JCSQDH
-- qks        2016.03.02    增加字段SFZTMC by YJ9-20160303-001
-- qks        2016.03.15    SFZTMC取值改为跟ZT0000类似，只修改XMZT00=4取值; by YJ9-20160316-001
-- qks        2016.12.03    多个项目执行后，打印项目执行单速度非常慢; by YJ9-20161204-001
-- qks        2017.12.18    增加 LBMC00; by MZSF9-20171216-003
-- gzj        2019.01.10    增加GHKS00,GHKSMC,DQBQ00,DQBQMC,DQKS00,DQKSMC,RYCWH0 YJ9-20181228-001
-- liwm       2019.03.27    增加YSZID0 YJ9-20190301-001
-- liwm       2019.06.29    病区护士检验采用"集成平台"模式后，因为采用新表YJ_BBZJB0,不再更新同步his_requisition,导致住院病人【标本状态】一栏都为“条码对照”。现场跟踪不到对应查询语句 YJ9-20190617-001	
-- liwm       2019.09.30    中间表改取YJ_BBZJB0。VW_YJ_YW0000视图对于住院部分，要改取YJ_BBZJB0 YJ9-20190930-002
-- linshu     2019.12.19    增加送检标本名称  SSMZ9-20191216-001 	
-- linzetao   2020.02.20    增加合并住院项目标志YJ_YW0000.HBZYXM    YJ9-20200219-001  
-- linzetao   2020.07.21    增加部位id栏位(BWID00)   YJ9-20200716-001
-- jlg        2020.10.15    处理如果是婴儿的项目，则姓名改为婴儿姓名（母亲姓名）的显示方法 YJ9-20201015-001
-- jlg        2020.11.11    住院病人床号改从BM_CWK000获取    YJ9-20201111-001
-- linbin     2021.04.15    增加是否自费栏位(SFZF00)         SSMZ9-20210325-001 
-- jlg        2021.04.18    将条件 y.FJDJBZ not in('3','4') 改为nvl(y.FJDJBZ,'0') not in('3','4')	YJ9-20210418-003
-- jlg        2021.08.05    增加TFSHR0,TFSHRQ,TFSHSJ等退费审核相关字段 YJ9-20210802-001
-- jlg        2021.11.10    重新梳理下本视图，把具体字段名称分解到各取值行中，不再用专门的字段名称 	SSMZ9-20211109-001
-- chenrh     2023.07.28    增加JZQRR0,JZQRXM等退费相关字段  YJ13H-20230728-001
select
  y.BRID00,                                                                                         --病人id
  y.ZYGHID,                                                                                         --住院挂号ID
  y.YJKSBH,                                                                                         --医技科室编号
  y.YJDJH0,                                                                                         --医技单据号
  y.YJFZH0,                                                                                         --分组号
  y.ZYHGHH,                                                                                         --住院挂号号
  decode(y.YEXM00,null,y.BRXM00,y.YEXM00||'('||y.BRXM00||')') as BRXM00,                            --病人姓名
  (select YBMC00 from IC_YBBRLB where YBLB00=d.YBLB00 and FBBH00=d.FBBH00) as BRYBMC,               --医保名称
  y.YEXM00,                                                                                         --婴儿姓名
  y.XB0000,                                                                                         --性别
  y.CSRQ00,                                                                                         --出生日期
  substrb(SF_XT_CSRQTONL(y.CSRQ00,y.KDRQ00),1,8) as BRNL00,                                         --病人年龄
  y.ZLXMID,                                                                                         --诊疗项目id
  y.ZLXMJC,                                                                                         --诊疗项目简称
  y.KDYSXM,                                                                                         --开单医生姓名
  b.BMMC00 as KDKSMC,                                                                               --开单科室名称
  y.KDKSBH,                                                                                         --开单科室编号
  y.KDRQ00,                                                                                         --开单日期
  y.JZBZ00,                                                                                         --急诊标志
  y.BWMC00,                                                                                         --部位名称
  y.APRQ00,                                                                                         --安排日期
  y.APSJ00,                                                                                         --安排时间
  substr(SF_YJ_JBZDMC(y.YJDJH0), 1, 100) as ZDMC00,                                                 --诊断名称
  y.YZCFID,                                                                                         --处方号 decode(y.DCBZ00, '0',null, y.YZCFID)
  y.XMZT00,                                                                                         --项目状态
  y.MZZYBZ,                                                                                         --门诊住院标志
  y.JGLR00,                                                                                         --结果
  y.BZ0000,                                                                                         --备注
  decode(y.APRQ00, null,'N','','N','Y') as APBZ00,                                                  --已经安排排标志
  y.ZJE000, --
  nvl((select X.APBZ00 from XT_KSZXXM X where X.ZXKSBH=y.YJKSBH and X.ZLXMID=y.ZLXMID and rownum=1),'N') as SFXYAP,--是否需要安排标志
  y.KDYS00, --
  y.ZDID00, --
  y.SL0000, --
  y.ZXSL00, --
  y.DCBZ00, --
  decode(y.JZBZ00, null, '否', 'Y', '是', 'N', '否', '否') as SFJZ00,
  decode(y.XMZT00,'0',decode(sign(y.SL0000),1,'未收费','申请退费'),
                  '1',decode(sign(y.SL0000),1,'未收费','申请退费'||decode(y.TFSHRQ,null,'','(已审核)')),
                  '2',decode(sign(y.SL0000),1,'已收费','已退费'),
                  '3',decode(sign(y.SL0000),1,'已执行','已退费'),
                  '4','已报告',
                  '5','疑退',
                  '6','已作废',
                  '7','已退费',
                  'T','申请退费',
                  '9','未审核') as ZT0000,
  decode(y.MZZYBZ,'1','住院','门诊') as MZZY00,                                              --
  y.BGYSXM,                                                                        --
  y.JGBGRQ,                                                                        --
  y.ZXRQ00,                                                                        --
  y.ZXSJ00,                                                                        --
  y.SFDJH0,                                                                        --
  y.CXDJH0,                                                                        --
  (select X.JGBGBZ from XT_KSZXXM X where X.ZXKSBH=y.YJKSBH and X.ZLXMID=y.ZLXMID and rownum=1) as JGBGBZ,
  y.LRXM00, 
  y.KDSJ00, 
  y.YJLRBZ, 
  y.KDBQ00, 
  y.ZXRXM0,                                -- 
  y.SM0000, 
  y.ZLDCCF, 
  y.CFID00, 
  y.BBID00, 
  y.CXSL00, 
  y.TXM000,                      --
  nvl(y.TSBSMC,(select BS0000 from YS_XMSQD0 where SQID00(+)=y.SQID00)) as BS0000,    --
  nvl(y.ZSDYMC,(select ZS0000 from YS_XMSQD0 where SQID00(+)=y.SQID00)) as ZS0000,    --
  d.BRDH00 as BRLXDH,                                                                 --
  (select ZXWXTS from BM_BMBM00 where BMBH00(+)=y.YJKSBH) as ZXWXTS,                  --技科室执行时显示的温馨提示
  y.SYLSH0,                                                                           --
  d.FBBH00,                                                                           --
  e.LBBH00,                                                                           --
  y.ZXR000,                                                                           --
  (select nvl(sum(ZXCS00),0) from YJ_YWZXJL where YJDJH0=y.YJDJH0 and ZXR000 is not null and ZXLB00<>'4') as SGZXCS,
  b.YYID00 as KDYYID,
  nvl(y.ZXYS00, y.KDYS00) as ZXYS00,
  (select ZWXM00 from BM_YGBM00 where YGBH00=nvl(y.ZXYS00,y.KDYS00)) as ZXYSXM,
  decode(y.MZZYBZ,'1',nvl((select CH0000 from BM_CWK000 where ZYID00=y.ZYGHID and rownum=1),y.CH0000),y.CH0000) as CH0000,  --床号
  (select KMH000 from IC_YHXX00 where BRID00=d.BRID00 and ZT0000='1' and rownum=1) as KMH000,
  decode(y.KDRQ00, null, null,substr(y.KDRQ00,3,2)||'-'||substr(y.KDRQ00,5,2)||'-'||substr(y.KDRQ00,7,2)||' '||substr(y.KDSJ00,1,5)) KDRQSJ, --开单日期时间
  decode(y.ZXRQ00, null, null,substr(y.ZXRQ00,3,2)||'-'||substr(y.ZXRQ00,5,2)||'-'||substr(y.ZXRQ00,7,2)||' '||substr(y.ZXSJ00,1,5)) ZXRQSJ, --执行日期时间
  y.YPNM00,                                                                           --皮试药品内码
  (select BMMC00 from BM_BMBM00 where BMBH00 = y.YJKSBH) as YJKSMC,                   --医技科室名称
  y.DW0000,
  case
    when  y.MZZYBZ<>'1' then
        (select decode(REQUISITION_STATE,'reported','报告完成','preinspect','对照完成','printed',
                 decode(y.DYRQTM,null,'条码生成','条码打印'),'canceled','条码撤销','state','声明','deleted','条码删除',
                 'returned','标本退回','inspecting','检验确认','samplinged','采集确认',
                 'received','接收确认','appended','项目追加','未定义') from HIS_REQUISITION where HIS_ID=to_char(y.YJDJH0) and rownum=1)
    else
        (select decode(STATE0,'reported','报告完成','preinspect','对照完成','printed',
                 decode(y.DYRQTM,null,'条码生成','条码打印'),'canceled','条码撤销','state','声明','deleted','条码删除',
                 'returned','标本退回','inspecting','检验确认','samplinged','采集确认',
                 'received','接收确认','appended','项目追加','未定义') from YJ_BBZJB0 where YJDJH0=to_char(y.YJDJH0) and rownum=1)
  end as BBZT00,
  case
    when y.Mzzybz<>'1' then
        (select REQUISITION_ID from HIS_REQUISITION where HIS_ID=to_char(y.YJDJH0) and rownum=1)
    else
        (select TXM000 from YJ_BBZJB0 where YJDJH0=to_char(y.YJDJH0) and rownum=1)
  end as JYTXM0,
  nvl(e.JCLBID,0) as JCLBID,
  (select BGURL0 from BM_JCLB00 where JCLBID=e.JCLBID) as BGURL0,
  (select YXURL0 from BM_JCLB00 where JCLBID=e.JCLBID) as YXURL0,
  nvl(e.JCZID0,0) as JCZID0,
  nvl(y.YYID00,0) as YYID00,
  y.HBXHID,
  nvl((select YYZT00  from YJ_YYBRB0 where YYID00=y.YYID00 ),'-1') as YYZT00,
  y.HBDJH0,
  y.SQID00,
  decode(nvl(y.YYID00,0),0,'','已预约') as YYBZ00,                                                            --预约标志
  decode(nvl(y.YYID00,0),0,'',(select JCSQDH from YJ_YYBRB0 where YYID00=y.YYID00 and YYZT00<>'2')) as JCSQDH,--检查申请单号
  decode(y.XMZT00,'0',decode(sign(y.SL0000),1,'未收费','申请退费'),
                  '1',decode(sign(y.SL0000),1,'未收费','申请退费'||decode(y.TFSHRQ,null,'','(已审核)')),
                  '2',decode(sign(y.SL0000),1,'已收费','已退费'),
                  '3',decode(sign(y.SL0000),1,'已收费','已退费'),
                  '4',decode(sign(y.SFDJH0),1,'已收费','未收费'),
                  '5','疑退','6','已作废','7','已退费','T','申请退费','9','未审核') as SFZTMC,             --收费状态名称
  (select LBMC00 from BM_ZLXMLB where LBBH00=e.LBBH00) as LBMC00,                                          --类别名称
  decode(y.MZZYBZ,'0',(select GHRQGG from VW_SF_BRGHXX where GHID00=y.ZYGHID and rownum=1),'') as GHRQ00,  --挂号日期
  decode(y.MZZYBZ,'0',(select JZYSXM from VW_SF_BRGHXX where GHID00=y.ZYGHID and rownum=1),'') as JZYSXM,  --就诊医生
  decode(y.MZZYBZ,'0',(select GHKS00 from VW_SF_BRGHXX where GHID00=y.ZYGHID and rownum=1),'') as GHKS00,  --挂号科室
  decode(y.MZZYBZ,'0',(select GHKSMC from VW_SF_BRGHXX where GHID00=y.ZYGHID and rownum=1),'') as GHKSMC,  --挂号科室名称
  decode(y.MZZYBZ,'1',(select DQBQ00 from VW_ZY_BRXXB0 where ZYID00=y.ZYGHID and rownum=1),'') as DQBQ00,  --当前病区
  decode(y.MZZYBZ,'1',(select DQBQMC from VW_ZY_BRXXB0 where ZYID00=y.ZYGHID and rownum=1),'') as DQBQMC,  --当前病区名称
  decode(y.MZZYBZ,'1',(select DQKS00 from VW_ZY_BRXXB0 where ZYID00=y.ZYGHID and rownum=1),'') as DQKS00,  --当前科室
  decode(y.MZZYBZ,'1',(select DQKSMC from VW_ZY_BRXXB0 where ZYID00=y.ZYGHID and rownum=1),'') as DQKSMC,  --当前科室名称
  decode(y.MZZYBZ,'1',(select RYCWH0 from VW_ZY_BRXXB0 where ZYID00=y.ZYGHID and rownum=1),'') as RYCWH0,  --床位号
  y.YSZID0,                                                                                                --医生组ID
  y.SJBBMC,                                                                                                --送检标本名称
  y.HBZYXM,                                                                                                --合并住院项目标志
  y.BWID00,                                                                                                --部位ID
  nvl(y.SFZF00,'0') as SFZF00,                                                                             --是否自费
  y.TFSHR0,                                                                                                --退费审核人
  (select ZWXM00 from BM_YGBM00 where YGBH00 = y.TFSHR0) as TFSHXM,                                        --退费审核人姓名
  y.TFSHRQ,                                                                                                --退费审核日期
  y.TFSHSJ,                                                                                                --退费审核时间
  y.TFSHRQ||' '||y.TFSHSJ as TSRQSJ,                                                                       --退费审核日期时间
  y.JZQRR0,                                                                                                --记账人
  (select ZWXM00 from BM_YGBM00 where YGBH00 = y.JZQRR0) as JZQRXM                                         --记账人姓名
from YJ_YW0000 y, BM_BMBM00 b, BM_BRXXB0 d, BM_ZLZD00 e
where y.BRID00 = d.BRID00 
  and y.YJKSBH <> 0
  and y.KDKSBH = b.BMBH00 
  and y.ZLXMID = e.ZLXMID 
  and nvl(y.FJDJBZ,'0') not in('3','4')
/

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%