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调用 as_YHMSG0 OUT VARCHAR, --存储过程提示的错误信息 as_SYSMSG OUT VARCHAR --系统提示的错误信息 ) as --更改结帐单号为单据号,更改YF_YZYPSQ游标的内容 ls_BRID00 BM_BRXXB0.BRID00%TYPE; --病人ID ls_YBBRLB BM_BRXXB0.YBBRLB%TYPE; --医保病人类别 ls_YBLB00 BM_BRXXB0.YBLB00%TYPE; --医保类别 ls_TYDWMC BM_BRXXB0.TYDWMC%TYPE; --病人单位 ls_FBBH00 BM_BRXXB0.FBBH00%TYPE; --病人费别 ls_SFMLXZ BM_YBBRLB.SFMLXZ%TYPE; --是否受目录限制,0:不受目录限制,1:受目录限制 ls_ZYZFSX BM_YBBRLB.ZYZFSX%type; --住院收费上限 ls_CWXMBH BM_YBBRLB.CWXMBH%type; --医保中心为各类病人指定新增的床位项目编号 ls_BRLBMC BM_YBBRLB.BRLBMC%type; --医保病人类别 ls_CWSXJE BM_YBSFXM.SFSX00%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_YBXMGG BM_YBSFXM.YPGG00%TYPE; --医保项目规格 ls_CBXMBH BM_YBFPXM.CBXMBH%TYPE; --超标项目编号 ls_CBXMMC BM_YBSFXM.XMMC00%TYPE; --超标项目名称 ls_CBFPBH BM_YBFPXM.BH0000%TYPE; --超标医保发票项目编号 ls_CBFPMC BM_YBFPXM.XMMC00%TYPE; --超标发票项目名称 ls_SFYBXM BM_YBSFDY.SFYBXM%TYPE; --是否医保项目 ls_YBXMLB BM_YBSFDY.YBLB00%TYPE; --医保项目类别 ls_ZWXM00 BM_YGBM00.ZWXM00%TYPE; --医生姓名 ls_ID0000 BM_ZYCF00_TEMP00.ID0000%TYPE; ls_YBZFBL ZY_FYMX00.ZFBL00%TYPE; --医保自付比例 ls_MXID00 ZY_FYMX00.MXID00%TYPE; --费用明细ID ls_XMBH00 ZY_FYMX00.XMBH00%TYPE; --费用项目编号 ls_XMMC00 ZY_FYMX00.XMMC00%TYPE; --费用项目名称 ls_HJJE00 ZY_FYMX00.HJJE00%TYPE; --费用合计金额 ls_ZFJE00 ZY_FYMX00.ZFJE00%TYPE; --费用合计金额 ls_ZFBL00 ZY_FYMX00.ZFBL00%TYPE; --自付比例 ls_XMDJ00 ZY_FYMX00.XMDJ00%TYPE; --项目单价 ls_GG0000 ZY_FYMX00.XMGG00%TYPE; --项目规格 ls_DW0000 ZY_FYMX00.XMDW00%TYPE; --项目单位 ls_XMSL00 ZY_FYMX00.XMSL00%TYPE; --项目数量 ls_BCBZ00 number(5); --包床标志 ls_ZYZFSX_TEMP bm_ybbrlb.ZYZFSX%type; ls_YBMCFS CHAR(1); --是否按医保项目名称发送医保接口 ls_YBBHQZ VARCHAR2(20); --医保编码前缀 ls_DJSYXJ CHAR(1); ls_COUNT0 NUMBER(4); ls_SYFPBH Char(1); --医保接口使用发票编号来传送 ls_CGSXSFCF CHAR(1); --收费项目的金额超过上限是否进行拆分 ls_FLAG00 CHAR(1); --标志 '0'费用,'1'零散处方,'2'医嘱处方 ZY_SFASXJS CHAR(2); --医保住院是否按上限结算 ls_XNHCSLX VARCHAR2(2); --农合病人是否实时结算 ls_XNHSFRQ VARCHAR2(2); --是否传送费用日期 ls_CZRQ00 ZY_FYMX00.CZRQ00%TYPE; --操作日期 ls_YBID00 BM_BRXXB0.YBID00%TYPE; --医保ID ls_YBKH00 BM_BRXXB0.YBKH00%TYPE; --医保卡号 ls_SFJE00 BM_YYSFXM.SFJE00%type; ls_SFCYGDDJ varchar2(1); --是否采用固定单价发送 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,INSTRB(a.XMMC00,'(包)')BCBZ00,--包床标志 substrb(nvl(c.GG0000,'无'),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,a.XMDJ00 SFJE00 from ZY_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,ZY_BRFY00 D,BM_YGBM00 E where d.ybfsdh='0' and (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 union all select '1'FLAG00,a.CFID00,round(a.LSDJ00*a.YPZSL0*100)/100,substrb(e.YPMC00,1,50)YPMC00,0, substrb(nvl(e.YPGG00,'无'),1,10) YPGG00,nvl(a.CFDW00,'无')CFDW00,a.YPNM00,c.YSXM00,a.LSDJ00,a.YPZSL0, round(a.LSDJ00*a.YPZSL0*100)/100 YPHJJE,a.ZFBL00,d.CZRQ00,0 SFJE00 from YF_ZYCFMX A,YF_ZYCF00 c,ZY_BRFY00 d,BM_YD0000 e WHERE d.ybfsdh='0' and (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'--是否自备药 union all select '2'FLAG00,a.YPQLPC,round(a.LSDJ00*a.YPZSL0*100)/100,substrb(d.YPMC00,1,50)YPMC00,0, substrb(nvl(d.YPGG00,'无'),1,10) YPGG00,nvl(a.QLDW00,'无')CFDW00,a.YPNM00,e.ZWXM00,a.LSDJ00,a.YPZSL0, round(a.LSDJ00*a.YPZSL0*100)/100 YPHJJE,a.ZFBL00,b.CZRQ00,0 SFJE00 from YF_YZYPSQ A,ZY_BRFY00 b,BM_YD0000 d,BM_YGBM00 E where b.ybfsdh='0' and (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; 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,INSTRB(a.XMMC00,'(包)')BCBZ00,--包床标志 substrb(nvl(c.GG0000,'无'),1,10) GG0000,nvl(c.DW0000,'无')DW0000,a.XMBH00,trim(substr(e.ZWXM00,1,4)) 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,a.XMDJ00 SFJE00 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 union all select '1'FLAG00,a.CFID00,round(a.LSDJ00*a.YPZSL0*100)/100,substrb(e.YPMC00,1,50)YPMC00,0, substrb(nvl(e.YPGG00,'无'),1,10) YPGG00,nvl(a.CFDW00,'无')CFDW00,a.YPNM00,trim(substr(c.YSXM00,1,4)) YSXM00,a.LSDJ00,a.YPZSL0, round(a.LSDJ00*a.YPZSL0*100)/100 YPHJJE,a.ZFBL00,d.CZRQ00,0 SFJE00 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'--是否自备药 union all select '2'FLAG00,a.YPQLPC,round(a.LSDJ00*a.YPZSL0*100)/100,substrb(d.YPMC00,1,50)YPMC00,0, substrb(nvl(d.YPGG00,'无'),1,10) YPGG00,nvl(a.QLDW00,'无')CFDW00,a.YPNM00,trim(substr(e.ZWXM00,1,4)) ZWXM00,a.LSDJ00,a.YPZSL0, round(a.LSDJ00*a.YPZSL0*100)/100 YPHJJE,a.ZFBL00,b.CZRQ00,0 SFJE00 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 b.ZYID00=ad_ZYID00; 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,INSTRB(a.XMMC00,'(包)')BCBZ00,--包床标志 substrb(nvl(c.GG0000,'无'),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,a.XMDJ00 SFJE00 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 union all select '1'FLAG00,a.CFID00,round(a.LSDJ00*a.YPZSL0*100)/100,substrb(e.YPMC00,1,50)YPMC00,0, substrb(nvl(e.YPGG00,'无'),1,10) YPGG00,nvl(a.CFDW00,'无')CFDW00,a.YPNM00,c.YSXM00,a.LSDJ00,a.YPZSL0, round(a.LSDJ00*a.YPZSL0*100)/100 YPHJJE,a.ZFBL00,d.CZRQ00,0 SFJE00 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 union all select '2'FLAG00,a.YPQLPC,round(a.LSDJ00*a.YPZSL0*100)/100,substrb(d.YPMC00,1,50)YPMC00,0, substrb(nvl(d.YPGG00,'无'),1,10) YPGG00,nvl(a.QLDW00,'无')CFDW00,a.YPNM00,e.ZWXM00,a.LSDJ00,a.YPZSL0, round(a.LSDJ00*a.YPZSL0*100)/100 YPHJJE,a.ZFBL00,b.CZRQ00,0 SFJE00 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 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,INSTRB(a.XMMC00,'(包)')BCBZ00,--包床标志 substrb(nvl(c.GG0000,'无'),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,a.XMDJ00 SFJE00 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 union all select '1'FLAG00,a.CFID00,round(a.LSDJ00*a.YPZSL0*100)/100,substrb(e.YPMC00,1,50)YPMC00,0, substrb(nvl(e.YPGG00,'无'),1,10) YPGG00,nvl(a.CFDW00,'无')CFDW00,a.YPNM00,c.YSXM00,a.LSDJ00,a.YPZSL0, round(a.LSDJ00*a.YPZSL0*100)/100 YPHJJE,a.ZFBL00,d.CZRQ00,0 SFJE00 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'--是否自备药 union all select '2'FLAG00,a.YPQLPC,round(a.LSDJ00*a.YPZSL0*100)/100,substrb(d.YPMC00,1,50)YPMC00,0, substrb(nvl(d.YPGG00,'无'),1,10) YPGG00,nvl(a.QLDW00,'无')CFDW00,a.YPNM00,e.ZWXM00,a.LSDJ00,a.YPZSL0, round(a.LSDJ00*a.YPZSL0*100)/100 YPHJJE,a.ZFBL00,b.CZRQ00,0 SFJE00 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 a.ZYID00=ad_ZYID00; 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; -- MODIFICATION HISTORY -- Person Date Comments -- xzw 2007.08.16 对收费的项目规格进行截取,截取的长度为10; -- zhr 2007.12.20 包床记录(zy_fymx00.xmmc00含"(包)"字样),自动转换成非医保费用进行发送 -- zhr 2008.03.25 南平医保为各类医保病人的床位费指定了一个医保项目编号，发送床位费时要用此编号进行发 -- zhr 2008.03.25 医保病人类别为"财政核拨离休干部"时，当病人单位含厅级，按"床位费（地市级医院离休厅级）"发送，否则按"床位费（地市级医院离休处级）" -- zhr 2009.03.04 监护病房床位费不拆分 -- yangy 2009.04.21 南平截取病人姓名前4个汉字 -- yangy 2009.07.13 监护病房床位费，虽然不拆分，但是发送时错误。 -- zhr 2009.08.18 当参数XT_YBJK_SFAYBXMMCFS为Y，生成的request.txt中的规格字段要用医保目录的规格，而不是院内的规格。 -- zhr 2010.05.30 医保病人类别为'财政核拨离休干部','破产企业离休人员' ,'特困企业离休人员'时，当病人单位含厅级，按"床位费（地市级医院离休厅级）"发送，否则按"床位费（地市级医院离休处级）" -- qks 2010.12.06 参数ZY_XNHCSLX_SFRQ控制农合病人是否增加传送费用发生日期 -- zhr 2011.09.17 CUR_ZY_FYMX00_MXID01:a.DJH000>= ad_QSDJH0 and a.DJH000<= ad_ZZDJH0 and a.ZYID00=ad_ZYID00;改为 -- a.DJH000>= ad_QSDJH0 and a.DJH000<= ad_ZZDJH0 and b.ZYID00=ad_ZYID00; -- dsm 2012.06.26 退小数的项目计算出的单价大于原来的单价,且这个单价不会被医保接受,所以修改：ZY_YBFSSFGDDJ='Y'时,如果没有超过最高限价的都用bm_yysfxm.sfje00 for ZYSF-20120604-002 -- dsm 2012.07.12 ZY_YBFSSFGDDJ='Y'时改成与ZY_FYMX00.XMDJ00比较 ZYSF-20120711-001 BEGIN /* 医保接口说明: 未对应为项目或超标项目的的处理办法:如果一个收费项目或药品未对应医保项目，那么有两种处理办法 一、如果BM_YBFPXM表有定义该医保发票项目的未对应或超标的项目医保接口使用的医保项目编号，那么医保接口使用此项目编号 二‘ 否则沿用以前的办法 采用'990000000000+医保发票编号方式，前缀由表BM_YBBRLB定义，如果没有定义默认前缀为990000000000 接口是否按医保目录的项目名称发送由参数XT_YBJK_SFAYBXMMCFS控制， 但有特例，铁路医保要求发院内项目名称,对应指定特殊医保项目的，也要发院内名称 接口文件是否使用医保发票编号,还是使用医保发票名称来传送 由参数ZY_YBFPBH控制 收费项目的金额超过上限是否进行拆分，由参数ZY_SFXMCGSXSFCF控制 医保住院是否按上限结算 由参数ZY_SFASXJS控制 所有医保接口都调用本过程，注意南平医保日清单发送接口 更正以前当SFYBXM为N就按未对应医保项目处理的办法 */ ls_ZYZFSX:=0; ls_ZYZFSX_TEMP:=0; ls_CWSXJE:=999999; 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') then--有传入ZYID00 select BRID00 into ls_BRID00 from ZY_BRXXB0 where ZYID00=ad_ZYID00; end if; select YBBRLB,FBBH00,YBLB00,TYDWMC,YBKH00,YBID00 into ls_YBBRLB,ls_FBBH00,ls_yblb00,ls_TYDWMC,ls_YBKH00,ls_YBID00 from BM_BRXXB0 where BRID00 = ls_BRID00; --取医保中心类别 BEGIN select YBZXLB into ls_YBZXLB 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; --泉州医保接口要求单价*数量必须等于金额 select upper(substrb(nvl(max(VALUE0),'N'),1,1)) into ls_DJSYXJ from XT_XTCS00 where NAME00 = 'SF_YBDJSLJE'; --医保单价*数量与金额是否要求严格相等 --收费项目的金额超过上限是否进行拆分 select upper(substrb(nvl(max(VALUE0),'Y'),1,1)) into ls_CGSXSFCF from XT_XTCS00 where NAME00 = 'ZY_SFXMCGSXSFCF'; --收费项目的金额超过上限是否进行拆分 --接口文件是否使用医保发票编号,还是使用医保发票名称来传送 select upper(substrb(nvl(max(value0),'N'),1,1)) into ls_SYFPBH from XT_XTCS00 where NAME00 = 'ZY_YBFPBH'; --是否使用医保发票编号 --接口是否按医保目录的项目名称发送 select upper(substrb(nvl(max(value0),'N'),1,1)) into ls_ybmcfs from xt_xtcs00 where name00='XT_YBJK_SFAYBXMMCFS'; --医保住院是否按上限结算 select upper(substrb(nvl(max(VALUE0),'N'),1,1)) into ZY_SFASXJS from XT_XTCS00 where NAME00 = 'ZY_SFASXJS'; --医保住院是否按上限结算 --农合病人是否实时结算 select upper(substrb(nvl(max(VALUE0),'N'),1,2)) into ls_XNHCSLX from XT_XTCS00 where NAME00 = 'ZY_XNHCSLX'; select upper(substrb(nvl(max(VALUE0),'N'),1,1)) into ls_XNHSFRQ from XT_XTCS00 where NAME00 = 'ZY_XNHCSLX_SFRQ'; --医保发送是否采用BM_YYSFXM的固定单价 select upper(substrb(nvl(max(VALUE0),'N'),1,1)) into ls_SFCYGDDJ from XT_XTCS00 where NAME00='ZY_YBFSSFGDDJ' ; --医保编号前缀编码,是否目录限制 BEGIN select FBMGZ0,SFMLXZ,ZYZFSX,CWXMBH,BRLBMC into ls_YBBHQZ,ls_SFMLXZ,ls_ZYZFSX,ls_CWXMBH,ls_BRLBMC from bm_ybbrlb where YBZXLB = ls_YBZXLB and YBBRLB = ls_YBBRLB; EXCEPTION WHEN NO_DATA_FOUND THEN ls_YBBHQZ:='990000000000'; --非医保项目的编码前缀 END; --2008.09.06 zhr 医保病人类别为"财政核拨离休干部"时，当病人单位含厅级，按"床位费（地市级医院离休厅级）"发送，否则按"床位费（地市级医院离休处级）" --2010.05.30 zhr 医保病人类别为'财政核拨离休干部','破产企业离休人员' ,'特困企业离休人员'时，当病人单位含厅级，按"床位费（地市级医院离休厅级）"发送，否则按"床位费（地市级医院离休处级）" if ls_YBZXLB='1' and ls_BRLBMC in ('财政核拨离休干部','破产企业离休人员' ,'特困企业离休人员') and instr(ls_TYDWMC,'厅级')>0 and ls_CWXMBH='711090000539' then ls_CWXMBH:='711090000537'; end if; --医保中心为各类病人指定新增的床位项目的收费上限 if ls_CWXMBH is not null then select decode(a.SFSX00,0,9999,null,9999,a.SFSX00) into ls_CWSXJE from BM_YBSFXM a where a.YBZXLB=ls_YBZXLB and a.XMBH00=ls_CWXMBH and a.YBZXLB=ls_YBZXLB; end if; 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; 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_BCBZ00,ls_GG0000,ls_DW0000, ls_XMBH00,ls_ZWXM00,ls_XMDJ00,ls_XMSL00,ls_HJJE00,ls_ZFBL00,ls_CZRQ00, ls_SFJE00; 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_BCBZ00,ls_GG0000,ls_DW0000, ls_XMBH00,ls_ZWXM00,ls_XMDJ00,ls_XMSL00,ls_HJJE00,ls_ZFBL00,ls_CZRQ00, ls_SFJE00; 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_BCBZ00,ls_GG0000,ls_DW0000, ls_XMBH00,ls_ZWXM00,ls_XMDJ00,ls_XMSL00,ls_HJJE00,ls_ZFBL00,ls_CZRQ00, ls_SFJE00; 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_BCBZ00,ls_GG0000,ls_DW0000, ls_XMBH00,ls_ZWXM00,ls_XMDJ00,ls_XMSL00,ls_HJJE00,ls_ZFBL00,ls_CZRQ00, ls_SFJE00; EXIT WHEN CUR_ZY_FYMX00_MXID03%notfound; end if; as_SYSMSG:='MXID00='||to_char(ls_MXID00); select a.XMMC00,a.BH0000,C.SFYBXM,nvl(C.SFSX00,0),D.XMMC00,C.YBXMBH,a.CBXMBH,C.ZFBL00,c.YBLB00,d.YPGG00 into ls_FPXMMC,ls_FPXMBH,ls_SFYBXM,ls_YBXMSX,ls_YBXMMC,ls_YBXMBH,ls_CBXMBH,ls_YBZFBL,ls_YBXMLB,ls_YBXMGG from BM_YBFPXM A,VW_BM_YBSFDY C,BM_YBSFXM D where a.BH0000 = C.YBZYFP 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 <> '1' ; --非门诊 --zhr 08.03.25南平医保为各类医保病人专门增设了各类新床位收费项目，如果病人所发生的床位单价超过了这个标准，发送时要用此编号发送, --超标部分再用非医保其他费发送，如果不超标用原始的收费项目进行发送。存在二次超标判定的问题。 if (ls_CWXMBH is not null) and (instr(ls_XMMC00,'床位')>0) and (instr(ls_XMMC00,'监护病房床位费')<=0) then--and (ls_XMDJ00>ls_CWSXJE) select count(*) into ls_COUNT0 from BM_CWJM00 where SFXMID=ls_XMBH00; if ls_COUNT0>0 then select D.SFYBXM,nvl(D.SFSX00,0),D.XMMC00,D.XMBH00,D.ZFBL00,D.YBLB00,D.YPGG00 into ls_SFYBXM,ls_YBXMSX,ls_YBXMMC,ls_YBXMBH,ls_YBZFBL,ls_YBXMLB,ls_YBXMGG from BM_YBSFXM D where D.XMBH00 = ls_CWXMBH and d.YBZXLB = ls_YBZXLB; 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; else ls_CBXMMC:=ls_XMMC00; ls_CBXMBH:=ls_YBBHQZ||ls_FPXMBH; ls_CBFPBH:=ls_FPXMBH; ls_CBFPMC:=ls_FPXMMC; end if; if ls_YBXMBH is null then ls_YBXMMC:=ls_CBXMMC; ls_YBXMBH:=ls_CBXMBH; ls_FPXMMC:=ls_CBFPMC; ls_FPXMBH:=ls_CBFPBH; end if; --2007.12.20 zhr 包床记录,自动转换成非医保项目进行发送。 if ls_BCBZ00>0 then--包床 ls_YBZFBL:=1; ls_YBXMMC:=ls_CBXMMC; ls_YBXMBH:=ls_CBXMBH; ls_FPXMMC:=ls_CBFPMC; ls_FPXMBH:=ls_CBFPBH; ls_SFYBXM:='N'; ls_YBXMSX:=9999999999; 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='1' 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; --判断实际单价是否超过医保上线,如果是要拆分成两条记录 if (ls_XMDJ00>ls_YBXMSX) and (ls_YBXMSX>0) and (ls_SFYBXM='Y') and (instr(ls_XMMC00,'监护病房床位费')<=0) 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) 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',ls_YBXMMC,ls_XMMC00)), decode(ls_ybmcfs,'Y',ls_YBXMGG,ls_GG0000),ls_DW0000,ls_YBXMSX,ls_XMSL00,round(ls_YBXMSX*ls_XMSL00,2),ls_ZWXM00,ls_CBXMBH,ls_CZRQ00); insert into BM_ZYCFMX_TEMP00(ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00,YSXM00,ZFXMBH,CZRQ00) values(ls_ID0000,ls_CBXMBH,'N',DECODE(ls_SYFPBH,'Y',ls_CBFPBH,ls_CBFPMC),ls_CBXMMC, ls_GG0000,ls_DW0000,(ls_XMDJ00-ls_YBXMSX),ls_XMSL00,round((ls_XMDJ00-ls_YBXMSX)*ls_XMSL00,2),ls_ZWXM00,ls_CBXMBH,ls_CZRQ00); else--不做任何转换 if ls_SFCYGDDJ='Y' and ls_FLAG00=0 and ls_XMDJ00>ls_sfje00 then --for ZYSF-20120604-002 --ls_XMSL00:=round(ls_HJJE00/ls_SFJE00,2); ls_XMDJ00:=ls_SFJE00; end if; insert into BM_ZYCFMX_TEMP00(ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00,YSXM00,ZFXMBH,CZRQ00) 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',ls_YBXMMC,ls_XMMC00)), decode(ls_ybmcfs,'Y',ls_YBXMGG,ls_GG0000),ls_DW0000,ls_XMDJ00,ls_XMSL00,round(ls_XMDJ00*ls_XMSL00,2),ls_ZWXM00,ls_CBXMBH,ls_CZRQ00); 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; end if; --农合病人实时结算 if (ls_XNHCSLX='YN' and ls_XNHSFRQ='Y') and (trim(ls_YBID00)=trim(ls_YBKH00)) then --先把数量为负数的合并到正数上 2010.12.06 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; --处理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) 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 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) 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 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) select ad_ID0000,XMBH00,SFYB00,FPXMMC,TRIM(XMMC00),XMGG00,MAX(XMDW00),XMDJ00,nvl(sum(XMSL00),0),NVL(sum(XMJE00),0),trim(max(YSXM00)),max(ZFXMBH) from BM_ZYCFMX_TEMP00 where ID0000 = ls_ID0000 group by XMBH00,SFYB00,FPXMMC,XMGG00,XMDJ00,TRIM(XMMC00); end if; if ls_SFCYGDDJ='Y' then update BM_ZYCFMX_TEMP00 set XMDJ00 = round(XMJE00/XMSL00,6) where XMJE00 <> round(XMDJ00 * XMSL00,2) and ID0000 = ad_ID0000 and XMSL00<>0; else update BM_ZYCFMX_TEMP00 set XMDJ00 = round(XMJE00/XMSL00,6) where XMJE00 <> (XMDJ00 * XMSL00) and ID0000 = ad_ID0000 and XMSL00<>0; end if; 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; 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; --2007.12.20 床位费要按实际住院日期收，多出的床位费(可能因为包床)，自动转为非医保床位费 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; 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; /