[Home] [Help]
PACKAGE BODY: APPS.CS_GET_REACTION_TIMES_PUB
Source
1 PACKAGE BODY CS_GET_REACTION_TIMES_PUB AS
2 /* $Header: csctrtmb.pls 115.9 99/07/16 08:54:04 porting ship $ */
3
4 /*****************************************************************************/
5
6 PROCEDURE Convert_To_Mts(
7 p_hours IN NUMBER,
8 p_minutes IN NUMBER,
9 x_all_minutes OUT NUMBER) IS
10 l_all_minutes NUMBER;
11 BEGIN
12 l_all_minutes := (nvl(p_hours,0) * 60) + nvl(p_minutes,0);
13 x_all_minutes := l_all_minutes;
14 END Convert_To_Mts;
15
16 /*****************************************************************************/
17
18 PROCEDURE Convert_To_GMT(
19 p_time_zone_id IN NUMBER,
20 p_time_mts IN OUT NUMBER,
21 p_date IN OUT DATE,
22 x_return_status OUT VARCHAR2,
23 x_msg_count OUT NUMBER,
24 x_msg_data OUT VARCHAR2) IS
25 CURSOR GMT_csr IS
26 SELECT TO_CHAR(TZ1.Offset_Time,'HH24') Offset_Hours,
27 TO_CHAR(TZ1.Offset_Time,'MI') Offset_Mts,
28 TZ1.Offset_Indicator Offset_Indicator
29 FROM CS_TIME_ZONES TZ1
30 WHERE TZ1.Time_Zone_Id = p_time_zone_id;
31
32 l_offset_hours NUMBER;
33 l_offset_mts NUMBER;
34 l_offset_all_mts NUMBER;
35 l_offset_indicator VARCHAR2(2);
36 BEGIN
37 OPEN GMT_csr;
38 FETCH GMT_csr
39 INTO l_offset_hours,
40 l_offset_mts,
41 l_offset_indicator;
42
43 IF GMT_csr%NOTFOUND THEN
44 FND_MESSAGE.Set_Name ('CS','CS_CONTRACTS_VALUE_NOT_FOUND');
45 FND_MESSAGE.Set_Token('VALUE','TIME ZONE');
46 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
47 END IF;
48 CLOSE GMT_csr;
49
50 -- DBMS_Output.Put_Line('Offset Hours='|| to_char(l_offset_hours));
51 -- DBMS_Output.Put_Line('Offset Mts='|| to_char(l_offset_mts));
52
53 Convert_to_Mts (l_offset_hours,
54 l_offset_mts,
55 l_offset_all_mts);
56
57 --DBMS_Output.Put_Line('Offset All Mts='|| to_char(l_offset_all_mts));
58
59 IF l_offset_indicator = '+' THEN
60 p_time_mts := p_time_mts + l_offset_all_mts;
61 /**
62 while (p_time_mts > 24*60) Loop
63 p_time_mts := p_time_mts - (24*60);
64 p_date := p_date + 1;
65 END LOOP;
66 **/
67 ELSE
68 p_time_mts := p_time_mts - l_offset_all_mts;
69 /***
70 While (p_time_mts < 0) LOOP
71 p_time_mts := p_time_mts + (24*60) ;
72 p_date := p_date + 1;
73 END LOOP;
74 **/
75 END IF;
76
77 END Convert_To_GMT;
78
79 PROCEDURE Convert_FROM_GMT(
80 p_time_zone_id IN NUMBER,
81 p_time_mts IN OUT NUMBER,
82 p_date IN OUT DATE,
83 x_return_status OUT VARCHAR2,
84 x_msg_count OUT NUMBER,
85 x_msg_data OUT VARCHAR2) IS
86 CURSOR GMT_csr IS
87 SELECT TO_CHAR(TZ1.Offset_Time,'HH24') Offset_Hours,
88 TO_CHAR(TZ1.Offset_Time,'MI') Offset_Mts,
89 TZ1.Offset_Indicator Offset_Indicator
90 FROM CS_TIME_ZONES TZ1
91 WHERE TZ1.Time_Zone_Id = p_time_zone_id;
92
93 l_offset_hours NUMBER;
94 l_offset_mts NUMBER;
95 l_offset_all_mts NUMBER;
96 l_offset_indicator VARCHAR2(2);
97 BEGIN
98 OPEN GMT_csr;
99 FETCH GMT_csr
100 INTO l_offset_hours,
101 l_offset_mts,
102 l_offset_indicator;
103
104 IF GMT_csr%NOTFOUND THEN
105 FND_MESSAGE.Set_Name ('CS','CS_CONTRACTS_VALUE_NOT_FOUND');
106 FND_MESSAGE.Set_Token('VALUE','TIME ZONE');
107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
108 END IF;
109 CLOSE GMT_csr;
110
111 Convert_to_Mts (l_offset_hours,
112 l_offset_mts,
113 l_offset_all_mts);
114
115 --DBMS_Output.Put_Line('Convert from GMT Offset='|| to_char(l_offset_all_mts));
116 --DBMS_Output.Put_Line('Offset Indicator='|| l_offset_indicator);
117
118 IF l_offset_indicator = '+' THEN
119 p_time_mts := p_time_mts - l_offset_all_mts;
120 /***
121 while (p_time_mts > 24*60) Loop
122 p_time_mts := p_time_mts + (24*60);
123 p_date := p_date + 1;
124 END LOOP;
125 ***/
126 ELSE
127 p_time_mts := p_time_mts + l_offset_all_mts;
128 IF (p_time_mts < 0) THEN
129 p_time_mts := p_time_mts * -1;
130 END IF;
131 /***
132 While (p_time_mts < 0) LOOP
133 p_time_mts := p_time_mts - (24*60) ;
134 p_date := p_date - 1;
135 END LOOP;
136 ***/
137 END IF;
138
139 END Convert_FROM_GMT;
140
141 /*****************************************************************************/
142
143 PROCEDURE Convert_To_Hours_Mts(
144 p_end_time_all_mts IN NUMBER,
145 x_end_time_hours OUT NUMBER,
146 x_end_time_mts OUT NUMBER) IS
147 BEGIN
148 x_end_time_hours := TRUNC(p_end_time_all_mts / 60);
149 x_end_time_mts := MOD(p_end_time_all_mts, 60);
150 END Convert_To_Hours_Mts;
151
152 /*****************************************************************************/
153
154 PROCEDURE Get_Next_Days_Coverage_Time(
155 p_coverage_txn_group_id IN NUMBER,
156 p_coverage_day IN OUT NUMBER,
157 x_cov_start_time_hours OUT NUMBER,
158 x_cov_start_time_mts OUT NUMBER,
162 x_msg_count OUT NUMBER,
159 x_cov_end_time_hours OUT NUMBER,
160 x_cov_end_time_mts OUT NUMBER,
161 x_return_status OUT VARCHAR2,
163 x_msg_data OUT VARCHAR2) IS
164
165 CURSOR Coverage_Time_csr IS
166 SELECT Decode(p_coverage_day,
167 1, To_Char(TXN.Sunday_Start_Time ,'HH24'),
168 2, To_Char(TXN.Monday_Start_Time ,'HH24'),
169 3, To_Char(TXN.Tuesday_Start_Time ,'HH24'),
170 4, To_Char(TXN.Wednesday_Start_Time,'HH24'),
171 5, To_Char(TXN.Thursday_Start_Time ,'HH24'),
172 6, To_Char(TXN.Friday_Start_Time ,'HH24'),
173 7, To_Char(TXN.Saturday_Start_Time ,'HH24'))
174 Cov_Start_Time_Hours,
175 Decode(p_coverage_day,
176 1, To_Char(TXN.Sunday_Start_Time ,'MI'),
177 2, To_Char(TXN.Monday_Start_Time ,'MI'),
178 3, To_Char(TXN.Tuesday_Start_Time ,'MI'),
179 4, To_Char(TXN.Wednesday_Start_Time,'MI'),
180 5, To_Char(TXN.Thursday_Start_Time ,'MI'),
181 6, To_Char(TXN.Friday_Start_Time ,'MI'),
182 7, To_Char(TXN.Saturday_Start_Time ,'MI'))
183 Cov_Start_Time_Mts,
184 Decode(p_coverage_day,
185 1, To_Char(TXN.Sunday_End_Time ,'HH24'),
186 2, To_Char(TXN.Monday_End_Time ,'HH24'),
187 3, To_Char(TXN.Tuesday_End_Time ,'HH24'),
188 4, To_Char(TXN.Wednesday_End_Time,'HH24'),
189 5, To_Char(TXN.Thursday_End_Time ,'HH24'),
190 6, To_Char(TXN.Friday_End_Time ,'HH24'),
191 7, To_Char(TXN.Saturday_End_Time ,'HH24'))
192 Cov_End_Time_Hours,
193 Decode(p_coverage_day,
194 1, To_Char(TXN.Sunday_End_Time ,'MI'),
195 2, To_Char(TXN.Monday_End_Time ,'MI'),
196 3, To_Char(TXN.Tuesday_End_Time ,'MI'),
197 4, To_Char(TXN.Wednesday_End_Time,'MI'),
198 5, To_Char(TXN.Thursday_End_Time ,'MI'),
199 6, To_Char(TXN.Friday_End_Time ,'MI'),
200 7, To_Char(TXN.Saturday_End_Time ,'MI'))
201 Cov_End_Time_Mts
202 FROM CS_COVERAGE_TXN_GROUPS TXN
203 WHERE TXN.Coverage_Txn_Group_Id = P_Coverage_Txn_Group_Id;
204 BEGIN
205
206 -- DBMS_Output.Put_Line('In get next days coverage');
207
208 p_coverage_day := p_coverage_day + 1;
209 IF (NVL(p_coverage_day,8) > 7) THEN
210 p_coverage_day := 1;
211
212 -- DBMS_Output.Put_Line('coverage day=1');
213 END IF;
214
215 OPEN Coverage_Time_csr;
216 FETCH Coverage_Time_csr
217 INTO x_cov_start_time_hours,
218 x_cov_start_time_mts,
219 x_cov_end_time_hours,
220 x_cov_end_time_mts;
221
222 -- DBMS_Output.Put_Line('Fetched cov. start and end');
223
224 IF Coverage_Time_csr%NOTFOUND THEN
225 FND_MESSAGE.Set_Name ('CS','CS_CONTRACTS_VALUE_NOT_FOUND');
226 FND_MESSAGE.Set_Token('VALUE','COVERAGE TIMES');
227 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
228 END IF;
229 CLOSE Coverage_Time_csr;
230
231 -- DBMS_Output.Put_Line('End of Get Next days coverage');
232
233 END Get_Next_Days_Coverage_Time;
234
235 /*****************************************************************************/
236
237 -- Start of comments
238 -- API name : Get_Reaction_Times
239 -- Type : Public
240 -- Pre-reqs : None.
241 -- Function : This procedure returns the reaction times as defined
242 -- for the specified transaction group within the contract.
243 -- It also calculates the expected completion time based
244 -- upon the coverage and reaction times as defined.
245 -- Parameters :
246 -- IN :
247 -- p_api_version NUMBER Required
248 -- p_coverage_id NUMBER Required
249 -- p_business_process_id NUMBER Required
250 -- p_start_date_time DATE Required
251 -- p_call_time_zone_id NUMBER Required
252 -- p_incident_severity_id NUMBER Required
253 -- p_exception_coverage_flag VARCHAR2
254 -- p_init_msg_list VARCHAR2 Required
255 -- p_commit VARCHAR2 Required
256 -- IN OUT :
257 -- p_Reaction_time_id NUMBER
258 -- OUT :
259 -- x_Reaction_time VARCHAR2
260 -- x_Reaction_time_Sunday NUMBER
261 -- x_Reaction_time_Monday NUMBER
262 -- x_Reaction_time_Tuesday NUMBER
263 -- x_Reaction_time_Wednesday NUMBER
264 -- x_Reaction_time_Thursday NUMBER
265 -- x_Reaction_time_Friday NUMBER
266 -- x_Reaction_time_Saturday NUMBER
267 -- x_Worckflow VARCHAR2
268 -- x_always_covered VARCHAR2
269 -- x_incident_severity VARCHAR2
270 -- x_Expected_End_Date_Time DATE
271 -- x_Use_for_SR_Date_Calc VARCHAR2
272 -- x_return_status VARCHAR2
273 -- x_msg_count NUMBER
274 -- x_msg_data VARCHAR2
275 --End of comments
276
277 PROCEDURE Get_Reaction_Times (
278 p_api_version IN NUMBER,
279 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
283 p_start_date_time IN DATE,
280 p_commit IN VARCHAR2 := FND_API.G_FALSE,
281 p_coverage_id IN NUMBER,
282 p_business_process_id IN NUMBER,
284 p_call_time_zone_id IN NUMBER,
285 p_incident_severity_id IN NUMBER,
286 p_exception_coverage_flag IN VARCHAR2,
287 p_Reaction_time_id IN OUT NUMBER,
288 x_Reaction_time OUT VARCHAR2,
289 x_Reaction_time_Sunday OUT NUMBER,
290 x_Reaction_time_Monday OUT NUMBER,
291 x_Reaction_time_Tuesday OUT NUMBER,
292 x_Reaction_time_Wednesday OUT NUMBER,
293 x_Reaction_time_Thursday OUT NUMBER,
294 x_Reaction_time_Friday OUT NUMBER,
295 x_Reaction_time_Saturday OUT NUMBER,
296 x_Workflow OUT VARCHAR2,
297 x_always_covered OUT VARCHAR2,
298 x_incident_severity OUT VARCHAR2,
299 x_Expected_End_Date_Time OUT DATE,
300 x_Use_for_SR_Date_Calc OUT VARCHAR2,
301 x_return_status OUT VARCHAR2,
302 x_msg_count OUT NUMBER,
303 x_msg_data OUT VARCHAR2 ) IS
304
305 l_coverage_id CS_COVERAGES.COVERAGE_ID%TYPE;
306
307 CURSOR Reaction_Times_csr IS
308 SELECT CRT.Reaction_time_id,
309 CRT.Name Reaction_Time,
310 Decode(TO_CHAR(TO_DATE(p_start_date_time),'D'),
311 1, CRT.Reaction_Time_Sunday ,
312 2, CRT.Reaction_Time_Monday ,
313 3, CRT.Reaction_Time_Tuesday ,
314 4, CRT.Reaction_Time_Wednesday,
315 5, CRT.Reaction_Time_Thursday ,
316 6, CRT.Reaction_Time_Friday ,
317 7, CRT.Reaction_Time_Saturday) Reaction_Time,
318 To_Char(p_start_date_time,'HH24') Start_Time_Hours,
319 To_Char(p_start_date_time,'MI') Start_Time_Mts,
320 Decode(TO_CHAR(TO_DATE(p_start_date_time),'D'),
321 1, SUBSTR(CRT.Reaction_Time_Sunday,1,
322 LENGTH(CRT.Reaction_Time_Sunday) - 2),
323 2, SUBSTR(CRT.Reaction_Time_Monday,1,
324 LENGTH(CRT.Reaction_Time_Monday) - 2),
325 3, SUBSTR(CRT.Reaction_Time_Tuesday,1,
326 LENGTH(CRT.Reaction_Time_Tuesday) - 2),
327 4, SUBSTR(CRT.Reaction_Time_Wednesday,1,
328 LENGTH(CRT.Reaction_Time_Wednesday) - 2),
329 5, SUBSTR(CRT.Reaction_Time_Thursday,1,
330 LENGTH(CRT.Reaction_Time_Thursday ) - 2),
331 6, SUBSTR(CRT.Reaction_Time_Friday,1,
332 LENGTH(CRT.Reaction_Time_Friday ) - 2),
333 7, SUBSTR(CRT.Reaction_Time_Saturday,1,
334 LENGTH(CRT.Reaction_Time_Saturday) - 2))
335 Reaction_Time_Hours,
336 Decode(TO_CHAR(TO_DATE(p_start_date_time),'D'),
337 1, SUBSTR(CRT.Reaction_Time_Sunday,
338 LENGTH(CRT.Reaction_Time_Sunday ) - 1,2),
339 2, SUBSTR(CRT.Reaction_Time_Monday,
340 LENGTH(CRT.Reaction_Time_Monday ) - 1,2),
341 3, SUBSTR(CRT.Reaction_Time_Tuesday,
342 LENGTH(CRT.Reaction_Time_Tuesday ) - 1,2),
343 4, SUBSTR(CRT.Reaction_Time_Wednesday,
344 LENGTH(CRT.Reaction_Time_Wednesday) - 1,2),
345 5, SUBSTR(CRT.Reaction_Time_Thursday,
346 LENGTH(CRT.Reaction_Time_Thursday ) - 1,2),
347 6, SUBSTR(CRT.Reaction_Time_Friday,
348 LENGTH(CRT.Reaction_Time_Friday ) - 1,2),
349 7, SUBSTR(CRT.Reaction_Time_Saturday,
350 LENGTH(CRT.Reaction_Time_Saturday) - 1,2))
351 Reaction_Time_Mts,
352 CRT.Reaction_Time_Sunday ,
353 CRT.Reaction_Time_Monday ,
354 CRT.Reaction_Time_Tuesday ,
355 CRT.Reaction_Time_Wednesday,
356 CRT.Reaction_Time_Thursday ,
357 CRT.Reaction_Time_Friday ,
358 CRT.Reaction_Time_Saturday ,
359 CRT.Workflow ,
360 INS.Name Incident_Severity,
361 CRT.Always_Covered ,
362 CRT.Use_for_SR_Date_Calc ,
363 Decode(TO_CHAR(TO_DATE(p_Start_date_time),'D'),
364 1, To_Char(TXN.Sunday_Start_Time ,'HH24'),
365 2, To_Char(TXN.Monday_Start_Time ,'HH24'),
366 3, To_Char(TXN.Tuesday_Start_Time ,'HH24'),
367 4, To_Char(TXN.Wednesday_Start_Time,'HH24'),
368 5, To_Char(TXN.Thursday_Start_Time ,'HH24'),
369 6, To_Char(TXN.Friday_Start_Time ,'HH24'),
370 7, To_Char(TXN.Saturday_Start_Time ,'HH24'))
371 Cov_Start_Time_Hours,
372 Decode(TO_CHAR(TO_DATE(p_Start_date_time),'D'),
373 1, To_Char(TXN.Sunday_Start_Time ,'MI'),
374 2, To_Char(TXN.Monday_Start_Time ,'MI'),
375 3, To_Char(TXN.Tuesday_Start_Time ,'MI'),
376 4, To_Char(TXN.Wednesday_Start_Time,'MI'),
377 5, To_Char(TXN.Thursday_Start_Time ,'MI'),
378 6, To_Char(TXN.Friday_Start_Time ,'MI'),
379 7, To_Char(TXN.Saturday_Start_Time ,'MI'))
380 Cov_Start_Time_Mts,
381 Decode(TO_CHAR(TO_DATE(p_Start_date_time),'D'),
382 1, To_Char(TXN.Sunday_End_Time ,'HH24'),
383 2, To_Char(TXN.Monday_End_Time ,'HH24'),
384 3, To_Char(TXN.Tuesday_End_Time ,'HH24'),
385 4, To_Char(TXN.Wednesday_End_Time,'HH24'),
386 5, To_Char(TXN.Thursday_End_Time ,'HH24'),
387 6, To_Char(TXN.Friday_End_Time ,'HH24'),
388 7, To_Char(TXN.Saturday_End_Time ,'HH24'))
389 Cov_End_Time_Hours,
390 Decode(TO_CHAR(TO_DATE(p_Start_date_time),'D'),
391 1, To_Char(TXN.Sunday_End_Time ,'MI'),
392 2, To_Char(TXN.Monday_End_Time ,'MI'),
393 3, To_Char(TXN.Tuesday_End_Time ,'MI'),
394 4, To_Char(TXN.Wednesday_End_Time,'MI'),
395 5, To_Char(TXN.Thursday_End_Time ,'MI'),
396 6, To_Char(TXN.Friday_End_Time ,'MI'),
397 7, To_Char(TXN.Saturday_End_Time ,'MI'))
401 2, 2,
398 Cov_End_Time_Mts,
399 Decode(TO_CHAR(TO_DATE(p_Start_date_time),'D'),
400 1, 1,
402 3, 3,
403 4, 4,
404 5, 5,
405 6, 6,
406 7, 7),
407 TXN.Time_Zone_Id ,
408 TXN.Coverage_Txn_Group_Id
409 FROM CS_COV_REACTION_TIMES CRT,
410 CS_INCIDENT_SEVERITIES INS,
411 CS_COVERAGES COV,
412 CS_COVERAGE_TXN_GROUPS TXN
413 WHERE COV.Coverage_id = l_coverage_id
414 AND COV.Coverage_id = TXN.Coverage_id
415 AND TXN.Business_Process_Id = p_business_process_id
416 AND TXN.Coverage_Txn_Group_Id = CRT.Coverage_Txn_Group_Id
417 AND CRT.Incident_Severity_Id = p_incident_severity_id
418 AND CRT.Use_For_SR_Date_Calc = 'Y'
419 AND CRT.Incident_Severity_Id = INS.Incident_Severity_Id(+);
420
421
422 CURSOR Reaction_Times_id_csr IS
423 SELECT CRT.Name Reaction_Time,
424 Decode(TO_CHAR(TO_DATE(p_start_date_time),'D'),
425 1, CRT.Reaction_Time_Sunday ,
426 2, CRT.Reaction_Time_Monday ,
427 3, CRT.Reaction_Time_Tuesday ,
428 4, CRT.Reaction_Time_Wednesday,
429 5, CRT.Reaction_Time_Thursday ,
430 6, CRT.Reaction_Time_Friday ,
431 7, CRT.Reaction_Time_Saturday) Reaction_Time,
432 To_Char(p_start_date_time,'HH24') Start_Time_Hours,
433 To_Char(p_start_date_time,'MI') Start_Time_Mts,
434 Decode(TO_CHAR(TO_DATE(p_start_date_time),'D'),
435 1, SUBSTR(CRT.Reaction_Time_Sunday,1,
436 LENGTH(CRT.Reaction_Time_Sunday) - 2),
437 2, SUBSTR(CRT.Reaction_Time_Monday,1,
438 LENGTH(CRT.Reaction_Time_Monday) - 2),
439 3, SUBSTR(CRT.Reaction_Time_Tuesday,1,
440 LENGTH(CRT.Reaction_Time_Tuesday) - 2),
441 4, SUBSTR(CRT.Reaction_Time_Wednesday,1,
442 LENGTH(CRT.Reaction_Time_Wednesday) - 2),
443 5, SUBSTR(CRT.Reaction_Time_Thursday,1,
444 LENGTH(CRT.Reaction_Time_Thursday ) - 2),
445 6, SUBSTR(CRT.Reaction_Time_Friday,1,
446 LENGTH(CRT.Reaction_Time_Friday ) - 2),
447 7, SUBSTR(CRT.Reaction_Time_Saturday,1,
448 LENGTH(CRT.Reaction_Time_Saturday) - 2))
449 Reaction_Time_Hours,
450 Decode(TO_CHAR(TO_DATE(p_start_date_time),'D'),
451 1, SUBSTR(CRT.Reaction_Time_Sunday,
452 LENGTH(CRT.Reaction_Time_Sunday ) - 1,2),
453 2, SUBSTR(CRT.Reaction_Time_Monday,
454 LENGTH(CRT.Reaction_Time_Monday ) - 1,2),
455 3, SUBSTR(CRT.Reaction_Time_Tuesday,
456 LENGTH(CRT.Reaction_Time_Tuesday ) - 1,2),
457 4, SUBSTR(CRT.Reaction_Time_Wednesday,
458 LENGTH(CRT.Reaction_Time_Wednesday) - 1,2),
459 5, SUBSTR(CRT.Reaction_Time_Thursday,
460 LENGTH(CRT.Reaction_Time_Thursday ) - 1,2),
461 6, SUBSTR(CRT.Reaction_Time_Friday,
462 LENGTH(CRT.Reaction_Time_Friday ) - 1,2),
463 7, SUBSTR(CRT.Reaction_Time_Saturday,
464 LENGTH(CRT.Reaction_Time_Saturday) - 1,2))
465 Reaction_Time_Mts,
466 CRT.Reaction_Time_Sunday ,
467 CRT.Reaction_Time_Monday ,
468 CRT.Reaction_Time_Tuesday ,
469 CRT.Reaction_Time_Wednesday,
470 CRT.Reaction_Time_Thursday ,
471 CRT.Reaction_Time_Friday ,
472 CRT.Reaction_Time_Saturday ,
473 CRT.Workflow ,
474 INS.Name Incident_Severity,
475 CRT.Always_Covered ,
476 CRT.Use_for_SR_Date_Calc ,
477 Decode(TO_CHAR(TO_DATE(p_Start_date_time),'D'),
478 1, To_Char(TXN.Sunday_Start_Time ,'HH24'),
479 2, To_Char(TXN.Monday_Start_Time ,'HH24'),
480 3, To_Char(TXN.Tuesday_Start_Time ,'HH24'),
481 4, To_Char(TXN.Wednesday_Start_Time,'HH24'),
482 5, To_Char(TXN.Thursday_Start_Time ,'HH24'),
483 6, To_Char(TXN.Friday_Start_Time ,'HH24'),
484 7, To_Char(TXN.Saturday_Start_Time ,'HH24'))
485 Cov_Start_Time_Hours,
486 Decode(TO_CHAR(TO_DATE(p_Start_date_time),'D'),
487 1, To_Char(TXN.Sunday_Start_Time ,'MI'),
488 2, To_Char(TXN.Monday_Start_Time ,'MI'),
489 3, To_Char(TXN.Tuesday_Start_Time ,'MI'),
490 4, To_Char(TXN.Wednesday_Start_Time,'MI'),
491 5, To_Char(TXN.Thursday_Start_Time ,'MI'),
492 6, To_Char(TXN.Friday_Start_Time ,'MI'),
493 7, To_Char(TXN.Saturday_Start_Time ,'MI'))
494 Cov_Start_Time_Mts,
495 Decode(TO_CHAR(TO_DATE(p_Start_date_time),'D'),
496 1, To_Char(TXN.Sunday_End_Time ,'HH24'),
497 2, To_Char(TXN.Monday_End_Time ,'HH24'),
498 3, To_Char(TXN.Tuesday_End_Time ,'HH24'),
499 4, To_Char(TXN.Wednesday_End_Time,'HH24'),
500 5, To_Char(TXN.Thursday_End_Time ,'HH24'),
501 6, To_Char(TXN.Friday_End_Time ,'HH24'),
502 7, To_Char(TXN.Saturday_End_Time ,'HH24'))
503 Cov_End_Time_Hours,
504 Decode(TO_CHAR(TO_DATE(p_Start_date_time),'D'),
505 1, To_Char(TXN.Sunday_End_Time ,'MI'),
506 2, To_Char(TXN.Monday_End_Time ,'MI'),
507 3, To_Char(TXN.Tuesday_End_Time ,'MI'),
508 4, To_Char(TXN.Wednesday_End_Time,'MI'),
509 5, To_Char(TXN.Thursday_End_Time ,'MI'),
510 6, To_Char(TXN.Friday_End_Time ,'MI'),
511 7, To_Char(TXN.Saturday_End_Time ,'MI'))
512 Cov_End_Time_Mts,
513 Decode(TO_CHAR(TO_DATE(p_Start_date_time),'D'),
514 1, 1,
515 2, 2,
516 3, 3,
517 4, 4,
518 5, 5,
519 6, 6,
520 7, 7),
521 TXN.Time_Zone_Id ,
522 TXN.Coverage_Txn_Group_Id
523 FROM CS_COV_REACTION_TIMES CRT,
524 CS_INCIDENT_SEVERITIES INS,
525 CS_COVERAGES COV,
526 CS_COVERAGE_TXN_GROUPS TXN
530 AND TXN.Coverage_Txn_Group_Id = CRT.Coverage_Txn_Group_Id
527 WHERE COV.Coverage_id = l_coverage_id
528 AND COV.Coverage_id = TXN.Coverage_id
529 AND TXN.Business_Process_Id = p_business_process_id
531 AND CRT.Reaction_time_id = p_reaction_time_id
532 AND CRT.Use_For_SR_Date_Calc = 'Y'
533 AND CRT.Incident_Severity_Id = INS.Incident_Severity_Id(+);
534
535
536
537 l_api_name CONSTANT VARCHAR2(30) := 'Get_Reaction_Times';
538 l_api_version CONSTANT NUMBER := 1;
539 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
540 l_start_time DATE;
541 l_end_time DATE;
542 l_end_time_all_mts NUMBER;
543 l_start_time_all_mts NUMBER;
544 l_end_time_mts NUMBER;
545 l_start_time_mts NUMBER;
546 l_end_time_hours NUMBER;
547 l_start_time_hours NUMBER;
548 l_day NUMBER;
549 l_reaction_time NUMBER;
550 l_reaction_time_hours NUMBER;
551 l_reaction_time_mts NUMBER;
552 l_reaction_time_all_mts NUMBER;
553 l_cov_start_time_hours NUMBER;
554 l_cov_end_time_hours NUMBER;
555 l_cov_start_time_mts NUMBER;
556 l_cov_end_time_mts NUMBER;
557 l_cov_start_time_all_mts NUMBER;
558 l_cov_end_time_all_mts NUMBER;
559 l_coverage_day NUMBER;
560 l_rtm_diff NUMBER;
561 l_cov_diff NUMBER;
562 l_end_date_time DATE;
563 l_time_zone_id NUMBER;
564 l_coverage_txn_group_id NUMBER;
565 l_count NUMBER;
566 l_reaction_time_used NUMBER;
567 l_reaction_time_remaining NUMBER;
568 l_loop_count NUMBER;
569 l_time_diff NUMBER;
570
571 BEGIN
572 l_return_status := TAPI_DEV_KIT.START_ACTIVITY( l_api_name,
573 G_PKG_NAME,
574 l_api_version,
575 p_api_version,
576 p_init_msg_list,
577 '_pub',
578 x_return_status);
579 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
580 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
581 END IF;
582
583 IF (NVL(p_exception_coverage_flag,'N') = 'Y') THEN
584 CS_GET_COVERAGE_VALUES_PUB.Get_Exception_Coverage(
585 1,
586 FND_API.G_FALSE,
587 FND_API.G_FALSE,
588 p_coverage_id,
589 l_coverage_id,
590 x_return_status,
591 x_msg_count,
592 x_msg_data);
593 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
594 FND_MESSAGE.Set_Name ('CS','CS_CONTRACTS_VALUE_NOT_FOUND');
595 FND_MESSAGE.Set_Token('VALUE','EXCEPTION COVERAGE');
596 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
597 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
598 FND_MESSAGE.Set_Name ('CS','CS_CONTRACTS_VALUE_NOT_FOUND');
599 FND_MESSAGE.Set_Token('VALUE','EXCEPTION COVERAGE');
600 RAISE FND_API.G_EXC_ERROR;
601 END IF;
602 ELSE
603 l_coverage_id := p_coverage_id;
604 END IF;
605
606 IF (p_reaction_time_id IS NOT NULL) THEN
607 OPEN Reaction_Times_id_csr;
608 FETCH Reaction_Times_id_csr
609 INTO x_reaction_time ,
610 l_reaction_time ,
611 l_start_time_hours ,
612 l_start_time_mts ,
613 l_reaction_time_hours ,
614 l_reaction_time_mts ,
615 x_reaction_time_sunday ,
616 x_reaction_time_monday ,
617 x_reaction_time_tuesday ,
618 x_reaction_time_wednesday ,
619 x_reaction_time_thursday ,
620 x_reaction_time_friday ,
621 x_reaction_time_saturday ,
622 x_workflow ,
623 x_incident_severity ,
624 x_always_covered ,
625 x_use_for_SR_date_calc ,
626 l_cov_start_time_hours ,
627 l_cov_start_time_mts ,
628 l_cov_end_time_hours ,
629 l_cov_end_time_mts ,
630 l_coverage_day ,
631 l_time_zone_id ,
632 l_coverage_txn_group_id ;
633
634
635 IF Reaction_Times_id_csr%NOTFOUND THEN
636 CLOSE Reaction_Times_id_csr;
637 FND_MESSAGE.Set_Name ('CS','CS_CONTRACTS_VALUE_NOT_FOUND');
638 FND_MESSAGE.Set_Token('VALUE','REACTION TIMES ID');
639 TAPI_DEV_KIT.END_ACTIVITY(p_commit,
640 x_msg_count,
641 x_msg_data);
642 x_return_status := FND_API.G_RET_STS_ERROR;
643 RETURN;
644 END IF;
645 CLOSE Reaction_Times_id_csr;
646 ELSE
647 -- DBMS_Output.Put_line('Reaction time id is null');
648 OPEN Reaction_Times_csr;
649 FETCH Reaction_Times_csr
650 INTO p_reaction_time_id ,
651 x_reaction_time ,
652 l_reaction_time ,
653 l_start_time_hours ,
654 l_start_time_mts ,
655 l_reaction_time_hours ,
659 x_reaction_time_tuesday ,
656 l_reaction_time_mts ,
657 x_reaction_time_sunday ,
658 x_reaction_time_monday ,
660 x_reaction_time_wednesday ,
661 x_reaction_time_thursday ,
662 x_reaction_time_friday ,
663 x_reaction_time_saturday ,
664 x_workflow ,
665 x_incident_severity ,
666 x_always_covered ,
667 x_use_for_SR_date_calc ,
668 l_cov_start_time_hours ,
669 l_cov_start_time_mts ,
670 l_cov_end_time_hours ,
671 l_cov_end_time_mts ,
672 l_coverage_day ,
673 l_time_zone_id ,
674 l_coverage_txn_group_id ;
675
676 IF Reaction_Times_csr%NOTFOUND THEN
677 CLOSE Reaction_Times_csr;
678 FND_MESSAGE.Set_Name ('CS','CS_CONTRACTS_VALUE_NOT_FOUND');
679 FND_MESSAGE.Set_Token('VALUE','REACTION TIMES');
680 TAPI_DEV_KIT.END_ACTIVITY(p_commit,
681 x_msg_count,
682 x_msg_data);
683 x_return_status := FND_API.G_RET_STS_ERROR;
684 RETURN;
685 END IF;
686 CLOSE Reaction_Times_csr;
687 END IF;
688
689 /***
690 DBMS_Output.Put_Line('Obtained values. Reaction time='|| x_reaction_time);
691 DBMS_Output.Put_Line('Start time hh=' || to_char(l_start_time_hours) ||
692 ' mts=' || to_char(l_start_time_mts) );
693 DBMS_Output.Put_Line('Reaction time hours=' || to_char(l_reaction_time_hours) ||
694 ' mts=' || to_char(l_reaction_time_mts));
695 DBMS_Output.Put_Line('Coverage_Day='|| to_char(l_coverage_day));
696 DBMS_Output.Put_Line('Cov start time hh=' || to_char(l_cov_start_time_hours) ||
697 ' mts=' || to_char(l_cov_start_time_mts));
698 DBMS_Output.Put_Line('Cov end time hh=' || to_char(l_cov_end_time_hours) ||
699 ' mts=' || to_char(l_cov_end_time_mts));
700 ***/
701
702 Convert_to_Mts( l_start_time_hours,
703 l_start_time_mts,
704 l_start_time_all_mts);
705
706 Convert_to_Mts( l_reaction_time_hours,
707 l_reaction_time_mts,
708 l_reaction_time_all_mts);
709
710 /*** If coverage Start and End time is not null then
711 convert to minutes **/
712
713 /****
714 IF ((NVL(l_cov_start_time_hours,0) <> 0) OR
715 (NVL(l_cov_start_time_mts,0) <> 0) ) AND
716 ((NVL(l_cov_end_time_hours,0) <> 0) OR
717 (NVL(l_cov_end_time_mts,0) <> 0) ) THEN
718 ****/
719 Convert_to_Mts( l_cov_start_time_hours,
720 l_cov_start_time_mts,
721 l_cov_start_time_all_mts);
722 Convert_to_Mts( l_cov_end_time_hours,
723 l_cov_end_time_mts,
724 l_cov_end_time_all_mts);
725 /****
726 ELSE
727 l_cov_start_time_all_mts := 0;
728 l_cov_end_time_all_mts := 0;
729
730 END IF;
731 ****/
732
733 l_end_date_time := p_start_date_time;
734
735 /***
736 DBMS_Output.PUT_LINE('1.Reaction time all mts='||
737 to_char(l_reaction_time_all_mts));
738 DBMS_Output.PUT_LINE('1.Start time all mts='||
739 to_char(l_start_time_all_mts));
740 DBMS_OUtput.Put_Line('1.Coverage start time='||
741 to_char(l_cov_start_time_all_Mts));
742 DBMS_OUtput.Put_Line('1.Coverage End time='||
743 to_char(l_cov_end_time_all_Mts));
744 ***/
745
746 l_loop_count := 0;
747
748 WHILE ((( l_loop_count < 6 ) AND
749 ( l_cov_start_time_all_mts = 0) AND
750 ( l_cov_end_time_all_mts = 0)) AND
751 ( l_reaction_time_all_mts <> 0 ))
752 LOOP
753 -- DBMS_Output.Put_Line('In while loop=' || to_char(l_loop_count));
754 Get_Next_Days_Coverage_Time(
755 l_coverage_txn_group_id,
756 l_coverage_day,
757 l_cov_start_time_hours,
758 l_cov_start_time_mts,
759 l_cov_end_time_hours,
760 l_cov_end_time_mts,
761 x_return_status,
762 x_msg_count,
763 x_msg_data);
764
765 convert_to_mts(l_cov_start_time_hours,
766 l_cov_start_time_mts,
767 l_cov_start_time_all_mts);
768
769 convert_to_mts(l_cov_end_time_hours,
770 l_cov_end_time_mts,
771 l_cov_end_time_all_mts);
772
773 l_loop_count := l_loop_count + 1;
774
775 l_start_time_all_mts := l_cov_start_time_all_mts;
776
777 l_end_date_time := l_end_date_time + 1;
778
779 -- DBMS_Output.Put_Line('Cov. start time=' || to_char(l_cov_start_time_all_mts));
780
781 END LOOP;
782
783
784 IF (NVL(l_time_zone_id,0) <> 0 ) AND
785 (p_call_time_zone_id <> l_time_zone_id) THEN
786
787 --DBMS_Output.Put_Line('l_time zone=' || to_char(l_time_zone_id));
788 Convert_To_GMT(p_call_time_zone_id,
789 l_start_time_all_mts,
790 l_end_date_time,
791 x_return_status,
792 x_msg_count,
793 x_msg_data);
794
795 Convert_To_GMT(l_time_zone_id,
796 l_cov_start_time_all_mts,
797 l_end_date_time,
798 x_return_status,
799 x_msg_count,
803 l_cov_end_time_all_mts,
800 x_msg_data);
801
802 Convert_To_GMT(l_time_zone_id,
804 l_end_date_time,
805 x_return_status,
806 x_msg_count,
807 x_msg_data);
808 END IF;
809
810
811 /** Perform calculation only if always covered is
812 not 'Y' and coverage start and end times are
813 not null ***/
814
815 /***
816 DBMS_Output.Put_Line('Reaction time all mts=' ||
817 to_char(l_reaction_time_all_mts));
818 **/
819
820 l_time_diff := l_cov_end_time_all_mts - l_cov_start_time_all_mts;
821
822 -- DBMS_Output.Put_Line('Cov. End - Cov. Start='||
823 -- to_char(l_time_diff));
824
825 IF ((x_always_covered <> 'Y') AND
826 (l_cov_end_time_all_mts <>0 ) AND
827 (l_cov_start_time_all_mts <>0 ) AND
828 (l_time_diff < l_reaction_time_all_mts )) THEN
829
830 -- DBMS_Output.Put_Line('Start time='|| to_char(l_start_time_all_mts));
831
832 IF (l_start_time_all_mts > l_cov_end_time_all_mts ) THEN
833 -- DBMS_Output.Put_Line('Getting next days coverage');
834 Get_Next_Days_Coverage_Time(l_coverage_txn_group_id,
835 l_coverage_day,
836 l_cov_start_time_hours,
837 l_cov_start_time_mts,
838 l_cov_end_time_hours,
839 l_cov_end_time_mts,
840 x_return_status,
841 x_msg_count,
842 x_msg_data);
843
844 l_end_date_time := l_end_date_time + 1;
845
846 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
847 FND_MESSAGE.Set_Name ('CS','CS_CONTRACTS_VALUE_NOT_FOUND');
848 FND_MESSAGE.Set_Token('VALUE','NEXT DAY COVERAGE');
849 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
850 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
851 FND_MESSAGE.Set_Name ('CS','CS_CONTRACTS_VALUE_NOT_FOUND');
852 FND_MESSAGE.Set_Token('VALUE','NEXT DAY COVERAGE');
853 RAISE FND_API.G_EXC_ERROR;
854 END IF;
855
856 convert_to_mts(l_cov_start_time_hours,
857 l_cov_start_time_mts,
858 l_cov_start_time_all_mts);
859
860 convert_to_mts(l_cov_end_time_hours,
861 l_cov_end_time_mts,
862 l_cov_end_time_all_mts);
863
864 IF (l_time_zone_id IS NOT NULL ) AND
865 (p_call_time_zone_id <> l_time_zone_id) THEN
866
867 Convert_To_GMT(l_time_zone_id,
868 l_cov_start_time_all_mts,
869 l_end_date_time,
870 x_return_status,
871 x_msg_count,
872 x_msg_data);
873
874 Convert_To_GMT(l_time_zone_id,
875 l_cov_end_time_all_mts,
876 l_end_date_time,
877 x_return_status,
878 x_msg_count,
879 x_msg_data);
880 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
881 FND_MESSAGE.Set_Name ('CS',
882 'CS_CONTRACTS_VALUE_NOT_FOUND');
883 FND_MESSAGE.Set_Token('VALUE','CONVERT TO GMT');
884 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
885 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
886 FND_MESSAGE.Set_Name ('CS',
887 'CS_CONTRACTS_VALUE_NOT_FOUND');
888 FND_MESSAGE.Set_Token('VALUE','CONVERT TO GMT');
889 RAISE FND_API.G_EXC_ERROR;
890 END IF;
891 END If;
892
893 l_start_time_all_mts := l_cov_start_time_all_mts;
894 -- DBMS_Output.Put_Line('IN here Start time='|| to_char(l_start_time_all_mts));
895
896 END IF;
897
898 /***
899 DBMS_Output.Put_Line('Reaction time='|| to_char(l_reaction_time_all_mts));
900 DBMS_Output.Put_Line('Start time='|| to_char(l_start_time_all_mts));
901 DBMS_Output.Put_Line('Cov start='|| to_char(l_cov_start_time_all_mts));
902 DBMS_Output.Put_Line('Cov end='|| to_char(l_cov_end_time_all_mts));
903 **/
904
905 l_cov_diff := l_cov_end_time_all_mts
906 - l_cov_start_time_all_mts;
907 l_reaction_time_used := l_cov_end_time_all_mts
908 - l_start_time_all_mts;
909 l_reaction_time_remaining := l_reaction_time_all_mts
910 - l_reaction_time_used;
911
912 IF (l_reaction_time_remaining <= 0) THEN
913 l_end_time_all_mts := l_start_time_all_mts
914 + l_reactioN_time_all_mts;
915 -- DBMS_OUTPUT.PUT_LINE('In one day end time=' || to_char(l_end_time_all_mts));
916 IF (p_call_time_zone_id <> l_time_zone_id) THEN
917 Convert_From_GMT( p_call_time_zone_id,
918 l_end_time_all_mts,
919 l_end_date_time,
923
920 x_return_status,
921 x_msg_count,
922 x_msg_data);
924 /***
925 DBMS_OUTPUT.Put_LIne('Converted from GMT. end time='||
926 to_char(l_end_time_all_mts));
927 **/
928 END IF;
929 ELSE
930 /***
931 DBMS_Output.Put_Line('2.RT Used='||
932 to_char(l_reaction_time_used));
933 DBMS_Output.Put_Line('2.RT Remaining='||
934 to_char(l_reaction_time_remaining));
935 ***/
936 while (l_reaction_time_remaining > 0) LOOP
937 Get_Next_Days_Coverage_Time(
938 l_coverage_txn_group_id,
939 l_coverage_day,
940 l_cov_start_time_hours,
941 l_cov_start_time_mts,
942 l_cov_end_time_hours,
943 l_cov_end_time_mts,
944 x_return_status,
945 x_msg_count,
946 x_msg_data);
947 /**
948 DBMS_Output.PUt_Line('Back from Next days cov date='||
949 to_char(l_end_date_time, 'DD-MON-RR HH24:MI'));
950 ***/
951
952 l_end_date_time := l_end_date_time + 1;
953 /***
954 DBMS_Output.Put_Line('End date time='||
955 to_char(l_end_date_time, 'DD-MON-RR HH24:MI'));
956 **/
957
958 IF (x_return_status =
959 FND_API.G_RET_STS_UNEXP_ERROR) THEN
960 FND_MESSAGE.Set_Name ('CS',
961 'CS_CONTRACTS_VALUE_NOT_FOUND');
962 FND_MESSAGE.Set_Token('VALUE','
963 NEXT DAY COVERAGE');
964 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
965 ELSIF(x_return_status = FND_API.G_RET_STS_ERROR)
966 THEN
967 FND_MESSAGE.Set_Name ('CS',
968 'CS_CONTRACTS_VALUE_NOT_FOUND');
969 FND_MESSAGE.Set_Token('VALUE',
970 'NEXT DAY COVERAGE');
971 RAISE FND_API.G_EXC_ERROR;
972 END IF;
973
977 l_cov_start_time_mts,
974 -- DBMS_Output.Put_Line('Converting to mts cov. start time');
975
976 convert_to_mts(l_cov_start_time_hours,
978 l_cov_start_time_all_mts);
979 -- DBMS_Output.Put_Line('Converting to mts cov. end time');
980 convert_to_mts(l_cov_end_time_hours,
981 l_cov_end_time_mts,
982 l_cov_end_time_all_mts);
983
984 -- DBMS_Output.Put_Line('Converted to mts');
985
986 IF (l_time_zone_id IS NOT NULL ) AND
987 (p_call_time_zone_id <> l_time_zone_id) THEN
988
989 Convert_To_GMT(l_time_zone_id,
990 l_cov_start_time_all_mts,
991 l_end_date_time,
992 x_return_status,
993 x_msg_count,
994 x_msg_data);
995
996 Convert_To_GMT(l_time_zone_id,
997 l_cov_end_time_all_mts,
998 l_end_date_time,
999 x_return_status,
1000 x_msg_count,
1001 x_msg_data);
1002
1003 IF (x_return_status =
1004 FND_API.G_RET_STS_UNEXP_ERROR) THEN
1005 FND_MESSAGE.Set_Name ('CS',
1006 'CS_CONTRACTS_VALUE_NOT_FOUND');
1007 FND_MESSAGE.Set_Token('VALUE',
1008 'CONVERT TO GMT');
1009 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1010 ELSIF (x_return_status =
1011 FND_API.G_RET_STS_ERROR) THEN
1012 FND_MESSAGE.Set_Name ('CS',
1013 'CS_CONTRACTS_VALUE_NOT_FOUND');
1014 FND_MESSAGE.Set_Token('VALUE',
1015 'CONVERT TO GMT');
1016 RAISE FND_API.G_EXC_ERROR;
1017 END IF;
1018 END IF;
1019 l_cov_diff := l_cov_end_time_all_mts
1020 - l_cov_start_time_all_mts;
1021
1022 /***
1023 DBMS_Output.Put_Line('Cov diff='|| to_char(l_cov_diff));
1024 DBMS_Output.Put_Line('reaction time remaining='||
1025 to_char(l_reaction_time_remaining));
1026 **/
1027
1028 IF (l_reaction_time_remaining <= l_cov_diff) THEN
1029 l_end_time_all_mts :=
1030 l_cov_start_time_all_mts +
1031 l_reaction_time_remaining;
1032 l_reaction_time_remaining := 0;
1033
1034 --DBMS_OUTPUT.PUT_LINE('Final Time='|| to_char(l_end_time_all_mts));
1035
1036 IF (l_time_zone_id <> p_call_time_zone_id) THEN
1037 Convert_From_GMT( p_call_time_zone_id,
1038 l_end_time_all_mts,
1039 l_end_date_time,
1040 x_return_status,
1044 --DBMS_OUTPUT.PUT_LINE('Final Time From GMT='|| to_char(l_end_time_all_mts));
1041 x_msg_count,
1042 x_msg_data);
1043
1045 END IF ;
1046 Exit;
1047 ELSE
1048 l_reaction_time_used :=
1049 l_reaction_time_used + l_cov_diff;
1050 l_reaction_time_remaining :=
1051 l_reaction_time_all_mts
1052 - l_reaction_time_used;
1053 END IF;
1054 END LOOP;
1055 END IF;
1056 ELSE
1057 l_cov_end_time_all_mts := (24 * 60) - 1;
1058 l_cov_start_time_all_mts := 0;
1059 IF ((l_cov_end_time_all_mts
1060 - l_start_time_all_mts )< l_reaction_time_all_mts)THEN
1061 l_rtm_diff := l_reaction_time_all_mts
1062 - (l_cov_end_time_all_mts - l_start_time_all_mts);
1063 --DBMS_Output.Put_Line('Else Rtm diff='|| to_char(l_rtm_diff));
1064 LOOP
1065 l_end_date_time := l_end_date_time + 1;
1066 l_cov_diff := l_cov_end_time_all_mts
1067 - l_cov_start_time_all_mts;
1068 --DBMS_Output.Put_Line('Else cov. diff='|| to_char(l_cov_diff));
1069 IF (l_rtm_diff < l_cov_diff) THEN
1070 l_end_time_all_mts := l_cov_start_time_all_mts + l_rtm_diff;
1071 EXIT;
1072 ELSE
1073 l_rtm_diff := l_rtm_diff - l_cov_diff;
1074 END IF;
1075 END LOOP;
1076 ELSE
1077 l_end_time_all_mts := l_start_time_all_mts + l_reaction_time_all_mts;
1078 END IF;
1079 END IF;
1080
1081 --DBMS_OUtput.Put_Line('Before convert l_end_time_all_mts='||
1082 -- to_char(l_end_time_all_mts));
1083
1084 IF (l_end_time_all_mts <> 0) THEN
1085 convert_to_hours_mts(l_end_time_all_mts,
1086 l_end_time_hours,
1087 l_end_time_mts);
1088 ELSE
1089 l_end_time_hours := l_start_time_hours;
1090 l_end_time_mts := l_start_time_mts;
1091 END IF;
1092
1093 /***
1094 DBMS_OUtput.Put_Line('Converted date='||
1095 to_char(l_end_date_time,'DD-MM-RR') );
1096 DBMS_Output.Put_Line('End hours='|| to_char(l_end_time_hours));
1097 DBMS_Output.Put_Line('End mts='|| to_char(l_end_time_mts));
1098 ***/
1099
1100 x_Expected_End_Date_Time := TO_DATE(TO_CHAR(l_end_date_time,'DD-MM-RR')
1101 || ' '
1102 || LPAD(l_end_time_hours,2,'0')
1103 || LPAD(l_end_time_mts,2,'0'),'DD-MM-RR HH24:MI');
1104
1105 /***
1106 DBMS_Output.Put_Line('Expected end date=' ||
1107 to_char(x_expected_end_date_time,'DD-MM-RR HH24:MI') );
1108 **/
1109
1110
1111 TAPI_DEV_KIT.END_ACTIVITY(p_commit,
1112 x_msg_count,
1113 x_msg_data);
1114 x_return_status := FND_API.G_RET_STS_SUCCESS;
1115
1116 EXCEPTION
1117
1118 WHEN FND_API.G_EXC_ERROR THEN
1119 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1120 (l_api_name,
1121 G_PKG_NAME,
1122 'FND_API.G_RET_STS_ERROR',
1123 x_msg_count,
1124 x_msg_data,
1125 '_pub');
1126 APP_EXCEPTION.RAISE_EXCEPTION;
1127 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1128 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1129 (l_api_name,
1130 G_PKG_NAME,
1131 'FND_API.G_RET_STS_UNEXP_ERROR',
1132 x_msg_count,
1133 x_msg_data,
1134 '_pub');
1135 APP_EXCEPTION.RAISE_EXCEPTION;
1136
1137 END Get_Reaction_Times;
1138
1139 END CS_GET_REACTION_TIMES_PUB;