[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Selection criteria for transactions : */
/* - only BOND deals with Benchmark Rate */
/* - Benchmark Rate is not null */
/* - RATE_FIXING_DATE is not null on transaction */
/* - RATE_FIXING_DATE within parameter date range */
/*-------------------------------------------------*/
CURSOR C_GET_ALL_BOND_ISSUES IS
select b.bond_issue_code,
b.rate_fixing_date,
b.coupon_date,
c.benchmark_rate,
nvl(c.float_margin,0)
FROM
XTR_BOND_COUPON_DATES b,
XTR_BOND_ISSUES c
WHERE b.bond_issue_code = c.bond_issue_code
AND b.bond_issue_code = NVL(p_bond_issue_code,b.bond_issue_code)
and b.rate_fixing_date between fnd_date.canonical_to_date(p_rateset_from)
and fnd_date.canonical_to_date(p_rateset_to)
and c.benchmark_rate is not null
and c.benchmark_rate = nvl(p_bench_mark, c.benchmark_rate)
and c.calc_type in ('FL IRREGULAR','FL REGULAR')
ORDER BY b.bond_issue_code,b.rate_fixing_date,b.coupon_date;
select 'Y'
from XTR_BOND_COUPON_DATES a
where a.bond_issue_code = l_bond_issue_code
and a.rate_fixing_date = l_ratefix_date
and a.rate_fixing_date < (
select max (b.rate_fixing_date)
FROM
XTR_BOND_COUPON_DATES b
WHERE b.bond_issue_code = l_bond_issue_code
and b.rate_fixing_date between fnd_date.canonical_to_date(p_rateset_from)
and fnd_date.canonical_to_date(p_rateset_to) );
select a.rate_fixing_date,
a.coupon_date
from XTR_BOND_COUPON_DATES a
where a.bond_issue_code = l_bond_issue_code
and a.rate_fixing_date >= l_ratefix_date
Order by a.rate_fixing_date;
if C_ONE_ROW_BOND%FOUND then -- update only one row. This is not the latest transaction of this deal number
close C_ONE_ROW_BOND;
UPDATE_BOND_DETAILS(l_bond_issue_code,l_coupon_date,
l_ratefix_date,l_new_rate,l_count1);
else --update current record as well as subsequent transactions
close C_ONE_ROW_BOND;
UPDATE_BOND_DETAILS(l_bond_issue_code,bond_info.coupon_date,
bond_info.rate_fixing_date,l_new_rate,l_count1);
select count(*)
from xtr_deals d, xtr_rollover_transactions rt
where d.deal_no=rt.deal_number
and d.bond_issue=p_bond_issue_code
and rt.maturity_date=p_coupon_date
and rt.interest<>rt.original_amount
and rt.interest>0;
PROCEDURE UPDATE_COUPON_DETAILS(p_bond_issue_code IN VARCHAR2,
p_coupon_date IN DATE,
p_new_rate IN NUMBER,
p_deal_number IN NUMBER,
p_transaction_number IN NUMBER,
p_update_type IN VARCHAR2) AS
CURSOR C_GET_SEQ_TRANSACTIONS IS
SELECT maturity_date,transaction_number
FROM XTR_ROLLOVER_TRANSACTIONS
WHERE deal_number = p_deal_number
AND transaction_number >= p_transaction_number
ORDER BY transaction_number;
IF p_update_type = 'SINGLE' THEN
UPDATE XTR_ROLLOVER_TRANSACTIONS
SET INTEREST_RATE=P_NEW_RATE,
UPDATED_ON = SYSDATE,
UPDATED_BY = fnd_global.user_id
WHERE STATUS_CODE = 'CURRENT'
AND MATURITY_DATE = P_COUPON_DATE
AND DEAL_NUMBER = P_DEAL_NUMBER;
UPDATE_COUPON_AMOUNT(p_bond_issue_code,p_deal_number,p_transaction_number);
UPDATE XTR_ROLLOVER_TRANSACTIONS
SET INTEREST_RATE=P_NEW_RATE,
UPDATED_ON = SYSDATE,
UPDATED_BY = fnd_global.user_id
WHERE STATUS_CODE = 'CURRENT'
AND MATURITY_DATE = l_COUPON_DATE
AND DEAL_NUMBER = P_DEAL_NUMBER;
UPDATE_COUPON_AMOUNT(p_bond_issue_code,p_deal_number,l_trans_no);
END UPDATE_COUPON_DETAILS;
PROCEDURE UPDATE_COUPON_AMOUNT(p_bond_issue_code IN VARCHAR2,
p_deal_number IN NUMBER,
p_transaction_number IN NUMBER
) AS
v_new_cpn_amt NUMBER;
select rt.deal_number,rt.transaction_number,rt.tax_settled_reference,
rt.currency,rt.deal_subtype,rt.tax_code,rt.tax_amount
from xtr_rollover_transactions rt
where rt.status_code='CURRENT'
and rt.deal_number = p_deal_number
and rt.transaction_number = p_transaction_number
and rt.deal_subtype in ('ISSUE','SHORT','BUY')
order by rt.deal_number,rt.transaction_number;
select nvl(round(p_amt/round(hce_rate,5),
rounding_factor),0)
from XTR_MASTER_CURRENCIES_V
where CURRENCY = p_ccy;
UPDATE xtr_rollover_transactions
set interest=v_new_cpn_amt,interest_hce=v_hce_amt,
tax_amount=v_tax_amt,original_amount=v_new_cpn_amt,
UPDATED_ON = sysdate,
UPDATED_BY = fnd_global.user_id
where deal_number=curr_trans_info.deal_number
and transaction_number=curr_trans_info.transaction_number;
UPDATE xtr_deal_date_amounts
set amount=nvl(v_new_cpn_amt_dda,0),
cashflow_amount=decode(curr_trans_info.deal_subtype,'BUY',1,-1)*nvl(v_new_cpn_amt_dda,0),
UPDATED_ON = sysdate,
UPDATED_BY = fnd_global.user_id
where deal_type='BOND'
and deal_number=curr_trans_info.deal_number
and transaction_number=curr_trans_info.transaction_number
and amount_type='COUPON' and date_type='COUPON';
UPDATE xtr_deal_date_amounts
set amount=v_tax_amt,
UPDATED_ON = sysdate,
UPDATED_BY = fnd_global.user_id
where deal_type='BOND'
and deal_number=curr_trans_info.deal_number
and transaction_number=curr_trans_info.transaction_number
and amount_type='TAX' and date_type='INCUR';
xtr_fps2_p.UPDATE_TAX_EXP (curr_trans_info.tax_settled_reference,
v_tax_diff);
xtr_fps2_p.UPDATE_TAX_DDA (curr_trans_info.tax_settled_reference,
v_tax_diff);
END UPDATE_COUPON_AMOUNT;
PROCEDURE UPDATE_BOND_DETAILS(p_bond_issue_code IN VARCHAR2,
p_coupon_date IN DATE,
p_ratefix_date DATE,
p_new_rate IN NUMBER,
p_count OUT NOCOPY NUMBER) AS
CURSOR C_GET_RELATED_TRANS(p_bond_issue_code VARCHAR2,
p_coupon_date DATE) is
select rt.deal_number,rt.transaction_number
from xtr_rollover_transactions rt, xtr_deals d
where rt.status_code='CURRENT'
and d.deal_no=rt.deal_number
and rt.deal_subtype in ('ISSUE','SHORT','BUY')
and d.bond_issue=p_bond_issue_code
and rt.maturity_date=p_coupon_date
order by rt.deal_number,rt.transaction_number;
SELECT maturity_balance_amount
FROM XTR_DEALS
WHERE DEAL_NO = p_deal;
SELECT max(cross_ref_start_date)
FROM XTR_BOND_ALLOC_DETAILS
WHERE DEAL_NO = p_deal_no;
UPDATE XTR_BOND_COUPON_DATES
SET RATE = p_new_rate
WHERE BOND_ISSUE_CODE = p_bond_issue_code
AND COUPON_DATE = p_coupon_date;
UPDATE_COUPON_DETAILS(p_bond_issue_code,
p_coupon_date,
p_new_rate,
trans_info.deal_number,
trans_info.transaction_number,
'SINGLE');
FND_MESSAGE.SET_NAME('XTR', 'XTR_UPDATE_BENCH_RATE_BOND');
END UPDATE_BOND_DETAILS;