[Home] [Help]
PACKAGE BODY: APPS.BIM_PROGRAMS_DENORM_PKG
Source
1 PACKAGE BODY bim_programs_denorm_pkg AS
2 /*$Header: bimprgdb.pls 120.7 2005/12/21 02:37:42 sbassi ship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(20) := 'BIM_PROGRAMS_DENORM';
5 g_file_name CONSTANT VARCHAR2(20) := 'bimprgdb2.pls';
6
7
8 PROCEDURE COMMON_UTILITIES
9 ( l_global_start_date OUT NOCOPY DATE
10 ,l_period_from OUT NOCOPY DATE
11 ,l_period_to OUT NOCOPY DATE
12 ,l_temp_start_date OUT NOCOPY DATE
13 ,l_start_date OUT NOCOPY DATE
14 ,l_end_date OUT NOCOPY DATE
15 ) IS
16 l_global_date CONSTANT DATE := bis_common_parameters.get_global_start_date;
17
18 BEGIN
19 l_global_start_date := l_global_date;
20
21 BEGIN
22 /* Set up the Object */
23 IF NOT bis_collection_utilities.setup('BIM_SOURCE_DENORM') THEN
24 bis_collection_utilities.log('Object Not Setup Properly ');
25 END IF;
26
27 bis_collection_utilities.get_last_refresh_dates('BIM_SOURCE_DENORM'
28 ,l_start_date,l_end_date,l_period_from,l_period_to);
29
30 EXCEPTION
31 WHEN NO_DATA_FOUND THEN
32 l_end_date := NULL;
33 bis_collection_utilities.log('First time running the concurrent program ');
34 WHEN OTHERS THEN
35 bis_collection_utilities.log('program '|| sqlerrm(sqlcode));
36 END;
37
38 /* End of the code for checking the data will be loaded for the first time or not. */
39
40 IF l_period_to IS NULL THEN
41 l_temp_start_date := sysdate-5000;
42 ELSE
43 l_temp_start_date := l_period_to;
44 END IF;
45
46 END COMMON_UTILITIES;
47
48
49
50 PROCEDURE POPULATE
51 (ERRBUF OUT NOCOPY VARCHAR2
52 ,RETCODE OUT NOCOPY NUMBER
53 ,p_api_version_number IN NUMBER
54 ,p_proc_num IN NUMBER
55 ,p_load_type IN VARCHAR2
56 ) IS
57
58 l_api_version_number CONSTANT NUMBER := 1.0;
59 l_api_name CONSTANT VARCHAR2(30) := 'POPULATE';
60 x_msg_count NUMBER;
61 x_msg_data VARCHAR2(240);
62 x_return_status VARCHAR2(1) ;
63 l_init_msg_list VARCHAR2(10);
64 l_date DATE;
65 l_start_date DATE;
66 l_end_date DATE;
67 l_period_from DATE;
68 l_period_to DATE;
69 l_temp_start_date DATE;
70
71 BEGIN
72
73 l_date:= bis_common_parameters.get_global_start_date;
74 -- Standard call to check for call compatibility.
75 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
76 p_api_version_number,
77 l_api_name,
78 g_pkg_name)
79 THEN
80 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
81 END IF;
82
83 POPULATE_SOURCE_DENORM
84 (p_api_version_number => 1.0
85 ,p_init_msg_list => FND_API.G_FALSE
86 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
87 ,p_commit => FND_API.G_FALSE
88 ,x_msg_Count => x_msg_count
89 ,x_msg_Data => x_msg_data
90 ,x_return_status => x_return_status
91 ,p_proc_num => p_proc_num
92 ,p_load_type => p_load_type
93 );
94
95 IF x_return_status = FND_API.g_ret_sts_error
96 THEN
97 RAISE FND_API.g_exc_error;
98 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
99 RAISE FND_API.g_exc_unexpected_error;
100 END IF;
101
102 IF x_return_status = FND_API.g_ret_sts_error
103 THEN
104 RAISE FND_API.g_exc_error;
105 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
106 RAISE FND_API.g_exc_unexpected_error;
107 END IF;
108
109 LOAD_ADMIN_RECORDS
110 (p_api_version_number => 1.0
111 ,p_init_msg_list => FND_API.G_FALSE
112 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
113 ,p_commit => FND_API.G_FALSE
114 ,x_msg_Count => x_msg_count
115 ,x_msg_Data => x_msg_data
116 ,x_return_status => x_return_status
117 );
118
119 IF x_return_status = FND_API.g_ret_sts_error
120 THEN
121 RAISE FND_API.g_exc_error;
122 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
123 RAISE FND_API.g_exc_unexpected_error;
124 END IF;
125
126 LOAD_TOP_LEVEL_OBJECTS
127 (p_api_version_number => 1.0
128 ,p_init_msg_list => FND_API.G_FALSE
129 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
130 ,p_commit => FND_API.G_FALSE
131 ,x_msg_Count => x_msg_count
132 ,x_msg_Data => x_msg_data
133 ,x_return_status => x_return_status
134 );
135
136 IF x_return_status = FND_API.g_ret_sts_error
137 THEN
138 RAISE FND_API.g_exc_error;
139 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
140 RAISE FND_API.g_exc_unexpected_error;
141 END IF;
142
143 EXCEPTION
144
145 WHEN FND_API.G_EXC_ERROR THEN
146 x_return_status := FND_API.g_ret_sts_error ;
147 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
148 p_count => x_msg_count,
149 p_data => x_msg_data);
150 ERRBUF := x_msg_data;
151 RETCODE := 2;
152
153 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
154 x_return_status := FND_API.g_ret_sts_unexp_error ;
155 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
156 p_count => x_msg_count,
157 p_data => x_msg_data);
158 ERRBUF := x_msg_data;
159 RETCODE := 2;
160
161 WHEN OTHERS THEN
162 x_return_status := FND_API.g_ret_sts_unexp_error ;
163 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
164 p_count => x_msg_count,
165 p_data => x_msg_data);
166 ERRBUF := sqlerrm(sqlcode);
167 RETCODE := sqlcode;
168
169 END POPULATE;
170
171
172 PROCEDURE POPULATE_SOURCE_DENORM
173 (p_api_version_number IN NUMBER
174 ,p_init_msg_list IN VARCHAR2
175 ,p_validation_level IN NUMBER
176 ,p_commit IN VARCHAR2
177 ,x_msg_Count OUT NOCOPY NUMBER
178 ,x_msg_Data OUT NOCOPY VARCHAR2
179 ,x_return_status OUT NOCOPY VARCHAR2
180 ,p_proc_num IN NUMBER
181 ,p_load_type IN VARCHAR2
182 ) IS
183
184 --l_date DATE := bis_common_parameters.get_global_start_date;
185 l_date DATE;
186 l_temp_start_date DATE;
187 l_temp_end_date DATE;
188 l_api_version_number CONSTANT NUMBER := 1.0;
189 l_api_name CONSTANT VARCHAR2(30) := 'POPULATE_DENORM';
190 l_init_msg_list VARCHAR2(10);
191 l_start_date DATE;
192 l_end_date DATE;
193 l_period_from DATE;
194 l_period_to DATE;
195
196 l_status VARCHAR2(5);
197 l_industry VARCHAR2(5);
198 l_schema VARCHAR2(30);
199 l_return BOOLEAN;
200
201
202 BEGIN
203
204 l_init_msg_list:= FND_API.G_FALSE;
205
206 IF p_load_type = 'F' THEN
207
208 --it is a call for First/Inital load, then truncate the denorm table first
209 bis_collection_utilities.log('Truncating the Source Denorm Table ');
210
211 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
212
213 Execute Immediate 'Truncate Table '||l_schema||'.bim_i_source_denorm';
214
215 BIS_COLLECTION_UTILITIES.deleteLogForObject('BIM_SOURCE_DENORM');
216
217 ELSE
218 /* This piece of code is for the objects that had an update somewhere in their hierarachy chain. */
219
220 DELETE bim_i_source_denorm
221 WHERE source_code_id IN
222 (SELECT source_code_id
223 FROM bim_i_source_codes a
224 WHERE obj_last_update_date > l_temp_start_date
225 );
226
227 /* This piece of code is for the campaigns that have changed from one program to another. */
228 /*DELETE bim_i_source_denorm
229 WHERE source_code_id IN
230 (
231 SELECT source_code_id
232 FROM bim_i_source_denorm
233 WHERE parent_source_code_id IN
234 (
235 SELECT b.source_code_id
236 FROM ams_campaigns_all_b a,
237 bim_i_source_codes b
238 WHERE a.rollup_type in ('RCAM')
239 and b.source_code_id = (-1)*b.object_id
240 AND obj_last_update_date > l_temp_start_date
241 )
242
243 );*/
244 --Modified the query as per performance bug 4901135
245 DELETE bim_i_source_denorm
246 WHERE source_code_id IN
247 (
248 SELECT source_code_id
249 FROM bim_i_source_denorm
250 WHERE parent_source_code_id IN
251 (
252 SELECT b.source_code_id
253 FROM bim_i_source_codes b
254 WHERE b.source_code_id = (-1)*b.object_id
255 AND obj_last_update_date > l_temp_start_date
256 )
257
258 );
259
260 END IF;
261
262 COMMON_UTILITIES
263 ( l_date
264 ,l_period_from
265 ,l_period_to
266 ,l_temp_start_date
267 ,l_start_date
268 ,l_end_date
269 );
270
271 IF l_period_to IS NOT NULL THEN
272
273 BIS_COLLECTION_UTILITIES.log('Updating leaf node flags for incremental load ');
274 --add update logic here
275 UPDATE bim_i_source_denorm SET leaf_node_flag = 'N'
276 WHERE source_code_id
277 IN
278 (
279 SELECT parent_source_code_id
280 FROM bim_i_source_codes a
281 WHERE rollup_type in ('CSCH', 'EVE0')
282 AND NOT EXISTS (
283 SELECT 1
284 FROM bim_i_source_denorm b
285 WHERE b.source_code_id = a.source_code_id
286 )
287 AND object_level = 1
288 );
289
290
291 /*************FOR CAMPAIGNS AND EVENTS OBJECTS ******************************/
292
293 INSERT INTO bim_i_source_denorm
294 (source_code_id
295 ,parent_source_code_id
296 ,immediate_parent_flag
297 ,immediate_parent_id
298 ,prior_id
299 ,object_level
300 ,rollup_type
301 ,parent_source_code_type
302 ,top_node_flag
303 ,leaf_node_flag
304 ,creation_date
305 ,last_update_date
306 ,created_by
307 ,last_updated_by
308 ,last_update_login
309 )
310
311 SELECT
312 x.source_code_id
313 ,x.parent_source_code_id
314 ,x.immediate_parent_flag
315 ,x.immediate_parent_id
316 ,s.parent_source_code_id
317 ,x.object_level
318 ,x.object_type
319 ,x.parent_source_code_type
320 ,decode(s.parent_source_code_id, NULL, 'Y', 'N')
321 ,(CASE
322 WHEN (x.leaf_node_flag = 'Y' AND x.object_level = 1)
323 THEN 'Y'
324 ELSE 'N'
325 END)
326 --,decode(x.object_level,1,'Y','N')
327 ,sysdate
328 ,sysdate
329 ,-1
330 ,-1
331 ,-1
332 FROM
333 (
334 SELECT
335 source_code_id source_code_id
336 ,TO_NUMBER(NVL(SUBSTR(SYS_CONNECT_BY_PATH(source_code_id,'/'),2,
337 INSTR(SYS_CONNECT_BY_PATH(source_code_id,'/'),'/',2) -2),source_code_id)) AS parent_source_code_id
338 ,decode(parent_source_code_id,NULL,'Y',decode(level,2,'Y','N')) immediate_parent_flag
339 ,parent_source_code_id immediate_parent_id
340 ,LEVEL object_level
341 ,rollup_type object_type
342 ,NVL(PRIOR(object_type),object_type) parent_source_code_type
343 ,decode(parent_source_code_id, NULL, 'Y', 'N') top_node_flag
344 ,(CASE
345 WHEN rollup_type in ('CSCH','EVEO','EONE') THEN 'Y'
346 WHEN source_code_id < 0 THEN 'N'
347 WHEN (select 'Y' from bim_i_source_codes b
348 where a.object_id = b.object_id
349 and a.object_type = b.object_type
350 and b.object_type in ('CAMP','EVEH')
351 and b.child_object_id > 0
352 and rownum = 1) is NULL THEN 'Y'
353 ELSE 'N'
354 END
355 ) leaf_node_flag
356
357 FROM bim_i_source_codes a
358 WHERE
359 NOT EXISTS
360 (SELECT 1
361 FROM BIM_I_SOURCE_DENORM b
362 WHERE b.source_code_id = a.source_code_id
363 -- AND nvl(b.parent_object_id,1) = nvl(a.parent_campaign_id,1)
364 )
365 CONNECT BY PRIOR source_code_id = parent_source_code_id ) x,
366 BIM_I_SOURCE_CODES s
367 WHERE s.source_code_id = x.parent_source_code_id;
368
369 null;
370
371 ELSE
372 -- for initial load
373 -- No not exist condition for the for intial load
374 --additional union all for unassigned values
375 bis_collection_utilities.log('Initial Load of Source Denorm Concurrent Program');
376
377 /*************FOR CAMPAIGNS AND EVENTS OBJECTS ******************************/
378
379 INSERT INTO bim_i_source_denorm
380 (source_code_id
381 ,parent_source_code_id
382 ,immediate_parent_flag
383 ,immediate_parent_id
384 ,prior_id
385 ,object_level
386 ,rollup_type
387 ,parent_source_code_type
388 ,top_node_flag
389 ,leaf_node_flag
390 ,creation_date
391 ,last_update_date
392 ,created_by
393 ,last_updated_by
394 ,last_update_login
395 )
396
397 SELECT
398 x.source_code_id source_code_id
399 ,x.parent_source_code_id parent_source_code_id
400 ,x.immediate_parent_flag immediate_parent_flag
401 ,x.immediate_parent_id immediate_parent_id
402 ,s.parent_source_code_id prior_id
403 ,x.object_level object_level
404 ,x.object_type object_type
405 ,x.parent_source_code_type
406 ,decode(s.parent_source_code_id, NULL, 'Y', 'N') top_node_flag
407 ,(CASE
408 WHEN (x.leaf_node_flag = 'Y' AND x.object_level = 1)
409 THEN 'Y'
410 ELSE 'N'
411 END) leaf_node_flag
412 --,decode(x.object_level,1,'Y','N') leaf_node_flag
413 ,sysdate
414 ,sysdate
415 ,-1
416 ,-1
417 ,-1
418 FROM
419 (
420 SELECT
421 source_code_id source_code_id
422 ,TO_NUMBER(NVL(SUBSTR(SYS_CONNECT_BY_PATH(source_code_id,'/'),2,
423 INSTR(SYS_CONNECT_BY_PATH(source_code_id,'/'),'/',2) -2),source_code_id)) AS parent_source_code_id
427 ,rollup_type object_type
424 ,decode(parent_source_code_id,NULL,'Y',decode(level,2,'Y','N')) immediate_parent_flag
425 ,parent_source_code_id immediate_parent_id
426 ,LEVEL object_level
428 ,NVL(PRIOR(object_type),object_type) parent_source_code_type
429 ,decode(parent_source_code_id, NULL, 'Y', 'N') top_node_flag
430 ,(CASE
431 WHEN rollup_type in ('CSCH','EVEO','EONE') THEN 'Y'
432 WHEN source_code_id < 0 THEN 'N'
433 WHEN (select 'Y' from bim_i_source_codes b
434 where a.object_id = b.object_id
435 and a.object_type = b.object_type
436 and b.object_type in ('CAMP','EVEH')
437 and b.child_object_id > 0
438 and rownum = 1) is NULL THEN 'Y'
439 ELSE 'N'
440 END
441 ) leaf_node_flag
442
443 FROM bim_i_source_codes a
444 CONNECT BY PRIOR source_code_id = parent_source_code_id ) x,
445 BIM_I_SOURCE_CODES s
446 WHERE s.source_code_id = x.parent_source_code_id
447 UNION ALL
448 SELECT
449 -1 source_code_id
450 ,-1 parent_source_code_id
451 ,'Y' immediate_parent_flag
452 ,null immediate_parent_id
453 ,null prior_id
454 ,1 object_level
455 ,null object_type
456 ,null parent_source_code_type
457 ,'Y' top_node_flag
458 ,'Y' leaf_node_flag
459 ,sysdate
460 ,sysdate
461 ,-1
462 ,-1
463 ,-1
464 FROM dual ;
465
466 END IF;
467
468 commit;
469 bis_collection_utilities.log('Source Denorm Concurrent Program Completed Succesfully ');
470
471 /********* commented becuase same package is being use to load programs denorm table also so only one wrapup is required*/
472 bis_collection_utilities.wrapup(p_status => TRUE
473 ,p_count => sql%rowcount
474 ,p_period_from => l_temp_start_date
475 ,p_period_to => sysdate
476 );
477
478 EXCEPTION
479 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
481 -- Standard call to get message count and if count=1, get the message
482 FND_MSG_PUB.Count_And_Get (
483 p_encoded => FND_API.G_FALSE,
484 p_count => x_msg_count,
485 p_data => x_msg_data
486 );
487
488 WHEN FND_API.g_exc_error THEN
489 x_return_status := FND_API.g_ret_sts_error;
490 FND_MSG_PUB.count_and_get(
491 p_encoded => FND_API.g_false,
492 p_count => x_msg_count,
493 p_data => x_msg_data
494 );
495
496 WHEN OTHERS THEN
497 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
498 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
499 THEN
500 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
501 END IF;
502 -- Standard call to get message count and if count=1, get the message
503 FND_MSG_PUB.Count_And_Get (
504 p_encoded => FND_API.G_FALSE,
505 p_count => x_msg_count,
506 p_data => x_msg_data
507 );
508
509 END POPULATE_SOURCE_DENORM;
510
511 PROCEDURE LOAD_ADMIN_RECORDS(
512 p_api_version_number IN NUMBER := 1.0,
513 p_init_msg_list IN VARCHAR2,
514 p_commit IN VARCHAR2,
515 p_validation_level IN NUMBER,
516 x_return_status OUT NOCOPY VARCHAR2,
517 x_msg_count OUT NOCOPY NUMBER,
518 x_msg_data OUT NOCOPY VARCHAR2
519 )
520 IS
521
522 l_api_version CONSTANT NUMBER := 1.0;
523 l_api_name CONSTANT VARCHAR2(30) := 'LOAD_ADMIN_RECORDS';
524 l_admin_id Number := null;
525 l_resource_id Number := null;
526 l_response_country Varchar2(30);
527
528 l_status VARCHAR2(5);
529 l_industry VARCHAR2(5);
530 l_schema VARCHAR2(30);
531 l_return BOOLEAN;
532
533 BEGIN
534
535 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
536 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_i_admin_group';
537
538
539 INSERT INTO bim_i_admin_group
540 (
541 Resource_Id
542 )
543 SELECT resource_id
544 FROM jtf_rs_group_members
545 WHERE group_id = fnd_profile.value('AMS_ADMIN_GROUP')
546 AND delete_flag ='N';
547
548 COMMIT;
549
550 EXCEPTION
551 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
553 -- Standard call to get message count and if count=1, get the message
554 FND_MSG_PUB.Count_And_Get (
555 p_encoded => FND_API.G_FALSE,
556 p_count => x_msg_count,
557 p_data => x_msg_data
558 );
559
560 WHEN FND_API.g_exc_error THEN
561 x_return_status := FND_API.g_ret_sts_error;
562 FND_MSG_PUB.count_and_get(
563 p_encoded => FND_API.g_false,
564 p_count => x_msg_count,
565 p_data => x_msg_data
566 );
567
568 WHEN OTHERS THEN
569 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
570 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
571 THEN
572 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
573 END IF;
574 -- Standard call to get message count and if count=1, get the message
575 FND_MSG_PUB.Count_And_Get (
576 p_encoded => FND_API.G_FALSE,
577 p_count => x_msg_count,
578 p_data => x_msg_data
579 );
580
581 END LOAD_ADMIN_RECORDS;
582
583
584 PROCEDURE LOAD_TOP_LEVEL_OBJECTS(
585 p_api_version_number IN NUMBER := 1.0,
586 p_init_msg_list IN VARCHAR2,
587 p_commit IN VARCHAR2,
588 p_validation_level IN NUMBER,
589 x_return_status OUT NOCOPY VARCHAR2,
590 x_msg_count OUT NOCOPY NUMBER,
591 x_msg_data OUT NOCOPY VARCHAR2
592 )
593 IS
594
595 l_api_version CONSTANT NUMBER := 1.0;
596 l_api_name CONSTANT VARCHAR2(30) := 'LOAD_TOP_LEVEL_OBJECTS';
597 l_admin_id Number := null;
598 l_resource_id Number := null;
599 l_response_country Varchar2(30);
600
601 l_status VARCHAR2(5);
602 l_industry VARCHAR2(5);
603 l_schema VARCHAR2(30);
604 l_return BOOLEAN;
605
606
607 BEGIN
608
609 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
610 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_i_top_objects';
611
612 INSERT INTO bim_i_top_objects
613 (
614 resource_id,
615 source_code_id ,
616 object_id,
617 object_type,
618 creation_date,
619 last_update_date,
620 created_by,
621 last_updated_by,
622 last_update_login)
623 SELECT c.resource_id,
624 b.parent_source_code_id,
625 null,
626 null,
627 sysdate,
628 sysdate,
629 -1,
630 -1,
631 -1
632 FROM bim_i_source_denorm b,
633 (
634 SELECT
635 a.resource_id,code1.source_code_id,
636 max(b.object_level) object_level ,
637 a.object_type object_type
638 FROM ams_act_access_denorm a,
639 bim_i_source_denorm b,
640 bim_i_source_codes code1,
641 ams_act_access_denorm c,
642 bim_i_source_codes code2
643 WHERE a.object_id = code1.object_id
644 AND a.object_type = code1.object_type
645 AND b.source_code_id=code1.source_code_id
646 AND code1.object_type in ('RCAM', 'CAMP', 'EVEH', 'EONE')
647 AND code1.child_object_id=0
648 AND a.edit_metrics_yn = 'Y'
649 AND NOT EXISTS
650 (SELECT resource_id FROM bim_i_admin_group WHERE resource_id = a.resource_id)
651 AND c.resource_id = a.resource_id
652 AND c.object_id = code2.object_id
653 AND c.object_type = code2.object_type
654 AND code2.source_code_id=b.parent_source_code_id
655 AND c.edit_metrics_yn = 'Y'
656 GROUP BY a.resource_id, code1.source_code_id, a.object_type) c
657 WHERE c.object_level = b.object_level
658 AND b.source_code_id = c.source_code_id
659 GROUP BY c.resource_id,b.parent_source_code_id;
660 COMMIT;
661
662 EXCEPTION
663 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
664 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
665 -- Standard call to get message count and if count=1, get the message
666 FND_MSG_PUB.Count_And_Get (
667 p_encoded => FND_API.G_FALSE,
668 p_count => x_msg_count,
669 p_data => x_msg_data
670 );
671
672 WHEN FND_API.g_exc_error THEN
673 x_return_status := FND_API.g_ret_sts_error;
674 FND_MSG_PUB.count_and_get(
675 p_encoded => FND_API.g_false,
676 p_count => x_msg_count,
677 p_data => x_msg_data
678 );
679
680 WHEN OTHERS THEN
681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
682 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
683 THEN
684 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
685 END IF;
686 -- Standard call to get message count and if count=1, get the message
687 FND_MSG_PUB.Count_And_Get (
688 p_encoded => FND_API.G_FALSE,
689 p_count => x_msg_count,
690 p_data => x_msg_data
691 );
692
693 END LOAD_TOP_LEVEL_OBJECTS;
694
695 END bim_programs_denorm_pkg;