[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
trg_select VARCHAR2(2000); -- Buffer for dynamic sql stmt
SELECT tl.target_level_id target_level_id,
tl.dimension1_level_id dim1_level_id,
tl.dimension2_level_id dim2_level_id,
decode(dl1.dimension_level_short_name,
c_t_gl_companys, -1,
c_gl_company, 0) dim1_level_code,
decode(dl2.dimension_level_short_name,
c_t_gl_sms, -1,
c_gl_sm, 0) dim2_level_code,
workflow_item_type wf_item_type,
workflow_process_short_name wf_process
FROM bis_indicators ind,
bisbv_target_levels tl,
bisbv_dimension_levels dl1,
bisbv_dimension_levels dl2
WHERE ind.short_name = c_gl_revenue_pm
AND tl.measure_id = ind.indicator_id
AND dl1.dimension_level_id = tl.dimension1_level_id
AND dl2.dimension_level_id = tl.dimension2_level_id
AND NOT (dl1.dimension_level_short_name = c_t_gl_companys
AND dl2.dimension_level_short_name = c_gl_sm)
ORDER BY tl.target_level_id;
SELECT substr(pp_period_id,
instr(pp_period_id, '+') + 1,
length(pp_period_id)),
substr(pp_period_id,
1, instr(pp_period_id, '+') -1)
INTO g_period_name,
g_period_set_name
FROM DUAL;
SELECT p.period_num,
p.period_year
INTO g_period_num,
g_period_year
FROM gl_periods p
WHERE period_set_name = g_period_set_name
AND period_name = g_period_name;
trg_select := 'SELECT ' ||
' trg.target_id ' ||
', bplan.plan_id ' ||
', bplan.name ' ||
', trg.org_level_value_id ' ||
', sob.name ' ||
', sob.chart_of_accounts_id ' ||
', trg.time_level_value_id ' ||
', trg.dim1_level_value_id ' ||
', trg.dim2_level_value_id ' ||
', trg.range1_low * -1' ||
', trg.range1_high ' ||
', trg.range2_low * -1' ||
', trg.range2_high ' ||
', trg.range3_low * -1' ||
', trg.range3_high ' ||
', trg.notify_resp1_id ' ||
', trg.notify_resp1_short_name ' ||
', trg.notify_resp2_id ' ||
', trg.notify_resp2_short_name ' ||
', trg.notify_resp3_id ' ||
', trg.notify_resp3_short_name ';
trg_select := trg_select || ', sg1.segment_num ';
trg_select := trg_select || ', -1 ';
trg_select := trg_select || ', sg2.segment_num ';
trg_select := trg_select || ', -1 ';
trg_select := trg_select ||
'FROM ' ||
' bisbv_targets trg ' ||
', bisbv_business_plans bplan ' ||
', bisbv_target_levels tl ' ||
', gl_sets_of_books sob ';
trg_select := trg_select ||
', bis_flex_mappings_v fm1 ' ||
', fnd_id_flex_segments sg1 ';
trg_select := trg_select ||
', bis_flex_mappings_v fm2 ' ||
', fnd_id_flex_segments sg2 ';
trg_select := trg_select ||
'WHERE tl.target_level_id = :target_level_id ' ||
'AND trg.target_level_id = tl.target_level_id ' ||
'AND trg.time_level_value_id = :period_id ' ||
'AND bplan.plan_id = trg.plan_id ' ||
'AND sob.set_of_books_id = to_number(trg.org_level_value_id) ';
trg_select := trg_select ||
'AND fm1.level_id = :dim1_level_id ' ||
'AND fm1.application_id = 101 ' ||
'AND fm1.id_flex_code = ''GL#'' ' ||
'AND fm1.structure_num = sob.chart_of_accounts_id ' ||
'AND sg1.application_id = 101 ' ||
'AND sg1.id_flex_code = ''GL#'' ' ||
'AND sg1.id_flex_num = fm1.structure_num ' ||
'AND sg1.application_column_name = fm1.application_column_name ';
trg_select := trg_select ||
'AND fm2.level_id = :dim2_level_id ' ||
'AND fm2.application_id = 101 ' ||
'AND fm2.id_flex_code = ''GL#'' ' ||
'AND fm2.structure_num = sob.chart_of_accounts_id ' ||
'AND sg2.application_id = 101 ' ||
'AND sg2.id_flex_code = ''GL#'' ' ||
'AND sg2.id_flex_num = fm2.structure_num ' ||
'AND sg2.application_column_name = fm2.application_column_name ' ||
'ORDER BY trg.org_level_value_id';
SELECT pt.number_per_fiscal_year
INTO l_num_per_year
FROM gl_sets_of_books sob,
gl_period_types pt
WHERE sob.set_of_books_id = to_number(p_organization_id)
AND pt.period_type = sob.accounted_period_type;
SELECT -1*period_to_date
INTO ret_amount
FROM gl_oasis_summary_data
WHERE set_of_books_id = to_number(p_organization_id)
AND fin_item_id = 'REVENUE'
AND drilldown_segnum1 = p_dim1_segnum
AND drilldown_segnum2 = p_dim2_segnum
AND actual_flag = p_amount_type
AND drilldown_segval1 = decode(p_dim1_segnum, -1, '-1', p_dim1_id)
AND drilldown_segval2 = decode(p_dim2_segnum, -1, '-1', p_dim2_id)
AND relative_period_pos = g_period_pos;
SELECT dl.dimension_level_name, dl.dimension_level_name
INTO l_dim1_value, l_dim1_name
FROM bisbv_dimension_levels dl
WHERE dl.dimension_level_short_name = c_t_gl_companys;
SELECT dl.dimension_level_name
INTO l_dim1_name
FROM bisbv_dimension_levels dl
WHERE dl.dimension_level_short_name = c_gl_company;
SELECT dl.dimension_level_name, dl.dimension_level_name
INTO l_dim2_value, l_dim2_name
FROM bisbv_dimension_levels dl
WHERE dl.dimension_level_short_name = c_t_gl_sms;
SELECT dl.dimension_level_name
INTO l_dim2_name
FROM bisbv_dimension_levels dl
WHERE dl.dimension_level_short_name = c_gl_sm;
OPEN c_target_rec FOR trg_select USING
tl_rec.target_level_id, p_period_id;
OPEN c_target_rec FOR trg_select USING
tl_rec.target_level_id, p_period_id, tl_rec.dim1_level_id;
OPEN c_target_rec FOR trg_select USING
tl_rec.target_level_id, p_period_id,
tl_rec.dim1_level_id, tl_rec.dim2_level_id;
dbms_output.put_line('======= TRG_SELECT =======');
dbms_output.put_line(substrb(trg_select, 1, 245));
dbms_output.put_line(substrb(trg_select, 246, 245));
dbms_output.put_line(substrb(trg_select, 491, 245));
l_user_selection_tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
SELECT per.period_set_name || '+' || per.period_name
INTO l_period_id
FROM gl_sets_of_books sob,
gl_periods per
WHERE sob.set_of_books_id = p_sob_id
AND per.period_set_name = sob.period_set_name
AND per.period_type = sob.accounted_period_type
AND trunc(sysdate) between per.start_date and per.end_date
AND nvl(per.adjustment_period_flag,'N')='N'; --bug3457467
BIS_ACTUAL_PUB.retrieve_user_selections
(p_api_version => 1.0,
p_target_level_rec => l_target_level_rec,
x_indicator_region_tbl => l_user_selection_tbl,
x_return_status => g_return_status,
x_msg_count => g_msg_count,
x_msg_data => g_msg_data,
x_error_tbl => g_error_tbl);
i := l_user_selection_tbl.FIRST;
IF l_user_selection_tbl(i).org_level_value_id = to_char(p_sob_id) THEN
-- If dimension level is GL COMPANY
IF (tl_rec.dim1_level_code = 0) THEN
l_dim1_segnum := p_mapped_segnum1;
p_organization_id => l_user_selection_tbl(i).org_level_value_id,
p_dim1_id => l_user_selection_tbl(i).dim1_level_value_id,
p_dim1_segnum => l_dim1_segnum,
p_dim2_id => l_user_selection_tbl(i).dim2_level_value_id,
p_dim2_segnum => l_dim2_segnum,
p_amount_type => 'A');
p_dim1_id => l_user_selection_tbl(i).dim1_level_value_id,
p_dim2_id => l_user_selection_tbl(i).dim2_level_value_id,
p_actual => nvl(l_actual, 0));
i := l_user_selection_tbl.NEXT(i);
END LOOP; -- l_user_selection_tbl loop
SELECT sg.segment_num
FROM bisbv_target_levels tl,
bisbv_dimension_levels dl,
gl_sets_of_books sob,
bis_flex_mappings_v fm,
fnd_id_flex_segments sg
WHERE tl.target_level_id = p_target_rec.target_level_id
AND dl.dimension_level_id = tl.dimension1_level_id
AND dl.dimension_level_short_name <> c_t_gl_companys
AND fm.level_id = tl.dimension1_level_id
AND fm.application_id = 101
AND fm.id_flex_code = 'GL#'
AND fm.structure_num = sob.chart_of_accounts_id
AND sg.application_id = 101
AND sg.id_flex_code = 'GL#'
AND sg.id_flex_num = fm.structure_num
AND sg.application_column_name = fm.application_column_name
AND sob.set_of_books_id = to_number(p_target_rec.org_level_value_id);
SELECT sg.segment_num
FROM bisbv_target_levels tl,
bisbv_dimension_levels dl,
gl_sets_of_books sob,
bis_flex_mappings_v fm,
fnd_id_flex_segments sg
WHERE tl.target_level_id = p_target_rec.target_level_id
AND dl.dimension_level_id = tl.dimension1_level_id
AND dl.dimension_level_short_name <> c_t_gl_sms
AND fm.level_id = tl.dimension2_level_id
AND fm.application_id = 101
AND fm.id_flex_code = 'GL#'
AND fm.structure_num = sob.chart_of_accounts_id
AND sg.application_id = 101
AND sg.id_flex_code = 'GL#'
AND sg.id_flex_num = fm.structure_num
AND sg.application_column_name = fm.application_column_name
AND sob.set_of_books_id = to_number(p_target_rec.org_level_value_id);
SELECT per.period_set_name || '+' || per.period_name
INTO l_period_id
FROM gl_sets_of_books sob,
gl_periods per
WHERE sob.set_of_books_id = to_number(p_target_rec.org_level_value_id)
AND per.period_set_name = sob.period_set_name
AND per.period_type = sob.accounted_period_type
AND trunc(sysdate) between per.start_date and per.end_date
AND nvl(per.adjustment_period_flag,'N')='N'; --bug3457467