[Home] [Help]
PACKAGE BODY: APPS.BIM_RESPONSE_FACTS_PKG
Source
1 PACKAGE BODY BIM_RESPONSE_FACTS_PKG AS
2 /*$Header: bimrspfb.pls 120.3 2005/11/11 05:08:59 arvikuma noship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(200) := 'BIM_RESPONSE_FACTS_PKG';
5 g_file_name CONSTANT VARCHAR2(20) := 'bimrspfb.pls';
6
7 ----------------------------------------------------------------------------------------------------
8 /* This procedure will conditionally call RESPONSES_FACTS_LOAD */
9 ----------------------------------------------------------------------------------------------------
10
11 PROCEDURE POPULATE
12 (
13 p_api_version_number IN NUMBER
14 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
15 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
16 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
17 ,x_msg_count OUT NOCOPY NUMBER
18 ,x_msg_data OUT NOCOPY VARCHAR2
19 ,x_return_status OUT NOCOPY VARCHAR2
20 ,p_start_date IN DATE
21 ,p_end_date IN DATE
22 ,p_para_num IN NUMBER
23 ) IS
24
25 l_profile NUMBER;
26 v_error_code NUMBER;
27 v_error_text VARCHAR2(1500);
28 l_last_update_date DATE;
29 l_start_date DATE;
30 l_end_date DATE;
31 l_user_id NUMBER := FND_GLOBAL.USER_ID();
32 l_sysdate DATE := SYSDATE;
33 l_api_version_number CONSTANT NUMBER := 1.0;
34 l_api_name CONSTANT VARCHAR2(30) := 'BIM_RESPONSE_FACTS_PKG';
35 l_success VARCHAR2(3);
36 s_date DATE := to_date('01/01/1950 01:01:01', 'DD/MM/YYYY HH:MI:SS') ;
37 l_temp DATE;
38 l_mesg_text VARCHAR2(100);
39 l_period_error VARCHAR2(5000);
40 l_currency_error VARCHAR2(5000);
41 l_err_code NUMBER;
42 l_temp_start_date DATE;
43 l_temp_end_date DATE;
44 l_temp_p_end_date DATE;
45
46 BEGIN
47
48 fnd_message.set_name('BIM','BIM_R_START_PROGRAM');
49 fnd_message.set_token('OBJECT_NAME','Response',FALSE);
50 fnd_file.put_line(fnd_file.log,fnd_message.get);
51
52 -- Standard call to check for call compatibility.
53 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
54 p_api_version_number,
55 l_api_name,
56 g_pkg_name)
57 THEN
58 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
59 END IF;
60
61 -- Initialize message list if p_init_msg_list is set to TRUE.
62 IF FND_API.to_Boolean( p_init_msg_list )
63 THEN
64 FND_MSG_PUB.initialize;
65 END IF;
66
67 -- Initialize API return status to SUCCESS
68 x_return_status := FND_API.G_RET_STS_SUCCESS;
69
70 /* Find if the data will be loaded for the first time or not.*/
71 DECLARE
72 CURSOR chk_history_data IS
73 SELECT MAX(end_date)
74 FROM bim_rep_history
75 WHERE object = 'RESPONSE';
76
77 BEGIN
78 OPEN chk_history_data;
79 FETCH chk_history_data INTO l_end_date;
80 CLOSE chk_history_data;
81 EXCEPTION
82 WHEN OTHERS THEN
83 FND_MSG_PUB.Count_And_Get (
84 -- p_encoded => FND_API.G_FALSE,
85 p_count => x_msg_count,
86 p_data => x_msg_data
87 );
88 END;
89
90 IF(trunc(p_end_date) = trunc(sysdate)) THEN
91 l_temp_p_end_date := trunc(p_end_date) - 1;
92 ELSE
93 l_temp_p_end_date := trunc(p_end_date);
94 END IF;
95
96 IF (l_end_date IS NOT NULL AND p_start_date IS NOT NULL)
97 THEN
98 fnd_message.set_name('BIM','BIM_R_FIRST_LOAD');
99 fnd_message.set_token('END_DATE',to_char(l_end_date,'DD-MON-RR'),FALSE);
100 fnd_file.put_line(fnd_file.log,fnd_message.get);
101 RAISE FND_API.G_EXC_ERROR;
102 ELSIF (l_end_date IS NULL AND p_start_date IS NULL) THEN
103 fnd_message.set_name('BIM','BIM_R_FIRST_SUBSEQUENT');
104 fnd_file.put_line(fnd_file.log,fnd_message.get);
105 RAISE FND_API.G_EXC_ERROR;
106 END IF;
107
108 IF p_start_date IS NOT NULL THEN
109
110 IF (p_start_date >= l_temp_p_end_date) THEN
111 fnd_message.set_name('BIM','BIM_R_DATE_VALIDATION');
112 fnd_file.put_line(fnd_file.log,fnd_message.get);
113 RAISE FND_API.G_EXC_ERROR;
114 END IF;
115
116 l_temp_start_date := trunc(p_start_date);
117 l_temp_end_date := trunc(l_temp_p_end_date);
118
119 ELSE
120 IF l_end_date IS NOT NULL THEN
121
122 IF (l_temp_p_end_date <= l_end_date) THEN
123 fnd_message.set_name('BIM','BIM_R_SUBSEQUENT_LOAD');
124 fnd_message.set_token('END_DATE',to_char(l_end_date,'DD-MON-RR'),FALSE);
125 fnd_file.put_line(fnd_file.log,fnd_message.get);
126 RAISE FND_API.g_exc_error;
127 END IF;
128
129 l_temp_start_date := trunc(l_end_date) + 1;
130 l_temp_end_date := trunc(l_temp_p_end_date);
131
132 END IF;
133
134 END IF;
135
136 RESPONSES_FACTS_LOAD(p_start_date => l_temp_start_date
137 ,p_end_date => l_temp_end_date
138 ,p_api_version_number => l_api_version_number
139 ,p_init_msg_list => FND_API.G_FALSE
140 ,x_msg_count => x_msg_count
141 ,x_msg_data => x_msg_data
142 ,x_return_status => x_return_status
143 );
144
145
146 IF x_return_status = FND_API.g_ret_sts_error
147 THEN
148 RAISE FND_API.g_exc_error;
149 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
150 RAISE FND_API.g_exc_unexpected_error;
151 END IF;
152
153
154 -- ams_utility_pvt.write_conc_log('----Period Validation----');
155 -- ams_utility_pvt.write_conc_log(l_period_error);
156 -- ams_utility_pvt.write_conc_log('----Currency Validation----');
157 -- ams_utility_pvt.write_conc_log(l_currency_error);
158
159 --Standard check of commit
160
161 IF FND_API.To_Boolean ( p_commit ) THEN
162 COMMIT WORK;
163 END IF;
164
165 fnd_message.set_name('BIM','BIM_R_END_PROGRAM');
166 fnd_file.put_line(fnd_file.log,fnd_message.get);
167
168 -- Standard call to get message count and if count is 1, get message info.
169 FND_MSG_PUB.Count_And_Get
170 (p_count => x_msg_count,
171 p_data => x_msg_data
172 );
173
174 EXCEPTION
175
176 WHEN FND_API.G_EXC_ERROR THEN
177 x_return_status := FND_API.G_RET_STS_ERROR;
178 -- Standard call to get message count and if count=1, get the message
179 FND_msg_PUB.Count_And_Get (
180 -- p_encoded => FND_API.G_FALSE,
181 p_count => x_msg_count,
182 p_data => x_msg_data
183 );
184
185
186 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
187
188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
189 -- Standard call to get message count and if count=1, get the message
190 FND_msg_PUB.Count_And_Get (
191 --p_encoded => FND_API.G_FALSE,
192 p_count => x_msg_count,
193 p_data => x_msg_data
194 );
195
196 WHEN OTHERS THEN
197 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
198 IF FND_msg_PUB.Check_msg_Level ( FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
199 THEN
200 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
201 END IF;
202 -- Standard call to get message count and if count=1, get the message
203 FND_msg_PUB.Count_And_Get (
204 -- p_encoded => FND_API.G_FALSE,
205 p_count => x_msg_count,
206 p_data => x_msg_data
207 );
208
209 END POPULATE;
210
211 --------------------------------------------------------------------------------------------------
212 /* This procedure will insert a HISTORY record whenever daily or first load is run */
213 --------------------------------------------------------------------------------------------------
214
215 PROCEDURE LOG_HISTORY
216 (--p_api_version_number IN NUMBER
217 --,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
218 --,x_msg_count OUT NUMBER
219 --,x_msg_data OUT VARCHAR2
220 --,x_return_status OUT VARCHAR2
221 p_object IN VARCHAR2,
222 p_start_date IN DATE DEFAULT NULL,
223 p_end_date IN DATE DEFAULT NULL
224 )
225 IS
226 l_user_id NUMBER := FND_GLOBAL.USER_ID();
227 l_sysdate DATE := SYSDATE;
228 l_api_version_number CONSTANT NUMBER := 1.0;
229 l_api_name CONSTANT VARCHAR2(30) := 'BIM_RESPONSE_FACTS_PKG';
230 l_success VARCHAR2(3);
231
232 BEGIN
233
234 -- Debug Message
235 --AMS_UTILITY_PVT.debug_message('Private API: ' || 'Running the LOG_HISTORY procedure ');
236
237 /* -- Standard call to check for call compatibility.
238 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
239 p_api_version_number,
240 l_api_name,
241 g_pkg_name)
242 THEN
243 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
244 END IF;
245
246 -- Initialize message list if p_init_msg_list is set to TRUE.
247 IF FND_API.to_Boolean( p_init_msg_list )
248 THEN
249 FND_msg_PUB.initialize;
250 END IF;
251
252 -- Initialize API return status to SUCCESS
253 x_return_status := FND_API.G_RET_STS_SUCCESS;
254 */
255
256 INSERT INTO bim_rep_history
257 (CREATION_DATE,
258 LAST_UPDATE_DATE,
259 CREATED_BY,
260 LAST_UPDATED_BY,
261 OBJECT,
262 OBJECT_LAST_UPDATED_DATE,
263 START_DATE,
264 END_DATE)
265 VALUES
266 (sysdate,
267 sysdate,
268 l_user_id,
269 l_user_id,
270 p_object,
271 sysdate,
272 p_start_date,
273 p_end_date);
274
275 /* -- Standard call to get message count and if count is 1, get message info.
276 FND_msg_PUB.Count_And_Get
277 (p_count => x_msg_count,
278 p_data => x_msg_data
279 );
280 EXCEPTION
281
282 WHEN FND_API.G_EXC_ERROR THEN
283 x_return_status := FND_API.G_RET_STS_ERROR;
284 -- Standard call to get message count and if count=1, get the message
285 FND_msg_PUB.Count_And_Get (
286 -- p_encoded => FND_API.G_FALSE,
287 p_count => x_msg_count,
288 p_data => x_msg_data
289 );
290
291 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
292 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
293 -- Standard call to get message count and if count=1, get the message
294 FND_msg_PUB.Count_And_Get (
295 --p_encoded => FND_API.G_FALSE,
296 p_count => x_msg_count,
297 p_data => x_msg_data
298 );
299
300 WHEN OTHERS THEN
301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
302 IF FND_msg_PUB.Check_msg_Level ( FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
303 THEN
304 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
305 END IF;
306 -- Standard call to get message count and if count=1, get the message
307 FND_msg_PUB.Count_And_Get (
308 -- p_encoded => FND_API.G_FALSE,
309 p_count => x_msg_count,
310 p_data => x_msg_data
311 );
312 */
313
314 END LOG_HISTORY;
315
316 --------------------------------------------------------------------------------------------------
317 -- This procedure will excute when data is loaded for the first time, and run the program incrementally.
318
319 -- PROCEDURE RESPONSES_FACTS_LOAD
320 --------------------------------------------------------------------------------------------------
321
322 PROCEDURE RESPONSES_FACTS_LOAD
323 ( p_start_date IN DATE
324 ,p_end_date IN DATE
325 ,p_api_version_number IN NUMBER
326 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
327 ,x_msg_count OUT NOCOPY NUMBER
328 ,x_msg_data OUT NOCOPY VARCHAR2
329 ,x_return_status OUT NOCOPY VARCHAR2
330 )
331 IS
332 l_user_id NUMBER := FND_GLOBAL.USER_ID();
333 l_start_date DATE;
334 l_end_date DATE;
335 l_last_update_date DATE;
336 l_success VARCHAR2(3);
337 l_wkdt DATE;
338 l_api_version_number CONSTANT NUMBER := 1.0;
339 l_api_name CONSTANT VARCHAR2(30) := 'RESPONSES_FACTS_LOAD';
340 l_def_tablespace VARCHAR2(100);
341 l_index_tablespace VARCHAR2(100);
342 l_oracle_username VARCHAR2(100);
343 l_table_name VARCHAR2(100);
344 l_temp_msg VARCHAR2(100);
345
346 TYPE generic_number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
347
348 TYPE generic_char_table IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
349
350 l_pct_free generic_number_table;
351 l_ini_trans generic_number_table;
352 l_max_trans generic_number_table;
353 l_initial_extent generic_number_table;
354 l_next_extent generic_number_table;
355 l_min_extents generic_number_table;
356 l_max_extents generic_number_table;
357 l_pct_increase generic_number_table;
358 l_column_position generic_number_table;
359 l_owner generic_char_table;
360 l_uniqueness generic_char_table;
361 l_index_name generic_char_table;
362 l_ind_column_name generic_char_table;
363 l_index_table_name generic_char_table;
364 temp_column_string VARCHAR2(2000);
365 temp_column_position NUMBER;
366 temp_index_name VARCHAR2(1000);
367 is_unique VARCHAR2(30);
368 i NUMBER;
369 l_creation_date DATE;
370
371 l_min_date date;
372 l_org_id number;
373 l_status VARCHAR2(5);
374 l_industry VARCHAR2(5);
375 l_schema VARCHAR2(30);
376 l_return BOOLEAN;
377
378
379 BEGIN
380
381 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
382
383 -- Standard call to check for call compatibility.
384 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
385 p_api_version_number,
386 l_api_name,
387 g_pkg_name)
388 THEN
389 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
390 END IF;
391
392 -- Initialize message list if p_init_msg_list is set to TRUE.
393 IF FND_API.to_Boolean( p_init_msg_list )
394 THEN
395 FND_msg_PUB.initialize;
396 END IF;
397
398 -- Initialize API return status to SUCCESS
399 x_return_status := FND_API.G_RET_STS_SUCCESS;
400
401 -- The below four commands are necessary for the purpose of the parallel insertion */
402
403 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL dml ';
404
405
406
407 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_rgrd_daily_facts nologging ';
408 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_rrsn_daily_facts nologging ';
409
410 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_rgrd_weekly_facts nologging ';
411 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_rrsn_weekly_facts nologging ';
412
413 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_rgrd_daily_facts_s CACHE 1000 ';
414 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_rrsn_daily_facts_s CACHE 1000 ';
415
416 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_rgrd_weekly_facts_s CACHE 1000 ';
417 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_rrsn_weekly_facts_s CACHE 1000 ';
418
419
420 /* Dropping INdexes */
421 BIM_UTL_PKG.DROP_INDEX('BIM_R_RGRD_DAILY_FACTS');
422 BIM_UTL_PKG.DROP_INDEX('BIM_R_RRSN_DAILY_FACTS');
423 BIM_UTL_PKG.DROP_INDEX('BIM_R_RGRD_WEEKLY_FACTS');
424 BIM_UTL_PKG.DROP_INDEX('BIM_R_RRSN_WEEKLY_FACTS');
425
426
427 l_org_id := 204;
428
429 l_table_name := 'BIM_R_RGRD_DAILY_FACTS';
430 fnd_message.set_name('BIM','BIM_R_BEFORE_POPULATE');
431 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
432 fnd_file.put_line(fnd_file.log,fnd_message.get);
433
434 INSERT /*+ append parallel(RDF,1) */
435 INTO bim_r_rgrd_daily_facts RDF
436 (
437 Grade_daily_transaction_id
438 ,creation_date
439 ,last_update_date
440 ,created_by
441 ,last_updated_by
442 ,last_update_login
443 ,Object_Id
444 ,Object_type
445 ,Object_status
446 ,Source_Code
447 ,Source_Code_Id
448 ,Response_Region
449 ,Response_Country
450 ,Business_Unit_Id
451 ,Response_Grade
452 ,Response_Grade_Count
453 ,landing_pad_hits
454 ,survey_completed
455 ,transaction_Create_Date
456 ,weekend_date
457 )
458 SELECT
459 bim_r_rgrd_daily_facts_s.nextval
460 ,sysdate
461 ,sysdate
462 ,-1
463 ,-1
464 ,-1
465 ,d.parent_object_id object_id
466 ,d.parent_object_type object_type
467 ,d.status object_status
468 ,a.source_code source_code
469 ,a.source_code_id source_code_id
470 ,a.region region
471 ,a.country country
472 ,d.business_unit_id business_unit_id
473 ,b.response_grade response_grade
474 ,b.response_grade_count response_grade_count
475 ,a.landing_pad_hits Landing_pad_hits
476 ,a.survey_completed survey_completed
477 ,a.response_create_date response_create_date
478 ,trunc((decode(decode( to_char(response_create_date,'MM') , to_char(next_day(response_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
479 ,'TRUE'
480 ,decode(decode(response_create_date , (next_day(response_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
481 ,'TRUE'
482 ,response_create_date
483 ,'FALSE'
484 ,next_day(response_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
485 ,'FALSE'
486 ,decode(decode(to_char(response_create_date,'MM'),to_char(next_day(response_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
487 ,'FALSE'
488 ,last_day(response_create_date))))) weekend_date
489 FROM
490 bim_r_resp_int_header a,
491 bim_r_resp_int_grades b,
492 bim_r_source_codes d
493 WHERE a.response_create_date >= p_start_date
494 AND a.response_create_date <= p_end_date
495 AND a.object_id = decode(d.object_type,'CAMP',d.parent_object_id,'EVEH',d.parent_object_id,
496 'CSCH',d.object_id,'EVEO',d.object_id)
497 AND a.object_type = d.object_type
498 AND a.interface_header_id = b.interface_header_id;
499
500 l_table_name := 'BIM_R_RGRD_DAILY_FACTS';
501 fnd_message.set_name('BIM','BIM_R_AFTER_POPULATE');
502 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
503 fnd_file.put_line(fnd_file.log,fnd_message.get);
504 -----------
505
506 l_table_name := 'BIM_R_RRSN_DAILY_FACTS';
507 fnd_message.set_name('BIM','BIM_R_BEFORE_POPULATE');
508 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
509 fnd_file.put_line(fnd_file.log,fnd_message.get);
510
511 INSERT /*+ append parallel(RDF,1) */
512 INTO bim_r_rrsn_daily_facts RDF
513 (
514 Reason_daily_transaction_id
515 ,creation_date
516 ,last_update_date
517 ,created_by
518 ,last_updated_by
519 ,last_update_login
520 ,Object_Id
521 ,Object_type
522 ,Object_status
523 ,Source_Code
524 ,Source_Code_Id
525 ,Response_Region
526 ,Response_Country
527 ,Business_Unit_Id
528 ,Invalid_Reason
529 ,Invalid_Responses
530 ,landing_pad_hits
531 ,survey_completed
532 ,transaction_Create_Date
533 ,weekend_date
534 )
535 SELECT
536 bim_r_rrsn_daily_facts_s.nextval
537 ,sysdate
538 ,sysdate
539 ,-1
540 ,-1
541 ,-1
542 ,d.parent_object_id object_id
543 ,d.parent_object_type object_type
544 ,d.status object_status
545 ,a.source_code source_code
546 ,a.source_code_id source_code_id
547 ,a.region region
548 ,a.country country
549 ,d.business_unit_id business_unit_id
550 ,b.invalid_reason invalid_reason
551 ,b.invalid_responses invalid_responses
552 ,a.landing_pad_hits Landing_pad_hits
553 ,a.survey_completed survey_completed
554 ,a.response_create_date response_create_date
555 ,trunc((decode(decode( to_char(response_create_date,'MM') , to_char(next_day(response_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
556 ,'TRUE'
557 ,decode(decode(response_create_date , (next_day(response_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
558 ,'TRUE'
559 ,response_create_date
560 ,'FALSE'
561 ,next_day(response_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
562 ,'FALSE'
563 ,decode(decode(to_char(response_create_date,'MM'),to_char(next_day(response_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
564 ,'FALSE'
565 ,last_day(response_create_date))))) weekend_date
566 FROM
567 bim_r_resp_int_header a,
568 bim_r_resp_int_reason b,
569 bim_r_source_codes d
570 WHERE a.response_create_date >= p_start_date
571 AND a.response_create_date <= p_end_date
572 AND a.object_type = d.object_type
573 AND a.object_id = decode(d.object_type,'CAMP',d.parent_object_id,'EVEH',d.parent_object_id,
574 'CSCH',d.object_id,'EVEO',d.object_id)
575 AND a.interface_header_id = b.interface_header_id;
576
577 ------------
578 l_table_name := 'BIM_R_RRSN_DAILY_FACTS';
579 fnd_message.set_name('BIM','BIM_R_AFTER_POPULATE');
580 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
581 fnd_file.put_line(fnd_file.log,fnd_message.get);
582
583 COMMIT;
584
585 /***************************************************************/
586
587 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
588
589 l_table_name := 'BIM_R_RGRD_DAILY_FACTS';
590 fnd_message.set_name('BIM','BIM_R_ANALYZE_TABLE');
591 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
592 fnd_file.put_line(fnd_file.log,fnd_message.get);
593
594 -- Analyze the daily facts table
595 DBMS_STATS.gather_table_stats('BIM','BIM_R_RGRD_DAILY_FACTS', estimate_percent => 5,
596 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
597
598 l_table_name := 'BIM_R_RRSN_DAILY_FACTS';
599 fnd_message.set_name('BIM','BIM_R_ANALYZE_TABLE');
600 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
601 fnd_file.put_line(fnd_file.log,fnd_message.get);
602
603 -- Analyze the daily facts table
604 DBMS_STATS.gather_table_stats('BIM','BIM_R_RRSN_DAILY_FACTS', estimate_percent => 5,
605 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
606
607 /***************************************************************/
608
609 /* INSERT INTO WEEKLY SUMMARY TABLE */
610
611 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
612
613 EXECUTE IMMEDIATE 'truncate table '||l_schema||'.bim_r_rgrd_weekly_facts';
614 EXECUTE IMMEDIATE 'truncate table '||l_schema||'.bim_r_rrsn_weekly_facts';
615
616 l_table_name := 'BIM_R_RGRD_WEEKLY_FACTS';
617 fnd_message.set_name('BIM','BIM_R_BEFORE_POPULATE');
618 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
619 fnd_file.put_line(fnd_file.log,fnd_message.get);
620
621 /*BEGIN BLOCK FOR THE WEEKLY INSERT */
622 INSERT /*+ append parallel(RWF,1) */
623 INTO bim_r_rgrd_weekly_facts RWF
624 (
625 Grade_Weekly_transaction_id
626 ,creation_date
627 ,last_update_date
628 ,created_by
629 ,last_updated_by
630 ,last_update_login
631 ,Object_Id
632 ,Object_type
633 ,Object_status
634 ,Source_Code
635 ,Source_Code_Id
636 ,Response_Region
637 ,Response_Country
638 ,Business_Unit_Id
639 ,Response_Grade
640 ,weekend_Date
641 ,Response_Grade_Count
642 ,landing_pad_hits
643 ,survey_completed
644 )
645 SELECT
646 bim_r_rgrd_weekly_facts_s.nextval
647 ,sysdate
648 ,sysdate
649 ,-1
650 ,-1
651 ,-1
652 ,Object_Id
653 ,Object_type
654 ,Object_status
655 ,Source_Code
656 ,Source_Code_Id
657 ,Response_Region
658 ,Response_Country
659 ,Business_Unit_Id
660 ,Response_Grade
661 ,weekend_date
662 ,Response_Grade_Count
663 ,landing_pad_hits
664 ,survey_completed
665 FROM (
666 SELECT
667 Object_Id
668 ,Object_type
669 ,Object_status
670 ,Source_Code
671 ,Source_Code_Id
672 ,Response_Region
673 ,Response_Country
674 ,Business_Unit_Id
675 ,Response_Grade
676 ,weekend_date
677 ,sum(Response_Grade_Count) Response_Grade_Count
678 ,sum(landing_pad_hits) Landing_pad_hits
679 ,sum(survey_completed) Survey_Completed
680 FROM bim_r_rgrd_daily_facts
681 GROUP BY
682 Object_Id
683 ,Object_type
684 ,Object_status
685 ,Source_Code
686 ,Source_Code_Id
687 ,Response_Region
688 ,Response_Country
689 ,Business_Unit_Id
690 ,Response_Grade
691 ,weekend_date
692 );
693
694 l_table_name := 'BIM_R_RGRD_WEEKLY_FACTS';
695 fnd_message.set_name('BIM','BIM_R_AFTER_POPULATE');
696 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
697 fnd_file.put_line(fnd_file.log,fnd_message.get);
698
699 ---------------
700
701 l_table_name := 'BIM_R_RRSN_WEEKLY_FACTS';
702 fnd_message.set_name('BIM','BIM_R_BEFORE_POPULATE');
703 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
704 fnd_file.put_line(fnd_file.log,fnd_message.get);
705
706
707 INSERT /*+ append parallel(RWF,1) */
708 INTO bim_r_rrsn_weekly_facts RWF
709 (
710 Reason_Weekly_transaction_id
711 ,creation_date
712 ,last_update_date
713 ,created_by
714 ,last_updated_by
715 ,last_update_login
716 ,Object_Id
717 ,Object_type
718 ,Object_status
719 ,Source_Code
720 ,Source_Code_Id
721 ,Response_Region
722 ,Response_Country
723 ,Business_Unit_Id
724 ,Invalid_Reason
725 ,weekend_Date
726 ,Invalid_Responses
727 ,landing_pad_hits
728 ,survey_completed
729 )
730 SELECT
731 bim_r_rrsn_weekly_facts_s.nextval
732 ,sysdate
733 ,sysdate
734 ,-1
735 ,-1
736 ,-1
737 ,Object_Id
738 ,Object_type
739 ,Object_status
740 ,Source_Code
741 ,Source_Code_Id
742 ,Response_Region
743 ,Response_Country
744 ,Business_Unit_Id
745 ,Invalid_Reason
746 ,weekend_date
747 ,Invalid_Responses
748 ,Landing_Pad_hits
749 ,Survey_Completed
750 FROM (
751 SELECT
752 Object_Id
753 ,Object_type
754 ,Object_status
755 ,Source_Code
756 ,Source_Code_Id
757 ,Response_Region
758 ,Response_Country
759 ,Business_Unit_Id
760 ,Invalid_Reason
761 ,weekend_date
762 ,sum(Invalid_Responses) Invalid_Responses
763 ,sum(landing_pad_hits) Landing_Pad_hits
764 ,sum(survey_completed) Survey_Completed
765 FROM bim_r_rrsn_daily_facts
766 GROUP BY
767 Object_Id
768 ,Object_type
769 ,Object_status
770 ,Source_Code
771 ,Source_Code_Id
772 ,Response_Region
773 ,Response_Country
774 ,Business_Unit_Id
775 ,Invalid_Reason
776 ,weekend_date
777 );
778
779 l_table_name := 'BIM_R_RRSN_WEEKLY_FACTS';
780 fnd_message.set_name('BIM','BIM_R_AFTER_POPULATE');
781 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
782 fnd_file.put_line(fnd_file.log,fnd_message.get);
783
784 LOG_HISTORY('RESPONSE', p_start_date, p_end_date);
785
786 COMMIT;
787
788 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
789
790 l_table_name := 'BIM_R_RGRD_WEEKLY_FACTS';
791 fnd_message.set_name('BIM','BIM_R_ANALYZE_TABLE');
792 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
793 fnd_file.put_line(fnd_file.log,fnd_message.get);
794
795 DBMS_STATS.gather_table_stats('BIM','BIM_R_RGRD_WEEKLY_FACTS', estimate_percent => 5,
796 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
797
798 l_table_name := 'BIM_R_RRSN_WEEKLY_FACTS';
799 fnd_message.set_name('BIM','BIM_R_ANALYZE_TABLE');
800 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
801 fnd_file.put_line(fnd_file.log,fnd_message.get);
802
803 DBMS_STATS.gather_table_stats('BIM','BIM_R_RRSN_WEEKLY_FACTS', estimate_percent => 5,
804 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
805
806
807 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
808 fnd_message.set_name('BIM','BIM_R_RECREATE_INDEXES');
809 fnd_file.put_line(fnd_file.log,fnd_message.get);
810
811 /* Recreating Indexes */
812 BIM_UTL_PKG.CREATE_INDEX('BIM_R_RGRD_DAILY_FACTS');
813 BIM_UTL_PKG.CREATE_INDEX('BIM_R_RRSN_DAILY_FACTS');
814 BIM_UTL_PKG.CREATE_INDEX('BIM_R_RGRD_WEEKLY_FACTS');
815 BIM_UTL_PKG.CREATE_INDEX('BIM_R_RRSN_WEEKLY_FACTS');
816
817 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_rgrd_weekly_facts_s CACHE 20';
818 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_rrsn_weekly_facts_s CACHE 20';
819
820 EXCEPTION
821
822 WHEN FND_API.G_EXC_ERROR THEN
823 x_return_status := FND_API.G_RET_STS_ERROR;
824 -- Standard call to get message count and if count=1, get the message
825 FND_msg_PUB.Count_And_Get (
826 -- p_encoded => FND_API.G_FALSE,
827 p_count => x_msg_count,
828 p_data => x_msg_data
829 );
830
831 ams_utility_pvt.write_conc_log('BIM_RESPONSE_FACTS_PKG:RESPONSES_FACTS_LOAD:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
832
833 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
834
835 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
836
837 -- Standard call to get message count and if count=1, get the message
838 FND_msg_PUB.Count_And_Get (
839 --p_encoded => FND_API.G_FALSE,
840 p_count => x_msg_count,
841 p_data => x_msg_data
842 );
843
844 ams_utility_pvt.write_conc_log('BIM_RESPONSE_FACTS_PKG:RESPONSES_FACTS_LOAD:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
845
846 WHEN OTHERS THEN
847
848 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
849
850 IF FND_msg_PUB.Check_msg_Level (FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
851 THEN
852 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
853 END IF;
854
855 -- Standard call to get message count and if count=1, get the message
856 FND_msg_PUB.Count_And_Get (
857 -- p_encoded => FND_API.G_FALSE,
858 p_count => x_msg_count,
859 p_data => x_msg_data
860 );
861
862 ams_utility_pvt.write_conc_log('BIM_RESPONSE_FACTS_PKG:RESPONSES_FACTS_LOAD:IN OTHERS EXCEPTION '||sqlerrm(sqlcode));
863
864
865 END RESPONSES_FACTS_LOAD;
866
867
868 END BIM_RESPONSE_FACTS_PKG;