prompt 26、门诊持卡病人的所有未结算的费用结算并打印发票 SP_SF_BRFY00_JS0000 create or replace procedure SP_SF_BRFY00_JS0000 --说明:若as_GHH000='0',即将病人所有的费用结算,若不为'0',只将对应该挂号号的费用进行结算 ( as_YBDJH0 in char, --医保单据流水号 as_KSRQ00 in char, --开始日期 as_JSRQ00 in char, --结束日期 as_GHH000 in Char, --病人挂号号 as_ICKH00 in char, --就诊卡号 ad_BRID00 in number, --病人ID ad_JFLBID in number, --病人交费类别ID as_XM0000 in char, --病人姓名 as_XB0000 in char , --病人性别 ad_BRFB00 in number, --病人费别 ad_BRZJE0 in number, --病人费用总金额 ad_JZJE00 in number, --记账金额 ad_GFJE00 in number, --公费金额 ad_HJJE00 in number, --合计应收金额=自付金额-减免金额 ad_JMJE00 in number, --减免金额 as_ZPZH00 In char, --病人所属医保中心的编号 ad_GRZHZF in number, --个人医疗账户支付(医保返回) ad_TCJJZF in number, --统筹基金支付(医保返回) ad_ZFJE00 in number, --自付金额(医保返回) ad_JZY000 in number, --结账操作员编码 as_JZYXM0 in char, --结账操作员姓名 ad_JZYKS0 in number, --结账操作员所在科室 as_JMYY00 In varchar2, --减免原因 as_YJJXH0 in char, --打印在发票上的外部序号 as_CZBZ00 In Char, --'0':结算留账留卡,'1':结算清账退卡,'2':结算清账 as_SFYB00 in char, --是否医保病人,'Y':医保 AS_TSBZBH in char default '0', --特殊病人病种 AS_BZJSLX in number default 2, --病种结算类型 0:全部费用 1：本病种+挂号费+诊查费 2：本病种+非病种全部费用 3:结算本病种费用 4:只结算非特殊病种费用 as_SFDYFP in char default 'Y', --是否打印发票，N为否，Y为是 ad_XJYE00 out number, --病人对应账户的现金余额 ad_ZZYE00 out number, --病人对应账户的转账余额 ad_JZDH00 out number, --病人结账单号 ad_ZFDJH0 out number, --病人退费记录对应的单据号 ad_GRDJH0 out number, --医保个人账户对应的单据号 ad_TJDJH0 out number, --医保统筹基金对应的单据号 ad_SBZHDH out number, --商保个人帐户单据流水号 ad_SBTJDH out number, --商保统筹基金单据流水号 ad_JJDJH0 out number, --保健基金单据号 ad_SYDJH0 out number, --商业保险支付单据号 ad_SBDJH0 out number, --商保基金支付单据号(省属三家市属九家医疗机构) ad_BJDJH0 out number, --保健基金支付单据号(省属三家市属九家医疗机构) ad_GWYDJH out number, --公务员补助单据号 as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar, --系统提示的错误信息 ad_hjje01 out number , --合计金额 ad_MSTID0 in char default '' , --健康通(民生通ID) ad_MSTYE0 in char default '' , --健康通(民生通)余额 as_WBPJLY in varchar2 default '' , --外部票据来源 02 博思发票 as_FPDYBZ in varchar2 default '1' ,--发票打印标志 0:结算单 1:正式发票 2:非正式发票 3:发票证明 as_YBTYPE in varchar2 default '' ,--医保接口标志 -1:自费 0:福建版本 1:国家基线版 as_INTXML in varchar2 default '' ,--备用xml as_SFDZPJ in varchar2 default '0',--是否电子票据 0:否 1:是 as_ID0000 in varchar2 default '' ,--中间表ID=SF_FYXX00_DJH000.ID0000 as_FKLSH0 in varchar2 default '' ,--分开结算流水号 as_FKJSLX in varchar2 default '' ,--分开结算类型 FYCF_ZF:自费处方 FYCF_ZC:正常处方 ad_FKJZDH in number default 0 ,--分开结算单号 ad_FKJZJE in number default 0 --分开结算金额 ) -- MODIFICATION HISTORY -- Person Date Comments -- xzw 2007.06.15 传递参数增加基金单据号; -- xzw 2007.07.06 对此参数SF_SFKYQZTK进行保护处理; -- jlg 2007.08.03 增加传出参数商业保险支付单据号ad_SYDJH0 MZSF-20070801-001 -- xzw 2008.04.28 增加商保基金支付金额和保健基金支付金额的处理MZSF-20080401-004 -- xzw 2009.05.06 公费病人清帐退卡的时候是否根据BM_YBSFDY.ZFBL00来处理（增加参数SF_GFBRKYJSFAZFBLLHQ） -- xzw 2009.08.04 卡的费用要不要结算（参数SF_ICKSFCYJS） -- xzw 2009.09.14 清帐退卡发票上的总金额要是清帐退卡后的金额，跟清单保持一致（参照3.0） -- qks 2009.11.26 修正SF_JZB000表JMJE00字段值 -- xzw 2009.12.18 清帐退卡的话，如果病人在院，则不允许清帐退卡 -- xzw 2010.03.03 增加传入参数ad_GWYDJH --zhangyc 2010.09.02 增加参数SF_ZZJESFYXJTK='Y'处理(转账余额小于100，将其金额用现金退还) --zhangyc 2010.10.15 增加限制某费别的病人不能清账退卡MZSF-20101013-001 --zhangyc 2010.12.06 注释掉MZSF-20101013-001该需求的限制，由权限模块限制 -- csf 2011.05.06 回复功能 2010.10.15 增加限制某费别的病人不能清账退卡MZSF-20101013-001 -- jinfl 2011.12.13 当只打印结算单的时候，sf_pjsyqk这张表的数据不产生 MZSF-20111115-001 -- jinfl 2012.01.12 如果选择打印结算单，则把结算单写入sf_pjsyqk中 MZSF-20120111-003 -- qks 2012.03.23 参数as_tsbzbh值:当前2个字母为“t,” 兼结算无特殊病种费用;当前2个字母为“w,”,只结算无特殊病种费用 MZSF-20120322-002 --顺便修改MZSF-20120222-001,把挂号费和诊察费附加进来。 --zhangyc 2012.05.30 增加参数as_bzjslx 0:全部费用 1：本病种+挂号费+诊查费 2：本病种+非病种全部费用 3:结算本病种费用 4:只结算非特殊病种费用 --zhangyc 2012.06.21 修正AS_BZJSLX=2 写法 by MZSF-20120620-001 --zhangc 2012.07.17 修改算法 by MZSF-20120717-002 --zhangyc 2014.09.16 修改插入结账表的mzid00取 根据结账单号从SF_BRFY00取最大的MZID00 by MZSF-20140911-001 --zhangyc 2014.11.10 修正语句插入SF_PJSYQK操作员0 by MZSF-20140918-004 --zhangyc 2015.06.29 增加血透析病种支付功能 by MZSF-20150630-001 --zhangyc 2015.07.22 增加健康通(民生通)支付功能 by MZSF-20150722-001 --zhangyc 2015.08.13 健康通改造 by MZSF-20150811-001 --zhangyc 2015.11.06 增加参数SF_SWFBQFJESZ by MZSF-20150910-002 --zhangyc 2016.01.19 单病种修改 by MZSF-20160115-001 -- qks 2016.05.28 增加参数SF_XZFBTFJS,限制某类费别病人不允许退预交金以及结算 by MZSF-20160512-001 --yanghq 2018.02.05 泉州市医科大学附属第二医院的体检病人不让结算 by MZSF-20180125-001 --zhangyc 2018.05.10 修正精准扶贫 1:是 0:否 by MZSF-20180426-006 -- dsm 2019.05.08 结算后自动执行for MZSF-20190426-002 --dsm 2019.07.25 增加调用SP_SF_YJYWZX（SP_SF_BRFY00_JS0000中的调用去掉） for BQ-20190726-001 --zhangyc 2019.12.21 增加:JSQYE0:结算前账户余额 、JSHYE0:结算后账户余额 by MZSF-20191219-003 --zhangyc 2020.02.04 修正bug:当参数SF_MZICKYJDJFS=Y 时，结算清账、退卡时触发器TR_SF_SF_BRJFB0_INS_CHECK异常报错 by MZSF-20200120-001 --zhangyc 2020.02.24 SF_MZICKYJDJFS=Y注释掉as_CZBZ00=1 处理 by MZSF-20200220-002 --linbin 2020.05.26 自费病人已收费项目自动执行, 调用SP_SF_YJYWZX by MZSF-20200508-004 --qiulf 2020.06.18 增加入参ad_WBPJLY外部票据类型 by MZSF-20200618-005 --zhangyc 2020.07.14 结账表增加直接插入YBZXLB、YBLB00、FBBH00、YBBRLB by MZSF-20200705-001 --linbin 2020.08.23 当参数SF_XZZYBZLZBNYTSBZJS='Y'时限制中医辩证论治不以特殊病种结算 by MZSF-20200821-002 --qiulf 2020.12.01 增加as_FPDYBZ by MZSF-20201023-001 --zhangyc 2020.12.10 SF_JZB000保存一些新的字段(FKJSLX,FKLSH0,FKJZDH,FKJZJE,YBBXZE,DLTCJJ,DLZHZF,YBQTZE,DLSYBX,DLJZFP,DLQTJJ,DLQYBC,MZBZJE,DLSBJE,GWBZJE,DLDBJJ,CJBTCE ) by MZSF-20201210-003 --qiulf 2020.12.15 结算单WBPJLY字段也要赋值 by MZSF-20201211-002 --zhangyc 2021.01.07 修改IC_YHXX00.CARDNO真实卡号 by MZSF-20210107-001 --zhangyc 2021.01.14 参数LS_TCJJQZFS=D时bkc062(其中:精准扶贫医疗叠加)、ake173（其中:其他基金支付）ake026（其中:企业补充）根据函数SF_SF_YBFHJE判断累加到统筹基金中 by MZSF-20201229-002 --zhangyc 2021.10.02 增加参数控制SF_YBFYJS_MZH000:门诊收费医保病人结算是否结算SF_BRFY00.MZH000=0的数据 1:参与结算 0:不参与结算 默认值:1 by MZSF-20211003-001 --zhangyc 2022.02.08 增加入参(as_YBTYPE,as_INTXML)SF_JZB000增加写入其它国家医保字段(ybtype:医保接口类型[-1:自费 0:福建版本 1:国家基线版]) by MZSF-20220208-007 --zhangyc 2023.04.14 优化自费病人结算时，结账表合计金额【3671.45】<>费用表合计金额【3686.45】,原因是费用表mzid00=0的ICK费【3*5=15】数据一起更新导致 by MZSF-20230417-001 --zhangyc 2023.07.12 增加入参:as_SFDZPJ、as_ID0000、as_FKLSH0、as_FKJSLX、ad_FKJZDH、ad_FKJZJE,并且根据as_ID0000查下表SF_FYXX00_DJH000作为更新条件 by MZSF-20230713-002 --linzetao 2024.07.11 增加调用追溯码销售退货处理过程(SP_SF_YPZSM0_XSTHCL) MZSF-20240711-002 --chenHeyi 2025.10.10 扩展参数SF_ZZJESFYXJTK MZSF-20241219-001 --zhangyc 2025.08.22 优化医保病人转自费结算无法调用过程：SP_SF_YJYWZX as LS_ERRMSG varchar2(100); --错误信息 ls_ICKJE0 IC_YHXX00.ICKJE0%TYPE; --就诊卡售卡金额 ls_DJH000 SF_BRFY00.DJH000%TYPE; --病人单据号 ls_PJH000 SF_PJSYQK.PJH000%TYPE; --票据流水号 ls_MZID00 SF_BRFY00.MZID00%TYPE; --门诊挂号信息表ID ls_MZID01 SF_BRFY00.MZID00%TYPE; --门诊ID ls_MZFPID BM_YYSFXM.MZFPID%TYPE; --门诊发票ID ls_SFXMID BM_YYSFXM.SFXMID%TYPE; --收费项目ID ls_ICKNBH IC_YHXX00.ICKNBH%TYPE; --IC卡内码 ls_TMPPJH SF_PJSYQK.PJH000%TYPE; ls_FBBH00 BM_BRXXB0.FBBH00%TYPE; ls_ZFBL00 SF_JZMX00.ZFBL00%TYPE; --自付比例 ls_CZRQ00 CHAR(8); ls_CZSJ00 CHAR(8); ls_COUNT0 NUMBER(5); ls_JZMX00 SF_JZMX00%ROWTYPE; LS_SFCFFP CHAR(2); LS_SFQZTK CHAR(2); ls_ICKRQ0 CHAR(8); ls_GFKJE0 CHAR(1); --参数SF_GFBRKYJSFAZFBLLHQ的值(特约单位病人)) lv_ZFJE00 SF_FYMX00.ZFJE00%TYPE; lv_JZJE00 SF_FYMX00.JZJE00%TYPE; ls_YBZXLB IC_YBBRLB.YBZXLB%TYPE; ls_YBLB00 IC_YBBRLB.YBLB00%TYPE; LS_YBBRLB BM_BRXXB0.YBBRLB%TYPE; ls_ICKSFCYJS CHAR(1); ls_BRZYZTSFKYTK CHAR(1); ls_SF_ZZJESFYXJTK XT_XTCS00.VALUE0%type; --扩展参数SF_ZZJESFYXJTK MZSF-20250103-001 ls_SF_ZZJESFYXJTK_SFQY XT_XTCS00.VALUE0%type; ls_SF_ZZJESFYXJTK_XZJE SF_BRZHXX.ZZYE00%type; ls_FBBH01 BM_BRXXB0.FBBH00%TYPE; ls_XZBRFB varchar2(50); ls_LSFBBH varchar2(50); I NUMBER(3,0); J NUMBER(3,0); LS_MSTID0 varchar2(30); LS_MSTZF0 number(12,2); LS_MSTYE0 number(12,2); SF_SWFBQFJESZ varchar2(100); VFBBH00_Temp00 number(5); VYXQFJE_Temp00 number(12,2); LS_JSQYE0 SF_JZB000.JSQYE0%type;--结算前账户余额 LS_JSHYE0 SF_JZB000.JSQYE0%type;--结算后账户余额 SF_MZICKYJDJFS varchar2(100);--门诊ICK押金是否以帐户冻结方式出现，默认为N ls_TYJE00 SF_BRZHXX.TYJE00%type; LS_ZHTYJE SF_BRZHXX.TYJE00%type; ls_XZZYBZLZBNYTSBZJS XT_XTCS00.VALUE0%type; LS_CARDNO IC_YHXX00.CARDNO%type;--真实卡号 LS_ICKH00 IC_YHXX00.ICKH00%type;--IC卡号 LS_KMH000 IC_YHXX00.KMH000%type;--卡面号 LS_YBKH00 BM_BRXXB0.YBKH00%type;--医保卡号 LS_YBID00 BM_BRXXB0.YBID00%type;--医保ID LS_FKJSLX SF_JZB000.FKJSLX%type;--分开结算类型 FYCF_ZF:自费处方 FYCF_ZC:正常处方 ZFJS:自费结算 YBJS:医保结算 CXJS:重新结算 LS_FKLSH0 SF_JZB000.FKLSH0%type;--分开结算流水号 LS_FKJZDH SF_JZB000.FKJZDH%type;--分开结算单号 LS_FKJZJE SF_JZB000.FKJZJE%type;--分开结算金额 LS_YBBXZE SF_JZB000.YBBXZE%type;--医保报销总额(DLTCJJ+DLZHZF+YBQTZE+DLSYBX+DLJZFP+DLQTJJ+DLQYBC+MZBZJE+DLSBJE+GWBZJE+DLDBJJ) LS_DLTCJJ SF_JZB000.DLTCJJ%type;--实际医保统筹基金 LS_DLZHZF SF_JZB000.DLZHZF%type;--实际医保账户支付 LS_YBQTZE SF_JZB000.YBQTZE%type;--医保其它总额 LS_DLSYBX SF_JZB000.DLSYBX%type;--商业保险支付(独立于统筹基金金额) LS_DLJZFP SF_JZB000.DLJZFP%type;--精准扶贫医疗(独立于统筹基金金额) LS_DLQTJJ SF_JZB000.DLQTJJ%type;--其他基金支付(独立于统筹基金金额) LS_DLQYBC SF_JZB000.DLQYBC%type;--企业补充(独立于统筹基金金额) LS_DLMZBZ SF_JZB000.MZBZJE%type;--民政补助金额(独立于统筹基金金额) LS_DLSBJE SF_JZB000.DLSBJE%type;--商保支付金额(独立于统筹基金金额) LS_GWBZJE SF_JZB000.GWBZJE%type;--公务员补助金额(独立于统筹基金金额) LS_DLDBJJ SF_JZB000.DLDBJJ%type;--大病基金支付金额(独立于统筹基金金额) LS_CJBTCE SF_JZB000.CJBTCE%type;--产检检查费（bkc064）超出个人自付金额后需补退的差额 LS_bkc620 SF_JZB000.bkc620%type;--(本地)成员共济账户支付[bkc620] LS_bkc619 SF_JZB000.bkc619%type;--(异地)成员共济账户支付[bkc619] LS_bke284 SF_JZB000.bke284%type;--身份认证类型(01:读卡02:融合码06:医保码20:医疗证) LS_bke921 SF_JZB000.bke921%type;--(家庭健康)共济账户扣款人 LS_bke922 SF_JZB000.bke922%type;--(家庭健康)共济账户扣款人金额 LS_jsqdbz SF_JZB000.jsqdbz%type;--医保结算单上传标志(1:已上传 2:已修改) LS_ybtype SF_JZB000.ybtype%type;--医保接口类型[-1:自费 0:福建版本 1:国家基线版] LS_med_type SF_JZB000.med_type%type;--[国家医保]医疗类别(aka130) LS_psn_type SF_JZB000.psn_type%type;--[国家医保]人员类别(bkc001) LS_medins_setl_id SF_JZB000.medins_setl_id%type;--[国家医保]医药机构结算ID LS_clr_optins SF_JZB000.clr_optins%type;--[国家医保]清算经办机构 LS_clr_way SF_JZB000.clr_way%type;--[国家医保]清算方式 LS_clr_type SF_JZB000.clr_type%type;--[国家医保]清算类别 LS_fulamt_ownpay_amt SF_JZB000.fulamt_ownpay_amt%type;--[国家医保]全自费金额 LS_overlmt_selfpay SF_JZB000.overlmt_selfpay%type;--[国家医保]超限价自费费用 LS_preselfpay_amt SF_JZB000.preselfpay_amt%type;--[国家医保]先行自付金额 LS_inscp_scp_amt SF_JZB000.inscp_scp_amt%type;--[国家医保]符合政策范围金额 LS_hifmi_pay SF_JZB000.hifmi_pay%type;--[国家医保]居民大病保险资金支出 LS_hifob_pay SF_JZB000.hifob_pay%type;--[国家医保]职工大额医疗费用补助基金支出 LS_acct_mulaid_pay SF_JZB000.acct_mulaid_pay%type;--[国家医保]个人账户共济支付金额 LS_pool_prop_selfpay SF_JZB000.pool_prop_selfpay%type;--[国家医保]基本医疗保险统筹基金支付比例 LS_hifdm_pay SF_JZB000.hifdm_pay%type;--[国家医保]伤残人员医疗保障基金支出 LS_hosp_part_amt SF_JZB000.hosp_part_amt%type;--[国家医保]医院负担金额 LS_IC_SFDM number(10); --ICK收费代码 LS_SF_BLGBFSFXMBH number(10); --病历本收费代码 LS_SF_YBFYJS_MZH000 varchar2(1); LS_PHLYXZ SF_PJSYQK.PHLYXZ%type;--票据领用限制 N:不限制 LS_JZB_BRZJE0 SF_JZB000.BRZJE0%type;--【结账表】费用总金额 LS_JZB_JZJE00 SF_JZB000.JZJE00%type;--【结账表】记帐金额 LS_JZB_GFJE00 SF_JZB000.GFJE00%type;--【结账表】公费金额 LS_JZB_HJJE00 SF_JZB000.HJJE00%type;--【结账表】合计金额 LS_JZB_JMJE00 SF_JZB000.JMJE00%type;--【结账表】减免金额 LS_JZB_GRZHZF SF_JZB000.GRZHZF%type;--【结账表】医疗帐户支付 LS_JZB_TCJJZF SF_JZB000.TCJJZF%type;--【结账表】统筹基金支付 LS_JZB_ZFJE00 SF_JZB000.ZFJE00%type;--【结账表】自付金额 LS_PJB_XJJE00 SF_PJSYQK.XJJE00%type;--【票据表】现金金额 LS_PJB_ZZJE00 SF_PJSYQK.ZZJE00%type;--【票据表】转帐金额 LS_PJB_YHKJE0 SF_PJSYQK.ZZJE00%type;--【票据表】银行卡金额 LS_FYB_HJJE00 SF_BRFY00.HJJE00%type;--【费用表】费用总金额 LS_FYB_ZFJE00 SF_BRFY00.ZFJE00%type;--【费用表】自费金额 LS_FYB_GFJE00 SF_BRFY00.GFJE00%type;--【费用表】公费金额 LS_FYB_JZJE00 SF_BRFY00.JZJE00%type;--【费用表】记帐金额 ls_OUTXML varchar2(2000); ls_ZXCGBZ number(10); ls_TSXX00 varchar2(240); ls_XXXX00 varchar2(240); cursor CUR_SF_BRFY00_JS0000 is select C.MZFPID,C.SFXMID from SF_FYMX00 A,BM_YYSFXM C,SF_BRFY00 D where A.XMBH00 = C.SFXMID and A.DJH000 = D.DJH000 and D.JZDH00 = ad_JZDH00 and D.JFLBID = ad_JFLBID --JFLBID=1是门诊预交金账户 and D.BRID00 = ad_BRID00 and D.CZRQ00 >= as_KSRQ00 and D.CZRQ00 <= as_JSRQ00 and (D.MZH000 = as_GHH000 or D.MZH000 = '0' or as_GHH000 = '0'); --若门诊号为'0',将所有的列出 cursor CUR_SF_JZMX00_ZFBL00 is select * from SF_JZMX00 where JZDH00 = ad_JZDH00 AND NVL(ZFBL00,1)<>0 order by ZFBL00; begin LS_ERRMSG :='开始变量赋值'; --定位错误信息 ls_CZRQ00 := to_char(sysdate,'YYYYMMDD'); ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS'); ad_ZFDJH0 := 0 ; --病人退费记录对应的单据号 ad_GRDJH0 := 0 ; --个人账户对应的单据号 ad_TJDJH0 := 0 ; --统筹基金对应的单据号 ad_SBZHDH := 0 ; --商保个人帐户单据流水号 ad_SBTJDH := 0 ; --商保统筹基金单据流水号 LS_JSQYE0:=0;--结算前账户总余额 LS_JSHYE0:=0;--结算后账户总余额 LS_FKJSLX:=nvl(as_FKJSLX,'ZFJS');--分开结算类型 空值正常结算 FYCF_ZF:自费处方 FYCF_ZC:正常处方 ZFJS:自费结算 YBJS:医保结算 CXJS:重新结算 LS_FKLSH0:=as_FKLSH0;--分开结算流水号 LS_FKJZDH:=ad_FKJZDH;--分开结算单号 LS_FKJZJE:=ad_FKJZJE;--分开结算金额 LS_YBBXZE:=0;--医保报销总额(DLTCJJ+DLZHZF+YBQTZE+DLSYBX+DLJZFP+DLQTJJ+DLQYBC+MZBZJE+DLSBJE+GWBZJE+DLDBJJ) LS_DLTCJJ:=0;--实际医保统筹基金 LS_DLZHZF:=0;--实际医保账户支付 LS_YBQTZE:=0;--医保其它总额 LS_DLSYBX:=0;--商业保险支付(独立于统筹基金金额) LS_DLJZFP:=0;--精准扶贫医疗(独立于统筹基金金额) LS_DLQTJJ:=0;--其他基金支付(独立于统筹基金金额) LS_DLQYBC:=0;--企业补充(独立于统筹基金金额) LS_DLMZBZ:=0;--民政补助金额(独立于统筹基金金额) LS_DLSBJE:=0;--商保支付金额(独立于统筹基金金额) LS_GWBZJE:=0;--公务员补助金额(独立于统筹基金金额) LS_DLDBJJ:=0;--大病基金支付金额(独立于统筹基金金额) LS_CJBTCE:=0;--产检检查费（bkc064）超出个人自付金额后需补退的差额 LS_bkc620:=0;--(本地)成员共济账户支付[bkc620] LS_bkc619:=0;--(异地)成员共济账户支付[bkc619] LS_bke284:=null;--身份认证类型(01:读卡02:融合码06:医保码20:医疗证) LS_bke921:=null;--(家庭健康)共济账户扣款人 LS_bke922:=null;--(家庭健康)共济账户扣款人金额 LS_jsqdbz:=null;--医保结算单上传标志(1:已上传 2:已修改) LS_ybtype:=nvl(as_YBTYPE,'-1');--医保接口类型[-1:自费 0:福建版本 1:国家基线版] LS_med_type:=null;--[国家医保]医疗类别(aka130) LS_psn_type:=null;--[国家医保]人员类别(bkc001) LS_medins_setl_id:=null;--[国家医保]医药机构结算ID LS_clr_optins:=null;--[国家医保]清算经办机构 LS_clr_way:=null;--[国家医保]清算方式 LS_clr_type:=null;--[国家医保]清算类别 LS_fulamt_ownpay_amt:=0;--[国家医保]全自费金额 LS_overlmt_selfpay:=0;--[国家医保]超限价自费费用 LS_preselfpay_amt:=0;--[国家医保]先行自付金额 LS_inscp_scp_amt:=0;--[国家医保]符合政策范围金额 LS_hifmi_pay:=0;--[国家医保]居民大病保险资金支出 LS_hifob_pay:=0;--[国家医保]职工大额医疗费用补助基金支出 LS_acct_mulaid_pay:=0;--[国家医保]个人账户共济支付金额 LS_pool_prop_selfpay:=0;--[国家医保]基本医疗保险统筹基金支付比例 LS_hifdm_pay:=0;--[国家医保]伤残人员医疗保障基金支出 LS_hosp_part_amt:=0;--[国家医保]医院负担金额 LS_IC_SFDM:=0;--ICK收费代码 LS_SF_BLGBFSFXMBH:=0;--病历本收费代码 LS_SF_YBFYJS_MZH000:='1';--门诊收费医保病人结算是否结算SF_BRFY00.MZH000=0的数据 1:参与结算 0:不参与结算 默认值:1 LS_PHLYXZ :=null;--票据领用限制 N:不限制 LS_ERRMSG :='结束变量赋值'; --定位错误信息 LS_ERRMSG :='开始获取相关参数'; --定位错误信息 if as_SFDZPJ='1' then --是否电子票据 0:否 1:是 LS_PHLYXZ :='N';--票据领用限制 N:不限制 end if; begin select trim(value0) into LS_SFCFFP from xt_xtcs00 where name00='SF_SFCFFP'; exception when others then LS_SFCFFP:='N'; end; --ICK卡收费代码 begin select to_number(trim(value0)) into LS_IC_SFDM from xt_xtcs00 where name00='IC_SFDM'; exception when others then LS_IC_SFDM:=0; end; --病历本收费代码 begin select to_number(trim(nvl(value0,'0'))) into LS_SF_BLGBFSFXMBH from XT_XTCS00 where name00='SF_BLGBFSFXMBH'; exception when others then LS_SF_BLGBFSFXMBH:=0; end; begin select trim(VALUE0) into ls_GFKJE0 from XT_XTCS00 where NAME00='SF_GFBRKYJSFAZFBLLHQ'; exception when others then ls_GFKJE0:='N'; end; begin select trim(VALUE0) into ls_ICKSFCYJS from XT_XTCS00 where NAME00='SF_ICKSFCYJS'; exception when others then ls_ICKSFCYJS:='N'; end; begin select trim(value0) into ls_BRZYZTSFKYTK from xt_xtcs00 where name00='SF_BRZYZTSFKYTK'; exception when others then ls_BRZYZTSFKYTK:='Y'; end; ---费用结算清账退卡有转账金额是否以现金方式退款 Y:是 N:否 默认为Y --扩展参数SF_ZZJESFYXJTK MZSF-20250103-001 ls_SF_ZZJESFYXJTK:=SF_XT_GETXTCSVALUE('SF_ZZJESFYXJTK','Y'); ls_SF_ZZJESFYXJTK_SFQY:=SF_SF_RETURNINDEXEH(ls_SF_ZZJESFYXJTK, '|',1) ; if not ls_SF_ZZJESFYXJTK_SFQY in ('Y','N') then ls_SF_ZZJESFYXJTK_SFQY:='Y'; end if; begin ls_SF_ZZJESFYXJTK_XZJE:= to_number(SF_SF_RETURNINDEXEH(ls_SF_ZZJESFYXJTK, '|',2)) ; if ls_SF_ZZJESFYXJTK_XZJE<=0 then ls_SF_ZZJESFYXJTK_XZJE:=100; end if; exception when others then ls_SF_ZZJESFYXJTK_XZJE:=100; end; ----扩展参数SF_ZZJESFYXJTK end MZSF-20250103-001 ---限制中医辨证论治不能以特殊病种结算(Y:限制,N:不限制,默认N) begin select trim(value0) into ls_XZZYBZLZBNYTSBZJS from xt_xtcs00 where name00='SF_XZZYBZLZBNYTSBZJS'; exception when others then ls_XZZYBZLZBNYTSBZJS:='N'; end; --门诊ICK押金是否以帐户冻结方式出现，默认为N begin select substrb(trim(value0),1,100) into SF_MZICKYJDJFS from xt_xtcs00 where name00='SF_MZICKYJDJFS'; exception when others then SF_MZICKYJDJFS:='N'; end; LS_ERRMSG :='结束获取相关参数'; --定位错误信息 --2020.02.04 zhangyc MZSF-20200120-001 清账、退卡，处理ICK卡费停用处理--begin-- if as_CZBZ00 in ('2') and SF_MZICKYJDJFS in ('Y') then --获取ICK金额 begin select A.ICKJE0,C.TYJE00 into LS_ICKJE0,LS_ZHTYJE from IC_YHXX00 A,BM_BRXXB0 B,SF_BRZHXX C where A.BRID00=b.BRID00 and B.BRID00=C.BRID00 and A.ZT0000='1' and b.BRID00=ad_BRID00 and C.JFLBID =ad_JFLBID; exception when others then LS_ICKJE0:=0; LS_ZHTYJE:=0; end; if nvl(LS_ICKJE0,0)>0 and nvl(LS_ZHTYJE,0)>0 then if nvl(LS_ICKJE0,0)>=nvl(LS_ZHTYJE,0) then ls_TYJE00:=0; else ls_TYJE00:=nvl(LS_ZHTYJE,0)-nvl(LS_ICKJE0,0); end if; Update SF_BRZHXX Set TYJE00 = ls_TYJE00 where BRID00 = ad_BRID00 and JFLBID =ad_JFLBID; end if; end if; --2020.02.04 zhangyc MZSF-20200120-001 清账、退卡，处理ICK卡费停用处理--End-- --获取结算前账户总余额 (参数：0：门诊 1：住院 参数2：BRID00 参数3：1：门诊 2:住院 参数4：0:可用余额 1:总余额 2：现金余额 3:停用金额) begin select SF_SF_GETBRZHYE(0,ad_BRID00,1,1) into LS_JSQYE0 from dual; exception when others then LS_JSQYE0:=0; end; begin Select trim(VALUE0) into SF_SWFBQFJESZ from xt_xtcs00 where name00 in ('SF_SWFBQFJESZ'); VFBBH00_Temp00:=to_number(substrb(SF_SWFBQFJESZ,1,instrb(SF_SWFBQFJESZ,',')-1)); VYXQFJE_Temp00:=to_number(substrb(SF_SWFBQFJESZ,instrb(SF_SWFBQFJESZ,',')+1,10)); exception when others then SF_SWFBQFJESZ:='0,0'; VFBBH00_Temp00:=0; VYXQFJE_Temp00:=0; end; begin select NVL(count(*),0) into ls_COUNT0 from ZY_BRXXB0 where BRID00=ad_BRID00 AND CYRQ00 is null; --病人未出院的话，门诊那边是否可以清帐退卡(默认是Y) if (ls_BRZYZTSFKYTK='N') and (ls_COUNT0 > 0) and (as_CZBZ00='1') then as_YHMSG0:='在院病人不允许进行退卡操作！'; as_SYSMSG:='在院病人不允许进行退卡操作！'||SQLERRM; rollback; return; end if; end; select SQ_SF_JZB000_JZDH00.nextval into ad_JZDH00 from dual; select NVL(max(GHID00),0) into ls_MZID00 from SF_BRXXB0 where BRID00 = ad_BRID00 and (GHH000 = as_GHH000 or as_GHH000 = '0'); --取使用预交金账户挂号最大的挂号ID select FBBH00,trim(YBLB00),YBBRLB,YBKH00,YBID00 into ls_FBBH00,ls_YBLB00,LS_YBBRLB,LS_YBKH00,LS_YBID00 from BM_BRXXB0 where BRID00 = ad_BRID00; if ls_FBBH00=3 then --门诊收费医保病人结算是否结算SF_BRFY00.MZH000=0的数据 1:参与结算 0:不参与结算 默认值:1 begin select substrb(trim(value0),1,1) into LS_SF_YBFYJS_MZH000 from xt_xtcs00 where name00='SF_YBFYJS_MZH000'; exception when others then LS_SF_YBFYJS_MZH000:='1'; end; LS_SF_YBFYJS_MZH000:=nvl(LS_SF_YBFYJS_MZH000,'1'); end if; begin select ','||trim(value0)||',' into ls_XZBRFB from xt_xtcs00 where name00 = 'SF_KZMZFBBRBNTK'; --控制某种费别的病人不允许退款 EXCEPTION WHEN OTHERS THEN ls_XZBRFB:=',-1,'; END; If (as_CZBZ00='1') and (ls_XZBRFB<>'-1') then if instr(ls_XZBRFB,','||to_char(ls_FBBH00)||',')>0 THEN as_YHMSG0:='该费别的病人不允许清帐退卡！'; as_SYSMSG:='该费别的病人不允许清帐退卡！'||SQLERRM; rollback; return; end if; end if; select count(1) into ls_COUNT0 from xt_xtcs00 where name00 = 'SF_XZFBTFJS' and instrb(','||trim(value0)||',',','||to_char(ls_FBBH00)||',')>0; if ls_COUNT0>0 then as_YHMSG0:='该费别的病人不允许结算！'; as_SYSMSG:='该费别的病人不允许结算！'||SQLERRM; rollback; return; end if; select count(1) into ls_COUNT0 from BM_BRXXB0 where BRID00=ad_BRID00 and SFGHXZ='1'; if (ls_COUNT0>0) and (SF_SF_GETYYID00='225085') then as_YHMSG0:='挂号限制的病人不允许结算！'; as_SYSMSG:='挂号限制的病人不允许结算！'||SQLERRM; rollback; return; end if; begin select trim(YBZXLB) into ls_YBZXLB from IC_YBBRLB where FBBH00=ls_FBBH00 and YBLB00=ls_YBLB00; EXCEPTION when NO_DATA_FOUND then as_YHMSG0:='系统字典维护不完整,请察看详细信息后与系统管理员联系！'; as_SYSMSG:=SQLERRM||'select * from IC_YBBRLB where YBLB00='||ls_YBLB00||' and BFFH00='||to_char(ls_fbbh00); rollback; return; end; ls_ICKJE0:=0; --若病人有退卡，生成要退卡的费用 begin select trim(value0) into LS_SFQZTK from xt_xtcs00 where name00='SF_SFKYQZTK'; --能否进行退卡清账操作 exception when others then LS_SFQZTK :='Y'; end; if as_CZBZ00 = '1' then --结算退卡,添加退就诊卡的记录 LS_ERRMSG :='开始结算清账退卡'; --定位错误信息 if LS_SFQZTK='N' then select czrq00 into ls_ICKRQ0 from ic_ywls00 where ickh00=as_ICKH00 and YWCZ00 in ('1','2'); if ls_ICKRQ0>='20070101' then as_YHMSG0:='此卡是2007年1月1日以后办的卡，不能进行退卡清账操作！'; rollback; return; end if; end if; Select NVL(SFDM00,0),NVL(ICKJE0,0) into LS_IC_SFDM,ls_ICKJE0 from IC_YHXX00 where ICKH00 = as_ICKH00 and ZT0000 <> '4'; if (LS_IC_SFDM > 0)and(ls_ICKJE0>0) then select count(A.FPXMID) into ls_COUNT0 from BM_FPXM00 A,BM_YYSFXM B where A.FPXMID = B.MZFPID and B.SFXMID=LS_IC_SFDM; if ls_COUNT0 = 0 then as_YHMSG0:='病人结算操作失败，发票项目不完整！'; as_SYSMSG:='BM_YYSFXM.SFXMID='||to_char(LS_IC_SFDM)||'的MZFPID的值NOT IN BM_FPXM00.FPXMID！'; rollback; return; end if; end if; LS_ERRMSG :='结算清账退卡-修改就诊卡的相关信息'; --定位错误信息 --1、修改就诊卡的相关信息 Update IC_YHXX00 Set ZT0000 = '4' --无效卡 where ICKH00 = as_ICKH00 and ZT0000 <> '4' ; --and ICKLB0 = '0' if SQL%NOTFOUND then as_YHMSG0:='病人就诊卡信息修改失败,请稍候再试！'; as_SYSMSG:='病人就诊卡信息修改失败,请稍候再试！'; rollback; return; end if; select ICKNBH into ls_ICKNBH from IC_YHXX00 where ICKH00 = as_ICKH00; LS_ERRMSG :='结算清账退卡-Insert into IC_YWLS00'; --定位错误信息 Insert into IC_YWLS00(YWID00,ICKH00,YGBH00,YWCZ00,CZRQ00,CZSJ00,FSJE00,ICKNBH) values(SQ_IC_YWLS00_YWID00.nextval,as_ICKH00,ad_JZY000,'5',ls_CZRQ00,ls_CZSJ00,ls_ICKJE0,ls_ICKNBH); if (LS_IC_SFDM > 0 )and(ls_ICKJE0>0) then select SQ_SF_BRJFB0_DJH000.nextval into ls_DJH000 from dual; if (ls_GFKJE0='Y') and (ls_FBBH00 not in(1,3)) then --公费病人ICK金额是否按BM_YBSFDY中的ZFBL00来结算 select NVL(ZFBL00*ls_ICKJE0,0),NVL((1-ZFBL00)*ls_ICKJE0,0) into lv_ZFJE00,lv_JZJE00 from bm_ybsfdy where FBBH00=ls_FBBH00 AND SFYP00='N' and YBZXLB=ls_YBZXLB and sfxmid=LS_IC_SFDM; --2、添加病人的账目信息 --select SQ_SF_BRJFB0_DJH000.nextval into ls_DJH000 from dual; LS_ERRMSG :='结算清账退卡-Insert into SF_BRFY00_0'; --定位错误信息 Insert into SF_BRFY00(DJH000,MZH000,MZID00,JFLBID,BRID00,XM0000,BRFB00,HJJE00,ZFJE00,JZJE00,GFJE00,BRYE00, CZRQ00,CZSJ00,CZY000,CZYXM0,CZYKS0,JZDH00) values(ls_DJH000,'0',ls_MZID00,ad_JFLBID,ad_BRID00,as_XM0000,ad_BRFB00,-ls_ICKJE0,decode(lv_ZFJE00,0,0,-lv_ZFJE00),decode(lv_JZJE00,0,0,-lv_JZJE00),0,0, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JZYXM0,ad_JZYKS0,0); LS_ERRMSG :='结算清账退卡-Insert into SF_FYMX00_0'; --定位错误信息 Insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,ZXKS00,YSKS00) select SQ_SF_FYMX00_MXID00.nextval,ls_DJH000,SFXMID,XMMC00,'',DW0000,ls_ICKJE0,-1,-ls_ICKJE0,decode(lv_ZFJE00,0,0,-lv_ZFJE00),0,decode(lv_JZJE00,0,0,-lv_JZJE00), ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00,ad_JZYKS0,ad_JZYKS0,ad_JZYKS0 from BM_YYSFXM where SFXMID = LS_IC_SFDM; else --2、添加病人的账目信息 select SQ_SF_BRJFB0_DJH000.nextval into ls_DJH000 from dual; LS_ERRMSG :='结算清账退卡-Insert into SF_BRFY00_1'; --定位错误信息 Insert into SF_BRFY00(DJH000,MZH000,MZID00,JFLBID,BRID00,XM0000,BRFB00,HJJE00,ZFJE00,JZJE00,GFJE00,BRYE00, CZRQ00,CZSJ00,CZY000,CZYXM0,CZYKS0,JZDH00) values(ls_DJH000,'0',ls_MZID00,ad_JFLBID,ad_BRID00,as_XM0000,ad_BRFB00,-ls_ICKJE0,-ls_ICKJE0,0,0,0, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JZYXM0,ad_JZYKS0,0); LS_ERRMSG :='结算清账退卡-Insert into SF_FYMX00_1'; --定位错误信息 Insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,ZXKS00,YSKS00) select SQ_SF_FYMX00_MXID00.nextval,ls_DJH000,SFXMID,XMMC00,'',DW0000,ls_ICKJE0,-1,-ls_ICKJE0,-ls_ICKJE0,0,0, ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00,ad_JZYKS0,ad_JZYKS0,ad_JZYKS0 from BM_YYSFXM where SFXMID = LS_IC_SFDM; end if; end if; LS_ERRMSG :='结束结算清账退卡'; --定位错误信息 end if; if (ls_ICKSFCYJS='N') or ((ls_ICKSFCYJS='Y') AND (as_CZBZ00 <> '1')) then ls_ICKJE0:=0; end if; select SQ_SF_PJSYQK_PJH000.nextval into ls_PJH000 from dual; ad_hjje01:=ad_BRZJE0-ls_ICKJE0; --2、填写病人结账明细表信息 --先判断一下要结算的项目是否有对应的发票项目 --2012.03.23 目前是不起作用，应该移到4、Update SF_BRFY00之后 open CUR_SF_BRFY00_JS0000; LOOP fetch CUR_SF_BRFY00_JS0000 into ls_MZFPID,ls_SFXMID; exit when CUR_SF_BRFY00_JS0000%NOTFOUND; select count(*) into ls_COUNT0 from BM_FPXM00 where FPXMID = ls_MZFPID; if ls_COUNT0 = 0 then as_YHMSG0:='病人结算操作失败，发票项目不完整！'; as_SYSMSG:='BM_YYSFXM.SFXMID='||to_char(ls_SFXMID)||'的MZFPID的值NOT IN BM_FPXM00.FPXMID！'; rollback; return; end if; end LOOP; close CUR_SF_BRFY00_JS0000; if (TRIM(as_SFYB00)='Y')or(ls_FBBH00=1) then LS_SFCFFP:='N'; end if; --4、Update SF_BRFY00 --4.1 结算全部费用 LS_ERRMSG :='开始修改费用表对应的结账单号'; --定位错误信息 if trim(as_ID0000) is not null then --通过过程:SP_SF_FYJS00_DJH000整理数据直接用中间表SF_FYXX00_DJH000作为条件 Update SF_BRFY00 D set YBDJH0 = as_YBDJH0, --医保的单据流水号 JZDH00 = ad_JZDH00 --结算单号 where D.BRID00 = ad_BRID00 and D.JFLBID = ad_JFLBID and D.JZDH00+0= 0 and exists (select 1 from SF_FYXX00_DJH000 AA where AA.BRID00=D.BRID00 and AA.DJH000=D.DJH000 and AA.BRID00=ad_BRID00 and AA.ID0000 = as_ID0000) ; else if AS_BZJSLX=0 then --0:全部费用 Update SF_BRFY00 D set YBDJH0 = as_YBDJH0, --医保的单据流水号 JZDH00 = ad_JZDH00 --结算单号 where D.JZDH00+0= 0 --未结算的病人 and ((ls_ICKSFCYJS='N' AND D.djh000 not in(select a.djh000 from sf_fymx00 a,sf_brfy00 b where a.djh000=b.djh000 and a.xmbh00=LS_IC_SFDM and b.brid00=ad_BRID00)) OR (ls_ICKSFCYJS='Y')) and D.JFLBID = ad_JFLBID --有门诊预交金账户的病人 and D.BRID00 = ad_BRID00 and D.CZRQ00 >= as_KSRQ00 and D.CZRQ00 <= as_JSRQ00 and (D.MZH000 = as_GHH000 or as_GHH000 = '0' or (D.MZH000 = '0' and LS_SF_YBFYJS_MZH000<>'0')); elsif AS_BZJSLX=1 then --1:本病种+挂号费+诊查费 Update SF_BRFY00 D set YBDJH0 = as_YBDJH0, --医保的单据流水号 JZDH00 = ad_JZDH00 --结算单号 where D.JZDH00+0= 0 --未结算的病人 and ((ls_ICKSFCYJS='N' AND D.djh000 not in(select a.djh000 from sf_fymx00 a,sf_brfy00 b where a.djh000=b.djh000 and a.xmbh00=LS_IC_SFDM and b.brid00=ad_BRID00)) OR (ls_ICKSFCYJS='Y')) and D.JFLBID = ad_JFLBID --有门诊预交金账户的病人 and D.BRID00 = ad_BRID00 and D.CZRQ00 >= as_KSRQ00 and D.CZRQ00 <= as_JSRQ00 and (D.MZH000 = as_GHH000 or as_GHH000 = '0' or (D.MZH000 = '0' and LS_SF_YBFYJS_MZH000<>'0')) and (exists (select 1 from VW_SF_YBTZBZ e where e.brid00=d.brid00 and e.djh000=decode(nvl(d.CXDJH0,0),0,d.djh000,D.CXDJH0) and e.tsbzbh=as_tsbzbh) or exists (select 1 from sf_fymx00 a,bm_yysfxm b,BM_FPXM00 c where a.djh000=d.djh000 and a.xmbh00=b.sfxmid and b.mzfpid=c.FPXMID and c.bh0000 in ('04','06'))); elsif AS_BZJSLX=2 then --2：本病种+非病种全部费用 Update SF_BRFY00 D set YBDJH0 = as_YBDJH0, --医保的单据流水号 JZDH00 = ad_JZDH00 --结算单号 where D.JZDH00+0= 0 --未结算的病人 and ((ls_ICKSFCYJS='N' AND D.djh000 not in(select a.djh000 from sf_fymx00 a,sf_brfy00 b where a.djh000=b.djh000 and a.xmbh00=LS_IC_SFDM and b.brid00=ad_BRID00)) OR (ls_ICKSFCYJS='Y')) and D.JFLBID = ad_JFLBID --有门诊预交金账户的病人 and D.BRID00 = ad_BRID00 and D.CZRQ00 >= as_KSRQ00 and D.CZRQ00 <= as_JSRQ00 and (D.MZH000 = as_GHH000 or as_GHH000 = '0' or (D.MZH000 = '0' and LS_SF_YBFYJS_MZH000<>'0')) and (exists (select 1 from VW_SF_YBTZBZ e where e.brid00=d.brid00 and e.djh000=decode(nvl(d.CXDJH0,0),0,d.djh000,D.CXDJH0) and e.tsbzbh=as_tsbzbh) or (decode(nvl(d.cxdjh0,0),0,d.djh000,d.cxdjh0) not in ( select distinct a.djh000 from VW_SF_YBTZBZ A,sf_brfy00 c where a.djh000=c.djh000 and c.jzdh00+0=0 and trim(a.tsbzbh) is not null and a.BRID00 =ad_BRID00 and c.JFLBID =ad_JFLBID and c.CZRQ00>=as_KSRQ00 and c.CZRQ00<=as_JSRQ00)) ) and (ls_XZZYBZLZBNYTSBZJS='N' or not exists (select 1 from sf_fymx00 a,bm_tyzd00 c where a.djh000=d.djh000 and a.xmbh00=c.XSXH00 and c.ZDMC00='不允许以特殊病种做结算项目')); --and not exists (select 1 from VW_SF_YBTZBZ e where e.brid00=d.brid00 and e.djh000=decode(nvl(d.CXDJH0,0),0,d.djh000,D.CXDJH0) and e.tsbzbh<>as_tsbzbh); elsif AS_BZJSLX=3 then --3:-结算本病种费用 Update SF_BRFY00 D set YBDJH0 = as_YBDJH0, --医保的单据流水号 JZDH00 = ad_JZDH00 --结算单号 where D.JZDH00+0= 0 --未结算的病人 and ((ls_ICKSFCYJS='N' AND D.djh000 not in(select a.djh000 from sf_fymx00 a,sf_brfy00 b where a.djh000=b.djh000 and a.xmbh00=LS_IC_SFDM and b.brid00=ad_BRID00)) OR (ls_ICKSFCYJS='Y')) and D.JFLBID = ad_JFLBID --有门诊预交金账户的病人 and D.BRID00 = ad_BRID00 and D.CZRQ00 >= as_KSRQ00 and D.CZRQ00 <= as_JSRQ00 and (D.MZH000 = as_GHH000 or as_GHH000 = '0' or (D.MZH000 = '0' and LS_SF_YBFYJS_MZH000<>'0')) and exists (select 1 from VW_SF_YBTZBZ e where e.brid00=d.brid00 and e.djh000=decode(nvl(d.CXDJH0,0),0,d.djh000,D.CXDJH0) and e.tsbzbh=as_tsbzbh); --2015.06.29 更新特殊病种减免费用表的单据号 Update SF_BRFY00 D set YBDJH0 = as_YBDJH0, --医保的单据流水号 JZDH00 = ad_JZDH00 --结算单号 where D.JZDH00+0= 0 --未结算的病人 and D.BRID00 = ad_BRID00 and D.CZRQ00 >= as_KSRQ00 and D.CZRQ00 <= as_JSRQ00 and (D.MZH000 = as_GHH000 or as_GHH000 = '0' or (D.MZH000 = '0' and LS_SF_YBFYJS_MZH000<>'0')) and exists (select 1 from SF_FYMX00 B Where B.DJH000=D.DJH000 and B.JMBZ00='5'); else --4:只结算非特殊病种费用 Update SF_BRFY00 D set YBDJH0 = as_YBDJH0, --医保的单据流水号 JZDH00 = ad_JZDH00 --结算单号 where D.JZDH00+0= 0 --未结算的病人 and ((ls_ICKSFCYJS='N' AND D.djh000 not in(select a.djh000 from sf_fymx00 a,sf_brfy00 b where a.djh000=b.djh000 and a.xmbh00=LS_IC_SFDM and b.brid00=ad_BRID00)) OR (ls_ICKSFCYJS='Y')) and D.JFLBID = ad_JFLBID --有门诊预交金账户的病人 and D.BRID00 = ad_BRID00 and D.CZRQ00 >= as_KSRQ00 and D.CZRQ00 <= as_JSRQ00 and (D.MZH000 = as_GHH000 or as_GHH000 = '0' or (D.MZH000 = '0' and LS_SF_YBFYJS_MZH000<>'0') ) and not exists (select 1 from VW_SF_YBTZBZ e where e.brid00=d.brid00 and e.djh000=decode(nvl(d.CXDJH0,0),0,d.djh000,D.CXDJH0)); end if; end if; LS_ERRMSG :='结束修改费用表对应的结账单号'; --定位错误信息 -- BY MZSF-20140911-001 不按挂号结算时重新取值避免本次结账表中mzid00同费用表中mzid00 不一致 if as_GHH000 = '0' then select NVL(max(MZID00),0) into ls_MZID01 from SF_BRFY00 where BRID00 = ad_BRID00 and JZDH00 = ad_JZDH00; if ls_MZID01<>0 then ls_MZID00:=ls_MZID01; end if; end if; LS_ERRMSG :='开始根据结账单号查找费用表金额'; --定位错误信息 --2023.04.14 处理结账表和费用表合计不一致问题---begin--- --根据结账单号查找费用表金额 begin select sum(A.HJJE00)as HJJE00,sum(A.ZFJE00)as ZFJE00,sum(A.GFJE00)as GFJE00,sum(A.JZJE00)as JZJE00 into LS_FYB_HJJE00,LS_FYB_ZFJE00,LS_FYB_GFJE00,LS_FYB_JZJE00 from SF_BRFY00 A where A.BRID00 = ad_BRID00 and A.JZDH00=ad_JZDH00; exception when others then LS_FYB_HJJE00 :=0;--【费用表】费用总金额 LS_FYB_ZFJE00 :=0;--【费用表】自费金额 LS_FYB_GFJE00 :=0;--【费用表】公费金额 LS_FYB_JZJE00 :=0;--【费用表】记帐金额 end; LS_JZB_BRZJE0 :=nvl(ad_BRZJE0,0)-nvl(ls_ICKJE0,0);--【结账表】费用总金额 LS_JZB_JZJE00 :=nvl(ad_JZJE00,0);--【结账表】记帐金额 LS_JZB_GFJE00 :=nvl(ad_GFJE00,0);--【结账表】公费金额 LS_JZB_HJJE00 :=nvl(ad_HJJE00,0)-nvl(ls_ICKJE0,0);--【结账表】合计金额 LS_JZB_JMJE00 :=nvl(ad_JMJE00,0);--【结账表】减免金额 LS_JZB_GRZHZF :=nvl(ad_GRZHZF,0);--【结账表】医疗帐户支付 LS_JZB_TCJJZF :=nvl(ad_TCJJZF,0);--【结账表】统筹基金支付 LS_JZB_ZFJE00 :=nvl(ad_ZFJE00,0)-nvl(ls_ICKJE0,0);--【结账表】自付金额 LS_PJB_XJJE00 :=nvl(ad_BRZJE0,0)-nvl(LS_ICKJE0,0);--【票据表】现金金额 LS_PJB_ZZJE00 :=0;--【票据表】转帐金额 LS_PJB_YHKJE0 :=0;--【票据表】银行卡金额 --自费病人如果结账表合计金额<>费用表合计金额 以费用表合计金额为准 if LS_FBBH00=1 then if LS_JZB_HJJE00<>LS_FYB_HJJE00 then LS_JZB_BRZJE0:=LS_FYB_HJJE00;--【结账表】费用总金额 LS_JZB_HJJE00:=LS_FYB_HJJE00;--【结账表】合计金额 LS_JZB_ZFJE00:=LS_FYB_HJJE00;--【结账表】自付金额 LS_PJB_XJJE00:=LS_FYB_HJJE00;--【票据表】现金金额 end if; end if; LS_ERRMSG :='结束根据结账单号查找费用表金额'; --定位错误信息 if LS_FBBH00 in (1,3) and LS_FYB_HJJE00<>LS_JZB_HJJE00 then as_YHMSG0:='病人费用结算失败，错误原因:费用表合计金额不等于结账表合计金额！费用合计金额:'||trim(to_char(LS_FYB_HJJE00,'999999990.99'))||' 结账合计金额:'||trim(to_char(LS_JZB_HJJE00,'999999990.99')); as_SYSMSG:=as_YHMSG0; rollback; return; end if; --2023.04.14 处理结账表和费用表合计不一致问题---End--- LS_ERRMSG :='开始添加病人结账表信息'; --定位错误信息 --1、添加病人结账表信息,若病人有退卡，还应该再添加上病人退卡的金额 Insert into SF_JZB000(JZDH00,BRID00,MZID00,BRZJE0,JZJE00,GFJE00,HJJE00,JMJE00,GRZHZF,TCJJZF,ZFJE00,PJH000, JZRQ00,JZSJ00,JZY000,JMYY00,MXZJE0, YBYL01,YBYL02,YBYL03,YBYL04,YBYL05,YBYL06,YBYL07,YBYL08,YBYL09, YBYL10,YBYL11,YBYL12,YBYL13,YBYL14,YBYL15,YBYL16,YBYL17,YBYL18,YBYL19, YBYL20,YBYL21,YBYL22,YBYL23,YBYL24,YBYL25,YBYL26,YBYL27,YBYL28,YBYL29, YBYL30,YBYL31,YBYL32,YBYL33,YBYL34,YBYL35,YBYL36,YBYL37,YBYL38,YBYL39, YBYL40,YBYL41,YBYL42,YBYL43,YBYL44,YBYL45,YBYL46,YBYL47,YBYL48,YBYL49, YBYL50,YBYL51,YBYL52,YBYL53,YBYL54,YBYL55,YBYL56,YBYL57,YBYL58,YBYL59, YBYL60,YBYL61,YBYL62,YBYL63,YBYL64,YBYL65,YBYL66,YBYL67,YBYL68,YBYL69, YBSM01,YBSM02,YBSM03,YBSM04,YBSM05,YBSM06,YBSM07,YBSM08,YBSM09,YBSM10, YBSM11,YBSM12,YBSM13,YBSM14,YBSM15,YBSM16,YBSM17,YBSM18,YBSM19,YBSM20, YBSM21,YBSM22,YBSM23,YBSM24,YBSM25,YBSM26,YBSM27,YBSM28,YBSM29, JSQYE0,JSHYE0,YBZXLB,YBLB00,FBBH00,YBBRLB, FKJSLX,--分开结算类型 空值正常结算 FYCF_ZF:拆分_自费 FYCF_YB:拆分_医保 FYCF_YE:拆分_婴儿 FYCF_MQ:拆分_母亲 FKLSH0,--分开结算流水号 FKJZDH,--分开结算单号 FKJZJE,--分开结算金额 YBBXZE,--医保报销总额(DLTCJJ+DLZHZF+YBQTZE+DLSYBX+DLJZFP+DLQTJJ+DLQYBC+MZBZJE+DLSBJE+GWBZJE+DLDBJJ) DLTCJJ,--实际医保统筹基金 DLZHZF,--实际医保账户支付 YBQTZE,--医保其它总额 DLSYBX,--商业保险支付(独立于统筹基金金额) DLJZFP,--精准扶贫医疗(独立于统筹基金金额) DLQTJJ,--其他基金支付(独立于统筹基金金额) DLQYBC,--企业补充(独立于统筹基金金额) MZBZJE,--民政补助金额(独立于统筹基金金额) DLSBJE,--商保支付金额(独立于统筹基金金额) GWBZJE,--公务员补助金额(独立于统筹基金金额) DLDBJJ,--大病基金支付金额(独立于统筹基金金额) CJBTCE,--产检检查费（bkc064）超出个人自付金额后需补退的差额 bkc620 ,--(本地)成员共济账户支付[bkc620] bkc619 ,--(异地)成员共济账户支付[bkc619] bke284 ,--身份认证类型(01:读卡02:融合码06:医保码20:医疗证) bke921 ,--(家庭健康)共济账户扣款人 bke922 ,--(家庭健康)共济账户扣款人金额 jsqdbz ,--医保结算单上传标志(1:已上传 2:已修改) ybtype ,--医保接口类型[-1:自费 0:福建版本 1:国家基线版] med_type ,--[国家医保]医疗类别(aka130) psn_type ,--[国家医保]人员类别(bkc001) medins_setl_id ,--[国家医保]医药机构结算ID clr_optins ,--[国家医保]清算经办机构 clr_way ,--[国家医保]清算方式 clr_type ,--[国家医保]清算类别 fulamt_ownpay_amt ,--[国家医保]全自费金额 overlmt_selfpay ,--[国家医保]超限价自费费用 preselfpay_amt ,--[国家医保]先行自付金额 inscp_scp_amt ,--[国家医保]符合政策范围金额 hifmi_pay ,--[国家医保]居民大病保险资金支出 hifob_pay ,--[国家医保]职工大额医疗费用补助基金支出 acct_mulaid_pay ,--[国家医保]个人账户共济支付金额 pool_prop_selfpay ,--[国家医保]基本医疗保险统筹基金支付比例 hifdm_pay ,--[国家医保]伤残人员医疗保障基金支出 hosp_part_amt --[国家医保]医院负担金额 ) values(ad_JZDH00,ad_BRID00,ls_MZID00,LS_JZB_BRZJE0,LS_JZB_JZJE00,LS_JZB_GFJE00,LS_JZB_HJJE00,LS_JZB_JMJE00,LS_JZB_GRZHZF,LS_JZB_TCJJZF,LS_JZB_ZFJE00,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JMYY00,0, 0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0, null,null,null,null,null,'-1',null,null,null,null, null,null,null,null,null,null,null,null,null,null, null,null,null,null,null,null,null,null,null, nvl(LS_JSQYE0,0),nvl(LS_JSHYE0,0), LS_YBZXLB,LS_YBLB00,LS_FBBH00,LS_YBBRLB, LS_FKJSLX,--分开结算类型 FYCF_ZF:自费处方 FYCF_ZC:正常处方 ZFJS:自费结算 YBJS:医保结算 CXJS:重新结算 LS_FKLSH0,--分开结算流水号 LS_FKJZDH,--分开结算单号 LS_FKJZJE,--分开结算金额 LS_YBBXZE,--医保报销总额(DLTCJJ+DLZHZF+YBQTZE+DLSYBX+DLJZFP+DLQTJJ+DLQYBC+MZBZJE+DLSBJE+GWBZJE+DLDBJJ) LS_DLTCJJ,--实际医保统筹基金 LS_DLZHZF,--实际医保账户支付 LS_YBQTZE,--医保其它总额 LS_DLSYBX,--商业保险支付(独立于统筹基金金额) LS_DLJZFP,--精准扶贫医疗(独立于统筹基金金额) LS_DLQTJJ,--其他基金支付(独立于统筹基金金额) LS_DLQYBC,--企业补充(独立于统筹基金金额) LS_DLMZBZ,--民政补助金额(独立于统筹基金金额) LS_DLSBJE,--商保支付金额(独立于统筹基金金额) LS_GWBZJE,--公务员补助金额(独立于统筹基金金额) LS_DLDBJJ,--大病基金支付金额(独立于统筹基金金额) LS_CJBTCE, --产检检查费（bkc064）超出个人自付金额后需补退的差额 LS_bkc620 ,--(本地)成员共济账户支付[bkc620] LS_bkc619 ,--(异地)成员共济账户支付[bkc619] LS_bke284 ,--身份认证类型(01:读卡02:融合码06:医保码20:医疗证) LS_bke921 ,--(家庭健康)共济账户扣款人 LS_bke922 ,--(家庭健康)共济账户扣款人金额 LS_jsqdbz ,--医保结算单上传标志(1:已上传 2:已修改) LS_ybtype ,--医保接口类型[-1:自费 0:福建版本 1:国家基线版] LS_med_type ,--[国家医保]医疗类别(aka130) LS_psn_type ,--[国家医保]人员类别(bkc001) LS_medins_setl_id ,--[国家医保]医药机构结算ID LS_clr_optins ,--[国家医保]清算经办机构 LS_clr_way ,--[国家医保]清算方式 LS_clr_type ,--[国家医保]清算类别 LS_fulamt_ownpay_amt ,--[国家医保]全自费金额 LS_overlmt_selfpay ,--[国家医保]超限价自费费用 LS_preselfpay_amt ,--[国家医保]先行自付金额 LS_inscp_scp_amt ,--[国家医保]符合政策范围金额 LS_hifmi_pay ,--[国家医保]居民大病保险资金支出 LS_hifob_pay ,--[国家医保]职工大额医疗费用补助基金支出 LS_acct_mulaid_pay ,--[国家医保]个人账户共济支付金额 LS_pool_prop_selfpay ,--[国家医保]基本医疗保险统筹基金支付比例 LS_hifdm_pay ,--[国家医保]伤残人员医疗保障基金支出 LS_hosp_part_amt --[国家医保]医院负担金额 ); --YBSM06 结算类型-1:自费0:医保1生育2:工伤3:保健 LS_ERRMSG :='结束天剑病人结账表信息'; --定位错误信息 if ad_MSTYE0 is not null then Update SF_JZB000 set MSTYE0=to_number(ad_MSTYE0) where BRID00=ad_BRID00 and JZDH00=ad_JZDH00; end if; if ad_MSTID0 is not null then begin select ID0000,JYJE00,YKTYE0 into LS_MSTID0,LS_MSTZF0,LS_MSTYE0 from SF_YKTKFB where BRID00=ad_BRID00 and ID0000=ad_MSTID0; exception when others then LS_MSTID0:=''; LS_MSTZF0:=0; LS_MSTYE0:=0; end; update SF_YKTKFB set JZDH00=ad_JZDH00 where BRID00=ad_BRID00 and ID0000=ad_MSTID0; update SF_JZB000 set MSTID0=LS_MSTID0,MSTZF0=LS_MSTZF0,MSTYE0=LS_MSTYE0 where BRID00=ad_BRID00 and JZDH00=ad_JZDH00; end if; LS_ERRMSG :='修改结账表JMJE00'; --定位错误信息 update SF_JZB000 a set JMJE00=(select nvl(sum(JMJE00),0) from sf_fymx00 b,sf_brfy00 c where b.djh000=c.djh000 and c.jzdh00=a.jzdh00) where a.JZDH00=ad_JZDH00; --3、填写病人票据信息表的内容 if TRIM(LS_SFCFFP)='N' then --2012.03.23 注释掉,条件D.JZDH00 = ad_JZDH00已满足 -- if as_tsbzbh='0' then if trim(as_ID0000) is not null then --通过过程:SP_SF_FYJS00_DJH000整理数据直接用中间表SF_FYXX00_DJH000作为条件 LS_ERRMSG :='Insert into SF_JZMX00_0'; --定位错误信息 Insert into SF_JZMX00(JZDH00,XMBH00,XMMC00,XMJE00,JMJE00,JMSHR0,JMYY00,ZFBL00,QZFJE0) select ad_JZDH00,B.FPXMID,B.XMMC00,sum(A.HJJE00),sum(a.JMJE00),null,null,1,sum(a.QZFJE0) from SF_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,SF_BRFY00 D where A.XMBH00 = C.SFXMID and C.MZFPID = B.FPXMID and A.DJH000 = D.DJH000 and D.JZDH00 = ad_JZDH00 and D.JFLBID = ad_JFLBID and D.BRID00 = ad_BRID00 and exists (select 1 from SF_FYXX00_DJH000 AA where AA.BRID00=D.BRID00 and AA.DJH000=D.DJH000 and AA.BRID00=ad_BRID00 and AA.ID0000 = as_ID0000) group by B.FPXMID,B.XMMC00; else LS_ERRMSG :='Insert into SF_JZMX00_1'; --定位错误信息 Insert into SF_JZMX00(JZDH00,XMBH00,XMMC00,XMJE00,JMJE00,JMSHR0,JMYY00,ZFBL00,QZFJE0) select ad_JZDH00,B.FPXMID,B.XMMC00,sum(A.HJJE00),sum(a.JMJE00),null,null,1,sum(a.QZFJE0) from SF_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,SF_BRFY00 D where A.XMBH00 = C.SFXMID and ((ls_ICKSFCYJS='N' AND A.XMBH00<>LS_IC_SFDM) OR (ls_ICKSFCYJS='Y')) and C.MZFPID = B.FPXMID and A.DJH000 = D.DJH000 and D.JZDH00 = ad_JZDH00 and D.JFLBID = ad_JFLBID --JFLBID=1是门诊预交金账户 and D.BRID00 = ad_BRID00 and D.CZRQ00 >= as_KSRQ00 and D.CZRQ00 <= as_JSRQ00 and (D.MZH000 = as_GHH000 or as_GHH000 = '0' or (D.MZH000 = '0' and LS_SF_YBFYJS_MZH000<>'0') ) group by B.FPXMID,B.XMMC00; end if; if as_SFDYFP='Y' then LS_ERRMSG :='Insert into SF_PJSYQK_0'; --定位错误信息 Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,WBPJLY,FPDYBZ,PHLYXZ) select ls_PJH000,PLBH00,PLMC00,decode(LS_PJB_XJJE00,0,'-0',as_YJJXH0),LS_PJB_XJJE00,LS_PJB_ZZJE00,LS_PJB_YHKJE0,ad_JZY000,as_JZYXM0, ls_CZRQ00,ls_CZSJ00,'Y','0',as_XM0000,as_XB0000,ad_BRID00,as_WBPJLY,as_FPDYBZ,LS_PHLYXZ from bm_pjlxb0 where PLBH00 = 1;--PLBH00=1是门诊发票 else LS_ERRMSG :='Insert into SF_PJSYQK_1'; --定位错误信息 Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,WBPJLY,FPDYBZ,PHLYXZ) select ls_PJH000,PLBH00,PLMC00,decode(LS_PJB_XJJE00,0,'-0',as_YJJXH0),LS_PJB_XJJE00,LS_PJB_ZZJE00,LS_PJB_YHKJE0,ad_JZY000,as_JZYXM0, ls_CZRQ00,ls_CZSJ00,'N','0',as_XM0000,as_XB0000,ad_BRID00,as_WBPJLY,as_FPDYBZ,LS_PHLYXZ from bm_pjlxb0 where PLBH00 = 1;--PLBH00=1是门诊发票 LS_ERRMSG :='Insert into SF_PJSYQK_2'; --定位错误信息 Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,FZPJH0,FPDYBZ,WBPJLY,PHLYXZ) select SQ_SF_PJSYQK_PJH000.nextval,PLBH00,PLMC00,0,0,0,0,ad_JZY000,as_JZYXM0, ls_CZRQ00,ls_CZSJ00,'Y','0',as_XM0000,as_XB0000,ad_BRID00,ls_PJH000,as_FPDYBZ,as_WBPJLY,LS_PHLYXZ from bm_pjlxb0 where PLBH00 = 11;--PLBH00=11是门诊结算单 end if; else --3、填写病人票据信息表的内容 --Modify on 2003-04-21,ZJF ADD 判断是否要拆分发票(条件:若SF_JZMX00的自付比率不同,则要拆分) --2012.03.23 注释掉,条件D.JZDH00 = ad_JZDH00已满足 if trim(as_ID0000) is not null then --通过过程:SP_SF_FYJS00_DJH000整理数据直接用中间表SF_FYXX00_DJH000作为条件 LS_ERRMSG :='Insert into SF_JZMX00_2'; --定位错误信息 Insert into SF_JZMX00(JZDH00,XMBH00,XMMC00,XMJE00,JMJE00,JMSHR0,JMYY00,ZFBL00,QZFJE0) select ad_JZDH00,B.FPXMID,B.XMMC00,sum(A.HJJE00),sum(a.JMJE00),null,null,A.ZFBL00,sum(a.QZFJE0) from SF_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,SF_BRFY00 D where A.XMBH00 = C.SFXMID and C.MZFPID = B.FPXMID and A.DJH000 = D.DJH000 and D.JZDH00 = ad_JZDH00 and D.JFLBID = ad_JFLBID and D.BRID00 = ad_BRID00 and exists (select 1 from SF_FYXX00_DJH000 AA where AA.BRID00=D.BRID00 and AA.DJH000=D.DJH000 and AA.BRID00=ad_BRID00 and AA.ID0000 = as_ID0000) group by B.FPXMID,B.XMMC00,A.ZFBL00; else LS_ERRMSG :='Insert into SF_JZMX00_3'; --定位错误信息 Insert into SF_JZMX00(JZDH00,XMBH00,XMMC00,XMJE00,JMJE00,JMSHR0,JMYY00,ZFBL00,QZFJE0) select ad_JZDH00,B.FPXMID,B.XMMC00,sum(A.HJJE00),sum(a.JMJE00),null,null,A.ZFBL00,sum(a.QZFJE0) from SF_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,SF_BRFY00 D where A.XMBH00 = C.SFXMID -- and A.XMBH00<>LS_IC_SFDM and ((ls_ICKSFCYJS='N' AND A.XMBH00<>LS_IC_SFDM) OR (ls_ICKSFCYJS='Y')) and C.MZFPID = B.FPXMID and A.DJH000 = D.DJH000 and D.JZDH00 = ad_JZDH00 and D.JFLBID = ad_JFLBID --JFLBID=1是门诊预交金账户 and D.BRID00 = ad_BRID00 and D.CZRQ00 >= as_KSRQ00 and D.CZRQ00 <= as_JSRQ00 and (D.MZH000 = as_GHH000 or as_GHH000 = '0' or (D.MZH000 = '0' and LS_SF_YBFYJS_MZH000<>'0')) group by B.FPXMID,B.XMMC00,A.ZFBL00; end if; Delete from SF_PJSYMX where PJH000 = ls_PJH000; --旧票据明细删除 Update SF_PJSYQK set XJJE00 = 0,ZZJE00 = 0,YHKJE0 = 0 where FZPJH0 = ls_PJH000; --原值去掉 select NVL(min(ZFBL00),1) into ls_ZFBL00 from SF_JZMX00 where JZDH00 = ad_JZDH00;-- and (ls_FBBH00 <> 5 or (ls_FBBH00 = 5 and instr(XMMC00,'诊察') <= 0)); ls_TMPPJH:=ls_PJH000; --开始的票据号取同组的组号 open CUR_SF_JZMX00_ZFBL00; LOOP fetch CUR_SF_JZMX00_ZFBL00 INTO ls_JZMX00; exit when CUR_SF_JZMX00_ZFBL00%NOTFOUND; --if ls_JZMX00.ZFBL00 <> 0 then--0的不打印出来 if ls_JZMX00.ZFBL00 <> ls_ZFBL00 then -- and ls_ZFBL00 <> 1 then --若NOT (自付比例为零同时该项目又是含有诊金的项目) if ls_JZMX00.ZFBL00 > ls_ZFBL00 then select SQ_SF_PJSYQK_PJH000.nextval into ls_TMPPJH from dual; end if; end if; --end if; if ls_JZMX00.ZFBL00 > ls_ZFBL00 then ls_ZFBL00:= ls_JZMX00.ZFBL00; end if; Insert into SF_PJSYMX(PJH000,XMBH00,XMMC00,XMJE00,XMBZ00,ZFBL00,QZFJE0) values(ls_TMPPJH,ls_JZMX00.XMBH00,ls_JZMX00.XMMC00,ls_JZMX00.XMJE00,0,ls_JZMX00.ZFBL00,ls_JZMX00.QZFJE0); Update SF_PJSYQK set XJJE00 = NVL(XJJE00,0)+ls_JZMX00.XMJE00*ls_JZMX00.ZFBL00, ZZJE00 = NVL(ZZJE00,0)+ls_JZMX00.XMJE00 - ls_JZMX00.XMJE00*ls_JZMX00.ZFBL00 where PJH000 = ls_TMPPJH; if SQL%NOTFOUND then Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,FZPJH0,BRID00,FPDYBZ,PHLYXZ) values(ls_TMPPJH,1,'门诊发票',SF_SF_GETPJXH(ad_JZY000,1),ls_JZMX00.XMJE00*ls_JZMX00.ZFBL00,ls_JZMX00.XMJE00-ls_JZMX00.XMJE00*ls_JZMX00.ZFBL00, ad_JZY000,as_JZYXM0,ls_CZRQ00,ls_CZSJ00,'Y','0',as_XM0000,as_XB0000,ls_PJH000,ad_BRID00,as_FPDYBZ,LS_PHLYXZ); end if; end LOOP; close CUR_SF_JZMX00_ZFBL00; end if; if SQL%NOTFOUND then as_YHMSG0:='系统字典不完整,所需的票据类型不存在,操作失败！'; as_SYSMSG:=SQLERRM||'票据类型(门诊发票)不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 2)'; rollback; return; end if; if as_CZBZ00 <> '0' then --若病人要清帐,检查一下病人是否还有未结算的费用,同时注意不能添加时间的条件 select count(*) into ls_COUNT0 from sf_fymx00 b,SF_BRFY00 a where a.djh000=b.djh000 and ((ls_ICKSFCYJS='N' and b.xmbh00 <>LS_IC_SFDM) or (ls_ICKSFCYJS='Y')) and a.BRID00 = ad_BRID00 and a.JFLBID = ad_JFLBID and a.JZDH00 +0 = 0; -- and CZRQ00 >= as_KSRQ00 and CZRQ00 <= as_JSRQ00 if ls_COUNT0 > 0 then as_YHMSG0:='病人还有未结算的费用,不能进行清账处理！'; as_SYSMSG:=SQLERRM||'提示:将所有未结算的费用结算后,再进行清账处理！'; rollback; return; end if; --漳州平和医院要求转帐(实际上为医保卡)的预交金不能退，只能作废 MZSF-20050428-006 select count(*) into ls_COUNT0 from xt_yyxx00 where TRIM(YYJC00)='平和县医院' or YYJC00 like '%邵武%人民%'; if ls_COUNT0>0 then select count(*) into ls_COUNT0 from sf_brzhxx where BRID00 = ad_BRID00 and JFLBID = ad_JFLBID and ZZYE00>0; if ls_COUNT0>0 then as_YHMSG0:='病人还有转帐余额,不能进行清账处理！'; rollback; return; end if; end if; end if; --5、若是医保病人，还应该添加病人的个人账户,统筹基金额的交费信息 if as_SFYB00 = 'Y' then LS_ERRMSG :='开始写入医保其它缴费信息'; --定位错误信息 select SQ_SF_BRJFB0_DJH000.nextval into ad_GRDJH0 from dual ; --个人账户对应的单据号 select SQ_SF_BRJFB0_DJH000.nextval into ad_TJDJH0 from dual ; --统筹基金对应的单据号 select SQ_SF_BRJFB0_DJH000.nextval into ad_SBZHDH from dual ; --商保个人帐户单据流水号 select SQ_SF_BRJFB0_DJH000.nextval into ad_SBTJDH from dual ; --商保统筹基金单据流水号 select SQ_SF_BRJFB0_DJH000.nextval into ad_jjdjh0 from dual ; --保健基金支付单据流水号 select SQ_SF_BRJFB0_DJH000.nextval into ad_SYDJH0 from dual ; --商业保险支付单据号 select SQ_SF_BRJFB0_DJH000.nextval into ad_SBDJH0 from dual ; --商保基金支付单据号(省属三家市属九家医疗机构) select SQ_SF_BRJFB0_DJH000.nextval into ad_BJDJH0 from dual ; --保健基金支付单据号(省属三家市属九家医疗机构) select SQ_SF_BRJFB0_DJH000.nextval into ad_GWYDJH from dual ; --公务员补助单据号 Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) values(ad_GRDJH0,ad_BRID00,ad_JFLBID,ad_GRZHZF,3,'医保个人账户',as_ZPZH00,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JZYXM0,0,'0',0,ad_JZDH00,1,ad_GRZHZF); Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) values(ad_TJDJH0,ad_BRID00,ad_JFLBID,ad_TCJJZF,4,'医保统筹基金',as_ZPZH00,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JZYXM0,0,'0',0,ad_JZDH00,1,ad_TCJJZF); Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select ad_SBZHDH,ad_BRID00,ad_JFLBID,0,ZFFSBH,ZFFSMC,as_ZPZH00,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JZYXM0,0,'0',0,ad_JZDH00,1,0 from BM_BRZFFS where ZFFSBH=10; Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select ad_SBTJDH,ad_BRID00,ad_JFLBID,0,ZFFSBH,ZFFSMC,as_ZPZH00,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JZYXM0,0,'0',0,ad_JZDH00,1,0 from BM_BRZFFS where ZFFSBH=11; Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select ad_jjdjh0,ad_BRID00,ad_JFLBID,0,ZFFSBH,ZFFSMC,as_ZPZH00,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JZYXM0,0,'0',0,ad_JZDH00,1,0 from BM_BRZFFS where ZFFSBH=6; Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select ad_SYDJH0,ad_BRID00,ad_JFLBID,0,ZFFSBH,ZFFSMC,as_ZPZH00,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JZYXM0,0,'0',0,ad_JZDH00,1,0 from BM_BRZFFS where ZFFSBH=13; Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select ad_SBDJH0,ad_BRID00,ad_JFLBID,0,ZFFSBH,ZFFSMC,as_ZPZH00,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JZYXM0,0,'0',0,ad_JZDH00,1,0 from BM_BRZFFS where ZFFSBH=14; Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select ad_BJDJH0,ad_BRID00,ad_JFLBID,0,ZFFSBH,ZFFSMC,as_ZPZH00,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JZYXM0,0,'0',0,ad_JZDH00,1,0 from BM_BRZFFS where ZFFSBH=15; Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select ad_GWYDJH,ad_BRID00,ad_JFLBID,0,ZFFSBH,ZFFSMC,as_ZPZH00,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JZYXM0,0,'0',0,ad_JZDH00,1,0 from BM_BRZFFS where ZFFSBH=16; LS_ERRMSG :='结束写入医保其它缴费信息'; --定位错误信息 end if; --补充： LS_ERRMSG :='Update SF_BRZHXX.WJSJE0'; --定位错误信息 Update SF_BRZHXX set WJSJE0 = WJSJE0 - ad_BRZJE0 where BRID00 = ad_BRID00 and JFLBID = ad_JFLBID; select XJYE00,ZZYE00 into ad_XJYE00,ad_ZZYE00 from SF_BRZHXX where BRID00 = ad_BRID00 and JFLBID = ad_JFLBID; if ls_SF_ZZJESFYXJTK_SFQY='Y' then --扩展参数SF_ZZJESFYXJTK MZSF-20250103-001 if ad_ZZYE00 <= ls_SF_ZZJESFYXJTK_XZJE then --若转账余额小等于 ls_SF_ZZJESFYXJTK_XZJE 限额，将其金额用现金退还 ad_XJYE00:=ad_XJYE00+ad_ZZYE00; ad_ZZYE00:=0; end if; end if; --结算后更新健康通预扣表的信息DJH000=挂号ID update SF_YKTKFB set DJH000=0,YKTYE0=0,JYJE00=0 where BRID00=ad_BRID00 and MKDM00='0'; --修改三无费别的允许欠费金额 by MZSF-20150910-002 if (LS_FBBH00=VFBBH00_Temp00) and (VYXQFJE_Temp00>0) then update SF_BRZHXX set YXQFJE=VYXQFJE_Temp00 where BRID00=ad_BRID00 and JFLBID=1; end if; --获取结算账户总余额 (参数：0：门诊 1：住院 参数2：BRID00 参数3：1：门诊 2:住院 参数4：0:可用余额 1:总余额 2：现金余额 3:停用金额) begin select SF_SF_GETBRZHYE(0,ad_BRID00,1,1) into LS_JSHYE0 from dual; exception when others then LS_JSHYE0:=0; end; update SF_JZB000 set JSHYE0=nvl(LS_JSHYE0,0) where BRID00=ad_BRID00 and JZDH00=ad_JZDH00; --COMMIT , 放在Delphi中处理 --按结账单号自动执行 MZSF-20190426-002 --取消自动执行 BQ-20190726-001 --自费病人自动执行 MZSF-20200508-004 if (LS_FBBH00=1) or (LS_FBBH00=3 and LS_ybtype='-1') then SP_SF_YJYWZX(ad_BRID00, ad_JZDH00); end if; --转化CARDNO并且写入到IC_YHXX00.CARDNO BY MZSF-20210107-001 LS_ERRMSG :='转化CARDNO并且写入到IC_YHXX00.CARDNO'; --定位错误信息 begin select substrb(SF_SF_MSTCARDNO(A.BRID00,'5.0',trim(A.ICKH00),trim(A.KLDH00),trim(A.ICKSX0),trim(A.ICKNBH),trim(A.KMH000)),1,50) CARDNO, substrb(trim(A.ICKH00),1,50)ICKH00,substrb(trim(A.KMH000),1,50)KMH000 into LS_CARDNO,LS_ICKH00,LS_KMH000 from IC_YHXX00 A where A.BRID00=ad_BRID00 and ZT0000='1'; exception when others then LS_CARDNO:=null; end; --莆田市民卡 if substrb(LS_ICKH00,1,6)='PTYBPT' then LS_CARDNO:=substrb(LS_ICKH00,5,50); end if; if trim(LS_CARDNO) is null and LS_FBBH00=3 and trim(LS_YBKH00) not in ('0') then LS_CARDNO:=substrb(trim(LS_YBKH00),1,50); end if; if trim(LS_CARDNO) is not null and trim(LS_CARDNO) not in ('0') then LS_ERRMSG :='Update IC_YHXX00.CARDNO'; --定位错误信息 Update IC_YHXX00 set CARDNO=LS_CARDNO where BRID00=ad_BRID00 and ZT0000='1' and CARDNO is null; end if; --修改中间表SF_FYXX00_DJH000.JZDH00 if trim(as_ID0000) is not null then LS_ERRMSG :='修改中间表SF_FYXX00_DJH000.JZDH00'; --定位错误信息 update SF_FYXX00_DJH000 set JZDH00=ad_JZDH00 where brid00=ad_BRID00 and ID0000=as_ID0000; update SF_FYXX00_TEMP00 set JZDH00=ad_JZDH00 where brid00=ad_BRID00 and ID0000=as_ID0000; update SF_FYXX00_TEMPMX set JZDH00=ad_JZDH00 Where brid00=ad_BRID00 and ID0000=as_ID0000; end if; if nvl(trim(SF_SF_TYZD00('医保追溯码配置','YBZSMSFQY')),'0') = '1' then begin SP_SF_YPZSM0_XSTHCL( Pywlx00=>2, --业务类型, 1退货2销售 Pjzdh00=>ad_JZDH00, --结算单号 Pczy000=>ad_JZY000, --操作员 Pczyxm0=>as_JZYXM0, --操作员姓名 Pczyks0=>ad_JZYKS0, --操作员科室 Pinxml0=>null, --xml入参 Pcommit=>'N', --是否提交 Poutxml=>ls_OUTXML, --xml出参 Pzxcgbz=>ls_ZXCGBZ, --执行成功标志 1成功 0失败 Ptsxx00=>ls_TSXX00, --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00=>ls_XXXX00 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ); exception when others then ls_ZXCGBZ := 0; ls_TSXX00 := '执行药品追溯码销售退货处理过程(SP_SF_YPZSM0_XSTHCL)失败！'; ls_XXXX00 := ls_TSXX00; end; end if; EXCEPTION when NO_DATA_FOUND then as_YHMSG0:=substrb('系统没有找到有效的就诊卡号！错误位置:'||LS_ERRMSG,1,220); as_SYSMSG:=SQLERRM||'卡号='||as_ICKH00; rollback; when OTHERS then as_YHMSG0:=substrb('出错原因不详,请记录此信息并和系统管理员联系！错误位置:'||LS_ERRMSG,1,220); as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_BRFY00_JS0000('||as_YBDJH0||','||as_GHH000||','||as_ICKH00||','|| to_char(ad_BRID00)||','||to_char(ad_JFLBID)||','||as_XM0000||','||as_XB0000||','||to_char(ad_BRFB00)||','|| to_char(ad_BRZJE0)||','||to_char(ad_JZJE00)||','||to_char(ad_GFJE00)||','||to_char(ad_HJJE00)||','||to_char(ad_JMJE00)||','|| as_ZPZH00||','||to_char(ad_GRZHZF)||','||to_char(ad_TCJJZF)||','||to_char(ad_ZFJE00)||','||to_char(ad_JZY000)||','|| as_JZYXM0||','||to_char(ad_JZYKS0)||','||as_JMYY00||','||as_YJJXH0||','||as_CZBZ00||','|| as_SFYB00||',ad_XJYE00,ad_ZZYE00,ad_JZDH00,ad_ZFDJH0,ad_GRDJH0,ad_TJDJH0,as_YHMSG0,as_SYSMSG)',1,150); rollback; end; / show error; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%