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, --截至日期 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':SP_ZY_XSEFY0_YBJK00 调用 as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar, --系统提示的错误信息 ad_YEXM00 in char default '全部' --婴儿姓名 ) -- MODIFICATION HISTORY -- Person Date Comments -- lintj 2012.12.31 modify 生成住院收费所需的医保接口通用存储过程 -- zhanghr 2013.04.20 整改 -- jlg 2013.07.08 jlg 修改当as_FLAG00='2'时也删除负数的记录 -- qks 2013.08.06 SF_XT_GETFYBMLBH增加入参as_YPSL00,as_YPDW00 -- qks 2013.10.15 医保目录审批:医保上限判断条件由单价改为总金额 -- qks 2013.10.29 BM_ZYCFMX_TEMP00增加字段YSZJHM -- qks 2013.11.06 生成发送医保费用改为只使用表ZY_FYMX00数据,不关联表YF_YZYPSQ -- qks 2013.12.13 BM_ZYCFMX_TEMP00.YSZJHM改写BM_YGBM00.SFZBH0 -- qks 2013.12.16 BM_ZYCFMX_TEMP00.YSZJHM默认为* -- qks 2014.03.13 "挂号费"项目退掉也无法生成医保接口数据 -- qks 2014.11.25 超标床位费判断，发票项目名称改为包含"床位"2个字 -- qks 2015.06.22 省直医保“调整部分高值医用耗材（第一批）”,要求传送医保耗材打包编码以及名称 -- qks 2015.11.26 启用住院医保费用监管功能,参数为ZY_YBFYJG by ZYSF9-20151129-001 -- xuzw 2016.01.19 由于政和规格可能为空，导致负数无法整合到正数中 故修改：AND XMGG00=XNH.XMGG00 -->AND NVL(XMGG00,'0')=NVL(XNH.XMGG00,'0') -- qks 2016.01.20 省直医保要求传送医保耗材打包编码以及名称,对应明细耗材单价以及金额改为0 by ZYSF9-20160122-001 -- qks 2016.01.26 打包高值耗材,发送给医保的发票项目需要改为“其他费” by ZYSF9-20160127-001 -- qks 2016.08.01 医保卡号K50220488，农合病人发送给医保中心的合计金额比院内合计金额少1分钱：主要是因为药品费用分次退费,最后一次退费合计金额<>单价*数量,差了1分钱（ZY_FYMX00.HJJE00-round(XMDJ00*XMSL00,2)=0.01); by ZYSF9-20160801-001 -- jlg 2016.11.25 增加出院病人医保补充发送的处理 ZYSF9-20161123-001 -- qks 2016.12.23 写入BM_ZYCFMX_TEMP00.YSZJHM,如果该医生的员工性质不包含处方医生，那么改用主管医生。for ZYSF9-20161223-001 -- qks 2017.03.13 省属医院单病种：超标床位费以及除外耗材单独写入。for ZYSF9-20170309-002 -- qks 2017.03.21 省属医院单病种：包床费用也算超标床位费。for ZYSF9-20170321-002 -- qks 2017.04.18 删除表BM_ZYCFMX_TEMP00中项目数量以及合计金额都为0记录。for ZYSF9-20170418-004 -- jlg 2017.05.08 预结算(as_FLAG00='4')金额为0的项目做删除处理 ZYSF9-20170210-001 -- qks 2017.05.11 增加参数SF_SFQYYBXJJK控制医保单位发送功能; for ZYSF9-20170512-001 -- qks 2017.05.23 存在药品数量为0问题; for MZSF9-20170523-001 -- jlg 2017.07.05 修改 CUR_ZY_FYMX00_MXID00 的取值问题 ZYSF9-20170627-001 -- jlg 2017.07.06 处理厦门医保有发送金额为0的项目的问题 ZYSF9-20170628-001 -- qks 2017.07.17 省人民反馈发送给医保费用合计比院内合计金额多出来问题; for ZYSF9-20170717-001 -- qks 2017.08.18 省人民反馈发送给医保药品单价超出限价,但是费用明细表中的单价没有超出限价; for ZYSF9-20170818-001 -- qks 2017.08.21 出现发给医保药品单位有经过转换,该条明细药品记录无法发送给医保问题; for ZYSF9-20170823-001 -- qks 2017.10.28 增加分娩生育病人超标床位费拆分发送功能：发送医保接口的项目编号使用BM_FPXM00.BZCBBH值; for ZYSF9-20171028-002 -- qks 2017.11.12 使用参数SF_YBFSXMZH控制:发送医保明细项目时,改为对应医保中心项目发送; for ZYSF9-20171110-001 -- qks 2018.01.06 CDRG结算增加超标床位费和超标高值耗材处理; for ZYSF9-20180106-002 -- qks 2018.01.11 CDRG结算：非超标高值耗材也要跟超标类似处理,需要再发送一条记录; for ZYSF9-20180111-001 -- qks 2018.01.20 CDRG结算超标床位费：正常明细中传送床位费单价改用原价,包括包床费用也算超标床位费; for ZYSF9-20180123-001 -- qks 2018.02.02 ls_YPYL00定义从varchar2(20)改为varchar2(10); for ZYSF9-20180202-002 -- jlg 2018.03.13 CUR_ZY_FYMX00_MXID00的 and a.YBFSDH='0' 条件去掉 ZYSF9-20180309-005 -- jlg 2018.03.14 SP_ZY_XSEFY0_YBJK00改用CUR_ZY_FYMX00_MXID06的，主要修改医生用主管医生的 ZYSF9-20180303-001 (bug修改补归档) -- qks 2018.04.04 当开单医生非处方医生,医生姓名也要改用主管医生姓名;修正BM_ZYCFMX_TEMP00.YSZJHM,YSXM00不匹配记录; for ZYSF9-20180404-002 -- qks 2018.04.09 ZY_BRXXB0.DBZBM0单病种：当参数ZY_YBDBZJSJK=Y,也处理耗材打包以及超标床位费。for ZYSF9-20180321-002 -- qks 2018.04.12 编号为011090000103的陪伴床,也算超标床位费。for ZYSF9-20180227-001 -- jlg 2018.04.19 处理新生儿医保费用发送时药品费用翻倍的问题CUR_ZY_FYMX00_MXID06 ZYSF9-20180419-001 -- gzj 2018.04.26 开单医生改用主管医生(厦门医保) ZYSF9-20180424-007 -- qks 2018.05.17 修正BM_ZYCFMX_TEMP00.YSZJHM,YSXM00不匹配记录 :增加条件ID0000=ad_ID0000 ; for ZYSF9-20180517-001 -- qks 2018.05.29 修改单价超过最高限价拆分参数：ZY_YBYPZGXJCF参数控制; for ZYSF9-20180601-004 -- qks 2018.07.21 根据前台选择具体的耗材打包项目(ZY_BRXXB0.HCDBBM)发送；以及当ZY_BRXXB0.HCDBBM为空（即前台不选择）,患者存在同一个收费项目,同时属于同个单病种下的2个打包耗材情况; for ZYSF9-20180724-002 -- pwt 2018.08.29 医保结算时，只要有被标记为自费结算,就是当ZFFSBZ='Y'时按自费发送; for ZYSF9-20180724-001 -- qks 2018.09.25 当C-DRG接口启用的时候，如果ZY_FYMX00.XMMC00有带包字的床位费,那么此项目要求发送为这个编码99000000000005; for ZYSF9-20180919-001 -- jlg 2018.10.10 支持同库同项目不同分院等级不同收费标准的模式改造之SFSX00的取值修改 ZYSF9-20181010-001 -- qks 2018.12.04 解决当参数ZY_YBYPZGXJCF启用时,超出限价拆分出来的记录,缺失“药品用量、用药天数、用药频率”内容问题; for ZYSF9-20181203-002 -- liwm 2019.01.17 在单病种情况下所有医保发送规则按单病种走 ZYSF9-20190115-001 -- gzj 2019.03.18 新增函数SF_SF_YWJZMC，如果结算的项目在通用字典中维护，生成的项目名称之后加(院外) for ZYSF9-20190318-001 -- liwm 2019.03.22 材料打包限价拆分 ZYSF9-20190315-002 -- gzj 2019.03.28 在进行限额拆分时，增加判断药品的BM_ZDSX00.VALUE0(NAME00='SFCF00')是否维护为Y，如果是Y，才允许进行拆分 for ZYSF9-20190328-001 -- gzj 2019.04.08 修改项目没有医保对应的提示内容 for ZYSF9-20190402-001 -- qks 2019.04.24 当LS_YBXJQY='Y',BM_ZYCFMX_TEMP00.HJJE00<>round(XMDJ00*XMSL00,2),改为单价保留不变,修改项目数量。 for ZYSF9-20190424-001 -- qks 2019.06.11 付费单病种结算，对应病种VW_ZY_DBZHC0.NBBH00值写到ZY_YBFSMX.GZHCID字段中。for ZYSF9-20190611-001 -- qks 2019.06.11 增加HIS5.0单病种维护方案,VW_ZY_DBZHC0关联表BM_GZHCFL数据,通过字段FLAG00标识。for ZYSF9-20190612-001 -- jlg 2019.06.22 截取BM_YBSFXM.YPGG00的长度，以免写入中间表时长度太长报错 ZYSF9-20190622-001 -- qks 2019.06.25 新增参数ZY_PBCCBCW控制：陪伴床费用是否计入超标床位费,0否1是,默认1; for ZYSF9-20190625-001 -- qks 2019.09.04 修正医保耗材打包bug; for ZYSF9-20190905-001 -- qks 2019.11.13 参数ZY_PBCCBCW功能扩展; for ZYSF9-20191112-001 -- qks 2019.12.10 改为不删除表BM_ZYCFMX_TEMP00条件为(nvl(XMJE00,0)<=0) or (nvl(XMSL00,0)<=0))记录; for ZYSF9-20191210-001 -- liwm 2019.12.14 漳州修改 ZYSF9-20191214-002 -- liwm 2019.12.18 解决漳州地区aka063为空问题 ZYSF9-20191218-001 -- jlg 2020.01.03 处理跨年结算之年后冲销的费用（单项目总合计金额小于0）一起合并到中途结算中结算的问题 ZYSF9-20200103-001 -- qks 2020.02.03 ZY_BRZHXX增加字段TXYLFW,单独保存单病种对应的特需医疗服务费用; for ZYSF9-20200203-002 -- jlg 2020.02.07 处理项目单价为0的也不发送医保，中间表直接删除 ZYSF9-20200207-001 -- jlg 2020.02.10 修改插入ZY_JSBCMX时默认JZDH00为0 ZYSF9-20200209-001 -- qks 2020.02.18 生成request.YPTS00超过医保限制范围（最高只允许999）; for ZYSF9-20200218-001 -- qks 2020.03.10 包床项目的费用定义为bm_yysfxm.xmmc00中包含“包床”两个字的,也算入超标床位费; for ZYSF9-20200305-002 -- qks 2020.03.19 修改：判断实际单价是否超过医保上线,如果是要拆分成两条记录; for ZYSF9-20200319-001 -- qks 2020.04.10 修改：XMSL00改为只保留2位小数; for ZYSF9-20200410-001 -- jlg 2020.04.23 增加部分药品或项目仅支持门诊统筹报销，住院不允许报销的处理 ZYSF9-20200303-001 -- qks 2020.05.08 药品医保项目单位转化：不能重新计算LS_XMDJ00; for ZYSF9-20200508-003; -- qks 2020.05.14 费用明细表游标对应药品先汇总处理;新增参数ZY_FSYBXMSL_SRWS:控制发送医保项目数量的小数位保留位数。for ZYSF9-20200514-002 -- qks 2020.05.23 解决宁德康复升级库出现医保结算速度很慢问题,优化VW_BM_YBSFDY_DJBZ相关SQL执行速度慢; for ZYSF9-20200525-001 -- qks 2020.06.18 减免金额值ZY_FYMX00.JMJE00，改为全部不参与医保发送计算; for ZYSF9-20200618-001 -- qks 2020.07.09 材料审批修改：针对需要审批病人的材料费用，以医保形式发送金额应小于等于医保办审批金额。for ZYSF9-20200709-003 -- qks 2020.08.07 沙县CDRG患者,指定床位项目不能转为超标床位费,通过BM_TYZD00.ZDMC00='住院单病种床位费超限价不转超标' 进行控制。for ZYSF9-20200810-001 -- qks 2020.08.17 修改：住院单病种床位费直接转超标; as 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; --住院医保临时表ID ls_FYBQZ0 IC_YBBRLB.FYBQZ0%type; --非医保项目编号前缀(首2位) 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_XMBM00 varchar2(20); --项目编码 ls_BCBZ00 number(5); --包床标志 ls_SFXNH0 IC_YBBRLB.SFXNH0%type; --是否新农合 ls_ZYZFSX_TEMP BM_YBBRLB.ZYZFSX%type; -- ls_YBMCFS char(1); --是否按医保项目名称发送医保接口 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_XNHSFRQ char(1); --农合病人实时结算是否发送费用日期 ls_CZRQ00 ZY_FYMX00.CZRQ00%type; --操作日期 ls_YCXCLXMXGS char(1); --一次性材料项目格式是否改为 医保对应项目+(医院收费项目)。 ls_XMYBJK XT_XTCS00.VALUE0%type; --是否厦门医保接口 ls_YBYPSP number(2); --医保病人药品是否需要审批 ls_SFTHML number(3); --医保病人药品是否替换成自费药品 0不替换 1替换 ls_JSXM01 BM_YYSFXM.JSXM00%type; -- ls_CLSHBZ char(1); --是否需要审核标志 ls_XMDJSX XT_XTCS00.VALUE0%type; --需要审批的单价上限 ls_SPJE00 YK_YBMLSP.SPJE00%type; --材料已经审批的单价上限金额 ls_WDYBC0 varchar2(2); --项目没有进行医保对应时，报错 ls_YSZJHM BM_YGBM00.SFZBH0%type; --医生证件号码 ls_FPXMMC_MZ BM_YBFPXM.XMMC00%type; --门诊专用发票项目名称 ls_GJBM00 BM_YYSFXM.GJBM00%type; --材料项目国家编码 ls_CLDBBH BM_TYZD00.NBBH00%type; --医保材料打包编码对应的通用字段内部编号 ls_GJBMZH varchar2(200); --国家编码前缀组合,用逗号分隔 ls_YPYL00 varchar2(10); --计量数量+单位 ls_YPTS00 varchar2(6); --用药天数 ls_YPPL00 varchar2(6); --频次编码 ls_YGXZ00 BM_YGBM00.YGXZ00%type; --医生证件号码 ls_HJJE00_TEMP ZY_FYMX00.HJJE00%type; -- ls_FFBZBM ZY_BRXXB0.FFBZBM%type; --省属医院付费病种编码 ls_BZCBBH BM_YBFPXM.BZCBBH%type; --单病种超标医保发票项目编号 ls_BZCBCW ZY_BRZHXX.DBZCWF%type; --单病种超标床位费 ls_BZLSHC ZY_BRZHXX.DBZHCF%type; --单病种另收耗材费 ls_TXYLFW ZY_BRZHXX.TXYLFW%type; --单病种特需医疗服务费用 ls_BZGJBM varchar2(200); --单病种允许另收耗材的国家编码前缀组合,用逗号分隔 ls_GZHCID BM_GZHCFL.GZHCID%type; --单病种高值耗材ID LS_YBXJQY varchar2(10); --门诊或住院系统是否启用医保限价接口 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.ZXJJDW%type; --医保项目单位（小单位） ls_RYLB00 ZY_BRXXB0.RYLB00%type; --入院类别 ls_FMBQBH ZY_BRXXB0.FMBQBH%type; --分娩病情编号 ls_FMCWCF char(1); --是否启用分娩生育病人超标床位费拆分发送:1启用,0或空值不启用 ls_BRZTQK BM_BRXXB0.BRZTQK%type; --病人职退情况 LS_YBFSZH XT_XTCS00.VALUE0%type; --参数：当病人职退情况(BM_BRXXB0.BRZTQK)包含在该参数值,则发送医保明细项目时,改为对应医保中心项目发送 LS_CDRGFS ZY_BRXXB0.CDRGFS%type; --CDRG发送标识 ls_CDRGJK XT_XTCS00.VALUE0%type; --参数：住院收费医保结算是否启用CDRG流程 0:不启用 1:启用，默认0 LS_CLYBSX number(12,2) ; --医保上限金额(材料) LS_CLCBBH varchar2(50) ; --超标项目编号(材料) LS_CLCBMC varchar2(100); --超标项目名称(材料) LS_BZCBMC BM_YBFPXM.BZCBMC%type; --单病种超标发票项目名称 LS_SFCFXM varchar2(10) ; --是否拆分项目 Y:材料(超标) M:床位费 M1:包床 N:否 L:材料(非超标) LS_DBZBM0 ZY_BRXXB0.DBZBM0%type; --单病种编码 ls_DBZJK0 XT_XTCS00.VALUE0%type; --医保单病种结算接口参数值 LS_YBZGDJ BM_YBSFXM.YBZGDJ%type; --医保最高单价(大单位) LS_YBZGDJ1 BM_YBSFXM.YBZGDJ1%type; --医保最高单价(小单位) ls_YBZGXJ XT_XTCS00.VALUE0%type; --住院收费医保药品超过医保最高限价是否拆分参数格式:(类别,超额收费项目或药品ID,是否药品) LS_ZGXJID number(12); --医保药品最高限价拆分项目ID ls_ZGXJCF varchar2(1); --医保药品最高限价拆分 Y:拆分 N:不拆分 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); --限价拆分数量 LS_HCDBBM ZY_BRXXB0.HCDBBM%type; --单病种：住院收费处选择的耗材打包编码 ls_ZFFSBZ ZY_FYMX00.ZFFSBZ%type; --自费发送标志:N否,Y是 ls_SFBZDJ XT_YYXX00.SFBZDJ%type; --收费标准等级 ls_HCYBXJ BM_TYZD00.DJ0000%type; --耗材医保限价,如果大于0要进行限价处理 ls_HCYBJE BM_TYZD00.DJ0000%type; --耗材医保合并的金额 LS_SFCF00 BM_ZDSX00.VALUE0%type; --超过医保限价是否拆分 Y拆分 N不拆分 ls_PBCBCW XT_XTCS00.VALUE0%type; --参数:医保超标床位费处理规则,当参数值为1:陪伴床费用计入超标床位费,2:普通病人的陪伴床、超过BM_YBBRLB.CWZGBZ都不拆分为自费发送,只有特殊病人病人拆分;默认为1 ls_DBZLX0 VW_ZY_DBZHC0.FLAG00%type; --单病种类型：1 BM_TYZD00(HIS9),2 BM_GZHCFL(HIS5模式) ls_SSWRWS number(1); --取设置医保项目数量的小数位保留位数 ls_CBCWF0 number(1); --住院单病种床位费直接转超标:1是,0否 ls_SPJE01 YK_YBMLSP.SPJE00%type; --材料已审批的单价上限金额(已使用) cursor CUR_ZY_FYMX00_MXID00 is select '0'FLAG00,a.MXID00,a.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, a.XMDJ00, a.XMSL00,a.HJJE00,a.ZFBL00,a.CZRQ00,C.JSXM00,nvl(e.SFZBH0,'*'),C.GJBM00, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'2'),''),a.XMSL00||a.XMDW00),1,10) as YPYL00, --计量数量+单位 decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'1'),'1'),'1') as YPTS00, --用药天数 decode(a.SFLB00,'3',nvl(SF_SF_GETPCBM('',a.yjjzid,'1'),'0240'),'*') as YPPL00, --医保频次编码 e.YGXZ00, a.ZFFSBZ, c.BH0000 from ZY_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,BM_YGBM00 E where a.JZDH00 = ad_JZDH00 and a.XMBH00 = C.SFXMID and C.ZYFPID = B.FPXMID and a.KDYS00=e.YGBH00 and B.SFYLXM='Y' and a.XMBH00<9999999900 --将西药,草药,成药,材料的去掉(以及将专门减免的也去掉9999999901~9999999990 jlg) and a.JMBZ00<>'2' ----如果JMBZ00='4'(事前减免),则发送医保时要把这部分减免的金额也发送给医保 for ZYSF-20070124-001 and a.SFLB00<>'3' union all select '2'FLAG00,max(a.MXID00),sum(a.ZFJE00),substrb(c.YPMC00,1,50) YPMC00,0,--包床标志 substrb(nvl(c.YPGG00,'无'),1,10) YPGG00,nvl(a.XMDW00,'无')CFDW00,a.XMBH00,max(e.ZWXM00), a.XMDJ00, sum(a.XMSL00),sum(a.HJJE00) HJJE00,max(a.ZFBL00),max(a.CZRQ00),'0',nvl(max(e.SFZBH0),'*'),null, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(max(a.yjjzid),0,'2'),''),max(a.XMSL00||a.XMDW00)),1,10) as YPYL00, --计量数量+单位 to_char(sum(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'1'),'1'),'1'))) as YPTS00, --用药天数 decode(a.SFLB00,'3',nvl(SF_SF_GETPCBM('',max(a.yjjzid),'1'),'0240'),'*') as YPPL00, --医保频次编码 max(e.YGXZ00), a.ZFFSBZ, c.YPBM00 from ZY_FYMX00 A,BM_YD0000 C,BM_YGBM00 E where a.JZDH00 = ad_JZDH00 and a.XMBH00 = C.YPNM00 and a.KDYS00=e.YGBH00 and a.JMBZ00<>'2' ----如果JMBZ00='4'(事前减免),则发送医保时要把这部分减免的金额也发送给医保 for ZYSF-20070124-001 and a.SFLB00='3' group by a.JMBZ00,c.YPMC00,c.YPGG00,a.XMDW00,a.XMBH00,a.XMDJ00,a.SFLB00,a.ZFFSBZ,c.YPBM00; cursor CUR_ZY_FYMX00_MXID01 is select '0'FLAG00,a.MXID00,a.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, a.XMDJ00, a.XMSL00,a.HJJE00,a.ZFBL00,a.CZRQ00,C.JSXM00,nvl(e.SFZBH0,'*'),C.GJBM00, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'2'),''),a.XMSL00||a.XMDW00),1,10) as YPYL00, --计量数量+单位 decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'1'),'1'),'1') as YPTS00, --用药天数 decode(a.SFLB00,'3',nvl(SF_SF_GETPCBM('',a.yjjzid,'1'),'0240'),'*') as YPPL00, --医保频次编码 e.YGXZ00, a.ZFFSBZ, c.BH0000 from ZY_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,BM_YGBM00 E where a.XMBH00 = C.SFXMID and C.ZYFPID = B.FPXMID and a.KDYS00 = e.YGBH00 and B.SFYLXM = 'Y' and a.XMBH00<9999999900 --将西药,草药,成药,材料的去掉(以及将专门减免的也去掉 jlg) and a.ZYID00 = ad_ZYID00 and a.JMBZ00<>'2' and a.YBFSDH='0' and ((a.DJH000>=ad_QSDJH0 and a.DJH000<=ad_ZZDJH0 and a.CZRQ00>=as_QSRQ00 and a.CZRQ00<=as_ZZRQ00) or (a.MXID00 in (select MXID00 from ZY_JSBCMX where ZYID00 = a.ZYID00 and ID0000 = ad_ID0000)) ) and a.JZDH00+0=0 and (decode(nvl(a.YEXM00,'0'),'0','母亲',a.YEXM00)=ad_YEXM00 or ad_YEXM00='全部') and a.SFLB00<>'3' union all select '2'FLAG00,max(a.MXID00),sum(a.ZFJE00),substrb(c.YPMC00,1,50) YPMC00,0,--包床标志 substrb(nvl(c.YPGG00,'无'),1,10) YPGG00,nvl(a.XMDW00,'无')CFDW00,a.XMBH00,max(e.ZWXM00), a.XMDJ00, sum(a.XMSL00),sum(a.HJJE00),max(a.ZFBL00),max(a.CZRQ00),'0',nvl(max(e.SFZBH0),'*'),null, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(max(a.yjjzid),0,'2'),''),max(a.XMSL00||a.XMDW00)),1,10) as YPYL00, --计量数量+单位 --decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'1'),'1'),'1') as YPTS00, --用药天数 to_char(sum(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'1'),'1'),'1'))) as YPTS00, --用药天数 decode(a.SFLB00,'3',nvl(SF_SF_GETPCBM('',max(a.yjjzid),'1'),'0240'),'*') as YPPL00, --医保频次编码 max(e.YGXZ00), a.ZFFSBZ, c.YPBM00 from ZY_FYMX00 A,BM_YD0000 C,BM_YGBM00 E where a.XMBH00 = C.YPNM00 and a.KDYS00 = e.YGBH00 and a.ZYID00 = ad_ZYID00 and a.JMBZ00<>'2' and a.YBFSDH='0' and ((a.DJH000>=ad_QSDJH0 and a.DJH000<=ad_ZZDJH0 and a.CZRQ00>=as_QSRQ00 and a.CZRQ00<=as_ZZRQ00) or (a.MXID00 in (select MXID00 from ZY_JSBCMX where ZYID00 = a.ZYID00 and ID0000 = ad_ID0000)) ) and a.JZDH00+0=0 and (decode(nvl(a.YEXM00,'0'),'0','母亲',a.YEXM00)=ad_YEXM00 or ad_YEXM00='全部') and a.SFLB00='3' group by a.JMBZ00,c.YPMC00,c.YPGG00,a.XMDW00,a.XMBH00,a.XMDJ00,a.SFLB00,a.ZFFSBZ,c.YPBM00; --a.SFLB00='3':'0'FLAG00->'2'FLAG00 cursor CUR_ZY_FYMX00_MXID02 is select '0'FLAG00,a.MXID00,a.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, a.XMDJ00, a.XMSL00,a.HJJE00,a.ZFBL00,a.CZRQ00,C.JSXM00,nvl(e.SFZBH0,'*'),C.GJBM00, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'2'),''),a.XMSL00||a.XMDW00),1,10) as YPYL00, --计量数量+单位 decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'1'),'1'),'1') as YPTS00, --用药天数 decode(a.SFLB00,'3',nvl(SF_SF_GETPCBM('',a.yjjzid,'1'),'0240'),'*') as YPPL00, --医保频次编码 e.YGXZ00, a.ZFFSBZ, c.BH0000 from ZY_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,BM_YGBM00 E where a.XMBH00 = C.SFXMID and C.ZYFPID = B.FPXMID and a.KDYS00=e.YGBH00 and not exists (select 1 from BM_RQDFS0 where ZYID00 = a.ZYID00 and QDRQ00=a.CZRQ00) and B.SFYLXM='Y' and a.XMBH00<9999999900 --将西药,草药,成药,材料的去掉(以及将专门减免的也去掉 jlg) and a.ZYID00=ad_ZYID00 and a.JMBZ00<>'2' and a.YBFSDH='0' and a.JZDH00+0=0 and (decode(nvl(a.YEXM00,'0'),'0','母亲',a.YEXM00)=ad_YEXM00 or ad_YEXM00='全部') and a.SFLB00<>'3' union all select '2'FLAG00,max(a.MXID00),sum(a.ZFJE00),substrb(c.YPMC00,1,50) YPMC00,0,--包床标志 substrb(nvl(c.YPGG00,'无'),1,10) YPGG00,nvl(a.XMDW00,'无')CFDW00,a.XMBH00,max(e.ZWXM00), a.XMDJ00, sum(a.XMSL00),sum(a.HJJE00),max(a.ZFBL00),max(a.CZRQ00),'0',nvl(max(e.SFZBH0),'*'),null, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(max(a.yjjzid),0,'2'),''),max(a.XMSL00||a.XMDW00)),1,10) as YPYL00, --计量数量+单位 --decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'1'),'1'),'1') as YPTS00, --用药天数 to_char(sum(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'1'),'1'),'1'))) as YPTS00, --用药天数 decode(a.SFLB00,'3',nvl(SF_SF_GETPCBM('',max(a.yjjzid),'1'),'0240'),'*') as YPPL00, --医保频次编码 max(e.YGXZ00), a.ZFFSBZ, c.YPBM00 from ZY_FYMX00 A,BM_YD0000 C,BM_YGBM00 E where a.XMBH00 = C.YPNM00 and a.KDYS00=e.YGBH00 and not exists (select 1 from BM_RQDFS0 where ZYID00 = a.ZYID00 and QDRQ00=a.CZRQ00) and a.ZYID00=ad_ZYID00 and a.JMBZ00<>'2' and a.YBFSDH='0' and a.JZDH00+0=0 and (decode(nvl(a.YEXM00,'0'),'0','母亲',a.YEXM00)=ad_YEXM00 or ad_YEXM00='全部') and a.SFLB00='3' group by a.JMBZ00,c.YPMC00,c.YPGG00,a.XMDW00,a.XMBH00,a.XMDJ00,a.SFLB00,a.ZFFSBZ,c.YPBM00; cursor CUR_ZY_FYMX00_MXID03 is select '0'FLAG00,a.MXID00,a.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, a.XMDJ00, a.XMSL00,a.HJJE00,a.ZFBL00,a.CZRQ00,C.JSXM00,nvl(e.SFZBH0,'*'),C.GJBM00, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'2'),''),a.XMSL00||a.XMDW00),1,10) as YPYL00, --计量数量+单位 decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'1'),'1'),'1') as YPTS00, --用药天数 decode(a.SFLB00,'3',nvl(SF_SF_GETPCBM('',a.yjjzid,'1'),'0240'),'*') as YPPL00, --医保频次编码 e.YGXZ00, a.ZFFSBZ, c.BH0000 from ZY_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,BM_YGBM00 E where a.XMBH00 = C.SFXMID and C.ZYFPID = B.FPXMID and a.KDYS00=e.YGBH00 and B.SFYLXM='Y' and a.XMBH00<9999999900 --将西药,草药,成药,材料的去掉(以及将专门减免的也去掉 jlg) and a.ZYID00=ad_ZYID00 and a.JMBZ00<>'2' and a.JZDH00+0=0 and (decode(nvl(a.YEXM00,'0'),'0','母亲',a.YEXM00)=ad_YEXM00 or ad_YEXM00='全部') and a.SFLB00<>'3' union all select '2'FLAG00,max(a.MXID00),sum(a.ZFJE00),substrb(c.YPMC00,1,50) YPMC00,0,--包床标志 substrb(nvl(c.YPGG00,'无'),1,10) YPGG00,nvl(a.XMDW00,'无')CFDW00,a.XMBH00,max(e.ZWXM00), a.XMDJ00, sum(a.XMSL00),sum(a.HJJE00),max(a.ZFBL00),max(a.CZRQ00),'0',nvl(max(e.SFZBH0),'*'),null, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(max(a.yjjzid),0,'2'),''),max(a.XMSL00||a.XMDW00)),1,10) as YPYL00, --计量数量+单位 --decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'1'),'1'),'1') as YPTS00, --用药天数 to_char(sum(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'1'),'1'),'1'))) as YPTS00, --用药天数 decode(a.SFLB00,'3',nvl(SF_SF_GETPCBM('',max(a.yjjzid),'1'),'0240'),'*') as YPPL00, --医保频次编码 max(e.YGXZ00), a.ZFFSBZ, c.YPBM00 from ZY_FYMX00 A,BM_YD0000 C,BM_YGBM00 E where a.XMBH00 = C.YPNM00 and a.KDYS00=e.YGBH00 and a.ZYID00=ad_ZYID00 and a.JMBZ00<>'2' and a.JZDH00+0=0 and (decode(nvl(a.YEXM00,'0'),'0','母亲',a.YEXM00)=ad_YEXM00 or ad_YEXM00='全部') and a.SFLB00='3' group by a.JMBZ00,c.YPMC00,c.YPGG00,a.XMDW00,a.XMBH00,a.XMDJ00,a.SFLB00,a.ZFFSBZ,c.YPBM00; cursor CUR_ZY_FYMX00_MXID05 is select '0'FLAG00,a.MXID00,a.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, a.XMDJ00, a.XMSL00,a.HJJE00,a.ZFBL00,a.CZRQ00,C.JSXM00,nvl(e.SFZBH0,'*'),C.GJBM00, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'2'),''),a.XMSL00||a.XMDW00),1,10) as YPYL00, --计量数量+单位 decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'1'),'1'),'1') as YPTS00, --用药天数 decode(a.SFLB00,'3',nvl(SF_SF_GETPCBM('',a.yjjzid,'1'),'0240'),'*') as YPPL00, --医保频次编码 e.YGXZ00, a.ZFFSBZ, c.BH0000 from ZY_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,BM_YGBM00 E where a.XMBH00 = C.SFXMID and C.ZYFPID = B.FPXMID and a.KDYS00=e.YGBH00 and a.DJH000>=ad_QSDJH0 and a.DJH000<=ad_ZZDJH0 and B.SFYLXM='Y' and a.XMBH00<9999999900 --将西药,草药,成药,材料的去掉(以及将专门减免的也去掉 jlg) and a.ZYID00=ad_ZYID00 and a.JMBZ00<>'2' and a.CZRQ00>=as_QSRQ00 and a.CZRQ00<=as_ZZRQ00 and (decode(nvl(a.YEXM00,'0'),'0','母亲',a.YEXM00)=ad_YEXM00 or ad_YEXM00='全部') and a.SFLB00<>'3' union all select '2'FLAG00,max(a.MXID00),sum(a.ZFJE00),substrb(c.YPMC00,1,50) YPMC00,0,--包床标志 substrb(nvl(c.YPGG00,'无'),1,10) YPGG00,nvl(a.XMDW00,'无')CFDW00,a.XMBH00,max(e.ZWXM00), a.XMDJ00, sum(a.XMSL00),sum(a.HJJE00),max(a.ZFBL00),max(a.CZRQ00),'0',nvl(max(e.SFZBH0),'*'),null, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(max(a.yjjzid),0,'2'),''),max(a.XMSL00||a.XMDW00)),1,10) as YPYL00, --计量数量+单位 --decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'1'),'1'),'1') as YPTS00, --用药天数 to_char(sum(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'1'),'1'),'1'))) as YPTS00, --用药天数 decode(a.SFLB00,'3',nvl(SF_SF_GETPCBM('',max(a.yjjzid),'1'),'0240'),'*') as YPPL00, --医保频次编码 max(e.YGXZ00), a.ZFFSBZ, c.YPBM00 from ZY_FYMX00 A,BM_YD0000 C,BM_YGBM00 E where a.XMBH00 = C.YPNM00 and a.KDYS00=e.YGBH00 and a.DJH000>=ad_QSDJH0 and a.DJH000<=ad_ZZDJH0 and a.ZYID00=ad_ZYID00 and a.JMBZ00<>'2' and a.CZRQ00>=as_QSRQ00 and a.CZRQ00<=as_ZZRQ00 and (decode(nvl(a.YEXM00,'0'),'0','母亲',a.YEXM00)=ad_YEXM00 or ad_YEXM00='全部') and a.SFLB00='3' group by a.JMBZ00,c.YPMC00,c.YPGG00,a.XMDW00,a.XMBH00,a.XMDJ00,a.SFLB00,a.ZFFSBZ,c.YPBM00; --用SXYS00来取对应医生 cursor CUR_ZY_FYMX00_MXID06 is select '0'FLAG00,a.MXID00,a.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, a.XMDJ00, a.XMSL00,a.HJJE00,a.ZFBL00,a.CZRQ00,C.JSXM00,nvl(e.SFZBH0,'*'),C.GJBM00, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'2'),''),a.XMSL00||a.XMDW00),1,10) as YPYL00, --计量数量+单位 decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'1'),'1'),'1') as YPTS00, --用药天数 decode(a.SFLB00,'3',nvl(SF_SF_GETPCBM('',a.yjjzid,'1'),'0240'),'*') as YPPL00, --医保频次编码 e.YGXZ00, a.ZFFSBZ, c.BH0000 from ZY_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,BM_YGBM00 E,ZY_BRXXB0 f where a.JZDH00 = ad_JZDH00 and a.ZYID00 = f.ZYID00 and a.XMBH00 = C.SFXMID and C.ZYFPID = B.FPXMID and f.SXYS00=e.YGBH00 and B.SFYLXM='Y' and a.XMBH00<9999999900 --将西药,草药,成药,材料的去掉(以及将专门减免的也去掉9999999901~9999999990 jlg) and a.JMBZ00<>'2' ----如果JMBZ00='4'(事前减免),则发送医保时要把这部分减免的金额也发送给医保 for ZYSF-20070124-001 and a.SFLB00<>'3' union all select '2'FLAG00,max(a.MXID00),sum(a.ZFJE00),substrb(c.YPMC00,1,50) YPMC00,0,--包床标志 substrb(nvl(c.YPGG00,'无'),1,10) YPGG00,nvl(a.XMDW00,'无')CFDW00,a.XMBH00,max(e.ZWXM00), a.XMDJ00, sum(a.XMSL00),sum(a.HJJE00),max(a.ZFBL00),max(a.CZRQ00),'0',nvl(max(e.SFZBH0),'*'),null, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(max(a.yjjzid),0,'2'),''),max(a.XMSL00||a.XMDW00)),1,10) as YPYL00, --计量数量+单位 --decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'1'),'1'),'1') as YPTS00, --用药天数 to_char(sum(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'1'),'1'),'1'))) as YPTS00, --用药天数 decode(a.SFLB00,'3',nvl(SF_SF_GETPCBM('',max(a.yjjzid),'1'),'0240'),'*') as YPPL00, --医保频次编码 max(e.YGXZ00), a.ZFFSBZ, c.YPBM00 from ZY_FYMX00 A,BM_FPXM00 B,BM_YD0000 C,BM_YGBM00 E,ZY_BRXXB0 f where a.JZDH00 = ad_JZDH00 and a.ZYID00 = f.ZYID00 and a.XMBH00 = C.YPNM00 and C.ZYFPID = B.FPXMID and f.SXYS00=e.YGBH00 and a.JMBZ00<>'2' ----如果JMBZ00='4'(事前减免),则发送医保时要把这部分减免的金额也发送给医保 for ZYSF-20070124-001 and a.SFLB00='3' group by a.JMBZ00,c.YPMC00,c.YPGG00,a.XMDW00,a.XMBH00,a.XMDJ00,a.SFLB00,a.ZFFSBZ,c.YPBM00; 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_FPXMMC_MZ := null; ls_GJBMZH := null; ls_CLDBBH := 0; ls_BZGJBM := null; ls_GZHCID := 0; ls_DBZLX0 := 0; ls_BZCBBH := null; ls_BZCBCW:=0; ls_BZLSHC:=0; ls_TXYLFW:=0; ls_FMCWCF:=null; LS_ZGXJCF:='N';--医保药品最高限价拆分 Y:拆分 N:不拆分 LS_ZGXJID:=0; --医保药品最高限价拆分项目ID select SF_ZY_GETSFDJBYZYID(ad_ZYID00) into ls_SFBZDJ from dual; --是否启用 医保单病种结算接口 select trim(nvl(max(VALUE0),'N')) into ls_DBZJK0 from XT_XTCS00 where NAME00='ZY_YBDBZJSJK'; if as_FLAG00 in ('0','6') then--只传入结帐单号 select max(BRID00),max(ZYID00),min(DJH000),max(DJH000) into ls_BRID00,ad_ZYID00,ad_QSDJH0,ad_ZZDJH0 from ZY_FYMX00 where JZDH00 = ad_JZDH00; elsif as_FLAG00='1'then select BRID00,ZYID00 into ls_BRID00,ad_ZYID00 from ZY_FYMX00 where DJH000=ad_QSDJH0; elsif as_FLAG00 in ('2','3','4','5') then--有传入ZYID00 select BRID00 into ls_BRID00 from ZY_BRXXB0 where ZYID00=ad_ZYID00; end if; select TYDWMC,BRZTQK into ls_TYDWMC,ls_BRZTQK from BM_BRXXB0 where BRID00 = ls_BRID00; select YBBRLB,FBBH00,YBLB00,FFBZBM,RYLB00,FMBQBH,decode(ls_DBZJK0,'Y',DBZBM0,''),trim(HCDBBM) into ls_YBBRLB,ls_FBBH00,ls_YBLB00,ls_FFBZBM,ls_RYLB00,ls_FMBQBH,LS_DBZBM0,LS_HCDBBM from ZY_BRXXB0 where ZYID00=ad_ZYID00; --取医保中心类别 begin select YBZXLB,FYBQZ0,SFXNH0 into ls_YBZXLB,ls_FYBQZ0,ls_SFXNH0 from IC_YBBRLB where FBBH00=ls_FBBH00 and YBLB00=ls_YBLB00; exception when no_data_found then as_YHMSG0:='系统字典数据不完整,病人的医保中心名称不存在,请与系统管理员联系!'; rollback; return; end; --发送医保明细项目时,是否改为对应医保中心项目发送 select nvl(trim(max(VALUE0)),'0') into LS_YBFSZH from XT_XTCS00 where name00='SF_YBFSXMZH'; if instrb(LS_YBFSZH,'|')>0 and instrb(LS_YBFSZH,ls_BRZTQK)>0 then LS_YBFSZH := substrb(LS_YBFSZH,instrb(LS_YBFSZH,'|')+1,lengthb(LS_YBFSZH)); ls_YBLB00 := substrb(LS_YBFSZH,1,instrb(LS_YBFSZH,',')-1); ls_YBBRLB := substrb(LS_YBFSZH,instrb(LS_YBFSZH,',')+1,lengthb(LS_YBFSZH)); select YBZXLB into ls_YBZXLB from IC_YBBRLB where YBLB00=ls_YBLB00 and FBBH00=3; end if; --查找当前病人是否有要打包的材料 select nvl(max(BH0002),'') into ls_GJBMZH from VW_ZY_YBHCDB where BZDYBH=ls_FBBH00||'-'||ls_YBZXLB||'-'||ls_YBBRLB; --2019.06.11 增加HIS5.0单病种维护方案,VW_ZY_DBZHC0关联表BM_GZHCFL数据,通过字段FLAG00标识 select nvl(max(BH0002),''),nvl(max(NBBH00),-1),nvl(max(Flag00),-1) into ls_BZGJBM,ls_GZHCID,ls_DBZLX0 from VW_ZY_DBZHC0 where BZDYBH=nvl(ls_FFBZBM,LS_DBZBM0); if ls_GZHCID<=0 then select nvl(max(a.GZHCID),-1) into ls_GZHCID from BM_GZHCFL a,BM_GZHCBZ b where a.GZHCID=b.GZHCID and b.DBZBM0=nvl(ls_FFBZBM,LS_DBZBM0) and rownum=1; if ls_GZHCID>0 then ls_DBZLX0 := 9; end if; end if; if ls_RYLB00 = '生育' and ls_FMBQBH is not null then select nvl(max(trim(substrb(BH0001,1,1))),'0') into ls_FMCWCF from BM_TYZD00 where ZDMC00 like '生育病种名称维护%' and BH0000=ls_FMBQBH; end if; --泉州医保接口要求单价*数量必须等于金额 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'; --是否使用医保发票编号 --医保接口项目名称发送格式 Y按医保名称,M按医保项目名称(院内项目名称),N按院内名称 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 nvl(max(value0),'N') into ls_XNHSFRQ from XT_XTCS00 where NAME00 = 'ZY_XNHCSLX_SFRQ'; --住院收费医保结算是否启用CDRG流程 0:不启用 1:启用，默认0 select nvl(max(value0),'0') into ls_CDRGJK from XT_XTCS00 where NAME00 = 'ZY_CDRGJKQY'; --医保超标床位费处理规则,当参数值为1:陪伴床费用计入超标床位费,2:普通病人的陪伴床、超过BM_YBBRLB.CWZGBZ都不拆分为自费发送,只有特殊病人病人拆分;默认为1 select nvl(max(value0),'1') into ls_PBCBCW from XT_XTCS00 where NAME00 = 'ZY_PBCCBCW'; --控制医保项目数量的小数位保留位数 select substrb(nvl(max(VALUE0),'4'),1,1) into ls_SSWRWS from XT_XTCS00 where NAME00='ZY_FSYBXMSL_SRWS'; select count(*) into ls_YBYPSP from BM_TYZD00 where ZDMC00='指定费别审批的药品目录' and YXBZ00='1' and BZDYBH=to_char(ls_FBBH00)||'-'||ls_YBZXLB||'-'||ls_YBBRLB; --一次性材料项目格式是否改为 医保对应项目+(医院收费项目) select nvl(max(value0),'N') into ls_YCXCLXMXGS from XT_XTCS00 where name00='ZY_YCXCLXMXGS'; --是否厦门医保接口 厦门医保接口ZY_YCXCLXMXGS='Y'时材料发送格式:医保对应项目+(医院收费项目)其它地区格式:医保对应项目(医院收费项目) select nvl(max(value0),'N') into ls_XMYBJK from XT_XTCS00 where name00='ZY_SFXMYBJK'; --取需要审批的材料单价 select nvl(max(value0),'0') into ls_XMDJSX from XT_XTCS00 where NAME00 = 'ZY_CLXMSPDJSX'; --项目没有进行医保对应时，报错 select nvl(max(VALUE0),'N') into ls_WDYBC0 from XT_XTCS00 where NAME00='ZY_SFYBMLDYCWFSYBZX'; --医保编号前缀编码,是否目录限制 begin select SFMLXZ,ZYZFSX,CWXMBH,BRLBMC,nvl(CWZGBZ,0) into ls_SFMLXZ,ls_ZYZFSX,ls_CWXMBH,ls_BRLBMC,ls_CWSXJE from BM_YBBRLB where YBZXLB = ls_YBZXLB and YBBRLB = ls_YBBRLB; exception when no_data_found then as_YHMSG0:='医保病人类别系统字典数据不完整,请与系统管理员联系!'; rollback; return; 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; --住院收费医保药品超过医保最高限价是否拆分参数格式:(类别,超额收费项目或药品ID,是否药品) 类别:0:都不拆分 1:都拆分 2:医保拆分(农合除外) 3:农合拆分(医保除外) 默认值:0,0,N都不拆分 select nvl(trim(max(VALUE0)),'0,0,N') into ls_YBZGXJ from XT_XTCS00 where NAME00 = 'ZY_YBYPZGXJCF'; begin LS_ZGXJID := to_number(substrb(ls_YBZGXJ,3,instrb(ls_YBZGXJ,',',-1)-3)); --医保药品最高限价拆分项目ID exception when others then as_YHMSG0:='参数ZY_YBYPZGXJCF值维护错误,默认值为0,0,N'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_ZY_YBJK00_FJ0000('||to_char(ad_QSDJH0)||','||to_char(ad_ZZDJH0)||',ad_ID0000,as_YHMSG0,as_SYSMSG)',1,150); rollback; return; end; if (LS_FBBH00=3) and (LS_YBXJQY='Y') then if (substrb(ls_YBZGXJ,1,1)='1') then LS_ZGXJCF:='Y'; end if; if (substrb(ls_YBZGXJ,1,1)='2') and (LS_SFXNH0 not in ('1')) then LS_ZGXJCF:='Y'; end if; --2:医保启用(农合除外) if (substrb(ls_YBZGXJ,1,1)='3') and (LS_SFXNH0 in ('1')) then LS_ZGXJCF:='Y'; end if; --3:农合启用(医保除外) end if; --CDRG发送标志：0未发送,1已发送,默认0 select nvl(max(CDRGFS),'0') into LS_CDRGFS from ZY_BRXXB0 where ZYID00=ad_ZYID00; 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 ('2') then if ls_FBBH00 = 3 then --目前限定医保病人 insert into ZY_JSBCMX(ZYID00,ID0000,JZDH00,MXID00,SFLB00,XMBH00) --结算补充费用明细表 select ad_ZYID00,ad_ID0000,0,MXID00,decode(SFLB00,3,3,0),XMBH00 from ZY_FYMX00 a where ZYID00 = ad_ZYID00 and JZDH00+0 = 0 and CZRQ00>as_ZZRQ00 and (XMBH00,decode(SFLB00,3,3,0)) in ( select XMBH00,decode(SFLB00,3,3,0) from ZY_FYMX00 where ZYID00=ad_ZYID00 and JZDH00+0=0 and CZRQ00>as_ZZRQ00 group by ZYID00,XMBH00,decode(SFLB00,3,3,0) having(sum(HJJE00)<0) ); end if; end if; 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='5' then open CUR_ZY_FYMX00_MXID05; elsif as_FLAG00='6' then open CUR_ZY_FYMX00_MXID06; 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_JSXM01,ls_YSZJHM, ls_GJBM00,ls_YPYL00,ls_YPTS00,ls_YPPL00,ls_YGXZ00,ls_ZFFSBZ,ls_XMBM00 ; 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_JSXM01,ls_YSZJHM, ls_GJBM00,ls_YPYL00,ls_YPTS00,ls_YPPL00,ls_YGXZ00,ls_ZFFSBZ,ls_XMBM00 ; 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_JSXM01,ls_YSZJHM, ls_GJBM00,ls_YPYL00,ls_YPTS00,ls_YPPL00,ls_YGXZ00,ls_ZFFSBZ,ls_XMBM00 ; 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_JSXM01,ls_YSZJHM, ls_GJBM00,ls_YPYL00,ls_YPTS00,ls_YPPL00,ls_YGXZ00,ls_ZFFSBZ,ls_XMBM00 ; exit when CUR_ZY_FYMX00_MXID03%notfound; elsif as_FLAG00='5' then fetch CUR_ZY_FYMX00_MXID05 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_JSXM01,ls_YSZJHM, ls_GJBM00,ls_YPYL00,ls_YPTS00,ls_YPPL00,ls_YGXZ00,ls_ZFFSBZ,ls_XMBM00 ; exit when CUR_ZY_FYMX00_MXID05%notfound; elsif as_FLAG00='6' then fetch CUR_ZY_FYMX00_MXID06 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_JSXM01,ls_YSZJHM, ls_GJBM00,ls_YPYL00,ls_YPTS00,ls_YPPL00,ls_YGXZ00,ls_ZFFSBZ,ls_XMBM00 ; exit when CUR_ZY_FYMX00_MXID06%notfound; end if; --药品需要审批,如果未审批的，按自费项目处理 if (ls_YBYPSP>0) and ((ls_FLAG00='2') or (ls_FLAG00='1') ) then ls_SFTHML:=SF_XT_GETFYBMLBH('1',ls_BRID00,ls_XMBH00,ad_ZYID00,ls_XMSL00,ls_DW0000);--返回0不替换 1替换 else ls_SFTHML:=0; end if; --开单医生非处方医生 或为厦门医保，改用主管医生 if (instrb(ls_YGXZ00,'1')=0) or (ls_XMYBJK = 'Y') then select nvl(e.SFZBH0,'*'),nvl(e.ZWXM00,'*') into ls_YSZJHM,ls_ZWXM00 from ZY_BRXXB0 a,BM_YGBM00 e where a.zyid00=ad_ZYID00 and a.sxys00=e.ygbh00; end if; --项目没有进行医保对应时，报错 if ls_WDYBC0='Y' then select count(1) into ls_COUNT0 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_COUNT0=0 then as_YHMSG0:=ls_XMMC00||'('||ls_XMBM00||')的医保收费项目没对应，请对应后再结算!'; as_SYSMSG:=SQLERRM||as_SYSMSG; rollback; return; end if; end if; begin as_SYSMSG:='MXID00='||to_char(ls_MXID00); select a.XMMC00,a.BH0000,C.ZYSFYB,nvl(C.SFSX00,0),D.XMMC00,C.ZYYBBH,a.CBXMBH,C.ZYZFBL,c.YBLB00,substrb(d.YPGG00,1,10),decode(a.SYBZ00,'1',a.XMMC00,ls_FPXMMC_MZ),A.BZCBBH,D.XMDW00,D.ZXJJDW,D.YBZGDJ,D.YBZGDJ1 into ls_FPXMMC,ls_FPXMBH,ls_SFYBXM,ls_YBXMSX,ls_YBXMMC,ls_YBXMBH,ls_CBXMBH,ls_YBZFBL,ls_YBXMLB,ls_YBXMGG,ls_FPXMMC_MZ,ls_BZCBBH,LS_YBDDW0,LS_YBXDW0,LS_YBZGDJ,LS_YBZGDJ1 from VW_BM_YBSFDY_DJBZ C,BM_YBFPXM A,BM_YBSFXM D where C.SFXMID = ls_XMBH00 and C.SFYP00 = decode(ls_FLAG00,'0','N','Y')--是否药品,'0'费用,'1'零散处方,'2'医嘱处方 and C.FBBH00 = ls_FBBH00 and C.YBZXLB = ls_YBZXLB and C.YBBRLB = ls_YBBRLB and C.SFBZDJ = ls_SFBZDJ and C.FBBH00 = a.FBBH00 and C.YBZYFP = a.BH0000 and C.YBZXLB = a.YBZXLB and C.FBBH00 = D.FBBH00(+) and C.YBZXLB = D.YBZXLB(+) and C.ZYYBBH = D.XMBH00(+); 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; --2020.03.10 qks 包床项目的费用定义为bm_yysfxm.xmmc00中包含“包床”两个字的,也算入超标床位费; 如果发票不属于“床位费”，需要重新获取ls_BZCBBH if instrb(ls_XMMC00,'包床')>0 then if instrb(ls_FPXMMC,'床位')=0 then select count(1) into ls_COUNT0 from BM_YYSFXM a,BM_FPXM00 b where a.sfxmid=ls_XMBH00 and a.zyfpid=b.FPXMID and b.XMMC00 like '%床位%'; if ls_COUNT0>0 then ls_BCBZ00 := 1; select max(BZCBBH),nvl(max(BH0000),ls_FPXMBH),nvl(max(XMMC00),ls_FPXMMC) into ls_BZCBBH,ls_FPXMBH,ls_FPXMMC from BM_YBFPXM where YBZXLB=ls_YBZXLB and instrb(XMMC00,'床位')>0; end if; else ls_BCBZ00 := 1; end if; end if; --qks 2017.05.11 药品医保项目单位转化 if (LS_YBXJQY='Y') and (LS_FLAG00 in ('1','2')) then if (trim(LS_DW0000)=trim(LS_YBDDW0)) or (trim(LS_DW0000)=trim(LS_YBXDW0)) then --发药=医保大单位或小单位 LS_DW0000:=LS_DW0000; else select YBDW00,YB2J00 into LS_YBDW00,LS_YB2J00 from BM_YD0000 where YPNM00=ls_XMBH00; if (trim(LS_YBDW00) is not null) and (nvl(LS_YB2J00,0)>0) then if trim(LS_DW0000)=trim(LS_YBDW00) then LS_DW0000:=LS_YBDW00; elsif LS_XMSL00<>0 and LS_HJJE00<>0 then select SF_YF_GETDWYPZHL('1',ls_XMBH00,LS_DW0000) into LS_Z2J000 from dual; --转换成医保小单位 LS_DW0000:=LS_YBDW00; --LS_HJJE00:=LS_HJJE00; if LS_Z2J000 <> nvl(LS_YB2J00,0) then LS_XMDJ00 := round(LS_XMDJ00*LS_YB2J00/LS_Z2J000,4); --2020.05.08 不能重新计算LS_XMDJ00。如果重新计算,会出现正单和负单单价不一致导致无法合并情况。 --2020.05.14 药品已经合并,可以重新计算LS_XMDJ00 if round(LS_XMDJ00*round(LS_HJJE00/LS_XMDJ00,ls_SSWRWS),2)<>LS_HJJE00 then LS_XMSL00 := ceil(LS_HJJE00/LS_XMDJ00*POWER(10,ls_SSWRWS))/POWER(10,ls_SSWRWS); LS_XMDJ00 := round(LS_HJJE00/LS_XMSL00,4); else LS_XMSL00 := round(LS_HJJE00/LS_XMDJ00,ls_SSWRWS); end if; end if; end if; end if; end if; end if; --zhr 08.03.25南平医保为各类医保病人专门增设了各类新床位收费项目，如果病人所发生的床位单价超过了这个标准，发送时要用此编号发送, --超标部分再用非医保其他费发送，如果不超标用原始的收费项目进行发送。存在二次超标判定的问题。 -- if (ls_CWXMBH is not null) and (instr(ls_XMMC00,'床位')>0 or instr(ls_XMMC00,'病房')>0 or instr(ls_FPXMMC,'床位')>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 -- begin -- select D.SFYBXM,nvl(D.SFSX00,0),D.XMMC00,D.XMBH00,D.ZFBL00,D.YBLB00,substrb(D.YPGG00,1,10) -- 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 no_data_found then -- as_YHMSG0:='医保收费项目字典数据不完整,请与系统管理员联系!'; -- rollback; -- return; -- end; -- end if; -- elsif ls_CWSXJE>0 and (nvl(ls_FMCWCF,'0')='0' and ls_PBCBCW<>'2' or nvl(ls_FFBZBM,LS_DBZBM0) is not null) -- and (instr(ls_XMMC00,'床位')>0 or instr(ls_XMMC00,'病房')>0 or instr(ls_FPXMMC,'床位')>0) and (instr(ls_XMMC00,'监护病房床位费')<=0) then -- if instr(ls_FPXMMC,'床位')>0 then -- ls_YBXMSX:=ls_CWSXJE; -- end if; -- end if; if ls_CWSXJE>0 and (nvl(ls_FMCWCF,'0')='0' and ls_PBCBCW<>'2' or nvl(ls_FFBZBM,LS_DBZBM0) is not null) and instr(ls_FPXMMC,'床位')>0 and instr(ls_XMMC00,'监护病房床位费')<=0 then --2020.08.07 沙县CDRG患者,指定床位项目不能转为超标床位费,通过BM_TYZD00.ZDMC00='住院单病种床位费超限价不转超标' 进行控制。 if nvl(ls_FFBZBM,LS_DBZBM0) is not null or LS_CDRGJK='1' and LS_CDRGFS = '1' then select count(1) into ls_COUNT0 from BM_TYZD00 where ZDMC00='住院单病种床位费超限价不转超标' and YXBZ00='1' and BH0000=to_char(LS_XMBH00); if ls_COUNT0 = 0 then ls_YBXMSX:=ls_CWSXJE; else LS_YBXMSX:=0; end if; else ls_YBXMSX:=ls_CWSXJE; end if; end if; --CDRG 2018.01.06 LS_CLYBSX:=0; --医保上限金额(材料) LS_CLCBBH:=null;--超标项目编号(材料) LS_CLCBMC:=null;--超标项目名称(材料) LS_SFCFXM:='N'; --是否拆分项目 Y:材料(超标) M:床位费 M1:包床 N:否 L:材料(非超标) LS_BZCBMC:=null; --CDRG获取材料医保上限 LS_CLYBSX:医保上限金额(材料) LS_CLCBBH:超标项目编号(材料)LS_CLCBMC:超标项目名称(材料) if LS_CDRGJK='1' and LS_CDRGFS = '1' and LS_FLAG00 in ('0') and LS_FPXMBH not in ('05') then begin select nvl(YBSXJE,0),CBXMBH,CBXMMC into LS_CLYBSX,LS_CLCBBH,LS_CLCBMC from BM_YBCBDY where YBZXLB=LS_YBZXLB and FBBH00=LS_FBBH00 and SFXMID=LS_XMBH00; exception when others then LS_CLYBSX:=0;--医保上限金额(材料) LS_CLCBBH:=null;--超标项目编号(材料) LS_CLCBMC:=null;--超标项目名称(材料) end; if LS_FLAG00 in ('0') and LS_CLCBBH is not null and nvl(LS_YBXMSX,0)=0 and nvl(LS_CLYBSX,0)>0 then --如果医保对应表没有维护上限，超标表有维护上限，重新赋值 LS_YBXMSX:=nvl(LS_CLYBSX,0); end if; if nvl(ls_XMDJ00,0)>nvl(LS_YBXMSX,0) then LS_SFCFXM:='Y'; elsif nvl(LS_CLYBSX,0)>0 then LS_SFCFXM:='L'; else LS_SFCFXM:='N'; end if; if LS_SFCFXM in ('Y','L') then LS_BZCBBH:=substrb(LS_CLCBBH,1,20); LS_BZCBMC:=substrb(LS_CLCBMC,1,50); end if; end if; --CDRG超标床位费处理 if LS_CDRGJK='1' and LS_CDRGFS = '1' and LS_FLAG00 in ('0') and LS_FPXMBH in ('05') then if LS_BZCBBH is not null and nvl(ls_XMDJ00,0)>nvl(LS_YBXMSX,0) then --如果医保对应表没有维护上限，超标表有维护上限，重新赋值 LS_SFCFXM:='M'; else LS_SFCFXM:='N'; 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 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.FBBH00=ls_FBBH00 and a.XMBH00=ls_CBXMBH and a.ZYFPXM=b.BH0000 and b.YBZXLB=ls_YBZXLB and b.FBBH00=ls_FBBH00; exception when no_data_found then as_YHMSG0:='医保收费项目与医保发票项目字典数据不完整,请与系统管理员联系!'; rollback; return; end; else ls_CBXMMC:=ls_XMMC00; ls_CBXMBH:=ls_FYBQZ0||ls_FPXMBH; ls_CBFPBH:=ls_FPXMBH; ls_CBFPMC:=ls_FPXMMC; end if; --2017.03.13 qks 单病种超标床位费相关字段重新取值 --2017.10.28 qks 增加生育病人超标床位费相关字段重新取值 --2018.04.09 ls_FFBZBM is not null->nvl(ls_FFBZBM,LS_DBZBM0) --2020.03.10 if (nvl(ls_FFBZBM,LS_DBZBM0) is not null or LS_CDRGJK='1' and LS_CDRGFS = '1') and ls_BZCBBH is not null then ls_CBXMBH := ls_BZCBBH; select nvl(BZCBMC,XMMC00) into ls_CBFPMC from BM_YBFPXM where YBZXLB=ls_YBZXLB and FBBH00=ls_FBBH00 and BH0000=ls_FPXMBH; ls_CBXMMC := nvl(LS_BZCBMC,ls_YBXMMC); --2020.08.17 ls_CBFPBH := ls_FPXMBH; end if; if ( ls_YBXMBH is null) or (ls_SFTHML=1) or (ls_ZFFSBZ='Y') then ls_YBXMMC:=ls_CBXMMC; ls_YBXMBH:=ls_CBXMBH; ls_FPXMMC:=ls_CBFPMC; ls_FPXMBH:=ls_CBFPBH; end if; if ls_SFTHML=1 or ls_ZFFSBZ='Y' then ls_SFYBXM:='N'; ls_YBZFBL:=1; end if; --2020.08.17 住院单病种床位费直接转超标 ls_CBCWF0 := 0; if instr(ls_FPXMMC,'床位')>0 and (nvl(ls_FFBZBM,LS_DBZBM0) is not null or LS_CDRGJK='1' and LS_CDRGFS = '1') then select count(1) into ls_CBCWF0 from BM_TYZD00 where ZDMC00='住院单病种床位费直接转超标' and YXBZ00='1' and BH0000=to_char(LS_XMBH00) and rownum=1; end if; --2007.12.20 zhr 包床记录,自动转换成非医保项目进行发送。 --2018.04.12 qks 增加陪伴床条件： or (ls_YBXMBH='011090000103' and LS_FPXMBH='05') --2020.08.17 增加ls_CBCWF0 if ls_CBCWF0>0 or ls_BCBZ00>0 or ls_PBCBCW<>'0' and (ls_YBXMBH='011090000103' and LS_FPXMBH='05') then--包床或陪伴床 ls_YBZFBL:=1; ls_SFYBXM:='N'; if LS_CDRGJK='1' and LS_CDRGFS = '1' then LS_SFCFXM:='M1'; ls_YBXMSX := 0; --2018.09.25 qks 当C-DRG接口启用的时候，如果ZY_FYMX00.XMMC00有带包字的床位费,那么此项目要求发送为这个编码99000000000005 ls_YBXMBH := '99000000000005'; else ls_YBXMMC:=ls_CBXMMC; ls_YBXMBH:=ls_CBXMBH; ls_FPXMMC:=ls_CBFPMC; ls_FPXMBH:=ls_CBFPBH; ls_YBXMSX:=9999999999; end if; if ls_BZCBBH is not null and ls_FPXMMC='床位费' then --ls_BZCBCW := ls_BZCBCW + round((ls_XMDJ00-ls_YBXMSX)*ls_XMSL00,2); ls_BZCBCW := ls_BZCBCW + round(ls_XMDJ00*ls_XMSL00,2); end if; end if; --检查材料需要审核,超过审核标准部分，转成非医保项目发送 ls_CLSHBZ:='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_CLSHBZ:='1'; select nvl(sum(SPJE00),0) into ls_SPJE00 from YK_YBMLSP where ZYID00=ad_ZYID00 and SFXMID=ls_XMBH00 and to_char(sysdate,'YYYYMMDD')<=JSSYRQ and nvl(ZFRQ00,'20991231')='20991231'; --2020.07.09 材料已审批合计金额 select greatest(0,nvl(sum(XMJE00),0)) into ls_SPJE01 from BM_ZYCFMX_TEMP00 where ID0000=ls_ID0000 and SFXMID=ls_XMBH00 and FLAG00='0' and XMBH00=ls_YBXMBH; ls_SPJE00 := ls_SPJE00 - ls_SPJE01; --if ls_SPJE00=0 then --2020.07.09 退费项目,如果存在已使用的审批金额,则需要计算退回审批金额 if ls_SPJE00<=0 and (ls_XMSL00>=0 or ls_XMSL00<0 and ls_SPJE01<=0) then ls_YBZFBL:=1; ls_YBXMMC:=ls_CBXMMC; ls_YBXMBH:=ls_CBXMBH; ls_FPXMMC:=ls_CBFPMC; ls_FPXMBH:=ls_CBFPBH; ls_SFYBXM:='N'; end if ; --2013.10.15 qks 医保目录审批:医保上限判断条件由单价改为总金额 --2020.07.09 增加条件and ls_SPJE00>0 if round(ls_XMDJ00*ls_XMSL00,2)>ls_SPJE00 and ls_SPJE00>0 then ls_YBXMSX:=ls_SPJE00/ls_XMSL00; end if; --2020.07.09 退费项目,如果存在已使用的审批金额,则需要计算退回审批金额 if ls_XMSL00<0 and ls_SPJE01>0 then ls_YBXMSX:=ls_SPJE01/ls_XMSL00; 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 update ZY_FYMX00 set ZFBL00=ls_YBZFBL where ZYID00 = ad_ZYID00 and MXID00=ls_MXID00; end if; --一次性材料项目格式是否改为 医保对应项目+(医院收费项目)--厦门同安医院=Y if (ls_YCXCLXMXGS='Y') and (ls_FLAG00='0') and (ls_JSXM01='2') then if ls_XMYBJK='Y' then --厦门医保接口 ls_YBXMMC:=substrb(ls_YBXMMC||'+('||ls_XMMC00||')',1,50); else ls_YBXMMC:=substrb(ls_YBXMMC||'('||ls_XMMC00||')',1,50); end if; end if; --医保耗材打包编码 2016.01.20 if ls_GJBMZH is not null and instrb(ls_GJBM00,ls_GJBMZH)>0 then begin select min(NBBH00) into ls_CLDBBH from VW_ZY_YBHCDB where instrb(ls_XMMC00,nvl(MC0001,' '))>0 or instrb(ls_XMMC00,nvl(MC0001,' '))<=0 and MC0001 is null; exception when no_data_found then as_YHMSG0:='医保耗材打包编码字典数据不完整,请与系统管理员联系!'; rollback; return; end; end if; --判断实际单价是否超过医保上线,如果是要拆分成两条记录 --2020.03.19 ls_SFYBXM='Y'改为 (ls_SFYBXM='Y' or nvl(ls_FFBZBM,LS_DBZBM0) is not null) if (ls_XMDJ00>ls_YBXMSX and ls_YBXMSX>0 and (ls_SFYBXM='Y' or nvl(ls_FFBZBM,LS_DBZBM0) is not null) and instr(ls_XMMC00,'监护病房床位费')<=0 or LS_SFCFXM='L' or LS_SFCFXM='M1') --增加包床费用处理 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,CLDBBM,YPYL00,YPTS00,YPPL00,YBSXCE,SFXMID,FLAG00,HCDBXM, FPXMBH) values(ls_ID0000,ls_YBXMBH,decode(ls_SFMLXZ,0,'Y',ls_SFYBXM),decode(ls_SYFPBH,'Y',ls_FPXMBH,ls_FPXMMC), substr(decode(ls_YBLB00,'Z',ls_XMMC00,decode(ls_YBMCFS,'Y',ls_YBXMMC,'M',ls_YBXMMC||'('||ls_XMMC00||')',ls_XMMC00)),1,50), decode(ls_YBMCFS,'Y',ls_YBXMGG,ls_GG0000),ls_DW0000, case when LS_SFCFXM in ('Y','L','M','M1') then LS_XMDJ00 else LS_YBXMSX end, ls_XMSL00, round((case when LS_SFCFXM in ('Y','L','M','M1') then LS_XMDJ00 else LS_YBXMSX end)*ls_XMSL00,2), ls_ZWXM00,ls_CBXMBH,ls_CZRQ00,ls_YSZJHM,ls_GJBM00,ls_YPYL00,ls_YPTS00,ls_YPPL00, decode(LS_SFCFXM,'L',0,round((LS_XMDJ00-decode(LS_SFCFXM,'M1',0,LS_YBXMSX))*LS_XMSL00,2)),ls_XMBH00,ls_FLAG00, decode(nvl(LS_SFCFXM,'N'),'M','2','M1','2','Y','3','L','3','0'), ls_FPXMBH ); insert into BM_ZYCFMX_TEMP00(ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00, XMJE00,YSXM00,ZFXMBH,CZRQ00,YSZJHM,CLDBBM,YPYL00,YPTS00,YPPL00,YBSXCE,SFXMID,FLAG00,HCDBXM, FPXMBH) values(ls_ID0000,ls_CBXMBH,'N',decode(ls_SYFPBH,'Y',ls_CBFPBH,ls_CBFPMC), substr(decode(ls_YBLB00,'Z',ls_XMMC00,decode(ls_YBMCFS,'Y',ls_CBXMMC,'M',ls_CBXMMC||'('||ls_XMMC00||')',ls_XMMC00)),1,50), ls_GG0000,ls_DW0000, ls_XMDJ00-(case when LS_SFCFXM in ('Y','L','M1') then 0 else LS_YBXMSX end), ls_XMSL00, round((ls_XMDJ00-(case when LS_SFCFXM in ('Y','L','M1') then 0 else LS_YBXMSX end))*ls_XMSL00,2), ls_ZWXM00,ls_CBXMBH,ls_CZRQ00,ls_YSZJHM,ls_GJBM00,ls_YPYL00,ls_YPTS00,ls_YPPL00, 0,ls_XMBH00,ls_FLAG00, decode(nvl(LS_SFCFXM,'N'),'M','2','M1','2','Y','4','L','4','0'), ls_FPXMBH ); --2017.03.13 qks 单病种超标床位费 if ls_BZCBBH is not null and ls_FPXMMC='床位费' then ls_BZCBCW := ls_BZCBCW + round((ls_XMDJ00-ls_YBXMSX)*ls_XMSL00,2); end if; else--不做任何转换 --if abs(ls_HJJE00-round(ls_XMDJ00*ls_XMSL00,2))=0.01 then --2016.08.01 qks -- ls_HJJE00_TEMP := ls_HJJE00; --else -- ls_HJJE00_TEMP := round(ls_XMDJ00*ls_XMSL00,2); --end if; --2020.05.14 药品先汇总,合计金额不允许通过单价*数量计算,统一都改为取HJJE00字段值。 ls_HJJE00_TEMP := ls_HJJE00; insert into BM_ZYCFMX_TEMP00(ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00, YSXM00,ZFXMBH,CZRQ00,YSZJHM,CLDBBM,YPYL00,YPTS00,YPPL00,YBSXCE,SFXMID,FLAG00,HCDBXM,FPXMBH) values(ls_ID0000,ls_YBXMBH,DECODE(ls_SFMLXZ,0,'Y',ls_SFYBXM),DECODE(ls_SYFPBH,'Y',ls_FPXMBH,ls_FPXMMC), substr(decode(ls_YBLB00,'Z',ls_XMMC00,decode(ls_YBMCFS,'Y',ls_YBXMMC,'M',ls_YBXMMC||'('||ls_XMMC00||')',ls_XMMC00)),1,50), decode(ls_ybmcfs,'Y',ls_YBXMGG,ls_GG0000),ls_DW0000,ls_XMDJ00,ls_XMSL00,ls_HJJE00_TEMP, ls_ZWXM00,ls_CBXMBH,ls_CZRQ00,ls_YSZJHM,ls_GJBM00,ls_YPYL00,ls_YPTS00,ls_YPPL00,0,ls_XMBH00,ls_FLAG00,'0', ls_FPXMBH); end if; exception when no_data_found then as_YHMSG0:='查找不到医保对应记录,请与系统管理员联系!'; if ls_FLAG00='0' then as_SYSMSG:=SQLERRM||'SFXMID='||to_char(ls_XMBH00)||'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 ='5' then close CUR_ZY_FYMX00_MXID05; elsif as_FLAG00='6' then close CUR_ZY_FYMX00_MXID06; end if; --农合病人实时结算 if (ls_XNHSFRQ='Y') and (ls_SFXNH0='1') 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 nvl(XMGG00,'0')=NVL(XNH.XMGG00,'0') 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,YSZJHM,YBSXCE,SFXMID,FLAG00,HCDBXM,FPXMBH) 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),sum(YBSXCE),max(SFXMID),max(FLAG00),max(HCDBXM),max(FPXMBH) from BM_ZYCFMX_TEMP00 where ID0000 = ls_ID0000 group by XMBH00,SFYB00,FPXMMC,XMGG00,XMDJ00,trim(XMMC00),CZRQ00,FPXMBH; --判断是否超过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,YPYL00,YPTS00,YPPL00,YBSXCE,SFXMID,FLAG00,HCDBXM,FPXMBH) 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(YPYL00),sum(YPTS00),max(YPPL00),sum(YBSXCE),max(SFXMID),max(FLAG00),max(HCDBXM),max(FPXMBH) from BM_ZYCFMX_TEMP00 where ID0000 = ls_ID0000 group by XMBH00,SFYB00,FPXMMC,XMGG00,XMDJ00,TRIM(XMMC00),FPXMBH; end if; else --进行汇总 insert into BM_ZYCFMX_TEMP00(ID0000,XMBH00,SFYB00,FPXMMC,FPXMBH,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00, YSXM00,ZFXMBH,YSZJHM,CLDBBM,YPYL00,YPTS00,YPPL00,YBSXCE,SFXMID,FLAG00,HCDBXM) select ad_ID0000,XMBH00,SFYB00,FPXMMC,FPXMBH,trim(XMMC00),XMGG00,max(XMDW00),XMDJ00,nvl(sum(XMSL00),0),nvl(sum(XMJE00),0), trim(max(YSXM00)),max(ZFXMBH),max(YSZJHM),CLDBBM,max(YPYL00),sum(YPTS00),max(YPPL00),sum(YBSXCE),max(SFXMID),max(FLAG00),max(HCDBXM) from BM_ZYCFMX_TEMP00 where ID0000 = ls_ID0000 group by XMBH00,SFYB00,FPXMMC,FPXMBH,XMGG00,XMDJ00,trim(XMMC00),CLDBBM; end if; --update BM_ZYCFMX_TEMP00 set XMDJ00 = round(XMJE00/XMSL00,6) where XMJE00 <> (XMDJ00 * XMSL00) and ID0000 = ad_ID0000 and XMSL00<>0; --2019.04.24 改为单价保留不变,修改项目数量 ZYSF9-20190424-001 if LS_YBXJQY='Y' then --update BM_ZYCFMX_TEMP00 set XMSL00 = round(XMJE00/XMDJ00,4) where XMJE00 <> round(XMDJ00 * XMSL00,2) and ID0000 = ad_ID0000 and XMDJ00<>0; --2020.04.10 修改：XMSL00改为只保留2位小数; for ZYSF9-20200410-001 --2020.05.14 使用参数ZY_FSYBXMSL_SRWS控制 医保项目数量的小数位保留位数 update BM_ZYCFMX_TEMP00 set XMSL00 = round(XMJE00/XMDJ00,ls_SSWRWS) where XMJE00 <> round(XMDJ00 * XMSL00,2) and ID0000 = ad_ID0000 and XMDJ00<>0; end if; --2020.04.10 统一重新修改XMDJ00 update BM_ZYCFMX_TEMP00 set XMDJ00 = round(XMJE00/XMSL00,4) where XMJE00 <> round(XMDJ00 * XMSL00,2) 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 XMJE00=0; -- and XMSL00=0 ZYSF9-20200207-001 要求单价为0的也不要发送 --2019.12.10 qks 注释掉 for ZYSF9-20191210-001 --if ls_XMYBJK<>'Y' 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; --单病种另收耗打包编码 2017.03.13,同时优先普通的医保耗材打包 --2018.07.21 增加字段ZY_BRXXB0.HCDBBM,增加条件 and (LS_HCDBBM is null or BH0000=LS_HCDBBM) --以及当ZY_BRXXB0.HCDBBM为空（即前台不选择）,患者存在同一个收费项目,同时属于同个单病种下的2个打包耗材情况 => BM_ZYCFMX_TEMP00.GZHCID-->VW_ZY_DBZHC0.NBID00 if ls_GZHCID>0 then if ls_BZGJBM is null then --2019.06.11 增加HIS5.0单病种维护方案 update BM_ZYCFMX_TEMP00 a set GZHCID=( select max(GZHCID) from BM_GZHCMX b where b.GZHCID in (select NBBH00 from VW_ZY_DBZHC0 b where ls_DBZLX0<>9 and b.BZDYBH=nvl(ls_FFBZBM,LS_DBZBM0) or ls_DBZLX0=9 and b.NBBH00=ls_GZHCID ) and b.SFXMID=a.SFXMID ) where ID0000=ad_ID0000 and a.CLDBBM is not null and exists ( select 1 from BM_GZHCMX b where b.GZHCID in (select NBBH00 from VW_ZY_DBZHC0 b where ls_DBZLX0<>9 and b.BZDYBH=nvl(ls_FFBZBM,LS_DBZBM0) or ls_DBZLX0=9 and b.NBBH00=ls_GZHCID ) and b.SFXMID=a.SFXMID ); else --(ls_DBZLX0=2 or ls_DBZLX0=9) update BM_ZYCFMX_TEMP00 a set GZHCID=(select max(NBBH00) from VW_ZY_DBZHC0 b where b.BZDYBH=nvl(ls_FFBZBM,LS_DBZBM0) and instrb(','||b.BH0002||',',','||a.CLDBBM||',')>0 and (LS_HCDBBM is null or b.BH0000=LS_HCDBBM) ) where ID0000=ad_ID0000 and a.CLDBBM is not null and exists (select 1 from VW_ZY_DBZHC0 b where b.BZDYBH=nvl(ls_FFBZBM,LS_DBZBM0) and instrb(','||b.BH0002||',',','||a.CLDBBM||',')>0 and (LS_HCDBBM is null or b.BH0000=LS_HCDBBM) ); end if; insert into BM_ZYCFMX_TEMP00(ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00, YSXM00,ZFXMBH,YSZJHM,CLDBBM,YPYL00,YPTS00,YPPL00,YBSXCE,SFXMID,FLAG00,HCDBXM,FPXMBH) select ad_ID0000,b.BH0000,'Y','其他费',b.MC0000,null,b.BH0001,sum(a.XMJE00),1,sum(a.XMJE00), trim(max(YSXM00)),max(ZFXMBH),max(YSZJHM),null,max(a.YPYL00),sum(a.YPTS00),max(a.YPPL00),0,max(SFXMID),max(a.FLAG00),'1',max(FPXMBH) from BM_ZYCFMX_TEMP00 a,VW_ZY_DBZHC0 b where a.ID0000=ad_ID0000 and a.CLDBBM is not null and a.GZHCID=b.NBBH00 group by b.BH0000,b.BH0001,b.MC0000; --2020.02.03 增加条件nvl(SFTXYL,'0')<>'1' select nvl(sum(XMJE00),0) into ls_BZLSHC from BM_ZYCFMX_TEMP00 a where ID0000=ad_ID0000 and a.CLDBBM is not null and exists (select 1 from VW_ZY_DBZHC0 b where a.GZHCID=b.NBBH00 and nvl(SFTXYL,'0')<>'1' ); select nvl(sum(XMJE00),0) into ls_TXYLFW from BM_ZYCFMX_TEMP00 a where ID0000=ad_ID0000 and a.CLDBBM is not null and exists (select 1 from VW_ZY_DBZHC0 b where a.GZHCID=b.NBBH00 and nvl(SFTXYL,'0')='1' ); update BM_ZYCFMX_TEMP00 a set XMDJ00=0,XMJE00=0 where ID0000=ad_ID0000 and a.CLDBBM is not null and exists (select 1 from VW_ZY_DBZHC0 b where a.GZHCID=b.NBBH00 ); --2016.01.20 要求传送医保耗材打包编码以及名称,2016.01.25 max(FPXMMC)改为'其他费' elsif ls_CLDBBH>0 then insert into BM_ZYCFMX_TEMP00(ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00, YSXM00,ZFXMBH,YSZJHM,CLDBBM,YPYL00,YPTS00,YPPL00,YBSXCE,SFXMID,FLAG00,HCDBXM,GZHCID,FPXMBH) select ad_ID0000,b.BH0000,'Y','其他费',b.MC0000,null,'套',sum(a.XMJE00),1,sum(a.XMJE00), trim(max(YSXM00)),max(ZFXMBH),max(YSZJHM),null,max(a.YPYL00),sum(a.YPTS00),max(a.YPPL00),0,max(SFXMID),max(a.FLAG00),'1',-9898,max(a.FPXMBH) from BM_ZYCFMX_TEMP00 a,VW_ZY_YBHCDB b where a.ID0000=ad_ID0000 and a.CLDBBM is not null and instrb(','||b.BH0001||',',','||a.CLDBBM||',')>0 and b.NBBH00=ls_CLDBBH group by b.BH0000,b.MC0000; update BM_ZYCFMX_TEMP00 a set XMDJ00=0,XMJE00=0 where ID0000=ad_ID0000 and exists (select 1 from VW_ZY_YBHCDB where NBBH00=ls_CLDBBH and instrb(','||BH0001||',',','||a.CLDBBM||',')>0); --如果材料有限价，进行拆分处理，目前只支持一条 select DJ0000 into ls_HCYBXJ from VW_ZY_YBHCDB where NBBH00 = ls_CLDBBH; select count(1) into ls_COUNT0 from BM_ZYCFMX_TEMP00 where GZHCID = -9898 and rownum=1; if ls_COUNT0>0 then select XMJE00 into ls_HCYBJE from BM_ZYCFMX_TEMP00 where GZHCID = -9898; if ls_HCYBXJ > 0 and ls_HCYBJE > ls_HCYBXJ then insert into BM_ZYCFMX_TEMP00(ID0000,XMBH00,SFYB00,FPXMMC,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00, YSXM00,ZFXMBH,YSZJHM,CLDBBM,YPYL00,YPTS00,YPPL00,YBSXCE,SFXMID,FLAG00,HCDBXM,FPXMBH) select ID0000, XMBH00, 'N',FPXMMC,XMMC00,XMGG00,XMDW00,ls_HCYBJE-ls_HCYBXJ,XMSL00,ls_HCYBJE-ls_HCYBXJ, YSXM00,ZFXMBH,YSZJHM,CLDBBM,YPYL00,YPTS00,YPPL00,YBSXCE,SFXMID,FLAG00,HCDBXM,FPXMBH from BM_ZYCFMX_TEMP00 where GZHCID = -9898; update BM_ZYCFMX_TEMP00 set XMDJ00 = ls_HCYBXJ, XMJE00 = ls_HCYBXJ where GZHCID = -9898; end if; update BM_ZYCFMX_TEMP00 set GZHCID = 0 where GZHCID = -9898; 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; if ls_FPXMMC_MZ is not null then select trim(max(XMMC00)) into ls_XMMC00 from BM_ZYCFMX_TEMP00 where ID0000 = ad_ID0000 and FPXMMC=ls_FPXMMC_MZ 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)||',ad_ID0000,as_YHMSG0,as_SYSMSG)',1,150); rollback; return; end if; end if; 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)||',ad_ID0000,as_YHMSG0,as_SYSMSG)',1,150); rollback; return; end if; --2018.04.04 修正BM_ZYCFMX_TEMP00.YSZJHM,YSXM00不匹配记录 update BM_ZYCFMX_TEMP00 a set YSXM00=(select ZWXM00 from BM_YGBM00 where SFZBH0=a.YSZJHM and rownum=1) where ID0000=ad_ID0000 and nvl(a.YSZJHM,'*')<>'*' and exists (select 1 from BM_YGBM00 where SFZBH0=a.YSZJHM and ZWXM00<>a.YSXM00); --把收费项目名称加(院外)只修改项目 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'医嘱处方; --2018.05.29 qks 修正超过最高限价拆分 if (LS_ZGXJCF='Y') and (LS_ZGXJID>0) then LS_FPXMBH := null; for CUR in CUR_YBZGXJ loop --在药典属性表中有维护可以拆分的药品，才进行拆分 select nvl(max(trim(VALUE0)),'Y') into LS_SFCF00 from BM_ZDSX00 where XMBH00=CUR.SFXMID and NAME00='SFCF00'; if LS_SFCF00='Y' then 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.ZXJJDW,nvl(D.YBZGDJ,0)YBZGDJ,nvl(D.YBZGDJ1,0)YBZGDJ1 into LS_YBDDW0,LS_YBXDW0,LS_YBZGDJ,LS_YBZGDJ1 from VW_BM_YBSFDY C,BM_YBFPXM A,BM_YBSFXM D where C.SFXMID = CUR.SFXMID and C.SFYP00 = 'Y' and C.FBBH00 = ls_FBBH00 and C.YBZXLB = LS_YBZXLB and C.YBBRLB = LS_YBBRLB and C.FBBH00 = D.FBBH00(+) and C.YBZXLB = D.YBZXLB(+) and C.YBXMBH = D.XMBH00(+) and C.FBBH00 = a.FBBH00 and C.YBZYFP = a.BH0000 and C.YBZXLB = a.YBZXLB 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; if LS_ZGXJDJ = 0 and trim(LS_YBDDW0)=trim(LS_YBXDW0) then LS_ZGXJDJ := greatest(nvl(LS_YBZGDJ,0),nvl(LS_YBZGDJ1,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 --如果合计金额>最高限价金额 拆分数据 if LS_FPXMBH is null then begin select a.XMMC00,a.BH0000,C.ZYSFYB,nvl(C.SFSX00,0),D.XMMC00,C.ZYYBBH,a.CBXMBH,C.ZFBL00,c.YBLB00,A.BZCBBH,A.BZCBMC,D.XMDW00,D.ZXJJDW 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 from VW_BM_YBSFDY_DJBZ C,BM_YBFPXM A,BM_YBSFXM D where C.SFXMID = LS_ZGXJID and C.SFYP00 = substrb(ls_YBZGXJ,instrb(ls_YBZGXJ,',',-1)+1,1) --是否药品：N否,Y是 and C.FBBH00 = LS_FBBH00 and C.YBZXLB = LS_YBZXLB and C.YBBRLB = LS_YBBRLB and C.SFBZDJ = ls_SFBZDJ and C.FBBH00 = a.FBBH00 and C.YBZYFP = a.BH0000 and C.YBZXLB = a.YBZXLB and C.FBBH00 = D.FBBH00(+) and C.YBZXLB = D.YBZXLB(+) and C.ZYYBBH = D.XMBH00(+) and a.SYBZ00 <> '1'; --非门诊 exception when others then AS_YHMSG0:='SFXMID:='||LS_ZGXJID||' 医保收费未对应!'; AS_SYSMSG:=SQLERRM||as_SYSMSG; rollback; end; end if; --修改单价为最高限价 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_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,FPXMBH) 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,CUR.YPTS00,CUR.YPPL00, CUR.YPYL00,0,'0','0','0', nvl(LS_ZGXJID,0),'2',CUR.YSZJHM, CUR.FPXMBH ); end if; 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; --2019.04.24 改为单价保留不变,修改项目数量 ZYSF9-20190424-001 if LS_YBXJQY='Y' then --update BM_ZYCFMX_TEMP00 set XMSL00 = round(XMJE00/XMDJ00,4) where XMJE00 <> round(XMDJ00 * XMSL00,2) and ID0000 = ad_ID0000 and XMDJ00<>0; --2020.04.10 修改：XMSL00改为只保留2位小数; for ZYSF9-20200410-001 --2020.05.14 使用参数ZY_FSYBXMSL_SRWS控制 医保项目数量的小数位保留位数 update BM_ZYCFMX_TEMP00 set XMSL00 = round(XMJE00/XMDJ00,ls_SSWRWS) where XMJE00 <> round(XMDJ00 * XMSL00,2) and ID0000 = ad_ID0000 and XMDJ00<>0; end if; --2020.04.10 统一重新修改XMDJ00 update BM_ZYCFMX_TEMP00 set XMDJ00 = round(XMJE00/XMSL00,4) where XMJE00 <> round(XMDJ00 * XMSL00,2) and ID0000 = ad_ID0000 and XMSL00<>0; end if; --2019.06.25 项目或药品的规格如果为空，需要改为‘无’发送 update BM_ZYCFMX_TEMP00 set XMGG00=nvl(XMGG00,'无') where ID0000=ad_ID0000 and XMGG00 is null; --2020.02.18 医保的用药天数,不允许超过3位数,超过999情况下改用1 update BM_ZYCFMX_TEMP00 set YPTS00=1 where ID0000=ad_ID0000 and nvl(YPTS00,0)>=1000; --2018.01.06 插入住院医保发送明细表ZY_YBFSMX --2019.06.11 写入ZY_YBFSMX.GZHCID 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,HCDBXM,GZHCID, FPXMBH ) select ID0000,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'),ad_ZYID00,0,trim(FPXMMC),SFYB00,SFXMID,XMBH00,XMMC00, XMGG00,XMDW00,XMDJ00,XMSL00,XMJE00,YSXM00,ZFXMBH,YSZJHM,YPTS00,YPPL00, YPYL00,YBSXCE,FLAG00,HCDBXM,GZHCID,FPXMBH from BM_ZYCFMX_TEMP00 where ID0000=ad_ID0000; if nvl(ls_FFBZBM,LS_DBZBM0) is not null then update ZY_BRZHXX set DBZCWF=ls_BZCBCW,DBZHCF=ls_BZLSHC,TXYLFW=ls_TXYLFW where ZYID00=ad_ZYID00; end if; --commit; 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)||',ad_ID0000,as_YHMSG0,as_SYSMSG)',1,150); rollback; end; / show error; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%