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