[Home] [Help]
PACKAGE BODY: APPS.QPR_COLLECT_TIME_DATA
Source
1 PACKAGE BODY QPR_COLLECT_TIME_DATA AS
2 /* $Header: QPRUCTMB.pls 120.0 2007/10/11 13:09:10 agbennet noship $ */
3
4 request_id number;
5 sys_date date:= sysdate;
6 user_id number:= fnd_global.user_id;
7 login_id number:= fnd_global.conc_login_id;
8 prg_appl_id number:= fnd_global.prog_appl_id;
9 prg_id number:= fnd_global.conc_program_id;
10 nrows number := 1000;
11
12 UNSUP_CAL_TYPE exception;
13 FISCAL_TIME_SRC_TBL varchar2(30):= 'QPR_SR_FISCAL_TIME_V';
14 NLS_DATE_LANG varchar2(50) := 'nls_date_language = AMERICAN';
15 GREG_CAL_DATE_FMT varchar2(15) := 'DD-MON-YYYY';
16
17 type char15_type is table of varchar2(15) index by PLS_INTEGER;
18 type char80_type is table of varchar2(80) index by PLS_INTEGER;
19 type date_type is table of date index by PLS_INTEGER;
20 type time_type is record(CALENDAR_CODE char15_type,
21 YEAR char15_type,
22 YEAR_DESC char80_type,
23 YEAR_STDT date_type,
24 YEAR_ENDT date_type,
25 QUARTER char15_type,
26 QUARTER_DESC char80_type,
27 QUARTER_STDT date_type,
28 QUARTER_ENDT date_type,
29 MONTH char15_type,
30 MONTH_DESC char80_type,
31 MONTH_STDT date_type,
32 MONTH_ENDT date_type,
33 DAY date_type,
34 DAY_DESC char80_type);
35 type fiscal_per_type is record(
36 CALENDAR_CODE char15_type,
37 YEAR char15_type,
38 YEAR_DESC char15_type,
39 YEAR_STDT date_type,
40 YEAR_ENDT date_type,
41 QUARTER char15_type,
42 QUARTER_DESC char15_type,
43 QUARTER_STDT date_type,
44 QUARTER_ENDT date_type,
45 MONTH char15_type,
46 MONTH_DESC char15_type,
47 MONTH_STDT date_type,
48 MONTH_ENDT date_type);
49
50 r_fis_cal fiscal_per_type;
51 r_time_data time_type;
52
53 procedure clean_time_data is
54 begin
55 r_time_data.CALENDAR_CODE.delete;
56 r_time_data.YEAR.delete;
57 r_time_data.YEAR_DESC.delete;
58 r_time_data.YEAR_STDT.delete;
59 r_time_data.YEAR_ENDT.delete;
60 r_time_data.QUARTER.delete;
61 r_time_data.QUARTER_DESC.delete;
62 r_time_data.QUARTER_STDT.delete;
63 r_time_data.QUARTER_ENDT.delete;
64 r_time_data.MONTH.delete;
65 r_time_data.MONTH_DESC.delete;
66 r_time_data.MONTH_STDT.delete;
67 r_time_data.MONTH_ENDT.delete;
68 r_time_data.DAY.delete;
69 r_time_data.DAY_DESC.delete;
70 end clean_time_data;
71
72 procedure clean_fiscal_data is
73 begin
74 r_fis_cal.CALENDAR_CODE.delete;
75 r_fis_cal.YEAR.delete;
76 r_fis_cal.YEAR_DESC.delete;
77 r_fis_cal.YEAR_STDT.delete;
78 r_fis_cal.YEAR_ENDT.delete;
79 r_fis_cal.QUARTER.delete;
80 r_fis_cal.QUARTER_DESC.delete;
81 r_fis_cal.QUARTER_STDT.delete;
82 r_fis_cal.QUARTER_ENDT.delete;
83 r_fis_cal.MONTH.delete;
84 r_fis_cal.MONTH_DESC.delete;
85 r_fis_cal.MONTH_STDT.delete;
86 r_fis_cal.MONTH_ENDT.delete;
87 end clean_fiscal_data;
88
89 procedure insert_time_data(p_instance_id in number,
90 p_cal_type in number) is
91 begin
92 forall i in r_time_data.YEAR.first..r_time_data.YEAR.last
93 insert into QPR_TIME(TIME_ID,INSTANCE_ID,CALENDAR_TYPE, CALENDAR_CODE,
94 YEAR,YEAR_DESCRIPTION,YEAR_START_DATE,
95 YEAR_END_DATE,QUARTER,QUARTER_DESCRIPTION,
96 QUARTER_START_DATE,QUARTER_END_DATE,MONTH,
97 MONTH_DESCRIPTION,MONTH_START_DATE,MONTH_END_DATE, DAY,
98 DAY_DESCRIPTION,CREATION_DATE, CREATED_BY,
99 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
100 PROGRAM_APPLICATION_ID,PROGRAM_ID,REQUEST_ID)
101 values(QPR_TIME_S.nextval, p_instance_id, p_cal_type,
102 r_time_data.CALENDAR_CODE(i), r_time_data.YEAR(i),
103 r_time_data.YEAR_DESC(i),
104 r_time_data.YEAR_STDT(i),
105 r_time_data.YEAR_ENDT(i),
106 r_time_data.QUARTER(i),
107 r_time_data.QUARTER_DESC(i),
108 r_time_data.QUARTER_STDT(i),
109 r_time_data.QUARTER_ENDT(i),
110 r_time_data.MONTH(i),
111 r_time_data.MONTH_DESC(i),
112 r_time_data.MONTH_STDT(i),
113 r_time_data.MONTH_ENDT(i),
114 r_time_data.DAY(i), r_time_data.DAY_DESC(i),
115 sys_date, user_id, sys_date, user_id,login_id,
116 prg_appl_id, prg_id, request_id);
117 clean_time_data;
118 exception
119 when OTHERS then
120 fnd_file.put_line(fnd_file.log, 'ERROR INSERTING TIME DATA');
121 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
122 raise;
123 end insert_time_data;
124
125 procedure explode_fiscal_cal(p_instance_id in number) is
126 prev_year varchar2(15) := '';
127 prev_cal_code varchar2(15);
128 l_seq_num PLS_INTEGER;
129 l_num_days PLS_INTEGER;
130 l_rec_ctr PLS_INTEGER := 0;
131 begin
132 for i in r_fis_cal.YEAR.first..r_fis_cal.YEAR.last loop
133 if prev_year is not null and prev_year <> r_fis_cal.YEAR(i) then
134 fnd_file.put_line(fnd_file.log,
135 'Inserting values for fiscal year ' || prev_year || ' of calendar code '
136 || prev_cal_code);
137 insert_time_data(p_instance_id, FISCAL_CALENDAR);
138 l_rec_ctr := 0;
139 end if;
140 prev_year := r_fis_cal.YEAR(i);
141 prev_cal_code := r_fis_cal.CALENDAR_CODE(i);
142 l_num_days := r_fis_cal.MONTH_ENDT(i) - r_fis_cal.MONTH_STDT(i);
143 for j in 0..l_num_days loop
144 l_rec_ctr := l_rec_ctr + 1;
145 r_time_data.CALENDAR_CODE(l_rec_ctr) := r_fis_cal.CALENDAR_CODE(i);
146 r_time_data.YEAR(l_rec_ctr) := r_fis_cal.YEAR(i);
147 r_time_data.YEAR_DESC(l_rec_ctr) := r_fis_cal.YEAR_DESC(i);
148 r_time_data.YEAR_STDT(l_rec_ctr) := r_fis_cal.YEAR_STDT(i);
149 r_time_data.YEAR_ENDT(l_rec_ctr) := r_fis_cal.YEAR_ENDT(i);
150 r_time_data.QUARTER(l_rec_ctr) := r_fis_cal.QUARTER(i);
151 r_time_data.QUARTER_DESC(l_rec_ctr) := r_fis_cal.QUARTER_DESC(i);
152 r_time_data.QUARTER_STDT(l_rec_ctr) := r_fis_cal.QUARTER_STDT(i);
153 r_time_data.QUARTER_ENDT(l_rec_ctr) := r_fis_cal.QUARTER_ENDT(i);
154 r_time_data.MONTH(l_rec_ctr) := r_fis_cal.MONTH(i);
155 r_time_data.MONTH_DESC(l_rec_ctr) := r_fis_cal.MONTH_DESC(i);
156 r_time_data.MONTH_STDT(l_rec_ctr) := r_fis_cal.MONTH_STDT(i);
157 r_time_data.MONTH_ENDT(l_rec_ctr) := r_fis_cal.MONTH_ENDT(i);
158 r_time_data.DAY(l_rec_ctr) := r_fis_cal.MONTH_STDT(i) + j;
159 r_time_data.DAY_DESC(l_rec_ctr) := r_fis_cal.MONTH_STDT(i) + j;
160 end loop;
161 end loop;
162 if prev_year = r_fis_cal.YEAR(r_fis_cal.YEAR.last) then
163 fnd_file.put_line(fnd_file.log,
164 'Inserting values for fiscal year ' || prev_year || ' of calendar code '
165 || prev_cal_code);
166 insert_time_data(p_instance_id, FISCAL_CALENDAR);
167 end if;
168 exception
169 when OTHERS then
170 fnd_file.put_line(fnd_file.log, 'ERROR EXPLODING FISCAL CALENDAR VALUES');
171 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
172 raise;
173 end explode_fiscal_cal;
174
175 procedure decode_quarter(p_month in varchar2, p_year in varchar2,
176 p_quarter out nocopy varchar2,
177 p_stdt out nocopy varchar2,
178 p_etdt out nocopy varchar2) is
179 begin
180
181 if (p_month = '01' or p_month = '02' or p_month = '03') then
182 p_quarter := 'Qtr 1';
183 p_stdt := '01-JAN-';
184 p_etdt := '31-MAR-';
185 elsif (p_month = '04' or p_month ='05' or p_month ='06') then
186 p_quarter := 'Qtr 2';
187 p_stdt := '01-APR-';
188 p_etdt := '30-JUN-';
189 elsif (p_month ='07' or p_month ='08' or p_month ='09') then
190 p_quarter := 'Qtr 3';
191 p_stdt := '01-JUL-';
192 p_etdt := '30-SEP-';
193 elsif (p_month ='10' or p_month ='11' or p_month ='12') then
194 p_quarter := 'Qtr 4';
195 p_stdt := '01-OCT-';
196 p_etdt := '31-DEC-';
197 else
198 p_quarter := '';
199 p_stdt := '';
200 p_etdt := '';
201 end if;
202 p_quarter := p_quarter || ' ' || p_year;
203 p_stdt := p_stdt || p_year;
204 p_etdt := p_etdt || p_year;
205 end decode_quarter;
206
207 procedure decode_month(p_month in varchar2, p_year in varchar2,
208 p_stdt out nocopy varchar2,
209 p_etdt out nocopy varchar2) is
210 begin
211 case p_month
212 when '01' then
213 p_stdt := '01-JAN-';
214 p_etdt := '31-JAN-';
215 when '02' then
216 p_stdt :='01-FEB-';
217 if mod(to_number(p_year),4)=0 then
218 p_etdt := '29-FEB-';
219 else
220 p_etdt := '28-FEB-';
221 end if;
222 when '03' then
223 p_stdt :='01-MAR-';
224 p_etdt := '31-MAR-';
225 when '04' then
226 p_stdt := '01-APR-';
227 p_etdt := '30-APR-';
228 when '05' then
229 p_stdt := '01-MAY-';
230 p_etdt := '31-MAY-';
231 when '06' then
232 p_stdt := '01-JUN-';
233 p_etdt := '30-JUN-';
234 when '07' then
235 p_stdt := '01-JUL-';
236 p_etdt := '31-JUL-';
237 when '08' then
238 p_stdt := '01-AUG-';
239 p_etdt := '31-AUG-';
240 when '09' then
241 p_stdt := '01-SEP-';
242 p_etdt := '30-SEP-';
243 when '10' then
244 p_stdt := '01-OCT-';
245 p_etdt := '31-OCT-';
246 when '11' then
247 p_stdt := '01-NOV-';
248 p_etdt := '30-NOV-';
249 when '12' then
250 p_stdt := '01-DEC-';
251 p_etdt := '31-DEC-';
252 else
253 p_stdt := '';
254 p_etdt := '';
255 end case;
256 p_stdt := p_stdt || p_year;
257 p_etdt := p_etdt || p_year;
258 end decode_month;
259
260 procedure build_gregorian_cal(p_date_from in date,
261 p_date_to in date) is
262 l_num_days PLS_INTEGER;
263 l_loop_ctr PLS_INTEGER;
264 l_low_limit PLS_INTEGER;
265 l_upper_limit PLS_INTEGER;
266 l_rec_ctr PLS_INTEGER;
267 date_ref date;
268 s_year varchar2(4);
269 s_month varchar2(4);
270 s_mon_name varchar2(20);
271 s_qtr varchar2(15);
272 s_stdt varchar2(15);
273 s_etdt varchar2(15);
274 begin
275 fnd_file.put_line(fnd_file.log,
276 'Building Gregorian Calendar for dates between ' || p_date_from || ' and '||
277 p_date_to);
278 l_num_days := (p_date_to - p_date_from) + 1;
279 l_loop_ctr := ceil(l_num_days/nrows);
280 for i in 1..l_loop_ctr loop
281 if l_num_days < nrows then
282 l_upper_limit := l_num_days -1;
283 else
284 l_upper_limit := nrows-1;
285 end if;
286 l_num_days := l_num_days - nrows;
287 l_rec_ctr := 0;
288 for j in 0..l_upper_limit loop
289 l_rec_ctr := l_rec_ctr + 1;
290 date_ref := p_date_from + j + ((i-1) * nrows);
291
292 s_year := to_char(date_ref,'YYYY', NLS_DATE_LANG);
293 r_time_data.CALENDAR_CODE(l_rec_ctr) := 'Gregorian';
294 r_time_data.YEAR(l_rec_ctr) := s_year;
295 r_time_data.YEAR_DESC(l_rec_ctr) := s_year;
296 r_time_data.YEAR_STDT(l_rec_ctr) := fnd_date.string_to_date(
297 '01-JAN-'|| s_year,GREG_CAL_DATE_FMT);
298 r_time_data.YEAR_ENDT(l_rec_ctr) := fnd_date.string_to_date(
299 '31-DEC-'||s_year,GREG_CAL_DATE_FMT);
300
301 s_month := to_char(date_ref,'MM', NLS_DATE_LANG);
302 decode_quarter(s_month,s_year, s_qtr, s_stdt, s_etdt);
303 r_time_data.QUARTER(l_rec_ctr) := s_qtr;
304 r_time_data.QUARTER_DESC(l_rec_ctr) := s_qtr;
305 r_time_data.QUARTER_STDT(l_rec_ctr) := fnd_date.string_to_date(s_stdt,
306 GREG_CAL_DATE_FMT);
307 r_time_data.QUARTER_ENDT(l_rec_ctr) := fnd_date.string_to_date(s_etdt,
308 GREG_CAL_DATE_FMT);
309
310 s_mon_name := to_char(date_ref,'MON', NLS_DATE_LANG) || ' ' || s_year;
311 decode_month(s_month, s_year, s_stdt, s_etdt);
312 r_time_data.MONTH(l_rec_ctr) := s_mon_name;
313 r_time_data.MONTH_DESC(l_rec_ctr) := s_mon_name;
314 r_time_data.MONTH_STDT(l_rec_ctr) := fnd_date.string_to_date(s_stdt,
315 GREG_CAL_DATE_FMT);
316 r_time_data.MONTH_ENDT(l_rec_ctr) := fnd_date.string_to_date(s_etdt,
317 GREG_CAL_DATE_FMT);
318
319 r_time_data.DAY(l_rec_ctr) := date_ref;
320 r_time_data.DAY_DESC(l_rec_ctr) := to_char(date_ref, GREG_CAL_DATE_FMT,
321 NLS_DATE_LANG);
322 end loop;
323 insert_time_data(-1, GREGORIAN_CALENDAR);
324 end loop;
325 exception
326 when OTHERS then
327 fnd_file.put_line(fnd_file.log, 'ERROR BUILDING GREGORIAN CALENDAR');
328 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
329 raise;
330 end build_gregorian_cal;
331
332 procedure collect_time_data(errbuf out nocopy varchar2,
333 retcode out nocopy number,
334 p_instance_id in number,
335 p_calendar_type in number,
336 p_calendar_code in varchar2,
337 p_date_from in varchar2,
338 p_date_to in varchar2) is
339 bfound boolean := false;
340 date_from date;
341 date_to date;
342 s_sql varchar2(1000);
343 src_table varchar2(200);
344 c_get_cal_data SYS_REFCURSOR;
345 l_sql_check_cal varchar2(1000);
346 l_count_cal number;
347 l_gl_table varchar2(200);
348 c_check_cal_code SYS_REFCURSOR;
349 begin
350 fnd_profile.get('CONC_REQUEST_ID', request_id);
351
352 -- Note: the date inputs are mandatory if not null condition needs to be
353 -- handled
354 date_from := FND_DATE.canonical_to_date(p_date_from);
355 date_to := FND_DATE.canonical_to_date(p_date_to);
356
357
358 if p_calendar_type = GREGORIAN_CALENDAR then
359 delete QPR_TIME
360 where CALENDAR_TYPE = GREGORIAN_CALENDAR
361 and day between date_from and date_to;
362
363 build_gregorian_cal(date_from, date_to);
364 elsif p_calendar_type = FISCAL_CALENDAR then
365
366 -- Added to resolve bug 5920571
367 l_gl_table := 'GL_PERIODS' || qpr_sr_util.get_dblink(p_instance_id);
368
369 l_sql_check_cal := 'select count(period_set_name) from ';
370 l_sql_check_cal := l_sql_check_cal || l_gl_table;
371 l_sql_check_cal := l_sql_check_cal || ' where period_set_name = ''';
372 l_sql_check_cal := l_sql_check_cal || p_calendar_code || '''';
373
374 open c_check_cal_code for l_sql_check_cal;
375 fetch c_check_cal_code into l_count_cal;
376 close c_check_cal_code;
377
378 if (l_count_cal = 0) then
379 fnd_file.put_line(fnd_file.log, 'There does not exist calendar: '||p_calendar_code||' for the specified instance.');
380 retcode := 1;
381 end if;
382
383 -- End of modification to handle bug 5920571
384
385 -- delete the old data from target table.
386 if p_calendar_code is null then
387 delete QPR_TIME
388 where INSTANCE_ID = p_instance_id
389 and CALENDAR_TYPE = FISCAL_CALENDAR
390 and day between date_from and date_to;
391 else
392 delete QPR_TIME
393 where INSTANCE_ID = p_instance_id
394 and CALENDAR_TYPE = FISCAL_CALENDAR
395 and CALENDAR_CODE = p_calendar_code
396 and day between date_from and date_to;
397 end if;
398
399 src_table := FISCAL_TIME_SRC_TBL || qpr_sr_util.get_dblink(p_instance_id);
400
401 s_sql := 'select CALENDAR_CODE, YEAR,YEAR_DESCRIPTION,YEAR_START_DATE, ';
402 s_sql := s_sql || 'YEAR_END_DATE,QUARTER,QUARTER_DESCRIPTION, ';
403 s_sql := s_sql || 'QUARTER_START_DATE,QUARTER_END_DATE,MONTH, ' ;
404 s_sql := s_sql || 'MONTH_DESCRIPTION,MONTH_START_DATE,MONTH_END_DATE from ';
405 s_sql := s_sql || src_table;
406 s_sql := s_sql || ' where MONTH_END_DATE between :1 and :2' ;
407 if p_calendar_code is null then
408 open c_get_cal_data for s_sql using date_from, date_to;
409 else
410 s_sql := s_sql || ' and CALENDAR_CODE = :3 ' ;
411 open c_get_cal_data for s_sql using date_from, date_to, p_calendar_code;
412 end if;
413 loop
414 fetch c_get_cal_data bulk collect into r_fis_cal limit nrows;
415 exit when r_fis_cal.YEAR.count = 0;
416 explode_fiscal_cal(p_instance_id);
417 clean_fiscal_data;
418 bfound := true;
419 end loop;
420 close c_get_cal_data;
421 if bfound = false then
422 fnd_file.put_line(fnd_file.log,
423 'No data retrieved from source for given date range and calendar code');
424 end if;
425 else
426 raise UNSUP_CAL_TYPE;
427 end if;
428 commit;
429 exception
430 when UNSUP_CAL_TYPE then
431 retcode := -1;
432 errbuf := 'ERROR: UNSUPPORTED CALENDAR TYPE';
433 fnd_file.put_line(fnd_file.log, ' UNSUPPORTED CALENDAR TYPE');
434 when OTHERS then
435 retcode := -1;
436 errbuf := 'ERROR: ' || substr(sqlerrm, 1, 1000);
437 fnd_file.put_line(fnd_file.log, substr(sqlerrm, 1, 1000));
438 fnd_file.put_line(fnd_file.log, 'CANNOT POPULATE CALENDAR DATA');
439 rollback;
440 end collect_time_data;
441 END;
442
443