HIS语法

–1、创建表空间 CASE
CONN ZHIYDBA/ZHIYDBA@MODIFY;
CREATE TABLESPACE “CASE” LOGGING DATAFILE ‘F:\DATABASE\ZHIS4\CASE.ora’ SIZE 5M EXTENT MANAGEMENT LOCAL;

ALTER DATABASE DATAFILE ‘F:\DATABASE\XMZYY\CASE.ORA’ RESIZE 10M;
ALTER DATABASE DATAFILE ‘F:\DATABASE\XMZYY\CASE.ORA’ AUTOEXTEND ON NEXT 10M;

–2、创建用户 CASE
CREATE USER “CASE” PROFILE “DEFAULT” IDENTIFIED BY “ZY02V4CE” DEFAULT TABLESPACE “CASE” TEMPORARY TABLESPACE “TMP” ACCOUNT UNLOCK;
GRANT “CONNECT” TO “CASE”;
RANT UNLIMITED TABLESPACE TO “CASE”

–3、创建表
conn zhiydba/zhiydba@modify

–病案基本信息表
create table case.case_basic_info
(residence_no varchar(20) not null primary key /*住院号*/,
case_no varchar(20) not null /*病案号*/,
visit_number number(3,0) not null /*住院次数*/,
sick_id char(10) not null /*病人ID*/,
sick_name varchar(20) not null /*病人姓名*/,
sex char(1) not null /*性别*/,
birth_date date not null /*出生日期*/,
age number(4,0) /*年龄*/ ,
age_unit varchar2(4) /*年龄单位*/,
marry_status char(1) not null /*婚姻状况*/,
work varchar2(4) /*职业*/ ,
sick_source varchar2(4) /*病人来源*/,
birth_palce varchar2(100) /*出生地*/,
nation varchar2(4) /*民族*/,
country varchar2(4) /*国籍*/,
idno varchar2(30) /*身份证号码*/,
work_unit varchar2(100) /*工作单位*/,
work_tel varchar2(20) /*工作单位电话*/,
work_postcode varchar2(20) /*工作单位邮编*/,
home_address varchar2(100) /*家庭地址*/,
home_tel varchar2(20) /*家庭电话*/,
home_postcode varchar2(20) /*家庭邮编*/,
contact_man varchar2(10) /*联系人*/,
relation varchar2(4) /*关系*/,
contact_address varchar2(100) /*联系人地址*/,
contact_tel varchar2(20) /*联系人电话*/,
in_date date not null /*入院日期*/,
in_dept varchar2(8) not null /*入院科室*/,
in_room varchar2(20) /*入院病室*/,
change_dept varchar2(8) /*转科科别*/,
out_date date not null /*出院日期*/,
out_dept varchar2(8) not null /*出院科室*/,
out_room varchar2(20) /*出院病室*/,
fact_days number(5,0) not null /*实际住院天数*/,
in_status char(1) not null /*入院时情况*/,
disp_diag varchar2(120) /*门诊诊断名称*/,
disp_diag_code varchar2(20) /*门诊诊断代码*/,
in_diag varchar2(120) /*入院诊断名称*/,
in_diag_code varchar2(20) /*入院诊断代码*/,
diagnose_date date /*入院后确诊日期*/,
pathology_diag varchar2(120) /*病理诊断*/,
pathology_diag_code varchar2(20) /*病理诊断代码*/,
mar_cause varchar2(120) /*损伤、中毒外部因素*/,
hbsag char(1) /*hbsag*/,
hcv_ab char(1) /*hcv_ab*/,
hiv_ab char(1) /*hiv_ab*/,
disp_out_same char(1) /*门诊与出院符合情况*/,
in_out_same char(1) /*入院与出院符合情况*/,
operat_same char(1) /*术前与术后符合情况*/,
clin_path_same char(1) /*临床与病理诊断情况*/,
shed_path_same char(1) /*放射与病理诊断情况*/,
aid_count number(4,0) /*抢救次数*/,
succeed_count number(4,0) /*成功次数*/,
dept_director varchar2(27) /*科主任*/,
doctor_director varchar2(27) /*主任医生*/,
doctor_attend varchar2(27) /*主治医生*/,
doctor_in varchar2(27) /*住院医生*/,
doctor_study varchar2(27) /*进修医生*/,
doctor_research varchar2(27) /*研究生实习医生*/,
doctor_practice varchar2(27) /*实习医生*/,
coding_operator varchar2(27) /*编码员*/,
case_quality char(1) /*病案质量*/,
control_doctor varchar2(27) /*质控医生 */,
control_nurse varchar2(27) /*质控护士 */,
case_date date /*质控日期*/,
pay_type char(1) /*付款方式*/,
is_follow char(1) /*是否随诊*/,
follow_range varchar2(20) /*随诊期限*/,
blood_type varchar2(4) /*血型*/,
rh char(1) /*RH*/,
blood_respone char(1) /*输血反应*/,
red_cell number(8,0) /*红细胞*/,
platelet number(8,0) /*血小板*/,
plasm number(8,0) /*血浆*/,
whole_blood number(8,0) /*全血*/,
other_blood number(8,0) /*其他血*/,
dead_inspect char(1) /*尸检*/,
frist_case char(1) /*是否本院第一例*/,
teach_case char(1) /*是否示教病历*/,
diag_pre varchar2(120) /*术前诊断*/,
diag_after varchar2(120) /*术后诊断*/,
is_antibiotic char(1) /*是否使用抗生素*/,
use_aim char(1) /*使用抗生素目的*/,
use_case char(1) /*使用方案*/,
use_days number(4,0) /*使用天数*/,
is_unite char(1) /*合并症*/,
is_syndrome char(1) /*并发症*/,
infection_report char(1) /*传染病报告*/,
input_oporator varchar2(27) /*录入者*/,
input_date date /*录入时间*/,
cure_class char(1) /*治疗类别*/,
case_class char(1) /*病历书写格式*/,
aid_is_chn char(1) /*是否中医药参与抢救*/,
is_argue_tend char(1) /*是否辨证施护*/,
is_blet char(1) /*急危重病人褥疮是否发生*/,
x_no varchar2(30) /*x光号*/,
ct_no varchar2(30) /*ct号*/,
mri_no varchar2(30) /*MRI号*/,
dsa_no varchar2(30) /*dsa号*/,
status varchar2(4) /*病案状态 */,
last_operator varchar2(27) /*最后修改人*/,
last_date date /*最后修改时间*/,
inout_date varchar2(27) /*上交时间*/,
dept_code varchar2(8) /*当前时间*/
);

create public synonym case_basic_info for case.case_basic_info;
conn case/zy02v4ce@zhis4;
grant select,insert,delete,update on case.case_basic_info to zhiydba with grant option;

