create or replace procedure sd_hospital.SP_SF_YBJK00_FJ0000 ( ad_BRID00 in number, --门诊病人ID as_GHH000 in char, --病人挂号号 as_YBMC00 in char, --医保中心名称 ad_JZDH00 in number, --结帐单号 ad_ID0000 in out number, --序列号ID as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar --系统提示的错误信息 ) -- MODIFICATION HISTORY -- Person Date Comments -- yangy 2009.04.13 新增参数SF_SFXYBFSJMJE 是否向医保发送减免金额 -- zhr 2009.08.18 当参数XT_YBJK_SFAYBXMMCFS为Y，生成的request.txt中的规格字段要用医保目录的规格，而不是院内的规格。 -- xuzw 2009.12.08 限制异地医保病人不能进行预结算（MZSF-20091125-002） -- zhangyc 2011.09.09 SF_YDYBSFKYYJS=N 原用(bm_brxxb0.ybkh00 like '%@%')来判断;现改为用（bm_brxxb0.ybkh00 like '%@%'）来判断 by MZSF-20110603-002 -- zhangyc 2012.12.27 增加发送YPTS00:用药天数 YPPL00:频率代码 YPYL00:剂量+单位 by MZSF-20121227-002 -- jinfl 2013.01.25 增加发送医生证件号码 MZSF-20130125-001 -- zhangyc 2013.04.27 修正部分药品产生接口报错 by MZSF-20130427-002 -- zhangyc 2015.09.18 修正药品用量,用药天数,用药频率医师证件号 by MZSF-20150907-004 -- zhangyc 2017.05.18 增加参数SF_SFQYYBXJJK控制医保单位发送功能 by MZSF-20170518-001 -- zhangyc 2017.05.24 修正数量为0bug by MZSF-20170524-002 -- jlg 2017.07.25 重整视图，处理参数ZY_YBFPBH的问题 MZSF-20170720-001 --zhangyc 2017.12.25 增加中间表CZRQ00处理 by MZSF-20171222-001 -- dsm 2018.11.27 医保限价，限单位后，存在四舍五入后DJ*SL<>院内金额的，改变数量 MZSF-20181010-003 -- zhangyc 2019.09.27 修正游标药品先汇总后在发送 by MZSF-20190927-003 -- qiulf 2019.12.31 中间表BM_ZYCFMX_TEMP00写入FPXMBH by MZSF-20191231-005 -- zhangyc 2020.01.13 变量ls_YPYL00去掉ls_CSL000直接取ls_JLDW00 by MZSF-20200113-001 as ls_YBBRLB BM_BRXXB0.YBBRLB%type; --医保病人类别 ls_YBLB00 BM_BRXXB0.YBLB00%type; --医保类别 ls_FBBH00 BM_BRXXB0.FBBH00%type; --病人费别 ls_SFMLXZ BM_YBBRLB.SFMLXZ%type; --是否受目录限制,0:不受目录限制,1:受目录限制 ls_YBZXLB BM_YBFPXM.YBZXLB%type; --医保中心类别编码 ls_FPXMMC BM_YBFPXM.XMMC00%type; --发票项目名称 ls_YBXMMC BM_YBSFXM.XMMC00%type; --医保收费/药品项目名称 ls_FPXMBH BM_YBFPXM.BH0000%type; --医保发票项目编号 ls_YBXMBH BM_YBSFXM.XMBH00%type; --医保收费/药品项目编号 ls_YBXMSX BM_YBSFXM.SFSX00%type; --医保项目收费上限 ls_YBXMGG BM_YBSFXM.YPGG00%type; --医保项目规格 ls_CBXMBH BM_YBFPXM.CBXMBH%type; --超标项目编号 ls_CBXMMC BM_YBSFXM.XMMC00%type; --超标项目名称 ls_CBFPBH BM_YBFPXM.BH0000%type; --超标医保发票项目编号 ls_CBFPMC BM_YBFPXM.XMMC00%type; --超标发票项目名称 ls_SFYBXM BM_YBSFDY.SFYBXM%type; --是否医保项目 ls_YBXMLB BM_YBSFDY.YBLB00%type; --医保项目类别 ls_ZWXM00 BM_YGBM00.ZWXM00%type; --医生的中文姓名 ls_ID0000 BM_SFCF00_TEMP00.ID0000%type; ls_MXID00 SF_FYMX00.MXID00%type; --病人费用明细ID ls_YBZFBL SF_FYMX00.ZFBL00%type; --医保自付比例 ls_XMBH00 SF_FYMX00.XMBH00%type; --费用项目编号 ls_XMMC00 SF_FYMX00.XMMC00%type; --费用项目名称 ls_HJJE00 SF_FYMX00.HJJE00%type; --费用合计金额 ls_ZFJE00 SF_FYMX00.ZFJE00%type; --费用合计金额 ls_ZFBL00 SF_FYMX00.ZFBL00%type; --自付比例 ls_XMDJ00 SF_FYMX00.XMDJ00%type; --项目单价 ls_GG0000 SF_FYMX00.XMGG00%type; --项目规格 ls_DW0000 SF_FYMX00.XMDW00%type; --项目单位 ls_XMSL00 SF_FYMX00.XMSL00%type; --项目数量 ls_YBBHQZ varchar2(20); --医保编码前缀 ls_YBMCFS char(1); --是否按医保项目名称发送医保接口 ls_COUNT0 number(4); ls_SYFPBH char(1); ls_DJSYXJ char(1); ls_SJXMDJ SF_FYMX00.XMDJ00%type; --费用项目实际单价 ls_CGSXSFCF char(1); --收费项目的金额超过上限是否进行拆分 ls_SF_YBCGS char(1); --门诊收费医保超过上限是否按原项目拆分 ls_FLAG00 char(1); --标志 '0'费用,'1'零散处方,'2'医嘱处方 ls_YYID00 XT_YYXX00.YYID00%type; ls_FSJMJE char(1); --是否向医保发送减免金额 ls_YDYBSFKYYJS char(1); --异地医保是否可以预结算 E_YJJXZ exception; ls_YPTS00 number(5); --用药天数 ls_PCMC00 varchar2(20); --频次名称 ls_YPPL00 varchar2(6); --频率代码 ls_YPYL00 varchar2(20); --剂量+单位 ls_CSL000 number(14,4); --剂量 ls_JLDW00 BM_YYSFXM.DW0000%type; --剂量单位 LS_YSZJHM BM_SFCFMX_TEMP00.YSZJHM%type; LS_CZRQ00 BM_SFCFMX_TEMP00.CZRQ00%type; --操作日期 LS_YSZJHM_TEMP varchar2(50); ls_num number(3); ls_len number(3); ls_temp varchar2(6); LS_YBXJQY varchar2(100); --门诊或住院系统是否启用医保限价接口 Y:启用 N:不启用 默认值N:不启用 LS_K2J000 BM_YD0000.K2J000%type; --库存单位转化率 LS_M2J000 BM_YD0000.M2J000%type; --门诊发药单位转化率 LS_Z2J000 BM_YD0000.Z2J000%type; --住院发药单位转化率 LS_YBDW00 BM_YD0000.YBDW00%type; --医保单位 LS_YB2J00 BM_YD0000.YB2J00%type; --医保转换率 LS_YBDDW0 BM_YBSFXM.XMDW00%type; --医保项目单位（大单位） LS_YBXDW0 BM_YBSFXM.XMDW01%type; --医保项目单位（小单位） LS_YBJSDJ BM_YBSFXM.YBJSDJ%type; --医保结算单价 LS_YBZGDJ BM_YBSFXM.YBZGDJ%type; --医保最高单价 cursor CUR_SF_FYMX00_MXID00 IS select '0'FLAG00,a.MXID00,decode(a.JMBZ00,'4',a.ZFJE00,a.ZFJE00-a.JMJE00) ZFJE00,substrb(c.XMMC00,1,50) XMMC00, nvl(c.GG0000,'无')GG0000,nvl(c.DW0000,'无')DW0000,a.XMBH00,trim(substr(e.ZWXM00,1,4)) ZWXM00, decode(a.JMBZ00,'4',decode(ls_FSJMJE,'Y',(a.HJJE00+a.JMJE00),(a.HJJE00-a.JMJE00)),(a.HJJE00-a.JMJE00))/decode(a.XMSL00,0,1,a.XMSL00) XMDJ00,a.XMSL00,decode(a.JMBZ00,'4',decode(ls_FSJMJE,'Y',(a.HJJE00+a.JMJE00),(a.HJJE00-a.JMJE00)),(a.HJJE00-a.JMJE00)) HJJE00, a.ZFBL00, 1 as YPTS00,--用药天数 '' as PCMC00,--频次名称 a.XMSL00 as CSL000, --剂量 c.DW0000 as JLDW00,--剂量单位 e.YSZJHM, --医生证件编号 null as K2J000,--库存单位转化率 null as M2J000,--门诊发药单位转化率 null as Z2J000,--住院发药单位转化率 null as YBDW00,--医保单位 null as YB2J00, --医保转换率 A.CZRQ00 as CZRQ00--费用日期 from SF_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,SF_BRFY00 D,BM_YGBM00 E where D.BRID00=ad_BRID00 and JZDH00=ad_JZDH00 and (d.MZH000 = as_GHH000 or as_GHH000='0') and a.XMBH00 = C.SFXMID and C.MZFPID = B.FPXMID and D.DJH000=a.DJH000 and a.KDYS00=e.YGBH00 and B.SFYLXM='Y' and a.XMBH00<9999999900 --将西药,草药,成药,材料的去掉(以及将专门减免的也去掉9999999901~9999999990 jlg) and a.JMBZ00<>'2' --如果JMBZ00='4'(事前减免),则发送医保时要把这部分减免的金额也发送给医保 for MZSF-20070119-006 union all select '1'FLAG00,max(a.CFID00)CFID00,sum(round(a.LSDJ00*a.YPZSL0*100)/100) as ZFJE,substrb(e.YPMC00,1,50)YPMC00, nvl(e.YPGG00,'无')YPGG00,nvl(a.CFDW00,'无')CFDW00,a.YPNM00,trim(substr(c.YSXM00,1,4)) YSXM00,a.LSDJ00, sum(a.YPZSL0)YPZSL0, sum(round(a.LSDJ00*a.YPZSL0*100)/100) YPHJJE, max(a.ZFBL00) as ZFBL00, max(a.YYSJ00) as YPTS00,--用药天数 max(a.PCMC00) as PCMC00,--频次名称 max(a.CSL000) as CSL000, --剂量 max(a.JLDW00) as JLDW00,--剂量单位 f.YSZJHM, --医生证件编号 e.K2J000 as K2J000,--库存单位转化率 e.M2J000 as M2J000,--门诊发药单位转化率 e.Z2J000 as Z2J000,--住院发药单位转化率 e.YBDW00 as YBDW00,--医保单位 e.YB2J00 as YB2J00, --医保转换率 max(c.SRRQ00) as CZRQ00 --费用日期 from YF_MZCFMX A,YF_MZCF00 c,SF_BRFY00 d,BM_YD0000 e,bm_ygbm00 f where d.BRID00=ad_BRID00 and d.JZDH00=ad_JZDH00 and (d.MZH000 = as_GHH000 or as_GHH000='0')and a.CFLSH0=c.CFLSH0 and c.DJH000=d.DJH000 and a.YPNM00=e.YPNM00 and a.SFZBY0='N' and c.ysgzh0=f.ygbh00 group by e.YPMC00,e.YPGG00,a.CFDW00,a.YPNM00,c.YSXM00,a.LSDJ00, f.YSZJHM,e.K2J000,e.M2J000,e.Z2J000, e.YBDW00,e.YB2J00 ; /* select '1'FLAG00,a.CFID00,round(a.LSDJ00*a.YPZSL0*100)/100,substrb(e.YPMC00,1,50)YPMC00, nvl(e.YPGG00,'无')YPGG00,nvl(a.CFDW00,'无')CFDW00,a.YPNM00,trim(substr(c.YSXM00,1,4)) YSXM00,a.LSDJ00,a.YPZSL0, round(a.LSDJ00*a.YPZSL0*100)/100 YPHJJE, a.ZFBL00, a.YYSJ00 as YPTS00,--用药天数 a.PCMC00 as PCMC00,--频次名称 a.CSL000 as CSL000, --剂量 a.JLDW00 as JLDW00,--剂量单位 f.YSZJHM, --医生证件编号 e.K2J000 as K2J000,--库存单位转化率 e.M2J000 as M2J000,--门诊发药单位转化率 e.Z2J000 as Z2J000,--住院发药单位转化率 e.YBDW00 as YBDW00,--医保单位 e.YB2J00 as YB2J00, --医保转换率 c.SRRQ00 as CZRQ00 --费用日期 from YF_MZCFMX A,YF_MZCF00 c,SF_BRFY00 d,BM_YD0000 e,bm_ygbm00 f where d.BRID00=ad_BRID00 and d.JZDH00=ad_JZDH00 and (d.MZH000 = as_GHH000 or as_GHH000='0')and a.CFLSH0=c.CFLSH0 and c.DJH000=d.DJH000 and a.YPNM00=e.YPNM00 and a.SFZBY0='N' and c.ysgzh0=f.ygbh00;--是否自备药 */ cursor CUR_SF_FYMX00_MXID00_LNLF IS --老年医院两费病人专用 select '0'FLAG00,a.MXID00,decode(a.JMBZ00,'4',a.ZFJE00,a.ZFJE00-a.JMJE00) ZFJE00,substrb(c.XMMC00,1,50) XMMC00, nvl(c.GG0000,'无')GG0000,nvl(c.DW0000,'无')DW0000,a.XMBH00,e.ZWXM00, decode(a.JMBZ00,'4',(a.HJJE00+a.JMJE00),(a.HJJE00-a.JMJE00))/decode(a.XMSL00,0,1,a.XMSL00) XMDJ00,a.XMSL00,decode(a.JMBZ00,'4',(a.HJJE00+a.JMJE00),(a.HJJE00-a.JMJE00)) HJJE00, a.ZFBL00, 1 as YPTS00,--用药天数 '' as PCMC00,--频次名称 a.XMSL00 as CSL000, --剂量 c.DW0000 as JLDW00,--剂量单位 e.YSZJHM, --医生证件编号 null as K2J000,--库存单位转化率 null as M2J000,--门诊发药单位转化率 null as Z2J000,--住院发药单位转化率 null as YBDW00,--医保单位 null as YB2J00, --医保转换率 A.CZRQ00 as CZRQ00--费用日期 from SF_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,SF_BRFY00 D,BM_YGBM00 E where D.BRID00=ad_BRID00 and JZDH00=ad_JZDH00 and (d.MZH000 = as_GHH000 or as_GHH000='0') and a.XMBH00 = C.SFXMID and C.MZFPID = B.FPXMID and D.DJH000=a.DJH000 and a.KDYS00=e.YGBH00 and B.SFYLXM='Y' and a.XMBH00<9999999900 --将西药,草药,成药,材料的去掉(以及将专门减免的也去掉9999999901~9999999990 jlg) and A.XMBH00 <> 20006 --老年医院两费病人不传挂号费给医保 added by jlg for MZSF-20070111-001 and a.JMBZ00<>'2' --如果JMBZ00='4'(事前减免),则发送医保时要把这部分减免的金额也发送给医保 for MZSF-20070119-006 union all select '1'FLAG00,a.CFID00,round(a.LSDJ00*a.YPZSL0*100)/100,substrb(e.YPMC00,1,50)YPMC00, nvl(e.YPGG00,'无')YPGG00,nvl(a.CFDW00,'无')CFDW00,a.YPNM00,c.YSXM00,a.LSDJ00,a.YPZSL0, round(a.LSDJ00*a.YPZSL0*100)/100 YPHJJE, a.ZFBL00, a.YYSJ00 as YPTS00,--用药天数 a.PCMC00 as PCMC00,--频次名称 a.CSL000 as CSL000, --剂量 a.JLDW00 as JLDW00,--剂量单位 f.YSZJHM, --医生证件编号 e.K2J000 as K2J000,--库存单位转化率 e.M2J000 as M2J000,--门诊发药单位转化率 e.Z2J000 as Z2J000,--住院发药单位转化率 e.YBDW00 as YBDW00,--医保单位 e.YB2J00 as YB2J00, --医保转换率 c.SRRQ00 as CZRQ00 --费用日期 from YF_MZCFMX A,YF_MZCF00 c,SF_BRFY00 d,BM_YD0000 e,bm_ygbm00 f where d.BRID00=ad_BRID00 and d.JZDH00=ad_JZDH00 and (d.MZH000 = as_GHH000 or as_GHH000='0')and a.CFLSH0=c.CFLSH0 and c.DJH000=d.DJH000 and a.YPNM00=e.YPNM00 and a.SFZBY0='N' and c.ysgzh0=f.ygbh00;--是否自备药 begin /* 医保接口说明: 未对应为项目或超标项目的的处理办法:如果一个收费项目或药品未对应医保项目，那么有两种处理办法 一、如果BM_YBFPXM表有定义该医保发票项目的未对应或超标的项目医保接口使用的医保项目编号，那么医保接口使用此项目编号 二‘ 否则沿用以前的办法 采用'990000000000+医保发票编号方式，前缀由表BM_YBBRLB定义，如果没有定义默认前缀为990000000000 接口是否按医保目录的项目名称发送由参数XT_YBJK_SFAYBXMMCFS控制， 但有特例，铁路医保要求发院内项目名称,对应指定特殊医保项目的，也要发院内名称 接口文件是否使用医保发票编号,还是使用医保发票名称来传送 由参数ZY_YBFPBH控制 收费项目的金额超过上限是否进行拆分，由参数ZY_SFXMCGSXSFCF控制 医保住院是否按上限结算 由参数ZY_SFASXJS控制 所有医保接口都调用本过程，注意南平医保日清单发送接口 更正以前当SFYBXM为N就按未对应医保项目处理的办法 */ begin select upper(trim(VALUE0)) into ls_FSJMJE from XT_XTCS00 where NAME00 = 'SF_SFXYBFSJMJE'; exception when others then ls_FSJMJE:='Y'; end; begin select upper(trim(VALUE0)) into ls_YDYBSFKYYJS from XT_XTCS00 where NAME00 = 'SF_YDYBSFKYYJS'; --异地医保是否可以预结算，默认为Y exception when others then ls_YDYBSFKYYJS:='Y'; end; --MZSF-20091102-001 异地医保病人默认不能预结算。 --MZSF-20091125-002 限制异地医保病人(bm_brxxb0.ybkh00 like '%@%')不能预结算，提示‘异地医保病人不允许预结算！’ 且增加参数SF_YDYBSFKYYJS为N时提示 --MZSF-20110603-002 原因是本院异地医保卡是换卡操作，所有IC_YHXX00.ICKH00都是自费卡号不带@， --这个需求过滤这个字段是无效的。可以通过过滤bm_brxxb0.ybkh00字段中带@的病人来过滤或者其他字段，不能让这些病人操作预结算 begin --modify by MZSF-20110603-002 改为用bm_brxxb0.ybkh00 来判断 --select count(*) into ls_COUNT0 from IC_YHXX00 where brid00=ad_BRID00 and ickh00 like '%@%' and zt0000='1'; select count(*) into ls_COUNT0 from bm_brxxb0 where brid00=ad_BRID00 and YBKH00 like '%@%'; end; if (ls_COUNT0>0) and (ad_JZDH00=0) and (ls_YDYBSFKYYJS='N') then --是异地医保病人且是预结算 参数ls_YDYBSFKYYJS为N RAISE E_YJJXZ; end if; begin select YYID00 into ls_YYID00 from XT_YYXX00 WHERE ROWNUM=1; exception when others then ls_YYID00:='0'; end; begin select YBBRLB,FBBH00,YBLB00 into ls_YBBRLB,ls_FBBH00,ls_YBLB00 from BM_BRXXB0 where BRID00 = ad_BRID00; exception when others then ls_YBBRLB:='0'; end; --取医保中心类别 begin select YBZXLB into ls_YBZXLB from IC_YBBRLB where (FBBH00,YBLB00)=(select FBBH00,YBLB00 from BM_BRXXB0 where brid00=ad_BRID00); exception when no_data_found then as_YHMSG0:='系统字典数据不完整,病人的医保中心名称不存在,请与系统管理员联系!'; as_SYSMSG:=SQLERRM||'select * from ic_ybbrlb where ybmc00='||nvl(as_YBMC00,'空的'); rollback; return; end; --泉州医保接口要求单价*数量必须等于金额 begin select VALUE0 into ls_DJSYXJ from XT_XTCS00 where NAME00 = 'SF_YBDJSLJE'; --医保单价*数量与金额是否要求严格相等 exception when others then ls_DJSYXJ:='N'; end; --收费项目的金额超过上限是否进行拆分 begin select upper(trim(VALUE0)) into ls_CGSXSFCF from XT_XTCS00 where NAME00 = 'SF_SFXMCGSXSFCF'; --收费项目的金额超过上限是否进行拆分 exception when others then ls_CGSXSFCF:='Y'; end; --门诊收费医保超过上限是否按原项目拆分,暂停使用此参数 begin select upper(trim(VALUE0)) into ls_SF_YBCGS from XT_XTCS00 WHERE NAME00='SF_YBCGSXSFYYXMCF'; --门诊收费医保超过上限是否按原项目拆分 exception when no_data_found then ls_SF_YBCGS:='N'; end; --接口文件是否使用医保发票编号,还是使用医保发票名称来传送 begin select upper(substrb(trim(VALUE0),1,1)) into ls_SYFPBH from XT_XTCS00 where NAME00 = 'ZY_YBFPBH'; --是否使用医保发票编号 exception when others then ls_SYFPBH:='N'; end; --接口是否按医保目录的项目名称发送 begin select upper(substrb(value0,1,1)) into ls_YBMCFS from xt_xtcs00 where name00='XT_YBJK_SFAYBXMMCFS'; exception when others then ls_YBMCFS:='N'; end; --医保编号前缀编码,是否目录限制 begin select FBMGZ0,SFMLXZ into ls_YBBHQZ,ls_SFMLXZ from BM_YBBRLB where YBZXLB = ls_YBZXLB and YBBRLB = ls_YBBRLB; exception when no_data_found then ls_YBBHQZ:='990000000000'; --非医保项目的编码前缀 end; --SF_SFQYYBXJJK 门诊或住院系统是否启用医保限价接口 Y:启用 N:不启用 默认值N:不启用 begin select SF_SF_YBXJQY(ad_BRID00,0,'0') into LS_YBXJQY from dual; --返回值 Y:启用 N:不启用 exception when others then LS_YBXJQY:='N'; end; select SF_SF_GetYYKSBM(ad_BRID00,as_GHH000,1)YSZJHM into LS_YSZJHM_TEMP from dual; select SQ_SF_TEMP00_ID0000.nextval into ls_ID0000 from dual; if nvl(ad_id0000,0)>0 then --前台录入的待记帐的费用 update BM_SFCFMX_TEMP00 set id0000 = ls_ID0000 where id0000 = ad_id0000; update BM_SFCF00_TEMP00 set ID0000 = ls_ID0000 where ID0000 = ad_id0000; --added by jlg for MZSF-20070514-001 end if; select SQ_SF_TEMP00_ID0000.nextval into ad_ID0000 from dual; if (ls_YYID00='220035') and (ls_YBLB00='3') then open CUR_SF_FYMX00_MXID00_LNLF; else open CUR_SF_FYMX00_MXID00; end if; loop begin if (ls_YYID00='220035') AND (ls_YBLB00='3') then fetch CUR_SF_FYMX00_MXID00_LNLF into ls_FLAG00,ls_MXID00,ls_ZFJE00,ls_XMMC00,ls_GG0000,ls_DW0000, ls_XMBH00,ls_ZWXM00,ls_XMDJ00,ls_XMSL00,ls_HJJE00,ls_ZFBL00,ls_YPTS00,ls_PCMC00,ls_CSL000,ls_JLDW00,LS_YSZJHM, LS_K2J000,--库存单位转化率 LS_M2J000,--门诊发药单位转化率 LS_Z2J000,--住院发药单位转化率 LS_YBDW00,--医保单位 LS_YB2J00, --医保转换率 LS_CZRQ00 --费用日期 ; exit when CUR_SF_FYMX00_MXID00_LNLF%notfound; else fetch CUR_SF_FYMX00_MXID00 into ls_FLAG00,ls_MXID00,ls_ZFJE00,ls_XMMC00,ls_GG0000,ls_DW0000, ls_XMBH00,ls_ZWXM00,ls_XMDJ00,ls_XMSL00,ls_HJJE00,ls_ZFBL00,ls_YPTS00,ls_PCMC00,ls_CSL000,ls_JLDW00,LS_YSZJHM, LS_K2J000,--库存单位转化率 LS_M2J000,--门诊发药单位转化率 LS_Z2J000,--住院发药单位转化率 LS_YBDW00,--医保单位 LS_YB2J00, --医保转换率 LS_CZRQ00 --费用日期 ; exit when CUR_SF_FYMX00_MXID00%notfound; end if; select a.XMMC00,a.BH0000,C.SFYBXM,nvl(C.SFSX00,0),D.XMMC00,C.YBXMBH,a.CBXMBH,C.ZFBL00,c.YBLB00,d.YPGG00,D.XMDW00,D.XMDW01,D.YBJSDJ,D.YBZGDJ into ls_FPXMMC,ls_FPXMBH,ls_SFYBXM,ls_YBXMSX,ls_YBXMMC,ls_YBXMBH,ls_CBXMBH,ls_YBZFBL,ls_YBXMLB,ls_YBXMGG,LS_YBDDW0,LS_YBXDW0,LS_YBJSDJ,LS_YBZGDJ from BM_YBFPXM A,VW_BM_YBSFDY C,BM_YBSFXM D where a.BH0000 = C.YBMZFP and a.YBZXLB = C.YBZXLB and C.YBBRLB = ls_YBBRLB and C.SFXMID = ls_XMBH00 and C.YBZXLB = D.YBZXLB(+) and C.YBXMBH = D.XMBH00(+) and C.SFYP00 = decode(ls_FLAG00,'0','N','Y')--是否药品,'0'费用,'1'零散处方,'2'医嘱处方 and a.YBZXLB = ls_YBZXLB and a.SYBZ00 <> '2' ; --非住院 --如果没有对应,查找BM_YBSFXM本发票记录有没有定义未对应或超标的项目医保接口使用的医保项目编号 --如果有定义,使用此定义的医保项目来生成接口文件,如果没有定义,使用'990000000000+医保发票编号方式 if ls_CBXMBH is not null then select a.XMMC00,a.MZFPXM,b.XMMC00 into ls_CBXMMC,ls_CBFPBH,ls_CBFPMC from BM_YBSFXM a,BM_YBFPXM b where a.YBZXLB=ls_YBZXLB and a.XMBH00=ls_CBXMBH and a.MZFPXM=b.BH0000 and b.YBZXLB=ls_YBZXLB; else ls_CBXMMC:=ls_XMMC00; ls_CBXMBH:=ls_YBBHQZ||ls_FPXMBH; ls_CBFPBH:=ls_FPXMBH; ls_CBFPMC:=ls_FPXMMC; end if; if ls_YBXMBH is null then ls_YBXMMC:=ls_CBXMMC; ls_YBXMBH:=ls_CBXMBH; ls_FPXMMC:=ls_CBFPMC; ls_FPXMBH:=ls_CBFPBH; end if; --药品医保项目单位转化 if (LS_YBXJQY='Y') and (LS_FLAG00 in ('1')) then if (trim(LS_DW0000)=trim(LS_YBDDW0)) or (trim(LS_DW0000)=trim(LS_YBXDW0)) then --发药=医保大单位或小单位 LS_DW0000:=LS_DW0000; else if (trim(LS_YBDW00) is not null) and (nvl(LS_YB2J00,0)>0) and (nvl(LS_M2J000,0)>0) and (nvl(LS_XMSL00,0)<>0) then if trim(LS_DW0000)=trim(LS_YBDW00) then LS_DW0000:=LS_YBDW00; else --转成计量单位 LS_DW0000:=LS_YBDW00; LS_HJJE00:=LS_HJJE00; LS_XMSL00:=round((LS_XMSL00*LS_M2J000)/LS_YB2J00,4); LS_XMDJ00:=round(LS_HJJE00/LS_XMSL00,4); if LS_XMDJ00*LS_XMSL00<>LS_HJJE00 then --MZSF-20181010-003 LS_XMSL00:=round(LS_HJJE00/LS_XMDJ00,4); end if; end if; end if; end if; end if; if ls_FLAG00='1' then begin select YBPCBM into ls_YPPL00 from BM_PC0000 aa where aa.PCMC00=ls_PCMC00 and rownum=1; exception when others then ls_YPPL00:=''; end; ls_YPYL00:=substrb(trim(ls_JLDW00),1,12); /* if instr(to_char(ls_CSL000),'.')>0 then select instr(to_char(ls_CSL000),'.') into ls_num from dual; Select length(to_char(ls_CSL000)) into ls_len from dual; begin select substr(to_char(ls_CSL000),ls_num+1,ls_len-ls_num) into ls_temp from dual; exception when others then ls_temp:='0'; end; Select length(ls_temp) into ls_len from dual; if ls_len=0 then ls_YPYL00:=substrb(trim(ls_CSL000||ls_JLDW00),1,20); end if; if ls_len=1 then ls_YPYL00:=substrb(trim(to_char(ls_CSL000,'9999990.0')||ls_JLDW00),1,20); end if; if ls_len=2 then ls_YPYL00:=substrb(trim(to_char(ls_CSL000,'9999990.00')||ls_JLDW00),1,20); end if; if ls_len=3 then ls_YPYL00:=substrb(trim(to_char(ls_CSL000,'9999990.000')||ls_JLDW00),1,20); end if; if ls_len=4 then ls_YPYL00:=substrb(trim(to_char(ls_CSL000,'9999990.0000')||ls_JLDW00),1,20); end if; else ls_YPYL00:=substrb(trim(ls_CSL000||ls_JLDW00),1,20); end if; */ else ls_YPPL00:=''; --ls_YPYL00:=substrb(trim(ls_CSL000||ls_JLDW00),1,20); ls_YPYL00:=substrb(trim(ls_JLDW00),1,12); if (ls_YPTS00=0) or (ls_YPTS00 is null) then ls_YPTS00:=1 ; end if; end if; --特殊处理，如果对应的是以下几个医保项目,需要传递院内项目名称 if ls_YBXMBH in ('01670000000001','01670000000002','01670000000005','01670000000006','01670000000007','01670000000008') then ls_YBXMMC:=ls_XMMC00; end if; update BM_SFCF00_TEMP00 set VALUE0 = NVL(VALUE0,0) + ls_HJJE00 where ID0000 = ls_ID0000 and NAME00 = ls_FPXMMC and BZ0000 = ls_SFYBXM; if sql%notfound then insert into BM_SFCF00_TEMP00(ID0000,NAME00,VALUE0,BZ0000) values(ls_ID0000,ls_FPXMMC,ls_HJJE00,ls_SFYBXM); end if; --如果原来的自付比例错误,要进行修改,保证清单的正确 zhr 2005.02.21 if ls_ZFBL00<>ls_YBZFBL then if ls_FLAG00='0' then Update SF_FYMX00 set ZFBL00=ls_YBZFBL where MXID00=ls_MXID00; elsif ls_FLAG00='0' then Update YF_MZCFMX set ZFBL00=ls_YBZFBL,YBLB00=ls_YBXMLB where CFID00=ls_MXID00; end if; end if; --判断实际单价是否超过医保上线,如果是要拆分成两条记录 if (ls_XMDJ00>ls_YBXMSX) and (ls_YBXMSX>0) and (ls_SFYBXM='Y') and (ls_CGSXSFCF='Y') and (ls_YBXMBH is not null) then --超过上线 拆分成两条记录 insert into BM_SFCFMX_TEMP00(ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00,YSXM00,YPTS00,YPPL00,YPYL00,YSZJHM,CZRQ00,FPXMBH) values(ls_ID0000,ls_YBXMBH,decode(ls_SFMLXZ,0,'Y',ls_SFYBXM),decode(trim(ls_yblb00),'Z',ls_FPXMMC,decode(ls_SYFPBH,'Y',ls_FPXMBH,ls_FPXMMC)), decode(trim(ls_yblb00),'Z',ls_XMMC00,decode(ls_YBMCFS,'Y',ls_YBXMMC,ls_XMMC00)), decode(ls_YBMCFS,'Y',ls_YBXMGG,ls_GG0000),ls_DW0000,ls_YBXMSX,ls_XMSL00,round(ls_YBXMSX*ls_XMSL00,2),ls_ZWXM00,ls_YPTS00,ls_YPPL00,ls_YPYL00,LS_YSZJHM,LS_CZRQ00,ls_FPXMBH); insert into BM_SFCFMX_TEMP00(ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00,YSXM00,YPTS00,YPPL00,YPYL00,YSZJHM,CZRQ00,FPXMBH) values(ls_ID0000,ls_CBXMBH,'N',decode(trim(ls_yblb00),'Z',ls_CBFPMC,decode(ls_SYFPBH,'Y',ls_CBFPBH,ls_CBFPMC)),ls_CBXMMC, ls_GG0000,ls_DW0000,(ls_XMDJ00-ls_YBXMSX),ls_XMSL00,round((ls_XMDJ00-ls_YBXMSX)*ls_XMSL00,2),ls_ZWXM00,ls_YPTS00,ls_YPPL00,ls_YPYL00,LS_YSZJHM,LS_CZRQ00,ls_FPXMBH); else insert into BM_SFCFMX_TEMP00(ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00,YSXM00,YPTS00,YPPL00,YPYL00,YSZJHM,CZRQ00,FPXMBH) values(ls_ID0000,ls_YBXMBH,decode(ls_SFMLXZ,0,'Y',ls_SFYBXM),decode(trim(ls_yblb00),'Z',ls_FPXMMC,decode(ls_SYFPBH,'Y',ls_FPXMBH,ls_FPXMMC)), decode(trim(ls_yblb00),'Z',ls_XMMC00,decode(ls_YBMCFS,'Y',ls_YBXMMC,ls_XMMC00)), decode(ls_YBMCFS,'Y',ls_YBXMGG,ls_GG0000),ls_DW0000,ls_XMDJ00,ls_XMSL00,round(ls_XMDJ00*ls_XMSL00,2),ls_ZWXM00,ls_YPTS00,ls_YPPL00,ls_YPYL00,LS_YSZJHM,LS_CZRQ00,ls_FPXMBH); end if; exception when no_data_found then as_YHMSG0:='查找不到医保对应记录,请与系统管理员联系!'; if ls_FLAG00='0' then as_SYSMSG:=SQLERRM||'SFXMID='||to_char(ls_XMBH00)||',MXID00='||to_char(ls_MXID00)||'查找不到医保对应记录!'; else as_SYSMSG:=SQLERRM||'YPNM00='||to_char(ls_XMBH00)||',CFID00='||to_char(ls_MXID00)||'查找不到医保对应记录!'; end if; rollback; return; when others then --说明是药品,注意有多条记录 as_YHMSG0:='医疗收费项目生成错误,请速与系统管理员联系!'; if ls_FLAG00='0' then as_SYSMSG:=SQLERRM||'SFXMID='||to_char(ls_XMBH00)||',MXID00='||to_char(ls_MXID00)||'查找不到医保对应记录!'; else as_SYSMSG:=SQLERRM||'YPNM00='||to_char(ls_XMBH00)||',CFID00='||to_char(ls_MXID00)||'查找不到医保对应记录!'; end if; rollback; return; end; end loop; if (ls_YYID00='220035') AND (ls_YBLB00='3') then close CUR_SF_FYMX00_MXID00_LNLF; else close CUR_SF_FYMX00_MXID00; end if; --进行汇总 insert into BM_SFCFMX_TEMP00(ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00, YSXM00,YPTS00,YPPL00,YPYL00,YSZJHM,CZRQ00,FPXMBH) select ad_ID0000,XMBH00,SFYB00,FPXMMC,max(XMMC00),XMGG00,max(XMDW00),XMDJ00,nvl(sum(XMSL00),0),NVL(sum(XMJE00),0), max(YSXM00),max(YPTS00),max(YPPL00),max(YPYL00),max(YSZJHM),max(CZRQ00),FPXMBH from BM_SFCFMX_TEMP00 where ID0000 = ls_ID0000 group by XMBH00,SFYB00,FPXMMC,XMGG00,XMDJ00,FPXMBH; if ls_DJSYXJ = 'Y' then --由于泉州医保要求单价*数量与金额要严格相同,所以本处添加了处理 update BM_SFCFMX_TEMP00 set XMDJ00 = XMJE00,XMSL00 = 1 where XMJE00 <> (XMDJ00 * XMSL00) and ID0000 = ad_ID0000; end if; --医保限价，限单位后，存在四舍五入后DJ*SL<>院内金额的，改变数量 MZSF-20181010-003 update BM_SFCFMX_TEMP00 set XMSL00 = round(XMJE00/XMDJ00,4) where XMJE00 <> (XMDJ00 * XMSL00) and ID0000 = ad_ID0000 and XMDJ00<>0; insert into BM_SFCF00_TEMP00(ID0000,NAME00,BZ0000,VALUE0) select ad_ID0000,NAME00,BZ0000,NVL(sum(VALUE0),0) from BM_SFCF00_TEMP00 where ID0000 = ls_ID0000 group by NAME00,BZ0000; if nvl(trim(LS_YSZJHM_TEMP),'0')='0' then select max(YSZJHM) into LS_YSZJHM_TEMP from BM_SFCFMX_TEMP00 where ID0000=ad_ID0000 and nvl(trim(YSZJHM),'0')<>'0'; end if; update BM_SFCFMX_TEMP00 Set yszjhm=LS_YSZJHM_TEMP where ID0000=ad_ID0000 and nvl(trim(YSZJHM),'0')='0'; --修改发送操作日期 by MZSF-20171222-001 update BM_SFCFMX_TEMP00 set CZRQ00=to_char(sysdate,'YYYYMMDD') where ID0000 = ad_ID0000 and CZRQ00 is null; delete from BM_SFCFMX_TEMP00 where ID0000= ad_ID0000 and nvl(XMJE00,0)=0; delete from BM_SFCFMX_TEMP00 where ID0000 = ls_ID0000; --本次生成的其它无用的去掉 delete from BM_SFCF00_TEMP00 where ID0000 = ls_ID0000; exception when E_YJJXZ then as_YHMSG0:='异地医保不能预结算!！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_YBJK00_FJ0000('||to_char(ad_BRID00)||','|| as_GHH000||','||as_YBMC00||','||to_char(ad_JZDH00)||',ad_ID0000,as_YHMSG0,as_SYSMSG)',1,150); rollback; when others then as_YHMSG0:='出错原因未知！请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_YBJK00_FJ0000('||to_char(ad_BRID00)||','|| as_GHH000||','||as_YBMC00||','||to_char(ad_JZDH00)||',ad_ID0000,as_YHMSG0,as_SYSMSG)',1,150); rollback; end;