一、与数据库表中进行对比看能否插入或更改:
代码实现:
1、编写检查重复时间段的package:
PROCEDURE check_date is
v_start_date date;
v_end_date date;
v_count number;
CURSOR accrual_date_cur
is select a.START_DATE,
a.END_DATE
from CUX_FIN_CE_FUND_ACCRUALS a
where a.BALANCE_TYPE_CODE = :CUX_FUND_ACCRUALS_L.BALANCE_TYPE_CODE
and a.ACCRUAL_CATEGORY = :CUX_FUND_ACCRUALS_L.ACCRUAL_CATEGORY
and a.ENABLE_FLAG = 'Y'
and a.ROWID != :CUX_FUND_ACCRUALS_L.ROW_ID;--cursor中需要排除当前需要新增或者修改的这一行记录
begin
v_count := 0;
for accrual_date_rec in accrual_date_cur loop
v_start_date := accrual_date_rec.start_date;
v_end_date := accrual_date_rec.end_date;
--输入结束日期和数据库结束日期都不为空
if :CUX_FUND_ACCRUALS_L.end_date is not null and v_end_date is not null then
if nvl(v_start_date,sysdate) <= :CUX_FUND_ACCRUALS_L.end_date
and v_end_date >= nvl(:CUX_FUND_ACCRUALS_L.start_date,sysdate)
then
--fnd_message.debug('OK');
v_count := v_count+1;
exit;
end if;
--输入结束日期为为空
elsif :CUX_FUND_ACCRUALS_L.end_date is null then
if nvl(v_end_date,sysdate) >= nvl(:CUX_FUND_ACCRUALS_L.start_date,sysdate)
then
--fnd_message.debug('OK2');
v_count := v_count+1;
exit;
end if;
--数据库结束日期为空时
elsif v_end_date is null then
if nvl(:CUX_FUND_ACCRUALS_L.end_date,sysdate) >= nvl(v_start_date,sysdate)
then
--fnd_message.debug('OK3');
v_count := v_count+1;
exit;
end if;
--都为空时
elsif v_end_date is null and :CUX_FUND_ACCRUALS_L.end_date is null
then
v_count := v_count+1;
exit;
end if;
end loop;
if v_count > 0 then
fnd_message.debug('同一时间段中的资金类型和费率类型不能相同');
raise form_trigger_failure;
end if;
end check_date;
2、在块级trigger:on-insert、on-update中调用;
Form界面同一时间段中相同类型的数据不能同时启用
最新推荐文章于 2024-08-12 02:18:05 发布