–create table case.case_diagnose_info
–病案诊断信息表
conn zhiydba/zhiydba@zhis4;
create table case.case_diagnose_info
(residence_no varchar(20) not null /*住院号*/,
sub_no number(3,0) not null /*子序号*/,
disease_code varchar2(20) /*疾病代码*/,
disease_name varchar2(120) /*疾病名称*/,
assis_code varchar2(20) /*辅助码*/,
add_code varchar2(20) /*附加码*/,
add_name varchar2(120) /*附加名称*/,
cure_result varchar2(4) /*治疗结果*/,
is_doubt char(1) /*是否疑诊*/,
diag_class varchar2(4) /*诊断类别*/,
diag_date date /*诊断日期*/
);

alter table case.case_diagnose_info
add constraint pk_case_diagnose_info primary key(residence_no,sub_no);

create public synonym case_diagnose_info for case.case_diagnose_info;
conn case/zy02v4ce@zhis4;
grant select,insert,delete,update on case.case_diagnose_info to zhiydba with grant option;

–create table case.case_allergic_info
–病案药物过敏信息表
create table case.case_allergic_info
(residence_no varchar(20) not null /*住院号*/,
sub_no number(3,0) not null /*子序号*/,
code varchar2(4) not null /*过敏药物代码*/,
name varchar2(40) /*药物过敏名称*/
)

–create table case.case_allergic_info
–病案费用信息表
create table case.case_fee_info
(residence_no varchar(20) not null /*住院号*/,
class varchar2(4) not null /*类别*/,
cost number(10,2) /*费用*/
)

–create table case.case_diagnose_info
–病案手术信息表
create table case.case_operation_info
(residence_no varchar(20) not null /*住院号*/,
sub_no number(3,0) not null /*子序号*/,
operat_code varchar2(20) /*疾病代码*/,
operat_name varchar2(120) /*疾病名称*/,
operat_cut varchar2(4) /*辅助码*/,
anaesthesia_type varchar2(4) /*麻醉方法*/,
operat_doctor varchar2(27) /*术者*/
operat_doctor_1 varchar2(27) /*一助*/,
operat_doctor_2 varchar2(27) /*二助*/,
anaesthesia_doctor varchar2(27) /*麻醉医生*/,
operat_date datetime /*诊断日期*/,
operat_class varchar2(4) /*手术类别*/,
operat_type varchar2(4) /*手术种类*/,
anaesthesia_dead char(1) /*是否有麻醉死亡*/,
is_accord char(1) /*手术 是否符合 */,
is_fester char(1) /*是否化脓*/,
is_infect char(1) /*是否感染*/,
unite_diag varchar2(120) /*合并症*/,
untie_diag_code varchar2(20) /*合并症代码*/
)

–create table case.case_bear_info
—-病案分娩信息表

create table case.case_bear_info
(residence_no varchar(20) not null /*住院号*/,
sub_no number(3,0) not null /*子序号*/,
bear_no number(3,0) /*产次*/,
fetation_no number(3,0) /*孕次*/
weeks number(4,0) /*孕周*/,
days number(4,0) /*剩余天数*/,
fetus_count varchar2(4) /*胎数*/,
baby_status varchar2(4) /*婴儿情况*/,
birth_date datetime /*出生日期*/,
bear_way varchar2(4) /*分娩方式*/,
cut varchar2(4) /*会阴破裂*/,
sex varchar2(4) /*性别*/,
weight number(8,0) /*体重*/,
apgar number(4,1) /*APGAR评分*/,
doctor varchar2(27) /*接生医生*/
);

–create table case.case_addition_report 辅助科室报表不做，放到二次开发做
create table case.case_addition_report
(date_t datetime /*日期*/,
class varchar2(20) /*类别*/,
class_sub number(4,0) /*指标*/,
value number(10,2) /*指标值*/,
input_date datetime /*录入日期*/,
input_operator varchar2(27) /*录入人*/
)

–create table case.case_dayreport_disp 门诊日报
drop table case.case_dayreport_disp ;
create table case.case_dayreport_disp
(log_no varchar2(20) primary key /*流水号*/,
log_date date /*日期*/,
dept_code varchar2(8) /*科室*/,
clinic_group_code varchar2(8) /*医疗小组*/,
first_diag number(6,0) /*初诊*/,
further_diag number(6,0) /*复诊*/,
comm_diag number(6,0) /*普通门诊*/,
emergency_diag number(6,0) /*急诊*/,
expert_diag number(6,0) /*专家*/,
baby_diag number(6,0) /*儿保*/,
consultation_diag number(6,0) /*会诊*/,
director_diag number(6,0) /*主任、副主任号*/,
single_check number(6,0) /*单项体检*/,
whole_check number(6,0) /*全身体检*/,
operat_num number(6,0) /*本科室手术次数*/,
salve_num number(6,0) /*抢救次数*/,
succ_salve number(6,0) /*成功抢救次数*/,
out_diag_num number(6,0) /*出诊人数*/,
car_num number(6,0) /*120出车数*/,
home_bed_num number(6,0) /*新开设家庭病床数*/,
home_service_num number(6,0) /*家庭卫生服务人次数*/,
leave_num number(6,0) /*离院人数*/,
in_num number(6,0) /*入院人数*/,
in_look_num number(6,0) /*入观人数*/,
change_num number(6,0) /*转院人数*/,
dead_num number(6,0) /*死亡人数*/,
trauma_num number(6,0) /*外伤人数*/,
toxicosis_num number(6,0) /*中毒人数*/,
input_date date /*录入时间*/,
input_operator varchar2(27) /*录入人*/,
status varchar2(4) /*状态*/,
report_operator varchar2(27) /*上报人*/,
report_date date /*上报日期*/,
check_operator varchar2(27) /*审核人*/,
check_date date /*审核日期*/,
last_date date /*最后修改日期*/,
last_operator varchar2(27) /*最后修改人*/,
memo varchar2(1024) /*备注*/
);

create index case_dayreport_disp_idx1 on case.case_dayreport_disp(log_date,dept_code);

create public synonym case_dayreport_disp for case.case_dayreport_disp;

conn case/zy02v4ce@zhis4;
grant select,insert,delete,update on case.case_dayreport_disp to zhiydba with grant option;

–门诊日报流水号序列
conn zhiydba/zhiydba@zhis4;
create sequence COMM.CASE_DAYREPORT_DISP_SEQ
minvalue 1
maxvalue 9999999999
start with 1
increment by 1
cache 20
order;

create public synonym CASE_DAYREPORT_DISP_SEQ for COMM.CASE_DAYREPORT_DISP_SEQ;

