[Oracle EBS] FORM 快速判断被锁字段

骑驴闲逛

已于 2022-06-28 11:11:56 修改

阅读量519

点赞数

分类专栏： EBS 技术 EBS Form

于 2022-06-28 11:00:45 首次发布

本文链接：https://blog.csdn.net/savior8718/article/details/125497375

版权

oracle issue 运维开发

EBS 技术同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

EBS Form

1 篇文章 0 订阅

订阅专栏

今天分享点EBS Form 开发独创的经验，在项目上分享给很多人，今天共享给大家了。

**背景：**我们开发form的时候由于使用替代字段、字段设置长度等等各种原因，甚至标准form，业务出现记录被锁的情况（不是因为多人操作，而是因为界面记录和后台表记录不一致造成的锁）。
基于这种场景，我们如何快速找到哪个字段被锁呢？答案就是利用个性化执行lock 语句判断字段不一致。

第一步，我们找到form 的lock 程序
通常form lock 一种通过on-lock 里面直接写语句进行锁记录，一种on-lock 通过包的形式进行锁记录，我们仅以第一种方式抛转引玉，第二种需要点改写，改写成第一种。

以我开发的一个配置form为例：

找到on-lock触发器获取锁记录内容
在这里插入图片描述

这两部分内容拿出来改写

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WIBy2RAb-1656382783471)(C:/Users/wangwj3/AppData/Roaming/Typora/typora-user-images/image-20220628095228679.png)]
源码：

PROCEDURE lock_row IS
  CURSOR c_row IS
    SELECT *
      FROM cux_gl_account_type
     WHERE ROWID = :accounts.row_id
       FOR UPDATE OF account_type_id NOWAIT;
  rec c_row%ROWTYPE;
  i   NUMBER := 0;
