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