–creat table case.case_dayreport_look 观察室日报
conn zhiydba/zhiydba@zhis4;
drop table case.case_dayreport_look;
create table case.case_dayreport_look
(log_date date /*日期*/,
dept_code varchar2(8) /*科室*/,
fact_bed_num number(6,0) /*实有观察床*/,
begin_num number(6,0) /*原有人数*/,
in_look_num number(6,0) /*入观人数*/,
out_look_num number(6,0) /*出观人数*/,
out_in_num number(6,0) /*出观入院人数*/,
out_change_num number(6,0) /*出观转院人数*/,
out_dead_num number(6,0) /*出观死亡人数*/,
out_return_num number(6,0) /*出观返回人数*/,
out_bed_num number(6,0) /*出观人员占用床位数*/,
open_bed_num number(6,0) /*开放观察床数*/,
end_num number(6,0) /*留观人数*/,
in_bed_num number(6,0) /*留观人员占用床位数*/,
input_date date /*录入日期*/,
input_operator varchar2(27) /*录入人员*/,
status varchar2(4) /*状态*/,
report_operator varchar2(27) /*上报人*/,
report_date date /*上报日期*/,
check_operator varchar2(27) /*审核人*/,
check_date date /*审核日期*/,
last_operator varchar2(27) /*最后修改人*/,
last_date date /*最后修改日期*/
);
alter table case.case_dayreport_look
add constraint pk_case_dayreport_look primary key(log_date,dept_code);
create public synonym case_dayreport_look for case.case_dayreport_look;

conn case/zy02v4ce@zhis4;
grant select,insert,delete,update on case.case_dayreport_look to zhiydba with grant option;

conn zhiydba/zhiydba@zhis4;
create sequence COMM.CASE_REPORTLOOK_NO_SEQ
minvalue 1
maxvalue 9999999999
start with 1
increment by 1
cache 20
order;

create public synonym CASE_REPORTLOOK_NO_SEQ for COMM.CASE_REPORTLOOK_NO_SEQ;

–create table case.case_dayreport_resi 住院日报
conn zhiydba/zhiydba@zhis4;
drop table case.case_dayreport_resi;
create table case.case_dayreport_resi
( log_no varchar2(20) primary key /*日志号 */,
status varchar2(4)/*状态*/,
report_operator varchar2(27) /*上报人*/,
report_date date /*上报日期*/,
input_operator varchar2(27) /*录入人员*/,
input_date date /*录入日期*/,
check_operator varchar2(27) /*审核人*/,
check_date date /*审核日期*/,
last_date date /*最后修改日期*/,
last_operator varchar2(27) /*最后修改人*/,
dept_code varchar2(8) not null/*科室代码*/,
clinic_group_code varchar2(8) /*医组代码*/,
log_date date not null/*日期*/,
begin_sick_num number(6,0)/*本日原有人数*/ ,
in_hospital_num number(6,0)/*入院人数*/,
trans_in_num number(6,0)/*转入人数*/,
out_num number(6,0)/*出院人数*/,
out_cure_num number(6,0)/*出院治愈人数*/,
out_mend_num number(6,0)/*出院好转人数*/,
out_uncure_num number(6,0)/*出院未愈人数*/,
out_auto_num number(6,0)/*自动出院人数*/,
out_dead_num number(6,0)/*出院死亡人数*/,
out_quickdead_num number(6,0)/*出院24小时死亡人数*/,
out_nonsick_num number(6,0)/*出院非病人数*/,
out_bearing_num number(6,0)/*其中计划生育人数*/,
out_childbirth_num number(6,0)/*其中正常分娩人数*/,
out_other_num number(6,0)/*其他出院人数*/,
trans_out_num number(6,0)/*转出人数*/,
end_sick_num number(6,0)/*本日现有人数*/ ,
occupy_bed_num number(8,1)/*出院病人占用床日总数*/,
accompany_num number(6,0)/*陪伴人数*/,
mistake_num number(6,0)/*差错次数*/,
memo varchar2(1024)/*备注*/,
salve_num number(6,0) /*抢救次数*/,
salve_succ number(6,0) /*抢救成功次数*/,
dangerous_num number(6,0) /*病危人数*/,
emergency_num number(6,0) /*病重人数*/,
operation_num number(6,0) /*手术次数*/,
transfusion number(6,0) /*输液次数*/,
transfusion_feedback number(6,0) /*输液反映次数*/,
blood number(6,0) /*输血次数*/,
blood_feedback number(6,0) /*输血反映次数*/,
bed_fact_num number(6,0)/*实际开放床数*/,
bed_have_num number(6,0)/*实有床位数*/,
bed_theory_num number(6,0)/*编制床位数*/,
cure_bed_num number(8,1)/*治愈出院病人占用床日总数*/,
in_bed_num number(10,1)/*住院病人占用床日总数*/,
bed_lend_num number(6,0) /*他科借出床数*/,
bed_temp_num number(6,0) /*临时加床数*/,
bed_empty_num number(6,0)/*空床数*/,
bed_pause_num number(6,0) /*暂停使用数*/,
bed_borrow_num number(6,0)/*他科借入床数*/,
bad_knub_num number(6,0)/*恶性肿瘤数*/,
origin_cancer_num number(6,0) /*原位癌数*/,
good_knub_num number(6,0) /*良性肿瘤数*/,
cross_bad_num number(6,0) /*交界恶性数*/,
dynamic_unknow_num number(6,0) /*动态未知数*/,
out_province_num number(6,0) /*外省人数*/,
out_area_num number(6,0) /*本省其他地区人数*/,
environs_num number(6,0) /*本市郊县人数*/,
city_num number(6,0) /*本市县区人数*/,
other_num number(6,0) /*本市其他人数*/,
out_country_num number(6,0) /*国外人数*/
);
create public synonym case_dayreport_resi for case.case_dayreport_resi;
create index case_dayreport_resi_idx1 on case.case_dayreport_resi(log_date,dept_code,clinic_group_code);
conn case/zy02v4ce@zhis4;
grant select,insert,delete,update on case.case_dayreport_resi to zhiydba with grant option;

–住院日报流水号序列
conn zhiydba/zhiydba@zhis4;
create sequence COMM.CASE_DAYREPORT_RESI_SEQ
minvalue 1
maxvalue 9999999999
start with 1
increment by 1
cache 20
order;

create public synonym CASE_DAYREPORT_RESI_SEQ for COMM.CASE_DAYREPORT_RESI_SEQ;

