CREATE OR REPLACE PROCEDURE SP_YS_YJXMZDDC --本存储过程应用于门诊医生开处方时，根据用法、皮试自动生成对应项目的过程。 -- MODIFICATION HISTORY -- Person Date Comments -- lintj 2012.08.27 modify -- zhanghr 2012.11.29 医保病人用法带出的项目变更为待审核状态 -- zhanghr 2013.02.06 省保带出不需要审核，其他医保需要，留观都不需要审核，省保留观少一次 -- zhangwz 2013.05.15 省保的“胰岛素”类药品带出的项目需要加入审核 -- zhaoxz 2016.10.11 添加医保病人自动带出项目是否需要审核判断，参数YS_YBBRZDDCSFXYSH控制 MZYS9-20161011-001 -- liuj 2016.11.18 织带维护的参数没有对应药典表的药品内码值带出织带报错 for MZYS9-20161118-002 -- huanglw 2018.06.04 过程加上SP_TRANSLOG MZYS9-20180531-002 -- cyhong 2019.10.18 用药带出规则增加YYID00，配置规则可以根据不同院区进行配置 for MZYS9-20191014-003 -- ruanbh 2022.09.06 带出项目默认处方的特殊病种 MZYS9-20220809-002 ( as_YZCFID in char , --注射相关处方号； ad_ZYGHID in char , --门诊(GHH000)流水号 as_LRXM00 in char , --录入人姓名； as_JZBZ00 in char , --急诊标志'Y'是'N'否； as_ZYCFPD in char --中药处方判定'1'中药处方'0'非中药处方 ) AS Vbrid00 YS_CFXXB0.BRID00%type; Vghid00 YS_CFXXB0.GHID00%type; Vkdys00 YS_CFXXB0.YSGZH0%type; Vkdksbh YS_CFXXB0.GHKS00%type; Vzyts00 YS_CFXXB0.ZYTS00%type; Vyysj00 YS_CFMX00.YYSJ00%type; Vypnm00 YS_CFMX00.YPNM00%type; Vzb0000 YS_CFMX00.CFID00%type; Vzdlczb YS_CFMX00.CFID00%type; --最大疗程对应的组别； Vpcid00 YS_CFMX00.PCID00%type; Vzqcd00 BM_PC0000.ZQCD00%type; Vzqcs00 BM_PC0000.ZQCS00%type; Vzqdw00 BM_PC0000.ZQDW00%type; Vjsxmks BM_BMBM00.BMBH00%type; Vlyxmid MZ_DCXMGZ.LYXMID%type; Vsl0000 YJ_YW0000.SL0000%type; Vzxcs00 YF_MZCFMX.ZXCS00%type; Vcfid00 YJ_YW0000.CFID00%type; Vzdnm00 BM_YD0000.YPNM00%type; --织袋药品内码 Vdcxmcf char(1); --带出项目拆分 Vbzypgs number(4); --该组处方药品个数 counteryps number(4); --皮试带出项目已收费或已执行 Vzdsl00 number(5); --药品剂数量 --20230918 number(2)改 Vzyfjyp number(10); Vxyfjyp number(10); Vcount0 number(5); Verrmsg varchar2(255); C_CFXZ00 YS_CFXXB0.CFXZ00%TYPE; C_YPSJDJ BM_YD0000.SJDJ00%TYPE; C_BRNL00 NUMBER(5); ls_ZSDSYSFJY char(1); ls_DXSYBZ BM_YD0000.DXSYBZ%TYPE; --是否大输液 VDcxmid MZ_DCXMGZ.DCXMID%type; Vekcfjdfs XT_XTCS00.VALUE0%TYPE; --儿科处方界定方式 E_ERROR0 EXCEPTION; VYPMC00 varchar(500); --药品名称 T_YPMC00 YS_CFMX00.YPMC00%TYPE; V_SQLSTR VARCHAR2(1000); V_KDCS00 number(5); TYPE T_CUR_YS_XYKDCS IS REF CURSOR; --西成药开单次数 CUR_YS_XYKDCS t_cur_ys_xykdcs; Vzydmcfcxts varchar2(500);--中药毒麻处方持续天数限制 Vdmzytsxz number(3); --中药毒麻处方持续天数限制 默认为0 v_ZQDW00 BM_PC0000.zqdw00%type;--周期单位 --特殊病种限制变量 Vtsbzmc bm_tsbzb0.mc0000%type; Vybzxlb ic_ybbrlb.ybzxlb%type; -- Vfbbh00 ic_ybbrlb.fbbh00%type; Vybbrlb bm_ybbrlb.YBBRLB%type; Vtsbzxz char(1); --是否根据特殊病种限制处方和项目，Y:是 N:否 默认为N Vtsbzbh yj_yw0000.bh0000%type; --特殊病种编号 Vmlxzbz bm_tsbzb0.MLXZBZ%type; --目录限制标志'0'不限制'1'只限制药品'2'只限制诊疗'3'都限制 scfid00 YJ_YW0000.CFID00%type; -- VARCHAR2(100); --拆分方式为N，将被合并的cfid00 YS_ZTJXHDCXMID varchar2(20); --嘱托加小壶带出的项目ID Vyfzt00 varchar2(20); --用法嘱托标志 Vjmsyyf varchar2(100); --静脉精密输液用法 v_JMSYDZTZ CHAR(1); --静脉输液单组时是否进行项目调整 单组时，用每方每疗程的项目替代第二组起每组一次的项目 Vzxcgbz number; Vtsxx00 varchar2(200); Vxxxx00 varchar2(200); Vxb0000 BM_BRXXB0.BRXB00%type; Vcsrq00 BM_BRXXB0.BRCSRQ%type; Vnl0000 varchar2(30); --年龄 Vbrxm00 BM_BRXXB0.BRXM00%type; Vkdysxm bm_ygbm00.zwxm00%type; Vysszks bm_ygbm00.bmbh00%type; Vydsjscl number; --胰岛素加收材料诊疗内码 Vczyds0 number; --胰岛素药品个数 Vjmsybz int; --精密输液标志 1 是 <>1否 Vlqxmid number; --留观诊疗项目ID Vypdlbh yf_mzcfmx.ypdlbh%type; --药品大类编号 Vybdcsfxysq XT_XTCS00.VALUE0%TYPE; --医保病人自动带出是否需要审核 Vyyid00 XT_YYXX00.YYID00%TYPE; --开单院区 VTSBZBH_CF YS_CFXXB0.TSBZBH%type; --处方特殊病种编号 --取处方组别 cursor CUR_MZ_DCXMGZ_ZB0000 is select distinct a.YFID00,nvl(a.ZB0000,a.CFID00)ZB0000,a.ZXCS00,a.PCID00,a.YYSJ00, '用法' yfzt00, sign(instrb(','||Vjmsyyf||',',','||YFID00||',')) JMZYBZ from YS_CFMX00 a,MZ_DCXMGZ b where a.CFLSH0=as_YZCFID and a.YFID00=b.LYXMID and A.ZXCS00>0 and a.PCID00 is not null and (b.YYID00 = '0' or b.YYID00 = Vyyid00) union all select distinct '0' YFID00,nvl(a.ZB0000,a.CFID00),a.ZXCS00,a.PCID00,a.YYSJ00, '嘱托' yfzt00,0 from YS_CFMX00 a where a.CFLSH0=as_YZCFID and A.ZXCS00>0 and a.PCID00 is not null and yyzf00 like '%加小壶%' and YS_ZTJXHDCXMID<>'0' order by ZB0000,YFID00; --取本组对应的CFID00 cursor CUR_CFID00 is select a.CFID00, a.YPNM00, nvl(a.CSL000, 0) csl000, nvl(a.zxcs00, 0) zxcs00, a.zhl000 from YS_CFMX00 a where a.CFLSH0=as_YZCFID and nvl(a.ZB0000,a.CFID00)=Vzb0000; --取本组带出的项目 cursor CUR_MZ_DCXMGZ_DCXMID is select decode(CJFS00,'3',decode(Vczyds0,0,DCXMID,Vydsjscl),DCXMID)DCXMID, SL0000,CJFS00 from MZ_DCXMGZ where LYXMID=Vlyxmid and CSLY00='0' and (YYID00 = '0' or YYID00 = Vyyid00) union all select to_number(YS_ZTJXHDCXMID) DCXMID, 1 SL0000,'a' CJFS00 from dual where Vlyxmid=0 and YS_ZTJXHDCXMID<>'0'; --取本组带出的项目_皮试 cursor CUR_MZ_DCXMGZ_DCXMID_PS is select DCXMID,SL0000,CJFS00 from MZ_DCXMGZ where LYXMID=Vlyxmid and CSLY00='1' and (YYID00 = '0' or YYID00 = Vyyid00); --如果该组别有第二组起每组一次的采集方式(加药) cursor C_JY0000 is select DCXMID,SL0000 from MZ_DCXMGZ where LYXMID=Vlyxmid and CJFS00 in ('5') and (YYID00 = '0' or YYID00 = Vyyid00); --中药处方的整剂用法 cursor CUR_MZ_DCXMGZ_ZJYFID is select distinct a.ZJYFID,nvl(a.ZB0000,999) from YS_CFMX00 a,MZ_DCXMGZ b where a.CFLSH0=as_YZCFID and a.ZJYFID=b.LYXMID and (b.YYID00 = '0' or b.YYID00 = Vyyid00) ORDER BY nvl(a.ZB0000,999),a.ZJYFID; --皮试项目 cursor CUR_MZ_DCXMGZ_PSLBID is select distinct a.PSLBID,nvl(a.ZB0000,a.CFID00),a.CFID00 from YS_CFMX00 a,MZ_DCXMGZ b where a.CFLSH0=as_YZCFID and a.PSLBID is not null and (b.YYID00 = '0' or b.YYID00 = Vyyid00) and a.PSLBID=b.LYXMID ORDER BY nvl(a.ZB0000,a.CFID00),a.PSLBID; --本次产生的诊疗单 cursor CUR_YJ_YW0000 is select YJDJH0,KDYS00,KDKSBH,ZLXMID,SL0000 from YJ_YW0000 where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and MZZYBZ='0' and XMZT00 in ('0','1','5','9') and DCBZ00 in ('1','2') ; --本次产生的诊疗单(每个带出项目只取最大一条记录) cursor CUR_YJ_YW0001 is select YJDJH0,ZLXMID from YJ_YW0000 a where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and MZZYBZ='0' and XMZT00 in ('0','1','5','9') and DCBZ00 in ('1','2') and YJDJH0= (select min(YJDJH0) from YJ_YW0000 where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and MZZYBZ='0' and XMZT00 in ('0','1','5','9') and DCBZ00 in ('1','2') and ZLXMID=a.ZLXMID); --本次产生的诊疗单(每个带出项目每组只取最大一条记录) cursor CUR_YJ_YW0002 is select YJDJH0,ZLXMID,CFID00 from YJ_YW0000 a where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and MZZYBZ='0' and XMZT00 in ('0','1','5','9') and DCBZ00 in ('1','2') and YJDJH0= (select min(YJDJH0) from YJ_YW0000 where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and MZZYBZ='0' and XMZT00 in ('0','1','5','9') and DCBZ00 in ('1','2') and ZLXMID=a.ZLXMID and nvl(CFID00,'0')=nvl(a.CFID00,'0')); --取毒麻处方的中药 cursor CUR_MZ_DMZYCF is Select b.* from YS_CFXXB0 a,YS_CFMX00 b where a.CFLSH0=as_YZCFID and a.CFXZ00>'0' and a.CFLSH0=b.CFLSH0 and b.PCID00 is not null and exists(select 1 from BM_YD0000 c where c.YPNM00=b.YPNM00 and c.LBBH00='2') ; --取特殊病种不允许开的药品 cursor c_tsbz is select e.YPMC00 from YS_CFMX00 e,VW_BM_YBSFDY f where e.CFLSH0=as_YZCFID and e.YPNM00=f.SFXMID and f.SFYP00='Y' and f.FBBH00=Vfbbh00 and f.YBZXLB=Vybzxlb and f.YBBRLB=Vybbrlb and not exists ( select 1 from BM_TSBZXM b, BM_TSBZB0 c where f.YBXMBH=B.XMBH00 AND b.BH0000=C.BH0000 and B.YBZXLB=C.YBZXLB and B.YBZXLB=Vybzxlb and ( B.BH0000=Vtsbzbh or ( exists (select 1 from VW_YS_TSBZB_GXYTNB L where L.BH0000=Vtsbzbh) and B.BH0000 in (select BH0000 from VW_YS_TSBZB_GXYTNB) ) ) and c.MLXZBZ in ('1','3')); v_LogStr varchar(500); begin v_LogStr:='SP_YS_YJXMZDDC(' ||as_YZCFID||',' ||ad_ZYGHID||',' ||as_LRXM00||',' ||as_JZBZ00||',' ||as_ZYCFPD||')'; SP_TRANSLOG(sysdate,'SP_YS_YJXMZDDC',0,0,v_LogStr); Verrmsg:=''; SP_YS_XZYPYF(as_YZCFID,'N'); select count(*) into Vcount0 from XT_XTCS00 where NAME00 = 'YS_SFXZXCYYTZKYC' and VALUE0 = 'Y'; select XM0000,XB0000,CSRQ00,SF_XT_CSRQTONL(CSRQ00, to_char(sysdate,'yyyymmdd')),TSBZBH,BRID00 into Vbrxm00,Vxb0000,Vcsrq00,Vnl0000,Vtsbzbh,Vbrid00 from SF_BRXXB0 where GHH000=ad_ZYGHID; select b.ybzxlb,a.fbbh00,a.ybbrlb into Vybzxlb,Vfbbh00,Vybbrlb from BM_BRXXB0 a,IC_YBBRLB b where a.FBBH00=b.FBBH00 and a.YBLB00=b.YBLB00 and brid00= Vbrid00; select TSBZBH into VTSBZBH_CF from YS_CFXXB0 where CFLSH0=as_YZCFID; if Vcount0 > 0 then --西成药一天只开一次 Vcount0 := 0; V_SQLSTR := 'SELECT a.ypmc00, count(1) KDCS00 FROM ys_cfmx00 a, ys_cfxxb0 b '|| 'WHERE a.cflsh0 = b.cflsh0 AND b.ypdlbh = ''0'' AND b.brid00 = '||Vbrid00|| 'AND b.srrq00 = TO_CHAR (SYSDATE, ''YYYYMMDD'') '|| 'having count(1) > 1 GROUP BY ypnm00, ypmc00'; open CUR_YS_XYKDCS for V_SQLSTR; loop fetch CUR_YS_XYKDCS into T_YPMC00, V_KDCS00; exit when CUR_YS_XYKDCS%NOTFOUND; Vcount0 := Vcount0 + 1; if Vcount0 = 1 then VYPMC00 := '（'||T_YPMC00||'）' ; else VYPMC00 := VYPMC00||'，（'||T_YPMC00||'）'; end if; end loop; if Vcount0 > 0 then Verrmsg := '西成药：'||VYPMC00||'。一天内相同药品不可开多条记录！请检杀￥！'; raise E_ERROR0; end if; end if; --毒麻中药处方持续天数限制 select to_number(nvl(max(trim(VALUE0)),'0')) into Vdmzytsxz from XT_XTCS00 where NAME00='YS_DMZYCFCXTSXZ'; if Vdmzytsxz>0 then Vzydmcfcxts:=' '; for DMZYCFMX in CUR_MZ_DMZYCF loop Verrmsg:='BM_PC0000找不到PCID00='||to_char(DMZYCFMX.PCID00)||'的记录'; select trim(ZQDW00) into v_ZQDW00 from BM_PC0000 where PCID00=DMZYCFMX.PCID00; if (((v_ZQDW00='天') and (DMZYCFMX.YYSJ00>Vdmzytsxz))) or (v_ZQDW00='周') or (((v_ZQDW00='小时') and (DMZYCFMX.YYSJ00>Vdmzytsxz*24))) then if Vzydmcfcxts=' ' then Vzydmcfcxts:=Vzydmcfcxts||DMZYCFMX.YPMC00; else Vzydmcfcxts:=Vzydmcfcxts||'、'||DMZYCFMX.YPMC00; end if; end if; end loop; if Vzydmcfcxts<>' ' then Verrmsg:=Vzydmcfcxts||'的持续天数不能超过'||to_char(Vdmzytsxz)||'天 '; raise E_ERROR0; end if; end if; --'Y' 所有带出项目都拆分成数量为1, 'N'相同项目按处方进行累加 --'T' 默认,相同项目按组别进行累加, 'M':'T'基础上按每天执行拆分 select nvl(max(trim(value0)),'N') into Vdcxmcf from XT_XTCS00 where NAME00='YS_YW0000_YJXMCF'; --嘱托加小壶带出的项目ID select nvl(max(trim(value0)),'0') into YS_ZTJXHDCXMID from XT_XTCS00 where name00='YS_ZTJXHDCXMID'; --静脉滴注费收法 select nvl(max(trim(value0)),'N') into v_JMSYDZTZ from XT_XTCS00 where name00='YS_JMSYDZTZ'; --静脉精密输液器用法 select nvl(max(trim(VALUE0)),'0') into Vjmsyyf from XT_XTCS00 where name00='YS_JMSYYF'; --注射大输液是否需要寄药 select trim(NVL(max(value0),'N')) into ls_ZSDSYSFJY from XT_XTCS00 where name00='YS_ZSDSYSFJY'; --急诊留观项目ID select to_number(NVL(max(value0),'0')) into Vlqxmid from XT_XTCS00 where name00='YS_JZLGZLXMID'; -- 取出儿科处方界定方式 参数 select nvl(max(trim(value0)),'0') into Vekcfjdfs from xt_xtcs00 where name00='YS_EKCFJDFS'; --胰岛素加药诊疗内码(固定每组一次加收) select to_number(nvl(max(value0),'0')) into Vydsjscl from XT_XTCS00 where name00='YS_YDSJSCL'; --医保病人自动带出是否需要审核 select nvl(max(trim(value0)),'N') into Vybdcsfxysq from XT_XTCS00 where NAME00='YS_YBBRZDDCSFXYSH'; --*******************************特殊病种对应的药品目录限制begin***********************************************************-- select nvl(MAX(TRIM(VALUE0)),'N') into Vtsbzxz FROM XT_XTCS00 WHERE NAME00='YS_TSBZXZCFXM'; if Vtsbzxz='Y' THEN if Vtsbzbh is null then select a.TSBZBH into Vtsbzbh from YF_MZCF00 a where CFLSH0=as_YZCFID; end if; if (trim(Vtsbzbh) is not null) then select nvl(max(MLXZBZ),'0'), max(mc0000) into Vmlxzbz,Vtsbzmc from bm_tsbzb0 where bh0000=Vtsbzbh and ybzxlb= Vybzxlb; if Vmlxzbz in ('1','3') then Vcount0 := 0; VYPMC00:=''; for tsbz in c_tsbz loop Vcount0 := Vcount0 +1; if Vcount0 = 1 then VYPMC00 := '（'||tsbz.YPMC00||'）' ; else VYPMC00 := VYPMC00||'，（'||tsbz.YPMC00||'）'; end if; end loop; if Vcount0 > 0 then Verrmsg :='特殊病种"'||trim(Vtsbzmc) ||'"不允许开药品：'|| VYPMC00; raise E_ERROR0; end if; end if; end if; end if; --***************************************特殊病种对应的药品目录限制end***************************************************-- --*******************************自动带出药品方式处理附加费*********************************-- if as_ZYCFPD='1' then --中药处方判定'1'中药处方'0'非中药处方 --医大附二要求根据表XT_ZDSZ00的设置，中药处方自动带出织袋，作为药品处理 select to_number(nvl(max(VALUE0),'0')) into Vzdnm00 from XT_XTCS00 where TRIM(UPPER(NAME00))='BM_ZDNM00'; if Vzdnm00 is not null then --清除旧的织袋费 delete from YS_CFMX00 where CFLSH0=AS_YZCFID and YPNM00=Vzdnm00; delete from YF_MZCFMX where CFLSH0=AS_YZCFID and YPNM00=Vzdnm00; -------------- --带出织袋费 --select NVL(count(1),0) into Vzdsl00 from YS_CFMX00 where CFLSH0=AS_YZCFID and YPNM00=Vzdnm00; select NVL(count(1),0) into Vzdsl00 from XT_ZDSZ00 A ,YS_CFMX00 B where CFLSH0=AS_YZCFID and A.YPNM00=B.YPNM00; if Vzdsl00>0 then ---- insert into YS_CFMX00 (CFID00, CFLSH0, YPNM00, YPMC00,csl000,YPZSL0,jldw00,CFDW00,ZHL000,LSDJ00,YPGG00,XMYFMC ) select (SQ_YS_CFMX00_CFID00.NEXTVAL),AS_YZCFID,Vzdnm00,YPMC00,Vzdsl00,ZYTS00*Vzdsl00,JLDW00,JLDW00,'1',LSJ000,YPGG00,'' from BM_YD0000,YS_CFXXB0 where CFLSH0=AS_YZCFID and YPNM00=Vzdnm00 and ROWNUM=1; --select lbbh00 into Vypdlbh from bm_yd0000 where ypnm00 = Vzdnm00; MZYS9-20161118-002 begin select lbbh00 into Vypdlbh from bm_yd0000 where ypnm00 = Vzdnm00; exception when no_data_found then vypdlbh:='2'; end; insert into YF_MZCFMX(CFID00,CFLSH0,YPNM00,YPMC00,YPGG00,LSDJ00,YPYFMC,PCMC00,CSL000,JLDW00,YYSJ00,YYSJDW,YFSMMC,ZB0000,ZJYFMC,XMYFMC,YPZSL0,CFDW00,ZHL000,CXBZ00,SFXS00,SFZBY0,THBZ00,PSLBID,ZFBL00,YBLB00,BZ0000,ZXCS00,YPDLBH) select A.CFID00,A.CFLSH0,A.YPNM00,A.YPMC00,A.YPGG00,A.LSDJ00,B.YPYFMC,C.PCMC00,A.CSL000,A.JLDW00,A.YYSJ00,A.YYSJDW,A.YYZF00,A.ZB0000,B.YPYFMC,A.XMYFMC,A.YPZSL0,A.CFDW00,A.ZHL000,'Z','Y',SFZBY0,'0',A.PSLBID,A.ZFBL00,A.YBLB00,A.YYZF00,ZXCS00,Vypdlbh from YS_CFMX00 A,BM_YPYF00 B,BM_PC0000 C where A.YPNM00= Vzdnm00 and A.CFLSH0=AS_YZCFID and A.YFID00=B.YFID00(+) and A.PCID00=C.PCID00(+); UPDATE YS_CFXXB0 SET CFZJE0=(select ROUND(SUM(LSDJ00*YPZSL0),2) from YF_MZCFMX where CFLSH0=AS_YZCFID and SFZBY0='N')where CFLSH0=AS_YZCFID; UPDATE YF_MZCF00 SET CFZJE0=(select ROUND(SUM(LSDJ00*YPZSL0),2) from YF_MZCFMX where CFLSH0=AS_YZCFID and SFZBY0='N')where CFLSH0=AS_YZCFID; end if; end if; --自动带出织袋处理完毕 --增加中药处方附加药品 select to_number(nvl(max(value0),'0')) into Vzyfjyp from xt_xtcs00 where name00='YF_ZYFJF_YPNM00'; if Vzyfjyp>0 then delete from YS_CFMX00 where CFLSH0=as_YZCFID and ypnm00=Vzyfjyp; delete from YF_MZCFMX where CFLSH0=as_YZCFID and ypnm00=Vzyfjyp; insert into YS_CFMX00 (CFID00, CFLSH0, YPNM00, YPMC00,csl000,YPZSL0,jldw00,CFDW00,ZHL000,LSDJ00,YPGG00,XMYFMC ) select SQ_YS_CFMX00_CFID00.NEXTVAL,as_YZCFID,Vzyfjyp,ypmc00,1,ZYTS00,mzfydw,mzfydw,'1',round(lsj000/k2j000,4),'','' from bm_yd0000 ,yf_mzcf00 where ypnm00=Vzyfjyp and CFLSH0=as_YZCFID; select lbbh00 into Vypdlbh from bm_yd0000 where ypnm00 = Vzyfjyp; insert into YF_MZCFMX(CFID00,CFLSH0,YPNM00,YPMC00,YPGG00,LSDJ00,YPYFMC,PCMC00,CSL000,JLDW00,YYSJ00,YYSJDW,YFSMMC,ZB0000,ZJYFMC,XMYFMC,YPZSL0,CFDW00,ZHL000,CXBZ00,SFXS00,SFZBY0,THBZ00,PSLBID,ZFBL00,YBLB00,BZ0000,ZXCS00,YPDLBH) select a.CFID00,a.CFLSH0,a.YPNM00,a.YPMC00,a.YPGG00,a.LSDJ00,b.YPYFMC,c.PCMC00,a.CSL000,a.JLDW00,a.YYSJ00,a.YYSJDW,a.YYZF00,a.ZB0000,b.YPYFMC,a.XMYFMC,a.YPZSL0,a.CFDW00,a.ZHL000,'Z','Y',SFZBY0,'0',a.PSLBID,a.ZFBL00,a.YBLB00,a.YYZF00,ZXCS00,Vypdlbh from YS_CFMX00 a,BM_YPYF00 b,BM_PC0000 c where a.YPNM00= Vzyfjyp and a.CFLSH0=as_YZCFID and a.YFID00=b.YFID00(+) and a.PCID00=c.PCID00(+); Update ys_cfxxb0 set CFZJE0=(select round(sum(LSDJ00*YPZSL0),2) from YF_MZCFMX where CFLSH0=as_YZCFID and SFZBY0='N')where CFLSH0=as_YZCFID; Update YF_MZCF00 set CFZJE0=(select round(sum(LSDJ00*YPZSL0),2) from YF_MZCFMX where CFLSH0=as_YZCFID and SFZBY0='N')where CFLSH0=as_YZCFID; end if; else --增加西药处方附加费 select to_number(nvl(max(value0),'0')) into Vxyfjyp from xt_xtcs00 where name00='YF_XCYFJF_YPNM00'; if Vxyfjyp>0 then delete from YS_CFMX00 where CFLSH0=as_YZCFID and ypnm00=Vxyfjyp; delete from YF_MZCFMX where CFLSH0=as_YZCFID and ypnm00=Vxyfjyp; insert into YS_CFMX00 (CFID00, CFLSH0, YPNM00, YPMC00,csl000,YPZSL0,jldw00,CFDW00,ZHL000,LSDJ00,YPGG00,XMYFMC ) select SQ_YS_CFMX00_CFID00.NEXTVAL,as_YZCFID,Vxyfjyp,ypmc00,null,1,mzfydw,mzfydw,'1',round(lsj000/k2j000,4),'','' from bm_yd0000 where ypnm00=Vxyfjyp; select lbbh00 into Vypdlbh from bm_yd0000 where ypnm00 = Vzyfjyp; insert into YF_MZCFMX(CFID00,CFLSH0,YPNM00,YPMC00,YPGG00,LSDJ00,YPYFMC,PCMC00,CSL000,JLDW00,YYSJ00,YYSJDW,YFSMMC,ZB0000,ZJYFMC,XMYFMC,YPZSL0,CFDW00,ZHL000,CXBZ00,SFXS00,SFZBY0,THBZ00,PSLBID,ZFBL00,YBLB00,BZ0000,ZXCS00,YPDLBH) select a.CFID00,a.CFLSH0,a.YPNM00,a.YPMC00,a.YPGG00,a.LSDJ00,b.YPYFMC,c.PCMC00,a.CSL000,a.JLDW00,a.YYSJ00,a.YYSJDW,a.YYZF00,a.ZB0000,b.YPYFMC,a.XMYFMC,a.YPZSL0,a.CFDW00,a.ZHL000,'Z','Y',SFZBY0,'0',a.PSLBID,a.ZFBL00,a.YBLB00,a.YYZF00,ZXCS00,Vypdlbh from YS_CFMX00 a,BM_YPYF00 b,BM_PC0000 c where a.YPNM00= Vxyfjyp and a.CFLSH0=as_YZCFID and a.YFID00=b.YFID00(+) and a.PCID00=c.PCID00(+); Update ys_cfxxb0 set CFZJE0=(select round(sum(LSDJ00*YPZSL0),2) from YF_MZCFMX where CFLSH0=as_YZCFID and SFZBY0='N')where CFLSH0=as_YZCFID; Update YF_MZCF00 set CFZJE0=(select round(sum(LSDJ00*YPZSL0),2) from YF_MZCFMX where CFLSH0=as_YZCFID and SFZBY0='N')where CFLSH0=as_YZCFID; end if; end if; --*******************************以上自动带出药品的处理*********************************-- --*******************************根据药品用法自动带出诊疗项目*********************************-- --初始化参数 select a.BRID00,a.GHID00,a.YSGZH0,a.GHKS00,a.ZYTS00,b.zwxm00,b.BMBH00 into Vbrid00,Vghid00,Vkdys00,Vkdksbh,Vzyts00,Vkdysxm,Vysszks from YS_CFXXB0 a,BM_YGBM00 b where a.YSGZH0=b.ygbh00 and a.CFLSH0=as_YZCFID; if Vkdksbh=323 then select count(*) into Vcount0 from XT_YYXX00 where YYID00='227049'; --儿科要按标准收静脉滴注费 if Vcount0>0 then v_JMSYDZTZ:='N'; end if; end if; --非中药处方带出项目； if as_ZYCFPD='0' then --删除YJ_YWMX00,YJ_YW0000表含有该处方号未收费项目； delete from YJ_YWMX00 where YJDJH0 in (select YJDJH0 from YJ_YW0000 where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and DCBZ00 in ('1') and XMZT00 in ('0','1','5','9')); delete from YJ_YW0000 where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and DCBZ00 in ('1') and XMZT00 in ('0','1','5','9'); open CUR_MZ_DCXMGZ_ZB0000;--取组 loop fetch CUR_MZ_DCXMGZ_ZB0000 into Vlyxmid,Vzb0000,Vzxcs00,Vpcid00,Vyysj00,Vyfzt00,Vjmsybz; exit when CUR_MZ_DCXMGZ_ZB0000%notFOUND; --处方每组总个数用于采集方式中N的值； if Vlyxmid <> 0 then select count(CFID00) into Vbzypgs from YS_CFMX00 where CFLSH0=as_YZCFID and YFID00=Vlyxmid and nvl(ZB0000,CFID00)=Vzb0000; if Vydsjscl>0 then--判断是否存在胰岛素 select count(*) into Vczyds0 from YS_CFMX00 where CFLSH0=as_YZCFID and YFID00=Vlyxmid and nvl(ZB0000,CFID00)=Vzb0000 and YPMC00 like '%胰岛素%'; end if; else select count(CFID00) into Vbzypgs from YS_CFMX00 where CFLSH0=as_YZCFID and YYZF00 LIKE '%加小壶%' and nvl(ZB0000,CFID00)=Vzb0000; end if; --取本组的CFID00 Vcfid00:=''; for CFID in CUR_CFID00 loop if ls_ZSDSYSFJY = 'Y' THEN--莆田大输液带出时填写寄药数量和寄药科室 select DXSYBZ INTO ls_DXSYBZ FROM BM_YD0000 WHERE YPNM00= CFID.YPNM00; if (ls_DXSYBZ='1') AND (CFID.CSL000>0) AND (CFID.ZXCS00>0) THEN select NVL(max(DCXMID),0) into VDcxmid from MZ_DCXMGZ where LYXMID=Vlyxmid and CSLY00='0' and (YYID00 = '0' or YYID00 = Vyyid00); Vjsxmks:=sf_ys_xmzxks(VDcxmid,Vkdksbh); UPDATE YF_MZCFMX SET JYSL00=ceil(CFID.CSL000*CFID.ZXCS00/CFID.ZHL000), JYKS00 = Vjsxmks WHERE CFID00= CFID.CFID00; END IF; END IF; --DECODE(d.DXSYBZ,''0'',0,''1'', Round(nvl(a.CSL000,0)*a.zxcs00/a.zhl000)) Vcfid00:=Vcfid00||','||CFID.CFID00; end loop; Vcfid00:=Vcfid00||','; --取带出项目 for DCXM in CUR_MZ_DCXMGZ_DCXMID loop --取出频次 Vsl0000:=0; Verrmsg:='BM_PC0000找不到PCID00='||to_char(Vpcid00)||'的记录'; select ZQCD00,ZQCS00,ZQDW00 into Vzqcd00,Vzqcs00,Vzqdw00 from BM_PC0000 where PCID00=Vpcid00; Verrmsg:=''; Vjsxmks:=sf_ys_xmzxks(DCXM.DCXMID,Vkdksbh); if dcxm.CJFS00='4' then if v_JMSYDZTZ in ('M','Y') then Vsl0000:=Vzxcs00; else Vsl0000:=ceil(Vzxcs00/Vzqcs00); end if; else select decode(dcxm.CJFS00,'2',ceil(Vzxcs00/Vzqcs00),Vzxcs00)*dcxm.SL0000*(decode(dcxm.CJFS00,'0',1,'1',Vbzypgs,'2',Vbzypgs-1,'3',CEIL((Vbzypgs-1)/Vbzypgs),'5',1))into Vsl0000 from dual where rownum=1; end if; --先全部按M的方式进行拆分，插入到医技主表 if Vsl0000>0 then if Vzqdw00='天' then Vzdsl00:=Vsl0000; Vcount0:=0; while Vsl0000>0 loop insert into YJ_YW0000 (ZLXMID, SL0000,DW0000,YJKSBH, KDYS00, KDKSBH, KDRQ00, KDSJ00, LRXM00,JZBZ00,XMZT00, YZCFID, DCBZ00, YJDJH0, BRID00, ZYGHID,MZZYBZ,CFID00, BZ0000, ZLXMJC, ZYHGHH, BRXM00, XB0000, CSRQ00, NL0000,KDYSXM,YSSZKS,BWMC00,FJDJBZ,BH0000) select dcxm.DCXMID,ceil(Vzdsl00/Vyysj00),a.DW0000,Vjsxmks,Vkdys00,Vkdksbh,to_char(sysdate+Vcount0*Vzqcd00,'YYYYMMDD'), to_char(sysdate,'HH24:MI:SS'),as_LRXM00,as_JZBZ00,'0',as_YZCFID,'1',SQ_YJ_YW0000_YJDJH0.NEXTVAL, Vbrid00,Vghid00,'0',Vcfid00,--decode(dcxm.CJFS00,'4',null,Vcfid00), decode(dcxm.CJFS00,'4','每天一次','5','第二组起','用药带出'), ZLXMJC, ad_ZYGHID,Vbrxm00,Vxb0000,Vcsrq00,Vnl0000,Vkdysxm,Vysszks,to_char(Vjmsybz),'a',VTSBZBH_CF from VW_BM_ZLZD00 a where A.ZLXMID=dcxm.DCXMID; Vsl0000:=Vsl0000-ceil(Vzdsl00/Vyysj00); Vcount0:=Vcount0+1; end loop; elsif Vzqdw00='周' then Vzdsl00:=Vsl0000; insert into YJ_YW0000 (ZLXMID, SL0000,DW0000,YJKSBH, KDYS00, KDKSBH, KDRQ00, KDSJ00, LRXM00,JZBZ00,XMZT00, YZCFID, DCBZ00, YJDJH0, BRID00, ZYGHID,MZZYBZ, CFID00,BZ0000, ZLXMJC, ZYHGHH, BRXM00, XB0000, CSRQ00, NL0000,KDYSXM,YSSZKS,BWMC00,FJDJBZ,BH0000) select dcxm.DCXMID,ceil(Vzdsl00/Vzqcs00),a.DW0000,Vjsxmks,Vkdys00,Vkdksbh,to_char(sysdate+Vcount0*Vzqcd00,'YYYYMMDD'), to_char(sysdate,'HH24:MI:SS'),as_LRXM00,as_JZBZ00,'0',as_YZCFID,'1',SQ_YJ_YW0000_YJDJH0.NEXTVAL, Vbrid00,Vghid00,'0',Vcfid00,--decode(dcxm.CJFS00,'4',null,Vcfid00), decode(dcxm.CJFS00,'4','每天一次','5','第二组起','用药带出'), a.ZLXMJC, ad_ZYGHID,Vbrxm00,Vxb0000,Vcsrq00,Vnl0000,Vkdysxm,Vysszks,to_char(Vjmsybz),'a',VTSBZBH_CF from VW_BM_ZLZD00 a,BM_YZPCMX b where A.ZLXMID=dcxm.DCXMID and b.PCID00=Vpcid00; else insert into YJ_YW0000 ( ZLXMID, SL0000,DW0000, YJKSBH, KDYS00, KDKSBH, KDRQ00,KDSJ00, LRXM00, JZBZ00, XMZT00, YZCFID, DCBZ00, YJDJH0,BRID00, ZYGHID, MZZYBZ, CFID00,BZ0000, ZLXMJC, ZYHGHH, BRXM00, XB0000, CSRQ00, NL0000,KDYSXM,YSSZKS,BWMC00,FJDJBZ,BH0000) select dcxm.DCXMID,Vsl0000,a.DW0000,Vjsxmks,Vkdys00,Vkdksbh,to_char(sysdate,'YYYYMMDD'), to_char(sysdate,'HH24:MI:SS'),as_LRXM00,as_JZBZ00,'0',as_YZCFID,'1',SQ_YJ_YW0000_YJDJH0.NEXTVAL, Vbrid00,Vghid00,'0',Vcfid00,--decode(dcxm.CJFS00,'4',null,Vcfid00), decode(dcxm.CJFS00,'4','每天一次','5','第二组起','用药带出'), a.ZLXMJC, ad_ZYGHID,Vbrxm00,Vxb0000,Vcsrq00,Vnl0000,Vkdysxm,Vysszks,to_char(Vjmsybz),'a',VTSBZBH_CF from VW_BM_ZLZD00 a where A.ZLXMID=dcxm.DCXMID; end if; end if; end loop; end loop; close CUR_MZ_DCXMGZ_ZB0000; --*******************************以上根据药品用法自动带出项目********************************-- --*******************************根据皮试类别自动带出项目********************************-- select count(YJDJH0) into counteryps from YJ_YW0000 where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and DCBZ00='2'and XMZT00 in ('2','3','4'); if counteryps=0 then--带出的项目都未收费 --删除YJ_YWMX00,YJ_YW0000表含有该处方号未收费项目； delete from YJ_YWMX00 where YJDJH0 in (select YJDJH0 from YJ_YW0000 where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and DCBZ00 in ('2') and XMZT00 in ('0','1','5','9')); delete from YJ_YW0000 where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and DCBZ00 in ('2') and XMZT00 in ('0','1','5','9'); Vcfid00:=''; open CUR_MZ_DCXMGZ_PSLBID; loop fetch CUR_MZ_DCXMGZ_PSLBID into Vlyxmid,Vzb0000,Vcfid00; exit when CUR_MZ_DCXMGZ_PSLBID%notFOUND; if CUR_MZ_DCXMGZ_PSLBID%ROWcount=1 then --20030907增加皮试带出药品 insert into YF_MZCFMX(CFID00,CFLSH0,YPNM00,YPMC00,YPGG00,LSDJ00, YPZSL0,CFDW00,ZHL000,CXBZ00,SFXS00,SFZBY0,THBZ00,BZ0000,YPDLBH) select SQ_YS_CFMX00_CFID00.NEXTVAL,a.CFLSH0,c.YPNM00,b.YPMC00,b.YPGG00,b.LSJ000/b.K2J000*b.M2J000, c.YPZSL0/b.M2J000,b.MZFYDW,b.M2J000,'Z','Y','N','0','皮试带出药品',B.LBBH00 from YF_MZCFMX a,BM_YD0000 b,XT_PSDCYP c where a.CFLSH0=as_YZCFID and c.YPNM00=b.YPNM00 and a.PSLBID=c.PSID00; end if; --处方每组总个数用于采集方式中N的值； select count(CFID00) into Vbzypgs from YS_CFMX00 where CFLSH0=as_YZCFID and PSLBID=Vlyxmid and nvl(ZB0000,CFID00)=Vzb0000 and PSLBID is not null; --**************得到项目申请总次数的初始值*************-- --取出用药时间数、取出药品内码； select PCID00,YYSJ00,YPNM00 into Vpcid00,Vyysj00,Vypnm00 from YS_CFMX00 where CFLSH0=as_YZCFID and PSLBID=Vlyxmid and ROWNUM=1 and nvl(ZB0000,CFID00)=Vzb0000 and PSLBID is not null; --**************得到项目申请总次数的初始值************-- for PSDCXM in CUR_MZ_DCXMGZ_DCXMID_PS loop --取出医技接收科室； Vjsxmks:=sf_ys_xmzxks(PSDCXM.DCXMID,Vkdksbh); --医技主表(插入)； insert into YJ_YW0000(ZLXMID,SL0000,DW0000,YJKSBH,KDYS00,KDKSBH,KDRQ00,KDSJ00, LRXM00,JZBZ00,XMZT00,YZCFID,YPNM00,DCBZ00,YJDJH0,BRID00,ZYGHID,MZZYBZ,CFID00, ZLXMJC, ZYHGHH, BRXM00, XB0000, CSRQ00, NL0000,KDYSXM,YSSZKS,FJDJBZ,BH0000 ) select C.DCXMID,DECODE(C.CJFS00,'0',1,'1',Vbzypgs,'2',Vbzypgs-1,'3',CEIL((Vbzypgs-1)/Vbzypgs),'5',1), a.DW0000,Vjsxmks,Vkdys00,Vkdksbh,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'), as_LRXM00,as_JZBZ00,'0',as_YZCFID,Vypnm00,'2',SQ_YJ_YW0000_YJDJH0.nextval,Vbrid00,Vghid00,'0',','||Vcfid00||',', a.ZLXMJC, ad_ZYGHID,Vbrxm00,Vxb0000,Vcsrq00,Vnl0000,Vkdysxm,Vysszks,'a',VTSBZBH_CF from VW_BM_ZLZD00 a,MZ_DCXMGZ c where C.LYXMID=Vlyxmid and C.DCXMID=PSDCXM.DCXMID and A.ZLXMID=C.DCXMID and C.CJFS00 not in ('4') and (c.YYID00 = '0' or c.YYID00 = Vyyid00); --取出采集方式为每方每疗程一次的医技执行次数； begin select NVL(a.ZB0000,a.CFID00) into Vzdlczb from YS_CFMX00 a,MZ_DCXMGZ b where a.PSLBID=b.LYXMID and CFLSH0=as_YZCFID and a.PSLBID=Vlyxmid and b.CJFS00 in ('4') and ROWNUM=1 and YYSJ00=(select MAX(YYSJ00) from YS_CFMX00 where PSLBID=Vlyxmid and CFLSH0=as_YZCFID) and (b.YYID00 = '0' or b.YYID00 = Vyyid00); exception when no_data_found then Vzdlczb:=null; end; if Vzb0000=Vzdlczb then begin select YYSJ00 into Vsl0000 from YS_CFMX00 where CFLSH0=as_YZCFID and NVL(ZB0000,CFID00)=Vzdlczb and ROWNUM=1; exception when no_data_found then Vsl0000:=null; end; --医技主表(插入)； if Vsl0000 is not null then insert into YJ_YW0000(ZLXMID,SL0000,DW0000,YJKSBH,KDYS00,KDKSBH,KDRQ00, KDSJ00,LRXM00,JZBZ00,XMZT00,YZCFID,DCBZ00,YJDJH0,BRID00,ZYGHID,MZZYBZ, ZLXMJC, ZYHGHH, BRXM00, XB0000, CSRQ00, NL0000,KDYSXM,YSSZKS,FJDJBZ,BH0000 ) select C.DCXMID,Vsl0000*C.SL0000,a.DW0000,Vjsxmks,Vkdys00,Vkdksbh,to_char(sysdate,'YYYYMMDD'), to_char(sysdate,'HH24:MI:SS'),as_LRXM00,as_JZBZ00,'0',as_YZCFID,'1',SQ_YJ_YW0000_YJDJH0.NEXTVAL,Vbrid00,Vghid00,'0', a.ZLXMJC, ad_ZYGHID,Vbrxm00,Vxb0000,Vcsrq00,Vnl0000,Vkdysxm,Vysszks,'a',VTSBZBH_CF from VW_BM_ZLZD00 a,MZ_DCXMGZ c where C.LYXMID=Vlyxmid and C.DCXMID=PSDCXM.DCXMID and A.ZLXMID=C.DCXMID and C.CJFS00 in ('4') and (c.YYID00 = '0' or c.YYID00 = Vyyid00); end if; end if; end loop; end loop; close CUR_MZ_DCXMGZ_PSLBID; --确保合计金额没有错误 Update YF_MZCF00 set CFZJE0=(select round(sum(round(LSDJ00*YPZSL0,2)),2) from YF_MZCFMX where CFLSH0=as_YZCFID and SFZBY0='N') where CFLSH0=as_YZCFID; end if; --*************以上根据皮试类别自动带出项目**************-- end if; --中药处方带出项目； if as_ZYCFPD='1' then --删除YJ_YWMX00表含有该处方号未收费项目； delete from YJ_YWMX00 where YJDJH0 in (select YJDJH0 from YJ_YW0000 where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and DCBZ00='1' and XMZT00 in ('0','1','5','9')); --删除YJ_YW0000表含有该处方号未收费项目； delete from YJ_YW0000 where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and DCBZ00='1' and XMZT00 in ('0','1','5','9'); open CUR_MZ_DCXMGZ_ZJYFID; loop fetch CUR_MZ_DCXMGZ_ZJYFID into Vlyxmid,Vzb0000; exit when CUR_MZ_DCXMGZ_ZJYFID%notFOUND; --处方每组总个数用于采集方式中N的值； select count(CFID00) into Vbzypgs from YS_CFMX00 where CFLSH0=as_YZCFID and ZJYFID=Vlyxmid; for ZYDCXM in CUR_MZ_DCXMGZ_DCXMID loop --取出医技接收科室； Vjsxmks:=sf_ys_xmzxks(ZYDCXM.DCXMID,Vkdksbh); insert into YJ_YW0000(ZLXMID,SL0000,DW0000,YJKSBH,KDYS00,KDKSBH,KDRQ00,KDSJ00, LRXM00,JZBZ00,XMZT00,YZCFID,DCBZ00,YJDJH0,BRID00,ZYGHID,MZZYBZ, ZLXMJC, ZYHGHH, BRXM00, XB0000, CSRQ00, NL0000,KDYSXM,YSSZKS,FJDJBZ,BH0000 ) select C.DCXMID,Vzyts00*C.SL0000*(DECODE(C.CJFS00,'0',1,'1',Vbzypgs,'2',Vbzypgs-1,'3',CEIL((Vbzypgs-1)/Vbzypgs),'5',1)), a.DW0000,Vjsxmks,Vkdys00,Vkdksbh,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'), as_LRXM00,as_JZBZ00,'0',as_YZCFID,'1',SQ_YJ_YW0000_YJDJH0.NEXTVAL,Vbrid00,Vghid00,'0', a.ZLXMJC, ad_ZYGHID,Vbrxm00,Vxb0000,Vcsrq00,Vnl0000,Vkdysxm,Vysszks,'a',VTSBZBH_CF from VW_BM_ZLZD00 a,MZ_DCXMGZ c where C.LYXMID=Vlyxmid and C.DCXMID=ZYDCXM.DCXMID and A.ZLXMID=C.DCXMID and C.CJFS00 not in ('4') and (c.YYID00 = '0' or c.YYID00 = Vyyid00); --取出采集方式为每方每疗程一次的医技执行次数； begin select NVL(a.ZB0000,999) into Vzdlczb from YS_CFMX00 a,MZ_DCXMGZ b where a.ZJYFID=b.LYXMID and CFLSH0=as_YZCFID and a.ZJYFID=Vlyxmid and b.CJFS00 in ('4') and ROWNUM=1 and (b.YYID00 = '0' or b.YYID00 = Vyyid00); exception when no_data_found then Vzdlczb:=null; end; if Vzdlczb is not null then insert into YJ_YW0000(ZLXMID,SL0000,DW0000,YJKSBH,KDYS00,KDKSBH,KDRQ00,KDSJ00, LRXM00,JZBZ00,XMZT00,YZCFID,DCBZ00,YJDJH0,BRID00,ZYGHID,MZZYBZ, ZLXMJC, ZYHGHH, BRXM00, XB0000, CSRQ00, NL0000,KDYSXM,YSSZKS,FJDJBZ,BH0000 ) select C.DCXMID,Vzyts00*C.SL0000,a.DW0000,Vjsxmks,Vkdys00,Vkdksbh,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'), as_LRXM00,as_JZBZ00,'0',as_YZCFID,'1',SQ_YJ_YW0000_YJDJH0.NEXTVAL,Vbrid00,Vghid00,'0', a.ZLXMJC, ad_ZYGHID,Vbrxm00,Vxb0000,Vcsrq00,Vnl0000,Vkdysxm,Vysszks,'a',VTSBZBH_CF from VW_BM_ZLZD00 a,MZ_DCXMGZ c where C.LYXMID=Vlyxmid and C.DCXMID=ZYDCXM.DCXMID and A.ZLXMID=C.DCXMID and C.CJFS00 in ('4') and (c.YYID00 = '0' or c.YYID00 = Vyyid00); end if; end loop; end loop; close CUR_MZ_DCXMGZ_ZJYFID; end if; --删除第二张处方单每天一次的收费方式, 全部按每组一次收取 delete from YJ_YW0000 a where ZYGHID=Vghid00 and MZZYBZ='0' and YZCFID=as_YZCFID and DCBZ00 in ('1','2') and XMZT00 in('0','1','5','9') and BZ0000='每天一次' and SL0000>0 and exists (select 1 from YJ_YW0000 where ZYGHID=Vghid00 and MZZYBZ='0' and YZCFID0); --YS_JMSYDZTZ ='Y' 如果只有一组药品，静脉滴注费1天按最高频次收(tid 3次)，如果有多组，静脉滴注费一天只收1次 -- ='N' 静脉滴注费一天固定收1次 -- ='M' 静脉滴注费一天内按最高频次收取 --处理每天一次的静脉滴注费 --2010.03.07 zhr 如果有精密输液器的话，静脉滴注费要优先按精密输液器的来收,算法做了较大调整 declare cursor c_kdrq00 is select count(distinct CFID00) ZS0000,nvl(max(SL0000),0)SL0000,KDRQ00 from YJ_YW0000 a where ZYGHID=Vghid00 and MZZYBZ='0' and YZCFID=as_YZCFID and DCBZ00 in ('1','2') and XMZT00 in('0','1','5','9') and BZ0000='每天一次' and SL0000>0 group by KDRQ00; begin for rq in c_kdrq00 loop Vzdsl00:=rq.SL0000;--每天一次的最高频次 if v_JMSYDZTZ='N' or (rq.ZS0000>1 and v_JMSYDZTZ='Y') then--处理每天一次的静滴费(包括输液器),病人可能当天还有其他的处方 Vzdsl00:=least(1,Vzdsl00); end if; --BWMC00字段作为精密输液识别 1是<>1 非精密输液 declare cursor c_cfid00 is select distinct CFID00,SL0000,BWMC00 from YJ_YW0000 a where ZYGHID=Vghid00 and MZZYBZ='0' and YZCFID=as_YZCFID and DCBZ00 in ('1','2') and XMZT00 in('0','1','5','9') and BZ0000='每天一次' and SL0000>0 and KDRQ00=rq.KDRQ00 order by BWMC00 desc; --and exists (select 1 from YS_CFMX00 b where CFLSH0=as_YZCFID and instrb(a.CFID00,','||to_char(b.CFID00)||',')>0 and instrb(','||Vjmsyyf||',',','||YFID00||',')>0); begin for cf in c_cfid00 loop Vsl0000:=least(cf.SL0000,Vzdsl00); Vzdsl00:=Vzdsl00-Vsl0000; update YJ_YW0000 a set SL0000=Vsl0000 where ZYGHID=Vghid00 and MZZYBZ='0' and YZCFID=as_YZCFID and DCBZ00 in ('1','2') and XMZT00 in('0','1','5','9') and BZ0000='每天一次' and SL0000>0 and KDRQ00=rq.KDRQ00 and CFID00=cf.CFID00 and BWMC00=cf.BWMC00; update YJ_YW0000 a set SL0000=SL0000-Vsl0000 where ZYGHID=Vghid00 and MZZYBZ='0' and YZCFID=as_YZCFID and BWMC00=cf.BWMC00 and DCBZ00 in ('1','2') and XMZT00 in('0','1','5','9') and BZ0000='第二组起' and SL0000>0 and KDRQ00=rq.KDRQ00 and CFID00=cf.CFID00 ; end loop; end; end loop; end; --###########################特殊处理结束###########################################--- if Vfbbh00=3 and Vybzxlb='1' then update YJ_YW0000 set SL0000=SL0000-1 where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and DCBZ00 in ('1','2') and XMZT00 in('0','1','5','9') and MZZYBZ='0' and ZLXMID=Vlqxmid and BZ0000='每天一次' and SL0000>0 and rownum=1; end if; --删除YJ_YW0000表数量为0的项目; delete from YJ_YW0000 where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and DCBZ00 in ('1','2') and SL0000=0 and XMZT00 in('0','1','5','9') and MZZYBZ='0'; if Vdcxmcf='Y' then --所有带出项目都拆分成数量为1的诊疗单 for YJ in CUR_YJ_YW0000 loop select SL0000 into Vsl0000 from YJ_YW0000 where YJDJH0=yj.YJDJH0; WHILE Vsl0000>0 loop Vsl0000:=Vsl0000-1; insert into YJ_YW0000(ZLXMID,SL0000,DW0000,YJKSBH,KDYS00,KDKSBH,KDRQ00,KDSJ00, LRXM00,JZBZ00,XMZT00,YZCFID,DCBZ00,YJDJH0,BRID00,ZYGHID,MZZYBZ,CFID00,BZ0000, ZLXMJC, ZYHGHH, BRXM00, XB0000, CSRQ00, NL0000,KDYSXM,YSSZKS,FJDJBZ,BH0000 ) select ZLXMID,1,DW0000,YJKSBH,KDYS00,KDKSBH,KDRQ00,KDSJ00, LRXM00,JZBZ00,XMZT00,YZCFID,DCBZ00,SQ_YJ_YW0000_YJDJH0.NEXTVAL,BRID00,ZYGHID,MZZYBZ,CFID00,BZ0000, ZLXMJC, ZYHGHH, BRXM00, XB0000, CSRQ00, NL0000,KDYSXM,YSSZKS,'a',VTSBZBH_CF from YJ_YW0000 where YJDJH0=yj.YJDJH0; end loop; --删除YJ_YWMX00表含有该处方号未收费项目； delete from YJ_YWMX00 where YJDJH0=yj.YJDJH0; --删除YJ_YW0000表含有该处方号未收费项目； delete from YJ_YW0000 where YJDJH0=yj.YJDJH0; end loop; elsif Vdcxmcf='T' then--按组进行累加 for YJ in CUR_YJ_YW0002 loop select nvl(sum(SL0000),0) into Vsl0000 from YJ_YW0000 where ZYHGHH=ad_ZYGHID AND YZCFID=as_YZCFID AND DCBZ00='1' AND ZLXMID=yj.ZLXMID and MZZYBZ='0' and YJDJH0<>yj.YJDJH0 and nvl(CFID00,0)=nvl(yj.CFID00,0); if Vsl0000>0 then Update YJ_YW0000 set SL0000=SL0000+Vsl0000 where YJDJH0=yj.YJDJH0; --删除YJ_YWMX00表含有该处方号未收费项目； delete from YJ_YWMX00 where YJDJH0 in (select YJDJH0 from YJ_YW0000 where ZYHGHH=ad_ZYGHID AND YZCFID=as_YZCFID AND DCBZ00='1' AND ZLXMID=yj.ZLXMID and MZZYBZ='0' and YJDJH0<>yj.YJDJH0 and nvl(CFID00,'0')=nvl(yj.CFID00,'0')); --删除YJ_YW0000表含有该处方号未收费项目； delete from YJ_YW0000 where ZYHGHH=ad_ZYGHID AND YZCFID=as_YZCFID AND DCBZ00='1' AND ZLXMID=yj.ZLXMID and MZZYBZ='0' and YJDJH0<>yj.YJDJH0 and nvl(CFID00,'0')=nvl(yj.CFID00,'0'); end if; end loop; elsif Vdcxmcf='N' then--按处方进行累加 for YJ in CUR_YJ_YW0001 loop --修改cfid00 scfid00:=''; --for CF in (select distinct CFID00 from YJ_YW0000 where ZYHGHH=ad_ZYGHID AND YZCFID=as_YZCFID AND DCBZ00='1' AND ZLXMID=yj.ZLXMID and MZZYBZ='0') loop for CF in (select distinct A.CFID00 from YS_CFMX00 A,MZ_DCXMGZ C WHERE C.CSLY00='0' AND C.XMLB00='1' AND C.DCXMID=yj.ZLXMID AND A.YFID00=C.LYXMID AND A.CFLSH0=as_YZCFID and (C.YYID00 = '0' or C.YYID00 = Vyyid00) ) loop scfid00:=scfid00||','||trim(CF.CFID00); end loop; scfid00:=scfid00||','; update YJ_YW0000 set CFID00=scfid00 where YJDJH0=yj.YJDJH0; select nvl(sum(SL0000),0) into Vsl0000 from YJ_YW0000 where ZYHGHH=ad_ZYGHID AND YZCFID=as_YZCFID AND DCBZ00='1' AND ZLXMID=yj.ZLXMID and MZZYBZ='0' and YJDJH0<>yj.YJDJH0; --合并记录 if Vsl0000>0 then Update YJ_YW0000 set SL0000=SL0000+Vsl0000 where YJDJH0=yj.YJDJH0; --删除YJ_YWMX00表含有该处方号未收费项目； delete from YJ_YWMX00 where YJDJH0 in (select YJDJH0 from YJ_YW0000 where ZYHGHH=ad_ZYGHID AND YZCFID=as_YZCFID AND DCBZ00='1' AND ZLXMID=yj.ZLXMID and MZZYBZ='0' and YJDJH0<>yj.YJDJH0); --删除YJ_YW0000表含有该处方号未收费项目； delete from YJ_YW0000 where ZYHGHH=ad_ZYGHID AND YZCFID=as_YZCFID AND DCBZ00='1' AND ZLXMID=yj.ZLXMID and MZZYBZ='0' and YJDJH0<>yj.YJDJH0; end if; end loop; end if; --给自动带出的诊疗单估价,插入医技明细表 for YJ in CUR_YJ_YW0000 loop SP_EXECUTE_SQL('insert into YJ_YWMX00 (ID0000,YJDJH0,ZLXMID,XMMC00,JGDW00,CKZ000,SL0000)'|| ' select SQ_YJ_YWMX00_ID0000.nextval,'||to_char(YJ.YJDJH0)||',ZLXMID,ZLXMJC,JGDW00,CKZ000,'||to_char(YJ.SL0000)|| ' from BM_ZLZD00 where ZLXMID in ('||YJ.ZLXMID||')'); SF_YJ_YWJJ_NOCOMMIT(yj.YJDJH0,1,yj.KDYS00,yj.KDKSBH,'N',Vzxcgbz,Vtsxx00,Vxxxx00); --医保病人，自动带出的项目变更成需要审核状态9 if Vfbbh00=3 and (Vybzxlb<>'1'or (Vybzxlb='1' and YJ.ZLXMID=Vlqxmid) or (Vybzxlb='1' and nvl(Vczyds0,0)>0) ) and Vybdcsfxysq='Y' then update YJ_YW0000 a set XMZT00='9' where YJDJH0=yj.YJDJH0 and DCBZ00 in ('1','2') and YZCFID=as_YZCFID and XMZT00 in ('0','1'); end if; end loop; --偶尔输液类加药卡无法打印 发现yj_yw0000的 yzcfid=null cfid00 is not null ,现在加保护行赋值。 update YJ_YW0000 a set YZCFID=(select b.CFLSH0 from YS_CFXXB0 b,YS_CFMX00 c where b.GHH000=ad_ZYGHID and b.CFLSH0=c.CFLSH0 and INSTR(a.CFID00,c.CFID00)>0 and rownum=1) where ZYHGHH=ad_ZYGHID and YZCFID IS null and CFID00 IS NOT NULL; exception when E_ERROR0 then RAISE_APPLICATION_ERROR(-20010, Verrmsg||'！**'); when others then Raise_Application_Error(-20001,dbms_utility.format_error_backtrace||'@@@'|| Verrmsg||NVL(SQLERRM, '原因不明出错！**')); end;