[Home] [Help]
PACKAGE BODY: APPS.BIM_SOURCE_CODE_PKG
Source
1 PACKAGE BODY BIM_SOURCE_CODE_PKG AS
2 /*$Header: bimsrcdb.pls 120.3 2005/11/11 01:55:04 arvikuma noship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(20) := 'BIM_SOURCE_CODE_PKG';
5 g_file_name CONSTANT VARCHAR2(20) := 'bimsrcdb.pls';
6
7 /* ----------------------------------------------------------------------
8 This procedure will insert a record for object = 'SOURCE' in
9 BIM_REP_HISTORY table, whenever the LOAD_DATA procedure is called
10 for the first time in the day.
11 ----------------------------------------------------------------------*/
12 PROCEDURE LOG_HISTORY
13 (
14 p_object IN VARCHAR2 DEFAULT 'SOURCE',
15 p_start_date IN DATE DEFAULT NULL,
16 p_end_date IN DATE DEFAULT NULL
17 )
18 IS
19 l_user_id NUMBER := FND_GLOBAL.USER_ID();
20 l_sysdate DATE := SYSDATE;
21 l_api_version_number CONSTANT NUMBER := 1.0;
22 l_api_name CONSTANT VARCHAR2(30) := 'LOG_HISTORY';
23 BEGIN
24
25 INSERT INTO BIM_REP_HISTORY
26 (creation_date,
27 last_update_date,
28 created_by,
29 last_updated_by,
30 object,
31 object_last_updated_date,
32 start_date,
33 end_date)
34 VALUES
35 (sysdate,
36 sysdate,
37 l_user_id,
38 l_user_id,
39 p_object,
40 sysdate,
41 p_start_date,
42 p_end_date);
43
44 END LOG_HISTORY;
45
46 /*----------------------------------------------------------------------------
47 This procedure will Populate BIM_R_SOURCE_CODES and BIM_R_LOCATIONS tables
48 ----------------------------------------------------------------------------*/
49
50 PROCEDURE LOAD_DATA
51 (p_api_version_number IN NUMBER
52 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
53 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
54 ,x_msg_count OUT NOCOPY NUMBER
55 ,x_msg_data OUT NOCOPY VARCHAR2
56 ,x_return_status OUT NOCOPY VARCHAR2
57 ) IS
58 l_api_version_number CONSTANT NUMBER := 1.0;
59 l_api_name CONSTANT VARCHAR2(30) := 'LOAD_DATA';
60 l_user_id NUMBER := FND_GLOBAL.USER_ID();
61 l_success VARCHAR2(3);
62 l_seq_name VARCHAR(100);
63 l_def_tablespace VARCHAR2(100);
64 l_index_tablespace VARCHAR2(100);
65 l_oracle_username VARCHAR2(100);
66 l_table_name VARCHAR2(100);
67 l_temp_msg VARCHAR2(100);
68
69 /* Following tables are declared for storing information about the indexes */
70
71 TYPE generic_number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
72 TYPE generic_char_table IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
73
74 i NUMBER;
75 l_creation_date DATE;
76
77 CURSOR chk_history_data
78 IS
79 SELECT trunc(max(creation_date))
80 FROM BIM_REP_HISTORY
81 WHERE object = 'SOURCE';
82 l_status VARCHAR2(5);
83 l_industry VARCHAR2(5);
84 l_schema VARCHAR2(30);
85 l_return BOOLEAN;
86
87
88 BEGIN /* Standard API call to check for call compatibility */
89 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
90
91
92 IF NOT FND_API.Compatible_API_Call (l_api_version_number, p_api_version_number, l_api_name, g_pkg_name)
93 THEN RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
94 END IF;
95
96 /* Initialize message list if p_init_msg_list is set to TRUE */
97 IF FND_API.to_Boolean( p_init_msg_list )
98 THEN
99 FND_MSG_PUB.initialize;
100 END IF;
101
102 /* Initialize API return status to SUCCESS */
103 x_return_status := FND_API.G_RET_STS_SUCCESS;
104
105 /* Find if the data will be loaded for the first time in the day or not */
106 OPEN chk_history_data;
107 FETCH chk_history_data INTO l_creation_date;
108 CLOSE chk_history_data;
109
110 --dbms_output.put_line('Just before checking last_creation_date from history table : '||l_creation_date);
111
112
113
114 /* Begin of the code for checking whether first-time or subsequent run */
115 IF ((l_creation_date) = trunc(sysdate)) THEN
116 /* Return control back to the caller before the normal end of procedure is reached */
117 /* No messages in the LOG file */
118 --ams_utility_pvt.write_conc_log('TABLES BIM_R_SOURCE_CODES and BIM_R_LOCATIONS are already POPULATED Today');
119 return;
120 END IF;
121
122 l_table_name := 'BIM_R_LOCATIONS';
123 fnd_message.set_name('BIM','BIM_R_TRUNCATE_TABLE');
124 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
125 fnd_file.put_line(fnd_file.log,fnd_message.get);
126
127 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' ||l_schema||'.BIM_R_LOCATIONS';
128
129 l_table_name := 'BIM_R_LOCATIONS';
130 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
131 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
132 fnd_file.put_line(fnd_file.log,fnd_message.get);
133
134 /* The INSERT statement to populate BIM_R_LOCATIONS table begins here */
135
136 INSERT
137 INTO BIM_R_LOCATIONS
138 (
139 country,
140 region)
141 SELECT
142 country_code, area2_code
143 FROM jtf_loc_hierarchies_b
144 WHERE location_type_code = 'COUNTRY'
145 AND country_code is not null;
146
147
148 l_table_name := 'BIM_R_LOCATIONS';
149 fnd_message.set_name('BIM','BIM_R_ANALYZE_TABLE');
150 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
151 fnd_file.put_line(fnd_file.log,fnd_message.get);
152
153 -- Analyze the bim_r_locations table
154 DBMS_STATS.gather_table_stats('BIM','BIM_R_LOCATIONS', estimate_percent => 5,
155 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
156
157
158 /* Dropping INdexes */
159 BIM_UTL_PKG.DROP_INDEX('BIM_R_SOURCE_CODES');
160
161
162 l_table_name := 'BIM_R_SOURCE_CODES';
163 fnd_message.set_name('BIM','BIM_R_TRUNCATE_TABLE');
164 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
165 fnd_file.put_line(fnd_file.log,fnd_message.get);
166
167 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' ||l_schema||'.BIM_R_SOURCE_CODES';
168
169
170 l_table_name := 'BIM_R_SOURCE_CODES';
171 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
172 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
173 fnd_file.put_line(fnd_file.log,fnd_message.get);
174
175 /* The INSERT statement to populate BIM_R_SOURCE_CODES table begins here */
176
177 INSERT /*+ append parallel(SRC,1) */
178 INTO BIM_R_SOURCE_CODES SRC
179 (
180 source_code_id,
181 source_code,
182 parent_object_type,
183 object_type,
184 parent_object_id,
185 object_id,
186 business_unit_id,
187 status,
188 country_code,
189 start_date,
190 end_date
191 )
192 SELECT /*+ parallel(INNER,1) */
193 inner.source_code_id,
194 inner.source_code,
195 inner.parent_object_type,
196 inner.object_type,
197 inner.parent_object_id,
198 inner.object_id,
199 inner.business_unit_id,
200 inner.status,
201 inner.country_code,
202 inner.start_date,
203 inner.end_date
204 FROM
205 (
206 SELECT
207 a.source_code_id source_code_id,
208 a.source_code source_code,
209 'CAMP' parent_object_type,
210 'CAMP' object_type,
211 b.campaign_id parent_object_id,
212 0 object_id,
213 b.business_unit_id,
214 b.status_code status,
215 c.country_code country_code,
216 b.actual_exec_start_date start_date,
217 b.actual_exec_end_date end_date
218 FROM
219 ams_source_codes a,
220 ams_campaigns_all_b b,
221 jtf_loc_hierarchies_b c
222 WHERE
223 a.source_code = b.source_code
224 AND a.source_code_for_id = b.campaign_id
225 AND b.city_id = c.location_hierarchy_id
226 AND a.arc_source_code_for = 'CAMP'
227 AND b.status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE')
228 UNION ALL
229 SELECT
230 a.source_code_id source_code_id,
231 a.source_code source_code,
232 'CAMP' parent_object_type,
233 'CSCH' object_type,
234 b.campaign_id parent_object_id,
235 c.schedule_id object_id,
236 b.business_unit_id,
237 b.status_code status,
238 d.country_code country_code,
239 c.start_date_time start_date,
240 c.end_date_time end_date
241 FROM
242 ams_source_codes a,
243 ams_campaigns_all_b b,
244 ams_campaign_schedules_b c,
245 jtf_loc_hierarchies_b d
246 WHERE
247 a.source_code = c.source_code
248 AND a.source_code_for_id = c.schedule_id
249 AND a.arc_source_code_for = 'CSCH'
250 AND b.campaign_id = c.campaign_id
251 AND b.city_id = d.location_hierarchy_id
252 AND b.status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE')
253 UNION ALL
254 SELECT
255 a.source_code_id source_code_id,
256 a.source_code source_code,
257 'EVEH' parent_object_type,
258 'EVEH' object_type,
259 b.event_header_id parent_object_id,
260 0 object_id,
261 b.business_unit_id,
262 b.system_status_code status,
263 c.country_code country_code,
264 b.active_from_date start_date,
265 b.active_to_date end_date
266 FROM
267 ams_source_codes a,
268 ams_event_headers_all_b b,
269 jtf_loc_hierarchies_b c
270 WHERE
271 a.source_code = b.source_code
272 AND a.source_code_for_id = b.event_header_id
273 AND b.country_code = c.location_hierarchy_id
274 AND a.arc_source_code_for = 'EVEH'
275 AND b.system_status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE')
276 UNION ALL
277 SELECT
278 a.source_code_id source_code_id,
279 a.source_code source_code,
280 'EVEH' parent_object_type,
281 a.arc_source_code_for object_type,
282 b.event_header_id parent_object_id,
283 c.event_offer_id object_id,
284 b.business_unit_id,
285 b.system_status_code status,
286 d.country_code country_code,
287 c.event_start_date start_date,
288 c.event_end_date end_date
289 FROM
290 ams_source_codes a,
291 ams_event_headers_all_b b,
292 ams_event_offers_all_b c,
293 jtf_loc_hierarchies_b d
294 WHERE
295 a.source_code = c.source_code
296 AND a.source_code_for_id = c.event_offer_id
297 AND a.arc_source_code_for in ('EONE', 'EVEO')
298 AND b.event_header_id = c.event_header_id
299 AND b.country_code = d.location_hierarchy_id
300 AND b.system_status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE')
301 ) "INNER";
302
303
304 /* Make entry in the history table */
305 IF SQL%ROWCOUNT > 0 THEN
306 LOG_HISTORY(P_OBJECT => 'SOURCE');
307 END IF;
308
309 COMMIT;
310
311 --dbms_output.put_Line('JUST A F T E R THE MAIN INSERT STATMENT for bim_r_source_codes');
312
313 l_table_name := 'BIM_R_SOURCE_CODES';
314 fnd_message.set_name('BIM','BIM_R_ANALYZE_TABLE');
315 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
316 fnd_file.put_line(fnd_file.log,fnd_message.get);
317
318 /* Analyze the bim_r_source_codes table */
319 DBMS_STATS.gather_table_stats('BIM','BIM_R_SOURCE_CODES', estimate_percent => 5,
320 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
321
322 /* Recreating Indexes */
323 BIM_UTL_PKG.CREATE_INDEX('BIM_R_SOURCE_CODES');
324
325 fnd_message.set_name('BIM','BIM_R_PROG_COMPLETION');
326 fnd_message.set_token('PROGRAM_NAME','Populating Source Codes',FALSE);
327 fnd_file.put_line(fnd_file.log,fnd_message.get);
328
329
330
331 /* Standard call to get message count and if count is 1, get message info */
332 FND_MSG_PUB.Count_And_Get
333 ( p_count => x_msg_count,
334 p_data => x_msg_data
335 );
336 COMMIT;
337
338 --dbms_output.put_line('S u c c e s s f u l l y e x i t i n g ......');
339 EXCEPTION
340 WHEN FND_API.G_EXC_ERROR THEN
341 x_return_status := FND_API.G_RET_STS_ERROR;
342 /* Standard call to get message count and if count=1, get the message */
343 FND_MSG_PUB.Count_And_Get
344 ( p_count => x_msg_count,
345 p_data => x_msg_data
346 );
347 ams_utility_pvt.write_conc_log('BIM_R_SOURCE_CODES:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
348
349 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
350 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
351 /* Standard call to get message count and if count=1, get the message */
352 FND_MSG_PUB.Count_And_Get
353 ( p_count => x_msg_count,
354 p_data => x_msg_data
355 );
356 ams_utility_pvt.write_conc_log('BIM_R_SOURCE_CODES:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
357
358 WHEN OTHERS THEN
359 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
360 IF FND_MSG_PUB.Check_msg_Level ( FND_msg_PUB.G_msg_LVL_UNEXP_ERROR) THEN
361 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
362 END IF;
363 /* Standard call to get message count and if count=1, get the message */
364 FND_MSG_PUB.Count_And_Get
365 ( p_count => x_msg_count,
366 p_data => x_msg_data
367 );
368 ams_utility_pvt.write_conc_log('BIM_R_SOURCE_CODES:IN OTHERS EXCEPTION '||sqlerrm(sqlcode));
369
370 --dbms_output.put_Line('EXCEPTIONS: OTHERS in bim_r_source_codes -- '||SQLERRM(SQLCODE));
371
372 /* End of Procedure */
373 END LOAD_DATA;
374
375 /* End of Package */
376 END BIM_SOURCE_CODE_PKG;