-- Start of DDL Script for Procedure SD_HOSPITAL.SP_ZY_BRFY00_JS0000 -- Generated 2021/3/24 10:49:34 from SD_HOSPITAL@zpxzyy CREATE OR REPLACE PROCEDURE sp_zy_brfy00_js0000 ( as_qsdjh0 in number, as_zzdjh0 in number, as_YBDJH0 IN CHAR, --医保单据流水号 as_ZYH000 IN CHAR, --住院号 ad_ZYID00 IN NUMBER, --病人住院信息表ID 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':出院结算 as_SFYB00 IN CHAR, --是否医保病人结算,'Y:医保病人 as_JFDJHS IN CHAR, --本次结算所使用的预交金的对应的交费单据号 as_JSZZRQ IN CHAR, --中途结算截止日期 as_TSBZBH IN CHAR, --结算的特殊病种编号 ad_TJDJH0 OUT NUMBER, --统筹基金的单据流水号 ad_GZDJH0 OUT NUMBER, --个人账户的单据流水号 ad_SBZHDH OUT NUMBER, --商保个人帐户单据流水号 ad_SBTJDH OUT NUMBER, --商保统筹基金单据流水号 ad_XJDJH0 OUT NUMBER, --病人现金的退款单据号 ad_ZZDJH0 OUT NUMBER, --病人转账的退款单据号 ad_XJYE00 OUT NUMBER, --病人账户上的现金余额 ad_ZZYE00 OUT NUMBER, --病人账户上的转账余额 ad_JZDH00 OUT NUMBER, --病人结账单号 ad_JJDJH0 OUT NUMBER, --保健基金单据流水号 ad_SYDJH0 OUT NUMBER, --商业保险支付单据流水号 ad_SBDJH0 OUT NUMBER, --商保基金支付单据号 ad_BJDJH0 OUT NUMBER, --保健基金支付单据号 ad_GWYDJH OUT NUMBER, --保健基金支付单据号 ad_YYZFDH OUT NUMBER, --医院支付单号 as_YHMSG0 OUT VARCHAR, --存储过程提示的错误信息 as_SYSMSG OUT VARCHAR, --系统提示的错误信息 ad_YEXM00 in char default '全部', --婴儿姓名 ad_DBZBM0 in char default '', --单病种编码 ad_XFBXJE in number default 0 , --血费报销金额 ad_XFBXID in char default '', --血费报销id ad_YBJKLX in varchar2 default '', --接口类型 -1:自费结算 0普通医保 1生育医保 2：工伤 3保健医保 ad_DDLSH0 in varchar2 default '', --订单流水号 as_WBPJLY in varchar2 default '', --外部票据来源 as_BDYFP0 in varchar2 default 'N',--擅自出院病人结算时不打印发票,Y不打印,N打印,默认N as_SFXYB0 in varchar2 default '0' ,--是否新医保 0:否 1:是 as_FKJSLX in varchar2 default '' ,--分开结算类型 空值正常结算 FYCF_ZF:拆分_自费 FYCF_YB:拆分_医保 FYCF_YE:拆分_婴儿 FYCF_MQ:拆分_母亲 FYCF_SND:拆分_上年度 FYCF_BND:拆分_本年度 ad_FKJZDH in number default 0 ,--分开结算单号 as_FKLSH0 in varchar2 default '' ,--分开结算流水号 ad_FKJZJE in number default 0 ,--分开结算金额 as_INTXML in varchar2 default '' --备用参数xml ) -- MODIFICATION HISTORY -- Person Date Comments -- xzw 2007.06.20 传递参数增加保健基金单据流水号; -- jlg 2007.08.07 增加商业保险支付金额的处理ZYSF-20070806-004 -- xzw 2007.09.07 对打印正式发票的情况进行处理;把得到的收费员票据序号的函数由SF_SF_GETPJXH->SF_ZY_GETPJXH -- xzw 2007.10.14 现在只有提示"是否打印正式发票"时才对XT_XTCS00中的ZY_SFDYZSFP进行修改， -- xzw 2007.01.30 对查询参数ZY_SFTSDYZSFP的语句进行保护，以免有的医院没有参数ZY_SFTSDYZSFP，结算报错; -- xzw 2008.04.25 增加商保基金支付金额和保健基金支付金额的处理ZYSF-20080425-002 -- xzw 2008.07.30 10G修改，使程序能够在10G库上正常操作 -- xzw 2008.08.27 把10G修改的恢复到修改之前 -- qks 2009.09.16 增加参数ZY_CYJSTKAYJJ:出院结算退预交金是否以预交金方式转入 ZYSF-20090914-001 -- xzw 2009.10.30 10G修改，使程序能够在10G库上正常操作 -- qks 2009.11.30 修正结帐表的减免金额 -- qks 2009.12.21 修正医保病人ad_ZZYE00 -- qks 2010.02.20 判定病人收费项目数量是否存在负数 -- xzw 2010.03.04 增加传入参数ad_GWYDJH -- qks 2010.04.01 判定病人收费项目数量是否存在负数:影响到速度,注释掉,医保病人在过程SP_ZY_YBJK00_FJ0000有判断数量负数 -- qks 2010.04.29 bug:当新农合中心返回的统筹基金大于病人的合计金额时,结算不了 ZYSF-20100429-001 -- qks 2010.05.28 当参数ZY_CYJSTKAYJJ=Y时,出院结算时,zy_jzb000.yszje0不做修改; 当ZY_JSTKAYJJFS=Y时,只修改非医保病人zy_jzb000.yszje0 -- qks 2010.06.07 修改：判定病人未结的费用主表和明细表是否一致 -- zhangwz 2010.06.30 增加判断对于住院发票内容是否有小于0的数据. -- zhangyc 2011.03.15 公费病人拆分发票时删除ZY_JZMX00 (XMJE00=0 and ZFJE00=0 and QZFJE0=0)数据 -- zhangyc 2011.06.20 增加传入参数AD_YEXM00(婴儿姓名) -- zhangyc 2011.11.30 增加传入参数AD_DBZBM0(单病种编码)by ZYSF-20111128-001 -- liuj 2012.09.26 过程以brid00条件查询，而费用结算界面显示的费用以zyid00条件查询，在某种情况下导致数据不一致 for ZYSF-20120925-003 -- liuj 2013.04.16 原来如果本次结算费用合计为0，直接UPDATE ZY_BRFY00.JZDH00=1后返回而不往结账表插数据, --现要改为 插入zy_jzb000,zy_jzmxxm记录，并修改ZY_BRFY00.jzdh00 for ZYSF-20130416-001 -- liuj 2013.07.05 修改判断本次结算费用为0判断条件及插入zy_jzmxxm数据条件 for ZYSF-20130705-002 --zhangyc 2013.07.24 参数 ZY_YKHTKJKTS开启 修改缴费表结账单号 by ZYSF-20130701-001 --zhangyc 2014.01.06 ZY_JZB000表增加字段XZQH00 BY ZYSF-20140102-004 --zhangyc 2014.07.10 增加入参数ad_XFBXJE,ad_XFBXID by ZYSF-20140710-001 --zhangyc 2014.10.15 增加参数ZY_ZZJESFXJTC控制转账金额是否转为现金金额退还 Y:是 N:否 by ZYSF-20140930-002 --zhangyc 2015.04.08 修正插入ZY_JZB000.ZFBL00 不要group by BY ZYSF-20150312-001 --zhangyc 2016.01.20 单病种修改 by ZYSF-20160108-001 --zhangyc 2016.04.25 增加团体住院功能 by ZYSF-20151227-001 --zhangyc 2016.11.25 注释掉合计金额为0 插入PJH000=-1 数据 by ZYSF-20161026-003 --zhangyc 2016.12.28 修正参数ZY_JSTKAYJJFS,ZY_ZZJESFXJTC=Y by ZYSF-20161217-001 --zhangyc 2017.03.15 修改单病种 by ZYSF-20170206-001 --zhangyc 2017.04.06 增加参数ZY_DBZZFBRJKQY控制 by ZYSF-20170405-001 --zhangyc 2017.06.12 增加微信支付和支付宝支付 by ZYSF-20170508-001 --zhangyc 2017.08.25 新农合跨省结算修正 by ZYSF-20170825-002 --zhangyc 2017.08.30 修改取消结算单号 by ZYSF-20170829-004 --zhangyc 2017.11.21 增加出院结算退款 by ZYSF-20171123-002 --zhangy 2018.04.10 榕医通修改(28:平台支付) by ZYSF-20180411-002 --zhangyc 2018.05.11 修正精准扶贫 1:是 0:否 by ZYSF-20180511-003 --zhangyc 2018.05.30 修改自费单病种逃跑结算bug by ZYSF-20180530-003 --zhangyc 2019.04.25 修改中途结算补交发票未显示补交金额 by ZYSF-20190425-003 --qks 2019.06.03 非医保病人收费项目合计金额小于0,则不允许结算; by ZYSF-20181217-004 --zhangyc 2019.08.08 精准扶贫接口改造(结账表写入：DBZMC0,BZBMLB) by ZYSF-20190808-005 --zhangyc 2019.12.25 结账表增加写入BKC001_MC、AAE140、AAE140_MC、BKE174、BKE174_MC by ZYSF-20191225-002 --dsm 2019.01.04 ZY_PJSYQK.XJJE00为0时，PHLYXZ=N，否则会提示没有领用票据 for ZYSF-20190102-002 --zhangyc 2020.01.15 婴儿结算时(婴儿结算XJFKJE=自负金额) by ZYSF-20200108-001 --qiulf 2020.06.19 增加入参ad_WBPJLY外部票据类型 by ZYSF-20200618-001 --qiulf 2020.06.23 母婴结算单病种允许分开结算 by ZYSF-20200518-001 --linbin 2020.07.17 增加入参as_BDYFP0控制 by ZYSF-20200630-004 --qiulf 2020.09.27 增加择日转账业务处理LS_ZRZZYJJ,以现金方式退出 by ZYSF-20200927-004 --zhangyc 2020.10.16 增加写入表回收表ZY_PJHSQK by ZYSF-20201019-002 --zhangyc 2020.12.10 ZY_JZB000保存一些新的字段(FKJSLX,FKLSH0,FKJZDH,FKJZJE,YBBXZE,DLTCJJ,DLZHZF,YBQTZE,DLSYBX,DLJZFP,DLQTJJ,DLQYBC,MZBZJE,DLSBJE,GWBZJE,DLDBJJ,CJBTCE ) by ZYSF-20201210-002 -- zhangyc 2020.12.28 增加医保跨年拆分业务处理(表ZY_JSBCMX取值) by ZYSF-20201229-004 as ls_PJH000 ZY_PJSYQK.PJH000%TYPE; --票据流水号 ls_PJH001 ZY_PJSYQK.PJH000%TYPE; --票据流水号 ls_TMPPJH ZY_PJSYQK.PJH000%TYPE; --临时使用的票据号 ls_FZPJH0 ZY_PJSYQK.PJH000%TYPE; ls_ZYFPID BM_YYSFXM.ZYFPID%TYPE; --住院发票ID ls_SFXMID BM_YYSFXM.SFXMID%TYPE; --收费项目ID ls_ZYE000 ZY_BRZHXX.ZYE000%TYPE; --病人账户总余额 ls_CYQKJE ZY_BRZHXX.ZYE000%TYPE; --病人账户总余额 ls_XJJFJE ZY_BRJFB0.JFJE00%TYPE; --病人现金交费金额 ls_ZZJFJE ZY_BRJFB0.JFJE00%TYPE; --病人转账交费金额 ls_HJJE00 ZY_BRFY00.HJJE00%TYPE; --合计金额 ls_HJZFJE ZY_BRFY00.ZFJE00%TYPE; --自付金额 ls_JZJE00 ZY_BRFY00.JZJE00%TYPE; --记帐金额 ls_GFJE00 ZY_BRFY00.GFJE00%TYPE; --公费金额 ls_YBZYH0 ZY_BRXXB0.YBZYH0%TYPE; --医保住院号 ls_SFJSDW IC_YBBRLB.SFJSDW%TYPE; --是否结算单位标志 ls_GRZHZF ZY_JZB000.GRZHZF%TYPE; --个人医疗账户支付(医保返回) ls_TCJJZF ZY_JZB000.TCJJZF%TYPE; --统筹基金支付(医保返回) ls_xjje00 ZY_BRFY00.HJJE00%TYPE; --四舍五入金额 ls_HSHJJE ZY_BRFY00.HJJE00%TYPE; --伙食合计金额 ls_CYYTK0 ZY_BRZHXX.ZYE000%TYPE; --出院应退款 ls_JZMX00 ZY_JZMX00%ROWTYPE; ls_ZFBL00 ZY_PJSYMX.ZFBL00%TYPE; ls_ZPH000 ZY_BRJFB0.ZPH000%TYPE; ls_ZPKHYH ZY_BRJFB0.ZPKHYH%TYPE; ls_ZPDWMC ZY_BRJFB0.ZPDWMC%TYPE; ls_plbh00 BM_PJLXB0.PLBH00%TYPE; ls_TMPDJH VARCHAR2(20); ls_COUNT0 NUMBER(10); --NUMBER(5); ls_JFDJHS VARCHAR2(2000); ls_CZRQ00 CHAR(8); ls_CZSJ00 CHAR(8); ls_SSWUTSCL char(4); LS_SFCFFP char(4); --是否拆分发票 LS_SFFSXM CHAR(4); --是否有伙食项目 LS_SF_ZY_FFJJ CHAR(4); ls_recode number(8); ZY_JSTKAYJJFS varchar2(4); ls_YJJPJ0 varchar2(4); ls_bqh00 zy_brxxb0.dqbq00%TYPE; ls_FYQSRQ ZY_JZB000.FYQSRQ%TYPE; ls_FYJZRQ ZY_JZB000.FYJZRQ%TYPE; ZY_SFTSDYZSFP varchar2(4); ls_zypjmc BM_FPXM00.XMMC00%TYPE; --发票名称 ZY_CYJSTKAYJJ varchar2(4); ls_YEXM00 varchar2(40);--婴儿姓名 ls_YEXM01 varchar2(40);--婴儿姓名 ls_ZY_YKHTKJKTS xt_xtcs00.value0%TYPE; LS_ZY_ZZJESFXJTC xt_xtcs00.value0%TYPE; LS_XZQH00 BM_BRXXB0.XZQH00%type; LS_YBKH00 BM_BRXXB0.YBKH00%type; LV_ZZYE00 NUMBER(12,4); LV_ZZJEBZ ZY_BRJFB0.BZ0000%type; LS_TTZHYE number(12,2); --团体可用余额 LS_GRZHYE number(12,2); --团体个人账户余额 LS_TTHZJE number(12,2); --团体需要划账金额 LS_TTBRID number(10); --团体病人ID LS_TTZYH0 char(12); LS_TTZYID number(10); LS_TTLX00 char(1); --团体类型 1:团体信息 0或null 团体成员信息 LS_SFTTCY number(10); --是否团体成员 0:不是 1:团体主账户 2:团体成员账户 LS_TTZDJH number(10); -- LS_TTDJH0 number(10); -- LS_TTZPJH number(10); -- LS_TTPJH0 number(10); -- LS_TTHJJE ZY_BRFY00.HJJE00%type; --合计金额 LS_BRXM00 BM_BRXXB0.BRXM00%TYPE; LS_BRXB00 BM_BRXXB0.BRXB00%TYPE; LS_BRXMTT BM_BRXXB0.BRXM00%TYPE; LS_BRXBTT BM_BRXXB0.BRXB00%TYPE; LS_FBBH00 BM_BRXXB0.FBBH00%TYPE; LS_DWID00 varchar2(10); MsgStr varchar2(200); V_YXQFJE ZY_BRZHXX.YXQFJE%TYPE; V_XJYE00 ZY_BRZHXX.XJYE00%TYPE; V_ZZYE00 ZY_BRZHXX.ZZYE00%TYPE; V_ZYE000 ZY_BRZHXX.ZYE000%TYPE; V_YKZYE0 ZY_BRZHXX.YKZYE0%TYPE; LS_YBJKLX varchar2(30);--接口类型 -1:自费结算 0普通医保 1生育医保 2：工伤 3保健医保 LS_ZFBZQY varchar2(100);--自费病人启用单病种 Y:启用 N:不启用 LS_DBZBM0 ZY_BRXXB0.DBZBM0%type; LS_DBZMC0 ZY_BRXXB0.DBZMC0%type; LS_BZBMLB ZY_BRXXB0.BZBMLB%type; LS_YBYL04 ZY_JZB000.YBYL04%type; LS_YBYL05 ZY_JZB000.YBYL05%type; LS_DBZCWF ZY_JZB000.YBYL33%type;--单病种超标床位费用 LS_DBZHCF ZY_JZB000.YBYL34%type;--单病种除外耗材费用 LS_YSZJE0 ZY_JZB000.YSZJE0%type; LS_XJYE00 ZY_JZB000.XJYE00%type; LS_ZFJE00 ZY_JZB000.ZFJE00%type; LS_DDLSH0 varchar2(32); LS_ZXZT00 number(12); LS_TCXJDH number(12); --现金退出单号 LS_TCXJJE number(12,2); --现金退出金额 LS_TCZZDH number(12); --转账退出单号 LS_TCZZJE number(12,2); --转账退出金额 LS_XJDJH0_CYJS number(12); --现金单据号（出院结算） LS_XJYE00_CYJS number(12,2); --现金余额（出院结算） LS_ZZDJH0_CYJS number(12); --转账单据号（出院结算） LS_ZZYE00_CYJS number(12,2); --转账余额（出院结算） LS_XJDJH0_TPJS number(12); --现金单据号（逃跑结算） LS_XJYE00_TPJS number(12,2);--现金余额（逃跑结算） LS_ZZDJH0_TPJS number(12); --转账单据号（逃跑结算） LS_ZZYE00_TPJS number(12,2); --转账余额（逃跑结算） LS_SFTPJS varchar2(10); --是否逃跑结算 Y:是 N:否 BM_bkc001_mc ZY_BRXXB0.bkc001_mc%type;--人员待遇类别名称(城乡居民、普通在职人员等等) BM_aae140 ZY_BRXXB0.aae140%type;--险种类型编码:310 城镇职工基本医疗保险 320:公务员医疗补助 380:新型农村合作医疗 390:城乡居民基本医疗保险 410:工伤保险 510:生育保险 520:机关生育 BM_aae140_mc ZY_BRXXB0.aae140_mc%type;--险种类型名称:城乡居民基本医疗保险、城镇职工基本医疗保险等等 LS_JSQYE0 ZY_JZB000.JSQYE0%type;--结算前账户余额 LS_JSHYE0 ZY_JZB000.JSQYE0%type;--结算后账户余额 LS_ZRZZYJJ number(12,2);--择日转账预交金 LS_FKJSLX ZY_JZB000.FKJSLX%type;--分开结算类型 空值正常结算 FYCF_ZF:拆分_自费 FYCF_YB:拆分_医保 FYCF_YE:拆分_婴儿 FYCF_MQ:拆分_母亲 ZFJS:自费结算 YBJS:医保结算 CXJS:重新结算 LS_FKLSH0 ZY_JZB000.FKLSH0%type;--分开结算流水号 LS_FKJZDH ZY_JZB000.FKJZDH%type;--分开结算单号 LS_FKJZJE ZY_JZB000.FKJZJE%type;--分开结算金额 LS_INTXML varchar2(1000);--备用参数xml LS_YBBXZE ZY_JZB000.YBBXZE%type;--医保报销总额(DLTCJJ+DLZHZF+YBQTZE+DLSYBX+DLJZFP+DLQTJJ+DLQYBC+MZBZJE+DLSBJE+GWBZJE+DLDBJJ) LS_DLTCJJ ZY_JZB000.DLTCJJ%type;--实际医保统筹基金 LS_DLZHZF ZY_JZB000.DLZHZF%type;--实际医保账户支付 LS_YBQTZE ZY_JZB000.YBQTZE%type;--医保其它总额 LS_DLSYBX ZY_JZB000.DLSYBX%type;--商业保险支付(独立于统筹基金金额) LS_DLJZFP ZY_JZB000.DLJZFP%type;--精准扶贫医疗(独立于统筹基金金额) LS_DLQTJJ ZY_JZB000.DLQTJJ%type;--其他基金支付(独立于统筹基金金额) LS_DLQYBC ZY_JZB000.DLQYBC%type;--企业补充(独立于统筹基金金额) LS_DLMZBZ ZY_JZB000.MZBZJE%type;--民政补助金额(独立于统筹基金金额) LS_DLSBJE ZY_JZB000.DLSBJE%type;--商保支付金额(独立于统筹基金金额) LS_GWBZJE ZY_JZB000.GWBZJE%type;--公务员补助金额(独立于统筹基金金额) LS_DLDBJJ ZY_JZB000.DLDBJJ%type;--大病基金支付金额(独立于统筹基金金额) LS_CJBTCE ZY_JZB000.CJBTCE%type;--产检检查费（bkc064）超出个人自付金额后需补退的差额 LS_FKJSQY varchar2(1);--分开结算启用 0:否 8:跨年结算分开结算 LS_CZBZ00 varchar2(2);--操作标志 0:正常结算 1:中途结算 2:年度结算 3:逃跑结算 01:跨年上年度 02:跨年本年度 CURSOR CUR_ZY_JZMX00_ZFBL00 IS SELECT * from ZY_JZMX00 where JZDH00 = ad_JZDH00 AND NVL(ZFBL00,1)<>0 and sfylxm='Y' order by ZFBL00; BEGIN ls_CZRQ00 := to_char(sysdate,'YYYYMMDD'); ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS'); ls_GRZHZF := ad_GRZHZF; ls_TCJJZF := ad_TCJJZF; ad_SBZHDH := 0; --商保个人帐户单据流水号 ad_SBTJDH := 0; --商保统筹基金单据流水号 ad_TJDJH0 := 0; --统筹基金的单据流水号 ad_GZDJH0 := 0; --个人账户的单据流水号 ls_CYQKJE := 0; --设置病人的出院欠款金额 ls_CYYTK0 := 0; ad_JJDJH0 := 0; ad_SYDJH0 := 0; ad_SBDJH0 := 0; ad_BJDJH0 := 0; ad_GWYDJH := 0; LS_YBJKLX:=substrb(ad_YBJKLX,1,30);--接口类型 -1:自费结算 0普通医保 1生育医保 2：工伤 3保健医保 LS_YBYL04:=0; LS_YBYL05:=0; LS_DDLSH0:=ad_DDLSH0; LS_TCXJDH:=0;--现金退出单号 LS_TCXJJE:=0;--现金退出金额 LS_TCZZDH:=0;--转账退出单号 LS_TCZZJE:=0;--转账退出金额 LS_XJDJH0_CYJS:=0;--现金单据号（出院结算） LS_XJYE00_CYJS:=0;--现金余额（出院结算） LS_ZZDJH0_CYJS:=0;--转账单据号（出院结算） LS_ZZYE00_CYJS:=0;--转账余额（出院结算） LS_XJDJH0_TPJS:=0;--现金单据号（逃跑结算） LS_XJYE00_TPJS:=0;--现金余额（逃跑结算） LS_ZZDJH0_TPJS:=0;--转账单据号（逃跑结算） LS_ZZYE00_TPJS:=0;--转账余额（逃跑结算） LS_SFTPJS:='N';--是否逃跑结算 Y:是 N:否 LS_JSQYE0 :=0;--结算前账户余额 LS_JSHYE0 :=0;--结算后账户余额 LS_ZRZZYJJ:=0;--择日转账预交金 LS_FKJSLX:=substrb(as_FKJSLX,1,20);--分开结算类型 空值正常结算 FYCF_ZF:拆分_自费 FYCF_YB:拆分_医保 FYCF_YE:拆分_婴儿 FYCF_MQ:拆分_母亲 ZFJS:自费结算 YBJS:医保结算 CXJS:重新结算 LS_FKLSH0:=substrb(as_FKLSH0,1,30);--分开结算流水号 LS_FKJZDH:=nvl(ad_FKJZDH,0);--分开结算单号 LS_FKJZJE:=nvl(ad_FKJZJE,0);--分开结算金额 LS_INTXML:=substrb(as_INTXML,1,1000);--备用参数xml 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_FKJSQY:='0';--分开结算启用 0:否 8:跨年结算分开结算 LS_CZBZ00:='0';--操作标志 0:正常结算 1:中途结算 2:年度结算 3:逃跑结算 01:跨年上年度 02:跨年本年度 if LS_FKJSLX='FYCF_SND' and as_FKLSH0 is not null then --FYCF_SND:拆分_上年度 LS_FKJSQY:='8'; LS_CZBZ00:='01'; end if; if ad_YEXM00 is null then ls_YEXM01:='全部'; else ls_YEXM01:=ad_YEXM00; end if; begin select upper(trim(value0)) into LS_SF_ZY_FFJJ from xt_xtcs00 where name00='SF_ZY_FFJJ';--逢分进角 exception when others then LS_SF_ZY_FFJJ:='N'; end; begin select substrb(value0,1,1) into ls_YJJPJ0 from xt_xtcs00 where name00 = 'ZY_YJJPJ0'; exception when others then ls_YJJPJ0:='N'; end; if trim(ls_YJJPJ0)='Y' then LS_PLBH00:=8; else LS_PLBH00:=4; end if; begin select upper(trim(value0)) into ZY_JSTKAYJJFS from xt_xtcs00 where name00='ZY_JSTKAYJJFS'; exception when others then ZY_JSTKAYJJFS:='N'; end; begin select upper(trim(value0)) into ZY_CYJSTKAYJJ from xt_xtcs00 where name00='ZY_CYJSTKAYJJ'; exception when others then ZY_CYJSTKAYJJ:='N'; end; begin select upper(trim(value0)) into LS_SFFSXM from xt_xtcs00 where name00='ZY_SFFSXM'; exception when others then LS_SFFSXM:='N'; end; begin select upper(trim(value0)) into LS_SFCFFP from xt_xtcs00 where name00='SF_SFCFFP'; exception when others then LS_SFCFFP:='N'; end; LS_SFCFFP:='N';--2021.03.19 begin select trim(value0) into ls_SSWUTSCL from xt_xtcs00 where name00 = 'SF_SSWUTSCL'; exception when others then ls_SSWUTSCL:='N'; end; begin select upper(trim(value0)) into ls_ZY_YKHTKJKTS from xt_xtcs00 where name00='ZY_YKHTKJKTS'; exception WHEN OTHERS THEN ls_ZY_YKHTKJKTS:='N'; end; --住院病人出院结算转账金额是否以现金金额退还 Y:是 N:否 默认值N不转为现金 begin select upper(trim(value0)) into LS_ZY_ZZJESFXJTC from xt_xtcs00 where name00='ZY_ZZJESFXJTC'; exception WHEN OTHERS THEN LS_ZY_ZZJESFXJTC:='N'; end; --ZY_DBZZFBRJKQY 住院单病种自费病人是否启用接口 Y:启用 N:不启用 默认值N:不启用 begin select nvl(max(upper(trim(value0))),'N') into LS_ZFBZQY from xt_xtcs00 where name00='ZY_DBZZFBRJKQY'; exception when others then LS_ZFBZQY:='N'; end; --获取结算前账户总余额 (参数：0：门诊 1：住院 参数2：BRID00 参数3：1：门诊 2:住院 参数4：0:可用余额 1:总余额 2：现金余额 3:停用金额) begin select SF_SF_GETBRZHYE(1,ad_BRID00,2,1) into LS_JSQYE0 from dual; exception when others then LS_JSQYE0:=0; end; select A.BRXM00,A.BRXB00,SFJSDW,A.XZQH00,A.YBKH00,A.TTBRID,A.TTLX00,A.FBBH00 into LS_BRXM00,LS_BRXB00,ls_SFJSDW,LS_XZQH00,LS_YBKH00,LS_TTBRID,LS_TTLX00,LS_FBBH00 from BM_BRXXB0 A,IC_YBBRLB B where A.FBBH00 = B.FBBH00 and A.YBLB00 = B.YBLB00 and A.BRID00 = ad_BRID00; LS_XZQH00:=SF_ZY_YBKHXZQH(LS_XZQH00,LS_YBKH00); begin select YBZYH0,TTZYH0,TTZYID,DWID00,DQBQ00,trim(DBZBM0)DBZBM0,trim(DBZMC0)DBZMC0,BZBMLB,BKC001_MC,AAE140, AAE140_MC into LS_YBZYH0,LS_TTZYH0,LS_TTZYID,LS_DWID00,LS_BQH00,LS_DBZBM0,LS_DBZMC0,LS_BZBMLB,BM_BKC001_MC,BM_AAE140,BM_AAE140_MC from ZY_BRXXB0 where ZYID00 = ad_ZYID00; exception when others then as_YHMSG0:='系统未找到满足条件的出院病人的信息！'; as_SYSMSG:=SQLERRM||'NOT EXISTS(select * from ZY_BRXXB0 where ZYID00 = '||to_char(ad_ZYID00)||')'; rollback; return; end; if LS_BZBMLB is null and LS_DBZBM0 is not null then begin select DBZMC0,BZBMLB into LS_DBZMC0,LS_BZBMLB from VW_ZY_DBZDEB where trim(DBZBM0)=LS_DBZBM0 and YBZXLB=SF_ZY_SFQYDBZJK(ad_ZYID00); exception when others then LS_DBZMC0:=null; LS_BZBMLB:=null; end; end if; if as_SFYB00 = 'Y' and ls_YBZYH0 = '0' THEN --允许欠费出院结算同时又是非医保的病人 select count(*) into ls_COUNT0 from BM_BRZFFS where ZFFSBH = 9; if ls_COUNT0 <> 1 then as_YHMSG0:='系统字典表维护不完整,请先维护系统字典表BM_BRZFFS后再进行本操作,请与系统管理员联系！'; as_SYSMSG:='NOT EXISTS (select * from bm_brzffs where zffsbh = 9 )'||SQLERRM; ROLLBACK; RETURN; end if; end if; --自费病人登记单病种不允许中途结算 if as_CZBZ00='0' and LS_FBBH00=1 and LS_DBZBM0 is not null and trim(ls_YEXM01) in ('全部','母亲') then AS_YHMSG0:='该病人已登记单病种不允许做中途结算！'; AS_SYSMSG:=SQLERRM; rollback; return; end if; --获取是否是团体 0:不是 1:团体主账户 2:团体成员账户 begin select SF_ZY_CHECKTTBR(ad_BRID00,LS_TTBRID,LS_TTLX00) into LS_SFTTCY from dual; exception when others then LS_SFTTCY:=0; end; ---婴儿姓名变量------modify by ZYSF-20110617-002-- if trim(ls_YEXM01) in ('全部','母亲') then ls_YEXM00:=''; else ls_YEXM00:=ls_YEXM01; end if; --判定病人未结的费用主表和明细表是否一致20031119 zhr begin SELECT nvl(max(DJH000),0) into ls_COUNT0 FROM ZY_BRFY00 a WHERE ZYID00=ad_ZYID00 and JZDH00+0=0 and (decode(nvl(a.yexm00,'0'),'0','母亲',a.yexm00)=ls_YEXM01 or ls_YEXM01='全部') --HJJE00<>(SELECT nvl(SUM(HJJE00),0) FROM ZY_FYMX00 b WHERE a.DJH000=b.DJH000)and rownum=1; and HJJE00<>(SELECT nvl(SUM(HJJE00),0) FROM VW_ZY_ZYFYMX b WHERE a.DJH000=b.DJH000) and rownum=1; if ls_COUNT0>0 then as_YHMSG0:='病人的费用明细与合计的不符,请与系统管理员联系！'; as_SYSMSG:='收费单据号'||to_char(ls_COUNT0)||'的明细表与主表合计不符'; ROLLBACK; RETURN; end if; exception when others then as_YHMSG0:=as_YHMSG0; end; --如果本次结算费用合计为0，直接UPDATE后返回 select sum(hjje00) into ls_HJJE00 FROM ZY_BRFY00 a WHERE ZYID00=ad_ZYID00 and JZDH00+0=0 and (decode(nvl(a.yexm00,'0'),'0','母亲',a.yexm00)=ls_YEXM01 or ls_YEXM01='全部'); -- by ZYSF-20161026-003 注释掉插入ZY_JZB000.PJH000=-1数据--begin-- /* if nvl(ls_HJJE00,0)=0 then ---modify by ZYSF-20110617-002-- --Update ZY_BRFY00 set JZDh00=1 WHERE ZYID00=ad_ZYID00 and JZDH00+0=0; SELECT min(Trim(CZRQ00)),max(Trim(CZRQ00)) into ls_FYQSRQ,ls_FYJZRQ FROM ZY_BRFY00 a WHERE ZYID00=ad_ZYID00 and JZDH00+0=0 and (decode(nvl(a.yexm00,'0'),'0','母亲',a.yexm00)=ls_YEXM01 or ls_YEXM01='全部'); select dqbq00 into ls_bqh00 from zy_brxxb0 where zyid00=ad_ZYID00; select SQ_ZY_JZB000_JZDH00.nextval into ad_JZDH00 from dual; --考虑实施人员由于安装问题,与系统保留的关键值有冲突,所以故意再本处添加了如小处理 if ad_JZDH00 = 1 then select SQ_ZY_JZB000_JZDH00.nextval into ad_JZDH00 from dual; --2 select SQ_ZY_JZB000_JZDH00.nextval into ad_JZDH00 from dual; --3 select SQ_ZY_JZB000_JZDH00.nextval into ad_JZDH00 from dual; --4 select SQ_ZY_JZB000_JZDH00.nextval into ad_JZDH00 from dual; --5 select SQ_ZY_JZB000_JZDH00.nextval into ad_JZDH00 from dual; --6 select SQ_ZY_JZB000_JZDH00.nextval into ad_JZDH00 from dual; --7 select SQ_ZY_JZB000_JZDH00.nextval into ad_JZDH00 from dual; --8 select SQ_ZY_JZB000_JZDH00.nextval into ad_JZDH00 from dual; --9 select SQ_ZY_JZB000_JZDH00.nextval into ad_JZDH00 from dual; --10 end if; if ls_YEXM01='全部' then Update ZY_BRFY00 set JZDh00=ad_JZDH00 WHERE ZYID00=ad_ZYID00 and JZDH00+0=0; else if ls_YEXM01='母亲' then Update ZY_BRFY00 set JZDh00=ad_JZDH00 WHERE ZYID00=ad_ZYID00 and JZDH00+0=0 and YEXM00 IS NULL; else Update ZY_BRFY00 set JZDh00=ad_JZDH00 WHERE ZYID00=ad_ZYID00 and JZDH00+0=0 and YEXM00=ls_YEXM01; end if; end if; Insert into ZY_JZB000(JZDH00,BRID00,ZYID00,BRZJE0,JZJE00,GFJE00,HJJE00,JMJE00,GRZHZF,TCJJZF,ZFJE00,PJH000, JZRQ00,JZSJ00,XJFKJE,ZZFKJE,JZY000,JMYY00,YSZJE0,ZZZJE0,CYYSK0,CYYTK0,Jzbqh0,TSBZBH,FYQSRQ,FYJZRQ, YEXM00,JZFS00,DBZBM0,XZQH00, mxzje0,YBYL31,YBYL32,YBYL33,YBYL34,YBYL35,YBYL36,YBYL37,YBYL38,YBYL39, YBYL40,YBYL41,YBYL42,YBYL43,YBYL44,YBYL45,YBYL46,YBYL47,YBYL48,YBYL49) values(ad_JZDH00,ad_BRID00,ad_ZYID00,0,0,0,0,0,0,0,0,-1, ls_CZRQ00,ls_CZSJ00,0,0,ad_JZY000,as_JMYY00,0,0,0,0,ls_bqh00,as_TSBZBH,ls_FYQSRQ,ls_FYJZRQ, ls_YEXM00,ls_YEXM01,ad_DBZBM0,LS_XZQH00, 0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0); insert into zy_jzmxxm select ad_JZDH00,mxid00,1 from zy_fymx00 a,zy_brfy00 b where b.zyid00=ad_ZYID00 and a.djh000=b.djh000 and b.jzdh00+0=0; return; end if; */ -- by ZYSF-20161026-003 注释掉插入ZY_JZB000.PJH000=-1数据--end-- --判定病人收费项目数量是否存在负数2010.02.20 qks -- begin -- select 'XMMC00='||xmmc00||',XMGG00='||xmgg00||',数量='||to_char(sl0000)||',GJBM00='||gjbm00||',SFXMID='||to_char(xmbh00),sl0000 -- into as_SYSMSG,ls_COUNT0 from -- (select xmbh00,gjbm00,xmmc00,xmgg00,sum(xmsl00) sl0000 from VW_ZY_RQDMX0 where zyid00=ad_ZYID00 -- group by xmbh00,gjbm00,xmmc00,xmgg00 having sum(xmsl00)<0) -- where rownum=1; -- if ls_COUNT0<0 then -- as_YHMSG0:='病人存在收费项目数量为负数,请与系统管理员联系！'; -- ROLLBACK; -- RETURN; -- end if; -- exception -- when others then -- as_YHMSG0:=as_YHMSG0; -- end; --判断是否存在单个住院票据名称总金额为负数现象 by zhangwz on 2010.06.30 begin select f.XMMC00,sum(b.HJJE00) into ls_zypjmc,ls_HJJE00 from ZY_BRFY00 a,ZY_FYMX00 b,BM_YYSFXM c,BM_FPXM00 f WHERE a.ZYID00=ad_ZYID00 and a.JZDH00+0=0 and (decode(nvl(a.yexm00,'0'),'0','母亲',a.yexm00)=ls_YEXM01 or ls_YEXM01='全部') ---modify by ZYSF-20110617-002-- and a.DJH000=b.DJH000 and b.XMBH00=c.SFXMID and c.ZYFPID=f.FPXMID group by f.XMMC00 having sum(b.HJJE00)<0; if nvl(ls_HJJE00,0)<0 then as_YHMSG0:='病人的单个住院发票项目存在金额为负数,请与系统管理员联系！'; as_SYSMSG:='住院发票 '||ls_zypjmc||' 存在总金额为负数现象.'; ROLLBACK; RETURN; end if; exception when others then as_YHMSG0:=as_YHMSG0; end; --判定病人未结的收费项目的合计金额是否为负数(只处理非医保病人) by qks on 2019.06.03 for ZYSF-20181217-004 if TRIM(ls_YBZYH0) = '0' then begin select nvl(max(XMBH00),0) into ls_COUNT0 from ( select b.XMBH00 from ZY_BRFY00 a,ZY_FYMX00 b where a.ZYID00=ad_ZYID00 and a.JZDH00+0=0 and a.DJH000=b.DJH000 group by b.XMBH00 having sum(b.HJJE00)<0 ); if ls_COUNT0>0 then as_YHMSG0:='病人的收费项目存在合计金额为负数,请与系统管理员联系！'; as_SYSMSG:='收费项目内码 '||to_char(ls_COUNT0)||' 存在合计金额为负数现象.'; ROLLBACK; RETURN; end if; exception when others then as_YHMSG0:=as_YHMSG0; end; end if; select SQ_ZY_JZB000_JZDH00.nextval into ad_JZDH00 from dual; --处理团体划账功能begin-- if LS_SFTTCY in (2) then --获取是否是团体 0:不是 1:团体主账户 2:团体成员账户 select A.BRXM00,A.BRXB00 into LS_BRXMTT,LS_BRXBTT from BM_BRXXB0 A where A.BRID00 = LS_TTBRID; select sum(ZYE000) into LS_GRZHYE from ZY_BRZHXX where BRID00=ad_BRID00; MsgStr:='获取主账户账户余额'; begin select SF_ZY_GETTTZHYE(ad_BRID00,'1') into LS_TTZHYE from dual; exception when others then LS_TTZHYE:=0; end; MsgStr:='获取本次结算的总费用'; begin select nvl(sum(HJJE00),0)into LS_TTHJJE from ZY_BRFY00 D where JZDH00+0=0 and JFLBID= ad_JFLBID --and (CZRQ00<=as_JSZZRQ or as_CZBZ00 = '1') and (CZRQ00<=as_JSZZRQ or as_CZBZ00 = '1' or nvl(LS_FKJSQY,'0') in ('8')) and (ZYH000=as_ZYH000 or ZYH000='0') --and djh000>=as_qsdjh0 and djh000<=as_zzdjh0 --跨年分开结算 and ((LS_FKJSQY='8' and exists (select 1 from ZY_JSBCMX FF where FF.DJH000=D.DJH000 and FF.ZYID00=ad_ZYID00 and FF.CZBZ00=LS_CZBZ00 and FF.FZDDH0=LS_FKLSH0)) or (LS_FKJSQY<>'8' and D.djh000>=as_qsdjh0 and D.djh000<=as_zzdjh0) ) and BRID00 = ad_BRID00 and ZYID00 = ad_ZYID00; exception when others then LS_TTHJJE:=0; end; LS_TTZHYE:=nvl(LS_TTHJJE,0); LS_TTHJJE:=nvl(LS_TTHJJE,0); LS_GRZHYE:=nvl(LS_GRZHYE,0); MsgStr:='判断总费用是否超过团体主账户余额'; if (LS_TTHJJE>0)and (LS_GRZHYE<0) then --如果本次结算费用<0并且个人账户余额<0 if LS_GRZHYE+LS_TTZHYE<0 then as_YHMSG0:='出院结算失败,该病人对应的团体主账户余额不足！'; as_SYSMSG:='本次费用总额:('||LS_TTHJJE||')团体主账户余额:('||LS_TTZHYE||')个人账户余额:('||LS_GRZHYE||')'||SQLERRM; rollback; return; else LS_TTHZJE:=LS_GRZHYE; select SQ_ZY_BRJFB0_DJH000.nextval into LS_TTZDJH from dual; select SQ_ZY_PJSYQK_PJH000.nextval into LS_TTZPJH from dual; select SQ_ZY_BRJFB0_DJH000.nextval into LS_TTDJH0 from dual; select SQ_ZY_PJSYQK_PJH000.nextval into LS_TTPJH0 from dual; MsgStr:='划出主账户的余额'; insert into ZY_BRJFB0(ZYID00,DJH000,BRID00,JFLBID,JFJE00,ZFFS00, ZFFSMC,ZPH000,ZPKHYH,ZPDWMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00, JFBZ00,JKBID0,JZDH00,TTBRID,TTDJH0,CZLX00) values(LS_TTZYID,LS_TTZDJH,LS_TTBRID,2,-abs(LS_TTHZJE),1, '现金','','','',LS_TTZPJH, LS_CZRQ00,LS_CZSJ00,ad_JZY000,as_JZYXM0,0, '1',0,ad_JZY000,ad_BRID00,LS_TTDJH0,'1'); --操作类型 1:转出 2:转入 if trim(ls_yjjpj0)='Y' then --退预交金和收预交金都用收款票据 Insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00, ZZJE00,YHKJE0,CZY000,CZYXM0,DYRQ00, DYSJ00,SFDY00,PJZT00,XM0000,XB0000) select LS_TTZPJH,PLBH00,PLMC00,'0',-abs(LS_TTHZJE), 0,0,ad_JZY000,as_JZYXM0,LS_CZRQ00, LS_CZSJ00,'Y','0',LS_BRXMTT,LS_BRXBTT from BM_PJLXB0 where PLBH00 = 8 ; --收预交金票据 else Insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00, ZZJE00,YHKJE0,CZY000,CZYXM0,DYRQ00, DYSJ00,SFDY00,PJZT00,XM0000,XB0000) select LS_TTZPJH,PLBH00,PLMC00,'0',-abs(LS_TTHZJE), 0,0,ad_JZY000,as_JZYXM0,LS_CZRQ00, LS_CZSJ00,'Y','0',LS_BRXMTT,LS_BRXBTT from BM_PJLXB0 where PLBH00 = 4 ; --收预交金票据 end if; MsgStr:='从团体主账户转入金额(ZY_BRJFB0)'; insert into ZY_BRJFB0(ZYID00,DJH000,BRID00,JFLBID,JFJE00,ZFFS00, ZFFSMC,ZPH000,ZPKHYH,ZPDWMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00, JFBZ00,JKBID0,JZDH00,TTBRID,TTDJH0,CZLX00) values(ad_ZYID00,LS_TTDJH0,ad_BRID00,2,abs(LS_TTHZJE),1, '现金','','','',LS_TTPJH0, LS_CZRQ00,LS_CZSJ00,ad_JZY000,as_JZYXM0,0, '1',0,ad_JZDH00,LS_TTBRID,LS_TTZPJH,'2'); --操作类型 1:转出 2:转入 MsgStr:='从团体主账户转入金额(ZY_PJSYQK)'; Insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00, ZZJE00,YHKJE0,CZY000,CZYXM0,DYRQ00, DYSJ00,SFDY00,PJZT00,XM0000,XB0000) select LS_TTPJH0,PLBH00,PLMC00,'0',abs(LS_TTHZJE), 0,0,ad_JZY000,as_JZYXM0,LS_CZRQ00, LS_CZSJ00,'Y','0',LS_BRXM00,LS_BRXB00 from BM_PJLXB0 where PLBH00 = 8 ; --收预交金票据 update ZY_BRZHXX set TTXFJE=nvl(TTXFJE,0)+abs(LS_TTHJJE),TTDJJE=nvl(TTDJJE,0)-abs(LS_TTHJJE) where BRID00=LS_TTBRID; end if; end if; end if; --处理团体划账功能end-- --考虑实施人员由于安装问题,与系统保留的关键值有冲突,所以故意再本处添加了如小处理 if ad_JZDH00 = 1 then select SQ_ZY_JZB000_JZDH00.nextval into ad_JZDH00 from dual; --2 select SQ_ZY_JZB000_JZDH00.nextval into ad_JZDH00 from dual; --3 select SQ_ZY_JZB000_JZDH00.nextval into ad_JZDH00 from dual; --4 select SQ_ZY_JZB000_JZDH00.nextval into ad_JZDH00 from dual; --5 select SQ_ZY_JZB000_JZDH00.nextval into ad_JZDH00 from dual; --6 select SQ_ZY_JZB000_JZDH00.nextval into ad_JZDH00 from dual; --7 select SQ_ZY_JZB000_JZDH00.nextval into ad_JZDH00 from dual; --8 select SQ_ZY_JZB000_JZDH00.nextval into ad_JZDH00 from dual; --9 select SQ_ZY_JZB000_JZDH00.nextval into ad_JZDH00 from dual; --10 end if; select SQ_ZY_PJSYQK_PJH000.nextval into ls_PJH000 from dual; --计算病人本次结算共交了预交金的总额 ls_JFDJHS:=as_JFDJHS; ls_XJJFJE:=0; ls_ZZJFJE:=0; --由于中途结算或出院结算时,还应该同时将病人的交费信息做结算处理 LOOP ls_TMPDJH:=substr(ls_JFDJHS,1,instr(ls_JFDJHS,',')-1); IF ls_TMPDJH <> '0' THEN select PJH000 into ls_TMPPJH from ZY_BRJFB0 where DJH000 = ls_TMPDJH; Update ZY_PJSYQK set HSCZY0 = ad_JZY000,HSCZXM = as_JZYXM0,HSRQ00 = ls_CZRQ00,HSSJ00 = ls_CZSJ00 where PJH000 = ls_TMPPJH; if SQL%NOTFOUND then as_YHMSG0:='病人预交金票据回收操作不成功,请与系统管理员联系！'; as_SYSMSG:='update zy_pjsyqk set hsczy0= where pjh000 ='||to_char(ls_TMPPJH)||SQLERRM; rollback; return; end if; Update ZY_BRJFB0 set JZDH00 = ad_JZDH00 where BRID00 = ad_BRID00 and ZYID00 = ad_ZYID00 and JZDH00+0=0 and DJH000 = ls_TMPDJH; if SQL%NOTFOUND then as_YHMSG0:='病人交费信息结算不成功,请与系统管理员联系！'; as_SYSMSG:='update zy_brjfb0 set jzdh00= where brid00 ='||to_char(ad_brid00)||' and jzdh00=0 and djh000='||ls_TMPDJH||SQLERRM; rollback; return; end if; END IF; ls_JFDJHS:=substr(ls_JFDJHS,instr(ls_JFDJHS,',')+1,length(ls_JFDJHS)); EXIT WHEN instr(ls_JFDJHS,',') = 0; END LOOP; IF ls_SFJSDW = 'N' THEN --若系统设置该病人在本院内处理的，则要在本院计算医保部分的金额 --医保个人帐户支付 Update ZY_BRJFB0 set JZDH00 = ad_JZDH00,PJH000 = ls_PJH000 where BRID00 = ad_BRID00 and ZYID00 = ad_ZYID00 and JZDH00+0=0 and ZFFS00 = 3; select NVL(sum(-JFJE00),0) into ls_GRZHZF from ZY_BRJFB0 where BRID00 = ad_BRID00 and ZYID00 = ad_ZYID00 and JZDH00 = ad_JZDH00 and ZFFS00 = 3; --医保统筹支付 Update ZY_BRJFB0 set JZDH00 = ad_JZDH00,PJH000 = ls_PJH000 where BRID00 = ad_BRID00 and ZYID00 = ad_ZYID00 and JZDH00+0=0 and ZFFS00 = 4; select NVL(sum(-JFJE00),0) into ls_TCJJZF from ZY_BRJFB0 where BRID00 = ad_BRID00 and ZYID00 = ad_ZYID00 and JZDH00 = ad_JZDH00 and ZFFS00 = 4; ELSE ls_GRZHZF:=0; ls_TCJJZF:=0; END IF; if trim(ls_ZY_YKHTKJKTS)<>'0' then --参数 ZY_YKHTKJKTS开启后在结算时后选择银行退款 Update ZY_BRJFB0 set JZDH00 = ad_JZDH00,PJH000 = ls_PJH000 where BRID00 = ad_BRID00 and ZYID00 = ad_ZYID00 and JZDH00+0=0 and ZFFS00 in (1,8,24,25,28) and JFBZ00 in ('1'); end if; --累计的现金交费金额 select NVL(sum(JFJE00),0) into ls_XJJFJE from ZY_BRJFB0 where BRID00 = ad_BRID00 and ZYID00 = ad_ZYID00 and JZDH00 = ad_JZDH00 and JFLBID = ad_JFLBID --and ZFFS00 <> 2 and ZFFS00 in (1,8,24,25,28); --现金 --累计的转帐交费金额 select NVL(sum(JFJE00),0) into ls_ZZJFJE from ZY_BRJFB0 where BRID00 = ad_BRID00 and ZYID00 = ad_ZYID00 and JZDH00 = ad_JZDH00 and JFLBID = ad_JFLBID and ZFFS00 = 2; --转帐 --择日转账预交金 BY 2020.09.27 --begin-- select NVL(sum(JFJE00),0) into LS_ZRZZYJJ from ZY_BRJFB0 where BRID00 = ad_BRID00 and ZYID00 = ad_ZYID00 and JZDH00 = ad_JZDH00 and JFLBID = ad_JFLBID and ZFFS00 = 2 and nvl(ZRZYLS,0)>0 and nvl(ZPDWMC,'转账') in ('择日住院预交金转入','择日住院预交金转出') ; --转帐 if nvl(LS_ZRZZYJJ,0)<>0 then ls_ZZJFJE:=nvl(ls_ZZJFJE,0)-nvl(LS_ZRZZYJJ,0);--转账预收款-择日转账预交金 ls_XJJFJE:=nvl(ls_XJJFJE,0)+nvl(LS_ZRZZYJJ,0);--现金预收款+择日转账预交金 end if; --择日转账预交金 BY 2020.09.27 --end-- --2020.10.16 by zhangyc add 插入回收票据情况表 ZY_PJHSQK（HSLX00:回收类型1:出院结算预交金回收 2:取消结算补收 3:重新结算补收 A:出院结算发票 B:取消结算发票 C:重新结算发票） insert into ZY_PJHSQK(ZYID00,JZDH00,DJH000,PJH000,CZY000,CZYXM0,CZRQ00,CZSJ00,CXJZDH,CXDJH0,HSJE00,HSCZY0,HSCZXM,HSRQ00,HSSJ00,HSLX00) select ZYID00,ad_JZDH00,DJH000,PJH000,JFCZY0,CZYXM0,JFRQ00,JFSJ00,0,0,JFJE00,ad_JZY000,as_JZYXM0,LS_CZRQ00,LS_CZSJ00,'1' from ZY_BRJFB0 where BRID00=ad_BRID00 and ZYID00=ad_ZYID00 and JZDH00=ad_JZDH00 and ZFFS00 in (1,2,8,18,24,25,28); --计算病人本次结算的费用总额 select NVL(sum(HJJE00),0),NVL(sum(ZFJE00),0),NVL(sum(GFJE00),0),NVL(sum(JZJE00),0),min(Trim(CZRQ00)),max(Trim(CZRQ00)) into ls_HJJE00,ls_HJZFJE,ls_GFJE00,ls_JZJE00,ls_FYQSRQ,ls_FYJZRQ from ZY_BRFY00 D where JZDH00+0=0 and JFLBID = ad_JFLBID and ( CZRQ00 <= as_JSZZRQ or as_CZBZ00 = '1' or nvl(LS_FKJSQY,'0') in ('8')) and (ZYH000= as_ZYH000 or ZYH000='0') ---modify by ZYSF-20110617-002-- and (decode(nvl(yexm00,'0'),'0','母亲',yexm00)=ls_YEXM01 or ls_YEXM01='全部') --and djh000>=as_qsdjh0 and djh000<=as_zzdjh0 --跨年分开结算 and ((LS_FKJSQY='8' and exists (select 1 from ZY_JSBCMX FF where FF.DJH000=D.DJH000 and FF.ZYID00=ad_ZYID00 and FF.CZBZ00=LS_CZBZ00 and FF.FZDDH0=LS_FKLSH0)) or (LS_FKJSQY<>'8' and D.djh000>=as_qsdjh0 and D.djh000<=as_zzdjh0) ) and BRID00 = ad_BRID00 and ZYID00 = ad_ZYID00; IF ls_SFJSDW = 'N' THEN if nvl(ls_HJJE00,0)<>nvl(ls_HJZFJE,0)+nvl(ls_GRZHZF,0)+nvl(ls_TCJJZF,0) then ls_HJZFJE:=nvl(ls_HJJE00,0)-nvl(ls_GRZHZF,0)-nvl(ls_TCJJZF,0); end if; ELSE if nvl(ls_hjje00,0)<>nvl(ls_hjzfje,0)+nvl(ls_GFJE00,0)+nvl(ls_JZJE00,0) then ls_HJZFJE:=nvl(ls_HJJE00,0)-nvl(ls_GFJE00,0)-nvl(ls_JZJE00,0); end if; END IF; if ls_XJJFJE + ls_ZZJFJE + ls_GRZHZF < ls_HJZFJE then ls_CYQKJE := ls_HJZFJE - ls_XJJFJE - ls_ZZJFJE - ls_GRZHZF ; --计算病人的欠费的金额 if as_SFYB00 = 'Y' then Update BM_BRXXB0 set ZYZT00 = '2' where BRID00 = ad_BRID00; --'4':病人欠费 if TRIM(ls_YBZYH0) <> '0' then ls_CYQKJE:=0; end if; else --zhangyc 2017.04.06 BY ZYSF-20170405-001 自费病人单病种在执行过程SP_ZY_DBZFYJS00后提示补交钱 if LS_ZFBZQY='Y' and LS_FBBH00=1 and LS_DBZBM0 is not null and AS_CZBZ00='1' then as_YHMSG0:=''; as_SYSMSG:=''; else as_YHMSG0:='病人账户金额不够,请先补交足够的金额,'||'至少要交'||to_char(ls_HJZFJE-ls_XJJFJE-ls_ZZJFJE-ls_GRZHZF)||'元,再进行本操作！'; as_SYSMSG:=sqlerrm; rollback; return; end if; end if; end if; if ls_ZZJFJE > ls_HJZFJE then ad_XJYE00 := ls_XJJFJE; ad_ZZYE00 := ls_ZZJFJE - ls_HJZFJE-ls_GRZHZF; else ad_XJYE00 := ls_XJJFJE+ls_ZZJFJE-ls_HJZFJE-ls_GRZHZF; --现金交费金额+转帐交费金额-合计自付金额 ad_ZZYE00 := 0; end if; --if (ad_ZZYE00 = 0) AND (as_SFYB00 = 'Y') AND (TRIM(ls_YBZYH0) <> '0') then --add by qks 2009.12.21 if (ls_ZZJFJE>0) and (ad_ZZYE00 = 0) AND (as_SFYB00 = 'Y') AND (TRIM(ls_YBZYH0) <> '0') then --modify by qks 2010.04.29 if ls_ZZJFJE>ls_HJZFJE-nvl(ad_GRZHZF,0)-nvl(ad_TCJJZF,0) then ad_ZZYE00 := ls_ZZJFJE - (ls_HJZFJE-nvl(ad_GRZHZF,0)-nvl(ad_TCJJZF,0)); end if; end if; if (ls_CYQKJE=0)AND (as_SFYB00 = 'Y') AND (TRIM(ls_YBZYH0) = '0') then ls_CYYTK0:=NVL(ad_XJYE00,0)+NVL(ad_ZZYE00,0); end if; --0.判断病人是否可以进行出院结算 if as_CZBZ00 = '1' then select count(*) into ls_COUNT0 from ZY_BRZHXX where BRID00 = ad_BRID00 and SFDJ00 <> '2' and JFLBID <> 0 ; if ls_COUNT0 > 1 then as_YHMSG0:='病人有多个账户,不能办理出院结算！请将其它账户结清后,再进行本操作！'; as_SYSMSG:=SQLERRM; ROLLBACK; RETURN; end if; end if; --4.补充: Update ZY_BRZHXX set WJSJE0 = WJSJE0 - ls_HJJE00 --YBTCJE = , --本次入院发生的医保统筹部分总金额,费用登记时,已添加了 --YBZFJE = , --本次入院发生的医保自付部分总金额 --YZYBJE = NVL(YZYBJE,0)+ls_JZJE00 where BRID00 = ad_BRID00 and JFLBID = ad_JFLBID; ad_XJYE00:=ad_XJYE00+nvl(ad_XFBXJE,0); /* --转账转为现金退出 IF (ad_ZZYE00>0) and (LS_ZY_ZZJESFXJTC='Y') THEN LV_ZZYE00:=ad_ZZYE00; ad_XJYE00:=ad_XJYE00+ad_ZZYE00; ad_ZZYE00:=0; LV_ZZJEBZ:='转账转现金退出:'||LV_ZZYE00; END IF; */ --1、添加病人结账表信息 Insert into ZY_JZB000(JZDH00,BRID00,ZYID00,BRZJE0,JZJE00,GFJE00,HJJE00,JMJE00,GRZHZF,TCJJZF,ZFJE00,PJH000, JZRQ00,JZSJ00,XJFKJE,ZZFKJE,JZY000,JMYY00,YSZJE0,ZZZJE0,CYYSK0,CYYTK0,JZBQH0,TSBZBH,FYQSRQ,FYJZRQ, YEXM00,JZFS00,DBZBM0,DBZMC0,BZBMLB,XZQH00,DDLSH0,YHTKJE,WXTK00,ZFBTK0, 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, YBJKID,MXLSH0,BKC001_MC,AAE140,AAE140_MC,BKE174,BKE174_MC,JSQYE0,JSHYE0, 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）超出个人自付金额后需补退的差额 ) values(ad_JZDH00,ad_BRID00,ad_ZYID00,LS_HJJE00,ls_JZJE00,ls_GFJE00,ls_HJJE00,ad_JMJE00,ls_GRZHZF,ls_TCJJZF,ls_HJZFJE,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_XJYE00,ad_ZZYE00,ad_JZY000,as_JMYY00,ls_XJJFJE,ls_ZZJFJE,ls_CYQKJE,ls_CYYTK0,LS_BQH00,as_TSBZBH,ls_FYQSRQ,ls_FYJZRQ, ls_YEXM00,ls_YEXM01,ad_DBZBM0,LS_DBZMC0,LS_BZBMLB,LS_XZQH00,LS_DDLSH0,0,0,0, 0,0,0,0,LS_YBYL04,LS_YBYL05,0,0,0,0, 0,0,0,0,0,0,0,0,0,0, 0,0,nvl(ad_XFBXJE,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,ad_XFBXID,null,LS_YBJKLX,null,null,null,null, null,null,null,null,null,null,null,null,null,null, null,null,null,null,null,null,null,null,null, null,null,BM_BKC001_MC,BM_AAE140,BM_AAE140_MC,null,null,LS_JSQYE0,LS_JSHYE0, LS_FKJSLX,--分开结算类型 空值正常结算 FYCF_ZF:拆分_自费 FYCF_YB:拆分_医保 FYCF_YE:拆分_婴儿 FYCF_MQ:拆分_母亲 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）超出个人自付金额后需补退的差额 ); if SQL%NOTFOUND then as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=SQLERRM; ROLLBACK; RETURN; end if; --2、填写病人结账明细表信息 --先判断一下要结算的项目是否有对应的发票项目 select count(*),MIN(SFXMID) into ls_COUNT0,ls_SFXMID from ZY_FYMX00 A,BM_YYSFXM C,ZY_BRFY00 D where A.XMBH00 = C.SFXMID and C.ZYFPID not in (select fpxmid from BM_FPXM00 ) and A.DJH000 = D.DJH000 and D.JZDH00+0=0 and D.JFLBID = ad_JFLBID and (D.CZRQ00 <= as_JSZZRQ or as_CZBZ00 = '1' or nvl(LS_FKJSQY,'0') in ('8')) and D.ZYH000= as_ZYH000 --and d.djh000>=as_qsdjh0 and d.djh000<=as_zzdjh0 and D.ZYID00 = ad_ZYID00 and (decode(nvl(D.yexm00,'0'),'0','母亲',D.yexm00)=ls_YEXM01 or ls_YEXM01='全部') --跨年分开结算 and ((LS_FKJSQY='8' and exists (select 1 from ZY_JSBCMX FF where FF.DJH000=D.DJH000 and FF.ZYID00=ad_ZYID00 and FF.CZBZ00=LS_CZBZ00 and FF.FZDDH0=LS_FKLSH0)) or (LS_FKJSQY<>'8' and d.djh000>=as_qsdjh0 and d.djh000<=as_zzdjh0) ) ; IF ls_COUNT0 > 0 THEN --若有存在没有发票项目对应的项目,则提示信息！ as_YHMSG0:='病人结算操作失败，发票项目不完整,请记录本信息并与系统管理员联系！'; as_SYSMSG:='BM_YYSFXM.SFXMID='||to_char(ls_SFXMID)||'的ZYFPID的值NOT IN BM_FPXM00.FPXMID！'; ROLLBACK; RETURN; END IF; if (LS_SFCFFP='Y')AND(TRIM(as_SFYB00)='N') then Insert into ZY_JZMX00(JZDH00,XMBH00,XMMC00,XMJE00,JMJE00,JMSHR0,JMYY00,ZFBL00,SFYLXM,ZFJE00,QZFJE0) select ad_JZDH00,B.FPXMID,B.XMMC00,NVL(sum(A.HJJE00),0),0,null,null,max(A.ZFBL00),B.SFYLXM,NVL(SUM(A.ZFJE00),0),NVL(SUM(A.QZFJE0),0) from ZY_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,ZY_BRFY00 D where A.XMBH00 = C.SFXMID and C.ZYFPID = B.FPXMID and (D.CZRQ00 <= as_JSZZRQ or as_CZBZ00 = '1' or nvl(LS_FKJSQY,'0') in ('8')) and A.DJH000 = D.DJH000 and D.JZDH00+0=0 ---modify by ZYSF-20110617-002-- and D.JFLBID = ad_JFLBID --and d.djh000>=as_qsdjh0 and d.djh000<=as_zzdjh0 and D.ZYID00 = ad_ZYID00 and D.BRID00 = ad_BRID00 and (decode(nvl(D.yexm00,'0'),'0','母亲',D.yexm00)=ls_YEXM01 or ls_YEXM01='全部') --跨年分开结算 and ((LS_FKJSQY='8' and exists (select 1 from ZY_JSBCMX FF where FF.DJH000=D.DJH000 and FF.ZYID00=ad_ZYID00 and FF.CZBZ00=LS_CZBZ00 and FF.FZDDH0=LS_FKLSH0)) or (LS_FKJSQY<>'8' and d.djh000>=as_qsdjh0 and d.djh000<=as_zzdjh0) ) group by B.FPXMID,B.XMMC00,B.SFYLXM; --自费金额冲销后，按公费金额记账，那么虽然自付比例为1,但总金额为0 ，所以不应插入表ZY_JZMX00 Delete from ZY_JZMX00 where JZDH00=ad_JZDH00 and XMJE00=0 and ZFJE00=0 and QZFJE0=0 ; Delete from ZY_PJSYMX where PJH000 = ls_PJH000; --旧票据明细删除 Update ZY_PJSYQK set XJJE00 = 0,ZZJE00 = 0,YHKJE0 = 0 where FZPJH0 = ls_PJH000; --原值去掉 SELECT NVL(min(ZFBL00),1) into ls_ZFBL00 from ZY_JZMX00 where zfbl00<>0 and JZDH00 = ad_JZDH00; ls_FZPJH0:=ls_PJH000; --开始的票据号取同组的组号 OPEN CUR_ZY_JZMX00_ZFBL00; LOOP FETCH CUR_ZY_JZMX00_ZFBL00 INTO ls_JZMX00; EXIT WHEN CUR_ZY_JZMX00_ZFBL00%NOTFOUND; if ls_JZMX00.ZFBL00 > ls_ZFBL00 then ls_ZFBL00:= ls_JZMX00.ZFBL00; end if; Insert into ZY_PJSYMX(PJH000,XMBH00,XMMC00,XMJE00,XMBZ00,ZFBL00,QZFJE0,SFYLXM) values(ls_FZPJH0,ls_JZMX00.XMBH00,ls_JZMX00.XMMC00,ls_JZMX00.XMJE00,0,ls_JZMX00.ZFBL00,ls_JZMX00.QZFJE0,LS_JZMX00.SFYLXM); Update ZY_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_FZPJH0; if SQL%NOTFOUND then if as_BDYFP0 = 'Y' then Insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,FZPJH0,YEXM00,WBPJLY) select ls_FZPJH0,PLBH00,PLMC00,'0',ls_JZMX00.XMJE00*ls_JZMX00.ZFBL00,ls_JZMX00.XMJE00-ls_JZMX00.XMJE00*ls_JZMX00.ZFBL00,ad_JZY000,as_JZYXM0, ls_CZRQ00,ls_CZSJ00,'N','0',as_XM0000,as_XB0000,ls_PJH000,ls_YEXM00,as_WBPJLY from bm_pjlxb0 where PLBH00 = 3;--PLBH00=3是住院发票 else Insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,FZPJH0,YEXM00,WBPJLY) select ls_FZPJH0,PLBH00,PLMC00,SF_ZY_GETPJXH(ad_JZY000,3,as_WBPJLY),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,ls_YEXM00,as_WBPJLY from bm_pjlxb0 where PLBH00 = 3;--PLBH00=3是住院发票 end if; end if; END LOOP; CLOSE CUR_ZY_JZMX00_ZFBL00; select nvl(count(1),0) into ls_recode from zy_pjsyqk where fzpjh0=ls_PJH000; if ls_recode=0 then if as_BDYFP0 = 'Y' then Insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,FZPJH0,YEXM00) select ls_FZPJH0,PLBH00,PLMC00,'0',0,0,ad_JZY000,as_JZYXM0, ls_CZRQ00,ls_CZSJ00,'N','0',as_XM0000,as_XB0000,ls_PJH000,ls_YEXM00 from bm_pjlxb0 where PLBH00 = 3;--PLBH00=3是住院发票 else Insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,FZPJH0,YEXM00) select ls_FZPJH0,PLBH00,PLMC00,'0',0,0,ad_JZY000,as_JZYXM0, ls_CZRQ00,ls_CZSJ00,'Y','0',as_XM0000,as_XB0000,ls_PJH000,ls_YEXM00 from bm_pjlxb0 where PLBH00 = 3;--PLBH00=3是住院发票 end if; end if; else Insert into ZY_JZMX00(JZDH00,XMBH00,XMMC00,XMJE00,JMJE00,JMSHR0,JMYY00,ZFBL00,SFYLXM,ZFJE00,QZFJE0) select ad_JZDH00,B.FPXMID,B.XMMC00,NVL(sum(A.HJJE00),0),0,null,null,1,B.SFYLXM,NVL(sum(a.zfje00),0),nvl(sum(a.QZFJE0),0) from ZY_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,ZY_BRFY00 D where A.XMBH00 = C.SFXMID and C.ZYFPID = B.FPXMID and A.DJH000 = D.DJH000 and D.JZDH00+0=0 and D.JFLBID = ad_JFLBID and (D.CZRQ00 <= as_JSZZRQ or as_CZBZ00 = '1' or nvl(LS_FKJSQY,'0') in ('8')) --and d.djh000>=as_qsdjh0 and d.djh000<=as_zzdjh0 and D.ZYID00 = ad_ZYID00 and D.BRID00 = ad_BRID00 and (decode(nvl(D.yexm00,'0'),'0','母亲',D.yexm00)=ls_YEXM01 or ls_YEXM01='全部') --跨年分开结算 and ((LS_FKJSQY='8' and exists (select 1 from ZY_JSBCMX FF where FF.DJH000=D.DJH000 and FF.ZYID00=ad_ZYID00 and FF.CZBZ00=LS_CZBZ00 and FF.FZDDH0=LS_FKLSH0)) or (LS_FKJSQY<>'8' and d.djh000>=as_qsdjh0 and d.djh000<=as_zzdjh0) ) group by B.FPXMID,B.XMMC00,B.SFYLXM; select NVL(sum(xmje00),0) INTO ls_HSHJJE from ZY_JZMX00 where JZDH00=ad_JZDH00 and sfylxm='Y'; Delete from ZY_PJSYMX where PJH000 = ls_PJH000 and sfylxm='N'; --旧票据明细删除 --3、填写病人票据信息表的内容 --2019.01.04 ls_HSHJJE 为0时PHLYXZ字段设置成N if as_BDYFP0 = 'Y' then Insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00, ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,YEXM00,PHLYXZ,WBPJLY) select LS_PJH000,PLBH00,PLMC00,'0',ls_HSHJJE,0,ad_JZY000,as_JZYXM0, LS_CZRQ00,LS_CZSJ00,'N','0',as_XM0000,as_XB0000,ls_YEXM00,decode(ls_HSHJJE,0,'N','Y'),as_WBPJLY from bm_pjlxb0 where PLBH00 = 3;--PLBH00=3是住院发票 else Insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00, ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,YEXM00,PHLYXZ,WBPJLY) select LS_PJH000,PLBH00,PLMC00,decode(ls_HSHJJE,0,'0',SF_ZY_GETPJXH(ad_JZY000,3,as_WBPJLY)),ls_HSHJJE,0,ad_JZY000,as_JZYXM0, LS_CZRQ00,LS_CZSJ00,'Y','0',as_XM0000,as_XB0000,ls_YEXM00,decode(ls_HSHJJE,0,'N','Y'),as_WBPJLY from bm_pjlxb0 where PLBH00 = 3;--PLBH00=3是住院发票 end if; if SQL%NOTFOUND then as_YHMSG0:='系统字典不完整,所需的票据类型不存在,操作失败！'; as_SYSMSG:=SQLERRM||'票据类型不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 3)'; ROLLBACK; RETURN; end if; end if; select NVL(sum(XMJE00),0) INTO ls_HSHJJE from ZY_JZMX00 where JZDH00=ad_JZDH00 and sfylxm='N'; IF ls_HSHJJE<>0 THEN select SQ_ZY_PJSYQK_PJH000.nextval into ls_PJH001 from dual; select NVL(sum(zfje00),0) INTO ls_HSHJJE from ZY_JZMX00 where JZDH00=ad_JZDH00 and sfylxm='N'; Insert into ZY_PJSYMX(PJH000,XMBH00,XMMC00,XMJE00,XMBZ00,ZFBL00,QZFJE0,SFYLXM) select ls_PJH001,XMBH00,XMMC00,XMJE00,0,ZFBL00,QZFJE0,SFYLXM from zy_jzmx00 where jzdh00=ad_jzdh00 and sfylxm='N'; if as_BDYFP0 = 'Y' then insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,FZPJH0,YEXM00) select ls_PJH001,PLBH00,PLMC00,'0',ls_HSHJJE,0,ad_JZY000,as_JZYXM0, ls_CZRQ00,ls_CZSJ00,'N','0',as_XM0000,as_XB0000,ls_PJH000,ls_YEXM00 from bm_pjlxb0 where PLBH00 = 10;--PLBH00=10是住院伙食发票 else insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,FZPJH0,YEXM00) select ls_PJH001,PLBH00,PLMC00,SF_ZY_GETPJXH(ad_JZY000,10),ls_HSHJJE,0,ad_JZY000,as_JZYXM0, ls_CZRQ00,ls_CZSJ00,'Y','0',as_XM0000,as_XB0000,ls_PJH000,ls_YEXM00 from bm_pjlxb0 where PLBH00 = 10;--PLBH00=10是住院伙食发票 end if; END IF; --2020.10.16 by zhangyc add 插入回收票据情况表 ZY_PJHSQK（HSLX00:回收类型1:出院结算预交金回收 2:取消结算补收 3:重新结算补收 A:出院结算发票 B:取消结算发票 C:重新结算发票） insert into ZY_PJHSQK(ZYID00,JZDH00,DJH000,PJH000,CZY000,CZYXM0,CZRQ00,CZSJ00,CXJZDH,CXDJH0,HSJE00,HSCZY0,HSCZXM,HSRQ00,HSSJ00,HSLX00) select ad_ZYID00,ad_JZDH00,0,PJH000,CZY000,CZYXM0,DYRQ00,DYSJ00,0,0,nvl(XJJE00,0)+nvl(ZZJE00,0)+nvl(YHKJE0,0),CZY000,CZYXM0,LS_CZRQ00,LS_CZSJ00,'A' from ZY_PJSYQK where ZYID00=ad_ZYID00 and PJH000=LS_PJH000 and PLBH00 = 3; ---modify by ZYSF-20110617-002-- if ls_YEXM01='全部' then Update ZY_BRFY00 D set YBDJH0 = as_YBDJH0, --医保的单据流水号 JZDH00 = ad_JZDH00 --结算单号 where JZDH00+0=0 --未结算的病人 --and djh000>=as_qsdjh0 and djh000<=as_zzdjh0 and JFLBID = ad_JFLBID --有预交金账户的病人 and ZYID00 = ad_ZYID00 and BRID00 = ad_BRID00 and (CZRQ00 <= as_JSZZRQ or as_CZBZ00 = '1' or nvl(LS_FKJSQY,'0') in ('8')) --跨年分开结算 and ((LS_FKJSQY='8' and exists (select 1 from ZY_JSBCMX FF where FF.DJH000=D.DJH000 and FF.ZYID00=ad_ZYID00 and FF.CZBZ00=LS_CZBZ00 and FF.FZDDH0=LS_FKLSH0)) or (LS_FKJSQY<>'8' and D.djh000>=as_qsdjh0 and D.djh000<=as_zzdjh0) ) ; else if ls_YEXM01='母亲' then Update ZY_BRFY00 D set YBDJH0 = as_YBDJH0, --医保的单据流水号 JZDH00 = ad_JZDH00 --结算单号 where JZDH00+0=0 --未结算的病人 and YEXM00 IS NULL --and djh000>=as_qsdjh0 and djh000<=as_zzdjh0 and JFLBID = ad_JFLBID --有预交金账户的病人 and ZYID00 = ad_ZYID00 and BRID00 = ad_BRID00 and (CZRQ00 <= as_JSZZRQ or as_CZBZ00 = '1' or nvl(LS_FKJSQY,'0') in ('8')) --跨年分开结算 and ((LS_FKJSQY='8' and exists (select 1 from ZY_JSBCMX FF where FF.DJH000=D.DJH000 and FF.ZYID00=ad_ZYID00 and FF.CZBZ00=LS_CZBZ00 and FF.FZDDH0=LS_FKLSH0)) or (LS_FKJSQY<>'8' and D.djh000>=as_qsdjh0 and D.djh000<=as_zzdjh0) ) ; else Update ZY_BRFY00 D set YBDJH0 = as_YBDJH0, --医保的单据流水号 JZDH00 = ad_JZDH00 --结算单号 where JZDH00+0=0 --未结算的病人 and YEXM00=ls_YEXM01 --and djh000>=as_qsdjh0 and djh000<=as_zzdjh0 and JFLBID = ad_JFLBID --有预交金账户的病人 and ZYID00 = ad_ZYID00 and BRID00 = ad_BRID00 and (CZRQ00 <= as_JSZZRQ or as_CZBZ00 = '1' or nvl(LS_FKJSQY,'0') in ('8')) --跨年分开结算 and ((LS_FKJSQY='8' and exists (select 1 from ZY_JSBCMX FF where FF.DJH000=D.DJH000 and FF.ZYID00=ad_ZYID00 and FF.CZBZ00=LS_CZBZ00 and FF.FZDDH0=LS_FKLSH0)) or (LS_FKJSQY<>'8' and D.djh000>=as_qsdjh0 and D.djh000<=as_zzdjh0) ) ; end if; end if; -- Update ZY_BRFY00 set YBDJH0 = as_YBDJH0, --医保的单据流水号 -- JZDH00 = ad_JZDH00 --结算单号 -- where JZDH00+0=0 --未结算的病人 -- and djh000>=as_qsdjh0 -- and djh000<=as_zzdjh0 -- and JFLBID = ad_JFLBID --有预交金账户的病人 -- and BRID00 = ad_BRID00 -- and (CZRQ00 <= as_JSZZRQ or as_CZBZ00 = '1'); --修正结帐表的减免金额 -- Update zy_jzb000 a set jmje00=(select nvl(sum(b.JMJE00),0) from zy_fymx00 b,zy_brfy00 c where b.djh000=c.djh000 and c.jzdh00=a.jzdh00) where a.jzdh00=ad_JZDH00 and a.jmje00=0; Update zy_jzb000 a set jmje00=(select nvl(sum(b.JMJE00),0) from zy_fymx00 b,zy_brfy00 c where b.djh000=c.djh000 and c.jzdh00=a.jzdh00 ---modify by ZYSF-20110617-002-- and (decode(nvl(C.yexm00,'0'),'0','母亲',C.yexm00)=ls_YEXM01 or ls_YEXM01='全部')) where a.jzdh00=ad_JZDH00 and a.jmje00=0; --5、若医保有返回金额，还应该添加病人的交费信息或逃跑病人出院结算 IF as_SFYB00 = 'Y' THEN --是否允许欠费结算 IF ls_YBZYH0 <> '0' THEN --医保病人 select SQ_ZY_BRJFB0_DJH000.nextval into ad_TJDJH0 from dual; --统筹基金的单据流水号 Insert into ZY_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00, JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0) values(ad_TJDJH0,ad_BRID00,ad_JFLBID,ls_TCJJZF,4,'医保统筹基金',as_ZPZH00,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JZYXM0,0,'0',0,ad_JZDH00, null,null,null,'11','出院结算',null); select SQ_ZY_BRJFB0_DJH000.nextval into ad_GZDJH0 from dual; --个人账户的单据流水号 Insert into ZY_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00, JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0) values(ad_GZDJH0,ad_BRID00,ad_JFLBID,ls_GRZHZF,3,'医保个人账户',as_ZPZH00,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JZYXM0,0,'0',0,ad_JZDH00, null,null,null,'11','出院结算',null); select SQ_ZY_BRJFB0_DJH000.nextval into ad_SBZHDH from dual; --商保个人帐户单据流水号 Insert into ZY_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00, JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0) 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, null,null,null,'11','出院结算',null from BM_BRZFFS where ZFFSBH=10; select SQ_ZY_BRJFB0_DJH000.nextval into ad_SBTJDH from dual; --商保统筹基金单据流水号 Insert into ZY_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00, JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0) 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, null,null,null,'11','出院结算',null from BM_BRZFFS where ZFFSBH=11; select SQ_ZY_BRJFB0_DJH000.nextval into ad_jjdjh0 from dual; --保健基金单据流水号 Insert into ZY_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00, JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0) 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, null,null,null,'11','出院结算',null from BM_BRZFFS where ZFFSBH=6; select SQ_ZY_BRJFB0_DJH000.nextval into ad_SYDJH0 from dual; --商业保险支付单据号 Insert into ZY_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00, JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0) 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, null,null,null,'11','出院结算',null from BM_BRZFFS where ZFFSBH=13; select SQ_ZY_BRJFB0_DJH000.nextval into ad_SBDJH0 from dual; --商保基金支付单据号 Insert into ZY_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00, JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0) 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, null,null,null,'11','出院结算',null from BM_BRZFFS where ZFFSBH=14; select SQ_ZY_BRJFB0_DJH000.nextval into ad_BJDJH0 from dual; --保健基金支付单据号 Insert into ZY_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00, JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0) 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, null,null,null,'11','出院结算',null from BM_BRZFFS where ZFFSBH=15; select SQ_ZY_BRJFB0_DJH000.nextval into ad_GWYDJH from dual; --公务员补助 Insert into ZY_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00, JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0) 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, null,null,null,'11','出院结算',null from BM_BRZFFS where ZFFSBH=16; --by ZYSF-20111128-001 增加医院支付金额 select SQ_ZY_BRJFB0_DJH000.nextval into ad_YYZFDH from dual; --医院支付金额 Insert into ZY_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00, JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0) select ad_YYZFDH,ad_BRID00,ad_JFLBID,0,ZFFSBH,ZFFSMC,as_ZPZH00,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JZYXM0,0,'0',0,ad_JZDH00, null,null,null,'11','出院结算',null from BM_BRZFFS where ZFFSBH=20; ELSE --逃跑病人出院结算 LS_SFTPJS:='Y';--是否逃跑结算 Y:是 N:否 select SQ_ZY_BRJFB0_DJH000.nextval into LS_XJDJH0_TPJS from dual; --现金单据号（逃跑结算） select decode(ls_CYQKJE,0,-1*ls_CYYTK0,ls_CYQKJE) into LS_XJYE00_TPJS from dual;--现金余额（逃跑结算） Insert into ZY_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,ZYID00, JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0) values(LS_XJDJH0_TPJS,ad_BRID00,0,LS_XJYE00_TPJS,9,'逃跑欠费','0',ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JZYXM0,0,'0',0,ad_JZDH00,ad_ZYID00,null,null,null,'11','出院结算',null); /* values(SQ_ZY_BRJFB0_DJH000.nextval,ad_BRID00,0,decode(ls_CYQKJE,0,-1*ls_CYYTK0,ls_CYQKJE),9,'逃跑欠费','0',ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JZYXM0,0,'0',0,ad_JZDH00,ad_ZYID00,null,null,null,'11','出院结算',null); */ END IF; END IF; if (nvl(ad_XFBXJE,0)>0) and (ad_XFBXID is not null ) then Insert into ZY_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00, JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0) select SQ_ZY_BRJFB0_DJH000.nextval,ad_BRID00,ad_JFLBID,ad_XFBXJE,ZFFSBH,ZFFSMC,as_ZPZH00,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JZYXM0,0,'0',0,ad_JZDH00, null,null,null,'11','出院结算',null from BM_BRZFFS where ZFFSBH=23; end if; --6.若病人账户上有剩余金额,还应该将该剩余金额退还给病人(由于医保病人还有医保帐户退回的,可以在医保接口完成后再Update..where DJH000=ad_XJDJH0) IF as_SFYB00 <> 'Y' THEN --是否医保病人结算,'Y:医保病人 IF (ls_SSWUTSCL = 'Y')AND(LS_SF_ZY_FFJJ='Y') THEN --对于四舍五入部分金额特殊处理 ls_xjje00:=ad_xjye00-round(ad_xjye00+0.04,1); if abs(ls_xjje00) > 0.1 then as_YHMSG0:='系统结算四舍五入金额发生错误,该部分的金额不能为大于1的值！'; as_SYSMSG:='退还金额:'||to_char(ad_xjye00)||',四舍五入后金额:'||to_char(round(ad_xjye00,1)); ROLLBACK; RETURN; end if; select SQ_ZY_BRJFB0_DJH000.nextval into ad_XJDJH0 from dual; --病人现金的退款单据号 Insert into ZY_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPKHYH,ZPDWMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00, JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0) values(ad_XJDJH0,ad_BRID00,ad_JFLBID,-ls_xjje00,5,'四舍五入金额','','','',ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JZYXM0,0,'1',0,ad_JZDH00, null,null,null,'11','出院结算',null); select SQ_ZY_BRJFB0_DJH000.nextval into ad_XJDJH0 from dual; --病人现金的退款单据号 --if (trim(ZY_JSTKAYJJFS)='Y')and (ad_xjye00>0)and(as_CZBZ00='0') then if ((ZY_JSTKAYJJFS='Y') and (as_CZBZ00='0') or (ZY_CYJSTKAYJJ='Y') and (as_CZBZ00='1')) and (ad_xjye00>0) then select SQ_ZY_PJSYQK_PJH000.nextval into ls_PJH000 from dual; if as_BDYFP0 = 'Y' then Insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,HSCZY0,HSCZXM,HSRQ00,HSSJ00,YEXM00) select ls_PJH000,PLBH00,PLMC00,'0',-round(ad_xjye00+0.04,1),0,0,ad_JZY000,as_JZYXM0, ls_CZRQ00,ls_CZSJ00,'N','0',as_XM0000,as_XB0000,ad_JZY000,as_JZYXM0,ls_CZRQ00,ls_CZSJ00,ls_YEXM00 from BM_PJLXB0 where PLBH00 = ls_plbh00 ; --退预交金时使用收款票据 else Insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,HSCZY0,HSCZXM,HSRQ00,HSSJ00,YEXM00) select ls_PJH000,PLBH00,PLMC00,SF_ZY_GETPJXH(ad_JZY000,ls_plbh00),-round(ad_xjye00+0.04,1),0,0,ad_JZY000,as_JZYXM0, ls_CZRQ00,ls_CZSJ00,'Y','0',as_XM0000,as_XB0000,ad_JZY000,as_JZYXM0,ls_CZRQ00,ls_CZSJ00,ls_YEXM00 from BM_PJLXB0 where PLBH00 = ls_plbh00 ; --退预交金时使用收款票据 end if; if SQL%NOTFOUND then as_YHMSG0:='系统字典不完整,所需的票据类型不存在,操作失败！'; as_SYSMSG:=SQLERRM||'票据类型不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 8)'; ROLLBACK; RETURN; end if; end if; Insert into ZY_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPKHYH,ZPDWMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,BZ0000, JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0) values(ad_XJDJH0,ad_BRID00,ad_JFLBID,-round(ad_xjye00+0.04,1),1,'现金','','','',ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JZYXM0,0,'1',0,ad_JZDH00,LV_ZZJEBZ, null,null,null,'11','出院结算',null); ad_xjye00:=round(ad_xjye00+0.04,1); ELSE select SQ_ZY_BRJFB0_DJH000.nextval into ad_XJDJH0 from dual; --病人现金的退款单据号 --if (trim(ZY_JSTKAYJJFS)='Y')and (ad_xjye00>0)and(as_CZBZ00='0') then if ((ZY_JSTKAYJJFS='Y') and (as_CZBZ00='0') or (ZY_CYJSTKAYJJ='Y') and (as_CZBZ00='1')) and (ad_xjye00>0) then select SQ_ZY_PJSYQK_PJH000.nextval into ls_PJH000 from dual; if as_BDYFP0 = 'Y' then Insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,HSCZY0,HSCZXM,HSRQ00,HSSJ00) select ls_PJH000,PLBH00,PLMC00,'0',-ad_XJYE00,0,0,ad_JZY000,as_JZYXM0, ls_CZRQ00,ls_CZSJ00,'N','0',as_XM0000,as_XB0000,ad_JZY000,as_JZYXM0,ls_CZRQ00,ls_CZSJ00 from BM_PJLXB0 where PLBH00 = ls_plbh00 ; --退预交金时使用收款票据 else Insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,HSCZY0,HSCZXM,HSRQ00,HSSJ00) select ls_PJH000,PLBH00,PLMC00,SF_ZY_GETPJXH(ad_JZY000,ls_plbh00),-ad_XJYE00,0,0,ad_JZY000,as_JZYXM0, ls_CZRQ00,ls_CZSJ00,'Y','0',as_XM0000,as_XB0000,ad_JZY000,as_JZYXM0,ls_CZRQ00,ls_CZSJ00 from BM_PJLXB0 where PLBH00 = ls_plbh00 ; --退预交金时使用收款票据 end if; if SQL%NOTFOUND then as_YHMSG0:='系统字典不完整,所需的票据类型不存在,操作失败！'; as_SYSMSG:=SQLERRM||'票据类型不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 8)'; ROLLBACK; RETURN; end if; end if; Insert into ZY_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPKHYH,ZPDWMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,BZ0000, JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0) values(ad_XJDJH0,ad_BRID00,ad_JFLBID,-ad_XJYE00,1,'现金','','','',ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JZYXM0,0,'1',0,ad_JZDH00,LV_ZZJEBZ, null,null,null,'11','出院结算',null); END IF; ELSE if ls_YBZYH0 <> '0' then --医保病人 --if (trim(ZY_JSTKAYJJFS)='Y')and (as_CZBZ00='0') then if ((ZY_JSTKAYJJFS='Y') and (as_CZBZ00='0') or (ZY_CYJSTKAYJJ='Y') and (as_CZBZ00='1')) then select SQ_ZY_PJSYQK_PJH000.nextval into ls_PJH000 from dual; if as_BDYFP0 = 'Y' then Insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,HSCZY0,HSCZXM,HSRQ00,HSSJ00) select ls_PJH000,PLBH00,PLMC00,'0',0,0,0,ad_JZY000,as_JZYXM0, ls_CZRQ00,ls_CZSJ00,'N','0',as_XM0000,as_XB0000,ad_JZY000,as_JZYXM0,ls_CZRQ00,ls_CZSJ00 from BM_PJLXB0 where PLBH00 = ls_plbh00 ; --退预交金时使用收款票据 else Insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,HSCZY0,HSCZXM,HSRQ00,HSSJ00) select ls_PJH000,PLBH00,PLMC00,SF_ZY_GETPJXH(ad_JZY000,ls_plbh00),0,0,0,ad_JZY000,as_JZYXM0, ls_CZRQ00,ls_CZSJ00,'Y','0',as_XM0000,as_XB0000,ad_JZY000,as_JZYXM0,ls_CZRQ00,ls_CZSJ00 from BM_PJLXB0 where PLBH00 = ls_plbh00 ; --退预交金时使用收款票据 end if; if SQL%NOTFOUND then as_YHMSG0:='系统字典不完整,所需的票据类型不存在,操作失败！'; as_SYSMSG:=SQLERRM||'票据类型不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 8)'; ROLLBACK; RETURN; end if; end if; select SQ_ZY_BRJFB0_DJH000.nextval into ad_XJDJH0 from dual; --病人现金的退款单据号 Insert into ZY_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPKHYH,ZPDWMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,BZ0000, JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0) values(ad_XJDJH0,ad_BRID00,ad_JFLBID,0,1,'现金','','','',ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JZYXM0,0,'1',0,ad_JZDH00,LV_ZZJEBZ, null,null,null,'11','出院结算',null); end if; END IF; IF ad_ZZYE00 <> 0 THEN --若病人有剩余转账金额,进行退款,同时退款对应的打印票据号是发票号 select ZPH000,ZPKHYH,ZPDWMC into ls_ZPH000,ls_ZPKHYH,ls_ZPDWMC from ZY_BRJFB0 where BRID00 = ad_BRID00 and ZYID00 = ad_ZYID00 and JZDH00 = ad_JZDH00 and JFLBID = ad_JFLBID and ZFFS00 = 2 and djh000=(select max(djh000) from ZY_BRJFB0 where BRID00 = ad_BRID00 and ZYID00 = ad_ZYID00 and JZDH00 = ad_JZDH00 and JFLBID = ad_JFLBID and ZFFS00 = 2); select SQ_ZY_BRJFB0_DJH000.nextval into ad_ZZDJH0 from dual; --病人转账的退款单据号 IF as_SFYB00 <> 'Y' THEN --是否医保病人结算,'Y:医保病人,医保病人这时退款应该为0,否则会与退款补充过程的退款重复 --if (trim(ZY_JSTKAYJJFS)='Y')and (ad_ZZYE00>0)and(as_CZBZ00='0') then if ((ZY_JSTKAYJJFS='Y') and (as_CZBZ00='0') or (ZY_CYJSTKAYJJ='Y') and (as_CZBZ00='1')) and (ad_ZZYE00>0) then select SQ_ZY_PJSYQK_PJH000.nextval into ls_PJH000 from dual; if as_BDYFP0 = 'Y' then Insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,HSCZY0,HSCZXM,HSRQ00,HSSJ00,YEXM00) select ls_PJH000,PLBH00,PLMC00,'0',0,-ad_ZZYE00,0,ad_JZY000,as_JZYXM0, ls_CZRQ00,ls_CZSJ00,'N','0',as_XM0000,as_XB0000,ad_JZY000,as_JZYXM0,ls_CZRQ00,ls_CZSJ00,ls_YEXM00 from BM_PJLXB0 where PLBH00 = ls_plbh00 ; --退预交金时使用收款票据 else Insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,HSCZY0,HSCZXM,HSRQ00,HSSJ00,YEXM00) select ls_PJH000,PLBH00,PLMC00,SF_ZY_GETPJXH(ad_JZY000,ls_plbh00),0,-ad_ZZYE00,0,ad_JZY000,as_JZYXM0, ls_CZRQ00,ls_CZSJ00,'Y','0',as_XM0000,as_XB0000,ad_JZY000,as_JZYXM0,ls_CZRQ00,ls_CZSJ00,ls_YEXM00 from BM_PJLXB0 where PLBH00 = ls_plbh00 ; --退预交金时使用收款票据 end if; if SQL%NOTFOUND then as_YHMSG0:='系统字典不完整,所需的票据类型不存在,操作失败！'; as_SYSMSG:=SQLERRM||'票据类型不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 8)'; ROLLBACK; RETURN; end if; end if; Insert into ZY_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPKHYH,ZPDWMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00, JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0) values(ad_ZZDJH0,ad_BRID00,ad_JFLBID,-ad_ZZYE00,2,'转账',ls_ZPH000,ls_ZPKHYH,ls_ZPDWMC,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JZYXM0,0,'1',0,ad_JZDH00, null,null,null,'11','出院结算',null); ELSE if ls_YBZYH0 <> '0' then --医保病人,若as_SFYB00='Y' and ls_YBZYH0='0'则是欠费出院的病人 --if (trim(ZY_JSTKAYJJFS)='Y')and(as_CZBZ00='0') then if ((ZY_JSTKAYJJFS='Y') and (as_CZBZ00='0') or (ZY_CYJSTKAYJJ='Y') and (as_CZBZ00='1')) then select SQ_ZY_PJSYQK_PJH000.nextval into ls_PJH000 from dual; if as_BDYFP0 = 'Y' then Insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,HSCZY0,HSCZXM,HSRQ00,HSSJ00,YEXM00) select ls_PJH000,PLBH00,PLMC00,'0',0,0,0,ad_JZY000,as_JZYXM0, ls_CZRQ00,ls_CZSJ00,'N','0',as_XM0000,as_XB0000,ad_JZY000,as_JZYXM0,ls_CZRQ00,ls_CZSJ00,ls_YEXM00 from BM_PJLXB0 where PLBH00 = ls_plbh00 ; --退预交金时使用收款票据 else Insert into ZY_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,HSCZY0,HSCZXM,HSRQ00,HSSJ00,YEXM00) select ls_PJH000,PLBH00,PLMC00,SF_ZY_GETPJXH(ad_JZY000,ls_plbh00),0,0,0,ad_JZY000,as_JZYXM0, ls_CZRQ00,ls_CZSJ00,'Y','0',as_XM0000,as_XB0000,ad_JZY000,as_JZYXM0,ls_CZRQ00,ls_CZSJ00,ls_YEXM00 from BM_PJLXB0 where PLBH00 = ls_plbh00 ; --退预交金时使用收款票据 end if; if SQL%NOTFOUND then as_YHMSG0:='系统字典不完整,所需的票据类型不存在,操作失败！'; as_SYSMSG:=SQLERRM||'票据类型不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 8)'; ROLLBACK; RETURN; end if; end if; Insert into ZY_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPKHYH,ZPDWMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00, JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0) values(ad_ZZDJH0,ad_BRID00,ad_JFLBID,0,2,'转账',ls_ZPH000,ls_ZPKHYH,ls_ZPDWMC,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_JZY000,as_JZYXM0,0,'1',0,ad_JZDH00, null,null,null,'11','出院结算',null); end if; END IF; END IF; if as_CZBZ00='1' then --由于出现出院登记不成功ZJF ADD 20030821 if as_SFYB00 = 'N' then Update ZY_BRZHXX set XJYE00=0,ZZYE00=0,ZYE000=0,YKZYE0=0 where BRID00 = ad_BRID00 and JFLBID = ad_JFLBID; else Update ZY_BRZHXX set XJYE00=nvl(ad_XJYE00,0),ZZYE00=nvl(ad_ZZYE00,0), ZYE000=nvl(ad_XJYE00,0)+nvl(ad_ZZYE00,0), YKZYE0=nvl(ad_XJYE00,0)+nvl(ad_ZZYE00,0) where BRID00 = ad_BRID00 and JFLBID = ad_JFLBID; end if; end if; --if ((ZY_JSTKAYJJFS='Y') and (as_CZBZ00='0') or (ZY_CYJSTKAYJJ='Y') and (as_CZBZ00='1')) then if (trim(ZY_JSTKAYJJFS)='Y') and(as_CZBZ00='0') and (TRIM(ls_YBZYH0) = '0') then --2010.05.28 qks 只处理非医保病人 if ad_XJYE00>0 then update ZY_JZB000 set xjye00=0,yszje0=yszje0-ad_XJYE00 where jzdh00=ad_JZDH00; end if; if ad_zzye00>0 then update ZY_JZB000 set zzye00=0,zzzje0=zzzje0-ad_zzye00 where jzdh00=ad_JZDH00; end if; else if (trim(ls_YEXM01) in ('全部','母亲')) then update ZY_JZB000 set xjye00=ad_xjye00,zzye00=ad_zzye00 where jzdh00=ad_JZDH00; else update ZY_JZB000 set xjye00=0,yszje0=yszje0-ad_XJYE00 where jzdh00=ad_JZDH00; end if; end if; --病人费用结算结束,若病人是出院结算,还应该再调用出院登记的存储过程 --COMMIT,由于本存储过程与有共用,所以放在Delphi中控制 sp_zy_jzmxxm_sjzl00; select nvl(trim(max(value0)),'N') into ZY_SFTSDYZSFP from xt_xtcs00 where name00='ZY_SFTSDYZSFP'; --有可能其他医院不许对XT_XTCS00进行修改，现在只有提示"是否打印正式发票"时才对XT_XTCS00进行修改 if ZY_SFTSDYZSFP='Y' then Update xt_xtcs00 set value0='Y' where name00='ZY_SFDYZSFP'; --add by xzw 如果要把ZY_SFDYZSFP改为Y,以免影响下次结算时候打印的发票ZYSF-20070903-001 end if; if LS_SFTTCY in (2) then --获取是否是团体 0:不是 1:团体主账户 2:团体成员账户 --把发票打印状态改为未打印 update ZY_PJSYQK set SFDY00='N' where PJH000=LS_PJH000; end if; -- add by ZYSF-20161217-001 非医保结算处理 转账转为现金退出 ，医保病人在过程SP_ZY_YBSF00_TKBC00中处理 if (ad_ZZYE00>0) and (LS_ZY_ZZJESFXJTC='Y') and ((nvl(trim(as_YBDJH0),'0')='0') and (LS_FBBH00<>3)) then LV_ZZYE00:=ad_ZZYE00; LV_ZZJEBZ:=ad_XJDJH0||'|'||ad_XJYE00||'|'||ad_ZZDJH0||'|'||LV_ZZYE00; ad_XJYE00:=ad_XJYE00+ad_ZZYE00; ad_ZZYE00:=0; select YXQFJE,XJYE00,ZZYE00,ZYE000,YKZYE0 into V_YXQFJE,V_XJYE00,V_ZZYE00,V_ZYE000,V_YKZYE0 from zy_brzhxx where BRID00 = ad_BRID00 and JFLBID = 2; --修改现金退款单据 update zy_brjfb0 set JFJE00=-(abs(nvl(JFJE00,0))+abs(nvl(LV_ZZYE00,0))),BZ0000=LV_ZZJEBZ where BRID00 = ad_BRID00 and DJH000=ad_XJDJH0; --修改转账退款单据 update zy_brjfb0 set JFJE00=0,BZ0000=LV_ZZJEBZ where BRID00 = ad_BRID00 and DJH000=ad_ZZDJH0; --修改结账表数据 update ZY_JZB000 set XJFKJE=nvl(XJFKJE,0)+nvl(ZZFKJE,0),ZZFKJE=0, XJYE00=nvl(XJYE00,0)+nvl(ZZYE00,0),ZZYE00=0, YBSM01=LV_ZZJEBZ where BRID00 = ad_BRID00 and JZDH00=ad_JZDH00; Update zy_brzhxx set YXQFJE =V_YXQFJE, XJYE00 =V_XJYE00, ZZYE00=V_ZZYE00, ZYE000 =V_ZYE000, YKZYE0 =V_YKZYE0 where BRID00 = ad_BRID00 and JFLBID = 2; end if; --中途结算预交金转入不显示应退金额 --2019.04.25 by ZYSF-20190425-003 如果是补交，不能把结账表ZZYE00、XJYE00设置为0 会导致发票错误 增加条件and nvl(AD_XJYE00,0)>0 if (as_CZBZ00='0') and (ZY_JSTKAYJJFS='Y') and ((nvl(trim(as_YBDJH0),'0')='0') and (LS_FBBH00<>3)) and nvl(AD_XJYE00,0)>0 then Update ZY_JZB000 set zzye00=0,XJYE00=0 where jzdh00=ad_JZDH00 and brid00 =ad_BRID00 ; end if; LS_XJDJH0_CYJS:=nvl(ad_XJDJH0,0);--现金单据号（出院结算） LS_XJYE00_CYJS:=nvl(ad_XJYE00,0);--现金余额（出院结算） LS_ZZDJH0_CYJS:=nvl(ad_ZZDJH0,0);--转账单据号（出院结算） LS_ZZYE00_CYJS:=nvl(ad_ZZYE00,0);--转账余额（出院结算） --zhangyc 2017.04.06 BY ZYSF-20170405-001 处理自费病人单病种 if LS_ZFBZQY='Y' and LS_FBBH00=1 and LS_DBZBM0 is not null and AS_CZBZ00='1' and nvl(LS_SFTPJS,'N') not in ('Y') then --逃跑结算暂时不按单病种处理 if LS_SFTPJS='Y' then --是否逃跑结算 Y:是 N:否 LS_TCXJDH:=LS_XJDJH0_TPJS; --现金退出单号 LS_TCXJJE:=LS_XJYE00_TPJS; --现金退出金额 LS_TCZZDH:=LS_ZZDJH0_TPJS; --转账退出单号 LS_TCZZJE:=LS_ZZYE00_TPJS; --转账退出金额 else LS_TCXJDH:=LS_XJDJH0_CYJS; --现金退出单号 LS_TCXJJE:=LS_XJYE00_CYJS; --现金退出金额 LS_TCZZDH:=LS_ZZDJH0_CYJS; --转账退出单号 LS_TCZZJE:=LS_ZZYE00_CYJS; --转账退出金额 end if; SP_ZY_DBZFYJS00( AD_ZYID00, --输入:ZYID00 AD_BRID00, --输入:BRID00 AD_JZDH00, --输入:结账单号 LS_PJH000, --输入:票据号 LS_TCXJDH, --输入:现金退款单据号 LS_TCZZDH, --输入:转账退款单据号 LS_TCXJJE, --输入:现金余额 LS_TCZZJE, --输入:转账余额 LS_DBZBM0, --输入:单病种编码 '0' , --输入:操作类型 0:费用结算 1:重新结算 AD_XJYE00, --输出:现金余额 AD_ZZYE00, --输出:转账余额 AS_YHMSG0, --输出:错误提示信息 AS_SYSMSG --输出:错误信息 ); if AD_XJYE00<0 then select YSZJE0,XJYE00,ZFJE00,YBYL04,YBYL05,YBYL33,YBYL34 into LS_YSZJE0,LS_XJYE00,LS_ZFJE00,LS_YBYL04,LS_YBYL05,LS_DBZCWF,LS_DBZHCF from ZY_JZB000 where ZYID00=AD_ZYID00 and BRID00=AD_BRID00 and JZDH00=AD_JZDH00; MsgStr:='该病人登记单病种,账户余额不足:至少需要补交'||trim(to_char(abs(AD_XJYE00),'9999990.00'))||'元！ ' ||'(病种需个人支付金额:'||trim(to_char(abs(LS_ZFJE00),'9999990.00'))||'元 ' ||' 预收款总金额:'||trim(to_char(abs(LS_YSZJE0),'9999990.00'))||'元)'; as_YHMSG0:=MsgStr; as_SYSMSG:=sqlerrm; rollback; return; end if; end if; --新农合跨省结算 if LS_DDLSH0 is not null then LS_TCXJJE:=ad_XJYE00; LS_TCZZJE:=ad_ZZYE00; SP_ZY_XNHKSJS_JSQR00( 4 ,--输入:接口类型编号 0：转诊单下载 1：住院病人信息上传 11：取消住院登记 3：住院费用上传 13：费用明细冲销 2：费用预结算 4：费用结算确认 14：取消结算确认 AD_ZYID00,--输入:住院ID LS_DDLSH0,--输入:订单流水号 AD_JZDH00,--输入:结账单号 '1',--输入:操作类型 0:前台调用 1：过程嵌套调用 'N',--输入:是否提交事务 Y:提交 N:不提交 AD_XJDJH0,--输入:现金退款单据号 LS_TCXJJE,--输入:现金余额 AD_ZZDJH0,--输入:转账退款单据号 LS_TCZZJE,--输入:转账余额 LS_ZXZT00,--输出:执行状态 0:失败 1：成功 AD_XJYE00,--输出:现金余额 AD_ZZYE00,--输出:转账余额 AS_SYSMSG --输出:错误信息 ); end if; --获取结算后账户总余额 (参数：0：门诊 1：住院 参数2：BRID00 参数3：1：门诊 2:住院 参数4：0:可用余额 1:总余额 2：现金余额 3:停用金额) begin select SF_SF_GETBRZHYE(1,AD_BRID00,2,1) into LS_JSHYE0 from dual; exception when others then LS_JSHYE0:=0; end; update ZY_JZB000 set JSHYE0=LS_JSHYE0 where ZYID00=AD_ZYID00 and BRID00=AD_BRID00 and JZDH00=AD_JZDH00; --2020.01.15 zhangyc by ZYSF-20200108-001 婴儿结算XJFKJE=自负金额 if trim(ls_YEXM01) not in ('全部','母亲') and trim(ls_YEXM01) is not null then update ZY_JZB000 set XJFKJE=ZFJE00,ZZFKJE=0 where ZYID00=AD_ZYID00 and BRID00=AD_BRID00 and JZDH00=AD_JZDH00; end if; if as_FKJSLX in ('FYCF_YB','FYCF_MQ','FYCF_BND') and nvl(ad_FKJZDH,0)>0 then --分开结算类型 空值正常结算 FYCF_ZF:拆分_自费 FYCF_YB:拆分_医保 FYCF_YE:拆分_婴儿 FYCF_MQ:拆分_母亲 FYCF_SND:拆分_上年度 FYCF_BND:拆分_本年度 update ZY_JZB000 set FKJZDH=AD_JZDH00 where ZYID00=AD_ZYID00 and BRID00=AD_BRID00 and JZDH00=ad_FKJZDH; end if; if as_FKJSLX in ('FYCF_SND') and as_FKLSH0 is not null then update ZY_JSBCMX set JZDH00=AD_JZDH00 where ZYID00=AD_ZYID00 and FZDDH0=as_FKLSH0 and CZBZ00='01'; end if; if as_FKJSLX in ('FYCF_BND') and as_FKLSH0 is not null then update ZY_JSBCMX set JZDH00=AD_JZDH00 where ZYID00=AD_ZYID00 and FZDDH0=as_FKLSH0 and CZBZ00='02'; end if; exception when others then as_YHMSG0:=MsgStr||'-->'||'出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_ZY_BRFY00_JS0000('||as_YBDJH0||','||as_ZYH000||','||to_char(ad_ZYID00)||','||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||','||as_JFDJHS||',ad_TJDJH0,ad_GZDJH0,ad_XJYE00,ad_ZZYE00,ad_JZDH00,as_YHMSG0,as_SYSMSG)',1,150); rollback; END; / -- End of DDL Script for Procedure SD_HOSPITAL.SP_ZY_BRFY00_JS0000