PROCEDURE SP_SF_FYXX00_TEMP00 ( ad_JSLX00 in number, --0费用登记 1结算 ad_BRID00 in number, --门诊病人ID as_GHID00 in number, --挂号ID as_GHH000 in char, --病人挂号号 ad_ID0000 in number default 0, --ID0000 AD_JZDH00 in number default 0, --结帐单号 AS_TSBZBH in varchar default '', --特殊病种编号 ad_CZY000 in number , ad_NEWID0 out number, --新的ID as_YHMSG0 out varchar2, --存储过程提示的错误信息 as_SYSMSG out varchar2 --系统提示的错误信息 ) -- MODIFICATION HISTORY -- Person Date Comments --zhangyc 2015.04.28 create by MZSF-20150428-001 --zhangyc 2015.05.21 增加获取批次价格 by MZSF-20150521-001 --zhangyc 2015.06.19 增加高值耗材发送功能 by MZSF-20150512-001 --zhangyc 2015.06.29 增加血透析病种支付功能 by MZSF-20150630-001 --zhangyc 2015.10.29 增加函数SF_SF_YWJZMC取值 by MZSF-20151021-002 --zhangyc 2017.05.09 增加参数SF_SFQYYBXJJK控制医保单位发送功能 by MZSF-20170506-001 -- dsm 2019.04.03 门诊代煎费医保不能报销 for MZSF-20190329-001 ---------- ------------ ------------------------------ as ls_YBBRLB BM_BRXXB0.YBBRLB%TYPE; --医保病人类别 ls_YBLB00 BM_BRXXB0.YBLB00%TYPE; --医保类别 LS_BRXM00 BM_BRXXB0.BRXM00%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_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_YSXM00 BM_YGBM00.YSZJHM%TYPE; ls_ZWXM01 BM_YGBM00.ZWXM00%TYPE; --医生的中文姓名YGBH00=0 ls_XMTEMP BM_YGBM00.ZWXM00%TYPE; --医生的中文姓名临时 ls_ID0000 BM_SFCF00_TEMP00.ID0000%TYPE; ls_TSBZID BM_SFCF00_TEMP00.ID0000%TYPE;--特殊病种需要替换的数据 ls_YGXZ00 BM_YGBM00.YGXZ00%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_SFXMMC 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_SFXMID BM_YYSFXM.SFXMID%TYPE; --收费项目id XT_ZDYBYPMLXYSPBR number(2);--是否是要替换的病人 0 不是 1是 ls_SFTHML number(3);--0 不替换 1替换 ls_YBBHQZ VARCHAR2(20); --医保编码前缀 ls_YBMCFS CHAR(1); --是否按医保项目名称发送医保接口 ls_COUNT0 number(8); ls_SYFPBH CHAR(1); ls_DJSYXJ CHAR(1); ls_SJXMDJ SF_FYMX00.XMDJ00%TYPE; --费用项目实际单价 ls_CGSXSFCF CHAR(1); --收费项目的金额超过上限是否进行拆分 ls_SF_YBCGSXSFYYXMCF char(1); --门诊收费医保超过上限是否按原项目拆分 ls_FLAG00 CHAR(1); --标志 '0'费用,'1'零散处方,'2'医嘱处方 ls_YYID00 XT_YYXX00.YYID00%TYPE; ls_FSJMJE CHAR(1); --是否向医保发送减免金额 ls_YDYBSFKYYJS char(1); --异地医保是否可以预结算 CS_YSZJHM xt_xtcs00.VALUE0%TYPE; --医生姓名是否改为医生证件号 ls_sfxmyb xt_xtcs00.VALUE0%TYPE; --是否厦门医保 ls_sfxmid_zcf000 number; --厦门医保诊查费的sfxmid ls_XMFSGS xt_xtcs00.VALUE0%TYPE; --控制项目发送格式(医保项目名称(院内项目名称)) ls_YCXCLXMXGS CHAR(1); --一次性材料项目格式是否改为 医保对应项目+(医院收费项目) ls_JSXM00 BM_YYSFXM.JSXM00%type; E_YJJXZ exception; ls_SFFSYYFMMC varchar2(10); ls_SFYP00 BM_YBSFDY.SFYP00%type; ls_YYFPMC BM_FPXM00.XMMC00%type; SF_TSBZXZCFXM XT_XTCS00.VALUE0%TYPE; --是否对特殊病种外的目录进行限制 Y限制 N不限制 默认为N lv_yszjhm varchar2(50); ls_YPTS00 number(6);--用药天数 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 varchar2(30);--医生证件编码 ls_MZQDSFNSBL varchar2(10); ls_SFXNHFSQZF XT_XTCS00.VALUE0%TYPE; ls_YYFPXM BM_FPXM00.BH0000%TYPE; ls_ZFFSBZ YF_MZCFMX.ZFFSBZ%type; LS_CFHAO0 SF_FYXX00_TEMPMX.CFHAO0%type; --'医院唯一处方号' LS_JX0000 SF_FYXX00_TEMPMX.JX0000%type; --'剂型'=BM_YPJX00.YBFSBM(BM_YD0000.JXBH00) LS_DCYYDW SF_FYXX00_TEMPMX.DCYYDW%type; --'单次用药单位' LS_QYZL00 SF_FYXX00_TEMPMX.QYZL00%type; --'取药总量'; LS_QYZLDW SF_FYXX00_TEMPMX.QYZLDW%type; --'取药总量单位' LS_YLTS00 SF_FYXX00_TEMPMX.YLTS00%type; --'药量天数' LS_GYTJ00 SF_FYXX00_TEMPMX.GYTJ00%type; --'给药途径' =BM_YPYF00.YBFSBM LS_YPCZFL bm_yd0000.YPCZFL%type; --0:普通药品 1:颗粒药 2:全成分，3：包药机药品 VCZRQ00 char(8); VCZSJ00 char(8); LS_DJH000 SF_BRFY00.DJH000%type; LS_XJZF00 number(12,4);--自费金额 LS_JZJE00 number(12,4);--记账金额 LS_GFJE00 number(12,4);--公费金额 LS_ZHZYE0 number(12,4);--账户总余额 LS_YJZJE0 number(12,4);--已记账金额 LS_YPDLBH varchar2(10); LS_FPBH00 varchar2(2); LS_FPMC00 varchar2(40); V_DLSJGL varchar2(100); --多零售价管理 v_YFBMBH YF_MZCF00.YFBMBH%TYPE; V_CFLSH0 YF_MZCF00.CFLSH0%TYPE; --是否科室领药 V_SFKSLY YF_MZCF00.SFKSLY%TYPE; --是否科室领药 v_SFXJJZ YF_MZCF00.SFXJJZ%TYPE; --是否现金记账 V_DQLSDJ YF_YPKCXX.LSDJ00%TYPE; --当前零售价 V_DQGJDJ YF_YPKCXX.GJDJ00%TYPE; --当前购进单据 V_ZHL000 YF_MZCFMX.ZHL000%TYPE; V_K2J000 BM_YD0000.K2J000%TYPE; V_YPZSL0 number(12,4); v_XMDJ00 number(12,4); V_XMJE00 number(12,4); LS_HCDBBM Varchar2(40);--耗材打包编码 LS_GZHCID number(10);--高值耗材ID=BM_GZHCMX.GZHCID LS_HCDBXM char(1); --是否耗材打包项目 1:是 0:否 LS_SFFSHC char(1); --是否发送高值耗材 1:发送 0:不发送 LS_HCJKQY char(1); --高值耗材接口启用 Y:是 N：否 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;--医保最高单价 LS_COU number(5); 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,decode(trim(CS_YSZJHM),'Y',decode(e.YSZJHM,null,e.ZWXM00,e.YSZJHM),e.ZWXM00),--e.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, 0 as YPTS00,--用药天数 '' as PCMC00,--频次名称 a.XMSL00 as CSL000, --剂量 c.DW0000 as JLDW00,--剂量单位 e.YSZJHM, --医生证件编号 'N' ZFFSBZ, '*' as CFHAO0, --医院唯一处方号 '*' as JX0000, --剂型=BM_YPJX00.YBFSBM '*' as DCYYDW, --单次用药单位 '1' as QYZL00, --取药总量; '*' as QYZLDW, --取药总量单位 '1' as YLTS00, --药量天数 '*' as GYTJ00, --给药途径 =BM_YPYF00.YBFSBM '' as YPCZFL, --0:普通药品 1:颗粒药 2:全成分，3：包药机药品 D.DJH000, --单据号 A.ZFJE00, --自费金额 A.JZJE00, --记账金额 A.GFJE00, --公费金额 B.BH0000 AS FPBH00, B.XMMC00 AS FPMC00, '0' AS YPDLBH, null as K2J000,--库存单位转化率 null as M2J000,--门诊发药单位转化率 null as Z2J000,--住院发药单位转化率 null as YBDW00,--医保单位 null as YB2J00 --医保转换率 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' or d.MZH000='0') --and (d.MZID00= as_GHID00 or as_GHID00='0' or d.MZID00=0) and d.MZH000= as_GHH000 and d.MZID00= as_GHID00 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,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,decode(trim(CS_YSZJHM),'Y',decode(f.YSZJHM,null,c.YSXM00,f.YSZJHM),c.YSXM00), decode(ls_MZQDSFNSBL,'Y',1/decode(E.NSBL00,0,1,E.NSBL00),1)*a.LSDJ00, decode(ls_MZQDSFNSBL,'Y',decode(E.NSBL00,0,1,E.NSBL00),1)*a.YPZSL0, round(a.LSDJ00*a.YPZSL0*100)/100 YPHJJE,a.ZFBL00, nvl(a.YYSJ00,1) as YPTS00,--用药天数 --a.PCMC00 as PCMC00,--频次名称 decode(a.ypdlbh,'2','bid',nvl(a.PCMC00,'qd')) as PCMC00,--by MZSF-20140813-002 nvl(a.CSL000,a.YPZSL0) as CSL000, --剂量 a.JLDW00 as JLDW00,--剂量单位 f.YSZJHM, --医生证件编号 nvl(a.ZFFSBZ,'N') ZFFSBZ, C.CFLSH0 as CFHAO0, --医院唯一处方号 (select YBFSBM from BM_YPJX00 aa where aa.JXBH00=e.JXBH00 and rownum=1) as JX0000, --剂型=BM_YPJX00.YBFSBM trim(A.JLDW00) as DCYYDW, --单次用药单位 Trim(to_char(A.YPZSL0,'99990.99')) as QYZL00, --取药总量; trim(A.CFDW00) as QYZLDW, --取药总量单位 Trim(to_char(A.YYSJ00,'99990.99')) as YLTS00, --药量天数 (select YBFSBM from BM_YPYF00 aa where aa.YPYFMC=a.YPYFMC and rownum=1) as GYTJ00, --给药途径 =BM_YPYF00.YBFSBM e.YPCZFL as YPCZFL, --0:普通药品 1:颗粒药 2:全成分，3：包药机药品 D.DJH000, A.ZFJE00, --自费金额 A.JZJE00, --记账金额 A.GFJE00, --公费金额 '' AS FPBH00, '' AS FPMC00, C.YPDLBH, e.K2J000 as K2J000,--库存单位转化率 e.M2J000 as M2J000,--门诊发药单位转化率 e.Z2J000 as Z2J000,--住院发药单位转化率 e.YBDW00 as YBDW00,--医保单位 e.YB2J00 as YB2J00 --医保转换率 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' or d.MZH000='0') --and (d.MZID00 = as_GHID00 or as_GHID00='0' or d.MZID00=0) and d.MZH000= as_GHH000 and d.MZID00= as_GHID00 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_MZCF00 IS select distinct CFLSH0 From SF_FYXX00_DJH000 where ID0000=ad_NEWID0 and BRID00=ad_BRID00 and (XMFL00='0' and CFID00>0) order by CFLSH0; CURSOR c_MZCFMX IS Select MXID00,CFID00,YPNM00,XMSL00 as YPZSL0,XMDJ00 FROM SF_FYXX00_TEMPMX WHERE ID0000=ad_NEWID0 and BRID00=ad_BRID00 and CFLSH0=V_CFLSH0 and (XMFL00='0' and CFID00>0) order by MXID00; v_MZCFMX c_MZCFMX%ROWTYPE; begin VCZRQ00:=to_char(sysdate,'YYYYMMDD'); VCZSJ00:=to_char(sysdate,'HH24:MI:SS'); LS_HCDBBM:=null; LS_GZHCID:=0; LS_SFFSHC:='1'; LS_HCDBXM:='0'; --SF_GZHCYBFS 门诊收费医保病人结算是否高值耗材打包项目发送接口 0:都不启用 1:都启用 2:医保启用(农合除外) 3:农合启用(医保除外) 4:省医保启用 5:市医保启用 select SF_SF_XTCS00(ad_BRID00,'SF_GZHCYBFS') into LS_HCJKQY from dual; begin select Trim(VALUE0) into V_DLSJGL from XT_XTCS00 where name00='YF_SFDLSJGL'; exception when others then V_DLSJGL:='N'; end; 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 trim(VALUE0) into ls_SFFSYYFMMC from XT_XTCS00 where NAME00='SF_CBXMSFYYYFPMCFS'; exception when others then insert into XT_XTCS00(NAME00,VALUE0,FLAG00,BZ0000,FL0000) values('SF_CBXMSFYYYFPMCFS','N','1','起标项目是否以院内发票名称进行发送。Y启用、N 不启用。默认为N',''); commit; ls_SFFSYYFMMC:='N'; end; begin select upper(trim(VALUE0)) into SF_TSBZXZCFXM from XT_XTCS00 where NAME00 = 'SF_TSBZXZCFXM'; --是否对特殊病种外的目录进行限制 EXCEPTION WHEN OTHERS THEN SF_TSBZXZCFXM:='N'; END; --病种编号为 居民体检 或职工体检那么不处理 IF (as_tsbzbh IS NOT NULL) AND ((as_tsbzbh='000003000006') OR (as_tsbzbh='000003000007')) then SF_TSBZXZCFXM:='N'; end if; begin --select YYID00 into ls_YYID00 from XT_YYXX00 WHERE ROWNUM=1; select SF_SF_GETYYID00() YYID00 into ls_YYID00 from dual; exception when OTHERS then ls_YYID00:='0'; 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 异地医保病人默认不能预结算。 begin select count(*) into ls_COUNT0 from IC_YHXX00 where brid00=ad_BRID00 and ickh00 like '%@%' and zt0000='1'; end; if (ls_COUNT0>0) and (ad_JZDH00=0) and (ls_YDYBSFKYYJS='N') then --是异地医保病人且是预结算 参数ls_YDYBSFKYYJS为N RAISE E_YJJXZ; end if; begin select upper(trim(VALUE0)) into ls_MZQDSFNSBL from XT_XTCS00 where NAME00 = 'SF_MZQDSFANSBL'; EXCEPTION when OTHERS then ls_MZQDSFNSBL:='N'; end; begin select YBBRLB,FBBH00,YBLB00,BRXM00 into ls_YBBRLB,ls_FBBH00,ls_YBLB00,LS_BRXM00 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; rollback; return; end; select ZYE000 into LS_ZHZYE0 from SF_BRZHXX where brid00=ad_BRID00 and JFLBID=1; ------------------------------------判断是否为新农合病人begin--------------------------------- begin select upper(trim(VALUE0)) into ls_SFXNHFSQZF from XT_XTCS00 where NAME00='SF_MZNHBRYJXMSFFSFYB'; EXCEPTION when OTHERS then ls_SFXNHFSQZF:='N'; end; if trim(ls_SFXNHFSQZF)='Y' then begin select decode(SFXNH0,'1','Y','N') into ls_SFXNHFSQZF from ic_ybbrlb where (FBBH00,YBLB00)=(select FBBH00,YBLB00 from BM_BRXXB0 where brid00=ad_BRID00); EXCEPTION when OTHERS then ls_SFXNHFSQZF:='N'; end; end if; ------------------------------------判断是否为新农合病人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_YBCGSXSFYYXMCF from XT_XTCS00 WHERE NAME00='SF_YBCGSXSFYYXMCF'; --门诊收费医保超过上限是否按原项目拆分 EXCEPTION when NO_DATA_FOUND then ls_SF_YBCGSXSFYYXMCF:='N'; end; --接口文件是否使用医保发票编号,还是使用医保发票名称来传送 begin select upper(substrb(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 upper(trim(VALUE0)) into ls_XMFSGS from xt_xtcs00 where name00='SF_YBFSXMMCGS'; exception when others then ls_XMFSGS:='N'; end; ----一次性材料项目格式是否改为 医保对应项目+(医院收费项目) begin select trim(value0) into ls_YCXCLXMXGS FROM xt_xtcs00 where name00='SF_YCXCLXMXGS'; exception when OTHERS then ls_YCXCLXMXGS:='N'; end; begin--是否厦门医保接口 select trim(value0) into ls_sfxmyb FROM xt_xtcs00 where name00='SF_SFXMYBJK'; exception when OTHERS then ls_sfxmyb:='N'; end; begin --处理厦门医保诊疗费的收费项目 select nvl(to_number(value0),0) into ls_sfxmid_zcf000 from xt_xtcs00 where name00='SF_XMYBZLFID'; exception when others then ls_sfxmid_zcf000:=0; end; begin ---医生姓名是否改为医生证件号 select trim(value0) into CS_YSZJHM FROM xt_xtcs00 where name00='SF_MZSFYSZJHM'; exception when OTHERS then CS_YSZJHM:='N'; end; --if trim(CS_YSZJHM)='Y' then --add by MZSF-20120409-003 select SF_SF_GetYYKSBM(ad_BRID00,as_GHH000,1)YSZJHM into lv_yszjhm from dual; --end if; --医保编号前缀编码,是否目录限制 begin select trim(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; -----取YGBH00=0的医生姓名----begin------- ls_XMTEMP:='99999'; begin select ZWXM00 into ls_ZWXM01 from BM_YGBM00 where YGBH00=0; exception when OTHERS then ls_ZWXM01:='99999'; 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; -----取YGBH00=0的医生姓名----END------- select count(*) into XT_ZDYBYPMLXYSPBR from BM_TYZD00 where ZDMC00='指定费别审批的药品目录' and YXBZ00='1' and BZDYBH=to_char(ls_FBBH00)||'-'||ls_YBZXLB||'-'||ls_YBBRLB; if ad_id0000>0 then ls_ID0000:=ad_id0000; else select SQ_SF_FYXX00_TEMP00_ID0000.nextval into ls_ID0000 from dual; end if; ---开始整理数据 OPEN CUR_SF_FYMX00_MXID00; LOOP begin 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_ZFFSBZ, LS_CFHAO0, --医院唯一处方号 LS_JX0000, --剂型=BM_YPJX00.YBFSBM(BM_YD0000.JXBH00) LS_DCYYDW, --单次用药单位 LS_QYZL00, --取药总量; LS_QYZLDW, --取药总量单位 LS_YLTS00, --药量天数 LS_GYTJ00, --给药途径 =BM_YPYF00.YBFSBM LS_YPCZFL, LS_DJH000, LS_XJZF00, LS_JZJE00, LS_GFJE00, LS_FPBH00, LS_FPMC00, LS_YPDLBH, LS_K2J000,--库存单位转化率 LS_M2J000,--门诊发药单位转化率 LS_Z2J000,--住院发药单位转化率 LS_YBDW00,--医保单位 LS_YB2J00 --医保转换率 ; exit when CUR_SF_FYMX00_MXID00%NOTFOUND; --取高值耗材项目ID if LS_FLAG00='0' then begin select Nvl(A.GZHCID,0),nvl(A.SFFSYB,'1'),B.HCDBBM into LS_GZHCID,LS_SFFSHC,LS_HCDBBM from BM_GZHCMX A,BM_GZHCFL B where A.GZHCID=B.GZHCID and A.SFXMID=LS_XMBH00 and rownum=1; exception when others then LS_GZHCID:=0; LS_SFFSHC:='1'; LS_HCDBBM:=''; end; LS_GZHCID:=Nvl(LS_GZHCID,0); LS_SFFSHC:=Nvl(LS_SFFSHC,1); if LS_HCDBBM is null then LS_GZHCID:=0; LS_SFFSHC:='1'; end if; end if; ls_SFTHML:=0; if (XT_ZDYBYPMLXYSPBR>0) and (ls_FLAG00='1') then ls_SFTHML:=SF_XT_GETFYBMLBH('0',ad_brid00,ls_XMBH00,0);--返回0不替换 1替换 else ls_SFTHML:=0; end if; --MZSF-20190329-001 begin LS_COU:=0; if ls_FLAG00='0' then select count(*) into LS_COU from BM_TYZD00 where ZDMC00='门诊自费发送项目' and DJ0000=ls_XMBH00 and instr(','||MC0000||',',','||ls_YBZXLB||',')>0 AND YXBZ00='1'; end if; --MZSF-20190329-001 end if ls_ZFFSBZ='Y' or LS_COU>0 then --for MZYS-20131016-001 ls_SFTHML:=1; end if; LS_SFXMMC:=ls_XMMC00; if ls_FBBH00=3 then select a.XMMC00,a.BH0000,C.SFYBXM,nvl(C.SFSX00,0),D.XMMC00,Substrb(C.YBXMBH,1,40),Substrb(a.CBXMBH,1,40),C.ZFBL00,c.YBLB00,c.SFYP00, 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_SFYP00, 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' ; --非住院 --药品医保项目单位转化--begin--- 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) 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); end if; end if; end if; end if; --药品医保项目单位转化--end--- if LS_SFXMYB='Y' then --厦门地区 ---YPTS00:用药天数 YPPL00:频率代码 YPYL00:剂量+单位 --非药品的项目频率编码‘9999’，用法填‘无’，天数填‘0’。 BY MZSF-20130625-002 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:='9999'; end; ls_YPYL00:=substrb(trim(to_char(ls_CSL000,'9999990.00')||ls_JLDW00),1,20); else ls_YPPL00:='9999'; ls_YPYL00:='无';--substrb(trim(ls_CSL000||ls_JLDW00),1,20); end if; if ls_YPPL00 is null then ls_YPPL00:='9999'; end if; if ls_YPYL00 is null then ls_YPYL00:='无'; end if; else --修正非厦门地区 三个为空默认值 药品用量=* 用药天数=1 使用频率=* BY MZSF-20131220-004 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; if ls_YPPL00 is null then ls_YPPL00:='*'; end if; if ls_YPPL00<>'*' then ls_YPYL00:=substrb(trim(to_char(ls_CSL000,'9999990.00')||ls_JLDW00),1,20); end if; else ls_YPPL00:='*'; ls_YPYL00:='*'; end if; if ls_YPPL00 is null then ls_YPPL00:='*'; end if; if ls_YPYL00 is null then ls_YPYL00:='*'; end if; if (ls_YPTS00=0) or (ls_YPTS00 is null) then ls_YPTS00:=1 ; end if; end if; --特殊病种目录外药品是否替换处理 if (ls_SFTHML=0) and (ls_SFYBXM='Y') and (ls_FLAG00='1') and (SF_TSBZXZCFXM='Y') and (as_tsbzbh is not null) and (ls_YBXMBH is not null) then select count(*) into ls_COUNT0 from BM_TSBZXM b, BM_TSBZB0 c where trim(B.XMBH00)=trim(ls_YBXMBH) AND b.BH0000=C.BH0000 and B.YBZXLB=C.YBZXLB and B.YBZXLB=ls_YBZXLB and ( B.BH0000=as_tsbzbh or ( exists (select 1 from VW_YS_TSBZB_GXYTNB L where L.BH0000=as_tsbzbh) and B.BH0000 in (select BH0000 from VW_YS_TSBZB_GXYTNB) ) ) and c.MLXZBZ in ('1','3'); if ls_COUNT0=0 then ls_SFTHML:=1; end if; end if; -----取挂号医生姓名----begin------- If (ls_ZWXM01<>'99999') and (ls_ZWXM00<>ls_ZWXM01 ) and (ls_XMTEMP='99999') then ls_XMTEMP:=ls_ZWXM00; end if; -----取挂号医生姓名----end------- begin select a.BH0000 into ls_YYFPXM from BM_FPXM00 a,BM_YYSFXM b where a.FPXMID=b.MZFPID AND SFXMID=ls_XMBH00; exception when others then ls_YYFPXM:=ls_FPXMBH; end; if (trim(ls_SFXNHFSQZF) = 'Y') and (ls_SFYP00='N') and (ls_YYFPXM<>'04') and (ls_YYFPXM<>'06') then ls_YBXMBH := null; ls_YBZFBL := 1; ls_SFYBXM := 'N'; end if; --如果没有对应,查找BM_YBSFXM本发票记录有没有定义未对应或超标的项目医保接口使用的医保项目编号 -- 如果有定义,使用此定义的医保项目来生成接口文件,如果没有定义,使用'990000000000+医保发票编号方式 if ls_CBXMBH is not null then select a.XMMC00,a.MZFPXM,b.XMMC00,c.XMMC00 into ls_CBXMMC,ls_CBFPBH,ls_CBFPMC,ls_YYFPMC from BM_YBSFXM a,BM_YBFPXM b,BM_FPXM00 c where a.YBZXLB=ls_YBZXLB and a.XMBH00=ls_CBXMBH and a.MZFPXM=b.BH0000 and b.YBZXLB=ls_YBZXLB and b.YYFPXM=c.FPXMID(+); --取院内发票名称 if (ls_SFFSYYFMMC='Y') and (ls_SFYP00='N') then ls_CBFPMC:=ls_YYFPMC; end if; else ls_CBXMMC:=ls_XMMC00; ls_CBXMBH:=substrb(TRIM(ls_YBBHQZ)||ls_FPXMBH,1,40); ls_CBFPBH:=ls_FPXMBH; ls_CBFPMC:=ls_FPXMMC; end if; if (ls_YBXMBH is null) or (ls_SFTHML=1) then ls_YBXMMC:=trim(ls_CBXMMC); ls_YBXMBH:=substrb(ls_CBXMBH,1,40); ls_FPXMMC:=trim(ls_CBFPMC); ls_FPXMBH:=trim(ls_CBFPBH); end if; if ls_SFTHML=1 then ls_YBZFBL:=1; ls_SFYBXM:='N'; 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; ls_ZFBL00:=ls_YBZFBL; end if; ---modify by MZSF-20110713-002---- --一次性材料项目格式是否改为 医保对应项目+(医院收费项目)---厦门同安医院=Y if (ls_YCXCLXMXGS='Y') and (ls_FLAG00='0') and (ls_XMFSGS='N') then select nvl(JSXM00,'0') into ls_JSXM00 FROM BM_YYSFXM where sfxmid=ls_XMBH00; if trim(ls_JSXM00)='2' then ls_YBXMMC:=ls_YBXMMC||'('||ls_XMMC00||')'; end if; end if; else --医保执行--begin ls_YBXMBH:=ls_XMBH00; ls_YBXMMC:=LS_XMMC00; ls_SFMLXZ:='1'; ls_SFYBXM:='N'; LS_FPXMBH:=''; ls_FPXMMC:=''; if ls_FLAG00 in ('1') then begin select SFXMMC into ls_FPXMMC from BM_YPLB00 where LBBH00=LS_YPDLBH and rownum=1; exception when others then ls_FPXMMC:='其他费'; end; begin select BH0000 into LS_FPXMBH from BM_FPXM00 where XMMC00=ls_FPXMMC and rownum=1; exception when others then LS_FPXMBH:='13'; end; else LS_FPXMBH:=LS_FPBH00; ls_FPXMMC:=LS_FPMC00; end if; if LS_FPXMBH is null then LS_FPXMBH:='13'; end if; if ls_FPXMMC is null then ls_FPXMMC:='其他费'; 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; if ls_YPPL00 is null then ls_YPPL00:='*'; end if; if ls_YPPL00<>'*' then ls_YPYL00:=substrb(trim(to_char(ls_CSL000,'9999990.00')||ls_JLDW00),1,20); end if; else ls_YPPL00:='*'; ls_YPYL00:='*'; end if; if ls_YPPL00 is null then ls_YPPL00:='*'; end if; if ls_YPYL00 is null then ls_YPYL00:='*'; end if; if (ls_YPTS00=0) or (ls_YPTS00 is null) then ls_YPTS00:=1 ; 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 SF_FYXX00_TEMPMX(MXID00,ID0000,XMBH00,YBXMBH,SFYB00,FPXMMC,YBXMMC,XMMC00,YPNM00, XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00,YSXM00,SFXMID,ZFBL00, YPTS00,YPPL00,YPYL00,YSZJHM,CFHAO0,JX0000,DCYYDW,QYZL00, QYZLDW,YLTS00,GYTJ00,YPCZFL,FPXMBH, CZY000,CZRQ00,CZSJ00,BRID00,FBBH00,YBZXLB, YBLB00,GHID00,GHH000,CFLSH0,CFID00,YJDJH0, DJH000,CXDJH0,JZDH00,JSLX00,XMFL00,CFBZ00, JZJE00,ZFJE00,GFJE00,XJZF00,QTZF00,KFZT00, FLAG00,GZHCID,SFFSHC,HCDBXM ) values(SQ_SF_FYTEMP_MXID00.NextVal,ls_ID0000,ls_YBXMBH,ls_YBXMBH,DECODE(ls_SFMLXZ,0,'Y',ls_SFYBXM),DECODE(ls_SYFPBH,'Y',ls_FPXMBH,ls_FPXMMC), decode(trim(ls_yblb00),'Z',ls_XMMC00, decode(ls_ybmcfs,'Y', decode(trim(ls_XMFSGS),'N',ls_YBXMMC,substrb(ls_YBXMMC||'('||ls_XMMC00||')',1,50)),1,ls_XMMC00)),LS_SFXMMC,LS_XMBH00, ls_GG0000,ls_DW0000,ls_YBXMSX,ls_XMSL00,round(ls_YBXMSX*ls_XMSL00,2),ls_ZWXM00,LS_XMBH00,ls_ZFBL00, ls_YPTS00,ls_YPPL00,ls_YPYL00,LS_YSZJHM, LS_CFHAO0,LS_JX0000,LS_DCYYDW,LS_QYZL00,LS_QYZLDW,LS_YLTS00,LS_GYTJ00,LS_YPCZFL,LS_FPXMBH, ad_CZY000,VCZRQ00,VCZSJ00,ad_BRID00,ls_FBBH00,ls_YBZXLB, ls_YBLB00,as_GHID00,as_GHH000,'',0,0, LS_DJH000,0,0,ad_JSLX00,'2',decode(ls_FLAG00,'1','0','1'), LS_JZJE00,LS_XJZF00,LS_GFJE00,LS_XJZF00,LS_GFJE00,'1', LS_FLAG00,LS_GZHCID,LS_SFFSHC,LS_HCDBXM ); insert into SF_FYXX00_TEMPMX(MXID00,ID0000,XMBH00,YBXMBH,SFYB00,FPXMMC,YBXMMC,XMMC00,YPNM00, XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00,YSXM00,SFXMID,ZFBL00, YPTS00,YPPL00,YPYL00,YSZJHM,CFHAO0,JX0000,DCYYDW,QYZL00, QYZLDW,YLTS00,GYTJ00,YPCZFL,FPXMBH, CZY000,CZRQ00,CZSJ00,BRID00,FBBH00,YBZXLB, YBLB00,GHID00,GHH000,CFLSH0,CFID00,YJDJH0, DJH000,CXDJH0,JZDH00,JSLX00,XMFL00,CFBZ00, JZJE00,ZFJE00,GFJE00,XJZF00,QTZF00,KFZT00, FLAG00,GZHCID,SFFSHC,HCDBXM ) values(SQ_SF_FYTEMP_MXID00.NextVal,ls_ID0000,ls_CBXMBH,ls_CBXMBH,'N',DECODE(ls_SYFPBH,'Y',ls_CBFPBH,ls_CBFPMC),ls_CBXMMC,LS_SFXMMC,LS_XMBH00, ls_GG0000,ls_DW0000,(ls_XMDJ00-ls_YBXMSX),ls_XMSL00,round((ls_XMDJ00-ls_YBXMSX)*ls_XMSL00,2),ls_ZWXM00,LS_XMBH00,ls_ZFBL00, ls_YPTS00,ls_YPPL00,ls_YPYL00,LS_YSZJHM, LS_CFHAO0,LS_JX0000,LS_DCYYDW,LS_QYZL00,LS_QYZLDW,LS_YLTS00,LS_GYTJ00,LS_YPCZFL,LS_FPXMBH, ad_CZY000,VCZRQ00,VCZSJ00,ad_BRID00,ls_FBBH00,ls_YBZXLB, ls_YBLB00,as_GHID00,as_GHH000,'',0,0, LS_DJH000,0,0,ad_JSLX00,'2',decode(ls_FLAG00,'1','0','1'), LS_JZJE00,LS_XJZF00,LS_GFJE00,LS_XJZF00,LS_GFJE00,'1', LS_FLAG00,LS_GZHCID,LS_SFFSHC,LS_HCDBXM ); else insert into SF_FYXX00_TEMPMX(MXID00,ID0000,XMBH00,YBXMBH,SFYB00,FPXMMC,YBXMMC,XMMC00,YPNM00, XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00,YSXM00,SFXMID,ZFBL00, YPTS00,YPPL00,YPYL00,YSZJHM,CFHAO0,JX0000,DCYYDW,QYZL00, QYZLDW,YLTS00,GYTJ00,YPCZFL,FPXMBH, CZY000,CZRQ00,CZSJ00,BRID00,FBBH00,YBZXLB, YBLB00,GHID00,GHH000,CFLSH0,CFID00,YJDJH0, DJH000,CXDJH0,JZDH00,JSLX00,XMFL00,CFBZ00, JZJE00,ZFJE00,GFJE00,XJZF00,QTZF00,KFZT00, FLAG00,GZHCID,SFFSHC,HCDBXM ) values(SQ_SF_FYTEMP_MXID00.NextVal,ls_ID0000,ls_YBXMBH,ls_YBXMBH,DECODE(ls_SFMLXZ,0,'Y',ls_SFYBXM),DECODE(ls_SYFPBH,'Y',ls_FPXMBH,ls_FPXMMC), decode(trim(ls_yblb00),'Z',ls_XMMC00, decode(ls_ybmcfs,'Y', decode(trim(ls_XMFSGS),'N',ls_YBXMMC,substrb(ls_YBXMMC||'('||ls_XMMC00||')',1,50)),ls_XMMC00)),LS_SFXMMC,LS_XMBH00, ls_GG0000,ls_DW0000,ls_XMDJ00,ls_XMSL00,round(ls_XMDJ00*ls_XMSL00,2),ls_ZWXM00,LS_XMBH00,ls_ZFBL00, ls_YPTS00,ls_YPPL00,ls_YPYL00,LS_YSZJHM, LS_CFHAO0,LS_JX0000,LS_DCYYDW,LS_QYZL00,LS_QYZLDW,LS_YLTS00,LS_GYTJ00,LS_YPCZFL,LS_FPXMBH, ad_CZY000,VCZRQ00,VCZSJ00,ad_BRID00,ls_FBBH00,ls_YBZXLB, ls_YBLB00,as_GHID00,as_GHH000,'',0,0, LS_DJH000,0,0,ad_JSLX00,'2',decode(ls_FLAG00,'1','0','1'), LS_JZJE00,LS_XJZF00,LS_GFJE00,LS_XJZF00,LS_GFJE00,'1', LS_FLAG00,LS_GZHCID,LS_SFFSHC,LS_HCDBXM ); 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; CLOSE CUR_SF_FYMX00_MXID00; --更新药品单价 update SF_FYXX00_TEMPMX a set XMDJ00=SF_SF_LSDJ00(a.BRID00,a.CFLSH0,a.cfid00,a.XMDJ00), XMJE00=round(SF_SF_LSDJ00(a.BRID00,a.CFLSH0,a.cfid00,a.XMDJ00)*XMSL00,2), ZFJE00=XMJE00,XJZF00=XMJE00, JZJE00=0,GFJE00=0,QTZF00=0 where a.BRID00=ad_BRID00 and a.ID0000=ls_ID0000 and a.CFID00>0 and a.XMFL00='0' and KFZT00='0'; --已记账金额 select nvl(sum(XMJE00),0) into LS_YJZJE0 from SF_FYXX00_TEMPMX where BRID00=ad_BRID00 and ID0000=ls_ID0000 and KFZT00='1'; Select count(1) into LS_COUNT0 from SF_FYXX00_TEMPMX where BRID00=ad_BRID00 and ID0000=ls_ID0000; if LS_COUNT0=0 then ad_NEWID0:=0; else --重新整理数据--begin-- select SQ_SF_FYXX00_TEMP00_ID0000.nextval into ad_NEWID0 from dual; --把单号插入到临时表 insert into SF_FYXX00_DJH000 ( ID0000,BRID00,GHID00,GHH000,CFLSH0, CFID00,YJDJH0,DJH000,XMFL00,YPNM00, SFXMID,CFBZ00,KFZT00,JZDH00 ) select ad_NEWID0,BRID00,GHID00,GHH000,CFLSH0, CFID00,YJDJH0,DJH000,XMFL00,YPNM00, SFXMID,CFBZ00,KFZT00,JZDH00 from SF_FYXX00_TEMPMX where BRID00=ad_BRID00 and ID0000 = LS_ID0000; insert into SF_FYXX00_TEMPMX(MXID00,ID0000,XMBH00,YBXMBH,SFYB00,FPXMMC,YBXMMC,XMMC00,YPNM00, XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00,YSXM00,SFXMID,ZFBL00, YPTS00,YPPL00,YPYL00,YSZJHM,CFHAO0,JX0000,DCYYDW,QYZL00, QYZLDW,YLTS00,GYTJ00,YPCZFL,FPXMBH, CZY000,CZRQ00,CZSJ00,BRID00,FBBH00,YBZXLB, YBLB00,GHID00,GHH000,CFLSH0,CFID00,YJDJH0, DJH000,CXDJH0,JZDH00,JSLX00,XMFL00,CFBZ00, JZJE00,ZFJE00,GFJE00,XJZF00,QTZF00,KFZT00, SFYDJ0,FLAG00,GZHCID,SFFSHC,HCDBXM ) select max(MXID00),ad_NEWID0,XMBH00,YBXMBH,SFYB00,FPXMMC,trim(YBXMMC),XMMC00,YPNM00, max(XMGG00),max(XMDW00),XMDJ00,Sum(XMSL00),Sum(XMJE00),max(YSXM00),max(SFXMID),max(ZFBL00), max(YPTS00),max(YPPL00),max(YPYL00),max(YSZJHM),max(CFHAO0),max(JX0000),max(DCYYDW),max(QYZL00), max(QYZLDW),max(YLTS00),max(GYTJ00),max(YPCZFL),FPXMBH, max(CZY000),max(CZRQ00),max(CZSJ00),BRID00,FBBH00,YBZXLB, YBLB00,GHID00,GHH000,max(CFLSH0),max(CFID00),max(YJDJH0), max(DJH000),max(CXDJH0),max(JZDH00),max(JSLX00),max(XMFL00),max(CFBZ00), Sum(JZJE00),Sum(ZFJE00),Sum(GFJE00),Sum(XJZF00),Sum(QTZF00),max(KFZT00), max(SFYDJ0),max(FLAG00),max(GZHCID),max(SFFSHC),max(HCDBXM) from SF_FYXX00_TEMPMX where BRID00=ad_BRID00 and ID0000 = LS_ID0000 group by XMBH00,YBXMBH,SFYB00,FPXMMC,YBXMMC,XMMC00,YPNM00, XMDJ00,FPXMBH,BRID00,FBBH00,YBZXLB, YBLB00,GHID00,GHH000; --更新批次库存的价格 if V_DLSJGL='Y' then for R_CF in CUR_MZCF00 loop V_CFLSH0:=R_CF.CFLSH0; begin select SFKSLY,SFXJJZ,YFBMBH into V_SFKSLY,v_SFXJJZ,v_YFBMBH from YF_MZCF00 Where BRID00=ad_BRID00 and CFLSH0=V_CFLSH0; exception when others then V_SFKSLY:='N'; v_SFXJJZ:='0'; end; if V_SFKSLY='N' then --科室领药不做科室批次预扣--begin-- OPEN c_MZCFMX; LOOP FETCH c_MZCFMX INTO v_MZCFMX; EXIT WHEN c_MZCFMX%NOTFOUND; begin select ZHL000 into v_ZHL000 from YF_MZCFMX Where CFID00=v_MZCFMX.CFID00 and CFLSH0=V_CFLSH0; exception when others then v_ZHL000:=1; end; begin select K2J000 INTO V_K2J000 from BM_YD0000 where YPNM00=v_MZCFMX.YPNM00; exception when others then V_K2J000:=1; end; v_YPZSL0:=v_MZCFMX.YPZSL0; V_DQLSDJ:=SF_SF_GETDQPCJG(v_YFBMBH,v_MZCFMX.YPNM00,v_YPZSL0*v_ZHL000,to_number(v_SFXJJZ),0,v_MZCFMX.XMDJ00); --V_DQGJDJ:=SF_SF_GETDQPCJG(v_YFBMBH,v_MZCFMX.YPNM00,v_YPZSL0*v_ZHL000,to_number(v_SFXJJZ),1,v_MZCFMX.XMDJ00); if nvl(V_DQLSDJ,0)<>0 then v_XMDJ00:=round(V_DQLSDJ*v_ZHL000/V_K2J000,4); V_XMJE00:=round(v_YPZSL0*v_XMDJ00,2); update SF_FYXX00_TEMPMX SET XMDJ00=v_XMDJ00,XMJE00=V_XMJE00,ZFJE00=V_XMJE00,XJZF00=V_XMJE00, JZJE00=0,GFJE00=0,QTZF00=0 where ID0000=ad_NEWID0 and BRID00=ad_BRID00 and CFLSH0=V_CFLSH0 and CFID00=v_MZCFMX.CFID00; end if; end loop; CLOSE c_MZCFMX; end if; --科室领药不做科室批次预扣--end-- end loop; end if; --整理数据到主表 insert into SF_FYXX00_TEMP00 (ID0000,CZY000,CZRQ00,CZSJ00,BRID00,GHID00,GHH000,FBBH00,YBZXLB,YBLB00, ZHZYE0,YJZJE0,HJJE00,JZJE00,ZFJE00,GFJE00,XJZF00,QTZF00,BRXM00 ) select ad_NEWID0,ad_CZY000,VCZRQ00,VCZSJ00,ad_BRID00,as_GHID00,as_GHH000,ls_FBBH00,ls_YBZXLB,ls_YBLB00, LS_ZHZYE0,LS_YJZJE0,sum(XMJE00),sum(JZJE00),sum(XMJE00),sum(GFJE00),sum(XJZF00),sum(QTZF00),LS_BRXM00 from SF_FYXX00_TEMPMX where BRID00=ad_BRID00 and ID0000=ad_NEWID0; --删除临时数据 delete SF_FYXX00_TEMPMX where BRID00=ad_BRID00 and ID0000=ls_ID0000; end if; --重新整理数据--end-- --增加高值耗材发送功能 by MZSF-20150512-001 if LS_HCJKQY='Y' then select count(1)into LS_COUNT0 from SF_FYXX00_TEMPMX where ID0000=ad_NEWID0 and GZHCID>0 and nvl(SFFSHC,'1')='1' and nvl(HCDBXM,'0')='0'; --插入耗材打包项目 if LS_COUNT0>0 then insert into SF_FYXX00_TEMPMX(MXID00,ID0000,XMBH00,YBXMBH,SFYB00,FPXMMC,YBXMMC,XMMC00,YPNM00, XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00,YSXM00,SFXMID,ZFBL00, YPTS00,YPPL00,YPYL00,YSZJHM,CFHAO0,JX0000,DCYYDW,QYZL00, QYZLDW,YLTS00,GYTJ00,YPCZFL,FPXMBH, CZY000,CZRQ00,CZSJ00,BRID00,FBBH00,YBZXLB, YBLB00,GHID00,GHH000,CFLSH0,CFID00,YJDJH0, DJH000,CXDJH0,JZDH00,JSLX00,XMFL00,CFBZ00, JZJE00,ZFJE00,GFJE00,XJZF00,QTZF00,KFZT00, SFYDJ0,FLAG00,GZHCID,SFFSHC,HCDBXM ) select max(MXID00),ad_NEWID0,max(XMBH00),max(YBXMBH),max(SFYB00),max(FPXMMC),max(YBXMMC),max(XMMC00),max(YPNM00), max(XMGG00),max(XMDW00),Sum(XMJE00),1,Sum(XMJE00),max(YSXM00),max(SFXMID),max(ZFBL00), max(YPTS00),max(YPPL00),max(YPYL00),max(YSZJHM),max(CFHAO0),max(JX0000),max(DCYYDW),max(QYZL00), max(QYZLDW),max(YLTS00),max(GYTJ00),max(YPCZFL),max(FPXMBH), max(CZY000),max(CZRQ00),max(CZSJ00),BRID00,FBBH00,YBZXLB, YBLB00,GHID00,GHH000,max(CFLSH0),max(CFID00),max(YJDJH0), max(DJH000),max(CXDJH0),max(JZDH00),max(JSLX00),max(XMFL00),max(CFBZ00), Sum(JZJE00),Sum(ZFJE00),Sum(GFJE00),Sum(XJZF00),Sum(QTZF00),max(KFZT00), max(SFYDJ0),max(FLAG00),GZHCID,'1','1' from SF_FYXX00_TEMPMX where BRID00=ad_BRID00 and ID0000 = ad_NEWID0 and GZHCID>0 and nvl(SFFSHC,'1')='1' and nvl(HCDBXM,'0')='0' group by BRID00,FBBH00,YBZXLB,YBLB00,GHID00,GHH000,GZHCID; --修改打包项目的编码 update SF_FYXX00_TEMPMX A set (A.YBXMBH,A.YBXMMC,A.XMBH00,A.XMMC00,A.XMDW00) =(select B.HCDBBM,B.HCDBMC,B.HCDBBM as XMBH00,B.HCDBMC as XMMC00, B.DW0000 from BM_GZHCFL B where B.GZHCID=A.GZHCID and rownum=1) Where BRID00=ad_BRID00 and ID0000=ad_NEWID0 and A.GZHCID>0 and nvl(A.SFFSHC,'1')='1' and nvl(A.HCDBXM,'0')='1'; --把对应的明细数量和金额改成0 Update SF_FYXX00_TEMPMX set XMDJ00=0,XMJE00=0,JZJE00=0,ZFJE00=0,GFJE00=0,XJZF00=0,QTZF00=0 where BRID00=ad_BRID00 and ID0000 = ad_NEWID0 and GZHCID>0 and nvl(SFFSHC,'1')='1' and nvl(HCDBXM,'0')='0'; end if; end if; --把收费项目名称加(院外) update SF_FYXX00_TEMPMX set YBXMMC=substrb(trim(YBXMMC)||SF_SF_YWJZMC(SFXMID),1,100) where BRID00=ad_BRID00 and ID0000=ad_NEWID0 and CFBZ00='1'; --commit; --删除项目金额数据 delete SF_FYXX00_TEMPMX where BRID00=ad_BRID00 and ID0000=ad_NEWID0 and nvl(XMJE00,0)=0; exception when no_data_found then as_YHMSG0:='出错原因未知！请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_FYXX00_TEMP00错误',1,150); rollback; when OTHERS then as_YHMSG0:='出错原因未知！请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_FYXX00_TEMP00错误',1,150); rollback; end;