eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.BIM_CMP_PKG

Source


1 PACKAGE BODY BIM_CMP_PKG AS
2 /*$Header: bimcmpgb.pls 115.18 2001/08/13 16:09:51 pkm ship      $*/
3 
4 PROCEDURE BIM_CMP_COST_PLEAD_POPULATE
5          (
6             p_start_date                  in date        DEFAULT NULL,
7             p_end_date                    in date        DEFAULT NULL,
8             p_campaign_id                 in number      DEFAULT NULL,
9             p_drill_down                  in varchar2    DEFAULT NULL,
10             p_campaign_status_id          in number      DEFAULT NULL,
11             p_campaign_type_id            in varchar2    DEFAULT NULL,
12             p_media_id                    in number      DEFAULT NULL,
13             p_channel_id                  in varchar2    DEFAULT NULL,
14             p_period_type                 in varchar2    DEFAULT NULL,
15             p_view_by                     in varchar2    DEFAULT NULL
16          )  IS
17 
18 l_sql_insert_stm            varchar2(10000);
19 l_sql_from                  varchar2(10000);
20 l_sql_where                 varchar2(30000);
21 l_sql_group_by              varchar2(2000);
22 l_sql_order_by              varchar2(2000);
23 l_sql_outer_where           varchar2(2000);
24 l_view_by                   varchar2(2000);
25 l_subject_name              varchar2(2000);
26 l_jtf_bis_util              varchar2(2000);
27 
28 begin
29 l_jtf_bis_util := 'jtf_bis_util.profileValue(''CRMBIS:PERIOD_SET_NAME'')';
30 
31 l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, subject_name, view_by_name)';
32 
33 l_sql_insert_stm := l_sql_insert_stm
34                  || ' SELECT ' ;
35 
36 l_sql_insert_stm := l_sql_insert_stm
37                  || ' Sum(bscc.num_of_leads),';
38 
39 l_sql_insert_stm := l_sql_insert_stm
40                  || ' decode(sum(BSCC.num_of_leads),0,0,( sum (BSCC.actual_cost) / sum (BSCC.num_of_leads) )),';
41 
42 l_sql_from := ' FROM bim_cmpgn_revcost_summ BSCC, bim_campaigns_denorm denorm,bim_dimv_campaigns BDC';
43 
44 --Build the where clause
45 
46 l_sql_where := ' where BSCC.campaign_id = denorm.campaign_id and denorm.parent_campaign_id = BDC.campaign_id and BSCC.PERIOD_start_date >= :p_start_date and BSCC.PERIOD_end_date <= :p_end_date';
47 
48 IF p_campaign_id IS NULL then
49     l_sql_where := l_sql_where || ' and :p_campaign_id is null ';
50 ELSIF p_drill_down = 'Y' then
51     l_sql_where := l_sql_where || ' and NVL(denorm.parent_campaign_id,-999) = :p_campaign_id';
52 ELSE l_sql_where := l_sql_where || ' and denorm.campaign_id =:p_campaign_id ';
53 END IF;
54 
55 IF p_campaign_status_id IS NOT NULL
56 THEN
57     l_sql_where := l_sql_where || ' and denorm.parent_campaign_status_id = :p_campaign_status_id';
58 ELSE
59     l_sql_where := l_sql_where || ' and :p_campaign_status_id IS NULL';
60 END IF;
61 
62 IF p_campaign_type_id IS NOT NULL
63 THEN
64     l_sql_where := l_sql_where || ' And denorm.parent_campaign_type = :p_campaign_type_id';
65 ELSE
66     l_sql_where := l_sql_where || ' and :p_campaign_type_id IS NULL ';
67 END IF;
68 
69 IF P_media_id IS NOT NULL
70 THEN
71     l_sql_where := l_sql_where || ' and BSCC.Media_id = :P_media_id';
72 ELSE
73     l_sql_where := l_sql_where || ' and :P_media_id IS NULL';
74 END IF;
75 
76 IF p_channel_id IS NOT NULL
77 THEN
78     l_sql_where := l_sql_where || ' and BSCC.channel_id = :p_channel_id';
79 ELSE
80     l_sql_where := l_sql_where || ' and :p_channel_id IS NULL';
81 END IF;
82 
83 if p_view_by = 'PER' THEN
84 IF p_period_type IS NOT NULL
85 THEN
86     l_sql_where := l_sql_where || ' AND BDP.PERIOD_TYPE like :p_period_type';
87 ELSE
88     l_sql_where := l_sql_where || ' and :p_period_type IS NULL';
89 END IF;
90 END IF;
91 
92 if p_view_by = 'MCH'
93 THEN
94    l_sql_insert_stm := l_sql_insert_stm || ' BDC.campaign_name,BDMC.Channel_name ';
95    l_sql_from := l_sql_from || ' ,BIM_DIMV_MEDIA_CHANNELS BDMC, dual';
96    l_sql_where := l_sql_where || ' AND BSCC.media_id = BDMC.media_id and BSCC.channel_id = BDMC.channel_id';
97    l_sql_group_by := l_sql_group_by || ' GROUP BY BDC.campaign_name, BDMC.channel_name   ';
98    l_sql_order_by := l_sql_order_by || ' Order by 3,2 desc';
99 ELSIF
100    p_view_by = 'ACT'
101 THEN
102    l_sql_insert_stm := l_sql_insert_stm || ' BDC.campaign_name,bdm.media_name ';
103    l_sql_from := l_sql_from || ' ,bim_dimv_media bdm, dual';
104    l_sql_where := l_sql_where || ' and bscc.media_id = BDM.media_id ';
105    l_sql_group_by := l_sql_group_by || ' GROUP BY BDC.campaign_name,bdm.media_name ';
106    l_sql_order_by := l_sql_order_by || ' Order by 3,2 desc';
107 ELSIF
108    p_view_by = 'PER'
109 THEN
110    l_sql_insert_stm := l_sql_insert_stm || ' BDC.Campaign_name,BSCC.period_name ';
111    l_sql_from := l_sql_from || ' ,bim_dimv_periods bdp, dual';
112    l_sql_where := l_sql_where || ' and bscc.period_start_date = bdp.start_date and bscc.period_end_date = bdp.end_date and BDP.period_set_name  =' || l_jtf_bis_util ;
113    l_sql_group_by := l_sql_group_by || ' GROUP BY BDC.Campaign_name,BSCC.period_name, bdp.start_date ';
114    l_sql_order_by := l_sql_order_by || ' Order by BDC.Campaign_name,bdp.start_date';
115 END IF;
116 
117 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 1, 150));
118 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 151, 150));
119 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 300, 150));
120 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_from, 1, 150));
121 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_from, 151, 150));
122 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 1, 150));
123 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 151, 150));
124 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 300, 150));
125 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 451, 150));
126 -- dbms_OUTPUT.PUT_LINE(l_sql_group_by);
127 -- dbms_OUTPUT.PUT_LINE(l_sql_order_by);
128 
129 IF P_VIEW_BY = 'PER' THEN
130 EXECUTE IMMEDIATE l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by
131 Using
132  p_start_date
133 ,p_end_date
134 ,p_campaign_id
135 ,p_campaign_status_id
136 ,p_campaign_type_id
137 ,p_media_id
138 ,p_channel_id
139 ,P_PERIOD_TYPE;
140 ELSE
141 EXECUTE IMMEDIATE l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by
142 Using
143  p_start_date
144 ,p_end_date
145 ,p_campaign_id
146 ,p_campaign_status_id
147 ,p_campaign_type_id
148 ,p_media_id
149 ,p_channel_id;
150 END IF;
151 
152 end BIM_CMP_COST_PLEAD_POPULATE;
153 
154 
155 PROCEDURE BIM_CMP_COST_POPULATE
156          (
157             p_start_date                  in date      DEFAULT NULL,
158             p_end_date                    in date      DEFAULT NULL,
159             p_campaign_id                 in number    DEFAULT NULL,
160             p_drill_down                  in varchar2  DEFAULT 'Y',
161             p_campaign_status_id          in number    DEFAULT NULL,
162             p_campaign_type_id            in varchar2  DEFAULT NULL,
163             p_media_id                    in number    DEFAULT NULL,
164             p_channel_id                  in varchar2  DEFAULT NULL,
165             p_view_by                     in varchar2  DEFAULT NULL
166          )  IS
167 
168 l_sql_insert_stm            varchar2(10000);
169 l_sql_from                  varchar2(10000);
170 l_sql_where                 varchar2(30000);
171 l_sql_group_by              varchar2(2000);
172 l_sql_order_by              varchar2(2000);
173 l_sql_outer_where           varchar2(2000);
174 l_view_by                   varchar2(2000);
175 l_subject_name              varchar2(2000);
176 
177 begin
178 
179 l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, measure3, measure4, subject_name, view_by_name)';
180 
181 l_sql_insert_stm := l_sql_insert_stm
182                  || ' SELECT ' ;
183 
184 l_sql_insert_stm := l_sql_insert_stm
185                  || ' decode(sum(ccost.actual_cost), 0, 0, ((sum(ccost.initiated_revenue)-sum(ccost.actual_cost))/sum(ccost.actual_cost))*100),';
186 
187 l_sql_insert_stm := l_sql_insert_stm
188                  || ' sum(ccost.forecasted_cost),';
189 
190 l_sql_insert_stm := l_sql_insert_stm
191                  || ' sum(ccost.actual_cost),';
192 
193 l_sql_insert_stm := l_sql_insert_stm
194                  || ' decode(sum(ccost.actual_cost), 0,0,((sum(ccost.forecasted_cost)-sum(ccost.actual_cost))/sum(ccost.actual_cost))*100),';
195 
196 l_sql_from := ' From BIM_CMPGN_REVCOST_SUMM ccost, bim_campaigns_denorm denorm, bim_dimv_campaigns dimv, bim_dimv_media_channels bdmc, dual';
197 
198 --Build the where clause
199 
200 l_sql_where := ' WHERE ccost.campaign_id = denorm.campaign_id AND CCOST.media_id = BDMC.media_id and CCOST.channel_id
201 = BDMC.channel_id and denorm.parent_campaign_id = dimv.campaign_id and CCOST.PERIOD_start_date >= :p_start_date and CCOST.PERIOD_end_date <= :p_end_date';
202 
203 IF p_campaign_id IS NULL then
204     l_sql_where := l_sql_where || ' and :p_campaign_id is null ';
205 ELSIF p_drill_down = 'Y' then
206     l_sql_where := l_sql_where || ' and NVL(denorm.parent_campaign_id,-999) = :p_campaign_id';
207 ELSE l_sql_where := l_sql_where || ' and denorm.campaign_id =:p_campaign_id ';
208 END IF;
209 
210 IF p_campaign_status_id IS NOT NULL
211 THEN
212     l_sql_where := l_sql_where || ' and denorm.parent_campaign_status_id = :p_campaign_status_id';
213 ELSE
214     l_sql_where := l_sql_where || ' and :p_campaign_status_id IS NULL';
215 END IF;
216 
217 IF p_campaign_type_id IS NOT NULL
218 THEN
219     l_sql_where := l_sql_where || ' And denorm.parent_campaign_type = :p_campaign_type_id';
220 ELSE
221     l_sql_where := l_sql_where || ' and :p_campaign_type_id IS NULL ';
222 END IF;
223 
224 IF P_media_id IS NOT NULL
225 THEN
226     l_sql_where := l_sql_where || ' and BSCC.Media_id = :P_media_id';
227 ELSE
228     l_sql_where := l_sql_where || ' and :P_media_id IS NULL';
229 END IF;
230 
231 IF p_channel_id IS NOT NULL
232 THEN
233     l_sql_where := l_sql_where || ' and BSCC.channel_id = :p_channel_id';
234 ELSE
235     l_sql_where := l_sql_where || ' and :p_channel_id IS NULL';
236 END IF;
237 
238 l_sql_order_by := ' ORDER BY 1,3,6 DESC';
239 
240 if p_view_by = 'MCH'
241 THEN
242    l_sql_insert_stm := l_sql_insert_stm || ' dimv.campaign_name, BDMC.channel_name ';
243    l_sql_group_by := l_sql_group_by || ' GROUP BY dimv.campaign_name, BDMC.channel_name   ';
244 ELSIF
245    p_view_by = 'ACT'
246 THEN
247    l_sql_insert_stm := l_sql_insert_stm || ' dimv.campaign_name, bdmc.media_name ';
248    l_sql_group_by := l_sql_group_by || ' GROUP BY dimv.campaign_name, bdmc.media_name ';
249 END IF;
250 
251 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 1, 150));
252 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 151, 150));
253 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 300, 150));
254 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_from, 1, 150));
255 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_from, 151, 150));
256 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 1, 150));
257 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 151, 150));
258 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 300, 150));
259 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 451, 150));
260 -- dbms_OUTPUT.PUT_LINE(l_sql_group_by);
261 -- dbms_OUTPUT.PUT_LINE(l_sql_order_by);
262 
263 EXECUTE IMMEDIATE l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by
264 Using
265 p_start_date
266 ,p_end_date
267 ,p_campaign_id
268 ,p_campaign_status_id
269 ,p_campaign_type_id
270 ,p_media_id
271 ,p_channel_id;
272 
273 end BIM_CMP_COST_POPULATE;
274 
275 PROCEDURE BIM_CMP_COST_SUM_POPULATE
276          (
277             p_all_value                   in varchar2 DEFAULT NULL,
278             p_campaign_status_id          in number   DEFAULT NULL,
279             p_campaign_type_id            in varchar2 DEFAULT NULL,
280             p_media_id                    in number   DEFAULT NULL,
281             p_channel_id                  in varchar2 DEFAULT NULL,
282             p_view_by                     in varchar2 DEFAULT NULL
283          )  IS
284 
285 l_sql_insert_stm            varchar2(10000);
286 l_sql_from                  varchar2(10000);
287 l_sql_where                 varchar2(30000);
288 l_sql_group_by              varchar2(2000);
289 l_sql_order_by              varchar2(2000);
290 l_sql_outer_where           varchar2(2000);
291 l_view_by                   varchar2(2000);
292 l_subject_name              varchar2(2000);
293 
294 begin
295 
296 l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, measure3, measure4, subject_name, view_by_name)';
297 
298 l_sql_insert_stm := l_sql_insert_stm
299                  || ' SELECT ' ;
300 
301 l_sql_insert_stm := l_sql_insert_stm
302                  || ' decode(sum(ccost.actual_cost), 0, 0, ((sum(ccost.initiated_revenue)-sum(ccost.actual_cost))/sum(ccost.actual_cost))*100),';
303 
304 l_sql_insert_stm := l_sql_insert_stm
305                  || ' sum(ccost.forecasted_cost),';
306 
307 l_sql_insert_stm := l_sql_insert_stm
308                  || ' sum(ccost.actual_cost),';
309 
310 l_sql_insert_stm := l_sql_insert_stm
311                  || ' decode(sum(ccost.actual_cost), 0,0,((sum(ccost.forecasted_cost)-sum(ccost.actual_cost))/sum(ccost.actual_cost))*100),';
312 
313 l_sql_from := ' From BIM_CMPGN_REVCOST_SUMM ccost, bim_campaigns_denorm denorm, bim_dimv_campaigns dimv, bim_dimv_media_channels bdmc, dual';
314 
315 --Build the where clause
316 
317 l_sql_where := ' WHERE ccost.campaign_id = denorm.campaign_id AND CCOST.media_id = BDMC.media_id and CCOST.channel_id = BDMC.channel_id and denorm.parent_campaign_id = dimv.campaign_id and denorm.level_from_parent = 0';
318 
319 IF p_campaign_status_id IS NOT NULL
320 THEN
321     l_sql_where := l_sql_where || ' and denorm.parent_campaign_status_id = :p_campaign_status_id';
322 ELSE
323     l_sql_where := l_sql_where || ' and :p_campaign_status_id IS NULL';
324 END IF;
325 
326 IF p_campaign_type_id IS NOT NULL
327 THEN
328     l_sql_where := l_sql_where || ' And denorm.parent_campaign_type = :p_campaign_type_id';
329 ELSE
330     l_sql_where := l_sql_where || ' and :p_campaign_type_id IS NULL ';
331 END IF;
332 
333 IF P_media_id IS NOT NULL
334 THEN
335     l_sql_where := l_sql_where || ' and BSCC.Media_id = :P_media_id';
336 ELSE
337     l_sql_where := l_sql_where || ' and :P_media_id IS NULL';
338 END IF;
339 
340 IF p_channel_id IS NOT NULL
341 THEN
342     l_sql_where := l_sql_where || ' and BSCC.channel_id = :p_channel_id';
343 ELSE
344     l_sql_where := l_sql_where || ' and :p_channel_id IS NULL';
345 END IF;
346 
347 l_sql_order_by := ' ORDER BY 1,3,6 DESC';
348 
349 if p_view_by = 'MCH'
350 THEN
351    l_sql_insert_stm := l_sql_insert_stm || ' :p_all_value, bdmc.channel_name ';
352    l_sql_group_by := l_sql_group_by || ' GROUP BY bdmc.channel_name   ';
353 ELSIF
354    p_view_by = 'ACT'
355 THEN
356    l_sql_insert_stm := l_sql_insert_stm || ' :p_all_value, bdmc.media_name ';
357    l_sql_group_by := l_sql_group_by || ' GROUP BY  bdmc.media_name ';
358 END IF;
359 
360 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 1, 150));
361 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 151, 150));
362 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 300, 150));
363 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_from, 1, 150));
364 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_from, 151, 150));
365 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 1, 150));
366 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 151, 150));
367 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 300, 150));
368 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 451, 150));
369 -- dbms_OUTPUT.PUT_LINE(l_sql_group_by);
370 -- dbms_OUTPUT.PUT_LINE(l_sql_order_by);
371 
372 EXECUTE IMMEDIATE l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by
373 Using
374 p_all_value
375 ,p_campaign_status_id
376 ,p_campaign_type_id
377 ,p_media_id
378 ,p_channel_id;
379 
383 PROCEDURE    BIM_CMP_PERF_POPULATE
380 end BIM_CMP_COST_SUM_POPULATE;
381 
382 
384          (
385             p_start_date                  in  date      DEFAULT NULL,
386             p_end_date                    in  date      DEFAULT NULL,
387             p_campaign_id                 in  number    DEFAULT NULL,
388             p_campaign_type_id            in  varchar2  DEFAULT NULL,
389             p_campaign_status_id          in  number    DEFAULT NULL,
390             p_media_id                    in  number    DEFAULT NULL,
391             p_channel_id                  in  varchar2  DEFAULT NULL,
392 		    p_drill_down			      in  varchar2  DEFAULT 'Y',
393             p_sales_channel_code          in  varchar2  DEFAULT NULL,
394             p_market_segment_id           in  number    DEFAULT NULL,
395             p_interest_type_id            in  number    DEFAULT NULL,
396             p_primary_interest_code_id    in  number    DEFAULT NULL,
397             p_secondary_interest_code_id  in  number    DEFAULT NULL,
398             p_geography_code              in  varchar2  DEFAULT NULL,
399             p_period_type                 in  varchar2  DEFAULT NULL,
400             p_view_by                     in  varchar2  DEFAULT NULL
401          )  IS
402 
403 v_num_rows_inserted  integer;
404 v_num_rows_updated   integer;
405 
406 l_view_by            	varchar2(50);
407 
408 l_sql_from		varchar2(5000);
409 l_sql_where	        varchar2(5000);
410 l_sql_group_by    	varchar2(5000);
411 l_sql_order_by		varchar2(5000);
412 l_sql_insert_stm 	varchar2(5000);
413 l_sql_stmt		varchar2(5000);
414 v_all_name           	varchar2(80);
415 
416 BEGIN
417 
418 /***************/
419 
420 l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, subject_name, view_by_name)';
421 
422 l_sql_insert_stm := l_sql_insert_stm
423                  || ' SELECT ' ;
424 
425 l_sql_insert_stm := l_sql_insert_stm
426                  || ' count(distinct(a.lead_id)), ';
427 
428 l_sql_insert_stm := l_sql_insert_stm
429                  || ' round(decode(count(distinct(a.lead_id)),0,0,sum(a.initiated_revenue)/count(distinct(a.lead_id))),2), ';
430 
431 /***************/
432 
433 /* l_sql_from := ' from  bim_cmpgn_perf_summ a '; */
434 
435 /***************/
436 
437 l_sql_where:= l_sql_where ||' where a.period_start_date   >= :p_start_date and a.period_end_date <= :p_end_date';
438 
439 /***************/
440 
441 IF p_campaign_id IS NULL THEN
442 	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 ) ';
443 ELSE
444 	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 ) ';
445 END IF;
446 
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 IF 	p_channel_id IS  NULL THEN
458 	l_sql_where:= l_sql_where|| ' and :p_channel_id IS NULL ';
459 ELSE
460 	l_sql_where:= l_sql_where|| ' and  a.channel_id = :p_channel_id';
461 END IF;
462 
463 /***************/
464 
465 IF 	p_sales_channel_code IS NULL THEN
466 	l_sql_where:= l_sql_where|| ' and :p_sales_channel_code IS NULL ';
467 ELSE
468 	l_sql_where:= l_sql_where|| ' and a.sales_channel_code = :p_sales_channel_code';
469 END IF;
470 
471 /***************/
472 
473 IF 	p_market_segment_id IS NULL
474 THEN
475 	l_sql_where:= l_sql_where|| ' and :p_market_segment_id IS NULL ';
476 ELSE
477 	l_sql_where:= l_sql_where|| ' and a.market_segment_id   = :p_market_segment_id';
478 END IF;
479 
480 
481 /***************/
482 
483 /* l_sql_where:= l_sql_where|| ' and a.interest_type_id  = d.interest_type_id and a.primary_interest_code_id = d.primary_interest_code_id and a.secondary_interest_code_id = d.secondary_interest_code_id'; */
484 
485 IF 	p_interest_type_id IS NULL THEN
486 	l_sql_where:= l_sql_where|| ' and :p_interest_type_id IS NULL ';
487 ELSE
488 	l_sql_where:= l_sql_where|| ' and a.interest_type_id = :p_interest_type_id ';
489 END IF;
490 
491 /***************/
492 
493 IF 	p_primary_interest_code_id IS NULL THEN
494 	l_sql_where:= l_sql_where||' and :p_primary_interest_code_id IS NULL ';
495 ELSE
496 	l_sql_where:= l_sql_where|| ' and a.primary_interest_code_id = :p_primary_interest_code_id ';
497 END IF;
498 
499 /***************/
500 
501 IF 	p_secondary_interest_code_id IS NULL THEN
502 	l_sql_where:= l_sql_where||' and :p_secondary_interest_code_id IS NULL ';
503 ELSE
504 	l_sql_where:= l_sql_where||' and a.secondary_interest_code_id = :p_secondary_interest_code_id ';
505 END IF;
506 
507 
508 /***************/
509 
510 IF 	p_geography_code IS NULL THEN
511 	l_sql_where:= l_sql_where|| ' and :p_geography_code IS NULL ';
512 ELSE
513 	l_sql_where:= l_sql_where|| ' and bill_to_geography_code like :p_geography_code';
514 END IF;
515 
516 /***************/
517 
521 	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 = c.media_id and a.channel_id = c.channel_id';
518 IF 	p_view_by = 'MCH' THEN
519 	l_sql_insert_stm := l_sql_insert_stm || ' d.campaign_name, c.channel_name';
520 	l_sql_from	:= l_sql_from|| ' from bim_cmpgn_perf_summ a,bim_campaigns_denorm b,bim_dimv_campaigns d, bim_dimv_media_channels c, dual ' ;
522 	l_sql_group_by 	:= l_sql_group_by || ' GROUP BY  d.campaign_name , c.channel_name ';
523     l_sql_order_by := ' Order by 3,2 desc ';
524 ELSIF p_view_by = 'ACT' THEN
525 	l_sql_insert_stm := l_sql_insert_stm || ' d.campaign_name, e.media_name ';
526 	l_sql_from	:= l_sql_from|| ' from 	bim_cmpgn_perf_summ a,bim_campaigns_denorm b,bim_dimv_campaigns d,bim_dimv_media e, dual ' ;
527 	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 = e.media_id ';
528 	l_sql_group_by 	:= l_sql_group_by || ' GROUP BY  d.campaign_name, e.media_name ';
529     l_sql_order_by := ' Order by 3,2 desc ';
530 ELSIF p_view_by = 'MSG' THEN
531 	l_sql_insert_stm := l_sql_insert_stm || ' d.campaign_name , e.market_segment_name ';
532 	l_sql_from:= l_sql_from|| ' from 	bim_cmpgn_perf_summ a,bim_campaigns_denorm b ,bim_dimv_campaigns d,bim_dimv_market_sgmts e,dual ' ;
533 	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 = e.market_segment_id ';
534 	l_sql_group_by := l_sql_group_by || ' GROUP BY  d.campaign_name , e.market_segment_name ';
535     l_sql_order_by := ' Order by 3,2 desc ';
536 ELSIF p_view_by = 'SCH' THEN
537 	l_sql_insert_stm := l_sql_insert_stm || ' d.campaign_name , e.sales_channel_name ';
538 	l_sql_from:= l_sql_from|| ' from 	bim_cmpgn_perf_summ a,bim_campaigns_denorm b,bim_dimv_campaigns d,bim_dimv_sales_channels e, dual ' ;
539 	l_sql_where:=  l_sql_where||' and a.campaign_id = b.campaign_id and b.campaign_id = d.campaign_id and a.sales_channel_code= e.sales_channel_code ';
540 	l_sql_group_by := l_sql_group_by || ' GROUP BY d.campaign_name, e.sales_channel_name ';
541     l_sql_order_by := ' Order by 3,2 desc ';
542 ELSIF 	p_view_by = 'PER' THEN
543 	l_sql_insert_stm := l_sql_insert_stm || ' d.campaign_name , e.period_name ';
544 	l_sql_from:= l_sql_from|| ' from 	bim_cmpgn_perf_summ a,bim_campaigns_denorm b,bim_dimv_campaigns d, bim_dimv_periods e, dual ' ;
545 	l_sql_where:=  l_sql_where|| ' and a.campaign_id = b.campaign_id and b.campaign_id = d.campaign_id and a.period_start_date >= e.start_date
546 and  a.period_end_date <= e.end_date and e.period_set_name = jtf_bis_util.ProfileValue(''CRMBIS:PERIOD_SET_NAME'')';
547     IF p_period_type IS NOT NULL
548     THEN
549         l_sql_where := l_sql_where || ' and e.PERIOD_TYPE like :p_period_type';
550     ELSE
551         l_sql_where := l_sql_where || ' and :p_period_type IS NULL';
552     END IF;
553     l_sql_group_by := l_sql_group_by || ' GROUP BY d.campaign_name , e.period_name, e.start_date';
554     l_sql_order_by := ' Order by d.campaign_name, e.start_date ';
555 END IF;
556 
557 /***************/
558 
559 l_sql_stmt := l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by;
560 -- dbms_OUTPUT.PUT_LINE('COMING HERE ');
561 
562 
563 /*
564 declare
565 fp  		utl_file.file_type;
566 location 	varchar2(100) := '/sqlcom/log/dom1151';
567 file_name	varchar2(50) := 'bpp.sql';
568 begin
569 		-- dbms_output.put_line('ENTERED THE SQL BLOCK ');
570 		fp := 	utl_file.fopen(location,file_name,'w');
571 		utl_file.put_line(fp,l_sql_insert_stm);
572 		utl_file.put_line(fp,l_sql_from);
573 		utl_file.put_line(fp,l_sql_where);
574 		utl_file.put_line(fp,l_sql_group_by);
575 		utl_file.fclose(fp);
576 exception
577 when 	UTL_FILE.INVALID_MODE then
578 	 	-- dbms_output.put_line('INVALID MODE EXCEPTION');
579 when 	UTL_FILE.INVALID_PATH then
580 		-- dbms_output.put_line('INVALID PATH EXCEPTION');
581 end;
582 */
583 
584 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 1, 150));
585 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 151, 150));
586 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 300, 150));
587 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_from, 1, 150));
588 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_from, 151, 150));
589 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 1, 150));
590 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 151, 150));
591 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 300, 150));
592 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 451, 150));
593 -- dbms_OUTPUT.PUT_LINE(l_sql_group_by);
594 -- dbms_OUTPUT.PUT_LINE(l_sql_order_by);
595 
596 
597 
598 
599 /******************/
600 
601 -- dbms_output.put_line('COMPLETED THE SQL STATEMENT ');
602 
603 if (p_view_by = 'PER') THEN
604 EXECUTE IMMEDIATE l_sql_stmt
605     USING
606      p_start_date,
607      p_end_date,
608      p_campaign_id,
609      p_media_id,
610      p_channel_id,
611      p_sales_channel_code,
612      p_market_segment_id,
613      p_interest_type_id,
614      p_primary_interest_code_id,
615      p_secondary_interest_code_id,
616      p_geography_code,
617      p_period_type;
618 ELSE
619 EXECUTE IMMEDIATE l_sql_stmt
620     USING
621      p_start_date,
622      p_end_date,
623      p_campaign_id,
624      p_media_id,
625      p_channel_id,
626      p_sales_channel_code,
627      p_market_segment_id,
628      p_interest_type_id,
629      p_primary_interest_code_id,
630      p_secondary_interest_code_id,
631      p_geography_code;
635 --    dbms_output.put_line ( 'Number of rows inserted in temp table IS : ' || v_num_rows_inserted );
632 END IF;
633 
634    v_num_rows_inserted := SQL%ROWCOUNT;
636 
637 END   BIM_CMP_PERF_POPULATE ;
638 
639 PROCEDURE   BIM_CMP_PERF_SUM_POPULATE
640          (
641             p_period_type                 in  varchar2  DEFAULT NULL,
642             p_all_value				      in  varchar2  DEFAULT 'ALL',
643             p_start_date                  in  date      DEFAULT NULL,
644             p_end_date                    in  date      DEFAULT NULL,
645             p_campaign_type_id            in  varchar2  DEFAULT NULL,
646             p_campaign_status_id          in  number    DEFAULT NULL,
647             p_media_id                    in  number    DEFAULT NULL,
648             p_channel_id                  in  varchar2  DEFAULT NULL,
649             p_sales_channel_code          in  varchar2  DEFAULT NULL,
650             p_market_segment_id           in  number    DEFAULT NULL,
651             p_interest_type_id            in  number    DEFAULT NULL,
652             p_primary_interest_code_id    in  number    DEFAULT NULL,
653             p_secondary_interest_code_id  in  number    DEFAULT NULL,
654             p_geography_code              in  varchar2  DEFAULT NULL,
655             p_view_by                     in  varchar2  DEFAULT NULL
656          )  IS
657 
658 v_num_rows_inserted  	integer;
659 v_num_rows_updated   	integer;
660 
661 l_view_by            	varchar2(50);
662 l_sql_from			varchar2(5000);
663 l_sql_where		      varchar2(5000);
664 l_sql_group_by    	varchar2(5000);
665 l_sql_order_by		varchar2(5000);
666 l_sql_insert_stm 		varchar2(5000);
667 l_sql_stmt			varchar2(5000);
668 v_all_name           	varchar2(80);
669 
670 BEGIN
671 
672 /***************/
673 
674 l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, subject_name, view_by_name)';
675 
676 l_sql_insert_stm := l_sql_insert_stm
677                  || ' SELECT ' ;
678 
679 l_sql_insert_stm := l_sql_insert_stm
680                  || ' count(distinct(a.lead_id)), ';
681 
682 l_sql_insert_stm := l_sql_insert_stm
683                  || ' round(decode(count(distinct(a.lead_id)),0,0,sum(a.initiated_revenue)/count(distinct(a.lead_id))),2), :p_all_value ,';
684 
685 l_sql_where:= l_sql_where||' where a.period_start_date   >= :p_start_date and a.period_end_date <= :p_end_date';
686 /***************/
687 
688 /* l_sql_from := ' from  bim_cmpgn_perf_summ a,bim_dimv_prod_lov d '; */
689 
690 
691 /***************/
692 
693 IF 	p_campaign_status_id IS NULL THEN
694 	l_sql_where:= l_sql_where||' and :p_campaign_status_id IS NULL' ;
695 ELSE
696 	l_sql_where:= l_sql_where|| ' and parent_campaign_status_id = :p_campaign_status_id';
697 END IF;
698 
699 IF 	p_campaign_type_id IS NULL THEN
700 	l_sql_where:= l_sql_where|| ' and :p_campaign_type_id  IS NULL';
701 ELSE
702 	l_sql_where:= l_sql_where|| ' and parent_campaign_type = :p_campaign_type_id';
703 END IF;
704 
705 /***************/
706 
707 IF 	p_media_id IS NULL THEN
708 	l_sql_where:= l_sql_where|| ' and :p_media_id IS NULL ';
709 ELSE
710 	l_sql_where:= l_sql_where|| ' and a.media_id = :p_media_id';
711 END IF;
712 
713 /***************/
714 
715 IF 	p_channel_id IS  NULL THEN
716 	l_sql_where:= l_sql_where|| ' and :p_channel_id IS NULL ';
717 ELSE
718 	l_sql_where:= l_sql_where|| ' and  a.channel_id = :p_channel_id';
719 END IF;
720 
721 /***************/
722 
723 IF 	p_sales_channel_code IS NULL THEN
724 	l_sql_where:= l_sql_where|| ' and :p_sales_channel_code IS NULL ';
725 ELSE
726 	l_sql_where:= l_sql_where|| ' and a.sales_channel_code = :p_sales_channel_code';
727 END IF;
728 
729 /***************/
730 
731 IF 	p_market_segment_id IS NULL
732 THEN
733 	l_sql_where:= l_sql_where|| ' and :p_market_segment_id IS NULL ';
734 ELSE
735 	l_sql_where:= l_sql_where|| ' and a.market_segment_id   = :p_market_segment_id';
736 END IF;
737 
738 /***************/
739 
740 IF 	p_interest_type_id IS NULL THEN
741 	l_sql_where:= l_sql_where|| ' and :p_interest_type_id IS NULL ';
742 ELSE
743 	l_sql_where:= l_sql_where|| ' and a.interest_type_id = :p_interest_type_id ';
744 END IF;
745 
746 IF 	p_primary_interest_code_id IS NULL THEN
747 	l_sql_where:= l_sql_where||' and :p_primary_interest_code_id IS NULL ';
748 ELSE
749 	l_sql_where:= l_sql_where|| ' and a.primary_interest_code_id = :p_primary_interest_code_id ';
750 END IF;
751 
752 IF 	p_secondary_interest_code_id IS NULL THEN
753 	l_sql_where:= l_sql_where||' and :p_secondary_interest_code_id IS NULL ';
754 ELSE
755 	l_sql_where:= l_sql_where||' and a.secondary_interest_code_id = :p_secondary_interest_code_id ';
756 END IF;
757 
758 /***************/
759 
760 IF 	p_geography_code IS NULL THEN
761 	l_sql_where:= l_sql_where|| ' and :p_geography_code IS NULL ';
762 ELSE
763 	l_sql_where:= l_sql_where|| ' and bill_to_geography_code like :p_geography_code';
764 END IF;
765 
766 IF P_VIEW_BY = 'PER' THEN
767     IF p_period_type IS NOT NULL
768     THEN
769      l_sql_where := l_sql_where || ' and d.PERIOD_TYPE like :p_period_type';
770     ELSE
774 /***************/
771      l_sql_where := l_sql_where || ' and :p_period_type IS NULL';
772     end if;
773 END IF;
775 
776 IF 	p_view_by = 'MCH' THEN
777 	l_sql_insert_stm := l_sql_insert_stm || ' c.channel_name ';
778 	l_sql_from	:= l_sql_from|| ' from bim_cmpgn_perf_summ   a ,bim_campaigns_denorm  b ,bim_dimv_media_channels c, dual ' ;
779 	l_sql_where	:=  l_sql_where|| ' and a.campaign_id = b.campaign_id and a.media_id = c.media_id and a.channel_id = c.channel_id and b.level_from_parent = 0 ';
780 	l_sql_group_by 	:= l_sql_group_by || ' GROUP BY  :p_all_value,c.channel_name ';
781     l_sql_order_by := ' Order by 3,2 desc';
782 ELSIF 	p_view_by = 'ACT' THEN
783 	l_sql_insert_stm := l_sql_insert_stm || ' d.media_name ';
784 	l_sql_from	:= l_sql_from|| ' from bim_cmpgn_perf_summ   a, bim_campaigns_denorm  b , bim_dimv_media d, dual  ' ;
785 	l_sql_where:=  l_sql_where|| ' and a.campaign_id = b.campaign_id and a.media_id = d.media_id and b.level_from_parent = 0 ';
786 	l_sql_group_by := l_sql_group_by || ' GROUP BY  :p_all_value , d.media_name ';
787     l_sql_order_by := ' Order by 3,2 desc';
788 ELSIF 	p_view_by = 'MSG' THEN
789 	l_sql_insert_stm := l_sql_insert_stm || ' d.market_segment_name ';
790 	l_sql_from:= l_sql_from|| ' from 	bim_cmpgn_perf_summ   a ,bim_campaigns_denorm  b, bim_dimv_market_sgmts d, dual ' ;
791 	l_sql_where:=  l_sql_where|| ' and a.campaign_id = b.campaign_id and a.market_segment_id = d.market_segment_id and b.level_from_parent = 0 ';
792 	l_sql_group_by := l_sql_group_by || ' GROUP BY  :p_all_value , d.market_segment_name ';
793     l_sql_order_by := ' Order by 3,2 desc';
794 ELSIF 	p_view_by = 'SCH' THEN
795 	l_sql_insert_stm := l_sql_insert_stm || ' d.sales_channel_name ';
796 	l_sql_from:= l_sql_from|| ' from 	bim_cmpgn_perf_summ  a ,bim_campaigns_denorm  b, bim_dimv_sales_channels d, dual ' ;
797 	l_sql_where:=  l_sql_where||' and a.campaign_id = b.campaign_id and a.sales_channel_code = d.sales_channel_code and b.level_from_parent = 0 ';
798 	l_sql_group_by := l_sql_group_by || ' GROUP BY  :p_all_value , d.sales_channel_name ';
799     l_sql_order_by := ' Order by 3,2 desc';
800 ELSIF p_view_by = 'PER' THEN
801 	l_sql_insert_stm := l_sql_insert_stm || '  d.period_name ';
802 	l_sql_from:= l_sql_from|| ' from bim_cmpgn_perf_summ  a, bim_campaigns_denorm b, bim_dimv_periods d, dual ' ;
803 	l_sql_where:=  l_sql_where|| ' and a.campaign_id = b.campaign_id and a.period_start_date >= d.start_date and  a.period_end_date <= d.end_date and d.period_set_name = jtf_bis_util.ProfileValue(''CRMBIS:PERIOD_SET_NAME'') ';
804     l_sql_group_by := l_sql_group_by || ' GROUP BY :p_all_value , d.period_name, d.start_date ';
805     l_sql_order_by := ' Order by d.start_date';
806 END IF;
807 
808 /***************/
809 
810 l_sql_stmt := l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by;
811 
812 /**************/
813 
814 /*
815 declare
816 fp  		utl_file.file_type;
817 location 	varchar2(100) := '/sqlcom/log/dom1151';
818 file_name	varchar2(50) := 'bpp.sql';
819 begin
820 		-- dbms_output.put_line('ENTERED THE SQL BLOCK ');
821 		fp := 	utl_file.fopen(location,file_name,'w');
822 		utl_file.put_line(fp,l_sql_insert_stm);
823 		utl_file.put_line(fp,l_sql_from);
824 		utl_file.put_line(fp,l_sql_where);
825 		utl_file.put_line(fp,l_sql_group_by);
826 		utl_file.fclose(fp);
827 exception
828 when 	UTL_FILE.INVALID_MODE then
829 	 	-- dbms_output.put_line('INVALID MODE EXCEPTION');
830 when 	UTL_FILE.INVALID_PATH then
831 		-- dbms_output.put_line('INVALID PATH EXCEPTION');
832 end;
833 */
834 
835 /******************/
836 delete bim_camp_acqu_summ_temp;
837 
838 -- dbms_output.put_line('COMPLETED THE SQL STATEMENT ');
839 
840 IF (p_view_by = 'PER') THEN
841 EXECUTE IMMEDIATE l_sql_stmt
842     USING
843      p_all_value,
844      p_start_date,
845      p_end_date,
846      p_campaign_status_id,
847      p_campaign_type_id,
848      p_media_id,
849      p_channel_id,
850      p_sales_channel_code,
851      p_market_segment_id,
852      p_interest_type_id,
853      p_primary_interest_code_id,
854      p_secondary_interest_code_id,
855      p_geography_code,
856      p_period_type,
857      p_all_value;
858 ELSE
859 EXECUTE IMMEDIATE l_sql_stmt
860     USING
861      p_all_value,
862      p_start_date,
863      p_end_date,
864      p_campaign_status_id,
865      p_campaign_type_id,
866      p_media_id,
867      p_channel_id,
868      p_sales_channel_code,
869      p_market_segment_id,
870      p_interest_type_id,
871      p_primary_interest_code_id,
872      p_secondary_interest_code_id,
873      p_geography_code,
874      p_all_value;
875 END IF;
876 
877    v_num_rows_inserted := SQL%ROWCOUNT;
878 --   -- dbms_output.put_line ( 'Number of rows inserted in temp table IS : ' || v_num_rows_inserted );
879 
880 END  BIM_CMP_PERF_SUM_POPULATE ;
881 
882 
883 PROCEDURE BIM_CMP_RESP_POPULATE
884 
885          (
886             p_start_date                  in date      default null,
887             p_end_date                    in date      default null,
888             p_drill_down                  in varchar2  default null,
889             p_media_id                    in number    default null,
890             p_channel_id                  in varchar2  default null,
891             p_market_segment_id           in number    default null,
895             p_campaign_type_id            in varchar2  default null,
892             p_geography_code              in varchar2  default null,
893             p_campaign_id                 in number    default null,
894             p_campaign_status_id          in number    default null,
896             p_period_type                 in varchar2  default null,
897             p_view_by                     in varchar2  default null
898          ) IS
899 
900 
901 
902 l_sql_insert_stm            varchar2(10000);
903 l_sql_from                  varchar2(10000);
904 l_sql_where                 varchar2(30000);
905 l_sql_group_by              varchar2(2000);
906 l_sql_order_by              varchar2(2000);
907 l_jtf_bis_util              varchar2(2000);
908 
909 
910 
911 
912 begin
913 
914 l_jtf_bis_util := 'jtf_bis_util.profileValue(''CRMBIS:PERIOD_SET_NAME'')';
915 
916 l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, subject_name, view_by_name)';
917 
918 l_sql_insert_stm := l_sql_insert_stm
919                  || ' SELECT ' ;
920 l_sql_insert_stm := l_sql_insert_stm || ' Sum(nvl(BSCR.num_responded,0)),';
921 l_sql_insert_stm := l_sql_insert_stm || ' decode(sum(BSCR.NUM_TARGETED),0,0,( SUM(BSCR.NUM_RESPONDED) / SUM(BSCR.NUM_TARGETED) ) * 100), ';
922 
923 l_sql_from := ' FROM BIM_CMPGN_RESP_SUMM BSCR,  bim_campaigns_denorm denorm, ';
924 
925 l_sql_where := ' WHERE BSCR.PERIOD_start_date >= :p_start_date And BSCR.PERIOD_end_date <= :p_end_date';
926 l_sql_where := l_sql_where || ' AND BSCR.campaign_id = denorm.campaign_id and denorm.parent_campaign_id = BDC.campaign_id ';
927 
928 
929 IF p_campaign_id IS NULL then
930     l_sql_where := l_sql_where || ' and :p_campaign_id is null ';
931 ELSIF p_drill_down = 'Y' then
932     l_sql_where := l_sql_where || ' and NVL(DENORM.parent_campaign_id, -999) = :p_campaign_id';
933 ELSE l_sql_where := l_sql_where || ' and DENORM.campaign_id = :p_campaign_id ';
934 END IF;
935 
936 
937 IF p_campaign_status_id IS NOT NULL
938 THEN
939     l_sql_where := l_sql_where || ' And denorm.parent_campaign_status_id = :p_campaign_status_id';
940 ELSE
941     l_sql_where := l_sql_where || ' and :p_campaign_status_id IS NULL';
942 END IF;
943 
944 IF p_campaign_type_id IS NOT NULL
945 THEN
946     l_sql_where := l_sql_where || ' And  denorm.Parent_Campaign_type = :p_campaign_type_id)';
947 ELSE
948     l_sql_where := l_sql_where || ' and :p_campaign_type_id IS NULL ';
949 END IF;
950 
951 IF P_media_id IS NOT NULL
952 THEN
953     l_sql_where := l_sql_where || ' and BSCR.Media_id = :p_media_id';
954 ELSE
955     l_sql_where := l_sql_where || ' and :p_media_id IS NULL';
956 END IF;
957 
958 IF p_channel_id IS NOT NULL
959 THEN
960     l_sql_where := l_sql_where || ' and BSCR.channel_id = :p_channel_id';
961 ELSE
962     l_sql_where := l_sql_where || ' and :p_channel_id IS NULL';
963 END IF;
964 
965 IF p_market_segment_id IS NOT NULL
966 THEN
967     l_sql_where := l_sql_where || ' and BSCR.market_segment_id = :p_market_segment_id';
968 ELSE
969     l_sql_where := l_sql_where || ' and :p_market_segment_id IS NULL';
970 END IF;
971 
972 IF p_geography_code IS NOT NULL
973 THEN
974     l_sql_where := l_sql_where || ' AND BSCR.geography_code like :p_geography_code';
975 ELSE
976     l_sql_where := l_sql_where || ' and :p_geography_code IS NULL';
977 END IF;
978 
979 
980 
981 IF p_view_by = 'MSG'
982 THEN
983    l_sql_insert_stm := l_sql_insert_stm || ' BDC.campaign_name,BDMS.market_segment_name  ';
984    l_sql_from := l_sql_from || '  bim_dimv_campaigns BDC, BIM_DIMV_MARKET_SGMTS BDMS, dual ';
985    l_sql_where := l_sql_where || ' and BSCR.MARKET_SEGMENT_ID = BDMS.MARKET_SEGMENT_ID ';
986    l_sql_group_by := ' GROUP BY BDC.campaign_name, BDMS.market_segment_name ';
987    l_sql_order_by := ' Order by 3,2 desc';
988 ELSIF
989    p_view_by = 'PER'
990 THEN
991    l_sql_insert_stm := l_sql_insert_stm || ' BDC.campaign_name,bdp.period_name ';
992    l_sql_from := l_sql_from || ' bim_dimv_periods bdp, bim_dimv_campaigns BDC,dual ';
993    l_sql_where := l_sql_where || ' and BSCR.period_start_date >= BDP.start_date and  BSCR.period_end_date <= BDP.end_date and BDP.period_set_name = jtf_bis_util.ProfileValue(''CRMBIS:PERIOD_SET_NAME'')';
994 
995    IF p_period_type IS NOT NULL
996    THEN
997       l_sql_where := l_sql_where || ' AND bdp.PERIOD_TYPE like :p_period_type';
998    ELSE
999       l_sql_where := l_sql_where || ' and :p_period_type IS NULL';
1000    END IF;
1001 
1002    l_sql_group_by := ' GROUP BY BDC.campaign_name, bdp.period_name, bdp.start_date ';
1003    l_sql_order_by := ' order by bdc.campaign_name, bdp.start_date';
1004 
1005 ELSIF
1006    p_view_by = 'ACT'
1007 THEN
1008    l_sql_insert_stm := l_sql_insert_stm || ' BDC.campaign_name,BDMC.media_name ';
1009    l_sql_from := l_sql_from || ' bim_dimv_campaigns BDC, bim_dimv_media_channels bdmc, dual ';
1010    l_sql_where := l_sql_where || ' and BSCR.media_id = BDMC.media_id and BSCR.channel_id = BDMC.channel_id ';
1011    l_sql_group_by := ' group by BDC.campaign_name, BDMC.media_name ';
1012    l_sql_order_by := ' order by 3,2 desc ';
1013 ELSIF
1014    p_view_by = 'MCH'
1015 THEN
1016    l_sql_insert_stm := l_sql_insert_stm || ' BDC.Campaign_name,BMC.Channel_name ';
1017    l_sql_from := l_sql_from || ' bim_dimv_campaigns BDC, bim_dimv_media_channels BMC, dual ';
1018    l_sql_where := l_sql_where || ' and BSCR.media_id = BMC.media_id and BSCR.channel_id = BMC.channel_id ';
1019    l_sql_group_by := ' group by BDC.campaign_name,BMC.channel_name ';
1020    l_sql_order_by := ' order by 3,2 desc ';
1021 END IF;
1022 
1023 
1024 
1025 
1026 
1027 
1028 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 1, 150));
1029 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 151, 150));
1030 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 300, 150));
1031 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_from, 1, 150));
1032 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_from, 151, 150));
1033 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 1, 150));
1034 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 151, 150));
1035 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 300, 150));
1036 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 451, 150));
1037 -- dbms_OUTPUT.PUT_LINE(l_sql_group_by);
1038 -- dbms_OUTPUT.PUT_LINE(l_sql_order_by);
1039 
1040 
1041 /*
1042 declare
1043 fp  		utl_file.file_type;
1044 location 	varchar2(100) := '/sqlcom/log/dom1151';
1045 file_name	varchar2(50) := 'bpp.sql';
1046 begin
1047 		-- dbms_output.put_line('ENTERED THE SQL BLOCK ');
1048 		fp := 	utl_file.fopen(location,file_name,'w');
1049 		utl_file.put_line(fp,l_sql_insert_stm);
1050 		utl_file.put_line(fp,l_sql_from);
1051 		utl_file.put_line(fp,l_sql_where);
1052 		utl_file.put_line(fp,l_sql_group_by);
1053 		utl_file.fclose(fp);
1054 exception
1055 when 	UTL_FILE.INVALID_MODE then
1056 	 	-- dbms_output.put_line('INVALID MODE EXCEPTION');
1057 when 	UTL_FILE.INVALID_PATH then
1058 		-- dbms_output.put_line('INVALID PATH EXCEPTION');
1059 end;
1060 
1061 */
1062 
1063 
1064 if p_view_by = 'PER' then
1065 EXECUTE IMMEDIATE l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by
1066 Using
1067 p_start_date,
1068 p_end_date,
1069 p_campaign_id,
1070 p_campaign_status_id,
1071 p_campaign_type_id,
1072 p_media_id,
1073 p_channel_id,
1074 p_market_segment_id,
1075 p_geography_code,
1076 p_period_type;
1077 
1078 
1082 p_start_date,
1079 else
1080 EXECUTE IMMEDIATE l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by || l_sql_order_by
1081 Using
1083 p_end_date,
1084 p_campaign_id,
1085 p_campaign_status_id,
1086 p_campaign_type_id,
1087 p_media_id,
1088 p_channel_id,
1089 p_market_segment_id,
1090 p_geography_code;
1091 
1092 end if;
1093 
1094 
1095 end BIM_CMP_RESP_POPULATE;
1096 
1097 
1098  PROCEDURE BIM_CMP_REV_POPULATE
1099          (
1100             p_campaign_id           in number     DEFAULT NULL,
1104 l_sql_insert_stm            varchar2(10000);
1101             p_drill_down			in  varchar2  DEFAULT 'Y'
1102          )  IS
1103 
1105 l_sql_from                  varchar2(10000);
1106 l_sql_where                 varchar2(30000);
1107 l_sql_group_by              varchar2(2000);
1108 l_sql_order_by              varchar2(2000);
1109 l_sql_outer_where           varchar2(2000);
1110 l_view_by                   varchar2(2000);
1111 l_subject_name              varchar2(2000);
1112 
1113 begin
1114 l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, measure3, subject_name )';
1115 
1116 l_sql_insert_stm := l_sql_insert_stm
1117                  || ' SELECT ' ;
1118 
1119 l_sql_insert_stm := l_sql_insert_stm
1120                  || '  sum(ccost.forecasted_revenue) , ';
1121 
1125 
1122 l_sql_insert_stm := l_sql_insert_stm
1123                  || ' sum(ccost.initiated_revenue) ,';
1124 
1126 l_sql_insert_stm := l_sql_insert_stm
1127                  || ' decode(sum(ccost.initiated_revenue),0,0,((sum(ccost.initiated_revenue)-sum(ccost.forecasted_revenue))/sum(ccost.initiated_revenue))*100) ,';
1128 
1129 l_sql_from := ' FROM BIM_CMPGN_REVCOST_SUMM CCOST, bim_campaigns_denorm denorm  , bim_dimv_campaigns dimv , dual';
1130 
1131 --Build the where clause
1132 
1133 l_sql_where := ' WHERE ccost.campaign_id = denorm.campaign_id and denorm.campaign_id = dimv.campaign_id ';
1134 
1135 
1136 
1137 IF p_campaign_id IS NULL THEN
1138 	l_sql_where:= l_sql_where|| ' and denorm .parent_campaign_id in (select campaign_id from bim_dimv_campaigns where parent_campaign_id is null  and :p_campaign_id is null ) ';
1139 ELSE
1143 
1140 	l_sql_where:= l_sql_where|| ' and denorm.parent_campaign_id in (select campaign_id from bim_dimv_campaigns where parent_campaign_id = :p_campaign_id ) ';
1141 END IF;
1142 
1144 l_sql_insert_stm := l_sql_insert_stm || ' dimv.campaign_name ';
1145 l_sql_group_by := l_sql_group_by || ' GROUP BY dimv.campaign_name ';
1146 
1147 
1148 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 1, 150));
1149 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 151, 150));
1150 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 300, 150));
1151 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 450, 150));
1152 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_from, 1, 150));
1153 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_from, 151, 150));
1154 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 1, 150));
1155 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 151, 150));
1156 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 300, 150));
1157 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 451, 150));
1158 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 600, 150));
1159 -- dbms_OUTPUT.PUT_LINE(l_sql_group_by);
1163 Using  p_campaign_id ;
1160 -- dbms_OUTPUT.PUT_LINE(l_sql_order_by);
1161 
1162 EXECUTE IMMEDIATE l_sql_insert_stm ||l_sql_from || l_sql_where || l_sql_group_by
1164 
1165 end  BIM_CMP_REV_POPULATE  ;
1166 
1167 
1168 PROCEDURE BIM_CMP_REV_SUM_POPULATE
1169          (
1170             p_all_value                   in varchar    DEFAULT 'ALL',
1171             p_campaign_type_id            in varchar2   DEFAULT NULL,
1172             p_campaign_status_id          in number     DEFAULT NULL
1173          )  IS
1174 
1175 l_sql_insert_stm            varchar2(10000);
1176 l_sql_from                  varchar2(10000);
1177 l_sql_where                 varchar2(30000);
1178 l_sql_group_by              varchar2(2000);
1182 l_subject_name              varchar2(2000);
1179 l_sql_order_by              varchar2(2000);
1180 l_sql_outer_where           varchar2(2000);
1181 l_view_by                   varchar2(2000);
1183 
1184 begin
1185 l_sql_insert_stm := 'INSERT INTO bim_camp_acqu_summ_temp (measure1, measure2, measure3, subject_name )';
1186 
1187 l_sql_insert_stm := l_sql_insert_stm
1188                  || ' SELECT ' ;
1189 
1190 l_sql_insert_stm := l_sql_insert_stm
1191                  || '  sum(ccost.forecasted_revenue) , ';
1192 
1193 l_sql_insert_stm := l_sql_insert_stm
1194                  || ' sum(ccost.initiated_revenue) ,';
1195 
1196 
1197 l_sql_insert_stm := l_sql_insert_stm
1198                  || ' decode(sum(ccost.initiated_revenue),0,0,((sum(ccost.initiated_revenue)-sum(ccost.forecasted_revenue))/sum(ccost.initiated_revenue))*100), :p_all_value';
1199 
1203 
1200 l_sql_from := ' FROM BIM_CMPGN_REVCOST_SUMM CCOST, bim_campaigns_denorm denorm , dual ';
1201 
1202 --Build the where clause
1204 l_sql_where := ' WHERE ccost.campaign_id = denorm.campaign_id AND  denorm.level_from_parent = 0 ';
1205 
1206 IF p_campaign_type_id IS NOT NULL
1207 THEN
1208     l_sql_where := l_sql_where || ' And denorm.parent_campaign_type = :p_campaign_type_id ';
1209 ELSE
1210     l_sql_where := l_sql_where || ' and :p_campaign_type_id IS NULL ';
1211 END IF;
1212 
1213 
1214 IF p_campaign_status_id IS NOT NULL
1215 THEN
1216     l_sql_where := l_sql_where || ' and denorm.parent_campaign_status_id = :p_campaign_status_id ';
1217 ELSE
1221 l_sql_group_by := l_sql_group_by || ' GROUP BY :p_all_value ';
1218     l_sql_where := l_sql_where || ' and :p_campaign_status_id IS NULL ';
1219 END IF;
1220 
1222 l_sql_order_by := l_sql_order_by || ' ORDER BY 3 DESC ' ;
1223 
1224 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 1, 150));
1225 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 151, 150));
1226 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 300, 150));
1227 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_insert_stm, 450, 150));
1228 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_from, 1, 150));
1229 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_from, 151, 150));
1230 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 1, 150));
1231 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 151, 150));
1232 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 300, 150));
1233 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 451, 150));
1234 -- dbms_OUTPUT.PUT_LINE(substr(l_sql_where, 600, 150));
1235 -- dbms_OUTPUT.PUT_LINE(l_sql_group_by);
1236 -- dbms_OUTPUT.PUT_LINE(l_sql_order_by);
1237 
1241 ,p_campaign_type_id
1238 EXECUTE IMMEDIATE l_sql_insert_stm ||l_sql_from || l_sql_where ||l_sql_group_by|| l_sql_order_by
1239 Using
1240 p_all_value
1242 ,p_campaign_status_id
1243 ,p_all_value;
1244 
1248 END BIM_CMP_PKG;
1245 end  BIM_CMP_REV_SUM_POPULATE  ;
1246 
1247