eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IEB_SERVICE_LEVELS_PKG

Source


1 package body IEB_SERVICE_LEVELS_PKG as
2 /* $Header: IEBSVCLVLB.pls 120.3 2005/09/29 06:09:03 appldev ship $ */
3 
4 procedure INSERT_ROW (
5   X_ROWID in out nocopy VARCHAR2,
6   X_SERVICE_LEVEL_ID in NUMBER,
7   X_DIRECTION in VARCHAR2,
8   X_MANDATORY_FLAG in VARCHAR2,
9   X_HOURLY_QUOTA in NUMBER,
10   X_MIN_AGENTS in NUMBER,
11   X_GOAL_PERCENT in NUMBER,
12   X_GOAL_TIME in NUMBER,
13   X_MAX_WAIT_TIME in NUMBER,
14   X_REROUTE_TIME in NUMBER,
15   X_REROUTE_WARNING_TIME in NUMBER,
16   X_OBJECT_VERSION_NUMBER in NUMBER,
17   X_SECURITY_GROUP_ID in NUMBER,
18   X_LEVEL_NAME in VARCHAR2,
19   X_DESCRIPTION in VARCHAR2,
20   X_CREATION_DATE in DATE,
21   X_CREATED_BY in NUMBER,
22   X_LAST_UPDATE_DATE in DATE,
23   X_LAST_UPDATED_BY in NUMBER,
24   X_LAST_UPDATE_LOGIN in NUMBER
25 ) is
26   cursor C is select ROWID from IEB_SERVICE_LEVELS_B
27     where SERVICE_LEVEL_ID = X_SERVICE_LEVEL_ID;
28 begin
29   insert into IEB_SERVICE_LEVELS_B (
30     SERVICE_LEVEL_ID,
31     DIRECTION,
32     MANDATORY_FLAG,
33     HOURLY_QUOTA,
34     MIN_AGENTS,
35     GOAL_PERCENT,
36     GOAL_TIME,
37     MAX_WAIT_TIME,
38     REROUTE_TIME,
39     REROUTE_WARNING_TIME,
40     OBJECT_VERSION_NUMBER,
41     SECURITY_GROUP_ID,
42     CREATION_DATE,
43     CREATED_BY,
44     LAST_UPDATE_DATE,
45     LAST_UPDATED_BY,
46     LAST_UPDATE_LOGIN
47   ) values (
48     X_SERVICE_LEVEL_ID,
49     X_DIRECTION,
50     X_MANDATORY_FLAG,
51     X_HOURLY_QUOTA,
52     X_MIN_AGENTS,
53     X_GOAL_PERCENT,
54     X_GOAL_TIME,
55     X_MAX_WAIT_TIME,
56     X_REROUTE_TIME,
57     X_REROUTE_WARNING_TIME,
58     X_OBJECT_VERSION_NUMBER,
59     X_SECURITY_GROUP_ID,
60     X_CREATION_DATE,
61     X_CREATED_BY,
62     X_LAST_UPDATE_DATE,
63     X_LAST_UPDATED_BY,
64     X_LAST_UPDATE_LOGIN
65   );
66 
67   insert into IEB_SERVICE_LEVELS_TL (
68     SERVICE_LEVEL_ID,
69     CREATED_BY,
70     CREATION_DATE,
71     LAST_UPDATED_BY,
72     LAST_UPDATE_DATE,
73     LAST_UPDATE_LOGIN,
74     LEVEL_NAME,
75     DESCRIPTION,
76     OBJECT_VERSION_NUMBER,
77     SECURITY_GROUP_ID,
78     LANGUAGE,
79     SOURCE_LANG
80   ) select
81     X_SERVICE_LEVEL_ID,
82     X_CREATED_BY,
83     X_CREATION_DATE,
84     X_LAST_UPDATED_BY,
85     X_LAST_UPDATE_DATE,
86     X_LAST_UPDATE_LOGIN,
87     X_LEVEL_NAME,
88     X_DESCRIPTION,
89     X_OBJECT_VERSION_NUMBER,
90     X_SECURITY_GROUP_ID,
91     L.LANGUAGE_CODE,
92     userenv('LANG')
93   from FND_LANGUAGES L
94   where L.INSTALLED_FLAG in ('I', 'B')
95   and not exists
96     (select NULL
97     from IEB_SERVICE_LEVELS_TL T
98     where T.SERVICE_LEVEL_ID = X_SERVICE_LEVEL_ID
99     and T.SERVICE_LEVEL_ID = X_SERVICE_LEVEL_ID
100     and T.LANGUAGE = L.LANGUAGE_CODE);
101 
102   open c;
103   fetch c into X_ROWID;
104   if (c%notfound) then
105     close c;
106     raise no_data_found;
107   end if;
108   close c;
109 
110 end INSERT_ROW;
111 
112 procedure LOCK_ROW (
113   X_SERVICE_LEVEL_ID in NUMBER,
114   X_DIRECTION in VARCHAR2,
115   X_MANDATORY_FLAG in VARCHAR2,
116   X_HOURLY_QUOTA in NUMBER,
117   X_MIN_AGENTS in NUMBER,
118   X_GOAL_PERCENT in NUMBER,
119   X_GOAL_TIME in NUMBER,
120   X_MAX_WAIT_TIME in NUMBER,
121   X_REROUTE_TIME in NUMBER,
122   X_REROUTE_WARNING_TIME in NUMBER,
123   X_OBJECT_VERSION_NUMBER in NUMBER,
124   X_SECURITY_GROUP_ID in NUMBER,
125   X_LEVEL_NAME in VARCHAR2,
126   X_DESCRIPTION in VARCHAR2
127 ) is
128   cursor c is select
129       DIRECTION,
130       MANDATORY_FLAG,
131       HOURLY_QUOTA,
132       MIN_AGENTS,
133       GOAL_PERCENT,
134       GOAL_TIME,
135       MAX_WAIT_TIME,
136       REROUTE_TIME,
137       REROUTE_WARNING_TIME,
138       OBJECT_VERSION_NUMBER,
139       SECURITY_GROUP_ID
140     from IEB_SERVICE_LEVELS_B
141     where SERVICE_LEVEL_ID = X_SERVICE_LEVEL_ID
142     and SERVICE_LEVEL_ID = X_SERVICE_LEVEL_ID
143     for update of SERVICE_LEVEL_ID nowait;
144   recinfo c%rowtype;
145 
146   cursor c1 is select
147       LEVEL_NAME,
148       DESCRIPTION,
149       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
150     from IEB_SERVICE_LEVELS_TL
151     where SERVICE_LEVEL_ID = X_SERVICE_LEVEL_ID
152     and SERVICE_LEVEL_ID = X_SERVICE_LEVEL_ID
153     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
154     for update of SERVICE_LEVEL_ID nowait;
155 begin
156   open c;
157   fetch c into recinfo;
158   if (c%notfound) then
159     close c;
160     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
161     app_exception.raise_exception;
162   end if;
163   close c;
164   if (    (recinfo.DIRECTION = X_DIRECTION)
165       AND ((recinfo.MANDATORY_FLAG = X_MANDATORY_FLAG)
166            OR ((recinfo.MANDATORY_FLAG is null) AND (X_MANDATORY_FLAG is null)))
167       AND ((recinfo.HOURLY_QUOTA = X_HOURLY_QUOTA)
168            OR ((recinfo.HOURLY_QUOTA is null) AND (X_HOURLY_QUOTA is null)))
169       AND ((recinfo.MIN_AGENTS = X_MIN_AGENTS)
170            OR ((recinfo.MIN_AGENTS is null) AND (X_MIN_AGENTS is null)))
171       AND ((recinfo.GOAL_PERCENT = X_GOAL_PERCENT)
172            OR ((recinfo.GOAL_PERCENT is null) AND (X_GOAL_PERCENT is null)))
173       AND ((recinfo.GOAL_TIME = X_GOAL_TIME)
174            OR ((recinfo.GOAL_TIME is null) AND (X_GOAL_TIME is null)))
175       AND ((recinfo.MAX_WAIT_TIME = X_MAX_WAIT_TIME)
176            OR ((recinfo.MAX_WAIT_TIME is null) AND (X_MAX_WAIT_TIME is null)))
177       AND ((recinfo.REROUTE_TIME = X_REROUTE_TIME)
178            OR ((recinfo.REROUTE_TIME is null) AND (X_REROUTE_TIME is null)))
179       AND ((recinfo.REROUTE_WARNING_TIME = X_REROUTE_WARNING_TIME)
180            OR ((recinfo.REROUTE_WARNING_TIME is null) AND (X_REROUTE_WARNING_TIME is null)))
181       AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
182            OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
183       AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
184            OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
185   ) then
186     null;
187   else
188     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
189     app_exception.raise_exception;
190   end if;
191 
192   for tlinfo in c1 loop
193     if (tlinfo.BASELANG = 'Y') then
194       if (    (tlinfo.LEVEL_NAME = X_LEVEL_NAME)
195           AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
196                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
197       ) then
198         null;
199       else
200         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
201         app_exception.raise_exception;
202       end if;
203     end if;
204   end loop;
205   return;
206 end LOCK_ROW;
207 
208 procedure UPDATE_ROW (
209   X_SERVICE_LEVEL_ID in NUMBER,
210   X_DIRECTION in VARCHAR2,
211   X_MANDATORY_FLAG in VARCHAR2,
212   X_HOURLY_QUOTA in NUMBER,
213   X_MIN_AGENTS in NUMBER,
214   X_GOAL_PERCENT in NUMBER,
215   X_GOAL_TIME in NUMBER,
216   X_MAX_WAIT_TIME in NUMBER,
217   X_REROUTE_TIME in NUMBER,
218   X_REROUTE_WARNING_TIME in NUMBER,
219   X_OBJECT_VERSION_NUMBER in NUMBER,
220   X_SECURITY_GROUP_ID in NUMBER,
221   X_LEVEL_NAME in VARCHAR2,
222   X_DESCRIPTION in VARCHAR2,
223   X_LAST_UPDATE_DATE in DATE,
224   X_LAST_UPDATED_BY in NUMBER,
225   X_LAST_UPDATE_LOGIN in NUMBER
226 ) is
227 begin
228   update IEB_SERVICE_LEVELS_B set
229     DIRECTION = X_DIRECTION,
230     MANDATORY_FLAG = X_MANDATORY_FLAG,
231     HOURLY_QUOTA = X_HOURLY_QUOTA,
232     MIN_AGENTS = X_MIN_AGENTS,
233     GOAL_PERCENT = X_GOAL_PERCENT,
234     GOAL_TIME = X_GOAL_TIME,
235     MAX_WAIT_TIME = X_MAX_WAIT_TIME,
236     REROUTE_TIME = X_REROUTE_TIME,
237     REROUTE_WARNING_TIME = X_REROUTE_WARNING_TIME,
238     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
239     SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
240     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
241     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
242     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
243   where SERVICE_LEVEL_ID = X_SERVICE_LEVEL_ID
244   and SERVICE_LEVEL_ID = X_SERVICE_LEVEL_ID;
245 
246   if (sql%notfound) then
247     raise no_data_found;
248   end if;
249 
250   update IEB_SERVICE_LEVELS_TL set
251     LEVEL_NAME = X_LEVEL_NAME,
252     DESCRIPTION = X_DESCRIPTION,
253     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
254     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
255     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
256     SOURCE_LANG = userenv('LANG')
257   where SERVICE_LEVEL_ID = X_SERVICE_LEVEL_ID
258   and SERVICE_LEVEL_ID = X_SERVICE_LEVEL_ID
259   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
260 
261   if (sql%notfound) then
262     raise no_data_found;
263   end if;
264 end UPDATE_ROW;
265 
266 procedure DELETE_ROW (
267   X_SERVICE_LEVEL_ID in NUMBER
268 ) is
269 begin
270   delete from IEB_SERVICE_LEVELS_TL
271   where SERVICE_LEVEL_ID = X_SERVICE_LEVEL_ID;
272 
273   if (sql%notfound) then
274     raise no_data_found;
275   end if;
276 
277   delete from IEB_SERVICE_LEVELS_B
278   where SERVICE_LEVEL_ID = X_SERVICE_LEVEL_ID;
279 
280   if (sql%notfound) then
281     raise no_data_found;
282   end if;
283 end DELETE_ROW;
284 
285 procedure LOAD_ROW (
286   p_service_level_id IN NUMBER,
287   p_mandatory_flag   IN VARCHAR2,
288   p_direction IN VARCHAR2,
289   p_hourly_quota IN NUMBER,
290   p_min_agents IN NUMBER,
291   p_goal_percent IN NUMBER,
292   p_goal_time IN NUMBER,
293   p_max_wait_time IN NUMBER,
294   p_reroute_time IN NUMBER,
295   p_reroute_warning_time IN NUMBER,
296   p_level_name IN VARCHAR2,
297   p_DESCRIPTION IN VARCHAR2,
298   p_OWNER IN VARCHAR2) is
299 
300   BEGIN
301     DECLARE
302         user_id  number := 0;
303         l_row_id varchar2(80);
304     BEGIN
305 
306 	 user_id := fnd_load_util.owner_id(p_OWNER);
307 
308     --select IEB_SVC_LEVEL_S1.nextval into l_row_id from dual;
309 
310   UPDATE_ROW(
311         X_SERVICE_LEVEL_ID => p_service_level_id ,
312         X_DIRECTION => p_direction,
313         X_MANDATORY_FLAG => p_mandatory_flag ,
314         X_HOURLY_QUOTA => p_hourly_quota ,
315         X_MIN_AGENTS => p_min_agents ,
316         X_GOAL_PERCENT => p_goal_percent,
317         X_GOAL_TIME => p_goal_time,
318         X_MAX_WAIT_TIME => p_max_wait_time,
319         X_REROUTE_TIME => p_reroute_time ,
320         X_REROUTE_WARNING_TIME => p_reroute_warning_time,
321         X_LEVEL_NAME => p_level_name,
322         X_DESCRIPTION => p_description ,
323         X_OBJECT_VERSION_NUMBER => NULL,
324         X_SECURITY_GROUP_ID => NULL,
325         X_LAST_UPDATE_DATE => sysdate,
326         X_LAST_UPDATED_BY => user_id,
327         X_LAST_UPDATE_LOGIN =>  1 );
328 
329       EXCEPTION
330          WHEN NO_DATA_FOUND THEN
331         INSERT_ROW (
332           X_ROWID => l_row_id,
333           X_SERVICE_LEVEL_ID => p_service_level_id ,
334           X_DIRECTION => p_direction ,
335           X_MANDATORY_FLAG => p_mandatory_flag ,
336           X_HOURLY_QUOTA => p_hourly_quota ,
337           X_MIN_AGENTS => p_min_agents ,
338           X_GOAL_PERCENT => p_goal_percent ,
339           X_GOAL_TIME => p_goal_time ,
340           X_MAX_WAIT_TIME => p_max_wait_time ,
341           X_REROUTE_TIME => p_reroute_time ,
342           X_REROUTE_WARNING_TIME => p_reroute_warning_time ,
343           X_OBJECT_VERSION_NUMBER => NULL ,
344           X_SECURITY_GROUP_ID => NULL ,
345           X_LEVEL_NAME => p_level_name ,
346           X_DESCRIPTION => p_description ,
347           X_CREATION_DATE => sysdate ,
348           X_CREATED_BY => user_id ,
349           X_LAST_UPDATE_DATE => sysdate ,
350           X_LAST_UPDATED_BY => user_id ,
351           X_LAST_UPDATE_LOGIN => 1 );
352 
353       END;
354 end LOAD_ROW;
355 
356 procedure LOAD_SEED_ROW (
357   p_service_level_id IN NUMBER,
358   p_mandatory_flag   IN VARCHAR2,
359   p_direction IN VARCHAR2,
360   p_hourly_quota IN NUMBER,
361   p_min_agents IN NUMBER,
362   p_goal_percent IN NUMBER,
363   p_goal_time IN NUMBER,
364   p_max_wait_time IN NUMBER,
365   p_reroute_time IN NUMBER,
366   p_reroute_warning_time IN NUMBER,
367   p_level_name IN VARCHAR2,
368   p_DESCRIPTION IN VARCHAR2,
369   p_OWNER IN VARCHAR2,
370   p_UPLOAD_MODE IN VARCHAR2) is
371 BEGIN
372   if (p_UPLOAD_MODE = 'NLS') then
373     IEB_SERVICE_LEVELS_PKG.TRANSLATE_ROW (
374 				       p_SERVICE_LEVEL_ID,
375 				       p_LEVEL_NAME,
376 				       p_DESCRIPTION,
377 				       p_OWNER);
378   else
379     IEB_SERVICE_LEVELS_PKG.LOAD_ROW (
380               p_service_level_id ,
381               p_mandatory_flag ,
382               p_direction ,
383               p_hourly_quota ,
384               p_min_agents ,
385               p_goal_percent ,
386               p_goal_time ,
387               p_max_wait_time ,
388               p_reroute_time ,
389               p_reroute_warning_time ,
390               p_level_name ,
391               p_DESCRIPTION ,
392               p_OWNER );
393   end if;
394 END LOAD_SEED_ROW;
395 
396 
397 procedure TRANSLATE_ROW (
398   X_SERVICE_LEVEL_ID IN NUMBER,
399   X_LEVEL_NAME       IN VARCHAR2,
400   X_DESCRIPTION      IN VARCHAR2,
401   X_OWNER            IN VARCHAR2) is
402 
403     BEGIN
404       DECLARE
405         user_id  number := 0;
406       BEGIN
407 
408 	   user_id := fnd_load_util.owner_id(X_OWNER);
409 
410        UPDATE ieb_service_levels_tl
411         SET
412           last_update_date=sysdate
413         , last_updated_by=user_id
414         , last_update_login=1
415         , LEVEL_NAME = DECODE(X_LEVEL_NAME,FND_API.G_MISS_CHAR,
416                                          NULL,X_LEVEL_NAME)
417         , DESCRIPTION = DECODE(X_DESCRIPTION,FND_API.G_MISS_CHAR,NULL,X_DESCRIPTION)
418         , source_lang = USERENV('LANG')
419          WHERE
420             service_level_id = X_SERVICE_LEVEL_ID
421          AND USERENV('LANG') IN (language, source_lang);
422 
423       END;
424 
425 end TRANSLATE_ROW;
426 
427 procedure ADD_LANGUAGE
428 is
429 begin
430   delete from IEB_SERVICE_LEVELS_TL T
431   where not exists
432     (select NULL
433     from IEB_SERVICE_LEVELS_B B
434     where B.SERVICE_LEVEL_ID = T.SERVICE_LEVEL_ID
435     and B.SERVICE_LEVEL_ID = T.SERVICE_LEVEL_ID
436     );
437 
438   update IEB_SERVICE_LEVELS_TL T set (
439       LEVEL_NAME,
440       DESCRIPTION
441     ) = (select
442       B.LEVEL_NAME,
443       B.DESCRIPTION
444     from IEB_SERVICE_LEVELS_TL B
445     where B.SERVICE_LEVEL_ID = T.SERVICE_LEVEL_ID
446     and B.SERVICE_LEVEL_ID = T.SERVICE_LEVEL_ID
447     and B.LANGUAGE = T.SOURCE_LANG)
448   where (
449       T.SERVICE_LEVEL_ID,
450       T.SERVICE_LEVEL_ID,
451       T.LANGUAGE
452   ) in (select
453       SUBT.SERVICE_LEVEL_ID,
454       SUBT.SERVICE_LEVEL_ID,
455       SUBT.LANGUAGE
456     from IEB_SERVICE_LEVELS_TL SUBB, IEB_SERVICE_LEVELS_TL SUBT
457     where SUBB.SERVICE_LEVEL_ID = SUBT.SERVICE_LEVEL_ID
458     and SUBB.SERVICE_LEVEL_ID = SUBT.SERVICE_LEVEL_ID
459     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
460     and (SUBB.LEVEL_NAME <> SUBT.LEVEL_NAME
461       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
462       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
463       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
464   ));
465 
466   insert into IEB_SERVICE_LEVELS_TL (
467     SERVICE_LEVEL_ID,
468     CREATED_BY,
469     CREATION_DATE,
470     LAST_UPDATED_BY,
471     LAST_UPDATE_DATE,
472     LAST_UPDATE_LOGIN,
473     LEVEL_NAME,
474     DESCRIPTION,
475     OBJECT_VERSION_NUMBER,
476     SECURITY_GROUP_ID,
477     LANGUAGE,
478     SOURCE_LANG
479   ) select
480     B.SERVICE_LEVEL_ID,
481     B.CREATED_BY,
482     B.CREATION_DATE,
483     B.LAST_UPDATED_BY,
484     B.LAST_UPDATE_DATE,
485     B.LAST_UPDATE_LOGIN,
486     B.LEVEL_NAME,
487     B.DESCRIPTION,
488     B.OBJECT_VERSION_NUMBER,
489     B.SECURITY_GROUP_ID,
490     L.LANGUAGE_CODE,
491     B.SOURCE_LANG
492   from IEB_SERVICE_LEVELS_TL B, FND_LANGUAGES L
493   where L.INSTALLED_FLAG in ('I', 'B')
494   and B.LANGUAGE = userenv('LANG')
495   and not exists
496     (select NULL
497     from IEB_SERVICE_LEVELS_TL T
498     where T.SERVICE_LEVEL_ID = B.SERVICE_LEVEL_ID
499     and T.SERVICE_LEVEL_ID = B.SERVICE_LEVEL_ID
500     and T.LANGUAGE = L.LANGUAGE_CODE);
501 end ADD_LANGUAGE;
502 
503 end IEB_SERVICE_LEVELS_PKG;