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