[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_AI_ORR_RPT_PKG
Source
1 PACKAGE BODY BIX_PMV_AI_ORR_RPT_PKG AS
2 /*$Header: bixiorrr.plb 120.1 2006/03/28 22:58:32 pubalasu noship $ */
3
4 FUNCTION GET_ZERONULL_CLAUSE RETURN VARCHAR2
5 IS
6 l_zeronull_clause VARCHAR2(1500);
7 BEGIN
8 l_zeronull_clause:=') WHERE (nvl(BIX_PMV_AI_COUNT,0)+nvl(BIX_PMV_AI_COUNTCHANGE,0))<>0';
9 return l_zeronull_clause;
10 EXCEPTION
11 WHEN OTHERS THEN
12 NULL;
13 END GET_ZERONULL_CLAUSE;
14
15
16 FUNCTION GET_MEASURES RETURN VARCHAR2
17 IS
18 l_measure_txt VARCHAR2(32000);
19 l_unknown VARCHAR2(50);
20
21 BEGIN
22
23 /*
24 pubalasu:
25 Get Measures for this report does not use the get_simple_measure, get_Divided
26 measure of Util Package because the format for measures in this report is
27 a,sum(a) and not sum(a),sum(sum(a)) over()
28 */
29
30
31 l_measure_txt:=
32 'SELECT outcome_code BIX_PMV_AI_OUTCOME,
33 NVL(result_code,:l_unknown) BIX_PMV_AI_RESULT
34 ,NVL(reason_code,:l_unknown) BIX_PMV_AI_REASON
35 ,NVL(c_count,0) BIX_PMV_AI_COUNT
36 ,sum(nvl(c_count,0)) over() BIX_PMV_TOTAL1
37 ,(NVL(c_count,0)*100/decode(c_counttot,0,null,c_counttot)) BIX_PMV_AI_PERTOTAL1
38 ,(c_count*100/decode(c_counttot,0,null,c_counttot)) -
39 (p_count*100/decode(p_counttot,0,null,p_counttot)) BIX_PMV_AI_COUNTCHANGE
40 FROM
41 (';
42
43 RETURN l_measure_txt;
44
45 END GET_MEASURES;
46
47 PROCEDURE GET_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
48 p_sql_text OUT NOCOPY VARCHAR2,
49 p_custom_output OUT NOCOPY bis_query_attributes_TBL
50 )
51 AS
52 l_sqltext VARCHAR2(32000) ;
53 l_func_area CONSTANT varchar2(5) := 'IORRR';
54 l_version varchar2(3) := NULL;
55 l_mv_set CONSTANT varchar2(3) := 'ITM';
56 l_where_clause VARCHAR2(1000) ;
57 l_filter_where VARCHAR2(1000) ;
58 l_mv VARCHAR2 (240);
59 l_comp_type VARCHAR2(500) ;
60 l_xtd VARCHAR2(500) ;
61 l_view_by VARCHAR2 (120);
62 l_view_by_select VARCHAR2(500) ;
63
64
65
66 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
67 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
68 l_timetype CONSTANT varchar2(3) := 'XTD';
69
70
71 BEGIN
72
73
74 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
75 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl ();
76 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
77
78
79 bix_pmv_dbi_utl_pkg.process_parameters
80 ( p_param => p_page_parameter_tbl
81 , p_trend => 'N'
82 , p_func_area => l_func_area
83 , p_version => l_version
84 , p_mv_set => l_mv_set
85 , p_where_clause => l_where_clause
86 , p_mv => l_mv
87 , p_join_tbl => l_join_tbl
88 , p_comp_type => l_comp_type
89 , p_xtd => l_xtd
90 , p_view_by_select => l_view_by_select
91 , p_view_by => l_view_by
92 );
93
94
95 /* pubalasu:Process parameters returns the correct where clause, mv for the query */
96 /* pubalasu:Add columns to the select list of the innermost query */
97 poa_dbi_util_pkg.add_column(
98 p_col_tbl => l_col_tbl
99 , p_col_name => 'AGENTCALL_ORR_COUNT'
100 , p_grand_total => 'N'
101 , p_alias_name => 'count'
102 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
103 , p_to_date_type => l_timetype
104 );
105 l_sqltext:= poa_dbi_template_pkg.status_sql (
106 p_fact_name => l_mv
107 , p_where_clause => l_where_clause
108 , p_filter_where => l_filter_where
109 , p_join_tables => l_join_tbl
110 , p_use_windowing => 'N'
111 , p_col_name => l_col_tbl
112 , p_use_grpid => 'N'
113 , p_paren_count => 3
114 , p_generate_viewby => 'N'
115 );
116
117
118
119 l_Sqltext:=
120 'SELECT * FROM
121 (
122 '
123 ||
124 GET_MEASURES
125 ||
126 '
127 (
128 select outcome_id,result_id,reason_id,c_count,p_count,
129 sum(c_count) over(partition by outcome_id) c_counttot,
130 sum(p_count) over(partition by outcome_id) p_counttot
131 from
132 '
133 ||l_sqltext
134 || ',' || bix_pmv_dbi_utl_pkg.get_orr_views
135 ||get_zeronull_clause
136
137 ;
138
139 p_sql_text:=l_sqltext;
140 bix_pmv_dbi_utl_pkg.get_bind_vars (p_custom_output,p_func_area => l_func_area);
141
142
143
144 EXCEPTION
145 WHEN OTHERS THEN
146 NULL;
147 END GET_SQL;
148 END BIX_PMV_AI_ORR_RPT_PKG;
149
150