BEGIN
  LOOP
    BEGIN
      i := i + 1;
      OPEN c_row;
      FETCH c_row
        INTO rec;
      IF (c_row%NOTFOUND) THEN
        CLOSE c_row;
        fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
        fnd_message.error;
        RAISE form_trigger_failure;
      END IF;
      CLOSE c_row;
      IF ((rec.account_type_id = :accounts.account_type_id) AND
         ((rec.flex_value_set_id = :accounts.flex_value_set_id) OR
         ((rec.flex_value_set_id IS NULL) AND
         (:accounts.flex_value_set_id IS NULL))) AND
         ((rec.flex_value_set_name = :accounts.flex_value_set_name) OR
         ((rec.flex_value_set_name IS NULL) AND
         (:accounts.flex_value_set_name IS NULL))) AND
         ((rec.account_code = :accounts.account_code) OR
         ((rec.account_code IS NULL) AND (:accounts.account_code IS NULL))) AND
         ((rec.account_type = :accounts.account_type) OR
         ((rec.account_type IS NULL) AND (:accounts.account_type IS NULL))) AND
         ((rec.attr_type = :accounts.attr_type) OR
         ((rec.attr_type IS NULL) AND (:accounts.attr_type IS NULL))) AND
         ((rec.start_activate_date = :accounts.start_activate_date) OR
         ((rec.start_activate_date IS NULL) AND
         (:accounts.start_activate_date IS NULL))) AND
         ((rec.end_activate_date = :accounts.end_activate_date) OR
         ((rec.end_activate_date IS NULL) AND
         (:accounts.end_activate_date IS NULL))) AND
         ((rec.enable_flag = :accounts.enable_flag) OR
         ((rec.enable_flag IS NULL) AND (:accounts.enable_flag IS NULL))) AND
         ((rec.object_version_number = :accounts.object_version_number) OR
         ((rec.object_version_number IS NULL) AND
         (:accounts.object_version_number IS NULL))) AND
         ((rec.attribute_category = :accounts.attribute_category) OR
         ((rec.attribute_category IS NULL) AND
         (:accounts.attribute_category IS NULL))) AND
         ((rec.attribute1 = :accounts.attribute1) OR
         ((rec.attribute1 IS NULL) AND (:accounts.attribute1 IS NULL))) AND
         ((rec.attribute2 = :accounts.attribute2) OR
         ((rec.attribute2 IS NULL) AND (:accounts.attribute2 IS NULL))) AND
         ((rec.attribute3 = :accounts.attribute3) OR
         ((rec.attribute3 IS NULL) AND (:accounts.attribute3 IS NULL))) AND
         ((rec.attribute4 = :accounts.attribute4) OR
         ((rec.attribute4 IS NULL) AND (:accounts.attribute4 IS NULL))) AND
         ((rec.attribute5 = :accounts.attribute5) OR
         ((rec.attribute5 IS NULL) AND (:accounts.attribute5 IS NULL))) AND
         ((rec.attribute6 = :accounts.attribute6) OR
         ((rec.attribute6 IS NULL) AND (:accounts.attribute6 IS NULL))) AND
         ((rec.attribute7 = :accounts.attribute7) OR
         ((rec.attribute7 IS NULL) AND (:accounts.attribute7 IS NULL))) AND
         ((rec.attribute8 = :accounts.attribute8) OR
         ((rec.attribute8 IS NULL) AND (:accounts.attribute8 IS NULL))) AND
         ((rec.attribute9 = :accounts.attribute9) OR
         ((rec.attribute9 IS NULL) AND (:accounts.attribute9 IS NULL))) AND
         ((rec.attribute10 = :accounts.attribute10) OR
         ((rec.attribute10 IS NULL) AND (:accounts.attribute10 IS NULL))) AND
         ((rec.attribute11 = :accounts.attribute11) OR
         ((rec.attribute11 IS NULL) AND (:accounts.attribute11 IS NULL))) AND
         ((rec.attribute12 = :accounts.attribute12) OR
         ((rec.attribute12 IS NULL) AND (:accounts.attribute12 IS NULL))) AND
         ((rec.attribute13 = :accounts.attribute13) OR
         ((rec.attribute13 IS NULL) AND (:accounts.attribute13 IS NULL))) AND
         ((rec.attribute14 = :accounts.attribute14) OR
         ((rec.attribute14 IS NULL) AND (:accounts.attribute14 IS NULL))) AND
         ((rec.attribute15 = :accounts.attribute15) OR
         ((rec.attribute15 IS NULL) AND (:accounts.attribute15 IS NULL))) AND
         ((rec.creation_date = :accounts.creation_date) OR
         ((rec.creation_date IS NULL) AND (:accounts.creation_date IS NULL))) AND
         ((rec.created_by = :accounts.created_by) OR
         ((rec.created_by IS NULL) AND (:accounts.created_by IS NULL))) AND
         ((rec.last_updated_by = :accounts.last_updated_by) OR
         ((rec.last_updated_by IS NULL) AND
         (:accounts.last_updated_by IS NULL))) AND
         ((rec.last_update_date = :accounts.last_update_date) OR
         ((rec.last_update_date IS NULL) AND
         (:accounts.last_update_date IS NULL))) AND
         ((rec.last_update_login = :accounts.last_update_login) OR
         ((rec.last_update_login IS NULL) AND
         (:accounts.last_update_login IS NULL))) AND
         ((rec.calc_direction = :accounts.calc_direction) OR
         ((rec.calc_direction IS NULL) AND (:accounts.calc_direction IS NULL))) AND
         ((rec.revaluation_flag = :accounts.revaluation_flag) OR
         ((rec.revaluation_flag IS NULL) AND
         (:accounts.revaluation_flag IS NULL)))) THEN
        RETURN;
      ELSE
        fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
        fnd_message.error;
        RAISE form_trigger_failure;
      END IF;
    EXCEPTION
      WHEN app_exception.record_lock_exception THEN
        app_exception.record_lock_error(i);
    END;
  END LOOP;
END lock_row;

第二步，改写成form 界面可运行的sql 语句

改写如下：

