Form开发技巧一 ------- 相同ORG只允许同时一个Session作业

需求：

1．开发一个批次release工单的程序

2．同时，只能有一个Session Release同一个ORG的工单

解决方法：

1．（WHEN-NEW-FORM-INSTANCE）在程序打开时，删除纪录程序运行table因程序异常关闭未清除的纪录

   参考SQL :

DELETE      program_lock_control a

           WHERE program_name = 'RELEASE_WO'

           AND NOT EXISTS (

                  SELECT 1

                    FROM v$session b

                   WHERE b.SID = a.lock_sid

                     AND b.serial# = a.lock_serial

                     AND b.audsid = a.lock_audsid);

2．（WHEN-NEW-FORM-INSTANCE）判断自己有没有打开了此程序，如已经打开，提示已经打开，关闭本次打开的程序，否则Insert 一笔纪录到控制table.

参考SQL:

   SELECT COUNT (1)

     INTO :parameter.v_runner

   FROM program_lock_control

               WHERE program_name = 'RELEASE_WO'

               AND user_id = :parameter.user_id;

            IF :parameter.v_runner > 0

               THEN

               show_alert_message ('您不能同时打开多次此程序!');

             DO_KEY ('exit_form');

            ELSE

              SELECT SID, serial#, audsid

              INTO v_sid, v_serial, v_audsid

              FROM v$session

              WHERE audsid = USERENV ('SESSIONID');

 

               SELECT user_name

                INTO v_runner

                FROM fnd_user

              WHERE user_id = :parameter.user_id;

 

              INSERT INTO  program_lock_control

                  (program_name, organization_id, lock_flag, user_id,

                   user_name, login_time, lock_sid, lock_serial, lock_audsid

                  )

               VALUES ('RELEASE_WO', 0, 'Y', :parameter.user_id,

                   v_runner, SYSDATE, v_sid, v_serial, v_audsid

                  );

 

             COMMIT;

            End if;

3. （Find when-button-pressed）按下寻找可Release工单的按钮，同样先删除异常退出程序造成的纪录（防止另外一个用户已经打开程序，一个用户异常退出）

参考SQL:

DELETE      program_lock_control a

           WHERE program_name = 'RELEASE_WO'

           AND NOT EXISTS (

                  SELECT 1

                    FROM v$session b

                   WHERE b.SID = a.lock_sid

                     AND b.serial# = a.lock_serial

                     AND b.audsid = a.lock_audsid);

4.（Find when-button-pressed）判断用户选择的ORG是否有被他人所定，如有，提示用户不可操作，如没有，则修改控制table,将ORG栏位改为用户选择的ORG，锁定此ORG.

参考SQL:

SELECT COUNT ('*')

           INTO v_count

           FROM program_lock_control

          WHERE organization_id = :header.org_id

            AND program_name = 'RELEASE_WO'

            AND lock_flag = 'Y';

 

         IF v_count > 0

         THEN

            show_alert_message

                   (   '同时只能有一个人Release同一Org的工单，目前 '

                    || v_runner

                    || '正在运行此程序！'

                   );

            RAISE form_trigger_failure;

         END IF;

      ELSE

         UPDATE program_lock_control

            SET organization_id = :header.org_id

          WHERE program_name = 'BATCH_RELEASE_WO'

            AND user_id = :parameter.user_id;

      End if;

  5. 用户退出时运行，解除锁定

  参考SQL:

  DELETE     program_lock_control

      WHERE program_name = 'RELEASE_WO'

            AND user_id=:parameter.user_id;

:System.Message_Level := 25;

commit;

do_key('exit_form');

转载于:https://www.cnblogs.com/kevinsun/archive/2013/02/21/2920523.html