[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_AO_CACAMP_PRTLT_PKG
Source
1 PACKAGE BODY BIX_PMV_AO_CACAMP_PRTLT_PKG AS
2 /*$Header: bixocamp.plb 115.8 2004/04/22 12:47:19 pubalasu 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 l_sqltext VARCHAR2(32000) ;
10 l_as_of_date DATE;
11 l_period_type varchar2(2000);
12 l_record_type_id NUMBER;
13 l_comp_type varchar2(2000);
14 l_sql_errm varchar2(32000);
15 l_previous_report_start_date DATE;
16 l_current_report_start_date DATE;
17 l_previous_as_of_date DATE;
18 l_period_type_id NUMBER;
19 l_time_id_column VARCHAR2(1000);
20 l_goal NUMBER;
21 --added for campaign, schedule and source code
22 l_campaign_id varchar2(3000);
23 l_schedule_id varchar2(3000);
24 l_source_code_id varchar2(3000);
25 l_campaign_where_clause VARCHAR2(3000);
26 l_schedule_where_clause VARCHAR2(3000);
27 l_source_code_where_clause VARCHAR2(3000);
28 l_agent_group varchar2(3000);
29 l_sess_source_where_clause VARCHAR2(3000);
30 l_call_center VARCHAR2(3000);
31 l_other_class VARCHAR2(30);
32 l_call_where_clause VARCHAR2(3000);
33 l_session_where_clause VARCHAR2(3000);
34 l_unknown VARCHAR2(50);
35 l_view_by VARCHAR2(3000);
36
37 l_custom_rec BIS_QUERY_ATTRIBUTES := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
38
39 BEGIN
40 --
41 --Initialize p_custom_output
42 --
43 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
44
45 -- Get the parameters
46
47
48 BIX_PMV_DBI_UTL_PKG.get_ao_page_params( p_page_parameter_tbl,
49 l_as_of_date,
50 l_period_type,
51 l_record_type_id,
52 l_comp_type,
53 l_call_center,
54 l_campaign_id,
55 l_schedule_id,
56 l_source_code_id,
57 l_agent_group,
58 l_view_by
59 );
60
61
62
63 IF l_call_center IS NOT NULL THEN
64 l_call_where_clause := ' AND mv.server_group_id IN (:l_call_center) ';
65 END IF;
66 IF l_campaign_id IS NOT NULL THEN
67 l_campaign_where_clause := ' AND mv.campaign_id IN (:l_campaign_id) ';
68 END IF;
69 IF l_schedule_id IS NOT NULL THEN
70 l_schedule_where_clause := ' AND mv.schedule_id IN (:l_schedule_id) ';
71 END IF;
72 IF l_source_code_id IS NOT NULL THEN
73 l_source_code_where_clause := ' AND mv.campaign_id IN (
74 select source_code_for_id from ams_source_codes where arc_source_code_for=''CAMP''
75 and source_code_id in (:l_source_code_id) and active_flag=''Y'' ) ';
76 END IF;
77
78 l_unknown := FND_MESSAGE.GET_STRING('BIX','BIX_PMV_UNKNOWN');
79
80 IF l_unknown IS NULL OR l_unknown = 'BIX_PMV_UNKNOWN'
81 THEN
82 l_unknown := 'Unknown';
83 END IF;
84
85
86 l_sqltext := '
87 SELECT decode(campmast.value,null,''</a><nobr>''|| :l_unknown ||''</nobr><a href=#>'',campmast.value) VIEWBY,
88 nvl(max(campmast.id),-1) VIEWBYID,
89 sum(hand) BIX_PMV_AO_OUTCALLHAND,
90 sum(sum(hand)) over() BIX_PMV_TOTAL2,
91 round(nvl(sum(ABAND)*100/
92 decode(sum(OFFRD),0,NULL,sum(OFFRD)),0),1) BIX_PMV_AO_ABANRATE,
93 round(sum(nvl(sum(ABAND),0)) over()*100/
94 decode(sum(sum(OFFRD)) over(),0,NULL,sum(sum(OFFRD)) over()),1) BIX_PMV_TOTAL4,
95 round(nvl(sum(USABAND)*100/
96 decode(sum(OFFRD),0,NULL,sum(OFFRD)),0),1) BIX_PMV_AO_US_ABANRATE,
97 round(sum(nvl(sum(USABAND),0)) over()*100/
98 decode(sum(sum(OFFRD)) over(),0,NULL,sum(sum(OFFRD)) over()),1) BIX_PMV_TOTAL11,
99 sum(sr) BIX_PMV_AO_SRCR,
100 sum(sum(sr)) over() BIX_PMV_TOTAL6,
101 sum(lead) BIX_PMV_AO_LECR,
102 sum(sum(lead)) over() BIX_PMV_TOTAL7,
103 sum(opp) BIX_PMV_AO_OPCR,
104 sum(sum(opp)) over() BIX_PMV_TOTAL8,
105 3600*nvl(sum(contacts)/decode(MIN(login),0,null,MIN(login)),0)
106 BIX_PMV_AO_CONTPERHR,
107 3600*sum(sum(contacts)) over()/decode(MIN(login),0,null,MIN(login))
108 BIX_PMV_TOTAL9,
109 nvl(sum(pr),0) BIX_PMV_AO_PORESP,
110 sum(nvl(sum(pr),0)) over() BIX_PMV_TOTAL10
111
112 FROM
113 (
114 /* Add another Inline view */
115 SELECT camp ,sourcecode sourcecode,OFFRD, HAND,ABAND,USABAND,TALK,SR,LEAD,OPP,CONTACTS,PR, SUM(LOGIN) over() LOGIN
116 FROM
117 ( SELECT campaign_id camp,
118 min(source_code_id) sourcecode,
119 sum(decode(dialing_method,''PRED'',CALL_CALLS_OFFERED_TOTAL,0) ) OFFRD,
120 sum(AGENT_CALLS_HANDLED_TOTAL) HAND,
121 sum(decode(dialing_method,''PRED'',CALL_CALLS_ABANDONED,0) ) ABAND,
122 sum(decode(dialing_method,''PRED'',CALL_CALLS_ABANDONED_US,0) ) USABAND,
123 sum(CALL_TALK_TIME) TALK,
124 sum(AGENT_SR_CREATED) SR,
125 sum(AGENT_LEADS_CREATED) LEAD,
126 sum(AGENT_OPPORTUNITIES_CREATED) OPP,
127 sum(AGENTCALL_CONTACT_COUNT) CONTACTS,
128 sum(AGENTCALL_PR_COUNT) PR,
129 0 LOGIN
130 FROM bix_ao_call_details_mv mv,
131 fii_time_rpt_struct cal
132 WHERE mv.time_id = cal.time_id
133 AND mv.row_type = :l_row_type
134 AND mv.period_type_id = cal.period_type_id
135 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) =
136 cal.record_type_id
137 AND cal.report_date = &BIS_CURRENT_ASOF_DATE ';
138 l_sqltext := l_sqltext || l_call_where_clause ||l_source_code_where_clause||
139 '
140 GROUP BY mv.campaign_id
141 UNION ALL
142 SELECT mv.campaign_id camp,
143 min(source_code_id) sourcecode,
144 sum(decode(dialing_method,''PRED'',CALL_CONT_CALLS_HANDLED_TOT_NA,0) ) OFFRD,
145 sum(AGENT_CONT_CALLS_HAND_NA) HAND,
146 0 ABAND,
147 0 USABAND,
148 0 TALK,
149 0 SR,
150 0 LEAD,
151 0 OPP,
152 0 CONTACTS,
153 0 PR,
154 0 LOGIN
155 FROM bix_ao_call_details_mv mv
156 WHERE mv.time_id = to_char(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')
157 AND mv.row_type = :l_row_type
158 AND mv.period_type_id = :l_period_type_id
159
160 ';
161
162 l_sqltext := l_sqltext || l_call_where_clause ||l_source_code_where_clause||
163 '
164 GROUP BY mv.campaign_id
165 UNION ALL
166 SELECT -999 camp,
167 -999 sourcecode,
168 0 OFFRD,
169 0 HAND,
170 0 ABAND,
171 0 USABAND,
172 0 TALK,
173 0 SR,
174 0 LEAD,
175 0 OPP,
176 0 CONTACTS,
177 0 PR,
178 SUM(LOGIN_TIME) LOGIN
179 FROM
180 bix_agent_session_f mv,
181 fii_time_rpt_struct cal
182 WHERE mv.time_id = cal.time_id
183 AND application_id = :l_application_id
184 AND mv.period_type_id = cal.period_type_id
185 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) =
186 cal.record_type_id
187 AND cal.report_date = &BIS_CURRENT_ASOF_DATE ';
188
189 l_sqltext := l_sqltext || l_call_where_clause||
190 ' GROUP BY mv.campaign_id )
191 ) summ
192 ,(
193 select source_code_for_id,camp.id id,camp.value value from bim_dimv_campaigns camp,ams_source_codes scodes
194 where scodes.source_code_id=camp.id and
195 arc_source_code_for=''CAMP'' and active_flag=''Y'' ) campmast
196 where summ.camp=campmast.source_code_for_id(+)
197 and camp<>-999
198 GROUP BY campmast.value
199
200 ';
201 /* Before passing l_sqltext to the calling proc, we trim it up a bit */
202 l_sqltext:=replace(replace(replace(replace(replace(l_sqltext,
203 ' ',' '),' ',' '),' ',' '),' ',' '),' ',' ');
204
205 p_custom_sql := l_sqltext;
206
207 l_custom_rec.attribute_name := ':l_row_type';
208 l_custom_rec.attribute_value:= 'C';
209 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
210 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
211
212 p_custom_output.Extend();
213 p_custom_output(p_custom_output.count) := l_custom_rec;
214
215
216 l_custom_rec.attribute_name := ':l_unknown';
217 l_custom_rec.attribute_value:= l_unknown;
218 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
219 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
220
221 p_custom_output.Extend();
222 p_custom_output(p_custom_output.count) := l_custom_rec;
223
224
225
226 IF l_call_center IS NOT NULL
227 THEN
228 l_custom_rec.attribute_name := ':l_call_center';
229 l_custom_rec.attribute_value:= l_call_center;
230 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
231 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
232
233 p_custom_output.Extend();
234 p_custom_output(p_custom_output.count) := l_custom_rec;
235 END IF;
236
237 IF l_campaign_id IS NOT NULL
238 THEN
239 l_custom_rec.attribute_name := ':l_campaign_id';
240 l_custom_rec.attribute_value:= l_campaign_id;
241 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
242 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
243
244 p_custom_output.Extend();
245 p_custom_output(p_custom_output.count) := l_custom_rec;
246 END IF;
247
248 IF l_schedule_id IS NOT NULL
249 THEN
250 l_custom_rec.attribute_name := ':l_schedule_id';
251 l_custom_rec.attribute_value:= l_schedule_id;
252 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
253 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
254
255 p_custom_output.Extend();
256 p_custom_output(p_custom_output.count) := l_custom_rec;
257 END IF;
258 IF l_source_code_id IS NOT NULL
259 THEN
260 l_custom_rec.attribute_name := ':l_source_code_id';
261 l_custom_rec.attribute_value:= l_source_code_id;
262 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
263 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
264
265 p_custom_output.Extend();
266 p_custom_output(p_custom_output.count) := l_custom_rec;
267 END IF;
268
269 l_custom_rec.attribute_name := ':l_period_type_id';
270 l_custom_rec.attribute_value:= 1;
271 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
272 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
273
274 p_custom_output.Extend();
275 p_custom_output(p_custom_output.count) := l_custom_rec;
276
277 l_custom_rec.attribute_name := ':l_unknown';
278 l_custom_rec.attribute_value:= l_unknown;
279 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
280 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
281
282 p_custom_output.EXTEND;
283 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
284
285 l_custom_rec.attribute_name := ':l_application_id';
286 l_custom_rec.attribute_value := 696;
287 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
288 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
289
290 p_custom_output.EXTEND;
291 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
292
293 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
294 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
295 l_custom_rec.attribute_value := 'CAMPAIGN+CAMPAIGN';
296
297 p_custom_output.EXTEND;
298 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
299
300 EXCEPTION
301 WHEN OTHERS THEN
302 NULL;
303 END GET_SQL;
304 END BIX_PMV_AO_CACAMP_PRTLT_PKG;