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

Source


1 package body OKE_TERMS_PKG as
2 /* $Header: OKETERMB.pls 120.1 2005/06/02 12:00:18 appldev  $ */
3 
4 PROCEDURE INSERT_ROW
5 ( X_ROWID             IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6 , X_TERM_CODE         IN     VARCHAR2
7 , X_TERM_TYPE_CODE    IN     VARCHAR2
8 , X_TERM_NAME         IN     VARCHAR2
9 , X_DESCRIPTION       IN     VARCHAR2
10 , X_USER_DEFINED_FLAG IN     VARCHAR2
11 , X_VIEW_APPL_ID      IN     NUMBER
12 , X_LOOKUP_TYPE       IN     VARCHAR2
13 , X_CREATION_DATE     IN     DATE
14 , X_CREATED_BY        IN     NUMBER
15 , X_LAST_UPDATE_DATE  IN     DATE
16 , X_LAST_UPDATED_BY   IN     NUMBER
17 , X_LAST_UPDATE_LOGIN IN     NUMBER
18 ) IS
19 
20   cursor C is
21     select ROWID from OKE_TERMS_B
22     where TERM_CODE = X_TERM_CODE;
23 
24 BEGIN
25 
26   INSERT INTO OKE_TERMS_B
27   ( TERM_CODE
28   , TERM_TYPE_CODE
29   , CREATION_DATE
30   , CREATED_BY
31   , LAST_UPDATE_DATE
32   , LAST_UPDATED_BY
33   , LAST_UPDATE_LOGIN
34   , USER_DEFINED_FLAG
35   , VIEW_APPLICATION_ID
36   , LOOKUP_TYPE
37   ) VALUES
38   ( X_TERM_CODE
39   , X_TERM_TYPE_CODE
40   , X_CREATION_DATE
41   , X_CREATED_BY
42   , X_LAST_UPDATE_DATE
43   , X_LAST_UPDATED_BY
44   , X_LAST_UPDATE_LOGIN
45   , X_USER_DEFINED_FLAG
46   , X_VIEW_APPL_ID
47   , X_LOOKUP_TYPE
48   );
49 
50   INSERT INTO OKE_TERMS_TL
51   ( TERM_CODE
52   , CREATION_DATE
53   , CREATED_BY
54   , LAST_UPDATE_DATE
55   , LAST_UPDATED_BY
56   , LAST_UPDATE_LOGIN
57   , TERM_NAME
58   , DESCRIPTION
59   , LANGUAGE
60   , SOURCE_LANG
61   )
62   SELECT
63     X_TERM_CODE
64   , X_CREATION_DATE
65   , X_CREATED_BY
66   , X_LAST_UPDATE_DATE
67   , X_LAST_UPDATED_BY
68   , X_LAST_UPDATE_LOGIN
69   , X_TERM_NAME
70   , X_DESCRIPTION
71   , L.LANGUAGE_CODE
72   , USERENV('LANG')
73   FROM FND_LANGUAGES L
74   WHERE L.INSTALLED_FLAG IN ('I', 'B')
75   AND NOT EXISTS
76     (SELECT NULL
77      FROM OKE_TERMS_TL T
78      WHERE T.TERM_CODE = X_TERM_CODE
79      AND T.LANGUAGE = L.LANGUAGE_CODE
80   );
81 
82   OPEN c;
83   FETCH c INTO X_ROWID;
84   IF (c%notfound) THEN
85     CLOSE c;
86     RAISE no_data_found;
87   END IF;
88   CLOSE c;
89 
90 END INSERT_ROW;
91 
92 
93 PROCEDURE LOCK_ROW
94 ( X_TERM_CODE      IN VARCHAR2
95 , X_TERM_TYPE_CODE IN VARCHAR2
96 , X_TERM_NAME      IN VARCHAR2
97 , X_DESCRIPTION    IN VARCHAR2
98 , X_USER_DEFINED_FLAG IN VARCHAR2
99 , X_VIEW_APPL_ID      IN NUMBER
100 , X_LOOKUP_TYPE       IN VARCHAR2
101 ) IS
102 
103   CURSOR c IS
104     SELECT TERM_TYPE_CODE
105     ,      USER_DEFINED_FLAG
106     ,      VIEW_APPLICATION_ID
107     ,      LOOKUP_TYPE
108     FROM   OKE_TERMS_B
109     WHERE  TERM_CODE = X_TERM_CODE
110     FOR UPDATE OF TERM_CODE NOWAIT;
111   RecInfo c%rowtype;
112 
113   CURSOR c1 IS
114     SELECT TERM_NAME
115     ,      DESCRIPTION
116     ,      DECODE(LANGUAGE, USERENV('LANG'), 'Y', 'N') BASELANG
117     FROM   OKE_TERMS_TL
118     WHERE  TERM_CODE = X_TERM_CODE
119     AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
120     FOR UPDATE OF TERM_CODE NOWAIT;
121 
122 BEGIN
123 
124   OPEN c;
125   FETCH c INTO RecInfo;
126   IF (c%notfound) THEN
127     CLOSE c;
128     FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
129     APP_EXCEPTION.RAISE_EXCEPTION;
130   END IF;
131   CLOSE c;
132 
133   IF (    ( ( recinfo.TERM_TYPE_CODE = X_TERM_TYPE_CODE )
134            OR ( ( recinfo.TERM_TYPE_CODE is null )
135                AND ( X_TERM_TYPE_CODE is null ) ) )
136       AND ( ( recinfo.VIEW_APPLICATION_ID = X_VIEW_APPL_ID )
137            OR ( ( recinfo.VIEW_APPLICATION_ID is null )
138                AND ( X_VIEW_APPL_ID is null ) ) )
139       AND ( ( recinfo.LOOKUP_TYPE = X_LOOKUP_TYPE )
140            OR ( ( recinfo.LOOKUP_TYPE is null )
141                AND ( X_LOOKUP_TYPE is null ) ) )
142       AND ( ( recinfo.USER_DEFINED_FLAG = X_USER_DEFINED_FLAG )
143            OR ( ( recinfo.USER_DEFINED_FLAG is null )
144                AND ( X_USER_DEFINED_FLAG is null ) ) )
145   )  THEN
146     NULL;
147   ELSE
148     FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
149     APP_EXCEPTION.RAISE_EXCEPTION;
150   END IF;
151 
152   FOR tlinfo IN c1 LOOP
153     IF (tlinfo.BASELANG = 'Y') then
154       IF (    (tlinfo.TERM_NAME = X_TERM_NAME)
155           AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
156                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
157       ) THEN
158         NULL;
159       ELSE
160         FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
161         APP_EXCEPTION.RAISE_EXCEPTION;
162       END IF;
163     END IF;
164   END LOOP;
165   RETURN;
166 
167 END LOCK_ROW;
168 
169 
170 PROCEDURE UPDATE_ROW
171 ( X_TERM_CODE         IN VARCHAR2
172 , X_TERM_TYPE_CODE    IN VARCHAR2
173 , X_TERM_NAME         IN VARCHAR2
174 , X_DESCRIPTION       IN VARCHAR2
175 , X_USER_DEFINED_FLAG IN VARCHAR2
176 , X_VIEW_APPL_ID      IN NUMBER
177 , X_LOOKUP_TYPE       IN VARCHAR2
178 , X_LAST_UPDATE_DATE  IN DATE
179 , X_LAST_UPDATED_BY   IN NUMBER
180 , X_LAST_UPDATE_LOGIN IN NUMBER
181 ) IS
182 
183 BEGIN
184 
185   UPDATE OKE_TERMS_B
186   SET TERM_TYPE_CODE    = X_TERM_TYPE_CODE
187   ,   USER_DEFINED_FLAG = X_USER_DEFINED_FLAG
188   ,   VIEW_APPLICATION_ID = X_VIEW_APPL_ID
189   ,   LOOKUP_TYPE       = X_LOOKUP_TYPE
190   ,   LAST_UPDATE_DATE  = X_LAST_UPDATE_DATE
191   ,   LAST_UPDATED_BY   = X_LAST_UPDATED_BY
192   ,   LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
193   WHERE TERM_CODE = X_TERM_CODE;
194 
195   IF (sql%notfound) THEN
196     RAISE no_data_found;
197   END IF;
198 
199   UPDATE OKE_TERMS_TL
200   SET TERM_NAME         = X_TERM_NAME
201   ,   DESCRIPTION       = X_DESCRIPTION
202   ,   LAST_UPDATE_DATE  = X_LAST_UPDATE_DATE
203   ,   LAST_UPDATED_BY   = X_LAST_UPDATED_BY
204   ,   LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
205   ,   SOURCE_LANG       = userenv('LANG')
206   WHERE TERM_CODE = X_TERM_CODE
207   AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
208 
209   IF (sql%notfound) THEN
210     RAISE no_data_found;
211   END IF;
212 
213 END UPDATE_ROW;
214 
215 
216 PROCEDURE DELETE_ROW
217 ( X_TERM_CODE IN VARCHAR2
218 ) IS
219 
220 BEGIN
221   DELETE FROM OKE_TERMS_TL
222   WHERE TERM_CODE = X_TERM_CODE;
223 
224   IF (sql%notfound) THEN
225     RAISE no_data_found;
226   END IF;
227 
228   DELETE FROM OKE_TERMS_B
229   WHERE TERM_CODE = X_TERM_CODE;
230 
231   IF (sql%notfound) THEN
232     RAISE no_data_found;
233   END IF;
234 
235 END DELETE_ROW;
236 
237 
238 PROCEDURE ADD_LANGUAGE
239 IS
240 BEGIN
241 
242   DELETE FROM OKE_TERMS_TL T
243   WHERE NOT EXISTS (
244     SELECT NULL
245     FROM OKE_TERMS_B B
246     WHERE B.TERM_CODE = T.TERM_CODE
247   );
248 
249   UPDATE OKE_TERMS_TL T
250   SET ( TERM_NAME , DESCRIPTION ) = (
251     SELECT B.TERM_NAME
252     ,      B.DESCRIPTION
253     FROM   OKE_TERMS_TL B
254     WHERE  B.TERM_CODE = T.TERM_CODE
255     AND    B.LANGUAGE = T.SOURCE_LANG)
256   WHERE ( T.TERM_CODE , T.LANGUAGE ) IN (
257     SELECT SUBT.TERM_CODE
258     ,      SUBT.LANGUAGE
259     FROM   OKE_TERMS_TL SUBB
260     ,      OKE_TERMS_TL SUBT
261     WHERE  SUBB.TERM_CODE = SUBT.TERM_CODE
262     AND    SUBB.LANGUAGE = SUBT.SOURCE_LANG
263     AND    (  SUBB.TERM_NAME <> SUBT.TERM_NAME
264            OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
265            OR (   SUBB.DESCRIPTION IS NULL
266               AND SUBT.DESCRIPTION IS NOT NULL
267               )
268            OR (   SUBB.DESCRIPTION IS NOT NULL
269               AND SUBT.DESCRIPTION IS NULL)
270            )
271     );
272 
273   INSERT INTO OKE_TERMS_TL
274   ( TERM_CODE
275   , CREATION_DATE
276   , CREATED_BY
277   , LAST_UPDATE_DATE
278   , LAST_UPDATED_BY
279   , LAST_UPDATE_LOGIN
280   , TERM_NAME
281   , DESCRIPTION
282   , LANGUAGE
283   , SOURCE_LANG
284   )
285   SELECT
286     B.TERM_CODE
287   , B.CREATION_DATE
288   , B.CREATED_BY
289   , B.LAST_UPDATE_DATE
290   , B.LAST_UPDATED_BY
291   , B.LAST_UPDATE_LOGIN
292   , B.TERM_NAME
293   , B.DESCRIPTION
294   , L.LANGUAGE_CODE
295   , B.SOURCE_LANG
296   FROM OKE_TERMS_TL B
297   ,    FND_LANGUAGES L
298   WHERE L.INSTALLED_FLAG IN ('I', 'B')
299   AND B.LANGUAGE = USERENV('LANG')
300   AND NOT EXISTS (
301     SELECT NULL
302     FROM   OKE_TERMS_TL T
303     WHERE  T.TERM_CODE = B.TERM_CODE
304     AND    T.LANGUAGE = L.LANGUAGE_CODE
305   );
306 
307 END ADD_LANGUAGE;
308 
309 PROCEDURE LOAD_ROW
310 ( X_TERM_CODE         IN     VARCHAR2
311 , X_OWNER             IN     VARCHAR2
312 , X_LAST_UPDATE_DATE  IN     DATE
313 , X_CUSTOM_MODE       IN     VARCHAR2
314 , X_TERM_TYPE_CODE    IN     VARCHAR2
315 , X_TERM_NAME         IN     VARCHAR2
316 , X_DESCRIPTION       IN     VARCHAR2
317 , X_USER_DEFINED_FLAG IN     VARCHAR2
318 , X_VIEW_APPL_ID      IN     NUMBER
319 , X_LOOKUP_TYPE       IN     VARCHAR2
320 ) IS
321 
322   Row_ID VARCHAR2(30);
323 
324   f_luby    NUMBER;  -- entity owner in file
325 
326 BEGIN
327   --
328   -- Translate owner and last_update_date
329   --
330   f_luby := FND_LOAD_UTIL.Owner_ID(X_OWNER);
331 
332   --
333   -- Load the record
334   --
335   UPDATE OKE_TERMS_B
336   SET    TERM_TYPE_CODE      = NVL(X_TERM_TYPE_CODE , TERM_TYPE_CODE)
337   ,      USER_DEFINED_FLAG   = X_USER_DEFINED_FLAG
338   ,      VIEW_APPLICATION_ID = X_VIEW_APPL_ID
339   ,      LOOKUP_TYPE         = X_LOOKUP_TYPE
340   ,      LAST_UPDATE_DATE    = SYSDATE
341   ,      LAST_UPDATED_BY     = f_luby
342   WHERE  TERM_CODE = X_TERM_CODE;
343 
344   IF ( sql%notfound ) THEN
345 
346     INSERT_ROW
347     ( Row_ID
348     , X_TERM_CODE
349     , X_TERM_TYPE_CODE
350     , X_TERM_NAME
351     , X_DESCRIPTION
352     , X_USER_DEFINED_FLAG
353     , X_VIEW_APPL_ID
354     , X_LOOKUP_TYPE
355     , X_LAST_UPDATE_DATE
356     , f_luby
357     , X_LAST_UPDATE_DATE
358     , f_luby
359     , 0
360     );
361 
362   END IF;
363 
364   UPDATE OKE_TERMS_TL
365   SET    TERM_NAME        = X_TERM_NAME
366   ,      DESCRIPTION      = X_DESCRIPTION
367   ,      LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
368   ,      LAST_UPDATED_BY  = f_luby
369   ,      SOURCE_LANG      = USERENV('LANG')
370   WHERE  TERM_CODE = X_TERM_CODE
371   AND    USERENV('LANG') IN ( LANGUAGE , SOURCE_LANG );
372 
373   IF ( sql%notfound ) THEN
374 
375     INSERT INTO OKE_TERMS_TL
376     ( TERM_CODE
377     , CREATION_DATE
378     , CREATED_BY
379     , LAST_UPDATE_DATE
380     , LAST_UPDATED_BY
381     , LAST_UPDATE_LOGIN
382     , TERM_NAME
383     , DESCRIPTION
384     , LANGUAGE
385     , SOURCE_LANG
386     )
387     SELECT
388       X_TERM_CODE
389     , X_LAST_UPDATE_DATE
390     , f_luby
391     , X_LAST_UPDATE_DATE
392     , f_luby
393     , 0
394     , X_TERM_NAME
395     , X_DESCRIPTION
396     , L.LANGUAGE_CODE
397     , USERENV('LANG')
398     FROM FND_LANGUAGES L
399     WHERE L.INSTALLED_FLAG IN ('I', 'B')
400     AND NOT EXISTS
401       (SELECT NULL
405     );
402        FROM OKE_TERMS_TL T
403        WHERE T.TERM_CODE = X_TERM_CODE
404        AND T.LANGUAGE = L.LANGUAGE_CODE
406 
407   END IF;
408 
409 END LOAD_ROW;
410 
411 
412 PROCEDURE TRANSLATE_ROW
413 ( X_TERM_CODE         IN     VARCHAR2
414 , X_OWNER             IN     VARCHAR2
415 , X_LAST_UPDATE_DATE  IN     DATE
416 , X_TERM_NAME         IN     VARCHAR2
417 , X_DESCRIPTION       IN     VARCHAR2
418 ) IS
419 
420   f_luby   NUMBER;
421 
422 BEGIN
423   --
424   -- Translate owner and last_update_date
425   --
426   f_luby := FND_LOAD_UTIL.Owner_ID(X_OWNER);
427 
428   UPDATE OKE_TERMS_TL
429   SET    TERM_NAME        = X_TERM_NAME
430   ,      DESCRIPTION      = X_DESCRIPTION
431   ,      LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
432   ,      LAST_UPDATED_BY  = f_luby
433   ,      SOURCE_LANG      = USERENV('LANG')
434   WHERE  TERM_CODE = X_TERM_CODE
435   AND    USERENV('LANG') IN ( LANGUAGE , SOURCE_LANG );
436 
437 END TRANSLATE_ROW;
438 
439 END OKE_TERMS_PKG;