[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select last_archive_time,archive_freq_type,freq_archive
from XTR_MARKET_PRICES
where (currency_a = quoted_1st or currency_b = quoted_1st)
and (currency_a = 'USD' or currency_b = 'USD')
and term_type = 'S';
select sr1.currency tmp_currency_first,
decode(sr2.currency_a,'USD',sr2.currency_b,sr2.currency_a)
tmp_currency_second,
decode(sr2.currency_a,'USD',sr2.bid_price ,(1/sr2.ask_price)) /
decode(sr3.currency_a, 'USD', sr3.ask_price, (1/sr3.bid_price))
tmp_bid_rate,
decode(sr2.currency_a,'USD',sr2.ask_price, (1/sr2.bid_price)) /
decode(sr3.currency_a, 'USD', sr3.bid_price, (1/sr3.ask_price))
tmp_offer_rate,
nvl(sr1.rate_date,sr1.spot_date) tmp_rate_date
from XTR_MASTER_CURRENCIES sr1,
XTR_MARKET_PRICES sr2,
XTR_MARKET_PRICES sr3
where sr1.currency = quoted_1st
and (sr2.currency_a = 'USD' or sr2.currency_b = 'USD')
and (sr3.currency_a = 'USD' or sr3.currency_b = 'USD')
and sr2.currency_a <> quoted_1st
and sr2.currency_b <> quoted_1st
and (sr3.currency_a = quoted_1st or sr3.currency_b= quoted_1st)
and sr2.term_type = 'S'
and sr3.term_type = 'S';
select CURRENCY_FIRST,CURRENCY_SECOND
from XTR_BUY_SELL_COMBINATIONS
where ((CURRENCY_BUY = ccy1 and
CURRENCY_SELL = ccy2) or
(CURRENCY_BUY = ccy2 and
CURRENCY_SELL = ccy1));
insert into XTR_CURRENCY_CROSS_RATES
(rate_date,currency_first,currency_second,bid_rate,offer_rate)
values
(nvl(spot.tmp_rate_date,SYSDATE),ccya,ccyb,
l_bid_rate,l_offer_rate);
select 'Y'
from XTR_DEAL_DATE_AMOUNTS_V
where AMOUNT_DATE = l_date
and COMPANY_CODE like nvl(l_company,'%')
and DEAL_TYPE like nvl(l_d_type,'%')
and DEAL_SUBTYPE like nvl(l_d_subty,'%')
and DEALER_CODE like nvl(l_dealer,'%');
select a.PARTY_CODE, a.SHORT_NAME, d.CURRENCY_CODE, b.NAME
from XTR_PARTIES_V a,
XTR_MASTER_CURRENCIES b,
HR_LEGAL_ENTITIES c,
GL_SETS_OF_BOOKS d
where a.PARTY_TYPE = 'C'
and a.DEFAULT_COMPANY = 'Y'
and c.ORGANIZATION_ID = a.LEGAL_ENTITY_ID
and c.SET_OF_BOOKS_ID = d.SET_OF_BOOKS_ID
and b.CURRENCY = d.CURRENCY_CODE;
select d.CURRENCY_CODE, b.NAME
from XTR_PARTIES_V a,
XTR_MASTER_CURRENCIES b,
HR_LEGAL_ENTITIES c,
GL_SETS_OF_BOOKS d
where a.PARTY_TYPE = 'C'
and a.PARTY_CODE = l_comp
and c.ORGANIZATION_ID = a.LEGAL_ENTITY_ID
and c.SET_OF_BOOKS_ID = d.SET_OF_BOOKS_ID
and b.CURRENCY = d.CURRENCY_CODE;
select a.PARTY_CODE, a.SHORT_NAME, d.CURRENCY_CODE, b.NAME
from XTR_PARTIES_V a,
XTR_MASTER_CURRENCIES b,
GL_LEDGER_LE_V c,
GL_SETS_OF_BOOKS d
where a.PARTY_TYPE = 'C'
and a.DEFAULT_COMPANY = 'Y'
and c.LEGAL_ENTITY_ID = a.LEGAL_ENTITY_ID
and c.LEDGER_ID = d.SET_OF_BOOKS_ID
and c.LEDGER_CATEGORY_CODE = 'PRIMARY'
and b.CURRENCY = d.CURRENCY_CODE;
select d.CURRENCY_CODE, b.NAME
from XTR_PARTIES_V a,
XTR_MASTER_CURRENCIES b,
GL_LEDGER_LE_V c,
GL_SETS_OF_BOOKS d
where a.PARTY_TYPE = 'C'
and a.PARTY_CODE = l_comp
and c.LEGAL_ENTITY_ID = a.LEGAL_ENTITY_ID
and c.LEDGER_ID = d.SET_OF_BOOKS_ID
and c.LEDGER_CATEGORY_CODE = 'PRIMARY'
and b.CURRENCY = d.CURRENCY_CODE;
select PORTFOLIO
from XTR_PORTFOLIOS
where COMPANY_CODE = l_comp
and DEFAULT_PORTFOLIO = 'Y';
select a.HOME_CURRENCY,b.NAME
from XTR_PARTIES_V a,
XTR_MASTER_CURRENCIES b
where a.PARTY_CODE = l_pty
and b.CURRENCY = a.HOME_CURRENCY;
select ACCOUNT_NUMBER
from XTR_BANK_ACCOUNTS
where PARTY_CODE = l_company
and CURRENCY = l_currency
and DEFAULT_ACCT = 'Y';
select 1
from DUAL
where to_char(to_date(in_date),'D') between 2 and 6;
select 1
from XTR_HOLIDAYS
where HOLIDAY_DATE = in_date
and CURRENCY IN (l_ccy1,l_ccy2);
select (a.BID_PRICE + a.ASK_PRICE) / 2,a.NOS_OF_DAYS
from XTR_YIELD_CURVE_DETAILS a
where a.CURRENCY = l_ccy
and a.GROUP_CODE = l_yield_curve
and a.NOS_OF_DAYS =
(select max(c.NOS_OF_DAYS)
from XTR_YIELD_CURVE_DETAILS c
where c.GROUP_CODE = l_yield_curve
and c.CURRENCY = l_ccy
and c.NOS_OF_DAYS < l_days);
select (a.BID_PRICE + a.ASK_PRICE) / 2,a.NOS_OF_DAYS
from XTR_YIELD_CURVE_DETAILS a
where a.CURRENCY = l_ccy
and a.GROUP_CODE = l_yield_curve
and a.NOS_OF_DAYS =
(select max(c.NOS_OF_DAYS)
from XTR_YIELD_CURVE_DETAILS c
where c.GROUP_CODE = l_yield_curve
and c.CURRENCY = l_ccy
and c.NOS_OF_DAYS >= l_days);
select (a.BID_PRICE + a.ASK_PRICE) / 2,a.NOS_OF_DAYS
from XTR_MARKET_PRICES a
where a.CURRENCY_A = l_ccy
and a.TERM_TYPE IN('D','M','Y','A')
and a.NOS_OF_DAYS =
(select max(c.NOS_OF_DAYS)
from XTR_MARKET_PRICES c
where c.TERM_TYPE IN('D','M','Y','A')
and c.CURRENCY_A = l_ccy
and c.NOS_OF_DAYS < l_days);
select (a.BID_PRICE + a.ASK_PRICE) / 2,a.NOS_OF_DAYS
from XTR_MARKET_PRICES a
where a.CURRENCY_A = l_ccy
and a.TERM_TYPE IN('D','M','Y','A')
and a.NOS_OF_DAYS =
(select max(c.NOS_OF_DAYS)
from XTR_MARKET_PRICES c
where c.TERM_TYPE IN('D','M','Y','A')
and c.CURRENCY_A = l_ccy
and c.NOS_OF_DAYS >= l_days);
select rounding_factor
from master_currencies
where currency=l_round_ccy;
select a.REVAL_RATE,a.NUMBER_OF_DAYS
from XTR_REVALUATION_RATES a
where a.COMPANY_CODE = l_company
and a.PERIOD_TO = l_period_to
and a.CURRENCYA = l_ccy
and a.CURRENCYB = l_ccyb
and a.VOLATILITY_OR_RATE = 'FWDS'
and a.NUMBER_OF_DAYS =
(select max(b.NUMBER_OF_DAYS)
from XTR_REVALUATION_RATES b
where b.COMPANY_CODE = l_company
and b.PERIOD_TO = l_period_to
and b.CURRENCYA = l_ccy
and b.CURRENCYB = l_ccyb
and b.VOLATILITY_OR_RATE = 'FWDS'
and b.NUMBER_OF_DAYS < l_days);
select a.REVAL_RATE,a.NUMBER_OF_DAYS
from XTR_REVALUATION_RATES a
where a.COMPANY_CODE = l_company
and a.PERIOD_TO = l_period_to
and a.CURRENCYA = l_ccy
and a.CURRENCYB = l_ccyb
and a.VOLATILITY_OR_RATE = 'FWDS'
and a.NUMBER_OF_DAYS =
(select min(b.NUMBER_OF_DAYS)
from XTR_REVALUATION_RATES b
where b.COMPANY_CODE = l_company
and b.PERIOD_TO = l_period_to
and b.CURRENCYA = l_ccy
and b.CURRENCYB = l_ccyb
and b.VOLATILITY_OR_RATE = 'FWDS'
and b.NUMBER_OF_DAYS >= l_days);
select PARTY_CODE, SHORT_NAME
from XTR_PARTIES_V
where PARTY_TYPE = 'C'
and DEFAULT_COMPANY = 'Y';
select PORTFOLIO, NULL NAME
from xtr_portfolios pf
where COMPANY_CODE = l_company_code
and DEFAULT_PORTFOLIO = 'Y';
select PORTFOLIO
from XTR_PORTFOLIOS
where COMPANY_CODE = l_company_code
and DEFAULT_PORTFOLIO = 'Y';
select b.BANK_SHORT_CODE,a.ACCOUNT_NO
from XTR_STANDING_INSTRUCTIONS a,
XTR_BANK_ACCOUNTS b
where a.PARTY_CODE = l_party
and a.CURRENCY = l_ccy
and (a.DEAL_TYPE = l_deal_type
or a.DEAL_TYPE is NULL)
and (a.DEAL_SUBTYPE = l_subtype
or a.DEAL_SUBTYPE is NULL)
and (a.PRODUCT_TYPE = l_product
or a.PRODUCT_TYPE is NULL)
and (a.AMOUNT_TYPE = l_amount_type
or a.AMOUNT_TYPE is NULL)
and a.PARTY_CODE = b.PARTY_CODE
and a.CURRENCY = b.CURRENCY
and a.ACCOUNT_NO = b.ACCOUNT_NUMBER
and nvl(b.AUTHORISED,'N') = 'Y'
order by a.DEAL_TYPE,a.DEAL_SUBTYPE,a.PRODUCT_TYPE,a.AMOUNT_TYPE;
select a.BANK_SHORT_CODE,a.ACCOUNT_NUMBER
from XTR_BANK_ACCOUNTS a
where a.PARTY_CODE = l_party
and a.CURRENCY = l_ccy
and nvl(a.AUTHORISED,'N') = 'Y'
--* bug #1723491, rravunny
--* default account column should also be checked
and Nvl(a.Default_Acct,'N') = 'Y'
--* end of fix
order by nvl(a.DEFAULT_ACCT,'N') desc;
select pd.PRINCIPAL_SETTLED_BY,
pd.INTEREST_SETTLED_BY,
pd.FREQ_INTEREST_SETTLED
from XTR_PARTY_DEFAULTS pd,
XTR_PARTIES_V p
where p.PARTY_CODE = l_ref_party
and pd.SETTLEMENT_DEFAULT_CATEGORY = p.SETTLEMENT_DEFAULT_CATEGORY
and pd.DEFAULT_TYPE = 'S'
and pd.DEAL_TYPE = l_deal_type
and (pd.DEAL_SUBTYPE = l_subtype or pd.DEAL_SUBTYPE is NULL)
and (pd.PRODUCT_TYPE = l_product or pd.PRODUCT_TYPE is NULL)
and (pd.PARTY_CODE = l_ref_party or pd.PARTY_CODE is NULL)
order by pd.DEAL_TYPE,pd.DEAL_SUBTYPE,pd.PRODUCT_TYPE;
select b.BROKERAGE_REFERENCE, d.AMOUNT_TYPE
from XTR_PARTY_DEFAULTS b,
XTR_PARTIES_V p,
XTR_TAX_BROKERAGE_SETUP a,
XTR_DEDUCTION_CALCS_V d
where p.PARTY_CODE = l_ref_party
and b.BROKERAGE_CATEGORY = p.BROKERAGE_CATEGORY
and b.DEFAULT_TYPE = 'B'
and b.DEAL_TYPE = l_deal_type
and (b.DEAL_SUBTYPE = l_subtype or b.DEAL_SUBTYPE is NULL)
and (b.PRODUCT_TYPE = l_product or b.PRODUCT_TYPE is NULL)
and (b.PARTY_CODE = l_ref_party or b.PARTY_CODE is NULL)
and nvl(a.AUTHORISED,'N')= 'Y'
and a.REFERENCE_CODE = b.BROKERAGE_REFERENCE
and a.DEAL_TYPE = l_deal_type
and d.DEAL_TYPE = a.DEAL_TYPE
and d.CALC_TYPE = a.CALC_TYPE
order by b.PARTY_CODE;
select b.TAX_REFERENCE, d.AMOUNT_TYPE
from XTR_PARTY_DEFAULTS b,
XTR_PARTIES_V p,
XTR_TAX_BROKERAGE_SETUP a,
XTR_DEDUCTION_CALCS_V d
where p.PARTY_CODE = l_ref_party
and b.TAX_CATEGORY = p.TAX_CATEGORY
and b.DEFAULT_TYPE = 'T'
and b.DEAL_TYPE = l_deal_type
and (b.DEAL_SUBTYPE = l_subtype or b.DEAL_SUBTYPE is NULL)
and (b.PRODUCT_TYPE = l_product or b.PRODUCT_TYPE is NULL)
and (b.PARTY_CODE = l_ref_party or b.PARTY_CODE is NULL)
and nvl(a.AUTHORISED,'N')= 'Y'
and a.REFERENCE_CODE = b.TAX_REFERENCE
and a.DEAL_TYPE = l_deal_type
and d.DEAL_TYPE = a.DEAL_TYPE
and d.CALC_TYPE = a.CALC_TYPE
order by b.PARTY_CODE;
select pd.PRINCIPAL_SETTLED_BY,
pd.INTEREST_SETTLED_BY,
pd.FREQ_INTEREST_SETTLED
from XTR_PARTY_DEFAULTS pd,
XTR_PARTIES_V p
where p.PARTY_CODE = l_ref_party
and pd.SETTLEMENT_DEFAULT_CATEGORY = p.SETTLEMENT_DEFAULT_CATEGORY
and pd.DEFAULT_TYPE = 'S'
and pd.DEAL_TYPE = l_deal_type
and (pd.DEAL_SUBTYPE = l_subtype or pd.DEAL_SUBTYPE is NULL)
and (pd.PRODUCT_TYPE = l_product or pd.PRODUCT_TYPE is NULL)
and (pd.PARTY_CODE = l_ref_party or pd.PARTY_CODE is NULL)
order by pd.DEAL_TYPE,pd.DEAL_SUBTYPE,pd.PRODUCT_TYPE;
select b.BROKERAGE_REFERENCE, d.AMOUNT_TYPE
from XTR_PARTY_DEFAULTS b,
XTR_PARTIES_V p,
XTR_TAX_BROKERAGE_SETUP a,
XTR_DEDUCTION_CALCS_V d
where p.PARTY_CODE = l_ref_party
and b.BROKERAGE_CATEGORY = p.BROKERAGE_CATEGORY
and b.DEFAULT_TYPE = 'B'
and b.DEAL_TYPE = l_deal_type
and (b.DEAL_SUBTYPE = l_subtype or b.DEAL_SUBTYPE is NULL)
and (b.PRODUCT_TYPE = l_product or b.PRODUCT_TYPE is NULL)
and (b.PARTY_CODE = l_ref_party or b.PARTY_CODE is NULL)
and nvl(a.AUTHORISED,'N')= 'Y'
and a.REFERENCE_CODE = b.BROKERAGE_REFERENCE
and a.DEAL_TYPE = l_deal_type
and d.DEAL_TYPE = a.DEAL_TYPE
and d.CALC_TYPE = a.CALC_TYPE
order by b.PARTY_CODE;
select b.TAX_REFERENCE, b.INCOME_TAX_REFERENCE, d.CALC_TYPE
from XTR_PARTY_DEFAULTS b,
XTR_PARTIES_V p,
XTR_TAX_BROKERAGE_SETUP a,
XTR_TAX_DEDUCTION_CALCS_V d
where p.PARTY_CODE = l_ref_party
and b.TAX_CATEGORY = p.TAX_CATEGORY
and b.DEFAULT_TYPE = 'T'
and b.DEAL_TYPE = l_deal_type
and (b.DEAL_SUBTYPE = l_subtype or b.DEAL_SUBTYPE is NULL)
and (b.PRODUCT_TYPE = l_product or b.PRODUCT_TYPE is NULL)
and (b.PARTY_CODE = l_ref_party or b.PARTY_CODE is NULL)
and nvl(a.AUTHORISED,'N')= 'Y'
and a.DEAL_TYPE = l_deal_type
and d.DEAL_TYPE = a.DEAL_TYPE
and d.CALC_TYPE = a.CALC_TYPE
order by b.DEAL_TYPE, b.DEAL_SUBTYPE, nvl(b.PRODUCT_TYPE, '');
select d.AMOUNT_TYPE
from XTR_TAX_BROKERAGE_SETUP a,
XTR_DEDUCTION_CALCS_V d
where a.DEAL_TYPE = l_deal_type
and a.REFERENCE_CODE = l_bkr_ref
and nvl(a.AUTHORISED,'N')= 'Y'
and d.DEAL_TYPE = a.DEAL_TYPE
and d.CALC_TYPE = a.CALC_TYPE;
select d.AMOUNT_TYPE
from XTR_TAX_BROKERAGE_SETUP a,
XTR_DEDUCTION_CALCS_V d
where a.DEAL_TYPE = l_deal_type
and a.REFERENCE_CODE = l_tax_ref
and nvl(a.AUTHORISED,'N')= 'Y'
and d.DEAL_TYPE = a.DEAL_TYPE
and d.CALC_TYPE = a.CALC_TYPE;
select round(abs(p_one_step_rec.p_amount) / s.HCE_RATE,2)
from XTR_MASTER_CURRENCIES_V s
where s.CURRENCY = upper(p_one_step_rec.p_CURRENCY);
Select tax_settle_method
Into v_settle_method
From Xtr_Tax_Brokerage_Setup_V
Where reference_code = p_one_step_rec.p_schedule_code;
Select settlement_code
Into v_settlement_code
From XTR_ONE_STEP_SETTLE_CODES_V
Where company_code = p_one_step_rec.p_company_code
And schedule = p_one_step_rec.p_schedule_code;
Select settlement_code
Into v_settlement_code
From XTR_ONE_STEP_SETTLE_CODES_V
Where company_code = p_one_step_rec.p_company_code
And schedule is null;
Select name
Into v_comments
From Xtr_Exposure_Types_V
Where Exposure_Type = v_settlement_code
And Company_Code = p_one_step_rec.p_company_code;
/***** EXP open API which inserts into Xtr_Exposure_Transactions and Xtr_Deal_Date_Amounts ******/
XTR_EXP_TRANSFERS_PKG.TRANSFER_EXP_DEALS(v_exp_rec,
p_one_step_rec.p_source,
v_user_error,
v_mandatory_error,
v_validation_error,
v_limit_error);
PROCEDURE UPDATE_JOURNALS (l_deal_nos IN NUMBER,
l_trans_nos IN NUMBER,
l_deal_type IN VARCHAR2) is
--
l_sysdate DATE;
select sysdate,user
from DUAL;
select sysdate, dealer_code
from xtr_dealer_codes_v
where user_id = fnd_global.user_id;
update JOURNALS
set JNL_REVERSAL_IND = 'C',
CANCELLED_IN_GL = 'Y'
where DEAL_NUMBER = l_deal_nos
and TRANSACTION_NUMBER = l_trans_nos
and DEAL_TYPE = l_deal_type
and GL_TRANSFER_DATE is null;
update JOURNALS
set JNL_REVERSAL_IND = 'Y',
UPDATED_ON = l_sysdate,
UPDATED_BY = l_user
where DEAL_NUMBER = l_deal_nos
and TRANSACTION_NUMBER = l_trans_nos
and DEAL_TYPE = l_deal_type
and GL_TRANSFER_DATE is not null;
end UPDATE_JOURNALS;
/* Updates transaction's floating rate based on the deal's */
/* Benchmark Rate and Margin. */
/* The parameters are passed in through concurrent program. */
/*************************************************************/
PROCEDURE RESET_FLOATING_RATES(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_rateset_from IN VARCHAR2,
p_rateset_to IN VARCHAR2,
p_rateset_adj IN NUMBER,
p_deal_type IN VARCHAR2,
p_company IN VARCHAR2,
p_cparty IN VARCHAR2,
p_portfolio IN VARCHAR2,
p_currency IN VARCHAR2,
p_ric_code IN VARCHAR2,
p_source IN VARCHAR2) IS
l_buf VARCHAR2(300);
/* Selection criteria for transactions : */
/* - only companies that are authorised to user */
/* - only TMM and IRS deals with Benchmark Rate */
/* - RATE_FIXING_DATE is not null on transaction */
/* - RATE_FIXING_DATE within parameter date range */
/*-------------------------------------------------*/
cursor curr_all_tran is
select distinct a.ROWID,
a.COMPANY_CODE,
a.DEAL_NUMBER,
a.TRANSACTION_NUMBER,
a.DEAL_TYPE,
a.START_DATE,
a.RATE_FIXING_DATE,
b.RATE_BASIS,
nvl(b.MARGIN,0)
from XTR_ROLLOVER_TRANSACTIONS a,
XTR_DEALS b,
XTR_PARTIES_V c
where a.deal_type = NVL(p_deal_type,a.deal_type)
and a.deal_type in ('TMM','IRS')
and a.company_code = NVL(p_company,a.company_code)
and a.company_code = c.party_code -- user access
and a.cparty_code = NVL(p_cparty,a.cparty_code)
and a.portfolio_code = NVL(p_portfolio,a.portfolio_code)
and a.currency = NVL(p_currency,a.currency)
and a.deal_number = b.deal_no
and a.rate_fixing_date is not null
and a.rate_fixing_date between fnd_date.canonical_to_date(p_rateset_from)
and fnd_date.canonical_to_date(p_rateset_to)
and b.rate_basis is not null
and b.rate_basis = nvl(p_ric_code, b.rate_basis)
and b.STATUS_CODE = 'CURRENT'
order by a.deal_type, a.deal_number,
a.start_date, a.transaction_number;
select 'Y'
from xtr_rollover_transactions
where deal_number = l_deal_no
and transaction_number = l_tran_no
and rate_fixing_date <
(select max(rate_fixing_date)
from xtr_rollover_transactions
where deal_number = l_deal_no
and rate_fixing_date between fnd_date.canonical_to_date(p_rateset_from)
and fnd_date.canonical_to_date(p_rateset_to));
if C_ONE_ROW%FOUND then -- update only one row
UPDATE_RATE_ONE_TRANSACTION(l_deal_no,
l_tran_no,
l_deal_type,
l_start_date,
l_new_rate + (l_margin/100));
FND_MESSAGE.SET_NAME('XTR', 'XTR_UPDATE_BENCH_RATE');
else --update current record as well as subsequent transactions
UPDATE_RATE_SEQ_TRANSACTION(l_deal_no,
l_tran_no,
l_deal_type,
l_start_date,
l_new_rate + (l_margin/100));
FND_MESSAGE.SET_NAME('XTR', 'XTR_UPDATE_BENCH_SEQ_RATE');
select nvl(min(amount_date),p_start_date) -- Ilavenil modified for witholding tax project
from xtr_deal_date_amounts
where company_code = p_company
and deal_number = p_deal_no
and settle = 'Y'
and amount_date > p_start_date
/******** Ilavenil modified for witholding tax *********/
union
select nvl(min(a.amount_date),p_start_date) -- Ilavenil modified for witholding tax project
from xtr_deal_date_amounts a,
xtr_rollover_transactions b
where b.company_code = p_company
and b.deal_number = p_deal_no
and (b.tax_settled_reference is not null)
and a.company_code = p_company
and a.deal_type = 'EXP'
and a.transaction_number in (b.tax_settled_reference)
and nvl(a.settle,'N') = 'Y'
and b.maturity_date > p_start_date
order by 1 desc;
select nvl(max(journal_date), p_start_date)
from xtr_journals
where deal_number = p_deal_no
and amount_type in ('INTSET','TAX') -- Ilavenil modified for witholding tax
union
/********* Ilavenil modified for witholding tax *********/
select nvl(max(b.maturity_date), p_start_date)
from xtr_journals a,
xtr_rollover_transactions b
where b.company_code =p_company
and b.deal_number = p_deal_no
and b.tax_settled_reference is not null
and a.company_code = p_company
and a.deal_type = 'EXP'
and a.transaction_number = b.tax_settled_reference
order by 1 desc;
select prepaid_interest
from XTR_DEALS_V
where deal_no = p_deal_no;
select max(maturity_date)
from xtr_rollover_transactions_v
where deal_number = p_deal_no
and org_trans_no in (select min(transaction_number)
from xtr_deal_date_amounts
where deal_number = p_deal_no
and amount_type = 'INTSET'
and nvl(settle, 'N') = 'Y')
and maturity_date > p_start_date;
select nvl(max(journal_date), p_start_date)
from xtr_journals
where deal_number = p_deal_no
and amount_type in ('INTSET','TAX')
union
select nvl(max(b.maturity_date), p_start_date)
from xtr_journals a,
xtr_rollover_transactions b
where b.company_code = p_company
and b.deal_number = p_deal_no
and b.tax_settled_reference is not null
and a.company_code = p_company
and a.deal_type = 'EXP'
and a.transaction_number = b.tax_settled_reference
order by 1 desc;
select nvl(max(maturity_date), p_start_date)
from xtr_rollover_transactions
where deal_number = p_deal_no
and transaction_number in (select transaction_number
from xtr_deal_date_amounts
where deal_number = p_deal_no
and amount_type = 'INTSET'
and batch_id is NOT NULL);
select max(period_to)
from xtr_accrls_amort
where deal_no = p_deal_no
and period_to > p_start_date;
select max(b.period_end)
from xtr_batches b, xtr_batch_events e, xtr_revaluation_details r
where b.batch_id = e.batch_id
and b.batch_id = r.batch_id
and r.deal_no = p_deal_no
and b.company_code = p_company
and e.event_code = 'REVAL'
and b.period_end > p_start_date;
/* Select interest rate that is : */
/* - either BID rate or ASK rate is not null */
/* - closest to the transaction Rate Reset Date, */
/* with rateset day adjustment. */
/*-------------------------------------------------*/
cursor curr_bench_rate is
select nvl(BID_RATE,OFFER_RATE)
from XTR_INTEREST_PERIOD_RATES_V
where UNIQUE_PERIOD_ID = p_ric_code
and trunc(rate_date) between (p_rate_date-nvl(p_rateset_adj,0)) -- Bug 5259621
and p_rate_date -- Bug 5259621
and (bid_rate is not null or offer_rate is not null)
order by rate_date desc;
PROCEDURE UPDATE_RATE_ONE_TRANSACTION(p_deal_no IN NUMBER,
p_trans_no IN NUMBER,
p_deal_type IN VARCHAR2,
p_start_date IN DATE,
p_new_rate IN NUMBER)IS
-- Add xtr_deals.rounding_type for Interest Override
cursor TMM_ROLL is
select r.deal_subtype, r.currency, r.rowid, r.adjusted_balance,
r.no_of_days,r.year_basis,r.interest, r.settle_term_interest,
r.accum_interest_bf, r.accum_interest,r.interest_hce,
r.interest_settled, r.trans_closeout_no, r.start_date,
r.maturity_date, r.transaction_number, r.interest_rate,
r.principal_action,r.principal_adjust, r.balance_out_bf,
d.rounding_type,
/**** code below added by Ilavenil for witholding tax project *****/
r.tax_amount,
r.tax_code,
r.tax_settled_reference, r.tax_amount_hce ,
r.tax_rate,
r.balance_out,
d.settle_account_no,
d.company_code, d.cparty_code, d.year_calc_type
/**********/
from XTR_ROLLOVER_TRANSACTIONS r,
XTR_DEALS d
where r.deal_number = d.deal_no
and r.deal_type = p_deal_type
and r.deal_type = 'TMM'
and r.deal_number = p_deal_no
and r.transaction_number = p_trans_no; -- bug 3814944
select rowid
from XTR_ROLLOVER_TRANSACTIONS_V
where deal_number = p_deal_no
and status_code = 'CURRENT'
order by maturity_date desc, start_date desc, transaction_number desc;
select r.currency, r.balance_out, r.no_of_days, d.year_basis,
d.deal_subtype, d.rounding_type
from XTR_DEALS d,
XTR_ROLLOVER_TRANSACTIONS r
where d.deal_type = p_deal_type
and d.deal_type = 'IRS'
and d.deal_no = r.deal_number
and r.deal_number = p_deal_no
and r.transaction_number = p_trans_no;
select rounding_factor, hce_rate
from XTR_MASTER_CURRENCIES_V
where currency = pmt.currency;
select rounding_factor, hce_rate
from XTR_MASTER_CURRENCIES_V
where currency = l_currency;
Update XTR_CONFIRMATION_DETAILS
set confirmation_validated_by = null,
confirmation_validated_on = to_date(null)
where deal_no = p_deal_no;
Update XTR_ROLLOVER_TRANSACTIONS
set interest_rate = p_new_rate
where deal_number = p_deal_no
and transaction_number = p_trans_no;
update XTR_DEAL_DATE_AMOUNTS
set transaction_rate = p_new_rate
where deal_number = p_deal_no
and transaction_number = p_trans_no;
update XTR_ROLLOVER_TRANSACTIONS
set accum_interest_bf = pmt.accum_interest_bf,
accum_interest_bf_hce = l_accum_int_bf_hce,
accum_interest_hce = l_accum_int_hce,
accum_interest = pmt.accum_interest,
interest = pmt.interest,
interest_settled = pmt.interest_settled,
interest_hce = l_int_hce,
original_amount = pmt.interest, --Add Interest Override
TAX_SETTLED_REFERENCE = v_RT.TAX_SETTLED_REFERENCE, -- Ilavenil Bug 234413
TAX_AMOUNT = v_RT.TAX_AMOUNT, -- Ilavenil Bug 234413
TAX_AMOUNT_HCE = v_RT.TAX_AMOUNT_HCE -- Ilavenil Bug 234413
where rowid = pmt.ROWID;
update XTR_DEAL_DATE_AMOUNTS
set amount = pmt.interest,
hce_amount = l_int_hce
where deal_number = p_deal_no
and transaction_number = pmt.transaction_number
and amount_type = 'INTERST';
update XTR_DEAL_DATE_AMOUNTS
set amount = pmt.interest_settled,
hce_amount = l_int_settle_hce,
cashflow_amount = decode(pmt.deal_subtype, 'FUND', (-1), 1) *
pmt.interest_settled
where deal_number = p_deal_no
and transaction_number = pmt.transaction_number
and amount_type = 'INTSET';
Update XTR_ROLLOVER_TRANSACTIONS
set interest_rate = l_int_rate,
interest = l_interest,
interest_settled = l_int_settled,
interest_hce = l_int_hce,
original_amount = l_interest --Add Interest Override
where deal_number = p_deal_no
and transaction_number = p_trans_no;
Update XTR_DEAL_DATE_AMOUNTS
set transaction_rate = l_int_rate
where deal_number = p_deal_no
and transaction_number = p_trans_no;
Update XTR_DEAL_DATE_AMOUNTS
set amount = l_interest,
hce_amount = l_int_hce,
cashflow_amount = decode(l_deal_subtype, 'FUND',
l_interest * (-1), l_interest)
where deal_number = p_deal_no
and transaction_number = p_trans_no
and amount_type = 'INTSET';
End UPDATE_RATE_ONE_TRANSACTION;
PROCEDURE UPDATE_RATE_SEQ_TRANSACTION(p_deal_no IN NUMBER,
p_trans_no IN NUMBER,
p_deal_type IN VARCHAR2,
p_start_date IN DATE,
p_new_rate IN NUMBER)IS
-- Add xtr_deals.rounding_type for Interest Override
cursor TMM_ROLL is
select r.deal_subtype, r.currency, r.rowid, r.adjusted_balance, r.no_of_days, r.year_basis,
r.interest, r.settle_term_interest, r.accum_interest_bf, r.accum_interest,
r.interest_hce, r.interest_settled, r.trans_closeout_no, r.start_date,
r.maturity_date, r.transaction_number, r.interest_rate, r.principal_action,
r.principal_adjust, r.balance_out_bf, d.rounding_type,
/**** code below added by Ilavenil for witholding tax project *****/
r.tax_amount,
r.tax_code,
r.tax_settled_reference, r.tax_amount_hce ,
r.tax_rate,
r.balance_out,
d.settle_account_no,
d.company_code, d.cparty_code, d.year_calc_type
/**********/
from XTR_ROLLOVER_TRANSACTIONS_V r,
XTR_DEALS d
where r.deal_number = d.deal_no
and r.deal_type = p_deal_type
and r.deal_type = 'TMM'
and r.deal_number = p_deal_no
and r.start_date >= p_start_date
order by r.start_date asc, r.maturity_date asc, r.transaction_number asc;
select rowid
from XTR_ROLLOVER_TRANSACTIONS_V
where deal_number = p_deal_no
and status_code = 'CURRENT'
order by maturity_date desc, start_date desc, transaction_number desc;
select r.rowid, r.transaction_number, d.currency, r.balance_out,
r.no_of_days, d.year_basis, d.deal_subtype, d.rounding_type
from XTR_DEALS d,
XTR_ROLLOVER_TRANSACTIONS r
where d.deal_type = p_deal_type
and d.deal_type = 'IRS'
and d.deal_no = r.deal_number
and d.deal_no = p_deal_no
and r.start_date >= p_start_date;
select rounding_factor, hce_rate
from XTR_MASTER_CURRENCIES_V
where currency = pmt.currency;
select rounding_factor, hce_rate
from XTR_MASTER_CURRENCIES_V
where currency = pms.currency;
Update XTR_CONFIRMATION_DETAILS
set confirmation_validated_by = null,
confirmation_validated_on = to_date(null)
where deal_no = p_deal_no;
update XTR_ROLLOVER_TRANSACTIONS
set interest_rate = p_new_rate,
accum_interest_bf = pmt.accum_interest_bf,
accum_interest_bf_hce = l_accum_int_bf_hce,
accum_interest_hce = l_accum_int_hce,
accum_interest = pmt.accum_interest,
interest = pmt.interest,
interest_settled = pmt.interest_settled,
interest_hce = l_int_hce,
original_amount = pmt.interest, --Add Interest Override
TAX_SETTLED_REFERENCE = v_RT.TAX_SETTLED_REFERENCE, -- Ilavenil Bug 234413
TAX_AMOUNT = v_RT.TAX_AMOUNT, -- Ilavenil Bug 234413
TAX_AMOUNT_HCE = v_RT.TAX_AMOUNT_HCE -- Ilavenil Bug 234413
where rowid = pmt.ROWID;
update XTR_DEAL_DATE_AMOUNTS
set transaction_rate = p_new_rate
where deal_number = p_deal_no
and transaction_number = pmt.transaction_number;
update XTR_DEAL_DATE_AMOUNTS
set amount = pmt.interest,
hce_amount = l_int_hce
where deal_number = p_deal_no
and transaction_number = pmt.transaction_number
and amount_type = 'INTERST';
update XTR_DEAL_DATE_AMOUNTS
set amount = pmt.interest_settled,
hce_amount = l_int_settle_hce,
cashflow_amount = decode(pmt.deal_subtype, 'FUND', (-1), 1) *
pmt.interest_settled
where deal_number = p_deal_no
and transaction_number = pmt.transaction_number
and amount_type = 'INTSET';
Update XTR_ROLLOVER_TRANSACTIONS
set interest_rate = p_new_rate,
interest = l_interest,
interest_settled = l_int_settled,
interest_hce = l_int_hce,
original_amount = l_interest --Add Interest Override
where rowid = pms.rowid;
Update XTR_DEAL_DATE_AMOUNTS
set transaction_rate = p_new_rate
where deal_number = p_deal_no
and transaction_number = pms.transaction_number;
Update XTR_DEAL_DATE_AMOUNTS
set amount = l_interest,
hce_amount = l_int_hce,
cashflow_amount = decode(pms.deal_subtype, 'FUND',
l_interest * (-1), l_interest)
where deal_number = p_deal_no
and transaction_number = pms.transaction_number
and amount_type = 'INTSET';
End UPDATE_RATE_SEQ_TRANSACTION;
SELECT decode(currency_a, p_currency_from ,ask_price,1/ask_price),
decode(currency_a, p_currency_from ,bid_price,1/bid_price)
FROM xtr_market_prices
WHERE ((currency_a= p_currency_from AND currency_b=p_currency_to)
OR (currency_a= p_currency_to AND currency_b=p_currency_from))
AND term_type='S';
select
decode(sr2.currency_a,p_currency_from,sr2.bid_price ,(1/sr2.ask_price)) /
decode(sr3.currency_a, p_currency_to, sr3.bid_price, (1/sr3.ask_price)),
decode(sr2.currency_a,p_currency_from,sr2.ask_price, (1/sr2.bid_price)) /
decode(sr3.currency_a, p_currency_to, sr3.ask_price, (1/sr3.bid_price))
from
XTR_MARKET_PRICES sr2,
XTR_MARKET_PRICES sr3
where (sr2.currency_a = 'USD' or sr2.currency_b = 'USD')
and (sr3.currency_a = 'USD' or sr3.currency_b = 'USD')
and (sr2.currency_a = p_currency_from or sr2.currency_b = p_currency_from)
and (sr3.currency_a = p_currency_to or sr3.currency_b= p_currency_to)
and sr2.term_type = 'S'
and sr3.term_type = 'S';
SELECT calc_type,
tax_settle_method
FROM XTR_TAX_BROKERAGE_SETUP
WHERE reference_code = l_tax_code;
select START_DATE,
MATURITY_DATE,
BALANCE_OUT
from XTR_ROLLOVER_TRANSACTIONS
where deal_number = p_RT.deal_number
order by start_date, maturity_date, transaction_number;
select hce_rate
from XTR_MASTER_CURRENCIES_V
where currency = l_ccy;
select a.rounding_factor
from XTR_MASTER_CURRENCIES_V a,
XTR_PRO_PARAM b
where b.param_name = 'SYSTEM_FUNCTIONAL_CCY'
and a.currency = param_value;
DELETE_TAX_EXPOSURE(p_RT.DEAL_NUMBER, p_RT.TRANSACTION_NUMBER);
/* The following logic handles update for OSG only if it is a 1-1 relationship
---------------------------------------------------------------------------
if p_prncpl_ref is not null and p_prncpl_method <> 'OSG' then
---------------------------------------
-- Replace this with DELETE_TAX_EXPOSURE
---------------------------------------
delete XTR_EXPOSURE_TRANSACTIONS
where TRANSACTION_NUMBER = p_prncpl_ref;
delete XTR_DEAL_DATE_AMOUNTS
where DEAL_TYPE = 'EXP'
and TRANSACTION_NUMBER = p_prncpl_ref;
delete XTR_EXPOSURE_TRANSACTIONS
where TRANSACTION_NUMBER = p_prncpl_ref;
delete XTR_DEAL_DATE_AMOUNTS
where DEAL_TYPE = 'EXP'
and TRANSACTION_NUMBER = p_prncpl_ref;
update XTR_EXPOSURE_TRANSACTIONS
set AMOUNT = abs(nvl(p_one_step.p_amount,0)),
AMOUNT_HCE = abs(nvl(p_amt_hce,0)),
VALUE_DATE = p_one_step.p_settlement_date
where TRANSACTION_NUMBER = p_prncpl_ref;
update XTR_DEAL_DATE_AMOUNTS
set AMOUNT = abs(p_one_step.p_amount),
HCE_AMOUNT = abs(p_amt_hce),
AMOUNT_DATE = p_one_step.p_settlement_date,
CASHFLOW_AMOUNT = decode(ACTION_CODE,'PAY',-1,1) * abs(p_one_step.p_amount)
where DEAL_TYPE = 'EXP'
and TRANSACTION_NUMBER = p_prncpl_ref;
/* The following logic handles update for OSG only if it is a 1-1 relationship
---------------------------------------------------------------------------
if p_income_ref is not null and p_income_method <> 'OSG' then
---------------------------------------
-- Replace this with Jeremy's procedure
---------------------------------------
delete XTR_EXPOSURE_TRANSACTIONS
where TRANSACTION_NUMBER = p_income_ref;
delete XTR_DEAL_DATE_AMOUNTS
where DEAL_TYPE = 'EXP'
and TRANSACTION_NUMBER = p_income_ref;
delete XTR_EXPOSURE_TRANSACTIONS
where TRANSACTION_NUMBER = p_income_ref;
delete XTR_DEAL_DATE_AMOUNTS
where DEAL_TYPE = 'EXP'
and TRANSACTION_NUMBER = p_income_ref;
update XTR_EXPOSURE_TRANSACTIONS
set AMOUNT = abs(nvl(p_one_step.p_amount,0)),
AMOUNT_HCE = abs(nvl(p_amt_hce,0)),
VALUE_DATE = p_one_step.p_settlement_date
where TRANSACTION_NUMBER = p_income_ref;
update XTR_DEAL_DATE_AMOUNTS
set AMOUNT = abs(p_one_step.p_amount),
HCE_AMOUNT = abs(p_amt_hce),
AMOUNT_DATE = p_one_step.p_settlement_date,
CASHFLOW_AMOUNT = decode(ACTION_CODE,'PAY',-1,1) * abs(p_one_step.p_amount)
where DEAL_TYPE = 'EXP'
and TRANSACTION_NUMBER = p_income_ref;
PROCEDURE DELETE_TAX_EXPOSURE(p_deal_no IN NUMBER,
p_trans_no IN NUMBER)
IS
-- The following 2 cursors are used if given transaction number
cursor t_rollover_deal_exposures is
select a.tax_settled_reference
from xtr_rollover_transactions a
where a.deal_number = p_deal_no and
a.tax_settled_reference is not null and
a.transaction_number = p_trans_no;
select a.principal_tax_settled_ref
from xtr_rollover_transactions a
where a.deal_number= p_deal_no and
a.principal_tax_settled_ref is not null and
a.transaction_number = p_trans_no;
select a.tax_settled_reference
from xtr_rollover_transactions a
where a.deal_number = p_deal_no and
a.tax_settled_reference is not null;
select a.principal_tax_settled_ref
from xtr_rollover_transactions a
where a.deal_number= p_deal_no and
a.principal_tax_settled_ref is not null;
select a.tax_settled_reference
from xtr_deals a where a.deal_no = p_deal_no;
select a.income_tax_settled_ref
from xtr_deals a where a.deal_no = p_deal_no;
DELETE_TAX_EXP_AND_UPDATE(deal_record.tax_settled_reference);
DELETE_TAX_EXP_AND_UPDATE(deal_record_int.income_tax_settled_ref);
DELETE_TAX_EXP_AND_UPDATE(roll_record.tax_settled_reference);
DELETE_TAX_EXP_AND_UPDATE(roll_record_p.principal_tax_settled_ref);
DELETE_TAX_EXP_AND_UPDATE(trans_roll_record.tax_settled_reference);
DELETE_TAX_EXP_AND_UPDATE(trans_roll_record_p.principal_tax_settled_ref);
END DELETE_TAX_EXPOSURE;
PROCEDURE DELETE_TAX_EXP_AND_UPDATE(p_tax_settle_no IN NUMBER)
IS
BEGIN
if p_tax_settle_no is not null then
delete from XTR_EXPOSURE_TRANSACTIONS
where TRANSACTION_NUMBER = p_tax_settle_no;
delete from XTR_DEAL_DATE_AMOUNTS_V
where DEAL_TYPE = 'EXP'
and deal_number = 0
and TRANSACTION_NUMBER = p_tax_settle_no;
update XTR_ROLLOVER_TRANSACTIONS
set tax_settled_reference = null
where tax_settled_reference = p_tax_settle_no;
update XTR_ROLLOVER_TRANSACTIONS
set principal_tax_settled_ref = null
where principal_tax_settled_ref = p_tax_settle_no;
update XTR_DEALS
set tax_settled_reference = null
where tax_settled_reference = p_tax_settle_no;
update XTR_DEALS
set income_tax_settled_ref = null
where income_tax_settled_ref = p_tax_settle_no;
END DELETE_TAX_EXP_AND_UPDATE;
PROCEDURE UPDATE_TAX_DDA (p_exp_number NUMBER,
p_amount NUMBER) IS
cursor get_dda_cashflow is
select cashflow_amount
from xtr_deal_date_amounts_v
where transaction_number=p_exp_number
and deal_type='EXP'
and deal_number=0;
update xtr_deal_date_amounts_v
set amount=amount-nvl(p_amount,0)
where transaction_number=p_exp_number
and deal_type='EXP'
and deal_number=0;
update xtr_deal_date_amounts_v
set amount=amount-nvl(p_amount,0),
cashflow_amount=cashflow_amount+nvl(p_amount,0)
where transaction_number=p_exp_number
and deal_type='EXP'
and deal_number=0;
END UPDATE_TAX_DDA;
PROCEDURE UPDATE_TAX_EXP (p_exp_number NUMBER,
p_amount NUMBER) IS
BEGIN
update xtr_exposure_transactions
set amount=amount-nvl(p_amount,0)
where transaction_number=p_exp_number;
END update_tax_exp;