create or replace procedure SP_XM_YBDR_XLS (AD_FBBH00 in number, --费别编号 AD_YBZXLB in char, --医保中心类别 AD_MLBH00 in char, --目录包含类型 '1':全部目录,'2'只包含药品,'3'只包含收费项目 --没用到 AD_GXLX00 in char, --更新类型 '1':完整更新,'2'部分更新 AD_CZY000 in number, --操作员编码 AD_GXHJLS out number, --更新之后记录数 AS_YHMSG0 out varchar, --存储过程提示的错误信息 AS_SYSMSG out varchar --系统提示的错误信息 ) as LS_COUNT0 number (5); LS_CZRQ00 char (8); LS_CZSJ00 char (8); cursor CUR_YBFPXM is select distinct YBFPXM from BM_YBYPML_TEMP09 A where not exists (select 1 from BM_YBFPXM B where B.XMMC00 = A.YBFPXM and YBZXLB = AD_YBZXLB); --项目编号重复记录 cursor CUR_XMBHCF is select XMBH00 from BM_YBYPML_TEMP09 A group by XMBH00 having count (1) > 1; -- MODIFICATION HISTORY -- Person Date Comments -- qks 2010.08.30 Create -- qks 2010.09.21 解决update bm_ybsfdy表速度慢问题,ad_MLBH00入参不再使用 -- zhangwz 2010.10.15 解决导入excel时如果是已经存在的医保编码时,没有导入zfbl00,YBLB00,SFYBXM,BZ0000相关信息. -- zhangwz 2011.01.29 zfbl00的decode函数增加以下几种自付比例:非医保,甲类,乙类.by XMGL-20110129-001 -- daihq 2013.01.08 增加写入BM_YBSFXM、BM_YBSFDY的ZYZFBL，取同表中的ZFBL00值,增加记录创建人、修改人、修改日期时间 -- zhangwz 2013.05.09 decode(zfbl00 的时候多考虑几种情况。 -- qks 2014.06.20 处理bm_ybypml_temp07.YBLB00取值错误问题;修改对应表药品、项目 -- qks 2014.12.05 修改对应表药品、项目,不修改XGR000,XGRQSJ -- qks 2014.12.11 铁保目录无法导入问题 -- qks 2016.02.23 重复的项目编号,需要进行异常提示; by XMGL9-20160223-001 -- qks 2016.03.17 省两费中心导入,部分项目YBLB00为空,SFYB00=N或Y; by XMGL9-20160318-001 -- qks 2016.03.23 医保项目导入，要求也要修改已存在医保项目的名称，规格，剂型，厂家字段; by XMGL9-20160324-001 -- qks 2016.03.30 医保项目导入，要求也要修改已存在医保项目的发票BM_YBSFXM.ZYFPXM,MZFPXM; by XMGL9-20160330-001 -- qks 2016.04.05 要求各种自付比例都可以导入 -- qks 2016.06.22 前台导入,生成医保项目会提示无效数字问题 by XMGL9-20160622-001 -- qks 2017.05.01 省人民反馈有些医保药品对应的医保限价字段值没有导入; by XMGL9-20170502-001 -- qks 2017.05.10 BM_YBSFXM表增加字段CPID00,ZXJJDW,CFBL00; by XMGL9-20170510-001 -- liwm 2018.06.04 BM_YBSFXM表增加字段YBZGDJ，YBZGDJ1 for XMGL9-20180604-001 begin LS_CZRQ00 := to_char (sysdate, 'YYYYMMDD'); LS_CZSJ00 := to_char (sysdate, 'HH24:MI:SS'); select count (1) into LS_COUNT0 from BM_YBYPML_TEMP09 where rownum = 1; if LS_COUNT0 = 0 then AS_YHMSG0 := '中间临时表bm_ybypml_temp09数据为空!'; AS_SYSMSG := sqlerrm; rollback; return; end if; for C_YBFPXM in CUR_XMBHCF loop AS_YHMSG0 := substrb (AS_YHMSG0 || C_YBFPXM.XMBH00 || ',', 1, 220); end loop; if AS_YHMSG0 is not null then AS_YHMSG0 := substrb ('以下项目编号存在重复:' || AS_YHMSG0, 1, 220); AS_SYSMSG := '请删除调整Excel文件重复项目编号记录后再导入！'; rollback; return; end if; for C_YBFPXM in CUR_YBFPXM loop AS_YHMSG0 := AS_YHMSG0 || C_YBFPXM.YBFPXM || ','; end loop; if AS_YHMSG0 is not null then AS_YHMSG0 := '以下医保发票项目不存在于表bm_ybfpxm中:' || AS_YHMSG0; AS_SYSMSG := '请直接修改字段bm_ybypml_temp09.YBFPXM或者重新修改Excel文件后再导入！'; rollback; return; end if; --1、从临时表bm_ybypml_temp09导入到bm_ybypml_temp07 SP_EXECUTE_SQL ('truncate table bm_ybypml_temp07'); --处理导入的excel编码与现有库的差集 SP_EXECUTE_SQL ('truncate table bm_ybypml_temp10'); --处理导入的excel编码与现有库的交集 update BM_YBYPML_TEMP09 set XMBH00 = trim (XMBH00), SFYP00 = decode (YBFPXM, '西药费', 'Y', '成药费', 'Y', '草药费', 'Y', 'N'), SFYB00 = decode (SFYB00, 'N', '否', 'Y', '是', SFYB00), SFSX00 = decode (trim (translate (trim (SFSX00), '.0123456789', ' ')), null, trim (SFSX00), null); --update bm_ybypml_temp09 set XMBH00=Trim(XMBH00),SFYP00=decode(ybfpxm,'西药费','Y','成药费','Y','草药费','Y','N'); insert into BM_YBYPML_TEMP07 --差集 (XMBH00, YPTYM0, YPMC00, YPJX00, YPJB00, YPJJDW, YPGG00, YPSCCJ, SFYB00, ZFBL00, YBLB00, YBFPXM, SFOTC0, BZ0000, CKJG00, GXY000, TNB000, MXXS00, SFSX00, GXRQ00, CPID00, ZXJJDW, CFBL00, YBZGDJ, YBZGDJ1) select A.XMBH00, YPTYM0, YPMC00, YPJX00, YPJB00, YPJJDW, YPGG00, YPSCCJ, decode (SFYB00, '是', 'Y', 'Y', 'Y', 'N'), decode ( SFYB00, '否', 1, decode ( nvl (ZFBL00, '无'), '供养子女', 1, '离休', 1, '无', decode (nvl (YBLB00, ' '), '非医保', 1, 0), '非医保', 1, '甲类', 0, '乙类', 0, '无自付', 0, '自付100%', 1, '自付5%', 0.05, '自付10%', 0.1, '自付15%', 0.15, '自付20%', 0.2, '自付25%', 0.25, '自付30%', 0.3, '自付35%', 0.35, '自付40%', 0.4, '自付60%', 0.6, '100%自付', 1, '5%自付', 0.05, '10%自付', 0.1, '15%自付', 0.15, '20%自付', 0.2, '25%自付', 0.25, '30%自付', 0.3, '35%自付', 0.35, '40%自付', 0.4, '60%自付', 0.6, '100%', 1, '0', 0, '5%', 0.05, '10%', 0.1, '15%', 0.15, '20%', 0.2, '25%', 0.25, '30%', 0.3, '35%', 0.35, '40%', 0.4, ' 60%', 0.6, '1', 1, '0', 0, '0.05', 0.05, '0.1', 0.1, '0.15', 0.15, '0.2', 0.2, '0.25', 0.25, '0.3', 0.3, '0.35', 0.35, '0.4', 0.4, '0.6', 0.6, '自付4%', 0.04, decode ( sign (instrb (ZFBL00, '自付')), 1, replace (replace (ZFBL00, '自付', ''), '%', '') / 100, ZFBL00))), --decode(sfyb00,'否',7,decode(yblb00,'非医保',7,'甲类',4,'乙类',5,'医保','8',decode(SFYB00,'Y','8','是','8','7'))) case when ( B.BH0000 = '01' or B.BH0000 = '02' or B.BH0000 = '03') then decode ( SFYB00, '否', '7', decode ( YBLB00, '非医保', '7', '甲类', '4', '乙类', '5', '医保', '8', decode (SFYB00, 'Y', '8', '是', '8', '7'))) else decode ( SFYB00, '否', '2', decode ( YBLB00, '非医保', '2', '甲类', '0', '乙类', '1', '医保', '1', decode (SFYB00, 'Y', '1', '是', '1', '2'))) end as YBLB00, B.BH0000, SFOTC0, BZ0000, CKJG00, GXY000, TNB000, MXXS00, SFSX00, GXRQ00, A.CPID00, A.ZXJJDW, A.CFBL00, A.YBZGDJ, A.YBZGDJ1 from BM_YBYPML_TEMP09 A, BM_YBFPXM B where B.XMMC00 = A.YBFPXM and B.YBZXLB = AD_YBZXLB and B.FBBH00 = AD_FBBH00 and not exists (select 1 from bm_ybsfxm where YBZXLB = AD_YBZXLB and FBBH00 = AD_FBBH00 and xmbh00=a.XMBH00); /*where B.XMMC00 = A.YBFPXM and B.YBZXLB = AD_YBZXLB and B.FBBH00 = AD_FBBH00 and A.XMBH00 in (select XMBH00 from BM_YBYPML_TEMP09 minus select XMBH00 from BM_YBSFXM where YBZXLB = AD_YBZXLB and FBBH00 = AD_FBBH00);*/ insert into BM_YBYPML_TEMP10 --交集 (XMBH00, YPTYM0, YPMC00, YPJX00, YPJB00, YPJJDW, YPGG00, YPSCCJ, SFYB00, ZFBL00, YBLB00, YBFPXM, SFOTC0, BZ0000, CKJG00, GXY000, TNB000, MXXS00, SFSX00, GXRQ00, CPID00, ZXJJDW, CFBL00, YBZGDJ, YBZGDJ1) select A.XMBH00, YPTYM0, YPMC00, YPJX00, YPJB00, YPJJDW, YPGG00, YPSCCJ, decode (SFYB00, '是', 'Y', 'Y', 'Y', 'N'), decode ( SFYB00, '否', 1, decode ( nvl (ZFBL00, '无'), '供养子女', 1, '离休', 1, '无', decode (nvl (YBLB00, ' '), '非医保', 1, 0), '非医保', 1, '甲类', 0, '乙类', 0, '无自付', 0, '自付100%', 1, '自付5%', 0.05, '自付10%', 0.1, '自付15%', 0.15, '自付20%', 0.2, '自付25%', 0.25, '自付30%', 0.3, '自付35%', 0.35, '自付40%', 0.4, '自付60%', 0.6, '100%自付', 1, '5%自付', 0.05, '10%自付', 0.1, '15%自付', 0.15, '20%自付', 0.2, '25%自付', 0.25, '30%自付', 0.3, '35%自付', 0.35, '40%自付', 0.4, '60%自付', 0.6, '100%', 1, '0', 0, '5%', 0.05, '10%', 0.1, '15%', 0.15, '20%', 0.2, '25%', 0.25, '30%', 0.3, '35%', 0.35, '40%', 0.4, ' 60%', 0.6, '1', 1, '0', 0, '0.05', 0.05, '0.1', 0.1, '0.15', 0.15, '0.2', 0.2, '0.25', 0.25, '0.3', 0.3, '0.35', 0.35, '0.4', 0.4, '0.6', 0.6, '自付4%', 0.04, decode ( sign (instrb (ZFBL00, '自付')), 1, replace (replace (ZFBL00, '自付', ''), '%', '') / 100, ZFBL00))), --decode(sfyb00,'否',7,decode(yblb00,'非医保',7,'甲类',4,'乙类',5,'医保','8',decode(SFYB00,'Y','8','是','8','7'))), case when ( B.BH0000 = '01' or B.BH0000 = '02' or B.BH0000 = '03') then decode ( SFYB00, '否', '7', decode ( YBLB00, '非医保', '7', '甲类', '4', '乙类', '5', '医保', '8', decode (SFYB00, 'Y', '8', '是', '8', '7'))) else decode ( SFYB00, '否', '2', decode ( YBLB00, '非医保', '2', '甲类', '0', '乙类', '1', '医保', '1', decode (SFYB00, 'Y', '1', '是', '1', '2'))) end as YBLB00, B.BH0000, SFOTC0, BZ0000, CKJG00, GXY000, TNB000, MXXS00, SFSX00, GXRQ00, A.CPID00, A.ZXJJDW, A.CFBL00, A.YBZGDJ, A.YBZGDJ1 from BM_YBYPML_TEMP09 A, BM_YBFPXM B where B.XMMC00 = A.YBFPXM and B.YBZXLB = AD_YBZXLB and B.FBBH00 = AD_FBBH00 and exists (select 1 from bm_ybsfxm where YBZXLB = AD_YBZXLB and FBBH00 = AD_FBBH00 and xmbh00=a.XMBH00); /* from BM_YBYPML_TEMP09 A, BM_YBFPXM B where B.XMMC00 = A.YBFPXM and B.YBZXLB = AD_YBZXLB and B.FBBH00 = AD_FBBH00 and A.XMBH00 in (select XMBH00 from BM_YBYPML_TEMP09 intersect select XMBH00 from BM_YBSFXM where YBZXLB = AD_YBZXLB and FBBH00 = AD_FBBH00);*/ commit; --2、备份表bm_ybsfxm数据 insert into BM_YBXMBF (YBZXLB, XMBH00, FLAG00, ZYFPXM, MZFPXM, XMMC00, YPJX00, XMDW00, ZFBL00, SFYX00, SFYBXM, YBLB00, PYSM00, WBSM00, GXXLH0, YBBRLB, SFSX00, SCCJ00, YPGG00, BZ0000, FBBH00, CZY000, CZRQ00, CZSJ00, ZYZFBL, CPID00, ZXJJDW, CFBL00, YBZGDJ, YBZGDJ1) select YBZXLB, XMBH00, FLAG00, ZYFPXM, MZFPXM, XMMC00, YPJX00, XMDW00, ZFBL00, SFYX00, SFYBXM, YBLB00, PYSM00, WBSM00, GXXLH0, YBBRLB, SFSX00, SCCJ00, YPGG00, BZ0000, FBBH00, AD_CZY000, LS_CZRQ00, LS_CZSJ00, ZYZFBL, CPID00, ZXJJDW, CFBL00, YBZGDJ, YBZGDJ1 from BM_YBSFXM A where YBZXLB = AD_YBZXLB and FBBH00 = AD_FBBH00; commit; --3、临时表bm_ybypml_temp09导入到BM_YBSFXM insert into BM_YBSFXM (YBZXLB, XMBH00, FLAG00, ZYFPXM, MZFPXM, XMMC00, YPJX00, XMDW00, ZFBL00, SFYX00, SFYBXM, YBLB00, PYSM00, WBSM00, GXXLH0, YBBRLB, SFSX00, YPGG00, BZ0000, SCCJ00, FBBH00, ZYZFBL, CJR000, CPID00, ZXJJDW, CFBL00, YBZGDJ, YBZGDJ1) select AD_YBZXLB, XMBH00, '1', YBFPXM, YBFPXM, nvl (YPTYM0, YPMC00), YPJX00, YPJJDW, ZFBL00, 'Y', SFYB00, YBLB00, substrb (SF_XT_GETPYSM (nvl (YPTYM0, YPMC00)), 1, 10), substrb (SF_XT_GETWBSM (nvl (YPTYM0, YPMC00)), 1, 10), 0, 'Z', SFSX00, YPGG00, BZ0000, YPSCCJ, AD_FBBH00, ZFBL00, AD_CZY000, CPID00, ZXJJDW, CFBL00, YBZGDJ, YBZGDJ1 from BM_YBYPML_TEMP07; SP_EXECUTE_SQL ('alter table bm_ybsfxm disable all triggers'); -- update bm_ybsfxm a set (zfbl00,yblb00,SFYBXM)= -- (select zfbl00,YBLB00,SFYB00 from bm_ybypml_temp07 b where b.xmbh00=trim(a.xmbh00)) -- where ybzxlb=ad_YBZXLB and FBBH00=ad_FBBH00 -- and exists (select 1 from bm_ybypml_temp07 b where b.xmbh00=trim(a.xmbh00)); --应该是处理交集而不是差集 同时该写法执行比较慢，主要是trim(a.xmbh00)慢 update BM_YBSFXM A set (ZFBL00, YBLB00, SFYBXM, BZ0000, ZYZFBL, XGR000, XGRQSJ, XMMC00, SCCJ00, YPGG00, YPJX00, ZYFPXM, MZFPXM, SFYX00, SFSX00, CPID00, ZXJJDW, CFBL00, YBZGDJ, YBZGDJ1) = (select ZFBL00, YBLB00, SFYB00, BZ0000, ZFBL00 ZYZFBL, AD_CZY000, LS_CZRQ00 || LS_CZSJ00, YPMC00, YPSCCJ, YPGG00, YPJX00, YBFPXM, YBFPXM, 'Y', SFSX00, CPID00, ZXJJDW, CFBL00, YBZGDJ, YBZGDJ1 from BM_YBYPML_TEMP10 B where B.XMBH00 = A.XMBH00) where YBZXLB = AD_YBZXLB and FBBH00 = AD_FBBH00 and exists (select 1 from BM_YBYPML_TEMP10 B where B.XMBH00 = A.XMBH00); commit; SP_EXECUTE_SQL ('alter table bm_ybsfxm enable all triggers'); --4、备份表bm_ybsfdy数据 insert into BM_YBDYBF (SFXMID, SFYP00, YBZXLB, YBXMBH, YBZYFP, YBMZFP, ZFBL00, SFYBXM, YBLB00, YBBRLB, FBBH00, YYDJ00, ZFJE00, JMBL00, JMJE00, SFSX00, BZ0000, YXBZ00, CZY000, CZRQ00, CZSJ00, ZYZFBL, ZYZFJE) select SFXMID, SFYP00, YBZXLB, YBXMBH, YBZYFP, YBMZFP, ZFBL00, SFYBXM, YBLB00, YBBRLB, FBBH00, YYDJ00, ZFJE00, JMBL00, JMJE00, SFSX00, BZ0000, YXBZ00, AD_CZY000, LS_CZRQ00, LS_CZSJ00, ZYZFBL, ZYZFJE from BM_YBSFDY A where YBZXLB = AD_YBZXLB and FBBH00 = AD_FBBH00; commit; --5、修改对应表的自付比例、医保类别、是否医保项目（以医保项目表的数据为准） --if ad_GXLX00='1' then SP_EXECUTE_SQL ('alter table bm_ybsfdy disable all triggers'); --end if; --5.1 修改对应表药品 /* --if ad_MLBH00 in ('1','2') then -- select count(1) into ls_COUNT0 from bm_ybypml_temp07 where length(trim(xmbh00))=20 and rownum=1; 应该是有更新的就做，而不是有新增时才做，而且不应该是全表bm_ybsfxm更新 select count(1) into ls_COUNT0 from bm_ybypml_temp09 where length(trim(xmbh00))=20 and rownum=1; if ls_COUNT0>0 then if ad_GXLX00='1' then update bm_ybsfdy a set ybxmbh=null,zfbl00='1',yblb00='7',sfybxm='N',ZYZFBL='1',XGR000=ad_CZY000,XGRQSJ=ls_CZRQ00||ls_CZSJ00 where length(trim(YBXMBH))=20 and ybzxlb=ad_YBZXLB and sfyp00='Y' and FBBH00=ad_FBBH00 and not exists (select 1 from bm_ybsfxm where FBBH00=a.FBBH00 and ybzxlb=a.YBZXLB and xmbh00=a.ybxmbh); end if; update bm_ybsfdy a set (zfbl00,yblb00,SFYBXM,BZ0000,ZYZFBL,XGR000,XGRQSJ)= (select zfbl00,yblb00,SFYBXM,BZ0000,ZYZFBL,ad_CZY000,ls_CZRQ00||ls_CZSJ00 from bm_ybsfxm b where b.xmbh00=a.ybxmbh and ybzxlb=a.ybzxlb and FBBH00=a.FBBH00) where length(trim(YBXMBH))=20 and ybzxlb=ad_YBZXLB and FBBH00=ad_FBBH00 and a.sfyp00='Y' -- and exists (select 1 from bm_ybsfxm where FBBH00=a.FBBH00 and ybzxlb=a.YBZXLB and xmbh00=a.ybxmbh); and exists (select 1 from bm_ybypml_temp09 where xmbh00=a.ybxmbh); end if; --end if; --5.2 修改对应表项目 --if ad_MLBH00 in ('1','3') then -- select count(1) into ls_COUNT0 from bm_ybypml_temp07 where length(trim(xmbh00))=12 and rownum=1; 应该是有更新的就做，而不是有新增时才做，而且不应该是全表bm_ybsfxm更新 select count(1) into ls_COUNT0 from bm_ybypml_temp09 where length(trim(xmbh00))=12 and rownum=1; if ls_COUNT0>0 then if ad_GXLX00='1' then update bm_ybsfdy a set ybxmbh=null,zfbl00='1',yblb00='7',sfybxm='N',ZYZFBL='1',XGR000=ad_CZY000,XGRQSJ=ls_CZRQ00||ls_CZSJ00 where length(trim(YBXMBH))=12 and ybzxlb=ad_YBZXLB and sfyp00='N' and FBBH00=ad_FBBH00 and not exists (select 1 from bm_ybsfxm where FBBH00=a.FBBH00 and ybzxlb=a.YBZXLB and xmbh00=a.ybxmbh); end if; update bm_ybsfdy a set (zfbl00,yblb00,SFYBXM,BZ0000,ZYZFBL,XGR000,XGRQSJ)= (select zfbl00,yblb00,SFYBXM,BZ0000,ZYZFBL,ad_CZY000,ls_CZRQ00||ls_CZSJ00 from bm_ybsfxm b where b.xmbh00=a.ybxmbh and ybzxlb=a.ybzxlb and FBBH00=a.FBBH00) where length(trim(YBXMBH))=12 and ybzxlb=ad_YBZXLB and FBBH00=ad_FBBH00 and a.sfyp00='N' -- and exists (select 1 from bm_ybsfxm where FBBH00=a.FBBH00 and ybzxlb=a.YBZXLB and xmbh00=a.ybxmbh); and exists (select 1 from bm_ybypml_temp09 where xmbh00=a.ybxmbh); end if; --end if; */ --5.3 修改对应表药品、项目 select count (1) into LS_COUNT0 from BM_YBYPML_TEMP09 where rownum = 1; if LS_COUNT0 > 0 then update BM_YBSFDY A set (ZFBL00, YBLB00, SFYBXM, BZ0000, ZYZFBL) = (select ZFBL00, YBLB00, SFYBXM, BZ0000, ZYZFBL from BM_YBSFXM B where B.XMBH00 = A.YBXMBH and YBZXLB = A.YBZXLB and FBBH00 = A.FBBH00) where YBZXLB = AD_YBZXLB and FBBH00 = AD_FBBH00 --and exists (select 1 from bm_ybypml_temp09 where xmbh00=a.ybxmbh and sfyp00=a.sfyp00); and (YBXMBH, SFYP00) in (select XMBH00, SFYP00 from BM_YBYPML_TEMP09); end if; commit; --if ad_GXLX00='1' then SP_EXECUTE_SQL ('alter table bm_ybsfdy enable all triggers'); --end if; select count (1) into AD_GXHJLS from BM_YBSFXM where YBZXLB = AD_YBZXLB and FBBH00 = AD_FBBH00; exception when no_data_found then AS_YHMSG0 := '数据没有找到，请核对您所输入的票据类别是否正确!'; AS_SYSMSG := sqlerrm; SP_EXECUTE_SQL ('alter table bm_ybsfdy enable all triggers'); rollback; when others then AS_YHMSG0 := '出错原因不详,请记录此信息并和系统管理员联系！'; AS_SYSMSG := substr ( sqlerrm || '执行存储过程错误:SP_XM_YBDR_XLS(' || AD_YBZXLB || ',' || AD_MLBH00 || ',' || AD_GXLX00 || ',as_YHMSG0,as_SYSMSG)', 1, 200); SP_EXECUTE_SQL ('alter table bm_ybsfdy enable all triggers'); rollback; end;