create or replace procedure SP_ZY_JSQDSC_WLQDCL ( PZYID00 in number, --住院ID PYBJKID in number, --医保接口id PBFSLB0 in varchar2, --不发送的列表 PYHMSG0 out varchar2, --存储过程提示的错误信息 PZXZT00 out number --执行状态 0失败 1成功 ) -- MODIFICATION HISTORY -- Person Date Comments -- chenHeyi 2022.03.30 从5.0移植 by ZYSF9-20220317-004 -- linzetao 2022.11.07 修正"手术开始时间"和"麻醉开始时间"节点名称 ZYSF9-20221104-002 -- zhangyc 2022.11.22 增加通用字典获取院内对照字段（select * from bm_ybsxzd where jkbm00=0 and sxbm00='medicalRecordtoHIS_setlinfo'）by ZYSF9-20221123-001 ---------- ------------ ---------------------------- as LS_payinfo_Num number(12);--[数量]基金支付信息 LS_opspdiseinfo_Num number(12);--[数量]门诊慢特病诊断信息 LS_diseinfo_Num number(12);--[数量]住院诊断信息< diseinfo> LS_iteminfo_Num number(12);--[数量]收费项目信息 LS_oprninfo_Num number(12);--[数量]手术操作信息 LS_icuinfo_Num number(12);--[数量]重症监护信息 LS_bldinfo_Num number(12);--[数量]输血信息 LS_STR varchar2(2000); LS_jsqd_Notsend_list varchar2(100);--医疗基金结算清单不发送list多个|隔开 LS_payinfo_Json ZY_YBJKMX.VALUE0%type;--[字符]基金支付信息 LS_opspdiseinfo_Json ZY_YBJKMX.VALUE0%type;--[字符]门诊慢特病诊断信息 LS_diseinfo_Json ZY_YBJKMX.VALUE0%type;--[字符]住院诊断信息< diseinfo> --LS_iteminfo_Json ZY_YBJKMX.VALUE0%type;--[字符]收费项目信息 LS_oprninfo_Json ZY_YBJKMX.VALUE0%type;--[字符]手术操作信息 LS_icuinfo_Json ZY_YBJKMX.VALUE0%type;--[字符]重症监护信息 LS_bldinfo_Json ZY_YBJKMX.VALUE0%type; --输血信息 fund_pay_type BAJK_WLBASY.VALUE0%type; fund_payamt BAJK_WLBASY.VALUE0%type; diag_name BAJK_WLBASY.VALUE0%type; diag_code BAJK_WLBASY.VALUE0%type; oprn_oprt_name BAJK_WLBASY.VALUE0%type; oprn_oprt_code BAJK_WLBASY.VALUE0%type; maindiag_flag BAJK_WLBASY.VALUE0%type; LS_diag_type BAJK_WLBASY.VALUE0%type; LS_diag_code BAJK_WLBASY.VALUE0%type; LS_diag_name BAJK_WLBASY.VALUE0%type; LS_adm_cond_type BAJK_WLBASY.VALUE0%type; LS_maindiag_flag BAJK_WLBASY.VALUE0%type; oprn_oprt_type BAJK_WLBASY.VALUE0%type; oprn_oprt_date BAJK_WLBASY.VALUE0%type; anst_way BAJK_WLBASY.VALUE0%type; oper_dr_name BAJK_WLBASY.VALUE0%type; oper_dr_code BAJK_WLBASY.VALUE0%type; anst_dr_name BAJK_WLBASY.VALUE0%type; anst_dr_code BAJK_WLBASY.VALUE0%type; LS_oprn_oprt_begintime BAJK_WLBASY.VALUE0%type; LS_oprn_oprt_begntime BAJK_WLBASY.VALUE0%type; LS_oprn_oprt_endtime BAJK_WLBASY.VALUE0%type; LS_anst_begintime BAJK_WLBASY.VALUE0%type; LS_anst_begntime BAJK_WLBASY.VALUE0%type; LS_anst_endtime BAJK_WLBASY.VALUE0%type; scs_cutd_ward_type BAJK_WLBASY.VALUE0%type; scs_cutd_inpool_time BAJK_WLBASY.VALUE0%type; scs_cutd_exit_time BAJK_WLBASY.VALUE0%type; scs_cutd_sum_dura BAJK_WLBASY.VALUE0%type; bld_cat BAJK_WLBASY.VALUE0%type; bld_amt BAJK_WLBASY.VALUE0%type; bld_unt BAJK_WLBASY.VALUE0%type; LS_YNDYZD varchar2(50);--院内字段对应编码 LS_YNZDCD number(5); --字段长度 LS_VALUE0 varchar2(1000);--值 begin PZXZT00 := 0; LS_jsqd_Notsend_list := PBFSLB0; for wbba in (select * from BAJK_WLBARZ a where a.ZYID00=PZYID00 and a.YXBZ00='1' order by BAID00) loop LS_payinfo_Num:=0; --[数量]基金支付信息 LS_opspdiseinfo_Num:=0; --[数量]门诊慢特病诊断信息 LS_diseinfo_Num:=0; --[数量]住院诊断信息< diseinfo> LS_iteminfo_Num:=0; --[数量]收费项目信息 LS_oprninfo_Num:=0; --[数量]手术操作信息 LS_icuinfo_Num:=0; --[数量]重症监护信息 LS_bldinfo_num :=0; --更新病案外部接口数据一级节点 for wbba_jg in (select b.* from BAJK_WLBASY b where b.BAID00=wbba.BAID00 and b.YBLBMC = 'setlinfo' and b.VALUE0 is not null and YBZDM0 not in ('psn_no','zyid00','mdtrt_id')) loop update ZY_BASYFS set VALUE0=wbba_jg.VALUE0 where ZYID00=PZYID00 and YBJKID=PYBJKID; --获取字典维护院内字段编码---begin--- LS_YNDYZD:=null;--院内字段对应编码 LS_YNZDCD:=0; --字段长度 LS_VALUE0:=substrb(trim(wbba_jg.VALUE0),1,1000);--值 begin select substrb(trim(YNDZBM),1,50),round(nvl(ZGXJBZ,0),5) into LS_YNDYZD,LS_YNZDCD from bm_ybsxzd where jkbm00=0 and sxbm00='medicalRecordtoHIS_setlinfo' and VALUE0=trim(wbba_jg.YBZDM0); exception when others then LS_YNDYZD:=null;--院内字段对应编码 LS_YNZDCD:=0; --字段长度 end; --获取字典维护院内字段编码---end--- if LS_YNDYZD is null then LS_YNDYZD:=substrb(trim(wbba_jg.YBZDM0),1,50);--院内字段对应编码 end if; if nvl(LS_YNZDCD,0)>0 then LS_VALUE0:=substrb(LS_VALUE0,1,LS_YNZDCD);--值 end if; if LS_VALUE0 is not null then begin LS_STR := 'update ZY_YBJSD0 set '||LS_YNDYZD|| '=:v1 where ZYID00=:v2 and YBJKID=:v3'; execute immediate LS_STR using LS_VALUE0, PZYID00,PYBJKID; exception when others then PYHMSG0 := '插入数据['||LS_YNDYZD||':'||LS_VALUE0||']错误！'; end; end if; end loop; /* 基金支付信息开始 1 fund_pay_type 基金支付类型 varchar2(6) 2 fund_payamt 基金支付金额 number(16,2) 基金支付信息结束 */ if (LS_jsqd_Notsend_list is null or instrb('|'||LS_jsqd_Notsend_list||'|','|'||'payinfo'||'|')<=0) and (LS_payinfo_Num=0) then for wbba_list_XH in (select LBXH00 from BAJK_WLBASY where BAID00=wbba.BAID00 and YBLBMC='payinfo' and VALUE0 is not null group by LBXH00 order by LBXH00) loop fund_pay_type := ''; fund_payamt := ''; for wbba_list_detail in (select * from BAJK_WLBASY where BAID00=wbba.BAID00 and YBLBMC='payinfo' and VALUE0 is not null and LBXH00=wbba_list_XH.LBXH00 ) loop if trim(wbba_list_detail.YBZDM0) = 'fund_pay_type' then fund_pay_type := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'fund_payamt' then fund_payamt := wbba_list_detail.VALUE0; end if; end loop; LS_payinfo_Json:=null; LS_payinfo_Json:=LS_payinfo_Json||'{'; LS_payinfo_Json:=LS_payinfo_Json||'"fund_pay_type"'||':'||'"'||fund_pay_type||'"'||','; LS_payinfo_Json:=LS_payinfo_Json||'"fund_payamt"'||':'||'"'||fund_payamt||'"'||''; LS_payinfo_Json:=LS_payinfo_Json||'}'; --插入ZY_YBJSDX insert into ZY_YBJSDX(YBJKID,JKJDBM,JKJDMC,rid,fund_pay_type,poolarea_fund_pay_type,poolarea_fund_pay_name,fund_payamt) select PYBJKID,'payinfo','基金支付信息',LS_payinfo_Num, substrb(fund_pay_type,1,10) as fund_pay_type, substrb(fund_pay_type,1,10) as poolarea_fund_pay_type, substrb((select aa.SXSM00 from bm_ybsxzd aa where aa.JKBM00=2 and aa.sxbm00='fund_pay_type' and aa.VALUE0=fund_pay_type and rownum=1),1,50) as poolarea_fund_pay_name, fund_payamt from dual A; --插入ZY_YBJKMX insert into ZY_YBJKMX(YBJKID,JKMXID,JDBM00,ZJDBM0,ZDBM00,ZDMC00,JKFSZ0,JKFHZ0,FSLXBZ,BZ0000,VALUE0) select PYBJKID,LS_payinfo_Num,'payinfo','data','payinfo','基金支付信息',null,null,'1',null,LS_payinfo_Json from dual; LS_payinfo_Num:=nvl(LS_payinfo_Num,0)+1; end loop; end if; /* 门诊慢特病诊断信息开始 1 diag_name 诊断名称 varchar2(100) 2 diag_code 诊断代码 varchar2(20) 3 oprn_oprt_name 手术操作名称 varchar2(500) 4 oprn_oprt_code 手术操作代码 varchar2(30) 5 maindiag_flag 主诊断标志 varchar2(3) 门诊慢特病诊断信息结束 */ if (LS_jsqd_Notsend_list is null or instrb('|'||LS_jsqd_Notsend_list||'|','|'||'opspdiseinfo'||'|')<=0) and (LS_opspdiseinfo_Num=0) then for wbba_list_XH in (select LBXH00 from BAJK_WLBASY where BAID00=wbba.BAID00 and YBLBMC='opspdiseinfo' and VALUE0 is not null group by LBXH00 order by LBXH00) loop diag_name := ''; diag_code := ''; oprn_oprt_name :=''; oprn_oprt_code := ''; maindiag_flag := ''; for wbba_list_detail in (select * from BAJK_WLBASY where BAID00=wbba.BAID00 and YBLBMC='opspdiseinfo' and VALUE0 is not null and LBXH00=wbba_list_XH.LBXH00) loop if trim(wbba_list_detail.YBZDM0) = 'diag_name' then diag_name := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'diag_code' then diag_code := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'oprn_oprt_name' then oprn_oprt_name := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'oprn_oprt_code' then oprn_oprt_code := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'maindiag_flag' then maindiag_flag := wbba_list_detail.VALUE0; end if; end loop; LS_opspdiseinfo_Json:=null; LS_opspdiseinfo_Json:=LS_opspdiseinfo_Json||'{'; LS_opspdiseinfo_Json:=LS_opspdiseinfo_Json||'"diag_name"'||':'||'"'||diag_name||'"'||','; LS_opspdiseinfo_Json:=LS_opspdiseinfo_Json||'"diag_code"'||':'||'"'||diag_code||'"'||','; LS_opspdiseinfo_Json:=LS_opspdiseinfo_Json||'"oprn_oprt_name"'||':'||'"'||oprn_oprt_name||'"'||','; LS_opspdiseinfo_Json:=LS_opspdiseinfo_Json||'"oprn_oprt_code"'||':'||'"'||oprn_oprt_code||'"'||','; LS_opspdiseinfo_Json:=LS_opspdiseinfo_Json||'"maindiag_flag"'||':'||'"'||maindiag_flag||'"'||''; LS_opspdiseinfo_Json:=LS_opspdiseinfo_Json||'}'; --插入ZY_YBJSDX insert into ZY_YBJSDX(YBJKID,JKJDBM,JKJDMC,rid,diag_name,diag_code,oprn_oprt_name,oprn_oprt_code,maindiag_flag) select PYBJKID,'opspdiseinfo','门诊慢特病诊断信息',LS_opspdiseinfo_Num, substrb(diag_name,1,50) as diag_name, substrb(diag_code,1,50) as diag_code, substrb(oprn_oprt_name,1,100) as oprn_oprt_name, substrb(oprn_oprt_code,1,50) as oprn_oprt_code, substrb(maindiag_flag,1,10) as maindiag_flag from dual A; --ZY_YBJKMX insert into ZY_YBJKMX(YBJKID,JKMXID,JDBM00,ZJDBM0,ZDBM00,ZDMC00,JKFSZ0,JKFHZ0,FSLXBZ,BZ0000,VALUE0) select PYBJKID,LS_opspdiseinfo_Num,'opspdiseinfo','data','opspdiseinfo','门诊慢特病诊断信息',null,null,'1',null, LS_opspdiseinfo_Json from dual; LS_opspdiseinfo_Num:=nvl(LS_opspdiseinfo_Num,0)+1; end loop; end if; /* 住院诊断信息< diseinfo>开始 1 diag_type 诊断类别 varchar2(3) 2 diag_code 诊断代码 varchar2(20) 3 diag_name 诊断名称 varchar2(100) 4 adm_cond_type 入院病情类型 varchar2(3) 5 maindiag_flag 主诊断标志 varchar2(3) 住院诊断信息< diseinfo>结束 */ if (LS_jsqd_Notsend_list is null or instrb('|'||LS_jsqd_Notsend_list||'|','|'||'diseinfo'||'|')<=0) and (LS_diseinfo_Num=0) then for wbba_list_XH in (select LBXH00 from BAJK_WLBASY where BAID00=wbba.BAID00 and YBLBMC='diseinfo' and VALUE0 is not null group by LBXH00 order by LBXH00) loop LS_diag_type := ''; LS_diag_code := ''; LS_diag_name := ''; LS_adm_cond_type :=''; LS_maindiag_flag := ''; for wbba_list_detail in (select * from BAJK_WLBASY where BAID00=wbba.BAID00 and YBLBMC='diseinfo' and VALUE0 is not null and LBXH00=wbba_list_XH.LBXH00) loop if trim(wbba_list_detail.YBZDM0) = 'diag_type' then LS_diag_type := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'diag_code' then LS_diag_code := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'diag_name' then LS_diag_name := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'adm_cond_type' then LS_adm_cond_type := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'maindiag_flag' then LS_maindiag_flag := wbba_list_detail.VALUE0; end if; end loop; LS_diseinfo_Json:=null; LS_diseinfo_Json:=LS_diseinfo_Json||'{'; LS_diseinfo_Json:=LS_diseinfo_Json||'"diag_type"'||':'||'"'||LS_diag_type||'"'||','; LS_diseinfo_Json:=LS_diseinfo_Json||'"diag_code"'||':'||'"'||LS_diag_code||'"'||','; LS_diseinfo_Json:=LS_diseinfo_Json||'"diag_name"'||':'||'"'||LS_diag_name||'"'||','; LS_diseinfo_Json:=LS_diseinfo_Json||'"adm_cond_type"'||':'||'"'||LS_adm_cond_type||'"'||','; LS_diseinfo_Json:=LS_diseinfo_Json||'"maindiag_flag"'||':'||'"'||LS_maindiag_flag||'"'||''; LS_diseinfo_Json:=LS_diseinfo_Json||'}'; --插入ZY_YBJSDX insert into ZY_YBJSDX(YBJKID,JKJDBM,JKJDMC,rid,diag_type,diag_code,diag_name,adm_cond_type,maindiag_flag) select PYBJKID,'diseinfo','住院诊断信息',LS_diseinfo_Num, substrb(LS_diag_type,1,10) as diag_type, substrb(LS_diag_code,1,50) as diag_code, substrb(LS_diag_name,1,100) as diag_name, substrb(LS_adm_cond_type,1,10) as adm_cond_type, substrb(LS_maindiag_flag,1,10) as maindiag_flag from dual; --插入ZY_YBJKMX insert into ZY_YBJKMX(YBJKID,JKMXID,JDBM00,ZJDBM0,ZDBM00,ZDMC00,JKFSZ0,JKFHZ0,FSLXBZ,BZ0000,VALUE0) select PYBJKID,LS_diseinfo_Num,'diseinfo','data','diseinfo','住院诊断信息',null,null,'1',null,LS_diseinfo_Json from dual; LS_diseinfo_Num:=nvl(LS_diseinfo_Num,0)+1; end loop; end if; /* 手术操作信息开始 1 oprn_oprt_type 手术操作类别 varchar2(3) 2 oprn_oprt_name 手术操作名称 varchar2(500) 3 oprn_oprt_code 手术操作代码 varchar2(30) 4 oprn_oprt_date 手术操作日期 number(8) 5 anst_way 麻醉方式 varchar2(6) 6 oper_dr_name 术者医师姓名 varchar2(50) 7 oper_dr_code 术者医师代码 varchar2(20) 8 anst_dr_name 麻醉医师姓名 varchar2(50) 9 anst_dr_code 麻醉医师代码 varchar2(20) 10 oprn_oprt_begntime 手术开始时间 number(14) 11 oprn_oprt_endtime 手术结束时间 number(14) 12 anst_begintime 麻醉开始时间 number(14) 13 anst_endtime 麻醉结束时间 number(14) 手术操作信息结束 */ if (LS_jsqd_Notsend_list is null or instrb('|'||LS_jsqd_Notsend_list||'|','|'||'oprninfo'||'|')<=0) and (LS_oprninfo_Num=0) then for wbba_list_XH in (select LBXH00 from BAJK_WLBASY where BAID00=wbba.BAID00 and YBLBMC='oprninfo' and VALUE0 is not null group by LBXH00 order by LBXH00) loop oprn_oprt_type :=''; oprn_oprt_name :=''; oprn_oprt_code :=''; oprn_oprt_date :=''; anst_way :=''; oper_dr_name :=''; oper_dr_code :=''; anst_dr_name :=''; anst_dr_code :=''; LS_oprn_oprt_begintime :=''; LS_oprn_oprt_begntime :=''; LS_oprn_oprt_endtime :=''; LS_anst_begintime :=''; LS_anst_begntime :=''; LS_anst_endtime :=''; for wbba_list_detail in (select * from BAJK_WLBASY where BAID00=wbba.BAID00 and YBLBMC='oprninfo' and VALUE0 is not null and LBXH00=wbba_list_XH.LBXH00) loop if trim(wbba_list_detail.YBZDM0) = 'oprn_oprt_type' then oprn_oprt_type := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'oprn_oprt_name' then oprn_oprt_name := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'oprn_oprt_code' then oprn_oprt_code := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'oprn_oprt_date' then oprn_oprt_date := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'anst_way' then anst_way := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'oper_dr_name' then oper_dr_name := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'oper_dr_code' then oper_dr_code := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'anst_dr_name' then anst_dr_name := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'anst_dr_code' then anst_dr_code := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'oprn_oprt_begintime' then LS_oprn_oprt_begintime := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'oprn_oprt_begntime' then LS_oprn_oprt_begntime := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'oprn_oprt_endtime' then LS_oprn_oprt_endtime := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'anst_begintime' then LS_anst_begintime := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'anst_begntime' then LS_anst_begntime := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'anst_endtime' then LS_anst_endtime := wbba_list_detail.VALUE0; end if; end loop; LS_oprninfo_Json:=null; LS_oprninfo_Json:=LS_oprninfo_Json||'{'; LS_oprninfo_Json:=LS_oprninfo_Json||'"oprn_oprt_type"'||':'||'"'||oprn_oprt_type||'"'||','; LS_oprninfo_Json:=LS_oprninfo_Json||'"oprn_oprt_name"'||':'||'"'||oprn_oprt_name||'"'||','; LS_oprninfo_Json:=LS_oprninfo_Json||'"oprn_oprt_code"'||':'||'"'||oprn_oprt_code||'"'||','; LS_oprninfo_Json:=LS_oprninfo_Json||'"oprn_oprt_date"'||':'||'"'||oprn_oprt_date||'"'||','; LS_oprninfo_Json:=LS_oprninfo_Json||'"anst_way"'||':'||'"'||anst_way||'"'||','; LS_oprninfo_Json:=LS_oprninfo_Json||'"oper_dr_name"'||':'||'"'||oper_dr_name||'"'||','; LS_oprninfo_Json:=LS_oprninfo_Json||'"oper_dr_code"'||':'||'"'||oper_dr_code||'"'||','; LS_oprninfo_Json:=LS_oprninfo_Json||'"anst_dr_name"'||':'||'"'||anst_dr_name||'"'||','; LS_oprninfo_Json:=LS_oprninfo_Json||'"anst_dr_code"'||':'||'"'||anst_dr_code||'"'||','; --LS_oprninfo_Json:=LS_oprninfo_Json||'"oprn_oprt_begintime"'||':'||'"'||LS_oprn_oprt_begintime||'"'||','; LS_oprninfo_Json:=LS_oprninfo_Json||'"oprn_oprt_begntime"'||':'||'"'||nvl(LS_oprn_oprt_begntime,LS_oprn_oprt_begintime)||'"'||','; LS_oprninfo_Json:=LS_oprninfo_Json||'"oprn_oprt_endtime"'||':'||'"'||LS_oprn_oprt_endtime||'"'||','; LS_oprninfo_Json:=LS_oprninfo_Json||'"anst_begntime"'||':'||'"'||nvl(LS_anst_begntime,LS_anst_begintime)||'"'||','; --LS_oprninfo_Json:=LS_oprninfo_Json||'"anst_begintime"'||':'||'"'||LS_anst_begintime||'"'||','; LS_oprninfo_Json:=LS_oprninfo_Json||'"anst_endtime"'||':'||'"'||LS_anst_endtime||'"'||''; LS_oprninfo_Json:=LS_oprninfo_Json||'}'; --插入ZY_YBJSDX insert into ZY_YBJSDX( YBJKID,JKJDBM,JKJDMC,rid,main_oprn_flag,oprn_oprt_name,oprn_oprt_code,oprn_oprt_date, anst_way,oper_dr_name,oper_dr_code,anst_dr_name,anst_dr_code,oprn_oprt_begntime, oprn_oprt_endtime,anst_begntime,anst_endtime ) select PYBJKID,'oprninfo','手术操作信息',LS_oprninfo_Num, substrb(oprn_oprt_type,1,30) as main_oprn_flag, substrb(oprn_oprt_name,1,100) as oprn_oprt_name, substrb(oprn_oprt_code,1,50) as oprn_oprt_code, substrb(oprn_oprt_date,1,20) as oprn_oprt_date, substrb(anst_way,1,30) as anst_way, substrb(oper_dr_name,1,100) as oper_dr_name, substrb(oper_dr_code,1,50) as oper_dr_code, substrb(anst_dr_name,1,100) as anst_dr_name, substrb(anst_dr_code,1,50) as anst_dr_code, substrb(nvl(LS_oprn_oprt_begntime,LS_oprn_oprt_begintime),1,20) as oprn_oprt_begintime, substrb(LS_oprn_oprt_endtime,1,20) as oprn_oprt_endtime, substrb(nvl(LS_anst_begntime,LS_anst_begintime),1,20) as anst_begintime, substrb(LS_anst_endtime,1,20) as anst_endtime from dual; --插入ZY_YBJKMX insert into ZY_YBJKMX(YBJKID,JKMXID,JDBM00,ZJDBM0,ZDBM00,ZDMC00,JKFSZ0,JKFHZ0,FSLXBZ,BZ0000,VALUE0) select PYBJKID,LS_oprninfo_Num,'oprninfo','data','oprninfo','手术操作信息',null,null,'1',null,LS_oprninfo_Json from dual; LS_oprninfo_Num:=nvl(LS_oprninfo_Num,0)+1; end loop; end if; /* 重症监护信息开始 1 scs_cutd_ward_type 重症监护病房类型 varchar2(6) 2 scs_cutd_inpool_time 重症监护进入时间 number(14) 3 scs_cutd_exit_time 重症监护退出时间 number(14) 4 scs_cutd_sum_dura 重症监护合计时长 varchar2(10) 重症监护信息结束 */ if (LS_jsqd_Notsend_list is null or instrb('|'||LS_jsqd_Notsend_list||'|','|'||'icuinfo'||'|')<=0) and (LS_icuinfo_Num=0) then for wbba_list_XH in (select LBXH00 from BAJK_WLBASY where BAID00=wbba.BAID00 and YBLBMC='icuinfo' and VALUE0 is not null group by LBXH00 order by LBXH00) loop scs_cutd_ward_type := ''; scs_cutd_inpool_time := ''; scs_cutd_exit_time := ''; scs_cutd_sum_dura := ''; for wbba_list_detail in (select * from BAJK_WLBASY where BAID00=wbba.BAID00 and YBLBMC='icuinfo' and VALUE0 is not null and LBXH00=wbba_list_XH.LBXH00) loop if trim(wbba_list_detail.YBZDM0) = 'scs_cutd_ward_type' then scs_cutd_ward_type := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'scs_cutd_inpool_time' then scs_cutd_inpool_time := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'scs_cutd_exit_time' then scs_cutd_exit_time := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'scs_cutd_sum_dura' then scs_cutd_sum_dura := wbba_list_detail.VALUE0; end if; end loop; LS_icuinfo_Json:=null; LS_icuinfo_Json:=LS_icuinfo_Json||'{'; LS_icuinfo_Json:=LS_icuinfo_Json||'"scs_cutd_ward_type"'||':'||'"'||scs_cutd_ward_type||'"'||','; LS_icuinfo_Json:=LS_icuinfo_Json||'"scs_cutd_inpool_time"'||':'||'"'||scs_cutd_inpool_time||'"'||','; LS_icuinfo_Json:=LS_icuinfo_Json||'"scs_cutd_exit_time"'||':'||'"'||scs_cutd_inpool_time||'"'||','; LS_icuinfo_Json:=LS_icuinfo_Json||'"scs_cutd_sum_dura"'||':'||'"'||scs_cutd_sum_dura||'"'||''; LS_icuinfo_Json:=LS_icuinfo_Json||'}'; --插入ZY_YBJSDX insert into ZY_YBJSDX(YBJKID,JKJDBM,JKJDMC,rid,scs_cutd_ward_type,scs_cutd_inpool_time,scs_cutd_exit_time,scs_cutd_sum_dura) select PYBJKID,'icuinfo','重症监护信息',LS_icuinfo_Num, scs_cutd_ward_type,scs_cutd_inpool_time,scs_cutd_exit_time,scs_cutd_sum_dura from dual; --插入ZY_YBJKMX insert into ZY_YBJKMX(YBJKID,JKMXID,JDBM00,ZJDBM0,ZDBM00,ZDMC00,JKFSZ0,JKFHZ0,FSLXBZ,BZ0000,VALUE0) select PYBJKID,LS_icuinfo_Num,'icuinfo','data','icuinfo','重症监护信息',null,null,'1',null,LS_icuinfo_Json from dual; LS_icuinfo_Num:=nvl(LS_icuinfo_Num,0)+1; end loop; end if; /* 输血信息 1 bld_cat 输血品种 2 bld_amt 输血量 3 bld_unt 输血计量单位 */ if (LS_jsqd_Notsend_list is null or instrb('|'||LS_jsqd_Notsend_list||'|','|'||'bldinfo'||'|')<=0) and (LS_bldinfo_Num=0) then for wbba_list_XH in (select LBXH00 from BAJK_WLBASY where BAID00=wbba.BAID00 and YBLBMC='bldinfo' and VALUE0 is not null group by LBXH00 order by LBXH00) loop bld_cat := ''; bld_amt := ''; bld_unt := ''; for wbba_list_detail in (select * from BAJK_WLBASY where BAID00=wbba.BAID00 and YBLBMC='bldinfo' and VALUE0 is not null and LBXH00=wbba_list_XH.LBXH00) loop if trim(wbba_list_detail.YBZDM0) = 'bld_cat' then bld_cat := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'bld_amt' then bld_amt := wbba_list_detail.VALUE0; elsif trim(wbba_list_detail.YBZDM0) = 'bld_unt' then bld_unt := wbba_list_detail.VALUE0; end if; end loop; LS_bldinfo_Json:=null; LS_bldinfo_Json:=LS_bldinfo_Json||'{'; LS_bldinfo_Json:=LS_bldinfo_Json||'"bld_cat"'||':'||'"'||bld_cat||'"'||','; LS_bldinfo_Json:=LS_bldinfo_Json||'"bld_amt"'||':'||'"'||bld_amt||'"'||','; LS_bldinfo_Json:=LS_bldinfo_Json||'"bld_unt"'||':'||'"'||bld_unt||'"'||''; LS_bldinfo_Json:=LS_bldinfo_Json||'}'; --插入ZY_YBJSDX insert into ZY_YBJSDX(YBJKID,JKJDBM,JKJDMC,rid,bld_cat,bld_amt,bld_unt) select PYBJKID,'bldinfo','输血信息',LS_bldinfo_Num, bld_cat,bld_amt,bld_unt from dual; --插入ZY_YBJKMX insert into ZY_YBJKMX(YBJKID,JKMXID,JDBM00,ZJDBM0,ZDBM00,ZDMC00,JKFSZ0,JKFHZ0,FSLXBZ,BZ0000,VALUE0) select PYBJKID,LS_bldinfo_Num,'bldinfo','data','bldinfo','输血信息',null,null,'1',null,LS_bldinfo_Json from dual; LS_bldinfo_Num:=nvl(LS_bldinfo_Num,0)+1; end loop; end if; PZXZT00 := 1; end loop; update ZY_YBJSD0 set payinfo_num=nvl(LS_payinfo_num,0), --基金支付信息_记录数 opspdiseinfo_num=nvl(LS_opspdiseinfo_num,0),--门诊慢特病诊断信息_记录数 diseinfo_num=nvl(LS_diseinfo_num,0), --住院诊断信息_记录数 iteminfo_num=nvl(LS_iteminfo_num,0), --收费项目信息_记录数 oprninfo_num=nvl(LS_oprninfo_num,0), --手术操作信息_记录数 icuinfo_num=nvl(LS_icuinfo_num,0), --重症监护信息_记录数 bldinfo_num=nvl(LS_bldinfo_num,0) --输血信息_记录数 where YBJKID=PYBJKID and ZYID00=PZYID00; exception when others then PYHMSG0 := dbms_utility.format_error_backtrace||'SP_ZY_JSQDSC_WLQDCL 新医保接口结算清单上传外连清单处理过程！'||SQLERRM; --raise_application_error(-20001,dbms_utility.format_error_backtrace||'数据整理出错！'||SQLERRM); rollback; end;