[Home] [Help]
PACKAGE BODY: APPS.BIM_SGMT_INTL_UI_PVT
Source
1 PACKAGE BODY BIM_SGMT_INTL_UI_PVT AS
2 /* $Header: bimsiuib.pls 120.31 2006/03/02 22:28:05 sbassi noship $ */
3
4
5 FUNCTION GLb( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL , num in number)
6 RETURN VARCHAR2 IS
7
8 l_val VARCHAR2(50);
9 Label1 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('BOOK_C');
10 Label2 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('OPPT_C');
11 Label3 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_C');
12 Label4 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('RESP_C');
13 Label5 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('ACT_C');
14 Label6 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('BOOK_G');
15 Label7 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('OPPT_G');
16 Label8 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('LEAD_G');
17 Label9 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('RESP_G');
18 Label10 CONSTANT varchar2(100):=BIM_PMV_DBI_UTL_PKG.GET_LOOKUP_VALUE('ACT_G');
19 BEGIN
20
21 IF (p_page_parameter_tbl.count > 0) THEN
22
23 FOR i IN p_page_parameter_tbl.FIRST..p_page_parameter_tbl.LAST
24 LOOP
25
26 IF p_page_parameter_tbl(i).parameter_name ='BIM_PARAMETER1' THEN
27
28 l_val := p_page_parameter_tbl(i).parameter_value;
29 EXIT;
30
31 END IF;
32
33 END LOOP;
34
35 END IF;
36
37 IF num = 1 THEN
38
39 ---the fuction is called for column name
40
41 CASE
42 WHEN l_val = 'ACT' THEN
43 RETURN Label5 ;
44 WHEN l_val = 'RESP' THEN
45 RETURN Label4 ;
46 WHEN l_val = 'LEAD' THEN
47 RETURN Label3 ;
48 WHEN l_val = 'OPPT' THEN
49 RETURN Label2 ;
50 WHEN l_val = 'BOOK' THEN
51 RETURN Label1 ;
52 END CASE;
53
54 ELSIF num = 2 THEN
55
56 ---the fuction is called for Graph Title
57 CASE
58 WHEN l_val = 'ACT' THEN
59 RETURN Label10 ;
60 WHEN l_val = 'RESP' THEN
61 RETURN Label9 ;
62 WHEN l_val = 'LEAD' THEN
63 RETURN Label8 ;
64 WHEN l_val = 'OPPT' THEN
65 RETURN Label7 ;
66 WHEN l_val = 'BOOK' THEN
67 RETURN Label6 ;
68 END CASE;
69
70 END IF;
71
72 EXCEPTION
73 WHEN OTHERS THEN
74 RETURN NULL;
75 END GLb;
76
77
78
79 FUNCTION GET_RESOURCE_ID return NUMBER IS
80 l_resource_id NUMBER := NULL;
81 CURSOR c_rid IS
82 SELECT resource_id
83 FROM JTF_RS_RESOURCE_EXTNS
84 WHERE user_id = FND_GLOBAL.user_id;
85 BEGIN
86 OPEN c_rid;
87 FETCH c_rid INTO l_resource_id;
88 CLOSE c_rid;
89 if (l_resource_id=null) then
90 l_resource_id := -1;
91 end if;
92 if (l_resource_id='') then
93 l_resource_id := -1;
94 end if;
95
96 return l_resource_id;
97 END GET_RESOURCE_ID;
98
99 FUNCTION GET_ADMIN_STATUS return VARCHAR2 IS
100 l_admin_count NUMBER := 0;
101 l_admin_flag varchar2(20) ;
102 CURSOR c_rid IS
103 SELECT count(*)
104 FROM JTF_RS_RESOURCE_EXTNS r, bim_i_admin_group a
105 WHERE user_id = FND_GLOBAL.user_id
106 AND r.resource_id = a.resource_id;
107
108 BEGIN
109 If GET_RESOURCE_ID is null
110 then
111 l_admin_flag := 'Y';
112
113 else
114
115 OPEN c_rid;
116 FETCH c_rid INTO l_admin_count;
117 CLOSE c_rid;
118 if (l_admin_count<=0)
119 then
120 l_admin_flag := 'N';
121 else
122 l_admin_flag := 'Y';
123 end if;
124 end if;
125 return l_admin_flag;
126 END GET_ADMIN_STATUS;
127
128 PROCEDURE GET_SGMT_VALUE_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
129 x_custom_sql OUT NOCOPY VARCHAR2,
130 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
131 IS
132 l_sqltext VARCHAR2 (20000);
133 l_as_of_date DATE;
134 l_period_type VARCHAR2 (2000);
135 l_record_type_id NUMBER;
136 l_cat_id VARCHAR2 (50) ;
137 l_sgmt_id VARCHAR2 (50) ;
138 l_curr VARCHAR2(50);
139 l_view_by VARCHAR2 (4000);
140 l_where VARCHAR2 (2000);
141 l_groupby VARCHAR2 (200);
142 l_custom_rec BIS_QUERY_ATTRIBUTES;
143 l_active_cust_col VARCHAR2(30);
144 l_start_date VARCHAR2(30);
145 l_curr_suffix VARCHAR2(2);
146 l_url_metric VARCHAR2(10);
147 l_url_viewby VARCHAR2(100);
148 l_url_viewbyid VARCHAR2(100);
149 l_view_by_id VARCHAR2(50);
150 l_url_str VARCHAR2(1000);
151 l_url_str_sgmt_jtf VARCHAR2(1000);
152 l_leaf_node_flag VARCHAR2(1);
153 l_from VARCHAR2(200);
154 l_col VARCHAR2(200);
155
156 BEGIN
157 x_custom_output := bis_query_attributes_tbl ();
158 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
159 bim_pmv_dbi_utl_pkg.get_bim_page_sgmt_params(p_page_parameter_tbl,
160 l_as_of_date ,
161 l_period_type,
162 l_record_type_id,
163 l_view_by,
164 l_cat_id ,
165 l_sgmt_id,
166 l_curr,
167 l_url_metric,
168 l_url_viewby,
169 l_url_viewbyid
170 ) ;
171
172 IF l_sgmt_id is null THEN
173 --i.e The value in Segment Lov is NULL , then show only Parent Segments
174
175 l_from := ' , bim_i_sgmt_denorm dn ' ;
176 l_where := ' AND dn.immediate_parent_id IS NULL AND a.segment_id=dn.segment_id ';
177 l_groupby := ' , dn.leaf_node_flag ' ;
178 l_col := ' , dn.leaf_node_flag leaf_node_flag ' ;
179
180 ELSE
181 -- two cases here, if the segment is a parent segment , then show the children underneath
182 -- else show that segment only
183 BEGIN
184
185 SELECT leaf_node_flag
186 INTO l_leaf_node_flag
187 FROM bim_i_sgmt_denorm
188 WHERE segment_id = replace(l_sgmt_id,'''',null)
189 AND segment_id = parent_segment_id;
190
191 EXCEPTION
192 WHEN NO_DATA_FOUND THEN
193 NULL;
194 END;
195
196 IF l_leaf_node_flag = 'Y' THEN
197 --Show the segment itself
198
199 l_where := ' AND a.segment_id = :l_sgmt_id ';
200 l_col := ' , '''||l_leaf_node_flag||''' leaf_node_flag ' ;
201
202 ELSE
203 --Show only immediate Children
204
205 l_from := ' , bim_i_sgmt_denorm dn ' ;
206 l_where := ' AND dn.immediate_parent_id=:l_sgmt_id AND dn.immediate_parent_flag = ''Y''
207 AND a.segment_id=dn.segment_id ';
208
209 l_groupby := ' , dn.leaf_node_flag ' ;
210 l_col := ' , dn.leaf_node_flag leaf_node_flag ' ;
211
212 END IF;
213
214 END IF;
215
216 l_view_by_id:='VIEWBYID';
217
218 IF (l_curr = '''FII_GLOBAL1''') THEN
219 l_curr_suffix := '';
220 ELSIF (l_curr = '''FII_GLOBAL2''') THEN
221 l_curr_suffix := '_s';
222 ELSE
223 l_curr_suffix := '';
224 END IF;
225
226 CASE l_period_type
227 WHEN 'FII_TIME_WEEK' THEN
228 l_active_cust_col := 'cust_count_week';
229 l_start_date:='week_start_date';
230 WHEN 'FII_TIME_ENT_PERIOD' THEN
231 l_active_cust_col := 'cust_count_month';
232 l_start_date:='ent_period_start_date';
233 WHEN 'FII_TIME_ENT_QTR' THEN
234 l_active_cust_col := 'cust_count_qtr';
235 l_start_date:='ent_qtr_start_date';
236 WHEN 'FII_TIME_ENT_YEAR' THEN
237 l_active_cust_col := 'cust_count_year';
238 l_start_date:='ent_year_start_date';
239 ELSE
240 l_active_cust_col := 'cust_count_week';
241 l_start_date:='week_start_date';
242 END CASE;
243
244 l_url_str :='''pFunctionName=BIM_I_SGMT_VAL_R&pParamIds=Y&VIEW_BY='||l_view_by||'&VIEW_BY_NAME=VIEW_BY_ID''';
245
246 --l_url_str_sgmt_jtf :='''pFunctionName=BIM_I_SGMT_DRILL&PAGE.OBJ.objType=CELL&PAGE.OBJ.objAttribute=HIER&PAGE.OBJ.ID_NAME0=treeSearchId&PAGE.OBJ.treeSearchFlag=Y&PAGE.OBJ.ID0=VIEWBYID&treeSearchId=VIEWBYID''';
247 l_url_str_sgmt_jtf := 'NULL';
248
249 l_sqltext:='
250 SELECT
251 VIEWBY,
252 VIEWBYID,
253 total_customers BIM_ATTRIBUTE2,
254 CASE WHEN prev_total_customers = 0 THEN NULL ELSE
255 ((total_customers-prev_total_customers)/prev_total_customers)*100 end BIM_ATTRIBUTE3,
256 active_customers BIM_ATTRIBUTE4,
257 CASE WHEN prev_active_customers=0 THEN NULL ELSE ((active_customers-prev_active_customers)/prev_active_customers)*100 end BIM_ATTRIBUTE5,
258 CASE WHEN total_customers = 0 THEN NULL ELSE
259 ((active_customers)/total_customers)*100 END BIM_ATTRIBUTE6,
260 total_customers - active_customers BIM_ATTRIBUTE7,
261 revenue BIM_ATTRIBUTE8,
262 CASE WHEN prev_revenue = 0 THEN NULL ELSE ((revenue-prev_revenue)/prev_revenue)*100 END BIM_ATTRIBUTE9,
263 CASE WHEN active_customers = 0 THEN NULL ELSE revenue/active_customers END BIM_ATTRIBUTE10,
264 CASE WHEN booked_count = 0 THEN NULL ELSE booked_amt/booked_count END BIM_ATTRIBUTE11,
265 CASE WHEN (prev_booked_count=0 OR prev_booked_amt=0 OR booked_count=0) THEN NULL ELSE (((booked_amt/booked_count)-(prev_booked_amt/prev_booked_count))/ (prev_booked_amt/prev_booked_count))*100
266 end BIM_ATTRIBUTE12,
267 segment_size BIM_ATTRIBUTE13,
268 CASE WHEN prev_segment_size=0 THEN NULL ELSE ((segment_size-prev_segment_size)/prev_segment_size)*100 END BIM_ATTRIBUTE14,
269 CASE WHEN prev_active_customers=0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_ACTIVE_CUST_T&pParamIds=Y&BIM_DIM11='||l_view_by_id||''' END BIM_URL1,
270 CASE WHEN prev_revenue = 0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_REV_T&pParamIds=Y&BIM_DIM11='||l_view_by_id||''' END BIM_URL2,
271 CASE WHEN (prev_booked_count=0 OR prev_booked_amt=0 OR booked_count=0) THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_AVG_TXN_VAL_T&pParamIds=Y&BIM_DIM11='||l_view_by_id||''' END BIM_URL3,
272 CASE WHEN prev_segment_size = 0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_SIZE_T&pParamIds=Y&BIM_DIM11='||l_view_by_id||''' END BIM_URL4 ,
273 decode( leaf_node_flag ,''Y'','||l_url_str_sgmt_jtf||', '||l_url_str||') BIM_URL5
274 FROM
275 (
276 SELECT
277 b.cell_name VIEWBY,
278 VIEWBYID,
279 a.leaf_node_flag leaf_node_flag,
280 SUM(revenue) revenue,
281 SUM(prev_revenue) prev_revenue,
282 SUM(total_customers ) total_customers,
283 SUM(prev_total_customers ) prev_total_customers,
284 SUM(booked_amt) booked_amt,
285 SUM(prev_booked_amt) prev_booked_amt,
286 SUM(booked_count) booked_count,
287 SUM(prev_booked_count) prev_booked_count,
288 SUM(segment_size) segment_size,
289 SUM(prev_segment_size) prev_segment_size,
290 SUM(active_customers) active_customers,
291 SUM(prev_active_customers) prev_active_customers
292 FROM
293 (
294 SELECT
295 a.segment_id viewbyid
296 '||l_col||' ,
297 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.revenue'||l_curr_suffix||',0)) revenue ,
298 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.revenue'||l_curr_suffix||',0)) prev_revenue,
299 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.booked_amt'||l_curr_suffix||',0)) booked_amt,
300 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.booked_amt'||l_curr_suffix||',0)) prev_booked_amt,
301 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.booked_count,0)) booked_count,
302 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.booked_count,0)) prev_booked_count,
303 0 total_customers,
304 0 prev_total_customers,
305 0 segment_size,
306 0 prev_segment_size,
307 0 active_customers,
308 0 prev_active_customers
309 FROM bim_sgmt_val_f_mv a,fii_time_rpt_struct_v cal '||l_from||'
310 WHERE a.time_id = cal.time_id
311 AND a.period_type_id = cal.period_type_id
312 AND BITAND(cal.record_type_id,:l_record_type)= cal.record_type_id
313 AND cal.calendar_id= -1
314 '||l_where
318 SELECT
315 ||' AND cal.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
316 GROUP BY a.segment_id '||l_groupby||'
317 UNION ALL
319 a.segment_id viewbyid
320 '||l_col||' ,
321 0 revenue ,
322 0 prev_revenue,
323 0 booked_amt,
324 0 prev_booked_amt,
325 0 booked_count,
326 0 prev_booked_count,
327 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.total_customers,0)) total_customers,
328 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.total_customers,0)) prev_total_customers,
329 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.segment_size,0)) segment_size,
330 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.segment_size,0)) prev_segment_size,
331 0 active_customers,
332 0 prev_active_customers
333 FROM bim_sgmt_val_f_mv a,fii_time_rpt_struct_v cal '||l_from||'
334 WHERE a.time_id = cal.time_id
335 AND a.period_type_id = cal.period_type_id
336 AND BITAND(cal.record_type_id,1143)= cal.record_type_id
337 AND cal.calendar_id= -1
338 '||l_where
339 ||' AND cal.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
340 GROUP BY a.segment_id '||l_groupby||'
341 UNION ALL
342 SELECT
343 a.segment_id viewbyid
344 '||l_col||' ,
345 0 revenue ,
346 0 prev_revenue,
347 0 booked_amt,
348 0 prev_booked_amt,
349 0 booked_count,
350 0 prev_booked_count,
351 0 total_customers,
352 0 prev_total_customers,
353 0 segment_size,
354 0 prev_segment_size,
355 SUM('||l_active_cust_col||') active_customers,
356 0 prev_active_customers
357 FROM bim_i_sgmt_facts a,fii_time_day cal '||l_from||'
358 WHERE a.transaction_create_date between cal.'||l_start_date||' and &BIS_CURRENT_ASOF_DATE
359 '||l_where
360 ||' AND cal.report_date =&BIS_CURRENT_ASOF_DATE
361 AND a.metric_type=''CUST''
362 GROUP BY a.segment_id '||l_groupby||'
363 UNION ALL
364 --Select to capture Previous Active Customer
365 SELECT
366 a.segment_id viewbyid
367 '||l_col||' ,
368 0 revenue ,
369 0 prev_revenue,
370 0 booked_amt,
371 0 prev_booked_amt,
372 0 booked_count,
373 0 prev_booked_count,
374 0 total_customers,
375 0 prev_total_customers,
376 0 segment_size,
377 0 prev_segment_size,
378 0 active_customers,
379 SUM('||l_active_cust_col||') prev_active_customers
380 FROM bim_i_sgmt_facts a,fii_time_day cal '||l_from||'
381 WHERE a.transaction_create_date between cal.'||l_start_date||' and &BIS_PREVIOUS_ASOF_DATE
382 '||l_where
383 ||' AND cal.report_date = &BIS_PREVIOUS_ASOF_DATE
384 AND a.metric_type=''CUST''
385 GROUP BY a.segment_id '||l_groupby||' ) a,ams_cells_all_tl b
386 WHERE a.viewbyid=b.cell_id
387 and b.language=userenv(''LANG'')
388 group by viewbyid,b.cell_name,a.leaf_node_flag )
389 &ORDER_BY_CLAUSE';
390 x_custom_sql := l_sqltext;
391
392 l_custom_rec.attribute_name := ':l_record_type';
393 l_custom_rec.attribute_value := l_record_type_id;
394 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
395 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
396 x_custom_output.EXTEND;
397 x_custom_output (1) := l_custom_rec;
398 l_custom_rec.attribute_name := ':l_sgmt_id';
399 l_custom_rec.attribute_value := l_sgmt_id;
400 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
401 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
402 x_custom_output.EXTEND;
403 x_custom_output (2) := l_custom_rec;
404 END;
405
406
407 PROCEDURE GET_SGMT_REVENUE_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
408 x_custom_sql OUT NOCOPY VARCHAR2,
409 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
410 IS
411 l_sqltext VARCHAR2 (20000);
412 iflag NUMBER;
413 l_period_type_hc NUMBER;
414 l_as_of_date DATE;
415 l_period_type VARCHAR2 (2000);
416 l_record_type_id NUMBER;
417 l_cat_id VARCHAR2 (50) ;
418 l_sgmt_id VARCHAR2 (50) ;
419 l_curr VARCHAR2(50);
420 l_view_by VARCHAR2 (4000);
421 l_select_filter VARCHAR2 (20000); -- to build select filter part
422 l_where VARCHAR2 (20000); -- static where clause
423 l_groupby VARCHAR2 (2000); -- to build group by clause
424 l_pc_from VARCHAR2 (20000); -- from clause to handle product category
425 l_pc_where VARCHAR2 (20000); -- where clause to handle product category
426 l_custom_rec BIS_QUERY_ATTRIBUTES;
427 l_object_type VARCHAR2(30);
428 l_url_link VARCHAR2(200);
429 l_url_camp1 VARCHAR2(3000);
430 l_url_camp2 VARCHAR2(3000);
431 l_start_date VARCHAR2(30);
432 l_curr_suffix VARCHAR2(2);
433 l_url_metric VARCHAR2(10);
434 l_url_viewby VARCHAR2(100);
435 l_url_viewbyid VARCHAR2(100);
436 l_from VARCHAR2(100);
437
438 BEGIN
439 x_custom_output := bis_query_attributes_tbl ();
440 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
441 bim_pmv_dbi_utl_pkg.get_bim_page_sgmt_params(p_page_parameter_tbl,
442 l_as_of_date ,
443 l_period_type,
444 l_record_type_id,
445 l_view_by,
446 l_cat_id ,
447 l_sgmt_id,
448 l_curr ,
449 l_url_metric,
450 l_url_viewby,
451 l_url_viewbyid
452 ) ;
453 IF l_sgmt_id is null THEN
454 l_where := 'AND dn.immediate_parent_id is null AND dn.segment_id = a.segment_id';
455 l_from := l_from||' , bim_i_sgmt_denorm dn ';
456 ELSE
457 l_where := 'AND a.segment_id=:l_sgmt_id ';
458
459 END IF;
460
461 IF (l_curr = '''FII_GLOBAL1''')
462 THEN l_curr_suffix := '';
463 ELSIF (l_curr = '''FII_GLOBAL2''')
464 THEN l_curr_suffix := '_s';
465 ELSE l_curr_suffix := '';
466 END IF;
467
468 l_sqltext:='
469 select
470 fii.name VIEWBY,
471 revenue bim_attribute2,
472 case when revenue_p =0 then null else ((revenue-revenue_p)/revenue_p)*100 end bim_attribute3
473 FROM
474 ( SELECT dates.start_date start_date,
475 sum(decode(dates.period, ''C'',a.revenue'||l_curr_suffix||',0)) revenue,
476 sum(decode(dates.period, ''P'',a.revenue'||l_curr_suffix||',0)) revenue_p
477 FROM
478 (
479 SELECT
480 fii.start_date START_DATE,
481 ''C'' PERIOD,
482 least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
483 FROM '||l_period_type||' fii
484 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
485 UNION ALL
486 SELECT
487 p2.start_date START_DATE,
488 ''P'' PERIOD,
489 p1.report_date REPORT_DATE
490 FROM
491 (
492 SELECT report_date , rownum id
493 FROM
494 (
495 SELECT
496 least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE
497 FROM ' ||l_period_type||' fii
498 WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND &BIS_PREVIOUS_ASOF_DATE
499 ORDER BY fii.start_date DESC
500 )
501 ) p1,
502 (
503 SELECT start_date , rownum id
504 FROM
505 (
506 SELECT
507 fii.start_date START_DATE
508 FROM ' ||l_period_type||' fii
509 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
510 ORDER BY fii.start_date DESC
511 )
512 ) p2
513 WHERE p1.id(+) = p2.id) dates, bim_sgmt_val_f_mv a,fii_time_rpt_struct_v cal '||l_from||'
514 WHERE cal.report_date = dates.report_date
515 AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id
516 AND a.time_id = cal.time_id
517 AND a.period_type_id = cal.period_type_id '||l_where||' group by dates.start_date )
518 s,'|| l_period_type||' fii
519 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE AND
520 fii.start_date = s.start_date(+)
521 order by fii.start_date ';
522
523
524
525 x_custom_sql := l_sqltext;
526 l_custom_rec.attribute_name := ':l_record_type';
527 l_custom_rec.attribute_value := l_record_type_id;
528 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
529 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
530 x_custom_output.EXTEND;
531 x_custom_output (1) := l_custom_rec;
532 l_custom_rec.attribute_name := ':l_sgmt_id';
533 l_custom_rec.attribute_value := l_sgmt_id;
534 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
535 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
536 x_custom_output.EXTEND;
537 x_custom_output (2) := l_custom_rec;
538 END GET_SGMT_REVENUE_SQL;
539
540 PROCEDURE GET_ACTIVE_CUST_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
541 x_custom_sql OUT NOCOPY VARCHAR2,
542 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
543 IS
544 l_sqltext VARCHAR2 (20000);
545 iflag NUMBER;
546 l_period_type_hc NUMBER;
547 l_as_of_date DATE;
548 l_period_type VARCHAR2 (2000);
549 l_record_type_id NUMBER;
550 l_cat_id VARCHAR2 (50) ;
551 l_sgmt_id VARCHAR2 (50) ;
552 l_curr VARCHAR2(50);
553 l_view_by VARCHAR2 (4000);
554 l_select_filter VARCHAR2 (20000); -- to build select filter part
555 l_where VARCHAR2 (20000); -- static where clause
556 l_groupby VARCHAR2 (2000); -- to build group by clause
560 l_object_type VARCHAR2(30);
557 l_pc_from VARCHAR2 (20000); -- from clause to handle product category
558 l_pc_where VARCHAR2 (20000); -- where clause to handle product category
559 l_custom_rec BIS_QUERY_ATTRIBUTES;
561 l_url_link VARCHAR2(200);
562 l_url_camp1 VARCHAR2(3000);
563 l_url_camp2 VARCHAR2(3000);
564 l_active_cust_col VARCHAR2(30);
565 l_start_date VARCHAR2(30);
566 l_curr_suffix VARCHAR2(2);
567 l_url_metric VARCHAR2(10);
568 l_url_viewby VARCHAR2(100);
569 l_url_viewbyid VARCHAR2(100);
570 l_from VARCHAR2(100);
571
572 BEGIN
573 x_custom_output := bis_query_attributes_tbl ();
574 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
575 bim_pmv_dbi_utl_pkg.get_bim_page_sgmt_params(p_page_parameter_tbl,
576 l_as_of_date ,
577 l_period_type,
578 l_record_type_id,
579 l_view_by,
580 l_cat_id ,
581 l_sgmt_id,
582 l_curr ,
583 l_url_metric,
584 l_url_viewby,
585 l_url_viewbyid
586 ) ;
587 IF l_sgmt_id IS NULL THEN
588
589 l_where := 'AND dn.immediate_parent_id is null AND dn.segment_id = a.segment_id';
590 l_from := l_from||' , bim_i_sgmt_denorm dn ';
591 ELSE
592
593 l_where := 'AND a.segment_id=:l_sgmt_id';
594
595 END IF;
596
597
598 CASE l_period_type
599 WHEN 'FII_TIME_WEEK' THEN
600 l_active_cust_col := 'cust_count_week';
601 l_start_date:='week_start_date';
602 WHEN 'FII_TIME_ENT_PERIOD' THEN
603 l_active_cust_col := 'cust_count_month';
604 l_start_date:='ent_period_start_date';
605 WHEN 'FII_TIME_ENT_QTR' THEN
606 l_active_cust_col := 'cust_count_qtr';
607 l_start_date:='ent_qtr_start_date';
608 WHEN 'FII_TIME_ENT_YEAR' THEN
609 l_active_cust_col := 'cust_count_year';
610 l_start_date:='ent_year_start_date';
611 ELSE l_active_cust_col := 'cust_count_week';
612 l_start_date:='week_start_date';
613 END CASE;
614
615 l_sqltext:='
616 select
617 fii.name VIEWBY,
618 active_customer bim_attribute2,
619 case when active_customer_p =0 then null else (( active_customer- active_customer_p)/active_customer_p)*100 end bim_attribute3
620 FROM
621 ( SELECT dates.start_date start_date,
622 sum(decode(dates.period, ''C'','||l_active_cust_col||',0)) active_customer,
623 sum(decode(dates.period, ''P'','||l_active_cust_col||',0)) active_customer_p
624 FROM
625 (
626 SELECT
627 fii.start_date START_DATE,
628 ''C'' PERIOD,
629 least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
630 FROM '||l_period_type||' fii
631 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
632 UNION ALL
633 SELECT
634 p2.start_date START_DATE,
635 ''P'' PERIOD,
636 p1.report_date REPORT_DATE
637 FROM
638 (
639 SELECT report_date, rownum id
640 FROM
641 (
642 SELECT
643 least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE
644 FROM ' ||l_period_type||' fii
645 WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND &BIS_PREVIOUS_ASOF_DATE
646 ORDER BY fii.start_date DESC
647 )
648 ) p1,
649 (
650 SELECT start_date, rownum id
651 FROM
652 (
653 SELECT
654 fii.start_date START_DATE
655 FROM ' ||l_period_type||' fii
656 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
657 ORDER BY fii.start_date DESC
658 )
659 ) p2
660 WHERE p1.id(+) = p2.id) dates, bim_i_sgmt_facts a,fii_time_day cal '||l_from||'
661 WHERE cal.report_date = dates.report_date
662 AND a.transaction_create_date between cal.'||l_start_date||' and cal.report_date
663 '||l_where||' group by dates.start_date )
664 s,'|| l_period_type||' fii
665 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE AND
666 fii.start_date = s.start_date(+)
667 order by fii.start_date ';
668 x_custom_sql := l_sqltext;
669 l_custom_rec.attribute_name := ':l_record_type';
670 l_custom_rec.attribute_value := l_record_type_id;
671 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
672 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
673 x_custom_output.EXTEND;
674 x_custom_output (1) := l_custom_rec;
675 l_custom_rec.attribute_name := ':l_sgmt_id';
676 l_custom_rec.attribute_value := l_sgmt_id;
677 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
678 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
679 x_custom_output.EXTEND;
680 x_custom_output (2) := l_custom_rec;
681 END GET_ACTIVE_CUST_SQL;
682
683
684
685 PROCEDURE GET_SGMT_SIZE_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
686 x_custom_sql OUT NOCOPY VARCHAR2,
687 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
688 IS
689 l_sqltext VARCHAR2 (20000);
690 iflag NUMBER;
691 l_period_type_hc NUMBER;
692 l_as_of_date DATE;
693 l_period_type VARCHAR2 (2000);
694 l_record_type_id NUMBER;
695 l_cat_id VARCHAR2 (50) ;
696 l_sgmt_id VARCHAR2 (50) ;
697 l_curr VARCHAR2(50);
698 l_view_by VARCHAR2 (4000);
702 l_pc_from VARCHAR2 (20000); -- from clause to handle product category
699 l_select_filter VARCHAR2 (20000); -- to build select filter part
700 l_where VARCHAR2 (20000); -- static where clause
701 l_groupby VARCHAR2 (2000); -- to build group by clause
703 l_pc_where VARCHAR2 (20000); -- where clause to handle product category
704 l_custom_rec BIS_QUERY_ATTRIBUTES;
705 l_object_type VARCHAR2(30);
706 l_url_link VARCHAR2(200);
707 l_url_camp1 VARCHAR2(3000);
708 l_url_camp2 VARCHAR2(3000);
709 l_start_date VARCHAR2(30);
710 l_curr_suffix VARCHAR2(2);
711 l_url_metric VARCHAR2(10);
712 l_url_viewby VARCHAR2(100);
713 l_url_viewbyid VARCHAR2(100);
714 l_from VARCHAR2(100);
715
716 BEGIN
717 x_custom_output := bis_query_attributes_tbl ();
718 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
719 bim_pmv_dbi_utl_pkg.get_bim_page_sgmt_params(p_page_parameter_tbl,
720 l_as_of_date ,
721 l_period_type,
722 l_record_type_id,
723 l_view_by,
724 l_cat_id ,
725 l_sgmt_id,
726 l_curr ,
727 l_url_metric,
728 l_url_viewby,
729 l_url_viewbyid
730 ) ;
731 IF l_sgmt_id is null THEN
732 l_where := 'AND dn.immediate_parent_id is null AND dn.segment_id = a.segment_id';
733 l_from := l_from||' , bim_i_sgmt_denorm dn ';
734 ELSE
735 l_where := 'AND a.segment_id=:l_sgmt_id';
736
737 END IF;
738
739 l_sqltext:='
740 select
741 fii.name VIEWBY,
742 seg_size bim_attribute2,
743 case when seg_size_p =0 then null else ((seg_size-seg_size_p)/seg_size_p)*100 end bim_attribute3
744 FROM
745 ( SELECT dates.start_date start_date,
746 sum(decode(dates.period, ''C'',a.segment_size,0)) seg_size,
747 sum(decode(dates.period, ''P'',a.segment_size,0)) seg_size_p
748 FROM
749 (
750 SELECT
751 fii.start_date START_DATE,
752 ''C'' PERIOD,
753 least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
754 FROM '||l_period_type||' fii
755 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
756 UNION ALL
757 SELECT
758 p2.start_date START_DATE,
759 ''P'' PERIOD,
760 p1.report_date REPORT_DATE
761 FROM
762 (
763 SELECT report_date , ROWNUM id
764 FROM
765 (
766 SELECT
767 least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE
768 FROM ' ||l_period_type||' fii
769 WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND &BIS_PREVIOUS_ASOF_DATE
770 ORDER BY fii.start_date DESC
771 )
772 ) p1,
773 (
774 SELECT start_date , ROWNUM id
775 FROM
776 (
777 SELECT fii.start_date START_DATE,
778 rownum ID
779 FROM ' ||l_period_type||' fii
780 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
781 ORDER BY fii.start_date DESC
782 )
783 ) p2
784 WHERE p1.id(+) = p2.id) dates, bim_sgmt_val_f_mv a,fii_time_rpt_struct_v cal '||l_from||'
785 WHERE cal.report_date = dates.report_date
786 AND bitand(cal.record_type_id,1143) = cal.record_type_id
787 AND a.time_id = cal.time_id
788 AND a.period_type_id = cal.period_type_id '||l_where||' group by dates.start_date )
789 s,'|| l_period_type||' fii
790 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE AND
791 fii.start_date = s.start_date(+)
792 order by fii.start_date ';
793 x_custom_sql := l_sqltext;
794 l_custom_rec.attribute_name := ':l_record_type';
795 l_custom_rec.attribute_value := l_record_type_id;
796 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
797 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
798 x_custom_output.EXTEND;
799 x_custom_output (1) := l_custom_rec;
800 l_custom_rec.attribute_name := ':l_sgmt_id';
801 l_custom_rec.attribute_value := l_sgmt_id;
802 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
803 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
804 x_custom_output.EXTEND;
805 x_custom_output (2) := l_custom_rec;
806 END GET_SGMT_SIZE_SQL;
807
808 PROCEDURE GET_SGMT_AVG_TXN_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
809 x_custom_sql OUT NOCOPY VARCHAR2,
810 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
811 IS
812 l_sqltext VARCHAR2 (20000);
813 iflag NUMBER;
814 l_period_type_hc NUMBER;
815 l_as_of_date DATE;
816 l_period_type VARCHAR2 (2000);
817 l_record_type_id NUMBER;
818 l_cat_id VARCHAR2 (50) ;
819 l_sgmt_id VARCHAR2 (50) ;
820 l_curr VARCHAR2(50);
821 l_view_by VARCHAR2 (4000);
822 l_select_filter VARCHAR2 (20000); -- to build select filter part
823 l_where VARCHAR2 (20000); -- static where clause
824 l_groupby VARCHAR2 (2000); -- to build group by clause
825 l_pc_from VARCHAR2 (20000); -- from clause to handle product category
826 l_pc_where VARCHAR2 (20000); -- where clause to handle product category
827 l_custom_rec BIS_QUERY_ATTRIBUTES;
828 l_object_type VARCHAR2(30);
829 l_url_link VARCHAR2(200);
833 l_curr_suffix VARCHAR2(2);
830 l_url_camp1 VARCHAR2(3000);
831 l_url_camp2 VARCHAR2(3000);
832 l_start_date VARCHAR2(30);
834 l_url_metric VARCHAR2(10);
835 l_url_viewby VARCHAR2(100);
836 l_url_viewbyid VARCHAR2(100);
837 l_from VARCHAR2(100);
838
839 BEGIN
840 x_custom_output := bis_query_attributes_tbl ();
841 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
842 bim_pmv_dbi_utl_pkg.get_bim_page_sgmt_params(p_page_parameter_tbl,
843 l_as_of_date ,
844 l_period_type,
845 l_record_type_id,
846 l_view_by,
847 l_cat_id ,
848 l_sgmt_id,
849 l_curr ,
850 l_url_metric,
851 l_url_viewby,
852 l_url_viewbyid
853 ) ;
854
855 IF l_sgmt_id is null THEN
856 l_where := 'AND dn.immediate_parent_id is null AND dn.segment_id = a.segment_id';
857 l_from := l_from||' , bim_i_sgmt_denorm dn ';
858 ELSE
859 l_where := 'AND a.segment_id=:l_sgmt_id';
860
861 END IF;
862
863 IF (l_curr = '''FII_GLOBAL1''')
864 THEN l_curr_suffix := '';
865 ELSIF (l_curr = '''FII_GLOBAL2''')
866 THEN l_curr_suffix := '_s';
867 ELSE l_curr_suffix := '';
868 END IF;
869
870 l_sqltext:='
871 select
872 fii.name VIEWBY,
873 case when booked_count =0 then null else (booked_amt/booked_count) end bim_attribute2,
874 case when (prev_booked_count=0 OR prev_booked_amt=0 OR booked_count=0) then null else (((booked_amt/booked_count)-(prev_booked_amt/prev_booked_count))/ (prev_booked_amt/prev_booked_count))*100
875 end bim_attribute3
876 FROM
877 ( SELECT dates.start_date start_date,
878 SUM(decode(dates.period, ''C'',a.booked_amt'||l_curr_suffix||',0)) booked_amt,
879 SUM(decode(dates.period, ''P'',a.booked_amt'||l_curr_suffix||',0)) prev_booked_amt,
880 SUM(decode(dates.period, ''C'',a.booked_count,0)) booked_count,
881 SUM(decode(dates.period, ''P'',a.booked_count,0)) prev_booked_count
882 FROM
883 (
884 SELECT
885 fii.start_date START_DATE,
886 ''C'' PERIOD,
887 least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
888 FROM '||l_period_type||' fii
889 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
890 UNION ALL
891 SELECT
892 p2.start_date START_DATE,
893 ''P'' PERIOD,
894 p1.report_date REPORT_DATE
895 FROM
896 (
897 SELECT report_date , ROWNUM id
898 FROM
899 (
900 SELECT
901 least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE
902 FROM ' ||l_period_type||' fii
903 WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND &BIS_PREVIOUS_ASOF_DATE
904 ORDER BY fii.start_date DESC
905 )
906 ) p1,
907 (
908 SELECT start_date, ROWNUM id
909 FROM
910 (
911 SELECT fii.start_date START_DATE
912 FROM ' ||l_period_type||' fii
913 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
914 ORDER BY fii.start_date DESC
915 )
916 ) p2
917 WHERE p1.id(+) = p2.id) dates, bim_sgmt_val_f_mv a,fii_time_rpt_struct_v cal '||l_from||'
918 WHERE cal.report_date = dates.report_date
919 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) = cal.record_type_id
920 AND a.time_id = cal.time_id
921 AND a.period_type_id = cal.period_type_id '||l_where||' group by dates.start_date )
922 s,'|| l_period_type||' fii
923 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE AND
924 fii.start_date = s.start_date(+)
925 order by fii.start_date ';
926
927
928
929
930 x_custom_sql := l_sqltext;
931 l_custom_rec.attribute_name := ':l_record_type';
932 l_custom_rec.attribute_value := l_record_type_id;
933 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
934 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
935 x_custom_output.EXTEND;
936 x_custom_output (1) := l_custom_rec;
937 l_custom_rec.attribute_name := ':l_sgmt_id';
938 l_custom_rec.attribute_value := l_sgmt_id;
939 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
940 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
941 x_custom_output.EXTEND;
942 x_custom_output (2) := l_custom_rec;
943 END GET_SGMT_AVG_TXN_SQL;
944
945 PROCEDURE GET_CAMP_EFF_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
946 x_custom_sql OUT NOCOPY VARCHAR2,
947 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
948 IS
949 l_sqltext VARCHAR2 (20000);
950 l_as_of_date DATE;
951 l_period_type VARCHAR2 (2000);
952 l_record_type_id NUMBER;
953 l_cat_id VARCHAR2 (50) ;
954 l_sgmt_id VARCHAR2 (50) ;
955 l_curr VARCHAR2(50);
956 l_view_by VARCHAR2 (4000);
957
958 l_where VARCHAR2 (20000); -- static where clause
959 l_groupby VARCHAR2 (2000); -- to build group by clause
960 l_pc_from VARCHAR2 (20000); -- from clause to handle product category
961 l_pc_where VARCHAR2 (20000); -- where clause to handle product category
962 l_custom_rec BIS_QUERY_ATTRIBUTES;
963 l_object_type VARCHAR2(30);
964 l_url_link VARCHAR2(200);
965
966 l_start_date VARCHAR2(30);
967 l_curr_suffix VARCHAR2(2);
968 l_bim_url2 VARCHAR2(50);
969 l_bim_url3 VARCHAR2(50);
970 l_bim_url4 VARCHAR2(50);
971 l_bim_url5 VARCHAR2(50);
972 l_bim_url6 VARCHAR2(50);
973 l_view_by_id VARCHAR2(50);
977 l_from VARCHAR2(200);
974 l_url_metric VARCHAR2(10);
975 l_url_viewby VARCHAR2(100);
976 l_url_viewbyid VARCHAR2(100);
978 l_url_str VARCHAR2(1000);
979 l_url_str_sgmt_jtf VARCHAR2(1000);
980 l_setup_id VARCHAR2(50);
981 l_url_str_csch_jtf VARCHAR2(1000);
982 l_leaf_node_flag VARCHAR2(1);
983 l_col VARCHAR2(200);
984
985
986
987 BEGIN
988 x_custom_output := bis_query_attributes_tbl ();
989 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
990 bim_pmv_dbi_utl_pkg.get_bim_page_sgmt_params(p_page_parameter_tbl,
991 l_as_of_date ,
992 l_period_type,
993 l_record_type_id,
994 l_view_by,
995 l_cat_id ,
996 l_sgmt_id,
997 l_curr,
998 l_url_metric,
999 l_url_viewby,
1000 l_url_viewbyid
1001 ) ;
1002
1003 l_bim_url2 := 'ACT';
1004 l_bim_url3 := 'RESP';
1005 l_bim_url4 := 'LEAD';
1006 l_bim_url5 := 'OPPT';
1007 l_bim_url6 := 'BOOK';
1008 l_view_by_id:='VIEWBYID';
1009
1010
1011 IF (l_curr = '''FII_GLOBAL1''') THEN
1012
1013 l_curr_suffix := '';
1014
1015 ELSIF (l_curr = '''FII_GLOBAL2''') THEN
1016
1017 l_curr_suffix := '_s';
1018
1019 ELSE
1020 l_curr_suffix := '';
1021
1022 END IF;
1023
1024
1025 IF l_cat_id IS NOT NULL THEN
1026
1027 l_pc_from := ' , eni_denorm_hierarchies edh , mtl_default_category_sets mdcs';
1028 l_pc_where := ' AND a.category_id = edh.child_id
1029 AND edh.object_type = ''CATEGORY_SET''
1030 AND edh.object_id = mdcs.category_set_id
1031 AND mdcs.functional_area_id = 11
1032 AND edh.dbi_flag = ''Y''
1033 AND edh.parent_id = :l_cat_id ';
1034
1035 ELSE
1036 l_pc_where := ' AND a.category_id = -9 ';
1037
1038 END IF;
1039
1040 IF l_view_by = 'TARGET SEGMENT+TARGET SEGMENT' THEN
1041
1042 l_url_link := '&BIM_DIM11='||l_view_by_id||'&ENI_ITEM_VBH_CAT='||replace(l_cat_id,'''',null)||'&BIM_PARAMETER2='||l_view_by_id||'&BIM_PARAMETER3='||l_view_by;
1043
1044 l_url_str :='''pFunctionName=BIM_I_SGMT_CAMP_EFF_R&pParamIds=Y&VIEW_BY='||l_view_by||'&VIEW_BY_NAME=VIEW_BY_ID''';
1045
1046 --l_url_str_sgmt_jtf :='''pFunctionName=BIM_I_SGMT_DRILL&PAGE.OBJ.objType=CELL&PAGE.OBJ.objAttribute=HIER&PAGE.OBJ.ID_NAME0=treeSearchId&PAGE.OBJ.treeSearchFlag=Y&PAGE.OBJ.ID0=VIEWBYID&treeSearchId=VIEWBYID''';
1047 l_url_str_sgmt_jtf := 'NULL';
1048
1049 IF l_sgmt_id is null THEN
1050 --i.e The value in Segment Lov is NULL , then show only Parent Segments
1051
1052 l_from := ' , bim_i_sgmt_denorm dn ' ;
1053 l_where := ' AND dn.immediate_parent_id IS NULL AND a.segment_id=dn.segment_id ';
1054 l_groupby := ' , dn.leaf_node_flag ' ;
1055 l_col := ' , dn.leaf_node_flag leaf_node_flag ' ;
1056
1057 ELSE
1058 -- two cases here, if the segment is a parent segment , then show the children underneath
1059 -- else show that segment only
1060 BEGIN
1061
1062 SELECT leaf_node_flag
1063 INTO l_leaf_node_flag
1064 FROM bim_i_sgmt_denorm
1065 WHERE segment_id = replace(l_sgmt_id,'''',null)
1066 AND segment_id = parent_segment_id;
1067
1068 EXCEPTION
1069 WHEN NO_DATA_FOUND THEN
1070 NULL;
1071 END;
1072
1073 IF l_leaf_node_flag = 'Y' THEN
1074 --Show the segment itself
1075
1076 l_where := ' AND a.segment_id = :l_sgmt_id ';
1077 l_col := ' , '''||l_leaf_node_flag||''' leaf_node_flag ' ;
1078
1079 ELSE
1080 --Show only immediate Children
1081
1082 l_from := ' , bim_i_sgmt_denorm dn ' ;
1083 l_where := ' AND dn.immediate_parent_id=:l_sgmt_id AND dn.immediate_parent_flag = ''Y''
1084 AND a.segment_id=dn.segment_id ';
1085
1086 l_groupby := ' , dn.leaf_node_flag ' ;
1087 l_col := ' , dn.leaf_node_flag leaf_node_flag ' ;
1088
1089 END IF;
1090
1091 END IF;
1092
1093 l_sqltext:='
1094 SELECT
1095 VIEWBY,
1096 VIEWBYID,
1097 activities BIM_ATTRIBUTE2,
1098 CASE WHEN prev_activities = 0 THEN NULL ELSE ((activities-prev_activities)/prev_activities)*100 end BIM_ATTRIBUTE3,
1099 responses BIM_ATTRIBUTE4,
1100 CASE WHEN prev_responses = 0 THEN NULL ELSE ((responses-prev_responses)/prev_responses)*100 end BIM_ATTRIBUTE5,
1101 leads BIM_ATTRIBUTE6,
1102 CASE WHEN prev_leads = 0 THEN NULL ELSE ((leads - prev_leads)/prev_leads)*100 end BIM_ATTRIBUTE7,
1103 new_opportunity_amt BIM_ATTRIBUTE8,
1104 CASE WHEN prev_new_opportunity_amt = 0 THEN NULL ELSE ((new_opportunity_amt - prev_new_opportunity_amt)/prev_new_opportunity_amt)*100 end BIM_ATTRIBUTE9,
1105 booked_amt BIM_ATTRIBUTE10,
1106 CASE WHEN prev_booked_amt = 0 THEN NULL ELSE ((booked_amt - prev_booked_amt)/prev_booked_amt)*100 end BIM_ATTRIBUTE11,
1107 CASE WHEN activities=0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_CAMP_ACT_R&pParamIds=Y'||l_url_link||''' END BIM_URL1,
1108 CASE WHEN prev_activities = 0 THEN null ELSE ''pFunctionName=BIM_I_SGMT_ACT_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url2||l_url_link||''' END BIM_URL2,
1109 CASE WHEN prev_responses = 0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_RESP_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url3||l_url_link||''' END BIM_URL3,
1110 CASE WHEN prev_leads = 0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_LEAD_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url4||l_url_link||''' END BIM_URL4,
1111 CASE WHEN prev_new_opportunity_amt = 0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_OPPT_AMT_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url5||l_url_link||''' END BIM_URL5,
1112 CASE WHEN prev_booked_amt = 0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_BOOK_ODR_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url6||l_url_link||''' END BIM_URL6,
1113 decode( leaf_node_flag ,''Y'','||l_url_str_sgmt_jtf||','||l_url_str||') BIM_URL7,
1114 NULL BIM_URL8,
1115 NULL BIM_URL9
1116 FROM
1117 (
1118 SELECT
1119 b.cell_name VIEWBY,
1120 VIEWBYID,
1121 a.leaf_node_flag leaf_node_flag,
1122 SUM(activities) activities,
1123 SUM(prev_activities) prev_activities,
1124 SUM(responses) responses,
1125 SUM(prev_responses) prev_responses,
1126 SUM(leads) leads,
1127 SUM(prev_leads) prev_leads,
1128 SUM(new_opportunity_amt) new_opportunity_amt,
1129 SUM(prev_new_opportunity_amt) prev_new_opportunity_amt,
1130 SUM(booked_amt) booked_amt,
1131 SUM(prev_booked_amt) prev_booked_amt
1132 FROM
1133 (
1134 SELECT
1135 a.segment_id viewbyid
1136 '||l_col||' ,
1137 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.activities_count,0)) activities ,
1138 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.activities_count,0)) prev_activities,
1139 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.responses,0)) responses,
1140 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.responses,0)) prev_responses,
1141 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.leads,0)) leads,
1142 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.leads,0)) prev_leads,
1143 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.new_opportunity_amt'||l_curr_suffix||',0)) new_opportunity_amt,
1144 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.new_opportunity_amt'||l_curr_suffix||',0)) prev_new_opportunity_amt,
1145 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.booked_amt'||l_curr_suffix||',0)) booked_amt,
1146 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.booked_amt'||l_curr_suffix||',0)) prev_booked_amt
1147 FROM bim_sgmt_act_mv a , fii_time_rpt_struct_v cal ' ||l_from||l_pc_from||
1148 ' WHERE a.time_id = cal.time_id
1149 AND a.period_type_id = cal.period_type_id
1150 AND BITAND(cal.record_type_id,:l_record_type)= cal.record_type_id
1151 AND cal.calendar_id = -1
1152 '||l_where ||l_pc_where
1153 ||' AND cal.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
1154 GROUP BY a.segment_id '||l_groupby||'
1155 ) a , ams_cells_all_tl b
1156 WHERE a.viewbyid=b.cell_id
1157 and b.language=userenv(''LANG'')
1158 group by viewbyid,b.cell_name , a.leaf_node_flag
1159 )
1160 WHERE activities > 0 OR prev_activities > 0 OR responses > 0 OR prev_responses > 0
1161 OR leads > 0 OR prev_leads > 0 OR new_opportunity_amt > 0 OR prev_new_opportunity_amt > 0
1162 OR booked_amt > 0 OR prev_booked_amt > 0
1163 &ORDER_BY_CLAUSE';
1164
1165 ELSIF l_view_by = 'MEDIA+MEDIA' THEN
1166
1167 --i.e view by is marketing Channnel
1168
1169 l_url_link := '&BIM_DIM11='||replace(l_sgmt_id,'''',null)||'&ENI_ITEM_VBH_CAT='||replace(l_cat_id,'''',null)||'&BIM_DIM9='||l_view_by_id||'&BIM_PARAMETER3='||l_view_by||'&BIM_PARAMETER4=VIEWBY'||'&BIM_PARAMETER2='||l_view_by_id;
1170
1171 IF l_sgmt_id is null THEN
1172
1173 l_where := 'AND dn.immediate_parent_id is null AND dn.segment_id = a.segment_id';
1174 l_from := l_from||' , bim_i_sgmt_denorm dn ';
1175 ELSE
1176
1177 l_where := 'AND a.segment_id=:l_sgmt_id ';
1178
1179 END IF;
1180
1181 l_sqltext:='
1182 SELECT
1183 VIEWBY,
1184 VIEWBYID,
1185 activities BIM_ATTRIBUTE2,
1186 CASE WHEN prev_activities = 0 THEN NULL ELSE ((activities-prev_activities)/prev_activities)*100 end BIM_ATTRIBUTE3,
1187 SUM(activities) OVER() BIM_GRAND_TOTAL1,
1188 CASE WHEN SUM(prev_activities) OVER() = 0 THEN NULL ELSE (SUM(activities) OVER()- SUM(prev_activities) OVER() ) * 100 / SUM(prev_activities) OVER() END BIM_GRAND_TOTAL2,
1189 responses BIM_ATTRIBUTE4,
1190 CASE WHEN prev_responses = 0 THEN NULL ELSE ((responses-prev_responses)/prev_responses)*100 end BIM_ATTRIBUTE5,
1191 SUM(responses) OVER() BIM_GRAND_TOTAL3,
1192 CASE WHEN SUM(prev_responses) OVER() = 0 THEN NULL ELSE (SUM(responses) OVER()- SUM(prev_responses) OVER() ) * 100 / SUM(prev_responses) OVER() END BIM_GRAND_TOTAL4,
1193 leads BIM_ATTRIBUTE6,
1194 CASE WHEN prev_leads = 0 THEN NULL ELSE ((leads - prev_leads)/prev_leads)*100 end BIM_ATTRIBUTE7,
1195 SUM(leads) OVER() BIM_GRAND_TOTAL5,
1196 CASE WHEN SUM(prev_leads) OVER() = 0 THEN NULL ELSE (SUM(leads) OVER()- SUM(prev_leads) OVER() ) * 100 / SUM(prev_leads) OVER() END BIM_GRAND_TOTAL6,
1197 new_opportunity_amt BIM_ATTRIBUTE8,
1198 CASE WHEN prev_new_opportunity_amt = 0 THEN NULL ELSE ((new_opportunity_amt - prev_new_opportunity_amt)/prev_new_opportunity_amt)*100 end BIM_ATTRIBUTE9,
1199 SUM(new_opportunity_amt) OVER() BIM_GRAND_TOTAL7,
1200 CASE WHEN SUM(prev_new_opportunity_amt) OVER() = 0 THEN NULL ELSE (SUM(new_opportunity_amt) OVER()- SUM(prev_new_opportunity_amt) OVER() ) * 100 / SUM(prev_leads) OVER() END BIM_GRAND_TOTAL8,
1201 booked_amt BIM_ATTRIBUTE10,
1202 CASE WHEN prev_booked_amt = 0 THEN NULL ELSE ((booked_amt - prev_booked_amt)/prev_booked_amt)*100 end BIM_ATTRIBUTE11,
1203 SUM(booked_amt) OVER() BIM_GRAND_TOTAL9,
1204 CASE WHEN SUM(prev_booked_amt) OVER() = 0 THEN NULL ELSE (SUM(booked_amt) OVER()- SUM(prev_booked_amt) OVER() ) * 100 / SUM(prev_booked_amt) OVER() END BIM_GRAND_TOTAL10,
1205 CASE WHEN activities=0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_CAMP_ACT_R&pParamIds=Y'||l_url_link||''' END BIM_URL1,
1206 CASE WHEN prev_activities = 0 THEN null ELSE ''pFunctionName=BIM_I_SGMT_ACT_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url2||l_url_link||''' END BIM_URL2,
1207 CASE WHEN prev_responses = 0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_RESP_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url3||l_url_link||''' END BIM_URL3,
1208 CASE WHEN prev_leads = 0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_LEAD_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url4||l_url_link||''' END BIM_URL4,
1209 CASE WHEN prev_new_opportunity_amt = 0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_OPPT_AMT_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url5||l_url_link||''' END BIM_URL5,
1210 CASE WHEN prev_booked_amt = 0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_BOOK_ODR_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url6||l_url_link||''' END BIM_URL6,
1211 NULL BIM_URL7,
1212 NULL BIM_URL8,
1213 NULL BIM_URL9
1214 FROM
1215 (
1216 SELECT
1217 b.value VIEWBY,
1218 VIEWBYID,
1219 SUM(activities) activities,
1220 SUM(prev_activities) prev_activities,
1221 SUM(responses) responses,
1222 SUM(prev_responses) prev_responses,
1223 SUM(leads) leads,
1224 SUM(prev_leads) prev_leads,
1225 SUM(new_opportunity_amt) new_opportunity_amt,
1226 SUM(prev_new_opportunity_amt) prev_new_opportunity_amt,
1227 SUM(booked_amt) booked_amt,
1228 SUM(prev_booked_amt) prev_booked_amt
1229 FROM
1230 (
1231 SELECT
1232 a.activity_id viewbyid,
1233 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.activities_count,0)) activities ,
1234 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.activities_count,0)) prev_activities,
1235 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.responses,0)) responses,
1236 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.responses,0)) prev_responses,
1237 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.leads,0)) leads,
1238 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.leads,0)) prev_leads,
1239 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.new_opportunity_amt'||l_curr_suffix||',0)) new_opportunity_amt,
1240 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.new_opportunity_amt'||l_curr_suffix||',0)) prev_new_opportunity_amt,
1241 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.booked_amt'||l_curr_suffix||',0)) booked_amt,
1242 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.booked_amt'||l_curr_suffix||',0)) prev_booked_amt
1246 AND BITAND(cal.record_type_id,:l_record_type)= cal.record_type_id
1243 FROM bim_sgmt_act_ch_mv a , fii_time_rpt_struct_v cal ' ||l_pc_from||l_from||
1244 ' WHERE a.time_id = cal.time_id
1245 AND a.period_type_id = cal.period_type_id
1247 AND cal.calendar_id = -1
1248 '||l_where ||l_pc_where
1249 ||'
1250 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
1251 GROUP BY a.activity_id
1252 ) a , bim_dimv_media b
1253 WHERE a.viewbyid = b.id (+)
1254 group by viewbyid,b.value
1255 )
1256 WHERE activities > 0 OR prev_activities > 0 OR responses > 0 OR prev_responses > 0
1257 OR leads > 0 OR prev_leads > 0 OR new_opportunity_amt > 0 OR prev_new_opportunity_amt > 0
1258 OR booked_amt > 0 OR prev_booked_amt > 0
1259 &ORDER_BY_CLAUSE';
1260
1261 ELSIF l_view_by = 'CAMPAIGN+CAMPAIGN' THEN
1262 --i.e view by is Camapign Activity
1263 --l_url_link := '&BIM_DIM11=replace('||l_sgmt_id||','''',null)&ENI_ITEM_VBH_CAT=replace('||l_cat_id||','''',null)&BIM_PARAMETER2='||l_view_by_id||'&BIM_PARAMETER3='||l_view_by;
1264 l_url_link := '&BIM_DIM11='||replace(l_sgmt_id,'''',null)||'&ENI_ITEM_VBH_CAT='||replace(l_cat_id,'''',null)||'&BIM_PARAMETER5=VIEWBY'||'&BIM_PARAMETER2='||l_view_by_id||'&BIM_PARAMETER3='||l_view_by;
1265 l_setup_id := 'custom_setup_id';
1266
1267 --l_url_str_csch_jtf :='pFunctionName=BIM_I_CSCH_START_DRILL&pParamIds=Y&VIEW_BY='||l_view_by||'&PAGE.OBJ.ID_NAME1=customSetupId&VIEW_BY_NAME=VIEW_BY_ID&PAGE.OBJ.ID1=''||'||l_setup_id||'||
1268 --''&PAGE.OBJ.objType=CSCH&PAGE.OBJ.objAttribute=DETL&PAGE.OBJ.ID_NAME0=objId&PAGE.OBJ.ID0=';
1269
1270 l_url_str_csch_jtf :='pFunctionName=AMS_WB_CSCH_UPDATE&pParamIds=Y&VIEW_BY='||l_view_by||'&objType=CSCH&objId=';
1271
1272 IF l_sgmt_id is null THEN
1273
1274 l_where := 'AND dn.immediate_parent_id is null AND dn.segment_id = a.segment_id';
1275 l_from := l_from||' , bim_i_sgmt_denorm dn ';
1276 ELSE
1277
1278 l_where := 'AND a.segment_id=:l_sgmt_id ';
1279
1280 END IF;
1281
1282 l_sqltext:='
1283 SELECT
1284 VIEWBY,
1285 VIEWBYID,
1286 activities BIM_ATTRIBUTE2,
1287 CASE WHEN prev_activities = 0 THEN NULL ELSE ((activities-prev_activities)/prev_activities)*100 end BIM_ATTRIBUTE3,
1288 SUM(activities) OVER() BIM_GRAND_TOTAL1,
1289 CASE WHEN SUM(prev_activities) OVER() = 0 THEN NULL ELSE (SUM(activities) OVER()- SUM(prev_activities) OVER() ) * 100 / SUM(prev_activities) OVER() END BIM_GRAND_TOTAL2,
1290 responses BIM_ATTRIBUTE4,
1291 CASE WHEN prev_responses = 0 THEN NULL ELSE ((responses-prev_responses)/prev_responses)*100 end BIM_ATTRIBUTE5,
1292 SUM(responses) OVER() BIM_GRAND_TOTAL3,
1293 CASE WHEN SUM(prev_responses) OVER() = 0 THEN NULL ELSE (SUM(responses) OVER()- SUM(prev_responses) OVER() ) * 100 / SUM(prev_responses) OVER() END BIM_GRAND_TOTAL4,
1294 leads BIM_ATTRIBUTE6,
1295 CASE WHEN prev_leads = 0 THEN NULL ELSE ((leads - prev_leads)/prev_leads)*100 end BIM_ATTRIBUTE7,
1296 SUM(leads) OVER() BIM_GRAND_TOTAL5,
1297 CASE WHEN SUM(prev_leads) OVER() = 0 THEN NULL ELSE (SUM(leads) OVER()- SUM(prev_leads) OVER() ) * 100 / SUM(prev_leads) OVER() END BIM_GRAND_TOTAL6,
1298 new_opportunity_amt BIM_ATTRIBUTE8,
1299 CASE WHEN prev_new_opportunity_amt = 0 THEN NULL ELSE ((new_opportunity_amt - prev_new_opportunity_amt)/prev_new_opportunity_amt)*100 end BIM_ATTRIBUTE9,
1300 SUM(new_opportunity_amt) OVER() BIM_GRAND_TOTAL7,
1301 CASE WHEN SUM(prev_new_opportunity_amt) OVER() = 0 THEN NULL ELSE (SUM(new_opportunity_amt) OVER()- SUM(prev_new_opportunity_amt) OVER() ) * 100 / SUM(prev_new_opportunity_amt) OVER() END BIM_GRAND_TOTAL8,
1302 booked_amt BIM_ATTRIBUTE10,
1303 CASE WHEN prev_booked_amt = 0 THEN NULL ELSE ((booked_amt - prev_booked_amt)/prev_booked_amt)*100 end BIM_ATTRIBUTE11,
1304 SUM(booked_amt) OVER() BIM_GRAND_TOTAL9,
1305 CASE WHEN SUM(prev_booked_amt) OVER() = 0 THEN NULL ELSE (SUM(booked_amt) OVER()- SUM(prev_booked_amt) OVER() ) * 100 / SUM(prev_booked_amt) OVER() END BIM_GRAND_TOTAL10,
1306 NULL BIM_URL1,
1307 CASE WHEN prev_activities = 0 THEN null ELSE ''pFunctionName=BIM_I_SGMT_ACT_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url2||l_url_link||''' END BIM_URL2,
1308 CASE WHEN prev_responses = 0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_RESP_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url3||l_url_link||''' END BIM_URL3,
1309 CASE WHEN prev_leads = 0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_LEAD_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url4||l_url_link||''' END BIM_URL4,
1310 CASE WHEN prev_new_opportunity_amt = 0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_OPPT_AMT_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url5||l_url_link||''' END BIM_URL5,
1311 CASE WHEN prev_booked_amt = 0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_BOOK_ODR_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url6||l_url_link||''' END BIM_URL6,
1312 NULL BIM_URL7,
1313 NULL BIM_URL8,
1314 '''||l_url_str_csch_jtf||'''||schedule_id BIM_URL9
1315 FROM
1316 (
1317 SELECT
1318 b.name VIEWBY,
1319 VIEWBYID,
1320 sch.schedule_id schedule_id,
1321 sch.custom_setup_id,
1322 SUM(activities) activities,
1323 SUM(prev_activities) prev_activities,
1324 SUM(responses) responses,
1325 SUM(prev_responses) prev_responses,
1326 SUM(leads) leads,
1327 SUM(prev_leads) prev_leads,
1328 SUM(new_opportunity_amt) new_opportunity_amt,
1329 SUM(prev_new_opportunity_amt) prev_new_opportunity_amt,
1330 SUM(booked_amt) booked_amt,
1331 SUM(prev_booked_amt) prev_booked_amt
1332 FROM
1333 (
1334 SELECT
1335 a.source_code_id viewbyid,
1336 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.activities_count,0)) activities ,
1337 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.activities_count,0)) prev_activities,
1338 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.responses,0)) responses,
1339 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.responses,0)) prev_responses,
1340 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.leads,0)) leads,
1341 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.leads,0)) prev_leads,
1345 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.booked_amt'||l_curr_suffix||',0)) prev_booked_amt
1342 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.new_opportunity_amt'||l_curr_suffix||',0)) new_opportunity_amt,
1343 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.new_opportunity_amt'||l_curr_suffix||',0)) prev_new_opportunity_amt,
1344 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.booked_amt'||l_curr_suffix||',0)) booked_amt,
1346 FROM bim_sgmt_act_sh_mv a , fii_time_rpt_struct_v cal ' ||l_pc_from|| l_from||
1347 ' WHERE a.time_id = cal.time_id
1348 AND a.period_type_id = cal.period_type_id
1349 AND BITAND(cal.record_type_id,:l_record_type)= cal.record_type_id
1350 AND cal.calendar_id = -1
1351 '||l_where ||l_pc_where
1352 ||' AND cal.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
1353 GROUP BY a.source_code_id
1354 ) a , bim_i_obj_name_mv b , ams_campaign_schedules_b sch
1355 WHERE a.viewbyid = b.source_code_id (+)
1356 and b.object_id = sch.schedule_id
1357 and b.language (+) =userenv(''LANG'')
1358 group by viewbyid,b.name,sch.schedule_id,sch.custom_setup_id
1359 )
1360 WHERE responses > 0 OR prev_responses > 0
1361 OR leads > 0 OR prev_leads > 0 OR new_opportunity_amt > 0 OR prev_new_opportunity_amt > 0
1362 OR booked_amt > 0 OR prev_booked_amt > 0
1363 &ORDER_BY_CLAUSE';
1364
1365 ELSIF l_view_by = 'ITEM+ENI_ITEM_VBH_CAT' THEN
1366
1367 --i.e view by is Product Category
1368 --l_url_link := '&BIM_DIM11='||replace(l_sgmt_id,'''',null)||'&BIS_ENI_ITEM_VBH_CAT='||l_view_by_id||'&BIM_PARAMETER2='||l_view_by_id||'&BIM_PARAMETER3='||l_view_by;
1369 l_url_link := '&ENI_ITEM_VBH_CAT='||l_view_by_id||'&BIM_DIM11='||replace(l_sgmt_id,'''',null)||'&BIM_PARAMETER2='||l_view_by_id||'&BIM_PARAMETER3='||l_view_by;
1370 l_url_str :='''pFunctionName=BIM_I_SGMT_CAMP_EFF_R&pParamIds=Y&VIEW_BY='||l_view_by||'&VIEW_BY_NAME=VIEW_BY_ID''';
1371
1372 IF l_sgmt_id is null THEN
1373
1374 l_where := 'AND dn.immediate_parent_id is null AND dn.segment_id = a.segment_id';
1375 l_from := l_from||' , bim_i_sgmt_denorm dn ';
1376 ELSE
1377
1378 l_where := 'AND a.segment_id=:l_sgmt_id ';
1379
1380 END IF;
1381
1382 IF l_cat_id IS NULL THEN
1383
1384 l_sqltext:='
1385 SELECT
1386 VIEWBY,
1387 VIEWBYID,
1388 activities BIM_ATTRIBUTE2,
1389 CASE WHEN prev_activities = 0 THEN NULL ELSE ((activities-prev_activities)/prev_activities)*100 end BIM_ATTRIBUTE3,
1390 SUM(activities) OVER() BIM_GRAND_TOTAL1,
1391 CASE WHEN SUM(prev_activities) OVER() = 0 THEN NULL ELSE (SUM(activities) OVER()- SUM(prev_activities) OVER() ) * 100 / SUM(prev_activities) OVER() END BIM_GRAND_TOTAL2,
1392 responses BIM_ATTRIBUTE4,
1393 CASE WHEN prev_responses = 0 THEN NULL ELSE ((responses-prev_responses)/prev_responses)*100 end BIM_ATTRIBUTE5,
1394 SUM(responses) OVER() BIM_GRAND_TOTAL3,
1395 CASE WHEN SUM(prev_responses) OVER() = 0 THEN NULL ELSE (SUM(responses) OVER()- SUM(prev_responses) OVER() ) * 100 / SUM(prev_responses) OVER() END BIM_GRAND_TOTAL4,
1396 leads BIM_ATTRIBUTE6,
1397 CASE WHEN prev_leads = 0 THEN NULL ELSE ((leads - prev_leads)/prev_leads)*100 end BIM_ATTRIBUTE7,
1398 SUM(leads) OVER() BIM_GRAND_TOTAL5,
1399 CASE WHEN SUM(prev_leads) OVER() = 0 THEN NULL ELSE (SUM(leads) OVER()- SUM(prev_leads) OVER() ) * 100 / SUM(prev_leads) OVER() END BIM_GRAND_TOTAL6,
1400 new_opportunity_amt BIM_ATTRIBUTE8,
1401 CASE WHEN prev_new_opportunity_amt = 0 THEN NULL ELSE ((new_opportunity_amt - prev_new_opportunity_amt)/prev_new_opportunity_amt)*100 end BIM_ATTRIBUTE9,
1402 SUM(new_opportunity_amt) OVER() BIM_GRAND_TOTAL7,
1403 CASE WHEN SUM(prev_new_opportunity_amt) OVER() = 0 THEN NULL ELSE (SUM(new_opportunity_amt) OVER()- SUM(prev_new_opportunity_amt) OVER() ) * 100 / SUM(prev_new_opportunity_amt) OVER() END BIM_GRAND_TOTAL8,
1404 booked_amt BIM_ATTRIBUTE10,
1405 CASE WHEN prev_booked_amt = 0 THEN NULL ELSE ((booked_amt - prev_booked_amt)/prev_booked_amt)*100 end BIM_ATTRIBUTE11,
1406 SUM(booked_amt) OVER() BIM_GRAND_TOTAL9,
1407 CASE WHEN SUM(prev_booked_amt) OVER() = 0 THEN NULL ELSE (SUM(booked_amt) OVER()- SUM(prev_booked_amt) OVER() ) * 100 / SUM(prev_booked_amt) OVER() END BIM_GRAND_TOTAL10,
1408 CASE WHEN activities=0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_CAMP_ACT_R&pParamIds=Y'||l_url_link||''' END BIM_URL1,
1409 CASE WHEN prev_activities = 0 THEN null ELSE ''pFunctionName=BIM_I_SGMT_ACT_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url2||l_url_link||''' END BIM_URL2,
1410 CASE WHEN prev_responses = 0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_RESP_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url3||l_url_link||''' END BIM_URL3,
1411 CASE WHEN prev_leads = 0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_LEAD_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url4||l_url_link||''' END BIM_URL4,
1412 CASE WHEN prev_new_opportunity_amt = 0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_OPPT_AMT_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url5||l_url_link||''' END BIM_URL5,
1413 CASE WHEN prev_booked_amt = 0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_BOOK_ODR_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url6||l_url_link||''' END BIM_URL6,
1414 NULL BIM_URL7,
1415 decode( leaf_node_flag ,''Y'', NULL, '||l_url_str||') BIM_URL8,
1416 NULL BIM_URL9
1417 FROM
1418 (
1419 SELECT
1420 value VIEWBY,
1421 VIEWBYID,
1422 leaf_node_flag,
1423 SUM(activities) activities,
1424 SUM(prev_activities) prev_activities,
1425 SUM(responses) responses,
1426 SUM(prev_responses) prev_responses,
1427 SUM(leads) leads,
1428 SUM(prev_leads) prev_leads,
1429 SUM(new_opportunity_amt) new_opportunity_amt,
1430 SUM(prev_new_opportunity_amt) prev_new_opportunity_amt,
1431 SUM(booked_amt) booked_amt,
1432 SUM(prev_booked_amt) prev_booked_amt
1433 FROM
1434 (
1435 SELECT
1436 p.value,
1437 p.parent_id viewbyid,
1438 p.leaf_node_flag leaf_node_flag,
1439 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.activities_count,0)) activities ,
1443 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.leads,0)) leads,
1440 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.activities_count,0)) prev_activities,
1441 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.responses,0)) responses,
1442 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.responses,0)) prev_responses,
1444 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.leads,0)) prev_leads,
1445 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.new_opportunity_amt'||l_curr_suffix||',0)) new_opportunity_amt,
1446 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.new_opportunity_amt'||l_curr_suffix||',0)) prev_new_opportunity_amt,
1447 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.booked_amt'||l_curr_suffix||',0)) booked_amt,
1448 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.booked_amt'||l_curr_suffix||',0)) prev_booked_amt
1449 FROM bim_sgmt_act_mv a , fii_time_rpt_struct_v cal, eni_denorm_hierarchies edh,mtl_default_category_sets mdcs'||l_from||'
1450 ,( SELECT e.parent_id parent_id ,e.value value,e.leaf_node_flag leaf_node_flag
1451 FROM eni_item_vbh_nodes_v e
1452 WHERE e.top_node_flag=''Y''
1453 AND e.child_id = e.parent_id) p
1454 WHERE a.time_id = cal.time_id
1455 AND a.period_type_id = cal.period_type_id
1456 AND BITAND(cal.record_type_id,:l_record_type)= cal.record_type_id
1457 AND cal.calendar_id = -1
1458 AND a.category_id = edh.child_id
1459 AND edh.object_type = ''CATEGORY_SET''
1460 AND edh.object_id = mdcs.category_set_id
1461 AND mdcs.functional_area_id = 11
1462 AND edh.dbi_flag = ''Y''
1463 AND edh.parent_id = p.parent_id
1464 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
1465 '||l_where||'
1466 GROUP BY p.value ,p.parent_id , p.leaf_node_flag
1467 )
1468 group by viewbyid, value , leaf_node_flag
1469 )
1470 WHERE activities > 0 OR prev_activities > 0 OR responses > 0 OR prev_responses > 0
1471 OR leads > 0 OR prev_leads > 0 OR new_opportunity_amt > 0 OR prev_new_opportunity_amt > 0
1472 OR booked_amt > 0 OR prev_booked_amt > 0
1473 &ORDER_BY_CLAUSE';
1474
1475 ELSE
1476 -- i.e User has select a Product Category from LOV
1477
1478 l_pc_where := ' AND a.category_id = :l_cat_id ';
1479
1480
1481 l_sqltext:='
1482 SELECT
1483 VIEWBY,
1484 VIEWBYID,
1485 activities BIM_ATTRIBUTE2,
1486 CASE WHEN prev_activities = 0 THEN NULL ELSE ((activities-prev_activities)/prev_activities)*100 end BIM_ATTRIBUTE3,
1487 SUM(activities) OVER() BIM_GRAND_TOTAL1,
1488 CASE WHEN SUM(prev_activities) OVER() = 0 THEN NULL ELSE (SUM(activities) OVER()- SUM(prev_activities) OVER() ) * 100 / SUM(prev_activities) OVER() END BIM_GRAND_TOTAL2,
1489 responses BIM_ATTRIBUTE4,
1490 CASE WHEN prev_responses = 0 THEN NULL ELSE ((responses-prev_responses)/prev_responses)*100 end BIM_ATTRIBUTE5,
1491 SUM(responses) OVER() BIM_GRAND_TOTAL3,
1492 CASE WHEN SUM(prev_responses) OVER() = 0 THEN NULL ELSE (SUM(responses) OVER()- SUM(prev_responses) OVER() ) * 100 / SUM(prev_responses) OVER() END BIM_GRAND_TOTAL4,
1493 leads BIM_ATTRIBUTE6,
1494 CASE WHEN prev_leads = 0 THEN NULL ELSE ((leads - prev_leads)/prev_leads)*100 end BIM_ATTRIBUTE7,
1495 SUM(leads) OVER() BIM_GRAND_TOTAL5,
1496 CASE WHEN SUM(prev_leads) OVER() = 0 THEN NULL ELSE (SUM(leads) OVER()- SUM(prev_leads) OVER() ) * 100 / SUM(prev_leads) OVER() END BIM_GRAND_TOTAL6,
1497 new_opportunity_amt BIM_ATTRIBUTE8,
1498 CASE WHEN prev_new_opportunity_amt = 0 THEN NULL ELSE ((new_opportunity_amt - prev_new_opportunity_amt)/prev_new_opportunity_amt)*100 end BIM_ATTRIBUTE9,
1499 SUM(new_opportunity_amt) OVER() BIM_GRAND_TOTAL7,
1500 CASE WHEN SUM(prev_new_opportunity_amt) OVER() = 0 THEN NULL ELSE (SUM(new_opportunity_amt) OVER()- SUM(prev_new_opportunity_amt) OVER() ) * 100 / SUM(prev_new_opportunity_amt) OVER() END BIM_GRAND_TOTAL8,
1501 booked_amt BIM_ATTRIBUTE10,
1502 CASE WHEN prev_booked_amt = 0 THEN NULL ELSE ((booked_amt - prev_booked_amt)/prev_booked_amt)*100 end BIM_ATTRIBUTE11,
1503 SUM(booked_amt) OVER() BIM_GRAND_TOTAL9,
1504 CASE WHEN SUM(prev_booked_amt) OVER() = 0 THEN NULL ELSE (SUM(booked_amt) OVER()- SUM(prev_booked_amt) OVER() ) * 100 / SUM(prev_booked_amt) OVER() END BIM_GRAND_TOTAL10,
1505 CASE WHEN activities=0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_CAMP_ACT_R&pParamIds=Y'||l_url_link||''' END BIM_URL1,
1506 CASE WHEN prev_activities = 0 THEN null ELSE ''pFunctionName=BIM_I_SGMT_ACT_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url2||l_url_link||''' END BIM_URL2,
1507 CASE WHEN prev_responses = 0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_RESP_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url3||l_url_link||''' END BIM_URL3,
1508 CASE WHEN prev_leads = 0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_LEAD_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url4||l_url_link||''' END BIM_URL4,
1509 CASE WHEN prev_new_opportunity_amt = 0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_OPPT_AMT_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url5||l_url_link||''' END BIM_URL5,
1510 CASE WHEN prev_booked_amt = 0 THEN NULL ELSE ''pFunctionName=BIM_I_SGMT_BOOK_ODR_T&pParamIds=Y&BIM_PARAMETER1='||l_bim_url6||l_url_link||''' END BIM_URL6,
1511 NULL BIM_URL7,
1512 decode( leaf_node_flag ,''Y'', NULL, '||l_url_str||') BIM_URL8,
1513 NULL BIM_URL9
1514 FROM
1515 (
1516 SELECT
1517 value VIEWBY,
1518 VIEWBYID,
1519 leaf_node_flag,
1520 SUM(activities) activities,
1521 SUM(prev_activities) prev_activities,
1522 SUM(responses) responses,
1523 SUM(prev_responses) prev_responses,
1524 SUM(leads) leads,
1525 SUM(prev_leads) prev_leads,
1526 SUM(new_opportunity_amt) new_opportunity_amt,
1527 SUM(prev_new_opportunity_amt) prev_new_opportunity_amt,
1528 SUM(booked_amt) booked_amt,
1529 SUM(prev_booked_amt) prev_booked_amt
1530 FROM
1531 (
1532 SELECT
1533 p.value,
1534 p.id viewbyid,
1535 p.leaf_node_flag leaf_node_flag,
1539 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.responses,0)) prev_responses,
1536 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.activities_count,0)) activities ,
1537 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.activities_count,0)) prev_activities,
1538 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.responses,0)) responses,
1540 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.leads,0)) leads,
1541 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.leads,0)) prev_leads,
1542 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.new_opportunity_amt'||l_curr_suffix||',0)) new_opportunity_amt,
1543 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.new_opportunity_amt'||l_curr_suffix||',0)) prev_new_opportunity_amt,
1544 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.booked_amt'||l_curr_suffix||',0)) booked_amt,
1545 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.booked_amt'||l_curr_suffix||',0)) prev_booked_amt
1546 FROM bim_sgmt_act_mv a , fii_time_rpt_struct_v cal , eni_denorm_hierarchies edh,mtl_default_category_sets mdcs
1547 ,( SELECT e.id id ,e.value value,e.leaf_node_flag leaf_node_flag
1548 FROM eni_item_vbh_nodes_v e
1549 WHERE e.parent_id = :l_cat_id
1550 AND e.id = e.child_id
1551 AND ((e.leaf_node_flag=''N'' AND e.parent_id<>e.id) OR e.leaf_node_flag=''Y'')) p '||l_from||'
1552 WHERE a.time_id = cal.time_id
1553 AND a.period_type_id = cal.period_type_id
1554 AND BITAND(cal.record_type_id,:l_record_type)= cal.record_type_id
1555 AND cal.calendar_id = -1
1556 AND a.category_id = edh.child_id
1557 AND edh.object_type = ''CATEGORY_SET''
1558 AND edh.object_id = mdcs.category_set_id
1559 AND mdcs.functional_area_id = 11
1560 AND edh.dbi_flag = ''Y''
1561 AND edh.parent_id = p.id
1562 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
1563 '||l_where||'
1564 GROUP BY p.value , p.id , p.leaf_node_flag
1565 UNION ALL
1566 SELECT
1567 bim_pmv_dbi_utl_pkg.get_lookup_value(''DASS'') value,
1568 p.id viewbyid,
1569 p.leaf_node_flag leaf_node_flag,
1570 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.activities_count,0)) activities ,
1571 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.activities_count,0)) prev_activities,
1572 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.responses,0)) responses,
1573 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.responses,0)) prev_responses,
1574 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.leads,0)) leads,
1575 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.leads,0)) prev_leads,
1576 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.new_opportunity_amt'||l_curr_suffix||',0)) new_opportunity_amt,
1577 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.new_opportunity_amt'||l_curr_suffix||',0)) prev_new_opportunity_amt,
1578 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.booked_amt'||l_curr_suffix||',0)) booked_amt,
1579 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,a.booked_amt'||l_curr_suffix||',0)) prev_booked_amt
1580 FROM bim_sgmt_act_mv a , fii_time_rpt_struct_v cal ,
1581 ( SELECT e.id id ,e.value value , leaf_node_flag
1582 FROM eni_item_vbh_nodes_v e
1583 WHERE e.parent_id = :l_cat_id
1584 AND e.parent_id = e.child_id
1585 AND leaf_node_flag <> ''Y'') p '||l_from||'
1586 WHERE a.time_id = cal.time_id
1587 AND a.period_type_id = cal.period_type_id
1588 AND BITAND(cal.record_type_id,:l_record_type)= cal.record_type_id
1589 AND cal.calendar_id = -1
1590 AND a.category_id = p.id
1591 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
1592 '||l_where||'
1593 GROUP BY p.value , p.id , p.leaf_node_flag
1594 )
1595 group by viewbyid, value , leaf_node_flag
1596 )
1597 WHERE activities > 0 OR prev_activities > 0 OR responses > 0 OR prev_responses > 0
1598 OR leads > 0 OR prev_leads > 0 OR new_opportunity_amt > 0 OR prev_new_opportunity_amt > 0
1599 OR booked_amt > 0 OR prev_booked_amt > 0
1600 &ORDER_BY_CLAUSE';
1601
1602 END IF;
1603 END IF;
1604
1605 x_custom_sql := l_sqltext;
1606 l_custom_rec.attribute_name := ':l_record_type';
1607 l_custom_rec.attribute_value := l_record_type_id;
1608 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1609 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
1610 x_custom_output.EXTEND;
1611 x_custom_output (1) := l_custom_rec;
1612 l_custom_rec.attribute_name := ':l_sgmt_id';
1613 l_custom_rec.attribute_value := l_sgmt_id;
1614 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1615 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
1616 x_custom_output.EXTEND;
1617 x_custom_output (2) := l_custom_rec;
1618 l_custom_rec.attribute_name := ':l_cat_id';
1619 l_custom_rec.attribute_value := l_cat_id;
1620 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1621 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
1622 x_custom_output.EXTEND;
1623 x_custom_output (3) := l_custom_rec;
1624
1625
1626 END GET_CAMP_EFF_SQL;
1627
1628 PROCEDURE GET_CAMP_ACT_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
1629 x_custom_sql OUT NOCOPY VARCHAR2,
1630 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1631 IS
1632 l_sqltext VARCHAR2 (20000);
1633 iflag NUMBER;
1634 l_period_type_hc NUMBER;
1635 l_as_of_date DATE;
1636 l_period_type VARCHAR2 (2000);
1637 l_record_type_id NUMBER;
1638 l_cat_id VARCHAR2 (50) ;
1639 l_sgmt_id VARCHAR2 (50) ;
1640 l_curr VARCHAR2(50);
1641 l_view_by VARCHAR2 (4000);
1642 l_select_filter VARCHAR2 (20000); -- to build select filter part
1643 l_where VARCHAR2 (20000); -- static where clause
1644 l_groupby VARCHAR2 (2000); -- to build group by clause
1645 l_pc_from VARCHAR2 (20000); -- from clause to handle product category
1646 l_pc_where VARCHAR2 (20000); -- where clause to handle product category
1647 l_custom_rec BIS_QUERY_ATTRIBUTES;
1648 l_object_type VARCHAR2(30);
1649 l_url_link VARCHAR2(200);
1650 l_url_camp1 VARCHAR2(3000);
1651 l_url_camp2 VARCHAR2(3000);
1652
1653 l_start_date VARCHAR2(30);
1654 l_curr_suffix VARCHAR2(2);
1655 l_bim_url2 VARCHAR2(50);
1656 l_bim_url3 VARCHAR2(50);
1657 l_bim_url4 VARCHAR2(50);
1658 l_bim_url5 VARCHAR2(50);
1659 l_bim_url6 VARCHAR2(50);
1660 l_view_by_id VARCHAR2(50);
1661 l_url_metric VARCHAR2(10);
1662 l_url_viewby VARCHAR2(100);
1663 l_url_viewbyid VARCHAR2(100);
1664 l_from VARCHAR2(100);
1665 l_resource_id NUMBER;
1666 l_setup_id VARCHAR2(50);
1667 l_url_str_csch_jtf VARCHAR2(1000);
1668 l_from_outer VARCHAR2(200);
1669 l_where_outer VARCHAR2(500);
1670
1671
1672 BEGIN
1673 x_custom_output := bis_query_attributes_tbl ();
1674 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
1675 bim_pmv_dbi_utl_pkg.get_bim_page_sgmt_params(p_page_parameter_tbl,
1676 l_as_of_date ,
1677 l_period_type,
1678 l_record_type_id,
1679 l_view_by,
1680 l_cat_id ,
1681 l_sgmt_id,
1682 l_curr,
1683 l_url_metric,
1684 l_url_viewby,
1685 l_url_viewbyid
1686 ) ;
1687
1688
1689 IF l_url_viewby = 'MEDIA+MEDIA' THEN
1690
1691
1692 l_where := ' AND a.activity_id = '||l_url_viewbyid;
1693
1694
1695 END IF;
1696
1697 IF (l_curr = '''FII_GLOBAL1''') THEN
1698
1699 l_curr_suffix := '';
1700
1701 ELSIF (l_curr = '''FII_GLOBAL2''') THEN
1702
1703 l_curr_suffix := '_s';
1704
1705 ELSE
1706
1707 l_curr_suffix := '';
1708
1709 END IF;
1710
1711 IF l_cat_id IS NOT NULL THEN
1712
1713 l_pc_from := ' , eni_denorm_hierarchies edh , mtl_default_category_sets mdcs';
1714 l_pc_where := ' AND a.category_id = edh.child_id
1715 AND edh.object_type = ''CATEGORY_SET''
1716 AND edh.object_id = mdcs.category_set_id
1717 AND mdcs.functional_area_id = 11
1718 AND edh.dbi_flag = ''Y''
1719 AND edh.parent_id = :l_cat_id ';
1720
1721 END IF;
1722
1723 IF l_sgmt_id IS NOT NULL THEN
1724
1725 l_where := l_where||' AND a.segment_id=:l_sgmt_id';
1726 ELSE
1727 l_from := l_from||',bim_i_sgmt_denorm dn';
1728 l_where := l_where||' and dn.immediate_parent_id is null
1729 and a.segment_id=dn.segment_id';
1730
1731 END IF;
1732
1733
1734 l_setup_id := 'custom_setup_id';
1735
1736 l_url_str_csch_jtf :='pFunctionName=AMS_WB_CSCH_UPDATE&pParamIds=Y&VIEW_BY='||l_view_by||'&objType=CSCH&objId=';
1737
1738 IF get_admin_status = 'N' THEN
1739
1740 l_from_outer := ' , ams_act_access_denorm ac ' ;
1741 l_where_outer := ' AND c.object_id = ac.object_id
1742 AND c.object_type = ac.object_type
1743 AND ac.resource_id = :l_resource_id ';
1744
1745 l_resource_id := GET_RESOURCE_ID ;
1746
1747 END IF;
1748
1749 l_sqltext:='
1750 SELECT
1751 VIEWBY,
1752 VIEWBYID,
1753 VIEWBY BIM_ATTRIBUTE1,
1754 meaning BIM_ATTRIBUTE2,
1755 responses BIM_ATTRIBUTE3,
1756 NULL BIM_ATTRIBUTE4,
1757 booked_amt BIM_ATTRIBUTE5,
1758 NULL BIM_ATTRIBUTE6
1759 ,'''||l_url_str_csch_jtf||'''||object_id BIM_URL1
1760 FROM
1761 (
1762 SELECT
1763 c.name VIEWBY,
1764 VIEWBYID,
1765 l.meaning meaning,
1766 c.object_id object_id,
1767 SUM(responses) responses ,
1768 SUM(prev_responses) prev_responses,
1769 SUM(booked_amt) booked_amt,
1770 SUM(prev_booked_amt) prev_booked_amt
1771 FROM
1772 (
1773 SELECT
1774 a.source_code_id viewbyid,
1775 a.schedule_purpose,
1776 SUM(a.responses) responses,
1777 0 prev_responses,
1778 SUM(a.booked_amt'||l_curr_suffix||') booked_amt,
1779 0 prev_booked_amt
1780 FROM bim_sgmt_act_h_mv facts, bim_sgmt_act_b_mv a '||l_pc_from|| l_from||
1781 ' WHERE ( facts.schedule_start_date BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE AND &BIS_CURRENT_ASOF_DATE )
1782 AND facts.source_code_id=a.source_code_id AND facts.segment_id=a.segment_id
1783 '||l_where ||l_pc_where
1784 ||'
1785 GROUP BY a.source_code_id , a.schedule_purpose
1786 UNION ALL
1787 SELECT
1788 a.source_code_id viewbyid,
1789 a.schedule_purpose,
1790 0 responses,
1791 SUM(a.responses) prev_responses,
1792 0 booked_amt,
1793 SUM(a.booked_amt'||l_curr_suffix||') prev_booked_amt
1794 FROM bim_sgmt_act_h_mv facts, bim_sgmt_act_b_mv a '||l_pc_from|| l_from||
1795 ' WHERE ( facts.schedule_start_date BETWEEN &BIS_PREVIOUS_EFFECTIVE_START_DATE AND &BIS_PREVIOUS_ASOF_DATE )
1796 AND facts.source_code_id=a.source_code_id AND facts.segment_id=a.segment_id
1797 '||l_where ||l_pc_where
1798 ||'
1799 GROUP BY a.source_code_id , a.schedule_purpose
1800 ) a , ams_lookups l,bim_i_obj_name_mv c '||l_from_outer||'
1801 WHERE l.lookup_type(+) =''AMS_ACTIVITY_PURPOSES'' and l.lookup_code(+) = a.schedule_purpose
1802 and a.viewbyid = c.source_code_id
1803 and c.language=userenv(''LANG'') '||l_where_outer||'
1804 group by viewbyid,c.name , l.meaning , c.object_id
1805 )
1806 ';
1807
1808
1809 x_custom_sql := l_sqltext;
1810 l_custom_rec.attribute_name := ':l_record_type';
1811 l_custom_rec.attribute_value := l_record_type_id;
1812 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1813 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
1814 x_custom_output.EXTEND;
1815 x_custom_output (1) := l_custom_rec;
1816 l_custom_rec.attribute_name := ':l_sgmt_id';
1817 l_custom_rec.attribute_value := l_sgmt_id;
1818 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1819 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
1820 x_custom_output.EXTEND;
1821 x_custom_output (2) := l_custom_rec;
1822 l_custom_rec.attribute_name := ':l_cat_id';
1823 l_custom_rec.attribute_value := l_cat_id;
1824 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1825 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
1826 x_custom_output.EXTEND;
1827 x_custom_output (3) := l_custom_rec;
1828 l_custom_rec.attribute_name := ':l_resource_id';
1829 l_custom_rec.attribute_value := l_resource_id;
1830 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1831 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
1832 x_custom_output.EXTEND;
1833 x_custom_output (4) := l_custom_rec;
1834
1835
1836 END GET_CAMP_ACT_SQL;
1837
1838 PROCEDURE GET_CAMP_TREND_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
1839 x_custom_sql OUT NOCOPY VARCHAR2,
1840 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1841 IS
1842 l_sqltext VARCHAR2 (20000);
1843 l_as_of_date DATE;
1844 l_period_type VARCHAR2 (2000);
1845 l_record_type_id NUMBER;
1846 l_cat_id VARCHAR2 (50) ;
1847 l_sgmt_id VARCHAR2 (50) ;
1848 l_curr VARCHAR2(50);
1849 l_view_by VARCHAR2 (4000);
1850 l_select_filter VARCHAR2 (20000); -- to build select filter part
1851 l_where VARCHAR2 (20000); -- static where clause
1852 l_groupby VARCHAR2 (2000); -- to build group by clause
1853 l_from VARCHAR2 (20000); -- from clause
1854 l_custom_rec BIS_QUERY_ATTRIBUTES;
1855 l_object_type VARCHAR2(30);
1856
1857 l_start_date VARCHAR2(30);
1858 l_curr_suffix VARCHAR2(2);
1859 l_url_metric VARCHAR2(10);
1860 l_url_viewby VARCHAR2(100);
1861 l_url_viewbyid VARCHAR2(100);
1862 l_col_name VARCHAR2(30);
1863 l_pc_from VARCHAR2(200);
1864 l_pc_where VARCHAR2(2000);
1865 l_col_where VARCHAR2(500);
1866
1867
1868 BEGIN
1869 x_custom_output := bis_query_attributes_tbl ();
1870 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
1871 bim_pmv_dbi_utl_pkg.get_bim_page_sgmt_params(p_page_parameter_tbl,
1872 l_as_of_date ,
1873 l_period_type,
1874 l_record_type_id,
1875 l_view_by,
1876 l_cat_id ,
1877 l_sgmt_id,
1878 l_curr ,
1879 l_url_metric,
1880 l_url_viewby,
1881 l_url_viewbyid
1882 ) ;
1883
1884 IF (l_curr = '''FII_GLOBAL1''') THEN
1885 l_curr_suffix := '';
1886
1887 ELSIF (l_curr = '''FII_GLOBAL2''') THEN
1888 l_curr_suffix := '_s';
1889
1890 ELSE
1891 l_curr_suffix := '';
1892 END IF;
1893
1894
1895
1896
1897 CASE l_url_metric
1898 WHEN 'ACT' THEN l_col_name := 'ACTIVITIES_COUNT';
1899 WHEN 'RESP' THEN l_col_name := 'RESPONSES';
1900 WHEN 'LEAD' THEN l_col_name := 'LEADS';
1901 WHEN 'OPPT' THEN l_col_name := 'NEW_OPPORTUNITY_AMT'||l_curr_suffix ;
1902 WHEN 'BOOK' THEN l_col_name := 'BOOKED_AMT'||l_curr_suffix;
1903 END CASE;
1904
1905 CASE l_url_viewby
1906
1907 WHEN 'TARGET SEGMENT+TARGET SEGMENT' THEN
1908
1909 l_from := 'bim_sgmt_act_mv a ';
1910
1911 WHEN 'MEDIA+MEDIA' THEN
1912
1913 l_from := 'bim_sgmt_act_ch_mv a ';
1914 l_col_where := ' AND activity_id = '||l_url_viewbyid;
1915
1916 WHEN 'CAMPAIGN+CAMPAIGN' THEN
1917
1918 l_from := 'bim_sgmt_act_sh_mv a ';
1919 l_col_where := ' AND source_code_id = '||l_url_viewbyid;
1920
1921 WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN
1922
1923 l_from := 'bim_sgmt_act_mv a ';
1924
1925 END CASE;
1926
1927 IF l_sgmt_id is null THEN
1928 l_where := 'AND dn.immediate_parent_id is null AND dn.segment_id = a.segment_id';
1929 l_from := l_from||' , bim_i_sgmt_denorm dn ';
1930 ELSE
1931 l_where := 'AND a.segment_id=:l_sgmt_id';
1932
1933 END IF;
1934
1935 IF l_cat_id IS NOT NULL THEN
1936
1937 l_pc_from := ' , eni_denorm_hierarchies edh , mtl_default_category_sets mdcs';
1938 l_pc_where := ' AND a.category_id = edh.child_id
1939 AND edh.object_type = ''CATEGORY_SET''
1940 AND edh.object_id = mdcs.category_set_id
1941 AND mdcs.functional_area_id = 11
1942 AND edh.dbi_flag = ''Y''
1943 AND edh.parent_id = :l_cat_id ';
1944
1945 ELSE
1946 l_pc_where := ' AND a.category_id = -9 ';
1947
1948 END IF;
1949
1950 l_sqltext:='
1951 SELECT
1952 VIEWBY,
1953 bim_attribute2,
1954 bim_attribute3
1955 FROM
1956 ( SELECT
1957 fii.name VIEWBY,
1958 '||l_col_name||' bim_attribute2,
1959 CASE WHEN '||l_col_name||'_p =0 THEN NULL ELSE (('||l_col_name||'-'||l_col_name||'_p)/'||l_col_name||'_p)*100 END bim_attribute3,
1960 fii.start_date startdate
1961 FROM
1962 (
1963 SELECT
1964 dates.start_date start_date,
1965 SUM(DECODE(dates.period, ''C'','||l_col_name||',0)) '||l_col_name||',
1966 SUM(DECODE(dates.period, ''P'','||l_col_name||',0)) '||l_col_name||'_p
1967 FROM
1968 (
1969 SELECT
1970 fii.start_date START_DATE,
1971 ''C'' PERIOD,
1972 LEAST(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
1973 FROM '||l_period_type||' fii
1974 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
1975 UNION ALL
1976 SELECT
1977 p2.start_date START_DATE,
1978 ''P'' PERIOD,
1979 p1.report_date REPORT_DATE
1980 FROM
1981 (
1982 SELECT report_date, rownum id
1983 FROM
1984 (
1985 SELECT
1986 LEAST(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE
1987 FROM ' ||l_period_type||' fii
1988 WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND &BIS_PREVIOUS_ASOF_DATE
1989 ORDER BY fii.start_date DESC
1990 )
1991
1992 ) p1
1993 ,
1994 (
1995 SELECT start_date,rownum id
1996 FROM
1997 (
1998 SELECT
1999 fii.start_date START_DATE
2000 FROM ' ||l_period_type||' fii
2001 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
2002 ORDER BY fii.start_date DESC
2003 )
2004 ) p2
2005 WHERE p1.id(+) = p2.id
2006 ) dates
2007 ,'||l_from||l_pc_from||' ,fii_time_rpt_struct_v cal
2008 WHERE cal.report_date = dates.report_date
2009 AND bitand(cal.record_type_id,:l_record_type) = cal.record_type_id
2010 AND a.time_id = cal.time_id
2011 AND a.period_type_id = cal.period_type_id '
2012 ||l_where||l_pc_where||l_col_where||' group by dates.start_date
2013 )s,'|| l_period_type||' fii
2014 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
2015 AND fii.start_date = s.start_date(+) ) a Order By a.startdate
2016 ';
2017
2018 x_custom_sql := l_sqltext;
2019 l_custom_rec.attribute_name := ':l_record_type';
2020 l_custom_rec.attribute_value := l_record_type_id;
2021 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
2022 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
2023 x_custom_output.EXTEND;
2024 x_custom_output (1) := l_custom_rec;
2025 l_custom_rec.attribute_name := ':l_sgmt_id';
2026 l_custom_rec.attribute_value := l_sgmt_id;
2027 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
2028 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
2029 x_custom_output.EXTEND;
2030 x_custom_output (2) := l_custom_rec;
2031 l_custom_rec.attribute_name := ':l_cat_id';
2032 l_custom_rec.attribute_value := l_cat_id;
2033 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
2034 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
2035 x_custom_output.EXTEND;
2036 x_custom_output (3) := l_custom_rec;
2037
2038
2039
2040 END GET_CAMP_TREND_SQL;
2041
2042 END BIM_SGMT_INTL_UI_PVT;