[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_AI_ATWTR_PRTLT_PKG
Source
1 PACKAGE BODY BIX_PMV_AI_ATWTR_PRTLT_PKG AS
2 /*$Header: bixitwrp.plb 120.0 2005/05/25 17:22:41 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 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 poa_dbi_util_pkg.add_column(bix_col_tab,'call_talk_time','talk' ,'N',2,'XTD');
74 poa_dbi_util_pkg.add_column(bix_col_tab,'agent_wrap_time_nac','wrap' ,'N',2,'XTD');
75 poa_dbi_util_pkg.add_column(bix_col_tab,'call_calls_handled_total','hand' ,'N',2,'XTD');
76
77 --pass l_xtd here. This
78 poa_dbi_util_pkg.add_column(p_cols,'0' ,'talk','N',2,'XTD');
79 poa_dbi_util_pkg.add_column(p_cols,'0' ,'wrap','N',2,'XTD');
80 poa_dbi_util_pkg.add_column(p_cols,'call_cont_calls_handled_tot_na' ,'hand','N',2,'XTD');
81
82 l_union_all_text := 'UNION ALL '||bix_pmv_dbi_utl_pkg.get_continued_measures
83 (p_bix_col_tab => p_cols,
84 p_where_clause => l_where_clause,
85 p_xtd => l_xtd,
86 p_comparison_type => l_comp_type,
87 p_mv_set => 'ITM');
88
89
90 l_sqltext := ' SELECT name VIEWBY,
91 -- SUM(p_talk)/DECODE(SUM(p_hand),0,null,SUM(p_hand)) BIX_PMV_AI_PREVAVGTALK,
92 SUM(curr_talk)/DECODE(SUM(curr_hand),0,null,SUM(curr_hand)) BIX_PMV_AI_AVGTALK ,
93 -- SUM(p_wrap)/DECODE(SUM(p_hand),0,null,SUM(p_hand)) BIX_PMV_AI_PREVAVGWRAP,
94 SUM(curr_wrap)/DECODE(SUM(curr_hand),0,null,SUM(curr_hand)) BIX_PMV_AI_AVGWRAP
95 FROM '||'(SELECT iset.*,cal.start_date st_date,cal.name
96 FROM '||bix_pmv_dbi_utl_pkg.trend_sql
97 (p_xtd => l_xtd,
98 p_comparison_type => l_comp_type,
99 p_fact_name => l_mv,
100 p_where_clause => l_where_clause,
101 p_col_name => bix_col_tab,
102 p_use_grpid => 'N',
103 p_in_join_tables => NULL,
104 p_fact_hint => NULL,
105 p_union_clause =>l_union_all_text )||'
106 ) GROUP BY name ,st_date order by st_date ';
107
108
109
110
111 p_custom_sql := l_sqltext;
112
113
114 poa_dbi_util_pkg.get_custom_trend_binds (p_xtd => l_xtd,
115 p_comparison_type => l_comp_type,
116 x_custom_output => p_custom_output);
117
118 bix_pmv_dbi_utl_pkg.get_bind_vars (x_custom_output => p_custom_output
119 ,p_func_area => NULL);
120
121 EXCEPTION
122 WHEN OTHERS THEN
123 l_sql_errm := SQLERRM;
124
125 END GET_SQL;
126 END BIX_PMV_AI_ATWTR_PRTLT_PKG;