[Home] [Help]
PACKAGE BODY: APPS.BIM_MCHACQ_ALL_TEMP_PVT
Source
1 PACKAGE BODY BIM_MCHACQ_ALL_TEMP_PVT AS
2 /* $Header: bimvnqab.pls 115.2 2000/03/03 20:36:18 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_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 ' ||
50 IF p_view_by = 'CMP'
47 ' and cmp.campaign_id = nvl(:p_campaign_id, cmp.campaign_id ) ';
48 END IF;
49
51 THEN
52 v_view_by := 'cmp.campaign_name' ;
53 ELSIF p_view_by = 'MED'
54 THEN
55 v_view_by := 'med.media_name' ;
56 ELSIF p_view_by = 'SCH'
57 THEN
58 v_view_by := 'sch.sales_channel_name' ;
59 from_clause := from_clause || ',bim_dimv_sales_channels sch' ;
60 where_clause := where_clause || ' and cperf.sales_channel_code = sch.sales_channel_code';
61 ELSIF p_view_by = 'MKT'
62 THEN
63 v_view_by := 'mkt.market_segment_name' ;
64 from_clause := from_clause || ',bim_dimv_market_sgmts mkt' ;
65 where_clause := where_clause || ' and cperf.market_segment_id = mkt.market_segment_id';
66 END IF;
67
68 OPEN LC_GET_ALL_NAME;
69 FETCH LC_GET_ALL_NAME INTO v_all_name;
70 CLOSE LC_GET_ALL_NAME;
71
72 EXECUTE IMMEDIATE ' INSERT INTO bim_camp_acqu_summ_temp
73 ( subject_name,
74 view_by_name,
75 rank_by,
76 measure1,
77 measure3 )
78 select ''' || v_all_name || ''',' ||
79 v_view_by ||
80 ', count( distinct cperf.cust_account_id ),
81 count( distinct cperf.cust_account_id ),
82 nvl(sum(cperf.initiated_revenue), 0 )
83 from bim_cmpgn_perf_summ cperf,
84 bim_dimv_campaigns cmp,
85 bim_dimv_media med ' || from_clause ||
86 ' where cperf.media_id = med.media_id' || where_clause ||
87 ' and cperf.period_start_date >= :p_start_date
88 and cperf.period_end_date <= :p_end_date
89 and cmp.user_status_id = nvl(:p_campaign_status_id, cmp.user_status_id )
90 and cmp.campaign_type = nvl(:p_campaign_type, cmp.campaign_type)
91 and med.media_type_code = nvl(:p_media_type, med.media_type_code )
92 and cperf.media_id = nvl(:p_media_id, cperf.media_id )
93 and cperf.sales_channel_code = nvl(:p_sales_channel_code, cperf.sales_channel_code)
94 and cperf.market_segment_id = nvl(:p_market_segment_id, cperf.market_segment_id)
95 and cperf.interest_type_id = nvl(:p_interest_type_id, cperf.interest_type_id )
96 and cperf.primary_interest_code_id = nvl(:p_primary_interest_code_id, cperf.primary_interest_code_id)
97 and cperf.secondary_interest_code_id = nvl(:p_secondary_interest_code_id, secondary_interest_code_id)
98 and cperf.bill_to_geography_code = nvl(:p_geography_code, cperf.bill_to_geography_code )
99 group by ' || v_view_by
100 USING
101 p_campaign_id,
102 p_start_date,
103 p_end_date,
104 p_campaign_status_id,
105 p_campaign_type,
106 p_media_type,
107 p_media_id,
108 p_sales_channel_code,
109 p_market_segment_id,
110 p_interest_type_id,
111 p_primary_interest_code_id,
112 p_secondary_interest_code_id,
113 p_geography_code;
114
115 v_num_rows_inserted := SQL%ROWCOUNT;
116 -- dbms_output.put_line ( 'Number of rows inserted in temp table is : ' || v_num_rows_inserted );
117
118
119 EXECUTE IMMEDIATE 'UPDATE bim_camp_acqu_summ_temp tmp
120 set tmp.measure2 =
121 ( select nvl(sum(cperf.initiated_revenue),0)
122 from bim_customer_rev_summ cperf,
123 bim_dimv_campaigns cmp,
124 bim_dimv_media med' || from_clause ||
125 ' where cperf.media_id = med.media_id' || where_clause ||
126 ' and cperf.period_start_date >= :p_start_date
127 and cperf.period_end_date <= :p_end_date
128 and cperf.first_order_date >= :p_start_date
129 and cperf.first_order_date <= :p_end_date
130 and cmp.user_status_id = nvl(:p_campaign_status_id, cmp.user_status_id )
131 and cmp.campaign_type = nvl(:p_campaign_type, cmp.campaign_type)
132 and med.media_type_code = nvl(:p_media_type, med.media_type_code )
133 and cperf.media_id = nvl(:p_media_id, cperf.media_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_sales_channel_code,
152 p_market_segment_id,
153 p_interest_type_id,
154 p_primary_interest_code_id,
155 p_secondary_interest_code_id,
156 p_geography_code;
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,
175 p_market_segment_id number DEFAULT NULL,
172 p_end_date date DEFAULT NULL,
173 p_media_type varchar2 DEFAULT NULL,
174 p_media_id number DEFAULT NULL,
176 p_sales_channel_code varchar2 DEFAULT NULL,
177 p_interest_type_id number DEFAULT NULL,
178 p_primary_interest_code_id number DEFAULT NULL,
179 p_secondary_interest_code_id number DEFAULT NULL,
180 p_geography_code varchar2 DEFAULT NULL
181 ) IS
182
183 v_num_rows_inserted integer;
184 v_num_rows_updated integer;
185 from_clause varchar2(100);
186 where_clause varchar2(250);
187 v_all_name varchar2(80);
188
189 CURSOR LC_GET_ALL_NAME
190 IS
191 SELECT meaning
192 FROM FND_LOOKUPS
193 WHERE lookup_type = 'BIM_VALUE_TYPE'
194 AND lookup_code = 'ALL' ;
195
196 BEGIN
197
198 IF p_campaign_id is not null
199 THEN
200 from_clause := from_clause || ',bim_campaigns_denorm dnm' ;
201 where_clause := where_clause || ' and cperf.campaign_id = dnm.campaign_id ' ||
202 ' and dnm.parent_campaign_id = cmp.campaign_id ' ||
203 ' and cmp.campaign_id = :p_campaign_id ' ;
204 ELSE
205 where_clause := where_clause || ' and cperf.campaign_id = cmp.campaign_id ' ||
206 ' and cmp.campaign_id = nvl(:p_campaign_id, cmp.campaign_id ) ';
207 END IF;
208
209 EXECUTE IMMEDIATE ' INSERT INTO bim_camp_acqu_summ_temp
210 ( subject_name,
211 view_by_name,
212 rank_by,
213 measure1,
214 measure3 )
215 select ''' || v_all_name ||
216 ''', per.period_name,
217 to_number( to_char(per.start_date, ''J'')),
218 count( distinct cperf.cust_account_id ),
219 nvl(sum(cperf.initiated_revenue), 0 )
220 from bim_cmpgn_perf_summ cperf,
221 bim_dimv_campaigns cmp,
222 bim_dimv_media med,
223 bim_dimv_periods per' || from_clause ||
224 ' where cperf.media_id = med.media_id' || where_clause ||
225 ' and cperf.period_start_date >= per.start_date
226 and cperf.period_end_date <= per.end_date
227 and per.period_type = :p_period_type
228 and per.period_set_name = jtf_bis_util.profileValue(''CRMBIS:PERIOD_SET_NAME'')
229 and per.start_date >= :p_start_date
230 and per.end_date <= :p_end_date
231 and cmp.user_status_id = nvl(:p_campaign_status_id, cmp.user_status_id )
232 and cmp.campaign_type = nvl(:p_campaign_type, cmp.campaign_type)
233 and med.media_type_code = nvl(:p_media_type, med.media_type_code )
234 and cperf.media_id = nvl(:p_media_id, cperf.media_id )
235 and cperf.sales_channel_code = nvl(:p_sales_channel_code, cperf.sales_channel_code)
236 and cperf.market_segment_id = nvl(:p_market_segment_id, cperf.market_segment_id)
237 and cperf.interest_type_id = nvl(:p_interest_type_id, cperf.interest_type_id )
238 and cperf.primary_interest_code_id = nvl(:p_primary_interest_code_id, cperf.primary_interest_code_id)
239 and cperf.secondary_interest_code_id = nvl(:p_secondary_interest_code_id, secondary_interest_code_id)
240 and cperf.bill_to_geography_code = nvl(:p_geography_code, cperf.bill_to_geography_code )
241 group by per.period_name, per.start_date'
242 USING
243 p_campaign_id,
244 p_period_type,
245 p_start_date,
246 p_end_date,
247 p_campaign_status_id,
248 p_campaign_type,
249 p_media_type,
250 p_media_id,
251 p_sales_channel_code,
252 p_market_segment_id,
253 p_interest_type_id,
254 p_primary_interest_code_id,
255 p_secondary_interest_code_id,
256 p_geography_code;
257
258 v_num_rows_inserted := SQL%ROWCOUNT;
259 -- dbms_output.put_line ( 'Number of rows inserted in temp table is : ' || v_num_rows_inserted );
260
261
262 EXECUTE IMMEDIATE 'UPDATE bim_camp_acqu_summ_temp tmp
263 set tmp.measure2 =
264 ( select nvl(sum(cperf.initiated_revenue),0)
265 from bim_customer_rev_summ cperf,
266 bim_dimv_campaigns cmp,
267 bim_dimv_media med,
268 bim_dimv_periods per ' || from_clause ||
269 ' where cperf.media_id = med.media_id ' || where_clause ||
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.sales_channel_code = nvl(:p_sales_channel_code, cperf.sales_channel_code)
283 and cperf.market_segment_id = nvl(:p_market_segment_id, cperf.market_segment_id)
284 and cperf.interest_type_id = nvl(:p_interest_type_id, cperf.interest_type_id )
285 and cperf.primary_interest_code_id = nvl(:p_primary_interest_code_id, cperf.primary_interest_code_id)
286 and cperf.secondary_interest_code_id = nvl(:p_secondary_interest_code_id, secondary_interest_code_id)
287 and cperf.bill_to_geography_code = nvl(:p_geography_code, cperf.bill_to_geography_code )
288 and tmp.view_by_name = per.period_name )'
289 USING
290 p_campaign_id,
291 p_period_type,
292 p_start_date,
293 p_end_date,
294 p_campaign_status_id,
295 p_campaign_type,
296 p_media_type,
297 p_media_id,
298 p_sales_channel_code,
299 p_market_segment_id,
300 p_interest_type_id,
301 p_primary_interest_code_id,
302 p_secondary_interest_code_id,
303 p_geography_code;
304
305
306 v_num_rows_updated := SQL%ROWCOUNT;
307 -- dbms_output.put_line ( 'Number of rows updated in temp table is : ' || v_num_rows_updated );
308
309
310 END POPULATE_TEMP_BY_PERIOD;
311
312
313 PROCEDURE populate_temp_table
314 (
315 p_campaign_id number DEFAULT NULL,
316 p_campaign_status_id number DEFAULT NULL,
317 p_campaign_type varchar2 DEFAULT NULL,
318 p_period_type varchar2 DEFAULT NULL,
319 p_start_date date DEFAULT NULL,
320 p_end_date date DEFAULT NULL,
321 p_media_type varchar2 DEFAULT NULL,
322 p_media_id number DEFAULT NULL,
323 p_market_segment_id number DEFAULT NULL,
324 p_sales_channel_code varchar2 DEFAULT NULL,
325 p_interest_type_id number DEFAULT NULL,
326 p_primary_interest_code_id number DEFAULT NULL,
327 p_secondary_interest_code_id number DEFAULT NULL,
328 p_geography_code varchar2 DEFAULT NULL,
329 p_view_by varchar2 DEFAULT NULL
330 ) IS
331
332 BEGIN
333
334
335 IF p_view_by = 'PER'
336 THEN
337 populate_temp_by_period (
338 p_campaign_id,
339 p_campaign_status_id,
340 p_campaign_type,
341 p_period_type,
342 p_start_date,
343 p_end_date,
344 p_media_type,
345 p_media_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_market_segment_id,
363 p_sales_channel_code,
364 p_interest_type_id,
365 p_primary_interest_code_id,
366 p_secondary_interest_code_id,
367 p_geography_code,
368 p_view_by );
369 END IF;
370
371 EXCEPTION
372 when others then
373 rollback;
374 -- dbms_output.put_line ( 'Exception raised ' || sqlcode || ':' || sqlerrm );
375 raise;
376 END populate_temp_table;
377
378 END BIM_MCHACQ_ALL_TEMP_PVT;