create or replace procedure SP_ZY_YBJK00_DATA ( PDDLSH0 in varchar2 ,--输入:订单流水号 PJKLXBH in number ,--输入:接口类型编号 PJKLX00 in varchar2 ,--输入:RQDSC:日清单上传 DD:多点结算 YD:移动结算 YB:医保结算 PBRID00 in number ,--输入:BRID00 PZYID00 in number ,--输入:住院ID PZYH000 in varchar2 ,--输入:住院号 PJZDH00 in number ,--输入:结账单号 PCSNBID in varchar2 ,--输入:输入参数内部ID(可为空值) POTHERS in varchar2 ,--输入:输入其它参数 PID0000 out number ,--序列号ID PSYSMSG out varchar --存储过程提示的错误信息 ) -- MODIFICATION HISTORY -- Person Date Comments --zhangyc 2019.01.03 created by ZYSF-20190104-002 --zhangyc 2019.07.25 增加自费病人报错 by ZYSF-20190725-001 --zhangyc 2019.07.26 修正(DD:多点结算 YD:移动结算 YB:医保结算)只取未结算的费用 by ZYSF-20190726-002 --zhangyc 2019.08.13 LS_YBXMMC未维护取LS_XMMC00 by ZYSF-20190813-001 --zhangyc 2019.09.03 修改非医疗项目拆分 by ZYSF-20190903-001 -- ------ ------------- --------------------------------------------------------------------------------- as --更改结帐单号为单据号,更改YF_YZYPSQ游标的内容 LS_ZYID00 ZY_BRXXB0.ZYID00%type; --ZYID00 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_ID0000_CBCW BM_ZYCF00_TEMP00.ID0000%type; --超标床位临时ID LS_ID0000_TEMP BM_ZYCF00_TEMP00.ID0000%type; --超标床位临时ID 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(10); LS_COUNT1 number(10); LS_COUNT2 number(10); LS_CWNUM0 number(10); 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); --是否采用固定单价发送 LS_YSZJHM varchar2(30); 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_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_SFXMID BM_YYSFXM.SFXMID%type; --收费项目id LS_YSYGBH number(5); 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;--医保最高单价（小单位） LS_DBJKQY varchar2(100); LS_SFXNH0 IC_YBBRLB.SFXNH0%type; --是否新农合 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);--是否单病种打包 LS_GJBM00 BM_YYSFXM.GJBM00%type; --国家编码 LS_BZCWSX number(12,2); --医保单病种超标床位上限 LS_YNFPBH BM_FPXM00.BH0000%type;--院内发票编号 LS_YNFPMC BM_FPXM00.XMMC00%type;--院内发票名称 LS_MSGSTR varchar2(250); LS_YBFPBH BM_YBFPXM.BH0000%type; --医保发票编号 LS_YBFPMC BM_YBFPXM.XMMC00%type; --医保发票编号 LS_HCFPMC BM_ZYCFMX_TEMP00.FPXMMC%type; --耗材打包发票项目编码和发票名称 LS_HCXZJE BM_GZHCFL.HCXZJE%type;--耗材限额 LS_HCCBBM BM_GZHCFL.HCCBBM%type;--超标编码 LS_HCCBMC BM_GZHCFL.HCCBMC%type;--超标名称 LS_CWZHBZ char(1);--普通床位费转为超标标志 Y：婴儿床位转化 P：普通床位转化 N：不转化 LS_DJH000 ZY_BRFY00.DJH000%type;--单据号 LS_CXDJH0 ZY_BRFY00.CXDJH0%type;--冲销单据号 LS_TEMPID BM_GHKSTJ.ID0000%type; LS_YNDW00 ZY_FYMX00.XMDW00%type;--院内单位 LS_YNDJ00 ZY_FYMX00.XMDJ00%type;--院内单价 LS_YBDZGJ BM_YBSFXM.YBZGDJ%type;--医保大单位--最高限价 LS_YBXZGJ BM_YBSFXM.YBZGDJ%type;--医保小单位--最高限价 LS_YYKSBM ZY_YBFSMX.YYKSBM%type;--医院科室编码(医保) LS_YYKSMC ZY_YBFSMX.YYKSMC%type;--医院科室名称(医保) LS_YYBQBM ZY_YBFSMX.YYBQBM%type;--医院病区编码(医保) LS_YYBQMC ZY_YBFSMX.YYBQMC%type;--医院病区名称(医保) LS_DQKS00 BM_BMBM00.BMBH00%type;--当前科室编码 LS_DQKSYB BM_BMBM00.YYKSBM%type;--当前科室编码(医保) LS_DQKSMC BM_BMBM00.BMMC00%type;--当前科室名称 LS_DQBQ00 BM_BMBM00.BMBH00%type;--当前病区编码 LS_DQBQYB BM_BMBM00.YYKSBM%type;--当前病区编码(医保) LS_DQBQMC BM_BMBM00.BMMC00%type;--当前病区名称 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, 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,a.XMDJ00 SFJE00, e.YSZJHM, --医生证件编号 1 as YPTS00,--用药天数 '' as PCMC00,--频次名称 a.XMSL00 as CSL000, --剂量 c.DW0000 as JLDW00, --剂量单位 C.SFXMID, --收费项目ID a.KDYS00 as YSYGBH, --医生员工编码 null as K2J000,--库存单位转化率 null as M2J000,--门诊发药单位转化率 null as Z2J000,--住院发药单位转化率 null as YBDW00,--医保单位 null as YB2J00, --医保转换率 trim(C.GJBM00)GJBM00, --国家编码 B.BH0000 as YNFPBH ,--院内发票编码 B.XMMC00 as YNFPMC ,--院内发票名称 D.DJH000 ,--单据号 nvl(D.CXDJH0,0) CXDJH0--冲销单据号 from ZY_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,ZY_BRFY00 D,BM_YGBM00 E where 1=1 and ( (PJZDH00=0 and ((PJKLX00 not in ('DD','YD','YB') and 1=1) or (PJKLX00 in ('DD','YD','YB') and d.JZDH00+0=0)) ) or ( PJZDH00<>0 and (d.JZDH00 in (select k.JZDH00 from ZY_JZB000 k where k.BRID00=LS_BRID00 start with k.JZDH00=PJZDH00 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 B.SFYLXM='Y' and a.XMBH00<9999999900 and d.ZYID00=LS_ZYID00 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, (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,--剂量单位 e.YPNM00 as SFXMID, --收费项目ID c.YSGZH0 as YSYGBH, --医生员工编码 e.K2J000 as K2J000,--库存单位转化率 e.M2J000 as M2J000,--门诊发药单位转化率 e.Z2J000 as Z2J000,--住院发药单位转化率 e.YBDW00 as YBDW00,--医保单位 e.YB2J00 as YB2J00, --医保转换率 null as GJBM00, --国家编码 decode(e.LBBH00,'0','01','1','02','2','03','01') as YNFPBH, --院内发票编码 decode(e.LBBH00,'0','西药费','1','成药费','2','草药费','西药费') as YNFPMC ,--院内发票名称 D.DJH000 ,--单据号 nvl(D.CXDJH0,0) CXDJH0--冲销单据号 from YF_ZYCFMX A,YF_ZYCF00 c,ZY_BRFY00 d,BM_YD0000 e where 1=1 and ( (PJZDH00=0 and ((PJKLX00 not in ('DD','YD','YB') and 1=1) or (PJKLX00 in ('DD','YD','YB') and d.JZDH00+0=0))) or ( PJZDH00<>0 and (d.JZDH00 in (select k.JZDH00 from ZY_JZB000 k where k.BRID00=LS_BRID00 start with k.JZDH00=PJZDH00 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=LS_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, e.YSZJHM, --医生证件编号 1 as YPTS00,--用药天数 a.PCMC00 as PCMC00,--频次名称 nvl(CSL000,a.YPZSL0) as CSL000, --剂量 a.JLDW00 as JLDW00,--剂量单位 d.YPNM00 as SFXMID, --收费项目ID a.YSGZH0 as YSYGBH, --医生员工编码 d.K2J000 as K2J000,--库存单位转化率 d.M2J000 as M2J000,--门诊发药单位转化率 d.Z2J000 as Z2J000,--住院发药单位转化率 d.YBDW00 as YBDW00,--医保单位 d.YB2J00 as YB2J00, --医保转换率 null as GJBM00, --国家编码 decode(D.LBBH00,'0','01','1','02','2','03','01') as YNFPBH, --院内发票编码 decode(D.LBBH00,'0','西药费','1','成药费','2','草药费','西药费') as YNFPMC ,--院内发票名称 B.DJH000 ,--单据号 nvl(B.CXDJH0,0) CXDJH0--冲销单据号 from YF_YZYPSQ A,ZY_BRFY00 b,BM_YD0000 d,BM_YGBM00 E where 1=1 and ( (PJZDH00=0 and ((PJKLX00 not in ('DD','YD','YB') and 1=1) or (PJKLX00 in ('DD','YD','YB') and b.JZDH00+0=0))) or ( PJZDH00<>0 and (b.JZDH00 in (select k.JZDH00 from ZY_JZB000 k where k.BRID00=LS_BRID00 start with k.JZDH00=PJZDH00 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.ZYID00=LS_ZYID00; --获取耗材打包项目 cursor CUR_HCDBXE is select A.* from ZY_YBFSMX A where A.ID0000 = LS_ID0000 and nvl(A.GZHCID,0)>0 and nvl(A.SFFSHC,'1')='1' and nvl(A.HCDBXM,'0')='1'; begin select SQ_ZY_TEMP00_ID0000.nextval into LS_ID0000 from dual; select SQ_ZY_TEMP00_ID0000.nextval into LS_ID0000_CBCW from dual; LS_ZYZFSX:=0; LS_ZYZFSX_TEMP:=0; LS_CWSXJE:=999999; LS_GZHCID:=0; LS_SFFSHC:='1'; LS_HCDBXM:='0'; LS_HCDBBM:=''; LS_BZCBBH:=null; --单病种超标医保发票项目编号 LS_BZCBMC:=null; --单病种超标发票项目名称 LS_BZCBCW:=0; --单病种超标床位费 LS_BZLSHC:=0; --单病种另收耗材费 LS_BZGJBM:=null; --单病种允许另收耗材的国家编码前缀组合,用逗号分隔 LS_SFBZDY:='N';--是否单病种打包 LS_BZCWSX:=0;--医保单病种超标床位上限 LS_MSGSTR:=''; LS_CWZHBZ:='N';--普通床位费转为超标标志 Y：婴儿床位转化 P：普通床位转化 N：不转化 begin select a.BRID00,a.ZYID00,a.DBZBM0,a.DBZMC0,b.YBBRLB,b.FBBH00,b.YBLB00,b.TYDWMC,b.YBKH00,b.YBID00,c.YBZXLB,c.SFXNH0, a.DQKS00,a.DQBQ00 into LS_BRID00,LS_ZYID00,LS_DBZBM0,LS_DBZMC0,LS_YBBRLB,LS_FBBH00,LS_YBLB00,LS_TYDWMC,LS_YBKH00,LS_YBID00,LS_YBZXLB,LS_SFXNH0 , LS_DQKS00,LS_DQBQ00 from ZY_BRXXB0 a,BM_BRXXB0 b,IC_YBBRLB c where a.BRID00=b.BRID00 and b.FBBH00=c.FBBH00 and b.YBLB00=c.YBLB00 and a.ZYID00=PZYID00 ; exception when others then PSYSMSG:='未找到有效的基本信息!'; rollback; return; end; if PJKLXBH='20024' then LS_DBZBM0:=''; LS_DBZMC0:=''; end if; begin select BMMC00,YYKSBM into LS_DQKSMC,LS_DQKSYB from BM_BMBM00 where BMBH00=LS_DQKS00; exception when others then LS_DQKSMC:='无'; LS_DQKSYB:=LS_DQKS00; end; begin select BMMC00,YYKSBM into LS_DQBQMC,LS_DQBQYB from BM_BMBM00 where BMBH00=LS_DQBQ00; exception when others then LS_DQBQMC:='无'; LS_DQBQYB:=LS_DQBQ00; end; --获取医院科室编码 select substrb(SF_ZY_GetYYKSBM(LS_ZYID00,0),1,20) into LS_YYKSBM from dual; LS_YYKSMC:=LS_DQKSMC;--医院科室名称(医保) LS_YYBQBM:=LS_DQBQ00;--医院病区编码(医保) LS_YYBQMC:=LS_DQBQMC;--医院病区名称(医保) --泉州医保接口要求单价*数量必须等于金额 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' ; --SF_SFQYYBXJJK 门诊或住院系统是否启用医保限价接口 Y:启用 N:不启用 默认值N:不启用 begin select SF_SF_YBXJQY(LS_BRID00,0,'0') into LS_YBXJQY from dual; --返回值 Y:启用 N:不启用 exception when others then LS_YBXJQY:='N'; end; begin select trim(VALUE0) into LS_DBJKQY from XT_XTCS00 where name00 in ('ZY_YBDBZJSJK'); exception when others then LS_DBJKQY:='N'; end; --ZY_DBZCWFSX :住院收费医保单病种超标床位费上线金额(ZY_YBDBZJSJK=R并且是单病种病人) 默认值:0 begin select to_number(trim(VALUE0)) into LS_BZCWSX from XT_XTCS00 where name00 in ('ZY_DBZCWFSX'); exception when others then LS_BZCWSX:=0; end; --医保编号前缀编码,是否目录限制 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; 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 SF_ZY_GETYYKSBM(LS_ZYID00,1) YSZJHM into LS_YSZJHM from dual; --0:医院科室编码 1:医生证件号 --医保部分耗材需要打包收费参数是否启用 Y:启用 N:不启用 select SF_ZY_XTCS00(LS_BRID00,'ZY_GZHCYBFS') into LS_HCJKQY from dual; --有登记单病种编码都判断2018.01.18 if (LS_DBZBM0 is not null) and (LS_DBJKQY not in ('N')) then select count(1) into LS_COUNT0 from BM_GZHCMX A,BM_GZHCFL B where A.GZHCID=B.GZHCID and B.SFDBZ0='1' and trim(B.XXZLXM) is not null and instrb(','||trim(B.XXZLXM)||',',','||trim(LS_DBZBM0)||',')>0; if LS_COUNT0>0 then LS_SFBZDY:='Y';--是否单病种打包 else --根据BM_GZHCBZ 表对应的判断 select count(1) into LS_COUNT0 from BM_GZHCMX A, BM_GZHCFL B where A.GZHCID=B.GZHCID and B.SFDBZ0='1' and B.GZHCID in (select aa.GZHCID from BM_GZHCBZ aa where aa.DBZBM0=LS_DBZBM0); if LS_COUNT0>0 then LS_SFBZDY:='Y';--是否单病种打包 end if; end if; end if; if LS_SFBZDY='Y' then LS_HCJKQY:='N'; end if; open CUR_ZY_FYMX00_MXID00;--重新医属计算记账金额 loop 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,LV_YSZJHM,LS_YPTS00,LS_PCMC00, LS_CSL000,LS_JLDW00,LS_SFXMID,LS_YSYGBH,LS_K2J000,LS_M2J000, LS_Z2J000,LS_YBDW00,LS_YB2J00,LS_GJBM00,LS_YNFPBH,LS_YNFPMC, LS_DJH000,LS_CXDJH0; exit when CUR_ZY_FYMX00_MXID00%notfound; LS_CWZHBZ:='N';--普通床位费转为超标标志 Y：婴儿床位转化 P：普通床位转化 N:不转化 LS_CWNUM0:=0; LS_GZHCID:=0; LS_SFFSHC:='1'; LS_HCDBBM:=''; LS_YNDW00:=LS_DW0000;--院内单位 LS_YNDJ00:=LS_XMDJ00;--院内单价 --取高值耗材项目ID--begin if LS_FLAG00='0' then begin if LS_SFBZDY='Y' then--是否单病种打包 select SF_ZY_DBZGZHCID(LS_ZYID00,LS_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; end if; if LS_HCJKQY='Y' then 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; 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; --取医保中心对应项目 begin select a.XMMC00 as FPXMMC,a.BH0000 as FPXMBH,C.SFYBXM,nvl(C.SFSX00,0) YBXMSX,D.XMMC00 as YBXMMC , C.YBXMBH,a.CBXMBH,C.ZFBL00 as YBZFBL,c.YBLB00 as YBXMLB,d.YPGG00 as YBXMGG, D.XMDW00,D.YBJSDJ,D.YBZGDJ, D.XMDW01,D.YBJSDJ1,D.YBZGDJ1, A.BZCBBH,A.BZCBMC into LS_FPXMMC,LS_FPXMBH,LS_SFYBXM,LS_YBXMSX,LS_YBXMMC, LS_YBXMBH,LS_CBXMBH,LS_YBZFBL,LS_YBXMLB,LS_YBXMGG, LS_YBDDW0,LS_YBJSDJ,LS_YBZGDJ, LS_YBXDW0,LS_YBJSDJ1,LS_YBZGDJ1, LS_BZCBBH,LS_BZCBMC 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 not in ( '1') ; --非门诊 exception when others then LS_FPXMBH:=LS_YNFPBH; LS_FPXMMC:=LS_YNFPMC; end; LS_YBXMMC :=nvl(trim(LS_YBXMMC),trim(LS_XMMC00)); LS_YBXMGG :=nvl(trim(LS_YBXMGG),trim(LS_GG0000)); LS_YBDDW0 :=nvl(trim(LS_YBDDW0),trim(LS_DW0000)); LS_YBFPBH:=LS_FPXMBH;--医保发票编码 LS_YBFPMC:=LS_FPXMMC;--医保发票名称 LS_YBDZGJ:=LS_YBZGDJ;--医保大单位--最高限价 LS_YBXZGJ:=LS_YBZGDJ1;--医保小单位--最高限价 if (LS_DBZBM0 is not null) and (LS_DBJKQY='R') then LS_BZCBBH:=LS_BZCBBH; LS_BZCBMC:=LS_BZCBMC; --床位费按参数设置控制 2017.12.27 if (instrb(trim(LS_FPXMMC),'床位费')>0) and (nvl(LS_BZCWSX,0)>0) and (LS_FLAG00='0') and (LS_YNFPBH in ('05')) then LS_YBXMSX:=nvl(LS_BZCWSX,0); end if; else LS_BZCBBH:=null; LS_BZCBMC:=null; end if; --zhr 08.03.25南平医保为各类医保病人专门增设了各类新床位收费项目，如果病人所发生的床位单价超过了这个标准，发送时要用此编号发送, --超标部分再用非医保其他费发送，如果不超标用原始的收费项目进行发送。存在二次超标判定的问题。 if (LS_CWXMBH is not null) and (instr(LS_XMMC00,'床位')>0) and (instr(LS_XMMC00,'监护病房床位费')<=0) then select count(*) into LS_COUNT0 from BM_CWJM00 where SFXMID=ls_XMBH00; if LS_COUNT0>0 then begin 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; exception when others then LS_SFYBXM:='N'; end; end if; end if; --如果没有对应,查找BM_YBSFXM本发票记录有没有定义未对应或超标的项目医保接口使用的医保项目编号 -- 如果有定义,使用此定义的医保项目来生成接口文件,如果没有定义,使用'990000000000+医保发票编号方式 if LS_CBXMBH is not null then begin 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; exception when others then LS_CBXMMC:=''; LS_CBFPBH:=''; LS_CBFPMC:=''; end; 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; --药品医保项目单位转化--begin--- if (LS_YBXJQY='Y') and (LS_FLAG00 in ('1','2')) and (nvl(LS_XMSL00,0)<>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/nvl(LS_Z2J000,1))*nvl(LS_YB2J00,1),4); LS_XMSL00 := round(LS_HJJE00/LS_XMDJ00,4); end if; end if; end if; end if; --2018.12.27 by zhangyc 增加院内单价和最高限价比较---begin-- if trim(LS_DW0000)=trim(LS_YBDDW0) and nvl(LS_XMDJ00,0)>nvl(LS_YBZGDJ,0) and nvl(LS_YBZGDJ,0)>0 then LS_DW0000:=trim(LS_YBDDW0); LS_HJJE00:=LS_HJJE00; LS_XMDJ00:=nvl(LS_YBZGDJ,0); LS_XMSL00:=round(LS_HJJE00/LS_XMDJ00,4); end if; if trim(LS_DW0000)=trim(LS_YBXDW0) and nvl(LS_XMDJ00,0)>nvl(LS_YBZGDJ1,0) and nvl(LS_YBZGDJ1,0)>0 then LS_DW0000:=trim(LS_YBXDW0); LS_HJJE00:=LS_HJJE00; LS_XMDJ00:=nvl(LS_YBZGDJ1,0); LS_XMSL00:=round(LS_HJJE00/LS_XMDJ00,4); end if; --2018.12.27 by zhangyc 增加院内单价和最高限价比较---end-- end if; --特殊处理，如果对应的是以下几个医保项目,需要传递院内项目名称 if LS_YBXMBH in ('01670000000001','01670000000002','01670000000005','01670000000006','01670000000007','01670000000008') then LS_YBXMMC:=LS_XMMC00; end if; --处理药品用量、用药天数、用药频率、医师证件号--begin-- 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:='*'; 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-- --单病种普通转成超标床位费处理--begin-- if LS_DBZBM0 is not null and LS_BZCBBH is not null and (instrb(trim(LS_FPXMMC),'床位费')>0 or LS_YNFPBH in ('05')) and LS_FLAG00='0' then --是否普通床位费转为超标 Y：是 N：否 select count(1) into LS_CWNUM0 from BM_TYZD00 where ZDMC00='住院单病种普通床位转超标' and YXBZ00='1' and BZ0000='普通床位' and BH0000=to_char(LS_XMBH00); if LS_CWNUM0>0 then ---字典有维护普通床位转超标 --根据时间查下中间表是否有数据 --ZY_YBFSZX: DDLSH0:收费项目ID JKBTBM:操作日志 FSLX00:0:普通床位 1：婴儿床位 DDLSH0:项目编码 JKBTBM:操作日期 --JKFSBM:单据号 JDBM00:冲销单据号 ZJDBM0:普通床位费转为超标标志 Y：婴儿床位转化 P：普通床位转化 N：不转化 if nvl(LS_CXDJH0,0)=0 then --2018.12.04 冲销普通床位或婴儿床位转超标发送处理 select count(1) into LS_CWNUM0 from ZY_YBFSZX where ID0000=LS_ID0000_CBCW and ZYID00=LS_ZYID00 and DDLSH0=to_char(LS_XMBH00) and JKBTBM=LS_CZRQ00 and FSLX00='0'; if LS_CWNUM0>0 then LS_CWZHBZ:='P';--普通床位费转为超标标志 Y：婴儿床位转化 P：普通床位转化 N：不转化 end if; insert into ZY_YBFSZX(ID0000,ZYID00,DDLSH0,JKBTBM,FSLX00,JKFSBM,JDBM00,ZJDBM0) values(LS_ID0000_CBCW,LS_ZYID00,to_char(LS_XMBH00),LS_CZRQ00,'0',LS_DJH000,LS_CXDJH0,LS_CWZHBZ); else --查找原单的类型(普通床位费转为超标标志 Y：婴儿床位转化 P：普通床位转化 N：不转化) select substrb(max(ZJDBM0),1,1) into LS_CWZHBZ from ZY_YBFSZX where ID0000=LS_ID0000_CBCW and ZYID00=LS_ZYID00 and DDLSH0=to_char(LS_XMBH00) and JKFSBM=nvl(LS_CXDJH0,0) ; end if; end if; select count(1) into LS_CWNUM0 from BM_TYZD00 where ZDMC00='住院单病种普通床位转超标' and YXBZ00='1' and BZ0000='婴儿床位' and BH0000=to_char(LS_XMBH00); if LS_CWNUM0>0 then ---字典有维护普通床位转超标 --根据时间查下中间表是否有数据 --ZY_YBFSZX: DDLSH0:收费项目ID JKBTBM:操作日志 FSLX00:0:普通床位 1：婴儿床位 LS_CWZHBZ:='Y';--普通床位费转为超标标志 Y：婴儿床位转化 P：普通床位转化 N：不转化 insert into ZY_YBFSZX(ID0000,ZYID00,DDLSH0,JKBTBM,FSLX00) values(LS_ID0000_CBCW,LS_ZYID00,to_char(LS_XMBH00),LS_CZRQ00,'1'); end if; end if; --单病种普通转成超标床位费处理--end-- if LS_FBBH00 not in (3) then LS_FPXMMC:=nvl(trim(LS_FPXMMC),LS_YNFPMC); LS_FPXMBH:=nvl(trim(LS_FPXMBH),LS_YNFPBH); LS_SFYBXM:=nvl(trim(LS_SFYBXM),'N'); LS_YBXMSX:=0; LS_YBXMMC:=nvl(trim(LS_YBXMMC),LS_XMMC00); LS_YBXMBH:=nvl(trim(LS_YBXMBH),LS_XMBH00); LS_YBXMGG:=nvl(trim(LS_YBXMGG),LS_GG0000); LS_YBDDW0:=nvl(trim(LS_YBDDW0),LS_DW0000); LS_SYFPBH:='N'; --是否使用医保发票编号 end if; if LS_CWZHBZ in ('Y','P') then --插入通用字典维护对应的普通床位转成超标床位 insert into ZY_YBFSMX( ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00, XMSL00,XMJE00,YSXM00,ZFXMBH,CZRQ00,FLAG00,GZHCID,SFFSHC, HCDBXM,SFXMID,YSZJHM,YPTS00,YPPL00,YPYL00,YSYGBH,DDLSH0, YNFPBH,YNFPMC,YBFPBH,YBFPMC,YNDW00,YNDJ00,YBDW00,YBDDW0, YBDZGJ,YBXDW0,YBXZGJ,ZYID00,MXID00,YYKSBM,YYKSMC,YYBQBM,YYBQMC ) values(LS_ID0000,nvl(LS_BZCBBH,LS_CBXMBH),decode(LS_BZCBBH,null,'N','N'), decode(trim(LS_YBLB00),'Z',LS_CBFPMC,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,LS_DW0000,LS_XMDJ00, LS_XMSL00,round(LS_XMDJ00*LS_XMSL00,2),LS_ZWXM00,nvl(LS_BZCBBH,LS_CBXMBH),LS_CZRQ00,LS_FLAG00,LS_GZHCID,LS_SFFSHC, decode(LS_BZCBBH,null,LS_HCDBXM,'2'),nvl(LS_SFXMID,0),LV_YSZJHM,LS_YPTS00,LS_YPPL00,LS_YPYL00,LS_YSYGBH,PDDLSH0, LS_YNFPBH,LS_YNFPMC,LS_YBFPBH,LS_YBFPMC,LS_YNDW00,LS_YNDJ00,LS_YBDW00,LS_YBDDW0, LS_YBDZGJ,LS_YBXDW0,LS_YBXZGJ,LS_ZYID00,SQ_ZY_YBFSZX_MXID00.nextval,LS_YYKSBM,LS_YYKSMC,LS_YYBQBM,LS_YYBQMC ); elsif ((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)) or --增加医保单病种超标床位费处理 ( (LS_XMDJ00>LS_YBXMSX) and (LS_YBXMSX>0) and (LS_SFYBXM='Y') and (LS_BZCBBH is not null) and (LS_DBZBM0 is not null) and (LS_DBJKQY='R') ) or --增加医保单病种超标床位费处理（非医疗项目） ( (LS_XMDJ00>LS_YBXMSX) and (LS_YBXMSX>0) and (LS_SFYBXM='N') and (LS_BZCBBH is not null) and (LS_DBZBM0 is not null) ) then --超过上线 拆分成两条记录 insert into ZY_YBFSMX( ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00, XMSL00,XMJE00,YSXM00,ZFXMBH,CZRQ00,FLAG00,GZHCID,SFFSHC, HCDBXM,SFXMID,YSZJHM,YPTS00,YPPL00,YPYL00,YSYGBH,DDLSH0, YNFPBH,YNFPMC,YBFPBH,YBFPMC,YNDW00,YNDJ00,YBDW00,YBDDW0, YBDZGJ,YBXDW0,YBXZGJ,ZYID00,MXID00,YYKSBM,YYKSMC,YYBQBM,YYBQMC) values(LS_ID0000,LS_YBXMBH,decode(LS_SFMLXZ,0,'Y',LS_SFYBXM),decode(trim(LS_YBLB00),'Z',LS_FPXMMC,decode(LS_SYFPBH,'Y',LS_FPXMBH,LS_FPXMMC)), decode(trim(LS_YBLB00),'Z',LS_XMMC00,decode(LS_YBMCFS,'Y',LS_YBXMMC,LS_XMMC00)), decode(LS_YBMCFS,'Y',LS_YBXMGG,LS_GG0000),LS_DW0000,LS_YBXMSX, LS_XMSL00,round(LS_YBXMSX*LS_XMSL00,2),LS_ZWXM00,LS_CBXMBH,LS_CZRQ00, LS_FLAG00,LS_GZHCID,LS_SFFSHC,LS_HCDBXM,nvl(LS_SFXMID,0), LV_YSZJHM,LS_YPTS00,LS_YPPL00,LS_YPYL00,LS_YSYGBH,PDDLSH0, LS_YNFPBH,LS_YNFPMC,LS_YBFPBH,LS_YBFPMC,LS_YNDW00,LS_YNDJ00,LS_YBDW00,LS_YBDDW0, LS_YBDZGJ,LS_YBXDW0,LS_YBXZGJ,LS_ZYID00,SQ_ZY_YBFSZX_MXID00.nextval,LS_YYKSBM,LS_YYKSMC,LS_YYBQBM,LS_YYBQMC ); insert into ZY_YBFSMX( ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00, XMSL00,XMJE00,YSXM00,ZFXMBH,CZRQ00,FLAG00,GZHCID,SFFSHC, HCDBXM,SFXMID,YSZJHM,YPTS00,YPPL00,YPYL00,YSYGBH,DDLSH0, YNFPBH,YNFPMC,YBFPBH,YBFPMC,YNDW00,YNDJ00,YBDW00,YBDDW0, YBDZGJ,YBXDW0,YBXZGJ,ZYID00,MXID00,YYKSBM,YYKSMC,YYBQBM,YYBQMC) values(LS_ID0000,nvl(LS_BZCBBH,LS_CBXMBH),decode(LS_BZCBBH,null,'N','N'), decode(trim(LS_YBLB00),'Z',LS_CBFPMC,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,LS_DW0000,(LS_XMDJ00-LS_YBXMSX),LS_XMSL00,round((LS_XMDJ00-LS_YBXMSX)*LS_XMSL00,2),LS_ZWXM00,nvl(LS_BZCBBH,LS_CBXMBH),LS_CZRQ00, LS_FLAG00,LS_GZHCID,LS_SFFSHC,decode(LS_BZCBBH,null,LS_HCDBXM,'2'),nvl(LS_SFXMID,0), LV_YSZJHM,LS_YPTS00,LS_YPPL00,LS_YPYL00,LS_YSYGBH,PDDLSH0, LS_YNFPBH,LS_YNFPMC,LS_YBFPBH,LS_YBFPMC,LS_YNDW00,LS_YNDJ00,LS_YBDW00,LS_YBDDW0, LS_YBDZGJ,LS_YBXDW0,LS_YBXZGJ,LS_ZYID00,SQ_ZY_YBFSZX_MXID00.nextval,LS_YYKSBM,LS_YYKSMC,LS_YYBQBM,LS_YYBQMC); else--不做任何转换 if LS_SFCYGDDJ='Y' and LS_FLAG00=0 and LS_XMDJ00>LS_SFJE00 then LS_XMDJ00:=LS_SFJE00; end if; insert into ZY_YBFSMX( ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00, XMSL00,XMJE00,YSXM00,ZFXMBH,CZRQ00,FLAG00,GZHCID,SFFSHC, HCDBXM,SFXMID,YSZJHM,YPTS00,YPPL00,YPYL00,YSYGBH,DDLSH0, YNFPBH,YNFPMC,YBFPBH,YBFPMC,YNDW00,YNDJ00,YBDW00,YBDDW0, YBDZGJ,YBXDW0,YBXZGJ,ZYID00,MXID00,YYKSBM,YYKSMC,YYBQBM,YYBQMC) values(LS_ID0000,LS_YBXMBH,decode(LS_SFMLXZ,0,'Y',LS_SFYBXM),decode(trim(LS_YBLB00),'Z',LS_FPXMMC,decode(LS_SYFPBH,'Y',LS_FPXMBH,LS_FPXMMC)), decode(trim(LS_YBLB00),'Z',LS_XMMC00,decode(LS_YBMCFS,'Y',LS_YBXMMC,LS_XMMC00)), decode(LS_YBMCFS,'Y',LS_YBXMGG,LS_GG0000),LS_DW0000,LS_XMDJ00,LS_XMSL00,round(LS_XMDJ00*LS_XMSL00,2),LS_ZWXM00,LS_CBXMBH,LS_CZRQ00, LS_FLAG00,LS_GZHCID,LS_SFFSHC,LS_HCDBXM,nvl(LS_SFXMID,0), LV_YSZJHM,LS_YPTS00,LS_YPPL00,LS_YPYL00,LS_YSYGBH,PDDLSH0, LS_YNFPBH,LS_YNFPMC,LS_YBFPBH,LS_YBFPMC,LS_YNDW00,LS_YNDJ00,LS_YBDW00,LS_YBDDW0, LS_YBDZGJ,LS_YBXDW0,LS_YBXZGJ,LS_ZYID00,SQ_ZY_YBFSZX_MXID00.nextval,LS_YYKSBM,LS_YYKSMC,LS_YYBQBM,LS_YYBQMC); end if; end loop ; close CUR_ZY_FYMX00_MXID00; --修改发送操作日期 update ZY_YBFSMX a set CZRQ00=to_char(sysdate,'YYYYMMDD') where a.ID0000 =LS_ID0000 and CZRQ00 is null; -- 修改项目数量*单价不等于金额 修改项目数量 update ZY_YBFSMX a set XMSL00 = round(XMJE00/XMDJ00,4) where XMJE00 <> (XMDJ00 * XMSL00) and a.ID0000 =LS_ID0000 and a.XMDJ00<>0; --修改医生姓名和医生证件编码 update ZY_YBFSMX a set (YSXM00,YSZJHM)=(select ZWXM00,YSZJHM from BM_YGBM00 b where b.YGBH00=a.YSYGBH) where a.ID0000 =LS_ID0000 and a.YSYGBH is not null; --高职耗材打包项目参数开启执行--begin-- if (LS_HCJKQY='Y') or (LS_SFBZDY='Y') then --获取BM_YBFPXM 其它费 begin select BH0000,XMMC00 into LS_YBFPBH,LS_YBFPMC from BM_YBFPXM where YBZXLB = LS_YBZXLB and BH0000 in ('13'); exception when others then LS_YBFPBH:='13'; LS_YBFPMC:='其它费'; end; if LS_SYFPBH='Y' then LS_HCFPMC:=nvl(LS_YBFPBH,'13');--耗材打包发票项目编码和发票名称 else LS_HCFPMC:=nvl(LS_YBFPMC,'其它费');--耗材打包发票项目编码和发票名称 end if; select count(1)into LS_COUNT0 from ZY_YBFSMX where ID0000=LS_ID0000 and nvl(GZHCID,0)>0 and nvl(SFFSHC,'1')='1' and nvl(HCDBXM,'0')='0'; --插入耗材打包项目 if LS_COUNT0>0 then select SQ_ZY_YBFSZX_MXID00.nextval into LS_MXID00 from dual; insert into ZY_YBFSMX(ID0000,MXID00,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00, YSXM00,ZFXMBH,YSZJHM,YPTS00,YPPL00,YPYL00,FLAG00,GZHCID,SFFSHC, HCDBXM,SFXMID,YSYGBH,CZRQ00,DDLSH0,YNFPBH,YNFPMC,YBFPBH,YBFPMC, YNDW00,YNDJ00,YBDW00,YBDDW0,YBDZGJ,YBXDW0,YBXZGJ,ZYID00,YYKSBM,YYKSMC,YYBQBM,YYBQMC) select LS_ID0000,LS_MXID00,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),3,GZHCID,'1', '1',0,max(YSYGBH),max(CZRQ00),max(DDLSH0),max(YNFPBH),max(YNFPMC),max(YBFPBH),max(YBFPMC), max(YNDW00),max(YNDJ00),max(YBDW00),max(YBDDW0),max(YBDZGJ),max(YBXDW0),max(YBXZGJ),max(ZYID00), max(YYKSBM),max(YYKSMC),max(YYBQBM),max(YYBQMC) from ZY_YBFSMX where ID0000 =LS_ID0000 and nvl(GZHCID,0)>0 and nvl(SFFSHC,'1')='1' and nvl(HCDBXM,'0')='0' and ZYID00=LS_ZYID00 group by GZHCID; --修改打包项目的编码 (发票项目按"其他费"发送) update ZY_YBFSMX A set (A.XMBH00,A.XMDW00,A.XMMC00,A.FPXMMC) -- =(select B.HCDBBM,B.DW0000,B.HCDBMC,decode(ls_SYFPBH,'Y','13','其他费') =(select B.HCDBBM,B.DW0000,B.HCDBMC,LS_HCFPMC from BM_GZHCFL B where B.GZHCID=A.GZHCID and rownum=1) Where ID0000 = LS_ID0000 and nvl(A.GZHCID,0)>0 and nvl(A.SFFSHC,'1')='1' and nvl(A.HCDBXM,'0')='1' and ZYID00=LS_ZYID00; --把对应的明细数量和金额改成0 update ZY_YBFSMX set XMDJ00=0,XMJE00=0 where ID0000 = LS_ID0000 and nvl(GZHCID,0)>0 and nvl(SFFSHC,'1')='1' and nvl(HCDBXM,'0')='0' and ZYID00=LS_ZYID00; --2018.08.24 zhangyc for CUR_XE in CUR_HCDBXE loop --获取耗材打包项目是否超额,以及最高限额、超额编码、超额名称 begin select nvl(HCXZJE,0)HCXZJE,trim(HCCBBM)HCCBBM,trim(HCCBMC)HCCBMC into LS_HCXZJE,LS_HCCBBM,LS_HCCBMC from BM_GZHCFL where GZHCID =CUR_XE.GZHCID; exception when others then LS_HCXZJE:=0;--耗材限额 LS_HCCBBM:='';--超标编码 LS_HCCBMC:='';--超标名称 end; --超过设置的最高限价重新拆分一笔记录 if nvl(LS_HCXZJE,0)>0 and nvl(CUR_XE.XMJE00,0)>nvl(LS_HCXZJE,0) then if LS_HCCBBM is null then LS_HCCBBM:=substrb(trim(CUR_XE.XMBH00),1,30); end if; if LS_HCCBMC is null then LS_HCCBMC:=substrb(trim(CUR_XE.XMMC00),1,100); end if; --修改原耗材打包项目金额和项目单价 update ZY_YBFSMX set XMJE00=nvl(LS_HCXZJE,0),XMDJ00=nvl(LS_HCXZJE,0) where ID0000 = LS_ID0000 and GZHCID=CUR_XE.GZHCID and XMBH00=CUR_XE.XMBH00 and XMMC00=CUR_XE.XMMC00 and ZYID00=LS_ZYID00; select SQ_ZY_YBFSZX_MXID00.nextval into LS_MXID00 from dual; --重新插入超额项目 insert into ZY_YBFSMX(ID0000,MXID00,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00, YSXM00,ZFXMBH,YSZJHM,YPTS00,YPPL00,YPYL00,FLAG00,GZHCID,SFFSHC, HCDBXM,SFXMID,YSYGBH,CZRQ00,DDLSH0,YNFPBH,YNFPMC,YBFPBH,YBFPMC, YNDW00,YNDJ00,YBDW00,YBDDW0,YBDZGJ,YBXDW0,YBXZGJ,ZYID00,YYKSBM,YYKSMC,YYBQBM,YYBQMC) values(CUR_XE.ID0000,LS_MXID00,LS_HCCBBM,CUR_XE.SFYB00,CUR_XE.FPXMMC,LS_HCCBMC,CUR_XE.XMGG00,CUR_XE.XMDW00,nvl(CUR_XE.XMJE00,0)-nvl(LS_HCXZJE,0),1,nvl(CUR_XE.XMJE00,0)-nvl(LS_HCXZJE,0), CUR_XE.YSXM00,CUR_XE.ZFXMBH,CUR_XE.YSZJHM,CUR_XE.YPTS00,CUR_XE.YPPL00,CUR_XE.YPYL00,CUR_XE.FLAG00,CUR_XE.GZHCID,CUR_XE.SFFSHC, CUR_XE.HCDBXM,CUR_XE.SFXMID,CUR_XE.YSYGBH,CUR_XE.CZRQ00,CUR_XE.DDLSH0,CUR_XE.YNFPBH,CUR_XE.YNFPMC,CUR_XE.YBFPBH,CUR_XE.YBFPMC, CUR_XE.YNDW00,CUR_XE.YNDJ00,CUR_XE.YBDW00,CUR_XE.YBDDW0,CUR_XE.YBDZGJ,CUR_XE.YBXDW0,CUR_XE.YBXZGJ,CUR_XE.ZYID00, CUR_XE.YYKSBM,CUR_XE.YYKSMC,CUR_XE.YYBQBM,CUR_XE.YYBQMC); end if; end loop; end if; end if; --高职耗材打包项目参数开启执行--end-- select SQ_ZY_TEMP00_ID0000.nextval into PID0000 from dual; insert into ZY_YBFSMX(ID0000,MXID00,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00, XMSL00,XMJE00,YSXM00,ZFXMBH,YSZJHM,YPTS00,YPPL00,YPYL00, FLAG00,GZHCID,SFFSHC,HCDBXM,SFXMID,YSYGBH,CZRQ00,DDLSH0,YNFPBH,YNFPMC,YBFPBH,YBFPMC, YNDW00,YNDJ00,YBDW00,YBDDW0,YBDZGJ,YBXDW0,YBXZGJ,ZYID00,YYKSBM,YYKSMC,YYBQBM,YYBQMC) select PID0000,max(MXID00),XMBH00,SFYB00,FPXMMC,trim(XMMC00),XMGG00,max(XMDW00),XMDJ00, nvl(sum(XMSL00),0),nvl(sum(XMJE00),0),trim(max(YSXM00)),max(ZFXMBH), max(YSZJHM)YSZJHM,max(YPTS00)YPTS00,max(YPPL00)YPPL00,max(YPYL00)YPYL00, max(FLAG00),max(GZHCID),max(SFFSHC),max(HCDBXM),max(SFXMID),max(YSYGBH),max(CZRQ00),max(DDLSH0),max(YNFPBH),max(YNFPMC),max(YBFPBH),max(YBFPMC), max(YNDW00),max(YNDJ00),max(YBDW00),max(YBDDW0),max(YBDZGJ),max(YBXDW0),max(YBXZGJ),ZYID00,max(YYKSBM),max(YYKSMC),max(YYBQBM),max(YYBQMC) from ZY_YBFSMX where ID0000 = LS_ID0000 and ZYID00=LS_ZYID00 -- and FLAG00 not in ('0') group by XMBH00,SFYB00,FPXMMC,XMGG00,XMDJ00,trim(XMMC00),ZYID00; delete ZY_YBFSMX where ID0000 = LS_ID0000 and ZYID00=LS_ZYID00; --修改项目单位、项目规格长度20 update ZY_YBFSMX set XMGG00=substrb(nvl(trim(XMGG00),'无'),1,20),XMDW00=substrb(trim(XMDW00),1,20) where ZYID00=LS_ZYID00 and ID0000=PID0000; delete zy_ybfsmx where id0000=PID0000 and xmje00=0 and xmsl00=0; exception when no_data_found then PSYSMSG:='执行业务过程SP_ZY_YBJK00_DATA异常!'; rollback; when others then PSYSMSG:='执行业务过程SP_ZY_YBJK00_DATA异常!'; rollback; end;