prompt 04、门诊病人票据使用情况表触发器 TR_SF_SF_PJSYQK_INSERT -- 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) 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_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); --异常声明 E_PJXHCZ EXCEPTION; --票据序号重复 E_PJWLY0 EXCEPTION; --票据未邻用 ls_SFQYYBZZJS char(1); --是否启用医保自助结算 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 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; 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'; --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; IF ls_VALUE1 = 'Y' and ls_value2='N' and ls_VALUE3='Y' and trim(:NEW.PJXH00)='0' and (ls_SFQYYBZZJS='N') 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; IF ls_VALUE0 = '1' THEN --若票据由系统自动生成 select NVL(min(PJZZH0),'0'),NVL(min(PJDQH0),'0'),min(PJLYID) into ls_PJZZH0,ls_PJDQH0,ls_PJLYID from BM_PJLYQK where PJLXBM = :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 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 PLBH00=:NEW.PLBH00 and PJSYR0=:NEW.CZY000 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; -- raise E_PJXHCZ; END LOOP; Update BM_PJLYQK set PJDQH0 =lpad(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<>1) 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; --取收费员当时的操作科室 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; :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; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%