–create table case.case_dayreport_resi 住院月报
conn zhiydba/zhiydba@zhis4;
drop table case.case_monthreport_resi
create table case.case_monthreport_resi
( log_no varchar2(20) primary key /*日志号 */,
year number(6,0) /*年*/,
month number(4,0) /*月*/,
last_date date /*最后修改日期*/,
last_operator varchar2(27) /*最后修改人*/,
dept_code varchar2(8) not null/*科室代码*/,
clinic_group_code varchar2(8) /*医组代码*/,
begin_sick_num number(10,0)/*本月原有人数*/ ,
in_hospital_num number(10,0)/*入院人数*/,
trans_in_num number(10,0)/*转入人数*/,
out_num number(10,0)/*出院人数*/,
out_cure_num number(10,0)/*出院治愈人数*/,
out_mend_num number(10,0)/*出院好转人数*/,
out_uncure_num number(10,0)/*出院未愈人数*/,
out_auto_num number(10,0)/*自动出院人数*/,
out_dead_num number(10,0)/*出院死亡人数*/,
out_quickdead_num number(10,0)/*出院24小时死亡人数*/,
out_nonsick_num number(10,0)/*出院非病人数*/,
out_bearing_num number(10,0)/*其中计划生育人数*/,
out_childbirth_num number(10,0)/*其中正常分娩人数*/,
out_other_num number(10,0)/*其他出院人数*/,
trans_out_num number(10,0)/*转出人数*/,
end_sick_num number(10,0)/*本日现有人数*/ ,
occupy_bed_num number(11,1)/*出院病人占用床日总数*/,
accompany_num number(10,0)/*陪伴人数*/,
mistake_num number(10,0)/*差错次数*/,
memo varchar2(1024)/*备注*/,
salve_num number(10,0) /*抢救次数*/,
salve_succ number(10,0) /*抢救成功次数*/,
dangerous_num number(10,0) /*病危人数*/,
emergency_num number(10,0) /*病重人数*/,
operation_num number(10,0) /*手术次数*/,
transfusion number(10,0) /*输液次数*/,
transfusion_feedback number(10,0) /*输液反映次数*/,
blood number(10,0) /*输血次数*/,
blood_feedback number(10,0) /*输血反映次数*/,
bed_fact_num number(10,0)/*实际开放床数*/,
bed_have_num number(10,0)/*实有床位数*/,
bed_theory_num number(10,0)/*编制床位数*/,
cure_bed_num number(11,1)/*治愈出院病人占用床日总数*/,
in_bed_num number(12,1)/*住院病人占用床日总数*/,
bed_lend_num number(10,0) /*他科借出床数*/,
bed_temp_num number(10,0) /*临时加床数*/,
bed_empty_num number(10,0)/*空床数*/,
bed_pause_num number(10,0) /*暂停使用数*/,
bed_borrow_num number(10,0)/*他科借入床数*/,
bad_knub_num number(10,0)/*恶性肿瘤数*/,
origin_cancer_num number(10,0) /*原位癌数*/,
good_knub_num number(10,0) /*良性肿瘤数*/,
cross_bad_num number(10,0) /*交界恶性数*/,
dynamic_unknow_num number(10,0) /*动态未知数*/,
out_province_num number(10,0) /*外省人数*/,
out_area_num number(10,0) /*本省其他地区人数*/,
environs_num number(10,0) /*本市郊县人数*/,
city_num number(10,0) /*本市县区人数*/,
other_num number(10,0) /*本市其他人数*/,
out_country_num number(10,0) /*国外人数*/
);
create public synonym case_monthreport_resi for case.case_monthreport_resi;
create index case_monthreport_resi_idx1 on case.case_monthreport_resi(year,month);
conn case/zy02v4ce@zhis4;
grant select,insert,delete,update on case.case_monthreport_resi to zhiydba with grant option;

–creat table case.case_monthreport_look 观察室月报
create table case.case_monthreport_look
(year number(6,0) /*年*/,
month number(4,0) /*月*/,
dept_code varchar2(8) /*科室*/,
fact_bed_num number(10,0) /*实有观察床*/,
begin_num number(10,0) /*原有人数*/,
in_look_num number(10,0) /*入观人数*/,
out_look_num number(10,0) /*出观人数*/,
out_in_num number(10,0) /*出观入院人数*/,
out_change_num number(10,0) /*出观转院人数*/,
out_dead_num number(10,0) /*出观死亡人数*/,
out_return_num number(10,0) /*出观返回人数*/,
out_bed_num number(10,0) /*出观人员占用床位数*/,
open_bed_num number(10,0) /*开放观察床数*/,
end_num number(10,0) /*留观人数*/,
in_bed_num number(10,0) /*留观人员占用床位数*/,
last_operator varchar2(27) /*最后修改人*/,
last_date date /*最后修改日期*/
);
alter table case.case_monthreport_look
add constraint pk_case_monthreport_look primary key(year,month,dept_code);
create public synonym case_monthreport_look for case.case_monthreport_look;

conn case/zy02v4ce@zhis4;
grant select,insert,delete,update on case.case_monthreport_look to zhiydba with grant option;

–create table case.case_dayreport_disp 门诊月报
drop table case.case_dayreport_disp ;
create table case.case_monthreport_disp
(log_no varchar2(20) primary key /*流水号*/,
year number(6,0) /*年*/,
month number(4,0) /*月*/,
dept_code varchar2(8) /*科室*/,
clinic_group_code varchar2(8) /*医疗小组*/,
first_diag number(10,0) /*初诊*/,
further_diag number(10,0) /*复诊*/,
comm_diag number(10,0) /*普通门诊*/,
emergency_diag number(10,0) /*急诊*/,
expert_diag number(10,0) /*专家*/,
baby_diag number(10,0) /*儿保*/,
consultation_diag number(10,0) /*会诊*/,
director_diag number(10,0) /*主任、副主任号*/,
single_check number(10,0) /*单项体检*/,
whole_check number(10,0) /*全身体检*/,
operat_num number(10,0) /*本科室手术次数*/,
salve_num number(10,0) /*抢救次数*/,
succ_salve number(10,0) /*成功抢救次数*/,
out_diag_num number(10,0) /*出诊人数*/,
car_num number(10,0) /*120出车数*/,
home_bed_num number(10,0) /*新开设家庭病床数*/,
home_service_num number(10,0) /*家庭卫生服务人次数*/,
leave_num number(10,0) /*离院人数*/,
in_num number(10,0) /*入院人数*/,
in_look_num number(10,0) /*入观人数*/,
change_num number(10,0) /*转院人数*/,
dead_num number(10,0) /*死亡人数*/,
trauma_num number(10,0) /*外伤人数*/,
toxicosis_num number(10,0) /*中毒人数*/,
last_date date /*最后修改日期*/,
last_operator varchar2(27) /*最后修改人*/,
memo varchar2(1024) /*备注*/
);

create index case_monthreport_disp_idx1 on case.case_monthreport_disp (year,month,dept_code,clinic_group_code );

create public synonym case_monthreport_disp for case.case_monthreport_disp ;

conn case/zy02v4ce@zhis4;
grant select,insert,delete,update on case.case_monthreport_disp to zhiydba with grant option;

–create table case.case_borrow_record –借阅记录
conn zhiydba/zhiydba@zhis4;
drop table case.case_borrow_record ;
create table case.case_borrow_record
(log_no varchar2(20) not null primary key,
status varchar2(4) /*状态*/,
borrow_date date not null /*借阅日期*/,
borrow_man varchar2(27) not null /*借阅人*/,
borrow_dept varchar2(8) /*借阅科室*/,
residence_no varchar2(20) /*住院号*/,
return_date date /*归还日期*/,
return_operator varchar2(27) /*归还操作者*/,
until_date date /*实际截止日期*/,
first_until_date date /*截止日期*/,
continue_num number(3,0) /*续借次数*/,
input_operator varchar2(27) /*录入人*/,
input_date date /*录入日期*/,
last_modifier varchar2(27) /*最后修改人*/,
last_time varchar2(27) /*最后修改时间*/,
memo varchar2(1024) /*备注*/
);

