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