"APPS"."XTR_AD_MIRROR_DDA_LIMIT_ROW_T"
AFTER DELETE
ON "XTR"."XTR_MIRROR_DDA_LIMIT_ROW#" FOR EACH ROW
--
Type
AFTER EACH ROW
Event
DELETE
Column
When
Referencing
REFERENCING NEW AS NEW OLD AS OLD
Body
declare
l_country VARCHAR2(100);
l_grp_party VARCHAR2(7);
l_dmmy NUMBER;
l_lim_type VARCHAR2(7);
l_home_ccy VARCHAR2(50);
--
cursor GET_COUNTRY is
select COUNTRY_CODE
from XTR_PARTIES_V
where PARTY_CODE = :OLD.LIMIT_PARTY;
--
cursor GET_GROUP_DET is
select nvl(CROSS_REF_TO_OTHER_PARTY,:OLD.LIMIT_PARTY)
from XTR_PARTIES_V
where PARTY_CODE = :OLD.LIMIT_PARTY;
--
cursor GET_HOMECCY is
select param_value
from XTR_PRO_PARAM
where param_name = 'SYSTEM_FUNCTIONAL_CCY';
--
cursor GET_TYPE is
select LIMIT_TYPE
from XTR_COMPANY_LIMITS
where LIMIT_CODE = :OLD.LIMIT_CODE;
cursor GET_FX_INVEST_FUND_TYPE is
select fx_invest_fund_type
from XTR_LIMIT_TYPES
where limit_type=l_lim_type;
--
cursor c_ok_to_do (pc3_param_name VARCHAR2) is
select nvl(param_value,'Y')
from XTR_PRO_PARAM
where param_name = pc3_param_name;
v_ok_to_do VARCHAR2(2);
l_rowid varchar2(30);
cursor CHK_LOCK_MASTER_CURRENCIES is
select rowid
from XTR_MASTER_CURRENCIES
where CURRENCY = :OLD.CURRENCY
for update of UTILISED_AMOUNT NOWAIT;
cursor CHK_LOCK_MASTER_CURRENCIES_1 is
select rowid
from XTR_MASTER_CURRENCIES
where CURRENCY = substrb(:OLD.CURRENCY_COMBINATION,1,instrb(:OLD.CURRENCY_COMBINATION,'/')-lengthb('/'))
--* bug#2920529, rravunny substr(:OLD.CURRENCY_COMBINATION,1,3)
-- and CURRENCY <> l_home_ccy
for update of UTILISED_AMOUNT NOWAIT;
cursor CHK_LOCK_MASTER_CURRENCIES_2 is
select rowid
from XTR_MASTER_CURRENCIES
where CURRENCY = substrb(:OLD.CURRENCY_COMBINATION,instrb(:OLD.CURRENCY_COMBINATION,'/')+lengthb('/'),lengthb(:OLD.CURRENCY_COMBINATION))
--* bug#2920529, rravunny substr(:OLD.CURRENCY_COMBINATION,5,3)
-- and CURRENCY <> l_home_ccy
for update of UTILISED_AMOUNT NOWAIT;
cursor CHK_LOCK_COUNTRY_LIMITS is
select rowid
from XTR_COUNTRY_COMPANY_LIMITS
where COMPANY_CODE=:OLD.COMPANY_CODE
and COUNTRY_CODE=l_country
for update of UTILISED_AMOUNT NOWAIT;
cursor CHK_LOCK_COMPANY_LIMITS is
select rowid
from XTR_COMPANY_LIMITS
where COMPANY_CODE = :OLD.COMPANY_CODE
and LIMIT_CODE = :OLD.LIMIT_CODE
for update of UTILISED_AMOUNT NOWAIT;
cursor CHK_LOCK_COUNTERPARTY_LIMITS is
select rowid
from XTR_COUNTERPARTY_LIMITS
where COMPANY_CODE = :OLD.COMPANY_CODE
and CPARTY_CODE = :OLD.LIMIT_PARTY
and LIMIT_CODE = :OLD.LIMIT_CODE
for update of UTILISED_AMOUNT NOWAIT;
cursor CHK_LOCK_GROUP_LIMITS is
select 1
from XTR_GROUP_LIMITS
where COMPANY_CODE = :OLD.COMPANY_CODE
and (LIMIT_TYPE = nvl(l_lim_type,'X')
or (LIMIT_TYPE='XI' and nvl(l_lim_type,'X') in('X','I')))
and CPARTY_CODE = l_grp_party
for update of UTILISED_AMOUNT NOWAIT;
--
begin
if :OLD.LIMIT_CODE is NOT NULL then
--
open c_ok_to_do('LIMIT_CHECK_SOVRN');
fetch c_ok_to_do into v_ok_to_do;
if c_ok_to_do%NOTFOUND then
v_ok_to_do := 'Y';
end if;
close c_ok_to_do;
--
if v_ok_to_do = 'Y' then
--------------------------
-- Country Limits
--------------------------
open GET_HOMECCY;
fetch GET_HOMECCY INTO l_home_ccy;
close GET_HOMECCY;
--
open GET_COUNTRY;
fetch GET_COUNTRY INTO l_country;
if GET_COUNTRY%FOUND then
close GET_COUNTRY;
open CHK_LOCK_COUNTRY_LIMITS;
fetch CHK_LOCK_COUNTRY_LIMITS into l_rowid;
if CHK_LOCK_COUNTRY_LIMITS%FOUND then
close CHK_LOCK_COUNTRY_LIMITS;
update XTR_COUNTRY_COMPANY_LIMITS
set UTILISED_AMOUNT = nvl(UTILISED_AMOUNT,0) -
nvl(:OLD.HCE_UTILISED_AMOUNT,0)
where rowid=l_rowid;
else
close CHK_LOCK_COUNTRY_LIMITS;
end if;
else
close GET_COUNTRY;
end if;
end if;
--------------------------
-- Global Limits
--------------------------
open c_ok_to_do('LIMIT_CHECK_GLOBAL');
fetch c_ok_to_do into v_ok_to_do;
if c_ok_to_do%NOTFOUND then
v_ok_to_do := 'Y';
end if;
close c_ok_to_do;
--
if v_ok_to_do = 'Y' then
open CHK_LOCK_COMPANY_LIMITS;
fetch CHK_LOCK_COMPANY_LIMITS into l_rowid;
if CHK_LOCK_COMPANY_LIMITS%FOUND then
close CHK_LOCK_COMPANY_LIMITS;
update XTR_COMPANY_LIMITS
set UTILISED_AMOUNT = nvl(UTILISED_AMOUNT,0) -
nvl(:OLD.HCE_UTILISED_AMOUNT,0)
where rowid=l_rowid;
else
close CHK_LOCK_COMPANY_LIMITS;
end if;
end if;
--------------------------
-- Counterparty Limits
--------------------------
open c_ok_to_do('LIMIT_CHECK_CPARTY');
fetch c_ok_to_do into v_ok_to_do;
if c_ok_to_do%NOTFOUND then
v_ok_to_do := 'Y';
end if;
close c_ok_to_do;
--
if v_ok_to_do = 'Y' then
open CHK_LOCK_COUNTERPARTY_LIMITS;
fetch CHK_LOCK_COUNTERPARTY_LIMITS into l_rowid;
if CHK_LOCK_COUNTERPARTY_LIMITS%FOUND then
close CHK_LOCK_COUNTERPARTY_LIMITS;
update XTR_COUNTERPARTY_LIMITS
set UTILISED_AMOUNT = nvl(UTILISED_AMOUNT,0) -
nvl(:OLD.HCE_UTILISED_AMOUNT,0)
where rowid=l_rowid;
else
close CHK_LOCK_COUNTERPARTY_LIMITS;
end if;
end if;
--------------------------
-- Currency Limits
--------------------------
open c_ok_to_do('LIMIT_CHECK_CCY');
fetch c_ok_to_do into v_ok_to_do;
if c_ok_to_do%NOTFOUND then
v_ok_to_do := 'Y';
end if;
close c_ok_to_do;
--
if v_ok_to_do = 'Y' then
if :OLD.CURRENCY_COMBINATION is NOT NULL then
open CHK_LOCK_MASTER_CURRENCIES_1;
fetch CHK_LOCK_MASTER_CURRENCIES_1 into l_rowid;
if CHK_LOCK_MASTER_CURRENCIES_1%FOUND then
close CHK_LOCK_MASTER_CURRENCIES_1;
update XTR_MASTER_CURRENCIES
set UTILISED_AMOUNT = nvl(UTILISED_AMOUNT,0) -
nvl(:OLD.HCE_UTILISED_AMOUNT,0)
where rowid=l_rowid;
else
close CHK_LOCK_MASTER_CURRENCIES_1;
end if;
open CHK_LOCK_MASTER_CURRENCIES_2;
fetch CHK_LOCK_MASTER_CURRENCIES_2 into l_rowid;
if CHK_LOCK_MASTER_CURRENCIES_2%FOUND then
close CHK_LOCK_MASTER_CURRENCIES_2;
update XTR_MASTER_CURRENCIES
set UTILISED_AMOUNT = nvl(UTILISED_AMOUNT,0) -
nvl(:OLD.HCE_UTILISED_AMOUNT,0)
where rowid=l_rowid;
else
close CHK_LOCK_MASTER_CURRENCIES_2;
end if;
--* bug#2920529, rravunny end if;
else --* bug#2920529, rravunny
-- bug 1289530 elsif :OLD.CURRENCY <> l_home_ccy then
open CHK_LOCK_MASTER_CURRENCIES;
fetch CHK_LOCK_MASTER_CURRENCIES into l_rowid;
if CHK_LOCK_MASTER_CURRENCIES%FOUND then
close CHK_LOCK_MASTER_CURRENCIES;
update XTR_MASTER_CURRENCIES
set UTILISED_AMOUNT = nvl(UTILISED_AMOUNT,0) -
nvl(:OLD.HCE_UTILISED_AMOUNT,0)
where rowid = l_rowid;
else
close CHK_LOCK_MASTER_CURRENCIES;
-- bug 1289530 end if;
end if; --* bug#2920529, rravunny
end if;
end if;
--------------------------
-- Group Limits
--------------------------
open c_ok_to_do('LIMIT_CHECK_GROUP');
fetch c_ok_to_do into v_ok_to_do;
if c_ok_to_do%NOTFOUND then
v_ok_to_do := 'Y';
end if;
close c_ok_to_do;
--
if v_ok_to_do = 'Y' then
if :OLD.LIMIT_TYPE is NULL then
open GET_TYPE;
fetch GET_TYPE INTO l_lim_type;
close GET_TYPE;
else
l_lim_type := :OLD.LIMIT_TYPE;
end if;
open GET_FX_INVEST_FUND_TYPE;
fetch GET_FX_INVEST_FUND_TYPE into l_lim_type;
close GET_FX_INVEST_FUND_TYPE;
open GET_GROUP_DET;
fetch GET_GROUP_DET INTO l_grp_party;
close GET_GROUP_DET;
open CHK_LOCK_GROUP_LIMITS;
fetch CHK_LOCK_GROUP_LIMITS into l_dmmy;
if CHK_LOCK_GROUP_LIMITS%FOUND then
close CHK_LOCK_GROUP_LIMITS;
update XTR_GROUP_LIMITS
set UTILISED_AMOUNT = nvl(UTILISED_AMOUNT,0) -
nvl(:OLD.HCE_UTILISED_AMOUNT,0)
where COMPANY_CODE = :OLD.COMPANY_CODE
and (LIMIT_TYPE = nvl(l_lim_type,'X') or
(LIMIT_TYPE='XI' and nvl(l_lim_type,'X') in('X','I')))
and CPARTY_CODE = l_grp_party;
else
close CHK_LOCK_GROUP_LIMITS;
end if;
end if;
end if;
exception
when app_exceptions.RECORD_LOCK_EXCEPTION then
if CHK_LOCK_MASTER_CURRENCIES%ISOPEN then
close CHK_LOCK_MASTER_CURRENCIES;
end if;
if CHK_LOCK_MASTER_CURRENCIES_1%ISOPEN then
close CHK_LOCK_MASTER_CURRENCIES_1;
end if;
if CHK_LOCK_MASTER_CURRENCIES_2%ISOPEN then
close CHK_LOCK_MASTER_CURRENCIES_2;
end if;
if CHK_LOCK_COUNTRY_LIMITS%ISOPEN then
close CHK_LOCK_COUNTRY_LIMITS;
end if;
if CHK_LOCK_COMPANY_LIMITS%ISOPEN then
close CHK_LOCK_COMPANY_LIMITS;
end if;
if CHK_LOCK_COUNTERPARTY_LIMITS%ISOPEN then
close CHK_LOCK_COUNTERPARTY_LIMITS;
end if;
if CHK_LOCK_GROUP_LIMITS%ISOPEN then
close CHK_LOCK_GROUP_LIMITS;
end if;
raise app_exceptions.RECORD_LOCK_EXCEPTION;
--
end;
[Home] [Help]