create public synonym case_borrow_record for case.case_borrow_record ;
create index case.case_borrow_record on case.case_borrow_record(borrow_date);

conn case/zy02v4ce@zhis4;
grant select,delete,update,insert on case.case_borrow_record to zhiydba with grant option;

conn zhiydba/zhiydba@zhis4;
create sequence COMM.CASE_BORROW_NO_SEQ
minvalue 1
maxvalue 9999999999
start with 1
increment by 1
cache 20
order;

create public synonym CASE_BORROW_NO_SEQ for COMM.CASE_BORROW_NO_SEQ;

–create table case.case_inout_master –交接主表
conn zhiydba/zhiydba@zhis4;
drop table case.case_inout_master;
create table case.case_inout_master
(log_no varchar2(20) not null primary key /*流水号主键*/,
out_dept varchar2(8) not null /*出让方*/,
in_dept varchar2(8) not null /*接收方*/,
type varchar2(4) not null /*交接类型*/,
status varchar2(4) not null /*状态*/,
out_operator varchar2(27) not null /*出让人*/,
in_operator varchar2(27) not null /*接手人*/,
inout_date date /*交接时间*/,
input_date date /*记录时间*/,
input_dept varchar2(8) /*开单科室*/,
input_operator varchar2(27) /*记录人*/,
check_date date /*审核日期*/,
check_operator varchar2(27) /*审核人*/ ,
cancel_date date /*作废日期*/,
cancel_operator varchar2(27) /*作废人*/,
last_operator varchar2(27) /*最后修改人*/,
last_date date /*最后修改时间*/,
memo varchar2(27) /*备注*/
);
create public synonym case_inout_master for case.case_inout_master;
create index case.case_inout_master_idx1 on case.case_inout_master(input_date);

conn case/zy02v4ce@zhis4;
grant select,delete,update,insert on case.case_inout_master to zhiydba with grant option;

— Create sequence
–病案交接流水号序列
conn zhiydba/zhiydba@zhis4;
create sequence COMM.CASE_INOUT_NO_SEQ
minvalue 1
maxvalue 9999999999
start with 1
increment by 1
cache 20
order;

create public synonym CASE_INOUT_NO_SEQ for COMM.CASE_INOUT_NO_SEQ;

–create table case.case_inout_detail –交接细表
conn zhiydba/zhiydba@zhis4;
drop table case.case_inout_detail;
create table case.case_inout_detail
(log_no varchar2(20) not null /*流水号*/,
sub_no number(4,0) not null /*子序号*/,
residence_no varchar2(20) not null /*病案号*/,
sick_id char(10) /*病人ID*/,
visit_number number(4,0) /*住院次数*/,
sick_name varchar2(20) /*姓名*/,
dept_code varchar2(8) /*出院科室*/
);

alter table case.case_inout_detail
add constraint pk_case_inout_detail primary key(log_no,sub_no);
create public synonym case_inout_detail for case.case_inout_detail ;

conn case/zy02v4ce@zhis4;
grant select,delete,update,insert on case.case_inout_detail to zhiydba with grant option;

–复印记录表
conn zhiydba/zhiydba@zhis4;
create table case.case_copy_record
(log_no varchar2(20) not null primary key /*流水号主键*/,
copy_date date /*复印日期*/,
ask_copy_man varchar2(20) /*要求复印人*/,
id_type varchar2(4) /*证件类型*/,
id_no varchar2(30) /*要求复印人身份证号码*/,
copy_operator varchar2(27) /*复印人*/,
input_operator varchar2(27) /*录入者*/,
input_date date /*录入日期*/,
copies number(4,0) /*复印份数*/,
copy_fee number(8,2) /*复印费用*/,
residence_no varchar2(20) not null /*病案号*/,
status varchar2(4) /*状态*/,
last_operator varchar2(27) /*最后修改人*/,
last_date date /*最后修改时间*/,
memo varchar2(1024) /*备注*/
);

create public synonym case_copy_record for case.case_copy_record;
create index case.idx_case_copy_record_1 on case_copy_record(copy_date);
conn case/zy02v4ce@zhis4;
grant select,insert,delete,update on case.case_copy_record to zhiydba with grant option;

–病案交接流水号序列
conn zhiydba/zhiydba@zhis4;
create sequence COMM.CASE_COPY_NO_SEQ
minvalue 1
maxvalue 9999999999
start with 1
increment by 1
cache 20
order;

create public synonym CASE_COPY_NO_SEQ for COMM.CASE_COPY_NO_SEQ;

–评分记录细表
create table case.case_grade_record
(residence_no varchar2(20) not null /*住院号*/,
sub_number number(4,0) not null /*子序号*/,
type varchar2(4) not null /*类别*/,
grade_doctor varchar2(27) /*评分医生*/,
grade_date date /*评分时间*/,
filed varchar2(4) /*指标*/,
value number(6,2) /*指标值*/,
status varchar2(4) /*状态*/,
memo varchar2(1024) /*备注*/
)

–病种分类表
conn zhiydba/zhiydba@zhis4;
drop table case.case_disease_class;
create table case.case_disease_class
(disease_code varchar2(20) not null primary key /*病种代码*/ ,
class_code varchar2(4) not null /*病种类类别 */,
disease_name varchar2(40) not null /*病种名称 */,
begin_icd varchar2(20) /*起始ICD10*/,
end_icd varchar2(20) /*终止ICD10*/,
data_source varchar2(4) /*数据来源*/,
valid_flag char(1) /*有效标志*/,
add_code_begin varchar2(20) /*附加码起始*/,
add_code_end varchar2(20) /*终止*/,
spell_code varchar2(100) /*拼音码*/,
wbzx_code varchar2(10) /*五笔码*/,
memo varchar2(1024) /*备注*/,
last_operator varchar2(27) /*最后修改人*/,
last_date date /*最后修改日期*/
);
drop public synonym case_disease_class ;
create public synonym case_disease_class for case.case_disease_class;

conn case/zy02v4ce@zhis4;
grant select,delete,update,insert on case.case_disease_class to zhiydba with grant option;

