CREATE OR REPLACE TRIGGER TR_SF_SF_PJSYQK_INSERT
 BEFORE 
 INSERT
 ON SD_SF.SF_PJSYQK
 REFERENCING OLD AS OLD NEW AS NEW
 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;  --操作员科室
        V_process   XT_XTRZ00.JCID00%TYPE;
        ls_SFQYYBZZJS varchar2(100);              --是否启用医保自助结算
        LSCOUNT0  number(10);
--异常声明
	E_PJXHCZ    EXCEPTION;	            --票据序号重复
	E_PJWLY0    EXCEPTION;	            --票据未邻用
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- zhr        2009.10.25   改变取当前登录科室的方法
begin
   Select  Value0  into  ls_VALUE0  from   XT_XTCS00  where  NAME00='SF_PJHSC0';

   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;
  
  begin
      select value0 into ls_value2 from xt_xtcs00 where name00='MZSF_SFTSDYZSFP';
  exception
     when others then
        ls_value2:='N';
  end;
  LSCOUNT0:=0;
 --增加判断自助票据--begin--- 
  select count(1) into LSCOUNT0  from xt_xtcs00 where instrb(','||trim(value0)||',',','||to_char(:NEW.CZY000)||',')>0 and name00='SST_ZZJSFY';
   if LSCOUNT0>0 then 
     ls_SFQYYBZZJS:='Y';
   else
     ls_SFQYYBZZJS:='N';
   end if;
 --增加判断自助票据--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;
   IF  ls_VALUE1 = 'Y' and ls_value2='N' 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(trim(min(PJZZH0)),'0'),NVL(trim(min(PJDQH0)),'0'),trim(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
           Update  BM_PJLYQK    set   PJDQH0 = 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) <> '-'))  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(trim(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;
   --取收费员当时的操作科室
   select nvl(max(BMBH00),0) into ls_CZYKS0 from BM_YGBM00 where YGBH00 = :NEW.CZY000;
   --取收费员当前登录的科室
   Select userenv('SESSIONID') into V_process  from dual where rownum=1;
   Select nvl(max(DLKS00),ls_CZYKS0) into ls_CZYKS0 From XT_XTRZ00 A Where JCID00=V_process And
          LSH000=(select max(LSH000) from XT_XTRZ00 where JCID00=A.JCID00);
   :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(-20001,trim(substr('非法操作,您未分配有效使用的票据号'||'!*',1,100)));
          ROLLBACK;
     WHEN OTHERS THEN
	  RAISE_APPLICATION_ERROR(-20910,substr(nvl(sqlerrm,'出错原因不详')||'PJXH00:'||:NEW.PJXH00||'ls_PJDQH0:'||ls_PJDQH0||'，请记录此信息并和系统管理员联系！'||'!*',1,150));
	  ROLLBACK;
end;
/