pacbody_regdateclass.sql

图书管理系统

      o_retCode                    OUT  VARCHAR2,
      o_err_msg                    OUT  VARCHAR2,
      o_dateclass_ref_detail3            OUT  ref_detail
      )

   IS
     BEGIN  o_retcode := '0';
     OPEN  o_dateclass_ref_detail3  FOR
         SELECT a.ruleid,a.rulecode,a.rulename,a.describes,a.startdate,a.enddate,a.remark1,a.state1,b.name,a.maker1,c.name,a.last_editor_time,a.last_check_time
           FROM OPDATEClASSRULE a join ctp_user b on b.ID=a.maker1 left join ctp_user c on c.ID=a.maker2
		   WHERE a.ruleid=i_ruleid
       ;
          EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         o_retcode := '1';
         RETURN;
      WHEN OTHERS
      THEN
         o_retcode := '-1';
         RETURN;

  END PROC_OMPS_QUYDETAILDATECLASS1;

  PROCEDURE PROC_OMPS_QUYDETAILDATECLASS2(
      i_ruleid                    IN   VARCHAR2,

   -- i_info                       IN   VARCHAR2,

      o_retCode                    OUT  VARCHAR2,
      o_err_msg                    OUT  VARCHAR2,
      o_dateclass_ref_detail4            OUT  ref_detail,
       o_dateclass_ref_detail5            OUT  ref_detail,
        o_dateclass_ref_detail6            OUT  ref_detail
      )

   IS
     BEGIN  o_retcode := '0';
     OPEN  o_dateclass_ref_detail4  FOR
         SELECT  ID,opdate, state
           FROM OPDATEClASS
		   WHERE RULEID=i_ruleid
        ORDER BY  opdate  asc;
        
        OPEN  o_dateclass_ref_detail5  FOR
         SELECT  opdate, state
           FROM OPDATEClASS
		   WHERE RULEID=i_ruleid and state='1'
        ORDER BY  opdate  asc;
        
        OPEN  o_dateclass_ref_detail6  FOR
         SELECT  opdate, state
           FROM OPDATEClASS
		   WHERE RULEID=i_ruleid and state='3'
        ORDER BY  opdate  asc;
          EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         o_retcode := '1';
         RETURN;
      WHEN OTHERS
      THEN
         o_retcode := '-1';
         RETURN;

  END PROC_OMPS_QUYDETAILDATECLASS2;
  PROCEDURE PROC_OMPS_QUYDETAILDATECLASS3(
      i_ruleid                    IN   VARCHAR2,

   -- i_info                       IN   VARCHAR2,

      o_retCode                    OUT  VARCHAR2,
      o_err_msg                    OUT  VARCHAR2,
      o_dateclass_ref_detail4            OUT  ref_detail
     
      )

   IS
     BEGIN  o_retcode := '0';
     OPEN  o_dateclass_ref_detail4  FOR
         SELECT  ID,opdate, state
           FROM OPDATEClASS
		   WHERE RULEID=i_ruleid
        ORDER BY  opdate  asc;
          EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         o_retcode := '1';
         RETURN;
      WHEN OTHERS
      THEN
         o_retcode := '-1';
         RETURN;

  END PROC_OMPS_QUYDETAILDATECLASS3;

  PROCEDURE   PROC_OMPS_DELDETAILDATE1(
      i_dateid                    IN   VARCHAR2,
      i_ruleid                    IN   VARCHAR2,
   -- i_info                       IN   VARCHAR2,

      o_retCode                    OUT  VARCHAR2,
      o_err_msg                    OUT  VARCHAR2
      )

   IS countdate      number;
     BEGIN  o_retcode := '0';
          delete
           FROM OPDATEClASS
          WHERE (id = i_dateid) ;

          select count(*) into countdate
           from OPDATEClASS
          where (ruleid=i_ruleid);
          if(countdate=0)
          then
           delete
           FROM OPDATEClASSRULE
          WHERE (ruleid = i_ruleid);
          end if;

     EXCEPTION
      WHEN OTHERS
      THEN
         o_retcode := '-1';
         RETURN;

  END PROC_OMPS_DELDETAILDATE1;

  PROCEDURE PROC_OMPS_QUYCHECKDATECLASS1(
      i_rulename                   IN   VARCHAR2,
      i_rulecode                   IN   VARCHAR2,
      i_startdate                  IN   VARCHAR2,
      i_maker                      IN   VARCHAR2,
      i_state1                      IN   VARCHAR2,
      i_state2                      IN   VARCHAR2,
   -- i_info                       IN   VARCHAR2,

      o_retCode                    OUT  VARCHAR2,
      o_err_msg                    OUT  VARCHAR2,
      o_checkdateclass_ref_detail            OUT  ref_detail
      )

   IS
     BEGIN  o_retcode := '0';

   if(i_maker='所有人')
    then

      OPEN  o_checkdateclass_ref_detail  FOR
         SELECT distinct a.ruleid,a.rulecode,a.rulename,
           a.startdate,a.enddate,b.name,a.state1,a.maker1
           FROM OPDATEClASSRULE a  join ctp_user b on a.maker1=b.ID  left join OPDATECLASS c
           ON c.RULEID=a.ruleid
		   WHERE
           ((a.state1 like '%'||i_state1||'%' ) and(a.state1<>'2' )) and (upper(a.rulename) like '%'||upper(i_rulename)||'%') AND
           (upper(a.rulecode) like '%'||upper(i_rulecode)||'%')   AND
           (a.startdate like '%'||i_startdate||'%')

        ORDER BY  a.rulecode  desc;
