[Home] [Help]
PACKAGE BODY: APPS.BIM_ACQ_PKG
Source
1 PACKAGE BODY BIM_ACQ_PKG AS
2 /*$Header: bimacqub.pls 115.18 2001/08/13 16:09:47 pkm ship $*/
3
4 PROCEDURE BIM_CMP_ACQ_POPULATE
5 (
6 p_campaign_id number DEFAULT NULL,
7 p_campaign_type_id varchar2 DEFAULT NULL,
8 p_campaign_status_id number DEFAULT NULL,
9 p_period_type varchar2 DEFAULT NULL,
10 p_start_date date DEFAULT NULL,
11 p_end_date date DEFAULT NULL,
12 p_media_id number DEFAULT NULL,
13 p_channel_id varchar2 DEFAULT NULL,
14 p_market_segment_id number DEFAULT NULL,
15 p_sales_channel_code varchar2 DEFAULT NULL,
16 p_interest_type_id number DEFAULT NULL,
17 p_primary_interest_code_id number DEFAULT NULL,
18 p_secondary_interest_code_id number DEFAULT NULL,
19 p_geography_code varchar2 DEFAULT NULL,
20 p_view_by varchar2 DEFAULT NULL,
21 p_drill_down varchar2 DEFAULT 'Y'
22 ) IS
23
24
25 v_num_rows_inserted integer;
26 v_num_rows_updated integer;
27 v_view_by varchar2(50);
28 from_clause varchar2(100);
29 where_clause varchar2(250);
30
31 l_sql_from varchar2(5000);
32 l_sql_where varchar2(5000);
33 l_sql_group_by varchar2(5000);
34 l_sql_order_by varchar2(5000);
35 l_sql_insert_stm varchar2(5000);
36 l_sql_stmt varchar2(5000);
37 v_all_name varchar2(80);
38
39 l_sql_update_stm varchar2(5000);
40 l_sql_upd_stmt varchar2(5000);
41
42 BEGIN
43
44 l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure3, subject_name, view_by_name)';
45
46 l_sql_insert_stm := l_sql_insert_stm
47 || ' SELECT ' ;
48
49 l_sql_insert_stm := l_sql_insert_stm
50 || ' count(distinct a.cust_account_id) , ';
51
52 l_sql_insert_stm := l_sql_insert_stm
53 || ' nvl(sum(a.initiated_revenue), 0 ) , ';
54
55 /***************/
56
57 l_sql_where := l_sql_where || ' where a.period_start_date >= :p_start_date and a.period_end_date <= :p_end_date ' ;
58
59 /***************/
60
61 IF p_campaign_status_id IS NULL THEN
62 l_sql_where:= l_sql_where ||' and :p_campaign_status_id IS NULL ' ;
63 ELSE
64 l_sql_where:= l_sql_where || ' and parent_campaign_status_id = :p_campaign_status_id ';
65 END IF;
66
67 IF p_campaign_type_id IS NULL THEN
68 l_sql_where:= l_sql_where || ' and :p_campaign_type_id IS NULL ';
69 ELSE
70 l_sql_where:= l_sql_where || ' and parent_campaign_type = :p_campaign_type_id ';
71 END IF;
72
73 IF p_campaign_id IS NULL THEN
74 l_sql_where:= l_sql_where|| ' and b.parent_campaign_id in (select campaign_id from bim_dimv_campaigns where parent_campaign_id is null and :p_campaign_id is null ) ';
75 ELSE
76 l_sql_where:= l_sql_where|| ' and b.parent_campaign_id in (select campaign_id from bim_dimv_campaigns where parent_campaign_id = :p_campaign_id ) ';
77 END IF;
78
79
80 /***************/
81
82 IF p_media_id IS NULL THEN
83 l_sql_where:= l_sql_where|| ' and :p_media_id IS NULL ';
84 ELSE
85 l_sql_where:= l_sql_where|| ' and a.media_id = :p_media_id';
86 END IF;
87
88 /***************/
89
90 IF p_channel_id IS NULL THEN
91 l_sql_where:= l_sql_where|| ' and :p_channel_id IS NULL ';
92 ELSE
93 l_sql_where:= l_sql_where|| ' and a.channel_id = :p_channel_id';
94 END IF;
95
96 /***************/
97
98 IF p_sales_channel_code IS NULL THEN
99 l_sql_where:= l_sql_where|| ' and :p_sales_channel_code IS NULL ';
100 ELSE
101 l_sql_where:= l_sql_where|| ' and a.sales_channel_code = :p_sales_channel_code';
102 END IF;
103
104 /***************/
105
106 IF p_market_segment_id IS NULL
107 THEN
108 l_sql_where:= l_sql_where|| ' and :p_market_segment_id IS NULL ';
109 ELSE
110 l_sql_where:= l_sql_where|| ' and a.market_segment_id = :p_market_segment_id';
111 END IF;
112
113 /***************/
114
115 IF p_interest_type_id IS NULL THEN
116 l_sql_where:= l_sql_where|| ' and :p_interest_type_id IS NULL ';
117 ELSE
118 l_sql_where:= l_sql_where|| ' and a.interest_type_id = :p_interest_type_id ';
119 END IF;
120
121 IF p_primary_interest_code_id IS NULL THEN
122 l_sql_where:= l_sql_where||' and :p_primary_interest_code_id IS NULL ';
123 ELSE
124 l_sql_where:= l_sql_where|| ' and a.primary_interest_code_id = :p_primary_interest_code_id ';
125 END IF;
126
127 IF p_secondary_interest_code_id IS NULL THEN
128 l_sql_where:= l_sql_where||' and :p_secondary_interest_code_id IS NULL ';
129 ELSE
130 l_sql_where:= l_sql_where||' and a.secondary_interest_code_id = :p_secondary_interest_code_id ';
131 END IF;
132
133 /***************/
134
135 IF p_geography_code IS NULL THEN
136 l_sql_where:= l_sql_where|| ' and :p_geography_code IS NULL ';
137 ELSE
138 l_sql_where:= l_sql_where|| ' and bill_to_geography_code like :p_geography_code';
139 END IF;
140
141 /***************/
142
143 IF p_view_by = 'ACT'
144 THEN
145 l_sql_insert_stm := l_sql_insert_stm || ' d.campaign_name , med.media_name ';
146 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a ,bim_campaigns_denorm b ,bim_dimv_campaigns d ,bim_dimv_media med ' ;
147 l_sql_where := l_sql_where || ' and a.campaign_id = b.campaign_id and b.parent_campaign_id =d.campaign_id and a.media_id = med.media_id ';
148 l_sql_group_by := l_sql_group_by || ' GROUP BY d.campaign_name , med.media_name ';
149 l_sql_order_by := ' Order by 1,3 desc';
150 v_view_by := ' med.media_name ' ;
151 from_clause := ' ,bim_dimv_media med ' ;
152 ELSIF p_view_by = 'MCH'
153 THEN
154 l_sql_insert_stm := l_sql_insert_stm || ' d.campaign_name , chn.channel_name ';
155 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a, bim_campaigns_denorm b,bim_dimv_campaigns d,bim_dimv_channels chn ' ;
156 l_sql_where := l_sql_where || ' and a.campaign_id = b.campaign_id and b.parent_campaign_id =d.campaign_id and a.channel_id = chn.channel_id ';
157 l_sql_group_by := l_sql_group_by || ' GROUP BY d.campaign_name , chn.channel_name ';
158 l_sql_order_by := ' Order by 1,3 desc';
159 v_view_by := ' chn.channel_name' ;
160 from_clause := ' ,bim_dimv_channels chn ' ;
161 ELSIF p_view_by = 'SCH'
162 THEN
163 l_sql_insert_stm := l_sql_insert_stm || ' d.campaign_name , sch.sales_channel_name ';
164 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a,bim_campaigns_denorm b,bim_dimv_campaigns d,bim_dimv_sales_channels sch ' ;
165 l_sql_where := l_sql_where || ' and a.campaign_id = b.campaign_id and b.parent_campaign_id =d.campaign_id and a.sales_channel_code = sch.sales_channel_code ';
166 l_sql_group_by := l_sql_group_by || ' GROUP BY d.campaign_name , sch.sales_channel_name ';
167 l_sql_order_by := ' Order by 1,3 desc';
168 v_view_by := ' sch.sales_channel_name' ;
169 from_clause := ' ,bim_dimv_sales_channels sch ' ;
170 ELSIF p_view_by = 'MSG'
171 THEN
172 l_sql_insert_stm := l_sql_insert_stm || ' d.campaign_name , mkt.market_segment_name ';
173 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a,bim_campaigns_denorm b,bim_dimv_campaigns d,bim_dimv_market_sgmts mkt ' ;
174 l_sql_where := l_sql_where || ' and a.campaign_id = b.campaign_id and b.parent_campaign_id =d.campaign_id and a.market_segment_id = mkt.market_segment_id';
175 l_sql_group_by := l_sql_group_by || ' GROUP BY d.campaign_name , mkt.market_segment_name ';
176 l_sql_order_by := ' Order by 1,3 desc';
177 v_view_by := ' mkt.market_segment_name' ;
178 from_clause := ' ,bim_dimv_market_sgmts mkt ' ;
179 ELSIF p_view_by = 'PER' THEN
180 l_sql_insert_stm := l_sql_insert_stm || ' d.campaign_name, per.period_name ';
181 l_sql_from:= l_sql_from|| ' from bim_cmpgn_perf_summ a, bim_campaigns_denorm b,bim_dimv_campaigns d ,bim_dimv_periods per,dual ' ;
182 l_sql_where:= l_sql_where|| ' and a.campaign_id = b.campaign_id and b.parent_campaign_id =d.campaign_id and a.period_start_date >= per.start_date
183 and a.period_end_date <= per.end_date and per.period_set_name = jtf_bis_util.ProfileValue(''CRMBIS:PERIOD_SET_NAME'') and per.period_type = :p_period_type ';
184 l_sql_group_by := l_sql_group_by || ' GROUP BY d.campaign_name, per.period_name , per.start_date ';
185 l_sql_order_by := ' Order by d.campaign_name,per.start_date ';
186 v_view_by := ' per.period_name' ;
187 from_clause := ' ,bim_dimv_periods per ' ;
188 END IF;
189
190 -- dbms_output.put_line(p_period_type);
191
192 /***************/
193
194 l_sql_stmt := l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by;
195
196 /*
197 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 1, 150));
198 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 151, 150));
199 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 300, 150));
200 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_from, 1, 150));
201 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 1, 150));
202 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 151, 150));
203 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 300, 150));
204 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 451, 150));
205 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 600, 150));
206 */
207
208 /*
209
210 declare
211 fp utl_file.file_type;
212 location varchar2(100) := '/sqlcom/log/dom1151';
213 file_name varchar2(50) := 'bpp1.sql';
214 begin
215 -- dbms_output.put_line('ENTERED THE SQL BLOCK ');
216 fp := utl_file.fopen(location,file_name,'w');
217 utl_file.put_line(fp,l_sql_insert_stm);
218 utl_file.put_line(fp,l_sql_from);
219 utl_file.put_line(fp,l_sql_where);
220 utl_file.put_line(fp,l_sql_group_by);
221 utl_file.fclose(fp);
222 exception
223 when UTL_FILE.INVALID_MODE then
224 -- dbms_output.put_line('INVALID MODE EXCEPTION');
225 when UTL_FILE.INVALID_PATH then
226 -- dbms_output.put_line('INVALID PATH EXCEPTION');
227 end;
228
229 */
230
231 IF (p_view_by = 'PER') THEN
232
233 -- dbms_OUTPUT.PUT_LINE(' VIEW BY PERIOD ');
234
235 EXECUTE IMMEDIATE l_sql_stmt
236 USING
237 p_start_date,
238 p_end_date,
239 p_campaign_status_id,
240 p_campaign_type_id,
241 p_campaign_id,
242 p_media_id,
243 p_channel_id,
244 p_sales_channel_code,
245 p_market_segment_id,
246 p_interest_type_id,
247 p_primary_interest_code_id,
248 p_secondary_interest_code_id,
249 p_geography_code,
250 p_period_type;
251 ELSE
252
253 -- dbms_OUTPUT.PUT_LINE(' NOT VIEW BY PERIOD ');
254
255 EXECUTE IMMEDIATE l_sql_stmt
256 USING
257 p_start_date,
258 p_end_date,
259 p_campaign_status_id,
260 p_campaign_type_id,
261 p_campaign_id,
262 p_media_id,
263 p_channel_id,
264 p_sales_channel_code,
265 p_market_segment_id,
266 p_interest_type_id,
267 p_primary_interest_code_id,
268 p_secondary_interest_code_id,
269 p_geography_code;
270
271 END IF;
272
273 v_num_rows_inserted := SQL%ROWCOUNT;
274 -- dbms_output.put_line ( 'Number of rows inserted in temp table is : ' || v_num_rows_inserted );
275
276 /************************************************************************************************************/
277
278 l_sql_update_stm := 'UPDATE bim_camp_acqu_summ_temp tmp
279 set tmp.measure4 = (select nvl(sum(a.initiated_revenue),0)
280 from bim_customer_rev_summ a, bim_campaigns_denorm b, bim_dimv_campaigns d ' ;
281
282 l_sql_where := l_sql_where || ' and a.first_order_date >= :p_start_date and a.first_order_date <= :p_end_date ';
283
284 l_sql_where := l_sql_where || ' and tmp.subject_name = d.campaign_name and tmp.view_by_name = ' || v_view_by || ' ) ' ;
285
286 l_sql_upd_stmt := l_sql_update_stm || from_clause || l_sql_where ;
287
288 /*
289 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_update_stm, 1, 150));
290 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_update_stm, 151, 150));
291 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_update_stm, 300, 150));
292 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 1, 150));
293 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 151, 150));
294 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 301, 150));
295 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 451, 150));
296 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 601, 150));
297 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 751, 150));
298 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_group_by, 1, 150));
299 */
300
301 /*
302 declare
303 fp utl_file.file_type;
304 location varchar2(100) := '/sqlcom/log/dom1151';
305 file_name varchar2(50) := 'bpp21.sql';
306 begin
307 -- dbms_output.put_line('ENTERED THE SQL BLOCK ');
308 fp := utl_file.fopen(location,file_name,'w');
309 utl_file.put_line(fp,l_sql_upd_stmt);
310 utl_file.fclose(fp);
311 exception
312 when UTL_FILE.INVALID_MODE then
313 -- dbms_output.put_line('INVALID MODE EXCEPTION');
314 when UTL_FILE.INVALID_PATH then
315 -- dbms_output.put_line('INVALID PATH EXCEPTION');
316 end;
317 */
318
319 /******************/
320
321 IF (p_view_by = 'PER') THEN
322
323 -- dbms_OUTPUT.PUT_LINE(' VIEW BY PERIOD ');
324
325 EXECUTE IMMEDIATE l_sql_upd_stmt
326 USING
327 p_start_date,
328 p_end_date,
329 p_campaign_status_id,
330 p_campaign_type_id,
331 p_campaign_id,
332 p_media_id,
333 p_channel_id,
334 p_sales_channel_code,
335 p_market_segment_id,
336 p_interest_type_id,
337 p_primary_interest_code_id,
338 p_secondary_interest_code_id,
339 p_geography_code,
340 p_period_type,
341 p_start_date,
342 p_end_date;
343 ELSE
344
345 -- dbms_OUTPUT.PUT_LINE(' NOT VIEW BY PERIOD ');
346
347 EXECUTE IMMEDIATE l_sql_upd_stmt
348 USING
349 p_start_date,
350 p_end_date,
351 p_campaign_status_id,
352 p_campaign_type_id,
353 p_campaign_id,
354 p_media_id,
355 p_channel_id,
356 p_sales_channel_code,
357 p_market_segment_id,
358 p_interest_type_id,
359 p_primary_interest_code_id,
360 p_secondary_interest_code_id,
361
362 p_geography_code,
363 p_start_date,
364 p_end_date;
365 END IF;
366
367 /******************/
368
369 UPDATE bim_camp_acqu_summ_temp SET measure2 = measure4*100/measure3;
370
371 v_num_rows_updated := SQL%ROWCOUNT;
372 -- dbms_output.put_line ( 'Number of rows updated in temp table is : ' || v_num_rows_updated );
373
374 END BIM_CMP_ACQ_POPULATE ;
375
376 /*****************************************************************************************************/
377
378 PROCEDURE BIM_CMP_ACQ_SUM_POPULATE
379 (
380 p_campaign_id number DEFAULT NULL,
381 p_campaign_type_id varchar2 DEFAULT NULL,
382 p_campaign_status_id number DEFAULT NULL,
383 p_period_type varchar2 DEFAULT NULL,
384 p_start_date date DEFAULT NULL,
385 p_end_date date DEFAULT NULL,
386 p_media_id number DEFAULT NULL,
387 p_channel_id varchar2 DEFAULT NULL,
388 p_market_segment_id number DEFAULT NULL,
389 p_sales_channel_code varchar2 DEFAULT NULL,
390 p_interest_type_id number DEFAULT NULL,
391 p_primary_interest_code_id number DEFAULT NULL,
392 p_secondary_interest_code_id number DEFAULT NULL,
396 p_all_value varchar2 DEFAULT 'ALL'
393 p_geography_code varchar2 DEFAULT NULL,
394 p_view_by varchar2 DEFAULT NULL,
395 p_drill_down varchar2 DEFAULT 'Y',
397 ) IS
398
399
400 v_num_rows_inserted integer;
401 v_num_rows_updated integer;
402 v_view_by varchar2(50);
403 from_clause varchar2(100);
404 where_clause varchar2(250);
405
406 l_sql_from varchar2(5000);
407 l_sql_where varchar2(5000);
408 l_sql_group_by varchar2(5000);
409 l_sql_order_by varchar2(5000);
410 l_sql_insert_stm varchar2(5000);
411 l_sql_stmt varchar2(5000);
412 v_all_name varchar2(80);
413
414 l_sql_update_stm varchar2(5000);
415 l_sql_upd_stmt varchar2(5000);
416
417 BEGIN
418
419 l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure3, subject_name, view_by_name)';
420
421 l_sql_insert_stm := l_sql_insert_stm
422 || ' SELECT ' ;
423
424 l_sql_insert_stm := l_sql_insert_stm
425 || ' count(distinct a.cust_account_id) , ';
426
427 l_sql_insert_stm := l_sql_insert_stm
428 || ' nvl(sum(a.initiated_revenue), 0 ) , ';
429
430 /***************/
431
432 l_sql_where := l_sql_where || ' where a.period_start_date >= :p_start_date and a.period_end_date <= :p_end_date ' ;
433
434 /***************/
435
436 IF p_campaign_status_id IS NULL THEN
437 l_sql_where:= l_sql_where ||' and :p_campaign_status_id IS NULL ' ;
438 ELSE
439 l_sql_where:= l_sql_where || ' and parent_campaign_status_id = :p_campaign_status_id ';
440 END IF;
441
442 IF p_campaign_type_id IS NULL THEN
443 l_sql_where:= l_sql_where || ' and :p_campaign_type_id IS NULL ';
444 ELSE
445 l_sql_where:= l_sql_where || ' and parent_campaign_type = :p_campaign_type_id ';
446 END IF;
447
448 /***************/
449
450 IF p_media_id IS NULL THEN
451 l_sql_where:= l_sql_where|| ' and :p_media_id IS NULL ';
452 ELSE
453 l_sql_where:= l_sql_where|| ' and a.media_id = :p_media_id';
454 END IF;
455
456 /***************/
457
458 IF p_channel_id IS NULL THEN
459 l_sql_where:= l_sql_where|| ' and :p_channel_id IS NULL ';
460 ELSE
461 l_sql_where:= l_sql_where|| ' and a.channel_id = :p_channel_id';
462 END IF;
463
464 /***************/
465
466 IF p_sales_channel_code IS NULL THEN
467 l_sql_where:= l_sql_where|| ' and :p_sales_channel_code IS NULL ';
468 ELSE
469 l_sql_where:= l_sql_where|| ' and a.sales_channel_code = :p_sales_channel_code';
470 END IF;
471
472 /***************/
473
474 IF p_market_segment_id IS NULL
475 THEN
476 l_sql_where:= l_sql_where|| ' and :p_market_segment_id IS NULL ';
477 ELSE
478 l_sql_where:= l_sql_where|| ' and a.market_segment_id = :p_market_segment_id';
479 END IF;
480
481 /***************/
482
483 IF p_interest_type_id IS NULL THEN
484 l_sql_where:= l_sql_where|| ' and :p_interest_type_id IS NULL ';
485 ELSE
486 l_sql_where:= l_sql_where|| ' and a.interest_type_id = :p_interest_type_id ';
487 END IF;
488
489 IF p_primary_interest_code_id IS NULL THEN
490 l_sql_where:= l_sql_where||' and :p_primary_interest_code_id IS NULL ';
491 ELSE
492 l_sql_where:= l_sql_where|| ' and a.primary_interest_code_id = :p_primary_interest_code_id ';
493 END IF;
494
495 IF p_secondary_interest_code_id IS NULL THEN
496 l_sql_where:= l_sql_where||' and :p_secondary_interest_code_id IS NULL ';
497 ELSE
498 l_sql_where:= l_sql_where||' and a.secondary_interest_code_id = :p_secondary_interest_code_id ';
499 END IF;
500
501 /***************/
502
503 IF p_geography_code IS NULL THEN
504 l_sql_where:= l_sql_where|| ' and :p_geography_code IS NULL ';
505 ELSE
506 l_sql_where:= l_sql_where|| ' and bill_to_geography_code like :p_geography_code';
507 END IF;
508
509 /***************/
510
511 IF p_view_by = 'ACT'
512 THEN
513 l_sql_insert_stm := l_sql_insert_stm || ' :p_all_value , med.media_name ';
514 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a ,bim_campaigns_denorm b ,bim_dimv_media med ' ;
515 l_sql_where := l_sql_where || ' and a.campaign_id = b.campaign_id and b.level_from_parent = 0 and a.media_id = med.media_id ';
516 l_sql_group_by := l_sql_group_by || ' GROUP BY :p_all_value , med.media_name ';
517 l_sql_order_by := ' Order by 1,3 desc';
518 v_view_by := ' med.media_name ' ;
519 from_clause := ' ,bim_dimv_media med ' ;
520 ELSIF p_view_by = 'MCH'
521 THEN
522 l_sql_insert_stm := l_sql_insert_stm || ' :p_all_value , chn.channel_name ';
523 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a, bim_campaigns_denorm b, bim_dimv_channels chn ' ;
524 l_sql_where := l_sql_where || ' and a.campaign_id = b.campaign_id and b.level_from_parent = 0 and a.channel_id = chn.channel_id';
525 l_sql_group_by := l_sql_group_by || ' GROUP BY :p_all_value , chn.channel_name ';
526 l_sql_order_by := ' Order by 1,3 desc';
527 v_view_by := ' chn.channel_name' ;
528 from_clause := ' ,bim_dimv_channels chn ' ;
529 ELSIF p_view_by = 'SCH'
530 THEN
531 l_sql_insert_stm := l_sql_insert_stm || ' :p_all_value , sch.sales_channel_name ';
535 l_sql_order_by := ' Order by 1,3 desc';
532 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a,bim_campaigns_denorm b ,bim_dimv_sales_channels sch ' ;
533 l_sql_where := l_sql_where || ' and a.campaign_id = b.campaign_id and b.level_from_parent = 0 and a.sales_channel_code = sch.sales_channel_code';
534 l_sql_group_by := l_sql_group_by || ' GROUP BY :p_all_value , sch.sales_channel_name ';
536 v_view_by := ' sch.sales_channel_name' ;
537 from_clause := ' ,bim_dimv_sales_channels sch ' ;
538 ELSIF p_view_by = 'MSG'
539 THEN
540 l_sql_insert_stm := l_sql_insert_stm || ' :p_all_value , mkt.market_segment_name ';
541 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a,bim_campaigns_denorm b ,bim_dimv_market_sgmts mkt ' ;
542 l_sql_where := l_sql_where || ' and a.campaign_id = b.campaign_id and b.level_from_parent = 0 and a.market_segment_id = mkt.market_segment_id';
543 l_sql_group_by := l_sql_group_by || ' GROUP BY :p_all_value , mkt.market_segment_name ';
544 l_sql_order_by := ' Order by 1,3 desc';
545 v_view_by := ' mkt.market_segment_name' ;
546 from_clause := ' ,bim_dimv_market_sgmts mkt ' ;
547 ELSIF p_view_by = 'PER' THEN
548 l_sql_insert_stm := l_sql_insert_stm || ' :p_all_value , per.period_name ';
549 l_sql_from:= l_sql_from|| ' from bim_cmpgn_perf_summ a, bim_campaigns_denorm b,bim_dimv_periods per,dual ' ;
550 l_sql_where:= l_sql_where|| ' and a.campaign_id = b.campaign_id and b.level_from_parent = 0 and a.period_start_date >= per.start_date and
551 a.period_end_date <= per.end_date and per.period_set_name = jtf_bis_util.ProfileValue(''CRMBIS:PERIOD_SET_NAME'') and per.period_type = :p_period_type ';
552 l_sql_group_by := l_sql_group_by || ' GROUP BY :p_all_value , per.period_name , per.start_date ';
553 l_sql_order_by := ' Order by :p_all_value, per.start_date ';
554 v_view_by := ' per.period_name' ;
555 from_clause := ' ,bim_dimv_periods per ' ;
556 END IF;
557
558
559 -- dbms_output.put_line(p_period_type);
560
561 /***************/
562
563 l_sql_stmt := l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by;
564
565 /**************/
566 /*
567 declare
568 fp utl_file.file_type;
569 location varchar2(100) := '/sqlcom/log/dom1151';
570 file_name varchar2(50) := 'bpp1.sql';
571 begin
572 -- dbms_output.put_line('ENTERED THE SQL BLOCK ');
573 fp := utl_file.fopen(location,file_name,'w');
574 utl_file.put_line(fp,l_sql_insert_stm);
575 utl_file.put_line(fp,l_sql_from);
576 utl_file.put_line(fp,l_sql_where);
577 utl_file.put_line(fp,l_sql_group_by);
578 utl_file.fclose(fp);
579 exception
580 when UTL_FILE.INVALID_MODE then
581 -- dbms_output.put_line('INVALID MODE EXCEPTION');
582 when UTL_FILE.INVALID_PATH then
583 -- dbms_output.put_line('INVALID PATH EXCEPTION');
584 end;
585 */
586
587 /******************/
588
589 IF (p_view_by = 'PER') THEN
590
591 -- dbms_OUTPUT.PUT_LINE(' VIEW BY PERIOD ');
592
593 EXECUTE IMMEDIATE l_sql_stmt
594 USING
595 p_all_value,
596 p_start_date,
597 p_end_date,
598 p_campaign_status_id,
599 p_campaign_type_id,
600 p_media_id,
601 p_channel_id,
602 p_sales_channel_code,
603 p_market_segment_id,
604 p_interest_type_id,
605 p_primary_interest_code_id,
606 p_secondary_interest_code_id,
607 p_geography_code,
608 p_period_type,
609 p_all_value,
610 p_all_value;
611 ELSE
612
613 -- dbms_OUTPUT.PUT_LINE(' NOT VIEW BY PERIOD ');
614
615 EXECUTE IMMEDIATE l_sql_stmt
616 USING
617 p_all_value,
618 p_start_date,
619 p_end_date,
620 p_campaign_status_id,
621 p_campaign_type_id,
622 p_media_id,
623 p_channel_id,
624 p_sales_channel_code,
625 p_market_segment_id,
626 p_interest_type_id,
627 p_primary_interest_code_id,
628 p_secondary_interest_code_id,
629 p_geography_code,
630 p_all_value;
631
632 END IF;
633
634 v_num_rows_inserted := SQL%ROWCOUNT;
635 -- dbms_output.put_line ( 'Number of rows inserted in temp table is : ' || v_num_rows_inserted );
636
637 /************************************************************************************************************/
638
639 l_sql_update_stm := 'UPDATE bim_camp_acqu_summ_temp tmp set tmp.measure4 = (select nvl(sum(a.initiated_revenue),0) from bim_customer_rev_summ a, bim_campaigns_denorm b ' ;
640
641 l_sql_where := l_sql_where || ' and a.first_order_date >= :p_start_date and a.first_order_date <= :p_end_date ';
642
643 l_sql_where := l_sql_where || ' and tmp.view_by_name = ' || v_view_by || ' ) ' ;
644
645 l_sql_upd_stmt := l_sql_update_stm || from_clause || l_sql_where ;
646
647
648 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_update_stm, 1, 150));
649 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_update_stm, 151, 150));
650 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_update_stm, 300, 150));
651 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 1, 150));
652 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 151, 150));
653 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 300, 150));
654 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 451, 150));
655
656 /*
657 declare
658 fp utl_file.file_type;
662 -- dbms_output.put_line('ENTERED THE SQL BLOCK ');
659 location varchar2(100) := '/sqlcom/log/dom1151';
660 file_name varchar2(50) := 'bpp2.sql';
661 begin
663 fp := utl_file.fopen(location,file_name,'w');
664 utl_file.put_line(fp,l_sql_upd_stmt);
665 utl_file.fclose(fp);
666 exception
667 when UTL_FILE.INVALID_MODE then
668 -- dbms_output.put_line('INVALID MODE EXCEPTION');
669 when UTL_FILE.INVALID_PATH then
670 -- dbms_output.put_line('INVALID PATH EXCEPTION');
671 end;
672
673 */
674
675 /******************/
676
677 IF (p_view_by = 'PER') THEN
678
679 -- dbms_OUTPUT.PUT_LINE(' VIEW BY PERIOD ');
680
681 EXECUTE IMMEDIATE l_sql_upd_stmt
682 USING
683 p_start_date,
684 p_end_date,
685 p_campaign_status_id,
686 p_campaign_type_id,
687 p_media_id,
688 p_channel_id,
689 p_sales_channel_code,
690 p_market_segment_id,
691 p_interest_type_id,
692 p_primary_interest_code_id,
693 p_secondary_interest_code_id,
694 p_geography_code,
695 p_period_type,
696 p_start_date,
697 p_end_date;
698 ELSE
699
700 -- dbms_OUTPUT.PUT_LINE(' NOT VIEW BY PERIOD ');
701
702 EXECUTE IMMEDIATE l_sql_upd_stmt
703 USING
704 p_start_date,
705 p_end_date,
706 p_campaign_status_id,
707 p_campaign_type_id,
708 p_media_id,
709 p_channel_id,
710 p_sales_channel_code,
711 p_market_segment_id,
712 p_interest_type_id,
713 p_primary_interest_code_id,
714 p_secondary_interest_code_id,
715 p_geography_code,
716 p_start_date,
717 p_end_date;
718 END IF;
719
720 /******************/
721
722 UPDATE bim_camp_acqu_summ_temp SET measure2 = measure4*100/measure3;
723
724
725 v_num_rows_updated := SQL%ROWCOUNT;
726 -- dbms_output.put_line ( 'Number of rows updated in temp table is : ' || v_num_rows_updated );
727
728 END BIM_CMP_ACQ_SUM_POPULATE ;
729
730 /**************************************************************************************************/
731
732 PROCEDURE BIM_ACT_ACQ_SUM_POPULATE
733 (
734 p_campaign_id number DEFAULT NULL,
735 p_campaign_type_id varchar2 DEFAULT NULL,
736 p_campaign_status_id number DEFAULT NULL,
737 p_period_type varchar2 DEFAULT NULL,
738 p_start_date date DEFAULT NULL,
739 p_end_date date DEFAULT NULL,
740 p_media_id number DEFAULT NULL,
741 p_channel_id varchar2 DEFAULT NULL,
742 p_market_segment_id number DEFAULT NULL,
743 p_sales_channel_code varchar2 DEFAULT NULL,
744 p_interest_type_id number DEFAULT NULL,
745 p_primary_interest_code_id number DEFAULT NULL,
746 p_secondary_interest_code_id number DEFAULT NULL,
747 p_geography_code varchar2 DEFAULT NULL,
748 p_view_by varchar2 DEFAULT NULL,
749 p_drill_down varchar2 DEFAULT 'Y',
750 p_all_value varchar2 DEFAULT 'ALL'
751 ) IS
752
753
754 v_num_rows_inserted integer;
755 v_num_rows_updated integer;
756 v_view_by varchar2(50);
757 from_clause varchar2(100);
758 where_clause varchar2(250);
759
760 l_sql_from varchar2(5000);
761 l_sql_where varchar2(5000);
762 l_sql_group_by varchar2(5000);
763 l_sql_order_by varchar2(5000);
764 l_sql_insert_stm varchar2(5000);
765 l_sql_stmt varchar2(5000);
766 v_all_name varchar2(80);
767
768 l_sql_update_stm varchar2(5000);
769 l_sql_upd_stmt varchar2(5000);
770
771 BEGIN
772
773 l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure3, subject_name, view_by_name)';
774
775 l_sql_insert_stm := l_sql_insert_stm
776 || ' SELECT ' ;
777
778 l_sql_insert_stm := l_sql_insert_stm
779 || ' count(distinct a.cust_account_id) , ';
780
781 l_sql_insert_stm := l_sql_insert_stm
782 || ' nvl(sum(a.initiated_revenue), 0 ) , ';
783
784 /***************/
785
786 l_sql_where := l_sql_where || ' where a.period_start_date >= :p_start_date and a.period_end_date <= :p_end_date ' ;
787
788 /***************/
789
790 IF p_campaign_status_id IS NULL THEN
791 l_sql_where:= l_sql_where ||' and :p_campaign_status_id IS NULL ' ;
792 ELSE
793 l_sql_where:= l_sql_where || ' and parent_campaign_status_id = :p_campaign_status_id ';
794 END IF;
795
796 IF p_campaign_type_id IS NULL THEN
797 l_sql_where:= l_sql_where || ' and :p_campaign_type_id IS NULL ';
798 ELSE
799 l_sql_where:= l_sql_where || ' and parent_campaign_type_id = :p_campaign_type_id ';
800 END IF;
801
802 /***************/
803
804 IF p_channel_id IS NULL THEN
805 l_sql_where:= l_sql_where|| ' and :p_channel_id IS NULL ';
806 ELSE
807 l_sql_where:= l_sql_where|| ' and a.channel_id = :p_channel_id';
808 END IF;
812 IF p_sales_channel_code IS NULL THEN
809
810 /***************/
811
813 l_sql_where:= l_sql_where|| ' and :p_sales_channel_code IS NULL ';
814 ELSE
815 l_sql_where:= l_sql_where|| ' and a.sales_channel_code = :p_sales_channel_code';
816 END IF;
817
818 /***************/
819
820 IF p_market_segment_id IS NULL
821 THEN
822 l_sql_where:= l_sql_where|| ' and :p_market_segment_id IS NULL ';
823 ELSE
824 l_sql_where:= l_sql_where|| ' and a.market_segment_id = :p_market_segment_id';
825 END IF;
826
827 /***************/
828
829 IF p_interest_type_id IS NULL THEN
830 l_sql_where:= l_sql_where|| ' and :p_interest_type_id IS NULL ';
831 ELSE
832 l_sql_where:= l_sql_where|| ' and a.interest_type_id = :p_interest_type_id ';
833 END IF;
834
835 IF p_primary_interest_code_id IS NULL THEN
836 l_sql_where:= l_sql_where||' and :p_primary_interest_code_id IS NULL ';
837 ELSE
838 l_sql_where:= l_sql_where|| ' and a.primary_interest_code_id = :p_primary_interest_code_id ';
839 END IF;
840
841 IF p_secondary_interest_code_id IS NULL THEN
842 l_sql_where:= l_sql_where||' and :p_secondary_interest_code_id IS NULL ';
843 ELSE
844 l_sql_where:= l_sql_where||' and a.secondary_interest_code_id = :p_secondary_interest_code_id ';
845 END IF;
846
847 /***************/
848
849 IF p_geography_code IS NULL THEN
850 l_sql_where:= l_sql_where|| ' and :p_geography_code IS NULL ';
851 ELSE
852 l_sql_where:= l_sql_where|| ' and bill_to_geography_code like :p_geography_code';
853 END IF;
854
855 /***************/
856
857 IF p_view_by = 'CMP'
858 THEN
859 l_sql_insert_stm := l_sql_insert_stm || ' :p_all_value , cmp.campaign_name ';
860 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a , bim_dimv_campaigns cmp ' ;
861 l_sql_where := l_sql_where || ' and a.campaign_id = cmp.campaign_id ';
862 l_sql_group_by := l_sql_group_by || ' GROUP BY :p_all_value , cmp.campaign_name ';
863 l_sql_order_by := ' Order by 1,3 desc';
864 v_view_by := 'cmp.campaign_name ' ;
865 from_clause := ' ,bim_dimv_campaigns cmp ' ;
866 ELSIF p_view_by = 'MCH'
867 THEN
868 l_sql_insert_stm := l_sql_insert_stm || ' :p_all_value , chn.channel_name ';
869 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a, bim_dimv_channels chn ' ;
870 l_sql_where := l_sql_where || ' and a.channel_id = chn.channel_id';
871 l_sql_group_by := l_sql_group_by || ' GROUP BY :p_all_value , chn.channel_name ';
872 l_sql_order_by := ' Order by 1,3 desc';
873 v_view_by := ' chn.channel_name' ;
874 from_clause := ' ,bim_dimv_channels chn ' ;
875 ELSIF p_view_by = 'SCH'
876 THEN
877 l_sql_insert_stm := l_sql_insert_stm || ' :p_all_value , sch.sales_channel_name ';
878 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a ,bim_dimv_sales_channels sch ' ;
879 l_sql_where := l_sql_where || ' and a.sales_channel_code = sch.sales_channel_code';
880 l_sql_group_by := l_sql_group_by || ' GROUP BY :p_all_value , sch.sales_channel_name ';
881 l_sql_order_by := ' Order by 1,3 desc';
882 v_view_by := ' sch.sales_channel_name' ;
883 from_clause := ' ,bim_dimv_sales_channels sch ' ;
884 ELSIF p_view_by = 'MSG'
885 THEN
886 l_sql_insert_stm := l_sql_insert_stm || ' :p_all_value , mkt.market_segment_name ';
887 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a ,bim_dimv_market_sgmts mkt ' ;
888 l_sql_where := l_sql_where || ' and a.market_segment_id = mkt.market_segment_id';
889 l_sql_group_by := l_sql_group_by || ' GROUP BY :p_all_value , mkt.market_segment_name ';
890 l_sql_order_by := ' Order by 1,3 desc';
891 v_view_by := ' mkt.market_segment_name' ;
892 from_clause := ' ,bim_dimv_market_sgmts mkt ' ;
893 ELSIF p_view_by = 'PER' THEN
894 l_sql_insert_stm := l_sql_insert_stm || ' :p_all_value , per.period_name ';
895 l_sql_from:= l_sql_from|| ' from bim_cmpgn_perf_summ a ,bim_dimv_periods per,dual ' ;
896 l_sql_where:= l_sql_where|| ' and a.period_start_date >= per.start_date and a.period_end_date <= per.end_date and per.period_set_name = jtf_bis_util.ProfileValue(''CRMBIS:PERIOD_SET_NAME'') and per.period_type = :p_period_type ';
897 l_sql_group_by := l_sql_group_by || ' GROUP BY :p_all_value , per.period_name , per.start_date ';
898 l_sql_order_by := ' Order by :p_all_value, per.start_date ';
899 v_view_by := ' per.period_name' ;
900 from_clause := ' ,bim_dimv_periods per ' ;
901 END IF;
902
903
904 -- dbms_output.put_line(p_period_type);
905
906 /***************/
907
908 l_sql_stmt := l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by;
909
910 /**************/
911
912 /*
913 declare
914 fp utl_file.file_type;
915 location varchar2(100) := '/sqlcom/log';
916 file_name varchar2(50) := 'bpp1.sql';
917 begin
918 -- dbms_output.put_line('ENTERED THE SQL BLOCK ');
919 fp := utl_file.fopen(location,file_name,'w');
920 utl_file.put_line(fp,l_sql_insert_stm);
921 utl_file.put_line(fp,l_sql_from);
922 utl_file.put_line(fp,l_sql_where);
923 utl_file.put_line(fp,l_sql_group_by);
924 utl_file.fclose(fp);
925 exception
926 when UTL_FILE.INVALID_MODE then
927 -- dbms_output.put_line('INVALID MODE EXCEPTION');
928 when UTL_FILE.INVALID_PATH then
929 -- dbms_output.put_line('INVALID PATH EXCEPTION');
933
930 end;
931
932 */
934 /******************/
935
936 IF (p_view_by = 'PER') THEN
937
938 -- dbms_OUTPUT.PUT_LINE(' VIEW BY PERIOD ');
939
940 EXECUTE IMMEDIATE l_sql_stmt
941 USING
942 p_all_value,
943 p_start_date,
944 p_end_date,
945 p_campaign_status_id,
946 p_campaign_type_id,
947 p_channel_id,
948 p_sales_channel_code,
949 p_market_segment_id,
950 p_interest_type_id,
951 p_primary_interest_code_id,
952 p_secondary_interest_code_id,
953 p_geography_code,
954 p_period_type,
955 p_all_value,
956 p_all_value;
957 ELSE
958
959 -- dbms_OUTPUT.PUT_LINE(' NOT VIEW BY PERIOD ');
960
961 EXECUTE IMMEDIATE l_sql_stmt
962 USING
963 p_all_value,
964 p_start_date,
965 p_end_date,
966 p_campaign_status_id,
967 p_campaign_type_id,
968 p_channel_id,
969 p_sales_channel_code,
970 p_market_segment_id,
971 p_interest_type_id,
972 p_primary_interest_code_id,
973 p_secondary_interest_code_id,
974 p_geography_code,
975 p_all_value;
976
977 END IF;
978
979 v_num_rows_inserted := SQL%ROWCOUNT;
980 -- dbms_output.put_line ( 'Number of rows inserted in temp table is : ' || v_num_rows_inserted );
981
982 /*****************************************************************************************/
983
984 l_sql_update_stm := 'UPDATE bim_camp_acqu_summ_temp tmp set tmp.measure4 = (select nvl(sum(a.initiated_revenue),0) from bim_customer_rev_summ a ' ;
985
986 l_sql_where := l_sql_where || ' and a.first_order_date >= :p_start_date and a.first_order_date <= :p_end_date ';
987
988 l_sql_where := l_sql_where || 'and tmp.view_by_name = ' || v_view_by || ' ) ' ;
989
990 l_sql_upd_stmt := l_sql_update_stm || from_clause || l_sql_where ;
991
992
993 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_update_stm, 1, 150));
994 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_update_stm, 151, 150));
995 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_update_stm, 300, 150));
996 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 1, 150));
997 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 151, 150));
998 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 300, 150));
999 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 451, 150));
1000
1001 /*
1002
1003 declare
1004 fp utl_file.file_type;
1005 location varchar2(100) := '/sqlcom/log/dom1151';
1006 file_name varchar2(50) := 'bpp2.sql';
1007 begin
1008 -- dbms_output.put_line('ENTERED THE SQL BLOCK ');
1009 fp := utl_file.fopen(location,file_name,'w');
1010 utl_file.put_line(fp,l_sql_upd_stmt);
1011 utl_file.fclose(fp);
1012 exception
1013 when UTL_FILE.INVALID_MODE then
1014 -- dbms_output.put_line('INVALID MODE EXCEPTION');
1015 when UTL_FILE.INVALID_PATH then
1016 -- dbms_output.put_line('INVALID PATH EXCEPTION');
1017 end;
1018
1019 */
1020
1021 /******************/
1022
1023 IF (p_view_by = 'PER') THEN
1024
1025 -- dbms_OUTPUT.PUT_LINE(' VIEW BY PERIOD ');
1026
1027 EXECUTE IMMEDIATE l_sql_upd_stmt
1028 USING
1029 p_start_date,
1030 p_end_date,
1031 p_campaign_status_id,
1032 p_campaign_type_id,
1033 p_channel_id,
1034 p_sales_channel_code,
1035 p_market_segment_id,
1036 p_interest_type_id,
1037 p_primary_interest_code_id,
1038 p_secondary_interest_code_id,
1039 p_geography_code,
1040 p_period_type,
1041 p_start_date,
1042 p_end_date;
1043 ELSE
1044
1045 -- dbms_OUTPUT.PUT_LINE(' NOT VIEW BY PERIOD ');
1046
1047 EXECUTE IMMEDIATE l_sql_upd_stmt
1048 USING
1049 p_start_date,
1050 p_end_date,
1051 p_campaign_status_id,
1052 p_campaign_type_id,
1053 p_channel_id,
1054 p_sales_channel_code,
1055 p_market_segment_id,
1056 p_interest_type_id,
1057 p_primary_interest_code_id,
1058 p_secondary_interest_code_id,
1059 p_geography_code,
1060 p_start_date,
1061 p_end_date;
1062 END IF;
1063
1064 /******************/
1065 UPDATE bim_camp_acqu_summ_temp SET measure2 = measure4*100/measure3;
1066
1067 v_num_rows_updated := SQL%ROWCOUNT;
1068 -- dbms_output.put_line ( 'Number of rows updated in temp table is : ' || v_num_rows_updated );
1069
1070 END BIM_ACT_ACQ_SUM_POPULATE ;
1071
1072 /****************************************************************************************************/
1073
1074 PROCEDURE BIM_ACT_ACQ_POPULATE
1075 (
1076 p_campaign_id number DEFAULT NULL,
1077 p_campaign_type_id varchar2 DEFAULT NULL,
1078 p_campaign_status_id number DEFAULT NULL,
1079 p_period_type varchar2 DEFAULT NULL,
1080 p_start_date date DEFAULT NULL,
1081 p_end_date date DEFAULT NULL,
1082 p_media_id number DEFAULT NULL,
1083 p_channel_id varchar2 DEFAULT NULL,
1084 p_market_segment_id number DEFAULT NULL,
1088 p_secondary_interest_code_id number DEFAULT NULL,
1085 p_sales_channel_code varchar2 DEFAULT NULL,
1086 p_interest_type_id number DEFAULT NULL,
1087 p_primary_interest_code_id number DEFAULT NULL,
1089 p_geography_code varchar2 DEFAULT NULL,
1090 p_view_by varchar2 DEFAULT NULL,
1091 p_drill_down varchar2 DEFAULT 'Y'
1092 ) IS
1093
1094 v_num_rows_inserted integer;
1095 v_num_rows_updated integer;
1096 v_view_by varchar2(50);
1097 from_clause varchar2(100);
1098 where_clause varchar2(250);
1099
1100 l_sql_from varchar2(5000);
1101 l_sql_where varchar2(5000);
1102 l_sql_group_by varchar2(5000);
1103 l_sql_order_by varchar2(5000);
1104 l_sql_insert_stm varchar2(5000);
1105 l_sql_stmt varchar2(5000);
1106 v_all_name varchar2(80);
1107
1108 l_sql_update_stm varchar2(5000);
1109 l_sql_upd_stmt varchar2(5000);
1110
1111
1112 BEGIN
1113
1114
1115 l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure3, subject_name, view_by_name)';
1116
1117 l_sql_insert_stm := l_sql_insert_stm
1118 || ' SELECT ' ;
1119
1120 l_sql_insert_stm := l_sql_insert_stm
1121 || ' count(distinct a.cust_account_id) , ';
1122
1123 l_sql_insert_stm := l_sql_insert_stm
1124 || ' nvl(sum(a.initiated_revenue), 0 ) , ';
1125
1126 /***************/
1127
1128 l_sql_where:= ' where a.period_start_date >= :p_start_date and a.period_end_date <= :p_end_date';
1129
1130 /***************/
1131
1132 l_sql_where:= l_sql_where|| ' and a.campaign_id in (select parent_campaign_id from bim_campaigns_denorm';
1133
1134 IF p_campaign_status_id IS NULL THEN
1135 l_sql_where:= l_sql_where||' where :p_campaign_status_id IS NULL' ;
1136 ELSE
1137 l_sql_where:= l_sql_where|| ' where parent_campaign_status_id = :p_campaign_status_id';
1138 END IF;
1139
1140 IF p_campaign_type_id IS NULL THEN
1141 l_sql_where:= l_sql_where|| ' and :p_campaign_type_id IS NULL';
1142 ELSE
1143 l_sql_where:= l_sql_where|| ' and parent_campaign_type = :p_campaign_type_id';
1144 END IF;
1145
1146 IF p_campaign_id IS NULL THEN
1147 l_sql_where:= l_sql_where|| ' and :p_campaign_id IS NULL ) ';
1148 ELSE
1149 l_sql_where:= l_sql_where|| ' and parent_campaign_id = :p_campaign_id ) ';
1150 END IF;
1151
1152
1153 /***************/
1154
1155 IF p_media_id IS NULL THEN
1156 l_sql_where:= l_sql_where|| ' and :p_media_id IS NULL ';
1157 ELSE
1158 l_sql_where:= l_sql_where|| ' and a.media_id = :p_media_id';
1159 END IF;
1160
1161 /***************/
1162
1163 IF p_channel_id IS NULL THEN
1164 l_sql_where:= l_sql_where|| ' and :p_channel_id IS NULL ';
1165 ELSE
1166 l_sql_where:= l_sql_where|| ' and a.channel_id = :p_channel_id';
1167 END IF;
1168
1169 /***************/
1170
1171 IF p_sales_channel_code IS NULL THEN
1172 l_sql_where:= l_sql_where|| ' and :p_sales_channel_code IS NULL ';
1173 ELSE
1174 l_sql_where:= l_sql_where|| ' and a.sales_channel_code = :p_sales_channel_code';
1175 END IF;
1176
1177 /***************/
1178
1179 IF p_market_segment_id IS NULL
1180 THEN
1181 l_sql_where:= l_sql_where|| ' and :p_market_segment_id IS NULL ';
1182 ELSE
1183 l_sql_where:= l_sql_where|| ' and a.market_segment_id = :p_market_segment_id';
1184 END IF;
1185
1186 /***************/
1187
1188 IF p_interest_type_id IS NULL THEN
1189 l_sql_where:= l_sql_where|| ' and :p_interest_type_id IS NULL ';
1190 ELSE
1191 l_sql_where:= l_sql_where|| ' and a.interest_type_id = :p_interest_type_id ';
1192 END IF;
1193
1194 IF p_primary_interest_code_id IS NULL THEN
1195 l_sql_where:= l_sql_where||' and :p_primary_interest_code_id IS NULL ';
1196 ELSE
1197 l_sql_where:= l_sql_where|| ' and a.primary_interest_code_id = :p_primary_interest_code_id ';
1198 END IF;
1199
1200 IF p_secondary_interest_code_id IS NULL THEN
1201 l_sql_where:= l_sql_where||' and :p_secondary_interest_code_id IS NULL ';
1202 ELSE
1203 l_sql_where:= l_sql_where||' and a.secondary_interest_code_id = :p_secondary_interest_code_id ';
1204 END IF;
1205
1206 /***************/
1207
1208 IF p_geography_code IS NULL THEN
1209 l_sql_where:= l_sql_where|| ' and :p_geography_code IS NULL ';
1210 ELSE
1211 l_sql_where:= l_sql_where|| ' and bill_to_geography_code like :p_geography_code';
1212 END IF;
1213
1214
1215 /***************/
1216
1217 IF p_view_by = 'CMP'
1218 THEN
1219 l_sql_insert_stm := l_sql_insert_stm || ' med.media_name , cmp.campaign_name ';
1220 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a , bim_dimv_media med ,bim_dimv_campaigns cmp' ;
1221 l_sql_where := l_sql_where || ' and a.media_id = med.media_id and a.campaign_id = cmp.campaign_id';
1222 l_sql_group_by := l_sql_group_by || ' GROUP BY med.media_name , cmp.campaign_name ';
1223 l_sql_order_by := ' Order by 1,3 desc';
1224 v_view_by := ' cmp.campaign_name ' ;
1225 from_clause := ' ,bim_dimv_campaigns cmp ' ;
1226 ELSIF p_view_by = 'MCH'
1227 THEN
1228 l_sql_insert_stm := l_sql_insert_stm || ' med.media_name , chn.channel_name ';
1232 l_sql_order_by := ' Order by 1,3 desc';
1229 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a, bim_dimv_channels chn ,bim_dimv_media med ' ;
1230 l_sql_where := l_sql_where || ' and a.channel_id = chn.channel_id and a.media_id = med.media_id';
1231 l_sql_group_by := l_sql_group_by || ' GROUP BY med.media_name , chn.channel_name ';
1233 v_view_by := ' chn.channel_name' ;
1234 from_clause := ' ,bim_dimv_channels chn ' ;
1235 ELSIF p_view_by = 'SCH'
1236 THEN
1237 l_sql_insert_stm := l_sql_insert_stm || ' med.media_name , sch.sales_channel_name ';
1238 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a, bim_dimv_sales_channels sch ,bim_dimv_media med ' ;
1239 l_sql_where := l_sql_where || ' and a.sales_channel_code = sch.sales_channel_code and a.media_id = med.media_id';
1240 l_sql_group_by := l_sql_group_by || ' GROUP BY med.media_name , sch.sales_channel_name ';
1241 l_sql_order_by := ' Order by 1,3 desc';
1242 v_view_by := ' sch.sales_channel_name' ;
1243 from_clause := ' ,bim_dimv_sales_channels sch ' ;
1244 ELSIF p_view_by = 'MSG'
1245 THEN
1246 l_sql_insert_stm := l_sql_insert_stm || ' med.media_name , mkt.market_segment_name ';
1247 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a, bim_dimv_market_sgmts mkt ,bim_dimv_media med ' ;
1248 l_sql_where := l_sql_where || ' and a.market_segment_id = mkt.market_segment_id and a.media_id = med.media_id';
1249 l_sql_group_by := l_sql_group_by || ' GROUP BY med.media_name , mkt.market_segment_name ';
1250 l_sql_order_by := ' Order by 1,3 desc';
1251 v_view_by := ' mkt.market_segment_name' ;
1252 from_clause := ' ,bim_dimv_market_sgmts mkt ' ;
1253 ELSIF p_view_by = 'PER' THEN
1254 l_sql_insert_stm := l_sql_insert_stm || ' med.media_name , per.period_name ';
1255 l_sql_from:= l_sql_from|| ' from bim_cmpgn_perf_summ a, bim_dimv_media med, bim_dimv_periods per,dual ' ;
1256 l_sql_where:= l_sql_where|| ' and a.media_id = med.media_id and a.period_start_date >= per.start_date and a.period_end_date <= per.end_date
1257 and per.period_set_name = jtf_bis_util.ProfileValue(''CRMBIS:PERIOD_SET_NAME'') and per.period_type = :p_period_type ';
1258 l_sql_group_by := l_sql_group_by || ' GROUP BY med.media_name , per.period_name , per.start_date ';
1259 l_sql_order_by := ' Order by med.media_name, per.start_date ';
1260 v_view_by := ' per.period_name' ;
1261 from_clause := ' ,bim_dimv_periods per ' ;
1262 END IF;
1263
1264 /***************/
1265
1266 l_sql_stmt := l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by;
1267
1268 /**************/
1269
1270 /*
1271 declare
1272 fp utl_file.file_type;
1273 location varchar2(100) := '/sqlcom/log/dom1151';
1274 file_name varchar2(50) := 'bpp1.sql';
1275 begin
1276 -- dbms_output.put_line('ENTERED THE SQL BLOCK ');
1277 fp := utl_file.fopen(location,file_name,'w');
1278 utl_file.put_line(fp,l_sql_insert_stm);
1279 utl_file.put_line(fp,l_sql_from);
1280 utl_file.put_line(fp,l_sql_where);
1281 utl_file.put_line(fp,l_sql_group_by);
1282 utl_file.fclose(fp);
1283 exception
1284 when UTL_FILE.INVALID_MODE then
1285 -- dbms_output.put_line('INVALID MODE EXCEPTION');
1286 when UTL_FILE.INVALID_PATH then
1287 -- dbms_output.put_line('INVALID PATH EXCEPTION');
1288 end;
1289 */
1290
1291 /******************/
1292
1293 IF (p_view_by = 'PER') THEN
1294
1295 EXECUTE IMMEDIATE l_sql_stmt
1296 USING
1297 p_start_date,
1298 p_end_date,
1299 p_campaign_status_id,
1300 p_campaign_type_id,
1301 p_campaign_id,
1302 p_media_id,
1303 p_channel_id,
1304 p_sales_channel_code,
1305 p_market_segment_id,
1306 p_interest_type_id,
1307 p_primary_interest_code_id,
1308 p_secondary_interest_code_id,
1309 p_geography_code,
1310 p_period_type;
1311 ELSE
1312 EXECUTE IMMEDIATE l_sql_stmt
1313 USING
1314 p_start_date,
1315 p_end_date,
1316 p_campaign_status_id,
1317 p_campaign_type_id,
1318 p_campaign_id,
1319 p_media_id,
1320 p_channel_id,
1321 p_sales_channel_code,
1322 p_market_segment_id,
1323 p_interest_type_id,
1324 p_primary_interest_code_id,
1325 p_secondary_interest_code_id,
1326 p_geography_code;
1327
1328 END IF;
1329
1330 v_num_rows_inserted := SQL%ROWCOUNT;
1331 -- dbms_output.put_line ( 'Number of rows inserted in temp table is : ' || v_num_rows_inserted );
1332
1333 /************************************************************************************************************/
1334
1335 l_sql_update_stm := 'UPDATE bim_camp_acqu_summ_temp tmp set tmp.measure4 = (select nvl(sum(a.initiated_revenue),0) from bim_customer_rev_summ a, bim_dimv_media med ' ;
1336
1337 l_sql_where := l_sql_where || ' and a.first_order_date >= :p_start_date and a.first_order_date <= :p_end_date ';
1338
1339 l_sql_where := l_sql_where || ' and tmp.subject_name = med.media_name and tmp.view_by_name = ' || v_view_by || ' ) ' ;
1340
1341 l_sql_upd_stmt := l_sql_update_stm || from_clause || l_sql_where ;
1342
1343 /*
1344 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_update_stm, 1, 150));
1345 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_update_stm, 151, 150));
1346 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_update_stm, 300, 150));
1347 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 1, 150));
1351 */
1348 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 151, 150));
1349 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 300, 150));
1350 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 451, 150));
1352
1353 /******************/
1354
1355 /*
1356 declare
1357 fp utl_file.file_type;
1358 location varchar2(100) := '/sqlcom/log/dom1151';
1359 file_name varchar2(50) := 'bpp21.sql';
1360 begin
1361 -- dbms_output.put_line('ENTERED THE SQL BLOCK ');
1362 fp := utl_file.fopen(location,file_name,'w');
1363 utl_file.put_line(fp,l_sql_upd_stmt);
1364 utl_file.fclose(fp);
1365 exception
1366 when UTL_FILE.INVALID_MODE then
1367 -- dbms_output.put_line('INVALID MODE EXCEPTION');
1368 when UTL_FILE.INVALID_PATH then
1369 -- dbms_output.put_line('INVALID PATH EXCEPTION');
1370 end;
1371 */
1372
1373
1374 /******************/
1375
1376 IF (p_view_by = 'PER') THEN
1377
1378 -- dbms_OUTPUT.PUT_LINE(' VIEW BY PERIOD ');
1379
1380 EXECUTE IMMEDIATE l_sql_upd_stmt
1381 USING
1382 p_start_date,
1383 p_end_date,
1384 p_campaign_status_id,
1385 p_campaign_type_id,
1386 p_campaign_id,
1387 p_media_id,
1388 p_channel_id,
1389 p_sales_channel_code,
1390 p_market_segment_id,
1391 p_interest_type_id,
1392 p_primary_interest_code_id,
1393 p_secondary_interest_code_id,
1394 p_geography_code,
1395 p_period_type,
1396 p_start_date,
1397 p_end_date;
1398 ELSE
1399
1400 -- dbms_OUTPUT.PUT_LINE(' NOT VIEW BY PERIOD ');
1401
1402 EXECUTE IMMEDIATE l_sql_upd_stmt
1403 USING
1404 p_start_date,
1405 p_end_date,
1406 p_campaign_status_id,
1407 p_campaign_type_id,
1408 p_campaign_id,
1409 p_media_id,
1410 p_channel_id,
1411 p_sales_channel_code,
1412 p_market_segment_id,
1413 p_interest_type_id,
1414 p_primary_interest_code_id,
1415 p_secondary_interest_code_id,
1416 p_geography_code,
1417 p_start_date,
1418 p_end_date;
1419 END IF;
1420
1421
1422 /******************/
1423
1424 UPDATE bim_camp_acqu_summ_temp SET measure2 = measure4*100/measure3;
1425
1426 v_num_rows_updated := SQL%ROWCOUNT;
1427 -- dbms_output.put_line ( 'Number of rows updated in temp table is : ' || v_num_rows_updated );
1428
1429
1430 END BIM_ACT_ACQ_POPULATE ;
1431
1432
1433 /***********************************************************************************************/
1434
1435
1436 PROCEDURE BIM_MCH_ACQ_SUM_POPULATE
1437 (
1438 p_campaign_id number DEFAULT NULL,
1439 p_campaign_status_id number DEFAULT NULL,
1440 p_campaign_type_id varchar2 DEFAULT NULL,
1441 p_period_type varchar2 DEFAULT NULL,
1442 p_start_date date DEFAULT NULL,
1443 p_end_date date DEFAULT NULL,
1444 p_media_id number DEFAULT NULL,
1445 p_channel_id varchar2 DEFAULT NULL,
1446 p_market_segment_id number DEFAULT NULL,
1447 p_sales_channel_code varchar2 DEFAULT NULL,
1448 p_interest_type_id number DEFAULT NULL,
1449 p_primary_interest_code_id number DEFAULT NULL,
1450 p_secondary_interest_code_id number DEFAULT NULL,
1451 p_geography_code varchar2 DEFAULT NULL,
1452 p_view_by varchar2 DEFAULT NULL,
1453 p_drill_down varchar2 DEFAULT 'Y',
1454 p_all_value varchar2 DEFAULT 'ALL'
1455 ) IS
1456
1457
1458 v_num_rows_inserted integer;
1459 v_num_rows_updated integer;
1460 v_view_by varchar2(50);
1461 from_clause varchar2(100);
1462 where_clause varchar2(250);
1463
1464 l_sql_from varchar2(5000);
1465 l_sql_where varchar2(5000);
1466 l_sql_group_by varchar2(5000);
1467 l_sql_order_by varchar2(5000);
1468 l_sql_insert_stm varchar2(5000);
1469 l_sql_stmt varchar2(5000);
1470 v_all_name varchar2(80);
1471
1472 l_sql_update_stm varchar2(5000);
1473 l_sql_upd_stmt varchar2(5000);
1474
1475 BEGIN
1476
1477 l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure3, subject_name, view_by_name)';
1478
1479 l_sql_insert_stm := l_sql_insert_stm
1480 || ' SELECT ' ;
1481
1482 l_sql_insert_stm := l_sql_insert_stm
1483 || ' count(distinct a.cust_account_id) , ';
1484
1485 l_sql_insert_stm := l_sql_insert_stm
1486 || ' nvl(sum(a.initiated_revenue), 0 ) , ';
1487
1488 /***************/
1489
1490 l_sql_where := l_sql_where || ' where a.period_start_date >= :p_start_date and a.period_end_date <= :p_end_date ' ;
1491
1492 /***************/
1493
1494 IF p_campaign_status_id IS NULL THEN
1495 l_sql_where:= l_sql_where ||' and :p_campaign_status_id IS NULL ' ;
1496 ELSE
1497 l_sql_where:= l_sql_where || ' and parent_campaign_status_id = :p_campaign_status_id ';
1498 END IF;
1499
1500 IF p_campaign_type_id IS NULL THEN
1504 END IF;
1501 l_sql_where:= l_sql_where || ' and :p_campaign_type_id IS NULL ';
1502 ELSE
1503 l_sql_where:= l_sql_where || ' and parent_campaign_type = :p_campaign_type_id ';
1505
1506 /***************/
1507
1508 IF p_media_id IS NULL THEN
1509 l_sql_where:= l_sql_where|| ' and :p_media_id IS NULL ';
1510 ELSE
1511 l_sql_where:= l_sql_where|| ' and a.media_id = :p_media_id';
1512 END IF;
1513
1514 /***************/
1515
1516 IF p_sales_channel_code IS NULL THEN
1517 l_sql_where:= l_sql_where|| ' and :p_sales_channel_code IS NULL ';
1518 ELSE
1519 l_sql_where:= l_sql_where|| ' and a.sales_channel_code = :p_sales_channel_code';
1520 END IF;
1521
1522 /***************/
1523
1524 IF p_market_segment_id IS NULL
1525 THEN
1526 l_sql_where:= l_sql_where|| ' and :p_market_segment_id IS NULL ';
1527 ELSE
1528 l_sql_where:= l_sql_where|| ' and a.market_segment_id = :p_market_segment_id';
1529 END IF;
1530
1531 /***************/
1532
1533 IF p_interest_type_id IS NULL THEN
1534 l_sql_where:= l_sql_where|| ' and :p_interest_type_id IS NULL ';
1535 ELSE
1536 l_sql_where:= l_sql_where|| ' and a.interest_type_id = :p_interest_type_id ';
1537 END IF;
1538
1539 IF p_primary_interest_code_id IS NULL THEN
1540 l_sql_where:= l_sql_where||' and :p_primary_interest_code_id IS NULL ';
1541 ELSE
1542 l_sql_where:= l_sql_where|| ' and a.primary_interest_code_id = :p_primary_interest_code_id ';
1543 END IF;
1544
1545 IF p_secondary_interest_code_id IS NULL THEN
1546 l_sql_where:= l_sql_where||' and :p_secondary_interest_code_id IS NULL ';
1547 ELSE
1548 l_sql_where:= l_sql_where||' and a.secondary_interest_code_id = :p_secondary_interest_code_id ';
1549 END IF;
1550
1551 /***************/
1552
1553 IF p_geography_code IS NULL THEN
1554 l_sql_where:= l_sql_where|| ' and :p_geography_code IS NULL ';
1555 ELSE
1556 l_sql_where:= l_sql_where|| ' and bill_to_geography_code like :p_geography_code';
1557 END IF;
1558
1559 /***************/
1560
1561 IF p_view_by = 'CMP'
1562 THEN
1563 l_sql_insert_stm := l_sql_insert_stm || ' :p_all_value , cmp.campaign_name ';
1564 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a , bim_dimv_campaigns cmp ' ;
1565 l_sql_where := l_sql_where || ' and a.campaign_id = cmp.campaign_id ';
1566 l_sql_group_by := l_sql_group_by || ' GROUP BY :p_all_value , cmp.campaign_name ';
1567 l_sql_order_by := ' Order by 1,3 desc';
1568 v_view_by := 'cmp.campaign_name ' ;
1569 from_clause := ' ,bim_dimv_campaigns cmp ' ;
1570 ELSIF p_view_by = 'ACT'
1571 THEN
1572 l_sql_insert_stm := l_sql_insert_stm || ' :p_all_value ,med.media_name ';
1573 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a, bim_dimv_media med ' ;
1574 l_sql_where := l_sql_where || ' and a.media_id = med.media_id';
1575 l_sql_group_by := l_sql_group_by || ' GROUP BY :p_all_value , med.media_name ';
1576 l_sql_order_by := ' Order by 1,3 desc';
1577 v_view_by := ' med.media_name ' ;
1578 from_clause := ' ,bim_dimv_media med ' ;
1579 ELSIF p_view_by = 'SCH'
1580 THEN
1581 l_sql_insert_stm := l_sql_insert_stm || ' :p_all_value , sch.sales_channel_name ';
1582 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a ,bim_dimv_sales_channels sch ' ;
1583 l_sql_where := l_sql_where || ' and a.sales_channel_code = sch.sales_channel_code';
1584 l_sql_group_by := l_sql_group_by || ' GROUP BY :p_all_value , sch.sales_channel_name ';
1585 l_sql_order_by := ' Order by 1,3 desc';
1586 v_view_by := ' sch.sales_channel_name' ;
1587 from_clause := ' ,bim_dimv_sales_channels sch ' ;
1588 ELSIF p_view_by = 'MSG'
1589 THEN
1590 l_sql_insert_stm := l_sql_insert_stm || ' :p_all_value , mkt.market_segment_name ';
1591 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a ,bim_dimv_market_sgmts mkt ' ;
1592 l_sql_where := l_sql_where || ' and a.market_segment_id = mkt.market_segment_id';
1593 l_sql_group_by := l_sql_group_by || ' GROUP BY :p_all_value , mkt.market_segment_name ';
1594 l_sql_order_by := ' Order by 1,3 desc';
1595 v_view_by := ' mkt.market_segment_name' ;
1596 from_clause := ' ,bim_dimv_market_sgmts mkt ' ;
1597 ELSIF p_view_by = 'PER' THEN
1598 l_sql_insert_stm := l_sql_insert_stm || ' :p_all_value , per.period_name ';
1599 l_sql_from:= l_sql_from|| ' from bim_cmpgn_perf_summ a ,bim_dimv_periods per,dual ' ;
1600 l_sql_where:= l_sql_where|| ' and a.period_start_date >= per.start_date and a.period_end_date <= per.end_date and per.period_set_name = jtf_bis_util.ProfileValue(''CRMBIS:PERIOD_SET_NAME'') and per.period_type = :p_period_type ';
1601 l_sql_group_by := l_sql_group_by || ' GROUP BY :p_all_value , per.period_name , per.start_date ';
1602 l_sql_order_by := ' Order by :p_all_value, per.start_date ';
1603 v_view_by := ' per.period_name' ;
1604 from_clause := ' ,bim_dimv_periods per ' ;
1605 END IF;
1606
1607
1608 -- dbms_output.put_line(p_period_type);
1609
1610 /***************/
1611
1612 l_sql_stmt := l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by;
1613
1614 /**************/
1615
1616 /*
1617 declare
1618 fp utl_file.file_type;
1619 location varchar2(100) := '/sqlcom/log/dom1151';
1620 file_name varchar2(50) := 'bpp1.sql';
1621 begin
1622 -- dbms_output.put_line('ENTERED THE SQL BLOCK ');
1623 fp := utl_file.fopen(location,file_name,'w');
1627 utl_file.put_line(fp,l_sql_group_by);
1624 utl_file.put_line(fp,l_sql_insert_stm);
1625 utl_file.put_line(fp,l_sql_from);
1626 utl_file.put_line(fp,l_sql_where);
1628 utl_file.fclose(fp);
1629 exception
1630 when UTL_FILE.INVALID_MODE then
1631 -- dbms_output.put_line('INVALID MODE EXCEPTION');
1632 when UTL_FILE.INVALID_PATH then
1633 -- dbms_output.put_line('INVALID PATH EXCEPTION');
1634 end;
1635 */
1636
1637 /******************/
1638
1639 IF (p_view_by = 'PER') THEN
1640
1641 -- dbms_OUTPUT.PUT_LINE(' VIEW BY PERIOD ');
1642
1643 EXECUTE IMMEDIATE l_sql_stmt
1644 USING
1645 p_all_value,
1646 p_start_date,
1647 p_end_date,
1648 p_campaign_status_id,
1649 p_campaign_type_id,
1650 p_media_id,
1651 p_sales_channel_code,
1652 p_market_segment_id,
1653 p_interest_type_id,
1654 p_primary_interest_code_id,
1655 p_secondary_interest_code_id,
1656 p_geography_code,
1657 p_period_type,
1658 p_all_value,
1659 p_all_value;
1660 ELSE
1661
1662 -- dbms_OUTPUT.PUT_LINE(' NOT VIEW BY PERIOD ');
1663
1664 EXECUTE IMMEDIATE l_sql_stmt
1665 USING
1666 p_all_value,
1667 p_start_date,
1668 p_end_date,
1669 p_campaign_status_id,
1670 p_campaign_type_id,
1671 p_media_id,
1672 p_sales_channel_code,
1673 p_market_segment_id,
1674 p_interest_type_id,
1675 p_primary_interest_code_id,
1676 p_secondary_interest_code_id,
1677 p_geography_code,
1678 p_all_value;
1679
1680 END IF;
1681
1682 v_num_rows_inserted := SQL%ROWCOUNT;
1683 -- dbms_output.put_line ( 'Number of rows inserted in temp table is : ' || v_num_rows_inserted );
1684
1685 /************************************************************************************************************/
1686
1687 l_sql_update_stm := 'UPDATE bim_camp_acqu_summ_temp tmp set tmp.measure4 = (select nvl(sum(a.initiated_revenue),0) from bim_customer_rev_summ a ' ;
1688
1689 l_sql_where := l_sql_where || ' and a.first_order_date >= :p_start_date and a.first_order_date <= :p_end_date ';
1690
1691 l_sql_where := l_sql_where || ' and tmp.view_by_name = ' || v_view_by || ' ) ' ;
1692
1693 l_sql_upd_stmt := l_sql_update_stm || from_clause || l_sql_where ;
1694
1695 /*
1696 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_update_stm, 1, 150));
1697 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_update_stm, 151, 150));
1698 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_update_stm, 300, 150));
1699 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 1, 150));
1700 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 151, 150));
1701 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 300, 150));
1702 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 451, 150));
1703
1704
1705 declare
1706 fp utl_file.file_type;
1707 location varchar2(100) := '/sqlcom/log/dom1151';
1708 file_name varchar2(50) := 'bpp2.sql';
1709 begin
1710 -- dbms_output.put_line('ENTERED THE SQL BLOCK ');
1711 fp := utl_file.fopen(location,file_name,'w');
1712 utl_file.put_line(fp,l_sql_upd_stmt);
1713 utl_file.fclose(fp);
1714 exception
1715 when UTL_FILE.INVALID_MODE then
1716 -- dbms_output.put_line('INVALID MODE EXCEPTION');
1717 when UTL_FILE.INVALID_PATH then
1718 -- dbms_output.put_line('INVALID PATH EXCEPTION');
1719 end;
1720
1721 */
1722
1723 /******************/
1724
1725 IF (p_view_by = 'PER') THEN
1726
1727 -- dbms_OUTPUT.PUT_LINE(' VIEW BY PERIOD ');
1728
1729 EXECUTE IMMEDIATE l_sql_upd_stmt
1730 USING
1731 p_start_date,
1732 p_end_date,
1733 p_campaign_status_id,
1734 p_campaign_type_id,
1735 p_media_id,
1736 p_sales_channel_code,
1737 p_market_segment_id,
1738 p_interest_type_id,
1739 p_primary_interest_code_id,
1740 p_secondary_interest_code_id,
1741 p_geography_code,
1742 p_period_type,
1743 p_start_date,
1744 p_end_date;
1745 ELSE
1746
1747 -- dbms_OUTPUT.PUT_LINE(' NOT VIEW BY PERIOD ');
1748
1749 EXECUTE IMMEDIATE l_sql_upd_stmt
1750 USING
1751 p_start_date,
1752 p_end_date,
1753 p_campaign_status_id,
1754 p_campaign_type_id,
1755 p_media_id,
1756 p_sales_channel_code,
1757 p_market_segment_id,
1758 p_interest_type_id,
1759 p_primary_interest_code_id,
1760 p_secondary_interest_code_id,
1761 p_geography_code,
1762 p_start_date,
1763 p_end_date;
1764 END IF;
1765
1766 /******************/
1767 UPDATE bim_camp_acqu_summ_temp SET measure2 = measure4*100/measure3;
1768
1769 v_num_rows_updated := SQL%ROWCOUNT;
1770 -- dbms_output.put_line ( 'Number of rows updated in temp table is : ' || v_num_rows_updated );
1771
1772 END BIM_MCH_ACQ_SUM_POPULATE ;
1773
1774 /**********************************************************************************************/
1775
1776 PROCEDURE BIM_MCH_ACQ_POPULATE
1777 (
1778 p_campaign_id number DEFAULT NULL,
1779 p_campaign_status_id number DEFAULT NULL,
1780 p_campaign_type_id varchar2 DEFAULT NULL,
1781 p_period_type varchar2 DEFAULT NULL,
1785 p_channel_id varchar2 DEFAULT NULL,
1782 p_start_date date DEFAULT NULL,
1783 p_end_date date DEFAULT NULL,
1784 p_media_id number DEFAULT NULL,
1786 p_market_segment_id number DEFAULT NULL,
1787 p_sales_channel_code varchar2 DEFAULT NULL,
1788 p_interest_type_id number DEFAULT NULL,
1789 p_primary_interest_code_id number DEFAULT NULL,
1790 p_secondary_interest_code_id number DEFAULT NULL,
1791 p_geography_code varchar2 DEFAULT NULL,
1792 p_view_by varchar2 DEFAULT NULL,
1793 p_drill_down varchar2 DEFAULT 'Y'
1794 ) IS
1795
1796 v_num_rows_inserted integer;
1797 v_num_rows_updated integer;
1798 v_view_by varchar2(50);
1799 from_clause varchar2(100);
1800 where_clause varchar2(250);
1801
1802 l_sql_from varchar2(5000);
1803 l_sql_where varchar2(5000);
1804 l_sql_group_by varchar2(5000);
1805 l_sql_order_by varchar2(5000);
1806 l_sql_insert_stm varchar2(5000);
1807 l_sql_stmt varchar2(5000);
1808 v_all_name varchar2(80);
1809
1810 l_sql_update_stm varchar2(5000);
1811 l_sql_upd_stmt varchar2(5000);
1812
1813
1814 BEGIN
1815
1816
1817 l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure3, subject_name, view_by_name)';
1818
1819 l_sql_insert_stm := l_sql_insert_stm
1820 || ' SELECT ' ;
1821
1822 l_sql_insert_stm := l_sql_insert_stm
1823 || ' count(distinct a.cust_account_id) , ';
1824
1825 l_sql_insert_stm := l_sql_insert_stm
1826 || ' nvl(sum(a.initiated_revenue), 0 ) , ';
1827
1828 /***************/
1829
1830 l_sql_where:= ' where a.period_start_date >= :p_start_date and a.period_end_date <= :p_end_date';
1831
1832 /***************/
1833
1834 l_sql_where:= l_sql_where|| ' and a.campaign_id in (select parent_campaign_id from bim_campaigns_denorm';
1835
1836 IF p_campaign_status_id IS NULL THEN
1837 l_sql_where:= l_sql_where||' where :p_campaign_status_id IS NULL' ;
1838 ELSE
1839 l_sql_where:= l_sql_where|| ' where parent_campaign_status_id = :p_campaign_status_id';
1840 END IF;
1841
1842 IF p_campaign_type_id IS NULL THEN
1843 l_sql_where:= l_sql_where|| ' and :p_campaign_type_id IS NULL';
1844 ELSE
1845 l_sql_where:= l_sql_where|| ' and parent_campaign_type = :p_campaign_type_id';
1846 END IF;
1847
1848 IF p_campaign_id IS NULL THEN
1849 l_sql_where:= l_sql_where|| ' and :p_campaign_id IS NULL ) ';
1850 ELSE
1851 l_sql_where:= l_sql_where|| ' and parent_campaign_id = :p_campaign_id ) ';
1852 END IF;
1853
1854
1855 /***************/
1856
1857 IF p_media_id IS NULL THEN
1858 l_sql_where:= l_sql_where|| ' and :p_media_id IS NULL ';
1859 ELSE
1860 l_sql_where:= l_sql_where|| ' and a.media_id = :p_media_id';
1861 END IF;
1862
1863 /***************/
1864
1865 IF p_channel_id IS NULL THEN
1866 l_sql_where:= l_sql_where|| ' and :p_channel_id IS NULL ';
1867 ELSE
1868 l_sql_where:= l_sql_where|| ' and a.channel_id = :p_channel_id';
1869 END IF;
1870
1871 /***************/
1872
1873 IF p_sales_channel_code IS NULL THEN
1874 l_sql_where:= l_sql_where|| ' and :p_sales_channel_code IS NULL ';
1875 ELSE
1876 l_sql_where:= l_sql_where|| ' and a.sales_channel_code = :p_sales_channel_code';
1877 END IF;
1878
1879 /***************/
1880
1881 IF p_market_segment_id IS NULL
1882 THEN
1883 l_sql_where:= l_sql_where|| ' and :p_market_segment_id IS NULL ';
1884 ELSE
1885 l_sql_where:= l_sql_where|| ' and a.market_segment_id = :p_market_segment_id';
1886 END IF;
1887
1891 l_sql_where:= l_sql_where|| ' and :p_interest_type_id IS NULL ';
1888 /***************/
1889
1890 IF p_interest_type_id IS NULL THEN
1892 ELSE
1893 l_sql_where:= l_sql_where|| ' and a.interest_type_id = :p_interest_type_id ';
1894 END IF;
1895
1896 IF p_primary_interest_code_id IS NULL THEN
1897 l_sql_where:= l_sql_where||' and :p_primary_interest_code_id IS NULL ';
1898 ELSE
1899 l_sql_where:= l_sql_where|| ' and a.primary_interest_code_id = :p_primary_interest_code_id ';
1900 END IF;
1901
1902 IF p_secondary_interest_code_id IS NULL THEN
1903 l_sql_where:= l_sql_where||' and :p_secondary_interest_code_id IS NULL ';
1904 ELSE
1905 l_sql_where:= l_sql_where||' and a.secondary_interest_code_id = :p_secondary_interest_code_id ';
1906 END IF;
1907
1908 /***************/
1909
1910 IF p_geography_code IS NULL THEN
1911 l_sql_where:= l_sql_where|| ' and :p_geography_code IS NULL ';
1912 ELSE
1913 l_sql_where:= l_sql_where|| ' and bill_to_geography_code like :p_geography_code';
1914 END IF;
1915
1916
1917 /***************/
1918
1919 IF p_view_by = 'CMP'
1920 THEN
1921 l_sql_insert_stm := l_sql_insert_stm || ' chn.channel_name , cmp.campaign_name ';
1922 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a , bim_dimv_channels chn ,bim_dimv_campaigns cmp' ;
1923 l_sql_where := l_sql_where || ' and a.channel_id = chn.channel_id and a.campaign_id = cmp.campaign_id';
1924 l_sql_group_by := l_sql_group_by || ' GROUP BY chn.channel_name , cmp.campaign_name ';
1925 l_sql_order_by := ' Order by 1,3 desc';
1926 v_view_by := ' cmp.campaign_name' ;
1927 from_clause := ' ,bim_dimv_campaigns cmp ' ;
1928 ELSIF p_view_by = 'ACT'
1929 THEN
1930 l_sql_insert_stm := l_sql_insert_stm || ' chn.channel_name , med.media_name ';
1931 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a, bim_dimv_channels chn ,bim_dimv_media med ' ;
1932 l_sql_where := l_sql_where || ' and a.channel_id = chn.channel_id and a.media_id = med.media_id';
1933 l_sql_group_by := l_sql_group_by || ' GROUP BY chn.channel_name, med.media_name ';
1934 l_sql_order_by := ' Order by 1,3 desc';
1935 v_view_by := ' med.media_name' ;
1936 from_clause := ' ,bim_dimv_media med ' ;
1937 ELSIF p_view_by = 'SCH'
1938 THEN
1939 l_sql_insert_stm := l_sql_insert_stm || ' chn.channel_name, sch.sales_channel_name ';
1940 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a, bim_dimv_sales_channels sch ,bim_dimv_channels chn ' ;
1941 l_sql_where := l_sql_where || ' and a.sales_channel_code = sch.sales_channel_code and a.channel_id = chn.channel_id';
1942 l_sql_group_by := l_sql_group_by || ' GROUP BY chn.channel_name, sch.sales_channel_name ';
1943 l_sql_order_by := ' Order by 1,3 desc';
1944 v_view_by := ' sch.sales_channel_name' ;
1945 from_clause := ' ,bim_dimv_sales_channels sch ' ;
1946 ELSIF p_view_by = 'MSG'
1947 THEN
1948 l_sql_insert_stm := l_sql_insert_stm || ' chn.channel_name , mkt.market_segment_name ';
1949 l_sql_from := l_sql_from || ' from bim_cmpgn_perf_summ a, bim_dimv_market_sgmts mkt ,bim_dimv_channels chn' ;
1950 l_sql_where := l_sql_where || ' and a.market_segment_id = mkt.market_segment_id and a.channel_id = chn.channel_id';
1951 l_sql_group_by := l_sql_group_by || ' GROUP BY chn.channel_name , mkt.market_segment_name ';
1952 l_sql_order_by := ' Order by 1,3 desc';
1953 v_view_by := ' mkt.market_segment_name' ;
1954 from_clause := ' ,bim_dimv_market_sgmts mkt ' ;
1955 ELSIF p_view_by = 'PER' THEN
1956 l_sql_insert_stm := l_sql_insert_stm || ' chn.channel_name, per.period_name ';
1957 l_sql_from:= l_sql_from|| ' from bim_cmpgn_perf_summ a, bim_dimv_channels chn, bim_dimv_periods per,dual ' ;
1958 l_sql_where:= l_sql_where|| ' and a.channel_id = chn.channel_id and a.period_start_date >= per.start_date and a.period_end_date <= per.end_date
1959 and per.period_set_name = jtf_bis_util.ProfileValue(''CRMBIS:PERIOD_SET_NAME'') and per.period_type = :p_period_type ';
1960 l_sql_group_by := l_sql_group_by || ' GROUP BY chn.channel_name, per.period_name , per.start_date ';
1961 l_sql_order_by := ' Order by chn.channel_name, per.start_date ';
1962 v_view_by := ' per.period_name' ;
1963 from_clause := ' ,bim_dimv_periods per ' ;
1964 END IF;
1965
1966 /***************/
1967
1968 l_sql_stmt := l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by;
1969
1970
1971 /*
1972 declare
1973 fp utl_file.file_type;
1974 location varchar2(100) := '/sqlcom/log/dom1151';
1975 file_name varchar2(50) := 'mm1.sql';
1976 begin
1977 -- dbms_output.put_line('ENTERED THE SQL BLOCK ');
1978 fp := utl_file.fopen(location,file_name,'w');
1979 utl_file.put_line(fp,l_sql_insert_stm);
1980 utl_file.put_line(fp,l_sql_from);
1981 utl_file.put_line(fp,l_sql_where);
1982 utl_file.put_line(fp,l_sql_group_by);
1983 utl_file.fclose(fp);
1984 exception
1985 when UTL_FILE.INVALID_MODE then
1986 -- dbms_output.put_line('INVALID MODE EXCEPTION');
1987 when UTL_FILE.INVALID_PATH then
1988 -- dbms_output.put_line('INVALID PATH EXCEPTION');
1989 end;
1990 */
1991
1992
1993 /******************/
1994
1995 IF (p_view_by = 'PER') THEN
1996
1997 EXECUTE IMMEDIATE l_sql_stmt
1998 USING
1999 p_start_date,
2000 p_end_date,
2001 p_campaign_status_id,
2002 p_campaign_type_id,
2003 p_campaign_id,
2004 p_media_id,
2005 p_channel_id,
2006 p_sales_channel_code,
2007 p_market_segment_id,
2008 p_interest_type_id,
2009 p_primary_interest_code_id,
2010 p_secondary_interest_code_id,
2011 p_geography_code,
2012 p_period_type;
2013 ELSE
2014 EXECUTE IMMEDIATE l_sql_stmt
2015 USING
2016 p_start_date,
2017 p_end_date,
2018 p_campaign_status_id,
2019 p_campaign_type_id,
2020 p_campaign_id,
2021 p_media_id,
2022 p_channel_id,
2023 p_sales_channel_code,
2024 p_market_segment_id,
2025 p_interest_type_id,
2026 p_primary_interest_code_id,
2027 p_secondary_interest_code_id,
2028 p_geography_code;
2029
2030 END IF;
2031
2032 v_num_rows_inserted := SQL%ROWCOUNT;
2033 -- dbms_output.put_line ( 'Number of rows inserted in temp table is : ' || v_num_rows_inserted );
2034
2038
2035 /************************************************************************************************************/
2036
2037 l_sql_update_stm := 'UPDATE bim_camp_acqu_summ_temp tmp set tmp.measure4 = (select nvl(sum(a.initiated_revenue),0) from bim_customer_rev_summ a, bim_dimv_channels chn ' ;
2039 l_sql_where := l_sql_where || ' and a.first_order_date >= :p_start_date and a.first_order_date <= :p_end_date ';
2040
2041 l_sql_where := l_sql_where || ' and tmp.subject_name = chn.channel_name and tmp.view_by_name = ' || v_view_by || ' ) ' ;
2042
2043 l_sql_upd_stmt := l_sql_update_stm || from_clause || l_sql_where ;
2044
2045 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_update_stm, 1, 150));
2046 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_update_stm, 151, 150));
2047 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_update_stm, 300, 150));
2048 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 1, 150));
2049 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 151, 150));
2050 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 300, 150));
2051 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 451, 150));
2052
2053 /******************/
2054
2055 /*
2056 declare
2057 fp utl_file.file_type;
2058 location varchar2(100) := '/sqlcom/log/dom1151';
2059 file_name varchar2(50) := 'bpp21.sql';
2060 begin
2061 -- dbms_output.put_line('ENTERED THE SQL BLOCK ');
2062 fp := utl_file.fopen(location,file_name,'w');
2063 utl_file.put_line(fp,l_sql_upd_stmt);
2064 utl_file.fclose(fp);
2065 exception
2066 when UTL_FILE.INVALID_MODE then
2067 -- dbms_output.put_line('INVALID MODE EXCEPTION');
2068 when UTL_FILE.INVALID_PATH then
2069 -- dbms_output.put_line('INVALID PATH EXCEPTION');
2070 end;
2071 */
2072
2073 /******************/
2074
2075 IF (p_view_by = 'PER') THEN
2076
2077 -- dbms_OUTPUT.PUT_LINE(' VIEW BY PERIOD ');
2078
2079 EXECUTE IMMEDIATE l_sql_upd_stmt
2080 USING
2081 p_start_date,
2082 p_end_date,
2083 p_campaign_status_id,
2084 p_campaign_type_id,
2085 p_campaign_id,
2086 p_media_id,
2087 p_channel_id,
2088 p_sales_channel_code,
2089 p_market_segment_id,
2090 p_interest_type_id,
2091 p_primary_interest_code_id,
2092 p_secondary_interest_code_id,
2093 p_geography_code,
2094 p_period_type,
2095 p_start_date,
2096 p_end_date;
2097 ELSE
2098
2102 USING
2099 -- dbms_OUTPUT.PUT_LINE(' NOT VIEW BY PERIOD ');
2100
2101 EXECUTE IMMEDIATE l_sql_upd_stmt
2103 p_start_date,
2104 p_end_date,
2105 p_campaign_status_id,
2106 p_campaign_type_id,
2107 p_campaign_id,
2108 p_media_id,
2109 p_channel_id,
2110 p_sales_channel_code,
2111 p_market_segment_id,
2112 p_interest_type_id,
2113 p_primary_interest_code_id,
2114 p_secondary_interest_code_id,
2115 p_geography_code,
2116 p_start_date,
2117 p_end_date;
2118 END IF;
2119
2120
2121 /******************/
2122
2123
2124
2125 UPDATE bim_camp_acqu_summ_temp SET measure2 = measure4*100/measure3;
2126
2127 v_num_rows_updated := SQL%ROWCOUNT;
2128 -- dbms_output.put_line ( 'Number of rows updated in temp table is : ' || v_num_rows_updated );
2129
2130
2131 END BIM_MCH_ACQ_POPULATE ;
2132
2133 END BIM_ACQ_PKG;