[Home] [Help]
PACKAGE BODY: APPS.BIM_LEAD_RG_PKG
Source
1 PACKAGE BODY BIM_LEAD_RG_PKG AS
2 /* $Header: bimldrgb.pls 120.3 2005/12/16 14:10:18 snallapa noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(20) :='BIM_LEAD_RG_PKG';
5 G_FILE_NAME CONSTANT VARCHAR2(20) :='bimldrgb.pls';
6
7 -----------------------------------------------------------------------
8 -- PROCEDURE
9 -- POPULATE
10 --
11 -----------------------------------------------------------------------
12
13 PROCEDURE POPULATE
14 (ERRBUF OUT NOCOPY VARCHAR2,
15 RETCODE OUT NOCOPY NUMBER
16 ) IS
17
18 l_table_name VARCHAR2(100);
19 l_return BOOLEAN;
20
21 l_status VARCHAR2(5);
22 l_industry VARCHAR2(5);
23 l_schema VARCHAR2(30);
24
25 BEGIN
26
27 ERRBUF :='SUCCESS';
28 RETCODE := 0;
29
30
31 --get the schema name
32
33 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
34
35 l_table_name := 'BIM_R_LEAD_GRP_MGR';
36 fnd_message.set_name('BIM','BIM_R_TRUNCATE_TABLE');
37 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
38 fnd_file.put_line(fnd_file.log,fnd_message.get);
39
40 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_r_lead_grp_mgr';
41
42 l_table_name := 'BIM_R_LEAD_SUM_FACTS';
43 fnd_message.set_name('BIM','BIM_R_TRUNCATE_TABLE');
44 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
45 fnd_file.put_line(fnd_file.log,fnd_message.get);
46
47 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_r_lead_sum_facts';
48
49 l_table_name := 'BIM_R_LEAD_RES_DENORM';
50 fnd_message.set_name('BIM','BIM_R_TRUNCATE_TABLE');
51 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
52 fnd_file.put_line(fnd_file.log,fnd_message.get);
53
54 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_r_lead_res_denorm';
55
56 l_table_name := 'BIM_R_LEAD_GRP_MGR';
57 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
58 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
59 fnd_file.put_line(fnd_file.log,fnd_message.get);
60
61
62 INSERT
63 INTO bim_r_lead_grp_mgr(
64 creation_date
65 ,created_by
66 ,last_update_date
67 ,last_updated_by
68 ,last_update_login
69 ,group_id
70 ,resource_id
71 )
72 SELECT
73 sysdate
74 ,-1
75 ,sysdate
76 ,-1
77 ,-1
78 ,inner.group_id
79 ,inner.resource_id
80 FROM (
81 SELECT
82 mem.group_id
83 ,res.resource_id
84 FROM
85 jtf_rs_resource_extns res
86 ,jtf_rs_roles_b rol
87 ,jtf_rs_role_relations rlt
88 ,jtf_rs_group_members mem
89 ,jtf_rs_groups_b grp
90 ,jtf_rs_group_usages u
91 WHERE
92 mem.group_member_id = rlt.role_resource_id
93 AND nvl(mem.delete_flag , 'N') <> 'Y'
94 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
95 AND rlt.end_date_active is NULL
96 AND nvl(rlt.delete_flag , 'N') <> 'Y'
97 AND rlt.role_id = rol.role_id
98 AND ((nvl(rol.manager_flag , 'N') = 'Y') OR (nvl(rol.admin_flag, 'N') = 'Y'))
99 AND mem.resource_id = res.resource_id
100 AND res.category = 'EMPLOYEE'
101 AND mem.group_id = grp.group_id
102 AND u.group_id = grp.group_id
103 AND u.usage = 'SALES'
104 GROUP BY
105 mem.group_id
106 ,res.resource_id
107 ) inner;
108 COMMIT;
109
110 l_table_name := 'BIM_R_LEAD_SUM_FACTS';
111 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
112 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
113 fnd_file.put_line(fnd_file.log,fnd_message.get);
114
115 INSERT
116 INTO bim_r_lead_sum_facts(
117 creation_date
118 ,created_by
119 ,last_update_date
120 ,last_updated_by
121 ,last_update_login
122 ,transaction_create_date
123 ,group_id
124 ,lead_rank_id
125 ,lead_source
126 ,lead_status
127 ,open_flag
128 ,object_type
129 ,object_id
130 ,region
131 ,country
132 ,business_unit_id
133 ,year
134 ,qtr
135 ,month
136 ,leads_open
137 ,leads_closed
138 ,leads_new
139 ,leads_dead
140 ,leads_changed
141 ,leads_unchanged
142 ,leads_assigned
143 ,opportunities
144 ,opportunities_open
145 ,quotes
146 ,quotes_open
147 ,orders
148 ,sleads_open
149 ,sleads_closed
150 ,sleads_new
151 ,sleads_dead
152 ,sleads_changed
153 ,sleads_unchanged
154 ,sleads_assigned
155 ,sopportunities
156 ,sopportunities_open
157 ,squotes
158 ,squotes_open
159 ,sorders
160 )
161 SELECT
162 sysdate
163 ,-1
164 ,sysdate
165 ,-1
166 ,-1
167 ,inner.transaction_create_date transaction_create_date
168 ,inner.group_id group_id
169 ,inner.lead_rank_id lead_rank_id
170 ,inner.lead_source lead_source
171 ,inner.lead_status lead_status
172 ,inner.open_flag open_flag
173 ,inner.object_type object_type
174 ,inner.object_id object_id
175 ,inner.region region
176 ,inner.country country
177 ,inner.business_unit_id business_unit_id
178 ,inner.year year
179 ,inner.qtr qtr
180 ,inner.month month
181 ,sum(inner.leads_open) leads_open
182 ,sum(inner.leads_closed) leads_closed
183 ,sum(inner.leads_new) leads_new
184 ,sum(inner.leads_dead) leads_dead
185 ,sum(inner.leads_changed) leads_changed
186 ,sum(inner.leads_unchanged) leads_unchanged
187 ,sum(inner.leads_assigned) leads_assigned
188 ,sum(inner.opportunities) opportunities
189 ,sum(inner.opportunities_open) opportunities_open
190 ,sum(inner.quotes) quotes
191 ,sum(inner.quotes_open) quotes_open
192 ,sum(inner.orders) orders
193 ,sum(inner.sleads_open) sleads_open
194 ,sum(inner.sleads_closed) sleads_closed
195 ,sum(inner.sleads_new) sleads_new
196 ,sum(inner.sleads_dead) sleads_dead
197 ,sum(inner.sleads_changed) sleads_changed
198 ,sum(inner.sleads_unchanged) sleads_unchanged
199 ,sum(inner.sleads_assigned) sleads_assigned
200 ,sum(inner.sopportunities) sopportunities
201 ,sum(inner.sopportunities_open) sopportunities_open
202 ,sum(inner.squotes) squotes
203 ,sum(inner.squotes_open) squotes_open
204 ,sum(inner.sorders) sorders
205 FROM (
206 SELECT
207 rgrp.group_id group_id
208 ,a.transaction_create_date transaction_create_date
209 ,a.lead_rank_id lead_rank_id
210 ,a.lead_source lead_source
211 ,a.lead_status lead_status
212 ,a.open_flag open_flag
213 ,a.object_type object_type
214 ,a.object_id object_id
215 ,a.region region
216 ,a.country country
217 ,a.business_unit_id business_unit_id
218 ,a.year year
219 ,a.qtr qtr
220 ,a.month month
221 ,sum(a.leads_open) leads_open
222 ,sum(a.leads_closed) leads_closed
223 ,sum(a.leads_new) leads_new
224 ,sum(a.leads_dead) leads_dead
225 ,sum(a.leads_changed) leads_changed
226 ,sum(a.leads_unchanged) leads_unchanged
227 ,sum(a.leads_assigned) leads_assigned
228 ,sum(a.opportunities) opportunities
229 ,sum(a.opportunities_open) opportunities_open
230 ,sum(a.quotes) quotes
231 ,sum(a.quotes_open) quotes_open
232 ,sum(a.orders) orders
233 ,0 sleads_open
234 ,0 sleads_closed
235 ,0 sleads_new
236 ,0 sleads_dead
237 ,0 sleads_changed
238 ,0 sleads_unchanged
239 ,0 sleads_assigned
240 ,0 sopportunities
241 ,0 sopportunities_open
242 ,0 squotes
243 ,0 squotes_open
244 ,0 sorders
245 FROM
246 (select group_id from
247 bim_r_lead_grp_mgr
248 group by group_id) RGRP
249 ,bim_r_lead_daily_facts a
250 ,jtf_rs_groups_denorm GDN
251 WHERE
252 rgrp.group_id = gdn.parent_group_id
253 AND gdn.group_id = a.group_id
254 AND gdn.end_date_active is null
255 GROUP BY
256 rgrp.group_id
257 ,a.transaction_create_date
258 ,a.lead_rank_id
259 ,a.lead_source
260 ,a.lead_status
261 ,a.open_flag
262 ,a.object_type
263 ,a.object_id
264 ,a.region
265 ,a.country
266 ,a.business_unit_id
267 ,a.year
268 ,a.qtr
269 ,a.month
270 ----------------
271 UNION ALL
272 ----------------
273 SELECT
274 rgrp.group_id group_id
275 ,a.transaction_create_date transaction_create_date
276 ,a.lead_rank_id lead_rank_id
277 ,a.lead_source lead_source
278 ,a.lead_status lead_status
279 ,a.open_flag open_flag
280 ,a.object_type object_type
281 ,a.object_id object_id
282 ,a.region region
283 ,a.country country
284 ,a.business_unit_id business_unit_id
285 ,a.year year
286 ,a.qtr qtr
287 ,a.month month
288 ,0 leads_open
289 ,0 leads_closed
290 ,0 leads_new
291 ,0 leads_dead
292 ,0 leads_changed
293 ,0 leads_unchanged
294 ,0 leads_assigned
295 ,0 opportunities
296 ,0 opportunities_open
297 ,0 quotes
298 ,0 quotes_open
299 ,0 orders
300 ,sum(a.leads_open) sleads_open
301 ,sum(a.leads_closed) sleads_closed
302 ,sum(a.leads_new) sleads_new
303 ,sum(a.leads_dead) sleads_dead
304 ,sum(a.leads_changed) sleads_changed
305 ,sum(a.leads_unchanged) sleads_unchanged
306 ,sum(a.leads_assigned) sleads_assigned
307 ,sum(a.opportunities) sopportunities
308 ,sum(a.opportunities_open) sopportunities_open
309 ,sum(a.quotes) squotes
310 ,sum(a.quotes_open) squotes_open
311 ,sum(a.orders) sorders
312 FROM
313 (select group_id from
314 bim_r_lead_grp_mgr
315 group by group_id) RGRP
316 ,bim_r_lead_daily_facts a
317 WHERE
318 rgrp.group_id = a.group_id
319 GROUP BY
320 rgrp.group_id
321 ,a.transaction_create_date
322 ,a.lead_rank_id
323 ,a.lead_source
324 ,a.lead_status
325 ,a.open_flag
326 ,a.object_type
327 ,a.object_id
328 ,a.region
329 ,a.country
330 ,a.business_unit_id
331 ,a.year
332 ,a.qtr
333 ,a.month
334 ) inner
335 GROUP BY
336 inner.group_id
337 ,inner.transaction_create_date
338 ,inner.lead_rank_id
339 ,inner.lead_source
340 ,inner.lead_status
341 ,inner.open_flag
342 ,inner.object_type
343 ,inner.object_id
344 ,inner.region
345 ,inner.country
346 ,inner.business_unit_id
347 ,inner.year
348 ,inner.qtr
349 ,inner.month
350 ;
351 COMMIT;
352
353 l_table_name := 'BIM_R_LEAD_RES_DENORM';
354 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
355 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
356 fnd_file.put_line(fnd_file.log,fnd_message.get);
357
358 INSERT
359 INTO bim_r_lead_res_denorm(
360 creation_date
361 ,created_by
362 ,last_update_date
363 ,last_updated_by
364 ,last_update_login
365 ,resource_id
366 ,group_id
367 ,child_group_id
368 ,owner_flag
369 )
370 SELECT
371 sysdate
372 ,-1
373 ,sysdate
374 ,-1
375 ,-1
376 ,inner.resource_id
377 ,inner.group_id
378 ,inner.child_group_id
379 ,inner.owner_flag
380 FROM (
381 SELECT
382 resource_id
383 ,group_id
384 ,child_group_id
385 ,owner_flag
386 FROM (
387 SELECT
388 a.resource_id,
389 b.parent_group_id group_id,
390 b.group_id child_group_id,
391 'N' owner_flag
392 FROM
393 bim_r_lead_grp_mgr a
394 ,jtf_rs_groups_denorm b
395 WHERE
396 a.group_id = b.parent_group_id
397 AND b.immediate_parent_flag = 'Y'
398 AND b.end_date_active is null
399 GROUP BY
400 a.resource_id,
401 b.parent_group_id,
402 b.group_id
403 ---------
404 UNION ALL
405 ---------
406 SELECT
407 a.resource_id,
408 b.parent_group_id group_id,
409 b.group_id child_group_id,
410 'Y' owner_flag
411 FROM
412 bim_r_lead_grp_mgr a
413 ,jtf_rs_groups_denorm b
414 WHERE
415 a.group_id = b.parent_group_id
416 AND b.parent_group_id = b.group_id
417 AND b.immediate_parent_flag = 'N'
418 AND b.end_date_active is null
419 GROUP BY
420 a.resource_id,
421 b.parent_group_id,
422 b.group_id
423 )
424 GROUP BY
425 resource_id,
426 group_id,
427 child_group_id,
428 owner_flag
429 ) inner;
430 COMMIT;
431
432
433
434 DELETE FROM bim_rep_history
435 WHERE object='LEAD_RG';
436 INSERT INTO
437 bim_rep_history
438 (creation_date,
439 last_update_date,
440 created_by,
441 last_updated_by,
442 object,
443 object_last_updated_date)
444 VALUES
445 (sysdate,
446 sysdate,
447 FND_GLOBAL.USER_ID(),
448 FND_GLOBAL.USER_ID(),
449 'LEAD_RG',
450 sysdate);
451 COMMIT;
452
453 fnd_message.set_name('BIM','BIM_R_PROG_COMPLETION');
454 fnd_message.set_token('PROGRAM_NAME','GROUP HIERARCHY SUM OF LEADS',FALSE);
455 fnd_file.put_line(fnd_file.log,fnd_message.get);
456
457
458 EXCEPTION
459
460 WHEN OTHERS THEN
461 ams_utility_pvt.write_conc_log('BIM_LEAD_RG_PKG--POPULATE: Error occured '||sqlerrm(sqlcode));
462 ERRBUF := sqlerrm(sqlcode);
463 RETCODE := sqlcode;
464
465 -- dbms_output.put_line('END OF POPULATING BIM_LEAD_RG_PKG');
466 -- dbms_output.put_line('END OF POPULATE');
467
468 END POPULATE;
469
470 END BIM_LEAD_RG_PKG;