a.visit_id,--住院流水号,
a.idno,--身份证号
b.NAME,--姓名
decode(b.sex, 1, '男', '2', '女', '未知') as SexName,--性别
a.apply_id,--转诊申请单号
case a.statusCode when 0 then '待审核' when 1 then '同意' when 2 then '拒绝' when 3 then '入院' when 4 then '出院结算' else '未知' end as statusCode,--申请状态
b.IN_AMOUNT,--住院总费用
(select f.amount from fee_in_acct_detail f inner
join fee_in_account e on f.account_id = e.account_id and f.itemcode = '006-35' where e.visit_id = a.visit_id and f.account_id = b.ACCOUNT_NO and rownum < 2) as amount,--家医优惠金额
a.applydate,--申请时间
b.CREATE_ON,--操作时间,
d.unit_name,--转入医院名称
b.ENTER_TIME,--入院时间
b.OUT_TIME,--出院时间
a.deptadmisscode,--申请转入科室编号
a.deptadmiss ,--申请转入科室名称
b.ENTER_DEPT,--实际转入科室代码
c.dept_name,--实际转入科室
(select a1.diag_code from mrs_doc_diagnosis @sj_cis a1 where a1.visit_id = a.visit_id and a1.diag_type = '1' and rownum < 2) as outdiagcode, --门诊诊断编码
(select a1.diag_name from mrs_doc_diagnosis @sj_cis a1 where a1.visit_id = a.visit_id and a1.diag_type = '1' and rownum < 2) as outdiagName, --门诊诊断名称
(select a1.diag_code from mrs_doc_diagnosis @sj_cis a1 where a1.visit_id = a.visit_id and a1.diag_type = '2' and rownum < 2) as indiagcode, --入院诊断编码
(select a1.diag_name from mrs_doc_diagnosis @sj_cis a1 where a1.visit_id = a.visit_id and a1.diag_type = '2' and rownum < 2) as indiagName, --入院诊断名称
(select a1.diag_code from mrs_doc_diagnosis @sj_cis a1 where a1.visit_id = a.visit_id and a1.diag_type = '3' and rownum < 2) as cindiagcode, --出院诊断编码
(select a1.diag_name from mrs_doc_diagnosis @sj_cis a1 where a1.visit_id = a.visit_id and a1.diag_type = '3' and rownum < 2) as cindiagName, --出院诊断名称
b.CREATE_BY as CreatNo,--创建人工号
(select t.user_name from sys_user t where t.user_id = b.CREATE_BY) as CreateName,--创建人名称
a.fdid as ApplyDoctorId ,--申请医生工号
a.name as ApplyDoctorName, --申请医生名字
b.CLINIC_DOCTOR as ClinicDoctorId ,--收住医生编码
(select t1.user_name from sys_user t1 where t1.user_id = b.CLINIC_DOCTOR) as ClinicDoctorName,--收住医生姓名
a.fdorgcode, --管理机构编码
a.fdorgnam, --管理机构,
a.fdteamcode, --签约团队编码
a.fdteamname,--签约团队名称
a.receptiontime--接收日期
from his_familydoctor_apply a
left join cis_inpatient b on a.visit_id = b.VISIT_ID
left join sys_dept c
on b.ENTER_DEPT = c.dept_id
left join sys_unit d on d.unit_id =b.UNIT_ID
