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

Source


1 package body JTF_NAV_TREE_ROOTS_PKG as
2 /* $Header: jtfntrb.pls 120.2 2005/12/12 15:20:53 stopiwal ship $ */
3 
4 procedure INSERT_ROW
5 (X_ROOT_VALUE in VARCHAR2,
6  X_VIEWBY_id in number,
7  X_SEQUENCE_NUMBER in NUMBER,
8  X_ROOT_LABEL 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) is
14 
15    l_tree_root_id number := 0;
16 
17 BEGIN
18    SELECT jtf_nav_tree_roots_s.nextval
19      INTO l_tree_root_id
20      FROM dual;
21    insert into JTF_NAV_TREE_ROOTS_B (
22      tree_root_id,
23      ROOT_VALUE,
24      VIEWBY_id,
25      SEQUENCE_NUMBER,
26      CREATION_DATE,
27      CREATED_BY,
28      LAST_UPDATE_DATE,
29      LAST_UPDATED_BY,
30      LAST_UPDATE_LOGIN) values (
31      l_tree_root_id,
32      X_ROOT_VALUE,
33      X_VIEWBY_id,
34      X_SEQUENCE_NUMBER,
35      X_CREATION_DATE,
36      X_CREATED_BY,
37      X_LAST_UPDATE_DATE,
38      X_LAST_UPDATED_BY,
39      X_LAST_UPDATE_LOGIN);
40 
41    insert into JTF_NAV_TREE_ROOTS_TL (
42     tree_root_id,
43     ROOT_LABEL,
44     CREATED_BY,
45     CREATION_DATE,
46     LAST_UPDATE_LOGIN,
47     LAST_UPDATE_DATE,
48     LAST_UPDATED_BY,
49     LANGUAGE,
50     SOURCE_LANG
51   ) select
52     l_tree_root_id,
53       X_ROOT_LABEL,
54       X_CREATED_BY,
55       X_CREATION_DATE,
56       X_LAST_UPDATE_LOGIN,
57       X_LAST_UPDATE_DATE,
58       X_LAST_UPDATED_BY,
59       L.LANGUAGE_CODE,
60       userenv('LANG')
61       from FND_LANGUAGES L
62       where L.INSTALLED_FLAG in ('I', 'B')
63       and not exists
64       (select NULL
65       from JTF_NAV_TREE_ROOTS_TL T
66       where T.tree_root_id = l_tree_root_id
67       and T.LANGUAGE = L.LANGUAGE_CODE);
68 
69 end INSERT_ROW;
70 
71 procedure UPDATE_ROW
72   (x_tree_root_id IN number,
73    X_ROOT_VALUE in VARCHAR2,
74    X_VIEWBY_id in number,
75    X_SEQUENCE_NUMBER in NUMBER,
76    X_ROOT_LABEL in VARCHAR2,
77    X_LAST_UPDATE_DATE in DATE,
78    X_LAST_UPDATED_BY in NUMBER,
79    X_LAST_UPDATE_LOGIN in NUMBER) is
80 begin
81    update JTF_NAV_TREE_ROOTS_B SET
82      root_value = x_root_value,
83      VIEWBY_id = X_VIEWBY_id,
84      SEQUENCE_NUMBER = X_SEQUENCE_NUMBER,
85      LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
86      LAST_UPDATED_BY = X_LAST_UPDATED_BY,
87     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
88   where tree_root_id = x_tree_root_id;
89 
90   if (sql%notfound) then
91     raise no_data_found;
92   end if;
93 
94   update JTF_NAV_TREE_ROOTS_TL set
95     ROOT_LABEL = X_ROOT_LABEL,
96     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
97     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
98     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
99     SOURCE_LANG = userenv('LANG')
100   where tree_root_id = x_tree_root_id
101   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
102 
103   if (sql%notfound) then
104      raise no_data_found;
105   end if;
106 end UPDATE_ROW;
107 
108 procedure LOCK_ROW
109   (x_tree_root_id in number,
110    X_VIEWBY_id in number,
111    x_root_value IN varchar2,
112    X_SEQUENCE_NUMBER in NUMBER,
113    X_ROOT_LABEL in VARCHAR2) is
114 
115    cursor c IS
116      SELECT root_value, VIEWBY_id, SEQUENCE_NUMBER
117     from JTF_NAV_TREE_ROOTS_B
118     where tree_root_id = x_tree_root_id
119     for update of tree_root_id nowait;
120   recinfo c%rowtype;
121 
122   cursor c1 is select
123       ROOT_LABEL,
124       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
125     from JTF_NAV_TREE_ROOTS_TL
126     where tree_root_id = x_tree_root_id
127     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
128     for update of tree_root_id nowait;
129 begin
130   open c;
131   fetch c into recinfo;
132   if (c%notfound) then
133     close c;
134     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
135     app_exception.raise_exception;
136   end if;
137   close c;
138   if (    ((recinfo.VIEWBY_id = X_VIEWBY_id)
139            OR ((recinfo.VIEWBY_id is null) AND (X_VIEWBY_id is null)))
140     AND (recinfo.SEQUENCE_NUMBER = X_SEQUENCE_NUMBER)
141     AND (recinfo.root_value = x_root_value)
142   ) then
143     null;
144   else
145     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
146     app_exception.raise_exception;
147   end if;
148 
149   for tlinfo in c1 loop
150     if (tlinfo.BASELANG = 'Y') then
151       if (    (tlinfo.ROOT_LABEL = X_ROOT_LABEL)
152       ) then
153         null;
154       else
155         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
156         app_exception.raise_exception;
157       end if;
158     end if;
159   end loop;
160   return;
161 end LOCK_ROW;
162 
163 procedure DELETE_ROW
164   (X_tree_ROOT_id in number) is
165 
166 begin
167   delete from JTF_NAV_TREE_ROOTS_TL
168     where tree_root_id = X_tree_ROOT_id;
169 
170   if (sql%notfound) then
171     raise no_data_found;
172   end if;
173 
174   delete from JTF_NAV_TREE_ROOTS_B
175   where tree_root_id = X_tree_ROOT_id;
176 
177   if (sql%notfound) then
178     raise no_data_found;
179   end if;
180 end DELETE_ROW;
181 
182 procedure ADD_LANGUAGE
183 is
184 begin
185   delete from JTF_NAV_TREE_ROOTS_TL T
186   where not exists
187     (select NULL
188     from JTF_NAV_TREE_ROOTS_B B
189     where B.tree_root_id = T.tree_root_id
190     );
191 
192   update JTF_NAV_TREE_ROOTS_TL T set (
193       ROOT_LABEL
194     ) = (select
195       B.ROOT_LABEL
196     from JTF_NAV_TREE_ROOTS_TL B
197     where B.tree_root_id = T.tree_root_id
198     and B.LANGUAGE = T.SOURCE_LANG)
199   where (
200       T.tree_root_id,
201       T.LANGUAGE
202   ) in (select
203       SUBT.tree_root_id,
204       SUBT.LANGUAGE
205     from JTF_NAV_TREE_ROOTS_TL SUBB, JTF_NAV_TREE_ROOTS_TL SUBT
206     where SUBB.tree_root_id = SUBT.tree_root_id
207     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
208     and (SUBB.ROOT_LABEL <> SUBT.ROOT_LABEL
209   ));
210 
211   insert into JTF_NAV_TREE_ROOTS_TL (
212     tree_root_id,
213     ROOT_LABEL,
214     CREATED_BY,
215     CREATION_DATE,
216     LAST_UPDATE_LOGIN,
217     LAST_UPDATE_DATE,
218     LAST_UPDATED_BY,
219     LANGUAGE,
220     SOURCE_LANG
221   ) select
222     B.tree_root_id,
223     B.ROOT_LABEL,
224     B.CREATED_BY,
225     B.CREATION_DATE,
226     B.LAST_UPDATE_LOGIN,
227     B.LAST_UPDATE_DATE,
228     B.LAST_UPDATED_BY,
229     L.LANGUAGE_CODE,
230     B.SOURCE_LANG
231   from JTF_NAV_TREE_ROOTS_TL B, FND_LANGUAGES L
232   where L.INSTALLED_FLAG in ('I', 'B')
233   and B.LANGUAGE = userenv('LANG')
234   and not exists
235     (select NULL
236     from JTF_NAV_TREE_ROOTS_TL T
237     where T.tree_root_id = B.tree_root_id
238     and T.LANGUAGE = L.LANGUAGE_CODE);
239 end ADD_LANGUAGE;
240 
241 -- --------------------------------------------------------------------
242 -- Procedure : LOAD_ROW
243 -- Description : Called by FNDLOAD to upload seed datas, this procedure
244 --    only handle seed data.
245 -- --------------------------------------------------------------------
246 PROCEDURE LOAD_row
247   (X_ROOT_VALUE in VARCHAR2,
248    X_VIEWBY_VALUE in VARCHAR2,
249    X_SEQUENCE_NUMBER in NUMBER,
250    X_ROOT_LABEL in VARCHAR2,
251    X_OWNER in VARCHAR2) IS
252    user_id NUMBER;
253    l_viewby_id number;
254    l_tree_root_id number;
255 BEGIN
256    -- Validate input data
257    IF (x_root_value IS NULL) OR (x_root_label IS NULL)
258      OR (x_viewby_value IS NULL) THEN
259       GOTO end_load_row;
260    END IF;
261 
262    /*
263    IF (x_owner IS NOT NULL) AND (x_owner = 'SEED') THEN
264       user_id := 1;
265     ELSE
266       user_id := 0;
267    END IF;
268    */
269    user_id := fnd_load_util.owner_id(x_owner);
270 
271    --select the viewby id
272    SELECT viewby_id
273      INTO l_viewby_id
274      FROM jtf_nav_viewbys_b
275      WHERE viewby_value = x_viewby_value;
276 
277    -- Load The record to _B table
278    UPDATE  jtf_nav_tree_roots_b SET
279      viewby_id = l_viewby_id,
280      sequence_number = x_sequence_number,
281      last_update_date = sysdate,
282      last_updated_by = user_id,
283      last_update_login = 0
284      WHERE root_value = x_root_value;
285 
286    IF (SQL%NOTFOUND) THEN
287       -- Insert new record to _B table
288       SELECT jtf_nav_tree_roots_s.nextval
289         INTO l_tree_root_id
290         FROM dual;
291 
292       INSERT INTO jtf_nav_tree_roots_b
293         (tree_root_id,
294         root_value,
295         viewby_id,
296         sequence_number,
297         creation_date,
298         created_by,
299         last_update_date,
300         last_updated_by,
301         last_update_login
302         ) VALUES
303         (l_tree_root_id,
304         x_root_value,
305         l_viewby_id,
306         x_sequence_number,
307         sysdate,
308         user_id,
309         sysdate,
310         user_id,
311         0
312         );
313    END IF;
314 
315    --select the tree root id
316    SELECT tree_root_id
317      INTO l_tree_root_id
318      FROM jtf_nav_tree_roots_b
319      WHERE root_value = x_root_value;
320 
321    -- Load The record to _TL table
322    UPDATE jtf_nav_tree_roots_tl SET
323      root_label = x_root_label,
324      last_update_date = sysdate,
325      last_updated_by = user_id,
326      last_update_login = 0,
327      source_lang = userenv('LANG')
328      WHERE tree_root_id = l_tree_root_id
329      AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
330 
331    IF (SQL%NOTFOUND) THEN
332       -- Insert new record to _TL table
333       INSERT INTO jtf_nav_tree_roots_tl
334         (tree_root_id,
335          root_label,
336          creation_date,
337          created_by,
338          last_update_date,
339          last_updated_by,
340          last_update_login,
341          language,
342          source_lang)
343         SELECT
344         l_tree_root_id,
345         x_root_label,
346         sysdate,
347         user_id,
348         sysdate,
349         user_id,
350         0,
351         l.language_code,
352         userenv('LANG')
353         FROM fnd_languages l
354         WHERE l.installed_flag IN ('I', 'B')
355         AND NOT EXISTS
356         (SELECT NULL
357          FROM jtf_nav_tree_roots_tl t
358           WHERE t.tree_root_id = l_tree_root_id
359           AND t.language = l.language_code);
360    END IF;
361    << end_load_row >>
362      NULL;
363 END LOAD_ROW ;
364 
365 -- --------------------------------------------------------------------
366 -- Procedure : TRANSLATE_ROW
367 -- Description : Called by FNDLOAD to translate seed datas, this procedure
368 --    only handle seed datas.
369 -- --------------------------------------------------------------------
370 PROCEDURE TRANSLATE_ROW
371   ( x_root_value IN VARCHAR2,
372     x_root_label IN VARCHAR2,
373     x_owner IN VARCHAR2) IS
374    user_id NUMBER;
375    l_tree_root_id number;
376 
377 BEGIN
378     -- Validate input data
379    IF (x_root_value IS NULL) OR (x_root_label IS NULL) THEN
380       GOTO end_translate_row;
381    END IF;
382 
383    /*
384    IF (x_owner IS NOT NULL) AND (x_owner = 'SEED') THEN
385       user_id := 1;
386     ELSE
387       user_id := 0;
388    END IF;
389    */
390    user_id := fnd_load_util.owner_id(x_owner);
391 
392    --select the tree root id
393    SELECT tree_root_id
394      INTO l_tree_root_id
395      FROM jtf_nav_tree_roots_b
396      WHERE root_value = x_root_value;
397 
398    -- Update the translation
399    UPDATE jtf_nav_tree_roots_tl SET
400      root_label = x_root_label,
401      last_update_date = sysdate,
402      last_updated_by = user_id,
403      last_update_login = 0,
404      source_lang = userenv('LANG')
405      WHERE tree_root_id = l_tree_root_id
406      AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
407 
408    << end_translate_row >>
409      NULL;
410 END TRANSLATE_ROW ;
411 
412 END JTF_NAV_TREE_ROOTS_PKG;