1 PACKAGE BODY OKS_COVERAGE_UTIL_PVT AS
2 /* $Header: OKSRCUTB.pls 120.4 2005/08/09 13:57:22 sasethi noship $ */
3
4 -- Purpose: Utility package for OKS Coverage Times
5 --
6
7 -- Global Constants
8 G_APP_NAME CONSTANT VARCHAR2(5) := 'OKS';
9 G_COV_DEF_TZONE_MSG CONSTANT VARCHAR2(30) := 'OKS_INQ_COV_DEF_TZONE_TXT';
10 G_COV_DEF_TZONE_MSG_TKN CONSTANT VARCHAR2(30) := 'TIMEZONE';
11
12
13 /**
14 * Helper API to check if the Coverage Times for the given Timezone Id has already
15 * been processed and inserted into the GT table within a session.
16 */
17 FUNCTION isTimezoneAlreadyProcessed (cov_timezone_id NUMBER)
18 RETURN VARCHAR2
19 IS
20 l_count number := 0;
21 l_api_name CONSTANT VARCHAR2(30) := 'isTimezoneAlreadyProcessed';
22 BEGIN
23 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
24 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
25 ,'100: Entered isTimezoneAlreadyProcessed');
26 END IF;
27
28 -- check if the Coverage Times for given timezone id has already been inserted into
29 -- GT table.
30 SELECT
31 count(*)
32 into l_count
33 from oks_coverage_times_gt
34 where COV_TZE_LINE_ID = cov_timezone_id;
35
36 -- If Count is greater then 0, return Y
37 IF l_count > 0 THEN
38 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
39 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
40 ,'110: Returning Y');
41 END IF;
42 return 'Y';
43 ELSE
44 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
45 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
46 ,'120: Returning N');
47 END IF;
48 return 'N';
49 END IF;
50
51 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
52 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
53 ,'200: Leaving isTimezoneAlreadyProcessed');
54 END IF;
55 END; -- End of isTimezoneAlreadyProcessed
56
57 /**
58 * Helper API to find out if the given Start and End time lies between the given
59 * Coverage Start and End Time
60 */
61 FUNCTION intervals_match
62 (start_time IN NUMBER,
63 end_time IN NUMBER,
64 cov_start_time IN NUMBER,
65 cov_end_time IN NUMBER
66 ) RETURN VARCHAR2
67 IS
68 l_api_name CONSTANT VARCHAR2(30) := 'intervals_match';
69 BEGIN
70 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
71 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
72 ,'100: Entering intervals_match');
73 END IF;
74
75 IF start_time >= cov_start_time AND end_time <= cov_end_time THEN
76 return 'Y';
77 ELSE
78 return 'N';
79 END IF;
80 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
81 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
82 ,'200: Leaving intervals_match');
83 END IF;
84 END; -- End of intervals_match
85
86 /**
87 * Procedure to process new Coverage Times into a PL/SQL record := new_coverage_times_rec
88 * and return back the record.
89 * @param flattened_st_end_limit_recs PL/SQL table structure of Flattened Start and End times
90 * @param coverage_times_recs Actual Coverage Times records from OKS_COVERAGE_TIMES loaded
91 * into this PL/SQL table structure
92 * @param new_coverage_times_rec New Coverage Times PL/SQL Record returned to the calling
93 * API (init_coverage_times_view)
94 */
95 PROCEDURE process_new_coverage_times(
96 flattened_st_end_limit_recs IN flattened_time_limits_TBL,
97 coverage_times_recs IN ui_coverage_times_tbl,
98 new_coverage_times_rec OUT NOCOPY ui_coverage_times_rec,
99 x_msg_data OUT NOCOPY VARCHAR2,
100 x_msg_count OUT NOCOPY NUMBER,
101 x_return_status OUT NOCOPY VARCHAR2)
102 IS
103 -- Procedure Name ussed for loggin
104 l_api_name CONSTANT VARCHAR2(30) := 'process_new_coverage_times';
105 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
106 l_intervals_match_flag VARCHAR2(1) := 'N';
107
108 BEGIN
109 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
110 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
111 ,'100: Entered process_new_coverage_times');
112 END IF;
113
114 -- Loop thru Flattened Start and End Times PL/SQL Table structure
115 IF flattened_st_end_limit_recs.COUNT > 0 THEN
116 -- Record current record's Start and Times, one concatenated with ":"
117 -- and other just like 2243, 2359 for computation
118 new_coverage_times_rec.start_time :=
119 flattened_st_end_limit_recs(0).concatenate_time;
120 new_coverage_times_rec.end_time :=
121 flattened_st_end_limit_recs(1).concatenate_time;
122 new_coverage_times_rec.start_hour_minute :=
123 flattened_st_end_limit_recs(0).time;
124 new_coverage_times_rec.end_hour_minute :=
125 flattened_st_end_limit_recs(1).time;
126
127 -- Default settings of New Coverage Times for Days of Week
128 new_coverage_times_rec.monday_yn := 'N';
129 new_coverage_times_rec.tuesday_yn := 'N';
130 new_coverage_times_rec.wednesday_yn := 'N';
131 new_coverage_times_rec.thursday_yn := 'N';
132 new_coverage_times_rec.friday_yn := 'N';
133 new_coverage_times_rec.saturday_yn := 'N';
134 new_coverage_times_rec.sunday_yn := 'N';
135
136 -- loop thru coverage_times_recs, identify if the new Start-End Time interval
137 -- exists in the given Coverage Time Interval, If found, process new Coverage
138 -- Times as per the Covered Days in the given Coverage time interval
139 IF coverage_times_recs.COUNT > 0 THEN
140 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
141 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,
142 '103: Number of records in coverage_times_recs :'||
143 to_char(coverage_times_recs.COUNT));
144 END IF;
145 FOR i IN coverage_times_recs.FIRST..coverage_times_recs.LAST LOOP
146
147 -- check if given start_time and end_time exists between the current
148 -- coverage_times interval
149 l_intervals_match_flag := intervals_match(
150 start_time => flattened_st_end_limit_recs(0).time,
151 end_time => flattened_st_end_limit_recs(1).time,
152 cov_start_time => coverage_times_recs(i).start_hour_minute,
153 cov_end_time => coverage_times_recs(i).end_hour_minute);
154
155 -- intiailize New Coverage Times record with the Coverage Timezone Id
156 new_coverage_times_rec.COV_TZE_LINE_ID := coverage_times_recs(i).COV_TZE_LINE_ID;
157
158 -- If Intervals match, create new coverage times
159 IF ( l_intervals_match_flag = 'Y' ) THEN
160
161 -- set Covered Days Flags
162 IF (new_coverage_times_rec.monday_yn = 'N') THEN
163 new_coverage_times_rec.monday_yn := coverage_times_recs(i).monday_yn;
164 END IF;
165 IF (new_coverage_times_rec.tuesday_yn = 'N') THEN
166 new_coverage_times_rec.tuesday_yn := coverage_times_recs(i).tuesday_yn;
167 END IF;
168 IF (new_coverage_times_rec.wednesday_yn = 'N') THEN
169 new_coverage_times_rec.wednesday_yn := coverage_times_recs(i).wednesday_yn;
170 END IF;
171 IF (new_coverage_times_rec.thursday_yn = 'N') THEN
172 new_coverage_times_rec.thursday_yn := coverage_times_recs(i).thursday_yn;
173 END IF;
174 IF (new_coverage_times_rec.friday_yn = 'N') THEN
175 new_coverage_times_rec.friday_yn := coverage_times_recs(i).friday_yn;
176 END IF;
177 IF (new_coverage_times_rec.saturday_yn = 'N') THEN
178 new_coverage_times_rec.saturday_yn := coverage_times_recs(i).saturday_yn;
179 END IF;
180 IF (new_coverage_times_rec.sunday_yn = 'N') THEN
181 new_coverage_times_rec.sunday_yn := coverage_times_recs(i).sunday_yn;
182 END IF;
183 END IF; -- IF ( l_intervals_match_flag = 'Y' )
184 END LOOP;
185 END IF; -- IF coverage_times_recs.COUNT > 0
186 END IF;
187 -- set return status as success
188 x_return_status := l_return_status;
189
190 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
191 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
192 ,'200: Leaving process_new_coverage_times');
193 END IF;
194 END; -- END OF process_new_coverage_times
195
196 /**
197 * Procedure invoked by the Middle-Tier application to initialize the GT table
198 * with the new Coverage Times processed in the database.
199 * @param cov_timezone_id Coverage Timezone Id
200 */
201 PROCEDURE init_coverage_times_view
202 ( p_api_version IN NUMBER,
203 p_init_msg_list IN VARCHAR2,
204 cov_timezone_id IN NUMBER,
205 x_msg_data OUT NOCOPY VARCHAR2,
206 x_msg_count OUT NOCOPY NUMBER,
207 x_return_status OUT NOCOPY VARCHAR2)
208 IS
209 -- Procedure Name ussed for loggin
210 l_api_name CONSTANT VARCHAR2(30) := 'init_coverage_times_view';
211 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
212
213 CURSOR flattened_time_limits_cur IS
214 SELECT
215 to_number(START_HOUR||decode(LENGTH(START_MINUTE),1,'0'||START_MINUTE,START_MINUTE)) time,
216 to_char(to_date(start_hour||':'||start_minute, 'HH24:MI'), 'HH24:MI') concatenate_time
217 FROM OKS_COVERAGE_TIMES
218 WHERE
219 COV_TZE_LINE_ID = cov_timezone_id
220 UNION ALL
221 SELECT to_number(END_HOUR||decode(LENGTH(END_MINUTE),1,'0'||END_MINUTE,END_MINUTE)) time,
222 to_char(to_date(end_hour||':'||end_minute, 'HH24:MI'), 'HH24:MI') concatenate_time
223 FROM OKS_COVERAGE_TIMES
224 WHERE
225 COV_TZE_LINE_ID = cov_timezone_id
226 ORDER BY time;
227
228 -- Declaration of original Coverage Times table, read into this PL/SQL table
229 coverage_times_recs ui_coverage_times_tbl;
230
231 -- Declaration of original Coverage Times table, write into this PL/SQL table
232 new_coverage_times_rec ui_coverage_times_rec;
233
234 -- Declaration of original Coverage Times table, write into this PL/SQL table
235 new_coverage_times_recs ui_coverage_times_tbl;
236
237 -- Declaration of Flattened time limits table
238 flattened_time_limits_recs flattened_time_limits_TBL;
239
240 -- Enter the procedure variables here. As shown below
241 start_time NUMBER := -1;
242 end_time NUMBER := -1;
243 flattened_st_end_limit_recs flattened_time_limits_TBL;
244 j PLS_INTEGER := 0;
245 is_timezone_exists VARCHAR2(1) := 'N';
246
247 BEGIN
248 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
249 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
250 ,'100: Entered init_coverage_times_view');
251 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
252 ,'101: Coverage Time Zone Id'||cov_timezone_id);
253 END IF;
254
255 -- check if coverage times for the given timezone id is already been
256 -- populated
257 IF isTimezoneAlreadyProcessed(cov_timezone_id) = 'Y' THEN
258 -- set return status as success
259 x_return_status := l_return_status;
260
261 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
262 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
263 ,'200: Leaving init_coverage_times_view');
264 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
265 ,'201: Coverage Time Zone Id'||cov_timezone_id);
266 END IF;
267 return;
268 END IF;
269
270 -- Bulk collect coverage times for the given timezone id
271 SELECT
272 NVL(MONDAY_YN, 'N') MONDAY_YN,
273 NVL(TUESDAY_YN, 'N') TUESDAY_YN,
274 NVL(WEDNESDAY_YN, 'N') WEDNESDAY_YN,
275 NVL(THURSDAY_YN, 'N') THURSDAY_YN,
276 NVL(FRIDAY_YN, 'N') FRIDAY_YN,
277 NVL(SATURDAY_YN, 'N') SATURDAY_YN,
278 NVL(SUNDAY_YN, 'N') SUNDAY_YN,
279 COV_TZE_LINE_ID,
280 null start_time,
281 null end_time,
282 to_number(START_HOUR||decode(LENGTH(START_MINUTE),1,'0'||START_MINUTE,START_MINUTE)) START_HOUR_MINUTE,
283 to_number(END_HOUR||decode(LENGTH(END_MINUTE),1,'0'||END_MINUTE,END_MINUTE))END_HOUR_MINUTE
284 BULK COLLECT INTO coverage_times_recs
285 FROM
286 OKS_COVERAGE_TIMES
287 WHERE
288 COV_TZE_LINE_ID = cov_timezone_id
289 ORDER BY start_hour asc;
290
291 -- Fetch Flattened time limits
292 OPEN flattened_time_limits_cur;
293 FETCH flattened_time_limits_cur BULK COLLECT INTO flattened_time_limits_recs;
294 IF flattened_time_limits_cur %ISOPEN THEN
295 CLOSE flattened_time_limits_cur ;
296 END IF;
297
298 -- Loop Through Flattened time limits
299 IF flattened_time_limits_recs.COUNT > 0 THEN
300
301 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
302 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,
303 '103: Number of records in flattened_time_limits_recs :'||to_char(flattened_time_limits_recs.COUNT));
304 END IF;
305
306 j := 0;
307 FOR i IN flattened_time_limits_recs.FIRST..flattened_time_limits_recs.LAST LOOP
308
309 -- This implies that start_time and end_time variables are not set
310 IF start_time = -1 AND end_time = -1 THEN
311
312 -- Get the first Start Limit into start_time
313 start_time := flattened_time_limits_recs(i).time;
314 flattened_st_end_limit_recs(0) := flattened_time_limits_recs(i);
315 -- and loop thru to get the next record
316
317 ELSE -- enter this condition from second row onwards
318 -- set end_time
319 end_time := flattened_time_limits_recs(i).time;
320 flattened_st_end_limit_recs(1) := flattened_time_limits_recs(i);
321
322 -- If start_time and end_time limits are set
323 IF start_time <> -1 AND end_time <> -1 THEN
324
325 -- If start_time limit is not equal to end_time limit
326 IF start_time <> end_time THEN
327 -- navigate thru orginal coverage times records and
328 -- identify covered days for the given start and end
329 -- intervals
330 process_new_coverage_times(
331 flattened_st_end_limit_recs => flattened_st_end_limit_recs,
332 coverage_times_recs => coverage_times_recs,
333 new_coverage_times_rec => new_coverage_times_rec,
334 x_msg_data => x_msg_data,
335 x_msg_count => x_msg_count,
336 x_return_status => x_return_status);
337
338 -- Add New Coverage Time record to TBL of new Coverage Times
339 new_coverage_times_recs(j) := new_coverage_times_rec;
340 j := j+1;
341
342 -- reset start_time and end_time intervals
343 start_time := end_time;
344 end_time := -1;
345 flattened_st_end_limit_recs(0) := flattened_st_end_limit_recs(1);
346
347 END IF; -- IF start_time <> end_time
348 END IF; -- IF start_time <> -1 AND end_time <> -1
349 END IF; -- IF end_time <> -1
350 END LOOP;
351 END IF; -- IF flattened_time_limits_recs.COUNT > 0
352
353 -- Bulk Insert
354 FORALL k IN new_coverage_times_recs.FIRST..new_coverage_times_recs.LAST
355 INSERT INTO OKS_COVERAGE_TIMES_GT VALUES new_coverage_times_recs(k);
356
357 -- set return status as success
358 x_return_status := l_return_status;
359
360 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
361 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
362 ,'200: Leaving init_coverage_times_view');
363 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
364 ,'201: Coverage Time Zone Id'||cov_timezone_id);
365 END IF;
366
367 EXCEPTION
368 WHEN FND_API.G_EXC_ERROR THEN
369 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
370 FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'1000: Leaving init_coverage_times_view with G_EXC_ERROR: '||
371 substr(sqlerrm,1,200));
372 END IF;
373 IF flattened_time_limits_cur %ISOPEN THEN
374 CLOSE flattened_time_limits_cur ;
375 END IF;
376 x_return_status := G_RET_STS_ERROR;
377 FND_MSG_PUB.Count_And_Get(
378 p_count => x_msg_count,
379 p_data => x_msg_data
380 );
381
382 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
383 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
384 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name
385 ,'1000: Leaving init_coverage_times_view with G_EXC_UNEXPECTED_ERROR :'||substr(sqlerrm,1,200));
386 END IF;
387 IF flattened_time_limits_cur %ISOPEN THEN
388 CLOSE flattened_time_limits_cur ;
389 END IF;
390 x_return_status := G_RET_STS_UNEXP_ERROR;
391 FND_MSG_PUB.Count_And_Get(
392 p_count => x_msg_count,
393 p_data => x_msg_data
394 );
395
396 WHEN OTHERS THEN
397 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
398 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,
399 '1000: Leaving init_coverage_times_view with OTHER ERRORS :'||substr(sqlerrm,1,200));
400 END IF;
401 IF flattened_time_limits_cur %ISOPEN THEN
402 CLOSE flattened_time_limits_cur ;
403 END IF;
404 x_return_status := G_RET_STS_UNEXP_ERROR;
405 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
406 FND_MSG_PUB.Add_Exc_Msg(G_PACKAGE_NAME,l_api_name);
407 END IF;
408 FND_MSG_PUB.Count_And_Get(
409 p_count => x_msg_count,
410 p_data => x_msg_data
411 );
412
413 END; -- END OF init_coverage_times_view
414
415 /**
416 * Returns Timezone value appended with (Default)
417 * @param p_timezone_name Timezone name
418 */
419 FUNCTION Get_Default_Timezone_Msg
420 (p_timezone_name IN VARCHAR2) RETURN VARCHAR2 IS
421
422 BEGIN
423
424 -- set message
425 Fnd_Message.Set_Name( G_APP_NAME, G_COV_DEF_TZONE_MSG );
426
427 -- set token
428 Fnd_Message.Set_Token( token => G_COV_DEF_TZONE_MSG_TKN,
429 value => p_timezone_name);
430
431 return Fnd_Message.Get;
432
433 END; -- END OF Get_Default_Timezone_Msg
434
435 -- End Added by SASETHI
436
437 END OKS_COVERAGE_UTIL_PVT;
438
[Home] [Help]