-- Start of DDL Script for Procedure SD_HOSPITAL.SP_ZY_MZZRFYCL -- Generated 2023-11-07 16:06:45 from SD_HOSPITAL@njxyy CREATE OR REPLACE PROCEDURE sp_zy_mzzrfycl ( ad_ZYH000 in char, --住院号 ad_BRID00 in number, --病人id ad_GHID00 in char, --挂号id(多值合并) ad_CZY000 in number, --操作员 ad_CZYKS0 in number, --操作员科室 as_ZRZYLS out number, --择日流水号 as_YHMSG0 out varchar2, --存储过程提示的错误信息 as_SYSMSG out varchar2, --系统提示的错误信息 ad_BQCZBZ in varchar2 default '0', --病区操作标示 1病区，0住院收费 ad_YJID00 in number default 0 --医技单临时表ID ) -- MODIFICATION HISTORY --Person Date Comment --zhangyc 2015.12.08 create by ZYSF-20151109-001 --zhangyc 2015.12.17 修正错误 BY ZYSF-20151210-002 --zhangyc 2017.03.23 增加执行过程 SP_ZY_UPDATE_DBZFY0 by ZYSF-20170324-002 --zhangyc 2020.05.18 条件XMZT00 in ('2','3')改为XMZT00 in ('2','3','4') --qiulf 2020.08.13 增加入参ad_BQCZBZ，病区统一使用'择日住院操作员' by BQ-20200810-001 -- dsm 2020.09.04 择日住院病人费用转住院费用时，把转的项目写入海泰的中间表EmrMedicalOrderZtInfo for ZYSF-20200904-003\ -- dsm 2020.09.15 调用SP_YJ_YWCX00时PZRZYCX传Y 归档 ZYSF-20200914-002 --qiulf 2020.09.24 增加参数ZY_ZRZYYEZRFS控制转入的金额为转账方式，BQ_BQZRZYCZY参数有维护时，禁止在收费系统进行 by ZYSF-20200924-004 --qiulf 2020.10.14 转账方式编号错误写成8，改为2 by ZYSF-20201014-001 -- dsm 2020.10.19 增加未执行判断 for BQ-20201014-001 --qiulf 2020.10.29 转入的金额为转账方式SF_BRZHXX.ZZYE00、XYYE00要保持不变 ZYSF-20201029-003 -- dsm 2020.12.11 检验项目收费后，签收后才可以转住院 for ZYSF-20201211-001 -- dsm 2021.01.13 部分冲销的也不转住院 for BQ-20201210-001 -- dsm 2021.03.29 报告没有出的不能转住院费用的项目错误时提示项目名称 for BQ-20210329-001 --qiulf 2021.05.08 修正YJ_YW0000.KDBQ00为0的问题 by ZYSF-20210508-001 --qiulf 2021.09.30 增加入参Ad_YJID00，值不为0时允许选择医技单做费用处理 by BQ-20210929-001 --qiulf 2021.10.21 修正选择医技单做费用处理时,YJFZH0不为空时，费用多计算的问题 by BQ-20211021-001 --pwx 2021.12.30 归档YJCX in CUR_CX_YJ_YW0000增加字段XMZT00 为4（已报告）的状态 by ZYSF-20211227-001 --dsm 2022.09.01 ZY_FYMX00.KDRQ00,KDSJ00写入 for BQ-20220901-002 --dsm 2023.05.25 增加门诊特殊病种判断 for BQ-20230519-001 --dsm 2023.06.05 诊转择日住院过程，如果选择项目转时，会把同个收费单据下的没有选择的费用一起转到住院 ZYSF-20230605-001 --dsm 2023.06.28 药品可以转择日住院处理 for BQ-20230628-001 --dsm 2023.08.07 单个转时只判断单个的检验检查状态 BQ-20230802-001 --dsm 2023.09.07 门诊药品退单的发药日期错误 for ZYSF-20230906-001 --dsm 2023.10.18 门诊冲销和住院记录备注记录实际操作员 for BQ-20231013-003 --dsm 2023.10.24 SFZRZY=Y改成 SFZRZY in(Y,Z),因为处理1次后会改成Z YJ-20231020-001 as LS_CZRQ00 char(8); LS_CZSJ00 char(8); L number(2); I number(2); J number(2); v_GHID00_temp varchar2(200); v_GHID00 varchar2(10); LS_DJH000 ZY_BRFY00.DJH000%TYPE; LS_YJDJH0 YJ_YW0000.YJDJH0%TYPE; LS_CFLSH0 YF_ZYCF00.CFLSH0%TYPE; LS_ZYH000 ZY_BRXXB0.ZYH000%TYPE; LS_ZYID00 ZY_BRXXB0.ZYID00%TYPE; LS_BRID00 ZY_BRXXB0.BRID00%TYPE; LS_DQBQ00 ZY_BRXXB0.DQBQ00%TYPE; LS_DQKS00 ZY_BRXXB0.DQKS00%TYPE; LS_RYCWH0 ZY_BRXXB0.RYCWH0%TYPE; LS_XM0000 ZY_BRXXB0.XM0000%TYPE; LS_XB0000 ZY_BRXXB0.XB0000%TYPE; LS_ZWXM00 bm_ygbm00.ZWXM00%TYPE; LS_ZRZYLS number(10); LS_MZCZRQ char(8); LS_MZCZSJ char(8); LS_MZSFDH number(10); LS_MZYZCF varchar2(20); LS_MZZLDC varchar2(50); LS_ZYCZRQ char(8); LS_ZYCZSJ char(8); LS_ZYSFDH number(10); LS_ZYYZCF varchar2(20); LS_ZYZLDC varchar2(50); LS_MXID00 number(12); LS_COUNT0 number(5); LS_HJJE00 number(12,2); LS_ZFJE00 number(12,2); LS_GFJE00 number(12,2); LS_JZJE00 number(12,2); LS_QZFJE0 number(12,2); LS_JMJE00 number(12,2); LS_CXYJDH number(10); --冲销医技单号 LS_CXSFDH number(10); --冲销收费单号 LS_CXZJE0 number(12,2);--冲销总金额 LS_ZRTKJE number(12,2);--冲销金额 LS_XJYE00 number(12,2);--现金余额 LS_ZYE000 number(12,2);--总余额 LS_TYJE00 number(12,2);--停用金额 ad_PJH000 number(12); LS_MZJFDH number(12);--门诊交费单号 LS_ZYJFDH number(12);--门诊交费单号 LS_PJXH00 varchar2(30); MsgStr varchar2(100); LS_WJSJE0 number(12,2); LS_BCFYZE number(12,2); --本次费用总额 LS_DBZCWF ZY_BRZHXX.DBZCWF%type;--单病种超标床位费 LS_DBZHCF ZY_BRZHXX.DBZHCF%type;--单病种可另收材料 LS_DQCZY0 BM_YGBM00.YGBH00%type; --当前使用的操作员 BQZRZYCZERR exception; --病区择日住院操作错误 LS_SQL000 varchar2(1000); ZY_ZRZYYEZRFS XT_XTCS00.VALUE0%type; LS_ZHXJYE SF_BRZHXX.XJYE00%type; LS_ZHZZYE SF_BRZHXX.ZZYE00%type; LS_COUNT1 number(10); ls_WZXXMMC varchar2(1000); ZY_ZRZYFYCLBGZT BM_TYZD00.MC0000%type; ls_KDRQ00 YJ_YW0000.KDRQ00%type; ls_KDSJ00 YJ_YW0000.KDSJ00%type; ls_CXCFH0 YF_MZCF00.CFLSH0%type; ls_CFZJE0 YF_MZCF00.CFZJE0%type; ls_CXDJH0 YF_MZCF00.DJH000%type; ls_ZYCFH0 YF_ZYCF00.CFLSH0%type; ls_ZYDJH0 ZY_BRFY00.DJH000%type; ls_SJCZYXM BM_YGBM00.ZWXM00%type; --用户选择医技单费用处理 cursor CUR_YJ_YW0000_YJID00 is select a.YJDJH0,a.ZYGHID,a.ZYHGHH,a.SL0000,a.ZXSL00,a.ZJE000, a.XMZT00,a.KDRQ00,a.KDSJ00,a.CXDJH0,a.SFDJH0 from YJ_YW0000 a where a.BRID00=LS_BRID00 and a.MZZYBZ='0' and nvl(ZRZYLS,0)=0 --项目状态,'0'申请'1'计价'2'已收费'3'已执行'4'已报告'5'疑退'6'作废 and a.XMZT00 in ('2','3','4') and NVL(CXDJH0,0)=0 and NVL(CXSL00,0)=0 and a.SFDJH0>0 --已收费未冲销 --是否为附加单据0主单1采血费2试管费3挂号费4诊查费5血库子单6预约挂号7代煎9其他子单a注射b床位c病区普通常规费 d病区常规项目附加费 e药品用药附加费(bq_fjf000) and nvl(a.FJDJBZ,'1') not in ('3','4')--3挂号费4诊查费 --门诊做退费申请的不转 and not exists(select 1 from yj_yw0000 where cxdjh0=a.yjdjh0 and mzzybz='0') and not exists(select 1 from SF_ZRZYMX where GHID00=a.ZYGHID and MZYJDH=a.YJDJH0) and exists (select 1 from BM_GHKSTJ where ID0000=ad_YJID00 and DYID00=a.YJDJH0) order by YJDJH0; cursor CUR_YJ_YW0000 is select a.YJDJH0,a.ZYGHID,a.ZYHGHH,a.SL0000,a.ZXSL00,a.ZJE000, a.XMZT00,a.KDRQ00,a.KDSJ00,a.CXDJH0,a.SFDJH0 from YJ_YW0000 a where a.BRID00=LS_BRID00 and a.ZYGHID=to_number(v_GHID00) and a.MZZYBZ='0' and nvl(ZRZYLS,0)=0 --项目状态,'0'申请'1'计价'2'已收费'3'已执行'4'已报告'5'疑退'6'作废 and a.XMZT00 in ('2','3','4') and NVL(CXDJH0,0)=0 and NVL(CXSL00,0)=0 and a.SFDJH0>0 --已收费未冲销 --是否为附加单据0主单1采血费2试管费3挂号费4诊查费5血库子单6预约挂号7代煎9其他子单a注射b床位c病区普通常规费 d病区常规项目附加费 e药品用药附加费(bq_fjf000) and nvl(a.FJDJBZ,'1') not in ('3','4')--3挂号费4诊查费 --门诊做退费申请的不转 and not exists(select 1 from yj_yw0000 where cxdjh0=a.yjdjh0 and mzzybz='0') and not exists(select 1 from SF_ZRZYMX where GHID00=a.ZYGHID and MZYJDH=a.YJDJH0) order by YJDJH0; cursor CUR_SF_ZRZYMX is select MXID00,ZRZYLS,BRID00,GHID00,GHH000, MZYJDH YJDJH0,XMZT00,MZSFDH SFDJH0,HJJE00 from SF_ZRZYMX where BRID00=LS_BRID00 and ZRZYLS=LS_ZRZYLS order by GHID00,MZYJDH; cursor CUR_SF_BRFY00 is select distinct BRID00,MZSFDH SFDJH0 from SF_ZRZYMX where BRID00=LS_BRID00 and ZRZYLS=LS_ZRZYLS order by MZSFDH; cursor CUR_CX_YJ_YW0000 is select GHID00,BRID00,MZYJDH YJDJH0,XMZT00,SL0000 from SF_ZRZYMX where BRID00=LS_BRID00 and ZRZYLS=LS_ZRZYLS order by MZYJDH; begin select to_number(trim(VALUE0)) into LS_DQCZY0 from XT_XTCS00 where trim(NAME00)='BQ_BQZRZYCZY'; if ad_BQCZBZ = '1' then if LS_DQCZY0 is null or LS_DQCZY0 = 0 then MsgStr:='[获取择日住院院操作员未维护参数BQ_BQZRZYCZY]'; raise BQZRZYCZERR; end if; else if LS_DQCZY0 is not null and LS_DQCZY0 not in(0) then MsgStr:='[择日住院院操作员已维护，禁止在收费系统进行，请到病区进行门诊择日住院费用转换，参数BQ_BQZRZYCZY]'; raise BQZRZYCZERR; end if; LS_DQCZY0 := ad_CZY000; end if; --择日住院费用处理校验报告状态 begin select trim(MC0000) into ZY_ZRZYFYCLBGZT from BM_TYZD00 where ZDMC00='择日住院费用处理校验报告状态配置' and BH0000='ZY_ZRZYFYCLBGZT' and rownum=1; exception when others then ZY_ZRZYFYCLBGZT:='0'; end; --择日住院余额转入方式,1：现金,2：转账,默认1 begin select trim(VALUE0) into ZY_ZRZYYEZRFS from XT_XTCS00 where trim(NAME00)='ZY_ZRZYYEZRFS'; exception when others then ZY_ZRZYYEZRFS := '1'; end; LS_CZRQ00 := to_char(sysdate,'YYYYMMDD'); LS_CZSJ00 := to_char(sysdate,'HH24:MI:SS'); LS_COUNT0:=0; LS_ZYH000:=ad_ZYH000; LS_BRID00:=ad_BRID00; select ZWXM00 into ls_SJCZYXM from BM_YGBM00 where YGBH00=ad_CZY000; Select ZWXM00 into LS_ZWXM00 from BM_YGBM00 where YGBH00=LS_DQCZY0; MsgStr:='[查找住院病人信息]'; begin select ZYID00,DQBQ00,DQKS00,RYCWH0,XM0000,XB0000 into LS_ZYID00,LS_DQBQ00,LS_DQKS00,LS_RYCWH0,LS_XM0000,LS_XB0000 from ZY_BRXXB0 where BRID00=LS_BRID00 and zyh000=rpad(LS_ZYH000,12,' ') and BRZT00 not IN ('4','5','9') ; exception when no_data_found then as_YHMSG0:='未找到有效的住院病人信息！'; as_SYSMSG:=MsgStr||SQLERRM; rollback; return; end; MsgStr:='[查找住院病人未执行项目]'; LS_WZXXMMC:=''; LS_COUNT1:=0; if ZY_ZRZYFYCLBGZT='Y' then for c_wzxxm in (select b.ZLXMJC from sf_brxxb0 a,yj_yw0000 b,bm_zlzd00 c where a.GHRQ00>=to_char(sysdate-60,'YYYYMMDD') and a.SFZRZY in('Y','Z') and a.GHID00=b.ZYGHID and b.XMZT00<>'6' and b.zlxmid=c.zlxmid and a.BRID00=ad_BRID00 and b.SL0000>0 and nvl(b.FJDJBZ,'1') not in ('3','4') and not exists(select 1 from yj_yw0000 where BRID00=ad_BRID00 and CXDJH0=b.YJDJH0) and nvl(b.SFDJH0,0)>0 and ((nvl(BBZT00,'0') not in('6','9') and c.LBBH00=4) or (b.XMZT00<>'3' and c.LBBH00<>4)) and ( ( nvl(ad_YJID00,0) = 0 and not exists(select 1 from BM_TYZD00 where ZDMC00='择日住院不校验报告状态的诊疗项目' and nvl(DJ0000,0)=b.ZLXMID)) or ( nvl(ad_YJID00,0)> 0 and exists (select 1 from BM_GHKSTJ where ID0000=ad_YJID00 and DYID00=b.YJDJH0) and not exists(select 1 from BM_TYZD00 where ZDMC00='择日住院不校验报告状态的诊疗项目' and nvl(DJ0000,0)=b.ZLXMID) ) ) ) loop if LS_WZXXMMC=' ' then LS_WZXXMMC:=c_wzxxm.ZLXMJC; else LS_WZXXMMC:=LS_WZXXMMC||'；'||c_wzxxm.ZLXMJC; end if; LS_COUNT1:=LS_COUNT1+1; end loop; end if; if LS_COUNT1>0 then as_YHMSG0:='存在未执行项目：'||chr(13)||LS_WZXXMMC; as_SYSMSG:=MsgStr||SQLERRM; rollback; return; end if; MsgStr:='[插入择日流水表信息SF_ZRZYXX]'; select SQ_SF_ZRZYXX_ZRZYLS.NEXTVAL into LS_ZRZYLS from dual; as_ZRZYLS:=LS_ZRZYLS; insert into SF_ZRZYXX (ZRZYLS,--择日住院流水 CZRQ00,--'操作日期' CZSJ00,--'操作时间' BRID00,--病人ID BRXM00,--病人姓名 BRXB00,--病人性别 DJKS00,--登记科室 DJYS00,--登记医生 CZY000,--操作员 CZKS00,--操作科室 DJRQ00,--登记日期 DJSJ00,--登记时间 ZT0000,--状态 0:未转住院 1:已转住院 2:取消转住院 GHID00,--挂号id GHH000,--门诊登记的门诊号，可能是空的。 ZYID00,--住院id ZYH000,--转入正式住院的住院号，可能是空的。 ZZYRQ0,--转住院日期 ZZYSJ0 --转住院时间 ) values ( LS_ZRZYLS,--择日住院流水 LS_CZRQ00,--'操作日期' LS_CZSJ00,--'操作时间' LS_BRID00,--病人ID LS_XM0000,--病人姓名 LS_XB0000,--病人性别 0,--登记科室 0,--登记医生 LS_DQCZY0,--操作员 ad_CZYKS0,--操作科室 LS_CZRQ00,--登记日期 LS_CZSJ00,--登记时间 '0',--状态 0:未转住院 1:已转住院 2:取消转住院 0,--挂号id '',--门诊登记的门诊号，可能是空的。 LS_ZYID00,--住院id LS_ZYH000,--转入正式住院的住院号，可能是空的。 LS_CZRQ00,--转住院日期 LS_CZSJ00 --转住院时间 ); if nvl(ad_YJID00,0) > 0 then --------------------1:begin-------------- MsgStr:='[开始-->循环执行插入择日住院明细SF_ZRZYMX]GHID00='||v_GHID00; for MZZR in CUR_YJ_YW0000_YJID00 loop begin select count(1) into LS_COUNT0 from SF_BRFY00 where BRID00=LS_BRID00 and DJH000=MZZR.SFDJH0 and JZDH00>0; exception when others then LS_COUNT0:=0; end; if LS_COUNT0=0 then --未结算时插入--begin-- select SQ_SF_ZRZYMX_MXID00.NEXTVAL into LS_MXID00 from dual; insert into SF_ZRZYMX ( MXID00,--明细ID ZRZYLS,--择日住院流水 BRID00,--病人ID GHID00,--挂号id GHH000,--门诊登记的门诊号 ZYID00,--住院id ZYH000,--住院号 CZY000,--操作员 CZRQ00,--操作日期 CZSJ00,--操作时间 SL0000,--数量 ZXSL00,--执行数量 HJJE00,--合计金额 XMZT00, --项目状态,'0'申请'1'计价'2'已收费'3'已执行'4'已报告'5'疑退'6'作废 XMBZ00, --0:费用项目 1:处方项目2:医技项目 MZCZRQ,--门诊操作日期 MZCZSJ,--门诊操作时间 MZYJDH,--门诊医技单据号 MZYJCX,--门诊冲销医技单据号 ZRYJDH,--择日医技单号 MZSFDH,--门诊收费单据号 ZYCZRQ,--住院操作日期 ZYCZSJ--住院操作时间 ) values ( LS_MXID00,--明细ID LS_ZRZYLS,--择日住院流水 LS_BRID00,--病人ID MZZR.ZYGHID,--挂号id MZZR.ZYHGHH,--门诊登记的门诊号 LS_ZYID00,--住院id LS_ZYH000, --住院号 LS_DQCZY0, --操作员 LS_CZRQ00, --操作日期 LS_CZSJ00, --操作时间 MZZR.SL0000,--数量 MZZR.ZXSL00,--执行数量 MZZR.ZJE000,--合计金额 MZZR.XMZT00, --项目状态,'0'申请'1'计价'2'已收费'3'已执行'4'已报告'5'疑退'6'作废 2, --0:费用项目 1:处方项目2:医技项目 MZZR.KDRQ00,--门诊操作日期 MZZR.KDSJ00,--门诊操作时间 MZZR.YJDJH0,--门诊医技单据号 MZZR.CXDJH0,--门诊冲销医技单据号 0,--择日医技单号 MZZR.SFDJH0,--门诊收费单据号 LS_CZRQ00,--住院操作日期 LS_CZSJ00--住院操作时间 ); update SF_ZRZYXX set HJJE00=nvl(HJJE00,0)+MZZR.ZJE000 where BRID00=LS_BRID00 and ZRZYLS=LS_ZRZYLS; update SF_BRXXB0 set ZRZYLS=LS_ZRZYLS where BRID00=LS_BRID00 and GHID00=MZZR.ZYGHID; update ZY_BRXXB0 set ZRZYLS=LS_ZRZYLS where ZYID00=LS_ZYID00; end if;--未结算时插入--end-- MsgStr:='[结束-->循环执行插入择日住院明细SF_ZRZYMX]YJDH00='||MZZR.YJDJH0; end loop; --------------------1:end-------------- else v_GHID00_temp:=ad_GHID00||','; --循环记帐; --I,记录出现'*'的上一次的位置, --J,计数器, --L,记录出现'*'的当前位置 I:=0; J:=1; SELECT INSTR(V_GHID00_TEMP,',',1,J) INTO L FROM DUAL; --取出第J次出现'*'的位置 --------------------1:begin-------------- while L>0 loop v_GHID00:=SUBSTR(V_GHID00_TEMP,I+1,L-I-1); MsgStr:='[开始-->循环执行插入择日住院明细SF_ZRZYMX]GHID00='||v_GHID00; for MZZR in CUR_YJ_YW0000 loop begin select count(1) into LS_COUNT0 from SF_BRFY00 where BRID00=LS_BRID00 and MZID00=to_number(v_GHID00) and DJH000=MZZR.SFDJH0 and JZDH00>0; exception when others then LS_COUNT0:=0; end; if LS_COUNT0=0 then --未结算时插入--begin-- select SQ_SF_ZRZYMX_MXID00.NEXTVAL into LS_MXID00 from dual; insert into SF_ZRZYMX ( MXID00,--明细ID ZRZYLS,--择日住院流水 BRID00,--病人ID GHID00,--挂号id GHH000,--门诊登记的门诊号 ZYID00,--住院id ZYH000,--住院号 CZY000,--操作员 CZRQ00,--操作日期 CZSJ00,--操作时间 SL0000,--数量 ZXSL00,--执行数量 HJJE00,--合计金额 XMZT00, --项目状态,'0'申请'1'计价'2'已收费'3'已执行'4'已报告'5'疑退'6'作废 XMBZ00, --0:费用项目 1:处方项目2:医技项目 MZCZRQ,--门诊操作日期 MZCZSJ,--门诊操作时间 MZYJDH,--门诊医技单据号 MZYJCX,--门诊冲销医技单据号 ZRYJDH,--择日医技单号 MZSFDH,--门诊收费单据号 ZYCZRQ,--住院操作日期 ZYCZSJ--住院操作时间 ) values ( LS_MXID00,--明细ID LS_ZRZYLS,--择日住院流水 LS_BRID00,--病人ID MZZR.ZYGHID,--挂号id MZZR.ZYHGHH,--门诊登记的门诊号 LS_ZYID00,--住院id LS_ZYH000, --住院号 LS_DQCZY0, --操作员 LS_CZRQ00, --操作日期 LS_CZSJ00, --操作时间 MZZR.SL0000,--数量 MZZR.ZXSL00,--执行数量 MZZR.ZJE000,--合计金额 MZZR.XMZT00, --项目状态,'0'申请'1'计价'2'已收费'3'已执行'4'已报告'5'疑退'6'作废 2, --0:费用项目 1:处方项目2:医技项目 MZZR.KDRQ00,--门诊操作日期 MZZR.KDSJ00,--门诊操作时间 MZZR.YJDJH0,--门诊医技单据号 MZZR.CXDJH0,--门诊冲销医技单据号 0,--择日医技单号 MZZR.SFDJH0,--门诊收费单据号 LS_CZRQ00,--住院操作日期 LS_CZSJ00--住院操作时间 ); update SF_ZRZYXX set HJJE00=nvl(HJJE00,0)+MZZR.ZJE000 where BRID00=LS_BRID00 and ZRZYLS=LS_ZRZYLS; update SF_BRXXB0 set ZRZYLS=LS_ZRZYLS where BRID00=LS_BRID00 and GHID00=to_number(v_GHID00); update ZY_BRXXB0 set ZRZYLS=LS_ZRZYLS where ZYID00=LS_ZYID00; end if;--未结算时插入--end-- MsgStr:='[结束-->循环执行插入择日住院明细SF_ZRZYMX]GHID00='||v_GHID00; end loop; I:=L; J:=J+1; select INSTR(V_GHID00_TEMP,',',1,J) INTO L FROM DUAL; --取出第J次出现'*'的位置 end loop; --------------------1:end-------------- end if; MsgStr:='[查找住院病人特殊病种项目]'; LS_WZXXMMC:=' '; LS_COUNT1:=0; for c_tsbzxm in (select b.ZLXMJC from SF_ZRZYMX a,yj_yw0000 b where a.ZRZYLS=LS_ZRZYLS and a.BRID00=b.BRID00 and a.MZYJDH=b.YJDJH0 and trim(b.BH0000) is not null ) loop if LS_WZXXMMC=' ' then LS_WZXXMMC:=c_tsbzxm.ZLXMJC; else LS_WZXXMMC:=LS_WZXXMMC||'；'||c_tsbzxm.ZLXMJC; end if; LS_COUNT1:=LS_COUNT1+1; end loop; if LS_COUNT1>0 then as_YHMSG0:='该患者有特殊病种，不允许导入，请联系医生取消择日。'||chr(13)||LS_WZXXMMC; as_SYSMSG:=MsgStr||SQLERRM; rollback; return; end if; MsgStr:='[修改住院病人账户表的允许欠费金额]'; --update zy_brzhxx set YXQFJE=nvl(YXQFJE,0)+nvl(LS_BCFYZE,0) where BRID00=LS_BRID00; update zy_brzhxx set YXQFJE=nvl(YXQFJE,0)+9999 where BRID00=LS_BRID00 and JFLBID=2; MsgStr:='[开始循环执行YJ_YW0000表数据转换'; for ZRMX in CUR_SF_ZRZYMX loop select SQ_YJ_YW0000_YJDJH0.NEXTVAL into LS_YJDJH0 from DUAL; MsgStr:='[开始-->执行门诊转住院费用(YJ_YW0000)YJDJH0]='||ZRMX.YJDJH0; insert into YJ_YW0000 (YJDJH0,BRID00,ZYGHID,ZYHGHH,MZZYBZ,BRXM00,XB0000,CSRQ00,YEXM00, ZLXMID,ZLXMJC,SL0000,ZXSL00,DW0000,YJKSBH,KDYS00,KDYSXM,KDKSBH,YSSZKS, KDRQ00,KDSJ00,LRXM00,JZBZ00,FBZXBZ,BWMC00,APRXM0,APRQ00,APSJ00,JGLR00, ZXR000,ZXRXM0,ZXRQ00,ZXSJ00,ZDID00,ZDMC00,JGBGRQ,JGBGYS,BGYSXM,XMZT00, SFDJH0,YZCFID,YPNM00,DCBZ00,TQPC00,CXDJH0,ZJE000,BZ0000,YJLRBZ,KDBQ00, JGZCBZ,BQSFBZ,CH0000,NL0000,MC0000,BH0000,CFID00,BBID00,LRY000,BBBZ00, ZLDCCF,TXM000,DYRQTM,DYSJTM,YJDJDY,SM0000,ZXYS00,CXSL00,ZHXMID,SQID00, ZSDYMC,TSBSMC,JCQZY0,QSPC00,QSRQ00,QSSJ00,QSR000,QSRXM0,DYBGRQ,SYLSH0, JZQRR0,JZQRSJ,JZQRRQ,CJKSBH,YYID00,DWZBID,ZPJLID,ZHJE00,QRYSBH, QRRQ00,KYXMMC,FJDJBZ,HBDJH0,BWBH00,HBXHID,YZLX00,BBZT00,YSZID0, ZRZYLS,ZRYJDH) select LS_YJDJH0,BRID00,LS_ZYID00,LS_ZYH000,'1',BRXM00,XB0000,CSRQ00,YEXM00, ZLXMID,ZLXMJC,SL0000,ZXSL00,DW0000,YJKSBH,KDYS00,KDYSXM,KDKSBH,YSSZKS, KDRQ00,KDSJ00,LRXM00,JZBZ00,FBZXBZ,BWMC00,APRXM0,APRQ00,APSJ00,JGLR00, ZXR000,ZXRXM0,ZXRQ00,ZXSJ00,ZDID00,ZDMC00,JGBGRQ,JGBGYS,BGYSXM,XMZT00, SFDJH0,YZCFID,YPNM00,DCBZ00,TQPC00,CXDJH0,ZJE000,decode(ad_BQCZBZ,'1','操作人:'||ls_SJCZYXM,BZ0000),YJLRBZ,LS_DQBQ00, --取当前病区 JGZCBZ,BQSFBZ,CH0000,NL0000,MC0000,BH0000,CFID00,BBID00,LRY000,BBBZ00, ZLDCCF,TXM000,DYRQTM,DYSJTM,YJDJDY,SM0000,ZXYS00,CXSL00,ZHXMID,SQID00, ZSDYMC,TSBSMC,JCQZY0,QSPC00,QSRQ00,QSSJ00,QSR000,QSRXM0,DYBGRQ,SYLSH0, JZQRR0,JZQRSJ,JZQRRQ,CJKSBH,YYID00,DWZBID,ZPJLID,ZHJE00,QRYSBH, QRRQ00,KYXMMC,FJDJBZ,HBDJH0,BWBH00,HBXHID,YZLX00,BBZT00,YSZID0, LS_ZRZYLS,YJDJH0 from YJ_YW0000 Where BRID00=ZRMX.BRID00 and YJDJH0=ZRMX.YJDJH0; MsgStr:='[结束-->执行门诊转住院费用(YJ_YW0000)YJDJH0]='||ZRMX.YJDJH0; MsgStr:='[开始-->执行门诊转住院费用(YJ_YWMX00)YJDJH0]='||ZRMX.YJDJH0; insert into YJ_YWMX00 (ID0000,YJDJH0,ZLXMID,XMMC00,JGDW00,CKZ000, JGMS00,BZ0000,ZXRXM0,ZXRQ00,ZXSJ00,SL0000, DW0000,TXM000,DAXXID,ZRZYLS) Select SQ_YJ_YWMX00_ID0000.NEXTVAL,LS_YJDJH0,ZLXMID,XMMC00,JGDW00,CKZ000, JGMS00,BZ0000,ZXRXM0,ZXRQ00,ZXSJ00,SL0000, DW0000,TXM000,DAXXID,LS_ZRZYLS from YJ_YWMX00 where YJDJH0=ZRMX.YJDJH0; MsgStr:='[结束-->执行门诊转住院费用(YJ_YWMX00)YJDJH0]='||ZRMX.YJDJH0; MsgStr:='[开始-->执行门诊转住院费用(YJ_YWJJ00)YJDJH0]='||ZRMX.YJDJH0; insert into YJ_YWJJ00 (ID0000,YJDJH0,SFXMID,SL0000,SFCS00,JJJE00,GFJE00,JZJE00,ZFJE00, SFXX00,SFSX00,CXBZ00,YBBZ00,GDBZ00,FJFYBZ,MXID00,SFLB00,GJBM00,ZFBL00, QZFJE0,JMSHR0,YSFJE0,JMJE00,CXSL00,TXM000,XKZH00,ZRZYLS) select SQ_YJ_YWJJ00_ID0000.NEXTVAL,LS_YJDJH0,SFXMID,SL0000,SFCS00,JJJE00,GFJE00,JZJE00,ZFJE00, SFXX00,SFSX00,CXBZ00,YBBZ00,GDBZ00,FJFYBZ,MXID00,SFLB00,GJBM00,ZFBL00, QZFJE0,JMSHR0,YSFJE0,JMJE00,CXSL00,TXM000,XKZH00,LS_ZRZYLS from YJ_YWJJ00 where YJDJH0=ZRMX.YJDJH0; MsgStr:='[结束-->执行门诊转住院费用(YJ_YWJJ00)YJDJH0]='||ZRMX.YJDJH0; MsgStr:='修改医技业务表YJ_YW0000数据YJDJH0]='||ZRMX.YJDJH0; update YJ_YW0000 set ZRZYLS=LS_ZRZYLS,ZRYJDH=LS_YJDJH0 Where BRID00=ZRMX.BRID00 and YJDJH0=ZRMX.YJDJH0; update YJ_YWMX00 set ZRZYLS=LS_ZRZYLS Where YJDJH0=ZRMX.YJDJH0; update YJ_YWJJ00 set ZRZYLS=LS_ZRZYLS Where YJDJH0=ZRMX.YJDJH0; MsgStr:='修改SF_ZRZYMX或SF_ZRZYXX表数据YJDJH0]='||ZRMX.YJDJH0; update SF_ZRZYMX set ZRYJDH=LS_YJDJH0,ZYYJDH=LS_YJDJH0 Where BRID00=ZRMX.BRID00 and MZYJDH=ZRMX.YJDJH0; update SF_ZRZYXX set ZRZYJE=nvl(ZRZYJE,0)+nvl(ZRMX.HJJE00,0) where BRID00=ZRMX.BRID00 and ZRZYLS=LS_ZRZYLS; end loop; MsgStr:='[开始循环执行SF_BRFY00转ZY_BRFY00'; for ZRFY in CUR_SF_BRFY00 loop MsgStr:='[开始-->执行门诊转住院费用(ZY_FYMX00)]DJH000='||ZRFY.SFDJH0; select SQ_ZY_BRJFB0_DJH000.nextval into LS_DJH000 from dual; --注意KDBQ00 暂时取LS_DQBQ00 select KDRQ00,KDSJ00 into ls_KDRQ00,ls_KDSJ00 from YJ_YW0000 where SFDJH0=ZRFY.SFDJH0 and rownum=1; Insert into ZY_FYMX00( MXID00,DJH000,XMBH00,XMMC00,XMGG00, XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00, GFJE00,JZJE00,QZFJE0,JMJE00,JMSHR0, JMYY00,JMBZ00,CZRQ00,CZSJ00,ZXRQ00, ZXSJ00,SFYDJ0,CXBZ00,KDKS00,YSKS00, KDYS00,KDBQ00,ZXKS00,ZXYS00,SFLSCF, ZFBL00,GJBM00,SFLB00,PH0000,FLAG00, DJID00,YJJZID,SHJG00,YSZID0,ZRZYLS, KDRQ00,KDSJ00) select SQ_ZY_FYMX00_MXID00.nextval,LS_DJH000,A.XMBH00,A.XMMC00,A.XMGG00, A.XMDW00,A.XMDJ00,A.XMSL00,A.HJJE00,A.ZFJE00, A.GFJE00,A.JZJE00,A.QZFJE0,A.JMJE00,A.JMSHR0, A.JMYY00,A.JMBZ00,LS_CZRQ00,LS_CZSJ00,LS_CZRQ00, LS_CZSJ00,A.SFYDJ0,A.CXBZ00,A.KDKS00,A.YSKS00, A.KDYS00,LS_DQBQ00,A.ZXKS00,A.ZXYS00,A.SFLSCF, A.ZFBL00,A.GJBM00,A.SFLB00,A.PH0000,'0', null,null,null,null,LS_ZRZYLS, ls_KDRQ00,ls_KDSJ00 from SF_FYMX00 A, SF_BRFY00 B where A.DJH000 = B.DJH000 and a.XMBH00<9999999990 and b.BRID00=ZRFY.BRID00 and B.DJH000=ZRFY.SFDJH0 and exists(select 1 from SF_ZRZYMX where ZRZYLS=LS_ZRZYLS and BRID00=LS_BRID00 and MZSFDH=ZRFY.SFDJH0 and MZYJDH=a.YJDJH0) and not exists(select 1 from YJ_YW0000 where YJDJH0=a.YJDJH0 and SFDJH0=a.DJH000 and nvl(CXSL00,0)>0); --20210113部分冲销过的都不转住院，否则控制不住 Select sum(HJJE00)HJJE00,sum(ZFJE00)ZFJE00,sum(GFJE00)GFJE00,sum(JZJE00)JZJE00,sum(QZFJE0)QZFJE0,sum(JMJE00)JMJE00 into LS_HJJE00,LS_ZFJE00,LS_GFJE00,LS_JZJE00,LS_QZFJE0,LS_JMJE00 from ZY_FYMX00 where DJH000=LS_DJH000; MsgStr:='[结束-->执行门诊转住院费用(ZY_FYMX00)]DJH000='||ZRFY.SFDJH0; SP_ZY_UPDATE_DBZFY0(LS_ZYID00,LS_DJH000,'N',LS_DBZCWF,LS_DBZHCF);--修改超标床位费和除外耗材费 by ZYSF-20170324-002 MsgStr:='[开始-->执行门诊转住院费用(ZY_BRFY00)]DJH000='||ZRFY.SFDJH0; Insert into ZY_BRFY00( DJH000,YBDJH0,ZYH000,ZYID00,JFLBID, BRID00,XM0000,BRFB00,HJJE00,ZFJE00, GFJE00,JZJE00,QZFJE0,BRYE00,CZRQ00, CZSJ00,CZY000,CZYXM0,CZYKS0,JZDH00, CXDJH0,CXPJR0,CXYY00,SFYKBZ,YEXM00, BRYKYE,QDRQ00,ZRZYLS,DBZCWF,DBZHCF) select LS_DJH000,'0',LS_ZYH000,LS_ZYID00,2, b.BRID00,B.XM0000,B.BRFB00,LS_HJJE00,LS_ZFJE00, --B.HJJE00,B.ZFJE00,B.GFJE00,B.JZJE00,B.QZFJE0, LS_GFJE00,LS_JZJE00,LS_QZFJE0,0,LS_CZRQ00, LS_CZSJ00,LS_DQCZY0,LS_ZWXM00,ad_CZYKS0,B.JZDH00, B.CXDJH0,B.CXPJR0,B.CXYY00,'0',null, 0,LS_CZRQ00,LS_ZRZYLS,nvl(LS_DBZCWF,0),nvl(LS_DBZHCF,0) from SF_BRFY00 B where b.BRID00=ZRFY.BRID00 and B.DJH000=ZRFY.SFDJH0; MsgStr:='[结束-->执行门诊转住院费用(ZY_BRFY00)]DJH000='||ZRFY.SFDJH0; update SF_FYMX00 set ZRZYLS=LS_ZRZYLS where DJH000=ZRFY.SFDJH0 and XMBH00<9999999990; update SF_BRFY00 set ZRZYLS=LS_ZRZYLS where BRID00=ZRFY.BRID00 and DJH000=ZRFY.SFDJH0; update SF_ZRZYMX set ZYSFDH=LS_DJH000 where BRID00=ZRFY.BRID00 and MZSFDH=ZRFY.SFDJH0; update YJ_YW0000 set SFDJH0=LS_DJH000 where BRID00=ZRFY.BRID00 and YJDJH0 in (select ZYYJDH from SF_ZRZYMX where BRID00=ZRFY.BRID00 and MZSFDH=ZRFY.SFDJH0); end loop; MsgStr:='[修改住院病人账户表的允许欠费金额]'; --update zy_brzhxx set YXQFJE=nvl(YXQFJE,0)+nvl(LS_BCFYZE,0) where BRID00=LS_BRID00; update zy_brzhxx set YXQFJE=nvl(YXQFJE,0)-9999 where BRID00=LS_BRID00 and JFLBID=2; MsgStr:='[开始循环执行YJ_YW0000冲销'; for YJCX in CUR_CX_YJ_YW0000 loop MsgStr:='[开始-->执行YJ_YW0000冲销YJDJH0]='||YJCX.YJDJH0; if YJCX.XMZT00 in ('2','3','4') then --已经收费 SP_YJ_YWCX00( YJCX.YJDJH0,--医技单据号 LS_DQCZY0,--员工编号 ad_CZYKS0,--科室编号 'Y',--病区申请的项目是否直接退费 YJCX.SL0000, --冲消数量 -1, --明细项目ID 'N', --是否提交 默认提交 0, 'Y' ); --修改冲销择日流水号 Select YJDJH0,SFDJH0,ZJE000 into LS_CXYJDH,LS_CXSFDH,LS_CXZJE0 from YJ_YW0000 where BRID00=YJCX.BRID00 and ZYGHID=YJCX.GHID00 and nvl(CXDJH0,0)=YJCX.YJDJH0; if nvl(LS_CXYJDH,0)>0 then update YJ_YW0000 set ZRZYLS=LS_ZRZYLS,ZRYJDH=LS_CXYJDH,BZ0000=decode(ad_BQCZBZ,'1','操作人:'||ls_SJCZYXM,BZ0000) where BRID00=YJCX.BRID00 and ZYGHID=YJCX.GHID00 and YJDJH0=LS_CXYJDH; update SF_FYMX00 set ZRZYLS=LS_ZRZYLS where DJH000=LS_CXSFDH; update SF_BRFY00 set ZRZYLS=LS_ZRZYLS where BRID00=YJCX.BRID00 and DJH000=LS_CXSFDH; update SF_ZRZYXX set ZRTKJE=nvl(ZRTKJE,0)+abs(nvl(LS_CXZJE0,0)),ZT0000='1' where BRID00=YJCX.BRID00 and ZRZYLS=LS_ZRZYLS; update SF_ZRZYMX set MZYJCX=nvl(LS_CXYJDH,0),MZSFCX=nvl(LS_CXSFDH,0) where BRID00=YJCX.BRID00 and MZYJDH=YJCX.YJDJH0; end if; end if; MsgStr:='[结束-->执行YJ_YW0000冲销YJDJH0]='||YJCX.YJDJH0; end loop; --开始退药品begin for c_zrcf in(select CFLSH0,CFZT00,FYRQ00,FYSJ00,PFR000,a.GHID00,a.GHH000,SRRQ00,SRSJ00,a.DJH000,FHR000,CZY000,FYR000 from YF_MZCF00 a,SF_BRXXB0 b where a.BRID00=LS_BRID00 and a.GHID00=b.GHID00 and b.SFZRZY in('Y','Z') and SRRQ00>=to_char(sysdate-14,'YYYYMMDD') and nvl(a.DJH000,0)>0 and exists (select 1 from BM_GHKSTJ c,YF_MZCFMX d where c.ID0000=ad_YJID00 and BEIZHU=a.CFLSH0 and nvl(GHRQ00,'0')='1' and d.CFLSH0=a.CFLSH0 and c.XMHJ00=d.CFID00 and d.CXBZ00='Z') ) loop MsgStr:='[开始-->执行药品冲销CFLSH0]='||c_zrcf.CFLSH0; if c_zrcf.CFZT00<>'2' then --没有发药的不能转住院，请先发药 as_YHMSG0:=c_zrcf.CFLSH0||'没有发药的不能转住院，请先发药！'; as_SYSMSG:=MsgStr||SQLERRM; rollback; return; end if; -- and nvl(HBZYXM,'0')='1' 条件先注释调 update YF_MZCFMX set BCCXSL=YPZSL0,ZRZYLS=ls_ZRZYLS where CFLSH0=c_zrcf.CFLSH0 and CXBZ00='Z' and CFID00 in(select XMHJ00 from BM_GHKSTJ where ID0000=ad_YJID00 and BEIZHU=c_zrcf.CFLSH0 and nvl(GHRQ00,'0')='1' ) ; SP_YF_MZCFTY( P_CFLSH0=>c_zrcf.CFLSH0, --输入：处方号 P_CZYBH0=>c_zrcf.CZY000, --输入：操作员编码 P_FHRBH0=>c_zrcf.PFR000, --输入：复核人编码 P_RET_CFLSH0=>ls_CXCFH0 , --输出：退药的处方号 P_CLBZ00=>'0', --输入：处理标志 ‘0’ 开单和退药 '1' 只开单 P_ZJTFBZ=>'1', -- in char default '0' --直接退费标志'0'否，‘1’ 直接退 P_ZRZYLS=>ls_ZRZYLS, P_COMMIT=>'N' ); update YF_MZCF00 set (CYJE00,YXJE00,CFZJE0)= (select Sum(DECODE(YPDLBH,'1',round(LSDJ00*YPZSL0,2),0)), sum(DECODE(YPDLBH,'3',round(LSDJ00*YPZSL0,2),0)), sum(round(YPZSL0*LSDJ00,2)) from YF_MZCFMX where CFLSH0=ls_CXCFH0 and SFZBY0='N') where CFLSH0=ls_CXCFH0; update YF_MZCF00 set --FYRQ00=c_zrcf.FYRQ00, --FYSJ00=c_zrcf.FYSJ00, FYRQ00=to_char(sysdate,'yyyymmdd'), FYSJ00=to_char(sysdate,'hh24:mi:ss'), PFR000=c_zrcf.PFR000, FHR000=c_zrcf.FHR000, FYR000=c_zrcf.FYR000 where CFLSH0=ls_CXCFH0; select abs(CFZJE0),DJH000 into ls_CFZJE0,ls_CXDJH0 from YF_MZCF00 where CFLSH0=ls_CXCFH0; select SQ_SF_ZRZYMX_MXID00.NEXTVAL into LS_MXID00 from dual; insert into SF_ZRZYMX( --明细ID,择日住院流水,病人ID,挂号id,门诊登记的门诊号,住院id,住院号,操作员 MXID00, ZRZYLS, BRID00, GHID00, GHH000, ZYID00, ZYH000, CZY000, --操作日期,操作时间,数量,执行数量,合计金额,项目状态,费用项目(0:费用项目 1:处方项目2:医技项目),门诊操作日期 CZRQ00, CZSJ00, SL0000, ZXSL00, HJJE00, XMZT00, XMBZ00, MZCZRQ, --门诊操作时间,门诊医技单据号,门诊冲销医技单据号,择日医技单号,门诊收费单据号,住院操作日期,住院操作时间,门诊处方号 MZCZSJ, MZYJDH, MZYJCX, ZRYJDH, MZSFDH, ZYCZRQ, ZYCZSJ, MZCFH0, --门诊冲销处方号,住院处方号 MZCXCF, ZYCFH0 ) values ( --明细ID,择日住院流水,病人ID,挂号id,门诊登记的门诊号,住院id,住院号,操作员 LS_MXID00,LS_ZRZYLS,LS_BRID00,c_zrcf.GHID00,c_zrcf.GHH000,LS_ZYID00,LS_ZYH000,LS_DQCZY0, --操作日期,操作时间,数量,执行数量,合计金额,项目状态,费用项目(0:费用项目 1:处方项目2:医技项目),门诊操作日期 LS_CZRQ00,LS_CZSJ00,1,1,ls_CFZJE0, c_zrcf.CFZT00, 2,c_zrcf.SRRQ00, --门诊操作时间,门诊医技单据号,门诊冲销医技单据号,择日医技单号,门诊收费单据号,住院操作日期,住院操作时间,门诊处方号 c_zrcf.SRSJ00,0, 0, 0, c_zrcf.DJH000, LS_CZRQ00, LS_CZSJ00, c_zrcf.CFLSH0, --门诊冲销处方号,住院处方号 ls_CXCFH0, '' ); update SF_ZRZYXX set HJJE00=nvl(HJJE00,0)+ls_CFZJE0 where BRID00=LS_BRID00 and ZRZYLS=LS_ZRZYLS; update SF_BRXXB0 set ZRZYLS=LS_ZRZYLS where BRID00=LS_BRID00 and GHID00=c_zrcf.GHID00 ; update ZY_BRXXB0 set ZRZYLS=LS_ZRZYLS where ZYID00=LS_ZYID00 ; MsgStr:='[开始-->生成住院处方CFLSH0]='||ls_ZYCFH0; select SF_YK_GETLSH('ZYYF_ZYCFH') into ls_ZYCFH0 from dual where rownum=1; insert into YF_ZYCF00 (CFLSH0, YFBMBH , ZYID00 , BRID00, ZYH000, BRXM00, CH0000 , XB0000 , CSRQ00 , BRDZ00 , YSGZH0 , DJH000 , YSXM00 , SSKS00 , DQBQ00 , DQKS00 , YPDLBH , CFXZ00 , BZID00 , BZMC00 , ZYTS00 , FJFDJH , CZY000 , CFZT00 , SRRQ00 , SRSJ00 , JZRQ00 , JZSJ00 , CFZJE0 , BZ0000 , SFKSLY , PSZT00 , CFSRBZ, TQDJH0 , ZYDJS0 , LYKS00 , YEXM00 , YSZID0 , ZXYSZ0, CFZJE1 ,ZRZYLS ) select ls_ZYCFH0,YFBMBH, LS_ZYID00, LS_BRID00, LS_ZYH000, BRXM00, LS_RYCWH0, LS_XB0000, CSRQ00, null,YSGZH0, null, Y.YSXM00,Y.SSKS00, LS_DQBQ00, GHKS00, YPDLBH ,Y.CFXZ00, null,null,ZYTS00, null , CZY000,'0', SRRQ00,SRSJ00, null,null, abs(CFZJE0),'择日住院',SFKSLY,'0','4',0, ZYDJS0,LYKS00,'', 0,0,(select abs(sum(round(LSDJ00*YPZSL0,4))) from YF_MZCFMX where CFLSH0=ls_CXCFH0),ZRZYLS from YF_MZCF00 Y where CFLSH0=ls_CXCFH0; insert into YF_ZYCFMX( CFID00 , CFLSH0 , YPNM00 , YPMC00 , YPGG00 , GJDJ00 , LSDJ00 , PFDJ00 , YPYFMC , PCID00 , PCMC00 , CSL000 , JLDW00 , YYSJ00 , SFZBY0 , YYSJDW , ZQCD00 , YFSMMC , ZJYFMC , XMYFMC , YPZSL0 , CFDW00 , ZHL000 , CXBZ00 , CXCFID , PSLBID , SFXS00 , YPSCCJ , YPSCPH , KPHSS0 , HSKPPH , ZB0000 , BZ0000 , YZID00 , YZMXID , TSYS00 , ZFFSBZ,ZFBL00,YBLB00,QZFJE0,GFJE00,ZFJE00,HJJE00,ZRZYLS) select SQ_YF_ZYCFMX_CFID00.NextVal ,ls_ZYCFH0 ,Y.YPNM00 ,Y.YPMC00 , Y.YPGG00 ,Y.GJDJ00 , Y.LSDJ00,Y.PFDJ00 ,Y.YPYFMC ,(select PCID00 from BM_PC0000 where PCMC00=Y.PCMC00) ,Y.PCMC00 ,Y.CSL000 , Y.JLDW00, Y.YYSJ00, 'N' ,substr(trim(Y.YYSJDW),1,6) ,Y.ZQCD00 ,Y.YFSMMC , null ,Y.XMYFMC ,abs(Y.YPZSL0) , Y.CFDW00,Y.ZHL000 , 'Z' , null , null, 'Y' , null , null , null , null , null , Y.BZ0000, 0 ,0,Y.TSYS00 ,Y.ZFFSBZ,ZFBL00,YBLB00,QZFJE0,GFJE00,ZFJE00,HJJE00,ls_ZRZYLS from YF_MZCFMX Y where CFLSH0=ls_CXCFH0; update YF_ZYCFMX set QZFJE0=round(LSDJ00*YPZSL0,2), GFJE00=0, ZFJE00=round(LSDJ00*YPZSL0,2), HJJE00=round(LSDJ00*YPZSL0,2), HJJE01=round(LSDJ00*YPZSL0,4) where CFLSH0=ls_ZYCFH0; --生成住院的处方收费 select SQ_ZY_BRJFB0_DJH000.nextval into ls_ZYDJH0 from DUAL; SP_YF_ZYCFJZ( P_CFLSH0=>ls_ZYCFH0, --处方流水号 P_DQRQ00=>to_char(sysdate,'YYYYMMDD'), --日期 P_DQSJ00=>to_char(sysdate,'HH24:MI:SS'), --时间 P_CZYBH0=>LS_DQCZY0, --操作员编码 P_DJH000=>ls_ZYDJH0, --输入：收费单据号 P_ZRZYLS=>LS_ZRZYLS ); select count(*) into LS_COUNT1 from ZY_BRFY00 where DJH000=ls_ZYDJH0; if LS_COUNT1=0 then as_YHMSG0:='住院处方收费错误！'; as_SYSMSG:='SP_YF_ZYCFJZ'; rollback; return; end if ; --原处方 update YF_ZYCF00 set DJH000=ls_ZYDJH0,CFZT00='2',JZRQ00=to_char(sysdate,'YYYYMMDD'),JZSJ00=to_char(sysdate,'HH24:MI:SS') ,FYRQ00=to_char(sysdate,'YYYYMMDD'),FYSJ00=to_char(sysdate,'HH24:MI:SS') ,FYR000=c_zrcf.FYR000,FHR000=c_zrcf.FHR000,PFR000=c_zrcf.PFR000 where CFLSH0=ls_ZYCFH0; update YF_MZCF00 set ZRZYLS=LS_ZRZYLS where CFLSH0=c_zrcf.CFLSH0 ; update SF_BRFY00 set ZRZYLS=LS_ZRZYLS where BRID00=LS_BRID00 and DJH000=c_zrcf.DJH000; update SF_ZRZYMX set ZYSFDH=ls_ZYDJH0 where BRID00=LS_BRID00 and MZSFDH=c_zrcf.DJH000; if nvl(ls_ZYDJH0,0)>0 then --门诊冲销处方 update YF_MZCF00 set BZ0000='择日转住院@'||ls_ZYCFH0 where BRID00=LS_BRID00 and GHID00=c_zrcf.GHID00 and CFLSH0=ls_CXCFH0; update SF_FYMX00 set ZRZYLS=LS_ZRZYLS where DJH000=ls_CXDJH0; update SF_BRFY00 set ZRZYLS=LS_ZRZYLS where BRID00=LS_BRID00 and DJH000=ls_CXDJH0; --修改门诊应退金额 update SF_ZRZYXX set ZRTKJE=nvl(ZRTKJE,0)+abs(nvl(ls_CFZJE0,0)),ZT0000='1' where BRID00=ls_BRID00 and ZRZYLS=LS_ZRZYLS; update SF_ZRZYMX set MZCXCF=nvl(ls_CXCFH0,0),MZSFCX=nvl(ls_CXDJH0,0) where BRID00=ls_BRID00 and MZCFH0=c_zrcf.CFLSH0; end if; end loop; --开始退药品end; MsgStr:='[查询本次择日住院总费用]'; begin select sum(HJJE00)into LS_BCFYZE from SF_ZRZYXX where BRID00=LS_BRID00 and ZRZYLS=LS_ZRZYLS; exception when others then LS_BCFYZE:=0; end; if nvl(LS_BCFYZE,0)<=0 then as_YHMSG0:='门诊费用已结算,未找到有效的需要转为住院的费用！'; as_SYSMSG:=MsgStr||SQLERRM; rollback; return; end if; MsgStr:='[判断退款金额和账户现金余额]'; begin select nvl(sum(ZRTKJE),0) into LS_ZRTKJE from SF_ZRZYXX where BRID00=LS_BRID00 and ZRZYLS=LS_ZRZYLS; exception when others then LS_ZRTKJE:=0; end; begin Select ZYE000,XJYE00,TYJE00 into LS_ZYE000,LS_XJYE00,LS_TYJE00 from SF_BRZHXX where BRID00=LS_BRID00 and JFLBID=1; exception when others then LS_ZYE000:=0; LS_XJYE00:=0; LS_TYJE00:=0; end; if (nvl(LS_XJYE00,0)<=0) or (nvl(LS_ZRTKJE,0)<=0)or (nvl(LS_XJYE00,0)=nvl(LS_BCFYZE,0) then LS_WJSJE0:=nvl(LS_WJSJE0,0)-nvl(LS_BCFYZE,0); end if; update ZY_BRFY00 set BRYE00=nvl(LS_ZYE000,0) where BRID00=LS_BRID00 and ZYID00=LS_ZYID00 and ZRZYLS=LS_ZRZYLS; update ZY_BRZHXX set WJSJE0=nvl(LS_WJSJE0,0) where BRID00=LS_BRID00 and JFLBID=2; --现金改转账 if ZY_ZRZYYEZRFS = '2' then select XJYE00,ZZYE00 into LS_ZHXJYE,LS_ZHZZYE from ZY_BRZHXX where BRID00=LS_BRID00 and JFLBID=2; update ZY_BRJFB0 set ZFFS00=2,ZFFSMC='转账' where BRID00=LS_BRID00 and DJH000=LS_ZYJFDH and ZFFS00=1; update ZY_PJSYQK set ZZJE00=ZZJE00+XJJE00,XJJE00=0 where PJH000 in (select PJH000 from ZY_BRJFB0 where BRID00=LS_BRID00 and DJH000=LS_ZYJFDH); update ZY_BRZHXX set XJYE00=LS_ZHXJYE,ZZYE00=LS_ZHZZYE where BRID00=LS_BRID00 and JFLBID=2; end if; else as_YHMSG0:='交住院预交金错误！'; as_SYSMSG:=MsgStr||as_SYSMSG; rollback; return; end if; MsgStr:='[结束-->充住院预交金]'; MsgStr:='[开始-->同步给海泰接口]'; LS_SQL000:='begin SP_BLJK_CLZRZYYZ('||LS_ZYID00||','||LS_ZRZYLS||','||ad_CZY000||','||ad_CZYKS0||'); end ;'; begin execute immediate LS_SQL000; exception when others then LS_COUNT0:=0; end; exception when BQZRZYCZERR then as_YHMSG0:='门诊择日住院费用转换失败,请联系管理！'; as_SYSMSG:=substrb(MsgStr||'-'||SQLERRM||'执行存储过程错误:SP_ZY_MZZRFYCL',1,255); rollback; when no_data_found then as_YHMSG0:='门诊择日住院费用转换失败,请联系管理！'; as_SYSMSG:=substrb(MsgStr||'-'||SQLERRM||'执行存储过程错误:SP_ZY_MZZRFYCL',1,255); rollback; when others then as_YHMSG0:='门诊择日住院费用转换失败,请联系管理！'; as_SYSMSG:=substrb(MsgStr||'-'||SQLERRM||'执行存储过程错误:SP_ZY_MZZRFYCL',1,255); rollback; end SP_ZY_MZZRFYCL; / -- End of DDL Script for Procedure SD_HOSPITAL.SP_ZY_MZZRFYCL