CREATE OR REPLACE TRIGGER TR_ZY_ZY_PJSYQK_INSERT BEFORE INSERT ON SD_ZY.ZY_PJSYQK REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW WHEN (NEW.PLBH00 <> 11 ) declare ls_VALUE0 XT_XTCS00.VALUE0%TYPE; --查看系统所设置的系统参数的值 ls_VALUE1 XT_XTCS00.VALUE0%TYPE; --若住院票据未领用的,不能使用系统 ls_VALUE2 XT_XTCS00.VALUE0%TYPE; ls_PJZZH0 BM_PJLYQK.PJZZH0%TYPE; --票据终止号 ls_PJDQH0 BM_PJLYQK.PJDQH0%TYPE; --当前使用票据号 ls_PJLYID BM_PJLYQK.PJLYID%TYPE; LS_CZYKS0 ZY_PJSYQK.CZYKS0%TYPE; --操作员科室 LS_HSYKS0 ZY_PJSYQK.HSYKS0%TYPE; --操作员科室 ls_CZYKS1 ZY_PJSYQK.CZYKS0%TYPE; --操作员所属科室 ls_MAXLSH XT_XTRZ00.LSH000%TYPE; --系统日志流水号 V_DQXH00 BM_PJLYQK.PJDQH0%TYPE; --当前使用票据号 V_XHZ000 Number(1); LSCOUNT0 NUMBER(2); ls_SFQYYBZZJS varchar2(10); --是否启用医保自助结算 --异常声明 E_PJXHCZ EXCEPTION; --票据序号重复 E_PJWLY0 EXCEPTION; --票据未邻用 LV_JZJE00 number(12,4); begin -- MODIFICATION HISTORY -- Person Date Comments -- xzw 2008.07.30 10G修改，使程序能够在10G库上正常操作 -- xzw 2008.08.27 把10G修改的恢复到修改之前 -- xzw 2009.10.30 10G修改，使程序能够在10G库上正常操作 --zhangyc 2010.05.20 当回收员不为空值是插入回收员科室 --zhangyc 2010.08.30 判断当前作废发票序号是否存在 --zhangyc 2010.10.25 票据跨号作废，恢复条件限制E_PJXHCZ（住院发票除外） --laijg 2011.07.15 原来V_DQXH00数据类型精度不够，现和ls_PJDQH0保持一致 --zhangyc 2014.03.13 修正自助机现金充值提示未领用票据（ZY_PJWLYBNSY=Y， ZY_SFTSDYZSFP=N 时PJXH00=0 会报错） by ZYSF-20140220-003 --zhangyc 2014.05.04 参数SST_ZZJSFY改为表SST_YGBHDY判断 by ZYSF-20140423-001 --zhangyc 2014.10.29 增加ZY_SFTSDYZSFP=Y以及记账金额判断 票据序号0错误判断 by ZYSF-20141020-002 --zhangyc 2016.11.04 重新整理写入CZYKS0，HSYKS0 脚本 by ZYSF-20161109-004 -- qks 2017.07.14 恢复需求ZYSF-20161109-004修改之前的写法,修改了取登录科室为0情况下,才重新取操作员所在科室; for ZYSF-20170714-001 --zhangyc 2018.03.29 增加住院冲正功能 by ZYSF-20180329-006 Select Value0 into ls_VALUE0 from XT_XTCS00 where NAME00='ZY_PJHSC0'; BEGIN Select Value0 into ls_VALUE1 from XT_XTCS00 where NAME00='ZY_PJWLYBNSY'; exception WHEN OTHERS THEN ls_VALUE1:='N'; END; begin Select Value0 into ls_VALUE2 from XT_XTCS00 where NAME00='ZY_SFTSDYZSFP'; exception WHEN OTHERS THEN ls_value2:='N'; end; /* begin select count(1) into LSCOUNT0 from xt_xtcs00 where instrb(','||trim(value0)||',',','||to_char(:NEW.CZY000)||',')>0 and name00='SST_ZZJSFY'; exception WHEN OTHERS THEN LSCOUNT0:=0; end; if LSCOUNT0>0 then ls_SFQYYBZZJS:='Y'; else ls_SFQYYBZZJS:='N'; end if; */ select count(1) into LSCOUNT0 from SST_YGBHDY where YGBH00=:NEW.CZY000; --by ZYSF-20140423-001 if nvl(LSCOUNT0,0)>0 then ls_SFQYYBZZJS:='Y'; else ls_SFQYYBZZJS:='N'; end if; IF (ls_VALUE1 = 'Y')and (ls_VALUE2='N') and (ls_SFQYYBZZJS='N') and (nvl(:NEW.PHLYXZ,'Y') not in ('N')) then if :NEW.PJXH00 = '0' and :NEW.PLBH00 <> 4 and :NEW.PLBH00 <> 6 then raise E_PJWLY0; end if; END IF; --by ZYSF-20141020-002 --begin-- --ZY_SFTSDYZSFP=Y 时有领用票据但是偶尔出现PJXH00=0 的数据 --顾增加判断没有记账金额时不允许票据序号为0数据出现 if (ls_VALUE1 = 'Y') and (ls_VALUE2='Y') and (nvl(:NEW.PHLYXZ,'Y') not in ('N') and (ls_SFQYYBZZJS='N') ) and (:NEW.PJXH00 = '0') and (:NEW.PLBH00 not in (4,6)) then BEGIN SELECT JZJE00 INTO LV_JZJE00 FROM ZY_JZB000 B WHERE B.PJH000=:NEW.FZPJH0; EXCEPTION WHEN OTHERS THEN LV_JZJE00:=0; END; if LV_JZJE00=0 then raise E_PJWLY0; end if; end if; --by ZYSF-20141020-002 --begin-- if :NEW.FZPJH0=0 then :NEW.FZPJH0 := :NEW.PJH000; end if; IF ls_VALUE0 = '1' THEN --若票据由系统自动生成 select min(trim(PJZZH0)),min(trim(PJDQH0)),min(trim(PJLYID)) into ls_PJZZH0,ls_PJDQH0,ls_PJLYID from BM_PJLYQK where PJLXBM = :NEW.PLBH00 and PJSYR0 = :NEW.CZY000 and SYBZ00 = 'Y'; --票据使用序号是有效的 if :NEW.PJXH00 = ls_PJDQH0 then ---循环判断是否有作废发票 V_DQXH00:=to_char(TO_Number(ls_PJDQH0)+1); V_XHZ000:=0; while V_XHZ000=0 loop BEGIN select COUNT(*) INTO LSCOUNT0 from SF_ZFFPXH where PLBH00=:NEW.PLBH00 and PJSYR0=:NEW.CZY000 and PJLYID=ls_PJLYID and PJXH00=trim(lpad(V_DQXH00,LENGTH(TRIM(ls_PJDQH0)),'0')); exception WHEN OTHERS THEN LSCOUNT0:=0; END; V_DQXH00:=to_char(to_number(V_DQXH00)+1); IF LSCOUNT0=0 THEN V_XHZ000:=1; END IF; -- raise E_PJXHCZ; END LOOP; Update BM_PJLYQK set PJDQH0 =lpad(to_number(V_DQXH00)-1,greatest(length(trim(pjdqh0)),length(nvl(pjdqh0,0)+1)),'0') --lpad(NVL(PJDQH0,0)+1,greatest(length(trim(pjdqh0)),length(nvl(pjdqh0,0)+1)),'0') --lpad(NVL(PJDQH0,0)+1,length(trim(pjdqh0)),'0') where PJLXBM = :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<>3) and (nvl(:NEW.PHLYXZ,'Y') not in ('N')) then raise E_PJXHCZ; end if; end if; --若用户的票据号已用完，将其标志改为无效 IF (ls_PJZZH0 = ls_PJDQH0)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 = :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 PJLXBM = :NEW.PLBH00 and PJSYR0 = :NEW.CZY000; Update BM_PJLYQK set SYBZ00 = 'Y' where PJLYID = ls_PJLYID; END IF; END IF; --插入住院ID IF :NEW.PLBH00 IN ('3','10') THEN BEGIN SELECT ZYID00 INTO :NEW.ZYID00 FROM ZY_JZB000 B WHERE B.PJH000=:NEW.FZPJH0; EXCEPTION WHEN OTHERS THEN ls_VALUE0:=ls_VALUE0; END; END IF; --取收费员当时的操作科室 if nvl(:NEW.CZYKS0,0)=0 then 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='5' 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 ls_CZYKS0 = 0 then :NEW.CZYKS0 := ls_CZYKS1; else :NEW.CZYKS0 := ls_CZYKS0; end if; --if :NEW.HSCZY0 is not null then -- :NEW.HSYKS0:=ls_CZYKS0; --end if; end if; --修改回收操作员科室 if (nvl(:NEW.HSYKS0,0)=0) and (nvl(:NEW.HSCZY0,0)<>0) then begin select BMBH00 into LS_CZYKS1 from BM_YGBM00 where YGBH00=:NEW.HSCZY0; exception WHEN OTHERS 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.HSCZY0 AND XTDM00='5' 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 LS_CZYKS0 = 0 then :NEW.HSYKS0 := LS_CZYKS1; else :NEW.HSYKS0 := LS_CZYKS0; end if; end if; /* --修改操作员科室 if nvl(:NEW.CZYKS0,0)=0 then begin select BMBH00 into LS_CZYKS0 from BM_YGBM00 where YGBH00 = :new.CZY000; exception when no_data_found then LS_CZYKS0 :=0; end; if nvl(LS_CZYKS0,0)=0 then 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='5' 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 := 0; end; end if; :new.CZYKS0:=nvl(LS_CZYKS0,0); end if; --修改回收操作员科室 if (nvl(:NEW.HSYKS0,0)=0) and (nvl(:NEW.HSCZY0,0)<>0) then begin select BMBH00 into LS_HSYKS0 from BM_YGBM00 where YGBH00 = :NEW.HSCZY0; exception when no_data_found then LS_HSYKS0 :=0; end; if nvl(LS_HSYKS0,0)=0 then 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='5' AND TCRQ00 IS NULL; select NVL(MAX(DLKS00),0) into LS_HSYKS0 from XT_XTRZ00 WHERE LSH000 = LS_MAXLSH; exception when others then LS_HSYKS0 := 0; end; end if; :NEW.HSYKS0:=nvl(LS_HSYKS0,0); end if; */ 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,'出错原因不详'),1,40)||'，请记录此信息并和系统管理员联系！'||'!*'); ROLLBACK; end; --/ --show error; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% /