[Home] [Help]
PACKAGE BODY: APPS.IBW_BI_CUSTOMER_PVT
Source
1 PACKAGE BODY IBW_BI_CUSTOMER_PVT AS
2 /* $Header: ibwbcusb.pls 120.9 2006/02/24 06:08 gjothiku noship $ */
3 /**********************************************************************************************
4 * PROCEDURE : GET_CUST_ACQUIS_TREND_SQL *
5 * PURPOSE : This procedure is used to build the portlet query required *
6 * to render the Web Customer Acquisition Report. *
7 * *
8 * PARAMETERS : *
9 * p_param varchar2 IN: This is used to get the parameters *
10 * selected from the parameter portlet *
11 * x_custom_sql varchar2 OUT This is used to send the portlet query *
12 * x_cusom_output varchar2 OUT This is used to send the bind variables *
13 * *
14 **********************************************************************************************/
15
16 PROCEDURE GET_CUST_ACQUIS_TREND_SQL(
17 p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
18 x_custom_sql OUT NOCOPY VARCHAR2,
19 x_custom_output OUT NOCOPY bis_query_attributes_TBL )
20 IS
21 -- Generic Variables
22
23 l_custom_sql VARCHAR2(15000) ; --Final Sql.
24 l_custom_rec BIS_QUERY_ATTRIBUTES;
25 l_site VARCHAR2(3200); --Site Id
26 l_period_type VARCHAR2(1000); --Period Type
27 l_page VARCHAR2(3200); -- Page
28 l_site_area VARCHAR2(3200); -- Site Area
29 l_referral VARCHAR2(3200); -- Referral Dimension
30 l_campaign VARCHAR2(3200);
31 l_cust_class VARCHAR2(3200); -- Customer Classification
32 l_cust VARCHAR2(3200); -- Customer
33 l_prod_catg VARCHAR2(3200); -- Product Category
34 l_prod VARCHAR2(3200); -- Product
35 l_view_by VARCHAR2(3200);
36 l_site_from VARCHAR2(3200);
37 l_site_where VARCHAR2(3200);
38 l_from VARCHAR2(3200);
39 l_where VARCHAR2(3200);
40 l_outer_select VARCHAR2(3200);
41 l_outer_where VARCHAR2(3200);
42
43 -- Specific Variables
44
45 l_currency VARCHAR2(3200) ; -- Currency
46 l_gp_currency VARCHAR2(15); --Global Primary Currency
47 l_gs_currency VARCHAR2(15); --Global Secondary Curr
48
49 --FND Logging
50 l_full_path VARCHAR2(50);
51 gaflog_value VARCHAR2(10);
52
53
54 BEGIN
55
56 --FND Logging
57 l_full_path := 'ibw.plsql.ibwbcusb.get_cust_acquis_trend_sql';
58 --Profile is : FND: Debug Log Enabled and FND: Debug Log Level for Log Level
59 gaflog_value := fnd_profile.value('AFLOG_ENABLED');
60
61 --Fetch all the Parameters into the Local Variables.
62
63 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
64 fnd_log.string(fnd_log.level_statement,l_full_path,'BEGIN');
65 END IF;
66
67 IF gaflog_value ='Y' AND (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
68 fnd_log.string(fnd_log.level_procedure,l_full_path,'Before the Call to UTL Package');
69 END IF;
70
71 IBW_BI_UTL_PVT.GET_PAGE_PARAMETERS
72 (
73 P_PMV_PARAMETERS => p_pmv_parameters,
74 X_PERIOD_TYPE => l_period_type,
75 X_SITE => l_site,
76 X_CURRENCY_CODE => l_currency,
77 X_SITE_AREA => l_site_area, --Not Wanted
78 X_PAGE => l_page, --Not Wanted
79 X_VIEW_BY => l_view_by, --Not Wanted
80 X_CAMPAIGN => l_campaign, --Not Wanted
81 X_REFERRAL => l_referral, --Not Wanted
82 X_PROD_CAT => l_prod_catg, --Not Wanted
83 X_PROD => l_prod, --Not Wanted
84 X_CUST_CLASS => l_cust_class,
85 X_CUST => l_cust --Not Wanted
86 );
87
88 IF gaflog_value ='Y' AND (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
89 fnd_log.string(fnd_log.level_procedure,l_full_path,'After the Call to UTL Package');
90 END IF;
91
92 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
93 fnd_log.string(fnd_log.level_statement,l_full_path,'l_site : ' || l_site ||' l_cust : '|| l_cust || ' l_currency : ' || l_currency || ' l_period_type : ' || l_period_type );
94 END IF;
95
96 --Initializing section starts
97 l_gp_currency := '''FII_GLOBAL1''' ;
98 l_gs_currency := '''FII_GLOBAL2''' ;
99 l_where := '';
100 l_from := '';
101 l_site_where := '';
102 l_site_from := '';
103 l_outer_select := '';
104 l_outer_where := '';
105 l_custom_sql := '';
106 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
107
108 --Initializing section completed
109
110 --Get the Table List
111
112 l_from := ' IBW_VISIT_CUST_TIME_MV CUSTACQUIS_MV' ||
113 ' ,FII_TIME_RPT_STRUCT_V CAL' ||
114 ' ,FII_PARTY_MKT_CLASS CUST_CLASS_MAP'; -- This is a mapping table between customer classification and customers ( party_id )
115
116 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
117 fnd_log.string(fnd_log.level_statement,l_full_path,'l_from : ' || l_from );
118 END IF;
119
120 -- Initialising where clause based for time dimension
121
122 l_where := l_where ||
123 ' cal.report_date = dates.report_date AND
124 CUSTACQUIS_MV.time_id = cal.time_id AND
125 CUSTACQUIS_MV.period_type_id = cal.period_type_id AND
126 bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id AND
127 cal.CALENDAR_ID = -1 ';
128
129 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
130 fnd_log.string(fnd_log.level_statement,l_full_path,'l_where : ' || l_where );
131 END IF;
132
133 -- Initialising where clause based on the parameter selection
134 -- The site where clause is kept in a seperate variable because New Web Customers through
135 -- all channels does not depend on site dimension.
136
137 IF upper(l_site) <> 'ALL' THEN
138 l_site_where := l_site_where ||
139 ' AND CUSTACQUIS_MV.SITE_ID in (&SITE+SITE)' ;
140
141 ELSE
142 l_site_from := l_site_from || ', IBW_BI_MSITE_DIMN_V SITE';
143 l_site_where := l_site_where ||
144 ' AND CUSTACQUIS_MV.SITE_ID = SITE.ID ';
145 END IF;
146
147 IF upper(l_cust_class) <> 'ALL' THEN
148 l_where := l_where ||
149 ' AND CUSTACQUIS_MV.CUSTOMER_ID = CUST_CLASS_MAP.PARTY_ID '||
150 ' AND CUST_CLASS_MAP.class_code in (&FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
151 ELSE
152 l_from := l_from || ' ,FII_TRADING_PARTNER_MKTCLASS_V CUSTCLASS';
153 l_where := l_where ||
154 ' AND CUSTACQUIS_MV.CUSTOMER_ID = CUST_CLASS_MAP.PARTY_ID '||
155 ' AND CUST_CLASS_MAP.class_code = CUSTCLASS.ID ';
156 END IF;
157
158 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
159 fnd_log.string(fnd_log.level_statement,l_full_path,' l_from : ' || l_from || ' l_where : ' || l_where ||' l_site_from : '|| l_site_from ||' l_site_where : '|| l_site_where);
160 END IF;
161
162 -- Initialising the outer select clause. For any ratios divide by zero issue is also handled
163
164 -- Not returning value for IBW_VAL2 and IBW_VAL4 as per bug # 4772549.
165
166
167 l_outer_select := ' time_dim.NAME VIEWBY,
168 NVL(WEB_REG_P,0) IBW_VAL8, --Bug#4727078 Issue#:21
169 NVL(WEB_REG_C,0) IBW_VAL1,
170 NVL(WEB_CUST_P,0) IBW_VAL9, --Bug#4727078 Issue#:21
171 NVL(WEB_CUST_C,0) IBW_VAL3,
172 (DECODE(NVL(WEB_CUST_ALL,0),0,null,
173 NVL(WEB_CUST_C,0)/WEB_CUST_ALL)*100) IBW_VAL5,
174 NVL(BOOKED_AMOUNT_C,0) IBW_VAL6,
175 DECODE(NVL(BOOKED_ORDERS_C,0),0,null,
176 NVL(BOOKED_AMOUNT_C,0)/BOOKED_ORDERS_C) IBW_VAL7
177 FROM (
178 SELECT
179 start_date START_DATE,
180 SUM(WEB_REG_C) WEB_REG_C,
181 SUM(WEB_REG_P) WEB_REG_P,
182 SUM(WEB_CUST_C) WEB_CUST_C,
183 SUM(WEB_CUST_P) WEB_CUST_P,
184 SUM(WEB_CUST_ALL) WEB_CUST_ALL,
185 SUM(BOOKED_AMOUNT_C) BOOKED_AMOUNT_C,
186 SUM(BOOKED_ORDERS_C) BOOKED_ORDERS_C ';
187
188 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
189 fnd_log.string(fnd_log.level_statement,l_full_path,'l_outer_select : ' || l_outer_select );
190 END IF;
191
192 -- Initialising the outer where clause
193
194 l_outer_where := ' time_dim.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE AND
195 time_dim.start_date = s.start_date(+) ';
196
197 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
198 fnd_log.string(fnd_log.level_statement,l_full_path,'l_outer_where : ' || l_outer_where );
199 END IF;
200
201 /*************************************************************************/
202 /* IBW_VAL1 : Web Registrations */
203 /* IBE_VAL2 : Change */
204 /* IBW_VAL3 : New Web Customers */
205 /* IBW_VAL4 : Change */
206 /* IBW_VAL5 : Percent New Web Customers */
207 /* IBW_VAL6 : Booked Web Orders Amount */
208 /* IBW_VAL7 : Average Web Order Value */
209 /* IBW_VAL8 : Prior Registrations */
210 /* IBW_VAL9 : Prior New Web Customers */
211 /*************************************************************************/
212 /* BIS parameters used */
213 /*************************************************************************/
214 /* &BIS_CURRENT_ASOF_DATE Current as of date */
215 /* &BIS_CURRENT_REPORT_START_DATE Start date based on report compare */
216 /* to parameter */
217 /* &BIS_PREVIOUS_ASOF_DATE Previous As of date */
218 /* &BIS_PREVIOUS_REPORT_START_DATE Previous start date based on report*/
219 /* compare to parameter */
220 /* &BIS_NESTED_PATTERN Used in the bitand function to */
221 /* select appropriate record_type_id */
222 /* based on the period selected */
223 /*************************************************************************/
224
225 -- The inner most select clause has two UNION ALLs
226 -- The first UNION ALL fetches Web Registrations,Change,New Web Customers,Change,Booked Web Orders Amount, Average Web Order Value
227 -- The second UNION ALL fetches new web customers through all channels which is used to calculate Percent New Web Customers
228
229 l_custom_sql := ' SELECT '|| l_outer_select ||
230 ' FROM ' ||
231 '(
232 SELECT
233 dates.start_date START_DATE,
234 decode(dates.period, ''C'',web_registrations,0) WEB_REG_C,
235 decode(dates.period, ''P'',web_registrations,0) WEB_REG_P,
236 decode(dates.period, ''C'',new_web_customers,0) WEB_CUST_C,
237 decode(dates.period, ''P'',new_web_customers,0) WEB_CUST_P,
238 NULL WEB_CUST_ALL,
239 decode(dates.period, ''C'',
240 decode(:l_currency,:l_gp_currency,BOOKED_AMT_G,:l_gs_currency,BOOKED_AMT_G1,CURRENCY_CD_F,BOOKED_AMT_F),0) BOOKED_AMOUNT_C,
241 decode(dates.period, ''C'',booked_web_orders,0) BOOKED_ORDERS_C
242 FROM
243 (
244 SELECT
245 time_dim.start_date START_DATE,
246 ''C'' PERIOD,
247 least(time_dim.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
248 FROM '||l_period_type||' time_dim
249 WHERE time_dim.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
250 UNION ALL
251 SELECT
252 p2.start_date START_DATE,
253 ''P'' PERIOD,
254 p1.report_date REPORT_DATE
255 FROM
256 (SELECT
257 least(time_dim.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE,
258 rownum ID
259 FROM ' ||l_period_type||' time_dim
260 WHERE time_dim.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND &BIS_PREVIOUS_ASOF_DATE
261 ORDER BY time_dim.start_date DESC) p1,
262 (SELECT time_dim.start_date START_DATE,
263 rownum ID
264 FROM ' ||l_period_type||' time_dim
265 WHERE time_dim.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
266 ORDER BY time_dim.start_date DESC) p2
267 WHERE p1.id(+) = p2.id) dates, '||l_from || l_site_from ||'
268 WHERE ' || l_where || l_site_where ||
269 ' UNION ALL
270 SELECT
271 dates.start_date START_DATE,
272 null WEB_REG_C,
273 null WEB_REG_P,
274 null WEB_CUST_C,
275 null WEB_CUST_P,
276 DECODE(dates.period, ''C'',new_web_customers_all,null) WEB_CUST_ALL,
277 null BOOKED_AMOUNT_C,
278 null BOOKED_ORDERS_C
279 FROM
280 (
281 SELECT
282 time_dim.start_date START_DATE,
283 ''C'' PERIOD,
284 least(time_dim.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
285 FROM '||l_period_type||' time_dim
289 p2.start_date START_DATE,
286 WHERE time_dim.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
287 UNION ALL
288 SELECT
290 ''P'' PERIOD,
291 p1.report_date REPORT_DATE
292 FROM
293 (
294 SELECT
295 REPORT_DATE,
296 rownum id
297 FROM
298 (
299 SELECT
300 least(time_dim.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE
301 FROM
302 ' ||l_period_type||' time_dim
303 WHERE
304 time_dim.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND &BIS_PREVIOUS_ASOF_DATE
305 ORDER BY time_dim.start_date DESC
306 )
307 ) p1,
308 (
309 SELECT
310 START_DATE,
311 rownum id
312 FROM
313 (
314 SELECT time_dim.start_date START_DATE
315 FROM ' ||l_period_type||' time_dim
316 WHERE time_dim.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
317 ORDER BY time_dim.start_date DESC
318 )
319 ) p2
320 WHERE p1.id(+) = p2.id) dates, '|| l_from ||'
321 WHERE '|| l_where ||
322 ' AND CUSTACQUIS_MV.site_id = -9999 )
323 GROUP BY start_date
324 ) s,'|| l_period_type||' time_dim
325 WHERE '|| l_outer_where ||
326 'ORDER BY time_dim.start_date';
327
328
329 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
330 fnd_log.string(fnd_log.level_statement,l_full_path,'l_custom_sql : ' || l_custom_sql );
331 END IF;
332
333 x_custom_sql := l_custom_sql;
334
335 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
336
337 l_custom_rec.attribute_name := ':l_currency' ;
338 l_custom_rec.attribute_value:= l_currency;
339 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
340 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
341 x_custom_output.EXTEND;
342 x_custom_output(1) := l_custom_rec;
343
344 l_custom_rec.attribute_name := ':l_gp_currency' ;
345 l_custom_rec.attribute_value:= l_gp_currency;
346 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
347 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
348 x_custom_output.EXTEND;
349 x_custom_output(2) := l_custom_rec;
350
351 l_custom_rec.attribute_name := ':l_gs_currency' ;
352 l_custom_rec.attribute_value:= l_gs_currency;
353 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
354 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
355 x_custom_output.EXTEND;
356 x_custom_output(3) := l_custom_rec;
357
358 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
359 fnd_log.string(fnd_log.level_statement,l_full_path,'END');
360 END IF;
361
362 EXCEPTION
363 WHEN OTHERS THEN
364 if (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
365 fnd_log.string(fnd_log.level_unexpected,l_full_path,SQLERRM);
366 end if;
367
368 END GET_CUST_ACQUIS_TREND_SQL;
369
370 /**********************************************************************************************
371 * PROCEDURE : GET_CUST_ACTY_TREND_SQL *
372 * PURPOSE : This procedure is used to build the portlet query required *
373 * to render the Web Customer Activity Trend Report. *
374 * *
375 * PRARAMETERS : *
376 * p_param varchar2 IN: This is used to get the parameters *
377 * selected from the parameter portlet *
378 * x_custom_sql varchar2 OUT This is used to send the portlet query *
379 * x_cusom_output varchar2 OUT This is used to send the bind variables *
380 * *
381 **********************************************************************************************/
382
383 PROCEDURE GET_CUST_ACTY_TREND_SQL(
384 p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
385 x_custom_sql OUT NOCOPY VARCHAR2,
386 x_custom_output OUT NOCOPY bis_query_attributes_TBL)
387 IS
388 -- Generic Variables
389
390 l_custom_sql VARCHAR2(15000) ; --Final Sql.
391 l_custom_rec BIS_QUERY_ATTRIBUTES;
392 l_site VARCHAR2(3200); --Site Id
393 l_period_type VARCHAR2(1000); --Period Type
394 l_page VARCHAR2(3200); -- Page
395 l_site_area VARCHAR2(3200); -- Site Area
396 l_referral VARCHAR2(3200); -- Referral Dimension
397 l_campaign VARCHAR2(3200);
398 l_cust_class VARCHAR2(3200); -- Customer Classification
399 l_cust VARCHAR2(3200); -- Customer
400 l_prod_catg VARCHAR2(3200); -- Product Category
401 l_prod VARCHAR2(3200); -- Product
402 l_view_by VARCHAR2(3200);
403 l_site_from VARCHAR2(3200);
404 l_site_where VARCHAR2(3200);
405 l_from VARCHAR2(3200);
406 l_where VARCHAR2(3200);
410 -- Specific Variables
407 l_outer_select VARCHAR2(3200);
408 l_outer_where VARCHAR2(3200);
409
411
412 l_currency VARCHAR2(3200) ; -- Currency
413 l_gp_currency VARCHAR2(15); --Global Primary Currency
414 l_gs_currency VARCHAR2(15); --Global Secondary Curr
415
416 --FND Logging
417 l_full_path VARCHAR2(50);
418 gaflog_value VARCHAR2(10);
419
420
421 BEGIN
422
423 --FND Logging
424 l_full_path := 'ibw.plsql.ibwbcusb.get_cust_acty_trend_sql';
425 --Profile is : FND: Debug Log Enabled and FND: Debug Log Level for Log Level
426 gaflog_value := fnd_profile.value('AFLOG_ENABLED');
427
428 --Fetch all the Parameters into the Local Variables.
429
430 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
431 fnd_log.string(fnd_log.level_statement,l_full_path,'BEGIN');
432 END IF;
433
434 IF gaflog_value ='Y' AND (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
435 fnd_log.string(fnd_log.level_procedure,l_full_path,'Before the Call to UTL Package');
436 END IF;
437
438 IBW_BI_UTL_PVT.GET_PAGE_PARAMETERS
439 (
440 P_PMV_PARAMETERS => p_pmv_parameters,
441 X_PERIOD_TYPE => l_period_type,
442 X_SITE => l_site,
443 X_CURRENCY_CODE => l_currency,
444 X_SITE_AREA => l_site_area, --Not Wanted
445 X_PAGE => l_page, --Not Wanted
446 X_VIEW_BY => l_view_by, --Not Wanted
447 X_CAMPAIGN => l_campaign, --Not Wanted
448 X_REFERRAL => l_referral, --Not Wanted
449 X_PROD_CAT => l_prod_catg, --Not Wanted
450 X_PROD => l_prod, --Not Wanted
451 X_CUST_CLASS => l_cust_class,
452 X_CUST => l_cust
453 );
454
455 IF gaflog_value ='Y' AND (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
456 fnd_log.string(fnd_log.level_procedure,l_full_path,'After the Call to UTL Package');
457 END IF;
458
459 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
460 fnd_log.string(fnd_log.level_statement,l_full_path,'l_site : ' || l_site ||' l_cust : '|| l_cust ||' l_cust_class : '|| l_cust_class || ' l_currency : ' || l_currency || ' l_period_type : ' || l_period_type );
461 END IF;
462
463 --Initializing section starts
464
465 l_gp_currency := '''FII_GLOBAL1''' ;
466 l_gs_currency := '''FII_GLOBAL2''' ;
467 l_where := '';
468 l_from := '';
469 l_site_where := '';
470 l_site_from := '';
471 l_custom_sql := '';
472 l_outer_select := '';
473 l_outer_where := '';
474 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
475
476 --Get the Table List
477
478 l_from := ' IBW_VISIT_CUST_TIME_MV CUSTACQUIS_MV' ||
479 ' ,FII_TIME_RPT_STRUCT_V CAL' ||
480 ' ,FII_PARTY_MKT_CLASS CUST_CLASS_MAP'; -- This is a mapping table between customer classification and customers ( party_id )
481
482 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
483 fnd_log.string(fnd_log.level_statement,l_full_path,'l_from : ' || l_from );
484 END IF;
485
486 -- Initialising where clause based for time dimension
487
488 l_where := l_where ||
489 ' cal.report_date = dates.report_date AND
490 CUSTACQUIS_MV.time_id = cal.time_id AND
491 CUSTACQUIS_MV.period_type_id = cal.period_type_id AND
492 bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id AND
493 cal.CALENDAR_ID = -1 ';
494
495 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
496 fnd_log.string(fnd_log.level_statement,l_full_path,'l_where : ' || l_where);
497 END IF;
498
499 -- Initialising where clause based on the parameter selection
500 -- The site where clause is kept in a seperate variable because Total Booked Orders Amount
501 -- and Total Booked Orders do not depend on site dimension.
502
503 IF upper(l_site) <> 'ALL' THEN
504 l_site_where := l_site_where ||
505 ' AND CUSTACQUIS_MV.SITE_ID in (&SITE+SITE)' ;
506
507 ELSE
508 l_site_from := l_site_from || ', IBW_BI_MSITE_DIMN_V SITE';
509 l_site_where := l_site_where ||
510 ' AND CUSTACQUIS_MV.SITE_ID = SITE.ID ';
511 END IF;
512
513 IF upper(l_cust_class) <> 'ALL' THEN
514 l_where := l_where ||
515 ' AND CUSTACQUIS_MV.CUSTOMER_ID = CUST_CLASS_MAP.PARTY_ID '||
516 ' AND CUST_CLASS_MAP.class_code in (&FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
517 ELSE
518 l_from := l_from || ' ,FII_TRADING_PARTNER_MKTCLASS_V CUSTCLASS';
519 l_where := l_where ||
520 ' AND CUSTACQUIS_MV.CUSTOMER_ID = CUST_CLASS_MAP.PARTY_ID '||
521 ' AND CUST_CLASS_MAP.class_code = CUSTCLASS.ID ';
522 END IF;
523
524 IF upper(l_cust) <> 'ALL' THEN
525 l_where := l_where ||
526 ' AND CUSTACQUIS_MV.CUSTOMER_ID in (&CUSTOMER+PROSPECT)';
527 ELSE
528 l_from := l_from || ' ,ASO_BI_PROSPECT_V CUST';
529 l_where := l_where ||
530 ' AND CUSTACQUIS_MV.CUSTOMER_ID = CUST.ID ';
531 END IF;
532
533 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
534 fnd_log.string(fnd_log.level_statement,l_full_path,' l_from : ' || l_from || ' l_where : ' || l_where ||' l_site_from : '|| l_site_from ||' l_site_where : '|| l_site_where);
535 END IF;
536
537 -- Initialising the outer select clause
538
542 NVL(A_LEADS,0) IBW_VAL3,
539 l_outer_select := ' time_dim.NAME VIEWBY,
540 NVL(VISITS,0) IBW_VAL1,
541 NVL(CARTS,0) IBW_VAL2,
543 DECODE(NVL(CARTS,0)
544 ,0,null,
545 (NVL(ORDERS,0)/
546 NVL(CARTS,0))*100) IBW_VAL4,
547 NVL(BOOKED_ORDERS,0) IBW_VAL5,
548 DECODE(NVL(P_BOOKED_ORDERS_ALL,0),0,null,
549 (NVL(P_BOOKED_ORDERS,0)/
550 NVL(P_BOOKED_ORDERS_ALL,0))*100) IBW_VAL17, --4727078 Issue#21
551 DECODE(NVL(BOOKED_ORDERS_ALL,0),0,null,
552 (NVL(BOOKED_ORDERS,0)/
553 NVL(BOOKED_ORDERS_ALL,0))*100) IBW_VAL6,
554 DECODE(NVL(BOOKED_ORDERS,0),0,null,
555 (NVL(ASSISTED_ORDERS,0)/
556 NVL(BOOKED_ORDERS,0))*100) IBW_VAL7,
557 NVL(P_BOOKED_AMOUNT,0) IBW_VAL18, --4727078 Issue#21
558 NVL(BOOKED_AMOUNT,0) IBW_VAL8,
559 NVL(BOOKED_AMOUNT_ALL,0) IBW_VAL9,
560 NVL(TOTAL_ORDER_INQUIRIES,0) IBW_VAL10,
561 NVL(TOTAL_INVOICE_INQUIRIES,0) IBW_VAL11,
562 NVL(TOTAL_PAYMENT_INQUIRIES,0) IBW_VAL12
563 FROM (
564 SELECT
565 start_date START_DATE,
566 SUM(VISITS) VISITS,
567 SUM(CARTS) CARTS,
568 SUM(A_LEADS) A_LEADS,
569 SUM(ORDERS) ORDERS,
570 SUM(BOOKED_ORDERS) BOOKED_ORDERS,
571 SUM(BOOKED_ORDERS_ALL) BOOKED_ORDERS_ALL,
572 SUM(ASSISTED_ORDERS) ASSISTED_ORDERS,
573 SUM(BOOKED_AMOUNT) BOOKED_AMOUNT,
574 SUM(BOOKED_AMOUNT_ALL) BOOKED_AMOUNT_ALL,
575 SUM(TOTAL_ORDER_INQUIRIES) TOTAL_ORDER_INQUIRIES,
576 SUM(TOTAL_INVOICE_INQUIRIES) TOTAL_INVOICE_INQUIRIES,
577 SUM(TOTAL_PAYMENT_INQUIRIES) TOTAL_PAYMENT_INQUIRIES,
578 SUM(P_BOOKED_AMOUNT) P_BOOKED_AMOUNT, --4727078 Issue#21
579 SUM(P_BOOKED_ORDERS) P_BOOKED_ORDERS, --4727078 Issue#21
580 SUM(P_BOOKED_ORDERS_ALL) P_BOOKED_ORDERS_ALL --4727078 Issue#21
581 ';
582
583 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
584 fnd_log.string(fnd_log.level_statement,l_full_path,'l_outer_select : ' || l_outer_select);
585 END IF;
586
587 -- Initialising the outer where clause
588
589 l_outer_where := ' time_dim.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE AND
590 time_dim.start_date = s.start_date(+) ';
591
592 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
593 fnd_log.string(fnd_log.level_statement,l_full_path,'l_outer_where : ' || l_outer_where);
594 END IF;
595
596 /*************************************************************************/
597 /* IBW_VAL1 : Visits */
598 /* IBE_VAL2 : Carts */
599 /* IBW_VAL3 : A Leads */
600 /* IBW_VAL4 : Cart Conversion */
601 /* IBW_VAL5 : Booked Orders */
602 /* IBW_VAL17 : Prior */
603 /* IBW_VAL6 : Percent Web Orders */
604 /* IBW_VAL7 : Assisted Orders */
605 /* IBW_VAL18 : Prior */
606 /* IBW_VAL8 : Booked Orders Amount */
607 /* IBW_VAL9 : Total Booked Orders Amount */
608 /* IBW_VAL10 : Order Status */
609 /* IBW_VAL11 : Invoice */
610 /* IBW_VAL12 : Payment */
611 /*************************************************************************/
612 /* BIS parameters used */
613 /*************************************************************************/
614 /* &BIS_CURRENT_ASOF_DATE Current as of date */
615 /* &BIS_CURRENT_REPORT_START_DATE Start date based on report compare */
616 /* to parameter */
617 /* &BIS_PREVIOUS_ASOF_DATE Previous As of date */
618 /* &BIS_PREVIOUS_REPORT_START_DATE Previous start date based on report*/
619 /* compare to parameter */
620 /* &BIS_NESTED_PATTERN Used in the bitand function to */
621 /* select appropriate record_type_id */
622 /* based on the period selected */
623 /*************************************************************************/
624 -- The inner most select clause has two UNION ALLs
625 -- The first UNION ALL fetches Visits,Carts,A Leads,Cart Conversion,Booked Orders,Assisted Orders,Booked Orders Amount,Order Status,Invoice,Payment
629 l_custom_sql := ' SELECT '|| l_outer_select ||
626 -- The second UNION ALL fetches Total Booked Orders which is used to calculate Percent Web Orders and Total Booked Orders Amount
627
628
630 ' FROM ' ||
631 '(
632 SELECT
633 dates.start_date START_DATE,
634 decode(dates.period, ''C'',visits,0) VISITS,
635 decode(dates.period, ''C'',carts,0) CARTS,
636 decode(dates.period, ''C'',a_leads,0) A_LEADS,
637 decode(dates.period, ''C'',orders,0) ORDERS,
638 decode(dates.period, ''C'',booked_web_orders,0) BOOKED_ORDERS,
639 NULL BOOKED_ORDERS_ALL,
640 decode(dates.period, ''C'',assisted_web_orders,0) ASSISTED_ORDERS,
641 decode(dates.period, ''C'',
642 decode(:l_currency,:l_gp_currency,booked_amt_g,:l_gs_currency,booked_amt_g1,currency_cd_f,booked_amt_f),0) BOOKED_AMOUNT,
643 NULL BOOKED_AMOUNT_ALL,
644 decode(dates.period, ''C'',total_order_inquiries,0) TOTAL_ORDER_INQUIRIES,
645 decode(dates.period, ''C'',total_invoice_inquiries,0) TOTAL_INVOICE_INQUIRIES,
646 decode(dates.period, ''C'',total_payment_inquiries,0) TOTAL_PAYMENT_INQUIRIES ,
647 decode(dates.period, ''P'',
648 decode(:l_currency,:l_gp_currency,booked_amt_g,:l_gs_currency,booked_amt_g1,currency_cd_f,booked_amt_f),0) P_BOOKED_AMOUNT, --4727078 Issue#21
649 decode(dates.period, ''P'',booked_web_orders,0) P_BOOKED_ORDERS, --4727078 Issue#21
650 NULL P_BOOKED_ORDERS_ALL --4727078 Issue#21
651 FROM
652 (
653 SELECT
654 time_dim.start_date START_DATE,
655 ''C'' PERIOD,
656 least(time_dim.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
657 FROM '||l_period_type||' time_dim
658 WHERE time_dim.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
659 UNION ALL
660 SELECT
661 p2.start_date START_DATE,
662 ''P'' PERIOD,
663 p1.report_date REPORT_DATE
664 FROM
665 (
666 SELECT
667 REPORT_DATE,
668 rownum id
669 FROM
670 (
671 SELECT
672 least(time_dim.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE
673 FROM
674 ' ||l_period_type||' time_dim
675 WHERE
676 time_dim.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND &BIS_PREVIOUS_ASOF_DATE
677 ORDER BY time_dim.start_date DESC
678 )
679 ) p1,
680 (
681 SELECT
682 START_DATE,
683 rownum id
684 FROM
685 (
686 SELECT time_dim.start_date START_DATE
687 FROM ' ||l_period_type||' time_dim
688 WHERE time_dim.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
689 ORDER BY time_dim.start_date DESC
690 )
691 ) p2
692 WHERE p1.id(+) = p2.id) dates, '||l_from || l_site_from ||'
693 WHERE ' || l_where || l_site_where ||
694 ' UNION ALL
695 SELECT
696 dates.start_date START_DATE,
697 NULL VISITS,
698 NULL CARTS,
699 NULL A_LEADS,
700 NULL ORDERS,
701 NULL BOOKED_ORDERS,
702 decode(dates.period, ''C'',total_booked_orders,0) BOOKED_ORDERS_ALL,
703 NULL ASSISTED_ORDERS,
704 NULL BOOKED_AMOUNT,
705 decode(dates.period, ''C'',
706 decode(:l_currency,:l_gp_currency,total_booked_amt_g,:l_gs_currency,total_booked_amt_g1,currency_cd_f,total_booked_amt_f),0) BOOKED_AMOUNT_ALL,
707 NULL TOTAL_ORDER_INQUIRIES,
708 NULL TOTAL_INVOICE_INQUIRIES,
709 NULL TOTAL_PAYMENT_INQUIRIES,
710 NULL P_BOOKED_AMOUNT, --4727078 Issue#21
711 NULL P_BOOKED_ORDERS, --4727078 Issue#21
712 decode(dates.period, ''P'',total_booked_orders,0) P_BOOKED_ORDERS_ALL --4727078 Issue#21
713 FROM
714 (
715 SELECT
716 time_dim.start_date START_DATE,
717 ''C'' PERIOD,
718 least(time_dim.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
719 FROM '||l_period_type||' time_dim
720 WHERE time_dim.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
721 UNION ALL
722 SELECT
723 p2.start_date START_DATE,
724 ''P'' PERIOD,
725 p1.report_date REPORT_DATE
726 FROM
727 (
728 SELECT
729 REPORT_DATE,
730 rownum id
731 FROM
732 (
733 SELECT
734 least(time_dim.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE
735 FROM
736 ' ||l_period_type||' time_dim
737 WHERE
741 ) p1,
738 time_dim.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND &BIS_PREVIOUS_ASOF_DATE
739 ORDER BY time_dim.start_date DESC
740 )
742 (
743 SELECT
744 START_DATE,
745 rownum id
746 FROM
747 (
748 SELECT time_dim.start_date START_DATE
749 FROM ' ||l_period_type||' time_dim
750 WHERE time_dim.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
751 ORDER BY time_dim.start_date DESC
752 )
753 ) p2
754 WHERE p1.id(+) = p2.id) dates, '|| l_from ||'
755 WHERE '|| l_where ||
756 ' AND CUSTACQUIS_MV.site_id = -9999 )
757 GROUP BY start_date
758 ) s,'|| l_period_type||' time_dim
759 WHERE '|| l_outer_where ||
760 'ORDER BY time_dim.start_date';
761
762
763 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
764 fnd_log.string(fnd_log.level_statement,l_full_path,'l_custom_sql : ' || l_custom_sql );
765 END IF;
766
767 x_custom_sql := l_custom_sql;
768
769 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
770
771 l_custom_rec.attribute_name := ':l_currency' ;
772 l_custom_rec.attribute_value:= l_currency;
773 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
774 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
775 x_custom_output.EXTEND;
776 x_custom_output(1) := l_custom_rec;
777
778 l_custom_rec.attribute_name := ':l_gp_currency' ;
779 l_custom_rec.attribute_value:= l_gp_currency;
780 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
781 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
782 x_custom_output.EXTEND;
783 x_custom_output(2) := l_custom_rec;
784
785 l_custom_rec.attribute_name := ':l_gs_currency' ;
786 l_custom_rec.attribute_value:= l_gs_currency;
787 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
788 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
789 x_custom_output.EXTEND;
790 x_custom_output(3) := l_custom_rec;
791
792 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
793 fnd_log.string(fnd_log.level_statement,l_full_path,'END');
794 END IF;
795
796 EXCEPTION
797 WHEN OTHERS THEN
798 if (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
799 fnd_log.string(fnd_log.level_unexpected,l_full_path,SQLERRM);
800 end if;
801
802 END GET_CUST_ACTY_TREND_SQL;
803
804 /**********************************************************************************************
805 * PROCEDURE : GET_CUST_ACTY_SQL *
806 * PURPOSE : This procedure is used to build the portlet query required *
807 * to render the Web Customer Activity Report. *
808 * *
809 * PRARAMETERS : *
810 * p_param varchar2 IN: This is used to get the parameters *
811 * selected from the parameter portlet *
812 * x_custom_sql varchar2 OUT This is used to send the portlet query *
813 * x_cusom_output varchar2 OUT This is used to send the bind variables *
814 * *
815 **********************************************************************************************/
816
817 PROCEDURE GET_CUST_ACTY_SQL(
818 p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
819 x_custom_sql OUT NOCOPY VARCHAR2,
820 x_custom_output OUT NOCOPY bis_query_attributes_TBL)
821 IS
822 -- Generic Variables
823
824 l_custom_sql VARCHAR2(15000) ; --Final Sql.
825 l_custom_rec BIS_QUERY_ATTRIBUTES;
826 l_site VARCHAR2(3200); --Site Id
827 l_period_type VARCHAR2(1000); --Period Type
828 l_page VARCHAR2(3200); -- Page
829 l_site_area VARCHAR2(3200); -- Site Area
830 l_referral VARCHAR2(3200); -- Referral Dimension
831 l_campaign VARCHAR2(3200);
832 l_cust_class VARCHAR2(3200); -- Customer Classification
833 l_cust VARCHAR2(3200); -- Customer
834 l_prod_catg VARCHAR2(3200); -- Product Category
835 l_prod VARCHAR2(3200); -- Product
836 l_view_by VARCHAR2(3200);
837 l_site_from VARCHAR2(3200);
838 l_site_where VARCHAR2(3200);
839 l_from VARCHAR2(3200);
840 l_where VARCHAR2(3200);
841 l_outer_select VARCHAR2(32000);
842 l_inner_select VARCHAR2(3200);
843 l_inner_select_all VARCHAR2(3200);
844 l_inner_group_by VARCHAR2(3200);
845 l_from_all VARCHAR2(3200);
846 l_where_all VARCHAR2(3200);
847 l_middle_select VARCHAR2(3200);
848 l_middle_group_by VARCHAR2(3200);
849 l_having VARCHAR2(3200);
850
851 -- Specific Variables
852
853 l_currency VARCHAR2(3200) ; -- Currency
854 l_gp_currency VARCHAR2(15); --Global Primary Currency
855 l_gs_currency VARCHAR2(15); --Global Secondary Curr
856
857 --FND Logging
858 l_full_path VARCHAR2(50);
859 gaflog_value VARCHAR2(10);
860
861
862 BEGIN
866 --Profile is : FND: Debug Log Enabled and FND: Debug Log Level for Log Level
863
864 --FND Logging
865 l_full_path := 'ibw.plsql.ibwbcusb.get_cust_acty_sql';
867 gaflog_value := fnd_profile.value('AFLOG_ENABLED');
868
869 --Fetch all the Parameters into the Local Variables.
870
871 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
872 fnd_log.string(fnd_log.level_statement,l_full_path,'BEGIN');
873 END IF;
874
875 IF gaflog_value ='Y' AND (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
876 fnd_log.string(fnd_log.level_procedure,l_full_path,'Before the Call to UTL Package');
877 END IF;
878
879 IBW_BI_UTL_PVT.GET_PAGE_PARAMETERS
880 (
881 P_PMV_PARAMETERS => p_pmv_parameters,
882 X_PERIOD_TYPE => l_period_type,
883 X_SITE => l_site,
884 X_CURRENCY_CODE => l_currency,
885 X_SITE_AREA => l_site_area, --Not Wanted
886 X_PAGE => l_page, --Not Wanted
887 X_VIEW_BY => l_view_by, --Not Wanted
888 X_CAMPAIGN => l_campaign, --Not Wanted
889 X_REFERRAL => l_referral, --Not Wanted
890 X_PROD_CAT => l_prod_catg, --Not Wanted
891 X_PROD => l_prod, --Not Wanted
892 X_CUST_CLASS => l_cust_class,
893 X_CUST => l_cust
894 );
895
896 IF gaflog_value ='Y' AND (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
897 fnd_log.string(fnd_log.level_procedure,l_full_path,'After the Call to UTL Package');
898 END IF;
899
900 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
901 fnd_log.string(fnd_log.level_statement,l_full_path,'l_site : ' || l_site ||' l_cust : '|| l_cust ||' l_cust_class : '|| l_cust_class || ' l_currency : ' || l_currency || ' l_view_by : ' || l_view_by );
902 END IF;
903
904 --Initializing section starts
905
906 l_gp_currency := '''FII_GLOBAL1''' ;
907 l_gs_currency := '''FII_GLOBAL2''' ;
908 l_site_from := '';
909 l_site_where := '';
910 l_from := '';
911 l_where := '';
912 l_outer_select := '';
913 l_inner_select := '';
914 l_inner_select_all := '';
915 l_inner_group_by := '';
916 l_from_all := '';
917 l_where_all := '';
918 l_middle_select := '';
919 l_middle_group_by := '';
920 l_having := '';
921 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
922
923
924 --Get the Table List
925
926 l_from := ' IBW_VISIT_CUST_TIME_MV CUSTACTY_MV' ||
927 ' ,FII_TIME_RPT_STRUCT_V CAL' ||
928 ' ,FII_PARTY_MKT_CLASS CUST_CLASS_MAP'; -- This is a mapping table between customer classification and customers ( party_id )
929
933
930 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
931 fnd_log.string(fnd_log.level_statement,l_full_path,'l_from : ' || l_from );
932 END IF;
934 -- Initialising where clause based for time dimension
935
936 l_where := ' CAL.report_date = &BIS_CURRENT_ASOF_DATE ' ||
937 ' AND CAL.period_type_id = CUSTACTY_MV.period_type_id ' ||
938 ' AND BITAND(CAL.RECORD_TYPE_ID,&BIS_NESTED_PATTERN)= CAL.RECORD_TYPE_ID '||
939 ' AND CUSTACTY_MV.TIME_ID = CAL.TIME_ID ' ||
940 ' AND CAL.CALENDAR_ID = -1 ';
941
942 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
943 fnd_log.string(fnd_log.level_statement,l_full_path,'l_where : ' || l_where );
944 END IF;
945
946 l_where_all := l_where;
947
948 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
952 --The Outer Select statement is recorded in this variable.
949 fnd_log.string(fnd_log.level_statement,l_full_path,'l_where_all : ' || l_where_all );
950 END IF;
951
953 -- IBW_G_TOT9 : Grand total for Total Web Orders Amount is non additive across site
954 -- and additive across customer classification and customer. So when view by is site
955 -- sum Over()is not done and for other view bys sum over() is done
956
957 l_outer_select := ' VIEW_BY VIEWBY,
958 VIEW_BY_ID VIEWBYID,
959 NVL(VISITS,0) IBW_VAL1,
960 NVL(CARTS,0) IBW_VAL2,
961 NVL(A_LEADS,0) IBW_VAL3,
962 DECODE(NVL(CARTS,0)
963 ,0,null,
964 (NVL(ORDERS,0)/
965 NVL(CARTS,0))*100) IBW_VAL4,
966 NVL(BOOKED_ORDERS,0) IBW_VAL5,
967 DECODE(NVL(BOOKED_ORDERS_ALL,0),0,null,
968 (NVL(BOOKED_ORDERS,0)/
969 NVL(BOOKED_ORDERS_ALL,0))*100) IBW_VAL6,
970 DECODE(NVL(BOOKED_ORDERS,0),0,null,
971 (NVL(ASSISTED_ORDERS,0)/
972 NVL(BOOKED_ORDERS,0))*100) IBW_VAL7,
973 NVL(BOOKED_AMOUNT,0) IBW_VAL8,
974 NVL(BOOKED_AMOUNT_ALL,0) IBW_VAL9,
975 NVL(TOTAL_ORDER_INQUIRIES,0) IBW_VAL10,
976 NVL(TOTAL_INVOICE_INQUIRIES,0) IBW_VAL11,
977 NVL(TOTAL_PAYMENT_INQUIRIES,0) IBW_VAL12,
978 SUM(NVL(VISITS,0)) over () IBW_G_TOT1,
979 SUM(NVL(CARTS,0)) over () IBW_G_TOT2,
980 SUM(NVL(A_LEADS,0)) over () IBW_G_TOT3,
981 DECODE(SUM(NVL(CARTS,0)) over(),0,null,
982 (SUM(NVL(ORDERS,0)) over()/
983 SUM(NVL(CARTS,0)) over())*100) IBW_G_TOT4,
984 SUM(NVL(BOOKED_ORDERS,0)) over () IBW_G_TOT5,
985 DECODE(DECODE('''|| l_view_by ||''',''SITE+SITE'',
986 NVL(BOOKED_ORDERS_ALL,0),
987 SUM(NVL(BOOKED_ORDERS_ALL,0)) over ())
988 ,0,null,
989 (SUM(NVL(BOOKED_ORDERS,0)) over()/
990 DECODE('''|| l_view_by ||''',''SITE+SITE'',
991 NVL(BOOKED_ORDERS_ALL,0),
992 SUM(NVL(BOOKED_ORDERS_ALL,0)) over ()))*100) IBW_G_TOT6, -- Changed grandtotal so that BOOKED_ORDERS_ALL is not summed up when view by is Site
993 DECODE(SUM(NVL(BOOKED_ORDERS,0)) over()
994 ,0,null,
995 (SUM(NVL(ASSISTED_ORDERS,0)) over()/
996 SUM(NVL(BOOKED_ORDERS,0)) over())*100) IBW_G_TOT7,
997 SUM(NVL(BOOKED_AMOUNT,0)) over () IBW_G_TOT8,
998 DECODE('''|| l_view_by ||''',''SITE+SITE'',
999 NVL(BOOKED_AMOUNT_ALL,0),
1000 SUM(NVL(BOOKED_AMOUNT_ALL,0)) over ()) IBW_G_TOT9,
1001 SUM(NVL(TOTAL_ORDER_INQUIRIES,0)) over () IBW_G_TOT10,
1002 SUM(NVL(TOTAL_INVOICE_INQUIRIES,0)) over () IBW_G_TOT11,
1003 SUM(NVL(TOTAL_PAYMENT_INQUIRIES,0)) over () IBW_G_TOT12 ';
1004
1005 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1006 fnd_log.string(fnd_log.level_statement,l_full_path,'l_outer_select : ' || l_outer_select );
1007 END IF;
1008
1009 --The middle Select statement is recorded in this variable.
1010
1011 l_middle_select := ' VIEW_BY VIEW_BY,
1012 VIEW_BY_ID VIEW_BY_ID,
1013 SUM(VISITS) VISITS,
1014 SUM(CARTS) CARTS,
1015 SUM(A_LEADS) A_LEADS,
1016 SUM(ORDERS) ORDERS,
1017 SUM(BOOKED_ORDERS) BOOKED_ORDERS,
1018 SUM(BOOKED_ORDERS_ALL) BOOKED_ORDERS_ALL,
1019 SUM(ASSISTED_ORDERS) ASSISTED_ORDERS,
1020 SUM(BOOKED_AMOUNT) BOOKED_AMOUNT,
1021 SUM(BOOKED_AMOUNT_ALL) BOOKED_AMOUNT_ALL,
1022 SUM(TOTAL_ORDER_INQUIRIES) TOTAL_ORDER_INQUIRIES,
1023 SUM(TOTAL_INVOICE_INQUIRIES) TOTAL_INVOICE_INQUIRIES,
1024 SUM(TOTAL_PAYMENT_INQUIRIES) TOTAL_PAYMENT_INQUIRIES ';
1025
1026 -- If all the columns in a row are 0 then the whole row is discarded
1027
1028 l_having := ' NVL(SUM(VISITS),0) > 0
1029 OR NVL(SUM(CARTS),0) > 0
1030 OR NVL(SUM(A_LEADS),0) > 0
1031 OR NVL(SUM(ORDERS),0) > 0
1032 OR NVL(SUM(BOOKED_ORDERS),0) > 0
1033 OR NVL(SUM(BOOKED_ORDERS_ALL),0) > 0
1034 OR NVL(SUM(ASSISTED_ORDERS),0) > 0
1035 OR NVL(SUM(BOOKED_AMOUNT),0) > 0
1036 OR NVL(SUM(BOOKED_AMOUNT_ALL),0) > 0
1037 OR NVL(SUM(TOTAL_ORDER_INQUIRIES),0) > 0
1038 OR NVL(SUM(TOTAL_INVOICE_INQUIRIES),0) > 0
1039 OR NVL(SUM(TOTAL_PAYMENT_INQUIRIES),0) > 0 ';
1040
1041 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1045 --The Inner Select statement is recorded in this variable.
1042 fnd_log.string(fnd_log.level_statement,l_full_path,'l_middle_select : ' || l_middle_select );
1043 END IF;
1044
1046 -- The select clause fetches Visits,Carts,A Leads,Cart Conversion,Booked Orders,Assisted Orders,Booked Orders Amount,Order Status,Invoice,Payment
1047 l_inner_select := ' SUM(VISITS) VISITS,
1048 SUM(CARTS) CARTS,
1049 SUM(A_LEADS) A_LEADS,
1050 SUM(ORDERS) ORDERS,
1051 SUM(BOOKED_WEB_ORDERS) BOOKED_ORDERS,
1052 NULL BOOKED_ORDERS_ALL,
1053 SUM(ASSISTED_WEB_ORDERS) ASSISTED_ORDERS,
1054 SUM(decode(:l_currency,:l_gp_currency,booked_amt_g,:l_gs_currency,booked_amt_g1,currency_cd_f,booked_amt_f)) BOOKED_AMOUNT,
1055 NULL BOOKED_AMOUNT_ALL,
1056 SUM(TOTAL_ORDER_INQUIRIES) TOTAL_ORDER_INQUIRIES,
1057 SUM(TOTAL_INVOICE_INQUIRIES) TOTAL_INVOICE_INQUIRIES,
1058 SUM(TOTAL_PAYMENT_INQUIRIES) TOTAL_PAYMENT_INQUIRIES ';
1059
1060 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1061 fnd_log.string(fnd_log.level_statement,l_full_path,'l_inner_select : ' || l_inner_select );
1062 END IF;
1063
1064
1065
1066 -- The select clause fetches Total Booked Orders which is used to calculate Percent Web Orders and Total Booked Orders Amount
1067
1068 l_inner_select_all := ' NULL VISITS,
1069 NULL CARTS,
1070 NULL A_LEADS,
1071 NULL ORDERS,
1072 NULL BOOKED_ORDERS,
1073 SUM(TOTAL_BOOKED_ORDERS) BOOKED_ORDERS_ALL,
1074 NULL ASSISTED_ORDERS,
1075 NULL BOOKED_AMOUNT,
1076 SUM(decode(:l_currency,:l_gp_currency,total_booked_amt_g,:l_gs_currency,total_booked_amt_g1,currency_cd_f,total_booked_amt_f)) BOOKED_AMOUNT_ALL,
1077 NULL TOTAL_ORDER_INQUIRIES,
1078 NULL TOTAL_INVOICE_INQUIRIES,
1079 NULL TOTAL_PAYMENT_INQUIRIES ';
1080
1081 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1082 fnd_log.string(fnd_log.level_statement,l_full_path,'l_inner_select_all : ' || l_inner_select_all );
1083 END IF;
1084
1085 -- Initialising where clause based on the view by parameter
1086 -- Since these two measures Total Booked Orders Amount,Total Booked Orders are not dependent on site dimension
1087 -- the following algorithm is followed for the second union all in the inner nost select clause
1088 -- View by Customer Classification or Customer and sites = 'All'
1089 -- Join with Customer Classification / Customer dimensions but not with Site dimension
1090 -- Where Clause has : MV.site_id = -9999 which indicates records in the MV that hold datathat are independent of site
1091 -- View by Customer Classification / Customer and specific site is selected
1092 -- Join with Customer Classification and Customer dimensions but not with Site dimension
1093 -- Where Clause has : MV.site_id = -9999 which indicates records in the MV that hold datathat are independent of site
1094 -- View by Site and sites = 'All'
1095 -- Join with Site dimension
1096 -- Where Clause has : MV.site_id = -9999 which indicates records in the MV that hold datathat are independent of site
1097 -- View by Site and specific site is selected
1098 -- Join with Site dimension
1099 -- Where Clause has : MV.site_id = -9999 which indicates records in the MV that hold datathat are independent of site
1100 -- and Site_Dimension.site_id = (<<selected sites>>)
1101
1102 l_where_all := l_where_all || ' AND CUSTACTY_MV.SITE_ID = -9999';
1103
1104 IF l_view_by = 'SITE+SITE' THEN --View by is Site
1105
1106 l_inner_select := ' SITE.VALUE VIEW_BY,SITE.ID VIEW_BY_ID, ' || l_inner_select;
1107 l_inner_select_all:= ' SITE.VALUE VIEW_BY,SITE.ID VIEW_BY_ID, ' || l_inner_select_all;
1108 l_from := l_from || ' ,IBW_BI_MSITE_DIMN_V SITE ';
1109 l_where := l_where || ' AND CUSTACTY_MV.SITE_ID = SITE.ID ';
1110 l_inner_group_by := l_inner_group_by || ' SITE.VALUE,SITE.ID ';
1111
1112 ELSIF l_view_by ='FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN --View by is Customer Classification
1113
1114 l_inner_select := ' CUSTCLASS.VALUE VIEW_BY,CUSTCLASS.ID VIEW_BY_ID, ' || l_inner_select;
1115 l_inner_select_all:= ' CUSTCLASS.VALUE VIEW_BY,CUSTCLASS.ID VIEW_BY_ID, ' || l_inner_select_all;
1116
1117 l_from := l_from || ' ,FII_TRADING_PARTNER_MKTCLASS_V CUSTCLASS ';
1118 l_where := l_where || ' AND CUSTACTY_MV.CUSTOMER_ID = CUST_CLASS_MAP.PARTY_ID '||
1119 ' AND CUST_CLASS_MAP.class_code = CUSTCLASS.ID ';
1120 l_where_all := l_where_all || ' AND CUSTACTY_MV.CUSTOMER_ID = CUST_CLASS_MAP.PARTY_ID '||
1121 ' AND CUST_CLASS_MAP.class_code = CUSTCLASS.ID ';
1125
1122 l_inner_group_by := l_inner_group_by || ' CUSTCLASS.VALUE,CUSTCLASS.ID ';
1123
1124 ELSIF l_view_by ='CUSTOMER+PROSPECT' THEN --View by is Customer
1126 l_inner_select := ' CUST.VALUE VIEW_BY,CUST.ID VIEW_BY_ID, ' || l_inner_select;
1127 l_inner_select_all:= ' CUST.VALUE VIEW_BY,CUST.ID VIEW_BY_ID, ' || l_inner_select_all;
1128
1129 l_from := l_from || ' ,ASO_BI_PROSPECT_V CUST ';
1130 l_where := l_where || ' AND CUSTACTY_MV.CUSTOMER_ID = CUST.ID ';
1131 l_where_all := l_where_all || ' AND CUSTACTY_MV.CUSTOMER_ID = CUST.ID ';
1132 l_inner_group_by := l_inner_group_by || ' CUST.VALUE,CUST.ID ';
1133
1134 END IF; --End if for l_view_by
1135
1136 l_from_all := l_from_all || l_from;
1137
1138 -- Initialising where clause based on the parameter selection in dimensions
1139
1140 IF upper(l_site) <> 'ALL' THEN
1141 IF l_view_by = 'SITE+SITE' THEN
1142 l_where_all := l_where_all || ' AND SITE.ID in (&SITE+SITE)';
1143 END IF;
1144 l_where := l_where || ' AND CUSTACTY_MV.SITE_ID in (&SITE+SITE)' ;
1145 ELSE
1146 IF l_view_by <> 'SITE+SITE' THEN
1147 l_from := l_from || ' ,IBW_BI_MSITE_DIMN_V SITE ';
1148 l_where := l_where || ' AND CUSTACTY_MV.SITE_ID = SITE.ID ';
1149 END IF;
1150 END IF;
1151
1152 IF upper(l_cust_class) <> 'ALL' THEN
1153 l_where := l_where ||
1154 ' AND CUSTACTY_MV.CUSTOMER_ID = CUST_CLASS_MAP.PARTY_ID '||
1155 ' AND CUST_CLASS_MAP.class_code in (&FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
1156 l_where_all:= l_where_all ||
1157 ' AND CUSTACTY_MV.CUSTOMER_ID = CUST_CLASS_MAP.PARTY_ID '||
1161 l_from := l_from || ' ,FII_TRADING_PARTNER_MKTCLASS_V CUSTCLASS ';
1158 ' AND CUST_CLASS_MAP.class_code in (&FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
1159 ELSE
1160 IF l_view_by <> 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN --View by is Customer Classification
1162 l_where := l_where || ' AND CUSTACTY_MV.CUSTOMER_ID = CUST_CLASS_MAP.PARTY_ID '||
1163 ' AND CUST_CLASS_MAP.class_code = CUSTCLASS.ID ';
1164 l_from_all := l_from_all || ' ,FII_TRADING_PARTNER_MKTCLASS_V CUSTCLASS ';
1165 l_where_all := l_where_all || ' AND CUSTACTY_MV.CUSTOMER_ID = CUST_CLASS_MAP.PARTY_ID '||
1166 ' AND CUST_CLASS_MAP.class_code = CUSTCLASS.ID ';
1167 END IF;
1168 END IF;
1169
1170 IF upper(l_cust) <> 'ALL' THEN
1171 l_where := l_where ||
1172 ' AND CUSTACTY_MV.CUSTOMER_ID in (&CUSTOMER+PROSPECT)';
1173 l_where_all := l_where_all ||
1174 ' AND CUSTACTY_MV.CUSTOMER_ID in (&CUSTOMER+PROSPECT)';
1175 ELSE
1176 IF l_view_by <> 'CUSTOMER+PROSPECT' THEN --View by is Customer
1177 l_from := l_from || ' ,ASO_BI_PROSPECT_V CUST ';
1178 l_where := l_where || ' AND CUSTACTY_MV.CUSTOMER_ID = CUST.ID ';
1179 l_from_all := l_from_all || ' ,ASO_BI_PROSPECT_V CUST ';
1180 l_where_all := l_where_all || ' AND CUSTACTY_MV.CUSTOMER_ID = CUST.ID ';
1181 END IF;
1182 END IF;
1183
1184 l_middle_group_by := ' VIEW_BY,VIEW_BY_ID ';
1185
1186 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1187 fnd_log.string(fnd_log.level_statement,l_full_path,'l_inner_select : ' || l_inner_select ||
1188 'l_inner_select_all : ' || l_inner_select_all || 'l_from : ' || l_from || 'l_where : ' ||
1189 l_where || 'l_from_all : ' || l_from_all || 'l_where_all : ' || l_where_all || 'l_inner_group_by : '
1190 || l_inner_select_all || 'l_middle_group_by : ' || l_middle_group_by );
1191 END IF;
1192
1193 /*************************************************************************/
1194 /* IBW_VAL1 : Visits */
1195 /* IBE_VAL2 : Carts */
1196 /* IBW_VAL3 : A Leads */
1197 /* IBW_VAL4 : Cart Conversion */
1198 /* IBW_VAL5 : Booked Orders */
1199 /* IBW_VAL6 : Percent Web Orders */
1200 /* IBW_VAL7 : Assisted Orders */
1201 /* IBW_VAL8 : Booked Orders Amount */
1202 /* IBW_VAL9 : Total Booked Orders Amount */
1203 /* IBW_VAL10 : Order Status */
1204 /* IBW_VAL11 : Invoice */
1205 /* IBW_VAL12 : Payment */
1206 /*************************************************************************/
1207 /* BIS parameters used */
1211 /* select appropriate record_type_id */
1208 /*************************************************************************/
1209 /* &BIS_CURRENT_ASOF_DATE Current as of date */
1210 /* &BIS_NESTED_PATTERN Used in the bitand function to */
1212 /* based on the period selected */
1213 /*************************************************************************/
1214
1215
1216 l_custom_sql := 'SELECT ' || l_outer_select ||
1217 ' FROM ' ||
1218 ' (SELECT ' || l_middle_select ||
1219 ' FROM ' ||
1220 ' (SELECT ' || l_inner_select ||
1221 ' FROM ' || l_from ||
1222 ' WHERE ' || l_where ||
1223 ' GROUP BY ' || l_inner_group_by ||
1224 ' UNION ALL ' ||
1225 ' SELECT ' || l_inner_select_all ||
1226 ' FROM ' || l_from_all ||
1227 ' WHERE ' || l_where_all ||
1228 ' GROUP BY ' || l_inner_group_by ||
1229 ' ) ' ||
1230 ' GROUP BY ' || l_middle_group_by ||
1231 ' HAVING ' || l_having ||
1232 ' ) ' ||
1233 ' &ORDER_BY_CLAUSE ';
1234
1235 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1236 fnd_log.string(fnd_log.level_statement,l_full_path,'l_custom_sql : ' || l_custom_sql );
1237 END IF;
1238
1239 x_custom_sql := l_custom_sql;
1240
1241 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
1242
1243 l_custom_rec.attribute_name := ':l_currency' ;
1244 l_custom_rec.attribute_value:= l_currency;
1245 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1246 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1247 x_custom_output.EXTEND;
1248 x_custom_output(1) := l_custom_rec;
1249
1250 l_custom_rec.attribute_name := ':l_gp_currency' ;
1251 l_custom_rec.attribute_value:= l_gp_currency;
1252 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1253 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1254 x_custom_output.EXTEND;
1255 x_custom_output(2) := l_custom_rec;
1256
1257 l_custom_rec.attribute_name := ':l_gs_currency' ;
1258 l_custom_rec.attribute_value:= l_gs_currency;
1259 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1260 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1261 x_custom_output.EXTEND;
1262 x_custom_output(3) := l_custom_rec;
1263
1264 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1265 fnd_log.string(fnd_log.level_statement,l_full_path,'END');
1266 END IF;
1267
1268 EXCEPTION
1269 WHEN OTHERS THEN
1270 if (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
1271 fnd_log.string(fnd_log.level_unexpected,l_full_path,SQLERRM);
1272 end if;
1273
1274 END GET_CUST_ACTY_SQL;
1275
1276 END IBW_BI_CUSTOMER_PVT;