SELECT 'Y'
  FROM cux_gl_account_type rec
 WHERE ROWID = :accounts.row_id
   AND (rec.account_type_id = :accounts.account_type_id)
   AND ((rec.flex_value_set_id = :accounts.flex_value_set_id) OR
       ((rec.flex_value_set_id IS NULL) AND
       (:accounts.flex_value_set_id IS NULL)))
   AND ((rec.flex_value_set_name = :accounts.flex_value_set_name) OR
       ((rec.flex_value_set_name IS NULL) AND
       (:accounts.flex_value_set_name IS NULL)))
   AND ((rec.account_code = :accounts.account_code) OR
       ((rec.account_code IS NULL) AND (:accounts.account_code IS NULL)))
   AND ((rec.account_type = :accounts.account_type) OR
       ((rec.account_type IS NULL) AND (:accounts.account_type IS NULL)))
   AND ((rec.attr_type = :accounts.attr_type) OR
       ((rec.attr_type IS NULL) AND (:accounts.attr_type IS NULL)))
   AND ((rec.start_activate_date = :accounts.start_activate_date) OR
       ((rec.start_activate_date IS NULL) AND
       (:accounts.start_activate_date IS NULL)))
   AND ((rec.end_activate_date = :accounts.end_activate_date) OR
       ((rec.end_activate_date IS NULL) AND
       (:accounts.end_activate_date IS NULL)))
   AND ((rec.enable_flag = :accounts.enable_flag) OR
       ((rec.enable_flag IS NULL) AND (:accounts.enable_flag IS NULL)))
   AND ((rec.object_version_number = :accounts.object_version_number) OR
       ((rec.object_version_number IS NULL) AND
       (:accounts.object_version_number IS NULL)))
   AND ((rec.attribute_category = :accounts.attribute_category) OR
       ((rec.attribute_category IS NULL) AND
       (:accounts.attribute_category IS NULL)))
   AND ((rec.attribute1 = :accounts.attribute1) OR
       ((rec.attribute1 IS NULL) AND (:accounts.attribute1 IS NULL)))
   AND ((rec.attribute2 = :accounts.attribute2) OR
       ((rec.attribute2 IS NULL) AND (:accounts.attribute2 IS NULL)))
   AND ((rec.attribute3 = :accounts.attribute3) OR
       ((rec.attribute3 IS NULL) AND (:accounts.attribute3 IS NULL)))
   AND ((rec.attribute4 = :accounts.attribute4) OR
       ((rec.attribute4 IS NULL) AND (:accounts.attribute4 IS NULL)))
   AND ((rec.attribute5 = :accounts.attribute5) OR
       ((rec.attribute5 IS NULL) AND (:accounts.attribute5 IS NULL)))
   AND ((rec.attribute6 = :accounts.attribute6) OR
       ((rec.attribute6 IS NULL) AND (:accounts.attribute6 IS NULL)))
   AND ((rec.attribute7 = :accounts.attribute7) OR
       ((rec.attribute7 IS NULL) AND (:accounts.attribute7 IS NULL)))
   AND ((rec.attribute8 = :accounts.attribute8) OR
       ((rec.attribute8 IS NULL) AND (:accounts.attribute8 IS NULL)))
   AND ((rec.attribute9 = :accounts.attribute9) OR
       ((rec.attribute9 IS NULL) AND (:accounts.attribute9 IS NULL)))
   AND ((rec.attribute10 = :accounts.attribute10) OR
       ((rec.attribute10 IS NULL) AND (:accounts.attribute10 IS NULL)))
   AND ((rec.attribute11 = :accounts.attribute11) OR
       ((rec.attribute11 IS NULL) AND (:accounts.attribute11 IS NULL)))
   AND ((rec.attribute12 = :accounts.attribute12) OR
       ((rec.attribute12 IS NULL) AND (:accounts.attribute12 IS NULL)))
   AND ((rec.attribute13 = :accounts.attribute13) OR
       ((rec.attribute13 IS NULL) AND (:accounts.attribute13 IS NULL)))
   AND ((rec.attribute14 = :accounts.attribute14) OR
       ((rec.attribute14 IS NULL) AND (:accounts.attribute14 IS NULL)))
   AND ((rec.attribute15 = :accounts.attribute15) OR
       ((rec.attribute15 IS NULL) AND (:accounts.attribute15 IS NULL)))
   AND ((rec.creation_date = :accounts.creation_date) OR
       ((rec.creation_date IS NULL) AND (:accounts.creation_date IS NULL)))
   AND ((rec.created_by = :accounts.created_by) OR
       ((rec.created_by IS NULL) AND (:accounts.created_by IS NULL)))
   AND ((rec.last_updated_by = :accounts.last_updated_by) OR
       ((rec.last_updated_by IS NULL) AND (:accounts.last_updated_by IS NULL)))
   AND ((rec.last_update_date = :accounts.last_update_date) OR
       ((rec.last_update_date IS NULL) AND
       (:accounts.last_update_date IS NULL)))
   AND ((rec.last_update_login = :accounts.last_update_login) OR
       ((rec.last_update_login IS NULL) AND
       (:accounts.last_update_login IS NULL)))
   AND ((rec.calc_direction = :accounts.calc_direction) OR
       ((rec.calc_direction IS NULL) AND (:accounts.calc_direction IS NULL)))
   AND ((rec.revaluation_flag = :accounts.revaluation_flag) OR
       ((rec.revaluation_flag IS NULL) AND
       (:accounts.revaluation_flag IS NULL)))

