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

Source


1 package body AMS_DS_TCA_ENTITY_PKG as
2 /* $Header: amsltceb.pls 120.2 2006/06/30 13:18:29 bmuthukr ship $ */
3 procedure INSERT_ROW (
4   X_ROWID in OUT NOCOPY VARCHAR2,
5   X_ENTITY_ID in NUMBER,
6   X_ENTITY_NAME in VARCHAR2,
7   X_TABLE_NAME in VARCHAR2,
8   X_DESCRIPTION in VARCHAR2,
9   X_CREATION_DATE in DATE,
10   X_CREATED_BY in NUMBER,
11   X_LAST_UPDATE_DATE in DATE,
12   X_LAST_UPDATED_BY in NUMBER,
13   X_LAST_UPDATE_LOGIN in NUMBER
14 ) is
15   cursor C is select ROWID from AMS_DS_TCA_ENTITY
16     where ENTITY_ID = X_ENTITY_ID
17     ;
18 begin
19   insert into AMS_DS_TCA_ENTITY (
20     ENTITY_ID,
21     ENTITY_NAME,
22     TABLE_NAME,
23     CREATION_DATE,
24     CREATED_BY,
25     LAST_UPDATE_DATE,
26     LAST_UPDATED_BY,
27     LAST_UPDATE_LOGIN
28   ) values (
29     X_ENTITY_ID,
30     X_ENTITY_NAME,
31     X_TABLE_NAME,
32     X_CREATION_DATE,
33     X_CREATED_BY,
34     X_LAST_UPDATE_DATE,
35     X_LAST_UPDATED_BY,
36     X_LAST_UPDATE_LOGIN
37   );
38 
39   insert into AMS_DS_TCA_ENTITY_TL (
40     LAST_UPDATED_BY,
41     ENTITY_ID,
42     LAST_UPDATE_DATE,
43     CREATION_DATE,
44     CREATED_BY,
45     LAST_UPDATE_LOGIN,
46     DESCRIPTION,
47     LANGUAGE,
48     SOURCE_LANG
49   ) select
50     X_LAST_UPDATED_BY,
51     X_ENTITY_ID,
52     X_LAST_UPDATE_DATE,
53     X_CREATION_DATE,
54     X_CREATED_BY,
55     X_LAST_UPDATE_LOGIN,
56     X_DESCRIPTION,
57     L.LANGUAGE_CODE,
58     userenv('LANG')
59   from FND_LANGUAGES L
60   where L.INSTALLED_FLAG in ('I', 'B')
61   and not exists
62     (select NULL
63     from AMS_DS_TCA_ENTITY_TL T
64     where T.ENTITY_ID = X_ENTITY_ID
65     and T.LANGUAGE = L.LANGUAGE_CODE);
66 
67   open c;
68   fetch c into X_ROWID;
69   if (c%notfound) then
70     close c;
71     raise no_data_found;
72   end if;
73   close c;
74 
75 end INSERT_ROW;
76 
77 procedure LOCK_ROW (
78   X_ENTITY_ID in NUMBER,
79   X_ENTITY_NAME in VARCHAR2,
80   X_TABLE_NAME in VARCHAR2,
81   X_DESCRIPTION in VARCHAR2
82 ) is
83   cursor c is select
84       ENTITY_NAME,
85       TABLE_NAME
86     from AMS_DS_TCA_ENTITY
87     where ENTITY_ID = X_ENTITY_ID
88     for update of ENTITY_ID nowait;
89   recinfo c%rowtype;
90 
91   cursor c1 is select
92       DESCRIPTION,
93       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
94     from AMS_DS_TCA_ENTITY_TL
95     where ENTITY_ID = X_ENTITY_ID
96     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
97     for update of ENTITY_ID nowait;
98 begin
99   open c;
100   fetch c into recinfo;
101   if (c%notfound) then
102     close c;
103     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
104     app_exception.raise_exception;
105   end if;
106   close c;
107   if (    (recinfo.ENTITY_NAME = X_ENTITY_NAME)
108       AND ((recinfo.TABLE_NAME = X_TABLE_NAME)
109            OR ((recinfo.TABLE_NAME is null) AND (X_TABLE_NAME is null)))
110   ) then
111     null;
112   else
113     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
114     app_exception.raise_exception;
115   end if;
116 
117   for tlinfo in c1 loop
118     if (tlinfo.BASELANG = 'Y') then
119       if (    ((tlinfo.DESCRIPTION = X_DESCRIPTION)
120                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
121       ) then
122         null;
123       else
124         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
125         app_exception.raise_exception;
126       end if;
127     end if;
128   end loop;
129   return;
130 end LOCK_ROW;
131 
132 procedure UPDATE_ROW (
133   X_ENTITY_ID in NUMBER,
134   X_ENTITY_NAME in VARCHAR2,
135   X_TABLE_NAME in VARCHAR2,
136   X_DESCRIPTION in VARCHAR2,
137   X_LAST_UPDATE_DATE in DATE,
138   X_LAST_UPDATED_BY in NUMBER,
139   X_LAST_UPDATE_LOGIN in NUMBER
140 ) is
141 begin
142   update AMS_DS_TCA_ENTITY set
143     ENTITY_NAME = X_ENTITY_NAME,
144     TABLE_NAME = X_TABLE_NAME,
145     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
146     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
147     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
148   where ENTITY_ID = X_ENTITY_ID;
149 
150   if (sql%notfound) then
151     raise no_data_found;
152   end if;
153 
154   update AMS_DS_TCA_ENTITY_TL set
155     DESCRIPTION = X_DESCRIPTION,
156     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
157     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
158     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
159     SOURCE_LANG = userenv('LANG')
160   where ENTITY_ID = X_ENTITY_ID
161   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
162 
163   if (sql%notfound) then
164     raise no_data_found;
165   end if;
166 end UPDATE_ROW;
167 
168 procedure DELETE_ROW (
169   X_ENTITY_ID in NUMBER
170 ) is
171 begin
172   delete from AMS_DS_TCA_ENTITY_TL
173   where ENTITY_ID = X_ENTITY_ID;
174 
175   if (sql%notfound) then
176     raise no_data_found;
177   end if;
178 
179   delete from AMS_DS_TCA_ENTITY
180   where ENTITY_ID = X_ENTITY_ID;
181 
182   if (sql%notfound) then
183     raise no_data_found;
184   end if;
185 end DELETE_ROW;
186 
187 
188 procedure LOAD_ROW (
189   X_ENTITY_ID in NUMBER,
190   X_ENTITY_NAME in VARCHAR2,
191   X_TABLE_NAME in VARCHAR2,
192   X_DESCRIPTION in VARCHAR2,
193   X_OWNER in VARCHAR2,
194   x_custom_mode IN VARCHAR2,
195   X_CREATION_DATE in DATE,
196   X_CREATED_BY in NUMBER,
197   X_LAST_UPDATE_DATE in DATE,
198   X_LAST_UPDATED_BY in NUMBER,
199   X_LAST_UPDATE_LOGIN in NUMBER
200   ) IS
201 
202 l_user_id number := 0;
203 l_colmap_id  number;
204 l_obj_verno number;
205 l_dummy_char  varchar2(1);
206 l_row_id    varchar2(100);
207 l_db_luby_id   number;
208 
209  cursor c_chk_col_exists is
210   select 'x'
211   from   AMS_DS_TCA_ENTITY
212   where  ENTITY_ID = X_ENTITY_ID;
213 
214   cursor c_get_col_mapping_id is
215   select AMS_DS_TCA_ENTITY_s.nextval
216   from dual;
217 
218   cursor c_get_luby is
219           select last_updated_by
220           from AMS_DS_TCA_ENTITY
221           where entity_id = X_ENTITY_ID;
222 
223 BEGIN
224         if X_OWNER = 'SEED' then
225                 l_user_id := 1;
226         elsif X_OWNER = 'ORACLE' then
227 	         l_user_id := 2;
228 	elsif X_OWNER = 'SYSADMIN' then
229 	         l_user_id := 0;
230         end if;
231 	   open c_chk_col_exists;
232         fetch c_chk_col_exists into l_dummy_char;
233         if c_chk_col_exists%notfound
234         then
235                 close c_chk_col_exists;
236                 if X_ENTITY_ID is null
237                 then
238                         open c_get_col_mapping_id;
239                         fetch c_get_col_mapping_id into l_colmap_id;
240                         close c_get_col_mapping_id;
241                 else
242                         l_colmap_id := X_ENTITY_ID;
243                 end if;
244                  AMS_DS_TCA_ENTITY_PKG.INSERT_ROW (
245                         X_ROWID            => l_row_id,
246                         X_ENTITY_ID        => X_ENTITY_ID,
247 			X_ENTITY_NAME      => X_ENTITY_NAME,
248                         X_TABLE_NAME       => X_TABLE_NAME,
249                         X_DESCRIPTION      => X_DESCRIPTION,
250                         X_CREATION_DATE    => X_CREATION_DATE, -- sysdate,
251                         X_CREATED_BY       => l_user_id,
252                         X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE, -- sysdate,
253                         X_LAST_UPDATED_BY  => l_user_id,
254                         X_LAST_UPDATE_LOGIN => 1);
255 
256               else
257                        close c_chk_col_exists;
258                        OPEN c_get_luby;
259 		       FETCH c_get_luby INTO  l_db_luby_id;
260 		       CLOSE c_get_luby;
261 
262 
263 		         if (l_db_luby_id IN (0, 1, 2) or NVL(x_custom_mode, 'PRESERVE')='FORCE')
264       			   then
265 
266                        l_colmap_id := X_ENTITY_ID ;
267 
268                         AMS_DS_TCA_ENTITY_PKG.UPDATE_ROW (
269                         X_ENTITY_ID        => X_ENTITY_ID,
270 			X_ENTITY_NAME      => X_ENTITY_NAME,
271                         X_TABLE_NAME       => X_TABLE_NAME,
272                         X_DESCRIPTION      => X_DESCRIPTION,
273                         X_LAST_UPDATE_DATE   => X_LAST_UPDATE_DATE, -- sysdate,
274                         X_LAST_UPDATED_BY    => l_user_id,
275                         X_LAST_UPDATE_LOGIN  => 1
276                         );
277                         end if;
278                end if;
279 
280 end LOAD_ROW;
281 
282 procedure ADD_LANGUAGE
283 is
284 begin
285   delete from AMS_DS_TCA_ENTITY_TL T
286   where not exists
287     (select NULL
288     from AMS_DS_TCA_ENTITY B
289     where B.ENTITY_ID= T.ENTITY_ID
290     );
291 
292   update AMS_DS_TCA_ENTITY_TL T set (
293       DESCRIPTION
294     ) = (select
295       B.DESCRIPTION
296     from AMS_DS_TCA_ENTITY_TL B
297     where B.ENTITY_ID= T.ENTITY_ID
298     and B.LANGUAGE = T.SOURCE_LANG)
299   where (
300       T.ENTITY_ID,
301       T.LANGUAGE
302   ) in (select
303       SUBT.ENTITY_ID,
304       SUBT.LANGUAGE
305     from AMS_DS_TCA_ENTITY_TL SUBB, AMS_DS_TCA_ENTITY_TL SUBT
306     where SUBB.ENTITY_ID= SUBT.ENTITY_ID
307     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
308     and (
309       SUBB.DESCRIPTION <> SUBT.DESCRIPTION
310       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
311       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
312   ));
313 
314   insert into AMS_DS_TCA_ENTITY_TL (
315     LAST_UPDATED_BY,
316     ENTITY_ID,
317     LAST_UPDATE_DATE,
318     CREATION_DATE,
319     CREATED_BY,
320     LAST_UPDATE_LOGIN,
321     DESCRIPTION,
322     LANGUAGE,
323     SOURCE_LANG
324   ) select
325     B.LAST_UPDATED_BY,
326     B.ENTITY_ID,
327     B.LAST_UPDATE_DATE,
328     B.CREATION_DATE,
329     B.CREATED_BY,
330     B.LAST_UPDATE_LOGIN,
331     B.DESCRIPTION,
332     L.LANGUAGE_CODE,
333     B.SOURCE_LANG
334   from FND_LANGUAGES L, AMS_DS_TCA_ENTITY_TL  B
335   where L.INSTALLED_FLAG in ('I', 'B')
336   and B.LANGUAGE = userenv('LANG')
337   and not exists
338     (select NULL
339     from AMS_DS_TCA_ENTITY_TL T
340     where T.ENTITY_ID = B.ENTITY_ID
341     and T.LANGUAGE = L.LANGUAGE_CODE);
342 
343 end ADD_LANGUAGE;
344 
345 PROCEDURE TRANSLATE_ROW (
346   X_ENTITY_ID               IN NUMBER,
347   X_DESCRIPTION             IN VARCHAR2,
348   X_OWNER                   IN VARCHAR2,
349   X_CUSTOM_MODE 	    IN VARCHAR2
350 ) IS
351 
352 CURSOR c_last_updated_by IS
353 SELECT last_updated_by
354   FROM AMS_DS_TCA_ENTITY_TL
355  WHERE ENTITY_ID = X_ENTITY_ID
356    AND USERENV('LANG') = LANGUAGE;
357 
358 l_luby number; --last updated by
359 
360 BEGIN
361     -- only UPDATE rows that have not been altered by user
362    OPEN c_last_updated_by;
363    FETCH c_last_updated_by INTO l_luby;
364    CLOSE c_last_updated_by;
365 
366    IF (l_luby IN (0, 1, 2) or NVL(X_CUSTOM_MODE, 'PRESERVE')='FORCE') THEN
367       UPDATE AMS_DS_TCA_ENTITY_TL
368          SET DESCRIPTION = NVL(X_DESCRIPTION, DESCRIPTION),
369              SOURCE_LANG = userenv('LANG'),
370              LAST_UPDATE_DATE = SYSDATE,
371              LAST_UPDATED_BY = decode(x_owner, 'SEED', 1,  'ORACLE', 2, 'SYSADMIN', 0, -1),
372              LAST_UPDATE_LOGIN = 0
373        WHERE ENTITY_ID = X_ENTITY_ID
374          AND userenv('LANG') IN (language, source_lang);
375     END IF;
376 END TRANSLATE_ROW;
377 
378 end AMS_DS_TCA_ENTITY_PKG;