[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nvl(c.LIMIT_WEIGHTING / 100,1)
from XTR_FX_PERIOD_WEIGHTINGS c
where c.DEAL_TYPE = pc_deal_type
and c.DEAL_SUBTYPE = pc_deal_subtype
and c.NOS_MONTHS =
(select max(d.NOS_MONTHS)
from XTR_FX_PERIOD_WEIGHTINGS d
where d.DEAL_TYPE = c.DEAL_TYPE
and d.DEAL_SUBTYPE = c.DEAL_SUBTYPE
and d.NOS_MONTHS <= decode(sign(months_between(trunc(pc_amount_date),trunc(sysdate)))
,-1,0
,months_between(trunc(pc_amount_date),trunc(sysdate))));
select round((pc2_amount / nvl(s.HCE_RATE ,1)),nvl(s.ROUNDING_FACTOR,2))
from XTR_MASTER_CURRENCIES_V s
where s.currency = pc2_currency;
select round((pc2_amount / nvl(s.HCE_RATE ,1)),nvl(s.ROUNDING_FACTOR,2))
from XTR_MASTER_CURRENCIES_V s
where s.currency = pc2_currency;
select upper(country_code),upper(nvl(cross_ref_to_other_party,pc_party_code))
from XTR_PARTY_INFO
where party_code = pc_party_code;
select upper(limit_type)
from XTR_COMPANY_LIMITS
where limit_code = pc_limit_code
and company_code = pc_comp_code;
select param_value
from XTR_PRO_PARAM
where param_name = 'SYSTEM_FUNCTIONAL_CCY';
select sum(nvl(hce_utilised_amount,0))
from XTR_MIRROR_DDA_LIMIT_ROW
where deal_number = pc_deal_number
and limit_code = p_limit_code
and limit_party = p_cparty_code
and currency = p_currency
and company_code = p_company_code;
select sum(nvl(hce_utilised_amount,0))
from XTR_MIRROR_DDA_LIMIT_ROW
where deal_number = pc_deal_number
and transaction_number = pc_trans_number
and limit_code = p_limit_code
and limit_party = p_cparty_code
and currency = p_currency
and company_code = p_company_code;
select XTR_LIMITS_EXCESS_LOG_S.nextval
from DUAL;
select param_value
from XTR_PRO_PARAM
where param_name = pc3_param_name;
select range
from XTR_TIME_RESTRICTIONS
where deal_type = p_deal_type
and (cparty_code like p_cparty_code or cparty_code is null)
and (deal_subtype like p_deal_subtype or deal_subtype is null)
and (security_name like p_product_type or security_name is null)
order by cparty_code,security_name,deal_subtype;
SELECT substr(param_name,13) limit_check
FROM xtr_pro_param_v
WHERE param_name in ('LIMIT_CHECK_CPARTY','LIMIT_CHECK_GROUP','LIMIT_CHECK_SOVRN')
AND nvl(param_value,'N') = 'Y';
INSERT into XTR_limit_excess_log
(LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
COMPANY_CODE,LIMIT_CODE,
LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
VALUES
(v_unique_num,-1,null,
v_company_code, p_limit_code,
p_limit_party, p_amount_date,nvl(v_hce_amount,0),NULL,
NULL, NULL, p_dealer_code,
v_limit_check_type,v_err_code,sysdate);
INSERT into XTR_limit_excess_log
(LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
COMPANY_CODE,LIMIT_CODE,
LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
VALUES
(v_unique_num,-1,null,
v_company_code, p_limit_code,
p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
v_util_amt - v_limit_amt, NULL, p_dealer_code,
'GLOBAL','EXCEEDED',sysdate);
INSERT into XTR_limit_excess_log
(LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
COMPANY_CODE,LIMIT_CODE,
LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
VALUES
(v_unique_num,-1,null,
v_company_code, p_limit_code,
p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
v_util_amt - v_limit_amt, NULL, p_dealer_code,
'GLOBAL',v_err_code,sysdate);
INSERT into XTR_limit_excess_log
(LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
COMPANY_CODE,LIMIT_CODE,
LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
VALUES
(v_unique_num,-1,null,
v_company_code, p_limit_code,
p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
v_util_amt - v_limit_amt, NULL, p_dealer_code,
'SOVRN','EXCEEDED',sysdate);
INSERT into XTR_limit_excess_log
(LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
COMPANY_CODE,LIMIT_CODE,
LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
VALUES
(v_unique_num,-1,null,
v_company_code, p_limit_code,
p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
v_util_amt - v_limit_amt, NULL, p_dealer_code,
'SOVRN',v_err_code,sysdate);
INSERT into XTR_limit_excess_log
(LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
COMPANY_CODE,LIMIT_CODE,
LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
VALUES
(v_unique_num,-1,null,
v_company_code, p_limit_code,
p_limit_party, p_amount_date,v_gross_amt,v_limit_amt,
v_gross_amt - v_limit_amt, NULL, p_dealer_code,
'DLR_DEAL','EXCEEDED',sysdate);
INSERT into XTR_limit_excess_log
(LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
COMPANY_CODE,LIMIT_CODE,
LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
VALUES
(v_unique_num,-1,null,
v_company_code, p_limit_code,
p_limit_party, p_amount_date,v_gross_amt,NULL,
NULL, NULL, p_dealer_code,
'DLR_DEAL',v_err_code,sysdate);
INSERT into XTR_limit_excess_log
(LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
COMPANY_CODE,LIMIT_CODE,
LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
VALUES
(v_unique_num,-1,null,
v_company_code, p_limit_code,
p_limit_party, p_amount_date,v_gross_amt,v_limit_amt,
v_gross_amt - v_limit_amt, NULL, p_dealer_code,
'DLR_DEAL',v_err_code,sysdate);
INSERT into XTR_limit_excess_log
(LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
COMPANY_CODE,LIMIT_CODE,
LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
VALUES
(v_unique_num,-1,null,
v_company_code, p_limit_code,
p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
v_util_amt - v_limit_amt, NULL, p_dealer_code,
'CPARTY','EXCEEDED',sysdate);
INSERT into XTR_limit_excess_log
(LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
COMPANY_CODE,LIMIT_CODE,
LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
VALUES
(v_unique_num,-1,null,
v_company_code, p_limit_code,
p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
v_util_amt - v_limit_amt, NULL, p_dealer_code,
'CPARTY',v_err_code,sysdate);
SELECT COUNT(*)
INTO v_dummy
FROM XTR_PRO_PARAM
WHERE PARAM_NAME='LIMIT_INCLUDE_PAYMENTS'
AND PARAM_VALUE='N';
INSERT into XTR_limit_excess_log
(LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
COMPANY_CODE,LIMIT_CODE,
LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
VALUES
(v_unique_num,-1,null,
v_company_code, p_limit_code,
p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
v_util_amt - v_limit_amt, NULL, p_dealer_code,
'SETTLE',v_settle_warn,sysdate);
INSERT into XTR_limit_excess_log
(LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
COMPANY_CODE,LIMIT_CODE,
LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
VALUES
(v_unique_num,-1,null,
v_company_code, p_limit_code,
p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
v_util_amt - v_limit_amt, NULL, p_dealer_code,
'SETTLE',v_err_code,sysdate);
INSERT into XTR_limit_excess_log
(LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
COMPANY_CODE,LIMIT_CODE,
LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE, CURRENCY) -- bug 1289530
VALUES
(v_unique_num,-1,null,
v_company_code, p_limit_code,
p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
v_util_amt - v_limit_amt, NULL, p_dealer_code,
'CCY','EXCEEDED',sysdate, p_currency); -- bug 1289530
INSERT into XTR_limit_excess_log
(LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
COMPANY_CODE,LIMIT_CODE,
LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE, CURRENCY) -- bug 1289530
VALUES
(v_unique_num,-1,null,
v_company_code, p_limit_code,
p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
v_util_amt - v_limit_amt, NULL, p_dealer_code,
'CCY',v_err_code,sysdate, p_currency); -- bug 1289530
INSERT into XTR_limit_excess_log
(LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
COMPANY_CODE,LIMIT_CODE,
LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE, CURRENCY) -- bug 1289530
VALUES
(v_unique_num,-1,null,
v_company_code, p_limit_code,
p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
v_util_amt - v_limit_amt, NULL, p_dealer_code,
'CCY','EXCEEDED',sysdate, p_currency_second); -- bug 1289530
INSERT into XTR_limit_excess_log
(LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
COMPANY_CODE,LIMIT_CODE,
LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE, CURRENCY) -- bug 1289530
VALUES
(v_unique_num,-1,null,
v_company_code, p_limit_code,
p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
v_util_amt - v_limit_amt, NULL, p_dealer_code,
'CCY',v_err_code,sysdate, p_currency_second); -- bug 1289530
INSERT into XTR_limit_excess_log
(LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
COMPANY_CODE,LIMIT_CODE,
LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
VALUES
(v_unique_num,-1,null,
v_company_code, p_limit_code,
v_group_party, p_amount_date,v_hce_amount,v_limit_amt,
v_util_amt - v_limit_amt, NULL, p_dealer_code,
'GROUP','EXCEEDED',sysdate);
INSERT into XTR_limit_excess_log
(LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
COMPANY_CODE,LIMIT_CODE,
LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
VALUES
(v_unique_num,-1,null,
v_company_code, p_limit_code,
v_group_party, p_amount_date,v_hce_amount,v_limit_amt,
v_util_amt - v_limit_amt, NULL, p_dealer_code,
'GROUP',v_err_code,sysdate);
INSERT into XTR_limit_excess_log
(LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
COMPANY_CODE,LIMIT_CODE,
LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
VALUES
(v_unique_num,-1,null,
v_company_code,null,p_cparty_code,p_amount_date,0,null,
((trunc(p_amount_date) - trunc(sysdate)) - v_time_limit),
null,p_dealer_code,'TIME','EXCEEDED',sysdate);
INSERT into XTR_limit_excess_log
(LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
COMPANY_CODE,LIMIT_CODE,
LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
VALUES
(v_unique_num,-1,null,
v_company_code, p_limit_code,
p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
v_util_amt - v_limit_amt, NULL, p_dealer_code,
'INTRA_DY','EXCEEDED',sysdate);
INSERT into XTR_limit_excess_log
(LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
COMPANY_CODE,LIMIT_CODE,
LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
VALUES
(v_unique_num,-1,null,
v_company_code, p_limit_code,
p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
v_util_amt - v_limit_amt, NULL, p_dealer_code,
'INTRA_DY',v_err_code,sysdate);
PROCEDURE UPDATE_LIMIT_EXCESS_LOG(p_deal_no IN NUMBER,
p_trans_no IN NUMBER,
p_user IN VARCHAR2,
p_log_id IN NUMBER) is
BEGIN
update XTR_LIMIT_EXCESS_LOG_V
set deal_number = p_deal_no,
transaction_number = p_trans_no,
authorised_by = p_user
where log_id = p_log_id;
END UPDATE_LIMIT_EXCESS_LOG;
cursor CHK_UPDATE is
select 1
from XTR_PRO_PARAM
where PARAM_NAME = 'LAST_LIMIT_RECALC'
and to_date(nvl(PARAM_VALUE,'01/01/1990'),'DD/MM/YYYY') < trunc(sysdate);
select COMPANY_CODE,CPARTY_CODE,LIMIT_CODE,LIMIT_PARTY,AMOUNT_DATE,AMOUNT,
HCE_AMOUNT,DEALER_CODE,DEAL_NUMBER,DEAL_TYPE,
TRANSACTION_NUMBER,DEAL_SUBTYPE,PORTFOLIO_CODE,STATUS_CODE,
PRODUCT_TYPE,CURRENCY
from XTR_DEAL_DATE_AMOUNTS
where LIMIT_CODE is NOT NULL
and STATUS_CODE = 'CURRENT'
and ((AMOUNT_DATE >= trunc(sysdate) and DEAL_TYPE NOT IN('ONC','CMF')) or
(AMOUNT_DATE >= trunc(sysdate) and DEAL_TYPE IN('ONC','CMF')
and PRODUCT_TYPE = 'FIXED') or
(DEAL_TYPE IN('ONC','CMF') and PRODUCT_TYPE <> 'FIXED'));
select country_code
from XTR_PARTY_INFO
where party_code = pc_party_code;
select amount,rowid row_id,DEAL_TYPE,DEAL_SUBTYPE,AMOUNT_DATE,COMPANY_CODE,CURRENCY
from XTR_MIRROR_DDA_LIMIT_ROW
for update of amount;
select param_value from xtr_pro_param where
param_name = 'RELEASE_LIMIT_UTIL';
open CHK_UPDATE;
fetch CHK_UPDATE INTO l_dummy;
if CHK_UPDATE%FOUND or p_auto_recalc = 'Y' then
IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
xtr_debug_pkg.debug('Before delete xtr_mirror_dda_limit_row on:'||to_char(sysdate,'MM:DD:HH24:MI:SS'));
delete from XTR_mirror_dda_limit_row
where amount_date < L_SYS_DATE
and deal_type not in('ONC','CA','IG', 'STOCK');
xtr_debug_pkg.debug('After delete xtr_mirror_dda_limit_row('||to_char(sql%rowcount)||') on:'||to_char(sysdate,'MM:DD:HH24:MI:SS'));
update XTR_mirror_dda_limit_row
set hce_amount = l_new_hce_amt,
hce_utilised_amount = l_new_hce_utilamt,
utilised_amount = l_new_utilamt
where rowid = c.row_id;
xtr_debug_pkg.debug('After update xtr_mirror_dda_limit_row('||c.row_id||') on:'||to_char(sysdate,'MM:DD:HH24:MI:SS'));
update XTR_PRO_PARAM
set PARAM_VALUE = to_char(sysdate,'DD/MM/YYYY')
where PARAM_NAME = 'LAST_LIMIT_RECALC';
close CHK_UPDATE;
PROCEDURE update_weightings(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER) IS
BEGIN
-- Pass as 'Y' to force the update to occur
calc_all_mirror_dda_limit_row('Y');
END update_weightings;
select limit_type
from XTR_company_limits
where company_code = p_new_company_code
and limit_code = p_new_limit_code;
select country_code,nvl(cross_ref_to_other_party,pc_party_code)
from XTR_PARTY_INFO
where party_code = pc_party_code;
if p_action <> 'DELETE' and (p_new_status_code = 'CURRENT'
OR (p_old_limit_code is not null and p_new_limit_code is not null and p_new_status_code IN ('EXERCISED','SETTLED','CANCELLED'))) then
-- Calculate weighted usage for this data.
v_utilised_amount := XTR_LIMITS_P.WEIGHTED_USAGE(p_new_deal_type,
p_new_deal_subtype,
p_new_amount_date,
p_new_amount);
if p_action = 'UPDATE' then
-- old lim code | new lim code | action
-- ++++++++++++++++++++++++++++++++++++++
-- null | null | does not reach here
-- null | X | INSERT
-- X | null | DELETE (status = 'CLOSED') (no reverse cof)
-- X | Y | UPDATE
--
-- Remember: to get this far, at least one of old/new limit code is NOT null.
if p_old_limit_code is null then
update XTR_mirror_dda_limit_row
set PRODUCT_TYPE =p_new_product_type,
COMPANY_CODE = p_new_COMPANY_CODE,
LIMIT_CODE =p_new_LIMIT_CODE,
LIMIT_PARTY =p_new_LIMIT_PARTY,
AMOUNT_DATE =p_new_AMOUNT_DATE,
AMOUNT =p_new_AMOUNT,
HCE_AMOUNT =v_hce_amt,
DEALER_CODE =p_new_DEALER_CODE,
COUNTRY_CODE =v_country_code,
DEAL_TYPE =p_new_DEAL_TYPE,
DEAL_SUBTYPE =p_new_DEAL_SUBTYPE,
PORTFOLIO_CODE =p_new_PORTFOLIO_CODE,
STATUS_CODE =p_new_STATUS_CODE,
UTILISED_AMOUNT = v_utilised_amount,
DATE_LAST_SET = sysdate,
HCE_UTILISED_AMOUNT = v_hce_utilised_amt,
CURRENCY = p_new_currency,
AMOUNT_INDIC = v_amount_indic,
TRANSACTION_RATE = p_transaction_rate,
CONTRA_CCY = v_contra_ccy,
CURRENCY_COMBINATION = p_currency_combination,
COMMENCE_DATE = l_commence_date,
ACCOUNT_NO = p_account_no,
CROSS_REF_TO_OTHER_PARTY = v_group_party,
LIMIT_TYPE = v_limit_type
where deal_number = p_new_deal_number
and transaction_number = p_new_transaction_number ;
if SQL%NOTFOUND then -- No row was updated.
insert into XTR_mirror_dda_limit_row
(COMPANY_CODE,LIMIT_CODE,LIMIT_PARTY,AMOUNT_DATE,AMOUNT,
HCE_AMOUNT,DEALER_CODE,COUNTRY_CODE,DEAL_NUMBER,DEAL_TYPE,
TRANSACTION_NUMBER,DEAL_SUBTYPE,PORTFOLIO_CODE,STATUS_CODE,
UTILISED_AMOUNT,PRODUCT_TYPE,DATE_LAST_SET,HCE_UTILISED_AMOUNT,CURRENCY,
AMOUNT_INDIC,TRANSACTION_RATE,CONTRA_CCY,CURRENCY_COMBINATION,ACCOUNT_NO,
COMMENCE_DATE,CROSS_REF_TO_OTHER_PARTY,LIMIT_TYPE)
values
(p_new_COMPANY_CODE,p_new_LIMIT_CODE,p_new_LIMIT_PARTY,p_new_AMOUNT_DATE,p_new_AMOUNT,
v_hce_amt,p_new_DEALER_CODE,v_country_code,p_new_DEAL_NUMBER,p_new_DEAL_TYPE,
p_new_TRANSACTION_NUMBER,p_new_DEAL_SUBTYPE,p_new_PORTFOLIO_CODE,p_new_STATUS_CODE,
v_utilised_amount,p_new_product_type,sysdate,v_hce_utilised_amt,p_new_currency,
v_amount_indic,p_transaction_rate,v_contra_ccy,p_currency_combination,p_account_no,
l_commence_date,v_group_party,v_limit_type);
delete from XTR_mirror_dda_limit_row
where deal_number = p_old_deal_number
and transaction_number = p_old_transaction_NUMBER;
update XTR_mirror_dda_limit_row
set PRODUCT_TYPE =p_new_product_type,
COMPANY_CODE = p_new_COMPANY_CODE,
LIMIT_CODE =p_new_LIMIT_CODE,
LIMIT_PARTY =p_new_LIMIT_PARTY,
AMOUNT_DATE =p_new_AMOUNT_DATE,
AMOUNT =p_new_AMOUNT,
HCE_AMOUNT =v_hce_amt,
DEALER_CODE =p_new_DEALER_CODE,
COUNTRY_CODE =v_country_code,
DEAL_TYPE =p_new_DEAL_TYPE,
DEAL_SUBTYPE =p_new_DEAL_SUBTYPE,
PORTFOLIO_CODE =p_new_PORTFOLIO_CODE,
STATUS_CODE =p_new_STATUS_CODE,
UTILISED_AMOUNT = v_utilised_amount,
DATE_LAST_SET = sysdate,
HCE_UTILISED_AMOUNT = v_hce_utilised_amt,
CURRENCY = p_new_currency,
AMOUNT_INDIC = v_amount_indic,
TRANSACTION_RATE = p_transaction_rate,
CONTRA_CCY = v_contra_ccy,
CURRENCY_COMBINATION = p_currency_combination,
COMMENCE_DATE = l_commence_date,
ACCOUNT_NO = p_account_no,
CROSS_REF_TO_OTHER_PARTY = v_group_party,
LIMIT_TYPE = v_limit_type
where deal_number = p_new_deal_number
and transaction_number = p_new_transaction_number ;
if SQL%NOTFOUND then -- No row was updated.
insert into XTR_mirror_dda_limit_row
(COMPANY_CODE,LIMIT_CODE,LIMIT_PARTY,AMOUNT_DATE,AMOUNT,
HCE_AMOUNT,DEALER_CODE,COUNTRY_CODE,DEAL_NUMBER,DEAL_TYPE,
TRANSACTION_NUMBER,DEAL_SUBTYPE,PORTFOLIO_CODE,STATUS_CODE,
UTILISED_AMOUNT,PRODUCT_TYPE,DATE_LAST_SET,HCE_UTILISED_AMOUNT,CURRENCY,
AMOUNT_INDIC,TRANSACTION_RATE,CONTRA_CCY,CURRENCY_COMBINATION,ACCOUNT_NO,
COMMENCE_DATE,CROSS_REF_TO_OTHER_PARTY,LIMIT_TYPE)
values
(p_new_COMPANY_CODE,p_new_LIMIT_CODE,p_new_LIMIT_PARTY,p_new_AMOUNT_DATE,p_new_AMOUNT,
v_hce_amt,p_new_DEALER_CODE,v_country_code,p_new_DEAL_NUMBER,p_new_DEAL_TYPE,
p_new_TRANSACTION_NUMBER,p_new_DEAL_SUBTYPE,p_new_PORTFOLIO_CODE,p_new_STATUS_CODE,
v_utilised_amount,p_new_product_type,sysdate,v_hce_utilised_amt,p_new_currency,
v_amount_indic,p_transaction_rate,v_contra_ccy,p_currency_combination,p_account_no,
l_commence_date,v_group_party,v_limit_type);
elsif p_action = 'INSERT' then
insert into XTR_mirror_dda_limit_row
(COMPANY_CODE,LIMIT_CODE,LIMIT_PARTY,AMOUNT_DATE,AMOUNT,
HCE_AMOUNT,DEALER_CODE,COUNTRY_CODE,DEAL_NUMBER,DEAL_TYPE,
TRANSACTION_NUMBER,DEAL_SUBTYPE,PORTFOLIO_CODE,STATUS_CODE,
UTILISED_AMOUNT,PRODUCT_TYPE,DATE_LAST_SET,HCE_UTILISED_AMOUNT,CURRENCY,
AMOUNT_INDIC,TRANSACTION_RATE,CONTRA_CCY,CURRENCY_COMBINATION,ACCOUNT_NO,
COMMENCE_DATE,CROSS_REF_TO_OTHER_PARTY,LIMIT_TYPE)
values
(p_new_COMPANY_CODE,p_new_LIMIT_CODE,p_new_LIMIT_PARTY,p_new_AMOUNT_DATE,p_new_AMOUNT,
v_hce_amt,p_new_DEALER_CODE,v_country_code,p_new_DEAL_NUMBER,p_new_DEAL_TYPE,
p_new_TRANSACTION_NUMBER,p_new_DEAL_SUBTYPE,p_new_PORTFOLIO_CODE,p_new_STATUS_CODE,
v_utilised_amount,p_new_product_type,sysdate,v_hce_utilised_amt,p_new_currency,
v_amount_indic,p_transaction_rate,v_contra_ccy,p_currency_combination,p_account_no,
l_commence_date,v_group_party,v_limit_type);
elsif p_action = 'DELETE' then
delete from XTR_mirror_dda_limit_row
where deal_number = p_old_deal_number
and transaction_number = p_old_transaction_number ;
SELECT nvl(limit_amount,0), nvl(utilised_amount,0)
FROM xtr_company_limits
WHERE company_code = pc1_company_code
AND limit_code = pc1_limit_code
AND (limit_amount <> 0 OR utilised_amount <> 0);
select nvl(param_value,'Y')
from XTR_PRO_PARAM
where param_name = pc3_param_name;
SELECT nvl(limit_amount,0), nvl(utilised_amount,0)
FROM xtr_group_limits
WHERE company_code = pc1_company_code
AND cparty_code = pc1_limit_party
AND ((limit_type = pc1_limit_type and limit_type <>'XI')
or (limit_type='XI' and pc1_limit_type in('X','I')))
AND (limit_amount <> 0 OR utilised_amount <> 0);
select nvl(param_value,'Y')
from XTR_PRO_PARAM
where param_name = pc3_param_name;
select fx_invest_fund_type
from XTR_LIMIT_TYPES
where limit_type=p_limit_type;
SELECT nvl(limit_amount,0), nvl(utilised_amount,0)
FROM xtr_country_company_limits
WHERE company_code = pc1_company_code
AND country_code = pc1_country_code
AND (limit_amount <> 0 OR utilised_amount <> 0);
select nvl(param_value,'Y')
from XTR_PRO_PARAM
where param_name = pc3_param_name;
select single_deal_limit_amount,authorised
from XTR_DEALER_LIMITS
where dealer_code = pc2_dealer_code
and deal_type = pc2_deal_type
and (product_type = pc2_product_type or product_type is NULL)
order by product_type;
select nvl(param_value,'Y')
from XTR_PRO_PARAM
where param_name = pc3_param_name;
select nvl(sum(decode(deal_subtype,'BUY',HCE_AMOUNT,'SELL',-HCE_AMOUNT,0)),0)
from XTR_mirror_dda_limit_row
where dealer_code = pc1_dealer_code
and deal_type = pc1_deal_type;
select limit_amount, authorised
from XTR_intra_day_limits
where dealer_code = pc2_dealer_code
and deal_type = pc2_deal_type;
select nvl(param_value,'Y')
from XTR_pro_param
where param_name = pc3_param_name;
SELECT nvl(cl.limit_amount,0), nvl(cl.utilised_amount,0)
FROM xtr_counterparty_limits cl, xtr_parties_v p
WHERE cl.company_code = pc1_company_code
AND cl.cparty_code = pc1_cparty_code
AND cl.limit_code = pc1_limit_code
AND cl.limit_code <> 'SETTLE'
AND cl.cparty_code = p.party_code||''
AND (cl.limit_amount <> 0 OR cl.utilised_amount <> 0);
select nvl(param_value,'Y')
from XTR_pro_param
where param_name = pc3_param_name;
select nvl(max(B.LIMIT_AMOUNT),0) LIMIT_AMT,
nvl(sum(round(abs(A.CASHFLOW_AMOUNT) / M.HCE_RATE,0)),0) UTILISED
from XTR_DEAL_DATE_AMOUNTS A,
XTR_COUNTERPARTY_LIMITS B,
XTR_MASTER_CURRENCIES M
where A.ACTUAL_SETTLEMENT_DATE = pc1_amount_date
and A.SETTLE = 'Y'
and A.COMPANY_CODE = pc1_company_code
and A.AMOUNT_TYPE NOT IN ('FXOBUY','FXOSELL')
and A.EXP_SETTLE_REQD = 'Y'
and NVL(A.MULTIPLE_SETTLEMENTS,'N') = 'N'
and A.DEAL_SUBTYPE <> 'INDIC'
and nvl(A.BENEFICIARY_PARTY,A.CPARTY_CODE) = pc1_limit_party
and B.CPARTY_CODE = A.CPARTY_CODE
and B.COMPANY_CODE = A.COMPANY_CODE
and B.LIMIT_CODE = 'SETTLE'
and M.CURRENCY = A.CURRENCY
and (
(v_include_payments='N' and A.CASHFLOW_AMOUNT>0)
or
(v_include_payments='Y')
);
select nvl(param_value,'Y')
from XTR_PRO_PARAM
where param_name = pc3_param_name;
select nvl(limit_amount,0)
from XTR_COUNTERPARTY_LIMITS
where cparty_code = pc1_limit_party
and company_code = pc1_company_code
and limit_code = 'SETTLE';
SELECT nvl(max(cl.limit_amount),0),
nvl(sum(round(abs(s.cashflow_amount)/mc.hce_rate,0)),0)
FROM xtr_settlements_v s,
xtr_counterparty_limits cl,
xtr_master_currencies mc
WHERE s.amount_date > trunc(sysdate)
AND cl.cparty_code = s.cparty
AND cl.company_code = s.company
AND cl.limit_code = 'SETTLE'
AND mc.currency = s.currency
AND s.amount_date = pc1_amount_date
AND s.cparty = pc1_limit_party
AND s.company = pc1_company_code
AND (
(v_include_payments='N' and S.CASHFLOW_AMOUNT>0)
OR
(v_include_payments='Y')
)
GROUP BY s.cparty, s.company, s.amount_date;
SELECT nvl(max(cl.limit_amount),0),
nvl(sum(sru.utilised_amount),0)
FROM (
SELECT nvl(SUM(ROUND(ABS(a.CASHFLOW_AMOUNT) / m.HCE_RATE,0)),0) UTILISED_AMOUNT,
a.CPARTY CPARTY_CODE,
a.COMPANY COMPANY_CODE,
nvl(a.AMOUNT_DATE,TRUNC(SYSDATE)) EFFECTIVE_DATE
FROM XTR_SETTLEMENTS_V a, XTR_MASTER_CURRENCIES m
WHERE a.AMOUNT_DATE = trunc(SYSDATE)
AND a.CASHFLOW_AMOUNT <> 0
AND m.CURRENCY = a.CURRENCY
AND (
(v_include_payments='N' and A.CASHFLOW_AMOUNT>0)
OR
(v_include_payments='Y')
)
GROUP by a.CPARTY,a.COMPANY,nvl(a.AMOUNT_DATE,trunc(SYSDATE))
) sru,
xtr_counterparty_limits cl
WHERE cl.cparty_code = sru.cparty_code(+)
AND cl.company_code = sru.company_code(+)
AND cl.limit_code = 'SETTLE'
AND nvl(sru.effective_date,trunc(sysdate)) = pc1_amount_date
AND cl.cparty_code = pc1_limit_party
AND cl.company_code = pc1_company_code
GROUP BY cl.cparty_code, cl.company_code, sru.effective_date;
select param_value
from XTR_PRO_PARAM
where param_name = pc3_param_name;
select nvl(limit_amount,0)
from XTR_COUNTERPARTY_LIMITS
where cparty_code = pc1_limit_party
and company_code = pc1_company_code
and limit_code = 'SETTLE'
and limit_amount <> 0;
SELECT nvl(mc.net_fx_exposure,0), nvl(mc.utilised_amount,0)
FROM xtr_master_currencies mc
WHERE mc.authorised = 'Y'
AND (mc.net_fx_exposure <> 0 OR mc.utilised_amount <> 0)
/* bug 1289530 limit check should be done for home ccy
AND mc.currency <> (SELECT max(param_value)
FROM xtr_pro_param p
WHERE p.param_name='SYSTEM_FUNCTIONAL_CCY')
bug 1289530 */
AND mc.currency = p_currency;
select nvl(param_value,'Y')
from XTR_PRO_PARAM
where param_name = pc3_param_name;
delete from XTR_LIMIT_EXCESS_LOG
where LOG_ID = p_log_id;
update XTR_LIMIT_EXCESS_LOG
set AUTHORISED_BY = p_user
where LOG_ID = p_log_id;
delete from xtr_MIRROR_DDA_LIM_ROW_TMP_V;
update xtr_COMPANY_LIMITS_V
set UTILISED_AMOUNT = 0;
update xtr_COUNTERPARTY_LIMITS_V
set UTILISED_AMOUNT = 0;
update xtr_MASTER_CURRENCIES
set UTILISED_AMOUNT = 0;
update xtr_COUNTRY_COMPANY_LIMITS_V
set UTILISED_AMOUNT = 0;
update xtr_GROUP_LIMITS_V
set UTILISED_AMOUNT = 0;
insert into xtr_MIRROR_DDA_LIM_ROW_TMP_V (
DEAL_NUMBER,DEAL_TYPE,TRANSACTION_NUMBER,LIMIT_CODE,
AMOUNT,HCE_AMOUNT,DATE_LAST_SET,PRODUCT_TYPE,COMPANY_CODE,
LIMIT_PARTY,AMOUNT_DATE,DEALER_CODE,COUNTRY_CODE,
DEAL_SUBTYPE,PORTFOLIO_CODE,STATUS_CODE,CURRENCY,
UTILISED_AMOUNT,HCE_UTILISED_AMOUNT,CROSS_REF_TO_OTHER_PARTY,
LIMIT_TYPE,COMMENCE_DATE,CURRENCY_COMBINATION,CONTRA_CCY,
TRANSACTION_RATE,AMOUNT_INDIC,ACCOUNT_NO)
select
DEAL_NUMBER,DEAL_TYPE,TRANSACTION_NUMBER,LIMIT_CODE,
AMOUNT,HCE_AMOUNT,DATE_LAST_SET,PRODUCT_TYPE,COMPANY_CODE,
LIMIT_PARTY,AMOUNT_DATE,DEALER_CODE,COUNTRY_CODE,
DEAL_SUBTYPE,PORTFOLIO_CODE,STATUS_CODE,CURRENCY,
UTILISED_AMOUNT,HCE_UTILISED_AMOUNT,CROSS_REF_TO_OTHER_PARTY,
LIMIT_TYPE,COMMENCE_DATE,CURRENCY_COMBINATION,CONTRA_CCY,
TRANSACTION_RATE,AMOUNT_INDIC,ACCOUNT_NO
from xtr_MIRROR_DDA_LIMIT_ROW_V;
delete from xtr_MIRROR_DDA_LIM_ROW_TMP_V;