[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT analysis_name, status, process_id
FROM qrm_analysis_settings
WHERE analysis_name=p_name and history_flag='C';
-- this updates the history_flag='C' row only!
UPDATE qrm_analysis_settings
SET status='3',
process_id=p_request_id,
last_run_date=p_ref_datetime,
dirty = 'N'
WHERE analysis_name=p_analysis_names(i);
SELECT *
FROM qrm_analysis_settings
WHERE analysis_name = p_a_name and history_flag='C';
SELECT *
FROM qrm_analysis_atts
WHERE analysis_name= p_a_name and history_flag='C';
SELECT where_clause
FROM qrm_filters
WHERE filter_name = p_filter_name;
SELECT row_number,value
FROM qrm_filter_conditions
WHERE filter_name = p_filter_name;
SELECT parameter_value_code
FROM xtr_company_parameters_v
WHERE company_code=p_company_code AND parameter_code='REVAL_DFMDS';
SELECT sob.currency_code
FROM gl_sets_of_books sob, xtr_party_info pinfo
WHERE pinfo.party_code = p_company_code AND
pinfo.set_of_books_id = sob.set_of_books_id;
SELECT NVL(ig_year_basis, 'ACTUAL/ACTUAL')
FROM xtr_master_currencies_v
WHERE currency = p_ccy;
SELECT deal_calc_id
FROM qrm_deal_calculations
WHERE deal_no = p_deal_no AND transaction_no = p_trans_no AND
market_data_set = mds AND
last_md_calc_date >= p_threshold_date;
SELECT *
FROM qrm_deal_calculations
WHERE deal_no = p_deal_no AND transaction_no = p_trans_no AND
market_data_set = mds;
SELECT qrm_deals_s.NEXTVAL
FROM dual;
SELECT max(coupon_date)
FROM xtr_bond_coupon_dates
WHERE coupon_date <= p_ref_date AND bond_issue_code = p_bond_issue_code;
SELECT *
FROM xtr_rollover_transactions_v
WHERE deal_number=p_deal_no AND deal_type=p_deal_type AND
start_date<=p_ref_date AND p_ref_date'CANCELLED' AND status_code<>'CLOSED'
ORDER BY start_date, maturity_date;
SELECT *
FROM xtr_rollover_transactions_v
WHERE deal_number=p_deal_no
AND ((p_ref_date=start_date AND p_ref_date'CANCELLED' AND status_code<>'CLOSED'
ORDER BY start_date, maturity_date;
SELECT ric_code, calc_type, year_calc_type, commence_date
FROM xtr_bond_issues
WHERE bond_issue_code = p_bond_issue AND ric_code IS NOT NULL;
SELECT rt.interest, d.interest_rate, rt.start_date, rt.maturity_date
FROM xtr_rollover_transactions_v rt, xtr_deals_v d
WHERE rt.deal_number=d.deal_no AND rt.deal_number=p_deal_no AND
((p_ref_date<=rt.start_date) OR (p_ref_date>=rt.start_date AND
p_ref_date'CANCELLED' AND rt.status_code<>'CLOSED'
-- only look at future coupons
ORDER BY rt.maturity_date; --bug 2804548
SELECT max(transaction_number)
FROM xtr_rollover_transactions_v
GROUP BY deal_number
HAVING deal_number=p_deal_no; Bug 4965436 */
SELECT max(transaction_number)
FROM xtr_rollover_transactions_v
WHERE deal_number=p_deal_no;
p_select_clause VARCHAR2(1000) := 'SELECT deal_no, transaction_no, market_data_set, company_code, call_or_put, pricing_model, deal_ccy, buy_ccy, sell_ccy, ';
p_select_stmt VARCHAR2(4000);
p_insert_or_update VARCHAR2(1);
p_att_updated_by XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_att_update_date SYSTEM.QRM_DATE_TABLE := SYSTEM.QRM_DATE_TABLE();
p_att_update_login XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_insert_counter NUMBER := 0;
p_update_counter NUMBER := 0;
p_num_of_cols NUMBER := 51; -- number of columns in select clause
p_deal_calc_id_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_deal_no_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_deal_no_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_company_code_insert SYSTEM.QRM_VARCHAR_TABLE := SYSTEM.QRM_VARCHAR_TABLE();
p_company_code_update SYSTEM.QRM_VARCHAR_TABLE := SYSTEM.QRM_VARCHAR_TABLE();
p_transaction_no_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_transaction_no_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_market_data_set_insert SYSTEM.QRM_VARCHAR_TABLE := SYSTEM.QRM_VARCHAR_TABLE();
p_market_data_set_update SYSTEM.QRM_VARCHAR_TABLE := SYSTEM.QRM_VARCHAR_TABLE();
p_deal_ccy_insert SYSTEM.QRM_VARCHAR_TABLE := SYSTEM.QRM_VARCHAR_TABLE();
p_deal_ccy_update SYSTEM.QRM_VARCHAR_TABLE := SYSTEM.QRM_VARCHAR_TABLE();
p_sob_ccy_insert SYSTEM.QRM_VARCHAR_TABLE := SYSTEM.QRM_VARCHAR_TABLE();
p_sob_ccy_update SYSTEM.QRM_VARCHAR_TABLE := SYSTEM.QRM_VARCHAR_TABLE();
p_base_ccy_amount_usd_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_base_ccy_amount_usd_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_base_ccy_amount_sob_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_base_ccy_amount_sob_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_contra_ccy_amount_usd_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_contra_ccy_amount_usd_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_contra_ccy_amount_sob_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_contra_ccy_amount_sob_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_foreign_amount_usd_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_foreign_amount_usd_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_foreign_amount_sob_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_foreign_amount_sob_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_domestic_amount_usd_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_domestic_amount_usd_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_domestic_amount_sob_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_domestic_amount_sob_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_buy_amount_usd_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_buy_amount_usd_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_buy_amount_sob_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_buy_amount_sob_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_sell_amount_usd_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_sell_amount_usd_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_sell_amount_sob_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_sell_amount_sob_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_days_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_days_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_fx_reval_rate_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_fx_reval_rate_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_mm_reval_rate_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_mm_reval_rate_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_fx_trans_rate_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_fx_trans_rate_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_trans_price_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_trans_price_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_trans_price_sob_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_trans_price_sob_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_trans_price_usd_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_trans_price_usd_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_reval_price_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_reval_price_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_reval_price_sob_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_reval_price_sob_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_reval_price_usd_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_reval_price_usd_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_mm_trans_rate_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_mm_trans_rate_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_fair_value_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_fair_value_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_fair_value_usd_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_fair_value_usd_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_fair_value_sob_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_fair_value_sob_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_gap_amount_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_gap_amount_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_gap_amount_usd_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_gap_amount_usd_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_gap_amount_sob_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_gap_amount_sob_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_maturity_amount_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_maturity_amount_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_maturity_amount_usd_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_maturity_amount_usd_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_maturity_amount_sob_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_maturity_amount_sob_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_premium_amount_usd_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_premium_amount_usd_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_premium_amount_sob_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_premium_amount_sob_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_accrued_interest_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_accrued_interest_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_accrued_interest_usd_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_accrued_interest_usd_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_accrued_interest_sob_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_accrued_interest_sob_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_duration_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_duration_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_mod_duration_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_mod_duration_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_convexity_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_convexity_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_delta_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_delta_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_gamma_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_gamma_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_theta_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_theta_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_rho_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_rho_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_rho_base_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_rho_base_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_rho_contra_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_rho_contra_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_rho_for_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_rho_for_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_rho_dom_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_rho_dom_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_vega_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_vega_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_bpv_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_bpv_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_bpv_usd_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_bpv_usd_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_bpv_sob_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_bpv_sob_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_delta_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_delta_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_delta_usd_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_delta_usd_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_delta_sob_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_delta_sob_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_gamma_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_gamma_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_gamma_usd_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_gamma_usd_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_gamma_sob_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_gamma_sob_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_theta_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_theta_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_theta_usd_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_theta_usd_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_theta_sob_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_theta_sob_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_usd_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_usd_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_sob_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_sob_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_base_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_base_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_base_usd_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_base_usd_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_base_sob_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_base_sob_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_contra_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_contra_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_contra_usd_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_contra_usd_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_contra_sob_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_contra_sob_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_for_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_for_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_for_usd_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_for_usd_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_for_sob_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_for_sob_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_dom_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_dom_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_dom_usd_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_dom_usd_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_dom_sob_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_rho_dom_sob_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_vega_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_vega_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_vega_usd_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_vega_usd_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_vega_sob_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_pos_vega_sob_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_volatility_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_volatility_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_ytm_insert XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_ytm_update XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
p_gap_date_insert SYSTEM.QRM_DATE_TABLE :=
SYSTEM.QRM_DATE_TABLE();
p_gap_date_update SYSTEM.QRM_DATE_TABLE :=
SYSTEM.QRM_DATE_TABLE();
p_upd_md_calc_date_update SYSTEM.QRM_DATE_TABLE :=
SYSTEM.QRM_DATE_TABLE();
p_select_clause := p_select_clause||'foreign_ccy, domestic_ccy, base_ccy, contra_ccy, premium_ccy, buy_amount, sell_amount, foreign_amount, domestic_amount, ';
p_select_clause := p_select_clause||'base_ccy_amount, contra_ccy_amount, start_amount, face_value, interest, accum_interest_bf, accum_int_action, ';
p_select_clause := p_select_clause||'accrued_interest, interest_settled, deal_date, end_date, gap_date, deal_subtype, deal_type, discount_yield, ';
p_select_clause := p_select_clause||'maturity_date, no_of_days, settle_date, premium_amount, phy_start_date, initial_basis, bond_issue_code, ';
p_select_clause := p_select_clause||'coupon_action, coupon_rate, margin, transaction_rate, coupon_frequency, next_coupon_date, day_count_basis, ';
p_select_clause := p_select_clause||'quantity_remaining, rounding_type, day_count_type, prepaid_interest ';
IN (select deal_no, max(transaction_no) from qrm_current_deals_v where NOT(accrued_interest=interest_settled
and face_value=0) group by deal_no)))';
p_from_where_clause := p_from_where_clause||' OR (deal_type=''IG'' and (deal_no, transaction_no) IN (select deal_no, max(transaction_no) from qrm_current_deals_v b where ';
p_from_where_clause := p_from_where_clause||' (deal_no,deal_date) = (select deal_no,max(deal_date) from qrm_current_deals_v c where b.deal_no = c.deal_no group by deal_no) group by b.deal_no) ';
DELETE
FROM qrm_analysis_settings
WHERE analysis_name = p_analysis_name and history_flag='S';
XTR_RISK_DEBUG_PKG.dlog('DML','Deleted analysis row with history_flag=S',
'QRM_PA_CALCULATION_P.RUN_ANALYSIS',g_event_level);
DELETE
FROM qrm_analysis_atts
WHERE analysis_name = p_analysis_name and history_flag='S';
XTR_RISK_DEBUG_PKG.dlog('DML','Deleted attributes rows with history_flag=S',
'QRM_PA_CALCULATION_P.RUN_ANALYSIS',g_event_level);
p_settings.last_updated_by||','||p_settings.last_update_date||','||
p_settings.last_update_login;
XTR_RISK_DEBUG_PKG.dlog('INSERT QRM_ANALYSIS_SETTINGS',v_log,
'QRM_PA_CALCULATION_P.RUN_ANALYSIS',g_state_level);
INSERT
INTO qrm_analysis_settings(analysis_name, description, analysis_type,
style, filter_name, md_set_code, threshold_num, threshold_type,
tb_label, tb_name, date_type, start_date, end_date,
start_date_ref, end_date_ref, start_date_offset, end_date_offset,
start_offset_type, end_offset_type, show_totals, currency_source,
curr_reporting, currency_used, units, dec_places, status, process_id,
last_run_date, dirty, business_week, gl_calendar_id, history_flag,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login)
VALUES (p_settings.analysis_name, p_settings.description,
p_settings.analysis_type, p_settings.style, p_settings.filter_name,
p_settings.md_set_code, p_settings.threshold_num,
p_settings.threshold_type, p_settings.tb_label, p_settings.tb_name,
p_settings.date_type, p_from_date, p_to_date,
p_settings.start_date_ref, p_settings.end_date_ref,
p_settings.start_date_offset, p_settings.end_date_offset,
p_settings.start_offset_type, p_settings.end_offset_type,
p_settings.show_totals, p_settings.currency_source,
p_settings.curr_reporting, p_settings.currency_used, p_settings.units,
p_settings.dec_places, '3', p_settings.process_id,
p_settings.last_run_date, p_settings.dirty, p_settings.business_week,
p_settings.gl_calendar_id, 'S',
p_settings.created_by, p_settings.creation_date,
p_settings.last_updated_by, p_settings.last_update_date,
p_settings.last_update_login);
XTR_RISK_DEBUG_PKG.dlog('DML','Inserted into QRM_ANALYSIS_SETTINGS attributes with history_flag=S',
'QRM_CALCULATION_P.RUN_ANALYSIS',g_event_level);
p_att_updated_by.EXTEND;
p_att_updated_by(p_att_counter) := atts_rec.last_updated_by;
p_att_update_date.EXTEND;
p_att_update_date(p_att_counter) := atts_rec.last_update_date;
p_att_update_login.EXTEND;
p_att_update_login(p_att_counter) := atts_rec.last_update_login;
XTR_RISK_DEBUG_PKG.dlog('INSERT QRM_ANALYSIS_ATTS',v_log,
'QRM_PA_CALCULATION_P.RUN_ANALYSIS',g_state_level);
INSERT
INTO qrm_analysis_atts (attribute_name, analysis_name, history_flag,
type, att_order, total_average, total_ind, percentage, created_by,
creation_date, last_updated_by, last_update_date, last_update_login)
VALUES (p_att_name(i), p_analysis_name, 'S', p_att_type(i),
p_att_order(i), p_att_average(i), p_att_ind(i), p_att_percentage(i),
p_att_created_by(i), p_att_creation_date(i), p_att_updated_by(i),
p_att_update_date(i), p_att_update_login(i));
XTR_RISK_DEBUG_PKG.dlog('DML','Inserted into QRM_ANALYSIS_ATTS attributes with history_flag=S',
'QRM_CALCULATION_P.RUN_ANALYSIS',g_event_level);
UPDATE qrm_analysis_settings
SET status = '3',
process_id = FND_GLOBAL.conc_request_id,
last_run_date = p_ref_datetime,
dirty = 'N'
WHERE analysis_name = p_analysis_name;
XTR_RISK_DEBUG_PKG.dlog('DML','Updated status column to 3 (In Progress)',
'QRM_CALCULATION_P.RUN_ANALYSIS',g_event_level);
XTR_RISK_DEBUG_PKG.dlog('run_analysis: ' || 'creating select stmt...');
p_select_clause := p_select_clause||' :'||i;
p_select_clause := p_select_clause||',';
p_select_stmt := p_select_clause || p_from_where_clause;
DELETE
FROM qrm_deals_analyses
WHERE analysis_name=p_analysis_name;
XTR_RISK_DEBUG_PKG.dlog('DML','DELETED QRM_DEALS_ANALYSES',
'QRM_CALCULATION_P.RUN_ANALYSIS',g_event_level);
p_select_stmt := p_select_stmt||p_appended_where||p_filter_where_clause;
dbms_sql.parse(v_cursor,p_select_stmt,dbms_sql.native);
p_select_stmt := 'BEGIN '||p_select_stmt||';END;';
XTR_RISK_DEBUG_PKG.dlog('run_analysis: ' || 'select stmt: ', p_select_stmt);
EXECUTE IMMEDIATE p_select_stmt USING OUT p_deal_nos,
OUT p_transaction_nos, OUT p_market_data_sets, OUT p_company_codes,
OUT p_calls_or_puts, OUT p_pricing_models, OUT p_deal_ccys,
OUT p_buy_ccys, OUT p_sell_ccys, OUT p_foreign_ccys,
OUT p_domestic_ccys, OUT p_base_ccys, OUT p_contra_ccys,
OUT p_premium_ccys,
OUT p_buy_amounts, OUT p_sell_amounts, OUT p_foreign_amounts,
OUT p_domestic_amounts, OUT p_base_amounts,OUT p_contra_amounts,
OUT p_start_amounts, OUT p_face_values, OUT p_interests,
OUT p_accum_int_bfs, OUT p_accum_int_actions, OUT p_accrued_interests,
OUT p_interests_settled, OUT p_deal_dates, OUT p_end_dates,
OUT p_gap_dates, OUT p_deal_subtypes, OUT p_deal_types,
OUT p_discount_yields, OUT p_maturity_dates, OUT p_no_of_days,
OUT p_settle_dates, OUT p_premium_amounts, OUT p_start_dates,
OUT p_initial_bases, OUT p_bond_issues,
OUT p_coupon_actions, OUT p_coupon_rates, OUT p_margins,
OUT p_transaction_rates, OUT p_coupon_freqs,
OUT p_next_coupon_dates, OUT p_day_count_bases, OUT p_quantity_out,
OUT p_rounding_type, OUT p_day_count_type, OUT p_prepaid_interests,
IN p_from_date,
IN p_ref_date, IN p_to_date;
p_select_stmt := p_select_stmt||p_appended_where
||p_filter_where_clause;
dbms_sql.parse(v_cursor,p_select_stmt,dbms_sql.native);
p_select_stmt := 'BEGIN '||p_select_stmt||';END;';
XTR_RISK_DEBUG_PKG.dlog('run_analysis: ' || 'select stmt: ', p_select_stmt);
EXECUTE IMMEDIATE p_select_stmt USING OUT p_deal_nos,
OUT p_transaction_nos, OUT p_market_data_sets,
OUT p_company_codes, OUT p_calls_or_puts, OUT p_pricing_models,
OUT p_deal_ccys, OUT p_buy_ccys, OUT p_sell_ccys,
OUT p_foreign_ccys, OUT p_domestic_ccys, OUT p_base_ccys,
OUT p_contra_ccys, OUT p_premium_ccys, OUT p_buy_amounts,
OUT p_sell_amounts, OUT p_foreign_amounts, OUT p_domestic_amounts,
OUT p_base_amounts,OUT p_contra_amounts, OUT p_start_amounts,
OUT p_face_values, OUT p_interests, OUT p_accum_int_bfs,
OUT p_accum_int_actions, OUT p_accrued_interests,
OUT p_interests_settled, OUT p_deal_dates, OUT p_end_dates,
OUT p_gap_dates, OUT p_deal_subtypes, OUT p_deal_types,
OUT p_discount_yields, OUT p_maturity_dates, OUT p_no_of_days,
OUT p_settle_dates, OUT p_premium_amounts, OUT p_start_dates,
OUT p_initial_bases, OUT p_bond_issues, OUT p_coupon_actions,
OUT p_coupon_rates, OUT p_margins, OUT p_transaction_rates,
OUT p_coupon_freqs, OUT p_next_coupon_dates,
OUT p_day_count_bases, OUT p_quantity_out,
OUT p_rounding_type, OUT p_day_count_type, OUT p_prepaid_interests,
IN p_to_date, IN p_from_date;
p_select_stmt := p_select_stmt||p_appended_where
||p_filter_where_clause;
dbms_sql.parse(v_cursor,p_select_stmt,dbms_sql.native);
p_select_stmt := 'BEGIN '||p_select_stmt||';END;';
XTR_RISK_DEBUG_PKG.dlog('run_analysis: ' || 'select stmt: ', p_select_stmt);
EXECUTE IMMEDIATE p_select_stmt USING OUT p_deal_nos,
OUT p_transaction_nos, OUT p_market_data_sets,
OUT p_company_codes, OUT p_calls_or_puts, OUT p_pricing_models,
OUT p_deal_ccys, OUT p_buy_ccys, OUT p_sell_ccys,
OUT p_foreign_ccys, OUT p_domestic_ccys, OUT p_base_ccys,
OUT p_contra_ccys, OUT p_premium_ccys, OUT p_buy_amounts,
OUT p_sell_amounts, OUT p_foreign_amounts, OUT p_domestic_amounts,
OUT p_base_amounts,OUT p_contra_amounts, OUT p_start_amounts,
OUT p_face_values, OUT p_interests, OUT p_accum_int_bfs,
OUT p_accum_int_actions, OUT p_accrued_interests,
OUT p_interests_settled, OUT p_deal_dates, OUT p_end_dates,
OUT p_gap_dates, OUT p_deal_subtypes, OUT p_deal_types,
OUT p_discount_yields, OUT p_maturity_dates, OUT p_no_of_days,
OUT p_settle_dates, OUT p_premium_amounts, OUT p_start_dates,
OUT p_initial_bases, OUT p_bond_issues, OUT p_coupon_actions,
OUT p_coupon_rates, OUT p_margins, OUT p_transaction_rates,
OUT p_coupon_freqs, OUT p_next_coupon_dates,
OUT p_day_count_bases, OUT p_quantity_out,
OUT p_rounding_type, OUT p_day_count_type,
OUT p_prepaid_interests, IN p_to_date;
p_select_stmt := p_select_stmt||p_appended_where||p_filter_where_clause;
dbms_sql.parse(v_cursor,p_select_stmt,dbms_sql.native);
p_select_stmt := 'BEGIN '||p_select_stmt||';END;';
XTR_RISK_DEBUG_PKG.dlog('run_analysis: ' || 'select stmt: ', p_select_stmt);
EXECUTE IMMEDIATE p_select_stmt USING OUT p_deal_nos,
OUT p_transaction_nos, OUT p_market_data_sets,
OUT p_company_codes, OUT p_calls_or_puts,
OUT p_pricing_models, OUT p_deal_ccys, OUT p_buy_ccys,
OUT p_sell_ccys, OUT p_foreign_ccys, OUT p_domestic_ccys,
OUT p_base_ccys, OUT p_contra_ccys, OUT p_premium_ccys,
OUT p_buy_amounts,
OUT p_sell_amounts, OUT p_foreign_amounts, OUT p_domestic_amounts,
OUT p_base_amounts, OUT p_contra_amounts, OUT p_start_amounts,
OUT p_face_values, OUT p_interests, OUT p_accum_int_bfs,
OUT p_accum_int_actions, OUT p_accrued_interests,
OUT p_interests_settled, OUT p_deal_dates, OUT p_end_dates,
OUT p_gap_dates, OUT p_deal_subtypes, OUT p_deal_types,
OUT p_discount_yields, OUT p_maturity_dates, OUT p_no_of_days,
OUT p_settle_dates, OUT p_premium_amounts, OUT p_start_dates,
OUT p_initial_bases, OUT p_bond_issues,
OUT p_coupon_actions, OUT p_coupon_rates, OUT p_margins,
OUT p_transaction_rates, OUT p_coupon_freqs,
OUT p_next_coupon_dates, OUT p_day_count_bases, OUT p_quantity_out,
OUT p_rounding_type, OUT p_day_count_type, OUT p_prepaid_interests;
XTR_RISK_DEBUG_PKG.dlog('run_analysis: ' || 'select stmt: ', p_select_stmt);
XTR_RISK_DEBUG_PKG.dlog('run_analysis: ' || 'made it past select statement');
p_insert_counter := 0;
p_update_counter := 0;
p_seq_nos.DELETE;
p_deal_no_insert.DELETE;
p_company_code_insert.DELETE;
p_transaction_no_insert.DELETE;
p_market_data_set_insert.DELETE;
p_deal_ccy_insert.DELETE;
p_sob_ccy_insert.DELETE;
p_base_ccy_amount_usd_insert.DELETE;
p_base_ccy_amount_sob_insert.DELETE;
p_contra_ccy_amount_usd_insert.DELETE;
p_contra_ccy_amount_sob_insert.DELETE;
p_foreign_amount_usd_insert.DELETE;
p_foreign_amount_sob_insert.DELETE;
p_domestic_amount_usd_insert.DELETE;
p_domestic_amount_sob_insert.DELETE;
p_buy_amount_usd_insert.DELETE;
p_buy_amount_sob_insert.DELETE;
p_sell_amount_usd_insert.DELETE;
p_sell_amount_sob_insert.DELETE;
p_days_insert.DELETE;
p_fx_reval_rate_insert.DELETE;
p_reval_price_insert.DELETE;
p_reval_price_usd_insert.DELETE;
p_reval_price_sob_insert.DELETE;
p_mm_reval_rate_insert.DELETE;
p_fx_trans_rate_insert.DELETE;
p_trans_price_insert.DELETE;
p_trans_price_usd_insert.DELETE;
p_trans_price_sob_insert.DELETE;
p_mm_trans_rate_insert.DELETE;
p_fair_value_insert.DELETE;
p_fair_value_usd_insert.DELETE;
p_fair_value_sob_insert.DELETE;
p_gap_amount_insert.DELETE;
p_gap_amount_usd_insert.DELETE;
p_gap_amount_sob_insert.DELETE;
p_maturity_amount_insert.DELETE;
p_maturity_amount_usd_insert.DELETE;
p_maturity_amount_sob_insert.DELETE;
p_premium_amount_usd_insert.DELETE;
p_premium_amount_sob_insert.DELETE;
p_accrued_interest_insert.DELETE;
p_accrued_interest_usd_insert.DELETE;
p_accrued_interest_sob_insert.DELETE;
p_duration_insert.DELETE;
p_mod_duration_insert.DELETE;
p_convexity_insert.DELETE;
p_delta_insert.DELETE;
p_gamma_insert.DELETE;
p_theta_insert.DELETE;
p_rho_insert.DELETE;
p_rho_base_insert.DELETE;
p_rho_contra_insert.DELETE;
p_rho_for_insert.DELETE;
p_rho_dom_insert.DELETE;
p_vega_insert.DELETE;
p_pos_bpv_insert.DELETE;
p_pos_bpv_usd_insert.DELETE;
p_pos_bpv_sob_insert.DELETE;
p_pos_delta_insert.DELETE;
p_pos_delta_usd_insert.DELETE;
p_pos_delta_sob_insert.DELETE;
p_pos_gamma_insert.DELETE;
p_pos_gamma_usd_insert.DELETE;
p_pos_gamma_sob_insert.DELETE;
p_pos_theta_insert.DELETE;
p_pos_theta_usd_insert.DELETE;
p_pos_theta_sob_insert.DELETE;
p_pos_rho_insert.DELETE;
p_pos_rho_usd_insert.DELETE;
p_pos_rho_sob_insert.DELETE;
p_pos_rho_base_insert.DELETE;
p_pos_rho_base_usd_insert.DELETE;
p_pos_rho_base_sob_insert.DELETE;
p_pos_rho_contra_insert.DELETE;
p_pos_rho_contra_usd_insert.DELETE;
p_pos_rho_contra_sob_insert.DELETE;
p_pos_rho_for_insert.DELETE;
p_pos_rho_for_usd_insert.DELETE;
p_pos_rho_for_sob_insert.DELETE;
p_pos_rho_dom_insert.DELETE;
p_pos_rho_dom_usd_insert.DELETE;
p_pos_rho_dom_sob_insert.DELETE;
p_pos_vega_insert.DELETE;
p_pos_vega_usd_insert.DELETE;
p_pos_vega_sob_insert.DELETE;
p_volatility_insert.DELETE;
p_ytm_insert.DELETE;
p_gap_date_insert.DELETE;
p_deal_calc_id_update.DELETE;
p_deal_no_update.DELETE;
p_company_code_update.DELETE;
p_transaction_no_update.DELETE;
p_market_data_set_update.DELETE;
p_deal_ccy_update.DELETE;
p_sob_ccy_update.DELETE;
p_base_ccy_amount_usd_update.DELETE;
p_base_ccy_amount_sob_update.DELETE;
p_contra_ccy_amount_usd_update.DELETE;
p_contra_ccy_amount_sob_update.DELETE;
p_foreign_amount_usd_update.DELETE;
p_foreign_amount_sob_update.DELETE;
p_domestic_amount_usd_update.DELETE;
p_domestic_amount_sob_update.DELETE;
p_buy_amount_usd_update.DELETE;
p_buy_amount_sob_update.DELETE;
p_sell_amount_usd_update.DELETE;
p_sell_amount_sob_update.DELETE;
p_days_update.DELETE;
p_fx_reval_rate_update.DELETE;
p_reval_price_update.DELETE;
p_reval_price_usd_update.DELETE;
p_reval_price_sob_update.DELETE;
p_mm_reval_rate_update.DELETE;
p_fx_trans_rate_update.DELETE;
p_trans_price_update.DELETE;
p_trans_price_usd_update.DELETE;
p_trans_price_sob_update.DELETE;
p_mm_trans_rate_update.DELETE;
p_fair_value_update.DELETE;
p_fair_value_usd_update.DELETE;
p_fair_value_sob_update.DELETE;
p_gap_amount_update.DELETE;
p_gap_amount_usd_update.DELETE;
p_gap_amount_sob_update.DELETE;
p_maturity_amount_update.DELETE;
p_maturity_amount_usd_update.DELETE;
p_maturity_amount_sob_update.DELETE;
p_premium_amount_usd_update.DELETE;
p_premium_amount_sob_update.DELETE;
p_accrued_interest_update.DELETE;
p_accrued_interest_usd_update.DELETE;
p_accrued_interest_sob_update.DELETE;
p_duration_update.DELETE;
p_mod_duration_update.DELETE;
p_convexity_update.DELETE;
p_delta_update.DELETE;
p_gamma_update.DELETE;
p_theta_update.DELETE;
p_rho_update.DELETE;
p_rho_base_update.DELETE;
p_rho_contra_update.DELETE;
p_rho_for_update.DELETE;
p_rho_dom_update.DELETE;
p_vega_update.DELETE;
p_pos_bpv_update.DELETE;
p_pos_bpv_usd_update.DELETE;
p_pos_bpv_sob_update.DELETE;
p_pos_delta_update.DELETE;
p_pos_delta_usd_update.DELETE;
p_pos_delta_sob_update.DELETE;
p_pos_gamma_update.DELETE;
p_pos_gamma_usd_update.DELETE;
p_pos_gamma_sob_update.DELETE;
p_pos_theta_update.DELETE;
p_pos_theta_usd_update.DELETE;
p_pos_theta_sob_update.DELETE;
p_pos_rho_update.DELETE;
p_pos_rho_usd_update.DELETE;
p_pos_rho_sob_update.DELETE;
p_pos_rho_base_update.DELETE;
p_pos_rho_base_usd_update.DELETE;
p_pos_rho_base_sob_update.DELETE;
p_pos_rho_contra_update.DELETE;
p_pos_rho_contra_usd_update.DELETE;
p_pos_rho_contra_sob_update.DELETE;
p_pos_rho_for_update.DELETE;
p_pos_rho_for_usd_update.DELETE;
p_pos_rho_for_sob_update.DELETE;
p_pos_rho_dom_update.DELETE;
p_pos_rho_dom_usd_update.DELETE;
p_pos_rho_dom_sob_update.DELETE;
p_pos_vega_update.DELETE;
p_pos_vega_usd_update.DELETE;
p_pos_vega_sob_update.DELETE;
p_volatility_update.DELETE;
p_ytm_update.DELETE;
p_gap_date_update.DELETE;
p_upd_md_calc_date_update.DELETE;
p_tb_deal_no.DELETE;
p_tb_transaction_no.DELETE;
p_tb_market_data_set.DELETE;
p_tb_pos_start_date.DELETE;
p_tb_pos_end_date.DELETE;
p_tb_start_date.DELETE;
p_tb_end_date.DELETE;
p_tb_outst_amount.DELETE;
p_tb_outst_amount_usd.DELETE;
p_tb_outst_amount_sob.DELETE;
p_tb_amt_qty_out.DELETE;
p_tb_amt_qty_out_usd.DELETE;
p_tb_amt_qty_out_sob.DELETE;
p_tb_interest_basis.DELETE;
p_tb_coupon_rate.DELETE;
p_tb_next_coupon_date.DELETE;
FOR i IN 1..n_num_rows_fetched LOOP -- loop through each selected deal
BEGIN
-- logic for determing market data set:
-- if mds specified in settings, use it for all deals
-- else, if mds defined on deal level, use it
-- else, use mds defined on company level (is always defined there)
IF (p_settings.md_set_code IS NOT NULL) THEN
p_market_data_set_temp(i) := p_settings.md_set_code;
XTR_RISK_DEBUG_PKG.dlog('run_analysis: ' || 'doesnt meet threshold, need to update');
p_insert_or_update := 'U'; -- update calculations
p_insert_or_update := 'N'; -- threshold met, do nothing
p_insert_or_update := 'I'; -- insert deal calculations
IF (p_insert_or_update <> 'N') THEN
QRM_FX_FORMULAS.fv_fxo(p_pricing_models(i),
p_deal_subtypes(i), p_calls_or_puts(i),
p_market_data_set_temp(i), p_foreign_ccys(i),
p_premium_ccys(i), p_buy_ccys(i),
p_sell_ccys(i),p_interpolation_method,p_ref_date,
p_end_dates(i),p_transaction_rates(i),
p_foreign_amounts(i), p_side,
p_fx_reval_rate_temp(i), p_fair_value_temp(i));
p_insert_or_update := 'U';
IF (p_insert_or_update <> 'N') THEN
-- FAIR VALUE of FX deal
-- fair value of FX is always in SOB ccy
QRM_FX_FORMULAS.fv_fx(p_pricing_models(i),
p_market_data_set_temp(i), p_buy_ccys(i),
p_sell_ccys(i), p_sob_ccy_temp(i),
p_interpolation_method,
p_ref_date, p_end_dates(i), p_buy_amounts(i),
p_sell_amounts(i), p_side, p_fx_reval_rate_temp(i),
p_fair_value_temp(i));
p_insert_or_update := 'U';
-- if select ZERO COUPON type, coupon rate must be 0
IF (p_bond_calc_type = 'ZERO COUPON') THEN
p_coupon_rates(i) := 0;
xtr_risk_debug_pkg.dlog('run_analysis: ' || 'insert/update val: '||p_insert_or_update);
IF (p_insert_or_update <> 'N') THEN
p_mm_reval_rate_temp(i):= null;
p_cashflows.DELETE;
p_days.DELETE;
p_insert_or_update := 'U';
xtr_risk_debug_pkg.dlog('run_analysis: ' || 'insert/update val: '||p_insert_or_update);
IF (p_insert_or_update <> 'N') THEN
QRM_EQ_FORMULAS.fv_stock(p_pricing_models(i), p_deal_ccys(i),
p_bond_issues(i), p_market_data_set_temp(i),
p_mm_trans_rate_temp(i), p_quantity_out(i),
p_ref_date, p_fair_value_temp(i),
p_reval_price_temp(i));
p_insert_or_update := 'U';
IF (p_insert_or_update <> 'N') THEN
-- fair value, data side, reval rate (in ACT/365)
QRM_MM_FORMULAS.fv_ni(p_pricing_models(i),
p_market_data_set_temp(i), p_deal_subtypes(i),
p_discount_yields(i), p_deal_ccys(i),
p_interpolation_method, p_day_count_bases(i),
p_ref_date, p_start_dates(i), p_maturity_dates(i),
p_face_values(i), p_margins(i), p_side,
p_mm_reval_rate_temp(i), p_fair_value_temp(i));
p_days.DELETE;
p_insert_or_update := 'U';
p_cashflows.DELETE;
p_days.DELETE;
p_trans_trans_nos.DELETE;
p_trans_start_dates.DELETE;
p_trans_maturity_dates.DELETE;
p_trans_settle_dates.DELETE;
p_trans_due_on_dates.DELETE; -- prepaid interest
p_trans_interest_refunds.DELETE; -- prepaid interest
p_trans_prin_actions.DELETE;
p_trans_yield_rates.DELETE;
p_trans_disc_rates.DELETE;
p_trans_interest_settled.DELETE;
p_trans_prin_adjusts.DELETE;
p_trans_accum_interests.DELETE;
p_trans_accum_interests_bf.DELETE;
p_trans_balance_outs.DELETE;
p_trans_settle_term_interest.DELETE;
IF (p_insert_or_update <> 'N') THEN
IF (p_ok_trans) THEN
-- get last transaction number
OPEN get_last_trans_no(p_deal_nos(i));
p_insert_or_update := 'U';
IF (p_insert_or_update <> 'N') THEN
-- fair value (reval rate=fra price=contract rate, in Act/365)
QRM_MM_FORMULAS.fv_fra(p_pricing_models(i),
p_market_data_set_temp(i), 'N', p_deal_subtypes(i),
p_deal_ccys(i), p_interpolation_method, p_ref_date,
p_start_dates(i), p_maturity_dates(i),
p_face_values(i), p_transaction_rates(i),
p_day_count_bases(i), p_side,
p_mm_reval_rate_temp(i), p_fair_value_temp(i));
p_days.DELETE;
p_insert_or_update := 'U';
IF (p_insert_or_update <> 'N') THEN
-- fair value, reval rate (= fwd fwd rate in Act/365)
IF (g_proc_level>=g_debug_level) THEN
XTR_RISK_DEBUG_PKG.dlog('run_analysis: ' || 'IRO deal ccy is: '||p_deal_ccys(i));
p_insert_or_update := 'U';
IF (p_insert_or_update <> 'N') THEN
-- accrued interest
p_accrued_interest_temp(i) :=
QRM_MM_FORMULAS.calculate_accrued_interest('R',
p_ref_date, p_start_dates(i),
p_maturity_dates(i), p_transaction_rates(i),
p_interests(i), p_accum_int_bfs(i),
p_face_values(i), p_no_of_days(i),
p_day_count_bases(i), p_accum_int_actions(i));
p_insert_or_update := 'U';
IF (p_insert_or_update <> 'N') THEN
-- accrued interest
IF (g_proc_level>=g_debug_level) THEN
XTR_RISK_DEBUG_PKG.dlog('run_analysis: ' || 'IG DCB: '||p_day_count_bases(i));
p_insert_or_update := 'U';
----- Place attributes in insert/update array ----------------
IF(p_insert_or_update='I') THEN -- insert deal
p_insert_counter := p_insert_counter + 1;
FETCH get_sequence_no INTO p_seq_nos(p_insert_counter);
p_deal_no_insert.EXTEND;
p_deal_no_insert(p_insert_counter):=p_deal_nos(i);
p_company_code_insert.EXTEND;
p_company_code_insert(p_insert_counter):=p_company_code_temp(i);
p_transaction_no_insert.EXTEND;
p_transaction_no_insert(p_insert_counter):=p_transaction_nos(i);
p_market_data_set_insert.EXTEND;
p_market_data_set_insert(p_insert_counter):=p_market_data_set_temp(i);
p_deal_ccy_insert.EXTEND;
p_deal_ccy_insert(p_insert_counter):=p_deal_ccy_temp(i);
p_sob_ccy_insert.EXTEND;
p_sob_ccy_insert(p_insert_counter):=p_sob_ccy_temp(i);
p_base_ccy_amount_usd_insert.EXTEND;
p_base_ccy_amount_usd_insert(p_insert_counter):=p_base_ccy_amount_usd_temp(i);
p_base_ccy_amount_sob_insert.EXTEND;
p_base_ccy_amount_sob_insert(p_insert_counter):=p_base_ccy_amount_sob_temp(i);
p_contra_ccy_amount_usd_insert.EXTEND;
p_contra_ccy_amount_usd_insert(p_insert_counter):=p_contra_ccy_amount_usd_temp(i);
p_contra_ccy_amount_sob_insert.EXTEND;
p_contra_ccy_amount_sob_insert(p_insert_counter):=p_contra_ccy_amount_sob_temp(i);
p_foreign_amount_usd_insert.EXTEND;
p_foreign_amount_usd_insert(p_insert_counter):=p_foreign_amount_usd_temp(i);
p_foreign_amount_sob_insert.EXTEND;
p_foreign_amount_sob_insert(p_insert_counter):=p_foreign_amount_sob_temp(i);
p_domestic_amount_usd_insert.EXTEND;
p_domestic_amount_usd_insert(p_insert_counter):=p_domestic_amount_usd_temp(i);
p_domestic_amount_sob_insert.EXTEND;
p_domestic_amount_sob_insert(p_insert_counter):=p_domestic_amount_sob_temp(i);
p_buy_amount_usd_insert.EXTEND;
p_buy_amount_usd_insert(p_insert_counter):=p_buy_amount_usd_temp(i);
p_buy_amount_sob_insert.EXTEND;
p_buy_amount_sob_insert(p_insert_counter):=p_buy_amount_sob_temp(i);
p_sell_amount_usd_insert.EXTEND;
p_sell_amount_usd_insert(p_insert_counter):=p_sell_amount_usd_temp(i);
p_sell_amount_sob_insert.EXTEND;
p_sell_amount_sob_insert(p_insert_counter):=p_sell_amount_sob_temp(i);
p_days_insert.EXTEND;
p_days_insert(p_insert_counter):=p_days_temp(i);
p_fx_reval_rate_insert.EXTEND;
p_fx_reval_rate_insert(p_insert_counter):=p_fx_reval_rate_temp(i);
p_reval_price_insert.EXTEND;
p_reval_price_insert(p_insert_counter):=p_reval_price_temp(i);
p_reval_price_usd_insert.EXTEND;
p_reval_price_usd_insert(p_insert_counter):=p_reval_price_usd_temp(i);
p_reval_price_sob_insert.EXTEND;
p_reval_price_sob_insert(p_insert_counter):=p_reval_price_sob_temp(i);
p_mm_reval_rate_insert.EXTEND;
p_mm_reval_rate_insert(p_insert_counter):=p_mm_reval_rate_temp(i);
p_fx_trans_rate_insert.EXTEND;
p_fx_trans_rate_insert(p_insert_counter):=p_fx_trans_rate_temp(i);
p_trans_price_insert.EXTEND;
p_trans_price_insert(p_insert_counter):=p_trans_price_temp(i);
p_trans_price_usd_insert.EXTEND;
p_trans_price_usd_insert(p_insert_counter):=p_trans_price_usd_temp(i);
p_trans_price_sob_insert.EXTEND;
p_trans_price_sob_insert(p_insert_counter):=p_trans_price_sob_temp(i);
p_mm_trans_rate_insert.EXTEND;
p_mm_trans_rate_insert(p_insert_counter):=p_mm_trans_rate_temp(i);
p_fair_value_insert.EXTEND;
p_fair_value_insert(p_insert_counter):=p_fair_value_temp(i);
p_fair_value_usd_insert.EXTEND;
p_fair_value_usd_insert(p_insert_counter):=p_fair_value_usd_temp(i);
p_fair_value_sob_insert.EXTEND;
p_fair_value_sob_insert(p_insert_counter):=p_fair_value_sob_temp(i);
p_gap_amount_insert.EXTEND;
p_gap_amount_insert(p_insert_counter):=p_gap_amount_temp(i);
p_gap_amount_usd_insert.EXTEND;
p_gap_amount_usd_insert(p_insert_counter):=p_gap_amount_usd_temp(i);
p_gap_amount_sob_insert.EXTEND;
p_gap_amount_sob_insert(p_insert_counter):=p_gap_amount_sob_temp(i);
p_maturity_amount_insert.EXTEND;
p_maturity_amount_insert(p_insert_counter):=p_maturity_amount_temp(i);
p_maturity_amount_usd_insert.EXTEND;
p_maturity_amount_usd_insert(p_insert_counter):=p_maturity_amount_usd_temp(i);
p_maturity_amount_sob_insert.EXTEND;
p_maturity_amount_sob_insert(p_insert_counter):=p_maturity_amount_sob_temp(i);
p_premium_amount_usd_insert.EXTEND;
p_premium_amount_usd_insert(p_insert_counter):=p_premium_amount_usd_temp(i);
p_premium_amount_sob_insert.EXTEND;
p_premium_amount_sob_insert(p_insert_counter):=p_premium_amount_sob_temp(i);
p_accrued_interest_insert.EXTEND;
p_accrued_interest_insert(p_insert_counter):=p_accrued_interest_temp(i);
p_accrued_interest_usd_insert.EXTEND;
p_accrued_interest_usd_insert(p_insert_counter):=p_accrued_interest_usd_temp(i);
p_accrued_interest_sob_insert.EXTEND;
p_accrued_interest_sob_insert(p_insert_counter):=p_accrued_interest_sob_temp(i);
p_duration_insert.EXTEND;
p_duration_insert(p_insert_counter):=p_duration_temp(i);
p_mod_duration_insert.EXTEND;
p_mod_duration_insert(p_insert_counter):=p_mod_duration_temp(i);
p_convexity_insert.EXTEND;
p_convexity_insert(p_insert_counter):=p_convexity_temp(i);
p_delta_insert.EXTEND;
p_delta_insert(p_insert_counter):=p_delta_temp(i);
p_gamma_insert.EXTEND;
p_gamma_insert(p_insert_counter):=p_gamma_temp(i);
p_theta_insert.EXTEND;
p_theta_insert(p_insert_counter):=p_theta_temp(i);
p_rho_insert.EXTEND;
p_rho_insert(p_insert_counter) := p_rho_temp(i);
p_rho_base_insert.EXTEND;
p_rho_base_insert(p_insert_counter) := p_rho_base_temp(i);
p_rho_contra_insert.EXTEND;
p_rho_contra_insert(p_insert_counter) := p_rho_contra_temp(i);
p_rho_for_insert.EXTEND;
p_rho_for_insert(p_insert_counter):=p_rho_for_temp(i);
p_rho_dom_insert.EXTEND;
p_rho_dom_insert(p_insert_counter):=p_rho_dom_temp(i);
p_vega_insert.EXTEND;
p_vega_insert(p_insert_counter):=p_vega_temp(i);
p_pos_bpv_insert.EXTEND;
p_pos_bpv_insert(p_insert_counter):=p_pos_bpv_temp(i);
p_pos_bpv_usd_insert.EXTEND;
p_pos_bpv_usd_insert(p_insert_counter) := p_pos_bpv_usd_temp(i);
p_pos_bpv_sob_insert.EXTEND;
p_pos_bpv_sob_insert(p_insert_counter) := p_pos_bpv_sob_temp(i);
p_pos_delta_insert.EXTEND;
p_pos_delta_insert(p_insert_counter):=p_pos_delta_temp(i);
p_pos_delta_usd_insert.EXTEND;
p_pos_delta_usd_insert(p_insert_counter) := p_pos_delta_usd_temp(i);
p_pos_delta_sob_insert.EXTEND;
p_pos_delta_sob_insert(p_insert_counter) := p_pos_delta_sob_temp(i);
p_pos_gamma_insert.EXTEND;
p_pos_gamma_insert(p_insert_counter):=p_pos_gamma_temp(i);
p_pos_gamma_usd_insert.EXTEND;
p_pos_gamma_usd_insert(p_insert_counter) := p_pos_gamma_usd_temp(i);
p_pos_gamma_sob_insert.EXTEND;
p_pos_gamma_sob_insert(p_insert_counter) := p_pos_gamma_sob_temp(i);
p_pos_theta_insert.EXTEND;
p_pos_theta_insert(p_insert_counter):=p_pos_theta_temp(i);
p_pos_theta_usd_insert.EXTEND;
p_pos_theta_usd_insert(p_insert_counter) := p_pos_theta_usd_temp(i);
p_pos_theta_sob_insert.EXTEND;
p_pos_theta_sob_insert(p_insert_counter) := p_pos_theta_sob_temp(i);
p_pos_rho_insert.EXTEND;
p_pos_rho_insert(p_insert_counter) := p_pos_rho_temp(i);
p_pos_rho_usd_insert.EXTEND;
p_pos_rho_usd_insert(p_insert_counter) := p_pos_rho_usd_temp(i);
p_pos_rho_sob_insert.EXTEND;
p_pos_rho_sob_insert(p_insert_counter) := p_pos_rho_sob_temp(i);
p_pos_rho_base_insert.EXTEND;
p_pos_rho_base_insert(p_insert_counter) := p_pos_rho_base_temp(i);
p_pos_rho_base_usd_insert.EXTEND;
p_pos_rho_base_usd_insert(p_insert_counter) := p_pos_rho_base_usd_temp(i);
p_pos_rho_base_sob_insert.EXTEND;
p_pos_rho_base_sob_insert(p_insert_counter) := p_pos_rho_base_sob_temp(i);
p_pos_rho_contra_insert.EXTEND;
p_pos_rho_contra_insert(p_insert_counter) := p_pos_rho_contra_temp(i);
p_pos_rho_contra_usd_insert.EXTEND;
p_pos_rho_contra_usd_insert(p_insert_counter) := p_pos_rho_contra_usd_temp(i);
p_pos_rho_contra_sob_insert.EXTEND;
p_pos_rho_contra_sob_insert(p_insert_counter) := p_pos_rho_contra_sob_temp(i);
p_pos_rho_for_insert.EXTEND;
p_pos_rho_for_insert(p_insert_counter):=p_pos_rho_for_temp(i);
p_pos_rho_for_usd_insert.EXTEND;
p_pos_rho_for_usd_insert(p_insert_counter) := p_pos_rho_for_usd_temp(i);
p_pos_rho_for_sob_insert.EXTEND;
p_pos_rho_for_sob_insert(p_insert_counter) := p_pos_rho_for_sob_temp(i);
p_pos_rho_dom_insert.EXTEND;
p_pos_rho_dom_insert(p_insert_counter):=p_pos_rho_dom_temp(i);
p_pos_rho_dom_usd_insert.EXTEND;
p_pos_rho_dom_usd_insert(p_insert_counter) := p_pos_rho_dom_usd_temp(i);
p_pos_rho_dom_sob_insert.EXTEND;
p_pos_rho_dom_sob_insert(p_insert_counter) := p_pos_rho_dom_sob_temp(i);
p_pos_vega_insert.EXTEND;
p_pos_vega_insert(p_insert_counter):=p_pos_vega_temp(i);
p_pos_vega_usd_insert.EXTEND;
p_pos_vega_usd_insert(p_insert_counter) := p_pos_vega_usd_temp(i);
p_pos_vega_sob_insert.EXTEND;
p_pos_vega_sob_insert(p_insert_counter) := p_pos_vega_sob_temp(i);
p_volatility_insert.EXTEND;
p_volatility_insert(p_insert_counter):=p_volatility_temp(i);
p_ytm_insert.EXTEND;
p_ytm_insert(p_insert_counter):=p_ytm_temp(i);
p_gap_date_insert.EXTEND;
p_gap_date_insert(p_insert_counter):=p_gap_date_temp(i);
ELSE -- update deal
p_update_counter := p_update_counter + 1;
p_deal_calc_id_update.EXTEND;
p_deal_calc_id_update(p_update_counter) := p_deal_calculations.deal_calc_id;
p_deal_no_update.EXTEND;
p_deal_no_update(p_update_counter):=p_deal_nos(i);
p_company_code_update.EXTEND;
p_company_code_update(p_update_counter):=p_company_code_temp(i);
p_transaction_no_update.EXTEND;
p_transaction_no_update(p_update_counter):=p_transaction_nos(i);
p_market_data_set_update.EXTEND;
p_market_data_set_update(p_update_counter):=p_market_data_set_temp(i);
p_deal_ccy_update.EXTEND;
p_deal_ccy_update(p_update_counter):=p_deal_ccy_temp(i);
p_sob_ccy_update.EXTEND;
p_sob_ccy_update(p_update_counter):=p_sob_ccy_temp(i);
p_base_ccy_amount_usd_update.EXTEND;
p_base_ccy_amount_usd_update(p_update_counter):=p_base_ccy_amount_usd_temp(i);
p_base_ccy_amount_sob_update.EXTEND;
p_base_ccy_amount_sob_update(p_update_counter):=p_base_ccy_amount_sob_temp(i);
p_contra_ccy_amount_usd_update.EXTEND;
p_contra_ccy_amount_usd_update(p_update_counter):=p_contra_ccy_amount_usd_temp(i);
p_contra_ccy_amount_sob_update.EXTEND;
p_contra_ccy_amount_sob_update(p_update_counter):=p_contra_ccy_amount_sob_temp(i);
p_foreign_amount_usd_update.EXTEND;
p_foreign_amount_usd_update(p_update_counter):=p_foreign_amount_usd_temp(i);
p_foreign_amount_sob_update.EXTEND;
p_foreign_amount_sob_update(p_update_counter):=p_foreign_amount_sob_temp(i);
p_domestic_amount_usd_update.EXTEND;
p_domestic_amount_usd_update(p_update_counter):=p_domestic_amount_usd_temp(i);
p_domestic_amount_sob_update.EXTEND;
p_domestic_amount_sob_update(p_update_counter):=p_domestic_amount_sob_temp(i);
p_buy_amount_usd_update.EXTEND;
p_buy_amount_usd_update(p_update_counter):=p_buy_amount_usd_temp(i);
p_buy_amount_sob_update.EXTEND;
p_buy_amount_sob_update(p_update_counter):=p_buy_amount_sob_temp(i);
p_sell_amount_usd_update.EXTEND;
p_sell_amount_usd_update(p_update_counter):=p_sell_amount_usd_temp(i);
p_sell_amount_sob_update.EXTEND;
p_sell_amount_sob_update(p_update_counter):=p_sell_amount_sob_temp(i);
p_days_update.EXTEND;
p_days_update(p_update_counter):=p_days_temp(i);
p_fx_reval_rate_update.EXTEND;
p_fx_reval_rate_update(p_update_counter):=p_fx_reval_rate_temp(i);
p_reval_price_update.EXTEND;
p_reval_price_update(p_update_counter):=p_reval_price_temp(i);
p_reval_price_usd_update.EXTEND;
p_reval_price_usd_update(p_update_counter):=p_reval_price_usd_temp(i);
p_reval_price_sob_update.EXTEND;
p_reval_price_sob_update(p_update_counter):=p_reval_price_sob_temp(i);
p_mm_reval_rate_update.EXTEND;
p_mm_reval_rate_update(p_update_counter):=p_mm_reval_rate_temp(i);
p_fx_trans_rate_update.EXTEND;
p_fx_trans_rate_update(p_update_counter):=p_fx_trans_rate_temp(i);
p_trans_price_update.EXTEND;
p_trans_price_update(p_update_counter):=p_trans_price_temp(i);
p_trans_price_usd_update.EXTEND;
p_trans_price_usd_update(p_update_counter):=p_trans_price_usd_temp(i);
p_trans_price_sob_update.EXTEND;
p_trans_price_sob_update(p_update_counter):=p_trans_price_sob_temp(i);
p_mm_trans_rate_update.EXTEND;
p_mm_trans_rate_update(p_update_counter):=p_mm_trans_rate_temp(i);
p_fair_value_update.EXTEND;
p_fair_value_update(p_update_counter):=p_fair_value_temp(i);
p_fair_value_usd_update.EXTEND;
p_fair_value_usd_update(p_update_counter):=p_fair_value_usd_temp(i);
p_fair_value_sob_update.EXTEND;
p_fair_value_sob_update(p_update_counter):=p_fair_value_sob_temp(i);
p_gap_amount_update.EXTEND;
p_gap_amount_update(p_update_counter):=p_gap_amount_temp(i);
p_gap_amount_usd_update.EXTEND;
p_gap_amount_usd_update(p_update_counter):=p_gap_amount_usd_temp(i);
p_gap_amount_sob_update.EXTEND;
p_gap_amount_sob_update(p_update_counter):=p_gap_amount_sob_temp(i);
p_maturity_amount_update.EXTEND;
p_maturity_amount_update(p_update_counter):=p_maturity_amount_temp(i);
p_maturity_amount_usd_update.EXTEND;
p_maturity_amount_usd_update(p_update_counter):=p_maturity_amount_usd_temp(i);
p_maturity_amount_sob_update.EXTEND;
p_maturity_amount_sob_update(p_update_counter):=p_maturity_amount_sob_temp(i);
p_premium_amount_usd_update.EXTEND;
p_premium_amount_usd_update(p_update_counter):=p_premium_amount_usd_temp(i);
p_premium_amount_sob_update.EXTEND;
p_premium_amount_sob_update(p_update_counter):=p_premium_amount_sob_temp(i);
p_accrued_interest_update.EXTEND;
p_accrued_interest_update(p_update_counter):=p_accrued_interest_temp(i);
p_accrued_interest_usd_update.EXTEND;
p_accrued_interest_usd_update(p_update_counter):=p_accrued_interest_usd_temp(i);
p_accrued_interest_sob_update.EXTEND;
p_accrued_interest_sob_update(p_update_counter):=p_accrued_interest_sob_temp(i);
p_duration_update.EXTEND;
p_duration_update(p_update_counter):=p_duration_temp(i);
p_mod_duration_update.EXTEND;
p_mod_duration_update(p_update_counter):=p_mod_duration_temp(i);
p_convexity_update.EXTEND;
p_convexity_update(p_update_counter):=p_convexity_temp(i);
p_delta_update.EXTEND;
p_delta_update(p_update_counter):=p_delta_temp(i);
p_gamma_update.EXTEND;
p_gamma_update(p_update_counter):=p_gamma_temp(i);
p_theta_update.EXTEND;
p_theta_update(p_update_counter):=p_theta_temp(i);
p_rho_update.EXTEND;
p_rho_update(p_update_counter) := p_rho_temp(i);
p_rho_base_update.EXTEND;
p_rho_base_update(p_update_counter) := p_rho_base_temp(i);
p_rho_contra_update.EXTEND;
p_rho_contra_update(p_update_counter) := p_rho_contra_temp(i);
p_rho_for_update.EXTEND;
p_rho_for_update(p_update_counter):=p_rho_for_temp(i);
p_rho_dom_update.EXTEND;
p_rho_dom_update(p_update_counter):=p_rho_dom_temp(i);
p_vega_update.EXTEND;
p_vega_update(p_update_counter):=p_vega_temp(i);
p_pos_bpv_update.EXTEND;
p_pos_bpv_update(p_update_counter):=p_pos_bpv_temp(i);
p_pos_bpv_usd_update.EXTEND;
p_pos_bpv_usd_update(p_update_counter) := p_pos_bpv_usd_temp(i);
p_pos_bpv_sob_update.EXTEND;
p_pos_bpv_sob_update(p_update_counter) := p_pos_bpv_sob_temp(i);
p_pos_delta_update.EXTEND;
p_pos_delta_update(p_update_counter):=p_pos_delta_temp(i);
p_pos_delta_usd_update.EXTEND;
p_pos_delta_usd_update(p_update_counter) := p_pos_delta_usd_temp(i);
p_pos_delta_sob_update.EXTEND;
p_pos_delta_sob_update(p_update_counter) := p_pos_delta_sob_temp(i);
p_pos_gamma_update.EXTEND;
p_pos_gamma_update(p_update_counter):=p_pos_gamma_temp(i);
p_pos_gamma_usd_update.EXTEND;
p_pos_gamma_usd_update(p_update_counter) := p_pos_gamma_usd_temp(i);
p_pos_gamma_sob_update.EXTEND;
p_pos_gamma_sob_update(p_update_counter) := p_pos_gamma_sob_temp(i);
p_pos_theta_update.EXTEND;
p_pos_theta_update(p_update_counter):=p_pos_theta_temp(i);
p_pos_theta_usd_update.EXTEND;
p_pos_theta_usd_update(p_update_counter) := p_pos_theta_usd_temp(i);
p_pos_theta_sob_update.EXTEND;
p_pos_theta_sob_update(p_update_counter) := p_pos_theta_sob_temp(i);
p_pos_rho_update.EXTEND;
p_pos_rho_update(p_update_counter) := p_pos_rho_temp(i);
p_pos_rho_usd_update.EXTEND;
p_pos_rho_usd_update(p_update_counter) := p_pos_rho_usd_temp(i);
p_pos_rho_sob_update.EXTEND;
p_pos_rho_sob_update(p_update_counter) := p_pos_rho_sob_temp(i);
p_pos_rho_base_update.EXTEND;
p_pos_rho_base_update(p_update_counter) := p_pos_rho_base_temp(i);
p_pos_rho_base_usd_update.EXTEND;
p_pos_rho_base_usd_update(p_update_counter) := p_pos_rho_base_usd_temp(i);
p_pos_rho_base_sob_update.EXTEND;
p_pos_rho_base_sob_update(p_update_counter) := p_pos_rho_base_sob_temp(i);
p_pos_rho_contra_update.EXTEND;
p_pos_rho_contra_update(p_update_counter) := p_pos_rho_contra_temp(i);
p_pos_rho_contra_usd_update.EXTEND;
p_pos_rho_contra_usd_update(p_update_counter) := p_pos_rho_contra_usd_temp(i);
p_pos_rho_contra_sob_update.EXTEND;
p_pos_rho_contra_sob_update(p_update_counter) := p_pos_rho_contra_sob_temp(i);
p_pos_rho_for_update.EXTEND;
p_pos_rho_for_update(p_update_counter):=p_pos_rho_for_temp(i);
p_pos_rho_for_usd_update.EXTEND;
p_pos_rho_for_usd_update(p_update_counter) := p_pos_rho_for_usd_temp(i);
p_pos_rho_for_sob_update.EXTEND;
p_pos_rho_for_sob_update(p_update_counter) := p_pos_rho_for_sob_temp(i);
p_pos_rho_dom_update.EXTEND;
p_pos_rho_dom_update(p_update_counter):=p_pos_rho_dom_temp(i);
p_pos_rho_dom_usd_update.EXTEND;
p_pos_rho_dom_usd_update(p_update_counter) := p_pos_rho_dom_usd_temp(i);
p_pos_rho_dom_sob_update.EXTEND;
p_pos_rho_dom_sob_update(p_update_counter) := p_pos_rho_dom_sob_temp(i);
p_pos_vega_update.EXTEND;
p_pos_vega_update(p_update_counter):=p_pos_vega_temp(i);
p_pos_vega_usd_update.EXTEND;
p_pos_vega_usd_update(p_update_counter) := p_pos_vega_usd_temp(i);
p_pos_vega_sob_update.EXTEND;
p_pos_vega_sob_update(p_update_counter) := p_pos_vega_sob_temp(i);
p_volatility_update.EXTEND;
p_volatility_update(p_update_counter):=p_volatility_temp(i);
p_ytm_update.EXTEND;
p_ytm_update(p_update_counter) := p_ytm_temp(i);
p_gap_date_update.EXTEND;
p_gap_date_update(p_update_counter):=p_gap_date_temp(i);
p_upd_md_calc_date_update.EXTEND;
p_upd_md_calc_date_update(p_update_counter) := p_upd_md_calc_date_temp(i);
----------- End: placing attributes into insert/update arrays -----
----- >>> ALWAYS RECALCULATE <<< -------
-- for QRM_TB_CALCULATIONS: calculate everytime for MM deals
-- for FX/FXO deals, simply save to table
IF (g_proc_level>=g_debug_level) THEN
XTR_RISK_DEBUG_PKG.dlog('run_analysis: ' || 'deal type is: '||p_deal_types(i));
p_except_deal_no.DELETE;
p_except_transaction_no.DELETE;
p_except_market_data_set.DELETE;
p_except_error_type.DELETE;
p_except_error_code.DELETE;
p_except_token_name.DELETE;
p_except_token_value.DELETE;
for j in 1..p_insert_counter loop
v_log := p_seq_nos(j)||';'||
p_deal_no_insert(j)||';'|| p_transaction_no_insert(j)||';'||
p_market_data_set_insert(j)||';'|| p_deal_ccy_insert(j)||';'||
p_sob_ccy_insert(j)||';'|| p_base_ccy_amount_usd_insert(j)||';'||
p_base_ccy_amount_sob_insert(j)||';'|| p_contra_ccy_amount_usd_insert(j)||';'||
p_contra_ccy_amount_sob_insert(j)||';'|| p_foreign_amount_usd_insert(j)||';'||
p_foreign_amount_sob_insert(j)||';'|| p_domestic_amount_usd_insert(j)||';'||
p_domestic_amount_sob_insert(j)||';'|| p_buy_amount_usd_insert(j)||';'||
p_buy_amount_sob_insert(j)||';'|| p_sell_amount_usd_insert(j)||';'||
p_sell_amount_sob_insert(j)||';'|| p_days_insert(j);
XTR_RISK_DEBUG_PKG.dlog('INSERT QRM_DEAL_CALCULATIONS Line 1',v_log,
'QRM_PA_CALCULATION_P.RUN_ANALYSIS',g_state_level);
v_log := p_fx_reval_rate_insert(j)||';'||p_mm_reval_rate_insert(j)||';'||
p_fx_trans_rate_insert(j)||';'|| p_mm_trans_rate_insert(j)||';'||
p_fair_value_insert(j)||';'|| p_fair_value_usd_insert(j)||';'||
p_fair_value_sob_insert(j)||';'|| p_gap_amount_insert(j)||';'||
p_gap_amount_usd_insert(j)||';'|| p_gap_amount_sob_insert(j)||';'||
p_maturity_amount_insert(j)||';'|| p_maturity_amount_usd_insert(j)||';'||
p_maturity_amount_sob_insert(j)||';'|| p_premium_amount_usd_insert(j)||';'||
p_premium_amount_sob_insert(j)||';'|| p_accrued_interest_insert(j)||';'||
p_accrued_interest_usd_insert(j)||';'|| p_accrued_interest_sob_insert(j);
XTR_RISK_DEBUG_PKG.dlog('INSERT QRM_DEAL_CALCULATIONS Line 2',v_log,
'QRM_PA_CALCULATION_P.RUN_ANALYSIS',g_state_level);
v_log := p_duration_insert(j)||';'|| p_mod_duration_insert(j)||';'||
p_convexity_insert(j)||';'|| p_delta_insert(j)||';'|| p_gamma_insert(j)||';'||
p_theta_insert(j)||';'|| p_rho_insert(j)||';'|| p_rho_base_insert(j)||';'||
p_rho_contra_insert(j)||';'|| p_rho_for_insert(j)||';'|| p_rho_dom_insert(j)||';'||
p_vega_insert(j)||';'|| p_pos_bpv_insert(j)||';'|| p_pos_bpv_usd_insert(j)||';'||
p_pos_bpv_sob_insert(j)||';'|| p_pos_delta_insert(j)||';'||
p_pos_delta_usd_insert(j)||';'|| p_pos_delta_sob_insert(j)||';'||
p_pos_gamma_insert(j)||';'|| p_pos_gamma_usd_insert(j)||';'||
p_pos_gamma_sob_insert(j)||';'|| p_pos_theta_insert(j)||';'||
p_pos_theta_usd_insert(j)||';'|| p_pos_theta_sob_insert(j)||';'||
p_pos_rho_insert(j)||';'|| p_pos_rho_usd_insert(j)||';'||
p_pos_rho_sob_insert(j)||';'|| p_pos_rho_base_insert(j)||';'||
p_pos_rho_base_usd_insert(j)||';'|| p_pos_rho_base_sob_insert(j)||';'||
p_pos_rho_contra_insert(j)||';'|| p_pos_rho_contra_usd_insert(j);
XTR_RISK_DEBUG_PKG.dlog('INSERT QRM_DEAL_CALCULATIONS Line 3',v_log,
'QRM_PA_CALCULATION_P.RUN_ANALYSIS',g_state_level);
v_log := p_pos_rho_contra_sob_insert(j)||';'|| p_pos_rho_for_insert(j)||';'||
p_pos_rho_for_usd_insert(j)||';'|| p_pos_rho_for_sob_insert(j)||';'||
p_pos_rho_dom_insert(j)||';'|| p_pos_rho_dom_usd_insert(j)||';'||
p_pos_rho_dom_sob_insert(j)||';'|| p_pos_vega_insert(j)||';'||
p_pos_vega_usd_insert(j)||';'|| p_pos_vega_sob_insert(j)||';'||
p_volatility_insert(j)||';'|| p_ytm_insert(j)||';'||
p_gap_date_insert(j)||';'|| p_reval_price_insert(j)||';'||
p_reval_price_usd_insert(j)||';'|| p_reval_price_sob_insert(j)||';'||
p_trans_price_insert(j)||';'|| p_trans_price_usd_insert(j)||';'||
p_trans_price_sob_insert(j)||';'|| p_ref_datetime;
XTR_RISK_DEBUG_PKG.dlog('INSERT QRM_DEAL_CALCULATIONS Line 4',v_log,
'QRM_PA_CALCULATION_P.RUN_ANALYSIS',g_state_level);
FORALL j IN 1..p_insert_counter
INSERT INTO qrm_deal_calculations (deal_calc_id, deal_no,
transaction_no, market_data_set, deal_ccy, sob_ccy,
base_ccy_amount_usd, base_ccy_amount_sob, contra_ccy_amount_usd,
contra_ccy_amount_sob, foreign_amount_usd, foreign_amount_sob,
domestic_amount_usd, domestic_amount_sob, buy_amount_usd,
buy_amount_sob, sell_amount_usd, sell_amount_sob, days,
fx_reval_rate, mm_reval_rate, fx_trans_rate, mm_trans_rate,
fair_value, fair_value_usd, fair_value_sob, gap_amount,
gap_amount_usd, gap_amount_sob, maturity_amount,
maturity_amount_usd, maturity_amount_sob, premium_amount_usd,
premium_amount_sob, accrued_interest, accrued_interest_usd,
accrued_interest_sob, duration, modified_duration,
convexity, delta, gamma, theta, rho, rho_base, rho_contra,
rho_foreign, rho_domestic, vega, pos_bpv, pos_bpv_usd,
pos_bpv_sob, pos_delta, pos_delta_usd, pos_delta_sob,
pos_gamma, pos_gamma_usd, pos_gamma_sob,
pos_theta, pos_theta_usd, pos_theta_sob, pos_rho,
pos_rho_usd, pos_rho_sob, pos_rho_base, pos_rho_base_usd,
pos_rho_base_sob, pos_rho_contra, pos_rho_contra_usd,
pos_rho_contra_sob, pos_rho_foreign, pos_rho_foreign_usd,
pos_rho_foreign_sob, pos_rho_domestic, pos_rho_domestic_usd,
pos_rho_domestic_sob, pos_vega, pos_vega_usd, pos_vega_sob,
volatility, yield_to_maturity, gap_date, revaluation_price,
revaluation_price_usd, revaluation_price_sob,
transaction_price, transaction_price_usd,
transaction_price_sob, last_md_calc_date,
created_by, creation_date, last_updated_by,
last_update_date, last_update_login, request_id,
program_application_id, program_id, program_update_date)
VALUES(p_seq_nos(j),-- stored earlier
p_deal_no_insert(j), p_transaction_no_insert(j),
p_market_data_set_insert(j), p_deal_ccy_insert(j),
p_sob_ccy_insert(j), p_base_ccy_amount_usd_insert(j),
p_base_ccy_amount_sob_insert(j), p_contra_ccy_amount_usd_insert(j),
p_contra_ccy_amount_sob_insert(j), p_foreign_amount_usd_insert(j),
p_foreign_amount_sob_insert(j), p_domestic_amount_usd_insert(j),
p_domestic_amount_sob_insert(j), p_buy_amount_usd_insert(j),
p_buy_amount_sob_insert(j), p_sell_amount_usd_insert(j),
p_sell_amount_sob_insert(j), p_days_insert(j),
p_fx_reval_rate_insert(j),p_mm_reval_rate_insert(j),
p_fx_trans_rate_insert(j), p_mm_trans_rate_insert(j),
p_fair_value_insert(j), p_fair_value_usd_insert(j),
p_fair_value_sob_insert(j), p_gap_amount_insert(j),
p_gap_amount_usd_insert(j), p_gap_amount_sob_insert(j),
p_maturity_amount_insert(j), p_maturity_amount_usd_insert(j),
p_maturity_amount_sob_insert(j), p_premium_amount_usd_insert(j),
p_premium_amount_sob_insert(j), p_accrued_interest_insert(j),
p_accrued_interest_usd_insert(j), p_accrued_interest_sob_insert(j),
p_duration_insert(j), p_mod_duration_insert(j),
p_convexity_insert(j), p_delta_insert(j), p_gamma_insert(j),
p_theta_insert(j), p_rho_insert(j), p_rho_base_insert(j),
p_rho_contra_insert(j), p_rho_for_insert(j), p_rho_dom_insert(j),
p_vega_insert(j), p_pos_bpv_insert(j), p_pos_bpv_usd_insert(j),
p_pos_bpv_sob_insert(j), p_pos_delta_insert(j),
p_pos_delta_usd_insert(j), p_pos_delta_sob_insert(j),
p_pos_gamma_insert(j), p_pos_gamma_usd_insert(j),
p_pos_gamma_sob_insert(j), p_pos_theta_insert(j),
p_pos_theta_usd_insert(j), p_pos_theta_sob_insert(j),
p_pos_rho_insert(j), p_pos_rho_usd_insert(j),
p_pos_rho_sob_insert(j), p_pos_rho_base_insert(j),
p_pos_rho_base_usd_insert(j), p_pos_rho_base_sob_insert(j),
p_pos_rho_contra_insert(j), p_pos_rho_contra_usd_insert(j),
p_pos_rho_contra_sob_insert(j), p_pos_rho_for_insert(j),
p_pos_rho_for_usd_insert(j), p_pos_rho_for_sob_insert(j),
p_pos_rho_dom_insert(j), p_pos_rho_dom_usd_insert(j),
p_pos_rho_dom_sob_insert(j), p_pos_vega_insert(j),
p_pos_vega_usd_insert(j), p_pos_vega_sob_insert(j),
p_volatility_insert(j), p_ytm_insert(j),
p_gap_date_insert(j), p_reval_price_insert(j),
p_reval_price_usd_insert(j), p_reval_price_sob_insert(j),
p_trans_price_insert(j), p_trans_price_usd_insert(j),
p_trans_price_sob_insert(j), p_ref_datetime,
FND_GLOBAL.user_id, p_ref_datetime,
FND_GLOBAL.user_id, p_ref_datetime, FND_GLOBAL.login_id,
FND_GLOBAL.conc_request_id, FND_GLOBAL.prog_appl_id,
FND_GLOBAL.conc_program_id, p_ref_datetime);
XTR_RISK_DEBUG_PKG.dlog('DML','INSERTED QRM_DEAL_CALCULATIONS',
'QRM_PA_CALCULATION_P.RUN_ANALYSIS',g_event_level);
XTR_RISK_DEBUG_PKG.dlog('run_analysis: ' || 'p_update_counter: '|| p_update_counter);
for j in 1..p_update_counter loop
v_log := p_deal_ccy_update(j)||';'||
p_sob_ccy_update(j)||';'|| p_base_ccy_amount_usd_update(j)||';'||
p_base_ccy_amount_sob_update(j)||';'|| p_contra_ccy_amount_usd_update(j)||';'||
p_contra_ccy_amount_sob_update(j)||';'|| p_foreign_amount_usd_update(j)||';'||
p_foreign_amount_sob_update(j)||';'|| p_domestic_amount_usd_update(j)||';'||
p_domestic_amount_sob_update(j)||';'|| p_buy_amount_usd_update(j)||';'||
p_buy_amount_sob_update(j)||';'|| p_sell_amount_usd_update(j)||';'||
p_sell_amount_sob_update(j)||';'|| p_days_update(j);
XTR_RISK_DEBUG_PKG.dlog('update QRM_DEAL_CALCULATIONS Line 1',v_log,
'QRM_PA_CALCULATION_P.RUN_ANALYSIS',g_state_level);
v_log := p_fx_reval_rate_update(j)||';'||p_mm_reval_rate_update(j)||';'||
p_fx_trans_rate_update(j)||';'|| p_mm_trans_rate_update(j)||';'||
p_fair_value_update(j)||';'|| p_fair_value_usd_update(j)||';'||
p_fair_value_sob_update(j)||';'|| p_gap_amount_update(j)||';'||
p_gap_amount_usd_update(j)||';'|| p_gap_amount_sob_update(j)||';'||
p_maturity_amount_update(j)||';'|| p_maturity_amount_usd_update(j)||';'||
p_maturity_amount_sob_update(j)||';'|| p_premium_amount_usd_update(j)||';'||
p_premium_amount_sob_update(j)||';'|| p_accrued_interest_update(j)||';'||
p_accrued_interest_usd_update(j)||';'|| p_accrued_interest_sob_update(j);
XTR_RISK_DEBUG_PKG.dlog('update QRM_DEAL_CALCULATIONS Line 2',v_log,
'QRM_PA_CALCULATION_P.RUN_ANALYSIS',g_state_level);
v_log := p_duration_update(j)||';'|| p_mod_duration_update(j)||';'||
p_convexity_update(j)||';'|| p_delta_update(j)||';'|| p_gamma_update(j)||';'||
p_theta_update(j)||';'|| p_rho_update(j)||';'|| p_rho_base_update(j)||';'||
p_rho_contra_update(j)||';'|| p_rho_for_update(j)||';'|| p_rho_dom_update(j)||';'||
p_vega_update(j)||';'|| p_pos_bpv_update(j)||';'|| p_pos_bpv_usd_update(j)||';'||
p_pos_bpv_sob_update(j)||';'|| p_pos_delta_update(j)||';'||
p_pos_delta_usd_update(j)||';'|| p_pos_delta_sob_update(j)||';'||
p_pos_gamma_update(j)||';'|| p_pos_gamma_usd_update(j)||';'||
p_pos_gamma_sob_update(j)||';'|| p_pos_theta_update(j)||';'||
p_pos_theta_usd_update(j)||';'|| p_pos_theta_sob_update(j)||';'||
p_pos_rho_update(j)||';'|| p_pos_rho_usd_update(j)||';'||
p_pos_rho_sob_update(j)||';'|| p_pos_rho_base_update(j)||';'||
p_pos_rho_base_usd_update(j)||';'|| p_pos_rho_base_sob_update(j)||';'||
p_pos_rho_contra_update(j)||';'|| p_pos_rho_contra_usd_update(j);
XTR_RISK_DEBUG_PKG.dlog('update QRM_DEAL_CALCULATIONS Line 3',v_log,
'QRM_PA_CALCULATION_P.RUN_ANALYSIS',g_state_level);
v_log := p_pos_rho_contra_sob_update(j)||';'|| p_pos_rho_for_update(j)||';'||
p_pos_rho_for_usd_update(j)||';'|| p_pos_rho_for_sob_update(j)||';'||
p_pos_rho_dom_update(j)||';'|| p_pos_rho_dom_usd_update(j)||';'||
p_pos_rho_dom_sob_update(j)||';'|| p_pos_vega_update(j)||';'||
p_pos_vega_usd_update(j)||';'|| p_pos_vega_sob_update(j)||';'||
p_volatility_update(j)||';'|| p_ytm_update(j)||';'||
p_gap_date_update(j)||';'|| p_reval_price_update(j)||';'||
p_reval_price_usd_update(j)||';'|| p_reval_price_sob_update(j)||';'||
p_trans_price_update(j)||';'|| p_trans_price_usd_update(j)||';'||
p_trans_price_sob_update(j)||';'|| p_ref_datetime;
XTR_RISK_DEBUG_PKG.dlog('update QRM_DEAL_CALCULATIONS Line 4',v_log,
'QRM_PA_CALCULATION_P.RUN_ANALYSIS',g_state_level);
FORALL j IN 1..p_update_counter
UPDATE qrm_deal_calculations
SET
deal_ccy=p_deal_ccy_update(j), sob_ccy=p_sob_ccy_update(j),
base_ccy_amount_usd= p_base_ccy_amount_usd_update(j),
base_ccy_amount_sob=p_base_ccy_amount_sob_update(j),
contra_ccy_amount_usd=p_contra_ccy_amount_usd_update(j),
contra_ccy_amount_sob=p_contra_ccy_amount_sob_update(j),
foreign_amount_usd=p_foreign_amount_usd_update(j),
foreign_amount_sob=p_foreign_amount_sob_update(j),
domestic_amount_usd=p_domestic_amount_usd_update(j),
domestic_amount_sob=p_domestic_amount_sob_update(j),
buy_amount_usd=p_buy_amount_usd_update(j),
buy_amount_sob=p_buy_amount_sob_update(j),
sell_amount_usd=p_sell_amount_usd_update(j),
sell_amount_sob=p_sell_amount_sob_update(j),
days=p_days_update(j), fx_reval_rate=p_fx_reval_rate_update(j),
mm_reval_rate=p_mm_reval_rate_update(j),
fx_trans_rate=p_fx_trans_rate_update(j),
mm_trans_rate=p_mm_trans_rate_update(j),
revaluation_price = p_reval_price_update(j),
revaluation_price_sob = p_reval_price_sob_update(j),
revaluation_price_usd = p_reval_price_usd_update(j),
transaction_price = p_trans_price_update(j),
transaction_price_sob = p_trans_price_sob_update(j),
transaction_price_usd = p_trans_price_usd_update(j),
fair_value=p_fair_value_update(j),
fair_value_usd=p_fair_value_usd_update(j),
fair_value_sob=p_fair_value_sob_update(j),
gap_amount=p_gap_amount_update(j),
gap_amount_usd=p_gap_amount_usd_update(j),
gap_amount_sob=p_gap_amount_sob_update(j),
maturity_amount=p_maturity_amount_update(j),
maturity_amount_usd=p_maturity_amount_usd_update(j),
maturity_amount_sob=p_maturity_amount_sob_update(j),
premium_amount_usd=p_premium_amount_usd_update(j),
premium_amount_sob=p_premium_amount_sob_update(j),
accrued_interest=p_accrued_interest_update(j),
accrued_interest_usd=p_accrued_interest_usd_update(j),
accrued_interest_sob=p_accrued_interest_sob_update(j),
duration=p_duration_update(j),
modified_duration=p_mod_duration_update(j),
convexity=p_convexity_update(j), delta=p_delta_update(j),
gamma=p_gamma_update(j), theta=p_theta_update(j),
rho=p_rho_update(j), rho_base=p_rho_base_update(j),
rho_contra=p_rho_contra_update(j), rho_foreign=p_rho_for_update(j),
rho_domestic=p_rho_dom_update(j), vega=p_vega_update(j),
pos_bpv=p_pos_bpv_update(j),
pos_bpv_usd=p_pos_bpv_usd_update(j),
pos_bpv_sob=p_pos_bpv_sob_update(j),
pos_delta=p_pos_delta_update(j),
pos_delta_usd=p_pos_delta_usd_update(j),
pos_delta_sob=p_pos_delta_sob_update(j),
pos_gamma=p_pos_gamma_update(j),
pos_gamma_usd=p_pos_gamma_usd_update(j),
pos_gamma_sob=p_pos_gamma_sob_update(j),
pos_theta=p_pos_theta_update(j),
pos_theta_usd=p_pos_theta_usd_update(j),
pos_theta_sob=p_pos_theta_sob_update(j),
pos_rho=p_pos_rho_update(j), pos_rho_usd=p_pos_rho_usd_update(j),
pos_rho_sob=p_pos_rho_sob_update(j),
pos_rho_base=p_pos_rho_base_update(j),
pos_rho_base_usd=p_pos_rho_base_usd_update(j),
pos_rho_base_sob=p_pos_rho_base_sob_update(j),
pos_rho_contra=p_pos_rho_contra_update(j),
pos_rho_contra_usd=p_pos_rho_contra_usd_update(j),
pos_rho_contra_sob=p_pos_rho_contra_sob_update(j),
pos_rho_foreign=p_pos_rho_for_update(j),
pos_rho_foreign_usd=p_pos_rho_for_usd_update(j),
pos_rho_foreign_sob=p_pos_rho_for_sob_update(j),
pos_rho_domestic=p_pos_rho_dom_update(j),
pos_rho_domestic_usd=p_pos_rho_dom_usd_update(j),
pos_rho_domestic_sob=p_pos_rho_dom_sob_update(j),
pos_vega=p_pos_vega_update(j),
pos_vega_usd=p_pos_vega_usd_update(j),
pos_vega_sob=p_pos_vega_sob_update(j),
volatility=p_volatility_update(j),
yield_to_maturity=p_ytm_update(j),
gap_date=p_gap_date_update(j),
last_md_calc_date=p_upd_md_calc_date_update(j),
created_by=FND_GLOBAL.user_id,
creation_date=p_ref_datetime, last_updated_by=FND_GLOBAL.user_id,
last_update_date=p_ref_datetime,
last_update_login=FND_GLOBAL.login_id,
request_id=FND_GLOBAL.conc_request_id,
program_application_id=FND_GLOBAL.prog_appl_id,
program_id=FND_GLOBAL.conc_program_id,
program_update_date=p_ref_datetime
WHERE deal_calc_id = p_deal_calc_id_update(j);
XTR_RISK_DEBUG_PKG.dlog('DML','UPDATED QRM_DEAL_CALCULATIONS',
'QRM_PA_CALCULATION_P.RUN_ANALYSIS',g_event_level);
XTR_RISK_DEBUG_PKG.dlog('run_analysis: ' || 'about to delete');
DELETE
FROM qrm_deals_analyses
WHERE analysis_name=p_analysis_name;
for j in 1..p_insert_counter loop
v_log := p_seq_nos(j)||','|| p_analysis_name||','|| p_company_code_insert(j)||','||
FND_GLOBAL.user_id||','|| p_ref_datetime||','|| FND_GLOBAL.user_id||','||
p_ref_datetime||','|| FND_GLOBAL.login_id||','|| FND_GLOBAL.conc_request_id||','||
FND_GLOBAL.prog_appl_id||','|| FND_GLOBAL.conc_program_id||','||p_ref_datetime;
XTR_RISK_DEBUG_PKG.dlog('INSERT QRM_DEALS_ANALYSES',v_log,
'QRM_PA_CALCULATION_P.RUN_ANALYSIS',g_state_level);
for j in 1..p_update_counter loop
v_log := p_deal_calc_id_update(j)||','||p_analysis_name||','||p_company_code_update(j);
XTR_RISK_DEBUG_PKG.dlog('INSERT QRM_DEALS_ANALYSES',v_log,
'QRM_PA_CALCULATION_P.RUN_ANALYSIS',g_state_level);
FORALL j IN 1..p_insert_counter
INSERT INTO qrm_deals_analyses (deal_calc_id, analysis_name,
company_code, created_by, creation_date, last_updated_by,
last_update_date, last_update_login, request_id,
program_application_id, program_id, program_update_date)
VALUES (p_seq_nos(j), p_analysis_name, p_company_code_insert(j),
FND_GLOBAL.user_id, p_ref_datetime, FND_GLOBAL.user_id,
p_ref_datetime, FND_GLOBAL.login_id, FND_GLOBAL.conc_request_id,
FND_GLOBAL.prog_appl_id, FND_GLOBAL.conc_program_id,p_ref_datetime);
FORALL j IN 1..p_update_counter
INSERT INTO qrm_deals_analyses (deal_calc_id, analysis_name,
company_code, created_by, creation_date, last_updated_by,
last_update_date, last_update_login, request_id,
program_application_id, program_id, program_update_date)
VALUES (p_deal_calc_id_update(j), p_analysis_name,
p_company_code_update(j), FND_GLOBAL.user_id, p_ref_datetime,
FND_GLOBAL.user_id, p_ref_datetime, FND_GLOBAL.login_id,
FND_GLOBAL.conc_request_id, FND_GLOBAL.prog_appl_id,
FND_GLOBAL.conc_program_id, p_ref_datetime);
XTR_RISK_DEBUG_PKG.dlog('DML','INSERTED QRM_DEAL_ANALYSES',
'QRM_PA_CALCULATION_P.RUN_ANALYSIS',g_event_level);
DELETE
FROM qrm_tb_calculations
WHERE deal_no=p_tb_deal_no(j)
AND transaction_no=p_tb_transaction_no(j)
AND market_data_set=p_tb_market_data_set(j);
XTR_RISK_DEBUG_PKG.dlog('DML','DELETED QRM_TB_CALCULATIONS',
'QRM_PA_CALCULATION_P.RUN_ANALYSIS',g_event_level);
XTR_RISK_DEBUG_PKG.dlog('INSERT QRM_TB_CALCULATIONS',v_log,
'QRM_PA_CALCULATION_P.RUN_ANALYSIS',g_state_level);
INSERT INTO qrm_tb_calculations (deal_no, transaction_no,
market_data_set, pos_start_date, pos_end_date, start_date,
end_date, interest_basis, outstanding_amount,
outstanding_amount_usd, outstanding_amount_sob, coupon_rate,
next_coupon_date,amt_qty_out, amt_qty_out_usd, amt_qty_out_sob,
created_by, creation_date, last_updated_by,
last_update_date, last_update_login, request_id,
program_application_id, program_id,
program_update_date)
VALUES (p_tb_deal_no(j),
p_tb_transaction_no(j), p_tb_market_data_set(j),
p_tb_pos_start_date(j), p_tb_pos_end_date(j),
p_tb_start_date(j), p_tb_end_date(j),
p_tb_interest_basis(j), p_tb_outst_amount(j),
p_tb_outst_amount_usd(j), p_tb_outst_amount_sob(j),
p_tb_coupon_rate(j), p_tb_next_coupon_date(j), p_tb_amt_qty_out(j),
p_tb_amt_qty_out_usd(j), p_tb_amt_qty_out_sob(j),
FND_GLOBAL.user_id, p_ref_datetime, FND_GLOBAL.user_id,
p_ref_datetime, FND_GLOBAL.login_id, FND_GLOBAL.conc_request_id,
FND_GLOBAL.prog_appl_id,FND_GLOBAL.conc_program_id,p_ref_datetime);
XTR_RISK_DEBUG_PKG.dlog('DML','INSERTED QRM_TB_CALCULATIONS',
'QRM_PA_CALCULATION_P.RUN_ANALYSIS',g_event_level);
p_except_deal_no.DELETE;
p_except_transaction_no.DELETE;
p_except_market_data_set.DELETE;
p_except_error_type.DELETE;
p_except_error_code.DELETE;
p_except_token_name.DELETE;
p_except_token_value.DELETE;
p_except_deal_no.DELETE;
p_except_transaction_no.DELETE;
p_except_market_data_set.DELETE;
p_except_error_type.DELETE;
p_except_error_code.DELETE;
p_except_token_name.DELETE;
p_except_token_value.DELETE;
p_except_deal_no.DELETE;
p_except_transaction_no.DELETE;
p_except_market_data_set.DELETE;
p_except_error_type.DELETE;
p_except_error_code.DELETE;
p_except_token_name.DELETE;
p_except_token_value.DELETE;
p_except_deal_no.DELETE;
p_except_transaction_no.DELETE;
p_except_market_data_set.DELETE;
p_except_error_type.DELETE;
p_except_error_code.DELETE;
p_except_token_name.DELETE;
p_except_token_value.DELETE;
p_except_deal_no.DELETE;
p_except_transaction_no.DELETE;
p_except_market_data_set.DELETE;
p_except_error_type.DELETE;
p_except_error_code.DELETE;
p_except_token_name.DELETE;
p_except_token_value.DELETE;
p_except_deal_no.DELETE;
p_except_transaction_no.DELETE;
p_except_market_data_set.DELETE;
p_except_error_type.DELETE;
p_except_error_code.DELETE;
p_except_token_name.DELETE;
p_except_token_value.DELETE;
p_except_deal_no.DELETE;
p_except_transaction_no.DELETE;
p_except_market_data_set.DELETE;
p_except_error_type.DELETE;
p_except_error_code.DELETE;
p_except_token_name.DELETE;
p_except_token_value.DELETE;
p_except_deal_no.DELETE;
p_except_transaction_no.DELETE;
p_except_market_data_set.DELETE;
p_except_error_type.DELETE;
p_except_error_code.DELETE;
p_except_token_name.DELETE;
p_except_token_value.DELETE;
UPDATE qrm_analysis_settings
SET last_run_date = p_ref_datetime, status = retcode
WHERE analysis_name = p_analysis_name;
XTR_RISK_DEBUG_PKG.dlog('DML','UPDATED LAST RUN DATE AND CALC STATUS IN SETTINGS TABLE',
'QRM_PA_CALCULATION_P.RUN_ANALYSIS',g_event_level);
DELETE
FROM qrm_deal_calc_errors
WHERE analysis_name=p_analysis_name;
XTR_RISK_DEBUG_PKG.dlog('DML','Deleted qrm_deal_calc_errors',
'QRM_PA_CALCULATION_P.RUN_ANALYSIS',g_event_level);
INSERT INTO qrm_deal_calc_errors (analysis_name, deal_no,
transaction_no, market_data_set, error_type, error_code,
token_name, token_value, created_by, creation_date,
last_updated_by, last_update_date, last_update_login,
request_id, program_application_id, program_id, program_update_date)
VALUES (p_analysis_name, p_except_deal_no(j), p_except_transaction_no(j),
p_except_market_data_set(j), p_except_error_type(j),
p_except_error_code(j), p_except_token_name(j),
p_except_token_value(j), FND_GLOBAL.user_id, p_ref_datetime,
FND_GLOBAL.user_id, p_ref_datetime, FND_GLOBAL.login_id,
FND_GLOBAL.conc_request_id, FND_GLOBAL.prog_appl_id,
FND_GLOBAL.conc_program_id, p_ref_datetime);
XTR_RISK_DEBUG_PKG.dlog('DML','Inserted new errors into table',
'QRM_PA_CALCULATION_P.RUN_ANALYSIS',g_event_level);
SELECT rate_fixing_date, start_date, maturity_date
FROM xtr_rollover_transactions_v
WHERE deal_number=p_deal_no;
SELECT max(maturity_date), settle_date
FROM qrm_current_deals_v
GROUP BY deal_no, settle_date
HAVING deal_no=p_deal_no; Bug 4965436*/
SELECT max(maturity_date), settle_date
FROM qrm_current_deals_v
WHERE deal_no=p_deal_no
GROUP BY settle_date ;
SELECT DISTINCT maturity_date
FROM xtr_rollover_transactions_v
WHERE deal_number=p_deal_no
AND ( (p_ref_date<=p_date
and start_date=p_ref_date)
OR(
p_ref_date>p_date
and start_date
SELECT deal_calc_id, deal_no, transaction_no
FROM qrm_deal_calculations
WHERE (deal_no, transaction_no) IN
(SELECT deal_no, transaction_no
FROM qrm_current_deals_v
WHERE nvl(end_date, p_ref_date+1) < p_ref_date);
SELECT deal_calc_id, deal_no, transaction_no
FROM qrm_deal_calculations
WHERE (deal_no, transaction_no) NOT IN
(SELECT deal_no, transaction_no
FROM qrm_current_deals_v); Bug 4965436 */
SELECT deal_calc_id, deal_no, transaction_no
FROM qrm_deal_calculations qdc
WHERE NOT EXISTS ( select 'x'
from qrm_current_deals_v qcdv
where qcdv.deal_no = qdc.deal_no
and qcdv.transaction_no = qdc.transaction_no ) ;
DELETE
FROM qrm_deal_calculations
WHERE deal_calc_id=p_deal_calc_ids(i);
DELETE
FROM qrm_tb_calculations
WHERE deal_no=p_deal_nos(i) AND transaction_no=p_transaction_nos(i);
/* -- don't want to delete rows of this table if analysis still exists
-- because need all instances of company code
FORALL i IN 1..p_deal_calc_ids.COUNT
DELETE
FROM qrm_deals_analyses
WHERE deal_calc_id=p_deal_calc_ids(i);
DELETE
FROM qrm_deal_calc_errors
WHERE deal_no=p_deal_nos(i) AND transaction_no=p_transaction_nos(i);
SELECT l.mm_fx FROM qrm_ana_atts_lookups l, qrm_analysis_atts a
WHERE a.analysis_name=p_analysis_name
AND a.history_flag='S'
AND a.type='M'
AND l.attribute_name=a.attribute_name;
SELECT l.applies_to_eqm FROM qrm_ana_atts_lookups l, qrm_analysis_atts a
WHERE a.analysis_name = p_analysis_name
AND a.history_flag= 'S'
AND a.type='M'
AND l.attribute_name = a.attribute_name;