–床位数记录表
conn zhiydba/zhiydba@zhis4;
drop table case.case_bed_num;
create table case.case_bed_num
(seq_no number(12,0) primary key /*流水号*/,
dept_code varchar2(8) not null /*科室代码*/,
clinic_group_code varchar2(8) /*医疗小组*/,
dept_type varchar2(4) /*科室属性 0:都不是 1:病区科室 2:观察室 3:门诊科室 */,
case_dept_code varchar2(8) /*病案科室代码*/,
bed_theory_num number(6,0) /*编制床位数*/,
bed_have_num number(6,0) /*实有床位数*/,
bed_fact_num number(6,0) /*实际开放床数*/,
bed_pause_num number(6,0) /*暂停使用数*/,
bed_borrow_num number(6,0) /*他科借入床数*/,
bed_lend_num number(6,0) /*他科借出床数*/,
memo varchar2(1024) /*备注*/,
last_operator varchar2(27) /*最后修改人*/,
last_date date /*最好修改时间*/,
sort_no number(4,0) /*排序号*/
);

create public synonym case_bed_num for case.case_bed_num;

conn case/zy02v4ce@zhis4;
grant select,delete,update,insert on case.case_bed_num to zhiydba with grant option;

–病案科室字典
conn zhiydba/zhiydba@zhis4;
drop table case.case_dept_dict
create table case.case_dept_dict
(dept_code varchar2(8) primary key /*科室代码*/,
dept_name varchar2(200) not null /*科室名称*/,
dept_propety varchar2(4) /*科室属性 0:都不是 1:病区科室 2:观察室 3:门诊科室 */,
under_dept varchar2(8) /*上级科室*/,
valid_flag char(1) not null /*有效标志*/,
bed_theory_num number(6,0) /*编制床位数*/,
bed_have_num number(6,0) /*实有床位数*/,
bed_fact_num number(6,0) /*实际开放床数*/,
bed_pause_num number(6,0) /*暂停使用数*/,
bed_borrow_num number(6,0) /*他科借入床数*/,
bed_lend_num number(6,0) /*他科借出床数*/,
memo varchar2(1024) /*备注*/,
spell_code varchar2(100) /*拼音首码*/,
wbzx_code varchar2(10) /*五笔码*/,
last_operator varchar2(27) /*最后修改人*/,
last_date date /*最好修改时间*/,
sort_no number(4,0) /*排序号*/
);

create public synonym case_dept_dict for case.case_dept_dict;

conn case/zy02v4ce@zhis4;
grant select,delete,update,insert on case.case_dept_dict to zhiydba with grant option;

–病案科室与HIS科室对照表
conn zhiydba/zhiydba@zhis4;
drop table case.case_dept_give;
create table case.case_dept_give
(case_dept_code varchar2(8)/*病案科室代码*/,
dept_code varchar2(8) /*his科室代码*/);

alter table case.case_dept_give
add constraint pk_case_dept_give primary key(case_dept_code ,dept_code);
create public synonym case_dept_give for case.case_dept_give;

conn case/zy02v4ce@zhis4;
grant insert,delete,update,select on case.case_dept_give to zhiydba with grant option;
==================
–观察室报表标准视图
drop view case.case_dayreport_look_vw;
drop public synonym case_dayreport_look_vw;
create or replace view case.v_case_dayreport_look
as select sysdate as log_date/*日期*/,
” as dept_code/*科室*/,
0 as fact_bed_num/*实有观察床*/,
0 as open_bed_num/*开放观察床数*/,
0 as begin_num/*原有人数*/,
0 as in_look_num/*入观人数*/,
0 as out_look_num/*出观人数*/,
0 as out_in_num/*出观入院人数*/,
0 as out_change_num/*出观转院人数*/,
0 as out_dead_num/*出观死亡人数*/,
0 as out_return_num/*出观返回人数*/,
0 as out_bed_num/*出观人员占用床位数*/,
0 as end_num/*留观人数*/,
0 as in_bed_num/*留观人员占用床位数*/
from dual
where 1=2;

create public synonym v_case_dayreport_look for case.v_case_dayreport_look

–门诊日报 标准视图

conn zhiydba/zhiydba@zhis4;
grant select on dispensary_sick_cure_info to case;
grant select on register_type to case;
drop view case.case_dayreport_disp_view;
drop public synonym case_dayreport_disp_vw;
create or replace view case.v_case_dayreport_disp
as select register_time as log_date/*日期*/,
nvl(cure_dept,’未就诊’) as dept_code/*挂号科室*/,
REGISTER_DOCTOR_GROUP_CODE as clinic_group_code/*医疗小组*/,
decode(trim(first_flag),’0′,1,0) as first_diag/*初诊*/,
decode(trim(first_flag),’1′,1,0) as further_diag/*复诊*/,
decode(b.control_flag,’N1′, 0,’2′, 0,1) as comm_diag/*普通门诊*/,
decode(b.control_flag,’2′, 1,0) as emergency_diag/*急诊*/,
decode(b.control_flag,’N1′, 1,0) as expert_diag/*专家*/,
decode(a.cure_dept,’儿保科代码’, 1,0) as baby_diag/*儿保*/,
0 as consultation_diag/*会诊*/ ,
decode(b.control_flag,’N1′, 1,0) as director_diag/*副、主任*/ ,
0 as single_check /*单项体检*/,
0 as whole_check /*全身体检*/,
0 as operat_num /*本科室手术次数*/,
0 as salve_num /*抢救次数*/,
0 as succ_salve /*成功抢救次数*/,
0 as out_diag_num/*出诊人数*/,
0 as car_num /*120出车数*/,
0 as home_bed_num/*新开设家庭病床数*/ ,
0 as home_service_num/*家庭卫生服务人次数*/ ,
0 as leave_num/*离院人数*/,
0 as in_num/*入院人数*/,
0 as in_look_num/*入观人数*/,
0 as change_num/*转院人数*/ ,
0 as dead_num/*死亡人数*/,
0 as trauma_num/*外伤人数*/,
0 as toxicosis_num/*中毒人数*/
from dispensary_sick_cure_info a,
register_type b
where a.clinic_class =b.code
and a.register_status not in (‘1’, ‘4’);

create public synonym v_case_dayreport_disp for case.v_case_dayreport_disp

