[Home] [Help]
PACKAGE BODY: APPS.MSD_DEM_TIME_DATA
Source
1 PACKAGE BODY MSD_DEM_TIME_DATA AS
2 /* $Header: msddemcalb.pls 120.4 2012/01/05 12:38:01 nallkuma ship $ $ */
3
4 Procedure msd_dem_fix_manufacturing(
5 p_cal_code in varchar2) is
6
7 -- distinct weeks by calendar
8 cursor weeks is
9 select distinct
10 week_start_date sd,
11 week_end_date ed,
12 week,
13 week_description,
14 month,
15 month_description,
16 calendar_code,
17 month_start_date,
18 month_end_date,
19 instance
20 from msd_dem_time
21 where calendar_type = 2
22 and calendar_code = nvl(p_cal_code, calendar_code);
23
24 begin
25
26 MSD_DEM_COMMON_UTILITIES.LOG_DEBUG('In Procedure: MSD_DEM_FIX_MANUFACTURING');
27
28
29 MSD_DEM_COMMON_UTILITIES.LOG_DEBUG('Start Time: '|| to_char(SYSTIMESTAMP,'DD-MM-YYYY HH24:MI:SS'));
30
31 for week in weeks loop
32
33 -- insert missing days in this week
34 insert into msd_dem_time(INSTANCE, CALENDAR_TYPE, CALENDAR_CODE,
35 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
36 CREATED_BY, LAST_UPDATE_LOGIN,
37 MONTH, MONTH_DESCRIPTION,
38 MONTH_START_DATE, MONTH_END_DATE,
39 WEEK, WEEK_DESCRIPTION,
40 WEEK_START_DATE, WEEK_END_DATE,
41 DAY, DAY_DESCRIPTION)
42 select week.instance, 2, week.calendar_code,
43 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, fnd_global.user_id,
44 week.month, week.month_description,
45 week.month_start_date, week.month_end_date,
46 week.week, week.week_description,
47 week.sd, week.ed,
48 day, to_char(day)
49 from
50 (
51 select week.sd+rownum-1 day
52 from msd_dem_time
53 where rownum < week.ed-week.sd+2
54 MINUS
55 select day
56 from msd_dem_time
57 where calendar_type = 2
58 and calendar_code = week.calendar_code
59 and week_start_date = week.sd
60 and week_end_date = week.ed
61 );
62 end loop;
63
64 MSD_DEM_COMMON_UTILITIES.LOG_DEBUG('End Time: '|| to_char(SYSTIMESTAMP,'DD-MM-YYYY HH24:MI:SS'));
65
66 end msd_dem_fix_manufacturing;
67
68
69
70 --
71
72 Procedure collect_time_data(
73 errbuf OUT NOCOPY VARCHAR2,
74 retcode OUT NOCOPY VARCHAR2,
75 P_AUTO_RUN_DOWNLOAD IN NUMBER )
76 IS
77
78
79 cursor c_cals is
80 select mai.instance_id instance, mdc.calendar_type calendar_type, mdc.calendar_code calendar_code
81 from msd_dem_calendars mdc,
82 msc_apps_instances mai
83 where mdc.instance = mai.instance_code;
84
85 cursor c_fiscal_cal_data(p_instance_id number, P_calendar_code varchar2) is
86 select
87 SR_INSTANCE_ID,
88 calendar_code,
89 YEAR,
90 YEAR_START_DATE,
91 YEAR_END_DATE,
92 QUARTER,
93 QUARTER_START_DATE,
94 QUARTER_END_DATE,
95 MONTH,
96 MONTH_START_DATE,
97 MONTH_END_DATE
98 from msc_calendar_months
99 where sr_instance_id = p_instance_id
100 and calendar_code = p_calendar_code;
101
102 cursor get_lowest_level is
103 select msd_dem_common_utilities.dm_time_level
104 from dual;
105
106 v_num_of_days number :=0;
107
108 l_instance_code varchar2(30);
109 l_calendar_code varchar2(30);
110 l_calendar_type number;
111
112 l_lowest_level varchar2(30);
113
114 l_stmt varchar2(5000);
115
116 g_msd_schema_name varchar2(50) := NULL;
117 x_sql VARCHAR2(500) := NULL;
118
119 BEGIN
120
121 MSD_DEM_COMMON_UTILITIES.LOG_DEBUG('In Procedure: COLLECT_TIME_DATA');
122
123
124 if p_auto_run_download <> 1 then
125
126 msd_dem_common_utilities.log_message('Calendars Download Not Selected');
127 msd_dem_common_utilities.log_debug('Calendars Download Not Selected');
128 retcode := 0;
129 return;
130
131 end if;
132
133
134 open get_lowest_level;
135 fetch get_lowest_level into l_lowest_level;
136 close get_lowest_level;
137
138
139 /* Get the msd schema name */
140 x_sql := 'DECLARE x_retval BOOLEAN; x_dummy1 VARCHAR2(50); x_dummy2 VARCHAR2(50); BEGIN x_retval := fnd_installation.get_app_info' || ' ( ''MSD'', x_dummy1, x_dummy2, :x_out1); END;';
141 EXECUTE IMMEDIATE x_sql USING OUT g_msd_schema_name;
142
143 msd_dem_common_utilities.log_debug ('MSD Schema: ' || g_msd_schema_name);
144
145 l_stmt := 'truncate table '|| g_msd_schema_name||'.msd_dem_time';
146
147 msd_dem_common_utilities.log_debug(l_stmt);
148 execute immediate l_stmt ;
149
150 for l_cals in c_cals
151 loop
152
153 l_stmt := null;
154
155
156
157 msd_dem_common_utilities.log_message('Collecting Calendar: '|| l_cals.Calendar_code);
158
159 if(l_cals.calendar_type = 'Fiscal') then
160
161 for l_fiscal_cal_data in c_fiscal_cal_data(l_cals.instance , /*substr(l_cals.calendar_code, instr(l_cals.calendar_code, ':')+1)*/ l_cals.calendar_code)
162 loop
163
164 if l_lowest_level = 'Day' then
165
166 For v_num_of_days in 0..(l_fiscal_cal_data.month_end_date - l_fiscal_cal_data.month_start_date)
167 loop
168
169 insert into msd_dem_time(instance,
170 calendar_type,
171 calendar_code,
172 YEAR,
173 YEAR_DESCRIPTION,
174 YEAR_START_DATE,
175 YEAR_END_DATE,
176 QUARTER,
177 QUARTER_DESCRIPTION,
178 QUARTER_START_DATE,
179 QUARTER_END_DATE,
180 MONTH,
181 MONTH_DESCRIPTION,
182 MONTH_START_DATE,
183 MONTH_END_DATE,
184 DAY,
185 DAY_DESCRIPTION,
186 LAST_UPDATE_DATE,
187 last_updated_by,
188 creation_date,
189 created_by,
190 LAST_UPDATE_LOGIN )
191 values (l_fiscal_cal_data.sr_instance_id,
192 3,
193 l_fiscal_cal_data.calendar_code,
194 /* Bug# 5859307
195 l_fiscal_cal_data.YEAR,
196 */ to_char(l_fiscal_cal_data.YEAR_START_DATE, 'YYYY/MM/DD'),
197 l_fiscal_cal_data.YEAR,
198 l_fiscal_cal_data.YEAR_START_DATE,
199 l_fiscal_cal_data.YEAR_END_DATE,
200 /* Bug# 5859307
201 l_fiscal_cal_data.QUARTER,
202 */ to_char(l_fiscal_cal_data.QUARTER_START_DATE, 'YYYY/MM/DD'),
203 l_fiscal_cal_data.QUARTER,
204 l_fiscal_cal_data.QUARTER_START_DATE,
205 l_fiscal_cal_data.QUARTER_END_DATE,
206 /* Bug# 5859307
207 l_fiscal_cal_data.MONTH,
208 */ to_char(l_fiscal_cal_data.MONTH_START_DATE, 'YYYY/MM/DD'),
209 l_fiscal_cal_data.MONTH,
210 l_fiscal_cal_data.MONTH_START_DATE,
211 l_fiscal_cal_data.MONTH_END_DATE,
212 l_fiscal_cal_data.MONTH_START_DATE + v_num_of_days,
213 l_fiscal_cal_data.MONTH_START_DATE + v_num_of_days,
214 sysdate,
215 FND_GLOBAL.USER_ID ,
216 sysdate,
217 FND_GLOBAL.USER_ID ,
218 FND_GLOBAL.USER_ID
219 ) ;
220 commit;
221
222 end loop ;
223
224 else
225
226 insert into msd_dem_time(instance,
227 calendar_type,
228 calendar_code,
229 YEAR,
230 YEAR_DESCRIPTION,
231 YEAR_START_DATE,
232 YEAR_END_DATE,
233 QUARTER,
234 QUARTER_DESCRIPTION,
235 QUARTER_START_DATE,
236 QUARTER_END_DATE,
237 MONTH,
238 MONTH_DESCRIPTION,
239 MONTH_START_DATE,
240 MONTH_END_DATE,
241 LAST_UPDATE_DATE,
242 last_updated_by,
243 creation_date,
244 created_by,
245 LAST_UPDATE_LOGIN )
246 values (l_fiscal_cal_data.sr_instance_id,
247 3,
248 l_fiscal_cal_data.calendar_code,
249 /* Bug# 5859307
250 l_fiscal_cal_data.YEAR,
251 */ to_char(l_fiscal_cal_data.YEAR_START_DATE, 'YYYY/MM/DD'),
252 l_fiscal_cal_data.YEAR,
253 l_fiscal_cal_data.YEAR_START_DATE,
254 l_fiscal_cal_data.YEAR_END_DATE,
255 /* Bug# 5859307
256 l_fiscal_cal_data.QUARTER,
257 */ to_char(l_fiscal_cal_data.QUARTER_START_DATE, 'YYYY/MM/DD'),
258 l_fiscal_cal_data.QUARTER,
259 l_fiscal_cal_data.QUARTER_START_DATE,
260 l_fiscal_cal_data.QUARTER_END_DATE,
261 /* Bug# 5859307
262 l_fiscal_cal_data.MONTH,
263 */ to_char(l_fiscal_cal_data.MONTH_START_DATE, 'YYYY/MM/DD'),
264 l_fiscal_cal_data.MONTH,
265 l_fiscal_cal_data.MONTH_START_DATE,
266 l_fiscal_cal_data.MONTH_END_DATE,
267 sysdate,
268 FND_GLOBAL.USER_ID ,
269 sysdate,
270 FND_GLOBAL.USER_ID ,
271 FND_GLOBAL.USER_ID
272 ) ;
273 commit;
274
275 end if;
276
277
278 end loop;
279
280 elsif (l_cals.calendar_type = 'Manufacturing') then
281
282 msd_dem_query_utilities.get_query(retcode, l_stmt, 'CAL', null);
283
284 MSD_DEM_COMMON_UTILITIES.LOG_DEBUG(L_STMT);
285
286 MSD_DEM_COMMON_UTILITIES.LOG_DEBUG('Instance: '|| l_cals.instance);
287 MSD_DEM_COMMON_UTILITIES.LOG_DEBUG('Calendar Code: '|| l_cals.calendar_CODE);
288
289 MSD_DEM_COMMON_UTILITIES.LOG_DEBUG('Start Time: '|| to_char(SYSTIMESTAMP,'DD-MM-YYYY HH24:MI:SS'));
290
291 execute immediate l_stmt using l_cals.instance, l_cals.calendar_CODE;
292
293 MSD_DEM_COMMON_UTILITIES.LOG_DEBUG('End Time: '|| to_char(SYSTIMESTAMP,'DD-MM-YYYY HH24:MI:SS'));
294
295
296 if l_lowest_level = 'Day' then
297 msd_dem_fix_manufacturing(l_cals.calendar_CODE);
298 end if;
299
300 commit;
301 END IF;
302 end loop;
303
304
305 l_stmt := 'begin ' || fnd_profile.value('MSD_DEM_SCHEMA') || '.Integration_1_Load_Calendars' ||
306 '(''' || fnd_profile.value('MSD_DEM_FISCAL_CALENDAR') || ''')' ||'; end;';
307
308 msd_dem_common_utilities.log_debug(l_stmt);
309
310 execute immediate 'alter session set current_schema=' || fnd_profile.value('MSD_DEM_SCHEMA');
311
312 execute immediate l_stmt;
313
314 l_stmt := 'alter session set current_schema=APPS';
315 execute immediate l_stmt;
316
317 retcode := 0;
318
319 EXCEPTION
320
321 when others then
322 l_stmt := 'alter session set current_schema=APPS';
323 execute immediate l_stmt;
324 MSD_DEM_COMMON_UTILITIES.LOG_MESSAGE( substr(SQLERRM,1,150));
325 errbuf := substr(SQLERRM,1,150);
326 retcode := -1 ;
327
328 end collect_time_data;
329
330 END MSD_DEM_TIME_DATA;
331