注：由于个性化内容可容纳的sql 长度比较小具体多少没测过，感兴趣去确认一下，不影响我们接下来的判断。

第三步，查询出记录后，通过个性化反复执行排查该记录那个字段界面值和数据库不一致造成的锁

打开界面，查询异常记录

找到个性化菜单：N:帮助>诊断>自定义代码>个性化
在这里插入图片描述
因为我们不需要真的定义个性化，所以在个性化界面的头记录，随便填一下，一会儿找到后，就删除该条个性化即可（或者不保存就行）。

在活动中随便找一个字段，我们的目的是通过下图值字段中执行sql来判断字段
将我们刚才整理改写的SQL 放进来进行获取值来判断是否一致。

因为值字段可写内容长度受限，我们分段来写，特别注意点一点保证至少保留一个唯一字段值，要不然SQL结果不具有参考价值，懂SQL的这个我想可以理解，主要是不好解释，哈哈。
在这里插入图片描述
提示Y，表明当前sql 涉及到的字段均一致，继续判断后续字段。
值内容参考：

SELECT 'Y'
  FROM cux_gl_account_type rec
 WHERE ROWID = :accounts.row_id
   AND (rec.account_type_id = :accounts.account_type_id)
   AND ((rec.flex_value_set_id = :accounts.flex_value_set_id) OR
       ((rec.flex_value_set_id IS NULL) AND
       (:accounts.flex_value_set_id IS NULL)))
   AND ((rec.flex_value_set_name = :accounts.flex_value_set_name) OR
       ((rec.flex_value_set_name IS NULL) AND
       (:accounts.flex_value_set_name IS NULL)))
   AND ((rec.account_code = :accounts.account_code) OR
       ((rec.account_code IS NULL) AND (:accounts.account_code IS NULL)))
   AND ((rec.account_type = :accounts.account_type) OR
       ((rec.account_type IS NULL) AND (:accounts.account_type IS NULL)))
   AND ((rec.attr_type = :accounts.attr_type) OR
       ((rec.attr_type IS NULL) AND (:accounts.attr_type IS NULL)))
   AND ((rec.start_activate_date = :accounts.start_activate_date) OR
       ((rec.start_activate_date IS NULL) AND
       (:accounts.start_activate_date IS NULL)))
   AND ((rec.end_activate_date = :accounts.end_activate_date) OR
       ((rec.end_activate_date IS NULL) AND
       (:accounts.end_activate_date IS NULL)))

以下我故意改成不同，看一下

错误值：黑体部分

SELECT 'Y'
  FROM cux_gl_account_type rec
 WHERE ROWID = :accounts.row_id
   AND (rec.account_type_id = :accounts.account_type_id)
   AND ((rec.flex_value_set_id = :accounts.flex_value_set_id) OR
       ((rec.flex_value_set_id IS NULL) AND
       (:accounts.flex_value_set_id IS NULL)))
   AND ((rec.flex_value_set_name = :accounts.flex_value_set_name) OR
       ((rec.flex_value_set_name IS NULL) AND
       (:accounts.flex_value_set_name IS NULL)))
   AND ((rec.account_code = :accounts.account_code) OR
       ((rec.account_code IS NULL) AND (:accounts.account_code IS NULL)))
   AND ((rec.account_type = :accounts.account_type) OR
       ((rec.account_type IS NULL) AND (:accounts.account_type IS NULL)))
   AND ((rec.attr_type = :accounts.attr_type) OR
       ((rec.attr_type IS NULL) AND (:accounts.attr_type IS NULL)))
   AND ((rec.start_activate_date = :accounts.start_activate_date) OR
       ((rec.start_activate_date IS NULL) AND
       (:accounts.start_activate_date IS NULL)))
   **AND ((rec.end_activate_date = SYSDATE) OR --修改位置
       ((rec.end_activate_date IS NULL) AND (SYSDATE IS NULL)))**