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_SVC_PLANS_PKG

Source


1 package body IEB_SVC_PLANS_PKG as
2 /* $Header: IEBSVCPB.pls 120.3 2005/09/29 06:17:12 appldev noship $ */
3 
4 procedure INSERT_ROW_B (
5   P_SVCPLN_ID             IN NUMBER,
6   P_SERVICE_PLAN_NAME     IN VARCHAR2,
7   P_DIRECTION             IN VARCHAR2,
8   P_TREATMENT             IN VARCHAR2,
9   P_PLAN_NAME             IN VARCHAR2,
10   P_DESCRIPTION           IN VARCHAR2,
11   P_OBJECT_VERSION_NUMBER IN NUMBER,
12   P_MEDIA_TYPE_ID         IN NUMBER,
13   P_OWNER_ID			 IN NUMBER,
14   X_ROWID                 IN OUT NOCOPY VARCHAR2
15 ) is
16   cursor C is select ROWID from IEB_SERVICE_PLANS
17     where SVCPLN_ID = P_SVCPLN_ID ;
18 begin
19 
20   insert into IEB_SERVICE_PLANS (
21     SVCPLN_ID,
22     SERVICE_PLAN_NAME,
23     DIRECTION,
24     TREATMENT,
25     OBJECT_VERSION_NUMBER ,
26     MEDIA_TYPE_ID,
27     CREATION_DATE,
28     CREATED_BY,
29     LAST_UPDATE_DATE,
30     LAST_UPDATED_BY,
31     LAST_UPDATE_LOGIN
32   ) values (
33     P_SVCPLN_ID,
34     P_SERVICE_PLAN_NAME,
35     P_DIRECTION,
36     P_TREATMENT,
37     P_OBJECT_VERSION_NUMBER,
38     P_MEDIA_TYPE_ID,
39     SYSDATE,
40     P_OWNER_ID,
41     SYSDATE,
42     P_OWNER_ID,
43     FND_GLOBAL.LOGIN_ID
44   );
45 
46   open c;
47   fetch c into X_ROWID;
48   if (c%notfound) then
49     close c;
50     raise no_data_found;
51   end if;
52   close c;
53 
54 end INSERT_ROW_B;
55 
56 
57 procedure INSERT_ROW_TL (
58   P_SVCPLN_ID             IN NUMBER,
59   P_PLAN_NAME             IN VARCHAR2,
60   P_DESCRIPTION           IN VARCHAR2,
61   P_OBJECT_VERSION_NUMBER IN NUMBER,
62   P_OWNER_ID			 IN NUMBER,
63   X_ROWID                 IN OUT NOCOPY VARCHAR2
64 ) is
65   cursor C is select ROWID from IEB_SERVICE_PLANS_TL
66     where SERVICE_PLAN_ID = P_SVCPLN_ID;
67 begin
68 
69   insert into IEB_SERVICE_PLANS_TL (
70     CREATION_DATE,
71     LAST_UPDATED_BY,
72     LAST_UPDATE_DATE,
73     CREATED_BY,
74     SERVICE_PLAN_ID,
75     OBJECT_VERSION_NUMBER,
76     DESCRIPTION,
77     LAST_UPDATE_LOGIN,
78     PLAN_NAME,
79     LANGUAGE,
80     SOURCE_LANG
81   ) select
82     SYSDATE,
83     P_OWNER_ID,
84     SYSDATE,
85     P_OWNER_ID,
86     P_SVCPLN_ID,
87     P_OBJECT_VERSION_NUMBER,
88     P_DESCRIPTION,
89     FND_GLOBAL.LOGIN_ID,
90     P_PLAN_NAME,
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_PLANS_TL T
98     where T.SERVICE_PLAN_ID = P_SVCPLN_ID
99     and T.LANGUAGE = L.LANGUAGE_CODE);
100 
101   open c;
102   fetch c into X_ROWID;
103   if (c%notfound) then
104     close c;
105     raise no_data_found;
106   end if;
107   close c;
108 
109 end INSERT_ROW_TL;
110 
111 procedure LOCK_ROW (
112   P_SVCPLN_ID             IN NUMBER,
113   P_SERVICE_PLAN_NAME     IN VARCHAR2,
114   P_DIRECTION             IN VARCHAR2,
115   P_TREATMENT             IN VARCHAR2,
116   P_PLAN_NAME             IN VARCHAR2,
117   P_DESCRIPTION           IN VARCHAR2,
118   P_OBJECT_VERSION_NUMBER IN NUMBER,
119   P_MEDIA_TYPE_ID         IN NUMBER
120 ) is
121   cursor c is select
122       SERVICE_PLAN_NAME,
123       DIRECTION,
124       TREATMENT,
125       OBJECT_VERSION_NUMBER,
126       MEDIA_TYPE_ID
127     FROM IEB_SERVICE_PLANS
128     WHERE SVCPLN_ID = P_SVCPLN_ID
129     FOR update of SVCPLN_ID nowait;
130   recinfo c%rowtype;
131 
132   cursor c1 is select
133       PLAN_NAME,
134       DESCRIPTION,
135       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
136     FROM IEB_SERVICE_PLANS_TL
137     WHERE SERVICE_PLAN_ID = P_SVCPLN_ID
138     AND userenv('LANG') in (LANGUAGE, SOURCE_LANG)
139     FOR update of SERVICE_PLAN_ID nowait;
140 begin
141   open c;
142   fetch c into recinfo;
143   if (c%notfound) then
144     close c;
145     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
146     app_exception.raise_exception;
147   end if;
148   close c;
149   if (    (recinfo.SERVICE_PLAN_NAME = P_SERVICE_PLAN_NAME)
150       AND (recinfo.DIRECTION = P_DIRECTION)
151       AND ((recinfo.TREATMENT = P_TREATMENT)
152            OR ((recinfo.TREATMENT is null) AND (P_TREATMENT is null)))
153       AND ((recinfo.MEDIA_TYPE_ID = P_MEDIA_TYPE_ID)
154            OR ((recinfo.MEDIA_TYPE_ID is null) AND (P_MEDIA_TYPE_ID is null)))
155       AND (recinfo.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER))
156   then
157     null;
158   else
159     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
160     app_exception.raise_exception;
161   end if;
162 
163   for tlinfo in c1 loop
164     if (tlinfo.BASELANG = 'Y') then
165       if (    (tlinfo.PLAN_NAME = P_PLAN_NAME)
166           AND (tlinfo.DESCRIPTION = P_DESCRIPTION)
167       ) then
168         null;
169       else
170         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
171         app_exception.raise_exception;
172       end if;
173     end if;
174   end loop;
175   return;
176 end LOCK_ROW;
177 
178 procedure UPDATE_ROW_B (
179   P_SVCPLN_ID             IN NUMBER,
180   P_SERVICE_PLAN_NAME             IN VARCHAR2,
181   P_DIRECTION             IN VARCHAR2,
182   P_TREATMENT             IN VARCHAR2,
183   P_PLAN_NAME             IN VARCHAR2,
184   P_DESCRIPTION           IN VARCHAR2,
185   P_OBJECT_VERSION_NUMBER IN NUMBER,
186   P_MEDIA_TYPE_ID         IN NUMBER,
187   P_OWNER_ID			 IN NUMBER
188 ) is
189 begin
190   update IEB_SERVICE_PLANS set
191     SERVICE_PLAN_NAME = P_SERVICE_PLAN_NAME,
192     DIRECTION = P_DIRECTION,
193     TREATMENT = P_TREATMENT,
194     OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER,
195     MEDIA_TYPE_ID = P_MEDIA_TYPE_ID,
196     LAST_UPDATE_DATE = SYSDATE,
197     LAST_UPDATED_BY = P_OWNER_ID,
198     LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
199   where SVCPLN_ID = P_SVCPLN_ID;
200 
201   if (sql%notfound) then
202     raise no_data_found;
203   end if;
204 end UPDATE_ROW_B;
205 
206 procedure UPDATE_ROW_TL (
207   P_SVCPLN_ID             IN NUMBER,
208   P_PLAN_NAME             IN VARCHAR2,
209   P_DESCRIPTION           IN VARCHAR2,
210   P_OBJECT_VERSION_NUMBER IN NUMBER,
211   P_OWNER_ID              IN NUMBER
212 ) is
213 begin
214   update IEB_SERVICE_PLANS_TL set
215     PLAN_NAME = P_PLAN_NAME,
216     DESCRIPTION = P_DESCRIPTION,
217     OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER,
218     LAST_UPDATE_DATE = SYSDATE,
219     LAST_UPDATED_BY = P_OWNER_ID,
220     LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
221     SOURCE_LANG = userenv('LANG')
222   where SERVICE_PLAN_ID = P_SVCPLN_ID
223   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
224 
225   if (sql%notfound) then
226     raise no_data_found;
227   end if;
228 end UPDATE_ROW_TL;
229 
230 
231 procedure DELETE_ROW (
232   P_SVCPLN_ID in NUMBER
233 ) is
234 begin
235   delete from IEB_SERVICE_PLANS
236   where SVCPLN_ID = P_SVCPLN_ID;
237 
238   if (sql%notfound) then
239     raise no_data_found;
240   end if;
241 
242   delete from IEB_SERVICE_PLANS_TL
243   where SERVICE_PLAN_ID = P_SVCPLN_ID;
244 
245   if (sql%notfound) then
246     raise no_data_found;
247   end if;
248 end DELETE_ROW;
249 
250 procedure ADD_LANGUAGE
251 is
252 begin
253   delete from IEB_SERVICE_PLANS_TL T
254   where not exists
255     (select NULL
256     from IEB_SERVICE_PLANS B
257     where B.SVCPLN_ID = T.SERVICE_PLAN_ID
258     );
259 
260   update IEB_SERVICE_PLANS_TL T set (
261       PLAN_NAME,
262       DESCRIPTION
263     ) = (select
264       B.PLAN_NAME,
265       B.DESCRIPTION
266     from IEB_SERVICE_PLANS_TL B
267     where B.SERVICE_PLAN_ID = T.SERVICE_PLAN_ID
268     and B.LANGUAGE = T.SOURCE_LANG)
269   where (
270       T.SERVICE_PLAN_ID,
271       T.LANGUAGE
272   ) in (select
273       SUBT.SERVICE_PLAN_ID,
274       SUBT.LANGUAGE
275     from IEB_SERVICE_PLANS_TL SUBB, IEB_SERVICE_PLANS_TL SUBT
276     where SUBB.SERVICE_PLAN_ID = SUBT.SERVICE_PLAN_ID
277     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
278     and (SUBB.PLAN_NAME <> SUBT.PLAN_NAME
279       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
280   ));
281 
282   insert into IEB_SERVICE_PLANS_TL (
283     CREATION_DATE,
284     LAST_UPDATED_BY,
285     LAST_UPDATE_DATE,
286     CREATED_BY,
287     SERVICE_PLAN_ID,
288     OBJECT_VERSION_NUMBER,
289     DESCRIPTION,
290     LAST_UPDATE_LOGIN,
291     PLAN_NAME,
292     LANGUAGE,
293     SOURCE_LANG
294   ) select
295     B.CREATION_DATE,
296     B.LAST_UPDATED_BY,
297     B.LAST_UPDATE_DATE,
298     B.CREATED_BY,
299     B.SERVICE_PLAN_ID,
300     B.OBJECT_VERSION_NUMBER,
301     B.DESCRIPTION,
302     B.LAST_UPDATE_LOGIN,
303     B.PLAN_NAME,
304     L.LANGUAGE_CODE,
305     B.SOURCE_LANG
306   from IEB_SERVICE_PLANS_TL B, FND_LANGUAGES L
307   where L.INSTALLED_FLAG in ('I', 'B')
308   and B.LANGUAGE = userenv('LANG')
309   and not exists
310     (select NULL
311     from IEB_SERVICE_PLANS_TL T
312     where T.SERVICE_PLAN_ID = B.SERVICE_PLAN_ID
313     and T.LANGUAGE = L.LANGUAGE_CODE);
314 end ADD_LANGUAGE;
315 
316 procedure TRANSLATE_ROW (
317   P_SVCPLN_ID   IN NUMBER,
318   P_PLAN_NAME   IN VARCHAR2,
319   P_DESCRIPTION IN VARCHAR2,
320   P_OWNER       IN VARCHAR2
321 ) IS
322 
323 BEGIN
324   --only update rows that have not been altered by user
325   UPDATE IEB_SERVICE_PLANS_TL
326   SET
327     PLAN_NAME = P_PLAN_NAME,
328     SOURCE_LANG = userenv( 'LANG' ),
329     DESCRIPTION = P_DESCRIPTION,
330     LAST_UPDATE_DATE = SYSDATE,
331     LAST_UPDATED_BY = fnd_load_util.owner_id(P_OWNER),
332     LAST_UPDATE_LOGIN = 0
333   WHERE
334     SERVICE_PLAN_ID = P_SVCPLN_ID
335   AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
336 
337 END TRANSLATE_ROW;
338 
339 
340 procedure LOAD_ROW (
341   P_SVCPLN_ID             IN NUMBER,
342   P_SERVICE_PLAN_NAME     IN VARCHAR2,
343   P_DIRECTION             IN VARCHAR2,
344   P_TREATMENT             IN VARCHAR2,
345   P_PLAN_NAME             IN VARCHAR2,
346   P_DESCRIPTION           IN VARCHAR2,
347   P_OBJECT_VERSION_NUMBER IN NUMBER,
348   P_MEDIA_TYPE_ID         IN NUMBER,
349   P_OWNER                 IN VARCHAR2
350 ) IS
351 
352 BEGIN
353 
354     DECLARE
355        user_id NUMBER := 0;
356        rowid   VARCHAR2(50);
357     BEGIN
358 
359       user_id := fnd_load_util.owner_id(P_OWNER);
360 
361       BEGIN
362         UPDATE_ROW_B (
363           P_SVCPLN_ID             ,
364           P_SERVICE_PLAN_NAME     ,
365           P_DIRECTION             ,
366           P_TREATMENT             ,
367           P_PLAN_NAME             ,
368           P_DESCRIPTION           ,
369           P_OBJECT_VERSION_NUMBER ,
370           P_MEDIA_TYPE_ID         ,
371 		user_id
372         );
373 
374         EXCEPTION
375           when no_data_found then
376 
377         INSERT_ROW_B (
378           P_SVCPLN_ID             ,
379           P_SERVICE_PLAN_NAME     ,
380           P_DIRECTION             ,
381           P_TREATMENT             ,
382           P_PLAN_NAME             ,
383           P_DESCRIPTION           ,
384           P_OBJECT_VERSION_NUMBER ,
385           P_MEDIA_TYPE_ID         ,
386 		user_id,
387           rowid
388         );
389       END;
390 
391       BEGIN
392         UPDATE_ROW_TL (
393           P_SVCPLN_ID             ,
394           P_PLAN_NAME             ,
395           P_DESCRIPTION           ,
396           P_OBJECT_VERSION_NUMBER ,
397 		user_id
398         );
399 
400         EXCEPTION
401           when no_data_found then
402 
403         INSERT_ROW_TL (
404           P_SVCPLN_ID             ,
405           P_PLAN_NAME             ,
406           P_DESCRIPTION           ,
407           P_OBJECT_VERSION_NUMBER ,
408 		user_id,
409           rowid
410         );
411       END;
412     END;
413 
414 END LOAD_ROW;
415 
416 procedure LOAD_SEED_ROW (
417   P_SVCPLN_ID             IN NUMBER,
418   P_SERVICE_PLAN_NAME     IN VARCHAR2,
419   P_DIRECTION             IN VARCHAR2,
420   P_TREATMENT             IN VARCHAR2,
421   P_PLAN_NAME             IN VARCHAR2,
422   P_DESCRIPTION           IN VARCHAR2,
423   P_OBJECT_VERSION_NUMBER IN NUMBER,
424   P_MEDIA_TYPE_ID         IN NUMBER,
425   P_OWNER                 IN VARCHAR2,
426   P_UPLOAD_MODE           IN VARCHAR2
427  ) IS
428  BEGIN
429  IF (P_UPLOAD_MODE = 'NLS') THEN
430    IEB_SVC_PLANS_PKG.TRANSLATE_ROW (
431                   P_SVCPLN_ID,
432 	             P_PLAN_NAME,
433 			   P_DESCRIPTION,
434 			   P_OWNER );
435  ELSE
436    IEB_SVC_PLANS_PKG.LOAD_ROW(
437              P_SVCPLN_ID,
438              P_SERVICE_PLAN_NAME,
439 	        P_DIRECTION,
440 	        P_TREATMENT,
441 	        P_PLAN_NAME,
442 	        P_DESCRIPTION,
443 	        P_OBJECT_VERSION_NUMBER,
444 	        P_MEDIA_TYPE_ID,
445 	        P_OWNER );
446   END IF;
447  END LOAD_SEED_ROW;
448 
449 end IEB_SVC_PLANS_PKG;