–住院日报标准视图
conn zhiydba/zhiydba@zhis4;
grant select on sick_transfer_record to case;
grant select on SICK_CASE_HEADER to case;
grant select on app_operat_main to case;
drop table case.case_dayreport_resi_vw;
drop public synonym case_dayreport_resi_vw;
create or replace view case.v_case_dayreport_resi
as select stayed_dept as dept_code/*科室*/,
discharge_time as log_date/*日期*/,
decode(type, ‘0’, decode(event_state, ‘1’, 1, 0), 0) as in_hospital_num/*入院人数*/,
0 as trans_in_num/*转入人数*/,
0 as out_num/*出院人数*/,
0 as out_cure_num/*出院治愈人数*/,
0 as out_mend_num/*出院好转人数*/,
0 as out_uncure_num/*出院未愈人数*/,
0 as out_auto_num /*自动出院人数*/,
0 as out_dead_num/*出院死亡人数*/,
0 as out_quickdead_num/*出院24小时死亡人数*/,
0 as out_nonsick_num /*出院非病人数*/,
0 as out_bearing_num/*其中计划生育人数*/,
0 as out_childbirth_num /*其中正常分娩人数*/,
0 as out_other_num/*其他出院人数*/,
DECODE(TYPE, ‘1’, DECODE(EVENT_STATE, ‘1’, 1, 0), 0) as trans_out_num /*转出人数*/,
0 as end_sick_num/*本日现有人数*/,
0 as occupy_bed_num/*出院病人占用床日总数*/,
0 as cure_bed_num /*治愈出院病人占用床日总数*/,
0 as in_bed_num /*住院病人占用床日总数*/,
0 as accompany_num /*陪伴人数*/,
0 as mistake_num/*差错次数*/,
0 as salve_num /*抢救次数*/,
0 as salve_succ/*抢救成功次数*/,
0 as dangerous_num /*病危人数*/,
0 as emergency_num/*病重人数*/,
0 as operation_num/*手术次数*/,
0 as transfusion/*输液次数*/,
0 as transfusion_feedback/*输液反映次数*/,
0 as blood /*输血次数*/,
0 as blood_feedback/*输血反映次数*/,
0 as bed_fact_num/*实际开放床数*/,
0 as bed_have_num/*实有床位数*/,
0 as bed_theory_num/*编制床位数*/,
0 as bed_lend_num/*他科借出床数*/,
0 as bed_temp_num/*临时加床数*/,
0 as bed_empty_num/*空床数*/,
0 as bed_pause_num/*暂停使用数*/,
0 as bed_borrow_num/*他科借入床数*/,
0 as bad_knub_num/*恶性肿瘤数*/,
0 as origin_cancer_num/*原位癌数*/,
0 as good_knub_num/*良性肿瘤数*/,
0 as cross_bad_num/*交界恶性数*/,
0 as dynamic_unknow_num/*动态未知数*/,
0 as out_province_num/*外省人数*/,
0 as out_area_num/*本省其他地区人数*/,
0 as environs_num/*本市郊县人数*/,
0 as city_num /*本市县区人数*/,
0 as other_num/*本市其他人数*/,
0 out_country_num/*国外人数*/
from sick_transfer_record
union all
select transfered_dept as dept_code/*科室*/,
discharge_time as log_date/*日期*/,
0 as in_hospital_num/*入院人数*/,
DECODE(TYPE, ‘1’, DECODE(EVENT_STATE, ‘1’, 1, 0), 0) as trans_in_num/*转入人数*/,
0 as out_num/*出院人数*/,
0 as out_cure_num/*出院治愈人数*/,
0 as out_mend_num/*出院好转人数*/,
0 as out_uncure_num/*出院未愈人数*/,
0 as out_auto_num /*自动出院人数*/,
0 as out_dead_num/*出院死亡人数*/,
0 as out_quickdead_num/*出院24小时死亡人数*/,
0 as out_nonsick_num /*出院非病人数*/,
0 as out_bearing_num/*其中计划生育人数*/,
0 as out_childbirth_num /*其中正常分娩人数*/,
0 as out_other_num/*其他出院人数*/,
0 as trans_out_num /*转出人数*/,
0 as end_sick_num/*本日现有人数*/,
0 as occupy_bed_num/*出院病人占用床日总数*/,
0 as cure_bed_num /*治愈出院病人占用床日总数*/,
0 as in_bed_num /*住院病人占用床日总数*/,
0 as accompany_num /*陪伴人数*/,
0 as mistake_num/*差错次数*/,
0 as salve_num /*抢救次数*/,
0 as salve_succ/*抢救成功次数*/,
0 as dangerous_num /*病危人数*/,
0 as emergency_num/*病重人数*/,
0 as operation_num/*手术次数*/,
0 as transfusion/*输液次数*/,
0 as transfusion_feedback/*输液反映次数*/,
0 as blood /*输血次数*/,
0 as blood_feedback/*输血反映次数*/,
0 as bed_fact_num/*实际开放床数*/,
0 as bed_have_num/*实有床位数*/,
0 as bed_theory_num/*编制床位数*/,
0 as bed_lend_num/*他科借出床数*/,
0 as bed_temp_num/*临时加床数*/,
0 as bed_empty_num/*空床数*/,
0 as bed_pause_num/*暂停使用数*/,
0 as bed_borrow_num/*他科借入床数*/,
0 as bad_knub_num/*恶性肿瘤数*/,
0 as origin_cancer_num/*原位癌数*/,
0 as good_knub_num/*良性肿瘤数*/,
0 as cross_bad_num/*交界恶性数*/,
0 as dynamic_unknow_num/*动态未知数*/,
0 as out_province_num/*外省人数*/,
0 as out_area_num/*本省其他地区人数*/,
0 as environs_num/*本市郊县人数*/,
0 as city_num /*本市县区人数*/,
0 as other_num/*本市其他人数*/,
0 out_country_num/*国外人数*/
from sick_transfer_record
union all
select out_dept as dept_code/*科室*/,
OUT_TIME as log_date/*日期*/,
0 as in_hospital_num/*入院人数*/,
0 as trans_in_num/*转入人数*/,
1 as out_num/*出院人数*/,
DECODE(DISEASE_STATUS, ‘1’, 1, 0) as out_cure_num/*出院治愈人数*/,
DECODE(DISEASE_STATUS, ‘2’, 1, 0) as out_mend_num/*出院好转人数*/,
DECODE(DISEASE_STATUS, ‘3’, 1, 0) as out_uncure_num/*出院未愈人数*/,
0 as out_auto_num /*自动出院人数*/,
DECODE(DISEASE_STATUS, ‘4’, 1, 0) as out_dead_num/*出院死亡人数*/,
0 as out_quickdead_num/*出院24小时死亡人数*/,
0 as out_nonsick_num /*出院非病人数*/,
0 as out_bearing_num/*其中计划生育人数*/,
0 as out_childbirth_num /*其中正常分娩人数*/,
DECODE(DISEASE_STATUS, ‘5’, 1, 0) as out_other_num/*其他出院人数*/,
0 as trans_out_num /*转出人数*/,
0 as end_sick_num/*本日现有人数*/,
DECODE(TRUNC(OUT_TIME) – TRUNC(IN_TIME), 0, 1, TRUNC(OUT_TIME) – TRUNC(IN_TIME)) as occupy_bed_num/*出院病人占用床日总数*/,
DECODE(DISEASE_STATUS, ‘1’,DECODE(TRUNC(OUT_TIME) – TRUNC(IN_TIME), 0, 1, TRUNC(OUT_TIME) – TRUNC(IN_TIME)),0) as cure_bed_num /*治愈出院病人占用床日总数*/,
0 as in_bed_num /*住院病人占用床日总数*/,
0 as accompany_num /*陪伴人数*/,
0 as mistake_num/*差错次数*/,
0 as salve_num /*抢救次数*/,
0 as salve_succ/*抢救成功次数*/,
0 as dangerous_num /*病危人数*/,
0 as emergency_num/*病重人数*/,
0 as operation_num/*手术次数*/,
0 as transfusion/*输液次数*/,
0 as transfusion_feedback/*输液反映次数*/,
0 as blood /*输血次数*/,
0 as blood_feedback/*输血反映次数*/,
0 as bed_fact_num/*实际开放床数*/,
0 as bed_have_num/*实有床位数*/,
0 as bed_theory_num/*编制床位数*/,
0 as bed_lend_num/*他科借出床数*/,
0 as bed_temp_num/*临时加床数*/,
0 as bed_empty_num/*空床数*/,
0 as bed_pause_num/*暂停使用数*/,
0 as bed_borrow_num/*他科借入床数*/,
0 as bad_knub_num/*恶性肿瘤数*/,
0 as origin_cancer_num/*原位癌数*/,
0 as good_knub_num/*良性肿瘤数*/,
0 as cross_bad_num/*交界恶性数*/,
0 as dynamic_unknow_num/*动态未知数*/,
0 as out_province_num/*外省人数*/,
0 as out_area_num/*本省其他地区人数*/,
0 as environs_num/*本市郊县人数*/,
0 as city_num /*本市县区人数*/,
0 as other_num/*本市其他人数*/,
0 out_country_num/*国外人数*/
from SICK_CASE_HEADER

