[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_AI_AURTR_PRTLT_PKG
Source
1 PACKAGE BODY BIX_PMV_AI_AURTR_PRTLT_PKG AS
2 /*$Header: bixiaurp.plb 120.0 2005/05/25 17:28:21 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 bix_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
54
55 BEGIN
56
57 --
58 --Initialize p_custom_output
59 --
60 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
61
62 --call the process parameters whith p_mv_set = 'SES' ,ie. for agent session
63 bix_pmv_dbi_utl_pkg.process_parameters(
64 p_param => p_page_parameter_tbl
65 ,p_trend => 'Y'
66 ,p_func_area => 'ITMAT'
67 ,p_version => '6.0'
68 ,p_mv_set => 'SES'
69 ,p_where_clause => l_where_clause
70 ,p_mv => l_mv
71 ,p_join_tbl => l_join_tbl
72 ,p_comp_type => l_comp_type
73 ,p_xtd => l_xtd
74 ,p_view_by_select => l_view_by_select
75 ,p_view_by => l_dummy);
76
77 poa_dbi_util_pkg.add_column(bix_col_tab,'agent_talk_time_nac','talk' ,'N',2,'XTD');
78 poa_dbi_util_pkg.add_column(bix_col_tab,'agent_wrap_time_nac','wrap' ,'N',2,'XTD');
79 poa_dbi_util_pkg.add_column(bix_col_tab,'0' ,'login','N',2,'XTD');
80 poa_dbi_util_pkg.add_column(bix_col_tab,'0' ,'work' ,'N',2,'XTD');
81 poa_dbi_util_pkg.add_column(bix_col_tab,'0' ,'avail','N',2,'XTD');
82 poa_dbi_util_pkg.add_column(bix_col_tab,'0' ,'idle' ,'N',2,'XTD');
83
84
85
86 poa_dbi_util_pkg.add_column(p_cols,'0' ,'talk' ,'N',2,'XTD');
87 poa_dbi_util_pkg.add_column(p_cols,'0' ,'wrap' ,'N',2,'XTD');
88 poa_dbi_util_pkg.add_column(p_cols,'login_time' ,'login','N',2,'XTD');
89 poa_dbi_util_pkg.add_column(p_cols,'work_time' ,'work ','N',2,'XTD');
90 poa_dbi_util_pkg.add_column(p_cols,'available_time','avail','N',2,'XTD');
91 poa_dbi_util_pkg.add_column(p_cols,'idle_time' ,'idle ','N',2,'XTD');
92
93
94 l_union_all_text := 'UNION ALL '||bix_pmv_dbi_utl_pkg.get_continued_measures
95 (p_bix_col_tab => p_cols,
96 p_where_clause => l_where_clause,
97 p_xtd => l_xtd,
98 p_comparison_type => l_comp_type,
99 p_mv_set => 'SES');
100
101 bix_pmv_dbi_utl_pkg.process_parameters(
102 p_param => p_page_parameter_tbl
103 ,p_trend => 'Y'
104 ,p_func_area => 'ITMAT'
105 ,p_version => '6.0'
106 ,p_mv_set => 'ITM'
107 ,p_where_clause => l_where_clause
108 ,p_mv => l_mv
109 ,p_join_tbl => l_join_tbl
110 ,p_comp_type => l_comp_type
111 ,p_xtd => l_xtd
112 ,p_view_by_select => l_view_by_select
113 ,p_view_by => l_dummy);
114
115 BEGIN
116
117 l_sqltext := 'SELECT name VIEWBY,
118 sum(nvl(curr_login,0)-nvl(curr_idle,0))*100/decode(sum(curr_login),0,null,sum(curr_login)) bix_pmv_ai_availrate,
119 /* sum(nvl(p_login,0)-nvl(p_idle,0))*100/decode(sum(p_login),0,null,sum(p_login)) bix_pmv_ai_prevavailrate,*/
120 sum(nvl(curr_login,0)-nvl(curr_idle,0)-nvl(curr_avail,0))*100/decode(sum(curr_login),0,null,sum(curr_login)) bix_pmv_ai_utilrate/*,
121 sum(nvl(p_login,0)-nvl(p_idle,0)-nvl(p_avail,0))*100/decode(sum(p_login),0,null,sum(p_login)) bix_pmv_ai_prevutilrate*/
122 FROM '||'(SELECT iset.*,cal.start_date st_date,cal.name
123 FROM '||bix_pmv_dbi_utl_pkg.trend_sql
124 (p_xtd => l_xtd,
125 p_comparison_type => l_comp_type,
126 p_fact_name => l_mv,
127 p_where_clause => l_where_clause,
128 p_col_name => bix_col_tab,
129 p_use_grpid => 'N',
130 p_in_join_tables => NULL,
131 p_fact_hint => NULL,
132 p_union_clause =>l_union_all_text )||'
133 ) GROUP BY name ,st_date order by st_date ';
134
135 EXCEPTION
136 WHEN OTHERS THEN
137 l_errmsg := SQLERRM;
138 END;
139
140
141 p_custom_sql := l_sqltext;
142
143
144 poa_dbi_util_pkg.get_custom_trend_binds (p_xtd => l_xtd
145 ,p_comparison_type => l_comp_type
146 ,x_custom_output => p_custom_output);
147
148 bix_pmv_dbi_utl_pkg.get_bind_vars (x_custom_output => p_custom_output
149 ,p_func_area => NULL);
150
151
152 EXCEPTION
153 WHEN OTHERS THEN
154 l_sql_errm := SQLERRM;
155 NULL;
156
157 END GET_SQL;
158 END BIX_PMV_AI_AURTR_PRTLT_PKG;