[Home] [Help]
PACKAGE BODY: APPS.BIM_MEDACQ_IND_TEMP_PVT
Source
1 PACKAGE BODY BIM_MEDACQ_IND_TEMP_PVT AS
2 /* $Header: bimvmqib.pls 115.3 2000/12/13 20:12:08 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 = '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 med.media_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_dimv_campaigns cmp,
77 bim_dimv_media med ' || from_clause ||
78 ' where 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 med.media_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 ' || from_clause ||
119 ' where cperf.media_id = med.media_id' || where_clause ||
120 ' and med.media_name = tmp.subject_name
121 and cperf.period_start_date >= :p_start_date
122 and cperf.period_end_date <= :p_end_date
123 and cperf.first_order_date >= :p_start_date
124 and cperf.first_order_date <= :p_end_date
125 and cmp.user_status_id = nvl(:p_campaign_status_id, cmp.user_status_id )
126 and cmp.campaign_type = nvl(:p_campaign_type, cmp.campaign_type)
127 and med.media_type_code = nvl(:p_media_type, med.media_type_code )
128 and cperf.media_id = nvl(:p_media_id, cperf.media_id )
129 and cperf.channel_id = nvl(:p_channel_id, cperf.channel_id)
130 and cperf.sales_channel_code = nvl(:p_sales_channel_code, cperf.sales_channel_code)
131 and cperf.market_segment_id = nvl(:p_market_segment_id, cperf.market_segment_id)
132 and cperf.interest_type_id = nvl(:p_interest_type_id, cperf.interest_type_id )
133 and cperf.primary_interest_code_id = nvl(:p_primary_interest_code_id, cperf.primary_interest_code_id)
134 and cperf.secondary_interest_code_id = nvl(:p_secondary_interest_code_id, secondary_interest_code_id)
135 and cperf.bill_to_geography_code = nvl(:p_geography_code, cperf.bill_to_geography_code )
136 and tmp.view_by_name = ' || v_view_by || ' ) '
137 USING
138 p_campaign_id,
139 p_start_date,
140 p_end_date,
141 p_start_date,
142 p_end_date,
143 p_campaign_status_id,
144 p_campaign_type,
145 p_media_type,
146 p_media_id,
147 p_channel_id,
148 p_sales_channel_code,
149 p_market_segment_id,
150 p_interest_type_id,
151 p_primary_interest_code_id,
152 p_secondary_interest_code_id,
153 p_geography_code;
154
155
156 v_num_rows_updated := SQL%ROWCOUNT;
157 -- dbms_output.put_line ( 'Number of rows updated in temp table is : ' || v_num_rows_updated );
158
159
160 UPDATE bim_camp_acqu_summ_temp SET measure2 = measure2*100/measure3;
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 med.media_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_periods per ' || from_clause ||
217 ' where cperf.media_id = med.media_id' || where_clause ||
218 ' and cperf.period_start_date >= per.start_date
219 and cperf.period_end_date <= per.end_date
220 and per.period_type = :p_period_type
221 and per.period_set_name = jtf_bis_util.profileValue(''CRMBIS:PERIOD_SET_NAME'')
222 and per.start_date >= :p_start_date
223 and per.end_date <= :p_end_date
224 and cmp.user_status_id = nvl(:p_campaign_status_id, cmp.user_status_id )
225 and cmp.campaign_type = nvl(:p_campaign_type, cmp.campaign_type)
226 and med.media_type_code = nvl(:p_media_type, med.media_type_code )
227 and cperf.media_id = nvl(:p_media_id, cperf.media_id )
228 and cperf.channel_id = nvl(:p_channel_id, cperf.channel_id)
229 and cperf.sales_channel_code = nvl(:p_sales_channel_code, cperf.sales_channel_code)
230 and cperf.market_segment_id = nvl(:p_market_segment_id, cperf.market_segment_id)
231 and cperf.interest_type_id = nvl(:p_interest_type_id, cperf.interest_type_id )
232 and cperf.primary_interest_code_id = nvl(:p_primary_interest_code_id, cperf.primary_interest_code_id)
233 and cperf.secondary_interest_code_id = nvl(:p_secondary_interest_code_id, secondary_interest_code_id)
234 and cperf.bill_to_geography_code = nvl(:p_geography_code, cperf.bill_to_geography_code )
235 group by med.media_name, per.period_name, per.start_date'
236 USING
237 p_campaign_id,
238 p_period_type,
239 p_start_date,
240 p_end_date,
241 p_campaign_status_id,
242 p_campaign_type,
243 p_media_type,
244 p_media_id,
245 p_channel_id,
246 p_sales_channel_code,
247 p_market_segment_id,
248 p_interest_type_id,
249 p_primary_interest_code_id,
250 p_secondary_interest_code_id,
251 p_geography_code;
252
253 v_num_rows_inserted := SQL%ROWCOUNT;
254 -- dbms_output.put_line ( 'Number of rows inserted in temp table is : ' || v_num_rows_inserted );
255
256
257 EXECUTE IMMEDIATE 'UPDATE bim_camp_acqu_summ_temp tmp
258 set tmp.measure2 =
259 ( select nvl(sum(cperf.initiated_revenue),0)
260 from bim_customer_rev_summ cperf,
261 bim_dimv_campaigns cmp,
262 bim_dimv_media med,
263 bim_dimv_periods per ' || from_clause ||
264 ' where cperf.media_id = med.media_id' || where_clause ||
265 ' and med.media_name = tmp.subject_name
266 and cperf.period_start_date >= per.start_date
267 and cperf.period_end_date <= per.end_date
268 and cperf.first_order_date >= per.start_date
269 and cperf.first_order_date <= per.end_date
270 and per.period_type = :p_period_type
271 and per.period_set_name = jtf_bis_util.profileValue(''CRMBIS:PERIOD_SET_NAME'')
272 and per.start_date >= :p_start_date
273 and per.end_date <= :p_end_date
274 and cmp.user_status_id = nvl(:p_campaign_status_id, cmp.user_status_id )
275 and cmp.campaign_type = nvl(:p_campaign_type, cmp.campaign_type)
276 and med.media_type_code = nvl(:p_media_type, med.media_type_code )
277 and cperf.media_id = nvl(:p_media_id, cperf.media_id )
278 and cperf.channel_id = nvl(:p_channel_id, cperf.channel_id)
279 and cperf.sales_channel_code = nvl(:p_sales_channel_code, cperf.sales_channel_code)
280 and cperf.market_segment_id = nvl(:p_market_segment_id, cperf.market_segment_id)
281 and cperf.interest_type_id = nvl(:p_interest_type_id, cperf.interest_type_id )
282 and cperf.primary_interest_code_id = nvl(:p_primary_interest_code_id, cperf.primary_interest_code_id)
283 and cperf.secondary_interest_code_id = nvl(:p_secondary_interest_code_id, secondary_interest_code_id)
284 and cperf.bill_to_geography_code = nvl(:p_geography_code, cperf.bill_to_geography_code )
285 and tmp.view_by_name = per.period_name )'
286 USING
287 p_campaign_id,
288 p_period_type,
289 p_start_date,
290 p_end_date,
291 p_campaign_status_id,
292 p_campaign_type,
293 p_media_type,
294 p_media_id,
295 p_channel_id,
296 p_sales_channel_code,
297 p_market_segment_id,
298 p_interest_type_id,
299 p_primary_interest_code_id,
300 p_secondary_interest_code_id,
301 p_geography_code ;
302
303
304 v_num_rows_updated := SQL%ROWCOUNT;
305 -- dbms_output.put_line ( 'Number of rows updated in temp table is : ' || v_num_rows_updated );
306
307
308 END POPULATE_TEMP_BY_PERIOD;
309
310
311 PROCEDURE populate_temp_table
312 (
313 p_campaign_id number DEFAULT NULL,
314 p_campaign_status_id number DEFAULT NULL,
315 p_campaign_type varchar2 DEFAULT NULL,
316 p_period_type varchar2 DEFAULT NULL,
317 p_start_date date DEFAULT NULL,
318 p_end_date date DEFAULT NULL,
319 p_media_type varchar2 DEFAULT NULL,
320 p_media_id number DEFAULT NULL,
321 p_channel_id varchar2 DEFAULT NULL,
322 p_market_segment_id number DEFAULT NULL,
323 p_sales_channel_code varchar2 DEFAULT NULL,
324 p_interest_type_id number DEFAULT NULL,
325 p_primary_interest_code_id number DEFAULT NULL,
326 p_secondary_interest_code_id number DEFAULT NULL,
327 p_geography_code varchar2 DEFAULT NULL,
328 p_view_by varchar2 DEFAULT NULL
329 ) IS
330
331 BEGIN
332
333
334 IF p_view_by = 'PER'
335 THEN
336 populate_temp_by_period (
337 p_campaign_id,
338 p_campaign_status_id,
339 p_campaign_type,
340 p_period_type,
341 p_start_date,
342 p_end_date,
343 p_media_type,
344 p_media_id,
345 p_channel_id,
346 p_market_segment_id,
347 p_sales_channel_code,
348 p_interest_type_id,
349 p_primary_interest_code_id,
350 p_secondary_interest_code_id,
351 p_geography_code );
352 ELSE
353 populate_temp (
354 p_campaign_id,
355 p_campaign_status_id,
356 p_campaign_type,
357 p_period_type,
358 p_start_date,
359 p_end_date,
360 p_media_type,
361 p_media_id,
362 p_channel_id,
363 p_market_segment_id,
364 p_sales_channel_code,
365 p_interest_type_id,
366 p_primary_interest_code_id,
367 p_secondary_interest_code_id,
368 p_geography_code,
369 p_view_by );
370 END IF;
371
372 EXCEPTION
373 when others then
374 rollback;
375 -- dbms_output.put_line ( 'Exception raised ' || sqlcode || ':' || sqlerrm );
376 raise;
377 END populate_temp_table;
378
379 END BIM_MEDACQ_IND_TEMP_PVT;