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

今天分享点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 长度比较小具体多少没测过,感兴趣去确认一下,不影响我们接下来的判断。

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

  1. 打开界面 ,查询异常记录
    在这里插入图片描述

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

  1. 在活动中随便找一个字段,我们的目的是通过下图值字段中执行sql来判断字段
  2. 将我们刚才整理改写的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)))**

如下图所示,无法取到值,表明上面sql 里有字段和表里是不一致的。
在这里插入图片描述
我们可以循环该动作直到找到差异字段。

以上,大家随便尝试,这个思路可以引用到很多地方,比如我们可以在这里判断当前职责的权限等等 ,适当的利用系统功能,转化成我们的工具。

祝君好运!

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
系统包含项目的源码和搭建的详细文档,以及数据库脚本 系统功能模块(开发时可取舍) 1. 权限管理:点开二级菜单进入三级菜单显示 角色(基础权限)和按钮权限 角色(基础权限): 分角色组和角色,独立分配菜单权限和增删改查权限。 按钮权限: 给角色分配按钮权限。 2. 按钮管理:自定义按钮管理,维护按钮权限标识等 3. 菜单管理:无限级别自定义菜单,自定义菜单图标,业务菜单和系统菜单分离,菜单状态显示隐藏(递归处理) 4. 数据字典:无限级别,支持多级别无限分类。内设编号,排序等 5. 组织机构:无限级别,公司or部门管理 6. 在线管理:websocket技术,实时检测在线用户列表,统计在线人数,可强制用户下线 同一用户只能在一个客户端登录 7. 系统用户:对各个基本的用户增删改查,单发、群发站内信邮件短信,导入导出excel表格,批量删除 8. 会员管理:对前台用户管理,分配会员级别,到期时间,状态,联系信息等资料 9. 代码生成:生成完整的模块代码,并保留生成记录模版,可复用 (超强悍开发利器) 正向生成: 生成完整的模块,页面、处理类、service层、myabaits的xml 建表的sql脚本等 反向生成: 任意连接其它数据库(mysql、oracle、sqlserver),根据表反射生成本系统的模块 10. 性能监控:监控整个系统的性能,SQL监控,SQL防火墙,URL监控,SPRING监控,SESSION监控等 11. 接口测试:POST or GET 方式检测系统接口,参数加密,json返回结果,计算服务器响应时间 12. 发送邮件:单发,群发邮件 13. 置二维码:生成二维码图表保存到服务器 or 解析读取二维码内信息 14. 图表报表:柱状图、饼状图、折线图、各种图表大全 15. 地图工具:打开地图, 鼠标点击地图某位置获取经纬度坐标,根据经纬度计算两点距离 16. 打印测试:页面打印预览测试 17. 图片管理:对批量上传的图片统一管理 ,点击放大,可打开多个,自由切换,绚丽预览效果 18. 图片爬虫:输入某网址,爬出其图片显示在页面上,可以放大预览。可保存到服务器上,到图片管理里面 19. 站内信:收信箱和发信箱, websocket技术通讯技术做的及时收信提醒,可配置语音提示来信 20. 系统设置:修改系统名称,邮件服务器配置,短信账号设置,图片水印配置,微信配置 21. 及时聊天:打开聊天窗口,可群聊、一对一聊天 22. 表单构建:拖拽式快速自定义构建表单,组建元素丰富,有富文本、上传控件、下拉框等等 23. 主附结构:提供一个主表和明细表模块的例子(用本代码生成器生成的) 24. 员工管理:和组织机构部门管理,可以绑定登录系统用户,授权数据权限 -------------------------------------------------------------------------------------------------------------------------数据库管理 25. 数据库备份:可备份单表、整库,支持本地和远程数据库备份(java界面编程技术,socket编程技术) 26. 备份定时器:quartz 2.2 强大的任务调度,多线程备份数据库,任务启动关闭异步操作 27. 数据库还原:历史备份记录,还原数据库 or 单表 ,统计备份时间和文件大小 28. SQL编辑器:强大的SQL编辑器,支持编辑语句复杂查询语句,生成动态报表,可导出excel ------------------------------------------------------------------------------------------------------------------------- 菜单权限:分配给每个角色不同的菜单权限, 每个角色看到的菜单不同,无限级别菜单 按钮权限:独立分配不同的角色不同的功能权限,增删改查权限分配具体到不同的菜单,自定义按钮管理 支持多用户分权限管理后台, 权限具体到不同的菜单不同的按钮

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值