[Home] [Help]
PACKAGE BODY: APPS.MSD_PULL_TIME_DATA
Source
1 PACKAGE BODY MSD_PULL_TIME_DATA AS
2 /* $Header: msdptimb.pls 115.4 2002/10/28 21:33:54 dkang ship $ */
3
4
5
6
7
8 procedure pull_time_data(
9 errbuf OUT NOCOPY VARCHAR2,
10 retcode OUT NOCOPY VARCHAR2) IS
11 x_source_table VARCHAR2(50) := MSD_COMMON_UTILITIES.TIME_STAGING_TABLE ;
12 x_dest_table VARCHAR2(50) := MSD_COMMON_UTILITIES.TIME_FACT_TABLE ;
13 /*********************************************************************
14 Cursor to get distinct Instance, Calendar_Code Max Data and Min Date
15 *********************************************************************/
16 /* DWK. Fix Bug 2220983. Do not include instance = '0' in the cursor */
17 Cursor Calendar is
18 select instance,
19 calendar_code,
20 calendar_type,
21 min(day) min_day,
22 max(day) max_day
23 from msd_st_time
24 where instance <> '0'
25 group by instance, calendar_code, calendar_type;
26 Begin
27
28
29 retcode :=0;
30
31 For Calendar_Rec IN Calendar LOOP
32
33
34 MSD_TRANSLATE_TIME_DATA.translate_time_data(
35 errbuf => errbuf,
36 retcode => retcode,
37 p_source_table => x_source_table,
38 p_dest_table => x_dest_table,
39 p_instance_id => Calendar_Rec.instance,
40 p_calendar_type_id => Calendar_Rec.calendar_type,
41 p_calendar_code => Calendar_Rec.calendar_code,
42 p_from_date => Calendar_Rec.min_day,
43 p_to_date => Calendar_Rec.max_day ) ;
44
45
46 Delete from msd_st_time
47 where instance = Calendar_Rec.instance
48 and calendar_code = Calendar_Rec.calendar_code
49 and calendar_type = Calendar_Rec.calendar_type
50 and day between Calendar_Rec.min_day and Calendar_Rec.max_day ;
51
52 commit ;
53
54 End Loop ;
55
56 /* Added by esubrama */
57 MSD_ANALYZE_TABLES.analyze_table('MSD_TIME',null);
58 MSD_ANALYZE_TABLES.analyze_table('MSD_ST_TIME',null);
59
60 exception
61
62 when others then
63
64 errbuf := substr(SQLERRM,1,150);
65 retcode := -1 ;
66
67
68 End pull_time_data ;
69
70
71 END MSD_PULL_TIME_DATA ;