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