create or replace view VW_SS_SSFYLB
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- lintj      2013.05.20   create
-- qks        2014.01.20   增加判断存在SS_SSYJD0表中
-- qks        2016.03.28   去掉判断条件存在于表SS_SSYJD0 by SSMZ9-20160328-001
-- qks        2016.04.05   增加判断条件 by SSMZ9-20160405-001
-- qks        2016.04.19   新增参数SS_SSYJDDY控制; by SSMZ9-20160420-001
-- qks        2017.06.09   新增字段DBZBZ0; for SSMZ9-20170612-002
-- whm        2017.08.08   增加BRRYCS字段 SSMZ9-20170707-001
-- gzj        2018.04.20   增加DQKSMC,DQBQMC字段    SSMZ9-20180418-001
-- gzj        2018.11.28   增加挂号科室或住院科室字段GHZYKS SSMZ9-20181127-001
-- gzj        2019.03.12   新增手术申请医生SQYSX0字段 SSMZ9-20190301-002
-- gzj        2019.04.03   过滤门诊挂号已退号的记录   SSMZ9-20190327-001
-- jlg        2019.05.06   SSZT00增加'0'(申请)条件的,同VW_SS_MZFYLB  SSMZ9-20190430-001
-- liwm       2019.07.18   增加精准扶贫 SSMZ9-20190718-001
-- gzj        2019.08.23   增加门诊病人标志a.MZZYBZ='0' SSMZ9-20190821-001
-- jlg        2020.02.24   增加 BRZYZT(病人住院状态),ZYZTMC 字段 	SSMZ9-20200224-001
-- linbin     2020.09.17   增加字段DBZBM0(单病种编码),DBZMC0(单病程名称)  SSMZ9-20200906-001
select 
  SSDH00, MZZYBZ, ZYHGHH, BRID00, SSTBH0, SSJH00, SSTC00, SSXH00, GLDJMC, 
  GLBZ00, SSDJMC, SSKS00, MZKS00, SQYS00, SQKS00, SQBQ00, SQR000, SRKSLB, 
  SQRQ00, SQSJ00, YYSSRQ, YYSSSJ, SSQSRQ, SSQSSJ, SSJSRQ, SSJSSJ, MZBH00, 
  MZMC00, BZ0000, SQTJ00, SSLXMC, SQBQSM, MZMYMC, SSSLMC, ZRL000, ZCL000, 
  SXL000, CXL000, SSZT00, HSHBBZ, JZBZ00, MZAPBZ, QXZBBZ, YPZBBZ, SSFYBZ, 
  MZFYBZ, TZBZ00, QXSM00, YYBZ00, YZMXID, SQZD00, SQZDMC, SHZD00, SHBQSM, 
  ZYGHID, SFGR00, XM0000, XB0000, XX0000, CZRQ00, NL0000, SQRQSJ, YYRQSJ, 
  SSRQSJ, JSRQSJ, SSZTMC, QXZB00, YPZB00, SSFY00, MZFY00, JZBZMC, SQYSXM, 
  SSKSMC, MZKSMC, SQKSMC, SQBQMC, SSMC00, SSBH00, QKDJMC, YHDJMC, RYCWH0, 
  SSJCSJ, GRQK00, SSZB00, SSSD00, BX0000, YYX000, BWMC00, YYID00, SHRY00, 
  SHRYXM, QKDJ00, SSYS00, SSYSXM, SSYSX1, SXYS00, SXYSXM, SXYSX1, MZYS00, 
  MZYSXM, MZYSX0, MZYSX1, MZYSX2, SSZS00, SSZSXM, SSZSX1, SSZSX2, SSZSX3, 
  SSZSX4, TSHS00, TSHSXM, TSHSX1, TSHSX2, GYHS00, GYHSXM, XHHSX1, XHHSX2, 
  XSHS00, XSHSXM, BRLB00, CZYXM0, BYH000, BRZJBH, YJKSBH, GCYSXM, CLDBBM, 
  CLDBMC, YSZID0, MZYSX00,MZYSX11,MZYSX22,TSHSX11,TSHSX22,XHHSX11,XHHSX22, 
  SSZSX11,SSZSX22,SSZSX33,SSZSX44,SSYSX11, TZDDY0, BRRYCS, DQKSMC, DQBQMC, GHZYKS,
  decode(SSZT00,'6',SQKS00,SSKS00) SSMZKS, DBZBZ0, SQYSX0, SFJZFP, BRZYZT,
  decode(substr(BRZYZT,1,1),'1','未入科','2','在院','3','出科','4','正常出院','5','欠费出院') as ZYZTMC,
  DBZBM0, DBZMC0 
from VW_SS_YW0000 a
where 
--where (SSZT00 in ('2','4') and exists(select 1 from SS_SSYJD0 where SSDH00=a.SSDH00) or SSZT00='6')
  (SSZT00 in ('0','2','4') and ((select count(1) from XT_XTCS00 where NAME00='SS_SSYJDDY' and VALUE0='Y')=1 or 
          exists (select 1 from SS_SSYJD0 where SSDH00=a.SSDH00)) or SSZT00='6')
  and not exists(select 1 from SF_BRXXB0 where ghh000=a.ZYHGHH and THBZ00='0' and a.MZZYBZ='0')
 /
 
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%



