[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_AI_SATR_PRTLT_PKG
Source
1 PACKAGE BODY BIX_PMV_AI_SATR_PRTLT_PKG AS
2 /*$Header: bixisoap.plb 120.0 2005/05/25 17:15:02 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 l_custom_rec BIS_QUERY_ATTRIBUTES := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
50
51 BEGIN
52 --
53 --Initialize p_custom_output
54 --
55 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
56
57 bix_pmv_dbi_utl_pkg.process_parameters(
58 p_param => p_page_parameter_tbl
59 ,p_trend => 'Y'
60 ,p_func_area => 'ITMAT'
61 ,p_version => '6.0'
62 ,p_mv_set => 'ITM'
63 ,p_where_clause => l_where_clause
64 ,p_mv => l_mv
65 ,p_join_tbl => l_join_tbl
66 ,p_comp_type => l_comp_type
67 ,p_xtd => l_xtd
68 ,p_view_by_select => l_view_by_select
69 ,p_view_by => l_dummy);
70
71
72 poa_dbi_util_pkg.add_column(bix_col_tab,'call_tot_queue_to_answer','anstime' ,'N',2,'XTD');
73 poa_dbi_util_pkg.add_column(bix_col_tab,'call_calls_handled_total','hand' ,'N',2,'XTD');
74
75 --Columns, for which sum is not to be taken, pass them as 0.
76
77 poa_dbi_util_pkg.add_column(p_cols,'0' ,'anstime','N',2,'XTD');
78 poa_dbi_util_pkg.add_column(p_cols,'agent_cont_calls_hand_na' ,'hand','N',2,'XTD');
79
80 l_union_all_text := 'UNION ALL '||bix_pmv_dbi_utl_pkg.get_continued_measures
81 (p_bix_col_tab => p_cols,
82 p_where_clause => l_where_clause,
83 p_xtd => l_xtd,
84 p_comparison_type => l_comp_type,
85 p_mv_set => 'ITM');
86
87 l_where_clause := l_where_clause ||' AND fact.media_item_type IN (''TELE_INB'', ''TELE_DIRECT'') ';
88
89 BEGIN
90
91 l_sqltext := 'SELECT name VIEWBY,
92 SUM(p_anstime)/NULLIF(SUM(p_hand),0) BIX_PMV_AI_PREVSPANS,
93 SUM(curr_anstime)/NULLIF(SUM(curr_hand),0) BIX_PMV_AI_SPANS
94 FROM '||'(SELECT iset.*,cal.start_date st_date,cal.name
95 FROM '||bix_pmv_dbi_utl_pkg.trend_sql
96 (p_xtd => l_xtd,
97 p_comparison_type => l_comp_type,
98 p_fact_name => l_mv,
99 p_where_clause => l_where_clause,
100 p_col_name => bix_col_tab,
101 p_use_grpid => 'N',
102 p_in_join_tables => NULL,
103 p_fact_hint => NULL,
104 p_union_clause =>l_union_all_text )||'
105 ) GROUP BY name ,st_date order by st_date ';
106
107 EXCEPTION
108 WHEN OTHERS THEN
109 l_errmsg := SQLERRM;
110 END;
111
112
113
114 p_custom_sql := l_sqltext;
115
116
117 poa_dbi_util_pkg.get_custom_trend_binds (p_xtd => l_xtd,
118 p_comparison_type => l_comp_type,
119 x_custom_output => p_custom_output);
120
121 bix_pmv_dbi_utl_pkg.get_bind_vars (x_custom_output => p_custom_output
122 ,p_func_area => NULL);
123
124 EXCEPTION
125 WHEN OTHERS THEN
126 l_sql_errm := SQLERRM;
127
128
129 END GET_SQL;
130 END BIX_PMV_AI_SATR_PRTLT_PKG;