CREATE OR REPLACE PROCEDURE SP_SST_ZZCFQH_YKT ( P_ZDBH00 in varchar2, --终端编号 P_YYJGDM in varchar2, --医院机构代码 P_CARDNO in varchar2, --卡号 P_CARDTYPE in varchar2, --卡类型 P_PXZXH0 in varchar2, --排序子序号 P_YBJJE0 out varchar2, --应补交金额 P_QYTSXX out varchar2, --取药提示信息 P_CFTSXX out varchar2, --处方数提示信息 P_ERRMSG out varchar2, --错误信息，NULL表示正确 R_CUR4 out pkg_fee_qry.my_cursor --结果集游标 /* 结果集说明： QYTM00:取药条码, YFMC00:药房名称, QYCK00:取药窗口, QYLSH0:取药流水号, XMING0:姓名, DDRS00:等待人数, PYDLBH:配药队列编号, XQ0000:星期,NL0000:年龄, XB0000:性别,DYSJ00:打印时间, BYCC00:备用出参数,CFDYXX:处方打印信息 Cardno：就诊卡号 CFJE00：处方金额，YNZHYE：院内账户余额 */ ) AS -- MODIFICATION HISTORY -- Person Date Comments -- qks 2013.03.04 "未找到未记账的处方和未发药的处方" 改为 如果已经取过号，那么提示所有的取号信息 --先对所有未记账处方一起扣费，之后再一起生成队列以及发药。 -- qks 2014.01.02 增加入参P_phzxh0 -- qks 2014.03.19 刷卡取药 改为 刷卡后等待取药 -- qks 2014.04.01 增加字段 YF_CFDL00.ZDBH00 -- zhangyc 2015.02.04 增加中药待煎金额 -- zhangyc 2016.12.27 按新接口文档改造 -- qks 2018.01.10 优化查询速度; 调用sf_zzqh_getqhxx改为sf_zzqh_getqhxx_ykt,并对过程sf_zzqh_getqhxx_ykt进行优化。for ZZJJK-20180111-002 -- liwm 2018.03.09 增加药事服务费处理 for ZZJJK-20180305-001 -- liwm 2018.08.31 需求：如果病人当天在中药房有药，在取号时添加提示：“您在中药房有药，无需取号，请直接到中药窗口取药” ZZJJK-20180828-001 -- liwm 2018.09.07 增加中医辨证论治费 的处理，同ZZJJK-20180305-001非常相似 MZPY9-20180307-001 -- liwm 2018.11.19 自助机取药扣费时，只对其中一条药事服务费进行了扣费操作,增加游标去处理 ZZJJK-20181116-001 -- liwm 2018.12.25 增加审方过滤 GGJK-20181225-002 -- liwm 2018.12.25 皮试流程参数开启先发皮试药品 GGJK-20181225-001 -- liwm 2019.01.16 处方删除自助机不让结算问题 ZZJJK-20190116-001 -- liwm 2019.05.21 中药代煎费重复扣 ZZJJK-20190521-001 -- qks 2019.08.01 医保病人未结算做提示先进行“医保结算”操作; for ZZJJK-20190723-001 -- liwm 2019.08.25 沙县自助机取药速度很慢，brid00索引生效解决 GGJK-20190825-001 -- qks 2019.08.26 沙县医院“离休”病人（BM_BRXXB0.FBBH00=4）需要先到收费处结算后，才允许再取号; for ZZJJK-20190827-001 -- qks 2019.08.27 修改调用药事服务费扣费过程SP_YJ_YWSF00_NEW、门诊处方发药过程sp_yf_mzcffy; for ZZJJK-20190827-002 -- qks 2019.10.28 使用参数PY_DMCFSFBJRPHDL控制毒麻处方是否进入排号队列:N进入,Y否; for ZZJJK-20191028-001 -- qks 2020.04.07 再次修改：使用参数PY_DMCFSFBJRPHDL控制毒麻处方是否进入排号队列:N进入,Y否; for ZZJJK-20191028-001 -- linshu 2020.07.06 增加了SST_ZZCFQHXZCXJT参数来设置查询几天内的挂号号，同时把原来用IN的语句改为EXISTS来提高查询速度 for ZZJJK-20200706-001 -- luowf 2020.07.15 修改事前审方提示语，未审核的提示显示药房 for SQSF9-20200710-001 -- qks 2021.05.07 修改yf_cfdl00增加 and zt0000 = '0'条件; for ZZJJK9-20210507-001 -- qiulf 2021.10.14 对参数SST_CFYJJS进行扩展: Y全部,N否,M:指定部分执行科室(具体格式如 M,部门编号1,部门编号2 )对未记账医技单不允许取号 by ZZJJK9-20211008-001 -- qks 2022.04.23 启用参数MZPY_JZHSFZJFY功能,但是默认值改为Y; for ZZJJK9-20220424-001 -- chenHeyi 2023.04.27 取号分院区，费用查询及相关游标查询条件加YYID00 ZZJJK9-20230413-001 -- zhangyc 2023.12.13 如果substrb(SF_SF_YBSXZD_EXISTS(-1,'Settle_NotCFDL_BMBH00',rec_kf.GHKSBM),1,10)='1',不生成队列(挂号科室不生成队列维护在his内部字典中) by ZZJJK9-20231212-001 -- zhangyc 2024.01.03 代码优化，参考SP_SST_JYZYD_PRINT_YKT先修改账户停用金额，到最后在次获取医保账户余额判断 by -- linshu 2024.04.24 游标cur_wpdcf增加判断处方状态(and cfzt00 not in ('1','2'))(注：翁浩铭现场归档); for ZZJJK9-20240423-001 -- zhangyc 2024.04.25 修正系统参数SST_ZZCFQH_CFYJJS变量赋值错误 -- linshu 2024.05.15 修正了脚本过程SP_SST_ZZCFQH_YKT（药房排队取号 ）中中医辩证费12块，没有进行提交扣费; for ZZJJK9-20240515-001 -- zhangyc 2024.05.30 业务过程sp_sf_jscfjg、sp_sf_yfcf00、SP_YJ_YWSF00_NEW报错后rollback以及修改停用前获取账户停用金额LS_ORDBRZHTYJE后面直接用改值更新; -- zhangyc 2024.07.23 优化调用过程：sp_py_scphdl、sp_yf_mzcffy报错，优先取pzxcgbz在取ptsxx00 -- linzetao 2024.11.18 执行医技单增加SL0000>0判断条件，修正退费申请被自动确认问题 --zhangyc 2024.12.09 YF_MZCFMX.SFMFBZ=2:政策免费,YJ_YW0000.SFMFBZ=2:政策免费,SF_FYMX00.SFMFBZ=2:政策免费 合计金额都为0 ls_num00 NUMBER (4); ls_num01 NUMBER (4); ls_num02 NUMBER (4); LS_ZYE000 NUMBER (12, 4); --病人总余额 ls_zfje00 NUMBER (12, 4); --自费金额 pzxcgbz NUMBER (12); ptsxx00 varchar2 (400); pxxxx00 varchar2 (400); ls_djh000 sf_brfy00.djh000%TYPE; --收费单据号 ls_kfpc00 yf_mzcf00.kfpc00%TYPE; --扣费批次号 LS_ICKH00 ic_yhxx00.ickh00%TYPE; --IC卡号 ls_brid00 bm_brxxb0.brid00%TYPE; --病人ID ls_zt0000 ic_yhxx00.zt0000%TYPE; --IC卡状态 ls_ygbh00 bm_ygbm00.ygbh00%TYPE; --结算操作员 ls_ygxm00 bm_ygbm00.zwxm00%TYPE; --结算操作员 ls_czyks0 sf_brfy00.czyks0%TYPE; --操作员科室 ls_hjje00 yf_mzcfmx.HJJE00%TYPE; --处方合计金额 mzhs_cyphyf xt_xtcs00.value0%TYPE; --进入诊结排号的药房 默认为0 若有药房 格式为 药房1,药房2.....n zzqh_dmcfsfjrpdjhxt xt_xtcs00.value0%TYPE; --如果病人有毒麻处方的话 提示到优先窗口 zzqh_drjrqhsjfgd xt_xtcs00.value0%TYPE; -- --当天处方进入排号队列的时间处理，00:00:00 取号时间在当天8点之前那么输入时间在前一天的处方也可进入排号队列， --<>00:00:00表示输入时间在当天并且只有处方的输入时间在参数设置时间点后的才进入排号队列 默认00:00:00 zzqh_jrpdjhcfblts xt_xtcs00.value0%TYPE; -- --参与排队的处方按处方输入日期控制，参数值天数内的处方参与排号，为0的话是当天的处方参与排号， --至于时间点的分割沿用参数ZZQH_DRJRQHSJFGD控制的做法 默认为0 mzpy_jzhsfzjfy xt_xtcs00.value0%TYPE; --配药自助取号记帐后是否直接发药 Y:是；N:不发药; 默认:Y vsftslc varchar2 (2); --是否特殊流程 '0'正常'1'特殊，如果定义了字典名称为"不进入排号队列药品目录"，那么走特殊流程 vsftsyxck varchar2 (2); --是否提示优先窗口 '0'不提示 '1'提示 vsftsdmck varchar2 (2); --毒麻处方是否提示到毒麻窗口 ls_czrq00 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_zxh000 NUMBER (12); LS_CarNum number(5); --卡号位数 LS_ZFFSBH SF_BRJFB0.ZFFS00%type; LS_ZFFSMC BM_BRZFFS.ZFFSMC%type; LS_JFBZ00 varchar2(1); LS_ZPH000 SF_BRJFB0.ZPH000%type; LS_ZPKHYH SF_BRJFB0.ZPKHYH%type; LS_ZPDWMC SF_BRJFB0.ZPDWMC%type; LS_MZZHID bm_brxxb0.MZZHID%type; LS_YBKH00 BM_BRXXB0.YBKH00%type; LS_YBID00 BM_BRXXB0.YBID00%type; LS_BRXM00 bm_brxxb0.BRXM00%type; LS_ID0000 SF_YKTKFB.ID0000%type; LS_MKDM00 varchar2(10); LS_YYID00 XT_YYXX00.YYID00%type; LS_ZYDJJE number(12,2);--中医待煎金额 LS_YSFWFJE number(12,2);--药事服务费总金额 LS_YJDJH0 YJ_YW0000.YJDJH0%type;--药事服务费的医技单据号 LS_YJKSBH YJ_YW0000.YJKSBH%type; LS_KDYS00 YJ_YW0000.KDYS00%type; LS_ZYBZLZF number(12 ,2);--中医辨证论治费 LS_ZYBZYJDJH0 YJ_YW0000.YJDJH0%type;--中医辨证论治费的医技单据号 LS_ZYBZZLXMID varchar2(500); --中医辨证论治费的zlxmid LS_ZYBZYJKSBH YJ_YW0000.YJKSBH%type; LS_ZYBZKDYS00 YJ_YW0000.KDYS00%type; LS_CZRQ01 varchar2(8); --挂号日期,比ls_czrq00早2天 LS_SST_ZYCFSFTXTS XT_XTCS00.VALUE0%type; LS_SST_ZYFBH XT_XTCS00.VALUE0%type; Vpslccs XT_XTCS00.NAME00%type; --皮试流程参数 LS_FBBH00 BM_BRXXB0.FBBH00%type; SST_YBWJZCFXZQH varchar2(100); --医保未记账处方不能取号 Y:不能取号 N:可以取号 默认值:N LS_DMCFDL number(1); --值为1表示进入排号队列(参数PY_DMCFSFBJRPHDL=N) LS_XZCXTS number(2); --限制查询几天内的数据 LS_FCFJE0 number(12,2); --未扣费非药品费用 LS_CFYJJS XT_XTCS00.VALUE0%type; --是否把医技，费用一起结算（医保病人都要 自费病人参数LS_ZFBRSFJS=Y 都要） LS_YJJZKS XT_XTCS00.VALUE0%type; --非医保病人,医技单一起记账科室(多个使用逗号分隔) LS_Prescription_CFDL varchar2(10); --是否生成处方队列 1:生成 0:不生成 LS_Prescription_CFFY varchar2(10); --是否处方发药 1:发药 0:不发药 LS_BCYXQFTYJE number(12,2); --本次欠费允许停用金额 LS_ORDBRZHTYJE number(12,2);--旧的账户停用金额 SST_ZZCFQH_CFYJJS XT_XTCS00.VALUE0%type;--自助机处方取号是否判断医技项目是否扣费 Y:判断 N:不判断 默认值:Y ls_temstr varchar2(10); --VSQL2毒麻处方，VSQL3未皮试处方,sPSJG00皮试结果 cursor cur_wkfcf IS --未扣费处方游标 select CFLSH0, BRID00, GHH000, THBZ00, YFBMBH, YPDLBH, DLXH00, CFZT00,PSJG00, PSZT00 from VW_SST_QHXX00 c where brid00 = LS_BRID00 --and cfdgs0 <> '0' and (cfdgs0 <> '0' and LS_DMCFDL=0 or LS_DMCFDL=1) and cfzt00 in ('0') and instrb(trim(MZHS_CYPHYF),to_char(c.yfbmbh))>0 and c.srrq00 >=LS_CZRQ00 and (Vpslccs = 'N' or ( Vpslccs = 'Y' and not exists ( select 1 from YS_PSCFDZ where ZCFLSH = c.CFLSH0 and GHID00 = c.GHID00 and nvl(FLAG00, '0') in ('0','2') ) )) and (c.YYID00=LS_YYID00 or LS_YYID00='0'); cursor cur_ykfcf IS --已扣费处方游标 select cflsh0, brid00, ghh000, thbz00, yfbmbh, ypdlbh, dlxh00, cfzt00, psjg00, pszt00,c.GHKSBM from vw_sst_qhxx00 c where brid00 = ls_brid00 --and cfdgs0 <> '0' and (cfdgs0 <> '0' and LS_DMCFDL=0 or LS_DMCFDL=1) and cfzt00 in ('1') and instrb(trim(MZHS_CYPHYF),to_char(c.yfbmbh))>0 and c.srrq00 >=LS_CZRQ00 and (c.YYID00=LS_YYID00 or LS_YYID00='0') ; cursor cur_wpdcf IS --毒麻和直接刷卡取药处方数 select distinct bmmc00 from ( select a.yfbmbh, b.bmmc00,decode (a.cfdgs0, '0', '0', '1') cfdgs0,count (1) cfsl00, sum (decode (a.kfpc00,null, decode (a.sfpdqh, 1, 1, 0),0)) tssl00 from yf_mzcf00 a, bm_bmbm00 b where a.srrq00>=LS_CZRQ00 and a.yfbmbh = b.bmbh00 and a.brid00 = ls_brid00 and a.sfksly = 'N' and a.cfzje0 > 0 and cfzt00 not in ('1','2') and (b.YYID00=LS_YYID00 or LS_YYID00='0') --2018.01.10优化查询速度 --and a.GHH000 in (select GHH000 from SF_BRXXB0 where BRID00=LS_BRID00 and GHRQ00>=LS_CZRQ01) and exists (select 1 from SF_BRXXB0 where BRID00=LS_BRID00 and GHH000=a.GHH000 and GHRQ00>=LS_CZRQ01) 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 (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 (d.YYID00=LS_YYID00 or LS_YYID00='0') --2018.01.10优化查询速度 --and c.GHH000 in (select GHH000 from SF_BRXXB0 where BRID00=LS_BRID00 and GHRQ00>=LS_CZRQ01) and exists (select 1 from SF_BRXXB0 where BRID00=LS_BRID00 and GHH000=c.GHH000 and GHRQ00>=LS_CZRQ01) and instrb(trim(MZHS_CYPHYF),to_char(c.yfbmbh))>0 and (((c.cfzt00 = '0' OR c.cfzt00 = '1')and c.srrq00||'' <=LS_CZRQ00 and c.srrq00 || c.srsj00 <=TO_CHAR (SYSDATE - 1 / 3 - 1,'YYYYMMDDHH24:MI:SS') and c.srrq00 >=to_char (SYSDATE + 1 - 2, 'YYYYMMDD') and c.bccfh0 is null ) and not exists (select 1 from yf_mzcf00 where brid00 = c.brid00 and ghh000=c.GHH000 and bccfh0 = c.cflsh0) ) and c.sfksly = 'N' GROUP BY c.yfbmbh, d.bmmc00; type cursor_type is ref cursor; cur_YSFWF cursor_type; --药事服务费游标 VCounter number(5); --事前审方计数器 Vysxm000 varchar2(100); --审方未过的医生姓名 Vyfmc000 varchar2(100); --审方未过的药房 Vsftyzd BM_TYZD00.MC0000%type; --审方是否启用 begin LS_Prescription_CFDL :=null; --是否生成处方队列 1:生成 0:不生成 LS_Prescription_CFFY :=null; --是否处方发药 1:发药 0:不发药 begin select to_number(VALUE0) into LS_XZCXTS from XT_XTCS00 where NAME00='SST_ZZCFQHXZCXJT'; exception when others then LS_XZCXTS := 3; end; LS_CZRQ00 := to_char (sysdate - 1 / 3 - 1, 'YYYYMMDD'); LS_CZRQ01 := to_char (sysdate - 1 / 3 - LS_XZCXTS, 'YYYYMMDD'); LS_DYRQ00 := to_char (sysdate, 'YYYY-MM-DD') || ' '|| to_char (sysdate, 'hh24:mi:ss'); VSFTSYXCK := '0'; VSFTSDMCK := '0'; LS_NUM00 := 0; LS_NUM01 := 0; LS_NUM02 := 0; LS_ZYE000 := 0; LS_ZFJE00 := 0; LS_CFTSXX := ''; LS_DMTSXX := ''; LS_FDRCFTS := ''; LS_YXCKTS := ''; LS_DMCKTS := ''; LS_WPSTS := ''; LS_PSWTGTS := ''; LS_ZJQYTS := ''; LS_ZYDJJE :=0; LS_YSFWFJE := 0; LS_ZYBZLZF := 0; P_YBJJE0:=''; LS_BCYXQFTYJE:=8888; --本次欠费允许停用金额 --获取终端对应的操作员 begin select YGBH00, ZWXM00 into LS_YGBH00, LS_YGXM00 from vw_sst_ygbmdyxx where zzjbh0 = p_zdbh00; exception when no_data_found then p_errmsg :='终端编号:' || p_zdbh00 || '在本院SST_YGBHDY表中未对应收费员!'; return; end; --卡号转化 begin LS_ICKH00:=substrb(SF_SST_GETICKH00_YKT (P_CARDNO),1,200); exception when others then p_errmsg :='卡号转化异常:CARDNO='||P_CARDNO; return; end; --获取基本信息 begin select a.MZZHID,a.BRXM00,a.BRID00,a.YBKH00, a.YBID00,b.ZT0000,a.FBBH00 into LS_MZZHID,LS_BRXM00,LS_BRID00,LS_YBKH00, LS_YBID00,LS_ZT0000,LS_FBBH00 from BM_BRXXB0 a, IC_YHXX00 b where a.brid00=b.brid00 and ICKH00 =LS_ICKH00 and ZT0000='1' and rownum=1; exception when no_data_found then p_errmsg := '卡号:' || LS_ICKH00 || '在本院所对应的病人信息未找到!'; return; end; begin select BMBH00 into LS_CZYKS0 from BM_YGBM00 where YGBH00 = LS_YGBH00; exception when others then LS_CZYKS0 := 0; end; --医院id begin LS_YYID00:=SF_SF_GETYYID00(LS_CZYKS0); exception when others then LS_YYID00:='0'; end; --获取系统参数 select nvl(max(trim(VALUE0)), 'N') into Vpslccs from XT_XTCS00 where NAME00= 'YS_SFKQPSBH'; begin select to_char(wm_concat(BH0000)) into LS_ZYBZZLXMID from BM_TYZD00 where ZDMC00 = '中医辩证论治费' and YXBZ00 ='1'; exception when others then LS_ZYBZZLXMID := '-99999999'; end; --自助机非医保病人结算是否把医技单一起扣费 Y:是 N:否 M:指定部分执行科室(具体格式如 M,部门编号1,部门编号2 ) 默认值:N begin select upper(trim(VALUE0)) into LS_CFYJJS from XT_XTCS00 where NAME00 = 'SST_CFYJJS'; exception when others then LS_CFYJJS:='N'; end; if substr(LS_CFYJJS,1,1)='M' then LS_YJJZKS := nvl(substr(LS_CFYJJS,3,length(LS_CFYJJS)-2),'0'); LS_CFYJJS := substr(LS_CFYJJS,1,1); end if; --自助机处方取号是否判断医技项目是否扣费 Y:判断 N:不判断 默认值:Y begin select upper(trim(VALUE0)) into SST_ZZCFQH_CFYJJS from XT_XTCS00 where NAME00 = 'SST_ZZCFQH_CFYJJS'; exception when others then SST_ZZCFQH_CFYJJS:='Y'; end; if nvl(SST_ZZCFQH_CFYJJS,'Y')<>'N' then --自助机处方取号是否判断医技项目是否扣费 Y:判断 N:不判断 默认值:Y --未扣费非药品费用 select nvl(sum(HJJE00), 0) into LS_FCFJE0 from VW_SST_WJZCF c where BRID00=LS_BRID00 and ZT0000=0 and exists ( select 1 from SF_BRXXB0 where BRID00=LS_BRID00 and GHH000=c.GHH000 and GHRQ00>=LS_CZRQ01) and ((LS_CFYJJS='N' and CFYJBZ='1') or(LS_CFYJJS='M' and CFYJBZ='1' and instrb(','||LS_YJJZKS||',',','||to_char(ZXKS00)||',')<=0)) and ( Vpslccs = 'N' or (Vpslccs = 'Y' and not exists (select 1 from YS_PSCFDZ a where a.ZCFLSH = CFLSH1 and GHID00 = A.GHID00 and nvl(FLAG00, '0') in ('0','2'))) ); if LS_FCFJE0 > 0 then p_errmsg := '你有治疗项目未扣费，请先做结算！'; return; end if; end if; --事前审方，处方是否审方不过的 select nvl(max(MC0000), '0') into Vsftyzd from BM_TYZD00 where ZDMC00 = '事前审方-事前审方系统' and BZ0000 = '是否启用审方系统'; if Vsftyzd = '1' then select count(*) into VCounter from VW_SQSF_DSCFDL where BRID00 = LS_BRID00 and SQSFZT = '3'; if VCounter > 0 then select to_char(wm_concat(YSXM00)) into Vysxm000 from (select distinct YSXM00 from VW_SQSF_DSCFDL where BRID00 = LS_BRID00 and SQSFZT = '3'); P_ERRMSG := '您就诊于'||Vysxm000||'医师的处方未能通过审核，请您携带就诊卡到诊室咨询医师。'; return; end if; select count(*) into VCounter from VW_SQSF_DSCFDL where BRID00 = LS_BRID00 and SQSFZT in ('1', '4'); if VCounter > 0 then select to_char(wm_concat(YF0000)) into Vyfmc000 from (select distinct YF0000 from VW_SQSF_DSCFDL where BRID00 = LS_BRID00 and SQSFZT in ('1', '4')); P_ERRMSG := '您的'||Vyfmc000||'处方还未审核，请稍后再次尝试。'; return; end if; end if; --进入诊结排号的药房 默认为0 若有药房 格式为 药房1,药房2.....n begin select value0 into MZHS_CYPHYF from XT_XTCS00 where name00 = 'MZHS_CYPHYF'; exception when others then mzhs_cyphyf := '0'; end; --如果病人有毒麻处方的话 提示到优先窗口 --2019.10.28 使用参数PY_DMCFSFBJRPHDL控制毒麻处方是否进入排号队列:N进入,Y否; select count(1) into LS_DMCFDL from xt_xtcs00 where name00 = 'PY_DMCFSFBJRPHDL' and value0='N'; begin select value0 into ZZQH_DRJRQHSJFGD from xt_xtcs00 where name00 = 'ZZQH_DRJRQHSJFGD'; exception when others then ZZQH_DRJRQHSJFGD := '00:00:00'; end; begin select value0 into ZZQH_JRPDJHCFBLTS from xt_xtcs00 where name00 = 'ZZQH_JRPDJHCFBLTS'; exception when others then zzqh_jrpdjhcfblts := '1'; end; --配药自助取号记帐后是否直接发药 Y:是；N:不发药; 默认:Y begin select trim(nvl(max(VALUE0),'Y')) into mzpy_jzhsfzjfy from xt_xtcs00 where name00 = 'MZPY_JZHSFZJFY'; exception when others then mzpy_jzhsfzjfy := 'Y'; end; --SST_YBWJZCFXZQH：医保未记账处方不能取号 Y:不能取号 N:可以取号 默认值:N --指定费别(多个使用逗号隔开)病人未记账处方不能直接取号,-1:不限制,默认值为-1 begin select trim(nvl(max(VALUE0),'N')) into SST_YBWJZCFXZQH from XT_XTCS00 where name00 = 'SST_YBWJZCFXZQH'; exception when others then SST_YBWJZCFXZQH := 'N'; end; LS_Prescription_CFDL :='1'; --是否生成处方队列 1:生成 0:不生成 --是否处方发药 1:发药 0:不发药 if mzpy_jzhsfzjfy='N' then --配药自助取号记帐后是否直接发药 Y:是；N:不发药; 默认:Y LS_Prescription_CFFY :='0'; else LS_Prescription_CFFY :='1'; end if; 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; ---取病人总余额--- if LS_FBBH00=3 then LS_ZYE000:=SF_SST_GetBRZHYE_YKT(0,LS_BRID00,1,1);--1:总余额 else LS_ZYE000:=SF_SST_GetBRZHYE_YKT(0,LS_BRID00,1,0);--0:可用余额 end if; --取自费金额-- select --sum (b.zfje00), --nvl(sum(b.HJJE00),0) sum (decode(nvl(b.SFMFBZ,'0'),'2',0,b.zfje00)) as zfje00, sum (decode(nvl(b.SFMFBZ,'0'),'2',0,b.HJJE00)) as HJJE00 into LS_ZFJE00, ls_HJJE00 from yf_mzcfmx b, yf_mzcf00 c where c.brid00 = LS_BRID00 and instrb(trim(MZHS_CYPHYF),to_char(c.yfbmbh))>0 and b.cflsh0 = c.cflsh0 and c.cfzt00 = '0' and c.bccfh0 is null and c.srrq00 >=LS_CZRQ00 and ((nvl(c.cfdgs0, '5') <>'0' and LS_DMCFDL=0) or LS_DMCFDL=1) and c.sfksly = 'N' and sf_mz_pscfys (c.ghh000, c.cflsh0) not in (-1, -2) and c.pszt00 <> '1' and c.pszt00 <> '2' and (SF_SF_GETYYID00(c.GHKS00)=LS_YYID00 or LS_YYID00='0'); --医保病人未扣费不做取号 ZZJJK-20190723-001 --指定费别(多个使用逗号隔开)病人未记账处方不能直接取号,-1:不限制,默认值为-1 ZZJJK-20190827-001 if LS_FBBH00='3' and instrb(','||SST_YBWJZCFXZQH||',',','||to_char(LS_FBBH00)||',')>0 and LS_ZFJE00>0 then p_errmsg :='医保患者费用未结算,请您先进行“医保结算”操作！'; return; elsif LS_FBBH00<>'3' and instrb(','||SST_YBWJZCFXZQH||',',','||to_char(LS_FBBH00)||',')>0 and (LS_ZFJE00>0 or ls_HJJE00>0) then p_errmsg :='请您先到收费处进行结算！'; return; end if; --中药待煎金额--begin-- begin select --sum(b.ZFJE00) sum(decode(nvl(a.SFMFBZ,'0'),'2',0,b.ZFJE00)) as ZFJE00 into LS_ZYDJJE from YJ_YW0000 a,YJ_YWJJ00 b where a.YJDJH0=b.YJDJH0 and a.BRID00=LS_BRID00 and a.MZZYBZ='0' and a.FJDJBZ = '7' and a.SFDJH0+0=0 --2018.01.10优化查询速度 --and a.ZYHGHH in (select GHH000 from SF_BRXXB0 where BRID00=LS_BRID00 and GHRQ00>=LS_CZRQ01) and exists (select 1 from SF_BRXXB0 where BRID00=LS_BRID00 and GHH000=a.ZYHGHH and GHRQ00>=LS_CZRQ01 ) and a.XMZT00 not in ('5','6') and (SF_SF_GETYYID00(a.KDKSBH)=LS_YYID00 or LS_YYID00='0') and a.YZCFID in ( select C.CFLSH0 from yf_mzcf00 c where c.brid00 = LS_BRID00 and c.cfzt00 = '0' and c.bccfh0 is null and c.srrq00 >=LS_CZRQ00 and c.sfksly = 'N' and c.pszt00 <> '1' and c.pszt00 <> '2' and c.ZYDJS0>0 ); exception when others then LS_ZYDJJE:=0; end; LS_ZFJE00:=nvl(LS_ZFJE00,0)+nvl(LS_ZYDJJE,0); --中药待煎金额--end-- --药事服务费费用计算 begin select --sum(b.ZFJE00) sum(decode(nvl(a.SFMFBZ,'0'),'2',0,b.ZFJE00)) as ZFJE00 into LS_YSFWFJE from YJ_YW0000 a,YJ_YWJJ00 b where a.YJDJH0=b.YJDJH0 and a.BRID00=LS_BRID00 and a.MZZYBZ='0' and a.FJDJBZ = '8' and a.SFDJH0+0=0 --优化查询速度 --and a.ZYHGHH in (select GHH000 from SF_BRXXB0 where BRID00=LS_BRID00 and GHRQ00>=LS_CZRQ01) and exists ( select 1 from SF_BRXXB0 where BRID00=LS_BRID00 and GHH000=a.ZYHGHH and GHRQ00>=LS_CZRQ01 ) and a.XMZT00 not in ('5','6') and (SF_SF_GETYYID00(a.KDKSBH)=LS_YYID00 or LS_YYID00='0'); exception when others then LS_YSFWFJE:=0; lS_YJDJH0 := -1; end; --药事服务费end --把需要付的金额相加 LS_ZFJE00:=nvl(LS_ZFJE00,0)+nvl(LS_YSFWFJE, 0); --中医辨证论治费 begin LS_ZYBZYJDJH0 := -1; if LS_ZYBZZLXMID <> '-99999999' then select --b.ZFJE00, decode(nvl(a.SFMFBZ,'0'),'2',0,b.ZFJE00) as ZFJE00, a.YJDJH0, a.YJKSBH, a.KDYS00 into LS_ZYBZLZF,LS_ZYBZYJDJH0,LS_ZYBZYJKSBH,LS_ZYBZKDYS00 from YJ_YW0000 a,YJ_YWJJ00 b where a.YJDJH0=b.YJDJH0 and a.BRID00=LS_BRID00 and a.MZZYBZ='0' and a.ZLXMID in (select to_number(BH0000) from BM_TYZD00 where ZDMC00 = '中医辩证论治费' and YXBZ00 ='1' and trim(BH0000) is not null ) and a.SFDJH0+0=0 --优化查询速度 --and a.ZYHGHH in (select GHH000 from SF_BRXXB0 where BRID00=LS_BRID00 and GHRQ00>=to_char(SYSDATE - 15, 'YYYYMMDD') ) and exists (select 1 from SF_BRXXB0 where BRID00=LS_BRID00 and GHH000=a.ZYHGHH and GHRQ00>=to_char(sysdate - 15, 'YYYYMMDD') ) and a.XMZT00 not in ('5','6') and (SF_SF_GETYYID00(a.KDKSBH)=LS_YYID00 or LS_YYID00='0'); end if; exception when others then LS_ZYBZLZF:=0; LS_ZYBZYJDJH0 := -1; end; --中医辨证论治费end LS_ZFJE00:=nvl(LS_ZFJE00,0)+nvl(LS_ZYBZLZF, 0); select count (1)into ls_num00 from vw_sst_qhxx00 c where brid00 = ls_brid00 and (c.YYID00=LS_YYID00 or LS_YYID00='0') and exists ( select 1 from SF_BRXXB0 where BRID00=LS_BRID00 and GHH000=c.GHH000 and GHRQ00>=LS_CZRQ01) and ((cfdgs0 <> '0' and LS_DMCFDL=0) or LS_DMCFDL=1) and cfzt00 in ('0', '1') and instrb(trim(MZHS_CYPHYF),to_char(c.yfbmbh))>0 and c.srrq00 >=LS_CZRQ00; --1:执行未扣费--begin-- IF NVL (ls_num00, 0) <= 0 then select nvl(max(VALUE0), 'N') into LS_SST_ZYCFSFTXTS from XT_XTCS00 where NAME00 = 'SST_ZYCFSFTXTS'; LS_SST_ZYFBH := replace(LS_SST_ZYCFSFTXTS, 'Y,', ''); if instrb(','||LS_SST_ZYCFSFTXTS,'Y') > 0 then --查找病人处方是不是只有中药房的处方 select count(*) into ls_num01 --中药处方数 from vw_sst_qhxx00 c where brid00 = ls_brid00 and ((cfdgs0 <> '0' and LS_DMCFDL=0) or LS_DMCFDL=1) and cfzt00 in ('0','1') and c.yfbmbh = LS_SST_ZYFBH and c.srrq00 >=LS_CZRQ00 and (c.YYID00=LS_YYID00 or LS_YYID00='0'); if ls_num01 > 0 then --只有中药处方的情况 p_errmsg := '您在中药房的药品，无需取号，请直接到中药窗口取药!'; return; end if; end if; --p_errmsg := '未找到未记账的处方和未发药的处方!'; --如果已经取过号，那么提示所有的取号信息 select sf_zzqh_getqhxx_ykt(ls_brid00,MZHS_CYPHYF) into p_errmsg from dual; if p_errmsg = '-1' then p_errmsg := '未找到未记账的处方和未发药的处方!'; return; end if; else --IF NVL (ls_zfje00, 0) <= NVL (ls_zye000, 0)then LS_ORDBRZHTYJE:=SF_SST_GetBRZHYE_YKT(0,LS_BRID00,1,3);--旧的账户停用金额 --修改停用金额 update SF_BRZHXX set TYJE00=nvl(TYJE00,0)-nvl(LS_BCYXQFTYJE,0) where BRID00=LS_BRID00 and JFLBID=1; select sq_yf_mzcf00_kfpc00.nextval into ls_kfpc00 from dual; FOR rec_kf in cur_wkfcf LOOP IF (rec_kf.psjg00 not in (-1, -2))then IF (rec_kf.cfzt00 = '0')then --未扣费执行扣费--- sp_sf_jscfjg (rec_kf.cflsh0, ls_ygbh00, ls_czyks0, 'N', pzxcgbz, ptsxx00, pxxxx00 ); IF Pzxcgbz=0 then p_errmsg := '修正处方明细和主表金额错误：' || ptsxx00; rollback; return; end IF; select sq_sf_brjfb0_djh000.nextval into ls_djh000 from dual; sp_sf_yfcf00 (rec_kf.cflsh0, ls_djh000, 0, 0, 0, 'N', ls_ygbh00, ls_czyks0, ptsxx00, pxxxx00, 0, ls_kfpc00 ); if trim (ptsxx00) is not null then p_errmsg := '处方记账错误：' || ptsxx00; rollback; return; end IF; end IF; end IF; end LOOP; --药事服务费扣费 --a.YJDJH0=LS_ZYBZYJDJH0这个是未收费的辨证论治费 begin /* open cur_YSFWF for select a.YJDJH0, a.YJKSBH,a.KDYS00 --into LS_YSFWFJE,lS_YJDJH0,LS_YJKSBH,LS_KDYS00 from YJ_YW0000 a,YJ_YWJJ00 b where a.YJDJH0=b.YJDJH0 and a.BRID00=LS_BRID00 and a.MZZYBZ='0' and (a.FJDJBZ = '8' or a.YJDJH0=LS_ZYBZYJDJH0) and a.SFDJH0+0=0 --优化查询速度 --and a.ZYHGHH in (select GHH000 from SF_BRXXB0 where BRID00=LS_BRID00 and GHRQ00>=LS_CZRQ01) and exists ( select 1 from SF_BRXXB0 where BRID00=LS_BRID00 and GHH000=a.ZYHGHH and GHRQ00>=LS_CZRQ01) and a.XMZT00 not in ('5','6') and (SF_SF_GETYYID00(a.KDKSBH)=LS_YYID00 or LS_YYID00='0'); loop fetch cur_YSFWF into lS_YJDJH0,LS_YJKSBH,LS_KDYS00; --2019.08.26 Pcwts00入参使用1 SP_YJ_YWSF00_NEW(LS_YJDJH0, LS_YJKSBH, LS_KDYS00, null, 'N',Pzxcgbz,Ptsxx00,Pxxxx00,'1'); IF trim(Ptsxx00) is not null then p_errmsg :=Ptsxx00; return; end if; end loop; close cur_YSFWF;*/ for cur_YSFWF0 in ( select a.YJDJH0, a.YJKSBH,a.KDYS00 --into LS_YSFWFJE,lS_YJDJH0,LS_YJKSBH,LS_KDYS00 from YJ_YW0000 a,YJ_YWJJ00 b where a.YJDJH0=b.YJDJH0 and a.BRID00=LS_BRID00 and a.MZZYBZ='0' --and (a.FJDJBZ = '8' or a.YJDJH0=LS_ZYBZYJDJH0) and (a.FJDJBZ = '8' or a.ZLXMID in (select to_number(BH0000) from BM_TYZD00 where ZDMC00 = '中医辩证论治费' and YXBZ00 ='1' and trim(BH0000) is not null )) and a.SFDJH0+0=0 and a.SL0000 > 0 --2024.11.15 只有正单才拿来执行 --优化查询速度 --and a.ZYHGHH in (select GHH000 from SF_BRXXB0 where BRID00=LS_BRID00 and GHRQ00>=LS_CZRQ01) and exists ( select 1 from SF_BRXXB0 where BRID00=LS_BRID00 and GHH000=a.ZYHGHH and GHRQ00>=LS_CZRQ01) and a.XMZT00 not in ('5','6') and (SF_SF_GETYYID00(a.KDKSBH)=LS_YYID00 or LS_YYID00='0')) loop SP_YJ_YWSF00_NEW(cur_YSFWF0.Yjdjh0, cur_YSFWF0.YJKSBH, cur_YSFWF0.KDYS00, null, 'N',Pzxcgbz,Ptsxx00,Pxxxx00,'1'); IF Pzxcgbz=0 then p_errmsg :=Ptsxx00; rollback; return; end if; end loop; exception when others then LS_YSFWFJE:=0; end; --修改停用金额 --update SF_BRZHXX set TYJE00=nvl(TYJE00,0)+nvl(LS_BCYXQFTYJE,0) where BRID00=LS_BRID00 and JFLBID=1; --病人账户还原到修改前停用金额 update SF_BRZHXX set TYJE00=nvl(LS_ORDBRZHTYJE,0) where BRID00=LS_BRID00 and JFLBID=1;--旧的账户停用金额 lS_YJDJH0 := -1; --生成处方队列以及发药 FOR rec_kf in cur_ykfcf LOOP --门诊结算不生成队列部门编码(1:不生成 0：生成),查找挂号科室是否维护在不生成队列属性表里 if nvl(substrb(SF_SF_YBSXZD_EXISTS(-1,'Settle_NotCFDL_BMBH00',rec_kf.GHKSBM),1,10),'0')='1' then LS_Prescription_CFDL :='0'; --是否生成处方队列 1:生成 0:不生成 LS_Prescription_CFFY :='0'; --是否处方发药 1:发药 0:不发药 end if; --LS_Prescription_CFDL:是否生成处方队列 1:生成 0:不生成 IF rec_kf.psjg00 not in (-1, -2) and LS_Prescription_CFDL='1' 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, ptsxx00, pxxxx00, ls_ygbh00, ls_czyks0 ); IF TRIM (ptsxx00) is not null then p_errmsg := '生产处方队列错误：' || ptsxx00; rollback; return; end IF; end IF; /* if LS_BRID00=4680293 then --update yf_cfdl00 set zt0000='0' where brid00 = LS_BRID00 and cflsh0 = rec_kf.cflsh0; select count(*)into ls_num02 from yf_cfdl00 a where brid00 = LS_BRID00 and cflsh0 = rec_kf.cflsh0 and phbz00 = '0' and zt0000 = '0'; select count(*)into ls_num00 from yf_cfdl00 a where brid00 = LS_BRID00 and cflsh0 = rec_kf.cflsh0 and phbz00 = '0'; select zt0000 into ls_temstr from yf_cfdl00 a where brid00 = LS_BRID00 and cflsh0 = rec_kf.cflsh0 and phbz00 = '0'; p_errmsg := 'ls_num02:'||ls_num02||',ls_num00:'||ls_num00||',zt0000:'||ls_temstr; return; end if; */ select COUNT (1) into ls_num00 from yf_cfdl00 where cflsh0 = rec_kf.cflsh0 and brid00 = ls_brid00; --LS_Prescription_CFFY :是否处方发药 1:发药 0:不发药 IF ls_num00 > 0 and LS_Prescription_CFFY='1' then --2019.08.26 sp_yf_mzcffy增加入参Pcwts00 sp_yf_mzcffy (rec_kf.cflsh0, ls_ygbh00, ls_ygbh00, '1', ls_ygbh00, ls_czyks0, 'N', pzxcgbz, ptsxx00, pxxxx00, '1' ); IF pzxcgbz=0 then p_errmsg := '处方发药错误：' ||ptsxx00; rollback; return; end IF; end IF; end IF; end LOOP; --整理处方队列表数据-- --ELSE -- p_errmsg :='院内账户余额不足,还需交:'||trim(TO_CHAR (ABS (NVL (ls_zfje00, 0) - NVL (ls_zye000, 0)),'9999990.00'))|| '元'; -- P_YBJJE0:=trim(TO_CHAR (ABS (NVL (ls_zfje00, 0) - NVL (ls_zye000, 0)),'9999990.00')); -- RETURN; --end IF; end IF; if LS_FBBH00=3 then LS_ZYE000:=SF_SST_GetBRZHYE_YKT(0,LS_BRID00,1,1);--1:总余额 else LS_ZYE000:=SF_SST_GetBRZHYE_YKT(0,LS_BRID00,1,0);--0:可用余额 end if; if nvl(LS_ZYE000,0)<0 then p_errmsg:='院内账户余额不足,还需交:'||trim(to_char(abs(nvl(LS_ZYE000,0)),'9999990.00'))|| '元'; P_YBJJE0:=trim(to_char (abs(nvl(LS_ZYE000,0)),'9999990.00')); rollback; return; end if; --1:执行未扣费--end-- if Vpslccs = 'N' then --2：未皮试提示-- select COUNT (1)into ls_num00 from vw_sst_qhxx00 c where brid00 = ls_brid00 and (c.YYID00=LS_YYID00 or LS_YYID00='0') --2018.01.10优化查询速度 --and c.GHH000 in (select GHH000 from SF_BRXXB0 where BRID00=LS_BRID00 and GHRQ00>=LS_CZRQ01) and exists (select 1 from SF_BRXXB0 where BRID00=LS_BRID00 and GHH000=c.GHH000 and GHRQ00>=LS_CZRQ01) and ((cfdgs0 <> '0' and LS_DMCFDL=0) or LS_DMCFDL=1) and psjg00 in (-2) and c.srrq00 >=LS_CZRQ00; IF ls_num00 > 0 then ls_wpsts := '您需要先到急诊科做皮试,皮试通过后再到自助机取号！'; end IF; --3:皮试未通过提示-- select COUNT (1) into ls_num00 from vw_sst_qhxx00 c where brid00 = ls_brid00 and (c.YYID00=LS_YYID00 or LS_YYID00='0') --2018.01.10优化查询速度 --and c.GHH000 in (select GHH000 from SF_BRXXB0 where BRID00=LS_BRID00 and GHRQ00>=LS_CZRQ01) and exists (select 1 from SF_BRXXB0 where BRID00=LS_BRID00 and GHH000=c.GHH000 and GHRQ00>=LS_CZRQ01) and ((cfdgs0 <> '0' and LS_DMCFDL=0) or LS_DMCFDL=1) and psjg00 in (-1) and c.srrq00 >=LS_CZRQ00; IF ls_num00 > 0 then ls_pswtgts := '您皮试未通过！'; end IF; end if; --判断别个药房是否有处方--请到【 ' + tmpclc2 + '】刷卡取药！ FOR rec_cf in cur_wpdcf loop ls_zjqyts := ls_zjqyts || ' ' || rec_cf.bmmc00; end loop; IF ls_zjqyts is not null then --ls_zjqyts := '请携带就诊卡到【' || ls_zjqyts || '】刷卡取药！'; ls_zjqyts := '请携带就诊卡到【' || ls_zjqyts || '】刷卡后等待取药！'; end IF; --4:判断别个药房是否有处方--请到【 ' + tmpclc2 + '】刷卡取药！ --5:判断病人非当日处方-- 有处方未进入排号队列,请到【 ' + tmpclc2 + '】刷卡取药！ 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 (c.YYID00=LS_YYID00 or LS_YYID00='0') --2018.01.10优化查询速度 --and c.GHH000 in (select GHH000 from SF_BRXXB0 where BRID00=LS_BRID00 and GHRQ00>=LS_CZRQ01) and exists (select 1 from SF_BRXXB0 where BRID00=LS_BRID00 and GHH000=c.GHH000 and GHRQ00>=LS_CZRQ01) and c.srrq00 >=LS_CZRQ00; 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 (c.YYID00=LS_YYID00 or LS_YYID00='0') --2018.01.10优化查询速度 --and c.GHH000 in (select GHH000 from SF_BRXXB0 where BRID00=LS_BRID00 and GHRQ00>=LS_CZRQ01) and exists (select 1 from SF_BRXXB0 where BRID00=LS_BRID00 and GHH000=c.GHH000 and GHRQ00>=LS_CZRQ01) and ((cfdgs0 <> '0' and LS_DMCFDL=0) or LS_DMCFDL=1) and c.srrq00 >=LS_CZRQ00 and exists ( select 1 from yf_mzcfmx s where s.cflsh0 = c.cflsh0 and NOT EXISTS ( select 1 from bm_tyzd00 where zdmc00 = '不进入排号队列药品目录' and yxbz00 = '1' and dj0000 = s.ypnm00)); IF ls_num01 > 0 then ls_yxckts :='请到优先窗口刷卡取药:处方数' || 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.YYID00=LS_YYID00 or LS_YYID00='0') --2018.01.10优化查询速度 --and c.GHH000 in (select GHH000 from SF_BRXXB0 where BRID00=LS_BRID00 and GHRQ00>=LS_CZRQ01) and exists (select 1 from SF_BRXXB0 where BRID00=LS_BRID00 and GHH000=c.GHH000 and GHRQ00>=LS_CZRQ01) and ((c.cfdgs0 in ('0','1') and LS_DMCFDL=0) or LS_DMCFDL=1) and c.srrq00 >=LS_CZRQ00; --用来判断是否有毒麻处方; IF ls_num02 > 0 then ls_dmckts := '请到毒麻窗口刷卡取药:处方数' ||to_char (ls_num02)||'张'; end if; p_qytsxx :=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 nvl(P_PXZXH0,0)>0 then ls_zxh000 := P_PXZXH0; else select sq_yf_cfdl00_phzxh0.NEXTVAL into ls_zxh000 from DUAL; end if; update yf_cfdl00 a SET phbz00 = '5', phzxh0 = ls_zxh000, phrq00 = TO_CHAR (SYSDATE, 'YYYYMMDD'), phdycs = 1, zdbh00 = p_zdbh00 where phbz00 = '0' and brid00 = ls_brid00 and zt0000 = '0' and a.cflsh0 in ( select cflsh0 from yf_mzcf00 where brid00 = ls_brid00 and (SF_SF_GETYYID00(GHKS00)=LS_YYID00 or LS_YYID00='0') and srrq00 >= ls_czrq00 --2018.01.10优化查询速度 --and GHH000 in (select GHH000 from SF_BRXXB0 where BRID00=LS_BRID00 and GHRQ00>=LS_CZRQ01) and exists (select 1 from SF_BRXXB0 where BRID00=LS_BRID00 and GHH000=a.GHH000 and GHRQ00>=LS_CZRQ01) and cfzt00 in ('0', '1', '2')); select count(*)into ls_num02 from vw_sst_zzcfqh a where brid00 = LS_BRID00 --2018.01.10优化查询速度 --and a.GHH000 in (select GHH000 from SF_BRXXB0 where BRID00=LS_BRID00 and GHRQ00>=LS_CZRQ01) and exists (select 1 from SF_BRXXB0 where BRID00=LS_BRID00 and GHH000=a.GHH000 and GHRQ00>=LS_CZRQ01) and zxh000 = ls_zxh000; /* if LS_BRID00=275736 then select count(*)into ls_num02 from yf_cfdl00 a where brid00 = LS_BRID00 and phzxh0 = ls_zxh000; p_errmsg := 'ls_num02:'||ls_num02||',ls_czrq00:'||ls_czrq00||',LS_CZRQ01:'||LS_CZRQ01; return; end if; */ if (ls_num02<=0) and ((trim(p_qytsxx)is null) and (trim(p_cftsxx) is null) ) then p_errmsg := '未找到该病人有效的取药单信息！'; return; else OPEN r_cur4 FOR select QYTM00, YFMC00, 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 (PYCKBH, QYLSH0, BRID00, LS_ZXH000) CFDYXX,0 CFXMS0, P_CARDNO as Cardno, --就诊卡号 CFJE00, --处方金额， SF_SST_GetBRZHYE_YKT(0,BRID00,1,1) as YNZHYE --院内账户余额 from (select distinct brid00, pyckbh, qytm00 AS qytm00, yfmc00, ckmc00 AS qyck00, fydlxh AS qylsh0, max (brxm00) AS xming0, max (ddrs00) ddrs00, max (pydlbh) pydlbh, week00 AS xq0000, max (nl0000) nl0000, max (xb0000) xb0000, sum(CFJE00)CFJE00 from VW_SST_ZZCFQH a where brid00 = ls_brid00 --2018.01.10优化查询速度 --and a.GHH000 in (select GHH000 from SF_BRXXB0 where BRID00=LS_BRID00 and GHRQ00>=LS_CZRQ01) and exists (select 1 from SF_BRXXB0 where BRID00=LS_BRID00 and GHH000=a.GHH000 and GHRQ00>=LS_CZRQ01) and zxh000 = ls_zxh000 group by brid00,pyckbh,qytm00,yfmc00,ckmc00,fydlxh,week00) order by qylsh0; end if; SP_TransLog(sysdate,'SP_SST_ZZCFQH_YKT',4200,null,ls_dyrq00||','||p_cardno); exception when no_data_found then p_errmsg := '自助处方取号异常！错误原因:'||sqlerrm; when others then p_errmsg := '自助处方取号异常！错误原因:'||sqlerrm; end;