[Home] [Help]
PACKAGE BODY: APPS.BIM_I_SRC_CODE_PKG
Source
1 PACKAGE BODY BIM_I_SRC_CODE_PKG AS
2 /*$Header: bimiscdb.pls 120.6 2006/01/23 02:08:06 arvikuma noship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(20) := 'BIM_I_SRC_CODE_PKG';
5 g_file_name CONSTANT VARCHAR2(20) := 'bimiscdb.pls';
6 g_start_date CONSTANT DATE := to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'),'MM/DD/YYYY');
7
8 ------------------------------------------------------------------------------------------------
9 ----
10 ----This procedure finds out if the user is trying to run first_load or subsequent load
11 ----and calls the load_data procedure with the specific parameters to each type of load
12 ----
13 ------------------------------------------------------------------------------------------------
14
15 PROCEDURE POPULATE
16 (
17 p_api_version_number IN NUMBER
18 ,p_init_msg_list IN VARCHAR2
19 ,p_validation_level IN NUMBER
20 ,p_commit IN VARCHAR2
21 ,x_msg_count OUT NOCOPY NUMBER
22 ,x_msg_data OUT NOCOPY VARCHAR2
23 ,x_return_status OUT NOCOPY VARCHAR2
24 ,p_start_date IN DATE
25 ,p_end_date IN DATE
26 ,p_para_num IN NUMBER
27 ,p_truncate_flg IN VARCHAR2
28 --,p_mode IN VARCHAR2
29 ) IS
30
31 l_object_name CONSTANT VARCHAR2(80) := 'BIM_SOURCE_CODE';
32 l_conc_start_date DATE;
33 l_conc_end_date DATE;
34 l_start_date DATE;
35 l_end_date DATE;
36 l_user_id NUMBER := FND_GLOBAL.USER_ID();
37 l_api_version_number CONSTANT NUMBER := 1.0;
38 l_api_name CONSTANT VARCHAR2(30) := 'BIM_I_SRC_CODE_PKG';
39 l_mesg_text VARCHAR2(100);
40 l_load_type VARCHAR2(100);
41 l_global_date CONSTANT DATE := bis_common_parameters.get_global_start_date;
42 l_missing_date BOOLEAN := FALSE;
43 l_sysdate DATE;
44
45 BEGIN
46
47
48 IF NOT bis_collection_utilities.setup(l_object_name) THEN
49 bis_collection_utilities.log('Object BIM_SOURCE_CODE Not Setup Properly');
50 RAISE FND_API.G_EXC_ERROR;
51 END IF;
52 bis_collection_utilities.log('Start of the Source Code Population Program');
53
54 -- Standard call to check for call compatibility.
55 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
56 p_api_version_number,
57 l_api_name,
58 g_pkg_name)
59 THEN
60 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
61 END IF;
62
63 -- Initialize message list if p_init_msg_list is set to TRUE.
64 IF FND_API.to_Boolean( p_init_msg_list )
65 THEN
66 FND_MSG_PUB.initialize;
67 END IF;
68
69 -- Initialize API return status to SUCCESS
70 x_return_status := FND_API.G_RET_STS_SUCCESS;
71
72 bis_collection_utilities.get_last_refresh_dates(l_object_name,
73 l_conc_start_date,
74 l_conc_end_date,
75 l_start_date,
76 l_end_date);
77 IF (l_end_date IS NULL) THEN
78
79
80 l_load_type := 'FIRST_LOAD';
81
82 FIRST_LOAD(p_start_date => greatest(trunc(l_global_date),trunc(p_start_date))
83 ,p_end_date => sysdate
84 ,p_api_version_number => l_api_version_number
85 ,p_init_msg_list => FND_API.G_FALSE
86 ,x_msg_count => x_msg_count
87 ,x_msg_data => x_msg_data
88 ,x_return_status => x_return_status
89 );
90
91 ELSE
92 --i.e Incremental has to be executed.
93 IF p_truncate_flg = 'Y' THEN
94
95 l_load_type := 'FIRST_LOAD';
96 l_sysdate := sysdate;
97
98 FIRST_LOAD(p_start_date => greatest(l_global_date,p_start_date)
99 ,p_end_date => l_sysdate
100 ,p_api_version_number => l_api_version_number
101 ,p_init_msg_list => FND_API.G_FALSE
102 ,x_msg_count => x_msg_count
103 ,x_msg_data => x_msg_data
104 ,x_return_status => x_return_status
105 );
106 ELSE
107 l_load_type := 'SUBSEQUENT_LOAD';
108
109 INCREMENTAL_LOAD(p_start_date => trunc(l_end_date)
110 ,p_end_date => sysdate
111 ,p_api_version_number => l_api_version_number
112 ,p_init_msg_list => FND_API.G_FALSE
113 ,x_msg_count => x_msg_count
114 ,x_msg_data => x_msg_data
115 ,x_return_status => x_return_status
116 );
117 END IF;
118 END IF;
119
120
121 IF x_return_status = FND_API.g_ret_sts_error
122 THEN
123 RAISE FND_API.g_exc_error;
124 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
125 RAISE FND_API.g_exc_unexpected_error;
126 END IF;
127
128 --Standard check of commit
129
130 IF FND_API.To_Boolean ( p_commit ) THEN
131 COMMIT WORK;
132 END IF;
133
134 COMMIT;
135
136
137 -- Standard call to get message count and if count is 1, get message info.
138 FND_MSG_PUB.Count_And_Get
139 (p_count => x_msg_count,
140 p_data => x_msg_data
141 );
142
143 EXCEPTION
144
145 WHEN FND_API.G_EXC_ERROR THEN
146 x_return_status := FND_API.G_RET_STS_ERROR;
147 -- Standard call to get message count and if count=1, get the message
148 FND_msg_PUB.Count_And_Get (
149 -- p_encoded => FND_API.G_FALSE,
150 p_count => x_msg_count,
151 p_data => x_msg_data
152 );
153
154
155 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
156
157 /* FOR l_counter IN 1 .. x_msg_count
158 LOOP
159 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
160 fnd_msg_pub.dump_msg(l_counter);
161 end loop; */
162
163 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
164 -- Standard call to get message count and if count=1, get the message
165 FND_msg_PUB.Count_And_Get (
166 --p_encoded => FND_API.G_FALSE,
167 p_count => x_msg_count,
168 p_data => x_msg_data
169 );
170
171 WHEN OTHERS THEN
172 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
173 IF FND_msg_PUB.Check_msg_Level ( FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
174 THEN
175 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
176 END IF;
177 -- Standard call to get message count and if count=1, get the message
178 FND_msg_PUB.Count_And_Get (
179 -- p_encoded => FND_API.G_FALSE,
180 p_count => x_msg_count,
181 p_data => x_msg_data
182 );
183
184 END POPULATE;
185
186
187 --------------------------------------------------------------------------------------------------
188 -- This procedure will populates all the data required into facts table for the first load.
189 --
190 -- PROCEDURE FIRST_LOAD
191 --------------------------------------------------------------------------------------------------
192
193 PROCEDURE FIRST_LOAD
194 ( p_start_date IN DATE
195 ,p_end_date IN DATE
196 ,p_api_version_number IN NUMBER
197 ,p_init_msg_list IN VARCHAR2
198 ,x_msg_count OUT NOCOPY NUMBER
199 ,x_msg_data OUT NOCOPY VARCHAR2
200 ,x_return_status OUT NOCOPY VARCHAR2
201 )
202 IS
203 l_user_id NUMBER := FND_GLOBAL.USER_ID();
204 l_api_version_number CONSTANT NUMBER := 1.0;
205 l_api_name CONSTANT VARCHAR2(30) := 'FIRST_LOAD';
206 l_table_name VARCHAR2(100);
207
208
209 l_status VARCHAR2(5);
210 l_industry VARCHAR2(5);
211 l_schema VARCHAR2(30);
212 l_return BOOLEAN;
213
214 TYPE generic_number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
215
216 TYPE generic_char_table IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
217
218 i NUMBER;
219 l_min_start_date DATE;
220
221 l_org_id number;
222
223
224
225 BEGIN
226 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
227
228 -- Standard call to check for call compatibility.
229 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
230 p_api_version_number,
231 l_api_name,
232 g_pkg_name)
233 THEN
234 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
235 END IF;
236
237 -- Initialize message list if p_init_msg_list is set to TRUE.
238 IF FND_API.to_Boolean( p_init_msg_list )
239 THEN
240 FND_msg_PUB.initialize;
241 END IF;
242
243 -- Initialize API return status to SUCCESS
244 x_return_status := FND_API.G_RET_STS_SUCCESS;
245
246
247
248 /* Dropping INdexes */
249 -- BIM_UTL_PKG.DROP_INDEX('BIM_I_SOURCE_CODES');
250
251 --EXECUTE IMMEDIATE 'TRUNCATE TABLE bim.bim_i_source_codes ';
252
253
254 l_table_name := 'BIM_I_SOURCE_CODES';
255 bis_collection_utilities.log('Running Initial Load of Source Codes');
256
257 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.BIM_I_SOURCE_CODES';
258
259 BIS_COLLECTION_UTILITIES.deleteLogForObject('BIM_SOURCE_CODE');
260
261 INSERT /*+ append parallel */
262 INTO BIM_I_SOURCE_CODES SRC
263 (
264 creation_date,
265 last_update_date,
266 created_by,
267 last_updated_by,
268 last_update_login,
269 source_code_id,
270 parent_source_code_id,
271 source_code,
272 rollup_type,
273 object_id,
274 object_type,
275 object_status,
276 object_purpose,
277 object_country,
278 object_region,
279 business_unit_id,
280 owner_user_id,
281 start_date,
282 end_date,
283 child_object_id,
284 child_object_type,
285 child_object_status,
286 child_object_purpose,
287 child_object_country,
288 child_object_region,
289 child_object_usage,
290 activity_id,
291 activity_type,
292 adj_start_date,
293 adj_end_date,
294 obj_last_update_date
295 )
296 SELECT
297 sysdate,
298 sysdate,
299 -1,
300 -1,
301 -1,
302 inner.source_code_id,
303 inner.parent_source_code_id,
304 inner.source_code,
305 inner.rollup_type,
306 inner.object_id,
307 inner.object_type,
308 inner.object_status,
309 inner.object_purpose,
310 inner.object_country,
311 inner.object_region,
312 inner.business_unit_id,
313 inner.owner_user_id,
314 trunc(inner.start_date),
315 trunc(inner.end_date),
316 inner.child_object_id,
317 inner.child_object_type,
318 inner.child_object_status,
319 inner.child_object_purpose,
320 inner.child_object_country,
321 inner.child_object_region,
322 inner.child_object_usage,
323 inner.activity_id,
324 inner.activity_type,
325 trunc(inner.adj_start_date),
326 trunc(inner.adj_end_date),
327 inner.obj_last_update_date
328 FROM
329 (
330 SELECT
331 a.source_code_id source_code_id,
332 b.parent_campaign_id*(-1) parent_source_code_id,
333 a.source_code source_code,
334 'CAMP' rollup_type,
335 b.campaign_id object_id,
336 --b.rollup_type object_type,
337 a.arc_source_code_for object_type,
338 b.status_code object_status,
339 b.campaign_type object_purpose,
340 d.country_code object_country,
341 t.parent_territory_code object_region,
342 b.business_unit_id business_unit_id,
343 b.owner_user_id owner_user_id,
344 b.actual_exec_start_date start_date,
345 b.actual_exec_end_date end_date,
346 0 child_object_id,
347 --b.rollup_type child_object_type,
348 a.arc_source_code_for child_object_type,
349 '' child_object_status,
350 '' child_object_purpose,
351 '' child_object_country,
352 '' child_object_region,
353 '' child_object_usage,
354 0 activity_id,
355 '' activity_type,
356 case
357 when b.actual_exec_end_date < g_start_date then null
358 else greatest(b.actual_exec_start_date,g_start_date) end adj_start_date,
359 case
360 when b.actual_exec_end_date < g_start_date then null
361 else b.actual_exec_end_date end adj_end_date,
362 b.last_update_date obj_last_update_date
363 FROM
364 ams_source_codes a,
365 ams_campaigns_all_b b,
366 jtf_loc_hierarchies_b d,
367 bis_territory_hierarchies t
368 WHERE
369 a.source_code = b.source_code
370 AND a.source_code_for_id = b.campaign_id
371 AND b.city_id = d.location_hierarchy_id
372 AND t.parent_territory_type(+) = 'AREA'
373 AND t.child_territory_type(+) = 'COUNTRY'
374 AND t.child_territory_code(+) = d.country_code
375 AND a.arc_source_code_for = 'CAMP'
376 AND b.rollup_type not in ('RCAM')
377 AND b.status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE', 'ON_HOLD')
378 AND b.actual_exec_start_date <= p_end_date
379 ----------------------------------------------+
380 UNION ALL
381 ----------------------------------------------+
382 SELECT
383 a.source_code_id source_code_id,
384 s.source_code_id parent_source_code_id,
385 a.source_code source_code,
386 'CSCH' rollup_type,
387 b.campaign_id object_id,
388 --b.rollup_type object_type,
389 'CAMP' object_type,
390 b.status_code object_status,
391 b.campaign_type object_purpose,
392 d.country_code object_country,
393 t.parent_territory_code object_region,
394 b.business_unit_id business_unit_id,
395 c.owner_user_id owner_user_id,
396 c.start_date_time start_date,
397 TO_DATE(DECODE(c.end_date_time,null,
398 DECODE(c.start_date_time,null,null,b.actual_exec_end_date),
399 c.end_date_time),'DD/MM/RRRR') end_date,
400 c.schedule_id child_object_id,
401 a.arc_source_code_for child_object_type,
402 c.status_code child_object_status,
403 c.purpose child_object_purpose,
404 d2.country_code child_object_country,
405 d2.area2_code child_object_region,
406 c.usage child_object_region,
407 c.activity_id activity_id,
408 c.activity_type_code activity_type,
409 case
410 when nvl(c.end_date_time,b.actual_exec_end_date) < g_start_date then null
411 else greatest(c.start_date_time,g_start_date) end adj_start_date,
412 case
413 when nvl(c.end_date_time,b.actual_exec_end_date) < g_start_date then null
414 else
415 TO_DATE(DECODE(c.end_date_time,null,
416 DECODE(c.start_date_time,null,null,b.actual_exec_end_date),
417 c.end_date_time),'DD/MM/RRRR') end adj_end_date,
418 c.last_update_date obj_last_update_date
419 FROM
420 ams_source_codes a,
421 ams_campaigns_all_b b,
422 ams_campaign_schedules_b c,
423 jtf_loc_hierarchies_b d,
424 jtf_loc_hierarchies_b d2,
425 bis_territory_hierarchies t,
426 ams_source_codes s
427 WHERE
428 a.source_code = c.source_code
429 AND a.source_code_for_id = c.schedule_id
430 AND a.arc_source_code_for = 'CSCH'
431 AND b.rollup_type not in ('RCAM')
432 AND b.campaign_id = c.campaign_id
433 AND b.city_id = d.location_hierarchy_id
434 AND c.country_id = d2.location_hierarchy_id
435 AND t.parent_territory_type(+) = 'AREA'
436 AND t.child_territory_type(+) = 'COUNTRY'
437 AND t.child_territory_code(+) = d.country_code
438 AND b.status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE', 'ON_HOLD')
439 AND c.status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE', 'ON_HOLD')
440 AND s.source_code_for_id = b.campaign_id
441 AND s.arc_source_code_for = 'CAMP'
442 AND a.active_flag = 'Y' -- do we need this condition ?
443 AND s.active_flag = 'Y' -- do we need this condition ?
444 AND c.start_date_time <= p_end_date
445 ----------------------------------------------+
446 UNION ALL
447 ----------------------------------------------+
448 SELECT
449 a.source_code_id source_code_id,
450 b.program_id*(-1) parent_source_code_id,
451 a.source_code source_code,
452 'EVEH' rollup_type,
453 b.event_header_id object_id,
454 a.arc_source_code_for object_type,
455 b.system_status_code object_status,
456 b.event_purpose_code object_purpose_2,
457 d.country_code object_country,
458 t.parent_territory_code object_region,
459 b.business_unit_id business_unit_id,
460 b.owner_user_id owner_user_id,
461 b.active_from_date start_date,
462 b.active_to_date end_date,
463 0 child_object_id,
464 a.arc_source_code_for child_object_type,
465 '' child_object_status,
466 '' child_object_purpose,
467 '' child_object_country,
468 '' child_object_region,
469 '' child_object_usage,
470 0 activity_id,
471 '' activity_type,
472 case
473 when b.active_to_date < g_start_date then null
474 else greatest(b.active_from_date,g_start_date) end adj_start_date,
475 case
476 when b.active_to_date < g_start_date then null
477 else b.active_to_date end adj_end_date,
478 b.last_update_date obj_last_update_date
479 FROM
480 ams_source_codes a,
481 ams_event_headers_all_b b,
482 jtf_loc_hierarchies_b d,
483 bis_territory_hierarchies t
484 WHERE
485 a.source_code = b.source_code
486 AND a.source_code_for_id = b.event_header_id
487 AND b.country_code = d.location_hierarchy_id
488 AND t.parent_territory_type(+) = 'AREA'
489 AND t.child_territory_type(+) = 'COUNTRY'
490 AND t.child_territory_code(+) = d.country_code
491 AND a.arc_source_code_for = 'EVEH'
492 AND b.system_status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE', 'ON_HOLD')
493 AND b.active_from_date <= p_end_date
494 ----------------------------------------------+
495 UNION ALL
496 ----------------------------------------------+
497 SELECT
498 a.source_code_id source_code_id,
499 s.source_code_id parent_source_code_id,
500 a.source_code source_code,
501 'EVEO' rollup_type,
502 b.event_header_id object_id,
503 'EVEH' object_type,
504 b.system_status_code object_status,
505 b.event_purpose_code object_purpose_2,
506 d.country_code object_country,
507 t.parent_territory_code object_region,
508 b.business_unit_id business_unit_id,
509 c.owner_user_id owner_user_id,
510 c.event_start_date start_date,
511 TO_DATE(DECODE(c.event_end_date,null,
512 DECODE(c.event_start_date,null,null,b.active_to_date),
513 c.event_end_date),'DD/MM/RRRR') end_date,
514 c.event_offer_id child_object_id,
515 a.arc_source_code_for child_object_type,
516 c.system_status_code child_object_status,
517 c.event_purpose_code child_object_purpose_2,
518 d2.country_code child_object_country,
519 d2.area2_code child_object_region,
520 '' child_object_usage,
521 0 activity_id,
522 '' activity_type,
523 case
524 when nvl(c.event_end_date,b.active_to_date) < g_start_date then null
525 else greatest(c.event_start_date,g_start_date) end adj_start_date,
526 case
527 when nvl(c.event_end_date,b.active_to_date) < g_start_date then null
528 else
529 TO_DATE(DECODE(c.event_end_date,null,
530 DECODE(c.event_start_date,null,null,b.active_to_date),
531 c.event_end_date),'DD/MM/RRRR') end adj_end_date,
532 c.last_update_date obj_last_update_date
533 FROM
534 ams_source_codes a,
535 ams_event_headers_all_b b,
536 ams_event_offers_all_b c,
537 jtf_loc_hierarchies_b d,
538 jtf_loc_hierarchies_b d2,
539 bis_territory_hierarchies t ,
540 ams_source_codes s
541 WHERE
542 a.source_code = c.source_code
543 AND a.source_code_for_id = c.event_offer_id
544 AND a.arc_source_code_for= 'EVEO'
545 AND b.event_header_id = c.event_header_id
546 AND b.country_code = d.location_hierarchy_id
547 AND c.country_code = d2.location_hierarchy_id
548 AND t.parent_territory_type(+) = 'AREA'
549 AND t.child_territory_type(+) = 'COUNTRY'
550 AND t.child_territory_code(+) = d.country_code
551 AND b.system_status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE', 'ON_HOLD')
552 AND c.system_status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE', 'ON_HOLD')
553 AND s.source_code_for_id = b.event_header_id
554 AND s.arc_source_code_for = 'EVEH'
555 AND a.active_flag = 'Y' -- do we need this condition ?
556 AND s.active_flag = 'Y' -- do we need this condition ?
557 AND c.event_start_date <= p_end_date
558 ----------------------------------------------+
559 UNION ALL
560 SELECT
561 a.source_code_id source_code_id,
562 c.parent_id*(-1) parent_source_code_id,
563 a.source_code source_code,
564 'EONE' rollup_type,
565 c.event_offer_id object_id,
566 'EONE' object_type,
567 c.system_status_code object_status,
568 c.event_purpose_code object_purpose_2,
569 d.country_code object_country,
570 t.parent_territory_code object_region,
571 c.business_unit_id business_unit_id,
572 c.owner_user_id owner_user_id,
573 c.event_start_date start_date,
574 c.event_end_date end_date,
575 0 child_object_id,
576 '' child_object_type,
577 '' child_object_status,
578 '' child_object_purpose_2,
579 '' child_object_country,
580 '' child_object_region,
581 '' child_object_usage,
582 0 activity_id,
583 '' activity_type,
584 case
585 when c.event_end_date < g_start_date then null
586 else greatest(c.event_start_date,g_start_date) end adj_start_date,
587 case
588 when c.event_end_date < g_start_date then null
589 else c.event_end_date end adj_end_date,
590 c.last_update_date obj_last_update_date
591 FROM ams_source_codes a,
592 ams_event_offers_all_b c,
593 jtf_loc_hierarchies_b d,
594 bis_territory_hierarchies t
595 WHERE a.source_code = c.source_code
596 AND a.source_code_for_id = c.event_offer_id
597 AND a.arc_source_code_for ='EONE'
598 AND nvl(c.parent_type,'N') <> 'CAMP'
599 AND c.country_code = d.location_hierarchy_id
600 AND t.parent_territory_type(+) = 'AREA'
601 AND t.child_territory_type(+) = 'COUNTRY'
602 AND t.child_territory_code(+) = d.country_code
603 AND c.system_status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE', 'ON_HOLD')
604 AND c.event_start_date <= p_end_date
605 ----------------------------------------------
606 UNION ALL
607 ----------------------------------------------
608 SELECT
609 b.campaign_id*(-1) source_code_id,
610 b.parent_campaign_id*(-1) parent_source_code_id,
611 b.source_code source_code,
612 'CAMP' rollup_type,
613 b.campaign_id object_id,
614 b.rollup_type object_type,
615 b.status_code object_status,
616 b.campaign_type object_purpose,
617 d.country_code object_country,
618 t.parent_territory_code object_region,
619 b.business_unit_id business_unit_id,
620 b.owner_user_id owner_user_id,
621 b.actual_exec_start_date start_date,
622 b.actual_exec_end_date end_date,
623 0 child_object_id,
624 b.rollup_type child_object_type,
625 '' child_object_status,
626 '' child_object_purpose,
627 '' child_object_country,
628 '' child_object_region,
629 '' child_object_usage,
630 0 activity_id,
631 '' activity_type,
632 case
633 when b.actual_exec_end_date < g_start_date then null
634 else greatest(b.actual_exec_start_date,g_start_date) end adj_start_date,
635 case
636 when b.actual_exec_end_date < g_start_date then null
637 else b.actual_exec_end_date end adj_end_date,
638 b.last_update_date obj_last_update_date
639 FROM
640 ams_campaigns_all_b b,
641 jtf_loc_hierarchies_b d,
642 bis_territory_hierarchies t
643 WHERE
644 b.actual_exec_start_date <= p_end_date
645 AND b.city_id = d.location_hierarchy_id
646 AND t.parent_territory_type(+) = 'AREA'
647 AND t.child_territory_type(+) = 'COUNTRY'
648 AND t.child_territory_code(+) = d.country_code
649 AND b.rollup_type = 'RCAM'
650 AND b.status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE', 'ON_HOLD')
651 ----------------------------------------------+
652 ) inner
653 ;
654
655 COMMIT;
656
657 --handle categories belong to Reporting category set
658
659 UPDATE BIM_I_SOURCE_CODES code
660 SET code.category_id
661 = (SELECT nvl(prod.category_id,-1) category_id
662 FROM
663 ams_act_products prod
664 WHERE
665 prod.act_product_used_by_id = object_id
666 AND prod.arc_act_product_used_by = object_type
667 AND prod.primary_product_flag = 'Y'
668 )
669 WHERE
670 EXISTS (SELECT 1
671 FROM
672 ams_act_products prod
673 WHERE
674 prod.act_product_used_by_id = object_id
675 AND prod.arc_act_product_used_by = object_type
676 AND prod.primary_product_flag = 'Y'
677 );
678
679 UPDATE BIM_I_SOURCE_CODES code
680 SET code.category_id
681 = (SELECT nvl(prod.category_id,-1) category_id
682 FROM
683 ams_act_products prod
684 WHERE
685 prod.act_product_used_by_id = child_object_id
686 AND prod.arc_act_product_used_by = child_object_type
687 AND prod.primary_product_flag = 'Y'
688 )
689 WHERE
690 EXISTS (SELECT 1
691 FROM
692 ams_act_products prod
693 WHERE
694 prod.act_product_used_by_id = child_object_id
695 AND prod.arc_act_product_used_by = child_object_type
696 AND prod.primary_product_flag = 'Y'
697 );
698
699 COMMIT;
700
701
702
703 bis_collection_utilities.wrapup(p_status => TRUE
704 ,p_count => sql%rowcount
705 ,p_period_from =>p_start_date
706 ,p_period_to => sysdate
707 );
708
709 /***************************************************************/
710
711 --l_table_name := 'BIM_I_SOURCE_CODES';
712 --fnd_message.set_name('BIM','BIM_R_ANALYZE_TABLE');
713 --fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
714 --fnd_file.put_line(fnd_file.log,fnd_message.get);
715 bis_collection_utilities.log('Before Analyze of the table BIM_I_SOURCE_CODES');
716
717 --Analyze the facts table
718 DBMS_STATS.gather_table_stats('BIM','BIM_I_SOURCE_CODES', estimate_percent => 5,
719 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
720
721 --EXECUTE IMMEDIATE ('TRUNCATE TABLE '||l_schema||'.MLOG$_BIM_I_SOURCE_CODES');
722
723 /* Recreating Indexes */
724 -- BIM_UTL_PKG.CREATE_INDEX('BIM_I_SOURCE_CODES');
725
726 /************--Start--********To get Resource ids of Active Employees***************************/
727
728 /*
729
730
731 bis_collection_utilities.log('Start of Initial Load of Resource_ids');
732
733 execute immediate 'truncate table bim.bim_i_resource';
734
735 insert into bim.bim_i_resource
736 (resource_id)
737 select res.resource_id from jtf_Rs_resource_extns res, fnd_user fn
738 where fn.user_id = res.user_id and
739 nvl(fn.end_date,sysdate+1) > sysdate and
740 nvl(res.end_date_active,sysdate+1)>sysdate and
741 category = 'EMPLOYEE'
742 and exists (
743 SELECT 1
744 FROM per_all_people_f per
745 , per_all_assignments_f asg
746 , per_assignment_status_types ast
747 WHERE asg.person_id = per.person_id
748 AND asg.assignment_status_type_id = ast.assignment_status_type_id
749 AND asg.assignment_type = 'E' -- give me only employee assignments
750 AND asg.primary_flag = 'Y' -- give me only primary assignments
751 AND TRUNC(SYSDATE) BETWEEN per.effective_start_date AND per.effective_end_date
752 AND TRUNC(SYSDATE) BETWEEN asg.effective_start_date AND asg.effective_end_date
753 AND ast.assignment_status_type_id = asg.assignment_status_type_id
754
755 AND ast.per_system_status IN ('ACTIVE_ASSIGN','SUSP_ASSIGN')
756 and per.person_id = res.source_id);
757
758
759
760
761 DBMS_STATS.gather_table_stats('BIM','bim_i_resource', estimate_percent => 5,
762 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
763
764 bis_collection_utilities.log('End Initial Load of Resource_ids');
765
766 commit;
767
768 */
769
770
771 /************--End--********To get Resource ids of Active Employees***************************/
772
773
774
775 bis_collection_utilities.log('Successful Completion of Source Codes Population Program');
776
777
778 EXCEPTION
779
780 WHEN FND_API.G_EXC_ERROR THEN
781 x_return_status := FND_API.G_RET_STS_ERROR;
782 -- Standard call to get message count and if count=1, get the message
783 FND_msg_PUB.Count_And_Get (
784 -- p_encoded => FND_API.G_FALSE,
785 p_count => x_msg_count,
786 p_data => x_msg_data
787 );
788
789 ams_utility_pvt.write_conc_log('BIM_I_SRC_CODE_PKG:FIRST_LOAD:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
790
791 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
792
793 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
794
795 -- Standard call to get message count and if count=1, get the message
796 FND_msg_PUB.Count_And_Get (
797 --p_encoded => FND_API.G_FALSE,
798 p_count => x_msg_count,
799 p_data => x_msg_data
800 );
801
802 ams_utility_pvt.write_conc_log('BIM_I_SRC_CODE_PKG:FIRST_LOAD:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
803
804 WHEN OTHERS THEN
805
806 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
807
808 IF FND_msg_PUB.Check_msg_Level (FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
809 THEN
810 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
811 END IF;
812
813 -- Standard call to get message count and if count=1, get the message
814 FND_msg_PUB.Count_And_Get (
815 -- p_encoded => FND_API.G_FALSE,
816 p_count => x_msg_count,
817 p_data => x_msg_data
818 );
819
820 ams_utility_pvt.write_conc_log('BIM_I_SRC_CODE_PKG:FIRST_LOAD:IN OTHERS EXCEPTION '||sqlerrm(sqlcode));
821
822
823 END FIRST_LOAD;
824
825 --------------------------------------------------------------------------------------------------
826 -- This procedure will populates all the data required into facts table for incremental load.
827 --
828 -- PROCEDURE INCREMENTAL_LOAD
829 --------------------------------------------------------------------------------------------------
830
831 PROCEDURE INCREMENTAL_LOAD
832 ( p_start_date IN DATE
833 ,p_end_date IN DATE
834 ,p_api_version_number IN NUMBER
835 ,p_init_msg_list IN VARCHAR2
836 ,x_msg_count OUT NOCOPY NUMBER
837 ,x_msg_data OUT NOCOPY VARCHAR2
838 ,x_return_status OUT NOCOPY VARCHAR2
839 )
840 IS
841 l_user_id NUMBER := FND_GLOBAL.USER_ID();
842 l_api_version_number CONSTANT NUMBER := 1.0;
843 l_api_name CONSTANT VARCHAR2(30) := 'INCREMENTAL_LOAD';
844 l_table_name VARCHAR2(100);
845
846
847 TYPE generic_number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
848
849 TYPE generic_char_table IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
850
851 i NUMBER;
852 l_min_start_date DATE;
853
854 l_org_id number;
855
856 CURSOR get_org_id IS
857 SELECT (TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10)))
858 FROM dual;
859
860
861 BEGIN
862
863 -- Standard call to check for call compatibility.
864 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
865 p_api_version_number,
866 l_api_name,
867 g_pkg_name)
868 THEN
869 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
870 END IF;
871
872 -- Initialize message list if p_init_msg_list is set to TRUE.
873 IF FND_API.to_Boolean( p_init_msg_list )
874 THEN
875 FND_msg_PUB.initialize;
876 END IF;
877
878 -- Initialize API return status to SUCCESS
879 x_return_status := FND_API.G_RET_STS_SUCCESS;
880
881
882 DELETE FROM bim_i_source_codes WHERE trunc(obj_last_update_date) = p_start_date;
883
884
885 l_table_name := 'BIM_I_SOURCE_CODES';
886 bis_collection_utilities.log('Running Incremental Load of Source Codes');
887
888 MERGE INTO bim_i_source_codes facts
889 USING (
890 SELECT
891 sysdate creation_date
892 ,sysdate last_update_date
893 ,-1 created_by
894 ,-1 last_updated_by
895 ,-1 last_update_login,
896 inner.source_code_id source_code_id,
897 inner.parent_source_code_id parent_source_code_id,
898 inner.source_code source_code,
899 inner.rollup_type rollup_type,
900 inner.object_id object_id,
901 inner.object_type object_type,
902 inner.object_status object_status,
903 inner.object_purpose object_purpose,
904 inner.object_country object_country,
905 inner.object_region object_region,
906 inner.business_unit_id business_unit_id,
907 inner.owner_user_id owner_user_id,
908 trunc(inner.start_date) start_date,
909 trunc(inner.end_date) end_date,
910 inner.child_object_id child_object_id,
911 inner.child_object_type child_object_type,
912 inner.child_object_status child_object_status,
913 inner.child_object_purpose child_object_purpose,
914 inner.child_object_country child_object_country,
915 inner.child_object_region child_object_region,
916 inner.child_object_usage child_object_usage,
917 inner.activity_id activity_id,
918 inner.activity_type activity_type,
919 trunc(inner.adj_start_date) adj_start_date,
920 trunc(inner.adj_end_date) adj_end_date,
921 inner.obj_last_update_date obj_last_update_date
922 FROM
923 (
924 SELECT
925 a.source_code_id source_code_id,
926 b.parent_campaign_id*(-1) parent_source_code_id,
927 a.source_code source_code,
928 'CAMP' rollup_type,
929 b.campaign_id object_id,
930 --b.rollup_type object_type,
931 a.arc_source_code_for object_type,
932 b.status_code object_status,
933 b.campaign_type object_purpose,
934 d.country_code object_country,
935 t.parent_territory_code object_region,
936 b.business_unit_id business_unit_id,
937 b.owner_user_id owner_user_id,
938 b.actual_exec_start_date start_date,
939 b.actual_exec_end_date end_date,
940 0 child_object_id,
941 --b.rollup_type child_object_type,
942 a.arc_source_code_for child_object_type,
943 '' child_object_status,
944 '' child_object_purpose,
945 '' child_object_country,
946 '' child_object_region,
947 '' child_object_usage,
948 0 activity_id,
949 '' activity_type,
950 case
951 when b.actual_exec_end_date < g_start_date then null
952 else greatest(b.actual_exec_start_date,g_start_date) end adj_start_date,
953 case
954 when b.actual_exec_end_date < g_start_date then null
955 else b.actual_exec_end_date end adj_end_date,
956 b.last_update_date obj_last_update_date
957 FROM
958 ams_source_codes a,
959 ams_campaigns_all_b b,
960 jtf_loc_hierarchies_b d,
961 bis_territory_hierarchies t
962 WHERE
963 ((b.last_update_date between p_start_date and p_end_date and b.actual_exec_start_date <= p_start_date) or ( b.actual_exec_start_date between p_start_date and p_end_date))
964 AND a.source_code = b.source_code
965 AND a.source_code_for_id = b.campaign_id
966 AND b.city_id = d.location_hierarchy_id
967 AND t.parent_territory_type(+) = 'AREA'
968 AND t.child_territory_type(+) = 'COUNTRY'
969 AND t.child_territory_code(+) = d.country_code
970 AND a.arc_source_code_for = 'CAMP'
971 AND b.rollup_type not in ('RCAM')
972 AND b.status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE', 'ON_HOLD')
973 ----------------------------------------------+
974 UNION ALL
975 ----------------------------------------------+
976 SELECT
977 a.source_code_id source_code_id,
978 s.source_code_id parent_source_code_id,
979 a.source_code source_code,
980 'CSCH' rollup_type,
981 b.campaign_id object_id,
982 --b.rollup_type object_type,
983 'CAMP' object_type,
984 b.status_code object_status,
985 b.campaign_type object_purpose,
986 d.country_code object_country,
987 t.parent_territory_code object_region,
988 b.business_unit_id business_unit_id,
989 c.owner_user_id owner_user_id,
990 c.start_date_time start_date,
991 TO_DATE(DECODE(c.end_date_time,null,
992 DECODE(c.start_date_time,null,null,b.actual_exec_end_date),
993 c.end_date_time),'DD/MM/RRRR') end_date,
994 c.schedule_id child_object_id,
995 a.arc_source_code_for child_object_type,
996 c.status_code child_object_status,
997 c.purpose child_object_purpose,
998 d2.country_code child_object_country,
999 d2.area2_code child_object_region,
1000 c.usage child_object_usage,
1001 c.activity_id activity_id,
1002 c.activity_type_code activity_type,
1003 case
1004 when nvl(c.end_date_time,b.actual_exec_end_date) < g_start_date then null
1005 else greatest(c.start_date_time,g_start_date) end adj_start_date,
1006 case
1007 when nvl(c.end_date_time,b.actual_exec_end_date) < g_start_date then null
1008 else
1009 TO_DATE(DECODE(c.end_date_time,null,
1010 DECODE(c.start_date_time,null,null,b.actual_exec_end_date),
1011 c.end_date_time),'DD/MM/RRRR') end adj_end_date,
1012 c.last_update_date obj_last_update_date
1013 FROM
1014 ams_source_codes a,
1015 ams_campaigns_all_b b,
1016 ams_campaign_schedules_b c,
1017 jtf_loc_hierarchies_b d,
1018 jtf_loc_hierarchies_b d2,
1019 bis_territory_hierarchies t,
1020 ams_source_codes s
1021 WHERE
1022 ((c.last_update_date between p_start_date and p_end_date and c.start_date_time <= p_start_date) or (c.start_date_time between p_start_date and p_end_date))
1023 AND a.source_code = c.source_code
1024 AND a.source_code_for_id = c.schedule_id
1025 AND a.arc_source_code_for = 'CSCH'
1026 AND b.rollup_type not in ('RCAM')
1027 AND b.campaign_id = c.campaign_id
1028 AND b.city_id = d.location_hierarchy_id
1029 AND c.country_id = d2.location_hierarchy_id
1030 AND t.parent_territory_type(+) = 'AREA'
1031 AND t.child_territory_type(+) = 'COUNTRY'
1032 AND t.child_territory_code(+) = d.country_code
1033 AND b.status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE', 'ON_HOLD')
1034 AND c.status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE', 'ON_HOLD')
1035 AND s.source_code_for_id = b.campaign_id
1036 AND s.arc_source_code_for = 'CAMP'
1037 AND a.active_flag = 'Y' -- do we need this condition ?
1038 AND s.active_flag = 'Y' -- do we need this condition ?
1039 ----------------------------------------------+
1040 UNION ALL
1041 ----------------------------------------------+
1042 SELECT
1043 a.source_code_id source_code_id,
1044 b.program_id*(-1) parent_source_code_id,
1045 a.source_code source_code,
1046 'EVEH' rollup_type,
1047 b.event_header_id object_id,
1048 a.arc_source_code_for object_type,
1049 b.system_status_code object_status,
1050 b.event_purpose_code object_purpose_2,
1051 d.country_code object_country,
1052 t.parent_territory_code object_region,
1053 b.business_unit_id business_unit_id,
1054 b.owner_user_id owner_user_id,
1055 b.active_from_date start_date,
1056 b.active_to_date end_date,
1057 0 child_object_id,
1058 a.arc_source_code_for child_object_type,
1059 '' child_object_status,
1060 '' child_object_purpose,
1061 '' child_object_country,
1062 '' child_object_region,
1063 '' child_object_usage,
1064 0 activity_id,
1065 '' activity_type,
1066 case
1067 when b.active_to_date < g_start_date then null
1068 else greatest(b.active_from_date,g_start_date) end adj_start_date,
1069 case
1070 when b.active_to_date < g_start_date then null
1071 else b.active_to_date end adj_end_date,
1072 b.last_update_date obj_last_update_date
1073 FROM
1074 ams_source_codes a,
1075 ams_event_headers_all_b b,
1076 jtf_loc_hierarchies_b d,
1077 bis_territory_hierarchies t
1078 WHERE
1079 ((b.last_update_date between p_start_date and p_end_date and b.active_from_date <= p_start_date) or (b.active_from_date between p_start_date and p_end_date))
1080 AND a.source_code = b.source_code
1081 AND a.source_code_for_id = b.event_header_id
1082 AND b.country_code = d.location_hierarchy_id
1083 AND t.parent_territory_type(+) = 'AREA'
1084 AND t.child_territory_type(+) = 'COUNTRY'
1085 AND t.child_territory_code(+) = d.country_code
1086 AND a.arc_source_code_for = 'EVEH'
1087 AND b.system_status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE', 'ON_HOLD')
1088 ----------------------------------------------+
1089 UNION ALL
1090 ----------------------------------------------+
1091 SELECT
1092 a.source_code_id source_code_id,
1093 s.source_code_id parent_source_code_id,
1094 a.source_code source_code,
1095 'EVEO' rollup_type,
1096 b.event_header_id object_id,
1097 'EVEH' object_type,
1098 b.system_status_code object_status,
1099 b.event_purpose_code object_purpose_2,
1100 d.country_code object_country,
1101 t.parent_territory_code object_region,
1102 b.business_unit_id business_unit_id,
1103 c.owner_user_id owner_user_id,
1104 c.event_start_date start_date,
1105 TO_DATE(DECODE(c.event_end_date,null,
1106 DECODE(c.event_start_date,null,null,b.active_to_date),
1107 c.event_end_date),'DD/MM/RRRR') end_date,
1108 c.event_offer_id child_object_id,
1109 a.arc_source_code_for child_object_type,
1110 c.system_status_code child_object_status,
1111 c.event_purpose_code child_object_purpose_2,
1112 d2.country_code child_object_country,
1113 d2.area2_code child_object_region,
1114 '' child_object_usage,
1115 0 activity_id,
1116 '' activity_type,
1117 case
1118 when nvl(c.event_end_date,b.active_to_date) < g_start_date then null
1119 else greatest(c.event_start_date,g_start_date) end adj_start_date,
1120 case
1121 when nvl(c.event_end_date,b.active_to_date) < g_start_date then null
1122 else
1123 TO_DATE(DECODE(c.event_end_date,null,
1124 DECODE(c.event_start_date,null,null,b.active_to_date),
1125 c.event_end_date),'DD/MM/RRRR') end adj_end_date,
1126 c.last_update_date obj_last_update_date
1127 FROM
1128 ams_source_codes a,
1129 ams_event_headers_all_b b,
1130 ams_event_offers_all_b c,
1131 jtf_loc_hierarchies_b d,
1132 jtf_loc_hierarchies_b d2,
1133 bis_territory_hierarchies t,
1134 ams_source_codes s
1135 WHERE
1136 ((c.last_update_date between p_start_date and p_end_date and c.event_start_date <= p_start_date) OR (c.event_start_date between p_start_date and p_end_date))
1137 AND a.source_code = c.source_code
1138 AND a.source_code_for_id = c.event_offer_id
1139 AND a.arc_source_code_for= 'EVEO'
1140 AND b.event_header_id = c.event_header_id
1141 AND b.country_code = d.location_hierarchy_id
1142 AND c.country_code = d2.location_hierarchy_id
1143 AND t.parent_territory_type(+) = 'AREA'
1144 AND t.child_territory_type(+) = 'COUNTRY'
1145 AND t.child_territory_code(+) = d.country_code
1146 AND b.system_status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE', 'ON_HOLD')
1147 AND c.system_status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE', 'ON_HOLD')
1148 AND s.source_code_for_id = b.event_header_id
1149 AND s.arc_source_code_for = 'EVEH'
1150 AND a.active_flag = 'Y' -- do we need this condition ?
1151 AND s.active_flag = 'Y' -- do we need this condition ?
1152 ----------------------------------------------+
1153 UNION ALL
1154 SELECT
1155 a.source_code_id source_code_id,
1156 c.parent_id*(-1) parent_source_code_id,
1157 a.source_code source_code,
1158 'EONE' rollup_type,
1159 c.event_offer_id object_id,
1160 'EONE' object_type,
1161 c.system_status_code object_status,
1162 c.event_purpose_code object_purpose_2,
1163 d.country_code object_country,
1164 t.parent_territory_code object_region,
1165 c.business_unit_id business_unit_id,
1166 c.owner_user_id owner_user_id,
1167 c.event_start_date start_date,
1168 c.event_end_date end_date,
1169 0 child_object_id,
1170 '' child_object_type,
1171 '' child_object_status,
1172 '' child_object_purpose_2,
1173 '' child_object_country,
1174 '' child_object_region,
1175 '' child_object_usage,
1176 0 activity_id,
1177 '' activity_type,
1178 case
1179 when c.event_end_date < g_start_date then null
1180 else greatest(c.event_start_date,g_start_date) end adj_start_date,
1181 case
1182 when c.event_end_date < g_start_date then null
1183 else c.event_end_date end adj_end_date,
1184 c.last_update_date obj_last_update_date
1185 FROM ams_source_codes a,
1186 ams_event_offers_all_b c,
1187 jtf_loc_hierarchies_b d,
1188 bis_territory_hierarchies t
1189 WHERE
1190 ((c.last_update_date between p_start_date and p_end_date and c.event_start_date <= p_start_date) or (c.event_start_date between p_start_date and p_end_date))
1191 AND a.source_code = c.source_code
1192 AND a.source_code_for_id = c.event_offer_id
1193 AND a.arc_source_code_for ='EONE'
1194 AND nvl(c.parent_type,'N') <> 'CAMP'
1195 AND c.country_code = d.location_hierarchy_id
1196 AND t.parent_territory_type(+) = 'AREA'
1197 AND t.child_territory_type(+) = 'COUNTRY'
1198 AND t.child_territory_code(+) = d.country_code
1199 AND c.system_status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE', 'ON_HOLD')
1200 ----------------------------------------------
1201 UNION ALL
1202 ----------------------------------------------
1203 SELECT
1204 b.campaign_id*(-1) source_code_id,
1205 b.parent_campaign_id*(-1) parent_source_code_id,
1206 b.source_code source_code,
1207 'CAMP' rollup_type,
1208 b.campaign_id object_id,
1209 b.rollup_type object_type,
1210 b.status_code object_status,
1211 b.campaign_type object_purpose,
1212 d.country_code object_country,
1213 t.parent_territory_code object_region,
1214 b.business_unit_id business_unit_id,
1215 b.owner_user_id owner_user_id,
1216 b.actual_exec_start_date start_date,
1217 b.actual_exec_end_date end_date,
1218 0 child_object_id,
1219 b.rollup_type child_object_type,
1220 '' child_object_status,
1221 '' child_object_purpose,
1222 '' child_object_country,
1223 '' child_object_region,
1224 '' child_object_usage,
1225 0 activity_id,
1226 '' activity_type,
1227 case
1228 when b.actual_exec_end_date < g_start_date then null
1229 else greatest(b.actual_exec_start_date,g_start_date) end adj_start_date,
1230 case
1231 when b.actual_exec_end_date < g_start_date then null
1232 else b.actual_exec_end_date end adj_end_date,
1233 b.last_update_date obj_last_update_date
1234 FROM
1235 ams_campaigns_all_b b,
1236 jtf_loc_hierarchies_b d,
1237 bis_territory_hierarchies t
1238 WHERE
1239 ((b.last_update_date between p_start_date and p_end_date and b.actual_exec_start_date <= p_start_date) or (b.actual_exec_start_date between p_start_date and p_end_date))
1240 AND b.city_id = d.location_hierarchy_id
1241 AND t.parent_territory_type(+) = 'AREA'
1242 AND t.child_territory_type(+) = 'COUNTRY'
1243 AND t.child_territory_code(+) = d.country_code
1244 AND b.rollup_type = 'RCAM'
1245 AND b.status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE', 'ON_HOLD')
1246 ----------------------------------------------+
1247 ) inner
1248 ) changes
1249 ON (
1250 facts.source_code_id = changes.source_code_id
1251 )
1252 WHEN MATCHED THEN UPDATE SET
1253 facts.parent_source_code_id = changes.parent_source_code_id
1254 ,facts.rollup_type = changes.rollup_type
1255 ,facts.object_id = changes.object_id
1256 ,facts.object_type = changes.object_type
1257 ,facts.object_status = changes.object_status
1258 ,facts.object_purpose = changes.object_purpose
1259 ,facts.object_country = changes.object_country
1260 ,facts.object_region = changes.object_region
1261 ,facts.business_unit_id = changes.business_unit_id
1262 ,facts.owner_user_id = changes.owner_user_id
1263 ,facts.start_date = changes.start_date
1264 ,facts.end_date = changes.end_date
1265 ,facts.child_object_id = changes.child_object_id
1266 ,facts.child_object_type = changes.child_object_type
1267 ,facts.child_object_status = changes.child_object_status
1268 ,facts.child_object_purpose = changes.child_object_purpose
1269 ,facts.child_object_country = changes.child_object_country
1270 ,facts.child_object_region = changes.child_object_region
1271 ,facts.child_object_usage = changes.child_object_usage
1272 ,facts.activity_id = changes.activity_id
1273 ,facts.activity_type = changes.activity_type
1274 --,facts.adj_start_date = greatest(changes.start_date,g_start_date)
1275 --,facts.adj_end_date = greatest(changes.end_date,g_start_date)
1276 ,facts.adj_start_date = changes.adj_start_date
1277 ,facts.adj_end_date = changes.adj_end_date
1278 ,facts.obj_last_update_date = changes.obj_last_update_date
1279 ,facts.last_update_date = changes.last_update_date
1280 WHEN NOT MATCHED THEN INSERT
1281 (
1282 facts.creation_date
1283 ,facts.last_update_date
1284 ,facts.created_by
1285 ,facts.last_updated_by
1286 ,facts.last_update_login
1287 ,facts.source_code_id
1288 ,facts.parent_source_code_id
1289 ,facts.source_code
1290 ,facts.rollup_type
1291 ,facts.object_id
1292 ,facts.object_type
1293 ,facts.object_status
1294 ,facts.object_purpose
1295 ,facts.object_country
1296 ,facts.object_region
1297 ,facts.business_unit_id
1298 ,facts.owner_user_id
1299 ,facts.start_date
1300 ,facts.end_date
1301 ,facts.child_object_id
1302 ,facts.child_object_type
1303 ,facts.child_object_status
1304 ,facts.child_object_purpose
1305 ,facts.child_object_country
1306 ,facts.child_object_region
1307 ,facts.child_object_usage
1308 ,facts.activity_id
1309 ,facts.activity_type
1310 ,facts.adj_start_date
1311 ,facts.adj_end_date
1312 ,facts.obj_last_update_date
1313 )
1314 VALUES
1315 (
1316 changes.creation_date
1317 ,changes.last_update_date
1318 ,changes.created_by
1319 ,changes.last_updated_by
1320 ,changes.last_update_login
1321 ,changes.source_code_id
1322 ,changes.parent_source_code_id
1323 ,changes.source_code
1324 ,changes.rollup_type
1325 ,changes.object_id
1326 ,changes.object_type
1327 ,changes.object_status
1328 ,changes.object_purpose
1329 ,changes.object_country
1330 ,changes.object_region
1331 ,changes.business_unit_id
1332 ,changes.owner_user_id
1333 ,changes.start_date
1334 ,changes.end_date
1335 ,changes.child_object_id
1336 ,changes.child_object_type
1337 ,changes.child_object_status
1338 ,changes.child_object_purpose
1339 ,changes.child_object_country
1340 ,changes.child_object_region
1341 ,changes.child_object_usage
1342 ,changes.activity_id
1343 ,changes.activity_type
1344 --,greatest(changes.start_date,g_start_date)
1345 -- ,greatest(changes.end_date,g_start_date)
1346 ,changes.adj_start_date
1347 ,changes.adj_end_date
1348 ,changes.obj_last_update_date
1349 );
1350
1351 COMMIT;
1352
1353 --handle categories belong to Reporting category set
1354
1355 UPDATE BIM_I_SOURCE_CODES code
1356 SET code.category_id
1357 = (SELECT nvl(prod.category_id,-1) category_id
1358 FROM
1359 ams_act_products prod
1360 WHERE
1361 prod.act_product_used_by_id = object_id
1362 AND prod.arc_act_product_used_by = object_type
1363 AND prod.primary_product_flag = 'Y'
1364 AND (prod.last_update_date between p_start_date and p_end_date
1365 OR code.obj_last_update_date between p_start_date and p_end_date)
1366 )
1367 WHERE ((code.child_object_id = 0)
1368 OR
1369 (NOT EXISTS (
1370 SELECT 1
1371 FROM ams_act_products prod
1372 WHERE prod.act_product_used_by_id = code.child_object_id
1373 AND prod.arc_act_product_used_by = code.child_object_type
1374 AND prod.primary_product_flag = 'Y'
1375 ))
1376 )
1377 AND
1378 EXISTS (SELECT 1
1379 FROM
1380 ams_act_products prod
1381 WHERE
1382 prod.act_product_used_by_id = object_id
1383 AND prod.arc_act_product_used_by = object_type
1384 AND prod.primary_product_flag = 'Y'
1385 AND (prod.last_update_date between p_start_date and p_end_date
1386 OR code.obj_last_update_date between p_start_date and p_end_date)
1387 );
1388
1389 UPDATE BIM_I_SOURCE_CODES code
1390 SET code.category_id
1391 = (SELECT nvl(prod.category_id,-1) category_id
1392 FROM
1393 ams_act_products prod
1394 WHERE
1395 prod.act_product_used_by_id = child_object_id
1396 AND prod.arc_act_product_used_by = child_object_type
1397 AND prod.primary_product_flag = 'Y'
1398 AND (prod.last_update_date between p_start_date and p_end_date
1399 OR code.obj_last_update_date between p_start_date and p_end_date)
1400 )
1401 WHERE
1402 EXISTS (SELECT 1
1403 FROM
1404 ams_act_products prod
1405 WHERE
1406 prod.act_product_used_by_id = child_object_id
1407 AND prod.arc_act_product_used_by = child_object_type
1408 AND prod.primary_product_flag = 'Y'
1409 AND (prod.last_update_date between p_start_date and p_end_date
1410 OR code.obj_last_update_date between p_start_date and p_end_date)
1411 );
1412
1413 UPDATE BIM_I_SOURCE_CODES code
1414 SET code.category_id = -1
1415 WHERE NOT EXISTS (select 1
1416 FROM
1417 ams_act_products prod
1418 WHERE
1419 act_product_used_by_id = code.object_id
1420 AND prod.arc_act_product_used_by in ('CAMP','EVEH','EONE')
1421 )
1422 AND ( (child_object_id = 0)
1423 OR
1424 (NOT EXISTS (select 1
1425 FROM
1426 ams_act_products prod
1427 WHERE
1428 act_product_used_by_id = code.child_object_id
1429 AND prod.arc_act_product_used_by in ('CSCH', 'EVEO')
1430 ))
1431 )
1432 AND code.category_id <> -1;
1433
1434
1435 COMMIT;
1436
1437
1438
1439 bis_collection_utilities.wrapup(p_status => TRUE
1440 ,p_count => sql%rowcount
1441 ,p_period_from => p_start_date
1442 ,p_period_to => sysdate
1443 );
1444
1445 /***************************************************************/
1446
1447
1448 bis_collection_utilities.log('Before Analyze of the table BIM_I_SOURCE_CODES');
1449
1450 --Analyze the facts table
1451 DBMS_STATS.gather_table_stats('BIM','BIM_I_SOURCE_CODES', estimate_percent => 5,
1452 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
1453
1454 /************--Start--********To get Resource ids of Active Employees***************************/
1455
1456 /*
1457 bis_collection_utilities.log('Start of INCREMENTAL Load of Resource_ids');
1458
1459 execute immediate 'truncate table bim_i_resource_stg';
1460
1461 insert into bim.bim_i_resource_stg
1462 (resource_id)
1463 select res.resource_id from jtf_Rs_resource_extns res, fnd_user fn
1464 where fn.user_id = res.user_id and
1465 nvl(fn.end_date,sysdate+1) > sysdate and
1466 nvl(res.end_date_active,sysdate+1)>sysdate and
1467 category = 'EMPLOYEE'
1468 and exists (
1469 SELECT 1
1470 FROM per_all_people_f per
1471 , per_all_assignments_f asg
1472 , per_assignment_status_types ast
1473 WHERE asg.person_id = per.person_id
1474 AND asg.assignment_status_type_id = ast.assignment_status_type_id
1475 AND asg.assignment_type = 'E' -- give me only employee assignments
1476 AND asg.primary_flag = 'Y' -- give me only primary assignments
1477 AND TRUNC(SYSDATE) BETWEEN per.effective_start_date AND per.effective_end_date
1478 AND TRUNC(SYSDATE) BETWEEN asg.effective_start_date AND asg.effective_end_date
1479 AND ast.assignment_status_type_id = asg.assignment_status_type_id
1480
1481 AND ast.per_system_status IN ('ACTIVE_ASSIGN','SUSP_ASSIGN')
1482 and per.person_id = res.source_id);
1483
1484
1485 insert into bim.bim_i_resource
1486 (resource_id)
1487 select resource_id from bim.bim_i_resource_stg a
1488 where not exists
1489 (select 'Y' from bim.bim_i_resource b
1490 where a.resource_id=b.resource_id);
1491
1492
1493
1494 delete from bim.bim_i_resource
1495 where resource_id in (
1496 select resource_id from bim.bim_i_resource a
1497 where not exists
1498 (select 'Y' from bim.bim_i_resource_stg b
1499 where a.resource_id=b.resource_id));
1500
1501 commit;
1502
1503
1504
1505
1506 DBMS_STATS.gather_table_stats('BIM','bim_i_resource', estimate_percent => 5,
1507 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
1508
1509 bis_collection_utilities.log('End Initial Load of Resource_ids');
1510
1511 commit;
1512
1513 */
1514
1515 /************--End--********To get Resource ids of Active Employees***************************/
1516
1517 bis_collection_utilities.log('Successful Completion of Source Codes Population Program');
1518
1519
1520 EXCEPTION
1521
1522 WHEN FND_API.G_EXC_ERROR THEN
1523 x_return_status := FND_API.G_RET_STS_ERROR;
1524 -- Standard call to get message count and if count=1, get the message
1525 FND_msg_PUB.Count_And_Get (
1526 -- p_encoded => FND_API.G_FALSE,
1527 p_count => x_msg_count,
1528 p_data => x_msg_data
1529 );
1530
1531 ams_utility_pvt.write_conc_log('BIM_I_SRC_CODE_PKG:INCREMENTAL_LOAD:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
1532
1533 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1534
1535 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1536
1537 -- Standard call to get message count and if count=1, get the message
1538 FND_msg_PUB.Count_And_Get (
1539 --p_encoded => FND_API.G_FALSE,
1540 p_count => x_msg_count,
1541 p_data => x_msg_data
1542 );
1543
1544 ams_utility_pvt.write_conc_log('BIM_I_SRC_CODE_PKG:INCREMENTAL_LOAD:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
1545
1546 WHEN OTHERS THEN
1547
1548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1549
1550 IF FND_msg_PUB.Check_msg_Level (FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
1551 THEN
1552 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
1553 END IF;
1554
1555 -- Standard call to get message count and if count=1, get the message
1556 FND_msg_PUB.Count_And_Get (
1557 -- p_encoded => FND_API.G_FALSE,
1558 p_count => x_msg_count,
1559 p_data => x_msg_data
1560 );
1561
1562 ams_utility_pvt.write_conc_log('BIM_I_SRC_CODE_PKG:INCREMENTAL_LOAD:IN OTHERS EXCEPTION '||sqlerrm(sqlcode));
1563
1564
1565 END INCREMENTAL_LOAD;
1566
1567
1568 END BIM_I_SRC_CODE_PKG;
1569