[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT dc.deal_ccy
--or reval_ccy for FX??
--Felicia will copy reval_ccy to currency for FX
--02/05/2002
FROM qrm_deal_calculations dc, qrm_deals_analyses da
WHERE da.analysis_name=p_name
AND da.deal_calc_id=dc.deal_calc_id;
SELECT DISTINCT dc.sob_ccy
FROM qrm_deal_calculations dc, qrm_deals_analyses da
WHERE da.analysis_name=p_name
AND da.deal_calc_id=dc.deal_calc_id;
SELECT DISTINCT v.base_ccy
FROM qrm_deal_calculations dc, qrm_deals_analyses da,
qrm_current_deals_v v
WHERE da.analysis_name=p_name
AND da.deal_calc_id=dc.deal_calc_id
AND v.deal_no=dc.deal_no
AND v.transaction_no=dc.transaction_no;
SELECT DISTINCT v.contra_ccy
FROM qrm_deal_calculations dc, qrm_deals_analyses da,
qrm_current_deals_v v
WHERE da.analysis_name=p_name
AND da.deal_calc_id=dc.deal_calc_id
AND v.deal_no=dc.deal_no
AND v.transaction_no=dc.transaction_no;
SELECT DISTINCT v.buy_ccy
FROM qrm_deal_calculations dc, qrm_deals_analyses da,
qrm_current_deals_v v
WHERE da.analysis_name=p_name
AND da.deal_calc_id=dc.deal_calc_id
AND dc.deal_no=v.deal_no
AND dc.transaction_no=v.transaction_no;
SELECT DISTINCT v.sell_ccy
FROM qrm_deal_calculations dc, qrm_deals_analyses da,
qrm_current_deals_v v
WHERE da.analysis_name=p_name
AND da.deal_calc_id=dc.deal_calc_id
AND dc.deal_no=v.deal_no
AND dc.transaction_no=v.transaction_no;
SELECT DISTINCT v.foreign_ccy
FROM qrm_deal_calculations dc, qrm_deals_analyses da,
qrm_current_deals_v v
WHERE da.analysis_name=p_name
AND da.deal_calc_id=dc.deal_calc_id
AND dc.deal_no=v.deal_no
AND dc.transaction_no=v.transaction_no;
SELECT DISTINCT v.domestic_ccy
FROM qrm_deal_calculations dc, qrm_deals_analyses da,
qrm_current_deals_v v
WHERE da.analysis_name=p_name
AND da.deal_calc_id=dc.deal_calc_id
AND dc.deal_no=v.deal_no
AND dc.transaction_no=v.transaction_no;
SELECT DISTINCT v.sensitivity_ccy
FROM qrm_deal_calculations dc, qrm_deals_analyses da,
qrm_current_deals_v v
WHERE da.analysis_name=p_name
AND da.deal_calc_id=dc.deal_calc_id
AND dc.deal_no=v.deal_no
AND dc.transaction_no=v.transaction_no;
SELECT deal_currency_col FROM qrm_ana_atts_lookups
WHERE attribute_name=v_meas_temp;
SELECT numerator,denominator,num_denom_origin
FROM qrm_ana_atts_lookups
WHERE attribute_name=v_col_name;
SELECT origin FROM qrm_ana_atts_lookups
WHERE attribute_name=v_col_name;
identical to the array used when inserting measures into
QRM_SAVED_ANALYSES_ROW.
Please refer to Bug 2566711.
***************************************************************/
PROCEDURE get_col_ccy_multp(p_col_ccy_multp IN OUT NOCOPY XTR_MD_NUM_TABLE,
p_a1 IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
p_default_ccy VARCHAR2,
p_row_agg_no NUMBER,
p_max_col_no NUMBER,
p_md_set_code VARCHAR2,
p_ref_date DATE)
IS
v_prev_ccy gl_sets_of_books.currency_code%TYPE;
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;
This function updates the columns' totals in the case of Crosstab
style with different underlying currencies for different columns.
Please refer to Bug 2566711.
***************************************************************/
FUNCTION calc_col_total(p_col_ccy_multp IN OUT NOCOPY XTR_MD_NUM_TABLE,
p_measure IN OUT NOCOPY XTR_MD_NUM_TABLE,
p_max_col_no NUMBER,
p_ccy_case_flag NUMBER,
p_ccy_agg_flag NUMBER,
p_max_col_agg_level NUMBER)
RETURN NUMBER IS
v_total NUMBER := 0;
This procedure insert 1 row at a time to qrm_saved_analyses_row
called when looping through the main cursor. Cannot do bulk insert
without initializing 100 arrays, thus, static insert per row basis
is the next viable option.
***************************************************************/
FUNCTION insert_row(sh IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
sm IN OUT NOCOPY XTR_MD_NUM_TABLE,
p_row_agg_no NUMBER,
p_max_col_no NUMBER,
p_name VARCHAR2,
p_row NUMBER,
p_type NUMBER,
p_hidden VARCHAR2,
p_tot_currency VARCHAR2,
p_style VARCHAR2,
p_ref_date DATE,
p_ccy_case_flag NUMBER,
p_ccy_agg_flag NUMBER,
p_col_ccy_multp IN OUT NOCOPY XTR_MD_NUM_TABLE)
RETURN BOOLEAN IS
v_tot_currency VARCHAR2(20);
xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.INSERT_ROW');
xtr_risk_debug_pkg.dlog('insert_row: ' || '',p_row||':'||v_test);
xtr_risk_debug_pkg.dlog('insert_row: ' || '',p_row||':'||v_test);
INSERT INTO qrm_saved_analyses_row(analysis_name,seq_no,type,hidden,
tot_currency,tot_currency_label,a1,a2,a3,a4,a5,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,m13,m14,m15,
m16,m17,m18,m19,m20,created_by,creation_date,last_updated_by,last_update_date,
last_update_login)
VALUES(p_name,p_row,p_type,p_hidden,v_tot_currency,v_tot_currency_label,
sh(1),sh(2),sh(3),sh(4),
sh(5),sm(1),sm(2),sm(3),sm(4),sm(5),sm(6),sm(7),sm(8),sm(9),sm(10),sm(11),
sm(12),sm(13),sm(14),sm(15),sm(16),sm(17),sm(18),sm(19),sm(20),
FND_GLOBAL.user_id,p_ref_date,FND_GLOBAL.user_id,p_ref_date,
FND_GLOBAL.login_id);
INSERT INTO qrm_saved_analyses_row(analysis_name,seq_no,type,hidden,
tot_currency,tot_currency_label,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,m1,m2,m3,m4,
m5,m6,m7,m8,m9,m10,m11,
m12,m13,m14,m15,m16,m17,m18,m19,m20,m21,m22,m23,m24,m25,m26,m27,m28,m29,m30,
m31,m32,m33,m34,m35,m36,m37,m38,m39,m40,m41,m42,m43,m44,m45,m46,m47,m48,m49,
m50,created_by,creation_date,last_updated_by,last_update_date,
last_update_login)
VALUES(p_name,p_row,p_type,p_hidden,v_tot_currency,v_tot_currency_label,
sh(1),sh(2),sh(3),sh(4),
sh(5),sh(6),sh(7),sh(8),sh(9),sh(10),sm(1),sm(2),sm(3),sm(4),sm(5),sm(6),sm(7),
sm(8),sm(9),sm(10),sm(11),sm(12),sm(13),sm(14),sm(15),sm(16),sm(17),sm(18),
sm(19),sm(20),sm(21),sm(22),sm(23),sm(24),sm(25),sm(26),sm(27),sm(28),sm(29),
sm(30),sm(31),sm(32),sm(33),sm(34),sm(35),sm(36),sm(37),sm(38),sm(39),sm(40),
sm(41),sm(42),sm(43),sm(44),sm(45),sm(46),sm(47),sm(48),sm(49),sm(50),
FND_GLOBAL.user_id,p_ref_date,FND_GLOBAL.user_id,p_ref_date,
FND_GLOBAL.login_id);
INSERT INTO qrm_saved_analyses_row(analysis_name,seq_no,type,hidden,
tot_currency,tot_currency_label,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,
a14,a15,a16,m1,m2,m3,
m4,m5,m6,m7,m8,m9,m10,m11,m12,m13,m14,m15,m16,m17,m18,m19,m20,m21,m22,m23,m24,
m25,m26,m27,m28,m29,m30,m31,m32,m33,m34,m35,m36,m37,m38,m39,m40,m41,m42,m43,
m44,m45,m46,m47,m48,m49,m50,m51,m52,m53,m54,m55,m56,m57,m58,m59,m60,m61,m62,
m63,m64,m65,m66,m67,m68,m69,m70,m71,m72,m73,m74,m75,m76,m77,m78,m79,m80,m81,
m82,m83,m84,m85,m86,m87,m88,m89,m90,m91,m92,m93,m94,m95,m96,m97,m98,m99,m100,
created_by,creation_date,last_updated_by,last_update_date,last_update_login)
VALUES(p_name,p_row,p_type,p_hidden,v_tot_currency,v_tot_currency_label,
sh(1),sh(2),sh(3),sh(4),
sh(5),sh(6),sh(7),sh(8),sh(9),sh(10),sh(11),sh(12),sh(13),sh(14),sh(15),sh(16),
sm(1),sm(2),sm(3),sm(4),sm(5),sm(6),sm(7),sm(8),sm(9),sm(10),sm(11),sm(12),
sm(13),sm(14),sm(15),sm(16),sm(17),sm(18),sm(19),sm(20),sm(21),sm(22),sm(23),
sm(24),sm(25),sm(26),sm(27),sm(28),sm(29),sm(30),sm(31),sm(32),sm(33),sm(34),
sm(35),sm(36),sm(37),sm(38),sm(39),sm(40),sm(41),sm(42),sm(43),sm(44),sm(45),
sm(46),sm(47),sm(48),sm(49),sm(50),sm(51),sm(52),sm(53),sm(54),sm(55),sm(56),
sm(57),sm(58),sm(59),sm(60),sm(61),sm(62),sm(63),sm(64),sm(65),sm(66),sm(67),
sm(68),sm(69),sm(70),sm(71),sm(72),sm(73),sm(74),sm(75),sm(76),sm(77),sm(78),
sm(79),sm(80),sm(81),sm(82),sm(83),sm(84),sm(85),sm(86),sm(87),sm(88),sm(89),
sm(90),sm(91),sm(92),sm(93),sm(94),sm(95),sm(96),sm(97),sm(98),sm(99),sm(100),
FND_GLOBAL.user_id,p_ref_date,FND_GLOBAL.user_id,p_ref_date,
FND_GLOBAL.login_id);
XTR_RISK_DEBUG_PKG.dlog('DML','Inserted into QRM_SAVED_ANALYSES_ROW',
'QRM_PA_AGGREGATION_P.INSERT_ROW',g_event_level);
xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.INSERT_ROW');
'QRM_PA_AGGREGATION_P.INSERT_ROW',G_ERROR_LEVEL);
END insert_row;
p_sql_col := 'SELECT DISTINCT ';
p_sql := 'SELECT ';
SELECT analysis_type, style, tb_label, tb_name, date_type, start_date,
start_date_ref, start_date_offset, start_offset_type, end_date,
end_date_ref, end_date_offset, end_offset_type, business_week,
gl_calendar_id,currency_source,curr_reporting,md_set_code
FROM qrm_analysis_settings
WHERE analysis_name=p_name AND history_flag='S';
SELECT DECODE(a.type,'R',1,'C',2,'M',3,4) seq, a.att_order,
a.attribute_name, a.type, a.total_average, a.percentage, l.numerator,
l.denominator, l.origin, l.amount, l.ccy_aggregate, l.sensitivity,
l.num_denom_origin
FROM qrm_analysis_atts a, qrm_ana_atts_lookups l
WHERE a.attribute_name=l.attribute_name AND a.analysis_name=p_name
AND history_flag='S'
ORDER BY 1,2;
SELECT COUNT(*) FROM qrm_saved_analyses_col
WHERE analysis_name=p_name
AND type>-2;
DELETE qrm_saved_analyses_row WHERE analysis_name=p_name;
DELETE qrm_saved_analyses_col WHERE analysis_name=p_name;
UPDATE qrm_analysis_settings
SET currency_used='R', last_updated_by=FND_GLOBAL.user_id,
last_update_date=p_ref_date, last_update_login=FND_GLOBAL.login_id
WHERE analysis_name=p_name AND history_flag='S';
UPDATE qrm_analysis_settings
SET currency_used=currency_source, last_updated_by=FND_GLOBAL.user_id,
last_update_date=p_ref_date, last_update_login=FND_GLOBAL.login_id
WHERE analysis_name=p_name AND history_flag='S';
XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_ANALYSIS_SETTINGS.CURRENCY_USED',
'QRM_PA_AGGREGATION_P.TRANSFORM_AND_SAVE',g_event_level);
update_timebuckets(p_name,p_ref_date,
v_tb_name,v_tb_label,v_as_of_date,
v_start_date_ref,v_start_date_offset,v_start_offset_type,
v_row_agg_no,v_max_col_no,'F',v_calendar_id,
v_business_week,v_col_seq_no,v_col_seq_no_key,
v_col_name_map,v_percent_col_name_map,v_a1,
v_col_type,v_col_hidden,v_start_date,v_end_date,
v_tb_label_arr);
INSERT INTO qrm_saved_analyses_col(analysis_name,seq_no,seq_no_key,
type,hidden,col_name_map,a1,percent_col_name_map,start_date,
end_date,tb_label,created_by,creation_date,last_updated_by,
last_update_date,
last_update_login) VALUES(p_name,v_col_seq_no(i),
v_col_seq_no_key(i),v_col_type(i),v_col_hidden(i),
v_col_name_map(i),
v_a1(i),v_percent_col_name_map(i),v_start_date(i),v_end_date(i),
v_tb_label_arr(i),
FND_GLOBAL.user_id,p_ref_date,FND_GLOBAL.user_id,p_ref_date,
FND_GLOBAL.login_id);
INSERT INTO qrm_saved_analyses_col(analysis_name,seq_no,seq_no_key,
type,hidden,col_name_map,a1,percent_col_name_map,created_by,
creation_date,last_updated_by,last_update_date,
last_update_login)
VALUES(p_name,v_col_seq_no(i),v_col_seq_no_key(i),v_col_type(i),
v_col_hidden(i),v_col_name_map(i),v_a1(i),
v_percent_col_name_map(i),
FND_GLOBAL.user_id,p_ref_date,FND_GLOBAL.user_id,p_ref_date,
FND_GLOBAL.login_id);
XTR_RISK_DEBUG_PKG.dlog('DML','Inserted into QRM_SAVED_ANALYSES_COL',
'QRM_PA_AGGREGATION_P.TRANSFORM_AND_SAVE',g_event_level);
UPDATE qrm_saved_analyses_col
SET tot_currency=v_curr_reporting,
tot_currency_label=' ('||v_curr_reporting||')',
last_updated_by=FND_GLOBAL.user_id,
last_update_date=p_ref_date,
last_update_login=FND_GLOBAL.login_id
WHERE type=1;
UPDATE qrm_saved_analyses_col
SET tot_currency=v_underlying_ccy,
tot_currency_label=' ('||v_underlying_ccy||')',
last_updated_by=FND_GLOBAL.user_id, last_update_date=p_ref_date,
last_update_login=FND_GLOBAL.login_id
WHERE type=1;
UPDATE qrm_saved_analyses_col
SET tot_currency=NULL,
tot_currency_label=NULL,
last_updated_by=FND_GLOBAL.user_id, last_update_date=p_ref_date,
last_update_login=FND_GLOBAL.login_id
WHERE type=1;
XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SAVED_ANALYSES_COL.TOT_CURRENCY',
'QRM_PA_AGGREGATION_P.TRANSFORM_AND_SAVE',g_event_level);
v_success := insert_row(v_save_header,v_save_measure,
v_row_agg_no,v_max_col_no,
p_name,v_row,v_type,v_hidden,NULL,
v_style,p_ref_date,v_ccy_case_flag,
v_ccy_agg_flag,v_col_ccy_multp);
v_success := insert_row(v_save_header,v_save_measure,
v_row_agg_no,v_max_col_no,
p_name,v_row,v_type,v_hidden,
v_current_ccy,
v_style,p_ref_date,v_ccy_case_flag,
v_ccy_agg_flag,v_col_ccy_multp);
ELSE --insert to side array to be totaled later
IF v_measure(k*m-n)=0 THEN
v_save_measure(k):=v_measure(k*m-n-1)/v_divisor;
v_success := insert_row(v_save_header,v_save_measure,
v_row_agg_no,v_max_col_no,
p_name,v_row,v_type,v_hidden,NULL,
v_style,p_ref_date,v_ccy_case_flag,
v_ccy_agg_flag,v_col_ccy_multp);
v_success := insert_row(v_save_header,v_save_measure,
v_row_agg_no,v_max_col_no,
p_name,v_row,v_type,v_hidden,
v_current_ccy,
v_style,p_ref_date,v_ccy_case_flag,
v_ccy_agg_flag,v_col_ccy_multp);
ELSE --insert to side array to be totaled later
IF v_measure(3)=0 THEN
v_save_measure(v_measure(1)-v_row_agg_no):=v_measure(2)/v_divisor;
v_success := insert_row(v_save_header,v_save_measure,
v_row_agg_no,v_max_col_no,
p_name,v_row,v_type,v_hidden,NULL,
v_style,p_ref_date,v_ccy_case_flag,
v_ccy_agg_flag,v_col_ccy_multp);
v_success := insert_row(v_save_header,v_save_measure,
v_row_agg_no,v_max_col_no,
p_name,v_row,v_type,v_hidden,
v_current_ccy,
v_style,p_ref_date,v_ccy_case_flag,
v_ccy_agg_flag,v_col_ccy_multp);
v_success := update_total(p_name,p_ref_date);
RAISE e_pagg_update_total_fail;
v_success := update_aggregate_curr(p_name,p_ref_date,v_ccy_case_flag,
v_ccy_agg_flag,v_ccy_agg_level,v_row_agg_no,v_max_col_no,
v_underlying_ccy,v_currency_source,v_curr_reporting,v_agg_col_curr);
RAISE e_pagg_update_agg_curr_fail;
v_success := update_percent (p_name,v_style,v_row_agg_no,v_max_col_no,
p_ref_date,v_md_set_code);
RAISE e_pagg_update_percent_fail;
v_success := update_label(p_name,v_agg,v_col_order,v_att_type,p_ref_date);
RAISE e_pagg_update_label_fail;
v_success := update_timebuckets_label(p_name);
RAISE e_pagg_update_tb_label_fail;
UPDATE qrm_analysis_settings SET dirty='N' WHERE analysis_name=p_name;
XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_ANALYSIS_SETTINGS.DIRTY=N',
'QRM_PA_AGGREGATION_P.TRANSFORM_AND_SAVE',g_event_level);
SELECT MIN(transaction_date)
INTO v_date_out
FROM gl_transaction_dates
WHERE transaction_calendar_id = p_calendar_id
AND TRUNC(transaction_date) >= TRUNC(p_date_in)
AND business_day_flag = 'Y';
SELECT MAX(transaction_date)
INTO v_date_out
FROM gl_transaction_dates
WHERE transaction_calendar_id = p_calendar_id
AND TRUNC(transaction_date) <= TRUNC(p_date_in)
AND business_day_flag = 'Y';
SELECT
fri_business_day_flag,
sat_business_day_flag,
sun_business_day_flag,
mon_business_day_flag,
tue_business_day_flag,
wed_business_day_flag,
thu_business_day_flag
FROM gl_transaction_calendar
WHERE transaction_calendar_id = p_gl_trans_calendar_id;
PROCEDURE update_timebuckets (p_name VARCHAR2,
p_ref_date DATE,
p_tb_name VARCHAR2,
p_tb_label VARCHAR2,
p_as_of_date DATE,
p_start_date_ref VARCHAR2,
p_start_date_offset NUMBER,
p_start_offset_type VARCHAR2,
p_row_agg_no NUMBER,
p_max_col_no OUT NOCOPY NUMBER,
p_date_type VARCHAR2,
p_calendar_id NUMBER,
p_business_week VARCHAR2,
p_col_seq_no IN OUT NOCOPY XTR_MD_NUM_TABLE,
p_col_seq_no_key IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
p_col_name_map IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
p_percent_col_name_map IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
p_a1 IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
p_col_type IN OUT NOCOPY XTR_MD_NUM_TABLE,
p_col_hidden IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
p_start_date IN OUT NOCOPY SYSTEM.QRM_DATE_TABLE,
p_end_date IN OUT NOCOPY SYSTEM.QRM_DATE_TABLE,
p_tb_label_arr IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE) IS
CURSOR v_tb_cursor IS
SELECT interval_length, interval_type, label
FROM qrm_time_intervals
WHERE tb_name = p_tb_name AND sequence_number<>0
ORDER BY sequence_number;
SELECT COUNT(*) FROM qrm_time_buckets
WHERE tb_name = p_tb_name;
xtr_risk_debug_pkg.dpush(null,'QRM_PA_P.UPDATE_TIMEBUCKETS'); --bug3236479
xtr_risk_debug_pkg.dpop(null,'QRM_PA_P.UPDATE_TIMEBUCKETS');--bug 3236479
END update_timebuckets;
update_timebuckets(p_name, p_ref_date, p_tb_name, p_tb_label, p_end_date,
p_end_date_ref, p_end_date_offset,
p_end_offset_type, v_row_agg_no, v_max_col_no,
p_date_type, p_calendar_id, p_business_week,
v_col_seq_no, v_col_seq_no_key, v_col_name_map,
v_percent_col_name_map, v_a1, v_col_type,
v_col_hidden, v_start_date, v_end_date,
v_tb_label_arr);
FUNCTION update_total(p_name VARCHAR2, p_ref_date DATE) RETURN BOOLEAN IS
BEGIN
IF (g_proc_level>=g_debug_level) THEN
xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.UPDATE_TOTAL');--bug 3236479
UPDATE qrm_saved_analyses_row
SET hidden='Y', last_updated_by=FND_GLOBAL.user_id, last_update_date=p_ref_date, last_update_login=FND_GLOBAL.login_id
WHERE type>0 AND analysis_name=p_name;
XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SET_ANALYSES_ROW.HIDDEN=Y',
'QRM_PA_AGGREGATION_P.UPDATE_TOTAL',g_event_level);
UPDATE qrm_saved_analyses_row
SET hidden='N', last_updated_by=FND_GLOBAL.user_id, last_update_date=p_ref_date, last_update_login=FND_GLOBAL.login_id
WHERE analysis_name=p_name
AND type IN (SELECT att_order FROM qrm_analysis_atts
WHERE analysis_name=p_name
AND total_ind<>'N'
AND type='R'
AND history_flag='S');
XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SET_ANALYSES_ROW.HIDDEN=N for those that need to be displayed',
'QRM_PA_AGGREGATION_P.UPDATE_TOTAL',g_event_level);
UPDATE qrm_saved_analyses_col
SET hidden='Y', last_updated_by=FND_GLOBAL.user_id, last_update_date=p_ref_date, last_update_login=FND_GLOBAL.login_id
WHERE type>0 AND analysis_name=p_name;
XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SET_ANALYSES_COL.HIDDEN=Y',
'QRM_PA_AGGREGATION_P.UPDATE_TOTAL',g_event_level);
UPDATE qrm_saved_analyses_col
SET hidden='N', last_updated_by=FND_GLOBAL.user_id, last_update_date=p_ref_date, last_update_login=FND_GLOBAL.login_id
WHERE analysis_name=p_name
AND type IN (SELECT att_order FROM qrm_analysis_atts
WHERE analysis_name=p_name
AND total_ind<>'N'
AND type='C'
AND history_flag='S');
XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SET_ANALYSES_COL.HIDDEN=N for those that need to be displayed',
'QRM_PA_AGGREGATION_P.UPDATE_TOTAL',g_event_level);
xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.UPDATE_TOTAL');--bug 3236479
xtr_risk_debug_pkg.dlog('EXCEPTION','OTHERS','QRM_PA_AGGREGATION_P.UPDATE_TOTAL',g_error_level);--bug 3236479
END update_total;
This function updates the percent columns (P1..P100) given
the analysis name and seq_no, one row at the time.
***************************************************************/
FUNCTION update_percent_cols(p_name VARCHAR2,
p_row NUMBER,
p_ref_date DATE,
p_meas XTR_MD_NUM_TABLE)
RETURN BOOLEAN IS
BEGIN
IF (g_proc_level>=g_debug_level) THEN
xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.UPDATE_PERCENT_COLS');--bug 3236479
UPDATE qrm_saved_analyses_row
SET p1=p_meas(1),p2=p_meas(2),p3=p_meas(3),
p4=p_meas(4),p5=p_meas(5),p6=p_meas(6),
p7=p_meas(7),p8=p_meas(8),p9=p_meas(9),
p10=p_meas(10),p11=p_meas(11),
p12=p_meas(12),
p13=p_meas(13),p14=p_meas(14),
p15=p_meas(15),
p16=p_meas(16),p17=p_meas(17),
p18=p_meas(18),
p19=p_meas(19),p20=p_meas(20),
p21=p_meas(21),p22=p_meas(22),
p23=p_meas(23),
p24=p_meas(24),p25=p_meas(25),
p26=p_meas(26),
p27=p_meas(27),p28=p_meas(28),
p29=p_meas(29),
p30=p_meas(30),p31=p_meas(31),
p32=p_meas(32),
p33=p_meas(33),p34=p_meas(34),
p35=p_meas(35),
p36=p_meas(36),p37=p_meas(37),
p38=p_meas(38),
p39=p_meas(39),p40=p_meas(40),
p41=p_meas(41),p42=p_meas(42),
p43=p_meas(43),
p44=p_meas(44),p45=p_meas(45),
p46=p_meas(46),
p47=p_meas(47),p48=p_meas(48),
p49=p_meas(49),p50=p_meas(50),
p51=p_meas(51),p52=p_meas(52),
p53=p_meas(53),
p54=p_meas(54),p55=p_meas(55),
p56=p_meas(56),
p57=p_meas(57),p58=p_meas(58),
p59=p_meas(59),
p60=p_meas(60),p61=p_meas(61),
p62=p_meas(62),
p63=p_meas(63),p64=p_meas(64),
p65=p_meas(65),
p66=p_meas(66),p67=p_meas(67),
p68=p_meas(68),
p69=p_meas(69),p70=p_meas(70),
p71=p_meas(71),p72=p_meas(72),
p73=p_meas(73),
p74=p_meas(74),p75=p_meas(75),
p76=p_meas(76),
p77=p_meas(77),p78=p_meas(78),
p79=p_meas(79),
p80=p_meas(80),p81=p_meas(81),
p82=p_meas(82),
p83=p_meas(83),p84=p_meas(84),
p85=p_meas(85),
p86=p_meas(86),p87=p_meas(87),
p88=p_meas(88),
p89=p_meas(89),p90=p_meas(90),
p91=p_meas(91),p92=p_meas(92),
p93=p_meas(93),
p94=p_meas(94),p95=p_meas(95),
p96=p_meas(96),
p97=p_meas(97),p98=p_meas(98),
p99=p_meas(99),p100=p_meas(100),
last_updated_by=FND_GLOBAL.user_id,
last_update_date=p_ref_date,
last_update_login=FND_GLOBAL.login_id
WHERE analysis_name=p_name
AND seq_no=p_row;
xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.UPDATE_PERCENT_COLS');--bug 3236479
xtr_risk_debug_pkg.dlog('EXCEPTION','OTHERS','QRM_PA_AGGREGATION_P.UPDATE_PERCENT_COLS');--bug 3236479
END update_percent_cols;
into USD and then updates the P1..P100 before calculating the
percent. This is necessary to get values based on 1 currency so
that they can be percentaged correctly.
Please refer to bug 2393589 for more details.
***************************************************************/
FUNCTION translate_to_usd (p_name VARCHAR2,
p_ref_date DATE,
p_md_set_code VARCHAR2,
p_tot_level NUMBER)
RETURN BOOLEAN IS
v_level NUMBER;
SELECT seq_no,type,
m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,m13,m14,m15,m16,m17,
m18,m19,m20,m21,m22,m23,m24,m25,m26,m27,m28,m29,m30,m31,m32,m33,m34,
m35,m36,m37,m38,m39,m40,m41,m42,m43,m44,m45,m46,m47,m48,m49,m50,m51,
m52,m53,m54,m55,m56,m57,m58,m59,m60,m61,m62,m63,m64,m65,m66,m67,m68,
m69,m70,m71,m72,m73,m74,m75,m76,m77,m78,m79,m80,m81,m82,m83,m84,m85,
m86,m87,m88,m89,m90,m91,m92,m93,m94,m95,m96,m97,m98,m99,m100,
curr1,curr2,curr3,curr4,curr5,curr6,curr7,curr8,curr9,
curr10,curr11,curr12,curr13,curr14,curr15,curr16,curr17,
curr18,curr19,curr20,curr21,curr22,curr23,curr24,curr25,curr26,
curr27,curr28,curr29,curr30,curr31,curr32,curr33,curr34,
curr35,curr36,curr37,curr38,curr39,curr40,curr41,curr42,curr43,
curr44,curr45,curr46,curr47,curr48,curr49,curr50,curr51,
curr52,curr53,curr54,curr55,curr56,curr57,curr58,curr59,curr60,
curr61,curr62,curr63,curr64,curr65,curr66,curr67,curr68,
curr69,curr70,curr71,curr72,curr73,curr74,curr75,curr76,curr77,
curr78,curr79,curr80,curr81,curr82,curr83,curr84,curr85,
curr86,curr87,curr88,curr89,curr90,curr91,curr92,curr93,curr94,
curr95,curr96,curr97,curr98,curr99,curr100
FROM qrm_saved_analyses_row
WHERE analysis_name=p_name
ORDER BY seq_no;
v_success := update_percent_cols(p_name,v_row,p_ref_date,v_meas);
This update_percent function should be used by external call
everytime the Total page is modified.
Internal call (transform_and_save) will use the other signature.
***************************************************************/
FUNCTION update_percent (p_name VARCHAR2,p_ref_date DATE)
RETURN BOOLEAN IS
CURSOR count_column IS
SELECT COUNT(*) FROM qrm_saved_analyses_col
WHERE analysis_name=p_name
AND type>-2;
SELECT COUNT(*) FROM qrm_saved_analyses_col
WHERE analysis_name=p_name
AND type=-2;
SELECT style,md_set_code FROM qrm_analysis_settings
WHERE analysis_name=p_name AND history_flag='S';
xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.UPDATE_PERCENT'); --bug 3236479
v_success:=update_percent(p_name,v_style,v_row_agg_no,v_max_col_no,
p_ref_date,v_md_set_code);
xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.UPDATE_PERCENT'); --bug 3236479
END update_percent;
This update_percent function should be used by internal call
from transform_and_save procedure.
External call should use the other signature.
***************************************************************/
FUNCTION update_percent (p_name VARCHAR2,
p_style VARCHAR2,
p_row_agg_no NUMBER,
p_max_col_no NUMBER,
p_ref_date DATE,
p_md_set_code VARCHAR2)
RETURN BOOLEAN IS
v_tot XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
SELECT type,att_order FROM qrm_analysis_atts
WHERE analysis_name=p_name AND percentage='Y' AND history_flag='S';
SELECT seq_no FROM qrm_saved_analyses_col
WHERE analysis_name=p_name AND type=v_level;
SELECT seq_no,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,m13,m14,m15,m16,m17,
m18,m19,m20,m21,m22,m23,m24,m25,m26,m27,m28,m29,m30,m31,m32,m33,m34,
m35,m36,m37,m38,m39,m40,m41,m42,m43,m44,m45,m46,m47,m48,m49,m50,m51,
m52,m53,m54,m55,m56,m57,m58,m59,m60,m61,m62,m63,m64,m65,m66,m67,m68,
m69,m70,m71,m72,m73,m74,m75,m76,m77,m78,m79,m80,m81,m82,m83,m84,m85,
m86,m87,m88,m89,m90,m91,m92,m93,m94,m95,m96,m97,m98,m99,m100
FROM qrm_saved_analyses_row
WHERE analysis_name=p_name AND type=v_level ORDER BY seq_no;
xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.UPDATE_PERCENT');
UPDATE qrm_saved_analyses_row
SET p1=100*p1/v_tot(1),p2=100*p2/v_tot(2),p3=100*p3/v_tot(3),
p4=100*p4/v_tot(4),p5=100*p5/v_tot(5),p6=100*p6/v_tot(6),
p7=100*p7/v_tot(7),p8=100*p8/v_tot(8),p9=100*p9/v_tot(9),
p10=100*p10/v_tot(10),p11=100*p11/v_tot(11),
p12=100*p12/v_tot(12),
p13=100*p13/v_tot(13),p14=100*p14/v_tot(14),
p15=100*p15/v_tot(15),
p16=100*p16/v_tot(16),p17=100*p17/v_tot(17),
p18=100*p18/v_tot(18),
p19=100*p19/v_tot(19),p20=100*p20/v_tot(20),
p21=100*p21/v_tot(21),p22=100*p22/v_tot(22),
p23=100*p23/v_tot(23),
p24=100*p24/v_tot(24),p25=100*p25/v_tot(25),
p26=100*p26/v_tot(26),
p27=100*p27/v_tot(27),p28=100*p28/v_tot(28),
p29=100*p29/v_tot(29),
p30=100*p30/v_tot(30),p31=100*p31/v_tot(31),
p32=100*p32/v_tot(32),
p33=100*p33/v_tot(33),p34=100*p34/v_tot(34),
p35=100*p35/v_tot(35),
p36=100*p36/v_tot(36),p37=100*p37/v_tot(37),
p38=100*p38/v_tot(38),
p39=100*p39/v_tot(39),p40=100*p40/v_tot(40),
p41=100*p41/v_tot(41),p42=100*p42/v_tot(42),
p43=100*p43/v_tot(43),
p44=100*p44/v_tot(44),p45=100*p45/v_tot(45),
p46=100*p46/v_tot(46),
p47=100*p47/v_tot(47),p48=100*p48/v_tot(48),
p49=100*p49/v_tot(49),p50=100*p50/v_tot(50),
p51=100*p51/v_tot(51),p52=100*p52/v_tot(52),
p53=100*p53/v_tot(53),
p54=100*p54/v_tot(54),p55=100*p55/v_tot(55),
p56=100*p56/v_tot(56),
p57=100*p57/v_tot(57),p58=100*p58/v_tot(58),
p59=100*p59/v_tot(59),
p60=100*p60/v_tot(60),p61=100*p61/v_tot(61),
p62=100*p62/v_tot(62),
p63=100*p63/v_tot(63),p64=100*p64/v_tot(64),
p65=100*p65/v_tot(65),
p66=100*p66/v_tot(66),p67=100*p67/v_tot(67),
p68=100*p68/v_tot(68),
p69=100*p69/v_tot(69),p70=100*p70/v_tot(70),
p71=100*p71/v_tot(71),p72=100*p72/v_tot(72),
p73=100*p73/v_tot(73),
p74=100*p74/v_tot(74),p75=100*p75/v_tot(75),
p76=100*p76/v_tot(76),
p77=100*p77/v_tot(77),p78=100*p78/v_tot(78),
p79=100*p79/v_tot(79),
p80=100*p80/v_tot(80),p81=100*p81/v_tot(81),
p82=100*p82/v_tot(82),
p83=100*p83/v_tot(83),p84=100*p84/v_tot(84),
p85=100*p85/v_tot(85),
p86=100*p86/v_tot(86),p87=100*p87/v_tot(87),
p88=100*p88/v_tot(88),
p89=100*p89/v_tot(89),p90=100*p90/v_tot(90),
p91=100*p91/v_tot(91),p92=100*p92/v_tot(92),
p93=100*p93/v_tot(93),
p94=100*p94/v_tot(94),p95=100*p95/v_tot(95),
p96=100*p96/v_tot(96),
p97=100*p97/v_tot(97),p98=100*p98/v_tot(98),
p99=100*p99/v_tot(99),p100=100*p100/v_tot(100),
last_updated_by=FND_GLOBAL.user_id,
last_update_date=p_ref_date,
last_update_login=FND_GLOBAL.login_id
WHERE analysis_name=p_name AND seq_no>=v_previous_count
AND seq_no<=v_row
AND (type=-1 OR type>=v_level);
XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SET_ANALYSES_ROW.P1..P100 Row Level Agg',
'QRM_PA_AGGREGATION_P.UPDATE_PERCENT',g_event_level);
UPDATE qrm_saved_analyses_col
SET col_percent_level=NULL, last_updated_by=FND_GLOBAL.user_id, last_update_date=p_ref_date, last_update_login=FND_GLOBAL.login_id
WHERE analysis_name=p_name;
UPDATE qrm_saved_analyses_col
SET col_percent_level=v_level, last_updated_by=FND_GLOBAL.user_id, last_update_date=p_ref_date, last_update_login=FND_GLOBAL.login_id
WHERE analysis_name=p_name AND type=-1;
XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SET_ANALYSES_COL.COL_PERCENT_LEVEL to null then '||v_level,
'QRM_PA_AGGREGATION_P.UPDATE_PERCENT',g_event_level);
v_sql:='UPDATE qrm_saved_analyses_row SET '||v_sql||' , last_updated_by=FND_GLOBAL.user_id, last_update_date=:p_ref_date, last_update_login=FND_GLOBAL.login_id WHERE analysis_name=:p_name';
UPDATE qrm_saved_analyses_col
SET col_percent_level=NULL, last_updated_by=FND_GLOBAL.user_id, last_update_date=p_ref_date, last_update_login=FND_GLOBAL.login_id
WHERE analysis_name=p_name;
UPDATE qrm_saved_analyses_col
SET col_percent_level=-v_level, last_updated_by=FND_GLOBAL.user_id, last_update_date=p_ref_date, last_update_login=FND_GLOBAL.login_id
WHERE analysis_name=p_name AND (type=-1 OR type>=v_level);
XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SET_ANALYSES_COL.COL_PERCENT_LEVEL to null then '||v_level,
'QRM_PA_AGGREGATION_P.UPDATE_PERCENT',g_event_level);
xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.UPDATE_PERCENT');--bug 3236479
xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.UPDATE_PERCENT');-- bug 3236479
'QRM_PA_AGGREGATION_P.UPDATE_PERCENT',g_error_level);--bug 3236479
END update_percent;
This function updates the label of the DEAL_TYPE and
DEAL_SUBTYPE aggregate attributes with the user defined ones
from table qrm_saved_analyses_col.
The label of table qrm_saved_analyses_row will be updated at run
time in OA.
DEAL_TYPE -> xtr_deal_types.user_deal_type
DEAL_SUBTYPE -> xtr_deal_subtypes.user_deal_subtype
is taken care in the dynamic cursor when aggregation is done.
***************************************************************/
FUNCTION update_label(p_name VARCHAR2,
p_agg IN OUT NOCOPY SYSTEM.QRM_VARCHAR240_TABLE,
p_col_order IN OUT NOCOPY XTR_MD_NUM_TABLE,
p_att_type IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
p_ref_date DATE)
RETURN BOOLEAN IS
v_col_name VARCHAR2(50);
xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.UPDATE_LABEL');
UPDATE qrm_saved_analyses_col
SET a1=(SELECT DISTINCT user_deal_type FROM xtr_deal_types WHERE deal_type=a1), last_updated_by=FND_GLOBAL.user_id, last_update_date=p_ref_date, last_update_login=FND_GLOBAL.login_id
WHERE analysis_name=p_name AND type=-1;
XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SET_ANALYSES_COL.A1 to user_deal_type',
'QRM_PA_AGGREGATION_P.UPDATE_LABEL',g_event_level);
xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.UPDATE_LABEL');
xtr_risk_debug_pkg.dlog('EXCEPTION','OTHERS','QRM_PA_AGGREGATION_P.UPDATE_LABEL',g_error_level);-- bug 3236479
END update_label;
SELECT origin,attribute_type,numerator,denominator,num_denom_origin
FROM qrm_ana_atts_lookups
WHERE attribute_name=v_temp;
UPDATE qrm_analysis_settings SET dirty='N' WHERE analysis_name=p_name;
xtr_risk_debug_pkg.dlog('DML','UPDATE QRM_ANALYSIS_SETTINGS.DIRTY=N',
'QRM_PA_AGGREGATION_P.CALC_TABLE_TOTAL',g_event_level);--bug 3236479
FUNCTION update_currency_columns(p_indicator NUMBER,
p_name VARCHAR2,
p_ref_date DATE,
v_curr IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE)
RETURN BOOLEAN IS
BEGIN
IF (g_proc_level>=g_debug_level) THEN
xtr_risk_debug_pkg.dpush('QRM_PA_AGGREGATION_P.UPDATE_CURR_COL');
UPDATE qrm_saved_analyses_row
SET curr1=v_curr(1),curr2=v_curr(2),curr3=v_curr(3),
curr4=v_curr(4),curr5=v_curr(5),curr6=v_curr(6),
curr7=v_curr(7),curr8=v_curr(8),curr9=v_curr(9),
curr10=v_curr(10),curr11=v_curr(11),curr12=v_curr(12),
curr13=v_curr(13),curr14=v_curr(14),curr15=v_curr(15),
curr16=v_curr(16),curr17=v_curr(17),curr18=v_curr(18),
curr19=v_curr(19),curr20=v_curr(20),
curr21=v_curr(21),curr22=v_curr(22),curr23=v_curr(23),
curr24=v_curr(24),curr25=v_curr(25),curr26=v_curr(26),
curr27=v_curr(27),curr28=v_curr(28),curr29=v_curr(29),
curr30=v_curr(30),curr31=v_curr(31),curr32=v_curr(32),
curr33=v_curr(33),curr34=v_curr(34),curr35=v_curr(35),
curr36=v_curr(36),curr37=v_curr(37),curr38=v_curr(38),
curr39=v_curr(39),curr40=v_curr(40),
curr41=v_curr(41),curr42=v_curr(42),curr43=v_curr(43),
curr44=v_curr(44),curr45=v_curr(45),curr46=v_curr(46),
curr47=v_curr(47),curr48=v_curr(48),
curr49=v_curr(49),curr50=v_curr(50),
curr51=v_curr(51),curr52=v_curr(52),curr53=v_curr(53),
curr54=v_curr(54),curr55=v_curr(55),curr56=v_curr(56),
curr57=v_curr(57),curr58=v_curr(58),curr59=v_curr(59),
curr60=v_curr(60),curr61=v_curr(61),curr62=v_curr(62),
curr63=v_curr(63),curr64=v_curr(64),curr65=v_curr(65),
curr66=v_curr(66),curr67=v_curr(67),curr68=v_curr(68),
curr69=v_curr(69),curr70=v_curr(70),
curr71=v_curr(71),curr72=v_curr(72),curr73=v_curr(73),
curr74=v_curr(74),curr75=v_curr(75),curr76=v_curr(76),
curr77=v_curr(77),curr78=v_curr(78),curr79=v_curr(79),
curr80=v_curr(80),curr81=v_curr(81),curr82=v_curr(82),
curr83=v_curr(83),curr84=v_curr(84),curr85=v_curr(85),
curr86=v_curr(86),curr87=v_curr(87),curr88=v_curr(88),
curr89=v_curr(89),curr90=v_curr(90),
curr91=v_curr(91),curr92=v_curr(92),curr93=v_curr(93),
curr94=v_curr(94),curr95=v_curr(95),curr96=v_curr(96),
curr97=v_curr(97),curr98=v_curr(98),
curr99=v_curr(99),curr100=v_curr(100),
last_updated_by=FND_GLOBAL.user_id,
last_update_date=p_ref_date,
last_update_login=FND_GLOBAL.login_id
WHERE analysis_name=p_name;
XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SAVED_ANALYSES_ROW.CURR1..100 with p_indicator=1',
'QRM_PA_AGGREGATION_P.UPDATE_CURRENCY_COLUMNS',g_event_level);
UPDATE qrm_saved_analyses_row
SET curr2=curr1,curr3=curr1,
curr4=curr1,curr5=curr1,curr6=curr1,
curr7=curr1,curr8=curr1,curr9=curr1,
curr10=curr1,curr11=curr1,curr12=curr1,
curr13=curr1,curr14=curr1,curr15=curr1,
curr16=curr1,curr17=curr1,curr18=curr1,
curr19=curr1,curr20=curr1,
curr21=curr1,curr22=curr1,curr23=curr1,
curr24=curr1,curr25=curr1,curr26=curr1,
curr27=curr1,curr28=curr1,curr29=curr1,
curr30=curr1,curr31=curr1,curr32=curr1,
curr33=curr1,curr34=curr1,curr35=curr1,
curr36=curr1,curr37=curr1,curr38=curr1,
curr39=curr1,curr40=curr1,
curr41=curr1,curr42=curr1,curr43=curr1,
curr44=curr1,curr45=curr1,curr46=curr1,
curr47=curr1,curr48=curr1,
curr49=curr1,curr50=curr1,
curr51=curr1,curr52=curr1,curr53=curr1,
curr54=curr1,curr55=curr1,curr56=curr1,
curr57=curr1,curr58=curr1,curr59=curr1,
curr60=curr1,curr61=curr1,curr62=curr1,
curr63=curr1,curr64=curr1,curr65=curr1,
curr66=curr1,curr67=curr1,curr68=curr1,
curr69=curr1,curr70=curr1,
curr71=curr1,curr72=curr1,curr73=curr1,
curr74=curr1,curr75=curr1,curr76=curr1,
curr77=curr1,curr78=curr1,curr79=curr1,
curr80=curr1,curr81=curr1,curr82=curr1,
curr83=curr1,curr84=curr1,curr85=curr1,
curr86=curr1,curr87=curr1,curr88=curr1,
curr89=curr1,curr90=curr1,
curr91=curr1,curr92=curr1,curr93=curr1,
curr94=curr1,curr95=curr1,curr96=curr1,
curr97=curr1,curr98=curr1,
curr99=curr1,curr100=curr1,
last_updated_by=FND_GLOBAL.user_id,
last_update_date=p_ref_date,
last_update_login=FND_GLOBAL.login_id
WHERE analysis_name=p_name;
XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SAVED_ANALYSES_ROW.CURR1..100 with p_indicator='||p_indicator,
'QRM_PA_AGGREGATION_P.UPDATE_CURRENCY_COLUMNS',g_event_level);
xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.UPDATE_CURR_COL');
xtr_risk_debug_pkg.dlog('EXCEPTION','OTHERS','QRM_PA_AGGREGATION_P.UPDATE_CURR_COL',g_error_level);--bug 3236479
END update_currency_columns;
FUNCTION update_aggregate_curr(p_name VARCHAR2,
p_ref_date DATE,
p_ccy_case_flag NUMBER,
p_ccy_agg_flag NUMBER,
p_ccy_agg_level NUMBER,
p_row_agg_no NUMBER,
p_max_col_no NUMBER,
p_underlying_ccy VARCHAR2,
p_currency_source VARCHAR2,
p_curr_reporting VARCHAR2,
p_agg_col_curr IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE)
RETURN BOOLEAN IS
v_curr SYSTEM.QRM_VARCHAR_table;
SELECT DECODE(type,-2,a1,-1,a1,tot_currency) FROM qrm_saved_analyses_col
WHERE analysis_name=p_name AND seq_no>p_row_agg_no
ORDER BY 1;
xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.UPDATE_AGG_CURR');--bug 3236479
v_success := update_currency_columns(1,p_name,p_ref_date,v_curr);
v_success := update_currency_columns(1,p_name,p_ref_date,v_curr);
v_sql := 'UPDATE qrm_saved_analyses_row SET curr1=DECODE(type,-1,a'||p_ccy_agg_level||',tot_currency) WHERE analysis_name=:analysis_name';
XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SAVED_ANALYSES_ROW.CURR1',
'QRM_PA_AGGREGATION_P.UPDATE_AGGREGATE_CURR',g_event_level);
v_success := update_currency_columns(2,p_name,p_ref_date,v_curr);
v_success := update_currency_columns(1,p_name,p_ref_date,v_curr);
v_success := update_currency_columns(1,p_name,p_ref_date,v_curr);
UPDATE qrm_saved_analyses_col
SET curr_col_name_map=v_curr_col_name_map(i)
WHERE analysis_name=p_name AND seq_no=i;
XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SAVED_ANALYSES_COL.CURR_COL_NAME',
'QRM_PA_AGGREGATION_P.UPDATE_AGGREGATE_CURR',g_event_level);
xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.UPDATE_AGG_CURR');-- bug 3236479
'QRM_PA_AGGREGATION_P.UPDATE_AGG_CURR',g_error_level);--bug 3236479
END update_aggregate_curr;
FUNCTION update_timebuckets_label(p_name VARCHAR2)
RETURN BOOLEAN IS
v_tb_label VARCHAR2(1);
SELECT tb_label FROM qrm_analysis_settings
WHERE analysis_name=p_name AND history_flag='S';
SELECT COUNT(*) FROM qrm_saved_analyses_col
WHERE analysis_name=p_name AND type=-2;
xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.UPDATE_TB_LABEL');
UPDATE qrm_saved_analyses_col SET a1=tb_label
WHERE analysis_name=p_name AND seq_no>v_row_agg_no
AND type<0;
UPDATE qrm_saved_analyses_col SET a1=TO_CHAR(end_date)
WHERE analysis_name=p_name AND seq_no>v_row_agg_no
AND type<0;
XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SAVED_ANALYSES_COL.A1 with v_tb_label='||v_tb_label,
'QRM_PA_AGGREGATION_P.UPDATE_TIMEBUCKETS_LABELS',g_event_level);
xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.UPDATE_TB_LABEL');
'QRM_PA_AGGREGATION_P.UPDATE_TB_LABEL',g_error_level);--bug 3236479
'QRM_PA_AGGREGATION_P.UPDATE_TB_LABEL',g_error_level);--bug 3236479
END update_timebuckets_label;
is semi-dirty that does not involved currency updates (S).
***************************************************************/
FUNCTION update_semidirty(p_name VARCHAR2, p_ref_date DATE)
RETURN VARCHAR2 IS
v_style VARCHAR2(1);
SELECT style FROM qrm_analysis_settings
WHERE analysis_name=p_name AND history_flag='S';
xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.UPDATE_SEMIDIRTY');
v_success := update_total(p_name,p_ref_date);
xtr_risk_debug_pkg.dlog('update_semidirty: ' || 'v_successT',v_success);
RAISE e_pagg_update_total_fail;
v_success := update_percent (p_name,p_ref_date);
xtr_risk_debug_pkg.dlog('update_semidirty: ' || 'v_success%',v_success);
RAISE e_pagg_update_percent_fail;
v_success := update_timebuckets_label(p_name);
xtr_risk_debug_pkg.dlog('update_semidirty: ' || 'v_successTBLabel',v_success);
RAISE e_pagg_update_tb_label_fail;
UPDATE qrm_analysis_settings SET dirty='N' WHERE analysis_name=p_name;
xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.UPDATE_SEMIDIRTY');
'QRM_PA_AGGREGATION_P.UPDATE_SEMIDIRTY',
g_error_level);
'QRM_PA_AGGREGATION_P.UPDATE_SEMIDIRTY',g_error_level);--bug 3236479
END update_semidirty;
SELECT usd_base_curr_bid_rate bid_rate,
usd_base_curr_offer_rate ask_rate,
1/usd_base_curr_offer_rate bid_rate_base,
1/usd_base_curr_bid_rate ask_rate_base,
currency
FROM xtr_spot_rates
WHERE (rate_date, currency) IN (SELECT MAX(rate_date), currency
FROM xtr_spot_rates
WHERE currency IN (p_base_ccy, p_contra_ccy)
AND currency <> 'USD'
AND trunc(rate_date) <= trunc(p_spot_date)
GROUP BY currency);