CREATE OR REPLACE PROCEDURE SP_SST_ZZCFQH_YKT ( P_ZDBH00 in varchar2, --终端编号 P_CARDNO in varchar2, --卡号 P_QYTSXX out varchar2, --取药提示信息 P_CFTSXX out varchar2, --处方数提示信息 --P_DJFJE0 OUT varchar2, --待交费金额，如果需要补交费用，该字段写入需要交多少钱 P_ERRMSG out varchar2, --错误信息，NULL表示正确 R_CUR4 out pkg_fee_qry.my_cursor --结果集游标 ) as -- MODIFICATION HISTORY -- Person Date Comments -- zhangyc 2013.08.19 "未找到未记账的处方和未发药的处方" 改为 如果已经取过号，那么提示所有的取号信息先对所有未记账处方一起扣费，之后再一起生成队列以及发药。 -- zhangyc 2014.01.01 重新整理过程 -- zhangyc 2014.03.12 增加QHDDY0=nvl(QHDDY0,0)+1 by ZZJJK-20140311-001 --zhangyc 2014.04.10 增加MZPY_JZHSFZJFY='Y' 是否直接发药 --zhangyc 2014.04.16 判断是否配药窗口是否开启 --zhangyc 2014.04.30 增加等待人数 --zhangyc 2014.07.01 增加参数SST_DMCFSFYXQDFY控制毒麻处方是否打印取药单后直接发药 --zhangyc 2014.08.25 增加YYID00='220007','820007' 显示中药待煎信息 -- linzy 2014.12.29 游标CUR_YKFCF增加当天录入处方日期过滤，避免隔天已记账未发药的处方入队列，导致处方已发药，但是配药未打处方的BUG(配药只打印当天处方) -- dsm 2015.01.20 自助机自助取药后,显示的IC余额与实际余额不符， sum(ye0000)--> ye0000这个出参 for ZZJJK-20150120-001 --zhangyc 2015.11.02 增加控制某台自助只能发往某些药房SST_YGBHDY.QYCKBH表中维护 --zhangyc 2017.07.26 增加参数SST_ZZJCFQHHBDY控制处理 -- -------- ------------- ----------------------------------------------------------------------------------------------- LS_NUM00 number(8); LS_NUM01 number(8); LS_NUM02 number(8); LS_NUM03 number(8); --患者代煎数 LS_ZYE000 number(12,4); LS_TYJE00 number(12,4); LS_ZFJE00 number(12,4); PZXCGBZ number(12); PTSXX00 varchar2(400); PXXXX00 varchar2(400); LS_DJH000 SF_BRFY00.DJH000%type; LS_CARDNO IC_YHXX00.ICKH00%type; LS_BRID00 BM_BRXXB0.BRID00%type; LS_ZT0000 IC_YHXX00.ZT0000%type; LS_YGBH00 BM_YGBM00.YGBH00%type; LS_YGXM00 BM_YGBM00.ZWXM00%type; LS_CZYKS0 SF_BRFY00.CZYKS0%type; MZHS_CYPHYF varchar2(200); LS_HJJE00 SF_BRFY00.HJJE00%type; LS_JZJE00 SF_BRFY00.HJJE00%type; LS_GFJE00 SF_BRFY00.HJJE00%type; ZZQH_DRJRQHSJFGD varchar2(200); ZZQH_JRPDJHCFBLTS varchar2(200); MZPY_JZHSFZJFY varchar2(200); ZZQH_YYZZQHXXTS varchar2(200); VSFTSLC varchar2(2); --是否特殊流程 '0'正常'1'特殊，如果定义了字典名称为"不进入排号队列药品目录"，那么走特殊流程 VSFTSYXCK varchar2(2); --是否提示优先窗口 '0'不提示 '1'提示 VSFTSDMCK varchar2(2); --毒麻处方是否提示到毒麻窗口 LS_CZRQ00 varchar2(8); LS_SRRQ00 varchar2(8); LS_DYRQ00 varchar2(30); LS_CFTSXX varchar2(400); --处方提示信息 LS_DMTSXX varchar2(400); --毒麻提示信息 LS_FDRCFTS varchar2(400); --判断病人非当日处方 LS_YXCKTS varchar2(400); --优先取号提示 LS_DMCKTS varchar2(400); --毒麻取号提示 LS_WPSTS varchar2(400); --未皮试提示 LS_PSWTGTS varchar2(400); --皮试未通过提示 LS_ZJQYTS varchar2(400); --直接刷卡取药提示 LS_YFPYCKWKQTS varchar2(400); --药房配药窗口未启用提示 LS_YQHTSXX varchar2(400); --已取号信息提示 LS_YPDJTS varchar2(400); --代煎提示 LS_ZXH000 number(12); LS_CYJE00 number(12,4); VPYCKSL number(12,4); --配药窗口数量 VFYCKSL number(12,4); --发药窗口数量 YS_JRZJPH varchar2(200);--药房窗口编号 SST_DMCFSFYXQDFY varchar2(10); VYYID00 XT_YYXX00.YYID00%TYPE; LS_XSZYDJ varchar2(2); LS_QYCKBH varchar2(100); LS_WQHXX0 varchar2(100); SST_ZZJCFQHHBDY varchar2(100);--自助机处方取药多张凭证是否打印 Y:合并 N:不合并 默认值:N --VSQL2毒麻处方，VSQL3未皮试处方,sPSJG00皮试结果 cursor CUR_WKFCF is --未扣费处方游标 select CFLSH0, BRID00, GHH000, THBZ00, YFBMBH, YPDLBH, DLXH00, CFZT00,psjg00, PSZT00,GHID00,FBBH00,BRXM00 from VW_SST_QHXX00 c where BRID00 = LS_BRID00 and ((SST_DMCFSFYXQDFY='N'and CFDGS0 <> '0') or ( SST_DMCFSFYXQDFY='Y' and 1=1)) and ((LS_QYCKBH in ('0') and 1=1) or (LS_QYCKBH not in ('0') and instrb(LS_QYCKBH,to_char(c.YFBMBH))>0)) and CFZT00 in ('0') and instrb(trim(YS_JRZJPH),to_char(c.YFBMBH))>0 ; cursor CUR_YKFCF is --已扣费处方游标 select CFLSH0, BRID00, GHH000, THBZ00, YFBMBH, YPDLBH, DLXH00, CFZT00,PSJG00, PSZT00 FROM VW_SST_QHXX00 C where BRID00 = LS_BRID00 and SRRQ00=to_char(sysdate,'yyyymmdd') and ((SST_DMCFSFYXQDFY='N'and CFDGS0 <> '0') or ( SST_DMCFSFYXQDFY='Y' and 1=1)) and ((LS_QYCKBH in ('0') and 1=1) or (LS_QYCKBH not in ('0') and instrb(LS_QYCKBH,to_char(c.YFBMBH))>0)) and CFZT00 in ('1') and instrb(trim(YS_JRZJPH),to_char(c.YFBMBH))>0; cursor CUR_WPDCF is --毒麻和直接刷卡取药处方数 select distinct BMMC00 from (select A.YFBMBH, B.BMMC00,decode (A.CFDGS0, '0', '0', '1') CFDGS0,count (1) cfsl00,0 tssl00 from YF_MZCF00 A, BM_BMBM00 B where a.SRRQ00 >=LS_SRRQ00 and a.YFBMBH = b.bmbh00 and a.brid00 = ls_brid00 and a.sfksly = 'N' and a.cfzje0 > 0 and not exists (select 1 from YF_CFDL00 where cflsh0 = a.cflsh0) and SF_MZ_PSCFYS(a.ghh000, a.cflsh0) not in (-1, -2) and ((LS_QYCKBH in ('0') and 1=1) or (LS_QYCKBH not in ('0') and instrb(LS_QYCKBH,to_char(a.YFBMBH))>0)) and instrb(trim(YS_JRZJPH),to_char(A.YFBMBH))>0 and ( a.cfzt00 IN ('0', '1') OR ( a.cfzt00 = '2' and exists (select 1 from yf_cfdl00 where cflsh0 = a.cflsh0 and zt0000 in ('0', '1', '2')) ) ) group by a.yfbmbh,b.bmmc00,DECODE (a.cfdgs0, '0', '0', '1') ); --非单日处方数 cursor cur_fdrcf IS select C.YFBMBH, D.BMMC00, count (1) CFSL00 from YF_MZCF00 C, BM_BMBM00 D where C.YFBMBH = D.BMBH00 AND C.BRID00 = LS_BRID00 and ((LS_QYCKBH in ('0') and 1=1) or (LS_QYCKBH not in ('0') and instrb(LS_QYCKBH,to_char(c.YFBMBH))>0)) --and instrb(trim(YS_JRZJPH),to_char(c.yfbmbh))>0 and ( ( (c.cfzt00 = '0' OR c.cfzt00 = '1') and c.SRRQ00 >=LS_SRRQ00 --and c.SRRQ00 ||c.SRSJ00 <=to_char (sysdate - 1 / 3 - 90,'YYYYMMDDHH24:MI:SS') --and c.SRRQ00 >=to_char (sysdate + 1 - 10, 'YYYYMMDD')--YF_MZCFBLTS 处方保留天数2 and c.bccfh0 is null ) and not exists (select 1 from yf_mzcf00 where brid00 = c.brid00 and bccfh0 = c.cflsh0) ) and c.sfksly = 'N' group by C.YFBMBH, D.BMMC00; cursor CUR_FYYFBH is select distinct a.BMBH00 from BM_CKBM00 a,BM_CKBM00 b,BM_CKDY00 c where a.FLAG00='2' and b.FLAG00='3' and a.CKBH00=c.SCKBH0 and b.CKBH00=c.DCKBH0 and a.ZT0000='0' and b.ZT0000='0' and instrb(trim(MZHS_CYPHYF),to_char(a.BMBH00))>0 and instrb(trim(MZHS_CYPHYF),to_char(b.BMBH00))>0; cursor CUR_WQHCF is select distinct YFMC00 from VW_SST_QHXX00 C where BRID00 = LS_BRID00 and ((SST_DMCFSFYXQDFY='N'and CFDGS0 <> '0') or ( SST_DMCFSFYXQDFY='Y' and 1=1)) and CFZT00 in ('0', '1') and instrb(LS_QYCKBH,to_char(c.YFBMBH))<=0 and instrb(trim(YS_JRZJPH),to_char(c.YFBMBH))>0; --取药凭证数据整理 cursor CUR_QYPZXX is select distinct BRID00, PYCKBH, QYTM00 as QYTM00, YFMC00, CKMC00 as QYCK00, FYDLXH as QYLSH0, BRXM00 as XMING0, max(DDRS00)as DDRS00, max(PYDLBH)as PYDLBH, max(WEEK00)as XQ0000, max(NL0000)as NL0000, max(XB0000)as XB0000, nvl(sum(ZYDJS0),0)ZYDJS0, nvl(sum(CFJE00),0)CFJE00,zyts00 from VW_SST_ZZCFQH a where BRID00 = LS_BRID00 and ZXH000 = LS_ZXH000 and instrb(trim(YS_JRZJPH),to_char(a.YFBMBH))>0 group by BRID00,BRXM00,PYCKBH,QYTM00, YFMC00,CKMC00,FYDLXH,zyts00; --判断是否存在为停用药房部门编号 cursor CUR_GET_TYPYCK is select distinct YFMC00 from VW_SST_QHXX00 c where BRID00 = LS_BRID00 and ((SST_DMCFSFYXQDFY='N' and CFDGS0 <> '0') or ( SST_DMCFSFYXQDFY='Y' and 1=1)) and ((LS_QYCKBH in ('0') and 1=1) or (LS_QYCKBH not in ('0') and instrb(LS_QYCKBH,to_char(c.YFBMBH))>0)) and CFZT00 in ('0','1') and instrb(trim(YS_JRZJPH),to_char(c.YFBMBH))=0 ; --取当日已取号信息 cursor CUR_GET_DRQHXX is select distinct d.bmmc00,c.ckmc00,b.fydlxh from yf_mzcf00 a,yf_cfdl00 b,BM_CKBM00 c,bm_bmbm00 d where a.cflsh0=b.cflsh0 and b.zt0000 in ('0','1','2') and b.fyckbh=c.ckbh00 and c.bmbh00=d.bmbh00 and a.brid00=LS_BRID00 and a.srrq00=to_char(sysdate,'yyyymmdd'); begin select SF_SF_GETYYID00() YYID00 into Vyyid00 from dual; if Trim(VYYID00) in ('220007','820007') then LS_XSZYDJ:='Y'; else LS_XSZYDJ:='N'; end if; LS_DYRQ00:= to_char(sysdate,'YYYY-MM-DD')|| ' '||to_char(sysdate, 'hh24:mi:ss'); --P_DJFJE0 := '0'; VSFTSYXCK:= '0'; VSFTSDMCK:= '0'; LS_NUM00 := 0; LS_NUM01 := 0; LS_NUM02 := 0; LS_NUM03 := 0; LS_ZYE000 := 0; LS_ZFJE00 := 0; LS_CFTSXX := ''; LS_DMTSXX := ''; LS_FDRCFTS := ''; LS_YXCKTS := ''; LS_DMCKTS := ''; LS_WPSTS := ''; LS_PSWTGTS := ''; LS_ZJQYTS := ''; YS_JRZJPH:=''; LS_WQHXX0:=''; P_QYTSXX:=''; P_CFTSXX:=''; P_ERRMSG:=''; LS_YFPYCKWKQTS:=''; LS_YPDJTS:=''; LS_YQHTSXX:=''; begin select YGBH00,ZWXM00,nvl(QYCKBH,'0') into LS_YGBH00,LS_YGXM00,LS_QYCKBH from VW_SST_YGBMDYXX where ZZJBH0 = P_ZDBH00; exception when no_data_found then p_errmsg :='终端编号:' || p_zdbh00 || '在本院SST_YGBHDY表中未对应收费员!'; return; end; if LS_QYCKBH is null then LS_QYCKBH:='0'; end if; select SF_SST_GETICKH00_YKT (P_CARDNO)into LS_CARDNO from dual; --读卡 begin select BRID00, ZT0000 into LS_BRID00, LS_ZT0000 from ic_yhxx00 where ickh00 = LS_CARDNO AND zt0000 = 1; exception when no_data_found then p_errmsg := '卡号:' || ls_cardno || '在本院所对应的病人信息未找到!'; return; end; begin select bmbh00 into LS_CZYKS0 from bm_ygbm00 where ygbh00 = ls_ygbh00; exception when others then ls_czyks0 := 0; end; --进入诊结排号的药房 默认为0 若有药房 格式为 药房1,药房2.....n begin select trim(value0) into MZHS_CYPHYF from xt_xtcs00 where name00 = 'MZHS_CYPHYF'; exception when others then MZHS_CYPHYF := '0'; end; ----------------*******---------------------- begin select trim(value0) into YS_JRZJPH from xt_xtcs00 where name00 = 'YS_JRZJPH'; exception when others then YS_JRZJPH := '0'; end; --毒麻处方是否允许在自助机打印取药单并发药 Y:是 N:否 默认值:Y begin select trim(value0) into SST_DMCFSFYXQDFY from xt_xtcs00 where name00 = 'SST_DMCFSFYXQDFY'; exception when others then SST_DMCFSFYXQDFY := 'Y'; end; --当天处方进入排号队列的时间处理，00:00:00 取号时间在当天8天之前那么输入时间在前一天的处方也可进入排号队列，<>00:00:00表示输入时间在当天并且只有处方的输入时间在参数设置时间点后的才进入排号队列 默认00:00:00 begin select trim(value0) into ZZQH_DRJRQHSJFGD from xt_xtcs00 where name00 = 'ZZQH_DRJRQHSJFGD'; exception when others then ZZQH_DRJRQHSJFGD := '00:00:00'; end; --参与排队的处方按处方输入日期控制，参数值天数内的处方参与排号，为0的话是当天的处方参与排号，至于时间点的分割沿用参数ZZQH_DRJRQHSJFGD控制的做法 默认为0 begin select trim(value0) into ZZQH_JRPDJHCFBLTS from xt_xtcs00 where name00 = 'ZZQH_JRPDJHCFBLTS'; exception when others then ZZQH_JRPDJHCFBLTS := '1'; end; LS_SRRQ00:=to_char(sysdate-1/3-to_number(ZZQH_JRPDJHCFBLTS),'YYYYMMDD'); LS_CZRQ00:=LS_SRRQ00; --配药自助取号记帐后是否直接发药 Y:是；N:不发药; 默认:N begin select trim(value0) into MZPY_JZHSFZJFY from xt_xtcs00 where name00 = 'MZPY_JZHSFZJFY'; exception when others then MZPY_JZHSFZJFY := 'N'; end; --医院自助取号信息提示，1、南平市第一医院，默认0 begin select trim(value0) into ZZQH_YYZZQHXXTS from xt_xtcs00 where name00 = 'ZZQH_YYZZQHXXTS'; exception when others then ZZQH_YYZZQHXXTS := '0'; end; --SST_ZZJCFQHHBDY：自助机处方取药多张凭证是否打印 Y:合并 N:不合并 默认值:N begin select trim(value0) into SST_ZZJCFQHHBDY from XT_XTCS00 where name00 = 'SST_ZZJCFQHHBDY'; exception when others then SST_ZZJCFQHHBDY := 'N'; end; ---->1:过滤没有开启状态的配药和发药窗口的药房<---- for REC_YFBH in CUR_FYYFBH loop if trim(YS_JRZJPH) is not null then YS_JRZJPH:=YS_JRZJPH||','||to_char(REC_YFBH.BMBH00); else YS_JRZJPH:=to_char(REC_YFBH.BMBH00); end if; end loop; if trim(YS_JRZJPH) is null then YS_JRZJPH:='0'; end if; ---->2:判断配药窗口和发药窗口是否都开启<---- select count(*) into VPYCKSL from BM_CKBM00 a,BM_CKDY00 b where a.CKBH00=b.SCKBH0 and a.FLAG00='2' and a.ZT0000='0'; --配药窗口数量 select count(*) into VFYCKSL from BM_CKBM00 a,BM_CKDY00 b where a.CKBH00=b.DCKBH0 and a.FLAG00='3' and a.ZT0000='0'; --发药窗口数量 if (nvl(VPYCKSL,0)=0) or (nvl(VFYCKSL,0)=0) or (trim(YS_JRZJPH)='0') then --判断配药窗口和发药窗口是否都关闭 p_errmsg := '由于排队系统窗口已经关闭,请直接到药房窗口取药或者等上班时间再来取药！'; return; end if; ---->3:不进入排号队列药品目录<---- select COUNT (*) sl into ls_num00 from BM_TYZD00 where zdmc00 = '不进入排号队列药品目录' and yxbz00 = '1' and dj0000> 0; IF ls_num00 > 0 then vsftslc := '1'; else vsftslc := '0'; end if; ---->5:判断病人账户余额是否足够然后扣费<---- select ZYE000,TYJE00 into LS_ZYE000,LS_TYJE00 from SF_BRZHXX where brid00 =LS_BRID00; --取病人总余额 if nvl(LS_TYJE00,0)<0 then LS_ZYE000:=nvl(LS_ZYE000,0); else LS_ZYE000:=nvl(LS_ZYE000,0)-nvl(LS_TYJE00,0); end if; --取自费金额-- select sum (b.zfje00) into LS_ZFJE00 from YF_MZCFMX B, YF_MZCF00 C where C.BRID00 = LS_BRID00 and B.CFLSH0 = C.CFLSH0 and C.CFZT00 = '0' and C.BCCFH0 is null and C.SFKSLY = 'N' and instrb(trim(YS_JRZJPH),to_char(c.yfbmbh))>0 and ((LS_QYCKBH in ('0') and 1=1) or (LS_QYCKBH not in ('0') and instrb(LS_QYCKBH,to_char(c.YFBMBH))>0)) and ((SST_DMCFSFYXQDFY='N' and C.CFXZ00='0') or (SST_DMCFSFYXQDFY='Y'and 1=1)) and C.SRRQ00>=LS_SRRQ00 and ((ZZQH_DRJRQHSJFGD='00:00:00' and 1=1) or (ZZQH_DRJRQHSJFGD<>'00:00:00' and C.SRSJ00>=ZZQH_DRJRQHSJFGD)); if LS_ZYE000-LS_ZFJE00<0 then --P_DJFJE0 := abs(LS_ZYE000-LS_ZFJE00); p_errmsg :='院内账户余额不足,还需交:'||trim(to_char (ABS (NVL (LS_ZYE000, 0) - NVL (LS_ZFJE00, 0)),'9999990.00'))||'元'; return; end if; select count (1) into ls_num00 from VW_SST_QHXX00 C where BRID00 = LS_BRID00 and ((SST_DMCFSFYXQDFY='N'and CFDGS0 <> '0') or ( SST_DMCFSFYXQDFY='Y' and 1=1) ) and CFZT00 in ('0', '1') and ((LS_QYCKBH in ('0') and 1=1) or (LS_QYCKBH not in ('0') and instrb(LS_QYCKBH,to_char(c.YFBMBH))>0)) and instrb(trim(YS_JRZJPH),to_char(c.YFBMBH))>0 ; --判断非设置窗口是否有处方信息有的话提示下 if LS_QYCKBH not in ('0') then for REC_WQHCF in CUR_WQHCF loop if LS_WQHXX0 is not null then LS_WQHXX0:=LS_WQHXX0||','||REC_WQHCF.YFMC00; else LS_WQHXX0:=REC_WQHCF.YFMC00; end if; end loop; if LS_WQHXX0 is not null then LS_WQHXX0:='['||LS_WQHXX0||']有处方未取号,请患者到该药房自助机上取号!'; end if; end if; --判断是否存在停用药房部门编号 if LS_QYCKBH in ('0') then for REC_TYPYCK in CUR_GET_TYPYCK loop if LS_YFPYCKWKQTS is not null then LS_YFPYCKWKQTS:=LS_YFPYCKWKQTS||','||REC_TYPYCK.YFMC00; else LS_YFPYCKWKQTS:=REC_TYPYCK.YFMC00; end if; end loop; if LS_YFPYCKWKQTS is not null then LS_YFPYCKWKQTS:='['||LS_YFPYCKWKQTS||']配药窗口未在工作中,请到对应药房窗口排队取药!'; end if; end if; if nvl(LS_NUM00,0)<=0 then select SF_ZZQH_GETQHXX_YKT(LS_BRID00,trim(YS_JRZJPH)) into p_errmsg from dual; if p_errmsg = '-1' then if LS_WQHXX0 is not null then p_errmsg:=LS_WQHXX0; elsif LS_YFPYCKWKQTS is not null then p_errmsg:=LS_YFPYCKWKQTS; else p_errmsg := '未找到未记账的处方和未发药的处方!'; end if; return; end if; else IF nvl(LS_ZFJE00,0)<= NVL(LS_ZYE000,0) then for REC_KF in CUR_WKFCF loop if (rec_kf.psjg00 not in (-1,-2)) then ----未扣费执行扣费----begin---- if (rec_kf.cfzt00 = '0') then select sq_sf_brjfb0_djh000.nextval into LS_DJH000 from dual; SP_SF_YFCFJZ(rec_kf.cflsh0, ls_djh000, 0, 0, 0, 0, pxxxx00, ptsxx00 ); if trim(pxxxx00) is not null then p_errmsg := '处方记账错误:' || ptsxx00; return; end if; SP_SF_BRFY00_REG000(0, rec_kf.ghh000, rec_kf.ghid00, '门诊预交金', rec_kf.brid00, rec_kf.BRXM00, rec_kf.fbbh00, ls_ygbh00, ls_ygxm00, ls_czyks0, ls_djh000, ls_hjje00, ls_zfje00, ls_jzje00, ls_gfje00, pxxxx00, ptsxx00 ); if trim (pxxxx00) is not null then p_errmsg := '处方记账错误：' || ptsxx00; return; end if; end if; ----未扣费执行扣费----end---- end if; end loop; ---已扣费生成队列--begin-- for REC_KF IN CUR_YKFCF LOOP IF (rec_kf.psjg00 not in (-1,-2)) then select COUNT (1) into ls_num00 from yf_cfdl00 where CFLSH0 = REC_KF.CFLSH0 and BRID00 = LS_BRID00; IF (ls_num00<=0) then sp_py_scphdl (rec_kf.cflsh0, 0, 0, 0, pxxxx00, ptsxx00 ); if TRIM (pxxxx00) is not null then p_errmsg := '生产处方队列错误：' || ptsxx00; return; end if; end if; select count(1) into ls_num00 from YF_CFDL00 where cflsh0= rec_kf.cflsh0 and brid00 = ls_brid00; --增加参数控制自动发药 by 2014.04.10 /* SP_YF_MZCFFY( P_YFBMBH in number, --输入：药房部门编号 P_CFLSH0 in char, --输入：处方流水号 P_CZYBH0 in number, --输入：操作员编码 P_FHRBH0 in number default null, --输入：复核人编码 P_SFXMBH in number, --收费项目编号(内码) P_SFXMMC in char, --收费项目名称 P_CFMXJE_SRWS in number, --输入：处方明细金额--舍入位数(小数位保留位数) P_CFZJE_SRWS in number, --输入：处方总金额--舍入位数(小数位保留位数) P_CFMXJE_CZFS in number, --输入：处方明细金额--取整方式0--四舍五入,1--向前取整 P_CFZJE_CZFS in number --输入：处方总金额--取整方式0--四舍五入,1--向前取整 */ if (MZPY_JZHSFZJFY='Y')and (ls_num00>0) then select sum(CYJE00) into ls_CYJE00 from yf_mzcf00 where cflsh0=rec_kf.cflsh0 and brid00=ls_brid00; SP_YF_MZCFFY(rec_kf.yfbmbh,rec_kf.cflsh0,LS_YGBH00,LS_YGBH00,0, '',ls_CYJE00,ls_CYJE00,ls_CYJE00,ls_CYJE00); --SP_YF_MZCFFY(rec_kf.yfbmbh,rec_kf.cflsh0,LS_YGBH00,LS_YGBH00,0,'',ls_CYJE00,'N',Pzxcgbz,Ptsxx00,Pxxxx00); if trim (pxxxx00) is not null then p_errmsg := '处方发药错误：' || pxxxx00; return; end if; end if; end if; end loop; ---已扣费生成队列--end-- end if; end if; --6：未皮试提示-- select COUNT (1) into ls_num00 from VW_SST_QHXX00 c where brid00 = LS_BRID00 and ((SST_DMCFSFYXQDFY='N'and CFDGS0 <> '0') or ( SST_DMCFSFYXQDFY='Y' and 1=1)) --and cfdgs0 <> '0' and psjg00 in (-2) and ((LS_QYCKBH in ('0') and 1=1) or (LS_QYCKBH not in ('0') and instrb(LS_QYCKBH,to_char(c.YFBMBH))>0)) and instrb(trim(YS_JRZJPH),to_char(c.YFBMBH))>0; if ls_num00 > 0 then ls_wpsts := '您需要先做皮试,皮试通过后再到自助机取号！'; P_ERRMSG:=ls_wpsts; return; end if; --7:皮试未通过提示-- select COUNT(1) into LS_NUM00 from VW_SST_QHXX00 C where BRID00 = LS_BRID00 --and cfdgs0 <> '0' and ((LS_QYCKBH in ('0') and 1=1) or (LS_QYCKBH not in ('0') and instrb(LS_QYCKBH,to_char(c.YFBMBH))>0)) and ((SST_DMCFSFYXQDFY='N'and CFDGS0 <> '0') or ( SST_DMCFSFYXQDFY='Y' and 1=1)) and psjg00 in (-1) and instrb(trim(YS_JRZJPH),to_char(c.YFBMBH))>0; if LS_NUM00 > 0 then ls_pswtgts := '您皮试未通过,请皮试通过后再到自助机取号！'; P_ERRMSG:=ls_pswtgts; return; end if; --判断别个药房是否有处方--请到【 ' + tmpclc2 + '】刷卡取药！ for rec_cf in cur_wpdcf loop if trim(ls_zjqyts) is not null then ls_zjqyts := ls_zjqyts || ' ' || rec_cf.bmmc00; else ls_zjqyts :=rec_cf.bmmc00; end if; end loop; if trim(ls_zjqyts) is not null then ls_zjqyts := '请携带就诊卡到【' || ls_zjqyts || '】刷卡取药！'; end if; FOR rec_drcf IN cur_fdrcf LOOP ls_fdrcfts :=ls_fdrcfts|| ' '|| rec_drcf.bmmc00|| ' '|| rec_drcf.cfsl00|| '张处方'; END LOOP; --BM_TYZD00 字典名称设置为 请到优先窗口刷卡取药！-- SELECT COUNT (*) sl INTO ls_num01 from bm_tyzd00 WHERE zdmc00 = '不进入排号队列药品目录' AND yxbz00 = '1' AND dj0000 > 0; SELECT COUNT (1) INTO ls_num02 from vw_sst_qhxx00 c WHERE brid00 = ls_brid00 and instrb(trim(YS_JRZJPH),to_char(c.YFBMBH))>0; IF (ls_num01 > 0) AND (ls_num02 > 0) THEN SELECT COUNT (1) INTO ls_num01 FROM vw_sst_qhxx00 c WHERE brid00 = ls_brid00 and ((SST_DMCFSFYXQDFY='N'and CFDGS0 <> '0') or ( SST_DMCFSFYXQDFY='Y' and 1=1)) --AND cfdgs0 <> '0' and ((LS_QYCKBH in ('0') and 1=1) or (LS_QYCKBH not in ('0') and instrb(LS_QYCKBH,to_char(c.YFBMBH))>0)) and instrb(trim(YS_JRZJPH),to_char(c.YFBMBH))>0 and EXISTS (SELECT 1 FROM yf_mzcfmx s WHERE s.cflsh0 = c.cflsh0 AND EXISTS (SELECT 1 FROM bm_tyzd00 -----------20190423 NOT WHERE zdmc00 = '不进入排号队列药品目录' AND yxbz00 = '1' AND dj0000 = s.ypnm00)); IF ls_num01 > 0 THEN --ls_yxckts :='有特殊管理药品,请到西药房窗口核对刷卡取药:处方数' || TO_CHAR (ls_num01)|| '张'; p_errmsg :='有特殊管理药品,请到西药房窗口核对刷卡取药:处方数' || TO_CHAR (ls_num01)|| '张'; END IF; END IF; --请到毒麻窗口刷卡取药！ SELECT COUNT (1) INTO ls_num02 FROM vw_sst_qhxx00 c WHERE brid00 = ls_brid00 AND c.cfdgs0 in ( '0','1'); --用来判断是否有毒麻处方; IF ls_num02 > 0 then --ls_dmckts := '请到毒麻窗口刷卡取药:处方数' || TO_CHAR (ls_num02)|| '张'; p_errmsg := '请到毒麻窗口刷卡取药:处方数' || TO_CHAR (ls_num02)|| '张'; END IF; --代煎处方提示 select count(*) into LS_NUM03 from VW_SST_QHXX00 c where 1=1 and BRID00 = LS_BRID00 and c.YPDLBH='2' and c.ZYDJS0>0 and CFZT00 in ('0','1'); if LS_NUM03>0 then LS_YPDJTS:='您有代煎处方，请到中药房窗口进行代煎预约！'; end if; if LS_WQHXX0 is not null then P_QYTSXX:=LS_WQHXX0; end if; if LS_ZJQYTS is not null then P_QYTSXX:=P_QYTSXX||' '||LS_ZJQYTS; end if; if LS_WPSTS is not null then P_QYTSXX:=P_QYTSXX||' '||LS_WPSTS; end if; if LS_YXCKTS is not null then P_QYTSXX:=P_QYTSXX||' '||LS_YXCKTS; end if; if LS_DMCKTS is not null then P_QYTSXX:=P_QYTSXX||' '||LS_DMCKTS; end if; --P_QYTSXX :=LS_WQHXX0||trim(LS_ZJQYTS||' '|| LS_WPSTS||' '|| LS_PSWTGTS||' '|| LS_YXCKTS||' '|| LS_DMCKTS); if LS_CFTSXX is not null then P_CFTSXX:=p_cftsxx||','||LS_CFTSXX; end if; IF LS_DMTSXX is not null then p_cftsxx:=P_CFTSXX||','||LS_DMTSXX; end if; if LS_FDRCFTS is not null then p_cftsxx := P_CFTSXX ||','||LS_FDRCFTS; end if; if LS_YFPYCKWKQTS is not null then P_QYTSXX:=P_QYTSXX||chr(13)||LS_YFPYCKWKQTS; end if; if LS_YPDJTS is not null then P_QYTSXX:=P_QYTSXX||chr(13)||LS_YPDJTS; end if; --整理数据-- SELECT sq_yf_cfdl00_phzxh0.nextval into ls_zxh000 FROM DUAL; update yf_cfdl00 a set phbz00 = '5', phzxh0 = ls_zxh000, PHRQ00 = TO_CHAR (SYSDATE, 'YYYYMMDD'), PHDYCS =nvl(phdycs,0)+1, QHDDY0=nvl(QHDDY0,0)+1 where phbz00 = '0' and brid00 = ls_brid00 and instrb(trim(YS_JRZJPH),to_char(a.YFBMBH))>0 and a.cflsh0 in (select cflsh0 from yf_mzcf00 where brid00 = LS_BRID00 and srrq00>= LS_CZRQ00 and cfzt00 IN ('0', '1', '2')); -- SP_TransLog(sysdate,'SP_SST_ZZCFQH_YKT',4200,null,ls_dyrq00||','||p_cardno); select count(*)into ls_num02 from vw_sst_zzcfqh a where brid00 = LS_BRID00 and zxh000 = ls_zxh000 and instrb(trim(YS_JRZJPH),to_char(a.yfbmbh))>0; if ls_num02<=0 then --增加对已取过号的提醒，20190429 xutz for REC_DRQHXX in CUR_GET_DRQHXX loop if trim(LS_YQHTSXX) is not null then LS_YQHTSXX:=LS_YQHTSXX||chr(13)||to_char(REC_DRQHXX.bmmc00)||' '||REC_DRQHXX.ckmc00||'号窗口'||REC_DRQHXX.fydlxh; else LS_YQHTSXX:=to_char(REC_DRQHXX.bmmc00)||' '||REC_DRQHXX.ckmc00||'号窗口 序号：'||REC_DRQHXX.fydlxh; end if; end loop; if LS_YQHTSXX is null then p_errmsg := '未找到该病人有效的取药单信息！'; else p_errmsg:='您今天已取过号:'||chr(13)||LS_YQHTSXX||chr(13)||LS_YFPYCKWKQTS; end if; return; else select ZYE000 into LS_ZYE000 from SF_BRZHXX B where B.BRID00=LS_BRID00 and JFLBID=1; --删除中间表 delete SST_YF_CFDL00 where BRID00=LS_BRID00; --循环执行 for REC in CUR_QYPZXX loop --SST_ZZJCFQHHBDY：自助机处方取药多张凭证是否打印 Y:合并 N:不合并 默认值:N if SST_ZZJCFQHHBDY='Y' then select count(1) into LS_NUM02 from SST_YF_CFDL00 where BRID00=LS_BRID00 and QYCK00=REC.QYCK00 and YFMC00=REC.YFMC00 ; --同一个取药窗口合并 else LS_NUM02:=0; end if; if LS_NUM02>0 then --修改数据 update SST_YF_CFDL00 set QYLSH0=QYLSH0||','||REC.QYLSH0,CFJE00=nvl(CFJE00,0)+REC.CFJE00,ZYDJS0=nvl(ZYDJS0,0)+REC.ZYDJS0 where BRID00=LS_BRID00 and QYCK00=REC.QYCK00 and YFMC00=REC.YFMC00; else --插入数据 insert into SST_YF_CFDL00 ( XTGZH0,--系统跟踪号 CZRQ00,--操作日期 CZSJ00,--操作时间 BRID00,--BRID00 XMING0,--姓名 XB0000,--性别 NL0000,--年龄 QYTM00,--取药条码 YFMC00,--药房名称 QYCK00,--取药窗口 QYLSH0,--取药流水号 DDRS00,--等待人数 PYDLBH,--配药队列编号 XQ0000,--星期 DYRQ00,--打印日期 DYSJ00,--打印时间 SFDYXX,--是否打印 SFTSXX,--是否提示信息 CFDYXX,--处方打印信息 CFXMS0,--处方项目数 ZYDJS0,--中药代煎数 YE0000,--余额 CFJE00,--处方金额 PXXH00,--排序号 BYCC00--备用 )values ( '0',--系统跟踪号 to_char(sysdate,'YYYYMMDD'),--操作日期 to_char(sysdate,'HH24:MI:SS'),--操作时间 REC.BRID00,--BRID00 REC.XMING0,--姓名 REC.XB0000,--性别 REC.NL0000,--年龄 REC.QYTM00,--取药条码 REC.YFMC00,--药房名称 REC.QYCK00,--取药窗口 REC.QYLSH0,--取药流水号 REC.DDRS00,--等待人数 REC.PYDLBH,--配药队列编号 REC.XQ0000,--星期 LS_DYRQ00,--打印日期 LS_DYRQ00,--打印时间 'Y',--是否打印 'Y',--是否提示信息 SF_SST_QYXX00(REC.PYCKBH, REC.QYLSH0, REC.BRID00, LS_ZXH000),--处方打印信息 0,--处方项目数 REC.ZYDJS0,--中药代煎数 LS_ZYE000,--余额 REC.CFJE00,--处方金额 0,--排序号 --''--备用 rec.zyts00 ); end if; end loop; --游标集输出数据 open r_cur4 for select QYTM00, YFMC00, decode(ZYDJS0,0,QYCK00,QYCK00||decode(LS_XSZYDJ,'Y','(请到8号窗口取代煎单)','')) as QYCK00, QYLSH0, XMING0, DDRS00, PYDLBH, XQ0000, NL0000, XB0000, DYRQ00, DYSJ00, SFDYXX, SFTSXX, BYCC00, CFDYXX,CFXMS0, trim(to_char(YE0000,'99990.00')) YE0000, trim(to_char(round(CFJE00,2),'99990.00')) CFJE00 from SST_YF_CFDL00 where BRID00=LS_BRID00 order by QYCK00; /* open r_cur4 for select qytm00, yfmc00, decode(ZYDJS0,0,qyck00,qyck00||decode(LS_XSZYDJ,'Y','(请到8号窗口取代煎单)','')) AS qyck00, --qyck00, qylsh0, xming0, ddrs00, pydlbh, xq0000, nl0000, xb0000, ls_dyrq00 AS dyrq00, ls_dyrq00 AS dysj00, 'Y' AS sfdyxx, 'Y' AS sftsxx, '' AS bycc00, SF_SST_QYXX00_YKT (pyckbh, qylsh0, brid00, ls_zxh000) cfdyxx, 0 cfxms0,Trim(to_char(ROUND(ye0000,2),'99990.00')) ye0000,Trim(to_char(ROUND(cfje00,2),'99990.00')) cfje00 from (select distinct brid00, pyckbh, qytm00 AS qytm00, yfmc00, ckmc00 AS qyck00, fydlxh AS qylsh0, max (brxm00) AS xming0, sum(ddrs00)ddrs00, max (pydlbh) pydlbh, max(week00) AS xq0000, max (nl0000) nl0000, max (xb0000) xb0000, ye0000,sum(cfje00)cfje00,nvl(sum(ZYDJS0),0)ZYDJS0 from vw_sst_zzcfqh a -- from VW_PY_ZZQHPRINT a where brid00 = ls_brid00 and zxh000 = ls_zxh000 and instrb(trim(YS_JRZJPH),to_char(a.yfbmbh))>0 group by brid00,ye0000,pyckbh,qytm00,yfmc00,ckmc00,fydlxh); */ end if; exception when no_data_found then p_errmsg := '发生未知错误' || SQLERRM; when others then p_errmsg := '发生未知错误' || SQLERRM; end; /