create or replace view VW_SS_MZFYLB
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- lintj      2013.05.20   create 麻醉业务类别
-- jlg        2016.07.05   增加显示具体字段明细 SSMZ9-20160517-001
-- jlg        2016.07.05   仿VW_SS_SSFYLB 新增参数SS_SSYJDDY控制; SSMZ9-20160517-001
-- qks        2017.06.09   新增字段DBZBZ0; for SSMZ9-20170612-002
-- qks        2017.06.25   要求未排台手术要可以进行麻醉划价; for SSMZ9-20170626-001
-- whm        2017.08.08   增加字段BRRYCS 
-- gzj        2018.04.20   增加DQKSMC,DQBQMC字段    SSMZ9-20180418-001
-- gzj        2018.11.28   增加挂号科室或住院科室字段GHZYKS SSMZ9-20181127-001
-- gzj        2019.03.12   新增手术申请医生SQYSX0字段 SSMZ9-20190301-002
-- gzj        2019.04.03   过滤门诊挂号已退号的记录   SSMZ9-20190327-001 
-- liwm       2019.07.18   增加精准扶贫    SSMZ9-20190718-001
-- gzj        2019.08.23   增加门诊病人标志a.MZZYBZ='0' SSMZ9-20190821-001
-- jlg        2020.02.24   增加 BRZYZT(病人住院状态),ZYZTMC 字段 	SSMZ9-20200224-001
-- linzetao   2020.10.19   增加DBZBM0,DBZMC0栏位    SSMZ9-20201019-001
select 
  SSDH00, MZZYBZ, ZYHGHH, BRID00, SSTBH0, SSJH00, SSTC00, SSXH00, GLDJMC, 
  GLBZ00, SSDJMC, SSKS00, MZKS00, SQYS00, SQKS00, SQBQ00, SQR000, SRKSLB, 
  SQRQ00, SQSJ00, YYSSRQ, YYSSSJ, SSQSRQ, SSQSSJ, SSJSRQ, SSJSSJ, MZBH00, 
  MZMC00, BZ0000, SQTJ00, SSLXMC, SQBQSM, MZMYMC, SSSLMC, ZRL000, ZCL000, 
  SXL000, CXL000, SSZT00, HSHBBZ, JZBZ00, MZAPBZ, QXZBBZ, YPZBBZ, SSFYBZ, 
  MZFYBZ, TZBZ00, QXSM00, YYBZ00, YZMXID, SQZD00, SQZDMC, SHZD00, SHBQSM, 
  ZYGHID, SFGR00, XM0000, XB0000, XX0000, CZRQ00, NL0000, SQRQSJ, YYRQSJ, 
  SSRQSJ, JSRQSJ, SSZTMC, QXZB00, YPZB00, SSFY00, MZFY00, JZBZMC, SQYSXM, 
  SSKSMC, MZKSMC, SQKSMC, SQBQMC, SSMC00, SSBH00, QKDJMC, YHDJMC, RYCWH0, 
  SSJCSJ, GRQK00, SSZB00, SSSD00, BX0000, YYX000, BWMC00, YYID00, SHRY00, 
  SHRYXM, QKDJ00, SSYS00, SSYSXM, SSYSX1, SXYS00, SXYSXM, SXYSX1, MZYS00, 
  MZYSXM, MZYSX0, MZYSX1, MZYSX2, SSZS00, SSZSXM, SSZSX1, SSZSX2, SSZSX3, 
  SSZSX4, TSHS00, TSHSXM, TSHSX1, TSHSX2, GYHS00, GYHSXM, XHHSX1, XHHSX2, 
  XSHS00, XSHSXM, BRLB00, CZYXM0, BYH000, BRZJBH, YJKSBH, GCYSXM, CLDBBM, 
  CLDBMC, YSZID0, MZYSX00,MZYSX11,MZYSX22,TSHSX11,TSHSX22,XHHSX11,XHHSX22, 
  SSZSX11,SSZSX22,SSZSX33,SSZSX44,SSYSX11, TZDDY0, BRRYCS, DQKSMC, DQBQMC, GHZYKS, 
  decode(SSZT00,'6',SQKS00,MZKS00) SSMZKS, DBZBZ0, SQYSX0, SFJZFP, BRZYZT,
  decode(substr(BRZYZT,1,1),'1','未入科','2','在院','3','出科','4','正常出院','5','欠费出院') as ZYZTMC,
  DBZBM0, DBZMC0 
from VW_SS_YW0000 a
where 
  --(SSZT00 in ('0','2','4') and exists(select 1 from SS_SSYJD0 where SSDH00=a.SSDH00 and SSMZBZ='0')) or (SSZT00='6')
  (SSZT00 in ('0','2','4') and ((select count(1) from XT_XTCS00 where NAME00='SS_SSYJDDY' and VALUE0='Y')=1 or 
          exists (select 1 from SS_SSYJD0 where SSDH00=a.SSDH00 and SSMZBZ='0')) or SSZT00='6')  
  and not exists(select 1 from SF_BRXXB0 where ghh000=a.ZYHGHH and THBZ00='0' and a.MZZYBZ='0')
/