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.PRP_GROUPS_PKG

Source


1 package body PRP_GROUPS_PKG as
2 /* $Header: PRPTGRPB.pls 120.1 2005/10/21 17:38:59 hekkiral noship $ */
3 procedure INSERT_ROW (
4   X_ROWID in out nocopy VARCHAR2,
5   X_GROUP_ID in NUMBER,
6   X_OBJECT_VERSION_NUMBER in NUMBER,
7   X_ATTRIBUTE_CATEGORY in VARCHAR2,
8   X_ATTRIBUTE1 in VARCHAR2,
9   X_ATTRIBUTE2 in VARCHAR2,
10   X_ATTRIBUTE3 in VARCHAR2,
11   X_ATTRIBUTE4 in VARCHAR2,
12   X_ATTRIBUTE5 in VARCHAR2,
13   X_ATTRIBUTE6 in VARCHAR2,
14   X_ATTRIBUTE7 in VARCHAR2,
15   X_ATTRIBUTE8 in VARCHAR2,
16   X_ATTRIBUTE9 in VARCHAR2,
17   X_ATTRIBUTE10 in VARCHAR2,
18   X_ATTRIBUTE11 in VARCHAR2,
19   X_ATTRIBUTE12 in VARCHAR2,
20   X_ATTRIBUTE13 in VARCHAR2,
21   X_ATTRIBUTE14 in VARCHAR2,
22   X_ATTRIBUTE15 in VARCHAR2,
23   X_GROUP_NAME in VARCHAR2,
24   X_GROUP_DESC in VARCHAR2,
25   X_CREATION_DATE in DATE,
26   X_CREATED_BY in NUMBER,
27   X_LAST_UPDATE_DATE in DATE,
28   X_LAST_UPDATED_BY in NUMBER,
29   X_LAST_UPDATE_LOGIN in NUMBER
30 ) is
31   cursor C is select ROWID from PRP_GROUPS_B
32     where GROUP_ID = X_GROUP_ID
33     ;
34 begin
35   insert into PRP_GROUPS_B (
36     GROUP_ID,
37     OBJECT_VERSION_NUMBER,
38     ATTRIBUTE_CATEGORY,
39     ATTRIBUTE1,
40     ATTRIBUTE2,
41     ATTRIBUTE3,
42     ATTRIBUTE4,
43     ATTRIBUTE5,
44     ATTRIBUTE6,
45     ATTRIBUTE7,
46     ATTRIBUTE8,
47     ATTRIBUTE9,
48     ATTRIBUTE10,
49     ATTRIBUTE11,
50     ATTRIBUTE12,
51     ATTRIBUTE13,
52     ATTRIBUTE14,
53     ATTRIBUTE15,
54     CREATION_DATE,
55     CREATED_BY,
56     LAST_UPDATE_DATE,
57     LAST_UPDATED_BY,
58     LAST_UPDATE_LOGIN
59   ) values (
60     X_GROUP_ID,
61     X_OBJECT_VERSION_NUMBER,
62     X_ATTRIBUTE_CATEGORY,
63     X_ATTRIBUTE1,
64     X_ATTRIBUTE2,
65     X_ATTRIBUTE3,
66     X_ATTRIBUTE4,
67     X_ATTRIBUTE5,
68     X_ATTRIBUTE6,
69     X_ATTRIBUTE7,
70     X_ATTRIBUTE8,
71     X_ATTRIBUTE9,
72     X_ATTRIBUTE10,
73     X_ATTRIBUTE11,
74     X_ATTRIBUTE12,
75     X_ATTRIBUTE13,
76     X_ATTRIBUTE14,
77     X_ATTRIBUTE15,
78     X_CREATION_DATE,
79     X_CREATED_BY,
80     X_LAST_UPDATE_DATE,
81     X_LAST_UPDATED_BY,
82     X_LAST_UPDATE_LOGIN
83   );
84 
85   insert into PRP_GROUPS_TL (
86     LAST_UPDATE_DATE,
87     LAST_UPDATE_LOGIN,
88     GROUP_ID,
89     GROUP_NAME,
90     GROUP_DESC,
91     CREATED_BY,
92     CREATION_DATE,
93     LAST_UPDATED_BY,
94     LANGUAGE,
95     SOURCE_LANG
96   ) select
97     X_LAST_UPDATE_DATE,
98     X_LAST_UPDATE_LOGIN,
99     X_GROUP_ID,
100     X_GROUP_NAME,
101     X_GROUP_DESC,
102     X_CREATED_BY,
103     X_CREATION_DATE,
104     X_LAST_UPDATED_BY,
105     L.LANGUAGE_CODE,
106     userenv('LANG')
107   from FND_LANGUAGES L
108   where L.INSTALLED_FLAG in ('I', 'B')
109   and not exists
110     (select NULL
111     from PRP_GROUPS_TL T
112     where T.GROUP_ID = X_GROUP_ID
113     and T.LANGUAGE = L.LANGUAGE_CODE);
114 
115   open c;
116   fetch c into X_ROWID;
117   if (c%notfound) then
118     close c;
119     raise no_data_found;
120   end if;
121   close c;
122 
123 end INSERT_ROW;
124 
125 procedure LOCK_ROW (
126   X_GROUP_ID in NUMBER,
127   X_OBJECT_VERSION_NUMBER in NUMBER,
128   X_ATTRIBUTE_CATEGORY in VARCHAR2,
129   X_ATTRIBUTE1 in VARCHAR2,
130   X_ATTRIBUTE2 in VARCHAR2,
131   X_ATTRIBUTE3 in VARCHAR2,
132   X_ATTRIBUTE4 in VARCHAR2,
133   X_ATTRIBUTE5 in VARCHAR2,
134   X_ATTRIBUTE6 in VARCHAR2,
135   X_ATTRIBUTE7 in VARCHAR2,
136   X_ATTRIBUTE8 in VARCHAR2,
137   X_ATTRIBUTE9 in VARCHAR2,
138   X_ATTRIBUTE10 in VARCHAR2,
139   X_ATTRIBUTE11 in VARCHAR2,
140   X_ATTRIBUTE12 in VARCHAR2,
141   X_ATTRIBUTE13 in VARCHAR2,
142   X_ATTRIBUTE14 in VARCHAR2,
143   X_ATTRIBUTE15 in VARCHAR2,
144   X_GROUP_NAME in VARCHAR2,
145   X_GROUP_DESC in VARCHAR2
146 ) is
147   cursor c is select
148       OBJECT_VERSION_NUMBER,
149       ATTRIBUTE_CATEGORY,
150       ATTRIBUTE1,
151       ATTRIBUTE2,
152       ATTRIBUTE3,
153       ATTRIBUTE4,
154       ATTRIBUTE5,
155       ATTRIBUTE6,
156       ATTRIBUTE7,
157       ATTRIBUTE8,
158       ATTRIBUTE9,
159       ATTRIBUTE10,
160       ATTRIBUTE11,
161       ATTRIBUTE12,
162       ATTRIBUTE13,
163       ATTRIBUTE14,
164       ATTRIBUTE15
165     from PRP_GROUPS_B
166     where GROUP_ID = X_GROUP_ID
167     for update of GROUP_ID nowait;
168   recinfo c%rowtype;
169 
170   cursor c1 is select
171       GROUP_NAME,
172       GROUP_DESC,
173       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
174     from PRP_GROUPS_TL
175     where GROUP_ID = X_GROUP_ID
176     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
177     for update of GROUP_ID nowait;
178 begin
179   open c;
180   fetch c into recinfo;
181   if (c%notfound) then
182     close c;
183     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
184     app_exception.raise_exception;
185   end if;
186   close c;
187   if (    (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
188       AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
189            OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
190       AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
191            OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
192       AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
193            OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
194       AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
195            OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
196       AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
197            OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
198       AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
199            OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
200       AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
201            OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
202       AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
203            OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
204       AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
205            OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
206       AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
207            OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
208       AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
209            OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
210       AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
211            OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
212       AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
213            OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
214       AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
215            OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
216       AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
217            OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
218       AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
219            OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
220   ) then
221     null;
222   else
223     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
224     app_exception.raise_exception;
225   end if;
226 
227   for tlinfo in c1 loop
228     if (tlinfo.BASELANG = 'Y') then
229       if (    (tlinfo.GROUP_NAME = X_GROUP_NAME)
230           AND ((tlinfo.GROUP_DESC = X_GROUP_DESC)
231                OR ((tlinfo.GROUP_DESC is null) AND (X_GROUP_DESC is null)))
232       ) then
233         null;
234       else
235         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
236         app_exception.raise_exception;
237       end if;
238     end if;
239   end loop;
240   return;
241 end LOCK_ROW;
242 
243 procedure UPDATE_ROW (
244   X_GROUP_ID in NUMBER,
245   X_OBJECT_VERSION_NUMBER in NUMBER,
246   X_ATTRIBUTE_CATEGORY in VARCHAR2,
247   X_ATTRIBUTE1 in VARCHAR2,
248   X_ATTRIBUTE2 in VARCHAR2,
249   X_ATTRIBUTE3 in VARCHAR2,
250   X_ATTRIBUTE4 in VARCHAR2,
251   X_ATTRIBUTE5 in VARCHAR2,
252   X_ATTRIBUTE6 in VARCHAR2,
253   X_ATTRIBUTE7 in VARCHAR2,
254   X_ATTRIBUTE8 in VARCHAR2,
255   X_ATTRIBUTE9 in VARCHAR2,
256   X_ATTRIBUTE10 in VARCHAR2,
257   X_ATTRIBUTE11 in VARCHAR2,
258   X_ATTRIBUTE12 in VARCHAR2,
259   X_ATTRIBUTE13 in VARCHAR2,
260   X_ATTRIBUTE14 in VARCHAR2,
261   X_ATTRIBUTE15 in VARCHAR2,
262   X_GROUP_NAME in VARCHAR2,
263   X_GROUP_DESC in VARCHAR2,
264   X_LAST_UPDATE_DATE in DATE,
265   X_LAST_UPDATED_BY in NUMBER,
266   X_LAST_UPDATE_LOGIN in NUMBER
267 ) is
268 begin
269   update PRP_GROUPS_B set
270     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
271     ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
272     ATTRIBUTE1 = X_ATTRIBUTE1,
273     ATTRIBUTE2 = X_ATTRIBUTE2,
274     ATTRIBUTE3 = X_ATTRIBUTE3,
275     ATTRIBUTE4 = X_ATTRIBUTE4,
276     ATTRIBUTE5 = X_ATTRIBUTE5,
277     ATTRIBUTE6 = X_ATTRIBUTE6,
278     ATTRIBUTE7 = X_ATTRIBUTE7,
279     ATTRIBUTE8 = X_ATTRIBUTE8,
280     ATTRIBUTE9 = X_ATTRIBUTE9,
281     ATTRIBUTE10 = X_ATTRIBUTE10,
282     ATTRIBUTE11 = X_ATTRIBUTE11,
283     ATTRIBUTE12 = X_ATTRIBUTE12,
284     ATTRIBUTE13 = X_ATTRIBUTE13,
285     ATTRIBUTE14 = X_ATTRIBUTE14,
286     ATTRIBUTE15 = X_ATTRIBUTE15,
287     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
288     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
289     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
290   where GROUP_ID = X_GROUP_ID;
291 
292   if (sql%notfound) then
293     raise no_data_found;
294   end if;
295 
296   update PRP_GROUPS_TL set
297     GROUP_NAME = X_GROUP_NAME,
298     GROUP_DESC = X_GROUP_DESC,
299     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
300     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
301     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
302     SOURCE_LANG = userenv('LANG')
303   where GROUP_ID = X_GROUP_ID
304   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
305 
306   if (sql%notfound) then
307     raise no_data_found;
308   end if;
309 end UPDATE_ROW;
310 
311 procedure DELETE_ROW (
312   X_GROUP_ID in NUMBER
313 ) is
314 begin
315   delete from PRP_GROUPS_TL
316   where GROUP_ID = X_GROUP_ID;
317 
318   if (sql%notfound) then
319     raise no_data_found;
320   end if;
321 
322   delete from PRP_GROUPS_B
323   where GROUP_ID = X_GROUP_ID;
324 
325   if (sql%notfound) then
326     raise no_data_found;
327   end if;
328 end DELETE_ROW;
329 
330 procedure ADD_LANGUAGE
331 is
332 begin
333   delete from PRP_GROUPS_TL T
334   where not exists
335     (select NULL
336     from PRP_GROUPS_B B
337     where B.GROUP_ID = T.GROUP_ID
338     );
339 
340   update PRP_GROUPS_TL T set (
341       GROUP_NAME,
342       GROUP_DESC
343     ) = (select
344       B.GROUP_NAME,
345       B.GROUP_DESC
346     from PRP_GROUPS_TL B
347     where B.GROUP_ID = T.GROUP_ID
348     and B.LANGUAGE = T.SOURCE_LANG)
349   where (
350       T.GROUP_ID,
351       T.LANGUAGE
352   ) in (select
353       SUBT.GROUP_ID,
354       SUBT.LANGUAGE
355     from PRP_GROUPS_TL SUBB, PRP_GROUPS_TL SUBT
356     where SUBB.GROUP_ID = SUBT.GROUP_ID
357     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
358     and (SUBB.GROUP_NAME <> SUBT.GROUP_NAME
359       or SUBB.GROUP_DESC <> SUBT.GROUP_DESC
360       or (SUBB.GROUP_DESC is null and SUBT.GROUP_DESC is not null)
361       or (SUBB.GROUP_DESC is not null and SUBT.GROUP_DESC is null)
362   ));
363 
364   insert into PRP_GROUPS_TL (
365     LAST_UPDATE_DATE,
366     LAST_UPDATE_LOGIN,
367     GROUP_ID,
368     GROUP_NAME,
369     GROUP_DESC,
370     CREATED_BY,
371     CREATION_DATE,
372     LAST_UPDATED_BY,
373     LANGUAGE,
374     SOURCE_LANG
375   ) select
376     B.LAST_UPDATE_DATE,
377     B.LAST_UPDATE_LOGIN,
378     B.GROUP_ID,
379     B.GROUP_NAME,
380     B.GROUP_DESC,
381     B.CREATED_BY,
382     B.CREATION_DATE,
383     B.LAST_UPDATED_BY,
384     L.LANGUAGE_CODE,
385     B.SOURCE_LANG
386   from PRP_GROUPS_TL B, FND_LANGUAGES L
387   where L.INSTALLED_FLAG in ('I', 'B')
388   and B.LANGUAGE = userenv('LANG')
389   and not exists
390     (select NULL
391     from PRP_GROUPS_TL T
392     where T.GROUP_ID = B.GROUP_ID
393     and T.LANGUAGE = L.LANGUAGE_CODE);
394 end ADD_LANGUAGE;
395 
396 --
397 -- Should be called only from lct file
398 --+
399 procedure LOAD_ROW
400   (
401   p_owner                 IN VARCHAR2,
402   p_group_id              IN NUMBER,
403   p_object_version_number IN NUMBER,
404   p_attribute_category    IN VARCHAR2,
405   p_attribute1            IN VARCHAR2,
406   p_attribute2            IN VARCHAR2,
407   p_attribute3            IN VARCHAR2,
408   p_attribute4            IN VARCHAR2,
409   p_attribute5            IN VARCHAR2,
410   p_attribute6            IN VARCHAR2,
411   p_attribute7            IN VARCHAR2,
412   p_attribute8            IN VARCHAR2,
413   p_attribute9            IN VARCHAR2,
414   p_attribute10           IN VARCHAR2,
415   p_attribute11           IN VARCHAR2,
416   p_attribute12           IN VARCHAR2,
417   p_attribute13           IN VARCHAR2,
418   p_attribute14           IN VARCHAR2,
419   p_attribute15           IN VARCHAR2,
420   p_group_name            IN VARCHAR2,
421   p_group_desc            IN VARCHAR2
422   )
423 is
424   l_user_id                        NUMBER := 0;
425   l_login_id                       NUMBER := 0;
426   l_rowid                          VARCHAR2(256);
427 begin
428 
429     l_user_id := fnd_load_util.owner_id(p_owner);
430 
431   BEGIN
432 
433     update_row
434       (
435       X_GROUP_ID               => p_group_id,
436       X_OBJECT_VERSION_NUMBER  => p_object_version_number,
437       X_ATTRIBUTE_CATEGORY     => p_attribute_category,
438       X_ATTRIBUTE1             => p_attribute1,
439       X_ATTRIBUTE2             => p_attribute2,
440       X_ATTRIBUTE3             => p_attribute3,
441       X_ATTRIBUTE4             => p_attribute4,
442       X_ATTRIBUTE5             => p_attribute5,
443       X_ATTRIBUTE6             => p_attribute6,
444       X_ATTRIBUTE7             => p_attribute7,
445       X_ATTRIBUTE8             => p_attribute8,
446       X_ATTRIBUTE9             => p_attribute9,
447       X_ATTRIBUTE10            => p_attribute10,
448       X_ATTRIBUTE11            => p_attribute11,
449       X_ATTRIBUTE12            => p_attribute12,
450       X_ATTRIBUTE13            => p_attribute13,
451       X_ATTRIBUTE14            => p_attribute14,
452       X_ATTRIBUTE15            => p_attribute15,
453       X_GROUP_NAME             => p_group_name,
454       X_GROUP_DESC             => p_group_desc,
455       X_LAST_UPDATE_DATE       => sysdate,
456       X_LAST_UPDATED_BY        => l_user_id,
457       X_LAST_UPDATE_LOGIN      => l_login_id
458       );
459 
460   EXCEPTION
461 
462      WHEN NO_DATA_FOUND THEN
463 
464        insert_row
465        (
466        X_ROWID                 => l_rowid,
467        X_GROUP_ID              => p_group_id,
468        X_OBJECT_VERSION_NUMBER => p_object_version_number,
469        X_ATTRIBUTE_CATEGORY    => p_attribute_category,
470        X_ATTRIBUTE1            => p_attribute1,
471        X_ATTRIBUTE2            => p_attribute2,
472        X_ATTRIBUTE3            => p_attribute3,
473        X_ATTRIBUTE4            => p_attribute4,
474        X_ATTRIBUTE5            => p_attribute5,
475        X_ATTRIBUTE6            => p_attribute6,
476        X_ATTRIBUTE7            => p_attribute7,
477        X_ATTRIBUTE8            => p_attribute8,
478        X_ATTRIBUTE9            => p_attribute9,
479        X_ATTRIBUTE10           => p_attribute10,
480        X_ATTRIBUTE11           => p_attribute11,
481        X_ATTRIBUTE12           => p_attribute12,
482        X_ATTRIBUTE13           => p_attribute13,
483        X_ATTRIBUTE14           => p_attribute14,
484        X_ATTRIBUTE15           => p_attribute15,
485        X_GROUP_NAME            => p_group_name,
486        X_GROUP_DESC            => p_group_desc,
487        X_CREATION_DATE         => sysdate,
488        X_CREATED_BY            => l_user_id,
489        X_LAST_UPDATE_DATE      => sysdate,
490        X_LAST_UPDATED_BY       => l_user_id,
491        X_LAST_UPDATE_LOGIN     => l_login_id
492        );
493 
494   END;
495 
496 end LOAD_ROW;
497 
498 procedure TRANSLATE_ROW
499   (
500    p_owner                              IN VARCHAR2,
501    p_group_id                           IN NUMBER,
502    p_group_name                         IN VARCHAR2,
503    p_group_desc                         IN VARCHAR2
504   )
505 IS
506   l_login_id                       NUMBER := 0;
507 BEGIN
508 
509   UPDATE prp_groups_tl
510     SET group_name = p_group_name,
511     group_desc = p_group_desc,
512     last_update_date = sysdate,
513     last_updated_by = decode(p_owner, 'SEED', 1, 0),
514     last_update_login = l_login_id,
515     source_lang = userenv('LANG')
516     WHERE userenv('LANG') in (LANGUAGE, SOURCE_LANG) AND
517     group_id = p_group_id;
518 
519 end TRANSLATE_ROW;
520 
521 end PRP_GROUPS_PKG;