CREATE OR REPLACE PROCEDURE SP_WM_TCMPAT_JXZHCL ( Popflag in varchar2, --操作类别 1不覆盖操作 2覆盖操作 Pxzczid in number, --WM_TCMPAT_LIST_DLD_DET表的xzczid值 Pmdcode in varchar2, --WM_TCMPAT_LIST_DLD_DET表的med_list_codg值 Pczy000 in number default 0, --操作员 Pczyks0 in number default 0, --操作员科室 Pcommit in varchar2 default 'N', --是否提交 Y是N否 Pycshow in varchar2 default '0', --出现异常是否显示 0不显示，1显示 Pxmlstr in varchar2 default '', --统一的备用入参，以xml格式编码 Pzxcgbz out number, --执行成功标志 1成功 0失败 Ptsxx00 out varchar2, --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 out varchar2, --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 Poutstr out varchar2 --统一的备用出参，以xml格式编码 ) -- MODIFICATION HISTORY -- Person Date Comments -- jlg 2021.12.29 created 国家医保平台西药中成药目录下载明细表解析转换处理过程 GGMK9EX-20211228-004 as ls_CZRQ00 char(8); ls_CZSJ00 char(8); ls_CZRQSJ char(16); ls_Count0 number(10); Ecustom exception; VRowMX WM_TCMPAT_LIST_DLD_DET%rowtype; --新医保目录更新明细表 VRowRZ GEN_LIST_DLD_LOG%rowtype; --新医保目录更新操作记录表 ls_YBGXRQ BM_YBSFXM.YBGXRQ%type; --医保更新日期 ls_PTFPBH BM_YBFPXM.PTFPBH%type; --国家医保平台发票项目类别 ls_ZYFPXM BM_YBSFXM.ZYFPXM%type; -- ls_MZFPXM BM_YBSFXM.MZFPXM%type; -- ls_XMMC00 BM_YBSFXM.XMMC00%type; -- ls_YPJX00 BM_YBSFXM.YPJX00%type; -- ls_XMDW00 BM_YBSFXM.XMDW00%type; -- ls_ZFBL00 BM_YBSFXM.ZFBL00%type; -- ls_SFYX00 BM_YBSFXM.SFYX00%type; -- ls_SFYBXM BM_YBSFXM.SFYBXM%type; -- ls_YBLB00 BM_YBSFXM.YBLB00%type; -- ls_PYSM00 BM_YBSFXM.PYSM00%type; -- ls_WBSM00 BM_YBSFXM.WBSM00%type; -- ls_GXXLH0 BM_YBSFXM.GXXLH0%type; -- ls_SFSX00 BM_YBSFXM.SFSX00%type; -- ls_SCCJ00 BM_YBSFXM.SCCJ00%type; -- ls_YPGG00 BM_YBSFXM.YPGG00%type; -- ls_BZ0000 BM_YBSFXM.BZ0000%type; -- ls_CPID00 BM_YBSFXM.CPID00%type; -- ls_ZXJJDW BM_YBSFXM.ZXJJDW%type; -- ls_CJR000 BM_YBSFXM.CJR000%type; -- ls_CFBL00 BM_YBSFXM.CFBL00%type; -- ls_YBZGDJ BM_YBSFXM.YBZGDJ%type; -- ls_YBZGDJ1 BM_YBSFXM.YBZGDJ1%type; -- ls_YBJSDJ BM_YBSFXM.YBJSDJ%type; -- ls_YBJSDJ1 BM_YBSFXM.YBJSDJ1%type; -- ls_BKEH83 BM_YBSFXM.BKEH83%type; --医保国家标准编码 cursor cur_YBZX is select FBBH00,YBZXLB from IC_YBBRLB a where FBBH00 = 3 and YBLB00 = YBZXLB and YBZXLB = 'G' and not exists(select 1 from BM_YBSFXM where FBBH00 = a.FBBH00 and YBZXLB = a.YBZXLB and XMBH00 = Pmdcode); --排除对应中心已存在的记录 begin Pzxcgbz:=0; ls_CZRQ00 := to_char(sysdate,'YYYYMMDD'); ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS'); ls_CZRQSJ := ls_CZRQ00||ls_CZSJ00; select * into VRowRZ from GEN_LIST_DLD_LOG where xzczid = Pxzczid; if sql%notfound then return; end if; select * into VRowMX from WM_TCMPAT_LIST_DLD_DET where xzczid = Pxzczid and med_list_codg = Pmdcode; if sql%notfound then return; end if; --如果不覆盖操作，则已转换的不处理 if Popflag = 1 and VRowMX.ZHCLBZ ='1' then Pzxcgbz:=1; return; end if; ls_YBGXRQ := replace(VRowMX.updt_time,'-',''); --医保更新日期 ls_XMMC00 := VRowMX.reg_name; --项目名称 ls_PTFPBH := (case when VRowMX.tcmpat_flag = '1' then '02' else '01' end); --医保发票项目 tcmpat_flag 中成药标志 1是 2否 --> 01西药 02成药 ls_YPJX00 := substr(VRowMX.drug_dosform,1,50); --剂型 ls_XMDW00 := substr(VRowMX.min_prcunt,1,20); --单位-->最小计价单位 ls_SFYX00 := (case when (VRowMX.vali_flag = '0' or VRowMX.vali_flag = 'N') then 'N' else 'Y' end); --是否有效 ls_ZFBL00 := 1; --自付比例 @@jlg暂无法从何取值 ls_SFYBXM := 'Y'; --是否医保项目 Y是 N否 @@jlg暂无法从何取值 ls_YBLB00 := (case when VRowMX.nat_hi_drug_list_clas_flag = '乙' then '5' else '4' end); --医保类别 (无对应项) 0医保项目,1部分自费医保项目,2非医保项目,3补充的非医保项目4甲类药品 5乙类药品 6民族药品 7非医保药品 ls_PYSM00 := substr(SF_XT_GETPYSM(ls_XMMC00),1,10); --拼音首码 ls_WBSM00 := substr(SF_XT_GETWBSM(ls_XMMC00),1,10); --五笔首码 ls_GXXLH0 := 0; --更新序列号 ls_SFSX00 := null; --收费上限 (无对应下) ls_SCCJ00 := substrb(VRowMX.prodentp_name,1,100); --生产厂家 ls_YPGG00 := substrb(VRowMX.drug_spec,1,50); --规格型号 --ls_BZ0000 := substrb(VRowMX.each_dos,1,250); --备注说明 ls_BZ0000 := substrb(trim(VRowMX.NAT_HI_DRUG_LIST_MEMO),1,250); --备注说明 WHY 20220602 ls_YBZGDJ := 0; --医保最高单价(大单位) ls_YBZGDJ1:= 0; --医保最高单价(小单位) ls_CPID00 := null; --医保产品ID号 (无对应项) ls_ZXJJDW := substrb(VRowMX.min_prcunt,1,10); --最小计价单位 ls_CFBL00 := null; --拆分比例（倍率） ls_CJR000 := VRowRZ.gxczy0; --创建人 ls_YBJSDJ := null; --医保结算单价(大单位) ls_YBJSDJ1:= null; --医保结算单价(小单位) ls_BKEH83 := substr(VRowMX.drug_std_code,1,50); --药监本位码 --先补充插入缺失的医保中心项目 for ybzx in cur_YBZX loop ls_ZYFPXM := SF_XT_GETYBFPBHByPTFPBH(ybzx.FBBH00,ybzx.YBZXLB,ls_PTFPBH); --根据国家医保平台发票编号获取院内医保住院发票 ls_MZFPXM := SF_XT_GETYBFPBHByPTFPBH(ybzx.FBBH00,ybzx.YBZXLB,ls_PTFPBH); --根据国家医保平台发票编号获取院内医保门诊发票 insert into BM_YBSFXM( FBBH00, YBZXLB, XMBH00, XMMC00, ZYFPXM, MZFPXM, YPJX00, XMDW00, ZFBL00, ZYZFBL, SFYX00, SFYBXM, YBLB00, PYSM00, WBSM00, GXXLH0, SFSX00, SCCJ00, YPGG00, BZ0000, YBZGDJ, YBZGDJ1, CPID00, ZXJJDW, CFBL00, CJR000, YBJSDJ, YBJSDJ1, BKEH83 ) values( ybzx.FBBH00, ybzx.YBZXLB, Pmdcode, ls_XMMC00, ls_ZYFPXM, ls_MZFPXM, ls_YPJX00, ls_XMDW00, ls_ZFBL00, ls_ZFBL00, ls_SFYX00, ls_SFYBXM, ls_YBLB00, ls_PYSM00, ls_WBSM00, ls_GXXLH0, ls_SFSX00, ls_SCCJ00, ls_YPGG00, ls_BZ0000, ls_YBZGDJ, ls_YBZGDJ1, ls_CPID00, ls_ZXJJDW, ls_CFBL00, ls_CJR000, ls_YBJSDJ, ls_YBJSDJ1, ls_BKEH83 ); end loop; --医保所有该项目的医保中心记录都修改；但只修改部分字段 update BM_YBSFXM set XMMC00 = ls_XMMC00, ZYFPXM = nvl(SF_XT_GETYBFPBHByPTFPBH(FBBH00,YBZXLB,ls_PTFPBH),ZYFPXM), --此三个字段，如果没有取到值，则仍按原来的 MZFPXM = nvl(SF_XT_GETYBFPBHByPTFPBH(FBBH00,YBZXLB,ls_PTFPBH),MZFPXM), -- YPJX00 = nvl(ls_YPJX00,YPJX00), -- XMDW00 = ls_XMDW00, SFYX00 = ls_SFYX00, PYSM00 = ls_PYSM00, WBSM00 = ls_WBSM00, SCCJ00 = ls_SCCJ00, YPGG00 = ls_YPGG00, BZ0000 = ls_BZ0000, ZFBL00 = ls_ZFBL00, ZYZFBL = ls_ZFBL00, SFYBXM = ls_SFYBXM, YBLB00 = ls_YBLB00, BKEH83 = nvl(ls_BKEH83,BKEH83), --改为如果传进来的为空，那还是按原来的 XGR000 = ls_CJR000, XGRQSJ = ls_CZRQSJ, YBGXRQ = ls_YBGXRQ where FBBH00 = 3 and YBZXLB = 'G' and XMBH00 = Pmdcode and (YBGXRQ is null or YBGXRQ < ls_YBGXRQ) --jlg 注意这个条件：只有当前医保更新日期小于或等于更新明细项目里的医保更新日期，才进行更新；如果当前医保更新日期大于更新明细项目的医保更新日期，则不进行更新 ; --此记录更新为 ‘已转换’ update WM_TCMPAT_LIST_DLD_DET set ZHCLBZ = '1' where xzczid = Pxzczid and med_list_codg = Pmdcode; --对应日志表更新为 ‘HIS提取未完成’ update GEN_LIST_DLD_LOG set HSTQBZ = '2' where xzczid = Pxzczid ; if Pcommit ='Y' then commit; end if; Pzxcgbz:=1; exception when no_data_found then if Pycshow = '1' then raise_application_error(-20001,substrb('没有找到数据！*'||sqlerrm,1,240)); else Ptsxx00:= substrb('没有找到数据！*'||sqlerrm,1,240); end if; when Ecustom then if Pycshow = '1' then raise_application_error(-20010,substrb(Ptsxx00||'！*',1,240)); end if; when others then if Pycshow = '1' then raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,240)); else Ptsxx00:= substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,240); end if; end;