union all
select dept_stayed as dept_code/*科室*/,
fill_in_date as log_date/*日期*/,
0 as in_hospital_num/*入院人数*/,
0 as trans_in_num/*转入人数*/,
0 as out_num/*出院人数*/,
0 as out_cure_num/*出院治愈人数*/,
0 as out_mend_num/*出院好转人数*/,
0 as out_uncure_num/*出院未愈人数*/,
0 as out_auto_num /*自动出院人数*/,
0 as out_dead_num/*出院死亡人数*/,
0 as out_quickdead_num/*出院24小时死亡人数*/,
0 as out_nonsick_num /*出院非病人数*/,
0 as out_bearing_num/*其中计划生育人数*/,
0 as out_childbirth_num /*其中正常分娩人数*/,
0 as out_other_num/*其他出院人数*/,
0 as trans_out_num /*转出人数*/,
0 as end_sick_num/*本日现有人数*/,
0 as occupy_bed_num/*出院病人占用床日总数*/,
0 as cure_bed_num /*治愈出院病人占用床日总数*/,
0 as in_bed_num /*住院病人占用床日总数*/,
0 as accompany_num /*陪伴人数*/,
0 as mistake_num/*差错次数*/,
0 as salve_num /*抢救次数*/,
0 as salve_succ/*抢救成功次数*/,
0 as dangerous_num /*病危人数*/,
0 as emergency_num/*病重人数*/,
1 as operation_num/*手术次数*/,
0 as transfusion/*输液次数*/,
0 as transfusion_feedback/*输液反映次数*/,
0 as blood /*输血次数*/,
0 as blood_feedback/*输血反映次数*/,
0 as bed_fact_num/*实际开放床数*/,
0 as bed_have_num/*实有床位数*/,
0 as bed_theory_num/*编制床位数*/,
0 as bed_lend_num/*他科借出床数*/,
0 as bed_temp_num/*临时加床数*/,
0 as bed_empty_num/*空床数*/,
0 as bed_pause_num/*暂停使用数*/,
0 as bed_borrow_num/*他科借入床数*/,
0 as bad_knub_num/*恶性肿瘤数*/,
0 as origin_cancer_num/*原位癌数*/,
0 as good_knub_num/*良性肿瘤数*/,
0 as cross_bad_num/*交界恶性数*/,
0 as dynamic_unknow_num/*动态未知数*/,
0 as out_province_num/*外省人数*/,
0 as out_area_num/*本省其他地区人数*/,
0 as environs_num/*本市郊县人数*/,
0 as city_num /*本市县区人数*/,
0 as other_num/*本市其他人数*/,
0 out_country_num/*国外人数*/
from app_operat_main
where status <> ‘9’;

create public synonym v_case_dayreport_resi for case.v_case_dayreport_resi;

–科室医疗小组联合视图视图
conn zhiydba/zhiydba@zhis4;
drop case.case_bed_vw;
drop public synonym case_bed_vw
drop view case.v_case_bed;
drop public synonym v_case_bed;
grant select on clinic_group_dict to case;
create or replace view case.v_case_dept
as select dept_code as dept_code/*科室代码*/,
dept_name as dept_name/*科室名称*/,
null || ” as clinic_group_code /*医疗小组*/,
null || ” as clinic_group_name /*医疗小组名称*/,
dept_propety as dept_propety /*科室属性*/,
nvl(bed_theory_num,0) as bed_theory_num/*编制床位数*/,
nvl(bed_have_num,0) as bed_have_num/*实有床位数*/,
nvl(bed_fact_num,0) as bed_fact_num/*实际开放床数*/ ,
nvl(bed_pause_num,0) as bed_pause_num/*暂停使用数*/,
nvl(bed_borrow_num,0) as bed_borrow_num/*他科借入床数*/,
nvl(bed_lend_num ,0) as bed_lend_num/*他科借出床数*/,
sort_no as sort_no/*排序号*/
from case_dept_dict
where valid_flag=’Y’;

create public synonym v_case_dept for case.v_case_dept;

create or replace view case.v_case_dept
as select c.dept_code as dept_code/*科室代码*/,
c.dept_name as dept_name/*科室名称*/,
a.clinic_group_code as clinic_group_code /*医疗小组*/,
d.clinic_group_name as clinic_group_name /*医疗小组名称*/,
c.dept_propety as dept_propety /*科室属性*/,
nvl(a.bed_theory_num,0) as bed_theory_num/*编制床位数*/,
nvl(a.bed_have_num,0) as bed_have_num/*实有床位数*/,
nvl(a.bed_fact_num,0) as bed_fact_num/*实际开放床数*/ ,
nvl(a.bed_pause_num,0) as bed_pause_num/*暂停使用数*/,
nvl(a.bed_borrow_num,0) as bed_borrow_num/*他科借入床数*/,
nvl(a.bed_lend_num ,0) as bed_lend_num/*他科借出床数*/,
a.sort_no as sort_no/*排序号*/
from case_bed_num a,
case_dept_give b,
case_dept_dict c,
clinic_group_dict d
where a.dept_code=b.dept_code
and b.case_dept_code=c.dept_code
and a.clinic_group_code=d.clinic_group_code(+)
and c.valid_flag=’Y’;

conn zhiydba/zhiydba@zhis4;

create or replace view case.v_case_dept_give
as select dept_code as dept_code/*科室代码*/,
case_dept_code as case_dept_code
from case_dept_give;

create public synonym v_case_dept_give for case.v_case_dept_give;