[Home] [Help]
PACKAGE BODY: APPS.BIM_SEGMENT_DENORM_PKG
Source
1 PACKAGE BODY bim_segment_denorm_pkg AS
2 /* $Header: bimisgdb.pls 120.16.12010000.1 2008/07/29 21:04:45 appldev ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(22) := 'BIM_SEGMENT_DENORM_PKG';
5 g_file_name CONSTANT VARCHAR2(20) := 'bimisgdb.pls';
6
7 PROCEDURE COMMON_UTILITIES
8 ( l_global_start_date OUT NOCOPY DATE
9 ,l_period_from OUT NOCOPY DATE
10 ,l_period_to OUT NOCOPY DATE
11 ,l_temp_start_date OUT NOCOPY DATE
12 ,l_start_date OUT NOCOPY DATE
13 ,l_end_date OUT NOCOPY DATE
14 ) IS
15 l_global_date CONSTANT DATE := bis_common_parameters.get_global_start_date;
16
17 BEGIN
18 l_global_start_date := l_global_date;
19
20 BEGIN
21 /* Set up the Object */
22 IF NOT bis_collection_utilities.setup('BIM_SEGMENT_DENORM_PKG') THEN
23 bis_collection_utilities.log('Object Not Setup Properly ');
24 END IF;
25
26 bis_collection_utilities.get_last_refresh_dates('BIM_SEGMENT_DENORM_PKG'
27 ,l_start_date,l_end_date,l_period_from,l_period_to);
28
29 EXCEPTION
30 WHEN NO_DATA_FOUND THEN
31 l_end_date := NULL;
32 bis_collection_utilities.log('First time running the concurrent program ');
33 WHEN OTHERS THEN
34 bis_collection_utilities.log('program '|| sqlerrm(sqlcode));
35 END;
36
37 /* End of the code for checking the data will be loaded for the first time or not. */
38
39 IF l_period_to IS NULL THEN
40 l_temp_start_date := sysdate-5000;
41 ELSE
42 l_temp_start_date := l_period_to;
43 END IF;
44
45 END COMMON_UTILITIES;
46
47 PROCEDURE POPULATE
48 ( ERRBUF OUT NOCOPY VARCHAR2
49 ,RETCODE OUT NOCOPY NUMBER
50 ,p_api_version_number IN NUMBER
51 ,p_proc_num IN NUMBER
52 ,p_load_type IN VARCHAR2
53 ) IS
54
55 l_api_version_number CONSTANT NUMBER := 1.0;
56 l_api_name CONSTANT VARCHAR2(30) := 'POPULATE';
57 x_msg_count NUMBER;
58 x_msg_data VARCHAR2(240);
59 x_return_status VARCHAR2(1) ;
60 l_init_msg_list VARCHAR2(10);
61 l_date DATE;
62 l_start_date DATE;
63 l_end_date DATE;
64 l_period_from DATE;
65 l_period_to DATE;
66 l_temp_start_date DATE;
67
68 BEGIN
69
70 l_date:= bis_common_parameters.get_global_start_date;
71 -- Standard call to check for call compatibility.
72
73 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
74 p_api_version_number,
75 l_api_name,
76 g_pkg_name)
77 THEN
78 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
79 END IF;
80
81 POPULATE_SEGMENT_DENORM
82 (p_api_version_number => 1.0
83 ,p_init_msg_list => FND_API.G_FALSE
84 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
85 ,p_commit => 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 ,p_proc_num => p_proc_num
90 ,p_load_type => p_load_type
91 );
92
93
94 IF x_return_status = FND_API.g_ret_sts_error
95 THEN
96 RAISE FND_API.g_exc_error;
97 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
98 RAISE FND_API.g_exc_unexpected_error;
99 END IF;
100
101 EXCEPTION
102
103 WHEN FND_API.G_EXC_ERROR THEN
104 x_return_status := FND_API.g_ret_sts_error ;
105 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
106 p_count => x_msg_count,
107 p_data => x_msg_data);
108 ERRBUF := x_msg_data;
109 RETCODE := 2;
110
111 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
112 x_return_status := FND_API.g_ret_sts_unexp_error ;
113 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
114 p_count => x_msg_count,
115 p_data => x_msg_data);
116 ERRBUF := x_msg_data;
117 RETCODE := 2;
118
119 WHEN OTHERS THEN
120 x_return_status := FND_API.g_ret_sts_unexp_error ;
121 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
122 p_count => x_msg_count,
123 p_data => x_msg_data);
124 ERRBUF := sqlerrm(sqlcode);
125 RETCODE := sqlcode;
126
127 END POPULATE;
128
129
130 PROCEDURE POPULATE_SEGMENT_DENORM
131 (p_api_version_number IN NUMBER
132 ,p_init_msg_list IN VARCHAR2
133 ,p_validation_level IN NUMBER
134 ,p_commit IN VARCHAR2
135 ,x_msg_Count OUT NOCOPY NUMBER
136 ,x_msg_Data OUT NOCOPY VARCHAR2
137 ,x_return_status OUT NOCOPY VARCHAR2
138 ,p_proc_num IN NUMBER
139 ,p_load_type IN VARCHAR2
140 ) IS
141
142 l_date DATE;
143 l_temp_start_date DATE /*:= TO_DATE('01-MAY-2005')*/;
144 l_temp_end_date DATE;
145 l_api_version_number CONSTANT NUMBER := 1.0;
146 l_api_name CONSTANT VARCHAR2(30) := 'POPULATE_DENORM';
147 l_init_msg_list VARCHAR2(10);
148 l_start_date DATE;
149 l_end_date DATE;
150 l_period_from DATE;
151 l_period_to DATE;
152 l_return BOOLEAN;
153
154 l_status VARCHAR2(5);
155 l_industry VARCHAR2(5);
156 l_schema VARCHAR2(30);
157
158 l_source VARCHAR2(80);
159 l_sql_id NUMBER(32);
160 l_query_string VARCHAR2(32767);
161
162 cursor c_query_source is
163 select list_query_id, query
164 from ams_list_queries_all ;
165
166 l_found VARCHAR2(1) := 'N';
167 l_master_type VARCHAR2(80);
168 l_master_type_id NUMBER;
169 l_source_object_name VARCHAR2(80);
170 l_source_object_pk_field VARCHAR2(80);
171 l_sql_string_tbl AMS_ListGeneration_PKG.sql_string;
172 l_from_position NUMBER;
173 l_from_counter NUMBER;
174 l_end_position NUMBER;
175 l_end_counter NUMBER;
176 l_count NUMBER;
177 l_string_copy VARCHAR2(32767);
178 l_length NUMBER;
179
180 BEGIN
181
182 Execute Immediate 'CREATE TABLE ' || ' source_query_sgdb ' || '( sql_id NUMBER(32), source_name VARCHAR2(80) )';
183
184 Execute Immediate 'TRUNCATE TABLE source_query_sgdb';
185
186 OPEN c_query_source;
187 LOOP
188 FETCH c_query_source INTO l_sql_id, l_query_string;
189 EXIT WHEN c_query_source%notfound;
190
191 if ( l_query_string is NULL ) then
192 l_source := 'NO_MASTER_TYPE';
193 else
194 l_count := 0;
195 l_string_copy := l_query_string;
196
197 l_length := length(l_string_copy);
198
199 LOOP
200 l_count := l_count + 1;
201 IF l_length < 1999 THEN
202 l_sql_string_tbl(l_count) := l_string_copy;
203 EXIT;
204 ELSE
205 l_sql_string_tbl(l_count) := substr(l_string_copy, 1, 2000);
206 l_string_copy := substr(l_string_copy, 2000);
207 END IF;
208 l_length := length(l_string_copy);
209 END LOOP;
210
211 l_found := 'N';
212 AMS_ListGeneration_PKG.validate_sql_string(
213 p_sql_string => l_sql_string_tbl ,
214 p_search_string => 'FROM',
215 p_comma_valid => 'N',
216 x_found => l_found,
217 x_position => l_from_position,
218 x_counter => l_from_counter) ;
219
220
221 l_found := 'N';
222
223 AMS_ListGeneration_PKG.get_master_types (
224 p_sql_string => l_sql_string_tbl,
225 p_start_length => 1,
226 p_start_counter => 1,
227 p_end_length => l_from_position,
228 p_end_counter => l_from_counter,
229 x_master_type_id=> l_master_type_id,
230 x_master_type=> l_master_type,
231 x_found=> l_found,
232 x_source_object_name => l_source_object_name,
233 x_source_object_pk_field => l_source_object_pk_field);
234
235 IF nvl(l_found,'N') = 'N' THEN
236 --No master type.
237 l_source_object_name := 'NO_MASTER_TYPE';
238 END IF;
239
240
241 l_source := l_master_type;
242 END IF;
243 -- bis_collection_utilities.log('running the function --- '||l_source||l_master_type_id||l_master_type);
244 Execute Immediate 'INSERT INTO source_query_sgdb(sql_id, source_name) VALUES('||l_sql_id||', '''||l_source||''')';
245 l_source := NULL;
246 l_query_string := NULL;
247 l_sql_id := NULL;
248 END LOOP;
249 CLOSE c_query_source;
250
251 l_init_msg_list:= FND_API.G_FALSE;
252
253 /* This Delete Statement is for the records changed after last run. */
254
255
256 IF p_load_type = 'F' THEN
257
258 --it is a call for First/Inital load, then truncate the denorm table first
259 bis_collection_utilities.log('Truncating the Denorm Table ');
260
261 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
262
263 Execute Immediate 'Truncate Table '||l_schema||'.bim_i_sgmt_denorm';
264
265 BIS_COLLECTION_UTILITIES.deleteLogForObject('BIM_SEGMENT_DENORM_PKG');
266
267 COMMON_UTILITIES(l_date,l_period_from,l_period_to,l_temp_start_date,l_start_date,l_end_date);
268
269 ELSE
270
271 COMMON_UTILITIES(l_date,l_period_from,l_period_to,l_temp_start_date,l_start_date,l_end_date);
272
273 DELETE bim_i_sgmt_denorm
274 WHERE segment_id IN
275 (SELECT cell_id
276 FROM ams_cells_all_b a
277 WHERE last_update_date > l_temp_start_date
278 );
279
280 /* This Delete Statement for the objects that had an update somewhere in their hierarachy chain.*/
281
282 DELETE bim_i_sgmt_denorm
283 WHERE segment_id IN (SELECT segment_id
284 FROM bim_i_sgmt_denorm
285 WHERE parent_segment_id in (SELECT b.segment_id
286 FROM ams_cells_all_b a
287 ,bim_i_sgmt_denorm b
288 WHERE b.segment_id = a.cell_id
289 AND a.last_update_date > l_temp_start_date
290 )
291 );
292
293 END IF;
294
295
296
297 IF l_period_to IS NOT NULL THEN
298
299 bis_collection_utilities.log('Updating leaf node flags for incremental load ');
300
301
302 UPDATE bim_i_sgmt_denorm
303 SET leaf_node_flag = 'N'
304 WHERE segment_id IN (SELECT parent_cell_id
305 FROM ams_cells_all_b a
306 WHERE NOT EXISTS (SELECT 1 FROM bim_i_sgmt_denorm b WHERE b.segment_id = a.cell_id)
307 AND object_level = 1
308 AND a.last_update_date > l_temp_start_date
309 );
310
311
312 /**********************FOR INCREMENT LOAD**********************/
313 /**************************************************************/
314 Execute Immediate ' INSERT INTO bim_i_sgmt_denorm ' ||
315 ' ( segment_id ' ||
316 ' ,parent_segment_id ' ||
317 ' ,immediate_parent_flag ' ||
318 ' ,immediate_parent_id ' ||
319 ' ,object_level ' ||
320 ' ,top_node_flag ' ||
321 ' ,leaf_node_flag ' ||
322 ' ,prior_id ' ||
323 ' ,creation_date ' ||
324 ' ,last_update_date ' ||
325 ' ,created_by ' ||
326 ' ,last_updated_by ' ||
327 ' ,last_update_login ' ||
328 ' ) ' ||
329 ' SELECT ' ||
330 ' x.segment_id ' ||
331 ' ,x.parent_segment_id ' ||
332 ' ,x.immediate_parent_flag ' ||
333 ' ,x.immediate_parent_id ' ||
334 ' ,x.object_level ' ||
335 ' ,decode(s.parent_cell_id, NULL, ''Y'', ''N'') top_node_flag ' ||
336 ' ,x.leaf_node_flag ' ||
337 ' ,s.parent_cell_id prior_id ' ||
338 ' ,sysdate ' ||
339 ' ,sysdate ' ||
340 ' ,-1 ' ||
341 ' ,-1 ' ||
342 ' ,-1 ' ||
343 ' FROM ' ||
344 ' ( SELECT ' ||
345 ' cell_id segment_id ' ||
346 ' ,TO_NUMBER(NVL(SUBSTR(SYS_CONNECT_BY_PATH(cell_id,''/''),2, INSTR(SYS_CONNECT_BY_PATH(cell_id,''/''),''/'',2) -2),cell_id)) AS parent_segment_id ' ||
347 ' ,decode(parent_cell_id,NULL,''Y'',decode(level,2,''Y'',''N'')) immediate_parent_flag ' ||
348 ' ,parent_cell_id immediate_parent_id ' ||
349 ' ,LEVEL object_level ' ||
350 ' ,decode(parent_cell_id, NULL,''Y'',''N'') top_node_flag ' ||
351 ' ,DECODE((SELECT COUNT(1) FROM ams_cells_all_b c WHERE parent_cell_id = a.cell_id),0,''Y'',''N'') leaf_node_flag ' ||
352 ' FROM (SELECT a.cell_id , a.parent_cell_id ' ||
353 ' FROM ams_cells_all_b a, ams_list_queries_all b, ams_list_src_types c, source_query_sgdb d ' ||
354 ' WHERE b.act_list_query_used_by_id = a.cell_id ' ||
355 ' AND b.arc_act_list_query_used_by =''CELL'' ' ||
356 ' AND b.list_query_id = d.sql_id ' ||
357 ' AND d.source_name = c.source_type_code ' ||
358 ' AND c.based_on_tca_flag = ''Y'' ' ||
359 ' AND a.sel_type =''SQL'' ' ||
360 ' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
361 ' UNION ALL ' ||
362 ' SELECT a.cell_id , a.parent_cell_id ' ||
363 ' FROM ams_cells_all_b a,ams_list_src_types b,ams_act_discoverer_all c,ams_discoverer_sql d ' ||
364 ' WHERE c.act_discoverer_used_by_id = a.cell_id ' ||
365 ' AND c.arc_act_discoverer_used_by =''CELL'' ' ||
366 ' AND c.discoverer_sql_id = d.discoverer_sql_id ' ||
367 ' AND d.source_type_code = b.source_object_name ' ||
368 ' AND b.based_on_tca_flag = ''Y'' ' ||
369 ' AND a.sel_type=''DIWB'' ' ||
370 ' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
371 ' ) a ' ||
372 ' WHERE NOT EXISTS ( SELECT 1 FROM bim_i_sgmt_denorm b ' ||
373 ' WHERE b.segment_id = a.cell_id ) ' ||
374 ' CONNECT BY PRIOR cell_id = parent_cell_id ' ||
375 ' )x, ams_cells_all_b s ' ||
376 ' WHERE s.cell_id = x.parent_segment_id' ;
377
378 Execute Immediate ' DROP TABLE source_query_sgdb ';
379
380 bis_collection_utilities.log('Records Inserted for Incremental load ');
381
382 ELSE
383
384 /**********************FOR INITITAL LOAD**********************/
385 bis_collection_utilities.log('Initial Load of Segment Denorm Program');
386
387 Execute Immediate ' INSERT INTO bim_i_sgmt_denorm ' ||
388 ' ( segment_id ' ||
389 ' ,parent_segment_id ' ||
390 ' ,immediate_parent_flag ' ||
391 ' ,immediate_parent_id ' ||
392 ' ,object_level ' ||
393 ' ,top_node_flag ' ||
394 ' ,leaf_node_flag ' ||
395 ' ,prior_id ' ||
396 ' ,creation_date ' ||
397 ' ,last_update_date ' ||
398 ' ,created_by ' ||
399 ' ,last_updated_by ' ||
400 ' ,last_update_login ' ||
401 ' ) ' ||
402 ' SELECT ' ||
403 ' x.segment_id ' ||
404 ' ,x.parent_segment_id ' ||
405 ' ,x.immediate_parent_flag ' ||
406 ' ,x.immediate_parent_id ' ||
407 ' ,x.object_level ' ||
408 ' ,decode(s.parent_cell_id, NULL, ''Y'', ''N'') top_node_flag ' ||
409 ' ,x.leaf_node_flag ' ||
410 ' ,s.parent_cell_id prior_id ' ||
411 ' ,sysdate ' ||
412 ' ,sysdate ' ||
413 ' ,-1 ' ||
414 ' ,-1 ' ||
415 ',-1 ' ||
416 ' FROM ' ||
417 ' (SELECT ' ||
418 ' cell_id segment_id ' ||
419 ' ,TO_NUMBER(NVL(SUBSTR(SYS_CONNECT_BY_PATH(cell_id,''/''),2, INSTR(SYS_CONNECT_BY_PATH(cell_id,''/''),''/'',2) -2),cell_id)) AS parent_segment_id ' ||
420 ' ,decode(parent_cell_id,NULL,''Y'',decode(level,2,''Y'',''N'')) immediate_parent_flag ' ||
421 ' ,parent_cell_id immediate_parent_id ' ||
422 ' ,LEVEL object_level ' ||
423 ' ,decode(parent_cell_id, NULL,''Y'',''N'') top_node_flag ' ||
424 ' ,DECODE((SELECT COUNT(1) FROM ams_cells_all_b c WHERE parent_cell_id = a.cell_id),0,''Y'',''N'') leaf_node_flag ' ||
425 ' FROM (SELECT a.cell_id , a.parent_cell_id ' ||
426 ' FROM ams_cells_all_b a, ams_list_queries_all b, ams_list_src_types c, source_query_sgdb d ' ||
427 ' WHERE b.act_list_query_used_by_id = a.cell_id ' ||
428 ' AND b.arc_act_list_query_used_by =''CELL'' ' ||
429 ' AND b.list_query_id = d.sql_id ' ||
430 ' AND d.source_name = c.source_type_code ' ||
431 ' AND c.based_on_tca_flag = ''Y'' ' ||
432 ' AND a.sel_type =''SQL'' ' ||
433 ' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
434 ' UNION ALL ' ||
435 ' SELECT a.cell_id , a.parent_cell_id ' ||
436 ' FROM ams_cells_all_b a,ams_list_src_types b,ams_act_discoverer_all c,ams_discoverer_sql d ' ||
437 ' WHERE c.act_discoverer_used_by_id = a.cell_id ' ||
438 ' AND c.arc_act_discoverer_used_by =''CELL'' ' ||
439 ' AND c.discoverer_sql_id = d.discoverer_sql_id ' ||
440 ' AND d.source_type_code = b.source_object_name ' ||
441 ' AND b.based_on_tca_flag = ''Y'' ' ||
442 ' AND a.sel_type=''DIWB'' ' ||
443 ' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
444 ' ) a ' ||
445 ' CONNECT BY PRIOR a.cell_id = a.parent_cell_id ' ||
446 ' )x, ams_cells_all_b s ' ||
447 ' WHERE s.cell_id = x.parent_segment_id' ;
448 /* UNION ALL
449 NOTE:-additional UNION ALL for unassigned values
450 SELECT
451 -1 segment_id
452 ,-1 parent_segment_id
453 ,'Y' immediate_parent_flag
454 ,null immediate_parent_id
455 ,1 object_level
456 ,'Y' top_node_flag
457 ,'Y' leaf_node_flag
458 ,null prior_id
459 ,sysdate creation_date
460 ,sysdate last_update_date
461 ,-1 created_by
462 ,-1 last_updated_by
463 ,-1 last_update_login
464 FROM dual ;*/
465
466 Execute Immediate ' DROP TABLE source_query_sgdb ';
467
468 bis_collection_utilities.log('Records Inserted for Initial load ');
469 END IF;
470
471 commit;
472
473 bis_collection_utilities.log('Segment Denorm Concurrent Program Completed Succesfully ');
474
475 bis_collection_utilities.wrapup(p_status => TRUE
476 ,p_count => sql%rowcount
477 ,p_period_from => l_temp_start_date
478 ,p_period_to => sysdate
479 );
480
481 EXCEPTION
482 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
483 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
484 Execute Immediate 'DROP TABLE source_query_sgdb';
485 -- Standard call to get message count and if count=1, get the message
486 FND_MSG_PUB.Count_And_Get (
487 p_encoded => FND_API.G_FALSE,
488 p_count => x_msg_count,
489 p_data => x_msg_data
490 );
491
492 WHEN FND_API.g_exc_error THEN
493 x_return_status := FND_API.g_ret_sts_error;
494 Execute immediate 'DROP TABLE source_query_sgdb';
495 FND_MSG_PUB.count_and_get(
496 p_encoded => FND_API.g_false,
497 p_count => x_msg_count,
498 p_data => x_msg_data
499 );
500
501 WHEN OTHERS THEN
502 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
503 Execute Immediate 'DROP TABLE source_query_sgdb';
504 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
505 THEN
506 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
507 END IF;
508 -- Standard call to get message count and if count=1, get the message
509 FND_MSG_PUB.Count_And_Get (
510 p_encoded => FND_API.G_FALSE,
511 p_count => x_msg_count,
512 p_data => x_msg_data
513 );
514
515 END POPULATE_SEGMENT_DENORM;
516
517 END BIM_SEGMENT_DENORM_PKG;