prompt 20.0、生成住院收费所需的医保接口存储过程 SP_ZY_YBJK00_FJ0000 create or replace procedure SP_ZY_YBJK00_FJ0000 ( ad_QSDJH0 in out number, --起始单据号 ad_ZZDJH0 in out number, --终止单据号 ad_JZDH00 in number, --结帐单号 ad_ZYID00 in out number, --住院ID as_qsrq00 in varchar, --起始日期 as_zzrq00 in varchar, --截至日期 as_YBMC00 in char, --医保中心名称 ad_ID0000 out number, --序列号ID as_FLAG00 in char, --标志 '0':SP_ZY_ZYSF00_YBJK00调用,'1':SP_ZY_ZYSF00_YBJK01调用, --'2':SP_ZY_NPYBFS_YBJK00调用,'3'SP_ZY_ZYSFYB_RQDFS0 调用 --'4':SP_ZY_ZYSFYB_YJS000调用,'5':单病种调用,'6':非单病种调用 as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar, --系统提示的错误信息 ad_YEXM00 in char default '全部', --婴儿姓名 ad_DBZBM0 in varchar2 default '' --单病种编码 ) -- MODIFICATION HISTORY -- Person Date Comments -- xzw 2007.08.16 对收费的项目规格进行截取,截取的长度为10; -- jlg 2007.10.19 把项目的备注作为规格传给医保 -- qks 2010.03.29 农合病人 增加传送费用发生日期 -- qks 2010.03.30 医保病人发送费用给医保之前，判断明细的收费项目合计数量是否小于0 -- qks 2010.04.01 新农合 先把数量为负数的合并到正数上 -- qks 2010.04.23 修改bug,ZYSF-20100423-001 -- qks 2010.05.10 bm_zycfmx_temp00删除记录增加条件 or nvl(xmsl00,0)=0 -- qks 2010.05.25 判断是否超过2000条记录，如果超过，只按同一天日期重新汇总 -- zhr 2010.06.10 解决异地医保结算时，有的医保分中心非医保项目要求用70打头，有的要求用99打头发送。 -- zhr 2010.06.25 处理XMSL00=0 ,XMJE00<>0 -- qks 2010.07.15 游标CUR_ZY_FYMX00_MXID00中项目单价XMDJ00取值方法修改 -- qks 2010.11.29 新增参数ZY_XNHCSLX_SFRQ ZYSF-20101130-003 -- qks 2011.03.18 新农合使用社保卡,同时判断IC_YBBRLB.SFXNH0='1' --zhangyc 2011.03.24 增加参数ZY_YCXCLXMXGS(同安医院=Y) 一次性材料项目(只针对收费项目)格式是否改为 医保对应项目+(医院收费项目) -- csf 2011.04.28 省二人民医院，保健病人的部分药品，是否使用医保目录需要审批 ，没有审批或审批不通过则替换成非医保目录 --zhangyc 2011.06.20 增加传入参数AD_YEXM00(婴儿姓名) --zhangyc 2011.06.28 莆田医保发送名称是否按新格式发送(医保项目名称(院内项目名称))参数控制(SF_YBFSXMMCGS) by ZYSF-20110614-003 -- csf 2011.06.28 审批部分原用参数XT_ZDYBYPMLXYSPBR控制，但维护麻烦，现改成直接从通用字典判断 --zhangyc 2011.07.22 ZY_YCXCLXMXGS='Y'时 材料发送格式厦门地区和其它地区分开 by ZYSF-20110713-003 --zhangyc 2011.08.12 modify by ZYSF-20110810-001 ZY_YCXCLXMXGS=Y的情况下如果名称超过25个汉字医保会报超过50个字节的错误提示，请修改过程，只发送25个汉字 --csf 2011.09.16 未审批被替换的项目，自付比例改成1 --jinfl 2012.01.30 药品医保目录对应有问题时，只做报错提示，不发送医保中心 ZYSF-20120113-002 --dsm 2012.02.01 两费保健 超出部分按自费结算,如未做审批设置，该材料应按全自费结算 for ZYSF-20110808-003 --csf 2012.02.22 SF_XT_GETFYBMLBH 加参数，本过程同步更改 XMGL-20120222-001 --zhangyc 2012.04.10 参数SF_MZSFYSZJHM='Y'明细医生姓名是否改医生证件号 by ZYSF-20120410-001 --zhangyc 2013.01.24 增加医生证件号 BY ZYSF-20130126-001 -- jinfl 2013.04.28 医保有限制项目名称不能超过50个字节,所以将substr改为substrb ZYSF-20130426-002 --zhangyc 2012.06.19 单病种分开结算 by ZYSF-20130619-001 入参as_FLAG00=5 or 6 --zhangyc 2013.06.26 增加发送YPTS00:用药天数 YPPL00:频率代码 YPYL00:剂量+单位 非药品的项目频率编码‘9999’，用法填‘无’，天数填‘0’ by ZYSF-20130625-002 --zhangyc 2013.07.04 ZY_YBDBZJSJK=M 时 VW_BM_YBSFDY.SFSX00 无效（过程SP_ZY_DBZCF0 中有用到） by ZYSF-20130703-001 --zhangyc 2013.07.08 超过医保上限金额写入到ZY_JZB000.YBRL19 BY ZYSF-20130708-002 -- liuj 2013.08.09 增加参数控制中药处方剂量是否不按浓缩比例转化 for ZYSF-20130403-002 --zhangyc 2013.12.23 修正非厦门地区 三个为空默认值 药品用量=* 用药天数=1 使用频率=* BY ZYSF-20131220-002 --dsm 2014.02.26 明细ZFFSBZ='Y'的按自费发送 for BQ-20131018-001 --zhangyc 2014.05.09 ls_YBBHQZ||ls_FPXMBH-->trim(ls_YBBHQZ)||ls_FPXMBH 修正生产request中错误 by ZYSF-20140425-001 --zhangyc 2014.11.17 增加保健医保接口功能 by ZYSF-20141111-003 --zhangyc 2015.01.04 修正厦门同安医院医保项目编码为13位报错 by ZYSF-20150104-001 --zhangyc 2015.05.29 增加判断如果合并后数据中有负数记录，重新算单价后在整理数据 by ZYSF-20150227-001 --zhangyc 2015.06.18 增加高值耗材发送功能 by ZYSF-20150505-001 --zhangyc 2015.10.29 增加函数SF_SF_YWJZMC取值 by ZYSF-20151021-004 --zhangyc 2015.11.06 把YPYL00长度截取到char(6) by ZYSF-20151021-001 --qks 2016.05.26 高值耗材发送修改：高值耗材包发送给医保的 发票项目、是否医保项目(Y或N)，使用项目管理系统中的“医保收费管理”对应项目的住院发票以及是否医保项目(Y或N); -- 如果没有对应的医保项目，则发票项目固定为“其他费”,是否医保项目固定为“Y”; by ZYSF-20151021-001 --zhangyc 2017.03.15 修改单病种 by ZYSF-20170206-001 --zhangyc 2017.03.30 修改bug by ZYSF-20170330-004 --zhangyc 2017.03.30 增加打包项目对应国家编码BM_GZHCFL.BZGJBM by ZYSF-20170330-005 --zhangyc 2017.04.06 修正单病种超标床位费SFYB00 全部发送N by ZYSF-20170406-001 --zhangyc 2017.04.12 修改单病种结算病人并且ZY_DBZCEFKJS=N非床位设置上限不拆分 by ZYSF-20170405-001 --zhangyc 2017.05.09 增加参数SF_SFQYYBXJJK控制医保单位发送功能 by ZYSF-20170506-001 --zhangyc 2017.05.24 修正数量为0出现bug by ZYSF-20170524-001 --zhangyc 2017.07.04 修改单价超过最高限价拆分参数：ZY_YBYPZGXJCF 控制 by ZYSF-20170626-001 --zhangyc 2017.08.09 修改单价超过异常 by ZYSF-20170810-001 as --更改结帐单号为单据号,更改YF_YZYPSQ游标的内容 LS_MSGSTR varchar2(100); ls_BRID00 BM_BRXXB0.BRID00%TYPE; --病人ID ls_YBBRLB BM_BRXXB0.YBBRLB%TYPE; --医保病人类别 ls_YBLB00 BM_BRXXB0.YBLB00%TYPE; --医保类别 ls_YBID00 BM_BRXXB0.YBID00%TYPE; --医保ID ls_YBKH00 BM_BRXXB0.YBKH00%TYPE; --医保卡号 ls_FBBH00 BM_BRXXB0.FBBH00%TYPE; --病人费别 ls_SFMLXZ BM_YBBRLB.SFMLXZ%TYPE; --是否受目录限制,0:不受目录限制,1:受目录限制 ls_ZYZFSX BM_YBBRLB.ZYZFSX%type; --住院收费上限 ls_YBZXLB BM_YBFPXM.YBZXLB%TYPE; --医保中心类别编码 ls_FPXMMC BM_YBFPXM.XMMC00%TYPE; --发票项目名称 ls_FPXMBH BM_YBFPXM.BH0000%TYPE; --医保发票项目编号 ls_YBXMMC BM_YBSFXM.XMMC00%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_ID0000 BM_ZYCF00_TEMP00.ID0000%TYPE; ls_FYBQZ0 IC_YBBRLB.FYBQZ0%TYPE; --非医保项目编号前缀(首2位) ls_YBZFBL ZY_FYMX00.ZFBL00%TYPE; --医保自付比例 --ls_MXID00 ZY_FYMX00.MXID00%TYPE; --费用明细ID ls_MXID00 NUMBER(20); --费用明细ID ls_XMBH00 ZY_FYMX00.XMBH00%TYPE; --费用项目编号 ls_XMMC00 ZY_FYMX00.XMMC00%TYPE; --费用项目名称 ls_HJJE00 ZY_FYMX00.HJJE00%TYPE; --费用合计金额 ls_XMJE01 ZY_FYMX00.HJJE00%TYPE; ls_ZFJE00 ZY_FYMX00.ZFJE00%TYPE; --费用合计金额 ls_ZFBL00 ZY_FYMX00.ZFBL00%TYPE; --自付比例 ls_XMDJ00 ZY_FYMX00.XMDJ00%TYPE; --项目单价 ls_XMDJ01 ZY_FYMX00.XMDJ00%TYPE; --项目单价 ls_GG0000 ZY_FYMX00.XMGG00%TYPE; --项目规格 ls_DW0000 ZY_FYMX00.XMDW00%TYPE; --项目单位 ls_XMSL00 ZY_FYMX00.XMSL00%TYPE; --项目数量 ls_XMSL01 ZY_FYMX00.XMSL00%TYPE; --项目数量 ls_CZRQ00 ZY_FYMX00.CZRQ00%TYPE; --操作日期 ls_SFXNH0 IC_YBBRLB.SFXNH0%TYPE; --是否新农合 ls_ZYZFSX_TEMP bm_ybbrlb.ZYZFSX%type; ls_YBMCFS CHAR(1); --是否按医保项目名称发送医保接口 ls_YBBHQZ VARCHAR2(20); --医保编码前缀 ls_DJSYXJ CHAR(1); ls_XNHCSLX VARCHAR2(2); --农合病人是否实时结算 ls_COUNT0 NUMBER(5); ls_SYFPBH Char(1); --医保接口使用发票编号来传送 ls_CGSXSFCF CHAR(1); --收费项目的金额超过上限是否进行拆分 ls_FLAG00 CHAR(1); --标志 '0'费用,'1'零散处方,'2'医嘱处方 ZY_SFASXJS CHAR(2); --医保住院是否按上限结算 ls_SFBZZWGG CHAR(1); --是否备注作为规格传送给医保 ls_XNHSFRQ CHAR(1); --农合病人实时结算是否发送费用日期 ls_YCXCLXMXGS CHAR(1); --一次性材料项目格式是否改为 医保对应项目+(医院收费项目)。 ls_JSXM00 BM_YYSFXM.JSXM00%type; ls_XMFSGS xt_xtcs00.VALUE0%TYPE; --控制项目发送格式(医保项目名称(院内项目名称)) ls_SFXMYBJK xt_xtcs00.VALUE0%TYPE; --是否厦门医保接口 XT_ZDYBYPMLXYSPBR NUMBER(2); --是否参数描述病人 ls_SFTHML number(3);--0 不替换 1替换 ls_XMDJSX xt_xtcs00.VALUE0%TYPE; --需要审批的单价上限 ls_JSXM01 BM_YYSFXM.JSXM00%type; ls_SPJE00 YK_YBMLSP.SPJE00%Type; --已经审批的金额 ls_SFXYSHEBZ CHAR(1); --是否需要审核标志 ls_SFYBMLDYCWFSYBZX VARCHAR2(2); --医保目录错误时，是否发送医保中心 ls_YBDYML_count NUMBER(5); --医保对应目录中医保项目编号不为空的项目数 ls_MZSFYSZJHM xt_xtcs00.VALUE0%TYPE; --医生姓名是否改为医生证件号参数 ls_YSZJHM varchar2(50); lv_YSZJHM varchar2(30); lv_MAXYSZJH varchar2(30); 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_DBZJK0 varchar2(100); ls_YBSXCE BM_ZYCFMX_TEMP00.YBSXCE%TYPE; --医保上限金额 ls_ZYQDSFNSBL char(1); ls_ZFFSBZ varchar2(1); LS_NewID0 BM_ZYCF00_TEMP00.ID0000%TYPE; LS_HCDBBM BM_GZHCFL.HCDBBM%type;--耗材打包编码 LS_HCDBMC BM_GZHCFL.HCDBMC%type;--耗材打包名称 LS_GZHCID number(10);--高值耗材ID=BM_GZHCMX.GZHCID LS_HCDBXM char(1); --是否耗材打包项目 1:是 0:否 2：超标费用 LS_SFFSHC char(1); --是否发送高值耗材 1:发送 0:不发送 LS_HCJKQY char(1); --高值耗材接口启用 Y:是 N：否 LS_SFXMID BM_YYSFXM.SFXMID%TYPE; --收费项目id LS_DBZBM0 ZY_BRXXB0.DBZBM0%type; --住院登记单病种编码 LS_DBZMC0 ZY_BRXXB0.DBZMC0%type; --住院登记单病种名称 LS_BZCBBH BM_YBFPXM.BZCBBH%type; --单病种超标医保发票项目编号 LS_BZCBMC BM_YBFPXM.BZCBMC%type; --单病种超标发票项目名称 LS_BZCBCW ZY_BRZHXX.DBZCWF%type; --单病种超标床位费 LS_BZLSHC ZY_BRZHXX.DBZHCF%type; --单病种另收耗材费 LS_BZGJBM varchar2(200); --单病种允许另收耗材的国家编码前缀组合,用逗号分隔 LS_SFBZDY char(1);--是否单病种打包 lv_CZRQ00 char(8); lv_CZSJ00 char(8); ZY_DBZCEFKJS varchar2(100); LS_GJBM00 BM_YYSFXM.GJBM00%type; --国家编码 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_YBJSDJ1 BM_YBSFXM.YBJSDJ1%type;--医保结算单价 LS_YBZGDJ1 BM_YBSFXM.YBZGDJ1%type;--医保最高单价 ZY_YBYPZGXJCF varchar2(100); LS_ZGXJCF varchar2(10);--医保药品最高限价拆分 Y:拆分 N:不拆分 LS_ZGXJCS varchar2(10);--医保药品最高限价参数 LS_ZGXJID number(12); --医保药品最高限价拆分项目ID LS_ZGXJDJ number(12,4); --最高限价单价 LS_XJCEDJ number(12,4); --药品超过最高限价单价 LS_XJCEJE number(12,2); --药品超过最高限价金额 LS_ZGXJJE number(12,2); --药品最高限价金额 LS_XJCFSL number(12,2); --限价拆分数量 CURSOR CUR_ZY_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, substrb(nvl(decode(ls_SFBZZWGG,'N',c.GG0000,c.BZ0000),'无'),1,10) GG0000,nvl(c.DW0000,'无') DW0000,a.XMBH00,e.ZWXM00, round(decode(a.JMBZ00,'0',a.XMDJ00,'4',(a.HJJE00+a.JMJE00)/decode(a.XMSL00,0,1,a.XMSL00),(a.HJJE00-a.JMJE00)/decode(a.XMSL00,0,1,a.XMSL00)),4) XMDJ00, a.XMSL00,decode(a.JMBZ00,'4',(a.HJJE00+a.JMJE00),(a.HJJE00-a.JMJE00)) HJJE00,a.ZFBL00,d.CZRQ00,C.JSXM00,e.YSZJHM, 0 as YPTS00,--用药天数 '' as PCMC00,--频次名称 a.XMSL00 as CSL000, --剂量 c.DW0000 as JLDW00, --剂量单位 'N' ZFFSBZ, C.SFXMID, --收费项目ID trim(C.GJBM00)GJBM00, --国家编码 null as K2J000,--库存单位转化率 null as M2J000,--门诊发药单位转化率 null as Z2J000,--住院发药单位转化率 null as YBDW00,--医保单位 null as YB2J00 --医保转换率 from ZY_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,ZY_BRFY00 D,BM_YGBM00 E where (d.jzdh00 in (select k.jzdh00 from ZY_JZB000 k where k.BRID00=ls_brid00 start with k.jzdh00=ad_JZDH00 connect by prior k.cxjzdh=k.jzdh00) or d.jzdh00+0=0)--考虑病人可能重新结算的 and a.XMBH00 = C.SFXMID and C.ZYFPID = B.FPXMID and D.DJH000=a.DJH000 and a.KDYS00=e.YGBH00 and d.djh000>=ad_QSDJH0 and d.djh000<=ad_ZZDJH0 and B.SFYLXM='Y' and a.XMBH00<9999999900 --将西药,草药,成药,材料的去掉(以及将专门减免的也去掉9999999901~9999999990 jlg) and d.ZYID00=ad_ZYID00 and a.JMBZ00<>'2' ----如果JMBZ00='4'(事前减免),则发送医保时要把这部分减免的金额也发送给医保 for ZYSF-20070124-001 and (decode(nvl(d.yexm00,'0'),'0','母亲',d.yexm00)=ad_YEXM00 or ad_YEXM00='全部') union all select '1'FLAG00,a.CFID00,round(a.LSDJ00*a.YPZSL0*100)/100,substrb(e.YPMC00,1,50)YPMC00, decode(ls_ZYQDSFNSBL,'Y',DECODE(TRIM(E.YPGG00),'100g/袋','散','250g/袋','散',nvl(e.YPGG00,'无')),nvl (e.YPGG00,'无')) YPGG00,nvl(a.CFDW00,'无')CFDW00,a.YPNM00,c.YSXM00, decode(ls_ZYQDSFNSBL,'Y',1/decode(E.NSBL00,0,1,E.NSBL00),1)*a.LSDJ00, decode(ls_ZYQDSFNSBL,'Y',decode(E.NSBL00,0,1,E.NSBL00),1)*a.YPZSL0, round(a.LSDJ00*a.YPZSL0*100)/100 YPHJJE,a.ZFBL00,d.CZRQ00,'0', (Select ee.YSZJHM from BM_YGBM00 ee where ee.YGBH00=c.YSGZH0 and rownum=1)YSZJHM, nvl(a.YYSJ00,1) as YPTS00,--用药天数 a.PCMC00 as PCMC00,--频次名称 nvl(CSL000,a.YPZSL0) as CSL000, --剂量 a.JLDW00 as JLDW00,--剂量单位 nvl(a.ZFFSBZ,'N') ZFFSBZ, e.YPNM00 as SFXMID, --收费项目ID null as GJBM00, --国家编码 e.K2J000 as K2J000,--库存单位转化率 e.M2J000 as M2J000,--门诊发药单位转化率 e.Z2J000 as Z2J000,--住院发药单位转化率 e.YBDW00 as YBDW00,--医保单位 e.YB2J00 as YB2J00 --医保转换率 from YF_ZYCFMX A,YF_ZYCF00 c,ZY_BRFY00 d,BM_YD0000 e WHERE (d.jzdh00 in (select k.jzdh00 from ZY_JZB000 k where k.BRID00=ls_brid00 start with k.jzdh00=ad_JZDH00 connect by prior k.cxjzdh=k.jzdh00) or d.jzdh00+0=0) and a.CFLSH0=c.CFLSH0 and c.DJH000=d.DJH000 and d.ZYID00=ad_ZYID00 and a.YPNM00=e.YPNM00 and d.DJH000>=ad_QSDJH0 and d.DJH000<=ad_ZZDJH0 and a.SFZBY0='N'--是否自备药 and (decode(nvl(d.yexm00,'0'),'0','母亲',d.yexm00)=ad_YEXM00 or ad_YEXM00='全部') union all select '2'FLAG00,a.YPQLPC,round(a.LSDJ00*a.YPZSL0*100)/100,substrb(d.YPMC00,1,50)YPMC00, decode(ls_ZYQDSFNSBL,'Y',DECODE(TRIM(d.YPGG00),'100g/袋','散','250g/袋','散',nvl(d.YPGG00,'无')),nvl (d.YPGG00,'无')) YPGG00,nvl(a.QLDW00,'无')CFDW00,a.YPNM00,e.ZWXM00,decode(ls_ZYQDSFNSBL,'Y',1/decode(d.NSBL00,0,1,d.NSBL00),1)*a.LSDJ00, decode(ls_ZYQDSFNSBL,'Y',decode(d.NSBL00,0,1,d.NSBL00),1)*a.YPZSL0, round(a.LSDJ00*a.YPZSL0*100)/100 YPHJJE,a.ZFBL00,b.CZRQ00,'0',e.YSZJHM, 1 as YPTS00,--用药天数 a.PCMC00 as PCMC00,--频次名称 nvl(CSL000,a.YPZSL0) as CSL000, --剂量 a.JLDW00 as JLDW00,--剂量单位 nvl(a.ZFFSBZ,'N') ZFFSBZ, d.YPNM00 as SFXMID, --收费项目ID null as GJBM00, --国家编码 d.K2J000 as K2J000,--库存单位转化率 d.M2J000 as M2J000,--门诊发药单位转化率 d.Z2J000 as Z2J000,--住院发药单位转化率 d.YBDW00 as YBDW00,--医保单位 d.YB2J00 as YB2J00 --医保转换率 from YF_YZYPSQ A,ZY_BRFY00 b,BM_YD0000 d,BM_YGBM00 E where (b.jzdh00 in (select k.jzdh00 from ZY_JZB000 k where k.BRID00=ls_brid00 start with k.jzdh00=ad_JZDH00 connect by prior k.cxjzdh=k.jzdh00) or b.jzdh00+0=0)and a.DJH000=b.DJH000 and a.YPNM00=d.YPNM00 and a.YSGZH0=e.YGBH00 and a.DJH000>= ad_QSDJH0 and a.DJH000<= ad_ZZDJH0 and a.ZYID00=ad_ZYID00 and (decode(nvl(b.yexm00,'0'),'0','母亲',b.yexm00)=ad_YEXM00 or ad_YEXM00='全部'); CURSOR CUR_ZY_FYMX00_MXID01 IS select '0'FLAG00,a.MXID00,decode(a.JMBZ00,'4',a.ZFJE00,a.ZFJE00-a.JMJE00) ZFJE00,substrb(c.XMMC00,1,50) XMMC00, substrb(nvl(decode(ls_SFBZZWGG,'N',c.GG0000,c.BZ0000),'无'),1,10) GG0000,nvl(c.DW0000,'无') DW0000,a.XMBH00,e.ZWXM00, round(decode(a.JMBZ00,'0',a.XMDJ00,'4',(a.HJJE00+a.JMJE00)/decode(a.XMSL00,0,1,a.XMSL00),(a.HJJE00-a.JMJE00)/decode(a.XMSL00,0,1,a.XMSL00)),4) XMDJ00, --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,d.CZRQ00,C.JSXM00,e.YSZJHM, 0 as YPTS00,--用药天数 '' as PCMC00,--频次名称 a.XMSL00 as CSL000, --剂量 c.DW0000 as JLDW00, --剂量单位 'N' ZFFSBZ, c.SFXMID as SFXMID, --收费项目ID trim(C.GJBM00)GJBM00, --国家编码 null as K2J000,--库存单位转化率 null as M2J000,--门诊发药单位转化率 null as Z2J000,--住院发药单位转化率 null as YBDW00,--医保单位 null as YB2J00 --医保转换率 from ZY_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,ZY_BRFY00 D,BM_YGBM00 E where a.XMBH00 = C.SFXMID and C.ZYFPID = B.FPXMID and D.DJH000=a.DJH000 and a.KDYS00=e.YGBH00 and d.djh000>=ad_QSDJH0 and d.djh000<=ad_ZZDJH0 and B.SFYLXM='Y' and a.XMBH00<9999999900 --将西药,草药,成药,材料的去掉(以及将专门减免的也去掉 jlg) and d.ZYID00=ad_ZYID00 and a.JMBZ00<>'2' and d.ybfsdh='0' and d.czrq00>=as_qsrq00 and d.czrq00<=as_zzrq00 and d.jzdh00+0=0 and (decode(nvl(d.yexm00,'0'),'0','母亲',d.yexm00)=ad_YEXM00 or ad_YEXM00='全部') union all select '1'FLAG00,a.CFID00,round(a.LSDJ00*a.YPZSL0*100)/100,substrb(e.YPMC00,1,50)YPMC00, decode(ls_ZYQDSFNSBL,'Y',DECODE(TRIM(E.YPGG00),'100g/袋','散','250g/袋','散',nvl(e.YPGG00,'无')),nvl (e.YPGG00,'无')) YPGG00, nvl(a.CFDW00,'无')CFDW00,a.YPNM00,c.YSXM00,decode(ls_ZYQDSFNSBL,'Y',1/decode(E.NSBL00,0,1,E.NSBL00),1)*a.LSDJ00, decode(ls_ZYQDSFNSBL,'Y',decode(E.NSBL00,0,1,E.NSBL00),1)*a.YPZSL0, round(a.LSDJ00*a.YPZSL0*100)/100 YPHJJE,a.ZFBL00,d.CZRQ00,'0', (select ee.YSZJHM from BM_YGBM00 ee where ee.YGBH00=c.YSGZH0 and rownum=1)YSZJHM, nvl(a.YYSJ00,1) as YPTS00,--用药天数 a.PCMC00 as PCMC00,--频次名称 nvl(CSL000,a.YPZSL0) as CSL000, --剂量 a.JLDW00 as JLDW00,--剂量单位 nvl(a.ZFFSBZ,'N') ZFFSBZ, e.YPNM00 as SFXMID, --收费项目ID null as GJBM00, --国家编码 e.K2J000 as K2J000,--库存单位转化率 e.M2J000 as M2J000,--门诊发药单位转化率 e.Z2J000 as Z2J000,--住院发药单位转化率 e.YBDW00 as YBDW00,--医保单位 e.YB2J00 as YB2J00 --医保转换率 from YF_ZYCFMX A,YF_ZYCF00 c,ZY_BRFY00 d,BM_YD0000 e where d.ybfsdh='0' and d.jzdh00+0=0 and d.czrq00>=as_qsrq00 and d.czrq00<=as_zzrq00 and a.CFLSH0=c.CFLSH0 and c.DJH000=d.DJH000 and d.ZYID00=ad_ZYID00 and a.YPNM00=e.YPNM00 and d.DJH000>=ad_QSDJH0 and d.DJH000<=ad_ZZDJH0 and a.SFZBY0='N'--是否自备药 and (decode(nvl(d.yexm00,'0'),'0','母亲',d.yexm00)=ad_YEXM00 or ad_YEXM00='全部') union all select '2'FLAG00,a.YPQLPC,round(a.LSDJ00*a.YPZSL0*100)/100,substrb(d.YPMC00,1,50)YPMC00, decode(ls_ZYQDSFNSBL,'Y',DECODE(TRIM(d.YPGG00),'100g/袋','散','250g/袋','散',nvl(d.YPGG00,'无')),nvl (d.YPGG00,'无')) YPGG00,nvl(a.QLDW00,'无')CFDW00,a.YPNM00,e.ZWXM00,decode(ls_ZYQDSFNSBL,'Y',1/decode(d.NSBL00,0,1,d.NSBL00),1)*a.LSDJ00, decode(ls_ZYQDSFNSBL,'Y',decode(d.NSBL00,0,1,d.NSBL00),1)*a.YPZSL0, round(a.LSDJ00*a.YPZSL0*100)/100 YPHJJE,a.ZFBL00,b.CZRQ00,'0',e.YSZJHM, 1 as YPTS00,--用药天数 a.PCMC00 as PCMC00,--频次名称 nvl(CSL000,a.YPZSL0) as CSL000, --剂量 a.JLDW00 as JLDW00,--剂量单位 nvl(a.ZFFSBZ,'N') ZFFSBZ, d.YPNM00 as SFXMID, --收费项目ID null as GJBM00, --国家编码 d.K2J000 as K2J000,--库存单位转化率 d.M2J000 as M2J000,--门诊发药单位转化率 d.Z2J000 as Z2J000,--住院发药单位转化率 d.YBDW00 as YBDW00,--医保单位 d.YB2J00 as YB2J00 --医保转换率 from YF_YZYPSQ A,ZY_BRFY00 b,BM_YD0000 d,BM_YGBM00 E where b.ybfsdh='0' and b.jzdh00+0=0 and b.czrq00>=as_qsrq00 and b.czrq00<=as_zzrq00 and a.DJH000=b.DJH000 and a.YPNM00=d.YPNM00 and a.YSGZH0=e.YGBH00 and a.DJH000>= ad_QSDJH0 and a.DJH000<= ad_ZZDJH0 and a.ZYID00=ad_ZYID00 and (decode(nvl(b.yexm00,'0'),'0','母亲',b.yexm00)=ad_YEXM00 or ad_YEXM00='全部'); CURSOR CUR_ZY_FYMX00_MXID02 IS select '0'FLAG00,a.MXID00,decode(a.JMBZ00,'4',a.ZFJE00,a.ZFJE00-a.JMJE00) ZFJE00,substrb(c.XMMC00,1,50) XMMC00, substrb(nvl(decode(ls_SFBZZWGG,'N',c.GG0000,c.BZ0000),'无'),1,10) 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,d.CZRQ00,C.JSXM00,e.YSZJHM, 0 as YPTS00,--用药天数 '' as PCMC00,--频次名称 a.XMSL00 as CSL000, --剂量 c.DW0000 as JLDW00, --剂量单位 'N' ZFFSBZ, c.SFXMID as SFXMID, --收费项目ID trim(C.GJBM00)GJBM00, --国家编码 null as K2J000,--库存单位转化率 null as M2J000,--门诊发药单位转化率 null as Z2J000,--住院发药单位转化率 null as YBDW00,--医保单位 null as YB2J00 --医保转换率 from ZY_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,ZY_BRFY00 D,BM_YGBM00 E where a.XMBH00 = C.SFXMID and C.ZYFPID = B.FPXMID and D.DJH000=a.DJH000 and a.KDYS00=e.YGBH00 and not exists (select 1 from BM_RQDFS0 where ZYID00 = D.ZYID00 and QDRQ00=D.CZRQ00) and B.SFYLXM='Y' and a.XMBH00<9999999900 --将西药,草药,成药,材料的去掉(以及将专门减免的也去掉 jlg) and d.ZYID00=ad_ZYID00 and a.JMBZ00<>'2' and d.ybfsdh='0' and d.jzdh00+0=0 and (decode(nvl(d.yexm00,'0'),'0','母亲',d.yexm00)=ad_YEXM00 or ad_YEXM00='全部') union all select '1'FLAG00,a.CFID00,round(a.LSDJ00*a.YPZSL0*100)/100,substrb(e.YPMC00,1,50)YPMC00, decode(ls_ZYQDSFNSBL,'Y',DECODE(TRIM(e.YPGG00),'100g/袋','散','250g/袋','散',nvl(e.YPGG00,'无')),nvl (e.YPGG00,'无')) YPGG00, nvl(a.CFDW00,'无')CFDW00,a.YPNM00,c.YSXM00,decode(ls_ZYQDSFNSBL,'Y',1/decode(e.NSBL00,0,1,e.NSBL00),1)*a.LSDJ00, decode(ls_ZYQDSFNSBL,'Y',decode(e.NSBL00,0,1,e.NSBL00),1)*a.YPZSL0, round(a.LSDJ00*a.YPZSL0*100)/100 YPHJJE,a.ZFBL00,d.CZRQ00,'0', (Select ee.YSZJHM from BM_YGBM00 ee where ee.YGBH00=c.YSGZH0 and rownum=1)YSZJHM, nvl(a.YYSJ00,1) as YPTS00,--用药天数 a.PCMC00 as PCMC00,--频次名称 nvl(CSL000,a.YPZSL0) as CSL000, --剂量 a.JLDW00 as JLDW00,--剂量单位 nvl(a.ZFFSBZ,'N') ZFFSBZ, e.YPNM00 as SFXMID, --收费项目ID null as GJBM00, --国家编码 e.K2J000 as K2J000,--库存单位转化率 e.M2J000 as M2J000,--门诊发药单位转化率 e.Z2J000 as Z2J000,--住院发药单位转化率 e.YBDW00 as YBDW00,--医保单位 e.YB2J00 as YB2J00 --医保转换率 from YF_ZYCFMX A,YF_ZYCF00 c,ZY_BRFY00 d,BM_YD0000 e where d.ybfsdh='0' and d.jzdh00+0=0 and a.YPNM00=e.YPNM00 and a.SFZBY0='N' and--是否自备药 not exists (select 1 from BM_RQDFS0 where ZYID00 = D.ZYID00 and QDRQ00=D.CZRQ00) and a.CFLSH0=c.CFLSH0 and c.DJH000=d.DJH000 and d.ZYID00=ad_ZYID00 and (decode(nvl(d.yexm00,'0'),'0','母亲',d.yexm00)=ad_YEXM00 or ad_YEXM00='全部') union all select '2'FLAG00,a.YPQLPC,round(a.LSDJ00*a.YPZSL0*100)/100,substrb(d.YPMC00,1,50)YPMC00, decode(ls_ZYQDSFNSBL,'Y',DECODE(TRIM(d.YPGG00),'100g/袋','散','250g/袋','散',nvl(d.YPGG00,'无')),nvl (d.YPGG00,'无')) YPGG00,nvl(a.QLDW00,'无')CFDW00,a.YPNM00,e.ZWXM00,decode(ls_ZYQDSFNSBL,'Y',1/decode(d.NSBL00,0,1,d.NSBL00),1)*a.LSDJ00, decode(ls_ZYQDSFNSBL,'Y',decode(d.NSBL00,0,1,d.NSBL00),1)*a.YPZSL0, round(a.LSDJ00*a.YPZSL0*100)/100 YPHJJE,a.ZFBL00,b.CZRQ00,'0',e.YSZJHM, 1 as YPTS00,--用药天数 a.PCMC00 as PCMC00,--频次名称 nvl(CSL000,a.YPZSL0) as CSL000, --剂量 a.JLDW00 as JLDW00,--剂量单位 nvl(a.ZFFSBZ,'N') ZFFSBZ, d.YPNM00 as SFXMID, --收费项目ID null as GJBM00, --国家编码 d.K2J000 as K2J000,--库存单位转化率 d.M2J000 as M2J000,--门诊发药单位转化率 d.Z2J000 as Z2J000,--住院发药单位转化率 d.YBDW00 as YBDW00,--医保单位 d.YB2J00 as YB2J00 --医保转换率 from YF_YZYPSQ A,ZY_BRFY00 b,BM_YD0000 d,BM_YGBM00 E where b.ybfsdh='0' and b.jzdh00+0=0 and a.ZYID00=ad_ZYID00 and a.DJH000=b.DJH000 and a.YPNM00=d.YPNM00 and a.YSGZH0=e.YGBH00 and (decode(nvl(b.yexm00,'0'),'0','母亲',b.yexm00)=ad_YEXM00 or ad_YEXM00='全部') and not exists ( select 1 from BM_RQDFS0 where ZYID00 = b.ZYID00 and QDRQ00 = b.CZRQ00); cursor CUR_ZY_FYMX00_MXID03 is select '0'FLAG00,a.MXID00,decode(a.JMBZ00,'4',a.ZFJE00,a.ZFJE00-a.JMJE00) ZFJE00,substrb(c.XMMC00,1,50) XMMC00, substrb(nvl(decode(ls_SFBZZWGG,'N',c.GG0000,c.BZ0000),'无'),1,10) 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,d.CZRQ00,C.JSXM00,e.YSZJHM, 0 as YPTS00,--用药天数 '' as PCMC00,--频次名称 a.XMSL00 as CSL000, --剂量 c.DW0000 as JLDW00, --剂量单位 'N' ZFFSBZ, c.SFXMID as SFXMID, --收费项目ID trim(C.GJBM00)GJBM00, --国家编码 null as K2J000,--库存单位转化率 null as M2J000,--门诊发药单位转化率 null as Z2J000,--住院发药单位转化率 null as YBDW00,--医保单位 null as YB2J00 --医保转换率 from ZY_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,ZY_BRFY00 D,BM_YGBM00 E where a.XMBH00 = C.SFXMID and C.ZYFPID = B.FPXMID and D.DJH000=a.DJH000 and a.KDYS00=e.YGBH00 and B.SFYLXM='Y' and a.XMBH00<9999999900 --将西药,草药,成药,材料的去掉(以及将专门减免的也去掉 jlg) and d.ZYID00=ad_ZYID00 and a.JMBZ00<>'2' and d.jzdh00+0=0 and (decode(nvl(d.yexm00,'0'),'0','母亲',d.yexm00)=ad_YEXM00 or ad_YEXM00='全部') union all select '1'FLAG00,a.CFID00,round(a.LSDJ00*a.YPZSL0*100)/100,substrb(e.YPMC00,1,50)YPMC00, decode(ls_ZYQDSFNSBL,'Y',DECODE(TRIM(e.YPGG00),'100g/袋','散','250g/袋','散',nvl(e.YPGG00,'无')),nvl (e.YPGG00,'无')) YPGG00,nvl(a.CFDW00,'无')CFDW00,a.YPNM00,c.YSXM00,decode(ls_ZYQDSFNSBL,'Y',1/decode(e.NSBL00,0,1,e.NSBL00),1)*a.LSDJ00, decode(ls_ZYQDSFNSBL,'Y',decode(e.NSBL00,0,1,e.NSBL00),1)*a.YPZSL0, round(a.LSDJ00*a.YPZSL0*100)/100 YPHJJE,a.ZFBL00,d.CZRQ00,'0', (Select ee.YSZJHM from BM_YGBM00 ee where ee.YGBH00=c.YSGZH0 and rownum=1)YSZJHM, nvl(a.YYSJ00,1) as YPTS00,--用药天数 a.PCMC00 as PCMC00,--频次名称 nvl(CSL000,a.YPZSL0) as CSL000, --剂量 a.JLDW00 as JLDW00,--剂量单位 nvl(a.ZFFSBZ,'N') ZFFSBZ, e.YPNM00 as SFXMID, --收费项目ID null as GJBM00, --国家编码 e.K2J000 as K2J000,--库存单位转化率 e.M2J000 as M2J000,--门诊发药单位转化率 e.Z2J000 as Z2J000,--住院发药单位转化率 e.YBDW00 as YBDW00,--医保单位 e.YB2J00 as YB2J00 --医保转换率 from YF_ZYCFMX A,YF_ZYCF00 c,ZY_BRFY00 d,BM_YD0000 e where d.jzdh00+0=0 and a.CFLSH0=c.CFLSH0 and c.DJH000=d.DJH000 and d.ZYID00=ad_ZYID00 and a.YPNM00=e.YPNM00 and a.SFZBY0='N'--是否自备药 and (decode(nvl(d.yexm00,'0'),'0','母亲',d.yexm00)=ad_YEXM00 or ad_YEXM00='全部') union all select '2'FLAG00,a.YPQLPC,round(a.LSDJ00*a.YPZSL0*100)/100,substrb(d.YPMC00,1,50)YPMC00, decode(ls_ZYQDSFNSBL,'Y',DECODE(TRIM(d.YPGG00),'100g/袋','散','250g/袋','散',nvl(d.YPGG00,'无')),nvl (d.YPGG00,'无')) YPGG00,nvl(a.QLDW00,'无')CFDW00,a.YPNM00,e.ZWXM00,decode(ls_ZYQDSFNSBL,'Y',1/decode(d.NSBL00,0,1,d.NSBL00),1)*a.LSDJ00, decode(ls_ZYQDSFNSBL,'Y',decode(d.NSBL00,0,1,d.NSBL00),1)*a.YPZSL0, round(a.LSDJ00*a.YPZSL0*100)/100 YPHJJE,a.ZFBL00,b.CZRQ00,'0',e.YSZJHM, 1 as YPTS00,--用药天数 a.PCMC00 as PCMC00,--频次名称 nvl(CSL000,a.YPZSL0) as CSL000, --剂量 a.JLDW00 as JLDW00,--剂量单位 nvl(a.ZFFSBZ,'N') ZFFSBZ, d.YPNM00 as SFXMID, --收费项目ID null as GJBM00, --国家编码 d.K2J000 as K2J000,--库存单位转化率 d.M2J000 as M2J000,--门诊发药单位转化率 d.Z2J000 as Z2J000,--住院发药单位转化率 d.YBDW00 as YBDW00,--医保单位 d.YB2J00 as YB2J00 --医保转换率 from YF_YZYPSQ A,ZY_BRFY00 b,BM_YD0000 d,BM_YGBM00 E where b.jzdh00+0=0 and a.DJH000=b.DJH000 and a.YPNM00=d.YPNM00 and a.YSGZH0=e.YGBH00 and (decode(nvl(b.yexm00,'0'),'0','母亲',b.yexm00)=ad_YEXM00 or ad_YEXM00='全部') and a.ZYID00=ad_ZYID00; --单病种游标 cursor CUR_ZY_FYMX00_DBZ000 is select A.FLAG00,a.MXID00, nvl(a.ZFJE00,0)ZFJE00,substrb(a.XMMC00,1,50) XMMC00, substrb(nvl(A.XMGG00,'无'),1,10) GG0000,nvl(A.XMDW00,'无') DW0000,a.XMBH00,e.ZWXM00, A.XMDJ00,a.XMSL00,A.HJJE00,a.ZFBL00,A.CZRQ00,a.JSXM00,e.YSZJHM, nvl(YPTS00,0) as YPTS00,--用药天数 PCMC00 as PCMC00,--频次名称 CSL000 as CSL000, --剂量 JLDW00 as JLDW00,--剂量单位 'N' ZFFSBZ, nvl(SFXMID,0)SFXMID, null as GJBM00, --国家编码 null as K2J000,--库存单位转化率 null as M2J000,--门诊发药单位转化率 null as Z2J000,--住院发药单位转化率 null as YBDW00,--医保单位 null as YB2J00 --医保转换率 from ZY_FYMX00_DBZ000 A,BM_YGBM00 E where a.KDYS00=e.YGBH00 and a.djh000>=ad_QSDJH0 and a.djh000<=ad_ZZDJH0 and a.ZYID00=ad_ZYID00 and a.BRID00=ls_BRID00 --and ((as_FLAG00='5' and A.DBZBM0 is not null) or (as_FLAG00='6' and A.DBZBM0 is null)) --and (decode(nvl(A.yexm00,'0'),'0','母亲',A.yexm00)=ad_YEXM00 or ad_YEXM00='全部') ; cursor CUR_BM_ZYCFMX_TEMP00 is select * from BM_ZYCFMX_TEMP00 where ID0000=ad_ID0000 and SFYB00='Y' order by XMJE00; cursor CUR_BM_ZYCFMX_TEMP00_XNH is select * from BM_ZYCFMX_TEMP00 where ID0000=LS_ID0000 and XMSL00<0; cursor CUR_YBZGXJ is select * from BM_ZYCFMX_TEMP00 where ID0000=ad_ID0000 and FLAG00 in ('1','2') order by SFXMID; BEGIN /* 医保接口说明: 未对应为项目或超标项目的的处理办法:如果一个收费项目或药品未对应医保项目，那么有两种处理办法 一、如果BM_YBFPXM表有定义该医保发票项目的未对应或超标的项目医保接口使用的医保项目编号，那么医保接口使用此项目编号 二‘ 否则沿用以前的办法 采用'990000000000+医保发票编号方式，前缀由表BM_YBBRLB定义，如果没有定义默认前缀为990000000000 IC_YBBRLB.FYBQZ0可以强制改变非医保项目的发送规则。 接口是否按医保目录的项目名称发送由参数XT_YBJK_SFAYBXMMCFS控制， 但有特例，铁路医保要求发院内项目名称,对应指定特殊医保项目的，也要发院内名称 接口文件是否使用医保发票编号,还是使用医保发票名称来传送 由参数ZY_YBFPBH控制 收费项目的金额超过上限是否进行拆分，由参数ZY_SFXMCGSXSFCF控制 医保住院是否按上限结算 由参数ZY_SFASXJS控制 所有医保接口都调用本过程，注意南平医保日清单发送接口 更正以前当SFYBXM为N就按未对应医保项目处理的办法 */ ls_ZYZFSX:=0; ls_ZYZFSX_TEMP:=0; ls_YBSXCE:=0; LS_GZHCID:=0; LS_SFFSHC:='1'; LS_HCDBXM:='0'; LS_BZCBBH:=null; --单病种超标医保发票项目编号 LS_BZCBMC:=null; --单病种超标发票项目名称 LS_BZCBCW:=0; --单病种超标床位费 LS_BZLSHC:=0; --单病种另收耗材费 LS_BZGJBM:=null; --单病种允许另收耗材的国家编码前缀组合,用逗号分隔 LS_SFBZDY:='N';--是否单病种打包 lv_CZRQ00:=to_char(sysdate,'YYYYMMDD'); lv_CZSJ00:=to_char(sysdate,'HH24:MI:SS'); LS_ZGXJCF:='N';--医保药品最高限价拆分 Y:拆分 N:不拆分 LS_ZGXJCS:='0';--医保药品最高限价参数 LS_ZGXJID:=0; --医保药品最高限价拆分项目ID LS_XJCEDJ:=0; LS_ZGXJDJ:=0; if as_FLAG00='0' then--只传入结帐单号 select max(BRID00),max(ZYID00),min(DJH000),max(DJH000) into ls_BRID00,ad_ZYID00,ad_QSDJH0,ad_ZZDJH0 from ZY_BRFY00 where JZDH00 = ad_JZDH00; elsif as_FLAG00='1'then select BRID00,ZYID00 into ls_BRID00,ad_ZYID00 from ZY_BRFY00 where DJH000=ad_QSDJH0; elsif as_FLAG00 in ('2','3','4','5','6') then--有传入ZYID00 select BRID00 into ls_BRID00 from ZY_BRXXB0 where ZYID00=ad_ZYID00; end if; select Trim(nvl(max(value0),'N')) into ls_DBZJK0 from xt_xtcs00 where name00='ZY_YBDBZJSJK'; select SF_ZY_XTCS00(ls_BRID00,'ZY_GZHCYBFS') into LS_HCJKQY from dual; if (ad_DBZBM0 is not null) then --select count(1) into LS_COUNT0 from BM_GZHCFL a,BM_GZHCMX b where a.GZHCID=b.GZHCID and a.SFDBZ0='1' and a.XXZLXM=ad_DBZBM0; select count(1) into LS_COUNT0 from BM_GZHCFL B where B.SFDBZ0='1' and trim(B.XXZLXM) is not null and instrb(','||trim(B.XXZLXM)||',',','||trim(ad_DBZBM0)||',')>0; if LS_COUNT0>0 then LS_SFBZDY:='Y';--是否单病种打包 end if; end if; select YBBRLB,FBBH00,YBLB00,YBID00,YBKH00 into ls_YBBRLB,ls_FBBH00,ls_yblb00,ls_YBID00,ls_YBKH00 from BM_BRXXB0 where BRID00 = ls_BRID00; --取医保中心类别 BEGIN select YBZXLB,FYBQZ0,SFXNH0 into ls_YBZXLB,ls_FYBQZ0,ls_SFXNH0 from IC_YBBRLB where (FBBH00,YBLB00)=(select FBBH00,YBLB00 from BM_BRXXB0 where brid00=ls_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 DBZBM0,DBZMC0 into LS_DBZBM0,LS_DBZMC0 from ZY_BRXXB0 where ZYID00=AD_ZYID00; exception when others then LS_DBZBM0:=null; LS_DBZMC0:=null; end; --农合病人是否实时结算 BEGIN select upper(trim(VALUE0)) into ls_XNHCSLX from XT_XTCS00 where NAME00 = 'ZY_XNHCSLX'; EXCEPTION WHEN OTHERS THEN ls_XNHCSLX:='N'; END; ---医生姓名是否改为医生证件号 by ZYSF-20120410-001----begin---- begin select trim(value0) into ls_MZSFYSZJHM FROM xt_xtcs00 where name00='SF_MZSFYSZJHM'; exception when OTHERS then ls_MZSFYSZJHM:='N'; end; if ls_MZSFYSZJHM='Y' then select SF_ZY_GetYYKSBM(ad_ZYID00,1) YSZJHM into ls_YSZJHM from dual; --0:医院科室编码 1:医生证件号 end if; --全成分浓缩比处理 begin select upper(trim(VALUE0)) into ls_ZYQDSFNSBL from XT_XTCS00 where NAME00 = 'ZY_ZYQDSFANSBL'; EXCEPTION when OTHERS then ls_ZYQDSFNSBL:='N'; end; ---医生姓名是否改为医生证件号 by ZYSF-20120410-001----end---- ls_XNHSFRQ := 'N'; if ls_XNHCSLX = 'YN' then select substrb(upper(trim(nvl(max(value0),'Y'))),1,1) into ls_XNHSFRQ from XT_XTCS00 where NAME00 = 'ZY_XNHCSLX_SFRQ'; end if; --医保单价*数量与金额是否要求严格相等,泉州医保接口要求单价*数量必须等于金额 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 = 'ZY_SFXMCGSXSFCF'; EXCEPTION WHEN OTHERS THEN ls_CGSXSFCF:='Y'; 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 ZY_SFASXJS from XT_XTCS00 where NAME00 = 'ZY_SFASXJS'; exception when others then ZY_SFASXJS:='N'; end; --是否项目的备注作为规格传送给医保 begin select trim(VALUE0) into ls_SFBZZWGG from XT_XTCS00 where NAME00='ZY_BYXMBZSFZWGGCSYB'; exception when others then ls_SFBZZWGG:='N'; end; select count(*) into XT_ZDYBYPMLXYSPBR from BM_TYZD00 where ZDMC00='指定费别审批的药品目录' and YXBZ00='1' and BZDYBH=to_char(ls_FBBH00)||'-'||ls_YBZXLB||'-'||ls_YBBRLB; ----一次性材料项目格式是否改为 医保对应项目+(医院收费项目) begin select trim(value0) into ls_YCXCLXMXGS FROM xt_xtcs00 where name00='ZY_YCXCLXMXGS'; exception WHEN OTHERS THEN ls_YCXCLXMXGS:='N'; end; --是否厦门医保接口 厦门医保接口ZY_YCXCLXMXGS='Y'时材料发送格式医保对应项目+(医院收费项目)其它地区格式:医保对应项目(医院收费项目) begin select trim(value0) into ls_SFXMYBJK FROM xt_xtcs00 where name00='ZY_SFXMYBJK'; exception WHEN OTHERS THEN ls_SFXMYBJK:='N'; end; --取需要审批的材料单价 BEGIN select upper(trim(VALUE0)) into ls_XMDJSX from XT_XTCS00 where NAME00 = 'ZY_CLXMSPDJSX'; EXCEPTION WHEN OTHERS THEN ls_XMDJSX:='0'; END; --医保对应目录发生错误时，是否发送医保中心 BEGIN select upper(trim(VALUE0)) into ls_SFYBMLDYCWFSYBZX from XT_XTCS00 where NAME00='ZY_SFYBMLDYCWFSYBZX'; EXCEPTION WHEN OTHERS THEN ls_SFYBMLDYCWFSYBZX:='N'; END; --单病种结算超额是否分开结算 Y:农合和医保都分开结算 M:医保分开结算(农合除外) P:农合分开结算(医保除外) N:都不分开结算 默认值N:不分开结算(ZY_YBDBZJSJK=M有效) begin select trim(VALUE0) into ZY_DBZCEFKJS from XT_XTCS00 where NAME00='ZY_DBZCEFKJS'; exception when others then ZY_DBZCEFKJS:='N'; end; --SF_SFQYYBXJJK 门诊或住院系统是否启用医保限价接口 Y:启用 N:不启用 默认值N:不启用 begin select SF_SF_YBXJQY(LS_BRID00,ad_ZYID00,'1') into LS_YBXJQY from dual;--返回值 Y:启用 N:不启用 exception when others then LS_YBXJQY:='N'; end; --ZY_YBYPZGXJCF 住院收费医保药品超过医保最高限价是否拆分参数格式:(类别,收费项目ID) 类别:0:都不拆分 1:都拆分 2:医保拆分(农合除外) 3:农合拆分(医保除外) 默认值:0,0都不拆分 begin select trim(VALUE0) into ZY_YBYPZGXJCF from XT_XTCS00 where NAME00 = 'ZY_YBYPZGXJCF'; exception when others then ZY_YBYPZGXJCF:='0,0'; end; begin LS_ZGXJCS:=to_number(substrb(trim(ZY_YBYPZGXJCF),1,1)); --医保药品最高限价参数 LS_ZGXJID:=to_number(substrb(trim(ZY_YBYPZGXJCF),3,12)); --医保药品最高限价拆分项目ID exception when others then LS_ZGXJCF:='N';--医保药品最高限价拆分 Y:拆分 N:不拆分 LS_ZGXJCS:='0';--医保药品最高限价参数 LS_ZGXJID:=0; --医保药品最高限价拆分项目ID end; if (LS_FBBH00=3) and (LS_YBXJQY='Y') then if (LS_ZGXJCS in ('1')) then LS_ZGXJCF:='Y'; end if; if (LS_ZGXJCS in ('2')) and (LS_SFXNH0 not in ('1')) then LS_ZGXJCF:='Y'; end if; --2:医保启用(农合除外) if (LS_ZGXJCS in ('3')) and (LS_SFXNH0 in ('1')) then LS_ZGXJCF:='Y'; end if; --3:农合启用(医保除外) end if; --医保编号前缀编码,是否目录限制 BEGIN select FBMGZ0,SFMLXZ,ZYZFSX into ls_YBBHQZ,ls_SFMLXZ,ls_ZYZFSX from bm_ybbrlb where YBZXLB = ls_YBZXLB and YBBRLB = ls_YBBRLB; EXCEPTION WHEN NO_DATA_FOUND THEN ls_YBBHQZ:='990000000000'; --非医保项目的编码前缀 END; select SQ_ZY_TEMP00_ID0000.nextval into LS_ID0000 from dual; select SQ_ZY_TEMP00_ID0000.nextval into ad_ID0000 from dual; if as_FLAG00 in ('0','1') then OPEN CUR_ZY_FYMX00_MXID00; elsif as_FLAG00='2' then OPEN CUR_ZY_FYMX00_MXID01; elsif as_FLAG00='3' then OPEN CUR_ZY_FYMX00_MXID02; elsif as_FLAG00='4' then OPEN CUR_ZY_FYMX00_MXID03; elsif as_FLAG00 in ('5','6') then open CUR_ZY_FYMX00_DBZ000; end if; LOOP begin if as_FLAG00 in ('0','1') then fetch CUR_ZY_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_CZRQ00,ls_JSXM01,lv_YSZJHM,ls_YPTS00,ls_PCMC00,ls_CSL000, ls_JLDW00,ls_ZFFSBZ,LS_SFXMID,LS_GJBM00, LS_K2J000,--库存单位转化率 LS_M2J000,--门诊发药单位转化率 LS_Z2J000,--住院发药单位转化率 LS_YBDW00,--医保单位 LS_YB2J00 --医保转换率 ; EXIT WHEN CUR_ZY_FYMX00_MXID00%notfound; elsif as_FLAG00='2' then fetch CUR_ZY_FYMX00_MXID01 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_CZRQ00,ls_JSXM01,lv_YSZJHM,ls_YPTS00,ls_PCMC00,ls_CSL000, ls_JLDW00,ls_ZFFSBZ,LS_SFXMID,LS_GJBM00, LS_K2J000,--库存单位转化率 LS_M2J000,--门诊发药单位转化率 LS_Z2J000,--住院发药单位转化率 LS_YBDW00,--医保单位 LS_YB2J00 --医保转换率 ; EXIT WHEN CUR_ZY_FYMX00_MXID01%notfound; elsif as_FLAG00='3' then fetch CUR_ZY_FYMX00_MXID02 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_CZRQ00,ls_JSXM01,lv_YSZJHM,ls_YPTS00,ls_PCMC00,ls_CSL000, ls_JLDW00,ls_ZFFSBZ,LS_SFXMID,LS_GJBM00, LS_K2J000,--库存单位转化率 LS_M2J000,--门诊发药单位转化率 LS_Z2J000,--住院发药单位转化率 LS_YBDW00,--医保单位 LS_YB2J00 --医保转换率 ; EXIT WHEN CUR_ZY_FYMX00_MXID02%notfound; elsif as_FLAG00='4' then fetch CUR_ZY_FYMX00_MXID03 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_CZRQ00,ls_JSXM01,lv_YSZJHM,ls_YPTS00,ls_PCMC00,ls_CSL000, ls_JLDW00,ls_ZFFSBZ,LS_SFXMID,LS_GJBM00, LS_K2J000,--库存单位转化率 LS_M2J000,--门诊发药单位转化率 LS_Z2J000,--住院发药单位转化率 LS_YBDW00,--医保单位 LS_YB2J00 --医保转换率 ; EXIT WHEN CUR_ZY_FYMX00_MXID03%notfound; elsif as_FLAG00 in ('5','6') then fetch CUR_ZY_FYMX00_DBZ000 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_CZRQ00,ls_JSXM01,lv_YSZJHM,ls_YPTS00,ls_PCMC00,ls_CSL000, ls_JLDW00,ls_ZFFSBZ,LS_SFXMID,LS_GJBM00, LS_K2J000,--库存单位转化率 LS_M2J000,--门诊发药单位转化率 LS_Z2J000,--住院发药单位转化率 LS_YBDW00,--医保单位 LS_YB2J00 --医保转换率 ; EXIT WHEN CUR_ZY_FYMX00_DBZ000%notfound; end if; --取高值耗材项目ID if LS_FLAG00='0' then begin if LS_SFBZDY='Y' then--是否单病种打包 select SF_ZY_DBZGZHCID(ad_ZYID00,ad_DBZBM0,LS_XMBH00,LS_GJBM00) into LS_GZHCID from dual; LS_SFFSHC:='1'; select trim(B.HCDBBM) into LS_HCDBBM from BM_GZHCFL B where B.GZHCID=LS_GZHCID; else 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 nvl(B.SFDBZ0,'0')='0' and rownum=1; /* and ( (LS_SFBZDY='Y' and B.SFDBZ0='1' and B.XXZLXM=ad_DBZBM0) or (LS_SFBZDY='N' and nvl(B.SFDBZ0,'0')='0') ) */ end if; 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; if (XT_ZDYBYPMLXYSPBR>0) and ((ls_FLAG00='2') or (ls_FLAG00='1') ) then ls_SFTHML:=SF_XT_GETFYBMLBH('1',ls_brid00,ls_XMBH00,ad_ZYID00);--返回0不替换 1替换 else ls_SFTHML:=0; end if; as_SYSMSG:='MXID00='||to_char(ls_MXID00); --医保对应目录发生错误时， if ls_SFYBMLDYCWFSYBZX='Y' then select count(1) into ls_YBDYML_count from VW_BM_YBSFDY where YBZXLB = ls_YBZXLB and SFXMID = ls_XMBH00 and YBBRLB = ls_YBBRLB and SFYP00 = decode(ls_FLAG00,'0','N','Y') and YBXMBH is not null; if ls_YBDYML_count=0 then as_YHMSG0:='查找不到对应的医保发票项目,请与系统管理员联系!'; as_SYSMSG:=SQLERRM||as_SYSMSG; ROLLBACK; return; end if; end if; select a.XMMC00,a.BH0000,C.SFYBXM,nvl(C.SFSX00,0),D.XMMC00,C.YBXMBH,a.CBXMBH,C.ZFBL00,c.YBLB00,A.BZCBBH,A.BZCBMC,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_BZCBBH,LS_BZCBMC,LS_YBDDW0,LS_YBXDW0,LS_YBJSDJ,LS_YBZGDJ from BM_YBFPXM A,VW_BM_YBSFDY C,BM_YBSFXM D where a.BH0000 = C.YBZYFP and a.YBZXLB = C.YBZXLB and C.FBBH00 = a.FBBH00(+) 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 <> '1' ; --非门诊 --药品医保项目单位转化--begin--- if (LS_YBXJQY='Y') and (LS_FLAG00 in ('1','2')) and (LS_XMSL00<>0) 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_Z2J000,0)>0) then if trim(LS_DW0000)=trim(LS_YBDW00) then LS_DW0000:=LS_YBDW00; else --转成计量单位 LS_DW0000:=LS_YBDW00; LS_HJJE00:=LS_HJJE00; if nvl(LS_Z2J000,0) <> nvl(LS_YB2J00,0) then LS_XMDJ00 := round(LS_XMDJ00*LS_Z2J000/LS_YB2J00,4); LS_XMSL00 := round(LS_HJJE00/LS_XMDJ00); end if; --LS_XMSL00:=round((LS_XMSL00*LS_Z2J000)/LS_YB2J00,4); --LS_XMDJ00:=round(LS_HJJE00/LS_XMSL00,4); end if; end if; end if; end if; --药品医保项目单位转化--end--- -- by ZYSF-20130708-002 上限超额金额 if nvl(LS_YBXMSX,0)>0 then begin ls_YBSXCE:=round(ls_XMDJ00*ls_XMSL00,2)-nvl(ls_YBXMSX,0); exception when others then ls_YBSXCE:=0; end; else ls_YBSXCE:=0; --上限超额金额 end if; if ls_YBSXCE<0 then ls_YBSXCE:=0; end if; --by ZYSF-20130703-001 单病种参数开启后 ls_YBXMSX=0 VW_BM_YBSFDY.SFSX00 无效 --if LS_DBZJK0='M' then LS_YBXMSX:=0; end if; -- by ZYSF-20170206-001 if (LS_DBZJK0='M') and (ad_DBZBM0 is not null) and (ZY_DBZCEFKJS not in ('N')) then LS_YBXMSX:=0; end if; --2017.04.12 增加单病种结算病人并且ZY_DBZCEFKJS=N非床位设置上限不拆分 if (LS_DBZJK0='M') and (ad_DBZBM0 is not null) and (ZY_DBZCEFKJS in ('N')) and (trim(LS_FPXMMC) not in ('床位费')) and (trim(LS_FPXMBH) not in ('05')) then LS_YBXMSX:=0; end if; if LS_SFXMYBJK='Y' then ---YPTS00:用药天数 YPPL00:频率代码 YPYL00:剂量+单位 --非药品的项目频率编码‘9999’，用法填‘无’，天数填‘0’。 BY ZYSF-20130625-002 if ls_FLAG00 in ('1','2') 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; ls_YPTS00:=nvl(ls_YPTS00,0); if ls_YPPL00 is null then ls_YPPL00:='9999'; end if; if ls_YPYL00 is null then ls_YPYL00:='无'; end if; else --修正非厦门地区 三个为空默认值 药品用量=* 用药天数=1 使用频率=* BY ZYSF-20131220-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:='*'; 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; --2010.06.10 zhr 如果该医保中心只支持非医保项目99打头格式，那么就不能用70打头项目 if ls_FYBQZ0='99' then if substrb(ls_CBXMBH,1,2)='70' then ls_CBXMBH:=null; end if; if substrb(ls_YBXMBH,1,2)='70' then ls_YBXMBH:=null; end if; elsif ls_FYBQZ0='70' then if substrb(ls_YBXMBH,1,2)='99' then ls_YBXMBH:=null; end if; end if; --如果没有对应,查找BM_YBSFXM本发票记录有没有定义未对应或超标的项目医保接口使用的医保项目编号 -- 如果有定义,使用此定义的医保项目来生成接口文件,如果没有定义,使用'990000000000+医保发票编号方式 if ls_CBXMBH is not null then select a.XMMC00,a.ZYFPXM,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.ZYFPXM=b.BH0000 and b.YBZXLB=ls_YBZXLB and a.FBBH00=B.FBBH00(+); 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; --2017.03.15 zhangyc 单病种超标床位费 if LS_DBZBM0 is null then LS_BZCBBH:=null; LS_BZCBMC:=null; end if; if ( ls_YBXMBH is null) or (ls_SFTHML=1) or (ls_ZFFSBZ='Y') then --for BQ-20131018-001 ls_YBXMMC:=trim(ls_CBXMMC); ls_YBXMBH:=trim(ls_CBXMBH); ls_FPXMMC:=trim(ls_CBFPMC); ls_FPXMBH:=trim(ls_CBFPBH); end if; if ls_SFTHML=1 or ls_ZFFSBZ='Y' then -- ls_ZFFSBZ='Yfor BQ-20131018-001 ls_SFYBXM:='N'; ls_YBZFBL:=1; end if; ls_SFXYSHEBZ:='0'; if ls_JSXM01='2' and to_number(ls_XMDJSX)>0 and ls_XMDJ00>to_number(ls_XMDJSX) and SF_ZY_SFXYYBSP(ad_ZYID00)>0 then --超过1000的材料需要审批 ls_spje00:=0; ls_SFXYSHEBZ:='1'; begin select nvl(SPJE00,0) into ls_spje00 from YK_YBMLSP where ZYID00=ad_ZYID00 and SFXMID=ls_XMBH00 and to_char(sysdate+1,'YYYYMMDD')ls_spje00 then ls_YBXMSX:=ls_spje00; end if; end if; --特殊处理，如果对应的是以下几个医保项目,需要传递院内项目名称 if ls_YBXMBH in ('01670000000001','01670000000002','01670000000005','01670000000006','01670000000007','01670000000008') then ls_YBXMMC:=ls_XMMC00; end if; update BM_ZYCF00_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_ZYCF00_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 ZY_FYMX00 set ZFBL00=ls_YBZFBL where MXID00=ls_MXID00; elsif ls_FLAG00='0' then Update YF_ZYCFMX set ZFBL00=ls_YBZFBL,YBLB00=ls_YBXMLB where CFID00=ls_MXID00; else Update YF_YZYPSQ set ZFBL00=ls_YBZFBL where YPQLPC=ls_MXID00; end if; end if; --一次性材料项目格式是否改为 医保对应项目+(医院收费项目)---厦门同安医院=Y if (ls_YCXCLXMXGS='Y') and (ls_FLAG00='0') then select nvl(JSXM00,'0') into ls_JSXM00 FROM BM_YYSFXM where sfxmid=ls_XMBH00; if ls_JSXM00='2' then if ls_SFXMYBJK='Y' then --厦门医保接口 ls_YBXMMC:=substrb(ls_YBXMMC||'+('||ls_XMMC00||')',1,100); else ls_YBXMMC:=substrb(ls_YBXMMC||'('||ls_XMMC00||')',1,100); end if; 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_ZYCFMX_TEMP00(ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00, XMDJ00,XMSL00,XMJE00,YSXM00,ZFXMBH,CZRQ00,YSZJHM, YPTS00,YPPL00,YPYL00,YBSXCE,FLAG00,GZHCID,SFFSHC,SFXMID,HCDBXM) values(ls_ID0000,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_GG0000,nvl(ls_DW0000,'无'),ls_YBXMSX,ls_XMSL00,round(ls_YBXMSX*ls_XMSL00,2),ls_ZWXM00,substrb(ls_CBXMBH,1,60),ls_CZRQ00,LV_YSZJHM, ls_YPTS00,ls_YPPL00,ls_YPYL00,round((ls_XMDJ00-ls_YBXMSX)*ls_XMSL00,2),LS_FLAG00,LS_GZHCID,LS_SFFSHC,nvl(LS_SFXMID,0),'0'); --拆分项目 insert into BM_ZYCFMX_TEMP00(ID0000,XMBH00,SFYB00,FPXMMC,XMMC00, XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00, YSXM00,ZFXMBH,CZRQ00,YPTS00,YPPL00, YPYL00,YBSXCE,FLAG00,GZHCID,SFFSHC, SFXMID,HCDBXM) values(LS_ID0000,nvl(LS_BZCBBH,LS_CBXMBH),decode(LS_BZCBBH,null,'N','N'),decode(LS_SYFPBH,'Y',decode(LS_BZCBBH,null,LS_CBFPBH,LS_FPXMBH),decode(LS_BZCBBH,null,LS_CBFPMC,LS_FPXMMC)),nvl(LS_BZCBMC,LS_CBXMMC), LS_GG0000,nvl(ls_DW0000,'无'),(LS_XMDJ00-LS_YBXMSX),LS_XMSL00,round((LS_XMDJ00-LS_YBXMSX)*LS_XMSL00,2), LS_ZWXM00,substrb(nvl(LS_BZCBBH,LS_CBXMBH),1,60),LS_CZRQ00,LS_YPTS00,LS_YPPL00, LS_YPYL00,0,LS_FLAG00,LS_GZHCID,LS_SFFSHC, nvl(LS_SFXMID,0),'2' ); /* --2017.03.15 zhangyc 单病种超标床位费 if (LS_BZCBBH is not null) and ((trim(LS_FPXMMC)='床位费') or(trim(LS_FPXMBH)='05')) then LS_BZCBCW := LS_BZCBCW + round((LS_XMDJ00-LS_YBXMSX)*LS_XMSL00,2); end if; */ elsif ls_SFXYSHEBZ='1' and LS_SPJE00=0 then insert into BM_ZYCFMX_TEMP00(ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00,YSXM00,ZFXMBH,CZRQ00,YSZJHM, YPTS00,YPPL00,YPYL00,YBSXCE,FLAG00,GZHCID,SFFSHC,SFXMID,HCDBXM) values(ls_ID0000,ls_CBXMBH,'N',DECODE(ls_SYFPBH,'Y',ls_CBFPBH,ls_CBFPMC),ls_CBXMMC, ls_GG0000,ls_DW0000,ls_XMDJ00,ls_XMSL00,round(ls_XMDJ00*ls_XMSL00,2),ls_ZWXM00,substrb(ls_CBXMBH,1,60),ls_CZRQ00,LV_YSZJHM, ls_YPTS00,ls_YPPL00,ls_YPYL00,ls_YBSXCE,LS_FLAG00,LS_GZHCID,LS_SFFSHC,nvl(LS_SFXMID,0),'0'); else if as_FLAG00 in ('5','6') then --单病种拆分的 ls_XMDJ01:=round(ls_HJJE00/nvl(ls_XMSL00,1),2); ls_XMSL01:=ls_XMSL00; ls_XMJE01:=ls_HJJE00; else ls_XMDJ01:=ls_XMDJ00; ls_XMSL01:=ls_XMSL00; ls_XMJE01:=round(ls_XMDJ00*ls_XMSL00,2); end if; insert into BM_ZYCFMX_TEMP00(ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00,YSXM00,ZFXMBH,CZRQ00,YSZJHM, YPTS00,YPPL00,YPYL00,YBSXCE,FLAG00,GZHCID,SFFSHC,SFXMID,HCDBXM) values(ls_ID0000,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_GG0000,nvl(ls_DW0000,'无'),ls_XMDJ01,ls_XMSL01,ls_XMJE01,ls_ZWXM00,substrb(ls_CBXMBH,1,60),ls_CZRQ00,LV_YSZJHM, ls_YPTS00,ls_YPPL00,ls_YPYL00,ls_YBSXCE,LS_FLAG00,LS_GZHCID,LS_SFFSHC,nvl(LS_SFXMID,0),'0'); end if; EXCEPTION WHEN NO_DATA_FOUND THEN as_YHMSG0:='查找不到医保对应记录,请与系统管理员联系!'; if ls_FLAG00='0' then as_SYSMSG:=SQLERRM||'SFXMID='||to_char(ls_XMBH00)||'XMMC00='||ls_XMMC00||',MXID00='||to_char(ls_MXID00)||',ZYID00='||to_char(ad_ZYID00)||'查找不到医保对应记录!'; elsif ls_FLAG00='1' then as_SYSMSG:=SQLERRM||'YPNM00='||to_char(ls_XMBH00)||'XMMC00='||ls_XMMC00||',CFID00='||to_char(ls_MXID00)||',ZYID00='||to_char(ad_ZYID00)||'查找不到医保对应记录!'; elsif ls_FLAG00='2' then as_SYSMSG:=SQLERRM||'YPNM00='||to_char(ls_XMBH00)||'XMMC00='||ls_XMMC00||',YPQLPC='||to_char(ls_MXID00)||',ZYID00='||to_char(ad_ZYID00)||'查找不到医保对应记录!'; end if; ROLLBACK; RETURN; WHEN OTHERS THEN --说明是药品,注意有多条记录 as_YHMSG0:='医疗收费项目生成错误,请速与系统管理员联系!'; if ls_FLAG00='0' then as_SYSMSG:=SQLERRM||'SFXMID='||to_char(ls_XMBH00)||'XMMC00='||ls_XMMC00||',MXID00='||to_char(ls_MXID00)||',ZYID00='||to_char(ad_ZYID00)||'查找不到医保对应记录!'; elsif ls_FLAG00='1' then as_SYSMSG:=SQLERRM||'YPNM00='||to_char(ls_XMBH00)||'XMMC00='||ls_XMMC00||',CFID00='||to_char(ls_MXID00)||',ZYID00='||to_char(ad_ZYID00)||'查找不到医保对应记录!'; elsif ls_FLAG00='2' then as_SYSMSG:=SQLERRM||'YPNM00='||to_char(ls_XMBH00)||'XMMC00='||ls_XMMC00||',YPQLPC='||to_char(ls_MXID00)||',ZYID00='||to_char(ad_ZYID00)||'查找不到医保对应记录!'; end if; ROLLBACK; RETURN; END; END LOOP; if as_FLAG00 in ('0','1') then Close CUR_ZY_FYMX00_MXID00; elsif as_FLAG00 ='2' then Close CUR_ZY_FYMX00_MXID01; elsif as_FLAG00 ='3' then Close CUR_ZY_FYMX00_MXID02; elsif as_FLAG00 ='4' then Close CUR_ZY_FYMX00_MXID03; elsif as_FLAG00 in ('5','6') then Close CUR_ZY_FYMX00_DBZ000; end if; --农合病人实时结算 if ((ls_XNHCSLX ='Y') or (ls_XNHCSLX='YN' and ls_XNHSFRQ='Y')) and (trim(ls_YBID00)=trim(ls_YBKH00) or ls_SFXNH0='1') then --先把数量为负数的合并到正数上 2010.04.01 FOR XNH IN CUR_BM_ZYCFMX_TEMP00_XNH LOOP UPDATE BM_ZYCFMX_TEMP00 SET XMSL00=XMSL00+XNH.XMSL00,XMJE00=XMJE00+XNH.XMJE00 WHERE XMBH00=XNH.XMBH00 AND SFYB00=XNH.SFYB00 AND FPXMMC=XNH.FPXMMC AND XMGG00=XNH.XMGG00 AND XMDJ00=XNH.XMDJ00 AND ID0000=XNH.ID0000 AND XMSL00>=-XNH.XMSL00 and rownum=1; if NOT SQL%NOTFOUND then delete from BM_ZYCFMX_TEMP00 WHERE XMBH00=XNH.XMBH00 AND SFYB00=XNH.SFYB00 AND FPXMMC=XNH.FPXMMC AND XMGG00=XNH.XMGG00 AND XMDJ00=XNH.XMDJ00 AND XMSL00=XNH.XMSL00 AND ID0000=XNH.ID0000 AND CZRQ00=XNH.CZRQ00 and rownum=1; else --如果没找到,把该项目的操作日期全部改为退费日期,便于后面合并 2010.04.23 update BM_ZYCFMX_TEMP00 set CZRQ00=XNH.CZRQ00 where ID0000=XNH.ID0000 AND XMBH00=XNH.XMBH00 AND SFYB00=XNH.SFYB00 AND FPXMMC=XNH.FPXMMC AND XMGG00=XNH.XMGG00 AND XMDJ00=XNH.XMDJ00; end if; END LOOP; --2010.06.25 ZHR 处理XMSL00=0 ,XMJE00<>0 DECLARE cursor c_XMSL is select XMBH00,SUM(XMJE00) XMJE00 from BM_ZYCFMX_TEMP00 where ID0000 = ls_ID0000 and XMSL00=0 AND XMJE00<>0 GROUP BY XMBH00; BEGIN FOR XM IN c_XMSL LOOP UPDATE BM_ZYCFMX_TEMP00 SET XMJE00=XMJE00+XM.XMJE00 WHERE ID0000 = ls_ID0000 and XMSL00<>0 AND XMBH00=XM.XMBH00 AND ROWNUM=1; END LOOP; DELETE FROM BM_ZYCFMX_TEMP00 where ID0000 = ls_ID0000 and XMSL00=0; END; insert into BM_ZYCFMX_TEMP00(ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00, YSXM00,ZFXMBH,CZRQ00,YSZJHM,YPTS00,YPPL00,YPYL00,YBSXCE,FLAG00,GZHCID, SFFSHC,SFXMID,HCDBXM) select ad_ID0000,XMBH00,SFYB00,FPXMMC,TRIM(XMMC00),XMGG00,MAX(XMDW00),XMDJ00,nvl(sum(XMSL00),0),NVL(sum(XMJE00),0), max(YSXM00),max(ZFXMBH),CZRQ00,max(YSZJHM),max(YPTS00),max(YPPL00),max(YPYL00),NVL(sum(YBSXCE),0), max(FLAG00),max(GZHCID),max(SFFSHC),max(SFXMID),max(HCDBXM) from BM_ZYCFMX_TEMP00 where ID0000 = ls_ID0000 group by XMBH00,SFYB00,FPXMMC,XMGG00,XMDJ00,TRIM(XMMC00),CZRQ00; --判断是否超过2000条记录，如果超过，只按同一天日期重新汇总 select count(1) into ls_COUNT0 from BM_ZYCFMX_TEMP00 where ID0000=ad_ID0000; if ls_COUNT0>=2000 then delete from BM_ZYCFMX_TEMP00 where ID0000=ad_ID0000; insert into BM_ZYCFMX_TEMP00(ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00, YSXM00,ZFXMBH,CZRQ00,YSZJHM,YPTS00,YPPL00,YPYL00,YBSXCE,FLAG00,GZHCID, SFFSHC,SFXMID,HCDBXM) select ad_ID0000,XMBH00,SFYB00,FPXMMC,TRIM(XMMC00),XMGG00,MAX(XMDW00),XMDJ00,nvl(sum(XMSL00),0),NVL(sum(XMJE00),0), max(YSXM00),max(ZFXMBH),ls_CZRQ00,max(YSZJHM),max(YPTS00),max(YPPL00),max(YPYL00),NVL(sum(YBSXCE),0),max(FLAG00),max(GZHCID), max(SFFSHC),max(SFXMID),max(HCDBXM) from BM_ZYCFMX_TEMP00 where ID0000 = ls_ID0000 group by XMBH00,SFYB00,FPXMMC,XMGG00,XMDJ00,TRIM(XMMC00); end if; else --进行汇总 insert into BM_ZYCFMX_TEMP00(ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00, YSXM00,ZFXMBH,YSZJHM,YPTS00,YPPL00,YPYL00,YBSXCE,FLAG00,GZHCID,SFFSHC,SFXMID,HCDBXM) select ad_ID0000,XMBH00,SFYB00,FPXMMC,TRIM(XMMC00),XMGG00,MAX(XMDW00),XMDJ00,nvl(sum(XMSL00),0),NVL(sum(XMJE00),0), max(YSXM00),max(ZFXMBH),max(YSZJHM),max(YPTS00),max(YPPL00),max(YPYL00),NVL(sum(YBSXCE),0),max(FLAG00),max(GZHCID),max(SFFSHC),max(SFXMID),max(HCDBXM) from BM_ZYCFMX_TEMP00 where ID0000 = ls_ID0000 group by XMBH00,SFYB00,FPXMMC,XMGG00,XMDJ00,TRIM(XMMC00); end if; update BM_ZYCFMX_TEMP00 set XMDJ00 = round(XMJE00/XMSL00,6) where XMJE00 <> (XMDJ00 * XMSL00) and ID0000 = ad_ID0000 and XMSL00<>0; if ls_DJSYXJ = 'Y' then --由于泉州医保要求单价*数量与金额要严格相同,所以本处添加了处理 update BM_ZYCFMX_TEMP00 set XMDJ00 = XMJE00,XMSL00 = 1 where XMJE00 <> (XMDJ00 * XMSL00) and ID0000 = ad_ID0000; end if; insert into BM_ZYCF00_TEMP00(ID0000,NAME00,BZ0000,VALUE0) select ad_ID0000,NAME00,BZ0000,NVL(sum(VALUE0),0) from BM_ZYCF00_TEMP00 where ID0000 = ls_ID0000 group by NAME00,BZ0000; --delete from bm_zycfmx_temp00 where id0000= ad_id0000 and (nvl(xmje00,0)=0; delete from bm_zycfmx_temp00 where id0000= ad_id0000 and (nvl(xmje00,0)=0 or nvl(xmsl00,0)=0); --2015.05.29 by ZYSF-20150227-001 判断是否有没有负数的记录--begin select count(1) into ls_COUNT0 from BM_ZYCFMX_TEMP00 where id0000=ad_id0000 and nvl(xmje00,0)<0; if (ls_COUNT0>=1) then select SQ_ZY_TEMP00_ID0000.nextval into LS_NewID0 from dual; --插入非负数项目编码 insert into BM_ZYCFMX_TEMP00(ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00, XMDJ00,XMSL00,XMJE00,YSXM00,ZFXMBH,YSZJHM,YPTS00, YPPL00,YPYL00,YBSXCE,FLAG00,GZHCID,SFFSHC,SFXMID,HCDBXM) select LS_NewID0,XMBH00,SFYB00,FPXMMC,TRIM(XMMC00),XMGG00,MAX(XMDW00), XMDJ00,nvl(sum(XMSL00),0),NVL(sum(XMJE00),0),max(YSXM00),max(ZFXMBH),max(YSZJHM),max(YPTS00), max(YPPL00),max(YPYL00),NVL(sum(YBSXCE),0),max(FLAG00),max(GZHCID),max(SFFSHC),max(SFXMID),max(HCDBXM) from BM_ZYCFMX_TEMP00 where ID0000 = ad_id0000 and (XMBH00,FPXMMC,XMMC00) not in (select XMBH00,FPXMMC,XMMC00 from BM_ZYCFMX_TEMP00 where id0000=ad_id0000 and nvl(xmje00,0)<0) group by XMBH00,SFYB00,FPXMMC,XMGG00,XMDJ00,trim(XMMC00); --插入负数项目编码一致合并金额后重新算单价 insert into BM_ZYCFMX_TEMP00(ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00, XMDJ00,XMSL00,XMJE00,YSXM00,ZFXMBH,YSZJHM,YPTS00, YPPL00,YPYL00,YBSXCE,FLAG00,GZHCID,SFFSHC,SFXMID,HCDBXM) select LS_NewID0,XMBH00,max(SFYB00),FPXMMC,trim(XMMC00),max(XMGG00),max(XMDW00), round(decode(nvl(sum(XMSL00),0),0,0,NVL(sum(XMJE00),0)/nvl(sum(XMSL00),0)),4)XMDJ00, nvl(sum(XMSL00),0),NVL(sum(XMJE00),0),max(YSXM00),max(ZFXMBH),max(YSZJHM),max(YPTS00), max(YPPL00),max(YPYL00),NVL(sum(YBSXCE),0),max(FLAG00),max(GZHCID),max(SFFSHC),max(SFXMID),max(HCDBXM) from BM_ZYCFMX_TEMP00 where ID0000 = ad_id0000 and (XMBH00,FPXMMC,XMMC00) in (select XMBH00,FPXMMC,XMMC00 from BM_ZYCFMX_TEMP00 where id0000=ad_id0000 and nvl(xmje00,0)<0) group by XMBH00,FPXMMC,trim(XMMC00); delete from BM_ZYCFMX_TEMP00 where id0000=LS_NewID0 and (nvl(xmje00,0)=0 or nvl(xmsl00,0)=0); --插入主表数据 if trim(ls_SYFPBH)='Y' then insert into BM_ZYCF00_TEMP00(ID0000,NAME00,BZ0000,VALUE0) select LS_NewID0,B.XMMC00,A.SFYB00,NVL(sum(A.XMJE00),0) from BM_ZYCFMX_TEMP00 a,bm_ybfpxm b where ID0000 = LS_NewID0 AND trim(A.FPXMMC)=trim(B.BH0000) group by B.XMMC00,A.SFYB00; else insert into BM_ZYCF00_TEMP00(ID0000,NAME00,BZ0000,VALUE0) select LS_NewID0,FPXMMC,SFYB00,NVL(sum(XMJE00),0) from BM_ZYCFMX_TEMP00 where ID0000 = LS_NewID0 group by FPXMMC,SFYB00; end if; --删除旧数据 delete from BM_ZYCF00_TEMP00 where id0000=ad_ID0000; delete from BM_ZYCFMX_TEMP00 where id0000=ad_ID0000; ad_id0000:=LS_NewID0; end if; --2015.05.29 by ZYSF-20150227-001 判断是否有没有负数的记录--end-- if as_FLAG00 in ('2','3') then delete from BM_ZYCFMX_TEMP00 where id0000=ad_id0000 and ((nvl(xmje00,0)<=0) or (nvl(XMSL00,0)<=0)); end if; if ZY_SFASXJS='Y' and as_FLAG00 in ('0','1') then FOR MX IN CUR_BM_ZYCFMX_TEMP00 LOOP ls_ZYZFSX_TEMP:=ls_ZYZFSX_TEMP+MX.XMJE00; if ls_ZYZFSX_TEMP>ls_ZYZFSX then UPDATE BM_ZYCFMX_TEMP00 SET XMBH00=ZFXMBH,SFYB00='N' WHERE XMBH00=MX.XMBH00 AND SFYB00=MX.SFYB00 AND FPXMMC=MX.FPXMMC AND XMGG00=MX.XMGG00 AND XMDJ00=MX.XMDJ00 AND ID0000=MX.ID0000; end if; END LOOP; Delete from BM_ZYCF00_TEMP00 where ID0000 = ad_ID0000; if trim(ls_SYFPBH)='Y' then insert into BM_ZYCF00_TEMP00(ID0000,NAME00,BZ0000,VALUE0) select ad_ID0000,B.XMMC00,A.SFYB00,NVL(sum(A.XMJE00),0) from BM_ZYCFMX_TEMP00 a,bm_ybfpxm b where ID0000 = AD_ID0000 AND trim(A.FPXMMC)=trim(B.BH0000) group by B.XMMC00,A.SFYB00; else insert into BM_ZYCF00_TEMP00(ID0000,NAME00,BZ0000,VALUE0) select ad_ID0000,FPXMMC,SFYB00,NVL(sum(XMJE00),0) from BM_ZYCFMX_TEMP00 where ID0000 = AD_ID0000 group by FPXMMC,SFYB00; end if; end if; ---ls_MZSFYSZJHM=Y 费用明细中传送医生的证件号于标题头中的医生证件号一致 BY ZYSF-20120410-001 if (ls_MZSFYSZJHM='Y') and (trim(ls_YSZJHM) is not null) then Update BM_ZYCFMX_TEMP00 Set YSXM00=ls_YSZJHM where ID0000=ls_ID0000; Update BM_ZYCFMX_TEMP00 Set YSXM00=ls_YSZJHM where ID0000=ad_ID0000; end if; Select max(YSZJHM) into lv_MAXYSZJH from BM_ZYCFMX_TEMP00 where ID0000=ls_ID0000 and YSZJHM is not null; if lv_MAXYSZJH is null then lv_MAXYSZJH:='无'; end if; update BM_ZYCFMX_TEMP00 set YSZJHM=lv_MAXYSZJH where ID0000=LS_ID0000 and nvl(trim(YSZJHM),'0') in ('0'); update BM_ZYCFMX_TEMP00 set YPPL00='*' where ID0000= ls_ID0000 and nvl(YPPL00,'0') in ('0'); update BM_ZYCFMX_TEMP00 set YPYL00='*' where ID0000= ls_ID0000 and nvl(YPYL00,'0') in ('0'); Select max(YSZJHM) into lv_MAXYSZJH from BM_ZYCFMX_TEMP00 where ID0000=ad_ID0000 and nvl(YSZJHM,'0') not in ('0'); if lv_MAXYSZJH is null then lv_MAXYSZJH:='无'; end if; update BM_ZYCFMX_TEMP00 set YSZJHM=lv_MAXYSZJH where ID0000=ad_ID0000 and nvl(trim(YSZJHM),'0') in ('0'); update BM_ZYCFMX_TEMP00 set YPPL00='*' where ID0000= ad_ID0000 and nvl(YPPL00,'0') in ('0'); update BM_ZYCFMX_TEMP00 set YPYL00='*' where ID0000= ad_ID0000 and nvl(YPYL00,'0') in ('0'); Delete from BM_ZYCFMX_TEMP00 where ID0000 = LS_ID0000; Delete from BM_ZYCF00_TEMP00 where ID0000 = LS_ID0000; select trim(max(XMMC00)) into ls_XMMC00 from BM_ZYCFMX_TEMP00 where ID0000 = ad_ID0000 and XMSL00<0; if ls_XMMC00 is not null then as_YHMSG0:='项目“'||ls_XMMC00||'”数量为负数，请与系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_ZY_YBJK00_FJ0000('||to_char(ad_QSDJH0)||','||to_char(ad_ZZDJH0)||','||as_YBMC00||',ad_ID0000,as_YHMSG0,as_SYSMSG)',1,150); ROLLBACK; RETURN; end if; --高值耗材打包或单病种耗材打包--begin-- --LS_SFBZDY:是否单病种打包 Y:是 N：否 LS_HCJKQY:高值耗材接口启用 Y:是 N：否 if (LS_HCJKQY='Y') or (LS_SFBZDY='Y') then select count(1)into LS_COUNT0 from BM_ZYCFMX_TEMP00 where ID0000=ad_ID0000 and GZHCID>0 and nvl(SFFSHC,'1')='1' and nvl(HCDBXM,'0')='0'; --插入耗材打包项目 if LS_COUNT0>0 then insert into BM_ZYCFMX_TEMP00(ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00, YSXM00,ZFXMBH,YSZJHM,YPTS00,YPPL00,YPYL00,YBSXCE,FLAG00,GZHCID,SFFSHC, HCDBXM,SFXMID) select ad_ID0000,max(XMBH00),max(SFYB00),max(FPXMMC),max(XMMC00),max(XMGG00),MAX(XMDW00),sum(XMJE00),1,sum(XMJE00), max(YSXM00),max(ZFXMBH),max(YSZJHM),max(YPTS00),max(YPPL00),max(YPYL00),sum(YBSXCE),3,GZHCID,'1', '1',max(SFXMID) from BM_ZYCFMX_TEMP00 where ID0000 = ad_ID0000 and GZHCID>0 and nvl(SFFSHC,'1')='1' and nvl(HCDBXM,'0')='0' group by GZHCID; --修改打包项目的编码 --医保发票项目直接使用"其他费" update BM_ZYCFMX_TEMP00 A set (A.XMBH00,A.XMDW00,A.XMMC00,A.FPXMMC,A.SFYB00) =(select B.HCDBBM,nvl(B.DW0000,'无'),B.HCDBMC,'其他费','Y' from BM_GZHCFL B where B.GZHCID=A.GZHCID and rownum=1) Where ID0000 = ad_ID0000 and A.GZHCID>0 and nvl(A.SFFSHC,'1')='1' and nvl(A.HCDBXM,'0')='1'; --2016.05.26 修改打包项目的发票项目名称 update BM_ZYCFMX_TEMP00 A set (FPXMMC,SFYB00) =(select nvl(max(D.XMMC00),A.FPXMMC),nvl(max(C.SFYBXM),A.SFYB00) from BM_YBSFXM C,BM_YBFPXM D where C.YBZXLB=ls_YBZXLB and C.FBBH00=ls_FBBH00 and C.XMBH00=A.XMBH00 and C.YBZXLB=D.YBZXLB and C.FBBH00=D.FBBH00 and C.ZYFPXM=D.BH0000) Where ID0000 = ad_ID0000 and A.GZHCID>0 and nvl(A.SFFSHC,'1')='1' and nvl(A.HCDBXM,'0')='1'; --把对应的明细数量和金额改成0 update BM_ZYCFMX_TEMP00 set XMDJ00=0,XMJE00=0 where ID0000 = ad_ID0000 and GZHCID>0 and nvl(SFFSHC,'1')='1' and nvl(HCDBXM,'0')='0'; end if; --select sum(XMJE00) into LS_BZLSHC from BM_ZYCFMX_TEMP00 where ID0000 = ad_ID0000 and GZHCID>0 and nvl(SFFSHC,'1')='1' and nvl(HCDBXM,'0')='1'; end if; --高值耗材打包或单病种耗材打包--end-- --把收费项目名称加(院外)只修改项目 update BM_ZYCFMX_TEMP00 set XMMC00=substrb(trim(XMMC00)||SF_SF_YWJZMC(SFXMID),1,100) where ID0000=ad_ID0000 and FLAG00='0'; --标志 '0'费用,'1'零散处方,'2'医嘱处方; update BM_ZYCFMX_TEMP00 set YPYL00=substr(trim(YPYL00),1,6) where ID0000=ad_ID0000; --zhangyc 2017.07.04 修正超过最高限价拆分--begin-- if (LS_ZGXJCF='Y') and (LS_ZGXJID>0) then --先按重新合并数据 insert into BM_ZYCFMX_TEMP00(ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00, YSXM00,ZFXMBH,YSZJHM,YPTS00,YPPL00,YPYL00,YBSXCE,FLAG00, GZHCID,SFFSHC,SFXMID,HCDBXM) select ad_ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00,nvl(sum(XMSL00),0),NVL(sum(XMJE00),0), max(YSXM00),substrb('@'||trim(max(ZFXMBH)),1,30),max(YSZJHM),max(YPTS00),max(YPPL00),max(YPYL00),NVL(sum(YBSXCE),0),max(FLAG00),max(GZHCID),max(SFFSHC),max(SFXMID),max(HCDBXM) from BM_ZYCFMX_TEMP00 where ID0000 = ad_ID0000 group by XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00; delete BM_ZYCFMX_TEMP00 where ID0000 = ad_ID0000 and substrb(ZFXMBH,1,1)='@'; update BM_ZYCFMX_TEMP00 set ZFXMBH=substrb(ZFXMBH,2,30) where ID0000 = ad_ID0000; --开始循环执行--begin-- for CUR in CUR_YBZGXJ loop LS_ZGXJDJ:=0; --最高限价单价 LS_XJCEDJ:=0; --药品超过最高限价单价 LS_XJCEJE:=0; --药品超过最高限价金额 LS_ZGXJJE:=0; --药品最高限价金额 LS_YBDDW0:=null; --医保大单位 LS_YBZGDJ:=0; --医保大单位最高限价 LS_YBXDW0:=null; --医保小单位 LS_YBZGDJ1:=0; --医保小单位最高限价 begin select D.XMDW00,D.XMDW01,nvl(D.YBZGDJ,0)YBZGDJ,nvl(D.YBZGDJ1,0)YBZGDJ1 into LS_YBDDW0,LS_YBXDW0,LS_YBZGDJ,LS_YBZGDJ1 from BM_YBFPXM A,VW_BM_YBSFDY C,BM_YBSFXM D where a.BH0000 = C.YBZYFP and a.YBZXLB = C.YBZXLB and C.FBBH00 = a.FBBH00(+) and C.YBZXLB = D.YBZXLB(+) and C.YBXMBH = D.XMBH00(+) and C.YBBRLB = LS_YBBRLB and a.YBZXLB = LS_YBZXLB and C.SFXMID = CUR.SFXMID and C.SFYP00 = 'Y' and a.SYBZ00 <> '1' ; exception when others then LS_YBDDW0:=null; LS_YBXDW0:=null; LS_YBZGDJ:=0; LS_YBZGDJ1:=0; end; --开始执行单位判断--begin-- if trim(CUR.XMDW00)=trim(LS_YBDDW0) then --医保大单 or (trim(LS_DW0000)=trim(LS_YBXDW0)) then --发药=医保大单位或小单位 LS_ZGXJDJ:=nvl(LS_YBZGDJ,0); elsif trim(CUR.XMDW00)=trim(LS_YBXDW0) then --小单位 LS_ZGXJDJ:=nvl(LS_YBZGDJ1,0); else LS_ZGXJDJ:=0; end if; --开始执行单位判断--end-- if (nvl(LS_ZGXJDJ,0)>0)and (nvl(CUR.XMDJ00,0)>0)and (nvl(CUR.XMSL00,0)>0) and (nvl(CUR.XMDJ00,0)>nvl(LS_ZGXJDJ,0)) then LS_ZGXJJE:=round(nvl(LS_ZGXJDJ,0)*nvl(CUR.XMSL00,0),2); --最高限价*总数据 if round(nvl(CUR.XMJE00,0),2)=round(nvl(LS_ZGXJJE,0),2) then --如何合计金额=最高限价的合计金额(保留两位小数位) 直接修改单价为最高限价 update BM_ZYCFMX_TEMP00 set XMDJ00=LS_ZGXJDJ where ID0000 = CUR.ID0000 and SFXMID = CUR.SFXMID and XMBH00=CUR.XMBH00 and XMMC00=CUR.XMMC00; else if round(nvl(CUR.XMJE00,0),2)>round(nvl(LS_ZGXJJE,0),2) then --如果合计金额>最高限价金额 拆分数据 begin select a.XMMC00,a.BH0000,C.SFYBXM,nvl(C.SFSX00,0),D.XMMC00,C.YBXMBH,a.CBXMBH,C.ZFBL00,c.YBLB00,A.BZCBBH,A.BZCBMC,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_BZCBBH,LS_BZCBMC,LS_YBDDW0,LS_YBXDW0,LS_YBJSDJ,LS_YBZGDJ from BM_YBFPXM A,VW_BM_YBSFDY C,BM_YBSFXM D where a.BH0000 = C.YBZYFP and a.YBZXLB = C.YBZXLB and C.FBBH00 = a.FBBH00(+) and C.YBBRLB = LS_YBBRLB and C.SFXMID = LS_ZGXJID and C.YBZXLB = D.YBZXLB(+) and C.YBXMBH = D.XMBH00(+) and C.SFYP00 = decode('0','0','N','Y')--是否药品,'0'费用,'1'零散处方,'2'医嘱处方 and a.YBZXLB = LS_YBZXLB and a.SYBZ00 <> '1' ; --非门诊 exception when others then AS_YHMSG0:='SFXMID:='||LS_ZGXJID||' 医保收费未对应!'; AS_SYSMSG:=SQLERRM||as_SYSMSG; rollback; end; --修改单价为最高限价 update BM_ZYCFMX_TEMP00 set XMDJ00=LS_ZGXJDJ,XMJE00=LS_ZGXJJE where 1=1 and ID0000=CUR.ID0000 and SFXMID=CUR.SFXMID and XMBH00=CUR.XMBH00 and XMMC00=CUR.XMMC00 and XMSL00=CUR.XMSL00 and XMDJ00=CUR.XMDJ00 and XMGG00=CUR.XMGG00 and XMDW00=CUR.XMDW00; --处理超过限额的数据 LS_XJCEJE:=round(nvl(CUR.XMJE00,0),2)-round(nvl(LS_ZGXJJE,0),2); --药品超过最高限价金额 LS_XJCFSL:=1; --LS_XJCFSL:=CUR.XMSL00; LS_XJCEDJ:=round(nvl(LS_XJCEJE,0)/nvl(LS_XJCFSL,0),4); --药品超过最高限价单价 insert into BM_ZYCFMX_TEMP00( ID0000,XMBH00,SFYB00,FPXMMC,XMMC00, XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00, YSXM00,ZFXMBH,CZRQ00,YPTS00,YPPL00, YPYL00,YBSXCE,FLAG00,GZHCID,SFFSHC, SFXMID,HCDBXM,YSZJHM) values(CUR.ID0000,LS_YBXMBH,LS_SFYBXM,decode(LS_SYFPBH,'Y',LS_FPXMBH,LS_FPXMMC),LS_YBXMMC, null,nvl(LS_YBDDW0,'无'),LS_XJCEDJ,LS_XJCFSL,LS_XJCEJE, CUR.YSXM00,substrb(LS_YBXMBH,1,60),CUR.CZRQ00,null,null, null,0,'0','0',LS_SFFSHC, nvl(LS_ZGXJID,0),'2',CUR.YSZJHM ); end if; end if; end if; end loop; --重新整理发票合计 delete BM_ZYCF00_TEMP00 where ID0000=ad_ID0000; if trim(ls_SYFPBH)='Y' then insert into BM_ZYCF00_TEMP00(ID0000,NAME00,BZ0000,VALUE0) select ad_ID0000,B.XMMC00,A.SFYB00,NVL(sum(A.XMJE00),0) from BM_ZYCFMX_TEMP00 a,bm_ybfpxm b where ID0000 = ad_ID0000 AND trim(A.FPXMMC)=trim(B.BH0000) group by B.XMMC00,A.SFYB00; else insert into BM_ZYCF00_TEMP00(ID0000,NAME00,BZ0000,VALUE0) select ad_ID0000,FPXMMC,SFYB00,NVL(sum(XMJE00),0) from BM_ZYCFMX_TEMP00 where ID0000 = ad_ID0000 group by FPXMMC,SFYB00; end if; --开始循环执行--end-- end if; --zhangyc 2017.07.04 修正超过最高限价拆分--end-- --插入住院医保发送明细表ZY_YBFSMX insert into ZY_YBFSMX( ID0000,CZRQ00,CZSJ00,ZYID00,JZDH00,FPXMMC,SFYB00,SFXMID,XMBH00,XMMC00, XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00,YSXM00,ZFXMBH,YSZJHM,YPTS00,YPPL00, YPYL00,YBSXCE,FLAG00,GZHCID,SFFSHC,HCDBXM ) select ID0000,lv_CZRQ00,lv_CZSJ00,ad_ZYID00,0,trim(FPXMMC),SFYB00,SFXMID,XMBH00,XMMC00, XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00,YSXM00,ZFXMBH,YSZJHM,YPTS00,YPPL00, YPYL00,YBSXCE,FLAG00,GZHCID,SFFSHC,HCDBXM from BM_ZYCFMX_TEMP00 where ID0000=ad_ID0000; --2017.03.15 单病种修改 by ZYSF-20170206-001 if LS_DBZBM0 is not null then select SF_ZY_DBZCBFY(ad_ZYID00,LS_BRID00,LS_DBZBM0,ad_ID0000,0) into LS_BZCBCW from dual;--超标床位费 select SF_ZY_DBZCBFY(ad_ZYID00,LS_BRID00,LS_DBZBM0,ad_ID0000,1) into LS_BZLSHC from dual;--除外耗材费 end if; update ZY_BRZHXX set DBZCWF=nvl(LS_BZCBCW,0),DBZHCF=nvl(LS_BZLSHC,0) where BRID00=LS_BRID00 and JFLBID = 2; exception WHEN NO_DATA_FOUND THEN as_YHMSG0:='查找不到对应的医保发票项目,请与系统管理员联系!'; as_SYSMSG:=SQLERRM||as_SYSMSG; ROLLBACK; WHEN OTHERS THEN as_YHMSG0:='出错原因未知！请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_ZY_YBJK00_FJ0000('||to_char(ad_QSDJH0)||','||to_char(ad_ZZDJH0)||','||as_YBMC00||',ad_ID0000,as_YHMSG0,as_SYSMSG)',1,150); ROLLBACK; end; / show error; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --modify end.