[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_AI_SLTR_PRTLT_PKG
Source
1 PACKAGE BODY BIX_PMV_AI_SLTR_PRTLT_PKG AS
2 /*$Header: bixisltp.plb 120.0 2005/05/25 17:21:30 appldev noship $ */
3
4 PROCEDURE GET_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
5 p_custom_sql OUT NOCOPY VARCHAR2,
6 p_custom_output OUT NOCOPY bis_query_attributes_TBL
7 )
8 AS
9
10 p_cols poa_dbi_util_pkg.poa_dbi_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
11 bix_col_tab poa_dbi_util_pkg.poa_dbi_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl() ;
12 poa_in_join_tab poa_dbi_util_pkg.poa_dbi_in_join_tbl := poa_dbi_util_pkg.poa_dbi_IN_join_tbl();
13
14 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
15
16 l_where_clause VARCHAR2(32000);
17 l_mv VARCHAR2(32000);
18 l_dummy VARCHAR2(32000);
19 l_view_by_select VARCHAR2(32000);
20
21 l_sqltext VARCHAR2(32000);
22 l_sqltext1 VARCHAR2(32000);
23 l_as_of_date DATE;
24 l_period_type VARCHAR2(2000);
25 l_record_type_id NUMBER;
26 l_comp_type VARCHAR2(50);
27 l_sql_errm VARCHAR2(32000);
28 l_dim_map poa_dbi_util_pkg.poa_dbi_dim_map;
29 l_previous_report_start_date DATE;
30 l_current_report_start_date DATE;
31 l_previous_as_of_date DATE;
32 l_period_type_id NUMBER;
33 l_nested_pattern NUMBER;
34 l_dim_bmap NUMBER;
35 l_curr_suffix VARCHAR2(20);
36 l_time_id_column VARCHAR2(1000);
37 l_goal NUMBER;
38 l_call_center VARCHAR2(3000);
39 l_classification VARCHAR2(3000);
40 l_dnis VARCHAR2(3000);
41 l_view_by VARCHAR2(3000);
42 l_xtd VARCHAR2(3);
43
44 l_call_where_clause VARCHAR2(3000);
45 l_errmsg VARCHAR2(1000);
46 l_session_where_clause VARCHAR2(3000);
47 l_union_all_text VARCHAR2(32000);
48
49
50
51 l_custom_rec BIS_QUERY_ATTRIBUTES := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
52
53 BEGIN
54 --
55 --Initialize p_custom_output
56 --
57 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
58
59 bix_pmv_dbi_utl_pkg.process_parameters(
60 p_param => p_page_parameter_tbl
61 ,p_trend => 'Y'
62 ,p_func_area => 'ITMAT'
63 ,p_version => '6.0'
64 ,p_mv_set => 'ITM'
65 ,p_where_clause => l_where_clause
66 ,p_mv => l_mv
67 ,p_join_tbl => l_join_tbl
68 ,p_comp_type => l_comp_type
69 ,p_xtd => l_xtd
70 ,p_view_by_select => l_view_by_select
71 ,p_view_by => l_dummy);
72
73
74
75 poa_dbi_util_pkg.add_column(bix_col_tab,'call_calls_offered_total','offrd' ,'N',2,'XTD');
76 poa_dbi_util_pkg.add_column(bix_col_tab,'agent_calls_answered_by_goal','ansgoal' ,'N',2,'XTD');
77
78
79 --Columns, for which sum is not to be taken, pass them as 0.
80
81 poa_dbi_util_pkg.add_column(p_cols,'call_cont_calls_offered_na' ,'offrd','N',2,'XTD');
82 poa_dbi_util_pkg.add_column(p_cols,'0' ,'ansgoal','N',2,'XTD');
83
84
85 l_union_all_text := 'UNION ALL '||bix_pmv_dbi_utl_pkg.get_continued_measures
86 (p_bix_col_tab => p_cols,
87 p_where_clause => l_where_clause,
88 p_xtd => l_xtd,
89 p_comparison_type => l_comp_type,
90 p_mv_set => 'ITM');
91
92 IF (FND_PROFILE.DEFINED('BIX_CALL_SLGOAL_PERCENT')) THEN
93 l_goal := TO_NUMBER(FND_PROFILE.VALUE('BIX_CALL_SLGOAL_PERCENT'));
94 ELSE
95 l_goal := 0;
96 END IF;
97
98 l_where_clause := l_where_clause || ' AND fact.media_item_type IN (''TELE_INB'', ''TELE_DIRECT'') ';
99
100 BEGIN
101
102 l_sqltext := 'SELECT name VIEWBY,
103 SUM(p_ansgoal)*100/DECODE(SUM(p_offrd),0,null,SUM(p_offrd)) BIX_PMV_AI_PREVSL,
104 SUM(curr_ansgoal)*100/DECODE(SUM(curr_offrd),0,null,SUM(curr_offrd)) BIX_PMV_AI_SL,
105 ' || l_goal ||' BIX_PMV_AI_SLGOAL
106 FROM '||'(SELECT iset.*,cal.start_date st_date,cal.name
107 FROM '||
108 bix_pmv_dbi_utl_pkg.trend_sql
109 (p_xtd => l_xtd,
110 p_comparison_type => l_comp_type,
111 p_fact_name => l_mv,
112 p_where_clause => l_where_clause,
113 p_col_name => bix_col_tab,
114 p_use_grpid => 'N',
115 p_in_join_tables => NULL,
116 p_fact_hint => NULL,
117 p_union_clause =>l_union_all_text )||'
118 ) GROUP BY name ,st_date order by st_date ';
119
120 EXCEPTION
121 WHEN OTHERS THEN
122 l_errmsg := SQLERRM;
123 END;
124
125
126 p_custom_sql := l_sqltext;
127
128
129 poa_dbi_util_pkg.get_custom_trend_binds (p_xtd => l_xtd
130 ,p_comparison_type => l_comp_type
131 ,x_custom_output => p_custom_output);
132
133 bix_pmv_dbi_utl_pkg.get_bind_vars (x_custom_output => p_custom_output
134 ,p_func_area => NULL);
135
136 EXCEPTION
137 WHEN OTHERS THEN
138 l_sql_errm := SQLERRM;
139
140 END GET_SQL;
141 END BIX_PMV_AI_SLTR_PRTLT_PKG;