prompt 04、门诊病人票据使用情况表触发器 TR_SF_SF_PJSYQK_INSERT create or replace trigger TR_SF_SF_PJSYQK_INSERT Before insert on SF_PJSYQK FOR EACH ROW WHEN (NEW.PLBH00 <> 9 ) declare ls_VALUE0 XT_XTCS00.VALUE0%TYPE; --查看系统所设置的系统参数的值 ls_VALUE1 XT_XTCS00.VALUE0%TYPE; --若门诊票据未领用的,不能使用系统 ls_VALUE2 XT_XTCS00.VALUE0%TYPE; ls_YJJPJ0 XT_XTCS00.VALUE0%TYPE; ls_PLBHCS XT_XTCS00.VALUE0%TYPE; ls_PLBH00 BM_PJLYQK.PJLXBM%TYPE; ls_PJZZH0 BM_PJLYQK.PJZZH0%TYPE; --票据终止号 ls_PJDQH0 BM_PJLYQK.PJDQH0%TYPE; --当前使用票据号 ls_PJXH00 BM_PJLYQK.PJDQH0%TYPE; --当前使用票据号 ls_PJLYID BM_PJLYQK.PJLYID%TYPE; ls_LBBH00 SF_BRXXB0.LBBH00%TYPE; --现金挂号/预交金挂号 ls_SF_CKBRSYGHD XT_XTCS00.VALUE0%TYPE; --有卡病人挂号时是否使用领用挂号单 ls_CZYKS0 SF_BRJFB0.CZYKS0%TYPE; --操作员科室 ls_CZYKS1 SF_BRJFB0.CZYKS0%TYPE; --操作员所属科室 ls_MAXLSH XT_XTRZ00.LSH000%TYPE; --系统日志流水号 ls_FBBH00 BM_BRFBB0.FBBH00%TYPE; ls_YYID00 XT_YYXX00.YYID00%TYPE; V_DQXH00 Number(12); V_XHZ000 Number(1); LSCOUNT0 NUMBER(2); ls_VALUE3 CHAR(1); v_ENDPJ CHAR(1); SF_SFDYJSD varchar2(20); --异常声明 E_PJXHCZ EXCEPTION; --票据序号重复 E_PJWLY0 EXCEPTION; --票据未邻用 ls_SFQYYBZZJS char(1); --是否启用医保自助结算 -- MODIFICATION HISTORY -- Person Date Comments -- jlg 2008.04.10 老年医院限制医疗救助病人不能缴退款 MZSF-20080312-002 -- jlg 2008.05.12 修改yyid00赋值类型错误引起的问题 -- yangy 2010.03.22 医保自助结算时允许pjxh00为0 -- qks 2010.07.08 电子钱包可以不使用票据 --zhangyc 2010.08.30 判断当前作废发票序号是否存在 --zhangyc 2010.10.22 票据跨号作废，恢复条件限制E_PJXHCZ（门诊发票除外） MZSF-20101022-001 --zhangyc 2010.11.05 修改医院id取值(select SF_SF_GETYYID00() YYID00 from dual) --zhangyc 2011.03.21 增加参数SF_YJJPJ0(门诊预交金退款票据是否取预交金缴款票据序号) --zhangyc 2012.08.10 参数SF_PJWLYBNSY=Y 时自助机结算无打印发票导致提示未发票票据号 by MZSF-20120809-003 -- jinfl 2014.03.31 当提前作废票据号为终止号的发票时,发票打印到最后一张时当前号会继续往下走 MZSF-20140324-001 -- zhangyc 2014.04.28 参数SST_ZZJSFY改为表SST_YGBHDY判断 by MZSF-20140422-003 --zhangyc 2014.05.05 把条件 (V_DQXH00=to_number(ls_PJZZH0)+1)增加条件(lscount0<>0) by MZSF-20140505-001 --zhangyc 2014.09.15 修正自助机交费插入SF_PJSYQK中CZYKS0为0的bug by MZSF-20140912-001 --zhangyc 2014.11.07 修正SF_PJWLYBNSY=Y and MZSF_SFTSDYZSFP=N时 打印结算单提示未领用票据 by MZSF-20141107-002 -- zhangyc 2015.10.09 操作员姓名为"趣医院" 不限制发票号 by MZSF-20151009-001 --zhangyc 2015.10.30 SF_PJSYQK.PHLYXZ=N 不限制票据领用功能 by MZSF-20151030-001 --qiulf 2020.06.18 博思电子发票不使用院内票据序号PJXH00 by MZSF-20200618-005 begin begin Select Value0 into ls_VALUE0 from XT_XTCS00 where NAME00='SF_PJHSC0'; exception WHEN OTHERS THEN ls_VALUE0:='0'; end; begin Select Value0 into ls_SF_CKBRSYGHD from XT_XTCS00 where NAME00='SF_CKBRSYGHD'; exception WHEN OTHERS THEN ls_SF_CKBRSYGHD:='Y'; END; BEGIN Select Value0 into ls_VALUE1 from XT_XTCS00 where NAME00='SF_PJWLYBNSY'; exception WHEN OTHERS THEN ls_VALUE1:='N'; END; if :new.dzqbje>0 then --电子钱包可以不使用票据 ls_VALUE1:='N'; end if; begin select value0 into ls_value2 from xt_xtcs00 where name00='MZSF_SFTSDYZSFP'; exception when others then ls_value2:='N'; end; begin select trim(value0) into ls_YJJPJ0 from xt_xtcs00 where name00 = 'SF_YJJPJ0'; exception when others then ls_YJJPJ0:='N'; end; begin --select yyid00 into ls_yyid00 from xt_yyxx00 where rownum = 1; select SF_SF_GETYYID00() YYID00 into ls_yyid00 from dual; exception when others then ls_yyid00:='0'; end; v_ENDPJ := 'N'; ls_VALUE3 :='Y'; if ls_yyid00 = '220035' then --老年医院医疗救助病人不打印发票 begin select fbbh00 into ls_fbbh00 from bm_brxxb0 where brid00 = :new.brid00; exception when others then ls_fbbh00:=1; end; if ls_fbbh00 = 22 then ls_VALUE3 :='N'; end if; end if ; --select nvl(trim(max(value0)),'N') into ls_SFQYYBZZJS from xt_xtcs00 where name00='SF_SFQYYBZZJS'; --自助机所对应的收费员无票据领用（自助机对应的收费员SST_YGBHDY） SF_PJWLYBNSY=Y 导致无法正常结算 by MZSF-20120809-003 ---begin--- -- select count(1) into LSCOUNT0 from xt_xtcs00 where instrb(','||trim(value0)||',',','||to_char(:NEW.CZY000)||',')>0 and name00='SST_ZZJSFY'; select count(1) into LSCOUNT0 from SST_YGBHDY where YGBH00=:NEW.CZY000; --by MZSF-20140422-003 if nvl(LSCOUNT0,0)>0 then ls_SFQYYBZZJS:='Y'; else ls_SFQYYBZZJS:='N'; end if; --自助机所对应的收费员无票据领用 SF_PJWLYBNSY=Y 导致无法正常结算 by MZSF-20120809-003 ---end--- --IF ls_VALUE1 = 'Y' THEN -- if :NEW.PJXH00 = '0' and ls_SF_CKBRSYGHD='Y' AND :NEW.PLBH00 <> 2 and :NEW.PLBH00 <> 5 then raise E_PJWLY0; end if; -- END IF; --SF_SFDYJSD N为只打印发票，Y为只打印结算单，M为都不打印 --增加判断只打印结算结算在结算过程SP_SF_BRFY00_JS0000插入的票据序号为0 SF_SFDYJSD:='N'; begin select trim(VALUE0) into SF_SFDYJSD from XT_YHCS00 where name00='SF_SFDYJSD' and YGBH00 =:NEW.CZY000; exception when others then SF_SFDYJSD:='N'; end; -- by MZSF-20151030-001 增加判断:NEW.PHLYXZ='N' if (:NEW.CZYXM0='趣医院') or (:NEW.PHLYXZ='N') then LS_SFQYYBZZJS:='Y'; end if; IF ls_VALUE1 = 'Y' and ls_value2='N' and ls_VALUE3='Y' and trim(:NEW.PJXH00)='0' and (ls_SFQYYBZZJS='N') and (SF_SFDYJSD not in ('Y')) and :NEW.WBPJLY is null then if :NEW.PLBH00='0' then--挂号单 select LBBH00 into ls_LBBH00 from sf_brxxb0 where pjh000=:new.pjh000; if ls_LBBH00=1 and ls_SF_CKBRSYGHD='N' then--如果不需要打印挂号单,就不进行判定 ls_SF_CKBRSYGHD:=ls_SF_CKBRSYGHD; else raise E_PJWLY0; end if; elsif :new.PLBH00<>'5' then raise E_PJWLY0; end if; END IF; if :NEW.FZPJH0 = 0 then :NEW.FZPJH0 := :NEW.PJH000; end if; if :NEW.XSRQ00 is null then :NEW.XSRQ00 := :NEW.DYRQ00; end if; ls_PLBH00:=:NEW.PLBH00; IF ls_VALUE0 = '1' THEN --若票据由系统自动生成 IF (ls_YJJPJ0='Y') and (ls_PLBH00=2) then ---退预交金 ls_PLBH00:=7; end if; select NVL(min(PJZZH0),'0'),NVL(min(PJDQH0),'0'),min(PJLYID) into ls_PJZZH0,ls_PJDQH0,ls_PJLYID from BM_PJLYQK where PJLXBM =ls_PLBH00 --:NEW.PLBH00 and PJSYR0 = :NEW.CZY000 and SYBZ00 = 'Y'; --票据使用序号是有效的 BEGIN select trim(:NEW.PJXH00) into ls_PJXH00 from dual; exception WHEN OTHERS THEN ls_PJXH00:='0'; END; if :NEW.WBPJLY is null and ls_PJXH00=ls_PJDQH0 then ---循环判断是否有作废发票 V_DQXH00:=to_number(ls_PJDQH0)+1; V_XHZ000:=0; while V_XHZ000=0 loop begin select count(*) into lscount0 from SF_ZFFPXH where PJSYR0=:new.CZY000 and PLBH00=ls_PLBH00--:NEW.PLBH00 and PJLYID=ls_PJLYID and PJXH00=trim(lpad(to_char(V_DQXH00),length(trim(ls_PJDQH0)),'0')); exception when others then LSCOUNT0:=0; end; V_DQXH00:=V_DQXH00+1; IF LSCOUNT0=0 THEN V_XHZ000:=1; end if; if (V_DQXH00=to_number(ls_PJZZH0)+1) and (lscount0<>0) then -- by MZSF-20140505-001 增加条件 (lscount0<>0) V_XHZ000:=1; v_ENDPJ:='Y'; end if; -- raise E_PJXHCZ; END LOOP; Update BM_PJLYQK set PJDQH0 =lpad(V_DQXH00-1,greatest(length(trim(pjdqh0)),length(nvl(pjdqh0,0)+1)),'0') where PJLXBM =ls_PLBH00 ---:NEW.PLBH00 and PJSYR0 = :NEW.CZY000 and SYBZ00 = 'Y'; --票据使用序号是有效的 :new.PJLYID:=ls_PJLYID; else if ((trim(:NEW.PJXH00) <> '0') and (substr(:NEW.PJXH00,1,1) <> '-')) and (:new.plbh00<>1) then raise E_PJXHCZ; end if; end if; --若用户的票据号已用完，将其标志改为无效 IF ((ls_PJZZH0 = ls_PJDQH0) or (v_ENDPJ='Y'))and ((trim(:NEW.PJXH00) <> '0') and (SUBSTR(:NEW.PJXH00,1,1) <> '-')) THEN Update BM_PJLYQK set PJDQH0 = ls_PJZZH0, PJTZRQ = to_char(sysdate,'yyyymmdd'), SYBZ00 = 'N' where PJLXBM = ls_PLBH00--:NEW.PLBH00 and PJSYR0 = :NEW.CZY000 and SYBZ00 = 'Y'; --票据使用序号是有效的 --同时再查看是否有可用的票据,将其启用 select NVL(min(PJLYID),'0') into ls_PJLYID from BM_PJLYQK where PJTZRQ is null and PJDQH0 = PJQSH0 and PJSYR0 = :NEW.CZY000 and PJLXBM =ls_PLBH00; --:NEW.PLBH00; Update BM_PJLYQK set SYBZ00 = 'Y' where PJLYID = ls_PJLYID; END IF; END IF; --取收费员当时的操作科室 begin select BMBH00 into ls_CZYKS1 from BM_YGBM00 where YGBH00 = :NEW.CZY000; exception WHEN NO_DATA_FOUND THEN ls_CZYKS1 :=0; end; begin --取收费员当前登录的科室 select nvl(max(lsh000),0) into ls_MAXLSH FROM XT_XTRZ00 where DLRQ00 >=to_char(sysdate-2,'yyyymmdd') and YGBH00 = :NEW.CZY000 AND XTDM00='4' AND TCRQ00 IS NULL; select NVL(MAX(DLKS00),0) into ls_CZYKS0 from XT_XTRZ00 WHERE LSH000 = ls_MAXLSH; exception WHEN OTHERS THEN ls_CZYKS0 := ls_CZYKS1; end; if nvl(ls_CZYKS0,0)=0 then ls_CZYKS0:=ls_CZYKS1; end if; -- by MZSF-20140912-001 :NEW.CZYKS0:=ls_CZYKS0; exception when e_pjxhcz then RAISE_APPLICATION_ERROR(-20908,'非法操作,您所使用的票据序号'||:new.pjxh00||'不等于系统所设定的当前号'||ls_PJDQH0||'，请稍侯再试！'||'!*'); rollback; when e_pjwly0 then RAISE_APPLICATION_ERROR(-20909,'非法操作,您未分配有效使用的票据号,请与财务科联系,先分配相关的票据号后再进行本操作！'||'!*'); rollback; when others then RAISE_APPLICATION_ERROR(-20910,substr(nvl(sqlerrm,'出错原因不详')||'PJXH00:'||:new.pjxh00||'ls_PJDQH0:'||ls_PJDQH0||'，请记录此信息并和系统管理员联系！'||'!*',1,150)); rollback; end; / show error; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%