[Home] [Help]
PACKAGE BODY: APPS.CZ_LOOKUP_VALUES_PKG
Source
1 package body CZ_LOOKUP_VALUES_PKG as
2 /* $Header: czilkvlb.pls 120.0 2005/05/25 05:51:13 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_LIST_NAME in VARCHAR2,
6 X_DATA_VALUE in VARCHAR2,
7 X_NULL_VALUE_FLAG in VARCHAR2,
8 X_DELETED_FLAG in VARCHAR2,
9 X_NUMERIC_ID_VALUE in NUMBER,
10 X_SEEDED_FLAG in VARCHAR2,
11 X_VALUE_LABEL in VARCHAR2,
12 X_VALUE_DESCRIPTION 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 cursor C is select ROWID from CZ_LOOKUP_VALUES_TL
20 where LIST_NAME = X_LIST_NAME
21 and DATA_VALUE = X_DATA_VALUE
22 and language = userenv('LANG');
23 begin
24 insert into CZ_LOOKUP_VALUES_TL (
25 LIST_NAME,
26 DATA_VALUE,
27 NULL_VALUE_FLAG,
28 VALUE_LABEL,
29 VALUE_DESCRIPTION,
30 LAST_UPDATE_LOGIN,
31 CREATION_DATE,
32 LAST_UPDATE_DATE,
33 CREATED_BY,
34 LAST_UPDATED_BY,
35 DELETED_FLAG,
36 NUMERIC_ID_VALUE,
37 SEEDED_FLAG,
38 SOURCE_LANG,
39 LANGUAGE
40 ) select
41 X_LIST_NAME,
42 X_DATA_VALUE,
43 X_NULL_VALUE_FLAG,
44 X_VALUE_LABEL,
45 X_VALUE_DESCRIPTION,
46 X_LAST_UPDATE_LOGIN,
47 X_CREATION_DATE,
48 X_LAST_UPDATE_DATE,
49 X_CREATED_BY,
50 X_LAST_UPDATED_BY,
51 X_DELETED_FLAG,
52 X_NUMERIC_ID_VALUE,
53 X_SEEDED_FLAG,
54 userenv('LANG'),
55 L.LANGUAGE_CODE
56 from FND_LANGUAGES L
57 where L.INSTALLED_FLAG in ('I', 'B')
58 and not exists
59 (select NULL
60 from CZ_LOOKUP_VALUES_TL T
61 where T.LIST_NAME = X_LIST_NAME
62 and T.DATA_VALUE = X_DATA_VALUE);
63
64 open c;
65 fetch c into X_ROWID;
66 if (c%notfound) then
67 close c;
68 raise no_data_found;
69 end if;
70 close c;
71
72 end INSERT_ROW;
73
74 procedure LOCK_ROW (
75 X_LIST_NAME in VARCHAR2,
76 X_DATA_VALUE in VARCHAR2,
77 X_NULL_VALUE_FLAG in VARCHAR2,
78 X_DELETED_FLAG in VARCHAR2,
79 X_NUMERIC_ID_VALUE in NUMBER,
80 X_SEEDED_FLAG in VARCHAR2,
81 X_VALUE_LABEL in VARCHAR2,
82 X_VALUE_DESCRIPTION in VARCHAR2
83 ) IS
84 begin
85 NULL;
86
87 end LOCK_ROW;
88
89 procedure UPDATE_ROW (
90 X_LIST_NAME in VARCHAR2,
91 X_DATA_VALUE in VARCHAR2,
92 X_NULL_VALUE_FLAG in VARCHAR2,
93 X_DELETED_FLAG in VARCHAR2,
94 X_NUMERIC_ID_VALUE in NUMBER,
95 X_SEEDED_FLAG in VARCHAR2,
96 X_VALUE_LABEL in VARCHAR2,
97 X_VALUE_DESCRIPTION in VARCHAR2,
98 X_LAST_UPDATE_DATE in DATE,
99 X_LAST_UPDATED_BY in NUMBER,
100 X_LAST_UPDATE_LOGIN in NUMBER
101 ) is
102 begin
103 update CZ_LOOKUP_VALUES_TL set
104 LIST_NAME = X_LIST_NAME,
105 DATA_VALUE = X_DATA_VALUE,
106 NULL_VALUE_FLAG = X_NULL_VALUE_FLAG,
107 DELETED_FLAG = X_DELETED_FLAG,
108 NUMERIC_ID_VALUE = X_NUMERIC_ID_VALUE,
109 SEEDED_FLAG = X_SEEDED_FLAG,
110 VALUE_LABEL = X_VALUE_LABEL,
111 VALUE_DESCRIPTION = X_VALUE_DESCRIPTION,
112 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
113 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
114 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
115 where LIST_NAME = X_LIST_NAME
116 and DATA_VALUE = X_DATA_VALUE;
117
118 if (sql%notfound) then
119 raise no_data_found;
120 end if;
121 end UPDATE_ROW;
122
123 procedure DELETE_ROW (
124 X_LIST_NAME in VARCHAR2,
125 X_DATA_VALUE in VARCHAR2
126 ) is
127 begin
128 delete from CZ_LOOKUP_VALUES_TL
129 where LIST_NAME = X_LIST_NAME
130 and DATA_VALUE = X_DATA_VALUE
131 and language = userenv('LANG');
132
133 if (sql%notfound) then
134 raise no_data_found;
135 end if;
136
137 end DELETE_ROW;
138
139 procedure ADD_LANGUAGE
140 is
141 begin
142
143 insert into CZ_LOOKUP_VALUES_TL (
144 LIST_NAME,
145 DATA_VALUE,
146 NULL_VALUE_FLAG,
147 VALUE_LABEL,
148 VALUE_DESCRIPTION,
149 LANGUAGE,
150 LAST_UPDATE_LOGIN,
151 CREATION_DATE,
152 LAST_UPDATE_DATE,
153 CREATED_BY,
154 LAST_UPDATED_BY,
155 DELETED_FLAG,
156 NUMERIC_ID_VALUE,
157 SEEDED_FLAG,
158 SOURCE_LANG
159 ) select /*+ ORDERED */
160 B.LIST_NAME,
161 B.DATA_VALUE,
162 B.NULL_VALUE_FLAG,
163 B.VALUE_LABEL,
164 B.VALUE_DESCRIPTION,
165 L.LANGUAGE_CODE,
166 B.LAST_UPDATE_LOGIN,
167 B.CREATION_DATE,
168 B.LAST_UPDATE_DATE,
169 B.CREATED_BY,
170 B.LAST_UPDATED_BY,
171 B.DELETED_FLAG,
172 B.NUMERIC_ID_VALUE,
173 B.SEEDED_FLAG,
174 B.SOURCE_LANG
175 from CZ_LOOKUP_VALUES_TL B, FND_LANGUAGES L
176 where L.INSTALLED_FLAG in ('I', 'B')
177 and B.LANGUAGE = userenv('LANG')
178 and not exists
179 (select NULL
180 from CZ_LOOKUP_VALUES_TL T
181 where T.LIST_NAME = B.LIST_NAME
182 and T.DATA_VALUE = B.DATA_VALUE
183 and T.LANGUAGE = L.LANGUAGE_CODE);
184 end ADD_LANGUAGE;
185
186 -- ----------------------------------------------------------------------
187 -- PROCEDURE: Translate_Row PUBLIC
188 --
189 -- PARAMETERS:
190 -- x_<developer key>
191 -- x_<translated columns>
192 -- x_owner user owning the row (SEED or other)
193 --
194 -- COMMENT:
195 -- Called from the FNDLOAD config file in 'NLS' mode to upload
196 -- translations.
197 -- ----------------------------------------------------------------------
198
199 PROCEDURE Translate_Row
200 (X_LIST_NAME IN VARCHAR2,
201 X_DATA_VALUE IN VARCHAR2,
202 x_value_label IN VARCHAR2,
203 x_value_description IN VARCHAR2,
204 X_OWNER IN VARCHAR2) IS
205
206 f_luby number; -- entity owner in file
207
208 BEGIN
209
210 -- Translate owner to file_last_updated_by
211 f_luby := fnd_load_util.owner_id(X_OWNER);
212
213 UPDATE CZ_LOOKUP_VALUES_TL
214 SET value_label = NVL(x_value_label, value_label)
215 , value_description = NVL(x_value_description, value_description)
216 , last_update_date = SYSDATE
217 , last_updated_by = f_luby
218 , last_update_login = 0
219 , source_lang = userenv('LANG')
220 WHERE LIST_NAME = X_LIST_NAME and DATA_VALUE = X_DATA_VALUE
221 AND userenv('LANG') IN (language, source_lang);
222 commit;
223
224 END Translate_Row;
225
226 procedure LOAD_ROW
227 (X_LIST_NAME IN VARCHAR2,
228 X_DATA_VALUE IN VARCHAR2,
229 x_value_label IN VARCHAR2,
230 x_value_description IN VARCHAR2,
231 X_NULL_VALUE_FLAG IN VARCHAR2,
232 X_DELETED_FLAG IN VARCHAR2,
233 X_NUMERIC_ID_VALUE IN VARCHAR2,
234 X_CREATION_DATE IN VARCHAR2,
235 X_OWNER IN VARCHAR2,
236 X_LAST_UPDATE_DATE in VARCHAR2,
237 X_SEEDED_FLAG IN VARCHAR2)
238 IS
239 s_lname cz_lookup_values.list_name%type; -- entity list_name
240 s_dvalue cz_lookup_values.data_value%type; -- entity data_value
241 f_luby number; -- entity owner in file
242 f_ludate date; -- entity update date in file
243 db_luby number; -- entity owner in db
244 db_ludate date; -- entity update date in db
245 row_id varchar2(64);
246
247
248 cursor c_lvals is
249 select list_name, data_value
250 from cz_lookup_values_tl
251 where list_name = x_list_name
252 and data_value = x_data_value
253 and language = userenv('LANG');
254
255 begin
256
257 -- Translate owner to file_last_updated_by
258 f_luby := fnd_load_util.owner_id(x_owner);
259
260 -- Translate char last_update_date to date
261 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
262
263 open c_lvals;
264 fetch c_lvals into s_lname, s_dvalue;
265
266 if (c_lvals%notfound) then
267 -- No matching rows
268 CZ_LOOKUP_VALUES_PKG.INSERT_ROW(
269 X_ROWID => row_id,
270 X_LIST_NAME => X_LIST_NAME,
271 X_DATA_VALUE => X_DATA_VALUE,
272 X_NULL_VALUE_FLAG => X_NULL_VALUE_FLAG,
273 X_DELETED_FLAG => X_DELETED_FLAG,
274 X_NUMERIC_ID_VALUE => X_NUMERIC_ID_VALUE,
275 X_SEEDED_FLAG => X_SEEDED_FLAG,
276 X_VALUE_LABEL => X_VALUE_LABEL,
277 X_VALUE_DESCRIPTION => X_VALUE_DESCRIPTION,
278 X_CREATION_DATE => nvl(to_date(X_CREATION_DATE, 'RRRR-MM-DD'), sysdate),
279 X_CREATED_BY => UID,
280 X_LAST_UPDATE_DATE => nvl(to_date(X_LAST_UPDATE_DATE, 'RRRR-MM-DD'), sysdate),
281 X_LAST_UPDATED_BY => f_luby,
282 X_LAST_UPDATE_LOGIN => UID
283 );
284 else
285 loop
286 select LAST_UPDATED_BY, LAST_UPDATE_DATE
287 into db_luby, db_ludate
288 from cz_lookup_values_tl
289 where list_name = x_list_name
290 and data_value = x_data_value
291 and language = userenv('LANG');
292 -- Update row in all matching lookups
293 CZ_LOOKUP_VALUES_PKG.UPDATE_ROW (
294 X_LIST_NAME => X_LIST_NAME,
295 X_DATA_VALUE => X_DATA_VALUE,
296 X_NULL_VALUE_FLAG => X_NULL_VALUE_FLAG,
297 X_DELETED_FLAG => X_DELETED_FLAG,
298 X_NUMERIC_ID_VALUE => X_NUMERIC_ID_VALUE,
299 X_SEEDED_FLAG => X_SEEDED_FLAG,
300 X_VALUE_LABEL => X_VALUE_LABEL,
301 X_VALUE_DESCRIPTION => X_VALUE_DESCRIPTION,
302 X_LAST_UPDATE_DATE => f_ludate,
303 X_LAST_UPDATED_BY => f_luby,
304 X_LAST_UPDATE_LOGIN => 0);
305
306 fetch c_lvals into s_lname, s_dvalue;
307 exit when c_lvals%notfound;
308 end loop;
309 end if;
310 close c_lvals;
311
312 end LOAD_ROW;
313
314 end CZ_LOOKUP_VALUES_PKG;