今天分享点EBS Form 开发独创的经验,在项目上分享给很多人,今天共享给大家了。
**背景:**我们开发form的时候由于使用替代字段、字段设置长度等等各种原因,甚至标准form,业务出现记录被锁的情况(不是因为多人操作,而是因为界面记录和后台表记录不一致造成的锁)。
基于这种场景,我们如何快速找到哪个字段被锁呢? 答案就是利用个性化执行lock 语句判断字段不一致。
第一步,我们找到form 的lock 程序
通常form lock 一种通过on-lock 里面直接写语句进行锁记录,一种on-lock 通过包的形式进行锁记录,我们仅以第一种方式抛转引玉,第二种需要点改写,改写成第一种。
以我开发的一个配置form为例:
找到on-lock触发器 获取锁记录内容
这两部分内容拿出来改写
源码:
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)))**
如下图所示,无法取到值,表明上面sql 里有字段和表里是不一致的。
我们可以循环该动作直到找到差异字段。
以上,大家随便尝试,这个思路可以引用到很多地方,比如我们可以在这里判断当前职责的权限等等 ,适当的利用系统功能,转化成我们的工具。
祝君好运!