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 '全部', --婴儿姓名 ad_SFXYB0 in char default '0', --是否新医保, 0否1是, 默认0 ad_JSXZID in number default 0 --结算选择记录ID ) -- 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 修改：住院单病种床位费直接转超标; for ZYSF9-20200817-001 -- qks 2020.08.31 当通用字典“单病种另行收费耗材”维护:1、【单位】值为“个”（值BM_TYZD00.BH0001=个）,2、同时【按个打包标志】要打勾（值BM_TYZD00.MC0001=1）。那么发送医保独立拆成一个包,即一条费用明细做成一个包发送给医保,如果单条数量>1也要拆分为多个数量为1的包; for ZYSF9-20200723-005 -- qks 2020.09.21 单病种另收耗材打包收费数量(HIS5.0单病种维护方案):可以按指定的收费项目收费总数量来发送(BM_ZYCFMX_TEMP00.HCDBSX=2),对应维护需求XMGL9-20200917-001; for ZYSF9-20200917-002 -- linzetao 2020.10.09 增加参数ZY_FSYBYPFYSFRQHZ处理, 参数启用as_FLAG00='2'时发送医保药品费用按日期汇总 ZYSF9-20200930-005 -- linzetao 2020.10.14 调整药品频率默认传值(由*调整为9999), 药品用量不含单位 ZYSF9-20201012-001 -- linzetao 2020.10.20 统计超标床位费增加发票项目名称"超标床位费" ZYSF9-20201020-001 -- qks 2020.10.22 当审批药品数量<病人实际发生药品数量,要求审批过的数量按医保结算,未审批过的数量按照自费计算; for ZYSF9-20201022-002 -- linzetao 2020.11.23 增加入参ad_SFXYB0(是否新医保), 处理新医保接口当参数ZY_MXCSFSYBMSC启用后ZFFSBZ=Y的项目不再转成超标项目编号仍按原项目编号发送, 同时处理药品部分剂型和给药途径为空的问题 ZYSF9-20201123-001 -- jlg 2020.12.07 增加入参ad_JSXZID（结算选择ID），处理中途结算选择结算的功能 ZYSF9-20201126-001 -- jlg 2021.01.07 跨年结算插入ZY_JSBCMX表时增加ZFFSBZ字段的处理 ZYSF9-20210106-002 -- linzetao 2021.02.25 整理bkeh83,bkeh05,bkeh06,bakxml数据 ZYSF9-20210223-004 -- linzetao 2021.03.05 LS_CBXMBH截取50位 MZSF9-20210305-001 -- linzetao 2021.03.19 CDRG结算增加超标药品费处理 ZYSF9-20180106-002 -- linzetao 2021.03.24 处理单病种高值耗材包超医保限额 ZYSF9-20210320-001 -- linzetao 2021.04.06 处理医保非单种高值耗材包,修正单病种耗材包不支持多个包问题 ZYSF9-20210325-001 -- linzetao 2021.04.14 处理C-DRG高值药品无上限限制 ZYSF9-20210409-003 -- qks 2021.04.17 参数ZY_YBYPZGXJCF功能扩展：第1位的[类别]增加了4:单价不超限价改变数量; for ZYSF9-20210417-002 -- linzetao 2021.04.19 续ZYSF9-20210417-002, 项目单价(LS_XMDJ00)等于0时不处理 MZSF9-20210419-003 -- linzetao 2021.04.22 修正高值耗材5.0模式, 单病种维护在BM_GZHCFL而未维护在BM_GZHCBZ时耗材包发送不正确问题 ZYSF9-20210422-002 -- qks 2021.04.27 处理药品单价超过对应的BM_YBSFDY.SFSX00情况下,只按单价进行拆分,导致拆分后的合计金额跟拆分前的原合计金额不等问题; for ZYSF9-20210425-001 -- linzetao 2021.05.08 处理ZY_SFXMCGSXSFCF参数未启用时的超标床位费 ZYSF9-20210507-003 -- linzetao 2021.06.08 增加字典配置处理ZY_SFXMCGSXSFCF参数未启用时床位费拆分问题 ZYSF9-20210603-002 -- linzetao 2021.07.26 增加按医保中心处理医保项目数量小数点位数 ZYSF9-20210726-001 -- chenHeyi 2021.07.29 通用字典"住院超标项目名称是否使用医保项目名称" 1:是 0:否 ZYSF9-20210720-002 -- qks 2021.08.06 单病种以及CDRG患者,当收费项目发票属于床位费,但是在BM_YBSFDY中对应为非床位费非医保项目，那么也按包床逻辑处理，全部算入超标床位费; for ZYSF9-20210805-001 -- qks 2021.08.15 参数ZY_FSYBYPFYSFRQHZ功能扩展：药品和项目费用是否按操作日期汇总[0:否 1:药品和项目 2:药品]默认:0; for ZYSF9-20210816-002 --zhangyc 2021.08.18 增加参数GetType_bkeh83控制(国家标准对应编码)取值方式 1:优先取BM_YBSFDY(为空取BM_YYSFXM),0:优先取BM_YYSFXM(为空取BM_YBSFDY),同时在汇总时SFXMID加(group by ) by ZYSF9-20210819-001 -- qks 2021.08.31 通用字典ZDMC00='医保新接口参数设置' and BH0000='ZY_CBYPMCSYYBYPMC',控制：住院超标药品是否使用原医保编码和名称(是否医保=N) ZYSF9-20210831-002 -- qks 2021.09.02 需求XMGL9-20210831-001对表BM_YBSFDY增加了字段BKEA96字段(医保限制范围，取Y或N)，ad_SFXYB0=0 (是否新医保 0:否 1:是)不取BM_YBSFDY.BKEA96 同时ZY_MXCSFSYBMSC=Y无效 for ZYSF9-20210904-001 -- qks 2021.09.06 处理:药品超限价拆分出来的金额,由于数量固定为1,超出金额即为拆分出来新生成记录对应的单价，该单价可能继续存在超限价问题。for ZYSF9-20210907-002 -- qks 2021.09.15 CDRG:超标药品增加判断医保上限金额,以及发送超标药品单位; for ZYSF9-20210909-003 -- linzetao 2021.09.16 处理: 超标床位费由医保计算时床位费不转超标项目 ZYSF9-20210914-001 -- linzetao 2021.09.22 处理bkc007节点发送医师国家编码 ZYSF9-20210921-001 -- linzetao 2021.09.27 处理发送超标项目不再发送700打头编号 ZYSF9-20210924-002 -- linzetao 2021.09.28 去除医师身份证号为空取*号 ZYSF9-20210928-003 -- linzetao 2021.09.30 处理当同一个项目不同开单医生通用明细上传bkc006与bkc007不是同一个人问题 ZYSF9-20210930-001 -- linzetao 2021.09.30 处理BKE045改成传国家码，耗材传C码。BKE046传值 ZYSF9-20210930-002 -- linzetao 2021.10.02 处理开单医生员工性质非1时不会转成主管医生(zy_brxxb0.sxys00),开单医师无国家医师编码改用主管医生问题 ZYSF9-20211002-001 -- qks 2021.10.10 当限价药品合计金额为0,项目单位也要进行转换; 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_KDYS00 BM_YGBM00.YGBH00%type; --员工编号 BM_YGBM00.YGBH00 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; --医保病人药品是否替换成自费药品 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_JX0000 varchar2(50); --药品剂型 ls_GYTJ00 varchar2(20); --给药途径 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_YPYBSX number(12,2) ; --医保上限金额(药品) LS_YPCBBH varchar2(50) ; --超标项目编号(药品) LS_YPCBMC varchar2(100); --超标项目名称(药品) LS_YPCBDW BM_YBCBDY.CBXMDW%type; --超标项目单位(药品) 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:不拆分 M:单价不超限价改变数量 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_CWFCF0 BM_TYZD00.MC0000%type; --住院床位费是否按最高标准拆分 ls_SPJE01 YK_YBMLSP.SPJE00%type; --材料已审批的单价上限金额(已使用) LS_DBSFSL ZY_FYMX00.XMSL00%type; --单病种另收耗材打包收费数量 LS_FSYBYPFYSF XT_XTCS00.VALUE0%type; --发送医保药品费用是否按日期汇总, 0否1是, 默认0 LS_WSYPSL ZY_FYMX00.XMSL00%type; --未审批药品数量 ls_MXCSFSYBMSC XT_XTCS00.VALUE0%type; --新医保明细上传ZFFSBZ=Y项目是否按原编码上传，Y：是,N：否,默认N LS_BKEH83 ZY_YBFSMX.BKEH83%type;--国家标准对应编码 LS_BKEH05 ZY_YBFSMX.BKEH05%type;--医保备用1 LS_BKEH06 ZY_YBFSMX.BKEH06%type;--医保备用2 LS_BAKXML ZY_YBFSMX.BAKXML%type;--HIS备用字段(xml格式)后续扩展 ls_XMDJ01 ZY_FYMX00.XMDJ00%type;--超过BM_YBSFDY.SFSX00拆分出来单价 ls_XMSL01 ZY_FYMX00.XMSL00%type;--超过BM_YBSFDY.SFSX00拆分出来数量 ls_HJJE01 ZY_FYMX00.HJJE00%type;--超过BM_YBSFDY.SFSX00拆分出来金额 LS_SLBLWS IC_YBBRLB.SLBLWS%type; --数量保留位数 LS_CBCWMS IC_YBBRLB.CBCWMS%type; --超标床位费计算模式, 0His计算1医保返回 ls_CBXMMCSYYBXMM BM_TYZD00.MC0000%type; --住院超标项目名称是否使用医保项目名称,1是0否,默认为0 ZYSF9-20210720-002 ls_ZYFPID ZY_FYMX00.ZYFPID%type; --住院发票ID ls_ZYFPBH BM_FPXM00.BH0000%type; --住院发票编号 LS_GetType_bkeh83 varchar2(10);--新医保接口bkeh83(国家标准对应编码)取值方式 1:优先取BM_YBSFDY(为空取BM_YYSFXM),0:优先取BM_YYSFXM(为空取BM_YBSFDY) LS_YYSFXM_BKEH83 BM_YYSFXM.BKEH83%type;--医院收费项目：医保国家标准编码 LS_YBSFDY_BKEH83 BM_YBSFDY.BKEH83%type;--医院收费对应：医保国家标准编码 LS_YBSFXM_BKEH83 BM_YBSFXM.BKEH83%type;--医保收费项目：医保国家标准编码 ls_CBYPMCSYYBYPMC BM_TYZD00.MC0000%type; --住院超标药品是否使用原医保编码和名称(是否医保=N) 1:是 0:否(参数ZY_YBYPZGXJCF控制)默认:0 ZYSF9-20210831-002 ls_YBXMMC1 BM_YBSFXM.XMMC00%type; --超限医保收费/药品项目名称 ls_YBXMBH1 BM_YBSFXM.XMBH00%type; --超限医保收费/药品项目编号 ls_FPXMBH1 BM_YBFPXM.BH0000%type; --超限医保收费/药品发票项目编号 ls_FPXMMC1 BM_YBFPXM.XMMC00%type; --超限医保收费/药品发票名称 ls_BKC007FSYSGJBM BM_TYZD00.MC0000%type; --bkc007发送医师国家编码 LS_Change_bkeh83_bke045 BM_TYZD00.MC0000%type; --字典Change_bkeh83_bke045 LS_Change_bke182_bke046 BM_TYZD00.MC0000%type; --字典Change_bke182_bke046 ls_YBYGDM BM_YGBM00.YBYGDM%type; --医保医师代码/医保护士代码 ls_BKC007 BM_YGBM00.YBYGDM%type; -- ls_BKC507 varchar2(50); --药师国家编码 ls_BKC506 varchar2(50); --药师姓名 LS_BKE181 BM_ZYCFMX_TEMP00.bke181%type; --bke181:HIS院内项目编码 LS_BKE182 BM_ZYCFMX_TEMP00.bke182%type; --bke182:His内部名称 LS_YBZXLB_MXCSFSYBMSC varchar2(100);--新医保医保项目70开头项目编码(系统参数:SF_MXCSFSYBMSC、ZY_MXCSFSYBMSC=Y)不转成原码费别多个用|隔开 LS_WGJYSBMSFGYZGYS BM_TYZD00.MC0000%type; --开单医生无国家医师编码是否改用主管医生, 0否1是, 默认0 LS_YBSFDY_BKEA96 varchar2(1);--限制范围类别: Y-范围内 N-范围外 LS_SFCF01 char(1); --是否拆分 LS_SFFSYM char(1); --是否发送原项目名称 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, substrb(SF_ZY_GETBRFYCZRQ(a.CZRQ00,a.OMXID0,LS_FSYBYPFYSF,'0'),1,10) CZRQ00, --C.JSXM00,nvl(e.SFZBH0,'*'),C.GJBM00, C.JSXM00,e.SFZBH0,C.GJBM00, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.YJJZID,0,'3'),''),a.XMSL00),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'),'9999') as YPPL00, --医保频次编码 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'1'),'000'),'') as JX0000, --剂型 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'2'),''),'') as GYTJ00, --给药途径 e.YGXZ00, a.ZFFSBZ, c.BH0000,a.ZYFPID,e.YBYGDM, null, --药师国家编码 null, --药师姓名 a.KDYS00, --开单医生 substrb(a.XMBH00,1,50) as bke181, --bke181:HIS院内项目编码 substrb(c.XMMC00,1,100) as bke182 --bke182:His内部名称 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), --SF_ZY_GETBRFYCZRQ:当PXTCS00=0并且PFLAG00 in ('1','2'),则返回空值 nvl(trim(SF_ZY_GETBRFYCZRQ(a.CZRQ00,a.OMXID0,LS_FSYBYPFYSF,'2')),max(a.CZRQ00)) CZRQ00, --'0',nvl(max(e.SFZBH0),'*'),null, '0',max(e.SFZBH0),null, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(max(a.yjjzid),0,'3'),''),max(a.XMSL00)),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'),'9999') as YPPL00, --医保频次编码 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'1'),'000'),'') as JX0000, --剂型 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'2'),''),'') as GYTJ00, --给药途径 max(e.YGXZ00), a.ZFFSBZ, c.YPBM00,max(a.ZYFPID),max(e.YBYGDM), null, --药师国家编码 null, --药师姓名 max(a.KDYS00) KDYS00, --开单医生 max(substrb(a.XMBH00,1,50)) as BKE181, --bke181:HIS院内项目编码 max(substrb(c.YPMC00,1,100)) as BKE182 --bke182:His内部名称 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, SF_ZY_GETBRFYCZRQ(a.CZRQ00,a.OMXID0,LS_FSYBYPFYSF,'2'); 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, --decode(LS_FSYBYPFYSF,'1',(select max(czrq00) from ZY_FYMX00 where ZYID00=a.ZYID00 and JZDH00+0=a.JZDH00 and SFLB00<>'3'),a.CZRQ00) CZRQ00, substrb(SF_ZY_GETBRFYCZRQ(a.CZRQ00,a.OMXID0,LS_FSYBYPFYSF,'0'),1,10) CZRQ00, --C.JSXM00,nvl(e.SFZBH0,'*'),C.GJBM00, C.JSXM00,e.SFZBH0,C.GJBM00, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'3'),''),a.XMSL00),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'),'9999') as YPPL00, --医保频次编码 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'1'),'000'),'') as JX0000, --剂型 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'2'),''),'') as GYTJ00, --给药途径 e.YGXZ00, a.ZFFSBZ, c.BH0000,a.ZYFPID,e.YBYGDM, null, --药师国家编码 null, --药师姓名 a.KDYS00, --开单医生 substrb(a.XMBH00,1,50) as bke181, --bke181:HIS院内项目编码 substrb(c.XMMC00,1,100) as bke182 --bke182:His内部名称 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 ( ((ad_JSXZID=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)))) or ((ad_JSXZID>0) and (a.MXID00 in (select MXID00 from ZY_JSXZMX where ZYID00 = a.ZYID00 and JSXZID = ad_JSXZID))) ) 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), --SF_ZY_GETBRFYCZRQ:当PXTCS00=0并且PFLAG00 in ('1','2'),则返回空值 nvl(trim(SF_ZY_GETBRFYCZRQ(a.CZRQ00,a.OMXID0,LS_FSYBYPFYSF,'2')),max(a.CZRQ00)) CZRQ00, --'0',nvl(max(e.SFZBH0),'*'),null, '0',max(e.SFZBH0),null, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(max(a.yjjzid),0,'3'),''),max(a.XMSL00)),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'),'9999') as YPPL00, --医保频次编码 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'1'),'000'),'') as JX0000, --剂型 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'2'),''),'') as GYTJ00, --给药途径 max(e.YGXZ00), a.ZFFSBZ, c.YPBM00,max(a.ZYFPID),max(e.YBYGDM), null, --药师国家编码 null, --药师姓名 max(a.KDYS00) KDYS00, --开单医生 max(substrb(a.XMBH00,1,50)) as BKE181, --bke181:HIS院内项目编码 max(substrb(c.YPMC00,1,100)) as BKE182 --bke182:His内部名称 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 ( ((ad_JSXZID=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)))) or ((ad_JSXZID>0) and (a.MXID00 in (select MXID00 from ZY_JSXZMX where ZYID00 = a.ZYID00 and JSXZID = ad_JSXZID))) ) and a.JZDH00+0=0 and (decode(nvl(a.YEXM00,'0'),'0','母亲',a.YEXM00)=ad_YEXM00 or ad_YEXM00='全部') and a.SFLB00='3' --and LS_FSYBYPFYSF <> '1' group by a.JMBZ00,c.YPMC00,c.YPGG00,a.XMDW00,a.XMBH00,a.XMDJ00,a.SFLB00,a.ZFFSBZ,c.YPBM00, SF_ZY_GETBRFYCZRQ(a.CZRQ00,a.OMXID0,LS_FSYBYPFYSF,'2'); -- union all -- select '2'FLAG00,max(a.MXID00),sum(a.ZFJE00),substrb(a.YPMC00,1,50) YPMC00,0,--包床标志 -- substrb(nvl(a.YPGG00,'无'),1,10) YPGG00,nvl(a.XMDW00,'无')CFDW00,a.XMBH00,max(a.ZWXM00), -- a.XMDJ00, -- sum(a.XMSL00),sum(a.HJJE00),max(a.ZFBL00),a.CZRQ00,'0',nvl(max(a.SFZBH0),'*'),null, -- substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(max(a.yjjzid),0,'3'),''),max(a.XMSL00)),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'),'9999') as YPPL00, --医保频次编码 -- decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'1'),'000'),'') as JX0000, --剂型 -- decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'2'),''),'') as GYTJ00, --给药途径 -- max(a.YGXZ00), a.ZFFSBZ, a.YPBM00,max(a.ZYFPID) -- from (select a.JMBZ00,a.MXID00,a.ZFJE00,substrb(c.YPMC00,1,50) YPMC00, -- substrb(nvl(c.YPGG00,'无'),1,10) YPGG00,nvl(a.XMDW00,'无') CFDW00,a.XMBH00,e.ZWXM00,a.XMDW00, -- a.XMDJ00,a.XMSL00,a.HJJE00,a.ZFBL00,SF_ZY_GETBRFYCZRQ(a.CZRQ00,a.OMXID0,LS_FSYBYPFYSF) CZRQ00,e.SFZBH0,a.YJJZID,e.YGXZ00,a.ZFFSBZ,c.YPBM00,a.SFLB00,a.ZYFPID -- 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 ( -- ((ad_JSXZID=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)))) -- or ((ad_JSXZID>0) and (a.MXID00 in (select MXID00 from ZY_JSXZMX where ZYID00 = a.ZYID00 and JSXZID = ad_JSXZID))) -- ) -- and a.JZDH00+0=0 -- and (decode(nvl(a.YEXM00,'0'),'0','母亲',a.YEXM00)=ad_YEXM00 or ad_YEXM00='全部') -- and a.SFLB00='3' -- and LS_FSYBYPFYSF = '1' -- ) a -- group by a.JMBZ00,a.YPMC00,a.YPGG00,a.XMDW00,a.XMBH00,a.XMDJ00,a.SFLB00,a.ZFFSBZ,a.YPBM00,a.CZRQ00; 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, substrb(SF_ZY_GETBRFYCZRQ(a.CZRQ00,a.OMXID0,LS_FSYBYPFYSF,'0'),1,10) CZRQ00, --C.JSXM00,nvl(e.SFZBH0,'*'),C.GJBM00, C.JSXM00,e.SFZBH0,C.GJBM00, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'3'),''),a.XMSL00),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'),'9999') as YPPL00, --医保频次编码 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'1'),'000'),'') as JX0000, --剂型 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'2'),''),'') as GYTJ00, --给药途径 e.YGXZ00, a.ZFFSBZ, c.BH0000,a.ZYFPID,e.YBYGDM, null, --药师国家编码 null, --药师姓名 a.KDYS00, --开单医生 substrb(a.XMBH00,1,50) as bke181, --bke181:HIS院内项目编码 substrb(c.XMMC00,1,100) as bke182 --bke182:His内部名称 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), nvl(trim(SF_ZY_GETBRFYCZRQ(a.CZRQ00,a.OMXID0,LS_FSYBYPFYSF,'2')),max(a.CZRQ00)) CZRQ00, --'0',nvl(max(e.SFZBH0),'*'),null, '0',max(e.SFZBH0),null, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(max(a.yjjzid),0,'3'),''),max(a.XMSL00)),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'),'9999') as YPPL00, --医保频次编码 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'1'),'000'),'') as JX0000, --剂型 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'2'),''),'') as GYTJ00, --给药途径 max(e.YGXZ00), a.ZFFSBZ, c.YPBM00,max(a.ZYFPID),max(e.YBYGDM), null, --药师国家编码 null, --药师姓名 max(a.KDYS00) KDYS00, --开单医生 max(substrb(a.XMBH00,1,50)) as BKE181, --bke181:HIS院内项目编码 max(substrb(c.YPMC00,1,100)) as BKE182 --bke182:His内部名称 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, SF_ZY_GETBRFYCZRQ(a.CZRQ00,a.OMXID0,LS_FSYBYPFYSF,'2'); 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, substrb(SF_ZY_GETBRFYCZRQ(a.CZRQ00,a.OMXID0,LS_FSYBYPFYSF,'0'),1,10) CZRQ00, --C.JSXM00,nvl(e.SFZBH0,'*'),C.GJBM00, C.JSXM00,e.SFZBH0,C.GJBM00, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'3'),''),a.XMSL00),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'),'9999') as YPPL00, --医保频次编码 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'1'),'000'),'') as JX0000, --剂型 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'2'),''),'') as GYTJ00, --给药途径 e.YGXZ00, a.ZFFSBZ, c.BH0000,a.ZYFPID,e.YBYGDM, null, --药师国家编码 null, --药师姓名 a.KDYS00, substrb(a.XMBH00,1,50) as bke181, --bke181:HIS院内项目编码 substrb(c.XMMC00,1,100) as bke182 --bke182:His内部名称 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), nvl(trim(SF_ZY_GETBRFYCZRQ(a.CZRQ00,a.OMXID0,LS_FSYBYPFYSF,'2')),max(a.CZRQ00)) CZRQ00, --'0',nvl(max(e.SFZBH0),'*'),null, '0',max(e.SFZBH0),null, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(max(a.yjjzid),0,'3'),''),max(a.XMSL00)),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'),'9999') as YPPL00, --医保频次编码 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'1'),'000'),'') as JX0000, --剂型 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'2'),''),'') as GYTJ00, --给药途径 max(e.YGXZ00), a.ZFFSBZ, c.YPBM00,max(a.ZYFPID),max(e.YBYGDM), null, --药师国家编码 null, --药师姓名 max(a.KDYS00) KDYS00, --开单医生 max(substrb(a.XMBH00,1,50)) as BKE181, --bke181:HIS院内项目编码 max(substrb(c.YPMC00,1,100)) as BKE182 --bke182:His内部名称 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, SF_ZY_GETBRFYCZRQ(a.CZRQ00,a.OMXID0,LS_FSYBYPFYSF,'2'); 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, substrb(SF_ZY_GETBRFYCZRQ(a.CZRQ00,a.OMXID0,LS_FSYBYPFYSF,'0'),1,10) CZRQ00, --C.JSXM00,nvl(e.SFZBH0,'*'),C.GJBM00, C.JSXM00,e.SFZBH0,C.GJBM00, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'3'),''),a.XMSL00),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'),'9999') as YPPL00, --医保频次编码 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'1'),'000'),'') as JX0000, --剂型 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'2'),''),'') as GYTJ00, --给药途径 e.YGXZ00, a.ZFFSBZ, c.BH0000,a.ZYFPID,e.YBYGDM, null, --药师国家编码 null, --药师姓名 a.KDYS00, --开单医生 substrb(a.XMBH00,1,50) as bke181, --bke181:HIS院内项目编码 substrb(c.XMMC00,1,100) as bke182 --bke182:His内部名称 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), nvl(trim(SF_ZY_GETBRFYCZRQ(a.CZRQ00,a.OMXID0,LS_FSYBYPFYSF,'2')),max(a.CZRQ00)) CZRQ00, --'0',nvl(max(e.SFZBH0),'*'),null, '0',max(e.SFZBH0),null, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(max(a.yjjzid),0,'3'),''),max(a.XMSL00)),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'),'9999') as YPPL00, --医保频次编码 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'1'),'000'),'') as JX0000, --剂型 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'2'),''),'') as GYTJ00, --给药途径 max(e.YGXZ00), a.ZFFSBZ, c.YPBM00,max(a.ZYFPID),max(e.YBYGDM), null, --药师国家编码 null, --药师姓名 max(a.KDYS00) KDYS00, --开单医生 max(substrb(a.XMBH00,1,50)) as BKE181, --bke181:HIS院内项目编码 max(substrb(c.YPMC00,1,100)) as BKE182 --bke182:His内部名称 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, SF_ZY_GETBRFYCZRQ(a.CZRQ00,a.OMXID0,LS_FSYBYPFYSF,'2'); --用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, substrb(SF_ZY_GETBRFYCZRQ(a.CZRQ00,a.OMXID0,LS_FSYBYPFYSF,'0'),1,10) CZRQ00, --C.JSXM00,nvl(e.SFZBH0,'*'),C.GJBM00, C.JSXM00,e.SFZBH0,C.GJBM00, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(a.yjjzid,0,'3'),''),a.XMSL00),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'),'9999') as YPPL00, --医保频次编码 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'1'),'000'),'') as JX0000, --剂型 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'2'),''),'') as GYTJ00, --给药途径 e.YGXZ00, a.ZFFSBZ, c.BH0000,a.ZYFPID,e.YBYGDM, null, --药师国家编码 null, --药师姓名 f.SXYS00, --主管医生 substrb(a.XMBH00,1,50) as bke181, --bke181:HIS院内项目编码 substrb(c.XMMC00,1,100) as bke182 --bke182:His内部名称 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), nvl(trim(SF_ZY_GETBRFYCZRQ(a.CZRQ00,a.OMXID0,LS_FSYBYPFYSF,'2')),max(a.CZRQ00)) CZRQ00, --'0',nvl(max(e.SFZBH0),'*'),null, '0',max(e.SFZBH0),null, substrb(decode(a.SFLB00,'3',nvl(SF_SF_GETYYTS(max(a.yjjzid),0,'3'),''),max(a.XMSL00)),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'),'9999') as YPPL00, --医保频次编码 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'1'),'000'),'') as JX0000, --剂型 decode(a.SFLB00,'3',nvl(SF_XT_GETYPYBXX(a.XMBH00,'2'),''),'') as GYTJ00, --给药途径 max(e.YGXZ00), a.ZFFSBZ, c.YPBM00,max(a.ZYFPID),max(e.YBYGDM), null, --药师国家编码 null, --药师姓名 max(f.SXYS00), --主管医生 max(substrb(a.XMBH00,1,50)) as BKE181, --bke181:HIS院内项目编码 max(substrb(c.YPMC00,1,100)) as BKE182 --bke182:His内部名称 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, SF_ZY_GETBRFYCZRQ(a.CZRQ00,a.OMXID0,LS_FSYBYPFYSF,'2'); 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_BKEH83:=null;--国家标准对应编码 LS_BKEH05:=null;--医保备用1 LS_BKEH06:=null;--医保备用2 LS_BAKXML:=null;--HIS备用字段(xml格式)后续扩展 LS_YYSFXM_BKEH83:=null;--医院收费项目：医保国家标准编码 LS_YBSFDY_BKEH83:=null;--医院收费对应：医保国家标准编码 LS_YBSFXM_BKEH83:=null;--医保收费项目：医保国家标准编码 LS_ZGXJCF:='N';--医保药品最高限价拆分 Y:拆分 N:不拆分 M:单价不超限价改变数量 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'; --发送医保药品费用是否按日期汇总, 0否1是, 默认0 select trim(nvl(max(VALUE0),'0')) into LS_FSYBYPFYSF from XT_XTCS00 where NAME00='ZY_FSYBYPFYSFRQHZ'; 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,SLBLWS,CBCWMS into ls_YBZXLB,ls_FYBQZ0,ls_SFXNH0,LS_SLBLWS,LS_CBCWMS 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(trim(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; --BM_GZHCBZ.DBZBM0->BM_GZHCFL.GZHCID 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; --医保病人床位费是否按最高标准(BM_YBBRLB.CWZGBZ)拆分(启用字典参数ZY_SFXMCGSXSFCF无效), 0不拆分1所有医保病人拆分2单病种病人拆分3旧医保接口和单病种病人拆分, 默认1 ls_CWFCF0 := nvl(SF_XT_TYZDMC('医保新接口参数设置','CWFSFAZGBZCF'),'1'); --BKC007发送医师国家编码0否1门诊2住院3门诊住院, 默认0 ls_BKC007FSYSGJBM := nvl(SF_XT_TYZDMC('医保新接口参数设置','BKC007_FSYSGJBM'),'0'); --泉州医保接口要求单价*数量必须等于金额 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'; --超标项目名称是否使用医保项目名称,1是0否,默认为0 ZYSF9-20210720-002 ls_CBXMMCSYYBXMM := nvl(SF_XT_TYZDMC('医保新接口参数设置','ZY_CBXMMCSYYBXMMC'),'0'); --GetType_bkeh83:新医保接口bkeh83(国家标准对应编码)取值方式 1:优先取BM_YBSFDY(为空取BM_YYSFXM),0:优先取BM_YYSFXM(为空取BM_YBSFDY) LS_GetType_bkeh83:=nvl(substrb(SF_XT_TYZDMC('医保新接口参数设置','GetType_bkeh83'),1,10),'1'); --BKC007发送医师国家编码0否1门诊2住院3门诊住院, 默认0 ls_BKC007FSYSGJBM := nvl(SF_XT_TYZDMC('医保新接口参数设置','BKC007_FSYSGJBM'),'0'); --新医保接口bkeh83(国家编码C码)是否转化为bke045(医保结算编码)第一位:0:不开启 1:门诊2:住院3:门诊和住院,第二位(1:项目2:药品3:项目和药品)默认值:00 LS_Change_bkeh83_bke045 := nvl(SF_XT_TYZDMC('医保新接口参数设置','Change_bkeh83_bke045'),'00'); --新医保接口bke182(院内项目名称)是否转化为bke046(医保项目名称)第一位:0:不开启 1:门诊2:住院3:门诊和住院,第二位(1:项目2:药品3:项目和药品)默认值:00 LS_Change_bke182_bke046 := nvl(SF_XT_TYZDMC('医保新接口参数设置','Change_bke182_bke046'),'00'); --开单医生无国家医师编码是否改用主管医生, 0否1是, 默认0 LS_WGJYSBMSFGYZGYS := nvl(SF_XT_TYZDMC('医保新接口参数设置','ZY_WGJYSBMSFGYZGYS'),'0'); 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'; --新医保明细上传ZFFSBZ=Y项目是否按原编码上传，Y：是,N：否,默认N select nvl(max(VALUE0),'N') into ls_MXCSFSYBMSC from XT_XTCS00 where NAME00='ZY_MXCSFSYBMSC'; --住院超标药品是否使用原医保编码和名称(是否医保=N) 1:是 0:否(参数ZY_YBYPZGXJCF控制)默认:0 ZYSF9-20210831-002 ls_CBYPMCSYYBYPMC := nvl(SF_XT_TYZDMC('医保新接口参数设置','ZY_CBYPMCSYYBYPMC'),'0'); if ad_SFXYB0='0' then ls_CBYPMCSYYBYPMC := '0'; end if; --YBZXLB_MXCSFSYBMSC:新医保医保项目70开头项目编码(系统参数:SF_MXCSFSYBMSC、ZY_MXCSFSYBMSC=Y)不转成原码医保中心类别(IC_YBBRLB.YBZXLB)多个用|隔开 默认值:空值 LS_YBZXLB_MXCSFSYBMSC:=trim(substrb(SF_XT_TYZDMC('医保新接口参数设置','YBZXLB_MXCSFSYBMSC'),1,100)); if LS_YBZXLB_MXCSFSYBMSC is not null and instrb('|'||LS_YBZXLB_MXCSFSYBMSC||'|','|'||trim(LS_YBZXLB)||'|')>0 then LS_MXCSFSYBMSC:='N';--新医保明细上传项目是否按原编码上传，Y：是,N：否 （70或90开头）,默认N end if; if ad_SFXYB0='0' then--是否新医保 0:否 1:是 LS_MXCSFSYBMSC:='N';--新医保明细上传项目是否按原编码上传，Y：是,N：否 （70或90开头）,默认N end if; --如果医保中心有设置医保项目数量小数点保留位数, 则以医保中心设置为准, 不以参数ZY_FSYBXMSL_SRWS为准 if nvl(LS_SLBLWS,0) > 0 then ls_SSWRWS := LS_SLBLWS; end if; --医保编号前缀编码,是否目录限制 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:农合拆分(医保除外) 4:单价不超限价改变数量 默认值: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:农合启用(医保除外) if (substrb(ls_YBZGXJ,1,1)='4') then LS_ZGXJCF:='M'; end if; --4:单价不超限价改变数量 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,ZFFSBZ) --结算补充费用明细表 select ad_ZYID00,ad_ID0000,0,MXID00,decode(SFLB00,3,3,0),XMBH00,nvl(ZFFSBZ,'N') from ZY_FYMX00 a where ZYID00 = ad_ZYID00 and JZDH00+0 = 0 and CZRQ00 > as_ZZRQ00 and (XMBH00,decode(SFLB00,3,3,0),nvl(ZFFSBZ,'N')) in ( select XMBH00,decode(SFLB00,3,3,0),nvl(ZFFSBZ,'N') from ZY_FYMX00 where ZYID00=ad_ZYID00 and JZDH00+0=0 and CZRQ00>as_ZZRQ00 group by ZYID00,XMBH00,decode(SFLB00,3,3,0),nvl(ZFFSBZ,'N') 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_JX0000, ls_GYTJ00,ls_YGXZ00,ls_ZFFSBZ,ls_XMBM00,ls_ZYFPID,ls_YBYGDM,ls_BKC507, ls_BKC506,ls_KDYS00,LS_BKE181,LS_BKE182; 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_JX0000, ls_GYTJ00,ls_YGXZ00,ls_ZFFSBZ,ls_XMBM00,ls_ZYFPID,ls_YBYGDM,ls_BKC507, ls_BKC506,ls_KDYS00,LS_BKE181,LS_BKE182; 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_JX0000, ls_GYTJ00,ls_YGXZ00,ls_ZFFSBZ,ls_XMBM00,ls_ZYFPID,ls_YBYGDM,ls_BKC507, ls_BKC506,ls_KDYS00,LS_BKE181,LS_BKE182; 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_JX0000, ls_GYTJ00,ls_YGXZ00,ls_ZFFSBZ,ls_XMBM00,ls_ZYFPID,ls_YBYGDM,ls_BKC507, ls_BKC506,ls_KDYS00,LS_BKE181,LS_BKE182; 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_JX0000, ls_GYTJ00,ls_YGXZ00,ls_ZFFSBZ,ls_XMBM00,ls_ZYFPID,ls_YBYGDM,ls_BKC507, ls_BKC506,ls_KDYS00,LS_BKE181,LS_BKE182; 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_JX0000, ls_GYTJ00,ls_YGXZ00,ls_ZFFSBZ,ls_XMBM00,ls_ZYFPID,ls_YBYGDM,ls_BKC507, ls_BKC506,ls_KDYS00,LS_BKE181,LS_BKE182; exit when CUR_ZY_FYMX00_MXID06%notfound; end if; LS_BKEH83:=null;--国家标准对应编码 LS_BKEH05:=null;--医保备用1 LS_BKEH06:=null;--医保备用2 LS_BAKXML:=null;--HIS备用字段(xml格式)后续扩展 LS_YYSFXM_BKEH83:=null;--医院收费项目：医保国家标准编码 LS_YBSFDY_BKEH83:=null;--医院收费对应：医保国家标准编码 LS_YBSFXM_BKEH83:=null;--医保收费项目：医保国家标准编码 --获取住院发票编号 select nvl(max(BH0000),'00') into ls_ZYFPBH from BM_FPXM00 where FPXMID=ls_ZYFPID; --药品需要审批,如果未审批的，按自费项目处理 LS_WSYPSL := 0; 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替换 --2020.10.22 当审批药品数量<病人实际发生药品数量,要求审批过的数量按医保结算,未审批过的数量按照自费计算 ls_SFTHML := SF_XT_GETFYBMLBH('1',ls_BRID00,ls_XMBH00,ad_ZYID00,ls_XMSL00,ls_DW0000,1); if ls_SFTHML<0 then LS_WSYPSL := -ls_SFTHML; if LS_WSYPSL >= ls_XMSL00 then LS_WSYPSL := 0; ls_SFTHML := 1; else ls_SFTHML := 0; end if; end if; else ls_SFTHML:=0; end if; --开单医生非处方医生 或为厦门医保，改用主管医生 if (instrb(ls_YGXZ00,'1')=0) or (ls_XMYBJK = 'Y') then --select nvl(e.SFZBH0,'*'),nvl(e.ZWXM00,'*'),e.YBYGDM into ls_YSZJHM,ls_ZWXM00,ls_YBYGDM from ZY_BRXXB0 a,BM_YGBM00 e where a.zyid00=ad_ZYID00 and a.sxys00=e.ygbh00; select e.SFZBH0,e.ZWXM00,e.YBYGDM,a.SXYS00 into ls_YSZJHM,ls_ZWXM00,ls_YBYGDM,ls_KDYS00 from ZY_BRXXB0 a,BM_YGBM00 e where a.zyid00=ad_ZYID00 and a.sxys00=e.ygbh00; elsif nvl(ad_SFXYB0,'0') = '1' and ls_BKC007FSYSGJBM in ('2','3') and nvl(LS_WGJYSBMSFGYZGYS,'0') = '1' then --新医保启用发送国家医生编码字典 --医师没有国家医师编码是否改用主管医生 if trim(ls_YBYGDM) is null then select e.SFZBH0,e.ZWXM00,e.YBYGDM,a.SXYS00 into ls_YSZJHM,ls_ZWXM00,ls_YBYGDM,ls_KDYS00 from ZY_BRXXB0 a,BM_YGBM00 e where a.zyid00=ad_ZYID00 and a.sxys00=e.ygbh00; end if; 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,C.BKEH83 as YBSFDY_BKEH83,D.BKEH83 as YBSFXM_BKEH83, upper(substrb(nvl(C.BKEA96,'Y'),1,1)) as BKEA96 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,LS_YBSFDY_BKEH83,LS_YBSFXM_BKEH83,LS_YBSFDY_BKEA96 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; --2021.09.02 SFYBXM改为取BM_YBSFDY.BKEA96 if ad_SFXYB0='0' then--是否新医保 0:否 1:是 LS_SFYBXM:=LS_SFYBXM; --旧医保根据SFYB00控制 elsif ad_SFXYB0='1' then--是否新医保 0:否 1:是 if LS_YBZXLB_MXCSFSYBMSC is not null then if instrb('|'||LS_YBZXLB_MXCSFSYBMSC||'|','|'||trim(LS_YBZXLB)||'|')>0 then LS_SFYBXM:=LS_SFYBXM; --某些费别，比如两费保健等等，取BM_YBSFDY.SFYBXM else LS_SFYBXM:=LS_YBSFDY_BKEA96; end if; else LS_SFYBXM:=LS_YBSFDY_BKEA96; end if; end if; --zhangyc 2021.08.18 增加参数GetType_bkeh83控制(国家标准对应编码)取值方式 1:优先取BM_YBSFDY(为空取BM_YYSFXM),0:优先取BM_YYSFXM(为空取BM_YBSFDY) LS_BKEH83:=LS_YBSFDY_BKEH83; if LS_FLAG00='0' then begin select BKEH83 into LS_YYSFXM_BKEH83 from BM_YYSFXM B where B.SFXMID=ls_XMBH00 and b.TZRQ00 is null; exception when others then LS_YYSFXM_BKEH83:=null; end; --GetType_bkeh83:新医保接口bkeh83(国家标准对应编码)取值方式 1:优先取BM_YBSFDY(为空取BM_YYSFXM),0:优先取BM_YYSFXM(为空取BM_YBSFDY) if LS_GetType_bkeh83='0' then LS_BKEH83:=nvl(nvl(LS_YYSFXM_BKEH83,LS_YBSFXM_BKEH83),LS_YBSFDY_BKEH83); --0:优先取BM_YYSFXM(为空取BM_YBSFDY) else LS_BKEH83:=nvl(LS_YBSFDY_BKEH83,nvl(LS_YYSFXM_BKEH83,LS_YBSFXM_BKEH83)); --1:优先取BM_YBSFDY(为空取BM_YYSFXM) end if; end if; --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; --2020.03.10 qks 包床项目的费用定义为bm_yysfxm.xmmc00中包含“包床”两个字的,也算入超标床位费; 如果发票不属于“床位费”，需要重新获取ls_BZCBBH --2021.08.06 qks 单病种以及CDRG患者,当收费发票项目属于床位费,但是在BM_YBSFDY中对应为非床位费非医保项目，那么也按包床逻辑处理，全部算入超标床位费； if instrb(ls_XMMC00,'包床')>0 --2021.08.06 or (nvl(ls_FFBZBM,LS_DBZBM0) is not null or LS_CDRGJK='1' and LS_CDRGFS = '1') and ls_ZYFPBH='05' and instrb(ls_FPXMMC,'床位')=0 and ls_SFYBXM='N' then if instrb(ls_FPXMMC,'床位')>0 or ls_ZYFPBH='05' then ls_BCBZ00 := 1; end if; if instrb(ls_FPXMMC,'床位')=0 and ls_ZYFPBH='05' then 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; 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; --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; --2021.10.10 elsif LS_HJJE00=0 then LS_DW0000:=LS_YBDW00; end if; end if; end if; --2021.04.17 qks LS_YBDDW0,LS_YBXDW0,LS_YBZGDJ,LS_YBZGDJ1 --if LS_ZGXJID<=0 then if LS_ZGXJCF='M' then if trim(LS_DW0000)=trim(LS_YBXDW0) and nvl(LS_YBZGDJ1,0)>0 and LS_XMDJ00>nvl(LS_YBZGDJ1,0) then LS_XMDJ00 := LS_YBZGDJ1; end if; if trim(LS_DW0000)=trim(LS_YBDDW0) and nvl(LS_YBZGDJ,0)>0 and LS_XMDJ00>nvl(LS_YBZGDJ,0) then LS_XMDJ00 := LS_YBZGDJ; end if; if LS_XMDJ00 <> 0 then 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); if round(LS_XMSL00*LS_XMDJ00,2)<>round(LS_HJJE00,2) then LS_XMDJ00 := round(LS_HJJE00/LS_XMSL00,4); end if; else LS_XMSL00 := round(LS_HJJE00/LS_XMDJ00,ls_SSWRWS); end if; end if; end if; end if; --CDRG 2018.01.06 LS_CLYBSX:=0; --医保上限金额(材料) LS_CLCBBH:=null;--超标项目编号(材料) LS_CLCBMC:=null;--超标项目名称(材料) LS_YPYBSX:=0; --医保上限金额(药品) LS_YPCBBH:=null;--超标项目编号(药品) LS_YPCBMC:=null;--超标项目名称(药品) LS_SFCFXM:='N'; --是否拆分项目 Y:材料(超标) M:床位费 M1:包床 N:否 L:材料(非超标) P:药品(超标) LS_BZCBMC:=null; LS_YPCBDW:=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 and nvl(SFYP00,'N')='N'; 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; --CDRG获取药品医保上限 LS_YPYBSX:医保上限金额(药品) LS_YPCBBH:超标项目编号(药品) LS_YPCBMC:超标项目名称(药品) if LS_CDRGJK='1' and LS_CDRGFS = '1' and LS_FLAG00 in ('2') then begin select nvl(YBSXJE,0),CBXMBH,CBXMMC,CBXMDW into LS_YPYBSX,LS_YPCBBH,LS_YPCBMC,LS_YPCBDW from BM_YBCBDY where YBZXLB=LS_YBZXLB and FBBH00=LS_FBBH00 and SFXMID=LS_XMBH00 and nvl(SFYP00,'N')='Y'; exception when others then LS_YPYBSX:=0;--医保上限金额(药品) LS_YPCBBH:=null;--超标项目编号(药品) LS_YPCBMC:=null;--超标项目名称(药品) LS_YPCBDW:=null;--超标项目单位(药品) end; --if LS_FLAG00 in ('2') and LS_YPCBBH is not null and nvl(LS_YBXMSX,0)=0 and nvl(LS_YPYBSX,0)>0 then --如果医保对应表没有维护上限，超标表有维护上限，重新赋值 -- LS_YBXMSX:=nvl(LS_YPYBSX,0); --end if; --if nvl(ls_XMDJ00,0)>nvl(LS_YBXMSX,0) then --超标药品没有上限, 只要设置超标编号就需要拆分 --2021.09.15 CDRG:超标药品增加判断医保上限金额,增加条件 and nvl(ls_XMDJ00,0)>=nvl(LS_YPYBSX,0) --if LS_YPCBBH is not null then if LS_YPCBBH is not null and nvl(ls_XMDJ00,0)>=nvl(LS_YPYBSX,0) then LS_SFCFXM:='P'; else LS_SFCFXM:='N'; end if; if LS_SFCFXM in ('P') then LS_BZCBBH:=substrb(LS_YPCBBH,1,20); LS_BZCBMC:=substrb(LS_YPCBMC,1,50); 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' and (ad_SFXYB0 = '0' or (ad_SFXYB0 = '1' and ls_MXCSFSYBMSC <> 'Y'))) then if ls_CBXMBH not in ('700000000001','700000000002','700000000003','700000000004') or (ls_FLAG00 = '2' and nvl(ls_CBYPMCSYYBYPMC,'0') not in ('1','3')) or (ls_FLAG00 = '0' and nvl(ls_CBYPMCSYYBYPMC,'0') not in ('2','3')) or ad_SFXYB0 in ('0') then ls_YBXMMC:=ls_CBXMMC; ls_YBXMBH:=substrb(ls_CBXMBH,1,50); ls_FPXMMC:=ls_CBFPMC; ls_FPXMBH:=ls_CBFPBH; end if; 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 --2021.09.16 单病种超标床位费由医保返回时所有床位费不转超标 if (LS_CBCWMS not in ('1') or nvl(ls_FFBZBM,LS_DBZBM0) is null) --超标床位费非医保返回或非单病种需转超标项目 and (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 --2021.09.15 注释 --ls_YBXMBH := '99000000000005'; else --2021.09.26超标项目编号非(700000000001,700000000002,700000000003,700000000004) --2021.09.26或超标项目按原医保编码和名称未启用(ZY_CBYPMCSYYBYPMC不等于2或3) --2021.09.26或旧医保 --2021.09.26医保项目名称转成超标项目名称 if ls_CBXMBH not in ('700000000001','700000000002','700000000003','700000000004') or nvl(ls_CBYPMCSYYBYPMC,'0') not in ('2','3') or ad_SFXYB0 in ('0') then ls_YBXMMC:=ls_CBXMMC; ls_YBXMBH:=substrb(ls_CBXMBH,1,50); ls_FPXMMC:=ls_CBFPMC; ls_FPXMBH:=ls_CBFPBH; ls_YBXMSX:=9999999999; end if; end if; --2021.08.06 ls_FPXMMC值判断改为 instrb(ls_FPXMMC,'床位')>0 --if ls_BZCBBH is not null and (ls_FPXMMC='床位费' or ls_FPXMMC='超标床位费' or ls_FPXMMC='超普通标准床位费') then if ls_BZCBBH is not null and instrb(ls_FPXMMC,'床位')>0 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:=substrb(ls_CBXMBH,1,50); 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; --2021.09.27 ZYSF9-20210924-002 LS_SFCF01 := ls_CGSXSFCF; --默认参数ZY_SFXMCGSXSFCF配置值, 床位费另外判断 if ls_FPXMBH in ('05') then --床位费是否按最高标准(BM_YBBRLB.CWZGBZ)拆分 if ls_CWFCF0 = '1' then --所有病人拆分 LS_SFCF01 := 'Y'; elsif ls_CWFCF0 = '2' and nvl(ls_FFBZBM,LS_DBZBM0) is not null then --单病种病人拆分 LS_SFCF01 := 'Y'; elsif ls_CWFCF0 = '3' then --旧医保和单病种拆分 if ad_SFXYB0 = '0' then --旧医保 LS_SFCF01 := 'Y'; elsif nvl(ls_FFBZBM,LS_DBZBM0) is not null then --单病种 LS_SFCF01 := 'Y'; else --其它不拆 LS_SFCF01 := 'N'; end if; end if; end if; --医师证件编号/医师国家编码 ls_BKC007 := ls_YSZJHM; --新医保且启用发送医师国家编码 if ad_SFXYB0 in ('1') and ls_BKC007FSYSGJBM in ('2','3') then ls_BKC007 := nvl(ls_YBYGDM,ls_YSZJHM); end if; --判断实际单价是否超过医保上线,如果是要拆分成两条记录 --2020.03.19 ls_SFYBXM='Y'改为 (ls_SFYBXM='Y' or nvl(ls_FFBZBM,LS_DBZBM0) is not null) --2021.04.14 C-DRG另算高值药品没有上限, 只要有对应的药品超标编号即需拆分 --2021.09.15 CDRG:超标药品增加判断医保上限金额,增加条件 and nvl(ls_XMDJ00,0)>=nvl(LS_YPYBSX,0) --2021.09.16 单病种超标床位费由医保返回时床位费不进行拆分 if (ls_XMDJ00>ls_YBXMSX and (ls_YBXMSX>0 or (LS_CDRGJK='1' and LS_CDRGFS = '1' and LS_FLAG00 in ('2') and LS_YPCBBH is not null and nvl(ls_XMDJ00,0)>=nvl(LS_YPYBSX,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_SFCF01 = 'Y' --2021.09.27 ZYSF9-20210924-002 --and (ls_CGSXSFCF='Y' or --启用医保限价拆分, -- --未启用医保限价拆分床位费也要根据字典确定是否拆分 -- (ls_FPXMBH in ('05') and (ls_CWFCF0 = '1' or --所有病人拆分 -- (ls_CWFCF0 = '2' and nvl(ls_FFBZBM,LS_DBZBM0) is not null )))) --单病种拆分 and (nvl(ls_FFBZBM,ls_DBZBM0) is null or LS_CBCWMS = '0' or (LS_CBCWMS = '1' and ls_FPXMBH not in ('05'))) --单病种超标床位费计算模式为医保返回时床位费项目不进行拆分 and (ls_YBXMBH is not null) then --超过上线 拆分成两条记录 --龙岩人民床位费需拆分2021.04.23 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,JX0000,GYTJ00,BKEH83,BKEH05,BKEH06,BAKXML,BKC507,BKC506, MXID00,YSYGBH,BKE181,BKE182) 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','P') then LS_XMDJ00 else LS_YBXMSX end, ls_XMSL00, round((case when LS_SFCFXM in ('Y','L','M','M1','P') then LS_XMDJ00 else LS_YBXMSX end)*ls_XMSL00,2), ls_ZWXM00,ls_CBXMBH,ls_CZRQ00, --decode(ad_SFXYB0,'0',ls_YSZJHM,decode(ls_BKC007FSYSGJBM,'2',nvl(ls_YBYGDM,ls_YSZJHM),'3',nvl(ls_YBYGDM,ls_YSZJHM),ls_YSZJHM)), --是否启用发送医师国家编码 ls_BKC007, 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','P','5','0'), ls_FPXMBH,ls_JX0000,ls_GYTJ00,LS_BKEH83,LS_BKEH05,LS_BKEH06,LS_BAKXML,ls_BKC507,ls_BKC506, SQ_BM_ZYCFMX_TEMP00_MXID00.nextval,ls_KDYS00,LS_BKE181,LS_BKE182 ); --2021.04.27 药品超过BM_YBSFDY.SFSX00情况下，单价改为差额,数量改为1。 --ls_XMDJ01 := ls_XMDJ00-(case when LS_SFCFXM in ('Y','L','M1','P') then 0 else LS_YBXMSX end); --ls_XMSL01 := ls_XMSL00; --ls_HJJE01 := round((ls_XMDJ00-(case when LS_SFCFXM in ('Y','L','M1','P') then 0 else LS_YBXMSX end))*ls_XMSL00,2); if LS_SFCFXM in ('Y','L','M1','P') then ls_XMDJ01 := ls_XMDJ00; ls_XMSL01 := ls_XMSL00; elsif ls_FLAG00='2' then ls_XMDJ01 := ls_HJJE00-round(LS_YBXMSX*ls_XMSL00,2); --ls_XMSL01 := 1; --2021.09.06 继续超出限价 ls_XMSL01 := ceil(ls_XMDJ01/LS_YBXMSX); ls_XMDJ01 := round(ls_XMDJ01/ls_XMSL01,4); else ls_XMDJ01 := ls_XMDJ00-LS_YBXMSX; ls_XMSL01 := ls_XMSL00; end if; ls_HJJE01 := round(ls_XMDJ01*ls_XMSL01,2); --2021.08.31 qks 通用字典控制,重新整理为变量ls_YBXMMC1,ls_YBXMBH1,ls_FPXMMC1,ls_FPXMBH1 --2021.09.27 用变量控制 LS_SFFSYM := 'N'; --是否发送原项目名称 if ls_CBXMBH in ('700000000001','700000000002','700000000003','700000000004') then --只有700打头才做转换 if nvl(ls_CBXMMCSYYBXMM,'0')='1' then --bh0000=ZY_CBXMMCSYYBXMMC启用 LS_SFFSYM := 'Y'; elsif ls_FLAG00 = '2' and nvl(ls_CBYPMCSYYBYPMC,'0') in ('1','3') then LS_SFFSYM := 'Y'; elsif ls_FLAG00 = '0' and nvl(ls_CBYPMCSYYBYPMC,'0') in ('2','3') then LS_SFFSYM := 'Y'; end if; end if; --if ((ls_FLAG00 = '2' and nvl(ls_CBYPMCSYYBYPMC,'0') in ('1','3')) -- or (ls_FLAG00 = '0' and nvl(ls_CBYPMCSYYBYPMC,'0') in ('2','3')) -- or nvl(ls_CBXMMCSYYBXMM,'0')='1' then if LS_SFFSYM = 'Y' then ls_YBXMMC1 := substr(case when ls_YBLB00='Z' then ls_XMMC00 when ls_YBMCFS ='Y' then ls_YBXMMC when ls_YBMCFS ='M' then ls_YBXMMC||'('||ls_XMMC00||')' else ls_XMMC00 end, 1,50); else ls_YBXMMC1 := substr(case when ls_YBLB00='Z' then ls_XMMC00 when ls_YBMCFS ='Y' then ls_CBXMMC when ls_YBMCFS ='M' then ls_CBXMMC||'('||ls_XMMC00||')' else ls_XMMC00 end, 1,50); end if; if ls_CBXMBH in ('700000000001','700000000002','700000000003','700000000004') --只有700打头的超标编号才发原编码 and ((ls_FLAG00 = '2' and nvl(ls_CBYPMCSYYBYPMC,'0') in ('1','3')) or (ls_FLAG00 = '0' and nvl(ls_CBYPMCSYYBYPMC,'0') in ('2','3'))) then ls_YBXMBH1 := ls_YBXMBH; if ls_SYFPBH = 'Y' then ls_FPXMMC1 := ls_FPXMBH; else ls_FPXMMC1 := ls_FPXMMC; end if; ls_FPXMBH1 := ls_FPXMBH; else ls_YBXMBH1 := ls_CBXMBH; if ls_SYFPBH = 'Y' then ls_FPXMMC1 := ls_CBFPBH; else ls_FPXMMC1 := ls_CBFPMC; end if; ls_FPXMBH1 := ls_CBFPBH; end if; --2021.09.15 CDRG:发送超标药品单位 ls_DW0000->nvl(LS_YPCBDW,ls_DW0000) 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,JX0000,GYTJ00,BKEH83,BKEH05,BKEH06,BAKXML,BKC507,BKC506, MXID00,YSYGBH,BKE181,BKE182) --ls_CBXMBH, --decode(ls_SYFPBH,'Y',ls_CBFPBH,ls_CBFPMC), --substr(decode(ls_YBLB00,'Z',ls_XMMC00,decode(ls_YBMCFS,'Y',decode(ls_CBXMMCSYYBXMM,'1',ls_YBXMMC,ls_CBXMMC),'M',decode(ls_CBXMMCSYYBXMM,'1',ls_YBXMMC,ls_CBXMMC)||'('||ls_XMMC00||')',ls_XMMC00)),1,50), --ls_CBFPBH values(ls_ID0000,ls_YBXMBH1,'N',ls_FPXMMC1, ls_YBXMMC1, ls_GG0000,nvl(LS_YPCBDW,ls_DW0000), --ls_XMDJ00-(case when LS_SFCFXM in ('Y','L','M1','P') then 0 else LS_YBXMSX end), ls_XMDJ01, --ls_XMSL00, ls_XMSL01, --round((ls_XMDJ00-(case when LS_SFCFXM in ('Y','L','M1','P') then 0 else LS_YBXMSX end))*ls_XMSL00,2), ls_HJJE01, ls_ZWXM00,ls_CBXMBH,ls_CZRQ00, --decode(ad_SFXYB0,'0',ls_YSZJHM,decode(ls_BKC007FSYSGJBM,'2',nvl(ls_YBYGDM,ls_YSZJHM),'3',nvl(ls_YBYGDM,ls_YSZJHM),ls_YSZJHM)), --是否启用发送医师国家编码 ls_BKC007, 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','P','5','0'), ls_FPXMBH1,ls_JX0000,ls_GYTJ00,null,null,null,LS_BAKXML,ls_BKC507,ls_BKC506, SQ_BM_ZYCFMX_TEMP00_MXID00.nextval,ls_KDYS00,LS_BKE181,LS_BKE182 ); --2017.03.13 qks 单病种超标床位费 if ls_BZCBBH is not null and (ls_FPXMMC='床位费' or ls_FPXMMC='超标床位费' or ls_FPXMMC='超普通标准床位费') then ls_BZCBCW := ls_BZCBCW + round((ls_XMDJ00-ls_YBXMSX)*ls_XMSL00,2); end if; else--不做任何转换 --2020.05.14 药品先汇总,合计金额不允许通过单价*数量计算,统一都改为取HJJE00字段值。 --2020.10.22 当审批药品数量<病人实际发生药品数量,要求审批过的数量按医保结算,未审批过的数量按照自费计算 if ls_SFYBXM='Y' and LS_WSYPSL>0 then ls_XMSL00 := ls_XMSL00 - LS_WSYPSL; ls_HJJE00_TEMP := ls_HJJE00 - round(ls_XMDJ00*LS_WSYPSL,2); 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,JX0000,GYTJ00,BKEH83,BKEH05,BKEH06,BAKXML,BKC507,BKC506, MXID00,YSYGBH,BKE181,BKE182) 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',decode(ls_CBXMMCSYYBXMM,'1',ls_YBXMMC,ls_CBXMMC),'M',decode(ls_CBXMMCSYYBXMM,'1',ls_YBXMMC,ls_CBXMMC)||'('||ls_XMMC00||')',ls_XMMC00)),1,50), ls_GG0000,ls_DW0000, ls_XMDJ00, LS_WSYPSL, round(ls_XMDJ00*LS_WSYPSL,2), ls_ZWXM00,ls_CBXMBH,ls_CZRQ00, --decode(ad_SFXYB0,'0',ls_YSZJHM,decode(ls_BKC007FSYSGJBM,'2',nvl(ls_YBYGDM,ls_YSZJHM),'3',nvl(ls_YBYGDM,ls_YSZJHM),ls_YSZJHM)), --是否启用发送医师国家编码 ls_BKC007, ls_GJBM00,ls_YPYL00,ls_YPTS00,ls_YPPL00, 0,ls_XMBH00,ls_FLAG00, '6', ls_FPXMBH,ls_JX0000,ls_GYTJ00,LS_BKEH83,LS_BKEH05,LS_BKEH06,LS_BAKXML,ls_BKC507,ls_BKC506, SQ_BM_ZYCFMX_TEMP00_MXID00.nextval,ls_KDYS00,LS_BKE181,LS_BKE182 ); else ls_HJJE00_TEMP := ls_HJJE00; end if; 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,JX0000,GYTJ00, BKEH83,BKEH05,BKEH06,BAKXML,BKC507, BKC506,MXID00,YSYGBH,BKE181,BKE182) 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, --decode(ad_SFXYB0,'0',ls_YSZJHM,decode(ls_BKC007FSYSGJBM,'2',nvl(ls_YBYGDM,ls_YSZJHM),'3',nvl(ls_YBYGDM,ls_YSZJHM),ls_YSZJHM)), --是否启用发送医师国家编码 ls_BKC007, ls_GJBM00, ls_YPYL00,ls_YPTS00,ls_YPPL00,0,ls_XMBH00, ls_FLAG00,'0', ls_FPXMBH,ls_JX0000,ls_GYTJ00, LS_BKEH83,LS_BKEH05,LS_BKEH06,LS_BAKXML,ls_BKC507,ls_BKC506, SQ_BM_ZYCFMX_TEMP00_MXID00.nextval,ls_KDYS00,LS_BKE181,LS_BKE182); 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, BKEH83,BKEH05,BKEH06,BAKXML,YSYGBH,BKE181,BKE182) 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),SFXMID,max(FLAG00),max(HCDBXM),max(FPXMBH), max(BKEH83),max(BKEH05),max(BKEH06),max(BAKXML),max(YSYGBH),max(BKE181),max(BKE182) from BM_ZYCFMX_TEMP00 where ID0000 = ls_ID0000 group by XMBH00,SFYB00,FPXMMC,XMGG00,XMDJ00,trim(XMMC00),CZRQ00,FPXMBH,SFXMID; --判断是否超过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, BKEH83,BKEH05,BKEH06,BAKXML,YSYGBH,BKE181,BKE182) 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),SFXMID,max(FLAG00),max(HCDBXM),max(FPXMBH), max(BKEH83),max(BKEH05),max(BKEH06),max(BAKXML),max(YSYGBH),max(BKE181),max(BKE182) from BM_ZYCFMX_TEMP00 where ID0000 = ls_ID0000 group by XMBH00,SFYB00,FPXMMC,XMGG00,XMDJ00,TRIM(XMMC00),FPXMBH,SFXMID; end if; else --if LS_FSYBYPFYSF = '1' and as_FLAG00 in ('2') then --2021.08.15 if LS_FSYBYPFYSF in ('1','2') then --进行汇总 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,CZRQ00, BKEH83,BKEH05,BKEH06,BAKXML,YSYGBH,BKE181, BKE182) 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),SFXMID,max(FLAG00),max(HCDBXM),nvl(decode(LS_FSYBYPFYSF,'1',CZRQ00,'2',decode(FLAG00,'0','',CZRQ00)),max(CZRQ00)) CZRQ00, max(BKEH83),max(BKEH05),max(BKEH06),max(BAKXML),max(YSYGBH),max(BKE181), max(BKE182) from BM_ZYCFMX_TEMP00 where ID0000 = ls_ID0000 group by XMBH00,SFYB00,FPXMMC,FPXMBH,XMGG00,XMDJ00,trim(XMMC00),CLDBBM,SFXMID, decode(LS_FSYBYPFYSF,'1',CZRQ00,'2',decode(FLAG00,'0','',CZRQ00)); 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,JX0000,GYTJ00, BKEH83,BKEH05,BKEH06,BAKXML,CZRQ00,YSYGBH, BKE181,BKE182) 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),SFXMID,max(FLAG00),max(HCDBXM),max(JX0000),max(GYTJ00), max(BKEH83),max(BKEH05),max(BKEH06),max(BAKXML),max(CZRQ00),max(YSYGBH), max(BKE181),max(BKE182) from BM_ZYCFMX_TEMP00 where ID0000 = ls_ID0000 group by XMBH00,SFYB00,FPXMMC,FPXMBH,XMGG00,XMDJ00,trim(XMMC00),CLDBBM,SFXMID; end if; end if; --汇总数据更新MXID00 update BM_ZYCFMX_TEMP00 set MXID00=SQ_BM_ZYCFMX_TEMP00_MXID00.nextval where ID0000=ad_ID0000 and MXID00 is null; --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 --2021.04.27 增加条件LS_ZGXJID=0。若有启用拆分功能,则数量不能做修改。 --2021.08.31 增加条件nvl(ls_CBYPMCSYYBYPMC,'0') = '0' if LS_YBXJQY='Y' and LS_ZGXJID=0 and nvl(ls_CBYPMCSYYBYPMC,'0') = '0' 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; --非单病种判断是否有耗材打包 if ls_GZHCID <= 0 and LS_FFBZBM is null and LS_DBZBM0 is null then select nvl(max(a.GZHCID),-1) into ls_GZHCID from VW_ZY_YBGZHC a,BM_GZHCMX b,BM_ZYCFMX_TEMP00 c where a.GZHCID=b.GZHCID and b.SFXMID=c.SFXMID and c.ID0000=ad_ID0000; 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 and (ls_FFBZBM is not null or LS_DBZBM0 is not null) 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 ); update BM_ZYCFMX_TEMP00 a set GZHCID=( select max(GZHCID) from BM_GZHCMX b where b.GZHCID in (select GZHCID from BM_GZHCBZ where DBZBM0=nvl(ls_FFBZBM,LS_DBZBM0)) 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 GZHCID from BM_GZHCBZ where DBZBM0=nvl(ls_FFBZBM,LS_DBZBM0)) and b.SFXMID=a.SFXMID ); --2021.03.24 更新耗材打包属性 update BM_ZYCFMX_TEMP00 a set HCDBSX=(select max(HCDBSX) from VW_ZY_DBZHC0 where NBBH00=a.GZHCID) where ID0000=ad_ID0000 and a.CLDBBM is not null and GZHCID>0; --2020.09.21 可以按指定的收费项目收费总数量来发送(BM_ZYCFMX_TEMP00.HCDBSX=2) update BM_ZYCFMX_TEMP00 a set HCDBSX='2' where ID0000=ad_ID0000 and a.CLDBBM is not null and GZHCID>0 and exists (select 1 from BM_GZHCMX where GZHCID=a.GZHCID and SFXMID=a.SFXMID and SFBZSL='1'); else --(ls_BZGJBM is not null) update BM_ZYCFMX_TEMP00 a set (GZHCID,HCDBSX)=(select max(NBBH00),max(HCDBSX) 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; --2020.08.31 1.按个打包(数量>1) for CUR in (select --a.*, a.ID0000,a.XMBH00,a.SFYB00,a.FPXMMC,a.XMMC00,a.XMGG00,a.XMDW00,a.XMDJ00,a.XMSL00,a.XMJE00, a.YSXM00,a.ZFXMBH,a.YSZJHM,a.CLDBBM,a.YPYL00,a.YPTS00,a.YPPL00,a.YBSXCE,a.SFXMID,a.FLAG00, a.HCDBXM,a.FPXMBH,a.HCDBSX, a.BKEH83,a.BKEH05,a.BKEH06,a.BAKXML,a.MXID00,a.GZHCID, b.BH0000,b.MC0000,b.BH0001,b.DJ0000,B.HCCBBM,B.HCCBMC, a.CZRQ00,a.YSYGBH,a.BKE181,a.BKE182 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 and (nvl(b.HCDBSX,'0')='1' and nvl(BH0001,' ')='个' --and XMSL00>1 ) ) loop --耗材超标编码不为空, 存在医保最高支付金额, 项目金额超过医保最高支付总金额(最高支付单价*项目数量) 2021.03.23 if CUR.HCCBBM is not null and nvl(CUR.DJ0000,0) > 0 and CUR.XMJE00 > CUR.DJ0000*CUR.XMSL00 then for i in 1..ceil(CUR.XMSL00) loop --CUR.YPYL00,CUR.YPTS00->1个,1 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,HCDBSX,BKEH83,BKEH05, BKEH06,BAKXML,MXID00,GZHCID,CZRQ00, YSYGBH,BKE181,BKE182) values(ad_ID0000,CUR.BH0000,'Y','其他费',CUR.MC0000, null,CUR.BH0001,CUR.DJ0000,1, CUR.DJ0000,CUR.YSXM00,CUR.ZFXMBH,CUR.YSZJHM,null,'1个', 1,CUR.YPPL00,0,CUR.SFXMID,CUR.FLAG00, '1',CUR.FPXMBH,'1',null,null, null,null,SQ_BM_ZYCFMX_TEMP00_MXID00.nextval,CUR.GZHCID,CUR.CZRQ00, CUR.YSYGBH,CUR.BKE181,CUR.BKE182); end loop; 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,HCDBSX,BKEH83,BKEH05, BKEH06,BAKXML,MXID00,GZHCID,CZRQ00, YSYGBH,BKE181,BKE182) values(ad_ID0000,CUR.HCCBBM,'N','其他费',CUR.HCCBMC, null,CUR.BH0001,CUR.XMJE00-CUR.DJ0000*CUR.XMSL00,1,CUR.XMJE00-CUR.DJ0000*CUR.XMSL00, CUR.YSXM00,CUR.ZFXMBH,CUR.YSZJHM,null,'1个', 1,CUR.YPPL00,0,CUR.SFXMID,CUR.FLAG00, '1',CUR.FPXMBH,'1',null,null, null,null,SQ_BM_ZYCFMX_TEMP00_MXID00.nextval,CUR.GZHCID,CUR.CZRQ00, CUR.YSYGBH,CUR.BKE181,CUR.BKE182); else for i in 1..ceil(CUR.XMSL00) loop --CUR.YPYL00,CUR.YPTS00->1个,1 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,HCDBSX,BKEH83,BKEH05, BKEH06,BAKXML,MXID00,GZHCID,CZRQ00, YSYGBH,BKE181,BKE182) values(ad_ID0000,CUR.BH0000,'Y','其他费',CUR.MC0000, null,CUR.BH0001,round(CUR.XMJE00/ceil(CUR.XMSL00),4),1,round(CUR.XMJE00/ceil(CUR.XMSL00),2), CUR.YSXM00,CUR.ZFXMBH,CUR.YSZJHM,null,'1个', 1,CUR.YPPL00,0,CUR.SFXMID,CUR.FLAG00, '1',CUR.FPXMBH,'1',null,null, null,null,SQ_BM_ZYCFMX_TEMP00_MXID00.nextval,CUR.GZHCID,CUR.CZRQ00, CUR.YSYGBH,CUR.BKE181,CUR.BKE182); end loop; end if; --拆成按个发送后, 原单项目数量和金额改成0 --update BM_ZYCFMX_TEMP00 set XMSL00=0,XMJE00=0 where ID0000=CUR.ID0000 and MXID00=CUR.MXID00; end loop; --2020.09.21 可以按指定的收费项目收费总数量来发送(BM_ZYCFMX_TEMP00.HCDBSX=2):XMSL00=1 -> greatest(sum(decode(nvl(a.HCDBSX,'0'),'2',a.XMSL00,0)),1) 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,HCDBSX,BKEH83,BKEH05, BKEH06,BAKXML,MXID00,GZHCID,CZRQ00, YSYGBH,BKE181,BKE182) select ad_ID0000,b.BH0000,'Y','其他费',b.MC0000, null,b.BH0001,round(sum(a.XMJE00)/greatest(sum(decode(nvl(a.HCDBSX,'0'),'2',a.XMSL00,0)),1),4), greatest(sum(decode(nvl(a.HCDBSX,'0'),'2',a.XMSL00,0)),1), sum(a.XMJE00), trim(max(a.YSXM00)),max(a.ZFXMBH),max(a.YSZJHM),null,max(a.YPYL00), sum(a.YPTS00),max(a.YPPL00),0,max(SFXMID),max(a.FLAG00), '1',max(a.FPXMBH),'0',max(a.BKEH83),max(a.BKEH05), max(a.BKEH06),max(a.BAKXML),max(a.MXID00),max(a.GZHCID),max(a.CZRQ00), max(a.YSYGBH),max(a.BKE181),max(a.BKE182) 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 --2020.08.31 2.剔除按个打包 and not (nvl(b.HCDBSX,'0')='1' and nvl(b.BH0001,' ')='个') group by b.BH0000,b.BH0001,b.MC0000; --汇总数据更新MXID00 update BM_ZYCFMX_TEMP00 set MXID00=SQ_BM_ZYCFMX_TEMP00_MXID00.nextval where ID0000=ad_ID0000 and MXID00 is null; --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' ); --2020.08.31 增加 YPYL00=substrb(XMSL00||XMDW00,1,10),YPTS00=to_char(XMSL00) update BM_ZYCFMX_TEMP00 a set XMDJ00=0,XMJE00=0,YPYL00=substrb(XMSL00||XMDW00,1,10),YPTS00=to_char(XMSL00) where ID0000=ad_ID0000 and a.CLDBBM is not null and exists (select 1 from VW_ZY_DBZHC0 b where a.GZHCID=b.NBBH00); --2021.03.24 处理高值耗材按套打包超医保最高支付金额 begin for cur in (select --a.*, a.ID0000,a.XMBH00,a.SFYB00,a.FPXMMC,a.XMMC00, a.XMGG00,a.XMDW00,a.XMDJ00,a.XMSL00,a.XMJE00, a.YSXM00,a.ZFXMBH,a.YSZJHM,a.CLDBBM,a.YPYL00, a.YPTS00,a.YPPL00,a.YBSXCE,a.SFXMID,a.FLAG00, a.HCDBXM,a.FPXMBH,a.HCDBSX,a.BKEH83,a.BKEH05, a.BKEH06,a.BAKXML,a.MXID00,a.GZHCID,b.BH0000, b.MC0000,b.BH0001,b.DJ0000,b.HCCBBM,b.HCCBMC, a.CZRQ00,a.YSYGBH,a.BKE181,a.BKE182 from BM_ZYCFMX_TEMP00 a,VW_ZY_DBZHC0 b where a.ID0000=ad_ID0000 and a.XMBH00=b.BH0000 and b.HCDBSX='0' ) loop if cur.HCCBBM is not null and nvl(cur.DJ0000,0) > 0 and cur.XMJE00 > nvl(cur.DJ0000,0) then update BM_ZYCFMX_TEMP00 set XMDJ00=cur.DJ0000,XMJE00=cur.DJ0000 where ID0000=cur.ID0000 and MXID00=cur.MXID00; 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,HCDBSX,BKEH83,BKEH05, BKEH06,BAKXML,MXID00,GZHCID,CZRQ00, YSYGBH,BKE181,BKE182) values(ad_ID0000,CUR.HCCBBM,'N','其他费',CUR.HCCBMC, null,CUR.BH0001,CUR.XMJE00-CUR.DJ0000,1,CUR.XMJE00-CUR.DJ0000, CUR.YSXM00,CUR.ZFXMBH,CUR.YSZJHM,null,'1个', 1,CUR.YPPL00,0,CUR.SFXMID,CUR.FLAG00, '1',CUR.FPXMBH,'0',null,null, null,null,SQ_BM_ZYCFMX_TEMP00_MXID00.nextval,CUR.GZHCID,CUR.CZRQ00, CUR.YSYGBH,CUR.BKE181,CUR.BKE182); end if; end loop; --2021.03.24 处理高值耗材按套打包超医保最高支付金额 end --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,MXID00,GZHCID,FPXMBH,BKEH83, BKEH05,BKEH06,BAKXML,CZRQ00,YSYGBH, BKE181,BKE182) 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',max(a.MXID00),-9999,max(a.FPXMBH),max(a.BKEH83), max(a.BKEH05),max(a.BKEH06),max(a.BAKXML),max(a.CZRQ00),max(a.YSYGBH), max(a.BKE181),max(a.BKE182) 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; --汇总数据更新MXID00 update BM_ZYCFMX_TEMP00 set MXID00=SQ_BM_ZYCFMX_TEMP00_MXID00.nextval where ID0000=ad_ID0000 and MXID00 is null; 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 = -9999 and rownum=1; if ls_COUNT0>0 then select XMJE00 into ls_HCYBJE from BM_ZYCFMX_TEMP00 where GZHCID = -9999; 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,BKEH83,BKEH05,BKEH06, BAKXML,MXID00,CZRQ00,YSYGBH,BKE181, BKE182) 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,null,null,null, null,SQ_BM_ZYCFMX_TEMP00_MXID00.nextval,CZRQ00,YSYGBH,BKE181, BKE182 from BM_ZYCFMX_TEMP00 where GZHCID = -9999; update BM_ZYCFMX_TEMP00 set XMDJ00 = ls_HCYBXJ, XMJE00 = ls_HCYBXJ where GZHCID = -9999; end if; update BM_ZYCFMX_TEMP00 set GZHCID = 0 where GZHCID = -9999; end if; elsif ls_GZHCID > 0 and ls_FFBZBM is null and ls_DBZBM0 is null then --2021.03.31医保非单病种, 泉州德诚模式 update BM_ZYCFMX_TEMP00 a set GZHCID=( select max(GZHCID) from BM_GZHCMX b where exists(select 1 from VW_ZY_YBGZHC c where c.GZHCID=b.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 exists(select 1 from VW_ZY_YBGZHC c where c.GZHCID=b.GZHCID) and b.SFXMID=a.SFXMID ); --2021.03.31 更新耗材打包属性 update BM_ZYCFMX_TEMP00 a set HCDBSX=(select max(HCDBSX) from VW_ZY_YBGZHC where GZHCID=a.GZHCID) where ID0000=ad_ID0000 and a.CLDBBM is not null and GZHCID>0; --可以按指定的收费项目收费总数量来发送(BM_ZYCFMX_TEMP00.HCDBSX=2) update BM_ZYCFMX_TEMP00 a set HCDBSX='2' where ID0000=ad_ID0000 and a.CLDBBM is not null and GZHCID>0 and exists (select 1 from BM_GZHCMX where GZHCID=a.GZHCID and SFXMID=a.SFXMID and SFBZSL='1'); --1.按个打包(数量>1) for CUR in (select A.ID0000,A.XMBH00,A.SFYB00,A.FPXMMC,A.XMMC00, A.XMGG00,A.XMDW00,A.XMDJ00,A.XMSL00,A.XMJE00, A.YSXM00,A.ZFXMBH,A.YSZJHM,A.CLDBBM,A.YPYL00, A.YPTS00,A.YPPL00,A.YBSXCE,A.SFXMID,A.FLAG00, A.HCDBXM,A.FPXMBH,A.HCDBSX,A.BKEH83,A.BKEH05, A.BKEH06,A.BAKXML,A.MXID00,A.GZHCID,b.HCDBBM, b.HCDBMC,b.DW0000,b.YBZGZF,b.HCCBBM,b.HCCBMC, a.CZRQ00,a.YSYGBH,a.BKE181,a.BKE182 from BM_ZYCFMX_TEMP00 a,VW_ZY_YBGZHC b where a.ID0000=ad_ID0000 and a.CLDBBM is not null and a.GZHCID=b.GZHCID and (nvl(b.HCDBSX,'0')='1' and nvl(B.DW0000,' ')='个' ) ) loop --耗材超标编码不为空, 存在医保最高支付金额, 项目金额超过医保最高支付总金额(最高支付单价*项目数量) 2021.03.23 if CUR.HCCBBM is not null and nvl(CUR.YBZGZF,0) > 0 and CUR.XMJE00 > CUR.YBZGZF*CUR.XMSL00 then for i in 1..ceil(CUR.XMSL00) loop --CUR.YPYL00,CUR.YPTS00->1个,1 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,HCDBSX,BKEH83,BKEH05, BKEH06,BAKXML,MXID00,GZHCID,CZRQ00, YSYGBH,BKE181,BKE182) values(ad_ID0000,CUR.HCDBBM,'Y','其他费',CUR.HCDBMC, null,CUR.DW0000,CUR.YBZGZF,1,CUR.YBZGZF, CUR.YSXM00,CUR.ZFXMBH,CUR.YSZJHM,null,'1个', 1,CUR.YPPL00,0,CUR.SFXMID,CUR.FLAG00, '1',CUR.FPXMBH,'1',null,null, null,null,SQ_BM_ZYCFMX_TEMP00_MXID00.nextval,CUR.GZHCID,CUR.CZRQ00, CUR.YSYGBH,CUR.BKE181,CUR.BKE182); end loop; 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,HCDBSX,BKEH83,BKEH05, BKEH06,BAKXML,MXID00,GZHCID,CZRQ00, YSYGBH,BKE181,BKE182) values(ad_ID0000,CUR.HCCBBM,'N','其他费',CUR.HCCBMC, null,CUR.DW0000,CUR.XMJE00-CUR.YBZGZF*CUR.XMSL00,1,CUR.XMJE00-CUR.YBZGZF*CUR.XMSL00, CUR.YSXM00,CUR.ZFXMBH,CUR.YSZJHM,null,'1个', 1,CUR.YPPL00,0,CUR.SFXMID,CUR.FLAG00, '1',CUR.FPXMBH,'1',null,null, null,null,SQ_BM_ZYCFMX_TEMP00_MXID00.nextval,CUR.GZHCID,CUR.CZRQ00, CUR.YSYGBH,CUR.BKE181,CUR.BKE182); else for i in 1..ceil(CUR.XMSL00) loop --CUR.YPYL00,CUR.YPTS00->1个,1 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,HCDBSX,BKEH83,BKEH05, BKEH06,BAKXML,MXID00,GZHCID,CZRQ00, YSYGBH,BKE181,BKE182) values(ad_ID0000,CUR.HCDBBM,'Y','其他费',CUR.HCDBMC, null,CUR.DW0000,round(CUR.XMJE00/ceil(CUR.XMSL00),4),1,round(CUR.XMJE00/ceil(CUR.XMSL00),2), CUR.YSXM00,CUR.ZFXMBH,CUR.YSZJHM,null,'1个', 1,CUR.YPPL00,0,CUR.SFXMID,CUR.FLAG00, '1',CUR.FPXMBH,'1',null,null, null,null,SQ_BM_ZYCFMX_TEMP00_MXID00.nextval,CUR.GZHCID,CUR.CZRQ00, CUR.YSYGBH,CUR.BKE181,CUR.BKE182); end loop; end if; --拆成按个发送后, 原单项目数量和金额改成0 --update BM_ZYCFMX_TEMP00 set XMSL00=0,XMJE00=0 where ID0000=CUR.ID0000 and MXID00=CUR.MXID00; end loop; --2020.09.21 可以按指定的收费项目收费总数量来发送(BM_ZYCFMX_TEMP00.HCDBSX=2):XMSL00=1 -> greatest(sum(decode(nvl(a.HCDBSX,'0'),'2',a.XMSL00,0)),1) 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,HCDBSX,BKEH83,BKEH05, BKEH06,BAKXML,GZHCID,CZRQ00,YSYGBH, BKE181,BKE182) select ad_ID0000,b.HCDBBM,'Y','其他费',b.HCDBMC, null,b.DW0000,round(sum(a.XMJE00)/greatest(sum(decode(nvl(a.HCDBSX,'0'),'2',a.XMSL00,0)),1),4),greatest(sum(decode(nvl(a.HCDBSX,'0'),'2',a.XMSL00,0)),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(a.FPXMBH),'0',max(a.BKEH83),max(a.BKEH05), max(a.BKEH06),max(a.BAKXML),max(a.GZHCID),max(a.CZRQ00),max(YSYGBH), max(BKE181),max(BKE182) from BM_ZYCFMX_TEMP00 a,VW_ZY_YBGZHC b where a.ID0000=ad_ID0000 and a.CLDBBM is not null and a.GZHCID=b.GZHCID --2020.08.31 2.剔除按个打包 and not (nvl(b.HCDBSX,'0')='1' and nvl(b.DW0000,' ')='个') group by b.HCDBBM,b.DW0000,b.HCDBMC; --汇总数据更新MXID00 update BM_ZYCFMX_TEMP00 set MXID00=SQ_BM_ZYCFMX_TEMP00_MXID00.nextval where ID0000=ad_ID0000 and MXID00 is null; --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_YBGZHC b where a.GZHCID=b.GZHCID 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_YBGZHC b where a.GZHCID=b.GZHCID and nvl(SFTXYL,'0')='1' ); --2020.08.31 增加 YPYL00=substrb(XMSL00||XMDW00,1,10),YPTS00=to_char(XMSL00) update BM_ZYCFMX_TEMP00 a set XMDJ00=0,XMJE00=0,YPYL00=substrb(XMSL00||XMDW00,1,10),YPTS00=to_char(XMSL00) where ID0000=ad_ID0000 and a.CLDBBM is not null and exists (select 1 from VW_ZY_YBGZHC b where a.GZHCID=b.GZHCID); --2021.03.31 处理高值耗材按套打包超医保最高支付金额 begin for cur in (select A.ID0000,A.XMBH00,A.SFYB00,A.FPXMMC,A.XMMC00, A.XMGG00,A.XMDW00,A.XMDJ00,A.XMSL00,A.XMJE00, A.YSXM00,A.ZFXMBH,A.YSZJHM,A.CLDBBM,A.YPYL00, A.YPTS00,A.YPPL00,A.YBSXCE,A.SFXMID,A.FLAG00, A.HCDBXM,A.FPXMBH,A.HCDBSX,A.BKEH83,A.BKEH05, A.BKEH06,A.BAKXML,A.MXID00,A.GZHCID,b.HCDBBM, b.HCDBMC,b.DW0000,b.YBZGZF,b.HCCBBM,b.HCCBMC, a.CZRQ00,a.YSYGBH,a.BKE181,a.BKE182 from BM_ZYCFMX_TEMP00 a,VW_ZY_YBGZHC b where a.ID0000=ad_ID0000 and a.XMBH00=b.HCDBBM and b.HCDBSX='0' ) loop if cur.HCCBBM is not null and nvl(cur.YBZGZF,0) > 0 and cur.XMJE00 > nvl(cur.YBZGZF,0) then update BM_ZYCFMX_TEMP00 set XMDJ00=cur.YBZGZF,XMJE00=cur.YBZGZF where ID0000=cur.ID0000 and MXID00=cur.MXID00; 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,HCDBSX,BKEH83,BKEH05, BKEH06,BAKXML,MXID00,GZHCID,CZRQ00, YSYGBH,BKE181,BKE182) values(ad_ID0000,CUR.HCCBBM,'N','其他费',CUR.HCCBMC, null,CUR.DW0000,CUR.XMJE00-CUR.YBZGZF,1,CUR.XMJE00-CUR.YBZGZF, CUR.YSXM00,CUR.ZFXMBH,CUR.YSZJHM,null,'1个', 1,CUR.YPPL00,0,CUR.SFXMID,CUR.FLAG00, '1',CUR.FPXMBH,'0',null,null, null,null,SQ_BM_ZYCFMX_TEMP00_MXID00.nextval,CUR.GZHCID,CUR.CZRQ00, CUR.YSYGBH,CUR.BKE181,CUR.BKE182); end if; end loop; --2021.03.31 处理高值耗材按套打包超医保最高支付金额 end 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 修正超过最高限价拆分 --2021.08.31 qks 增加条件or nvl(ls_CBYPMCSYYBYPMC,'0') = '1 if (LS_ZGXJCF='Y') and (LS_ZGXJID>0 or nvl(ls_CBYPMCSYYBYPMC,'0') in ('1','2','3')) then --LS_FPXMBH := null; --if LS_FPXMBH is null then if nvl(ls_CBYPMCSYYBYPMC,'0') = '0' 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,C.BKEH83,substrb(d.YPGG00,1,10) into LS_FPXMMC,LS_FPXMBH,LS_SFYBXM,LS_YBXMSX,LS_YBXMMC,LS_YBXMBH,LS_CBXMBH,LS_YBZFBL,LS_YBXMLB, LS_BZCBBH,LS_BZCBMC,LS_YBDDW0,LS_YBXDW0,LS_BKEH83,ls_YBXMGG 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; 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; --医保小单位最高限价 LS_BKEH83:=null;--国家标准对应编码 LS_BKEH05:=null;--医保备用1 LS_BKEH06:=null;--医保备用2 LS_BAKXML:=null;--HIS备用字段(xml格式)后续扩展 begin select D.XMDW00,D.ZXJJDW,nvl(D.YBZGDJ,0) YBZGDJ,nvl(D.YBZGDJ1,0) YBZGDJ1,C.BKEH83 into LS_YBDDW0,LS_YBXDW0,LS_YBZGDJ,LS_YBZGDJ1,LS_BKEH83 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 --如果合计金额>最高限价金额 拆分数据 --修改单价为最高限价 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); --药品超过最高限价金额 --2021.09.06 继续超出限价 if LS_XJCEJE>LS_ZGXJDJ then LS_XJCFSL := ceil(LS_XJCEJE/LS_ZGXJDJ); else LS_XJCFSL:=1; end if; LS_XJCEDJ:=round(nvl(LS_XJCEJE,0)/nvl(LS_XJCFSL,0),4); --药品超过最高限价单价 if nvl(ls_CBYPMCSYYBYPMC,'0') in ('1','2','3') --只有700打头的才发原编码 and LS_YBXMBH in ('700000000001','700000000002','700000000003','700000000004') then --SFYB00='N' 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,JX0000, GYTJ00,BKEH83,BKEH05,BKEH06,BAKXML, MXID00,YSYGBH,BKE181,BKE182) values(CUR.ID0000,CUR.XMBH00,'N',CUR.FPXMMC,CUR.XMMC00, CUR.XMGG00,CUR.XMDW00,LS_XJCEDJ,LS_XJCFSL,LS_XJCEJE, CUR.YSXM00,CUR.ZFXMBH,CUR.CZRQ00,CUR.YPTS00,CUR.YPPL00, CUR.YPYL00,0,'0','0','0', CUR.SFXMID,'2',CUR.YSZJHM,CUR.FPXMBH,CUR.JX0000, CUR.GYTJ00,CUR.BKEH83,CUR.BKEH05,CUR.BKEH06,CUR.BAKXML, SQ_BM_ZYCFMX_TEMP00_MXID00.nextval,CUR.YSYGBH,CUR.BKE181,CUR.BKE182); else 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,JX0000, GYTJ00,BKEH83,BKEH05,BKEH06,BAKXML, MXID00,YSYGBH,BKE181,BKE182) --2021.09.02 LS_SFYBXM->'N' values(CUR.ID0000,LS_YBXMBH,'N',decode(LS_SYFPBH,'Y',LS_FPXMBH,LS_FPXMMC), --LS_YBXMMC, substr(decode(ls_YBLB00,'Z',CUR.XMMC00,decode(ls_YBMCFS,'Y',ls_YBXMMC,'M',ls_YBXMMC||'('||CUR.XMMC00||')',CUR.XMMC00)),1,50), decode(ls_ybmcfs,'Y',ls_YBXMGG,CUR.XMGG00),nvl(LS_YBDDW0,'无'),LS_XJCEDJ,LS_XJCFSL,LS_XJCEJE, CUR.YSXM00,nvl(CUR.ZFXMBH,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,CUR.JX0000, CUR.GYTJ00,CUR.BKEH83,CUR.BKEH05,CUR.BKEH06,CUR.BAKXML, SQ_BM_ZYCFMX_TEMP00_MXID00.nextval,CUR.YSYGBH,CUR.BKE181,CUR.BKE182); end if; 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; --新医保接口明细上传bkc007节点是否开启发送医师国家编码(=BM_YGBM00.YBYGDM),=0原来模式[0:否,1:门诊,2:住院,3:门诊住院] 默认0 if nvl(ad_SFXYB0,'0') = '1' and ls_BKC007FSYSGJBM in ('2','3') then--是否新医保 0:否 1:是 update BM_ZYCFMX_TEMP00 set (YSXM00,YSZJHM)=(select substrb(A.ZWXM00,1,30),nvl(substrb(A.YBYGDM,1,30),A.SFZBH0) from BM_YGBM00 A where A.YGBH00=YSYGBH and rownum=1) where ID0000 = ad_ID0000 and YSYGBH is not null ; end if; --新医保接口bkeh83(国家编码C码)是否转化为bke045(医保结算编码)第一位:0:不开启 1:门诊2:住院3:门诊和住院,第二位(1:项目2:药品3:项目和药品)默认值:00 if nvl(ad_SFXYB0,'0') = '1' and substrb(LS_Change_bkeh83_bke045,1,1) in ('2','3') then --新医保接口处理 if substrb(LS_Change_bkeh83_bke045,2,1) in ('1') then --1:项目2:药品3:项目和药品 update BM_ZYCFMX_TEMP00 aa set aa.XMBH00=substrb(nvl(trim(aa.BKEH83),trim(aa.XMBH00)),1,50) where aa.ID0000 = ad_ID0000 and aa.FLAG00 in ('0') and aa.FLAG00 is not null; elsif substrb(LS_Change_bkeh83_bke045,2,1) in ('2') then --1:项目2:药品3:项目和药品 update BM_ZYCFMX_TEMP00 aa set aa.XMBH00=substrb(nvl(trim(aa.BKEH83),trim(aa.XMBH00)),1,50) where aa.ID0000 = ad_ID0000 and aa.FLAG00 not in ('0') and aa.FLAG00 is not null; elsif substrb(LS_Change_bkeh83_bke045,2,1) in ('3') then --1:项目2:药品3:项目和药品 update BM_ZYCFMX_TEMP00 aa set aa.XMBH00=substrb(nvl(trim(aa.BKEH83),trim(aa.XMBH00)),1,50) where aa.ID0000 = ad_ID0000 and aa.FLAG00 is not null; end if; end if; --新医保接口bke182(院内项目名称)是否转化为bke046(医保项目名称)第一位:0:不开启 1:门诊2:住院3:门诊和住院,第二位(1:项目2:药品3:项目和药品)默认值:00 if nvl(ad_SFXYB0,'0') = '1' and substrb(LS_Change_bke182_bke046,1,1) in ('2','3') then --新医保接口处理 if substrb(LS_Change_bke182_bke046,2,1) in ('1') then --1:项目2:药品3:项目和药品 update BM_ZYCFMX_TEMP00 aa set aa.XMMC00=substrb(nvl(trim(aa.bke182),trim(aa.XMMC00)),1,100) where aa.ID0000 = ad_ID0000 and aa.FLAG00 in ('0') and aa.FLAG00 is not null; elsif substrb(LS_Change_bke182_bke046,2,1) in ('2') then --1:项目2:药品3:项目和药品 update BM_ZYCFMX_TEMP00 aa set aa.XMMC00=substrb(nvl(trim(aa.bke182),trim(aa.XMMC00)),1,100) where aa.ID0000 = ad_ID0000 and aa.FLAG00 not in ('0') and aa.FLAG00 is not null; elsif substrb(LS_Change_bke182_bke046,2,1) in ('3') then --1:项目2:药品3:项目和药品 update BM_ZYCFMX_TEMP00 aa set aa.XMMC00=substrb(nvl(trim(aa.bke182),trim(aa.XMMC00)),1,100) where aa.ID0000 = ad_ID0000 and aa.FLAG00 is not null; end if; end if; --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,JXMC00,GYTJ00,BKEH83,BKEH05, BKEH06,BAKXML,BKC507,BKC506,YSYGBH,BKE181, BKE182) select --decode(LS_FSYBYPFYSF,'1',CZRQ00,'2',CZRQ00,to_char(sysdate,'YYYYMMDD')) CZRQ00, ID0000,nvl(CZRQ00,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,JX0000,GYTJ00,BKEH83,BKEH05, BKEH06,BAKXML,BKC507,BKC506,YSYGBH,BKE181, BKE182 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; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%