[Home] [Help]
PACKAGE BODY: APPS.BIM_SLCHN_CMPR_TEMP_PVT
Source
1 PACKAGE BODY BIM_SLCHN_CMPR_TEMP_PVT AS
2 /* $Header: bimvsccb.pls 115.4 2000/08/15 13:01:31 pkm ship $ */
3
4 PROCEDURE populate_temp
5 (
6 p_campaign_id number DEFAULT NULL,
7 p_campaign_status_id number DEFAULT NULL,
8 p_campaign_type varchar2 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_type varchar2 DEFAULT NULL,
13 p_media_id number DEFAULT NULL,
14 p_channel_id varchar2 DEFAULT NULL,
15 p_market_segment_id number DEFAULT NULL,
16 p_sales_channel_code varchar2 DEFAULT NULL,
17 p_interest_type_id number DEFAULT NULL,
18 p_primary_interest_code_id number DEFAULT NULL,
19 p_secondary_interest_code_id number DEFAULT NULL,
20 p_geography_code varchar2 DEFAULT NULL,
21 p_view_by varchar2 DEFAULT NULL
22 ) IS
23
24 v_num_rows_inserted integer;
25 v_num_rows_updated integer;
26 v_view_by varchar2(50);
27 from_clause varchar2(100);
28 where_clause varchar2(250);
29
30
31 BEGIN
32
33 IF p_campaign_id is not null
34 THEN
35 from_clause := from_clause || ' ,bim_campaigns_denorm dnm' ;
36 where_clause := where_clause || ' and cperf.campaign_id = dnm.campaign_id ' ||
37 ' and dnm.parent_campaign_id = cmp.campaign_id ' ||
38 ' and cmp.campaign_id = :p_campaign_id ' ;
39 ELSE
40 where_clause := where_clause || ' and cperf.campaign_id = cmp.campaign_id ' ||
41 ' and cmp.campaign_id = nvl(:p_campaign_id, cmp.campaign_id ) ';
42 END IF;
43
44 IF p_view_by = 'CMP'
45 THEN
46 v_view_by := 'cmp.campaign_name' ;
47 ELSIF p_view_by = 'MED'
48 THEN
49 v_view_by := 'med.media_name' ;
50 ELSIF p_view_by = 'MCH'
51 THEN
52 from_clause := from_clause || ',bim_dimv_channels chn' ;
53 v_view_by := 'chn.channel_name' ;
54 where_clause := where_clause || ' and cperf.channel_id = chn.channel_id';
55 ELSIF p_view_by = 'MKT'
56 THEN
57 v_view_by := 'mkt.market_segment_name' ;
58 from_clause := from_clause || ',bim_dimv_market_sgmts mkt' ;
59 where_clause := where_clause || ' and cperf.market_segment_id = mkt.market_segment_id';
60 END IF;
61
62 EXECUTE IMMEDIATE ' INSERT INTO bim_camp_acqu_summ_temp
63 ( subject_name,
64 view_by_name,
65 rank_by,
66 measure1,
67 measure3,
68 measure4 )
69 select sch.sales_channel_name,' ||
70 v_view_by ||
71 ', count( distinct cperf.lead_id ),
72 count( distinct cperf.lead_id ),
73 nvl(sum(cperf.initiated_revenue), 0 ),
74 count( distinct cperf.cust_account_id )
75 from bim_cmpgn_perf_summ cperf,
76 bim_dimv_campaigns cmp,
77 bim_dimv_media med,
78 bim_dimv_sales_channels sch ' || from_clause ||
79 ' where cperf.sales_channel_code = sch.sales_channel_code
80 and cperf.media_id = med.media_id' || where_clause ||
81 ' and cperf.period_start_date >= :p_start_date
82 and cperf.period_end_date <= :p_end_date
83 and cmp.user_status_id = nvl(:p_campaign_status_id, cmp.user_status_id )
84 and cmp.campaign_type = nvl(:p_campaign_type, cmp.campaign_type)
85 and med.media_type_code = nvl(:p_media_type, med.media_type_code )
86 and cperf.media_id = nvl(:p_media_id, cperf.media_id )
87 and cperf.channel_id = nvl(:p_channel_id, cperf.channel_id)
88 and cperf.sales_channel_code = nvl(:p_sales_channel_code, cperf.sales_channel_code)
89 and cperf.market_segment_id = nvl(:p_market_segment_id, cperf.market_segment_id)
90 and cperf.interest_type_id = nvl(:p_interest_type_id, cperf.interest_type_id )
91 and cperf.primary_interest_code_id = nvl(:p_primary_interest_code_id, cperf.primary_interest_code_id)
92 and cperf.secondary_interest_code_id = nvl(:p_secondary_interest_code_id, secondary_interest_code_id)
93 and cperf.bill_to_geography_code = nvl(:p_geography_code, cperf.bill_to_geography_code )
94 group by sch.sales_channel_name, ' || v_view_by
95 USING
96 p_campaign_id,
97 p_start_date,
98 p_end_date,
99 p_campaign_status_id,
100 p_campaign_type,
101 p_media_type,
102 p_media_id,
103 p_channel_id,
104 p_sales_channel_code,
105 p_market_segment_id,
106 p_interest_type_id,
107 p_primary_interest_code_id,
108 p_secondary_interest_code_id,
109 p_geography_code;
110
111 v_num_rows_inserted := SQL%ROWCOUNT;
112 -- dbms_output.put_line ( 'Number of rows inserted in temp table is : ' || v_num_rows_inserted );
113
114
115 EXECUTE IMMEDIATE 'UPDATE bim_camp_acqu_summ_temp tmp
116 set tmp.measure2 =
117 ( select nvl(sum(cperf.initiated_revenue), 0)
118 from bim_customer_rev_summ cperf,
119 bim_dimv_campaigns cmp,
120 bim_dimv_media med,
121 bim_dimv_sales_channels sch ' || from_clause ||
122 ' where cperf.sales_channel_code = sch.sales_channel_code
123 and cperf.media_id = med.media_id' || where_clause ||
124 ' and sch.sales_channel_name = tmp.subject_name
125 and cperf.period_start_date >= :p_start_date
126 and cperf.period_end_date <= :p_end_date
127 and cperf.first_order_date >= :p_start_date
128 and cperf.first_order_date <= :p_end_date
129 and cmp.user_status_id = nvl(:p_campaign_status_id, cmp.user_status_id )
130 and cmp.campaign_type = nvl(:p_campaign_type, cmp.campaign_type)
131 and med.media_type_code = nvl(:p_media_type, med.media_type_code )
132 and cperf.media_id = nvl(:p_media_id, cperf.media_id )
133 and cperf.channel_id = nvl(:p_channel_id, cperf.channel_id)
134 and cperf.sales_channel_code = nvl(:p_sales_channel_code, cperf.sales_channel_code)
135 and cperf.market_segment_id = nvl(:p_market_segment_id, cperf.market_segment_id)
136 and cperf.interest_type_id = nvl(:p_interest_type_id, cperf.interest_type_id )
137 and cperf.primary_interest_code_id = nvl(:p_primary_interest_code_id, cperf.primary_interest_code_id)
138 and cperf.secondary_interest_code_id = nvl(:p_secondary_interest_code_id, secondary_interest_code_id)
139 and cperf.bill_to_geography_code = nvl(:p_geography_code, cperf.bill_to_geography_code )
140 and tmp.view_by_name = ' || v_view_by || ' ) '
141 USING
142 p_campaign_id,
143 p_start_date,
144 p_end_date,
145 p_start_date,
146 p_end_date,
147 p_campaign_status_id,
148 p_campaign_type,
149 p_media_type,
150 p_media_id,
151 p_channel_id,
152 p_sales_channel_code,
153 p_market_segment_id,
154 p_interest_type_id,
155 p_primary_interest_code_id,
156 p_secondary_interest_code_id,
157 p_geography_code;
158
159 -- changed on 8/14 to fix bug: 1361701
160
161 UPDATE bim_camp_acqu_summ_temp SET measure2 = measure2*100/measure3;
162
163 UPDATE bim_camp_acqu_summ_temp SET measure3 = measure3/measure1;
164
165
166 v_num_rows_updated := SQL%ROWCOUNT;
167 -- dbms_output.put_line ( 'Number of rows updated in temp table is : ' || v_num_rows_updated );
168
169
170 END POPULATE_TEMP;
171
172
173 PROCEDURE populate_temp_by_period
174 (
175 p_campaign_id number DEFAULT NULL,
176 p_campaign_status_id number DEFAULT NULL,
177 p_campaign_type varchar2 DEFAULT NULL,
178 p_period_type varchar2 DEFAULT NULL,
179 p_start_date date DEFAULT NULL,
180 p_end_date date DEFAULT NULL,
181 p_media_type varchar2 DEFAULT NULL,
182 p_media_id number DEFAULT NULL,
183 p_channel_id varchar2 DEFAULT NULL,
184 p_market_segment_id number DEFAULT NULL,
185 p_sales_channel_code varchar2 DEFAULT NULL,
186 p_interest_type_id number DEFAULT NULL,
187 p_primary_interest_code_id number DEFAULT NULL,
188 p_secondary_interest_code_id number DEFAULT NULL,
189 p_geography_code varchar2 DEFAULT NULL
190 ) IS
191
192 v_num_rows_inserted integer;
193 v_num_rows_updated integer;
194 from_clause varchar2(100);
195 where_clause varchar2(250);
196
197 BEGIN
198
199 IF p_campaign_id is not null
200 THEN
201 from_clause := from_clause || ',bim_campaigns_denorm dnm' ;
202 where_clause := where_clause || ' and cperf.campaign_id = dnm.campaign_id ' ||
203 ' and dnm.parent_campaign_id = cmp.campaign_id ' ||
204 ' and cmp.campaign_id = :p_campaign_id ' ;
205 ELSE
206 where_clause := where_clause || ' and cperf.campaign_id = cmp.campaign_id ' ||
207 ' and cmp.campaign_id = nvl(:p_campaign_id, cmp.campaign_id ) ';
208 END IF;
209
210 EXECUTE IMMEDIATE ' INSERT INTO bim_camp_acqu_summ_temp
211 ( subject_name,
212 view_by_name,
213 rank_by,
214 measure1,
215 measure2,
216 measure3 )
217 select sch.sales_channel_name,
218 per.period_name,
219 to_number( to_char(per.start_date, ''J'')),
220 count( distinct cperf.lead_id ),
221 nvl(sum(cperf.initiated_revenue), 0 ),
222 count( distinct cperf.cust_account_id )
223 from bim_cmpgn_perf_summ cperf,
224 bim_dimv_campaigns cmp,
225 bim_dimv_media med,
226 bim_dimv_sales_channels sch,
227 bim_dimv_periods per ' || from_clause ||
228 ' where cperf.sales_channel_code = sch.sales_channel_code
229 and cperf.media_id = med.media_id' || where_clause ||
230 ' and cperf.period_start_date >= per.start_date
231 and cperf.period_end_date <= per.end_date
232 and per.period_type = :p_period_type
233 and per.period_set_name = jtf_bis_util.profileValue(''CRMBIS:PERIOD_SET_NAME'')
234 and per.start_date >= :p_start_date
235 and per.end_date <= :p_end_date
236 and cmp.user_status_id = nvl(:p_campaign_status_id, cmp.user_status_id )
237 and cmp.campaign_type = nvl(:p_campaign_type, cmp.campaign_type)
238 and med.media_type_code = nvl(:p_media_type, med.media_type_code )
239 and cperf.media_id = nvl(:p_media_id, cperf.media_id )
240 and cperf.channel_id = nvl(:p_channel_id, cperf.channel_id)
241 and cperf.sales_channel_code = nvl(:p_sales_channel_code, cperf.sales_channel_code)
242 and cperf.market_segment_id = nvl(:p_market_segment_id, cperf.market_segment_id)
243 and cperf.interest_type_id = nvl(:p_interest_type_id, cperf.interest_type_id )
244 and cperf.primary_interest_code_id = nvl(:p_primary_interest_code_id, cperf.primary_interest_code_id)
245 and cperf.secondary_interest_code_id = nvl(:p_secondary_interest_code_id, secondary_interest_code_id)
246 and cperf.bill_to_geography_code = nvl(:p_geography_code, cperf.bill_to_geography_code )
247 group by sch.sales_channel_name, per.period_name, per.start_date'
248 USING
249 p_campaign_id,
250 p_period_type,
251 p_start_date,
252 p_end_date,
253 p_campaign_status_id,
254 p_campaign_type,
255 p_media_type,
256 p_media_id,
257 p_channel_id,
258 p_sales_channel_code,
259 p_market_segment_id,
260 p_interest_type_id,
261 p_primary_interest_code_id,
262 p_secondary_interest_code_id,
263 p_geography_code;
264
265 v_num_rows_inserted := SQL%ROWCOUNT;
266 -- dbms_output.put_line ( 'Number of rows inserted in temp table is : ' || v_num_rows_inserted );
267
268
269 EXECUTE IMMEDIATE 'UPDATE bim_camp_acqu_summ_temp tmp
270 set tmp.measure4 =
271 ( select nvl(sum(cperf.initiated_revenue), 0)
272 from bim_customer_rev_summ cperf,
273 bim_dimv_campaigns cmp,
274 bim_dimv_media med,
275 bim_dimv_sales_channels sch,
276 bim_dimv_periods per ' || from_clause ||
277 ' where cperf.sales_channel_code = sch.sales_channel_code
278 and cperf.media_id = med.media_id' || where_clause ||
279 ' and sch.sales_channel_name = tmp.subject_name
280 and cperf.period_start_date >= per.start_date
281 and cperf.period_end_date <= per.end_date
282 and cperf.first_order_date >= per.start_date
283 and cperf.first_order_date <= per.end_date
284 and per.period_type = :p_period_type
285 and per.period_set_name = jtf_bis_util.profileValue(''CRMBIS:PERIOD_SET_NAME'')
286 and per.start_date >= :p_start_date
287 and per.end_date <= :p_end_date
288 and cmp.user_status_id = nvl(:p_campaign_status_id, cmp.user_status_id )
289 and cmp.campaign_type = nvl(:p_campaign_type, cmp.campaign_type)
290 and med.media_type_code = nvl(:p_media_type, med.media_type_code )
291 and cperf.media_id = nvl(:p_media_id, cperf.media_id )
292 and cperf.channel_id = nvl(:p_channel_id, cperf.channel_id)
293 and cperf.sales_channel_code = nvl(:p_sales_channel_code, cperf.sales_channel_code)
294 and cperf.market_segment_id = nvl(:p_market_segment_id, cperf.market_segment_id)
295 and cperf.interest_type_id = nvl(:p_interest_type_id, cperf.interest_type_id )
296 and cperf.primary_interest_code_id = nvl(:p_primary_interest_code_id, cperf.primary_interest_code_id)
297 and cperf.secondary_interest_code_id = nvl(:p_secondary_interest_code_id, secondary_interest_code_id)
298 and cperf.bill_to_geography_code = nvl(:p_geography_code, cperf.bill_to_geography_code )
299 and tmp.view_by_name = per.period_name )'
300 USING
301 p_campaign_id,
302 p_period_type,
303 p_start_date,
304 p_end_date,
305 p_campaign_status_id,
306 p_campaign_type,
307 p_media_type,
308 p_media_id,
309 p_channel_id,
310 p_sales_channel_code,
311 p_market_segment_id,
312 p_interest_type_id,
313 p_primary_interest_code_id,
314 p_secondary_interest_code_id,
315 p_geography_code;
316
317
318 v_num_rows_updated := SQL%ROWCOUNT;
319 -- dbms_output.put_line ( 'Number of rows updated in temp table is : ' || v_num_rows_updated );
320
321
322 END POPULATE_TEMP_BY_PERIOD;
323
324
325 PROCEDURE populate_temp_table
326 (
327 p_campaign_id number DEFAULT NULL,
328 p_campaign_status_id number DEFAULT NULL,
329 p_campaign_type varchar2 DEFAULT NULL,
330 p_period_type varchar2 DEFAULT NULL,
331 p_start_date date DEFAULT NULL,
332 p_end_date date DEFAULT NULL,
333 p_media_type varchar2 DEFAULT NULL,
334 p_media_id number DEFAULT NULL,
335 p_channel_id varchar2 DEFAULT NULL,
336 p_market_segment_id number DEFAULT NULL,
337 p_sales_channel_code varchar2 DEFAULT NULL,
338 p_interest_type_id number DEFAULT NULL,
339 p_primary_interest_code_id number DEFAULT NULL,
340 p_secondary_interest_code_id number DEFAULT NULL,
341 p_geography_code varchar2 DEFAULT NULL,
342 p_view_by varchar2 DEFAULT NULL
343 ) IS
344
345 BEGIN
346
347
348 IF p_view_by = 'PER'
349 THEN
350 populate_temp_by_period (
351 p_campaign_id,
352 p_campaign_status_id,
353 p_campaign_type,
354 p_period_type,
355 p_start_date,
356 p_end_date,
357 p_media_type,
358 p_media_id,
359 p_channel_id,
360 p_market_segment_id,
361 p_sales_channel_code,
362 p_interest_type_id,
363 p_primary_interest_code_id,
364 p_secondary_interest_code_id,
365 p_geography_code );
366 ELSE
367 populate_temp (
368 p_campaign_id,
369 p_campaign_status_id,
370 p_campaign_type,
371 p_period_type,
372 p_start_date,
373 p_end_date,
374 p_media_type,
375 p_media_id,
376 p_channel_id,
377 p_market_segment_id,
378 p_sales_channel_code,
379 p_interest_type_id,
380 p_primary_interest_code_id,
381 p_secondary_interest_code_id,
382 p_geography_code,
383 p_view_by );
384 END IF;
385
386 EXCEPTION
387 when others then
388 rollback;
389 -- dbms_output.put_line ( 'Exception raised ' || sqlcode || ':' || sqlerrm );
390 raise;
391 END populate_temp_table;
392
393 END BIM_SLCHN_CMPR_TEMP_PVT;