MERGE INTO TD_HOSP p
USING (select 0 as row_wid,
'NA' as code_id,
1 lvl,
1 par_id,
'OTHER' en_name,
'其他' cn_name,
'Y' isleaf_flg,
'Unspecified' long_name,
'OTHER' datasource_type,
'OTHER' integration_id,
'en' lang_id,
'N' src_delete_flg,
'Y' active_flg,
'999999' disp_order,
to_date('19500101', 'yyyymmdd') src_eff_from_dt,
to_date('99991231', 'yyyymmdd') src_eff_to_dt,
to_date('19500101', 'yyyymmdd') created_on_dt,
to_date('99991231', 'yyyymmdd') changed_on_dt,
'Y' current_flg,
to_date('19500101', 'yyyymmdd') begin_dt,
to_date('99991231', 'yyyymmdd') end_dt,
sysdate w_insert_dt,
sysdate w_update_dt
from dual) np
ON (p.row_wid = np.row_wid)
WHEN NOT MATCHED THEN
insert(
row_wid ,
hosp_id ,
hosp_name ,
hosp_shrt_nm ,
hosp_etmsid ,
hosp_dimsid ,
region_id ,
province_id ,
city_id ,
county_id ,
master_partner_type ,
partner_type ,
status ,
main_ph_num ,
address ,
telephone ,
zipcode ,
scale ,
bedqty ,
weijiqty ,
doctorqty ,
mediastinusqty ,
bedqty_yufangbaojiake ,
bedqty_quankeyiliaoke ,
bedqty_neike ,
bedqty_waike ,
bedqty_erke ,
bedqty_fuchanke ,
bedqty_yanke ,
bedqty_erbiyanhouke ,
bedqty_kouqiangke ,
bedqty_pifuke ,
bedqty_yiliaomeirongke ,
bedqty_jinshengke ,
bedqty_chuanranke ,
bedqty_jieheke ,
bedqty_zhongliuke ,
bedqty_kangfuyixueke ,
bedqty_zhiyebingke ,
bedqty_zhognyike ,
qty_yufangbaojiake ,
qty_quankeyiliaoke ,
qty_neike ,
qty_waike ,
qty_erke ,
qty_fuchanke ,
qty_yanke ,
qty_erbiyanhouke ,
qty_kouqiangke ,
qty_pifuke ,
qty_yiliaomeirongke ,
qty_jinshengke ,
qty_chuanranke ,
qty_jieheke ,
qty_zhongliuke ,
qty_kangfuyixueke ,
qty_zhiyebingke ,
qty_zhognyike ,
zhengliaoqty ,
leavehospitalqty ,
amount_medicineincome ,
amount_mengzhengincome ,
amount_inhospitalincome ,
amount_medicineexpend ,
targetstatus ,
src_hosp_num ,
src_hosp_name ,
active_flg ,
integration_id ,
datasource_id ,
disp_order ,
lang_id ,
src_delete_flg ,
src_eff_from_dt ,
src_eff_to_dt ,
created_on_dt ,
changed_on_dt ,
current_flg ,
begin_dt ,
end_dt ,
w_insert_dt ,
w_update_dt
)
VALUES
( np.row_wid,
np.code_id,
np.en_name,
np.en_name,
np.code_id,
np.code_id,
np.code_id,
np.code_id,
np.code_id,
np.code_id,
np.code_id,
np.code_id,
np.code_id,
np.code_id,
np.code_id,
np.code_id,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0 ,
0,
np.code_id,
np.code_id,
np.active_flg,
np.integration_id,
np.datasource_type,
np.disp_order,
np.lang_id,
np.src_delete_flg,
np.src_eff_from_dt,
np.src_eff_to_dt,
np.created_on_dt,
np.changed_on_dt,
np.current_flg,
np.begin_dt,
np.end_dt,
sysdate,
sysdate
)
WHEN MATCHED THEN
UPDATE
SET p.hosp_id = np.code_id,
p.hosp_name = np.en_name,
p.hosp_shrt_nm = np.en_name,
p.hosp_etmsid = np.code_id,
p.hosp_dimsid = np.code_id,
p.region_id = np.code_id,
p.province_id = np.code_id,
p.city_id = np.code_id,
p.county_id = np.code_id,
p.status = np.code_id,
p.main_ph_num = np.code_id,
p.address = np.code_id,
p.telephone = np.code_id,
p.zipcode = np.code_id,
p.scale = np.code_id,
p.bedqty = 0,
p.weijiqty = 0,
p.doctorqty = 0,
p.mediastinusqty = 0,
p.bedqty_yufangbaojiake = 0,
p.bedqty_quankeyiliaoke = 0,
p.bedqty_neike = 0,
p.bedqty_waike = 0,
p.bedqty_erke = 0,
p.bedqty_fuchanke = 0,
p.bedqty_yanke = 0,
p.bedqty_erbiyanhouke = 0,
p.bedqty_kouqiangke = 0,
p.bedqty_pifuke = 0,
p.bedqty_yiliaomeirongke = 0,
p.bedqty_jinshengke = 0,
p.bedqty_chuanranke = 0,
p.bedqty_jieheke = 0,
p.bedqty_zhongliuke = 0,
p.bedqty_kangfuyixueke = 0,
p.bedqty_zhiyebingke = 0,
p.bedqty_zhognyike = 0,
p.qty_yufangbaojiake = 0,
p.qty_quankeyiliaoke = 0,
p.qty_neike = 0,
p.qty_waike = 0,
p.qty_erke = 0,
p.qty_fuchanke = 0,
p.qty_yanke = 0,
p.qty_erbiyanhouke = 0,
p.qty_kouqiangke = 0,
p.qty_pifuke = 0,
p.qty_yiliaomeirongke = 0,
p.qty_jinshengke = 0,
p.qty_chuanranke = 0,
p.qty_jieheke = 0,
p.qty_zhongliuke = 0,
p.qty_kangfuyixueke = 0,
p.qty_zhiyebingke = 0,
p.qty_zhognyike = 0,
p.zhengliaoqty = 0,
p.leavehospitalqty = 0,
p.amount_medicineincome = 0,
p.amount_mengzhengincome = 0,
p.amount_inhospitalincome = 0,
p.amount_medicineexpend = 0,
p.targetstatus = 0,
p.src_hosp_num = np.code_id,
p.src_hosp_name = np.code_id,
p.active_flg = np.active_flg,
p.integration_id = np.integration_id,
p.datasource_id = np.datasource_type,
p.disp_order = np.disp_order,
p.lang_id = np.lang_id,
p.src_delete_flg = np.src_delete_flg,
p.src_eff_from_dt = np.src_eff_from_dt,
p.src_eff_to_dt = np.src_eff_to_dt,
p.created_on_dt = np.created_on_dt,
p.changed_on_dt = np.changed_on_dt,
p.current_flg = np.current_flg,
p.begin_dt = np.begin_dt,
p.end_dt = np.end_dt,
p.w_update_dt = sysdate,
p.master_partner_type = np.code_id,
p.partner_type = np.code_id;
USING (select 0 as row_wid,
'NA' as code_id,
1 lvl,
1 par_id,
'OTHER' en_name,
'其他' cn_name,
'Y' isleaf_flg,
'Unspecified' long_name,
'OTHER' datasource_type,
'OTHER' integration_id,
'en' lang_id,
'N' src_delete_flg,
'Y' active_flg,
'999999' disp_order,
to_date('19500101', 'yyyymmdd') src_eff_from_dt,
to_date('99991231', 'yyyymmdd') src_eff_to_dt,
to_date('19500101', 'yyyymmdd') created_on_dt,
to_date('99991231', 'yyyymmdd') changed_on_dt,
'Y' current_flg,
to_date('19500101', 'yyyymmdd') begin_dt,
to_date('99991231', 'yyyymmdd') end_dt,
sysdate w_insert_dt,
sysdate w_update_dt
from dual) np
ON (p.row_wid = np.row_wid)
WHEN NOT MATCHED THEN
insert(
row_wid ,
hosp_id ,
hosp_name ,
hosp_shrt_nm ,
hosp_etmsid ,
hosp_dimsid ,
region_id ,
province_id ,
city_id ,
county_id ,
master_partner_type ,
partner_type ,
status ,
main_ph_num ,
address ,
telephone ,
zipcode ,
scale ,
bedqty ,
weijiqty ,
doctorqty ,
mediastinusqty ,
bedqty_yufangbaojiake ,
bedqty_quankeyiliaoke ,
bedqty_neike ,
bedqty_waike ,
bedqty_erke ,
bedqty_fuchanke ,
bedqty_yanke ,
bedqty_erbiyanhouke ,
bedqty_kouqiangke ,
bedqty_pifuke ,
bedqty_yiliaomeirongke ,
bedqty_jinshengke ,
bedqty_chuanranke ,
bedqty_jieheke ,
bedqty_zhongliuke ,
bedqty_kangfuyixueke ,
bedqty_zhiyebingke ,
bedqty_zhognyike ,
qty_yufangbaojiake ,
qty_quankeyiliaoke ,
qty_neike ,
qty_waike ,
qty_erke ,
qty_fuchanke ,
qty_yanke ,
qty_erbiyanhouke ,
qty_kouqiangke ,
qty_pifuke ,
qty_yiliaomeirongke ,
qty_jinshengke ,
qty_chuanranke ,
qty_jieheke ,
qty_zhongliuke ,
qty_kangfuyixueke ,
qty_zhiyebingke ,
qty_zhognyike ,
zhengliaoqty ,
leavehospitalqty ,
amount_medicineincome ,
amount_mengzhengincome ,
amount_inhospitalincome ,
amount_medicineexpend ,
targetstatus ,
src_hosp_num ,
src_hosp_name ,
active_flg ,
integration_id ,
datasource_id ,
disp_order ,
lang_id ,
src_delete_flg ,
src_eff_from_dt ,
src_eff_to_dt ,
created_on_dt ,
changed_on_dt ,
current_flg ,
begin_dt ,
end_dt ,
w_insert_dt ,
w_update_dt
)
VALUES
( np.row_wid,
np.code_id,
np.en_name,
np.en_name,
np.code_id,
np.code_id,
np.code_id,
np.code_id,
np.code_id,
np.code_id,
np.code_id,
np.code_id,
np.code_id,
np.code_id,
np.code_id,
np.code_id,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0 ,
0,
np.code_id,
np.code_id,
np.active_flg,
np.integration_id,
np.datasource_type,
np.disp_order,
np.lang_id,
np.src_delete_flg,
np.src_eff_from_dt,
np.src_eff_to_dt,
np.created_on_dt,
np.changed_on_dt,
np.current_flg,
np.begin_dt,
np.end_dt,
sysdate,
sysdate
)
WHEN MATCHED THEN
UPDATE
SET p.hosp_id = np.code_id,
p.hosp_name = np.en_name,
p.hosp_shrt_nm = np.en_name,
p.hosp_etmsid = np.code_id,
p.hosp_dimsid = np.code_id,
p.region_id = np.code_id,
p.province_id = np.code_id,
p.city_id = np.code_id,
p.county_id = np.code_id,
p.status = np.code_id,
p.main_ph_num = np.code_id,
p.address = np.code_id,
p.telephone = np.code_id,
p.zipcode = np.code_id,
p.scale = np.code_id,
p.bedqty = 0,
p.weijiqty = 0,
p.doctorqty = 0,
p.mediastinusqty = 0,
p.bedqty_yufangbaojiake = 0,
p.bedqty_quankeyiliaoke = 0,
p.bedqty_neike = 0,
p.bedqty_waike = 0,
p.bedqty_erke = 0,
p.bedqty_fuchanke = 0,
p.bedqty_yanke = 0,
p.bedqty_erbiyanhouke = 0,
p.bedqty_kouqiangke = 0,
p.bedqty_pifuke = 0,
p.bedqty_yiliaomeirongke = 0,
p.bedqty_jinshengke = 0,
p.bedqty_chuanranke = 0,
p.bedqty_jieheke = 0,
p.bedqty_zhongliuke = 0,
p.bedqty_kangfuyixueke = 0,
p.bedqty_zhiyebingke = 0,
p.bedqty_zhognyike = 0,
p.qty_yufangbaojiake = 0,
p.qty_quankeyiliaoke = 0,
p.qty_neike = 0,
p.qty_waike = 0,
p.qty_erke = 0,
p.qty_fuchanke = 0,
p.qty_yanke = 0,
p.qty_erbiyanhouke = 0,
p.qty_kouqiangke = 0,
p.qty_pifuke = 0,
p.qty_yiliaomeirongke = 0,
p.qty_jinshengke = 0,
p.qty_chuanranke = 0,
p.qty_jieheke = 0,
p.qty_zhongliuke = 0,
p.qty_kangfuyixueke = 0,
p.qty_zhiyebingke = 0,
p.qty_zhognyike = 0,
p.zhengliaoqty = 0,
p.leavehospitalqty = 0,
p.amount_medicineincome = 0,
p.amount_mengzhengincome = 0,
p.amount_inhospitalincome = 0,
p.amount_medicineexpend = 0,
p.targetstatus = 0,
p.src_hosp_num = np.code_id,
p.src_hosp_name = np.code_id,
p.active_flg = np.active_flg,
p.integration_id = np.integration_id,
p.datasource_id = np.datasource_type,
p.disp_order = np.disp_order,
p.lang_id = np.lang_id,
p.src_delete_flg = np.src_delete_flg,
p.src_eff_from_dt = np.src_eff_from_dt,
p.src_eff_to_dt = np.src_eff_to_dt,
p.created_on_dt = np.created_on_dt,
p.changed_on_dt = np.changed_on_dt,
p.current_flg = np.current_flg,
p.begin_dt = np.begin_dt,
p.end_dt = np.end_dt,
p.w_update_dt = sysdate,
p.master_partner_type = np.code_id,
p.partner_type = np.code_id;