else
      OPEN  o_checkdateclass_ref_detail  FOR
         SELECT  distinct a.ruleid,a.rulecode,a.rulename,a.startdate,a.enddate,b.name,a.state1,a.maker1
           FROM OPDATEClASSRULE a join ctp_user b on b.ID=a.maker1 left join OPDATECLASS c
           ON c.RULEID=a.ruleid
		   WHERE ((a.state1 like '%'||i_state1||'%' ) and(a.state1<>'2' ))  and (upper(a.rulename) like '%'||upper(i_rulename)||'%') AND
           (upper(a.rulecode) like '%'||upper(i_rulecode)||'%')   AND
           (a.startdate like '%'||i_startdate||'%') AND
           (a.maker1 like '%'||i_maker||'%' )
        ORDER BY  a.rulecode  desc
          ;
          end if;


     EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         o_retcode := '1';
         RETURN;
      WHEN OTHERS
      THEN
         o_retcode := '-1';
         RETURN;

  END PROC_OMPS_QUYCHECKDATECLASS1;

  

  
  PROCEDURE   PROC_OMPS_WAITDELDETAILDATE(
      i_dateid                    IN   VARCHAR2,
      i_ruleid                    IN   VARCHAR2,
      i_maker1                    IN   VARCHAR2,
   -- i_info                       IN   VARCHAR2,

      o_retCode                    OUT  VARCHAR2,
      o_err_msg                    OUT  VARCHAR2
      )

   IS  in_last_editor_time   varchar2(20) ;

      BEGIN  o_retCode := '0';
        in_last_editor_time := TO_CHAR(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS');

            UPDATE OPDATEClASSRULE a
            SET a.maker1 = i_maker1,
                a.last_editor_time=in_last_editor_time,
                a.maker2='',
                a.last_check_time='',
                a.state1 = '4',
                a.state2 = '3'
          WHERE (a.ruleid = i_ruleid);

          UPDATE OPDATEClASS b
            SET b.state = '3'
          WHERE (b.id = i_dateid) and (b.state='2');
           commit;
     EXCEPTION
      WHEN OTHERS
      THEN
         o_retcode := '-1';
         RETURN;

  END  PROC_OMPS_WAITDELDETAILDATE;
  
   PROCEDURE PROC_OMPS_QRYWAITDATE(
      i_ruleid                    IN   VARCHAR2,
      i_date                      IN   VARCHAR2,
   -- i_info                       IN   VARCHAR2,

      o_retCode                    OUT  VARCHAR2,
      o_err_msg                    OUT  VARCHAR2
      )

   IS countdate          number;
     BEGIN  o_retcode := '0';
    select count(*) into countdate
           from OPDATEClASS
          where (ruleid=i_ruleid) and (opdate=i_date);
          if(countdate=0)
          then
          o_retcode := '0';
    else
          o_retcode := '2';
          end if;
     EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         o_retcode := '1';
         RETURN;
      WHEN OTHERS
      THEN
         o_retcode := '-1';
         RETURN;

  END PROC_OMPS_QRYWAITDATE;
  
  PROCEDURE   PROC_OMPS_ADDWAITNEWDATE(
      i_ruleid                    IN   VARCHAR2,
      i_maker1                    IN   VARCHAR2,
      i_newdate                    IN   VARCHAR2,
      i_rulename                    IN   VARCHAR2,
      i_rulecode                    IN   VARCHAR2,
      i_state                    IN   VARCHAR2,
   -- i_info                       IN   VARCHAR2,

      o_retCode                    OUT  VARCHAR2,
      o_err_msg                    OUT  VARCHAR2
      )

   IS  in_last_editor_time   varchar2(20) ;

      BEGIN  o_retCode := '0';
        in_last_editor_time := TO_CHAR(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS');

            UPDATE OPDATEClASSRULE a
            SET a.maker1 = i_maker1,
                a.last_editor_time=in_last_editor_time,
                a.maker2='',
                a.last_check_time='',
                a.state1 = '1'
          WHERE (a.ruleid = i_ruleid) ;
         INSERT INTO OPDATEClASS
          (id,opdate,state,ruleid)
           VALUES
       (OPDATE.NEXTVAL,i_newdate,i_state,i_ruleid);
            commit;
     EXCEPTION
      WHEN OTHERS
      THEN
         o_retcode := '-1';
         RETURN;

  END  PROC_OMPS_ADDWAITNEWDATE;
  
  PROCEDURE   PROC_OMPS_CHECKDETAILDATE1(
      i_ruleid                    IN   VARCHAR2,
      i_dateid                    IN   VARCHAR2,
      i_state1                   IN   VARCHAR2,
      i_maker1                   IN   VARCHAR2,
      
   -- i_info                       IN   VARCHAR2,

      o_retCode                    OUT  VARCHAR2,
      o_err_msg                    OUT  VARCHAR2
      )

   IS   in_last_check_time   varchar2(20) ;
          countdate          number;
          
     BEGIN  o_retcode := '0';
           UPDATE OPDATEClASS a
            SET a.state = '2'
          WHERE (a.id = i_dateid) and (a.state='1');
               
          in_last_check_time := TO_CHAR(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS');
           UPDATE OPDATECLASSRULE b
            SET b.state1='2',
                b.maker2 = i_maker1,
                b.last_check_time=in_last_check_time
          WHERE (b.ruleid = i_ruleid);
       
           select count(*) into countdate
           from OPDATEClASS c
          where (c.ruleid=i_ruleid)and (c.state='1') ;
          if(countdate=0)
          then
         UPDATE OPDATECLASSRULE
            SET OPDATECLASSRULE.state2='2'
            where (OPDATECLASSRULE.ruleid=i_ruleid) ;
          end if;
     EXCEPTION
      WHEN OTHERS
      THEN
         o_retcode := '-1';
         RETURN;

  END  PROC_OMPS_CHECKDETAILDATE1;
  
  PROCEDURE  PROC_OMPS_CHECKDATECLASS(
      i_ruleid                    IN   VARCHAR2,
      i_maker1                   IN   VARCHAR2,
   -- i_info                       IN   VARCHAR2,

      o_retCode                    OUT  VARCHAR2,
      o_err_msg                    OUT  VARCHAR2
      )

   IS  in_last_check_time   varchar2(20) ;
     BEGIN  o_retcode := '0';
    in_last_check_time := TO_CHAR(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS');
             UPDATE OPDATEClASSRULE a
            SET a.state1 = '2',
                a.maker2 = i_maker1,
                a.last_check_time=in_last_check_time,
                a.state2 = '2'
          WHERE (a.ruleid = i_ruleid);

          UPDATE OPDATEClASS b
            SET b.state = '2'
          WHERE (b.ruleid = i_ruleid) and (b.state='1') ;
           commit;
     EXCEPTION
      WHEN OTHERS
      THEN
         o_retcode := '-1';
         RETURN;

  END  PROC_OMPS_CHECKDATECLASS;
  
  PROCEDURE PROC_OMPS_ADDUNREGQRYCODE(
      i_rulecode                   IN   VARCHAR2,

   -- i_info                       IN   VARCHAR2,

      o_retCode                    OUT  VARCHAR2,
      o_err_msg                    OUT  VARCHAR2
      )

   IS countdate          number;
     BEGIN  o_retcode := '0';
    select count(*) into countdate
           from OPDATEClASSRULE
          where (rulecode=i_rulecode) ;
          if(countdate=0)
          then
          o_retcode := '0';
    else
          o_retcode := '2';
          end if;
     EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         o_retcode := '1';
         RETURN;
      WHEN OTHERS
      THEN
         o_retcode := '-1';
         RETURN;

  END PROC_OMPS_ADDUNREGQRYCODE;
  
 PROCEDURE PROC_OMPS_ADDUNREGQRYDATECLASS(
      i_rulename                   IN   VARCHAR2,

   -- i_info                       IN   VARCHAR2,

      o_retCode                    OUT  VARCHAR2,