create or replace procedure SP_SF_GJYBRZ_MZZDSC( PYBJKID in varchar2 ,--[1]输入:医保接口ID PJKCJBM in varchar2 ,--[2]输入:接口场景编码 PBRID00 in number ,--[3]输入:BRID00 PGHID00 in number ,--[4]输入:GHID000 PGHH000 in varchar2 ,--[5]输入:GHH000 PCZY000 in number ,--[6]输入:操作员编码 PCZYXM0 in varchar2 ,--[7]输入:操作员姓名 PCZYKS0 in number ,--[8]输入:操作员科室 PQQCSID in varchar2 default '' ,--[9]输入:参数中间ID PINXML0 in varchar2 default '' ,--[10]输入:请求参数(XML) POTHER0 in varchar2 default '' ,--[11]输入:备用参数 PCOMMIT in varchar2 default 'N' ,--[12]输入:是否提交事务 Y:提交 N:不提交 PZXZT00 out number ,--[13]输出:执行状态 0:失败 1：成功 POUTXML out varchar2 ,--[14]输出:出参(XML) PERRMSG out varchar2 --[15]输出:错误信息 ) as -- MODIFICATION HISTORY -- Person Date Comments -- linzetao 2022.02.08 created by 【2203】门诊就诊信息上传_日志生成 MZSF9-20220208-001 -- linzetao 2022.02.10 调整vali_flag节点取值(由Y改为1) MZSF9-20220210-002 -- linzetao 2022.02.18 调整报文格式 MZSF9-20220218-001 -- zhangyc 2022.03.21 dise_codg,dise_name为空加个空格 by MZSF9-20220321-003 -- zhangyc 2022.04.05 特殊病种为生育类别med_type=51 by MZSF9-20220406-002 --zhangyc 2022.04.08 增加med_type:医疗类别=9107:体检 510102:产前检查 by MZSF9-20220407-003 --zhangyc 2022.04.12 修正诊断错误 by MZSF9-20220412-003 -- linzetao 2022.08.05 增加节点matn_type(生育类别)和geso_val(孕周数) MZSF9-20220727-001 -- --------- ---------- ------- LS_COUNT0 number(12,2); LS_CZRQ00 char(8); LS_CZSJ00 char(8); LS_INFNLX BM_TYCJBM.INFNLX%type;--:[国家医保]交易类型 1:直接调用医保DLL 3:调用服务接口 LS_QQJDBS BM_TYCJBM.QQJDBS%type;--:请求节点标识 LS_FHJDBS BM_TYCJBM.FHJDBS%type;--:返回节点标识 LS_DateTimeStr varchar2(20); --日期时间型:格式为：yyyy-MM-dd HH:mm:ss LS_DateStr varchar2(10); --日期型:格式为：yyyy-MM-dd LS_TimeStr varchar2(10); --时间型:格式为：HH:mm:ss LS_JKFSZ0 SF_YBJKMX.JKFSZ0%type;--接口发送值 LS_JKFHZ0 SF_YBJKMX.JKFHZ0%type;--接口返回值 LS_VALUE0 SF_YBJKMX.VALUE0%type;--请求报文字符串 LS_HISCS0 SF_YBJKRZ.HISCS0%type;--HIS前台请求参数 LS_QQDATA SF_YBJKRZ.QQDATA%type;--请求报文 LS_MainJson SF_YBJKRZ.QQDATA%type;--主项Json LS_detailJson SF_YBJKRZ.QQDATA%type;--明细Json LS_inputJson SF_YBJKRZ.QQDATA%type;--input输入 LS_diseinfoJson SF_YBJKRZ.QQDATA%type;--诊断列表Json LS_zdxxJson SF_YBJKRZ.QQDATA%type;--诊断Json LS_zdxxListJson SF_YBJKRZ.QQDATA%type;--诊断列表Json ls_yyid00 XT_YYXX00.YYID00%TYPE; LS_YBGHH0 SF_BRXXB0.YBGHH0%type; --医保挂号号 LS_AAC999 BM_BRXXB0.AAC999%type; --人员编号 LS_JZRQ00 SF_BRXXB0.JZRQ00%type; --就诊日期 LS_JZSJ00 SF_BRXXB0.JZSJ00%type; --就诊时间 LS_YBZXLB SF_BRXXB0.YBZXLB%type; --医保中心类别 --LS_BAE465 BM_TSBZB0.BAE465%type; --国家病种编码 LS_BAEMC0 BM_TSBZB0.BAEMC0%type; --国家病种名称 LS_BRZS00 YS_BRBQB0.BRZS00%type; --病人主述 LS_TYCJ00 BM_TYCJBM%rowtype; in_TSBZBH varchar2(30); --特殊病种编号 in_SYSSLB varchar2(6); --计划生育手术类别 in_SYSSRQ varchar2(20); --计划生育手术日期 in_matn_type varchar2(6); --生育类别 in_geso_val varchar2(2); --孕周数 in_MED_TYPE varchar2(6); --医疗类别 LS_birctrl_type_brxx SF_BRXXB0.birctrl_type%type; --计划生育手术类别 LS_birctrl_matn_date_brxx SF_BRXXB0.birctrl_matn_date%type; --计划生育手术或生育日期 LS_matn_type_brxx SF_BRXXB0.matn_type%type; --生育类别 LS_geso_val_brxx SF_BRXXB0.geso_val%type; --孕周数 LS_mdtrt_id_fm_ybghh0 varchar2(30); --mdtrt_id: 就诊ID BM_psn_no varchar2(30); --psn_no: 人员编号 LS_main_cond_dscr varchar2(1000); --main_cond_dscr: 主要病情描述 LS_dise_codg_fm_tsbzbh varchar2(30); --dise_codg: 病种编码, 按照标准编码填写：按病种结算病种目录代码(bydise_setl_list_code)、门诊慢特病病种目录代码(opsp_dise_cod) LS_birctrl_type varchar2(10); --birctrl_type: 计划生育手术类别, 生育门诊按需录入 LS_birctrl_matn_date varchar2(20); --birctrl_matn_date: 计划生育手术或生育日期, 生育门诊按需录入，yyyy-MM-dd LS_matn_type varchar2(6); --matn_type: 生育类别 LS_geso_val varchar2(2); --geso_val: 孕周数 LS_exp_content varchar2(4000); --exp_content: 字段扩展 LS_dise_codg SF_YBJKRZ.dise_codg%type;--dise_codg: 病种编码, 按照标准编码填写：按病种结算病种目录代码(bydise_setl_list_code)、门诊慢特病病种目录代码(opsp_dise_cod) LS_dise_name SF_YBJKRZ.dise_name%type;--dise_name: 病种名称 LS_aaz149 SF_YBJKRZ.aaz149%type;--aaz149:病种编码 LS_bae465 SF_YBJKRZ.bae465%type;--病种国家病种代码 LS_akc190 SF_YBJKRZ.AKC190%type;--akc190:医保挂号号 LS_aae072 SF_YBJKRZ.AAE072%type;--aae072:医保单据号 LS_aka078 SF_YBJKRZ.aka078%type;--医疗就诊方式(福建:aka078,国家:) LS_aka130 SF_YBJKRZ.aka130%type;--医疗类别(福建:aka130,国家:med_type) LS_med_type SF_YBJKRZ.med_type%type;--med_type: 医疗类别 LS_aaz500 SF_YBJKRZ.aaz500%type;--aaz500:社会保障卡号 LS_aac002 SF_YBJKRZ.aac002%type;--aac002:证件号码（社会保障号） LS_aac003 SF_YBJKRZ.aac003%type;--aac003:姓名 LS_psn_no SF_YBJKRZ.psn_no%type;--psn_no:国家医保]人员编号 LS_begntime SF_YBJKRZ.begntime%type;--医保入院时间, 格式："YYYY-MM-DD HH24:MI:SS"=akc192 LS_endtime SF_YBJKRZ.endtime%type;--医保出院时间, 格式："YYYY-MM-DD HH24:MI:SS"=akc194 LS_dr_codg SF_YBJKRZ.atddr_no%type;--[国家医保]医师编码=bkc007 LS_dr_name SF_YBJKRZ.dr_name%type;--[国家医保]医师姓名=bkc006 LS_dept_code SF_YBJKRZ.dept_code%type;--[国家医保]科室编码=akf001 LS_dept_name SF_YBJKRZ.dept_name%type;--[国家医保]科室名称=akf002 LS_mdtrt_id SF_YBJKRZ.mdtrt_id%type;--就诊id或akc190 LS_diag_type_fm_zzdbz0 varchar2(10); --diag_type: 诊断类别 LS_diag_srt_no varchar2(10); --diag_srt_no: 诊断排序号 LS_diag_code_fm_gjbzbm varchar2(50); --diag_code: 诊断代码 LS_diag_name_fm_gjbzmc varchar2(100); --diag_name: 诊断名称 LS_diag_dept_fm_ghks00 varchar2(50); --diag_dept: 诊断科室 LS_dise_dor_no_fm_jzys00 varchar2(30); --dise_dor_no: 诊断医生编码 LS_dise_dor_name_fm_jzysxm varchar2(50); --dise_dor_name: 诊断医生姓名 LS_diag_time_fm_zdrqsj varchar2(20); --diag_time: 诊断时间, yyyy-MM-dd HH:mm:ss LS_vali_flag varchar2(10); --vali_flag: 有效标志 LS_medical_type varchar2(10); --medicaltype:体检病人标识 1：体检病人 0:非体检病人 LS_medical_bke087 varchar2(50);--medicalbke087:体检结算：诊断编码bke087 LS_medical_bke045 varchar2(50);--medicalbke045:体检结算：固定收费项目bke045 --修改医保发送明细表 cursor CUR_UPDATE_YBJKMX_MDTRTINFO is select YBJKID,JKMXID,JDBM00,ZJDBM0,JKSXBM,JKSXMC,JKFSZ0,JKFHZ0,FSLXBZ from SF_YBJKMX where YBJKID=PYBJKID and FSLXBZ in ('1','3') order by JKMXID; --查询主项数据 cursor CUR_QUERY_Main is select YBJKID,JKMXID,JDBM00,ZJDBM0,JKSXBM,JKSXMC,JKFSZ0,JKFHZ0,FSLXBZ from SF_YBJKMX where YBJKID=PYBJKID and FSLXBZ in ('1','3') and CJBM00 in ('AllIn') order by JKMXID; --查询明细数据 cursor CUR_QUERY_detail is select YBJKID,JKMXID,JDBM00,ZJDBM0,JKSXBM,JKSXMC,JKFSZ0,JKFHZ0,FSLXBZ from SF_YBJKMX where YBJKID=PYBJKID and FSLXBZ in ('1','3') and CJBM00=PJKCJBM order by JKMXID; --查询诊断数据 cursor CUR_QUERY_brzdxx is select a.DIAG_TYPE,rownum DIAG_SRT_NO,a.DIAG_CODE,a.DIAG_NAME,a.DIAG_DEPT,a.DISE_DOR_NO,a.DISE_DOR_NAME, substr(a.ZDRQ00,1,4)||'-'||substr(a.ZDRQ00,5,2)||'-'||substr(a.ZDRQ00,7,2)||' '||a.ZDSJ00 DIAG_TIME, '1' as vali_flag --vali_flag: 有效标志(0:无效 1:有效) from ( select nvl(SF_XT_TransYNZDLBToYBBM('0',ZZDBZ0),a.ZZDBZ0) diag_type, nvl(nvl(a.GJBZBM,SF_XT_GETGJBZBMForICD900(a.ICD900,'1')),a.ICD900) diag_code, nvl(nvl(a.GJBZMC,SF_XT_GETGJBZBMForICD900(a.ICD900,'2')),a.ZDMC00) diag_name, nvl(SF_XT_TransKSBMToGJYBBM(A.JZKS00,'1'),A.JZKS00) diag_dept, nvl(SF_XT_TransYSToGJYBDM(A.JZYS00),A.JZYS00) dise_dor_no, A.JZYSXM dise_dor_name, nvl(trim(a.ZDRQ00),to_char(sysdate,'YYYYMMDD')) ZDRQ00, nvl(trim(a.ZDSJ00),to_char(sysdate,'HH24:MI:SS')) ZDSJ00 from YS_BRZDXX A where GHID00=PGHID00 order by nvl(SF_XT_TransYNZDLBToYBBM('0',ZZDBZ0),a.ZZDBZ0) ) a; --查询诊断信息字典 cursor CUR_QUERY_zdxxzd is select A.JDBM00,A.JKSXBM,A.JKSXMC, substrb(a.JKFSLX,1,1) FSLXBZ,substrb(a.ZDTYPE,1,1) ZDLX00,a.BZ0000,A.CJBM00 from BM_TYCJZD A where A.CJBM00=PJKCJBM and JDBM00 in ('diseinfo') and nvl(A.SFYX00,'1')='1' and A.JKFSLX in ('1','2','3'); --查询(体检) cursor CUR_medical_brzdxx is select '1' as diag_type, --1.diag_type: 诊断类别 '1' as diag_srt_no, --2.diag_srt_no: 诊断排序号 nvl(a.DYBZBM,a.ICD900) diag_code, --3.diag_code: 诊断代码 nvl(a.DYJBMC,a.JBMC00) diag_name, --4.diag_name: 诊断名称 LS_dept_code as diag_dept, --5.diag_dept: 诊断科室 LS_dr_codg as dise_dor_no, --6.dise_dor_no: 诊断医生编码 LS_dr_name as dise_dor_name, --7.dise_dor_name: 诊断医生姓名 LS_begntime as diag_time, --8.diag_time: 诊断时间 '1' as vali_flag --9.vali_flag: 有效标志(0:无效 1:有效) from XT_ICD900 A where A.ICD900=LS_medical_bke087 and nvl(A.SYBZ00,'1')='1'; begin PZXZT00:=0; --输出:执行状态 0:失败 1：成功 POUTXML:=null;--输出:出参(XML) PERRMSG:=null;--输出:错误信息 LS_QQDATA:=null;--请求报文 LS_MainJson:=null;---主项Json LS_detailJson:=null;---明细Json LS_inputJson:=null;---input输入 LS_zdxxJson := null; --诊断Json LS_zdxxListJson := null; --诊断列表Json LS_diseinfoJson := null; LS_HISCS0:=substrb(PINXML0,1,4000);--HIS前台请求参数 LS_CZRQ00:=to_char(sysdate,'YYYYMMDD'); LS_CZSJ00:=to_char(sysdate,'HH24:MI:SS'); LS_DateTimeStr:=to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'); --日期时间型:格式为：yyyy-MM-dd HH:mm:ss LS_DateStr:=to_char(sysdate,'YYYY-MM-DD'); --日期型:格式为：yyyy-MM-dd LS_TimeStr:=to_char(sysdate,'HH24:MI:SS'); --时间型:格式为：HH:mm:ss --:[国家医保]交易类型 1:直接调用医保DLL 3:调用服务接口 LS_INFNLX:= substrb(SF_XT_GETXMLSTR('INFNLX',PINXML0),1,2); if LS_INFNLX is null then LS_INFNLX:= substrb(SF_XT_GETXMLSTR('INFNLX',POTHER0),1,2); end if; --:请求节点标识 LS_QQJDBS:= substrb(SF_XT_GETXMLSTR('QQJDBS',PINXML0),1,30); if LS_QQJDBS is null then LS_QQJDBS:= substrb(SF_XT_GETXMLSTR('QQJDBS',POTHER0),1,30); end if; --:返回节点标识 LS_FHJDBS:= substrb(SF_XT_GETXMLSTR('FHJDBS',PINXML0),1,30); if LS_FHJDBS is null then LS_FHJDBS:= substrb(SF_XT_GETXMLSTR('FHJDBS',POTHER0),1,30); end if; if nvl(PBRID00,0) <= 0 then PERRMSG := '病人id为空'; PZXZT00:=0; --执行状态 0:失败 1：成功 return; end if; if nvl(PGHID00,0) <= 0 then PERRMSG := '挂号id为空'; PZXZT00:=0; --执行状态 0:失败 1：成功 return; end if; begin select a.YBGHH0,b.AAC999,b.PSN_NO,a.YBZXLB, nvl(a.JZRQ00,nvl(a.GHRQ00,to_char(sysdate,'YYYYMMDD'))), nvl(a.JZSJ00,nvl(a.GHSJ00,to_char(sysdate,'HH24:MI:SS'))), (select BRZS00 from YS_BRBQB0 where GHID00=a.GHID00), a.birctrl_type,a.birctrl_matn_date,a.matn_type,a.geso_val into LS_YBGHH0,LS_AAC999,BM_psn_no,LS_YBZXLB, LS_JZRQ00, LS_JZSJ00, LS_BRZS00, LS_birctrl_type_brxx,LS_birctrl_matn_date_brxx,LS_matn_type_brxx,LS_geso_val_brxx from SF_BRXXB0 a,BM_BRXXB0 b where a.BRID00=b.BRID00 and a.BRID00=PBRID00 and a.GHID00=PGHID00; exception when others then PERRMSG := '未找到病人基本信息(BRID00='||PBRID00||')'; PZXZT00:=0; --执行状态 0:失败 1：成功 return; end; --获取中间表数据 begin select A.aaz149,A.bae465,A.dise_codg,A.dise_name,a.akc190,a.aae072,a.aka078,a.aka130,a.med_type, A.aaz500,A.aac002,A.aac003,A.psn_no,A.begntime,A.endtime,a.atddr_no,a.dr_name,a.dept_code,a.dept_name,a.mdtrt_id into LS_aaz149,LS_bae465,LS_dise_codg,LS_dise_name,LS_akc190,LS_aae072,LS_aka078,LS_aka130,LS_med_type, LS_aaz500,LS_aac002,LS_aac003,LS_psn_no,LS_begntime,LS_endtime,LS_dr_codg,LS_dr_name,LS_dept_code,LS_dept_name,LS_mdtrt_id from SF_YBJKRZ A where YBJKID=PYBJKID; exception when others then LS_aaz149:=null;--aaz149:病种编码 LS_bae465:=null;--病种国家病种代码 LS_dise_codg:=null;--dise_codg: 病种编码 LS_dise_name:=null;--dise_name: 病种名称 LS_akc190:=null;--akc190:医保挂号号 LS_aae072:=null;--aae072:医保单据号 LS_aka078:=null;--医疗就诊方式(福建:aka078,国家:) LS_aka130:=null;--医疗类别(福建:aka130,国家:med_type) LS_med_type:=null;--med_type: 医疗类别 LS_aaz500:=null;---aaz500:社会保障卡号 LS_aac002:=null;---aac002:证件号码（社会保障号） LS_aac003:=null;---aac003:姓名 LS_psn_no:=null;---psn_no:国家医保]人员编号 LS_begntime:=null;---医保入院时间, 格式："YYYY-MM-DD HH24:MI:SS"=akc192 LS_endtime:=null;---医保出院时间, 格式："YYYY-MM-DD HH24:MI:SS"=akc194 LS_dr_codg:=null;--[国家医保]医师编码=bkc007 LS_dr_name:=null;--[国家医保]医师姓名=bkc006 LS_dept_code:=null;--[国家医保]科室编码=akf001 LS_dept_name:=null;--[国家医保]科室名称=akf002 end; in_TSBZBH := substrb(SF_XT_GETXMLSTR('tsbzbh',PINXML0),1,30); --病种编号 in_MED_TYPE := substrb(SF_XT_GETXMLSTR('med_type',PINXML0),1,30); --医疗类别 in_SYSSLB := substrb(SF_XT_GETXMLSTR('birctrl_type',PINXML0),1,10); --计划生育手术类别 in_SYSSRQ := substrb(SF_XT_GETXMLSTR('birctrl_matn_date',PINXML0),1,20); --计划生育手术日期s in_matn_type := substrb(SF_XT_GETXMLSTR('matn_type',PINXML0),1,20); --生育类别 in_geso_val := substrb(SF_XT_GETXMLSTR('geso_val',PINXML0),1,20); --孕周数 --体检病人信息 LS_medical_type :=substrb(SF_XT_GETXMLSTR('medicaltype',POTHER0),1,10); --medicaltype:体检病人标识 1：体检病人 0:非体检病人 LS_medical_bke087 :=substrb(SF_XT_GETXMLSTR('medicalbke087',POTHER0),1,50);--medicalbke087:体检结算：诊断编码bke087 LS_medical_bke045 :=substrb(SF_XT_GETXMLSTR('medicalbke045',POTHER0),1,50);--medicalbke045:体检结算：固定收费项目bke045 if LS_BAE465 is null then LS_BAE465 := SF_XT_GETGJBZBMForTSBZBM(LS_YBZXLB,in_TSBZBH,'1'); end if; LS_BAEMC0 := SF_XT_GETGJBZBMForTSBZBM(LS_YBZXLB,in_TSBZBH,'2'); LS_med_type := nvl(LS_med_type,in_MED_TYPE); if LS_med_type is null then if in_TSBZBH is not null then --门诊慢特病 LS_med_type := '14'; elsif in_SYSSLB is not null or LS_birctrl_type_brxx is not null then --生育门诊 LS_med_type := '51'; else LS_med_type := '11'; --普通门诊 end if; end if; ---诊断为“健康查体” 修改 med_type select yyid00 into ls_yyid00 from xt_yyxx00 ; if ls_yyid00='229112' then if PGHID00 > 0 then for ZDXX in CUR_QUERY_brzdxx loop if instrb(ZDXX.diag_name,'健康查体') > 0 then LS_med_type:='9107'; end if; end loop; end if; END IF; LS_mdtrt_id_fm_ybghh0 := trim(substrb(nvl(LS_mdtrt_id,LS_YBGHH0),1,30)); --1.mdtrt_id: 就诊ID LS_psn_no := trim(substrb(nvl(BM_psn_no,LS_psn_no),1,30)); --2.psn_no: 人员编号 LS_med_type := trim(substrb(LS_med_type,1,10)); --3.med_type: 医疗类别(要写成函数转换) if LS_begntime is null then LS_begntime := to_char(to_date(LS_JZRQ00||LS_JZSJ00,'YYYYMMDDHH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS'); --4.begntime: 开始时间, 就诊时间yyyy-MM-dd HH:mm:ss end if; LS_main_cond_dscr := trim(substrb(LS_BRZS00,1,1000)); --5.main_cond_dscr: 主要病情描述 LS_dise_codg_fm_tsbzbh := trim(substrb(nvl(LS_dise_codg,LS_BAE465),1,30)); --6.dise_codg: 病种编码, 按照标准编码填写：按病种结算病种目录代码(bydise_setl_list_code)、门诊慢特病病种目录代码(opsp_dise_cod) LS_dise_name := trim(substrb(nvl(LS_dise_name,LS_BAEMC0),1,200)); --7.dise_name: 病种名称 if LS_med_type in ('51') then if in_SYSSLB is not null then LS_birctrl_type := trim(substrb(in_SYSSLB,1,10)); --8.birctrl_type: 计划生育手术类别, 生育门诊按需录入 else LS_birctrl_type := trim(substrb(LS_birctrl_type_brxx,1,10)); end if; if in_SYSSRQ is not null then --9.birctrl_matn_date: 计划生育手术或生育日期, 生育门诊按需录入，yyyy-MM-dd LS_birctrl_matn_date := substr(in_SYSSRQ,1,4)||'-'||substr(in_SYSSRQ,5,2)||'-'||substr(in_SYSSRQ,7,2); elsif LS_birctrl_matn_date_brxx is not null then LS_birctrl_matn_date := substr(LS_birctrl_matn_date_brxx,1,4)||'-'||substr(LS_birctrl_matn_date_brxx,5,2)||'-'||substr(LS_birctrl_matn_date_brxx,7,2); else LS_birctrl_matn_date := null; end if; if in_matn_type is not null then --10. matn_type: 生育类别 LS_matn_type := substrb(trim(in_matn_type),1,6); else LS_matn_type := substrb(trim(LS_matn_type_brxx),1,6); end if; if in_geso_val is not null then --11. geso_val: 孕周数 LS_geso_val := substrb(trim(in_geso_val),1,2); else LS_geso_val := substrb(trim(LS_geso_val_brxx),1,2); end if; else LS_birctrl_type := null; LS_birctrl_matn_date := null; LS_matn_type := null; LS_geso_val := null; end if; LS_exp_content := null; --12. exp_content: 字段扩展 --2022.03.21 LS_dise_codg_fm_tsbzbh 为空加个空格 if LS_dise_codg_fm_tsbzbh is null then LS_dise_codg_fm_tsbzbh:=' '; end if; if LS_dise_name is null then LS_dise_name:=' '; end if; --插入表SF_YBJKMX insert into SF_YBJKMX(YBJKID,JKMXID,JDBM00,ZJDBM0,ZDBM00,ZDMC00, JKSXBM,JKSXMC,JKFSZ0,JKFHZ0,FSLXBZ,ZDLX00,BZ0000,CJBM00) select PYBJKID,A.ZDNBID,A.JDBM00,null,substrb(nvl(A.JKDZBM,A.JKSXBM),1,50),substrb(nvl(A.JKDZMC,JKSXMC),1,100), A.JKSXBM,A.JKSXMC,null,null,substrb(a.JKFSLX,1,1),substrb(a.ZDTYPE,1,1),a.BZ0000,A.CJBM00 from BM_TYCJZD A where A.CJBM00=PJKCJBM and JDBM00 in ('mdtrtinfo') and nvl(A.SFYX00,'1')='1' and A.JKFSLX in ('1','2','3'); --修改:医保发送变量赋值 for C_YBFS in CUR_UPDATE_YBJKMX_MDTRTINFO loop LS_JKFSZ0:=null;--接口发送值 LS_JKFHZ0:=null;--接口返回值 LS_VALUE0:=null;--请求报文字符串 if C_YBFS.JKSXBM = 'mdtrt_id' then LS_JKFSZ0:=substrb(LS_mdtrt_id_fm_ybghh0,1,250); --1.mdtrt_id: 就诊ID update SF_YBJKMX set JKFSZ0=LS_JKFSZ0,JKFHZ0=LS_JKFHZ0,VALUE0=LS_VALUE0 where YBJKID=PYBJKID and JKSXBM=C_YBFS.JKSXBM and JKMXID=C_YBFS.JKMXID; elsif C_YBFS.JKSXBM = 'psn_no' then LS_JKFSZ0:=substrb(LS_psn_no,1,250); --2.psn_no: 人员编号 update SF_YBJKMX set JKFSZ0=LS_JKFSZ0,JKFHZ0=LS_JKFHZ0,VALUE0=LS_VALUE0 where YBJKID=PYBJKID and JKSXBM=C_YBFS.JKSXBM and JKMXID=C_YBFS.JKMXID; elsif C_YBFS.JKSXBM='med_type' then --3.med_type: 医疗类别 LS_JKFSZ0:=substrb(LS_med_type,1,250); update SF_YBJKMX set JKFSZ0=LS_JKFSZ0,JKFHZ0=LS_JKFHZ0,VALUE0=LS_VALUE0 where YBJKID=PYBJKID and JKSXBM=C_YBFS.JKSXBM and JKMXID=C_YBFS.JKMXID; elsif C_YBFS.JKSXBM='begntime' then --4.begntime: 开始时间, 就诊时间yyyy-MM-dd HH:mm:ss LS_JKFSZ0:=substrb(LS_begntime,1,250); update SF_YBJKMX set JKFSZ0=LS_JKFSZ0,JKFHZ0=LS_JKFHZ0,VALUE0=LS_VALUE0 where YBJKID=PYBJKID and JKSXBM=C_YBFS.JKSXBM and JKMXID=C_YBFS.JKMXID; elsif C_YBFS.JKSXBM='main_cond_dscr' then --5.main_cond_dscr: 主要病情描述 LS_JKFSZ0:=substrb(LS_main_cond_dscr,1,250); update SF_YBJKMX set JKFSZ0=LS_JKFSZ0,JKFHZ0=LS_JKFHZ0,VALUE0=LS_VALUE0 where YBJKID=PYBJKID and JKSXBM=C_YBFS.JKSXBM and JKMXID=C_YBFS.JKMXID; elsif C_YBFS.JKSXBM='dise_codg' then --6.dise_codg: 病种编码, 按照标准编码填写：按病种结算病种目录代码(bydise_setl_list_code)、门诊慢特病病种目录代码(opsp_dise_cod) LS_JKFSZ0:=substrb(LS_dise_codg_fm_tsbzbh,1,250); update SF_YBJKMX set JKFSZ0=LS_JKFSZ0,JKFHZ0=LS_JKFHZ0,VALUE0=LS_VALUE0 where YBJKID=PYBJKID and JKSXBM=C_YBFS.JKSXBM and JKMXID=C_YBFS.JKMXID; elsif C_YBFS.JKSXBM='dise_name' then --7.dise_name: 病种名称 LS_JKFSZ0:=substrb(LS_dise_name,1,250); update SF_YBJKMX set JKFSZ0=LS_JKFSZ0,JKFHZ0=LS_JKFHZ0,VALUE0=LS_VALUE0 where YBJKID=PYBJKID and JKSXBM=C_YBFS.JKSXBM and JKMXID=C_YBFS.JKMXID; elsif C_YBFS.JKSXBM='birctrl_type' then --8.birctrl_type: 计划生育手术类别, 生育门诊按需录入 LS_JKFSZ0:=substrb(LS_birctrl_type,1,250); update SF_YBJKMX set JKFSZ0=LS_JKFSZ0,JKFHZ0=LS_JKFHZ0,VALUE0=LS_VALUE0 where YBJKID=PYBJKID and JKSXBM=C_YBFS.JKSXBM and JKMXID=C_YBFS.JKMXID; elsif C_YBFS.JKSXBM='birctrl_matn_date' then --9.birctrl_matn_date: 计划生育手术或生育日期, 生育门诊按需录入，yyyy-MM-dd LS_JKFSZ0:=substrb(LS_birctrl_matn_date,1,250); update SF_YBJKMX set JKFSZ0=LS_JKFSZ0,JKFHZ0=LS_JKFHZ0,VALUE0=LS_VALUE0 where YBJKID=PYBJKID and JKSXBM=C_YBFS.JKSXBM and JKMXID=C_YBFS.JKMXID; elsif C_YBFS.JKSXBM='matn_type' then --10.matn_type: 生育类别 LS_JKFSZ0:=substrb(LS_matn_type,1,250); update SF_YBJKMX set JKFSZ0=LS_JKFSZ0,JKFHZ0=LS_JKFHZ0,VALUE0=LS_VALUE0 where YBJKID=PYBJKID and JKSXBM=C_YBFS.JKSXBM and JKMXID=C_YBFS.JKMXID; elsif C_YBFS.JKSXBM='geso_val' then --11.geso_val: 孕周数 LS_JKFSZ0:=substrb(LS_geso_val,1,250); update SF_YBJKMX set JKFSZ0=LS_JKFSZ0,JKFHZ0=LS_JKFHZ0,VALUE0=LS_VALUE0 where YBJKID=PYBJKID and JKSXBM=C_YBFS.JKSXBM and JKMXID=C_YBFS.JKMXID; elsif C_YBFS.JKSXBM='exp_content' then --12.exp_content: 字段扩展 LS_JKFSZ0:=substrb(LS_exp_content,1,250); update SF_YBJKMX set JKFSZ0=LS_JKFSZ0,JKFHZ0=LS_JKFHZ0,VALUE0=LS_VALUE0 where YBJKID=PYBJKID and JKSXBM=C_YBFS.JKSXBM and JKMXID=C_YBFS.JKMXID; end if; end loop; --查询主项数据 for C_Main in CUR_QUERY_Main loop LS_MainJson := SF_XT_GETJSONSTR(LS_MainJson,C_Main.JKSXBM,C_Main.JKFSZ0,'0'); end loop; if LS_MainJson is null then PZXZT00:=0; PERRMSG:='国家医保接口(就诊信息上传)主项报文不能为空!'; return; end if; --查询明细数据 for C_detail in CUR_QUERY_detail loop LS_detailJson := SF_XT_GETJSONSTR(LS_detailJson,C_detail.JKSXBM,C_detail.JKFSZ0,'0'); end loop; --查询诊断数据 LS_diseinfoJson := null; LS_zdxxListJson := null; --体检病人信息 if LS_medical_type='1' and LS_medical_bke087 is not null then for C_zdxxlist in CUR_medical_brzdxx loop LS_zdxxJson := null; LS_diag_type_fm_zzdbz0 :=trim(substrb(C_zdxxlist.diag_type,1,3));--diag_type: 诊断类别 LS_diag_srt_no :=trim(substrb(C_zdxxlist.diag_srt_no,1,2));--diag_srt_no: 诊断排序号 LS_diag_code_fm_gjbzbm :=trim(substrb(C_zdxxlist.diag_code,1,50));--diag_code: 诊断代码 LS_diag_name_fm_gjbzmc :=trim(substrb(C_zdxxlist.diag_name,1,100));--diag_name: 诊断名称 LS_diag_dept_fm_ghks00 :=trim(substrb(C_zdxxlist.diag_dept,1,50)); --diag_dept: 诊断科室 LS_dise_dor_no_fm_jzys00 :=trim(substrb(C_zdxxlist.dise_dor_no,1,30));--dise_dor_no: 诊断医生编码 LS_dise_dor_name_fm_jzysxm :=trim(substrb(C_zdxxlist.dise_dor_name,1,50));--dise_dor_name: 诊断医生姓名 LS_diag_time_fm_zdrqsj :=trim(substrb(C_zdxxlist.diag_time,1,20)); --diag_time: 诊断时间, yyyy-MM-dd HH:mm:ss if lengthb(LS_diag_time_fm_zdrqsj)<>19 then LS_diag_time_fm_zdrqsj:=LS_begntime; end if; LS_vali_flag :=trim(substrb(C_zdxxlist.vali_flag,1,3)); --vali_flag: 有效标志(0:无效 1:有效) LS_zdxxJson := SF_XT_GETJSONSTR(LS_zdxxJson,'diag_type',LS_diag_type_fm_zzdbz0,'0'); --1.diag_type: 诊断类别 LS_zdxxJson := SF_XT_GETJSONSTR(LS_zdxxJson,'diag_srt_no',LS_diag_srt_no,'0'); --2.diag_srt_no: 诊断排序号 LS_zdxxJson := SF_XT_GETJSONSTR(LS_zdxxJson,'diag_code',LS_diag_code_fm_gjbzbm,'0'); --3.diag_code: 诊断代码 LS_zdxxJson := SF_XT_GETJSONSTR(LS_zdxxJson,'diag_name',LS_diag_name_fm_gjbzmc,'0'); --4.diag_name: 诊断名称 LS_zdxxJson := SF_XT_GETJSONSTR(LS_zdxxJson,'diag_dept',LS_diag_dept_fm_ghks00,'0'); --5.diag_dept: 诊断科室 LS_zdxxJson := SF_XT_GETJSONSTR(LS_zdxxJson,'dise_dor_no',LS_dise_dor_no_fm_jzys00,'0'); --6.dise_dor_no: 诊断医生编码 LS_zdxxJson := SF_XT_GETJSONSTR(LS_zdxxJson,'dise_dor_name',LS_dise_dor_name_fm_jzysxm,'0'); --7.dise_dor_name: 诊断医生姓名 LS_zdxxJson := SF_XT_GETJSONSTR(LS_zdxxJson,'diag_time',LS_diag_time_fm_zdrqsj,'0'); --8.diag_time: 诊断时间 LS_zdxxJson := SF_XT_GETJSONSTR(LS_zdxxJson,'vali_flag',LS_vali_flag,'0'); --9.vali_flag: 有效标志(0:无效 1:有效) LS_zdxxListJson := SF_XT_GETJSONSTR(LS_zdxxListJson,null,LS_zdxxJson,'1'); --插入表SF_YBJKMX insert into SF_YBJKMX(YBJKID,JKMXID,JDBM00,ZJDBM0,ZDBM00,ZDMC00,JKFSZ0,JKFHZ0,FSLXBZ,BZ0000,VALUE0) select PYBJKID,SQ_SF_YBJKMX_JKMXID.nextval,'diseinfo','input','diseinfo','诊断明细',null,null,'1',null,'{'||LS_zdxxJson||'}' from dual; end loop; else for C_zdxxlist in CUR_QUERY_brzdxx loop LS_zdxxJson := null; LS_diag_type_fm_zzdbz0 :=trim(substrb(C_zdxxlist.diag_type,1,3));--diag_type: 诊断类别 LS_diag_srt_no :=trim(substrb(C_zdxxlist.diag_srt_no,1,2));--diag_srt_no: 诊断排序号 LS_diag_code_fm_gjbzbm :=trim(substrb(C_zdxxlist.diag_code,1,50));--diag_code: 诊断代码 LS_diag_name_fm_gjbzmc :=trim(substrb(C_zdxxlist.diag_name,1,100));--diag_name: 诊断名称 LS_diag_dept_fm_ghks00 :=trim(substrb(C_zdxxlist.diag_dept,1,50)); --diag_dept: 诊断科室 LS_dise_dor_no_fm_jzys00 :=trim(substrb(C_zdxxlist.dise_dor_no,1,30));--dise_dor_no: 诊断医生编码 LS_dise_dor_name_fm_jzysxm :=trim(substrb(C_zdxxlist.dise_dor_name,1,50));--dise_dor_name: 诊断医生姓名 LS_diag_time_fm_zdrqsj :=trim(substrb(C_zdxxlist.diag_time,1,20)); --diag_time: 诊断时间, yyyy-MM-dd HH:mm:ss if lengthb(LS_diag_time_fm_zdrqsj)<>19 then LS_diag_time_fm_zdrqsj:=LS_begntime; end if; LS_vali_flag :=trim(substrb(C_zdxxlist.vali_flag,1,3)); --vali_flag: 有效标志(0:无效 1:有效) LS_zdxxJson := SF_XT_GETJSONSTR(LS_zdxxJson,'diag_type',LS_diag_type_fm_zzdbz0,'0'); --1.diag_type: 诊断类别 LS_zdxxJson := SF_XT_GETJSONSTR(LS_zdxxJson,'diag_srt_no',LS_diag_srt_no,'0'); --2.diag_srt_no: 诊断排序号 LS_zdxxJson := SF_XT_GETJSONSTR(LS_zdxxJson,'diag_code',LS_diag_code_fm_gjbzbm,'0'); --3.diag_code: 诊断代码 LS_zdxxJson := SF_XT_GETJSONSTR(LS_zdxxJson,'diag_name',LS_diag_name_fm_gjbzmc,'0'); --4.diag_name: 诊断名称 LS_zdxxJson := SF_XT_GETJSONSTR(LS_zdxxJson,'diag_dept',LS_diag_dept_fm_ghks00,'0'); --5.diag_dept: 诊断科室 LS_zdxxJson := SF_XT_GETJSONSTR(LS_zdxxJson,'dise_dor_no',LS_dise_dor_no_fm_jzys00,'0'); --6.dise_dor_no: 诊断医生编码 LS_zdxxJson := SF_XT_GETJSONSTR(LS_zdxxJson,'dise_dor_name',LS_dise_dor_name_fm_jzysxm,'0'); --7.dise_dor_name: 诊断医生姓名 LS_zdxxJson := SF_XT_GETJSONSTR(LS_zdxxJson,'diag_time',LS_diag_time_fm_zdrqsj,'0'); --8.diag_time: 诊断时间 LS_zdxxJson := SF_XT_GETJSONSTR(LS_zdxxJson,'vali_flag',LS_vali_flag,'0'); --9.vali_flag: 有效标志(0:无效 1:有效) LS_zdxxListJson := SF_XT_GETJSONSTR(LS_zdxxListJson,null,LS_zdxxJson,'1'); --插入表SF_YBJKMX insert into SF_YBJKMX(YBJKID,JKMXID,JDBM00,ZJDBM0,ZDBM00,ZDMC00,JKFSZ0,JKFHZ0,FSLXBZ,BZ0000,VALUE0) select PYBJKID,SQ_SF_YBJKMX_JKMXID.nextval,'diseinfo','input','diseinfo','诊断明细',null,null,'1',null,'{'||LS_zdxxJson||'}' from dual; end loop; end if; --LS_diseinfoJson := SF_XT_GETJSONSTR(LS_diseinfoJson,'diseinfo',LS_zdxxListJson,'2'); LS_diseinfoJson := SF_XT_GETJSONSTR(LS_diseinfoJson,'diseinfo',LS_zdxxListJson,'2'); if LS_detailJson is null then PZXZT00:=0; PERRMSG:='国家医保接口(就诊信息上传)明细报文不能为空!'; return; end if; --明细项加标题 LS_detailJson := SF_XT_GETJSONSTR(null,'mdtrtinfo',LS_detailJson,'1'); --input输入 LS_inputJson := SF_XT_GETJSONSTR(LS_inputJson,'input',LS_detailJson||','||LS_diseinfoJson,'1'); --拼接整个报文, http服务拼接arg0, 非http直接拼接 if LS_INFNLX='3' then LS_QQDATA := SF_XT_GETJSONSTR(LS_QQDATA,'arg0',LS_MainJson||','||LS_inputJson,'1'); else LS_QQDATA := LS_MainJson||','||LS_inputJson; end if; LS_QQDATA:='{'||LS_QQDATA||'}'; --修改请求报文 update SF_YBJKRZ set QQDATA=LS_QQDATA where YBJKID=PYBJKID;--请求报文 if PCOMMIT='Y' then commit; end if; PZXZT00:=1; --输出:执行状态 0:失败 1：成功 PERRMSG:=null; --输出:错误信息 exception when no_data_found then PZXZT00:=0; PERRMSG:=substrb('门诊医保接口交互生成失败!原因:'||sqlerrm,1,200); rollback; when others then PZXZT00:=0; PERRMSG:=substrb('门诊医保接口交互生成失败!原因:'||sqlerrm,1,200); rollback; end;