[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION get_viewby_select_clause (
p_view_by IN VARCHAR2
, p_func_area IN VARCHAR2
, p_version IN VARCHAR2)
RETURN VARCHAR2 ;
, p_view_by_select OUT NOCOPY VARCHAR2
, p_view_by OUT NOCOPY VARCHAR2
)
IS
l_dim_map poa_dbi_util_pkg.poa_dbi_dim_map;
insert into bix_debug values
( 'p_view_by:' || p_view_by ||
'p_comparison_type:' || p_comparison_type ||
'p_xtd:' || p_xtd ||
'p_as_of_date:' || p_as_of_date ||
'p_prev_as_of_date:' || p_prev_as_of_date ||
'p_nested_pattern:' || p_nested_pattern
);
/* Get the filter where clause [value of the dimensions selected ] and
concatenate it with the where clause with row_type*/
IF p_mv_set='SES' THEN
p_where_clause :=poa_dbi_util_pkg.get_where_clauses (l_dim_map, p_trend)
||get_agent_group_where_clause(p_param,p_mv_set)||' and application_id=696';
IF p_trend <> 'Y' THEN /* For Trend portlets, get join info and view by select is not necessary */
get_join_info (p_view_by
, l_dim_map
, p_join_tbl
, p_func_area
, p_version);
p_view_by_select := get_viewby_select_clause(p_view_by,p_func_area,p_version);
/* pubalasu: p_insetclause can be any where condition to insert
before the order by clause
*/
RETURN ' jtf_ih_outcomes_vl outcome,jtf_ih_results_vl result,jtf_ih_reasons_vl reason
WHERE oset.outcome_id = outcome.outcome_id(+)
AND oset.result_id = result.result_id(+)
AND oset.reason_id = reason.reason_id(+)
order by outcome.outcome_code,result.result_code,reason.reason_code
' ;
get_dnis_where_clause:Determines if DNIS is selected and creates a where clause
accordingly
----------------------------------------------------------------------------- */
FUNCTION get_dnis_where_clause(p_page_parameter_tbl IN bis_pmv_page_parameter_tbl) RETURN VARCHAR2
IS
l_dnis varchar2(3000);
get_agent_group_where_clause:Determines if agent group is selected and creates a where clause
accordingly
----------------------------------------------------------------------------- */
FUNCTION get_agent_group_where_clause(
p_page_parameter_tbl IN bis_pmv_page_parameter_tbl,
p_mv_set IN varchar2
) RETURN VARCHAR2
IS
l_agent_group varchar2(3000);
SELECT 1
FROM jtf_rs_group_members mem
WHERE fact.agent_id = mem.resource_id
AND mem.group_id IN (:l_agent_group)
AND nvl(mem.delete_flag, ''N'') <> ''Y''
)';
SELECT 1
FROM jtf_rs_group_members mem
WHERE fact.resource_id = mem.resource_id
AND mem.group_id IN (:l_agent_group)
AND nvl(mem.delete_flag, ''N'') <> ''Y''
)';
'(Select dummy from dual)'
END
);
get_viewby_select_clause:returns select clause for view by
----------------------------------------------------------------------------- */
FUNCTION get_viewby_select_clause (
p_view_by IN VARCHAR2
, p_func_area IN VARCHAR2
, p_version IN VARCHAR2)
RETURN VARCHAR2
IS
l_view_by_select VARCHAR2(1000);
l_view_by_select:='SELECT '||
(CASE p_view_by
WHEN g_ai_ccntr_dim
THEN 'v.group_name VIEWBY,v.server_group_id VIEWBYID '
WHEN g_ai_dnis_dim
THEN 'dnis_name VIEWBY,dnis_name VIEWBYID '
WHEN g_ai_class_dim
THEN 'classification_value VIEWBY,classification_value VIEWBYID '
ELSE
'v.value VIEWBY,v.id VIEWBYID '
END);
RETURN l_view_by_select;
'(select '
|| l_group_and_sel_clause
|| l_col_names
|| '
from '
|| p_fact_name
|| ' fact'
|| l_full_where_clause
|| '
group by '
|| l_group_and_sel_clause
|| ')';
'(select n.start_date'
|| case when p_xtd like 'RL%' then ', n.ordinal ' end
|| '
' ||l_col_names || '
from (select ' || p_fact_hint || ' '
|| case when p_xtd like 'RL%' then 'n.ordinal, ' end
|| 'n.start_date, n.report_date '
|| l_inner_col_names
|| ' from '
|| p_fact_name
|| ' fact,
'
|| case
when p_xtd like 'RL%' then
case
when l_balance_report = 'N' then
'( select /*+ NO_MERGE */ cal.ordinal,n.time_id,n.record_type_id,n.period_type_id,n.report_date,cal.start_date,cal.end_date'
|| ' from ' || poa_dbi_util_pkg.get_calendar_table(p_xtd)
|| ' cal, fii_time_structures n
where '
|| l_time_clause
|| l_cal_clause
-- &RLX_NESTED_PATTERN should be replaced with
-- some &BIS bind substitution when available from fii/bis team.
|| ' and bitand(n.record_type_id,&RLX_NESTED_PATTERN) = &RLX_NESTED_PATTERN ) n'
|| l_in_join_tables
|| '
where fact.time_id = n.time_id
and fact.period_type_id = n.period_type_id
'
else
'( select /*+ NO_MERGE */ cal.ordinal,cal.start_date, cal.report_date'
|| ' from ' || poa_dbi_util_pkg.get_calendar_table(p_xtd,'Y',l_compute_opening_bal)
|| ' cal where '
|| l_time_clause
|| l_cal_clause
|| ' ) n
where fact.report_date = least(n.report_date,&LAST_COLLECTION)
'
end
else -- non RL%
' (select /*+ NO_MERGE */ n.time_id,n.record_type_id, n.period_type_id,n.report_date,cal.start_date,cal.end_date
from '
|| poa_dbi_util_pkg.get_calendar_table (p_xtd)
|| ' cal, fii_time_rpt_struct_v n
where '
|| l_time_clause
|| l_cal_clause
|| ' and bitand(n.record_type_id, '
|| CASE -- if one or more columns = YTD then use nested pattern
WHEN BITAND (l_inlist_bmap, g_inlist_ytd) = g_inlist_ytd
THEN '&YTD_NESTED_PATTERN'
ELSE '&BIS_NESTED_PATTERN'
END
|| ') = n.record_type_id ) n
' || l_in_join_tables || '
where fact.time_id = n.time_id
'
end
|| l_grpid_clause || '
' || p_where_clause || '
group by '
|| case when p_xtd like 'RL%' then 'n.ordinal, ' end
|| ' n.start_date, n.report_date) i, '
|| poa_dbi_util_pkg.get_calendar_table(p_xtd,'Y',l_compute_opening_bal)
|| ' n where i.start_date (+) = n.start_date '
|| l_outer_time_clause
|| case when p_xtd like 'RL%' then 'and i.ordinal(+) = n.ordinal ' end
|| ' group by '
|| case when p_xtd like 'RL%' then 'n.ordinal, ' end
|| 'n.start_date '
|| p_union_clause
|| ') iset, '
|| poa_dbi_util_pkg.get_calendar_table (p_xtd,'N','N')
|| ' cal '
|| '
where cal.start_date between '
|| case
when p_xtd like 'RL%' then
poa_dbi_util_pkg.get_report_start_date(p_xtd)
else
'&BIS_CURRENT_REPORT_START_DATE'
end
|| ' and &BIS_CURRENT_ASOF_DATE
and cal.start_date = iset.start_date(+)'
|| case when p_xtd like 'RL%' then ' and cal.ordinal = iset.ordinal(+)' end
|| '
order by cal.start_date';
l_select_list VARCHAR2(4000);
l_select_list := 'SELECT fii1.START_DATE ' ;
l_select_list := l_select_list||fnd_global.newline||','||p_bix_col_tab(i).COLUMN_NAME||' '||'curr_'||p_bix_col_tab(i).COLUMN_ALIAS
||fnd_global.newline||','||p_bix_col_tab(i).COLUMN_NAME||' '||'p_'||p_bix_col_tab(i).COLUMN_ALIAS;
l_select_list := l_select_list||fnd_global.newline||','||p_bix_col_tab(i).COLUMN_NAME||' '||'curr_'||p_bix_col_tab(i).COLUMN_ALIAS;
l_select_list := l_select_list||fnd_global.newline||','||
'SUM(CASE when(fii1.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE)
THEN '||p_bix_col_tab(i).COLUMN_NAME||' ELSE 0 END) '||'curr_'||p_bix_col_tab(i).COLUMN_ALIAS
||fnd_global.newline||','||'SUM(CASE when(fii1.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE)
THEN '||p_bix_col_tab(i).COLUMN_NAME||' ELSE 0 END) '||'p_'||p_bix_col_tab(i).COLUMN_ALIAS;
l_select_list := l_select_list||fnd_global.newline||','||
'SUM(CASE when(fii1.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE)
THEN '||p_bix_col_tab(i).COLUMN_NAME||' ELSE 0 END) '||'curr_'||p_bix_col_tab(i).COLUMN_ALIAS;
p_bix_col_tab.DELETE;
l_select_list := l_select_list||l_from_list||l_where_clause||l_group_by_clause;
RETURN l_select_list;
SELECT week_start_date INTO l_period_start_date
FROM fii_time_day WHERE report_date = l_as_of_date;
SELECT ent_period_start_date INTO l_period_start_date
FROM fii_time_day WHERE report_date = l_as_of_date;
SELECT ent_qtr_start_date INTO l_period_start_date
FROM fii_time_day WHERE report_date = l_as_of_date;
SELECT ent_year_start_date INTO l_period_start_date
FROM fii_time_day WHERE report_date = l_as_of_date;
SELECT to_char(sysdate,'DD-MON-YYYY')
INTO v_date
FROM dual;
SELECT to_char(sysdate,'DD-MON-YYYY')
INTO v_date
FROM dual;
SELECT to_char(sysdate,'DD-MON-YYYY')
INTO v_date
FROM dual;