create or replace view vw_mzsf_outpatient as
select
distinct
a.ghid00 as OutpatientCode ,  --门诊编码
a.brid00 as PersonnelCode  ,  --人员编码
b.brxm00 as Name           ,  --姓名
decode(b.brxb00,'男',1,'女',2) as Sex ,--性别
b.brcsrq as DateOfBirth,  --出生日期
b.brjtdz as Address,      --患者住址
b.brzjbh as IDcardNo ,  --身份证号码
a.ghrq00||' '||a.ghsj00 as OutpatientDate,  --门诊日期
--(select bmmc00 from bm_bmbm00 where bmbh00=a.ghks00) as OutpatientDepartment,  --门诊科室
a.ghks00 as OutpatientDepartment,  --门诊科室
a.ghyxm0 as OutpatientPhysician, --门诊医师
c.hjje00 as OutpatientExpenses, --门诊费用
c.tcjjzf as InsuranceExpenses, --医保费用
(select zdmc00 from ys_brzdxx where ghid00=a.ghid00 and rownum=1)as OutpatientDiagnosis, --门诊诊断
'' as PatientSymptoms ,   --病人症状
b.AAE140_MC as InsuranceType,--医保类型
b.brdh00 as Telephone,--电话
(select value0 from xt_xtcs00 where name00='YS_Medicalinfo') as View360   --360视图
,d.zlxmjc    --新增诊疗项目   by cly 20241105
 from sf_brxxb0 a ,bm_brxxb0 b,sf_jzb000 c
 ,yj_yw0000 d    --by cly 20241031
 where a.brid00=b.brid00 and a.ghid00=c.mzid00 and a.thbz00<>'0' --and b.brid00=c.brid00
and c.hjje00>'0'and not exists (select * from zy_jzb000 C1 where C1.CXJZDH=c.jzdh00)
and a.ghks00 <>'38'

and a.ghid00=d.zyghid
/*and
(
(
--exists (select 1 from bm_zlzd00 C1,yj_yw0000 d1 where C1.zlxmid=d1.zlxmid and d1.zyghid=d.zyghid and lbbh00='4')and
 exists (select 1 from bm_zlzd00 C1,yj_yw0000 d1 where C1.zlxmid=d1.zlxmid and d1.zyghid=d.zyghid and lbbh00='3'and C1.JCLBID='2' )    --超声
and exists (select 1 from bm_zlzd00 C1,yj_yw0000 d1 where C1.zlxmid=d1.zlxmid and d1.zyghid=d.zyghid and lbbh00='3'and d1.yjksbh='129' )    --放射
)
or exists (select 1 from bm_zlzd00 C1,yj_yw0000 d1 where C1.zlxmid=d1.zlxmid and d1.zyghid=d.zyghid and lbbh00='3'and  C1.jclbid='13')    --胃肠镜
)*/   --by cly 20241031  新增判断：随访升级群要求 查询结果只体现同时开了检验和超声、胃肠镜、放射项目的人
and  zlxmjc not like'%门诊诊查费%'and  zlxmjc not like'%碳呼气试验%'
and (exists (select 1 from bm_zlzd00 C1,yj_yw0000 d1 where C1.zlxmid=d1.zlxmid and d1.zyghid=d.zyghid and lbbh00='4')
or exists (select 1 from bm_zlzd00 C1,yj_yw0000 d1 where C1.zlxmid=d1.zlxmid and d1.zyghid=d.zyghid and lbbh00='3'))
and zyghid not in (select ghid00 from sf_brxxb0 where jzks00='64'and brid00 in (select brid00 from bm_brxxb0 where TYDWMC like'%公安%'))
and yjksbh in ('55','27','127','47','129')  --检验、超声、放射、内镜、心电图          --by cly 20241121
--提取开了检查或者检验的病人，过滤急诊科的公安病人   --by cly 20241120
;