[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_AO_ORR_RPT_PKG
Source
1 PACKAGE BODY BIX_PMV_AO_ORR_RPT_PKG AS
2 /*$Header: bixoorrr.plb 115.3 2004/06/01 09:06:05 pubalasu noship $ */
3
4 PROCEDURE GET_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
5 p_sql_text OUT NOCOPY VARCHAR2,
6 p_custom_output OUT NOCOPY bis_query_attributes_TBL
7 )
8 AS
9 l_sqltext VARCHAR2(32000) ;
10 l_where_clause VARCHAR2(1000) ;
11 l_call_where_clause VARCHAR2(1000) ;
12 l_source_code_where_clause VARCHAR2(1000) ;
13 l_campaign_where_clause VARCHAR2(1000) ;
14 l_schedule_where_clause VARCHAR2(1000) ;
15 l_as_of_date DATE;
16 l_period_type varchar2(2000);
17 l_comp_type varchar2(2000);
18 l_sql_errm varchar2(32000);
19 l_agent_cost NUMBER;
20 l_custom_rec BIS_QUERY_ATTRIBUTES;
21 l_record_type_id NUMBER ;
22 l_start_date DATE;
23 l_end_date DATE;
24 l_period_from DATE;
25 l_period_to DATE;
26 l_dummy_cust NUMBER;
27 l_max_collect_date VARCHAR2(100);
28 l_period_start_date DATE;
29 l_unident_string VARCHAR2(100);
30 l_application_id NUMBER := 696;
31 l_view_by varchar2(1000);
32 l_row_type varchar2(10) := 'CORR';
33 l_unknown varchar2(1000);
34 l_outcome_filter NUMBER := -1;
35 l_subtotal varchar2(1000);
36 l_call_center VARCHAR2(3000);
37
38 l_campaign_id varchar2(3000);
39 l_schedule_id varchar2(3000);
40 l_source_code_id varchar2(3000);
41 l_agent_group VARCHAR2(3000);
42
43
44 BEGIN
45
46 --
47 --Initialize p_custom_output
48 --
49
50 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
51 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
52
53 -- Get the parameters
54 BIX_PMV_DBI_UTL_PKG.get_ao_page_params( p_page_parameter_tbl,
55 l_as_of_date,
56 l_period_type,
57 l_record_type_id,
58 l_comp_type,
59 l_call_center,
60 l_campaign_id,
61 l_schedule_id,
62 l_source_code_id,
63 l_agent_group,
64 l_view_by
65 );
66
67
68
69
70 l_unknown := FND_MESSAGE.GET_STRING('BIX','BIX_PMV_UNKNOWN');
71
72 IF l_unknown IS NULL OR l_unknown = 'BIX_PMV_UNKNOWN' THEN
73 l_unknown := 'Unknown';
74 END IF;
75
76
77 l_subtotal := FND_MESSAGE.GET_STRING('BIX','BIX_PMV_SUBTOTAL');
78
79 IF l_subtotal IS NULL OR l_subtotal = 'BIX_PMV_SUBTOTAL' THEN
80 l_subtotal := 'Subtotal';
81 END IF;
82
83 IF l_call_center IS NOT NULL THEN
84 l_call_where_clause := ' AND fact.server_group_id IN (:l_call_center) ';
85 END IF;
86
87 IF l_source_code_id IS NOT NULL THEN
88 l_source_code_where_clause := ' AND fact.campaign_id in (select source_code_for_id from
89 ams_source_codes where source_code_id IN (:l_source_code_id) and arc_source_code_for=''CAMP'' and active_flag=''Y'') ';
90
91 END IF;
92
93 l_where_clause:=l_where_clause||l_call_where_clause||l_source_code_where_clause;
94 --
95 --Two grouping sets - one by O,R,R and one by just O - this is for the
96 --SUBTOTAL. Grouping set will produce a binary number of 0s if the column
97 --is present and binary of 1 if the column is not present.
98 --
99
100 l_sqltext :=
101 ' SELECT
102 DECODE(g_id,3,:l_subtotal,outcome.outcome_code) BIX_PMV_AO_OUTCOME,
103 DECODE(g_id,3,'' '',NVL(result.result_code,:l_unknown)) BIX_PMV_AO_RESULT,
104 DECODE(g_id,3,'' '',NVL(reason.reason_code,:l_unknown )) BIX_PMV_AO_REASON,
105 NVL(curr_count,0) BIX_PMV_AO_COUNT,
106 nvl(sum(decode(g_id,3,curr_count)) over(),0) BIX_PMV_TOTAL1,
107 ROUND(nvl(curr_count * 100/DECODE(sum(decode(g_id,3,curr_count)) over(),0,NULL,sum(decode(g_id,3,curr_count)) over()),0),1) BIX_PMV_AO_PERTOTAL1,
108 ROUND(nvl(curr_count * 100/DECODE(sum(decode(g_id,3,curr_count)) over(),0,NULL,sum(decode(g_id,3,curr_count)) over()),0),1) -
109 ROUND(NVL(prev_count * 100/DECODE(sum(decode(g_id,3,prev_count)) over(),0,NULL,sum(decode(g_id,3,prev_count)) over()),0),1) BIX_PMV_AO_COUNTCHANGE
110 /*,sum( ROUND(NVL(curr_count,0) * 100/DECODE(SUM(curr_count) OVER(),0,NULL,SUM(curr_count) OVER()),1) -
111 ROUND(NVL(prev_count,0) * 100/DECODE(SUM(prev_count) OVER(),0,NULL,SUM(prev_count) OVER()),1) ) over() over() BIX_PMV_TOTAL2
112 */
113 FROM
114 (
115 SELECT outcome_id,
116 result_id,
117 reason_id,
118 grouping_id(outcome_id,result_id,reason_id) g_id,
119 SUM(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,AGENTCALL_ORR_COUNT,NULL)) curr_count,
120 SUM(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,AGENTCALL_ORR_COUNT,NULL)) prev_count
121 FROM bix_ao_call_details_mv fact,
122 fii_time_rpt_struct cal
123 WHERE fact.time_id = cal.time_id
124 AND fact.row_type = :l_row_type
125 AND fact.period_type_id = cal.period_type_id
126 AND fact.outcome_id != :l_outcome_filter
127 AND cal.report_date IN ( &BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
128 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
129 ' || l_where_clause ||
130 ' GROUP BY
131 GROUPING SETS
132 (
133 ( outcome_id,result_id,reason_id),
134 ( outcome_id)
135 )
136 ) fact, jtf_ih_outcomes_vl outcome,jtf_ih_results_vl result,jtf_ih_reasons_vl reason
137 WHERE fact.outcome_id = outcome.outcome_id(+)
138 AND fact.result_id = result.result_id(+)
139 AND fact.reason_id = reason.reason_id(+)
140 order by outcome.outcome_code,g_id,result.result_code,reason.reason_code ';
141 /* Before passing l_sqltext to the calling proc, we trim it up a bit */
142 l_sqltext:=replace(replace(replace(replace(replace(l_sqltext,
143 ' ',' '),' ',' '),' ',' '),' ',' '),' ',' ');
144 p_sql_text := l_sqltext;
145
146
147 -- insert Period Type ID bind variable
148
149 l_custom_rec.attribute_name := ':l_period_type_id' ;
150 l_custom_rec.attribute_value:= 1;
151 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
152 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
153
154 p_custom_output.Extend();
155 p_custom_output(p_custom_output.count) := l_custom_rec;
156
157 l_custom_rec.attribute_name := ':l_outcome_filter' ;
158 l_custom_rec.attribute_value:= l_outcome_filter;
159 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
160 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
161
162 p_custom_output.Extend();
163 p_custom_output(p_custom_output.count) := l_custom_rec;
164
165 l_custom_rec.attribute_name := ':l_row_type' ;
166 l_custom_rec.attribute_value:= l_row_type;
167 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
168 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
169
170 p_custom_output.Extend();
171 p_custom_output(p_custom_output.count) := l_custom_rec;
172
173
174 l_custom_rec.attribute_name := ':l_subtotal' ;
175 l_custom_rec.attribute_value:= l_subtotal;
176 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
177 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
178
179 p_custom_output.Extend();
180 p_custom_output(p_custom_output.count) := l_custom_rec;
181
182
183 l_custom_rec.attribute_name := ':l_unknown' ;
184 l_custom_rec.attribute_value:= l_unknown;
185 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
186 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
187
188 p_custom_output.Extend();
189 p_custom_output(p_custom_output.count) := l_custom_rec;
190
191 IF l_call_center IS NOT NULL
192 THEN
193 l_custom_rec.attribute_name := ':l_call_center';
194 l_custom_rec.attribute_value:= l_call_center;
195 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
196 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
197
198 p_custom_output.Extend();
199 p_custom_output(p_custom_output.count) := l_custom_rec;
200 END IF;
201 IF l_campaign_id IS NOT NULL
202 THEN
203 l_custom_rec.attribute_name := ':l_campaign_id';
204 l_custom_rec.attribute_value:= l_campaign_id;
205 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
206 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
207
208 p_custom_output.Extend();
209 p_custom_output(p_custom_output.count) := l_custom_rec;
210 END IF;
211
212 IF l_schedule_id IS NOT NULL
213 THEN
214 l_custom_rec.attribute_name := ':l_schedule_id';
215 l_custom_rec.attribute_value:= l_schedule_id;
216 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
217 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
218
219 p_custom_output.Extend();
220 p_custom_output(p_custom_output.count) := l_custom_rec;
221 END IF;
222 IF l_source_code_id IS NOT NULL
223 THEN
224 l_custom_rec.attribute_name := ':l_source_code_id';
225 l_custom_rec.attribute_value:= l_source_code_id;
226 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
227 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
228
229 p_custom_output.Extend();
230 p_custom_output(p_custom_output.count) := l_custom_rec;
231 END IF;
232
233 EXCEPTION
234 WHEN OTHERS THEN
235 NULL;
236 END GET_SQL;
237 END BIX_PMV_AO_ORR_RPT_PKG;
238
239
240
241
242