[Home] [Help]
PACKAGE BODY: APPS.FND_FLEX_HIERARCHIES_PKG
Source
1 package body FND_FLEX_HIERARCHIES_PKG as
2 /* $Header: AFFFHIRB.pls 120.2.12010000.1 2008/07/25 14:14:01 appldev ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_FLEX_VALUE_SET_ID in NUMBER,
7 X_HIERARCHY_ID in NUMBER,
8 X_HIERARCHY_CODE in VARCHAR2,
9 X_HIERARCHY_NAME in VARCHAR2,
10 X_DESCRIPTION in VARCHAR2,
11 X_CREATION_DATE in DATE,
12 X_CREATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER
16 ) is
17 cursor C is select ROWID from FND_FLEX_HIERARCHIES
18 where FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID
19 and HIERARCHY_ID = X_HIERARCHY_ID
20 ;
21 begin
22 insert into FND_FLEX_HIERARCHIES (
23 FLEX_VALUE_SET_ID,
24 HIERARCHY_ID,
25 HIERARCHY_CODE,
26 CREATION_DATE,
27 CREATED_BY,
28 LAST_UPDATE_DATE,
29 LAST_UPDATED_BY,
30 LAST_UPDATE_LOGIN
31 ) values (
32 X_FLEX_VALUE_SET_ID,
33 X_HIERARCHY_ID,
34 X_HIERARCHY_CODE,
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
42 insert into FND_FLEX_HIERARCHIES_TL (
43 FLEX_VALUE_SET_ID,
44 HIERARCHY_ID,
45 HIERARCHY_NAME,
46 LAST_UPDATE_DATE,
47 LAST_UPDATED_BY,
48 CREATION_DATE,
49 CREATED_BY,
50 LAST_UPDATE_LOGIN,
51 DESCRIPTION,
52 LANGUAGE,
53 SOURCE_LANG
54 ) select
55 X_FLEX_VALUE_SET_ID,
56 X_HIERARCHY_ID,
57 X_HIERARCHY_NAME,
58 X_LAST_UPDATE_DATE,
59 X_LAST_UPDATED_BY,
60 X_CREATION_DATE,
61 X_CREATED_BY,
62 X_LAST_UPDATE_LOGIN,
63 X_DESCRIPTION,
64 L.LANGUAGE_CODE,
65 userenv('LANG')
66 from FND_LANGUAGES L
67 where L.INSTALLED_FLAG in ('I', 'B')
68 and not exists
69 (select NULL
70 from FND_FLEX_HIERARCHIES_TL T
71 where T.FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID
72 and T.HIERARCHY_ID = X_HIERARCHY_ID
73 and T.LANGUAGE = L.LANGUAGE_CODE);
74
75 open c;
76 fetch c into X_ROWID;
77 if (c%notfound) then
78 close c;
79 raise no_data_found;
80 end if;
81 close c;
82
83 end INSERT_ROW;
84
85 procedure LOCK_ROW (
86 X_FLEX_VALUE_SET_ID in NUMBER,
87 X_HIERARCHY_ID in NUMBER,
88 X_HIERARCHY_CODE in VARCHAR2,
89 X_HIERARCHY_NAME in VARCHAR2,
90 X_DESCRIPTION in VARCHAR2
91 ) is
92 cursor c is select
93 HIERARCHY_CODE
94 from FND_FLEX_HIERARCHIES
95 where FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID
96 and HIERARCHY_ID = X_HIERARCHY_ID
97 for update of FLEX_VALUE_SET_ID nowait;
98 recinfo c%rowtype;
99
100 cursor c1 is select
101 HIERARCHY_NAME,
102 DESCRIPTION,
103 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
104 from FND_FLEX_HIERARCHIES_TL
105 where FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID
106 and HIERARCHY_ID = X_HIERARCHY_ID
107 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
108 for update of FLEX_VALUE_SET_ID nowait;
109 begin
110 open c;
111 fetch c into recinfo;
112 if (c%notfound) then
113 close c;
114 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
115 app_exception.raise_exception;
116 end if;
117 close c;
118 if ( (recinfo.HIERARCHY_CODE = X_HIERARCHY_CODE)
119 ) then
120 null;
121 else
122 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
123 app_exception.raise_exception;
124 end if;
125
126 for tlinfo in c1 loop
127 if (tlinfo.BASELANG = 'Y') then
128 if ( (tlinfo.HIERARCHY_NAME = X_HIERARCHY_NAME)
129 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
130 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
131 ) then
132 null;
133 else
134 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
135 app_exception.raise_exception;
136 end if;
137 end if;
138 end loop;
139 return;
140 end LOCK_ROW;
141
142 procedure UPDATE_ROW (
143 X_FLEX_VALUE_SET_ID in NUMBER,
144 X_HIERARCHY_ID in NUMBER,
145 X_HIERARCHY_CODE in VARCHAR2,
146 X_HIERARCHY_NAME in VARCHAR2,
147 X_DESCRIPTION in VARCHAR2,
148 X_LAST_UPDATE_DATE in DATE,
149 X_LAST_UPDATED_BY in NUMBER,
150 X_LAST_UPDATE_LOGIN in NUMBER
151 ) is
152 begin
153 update FND_FLEX_HIERARCHIES set
154 HIERARCHY_CODE = X_HIERARCHY_CODE,
155 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
156 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
157 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
158 where FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID
159 and HIERARCHY_ID = X_HIERARCHY_ID;
160
161 if (sql%notfound) then
162 raise no_data_found;
163 end if;
164
165 update FND_FLEX_HIERARCHIES_TL set
166 HIERARCHY_NAME = X_HIERARCHY_NAME,
167 DESCRIPTION = X_DESCRIPTION,
168 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
169 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
170 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
171 SOURCE_LANG = userenv('LANG')
172 where FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID
173 and HIERARCHY_ID = X_HIERARCHY_ID
174 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
175
176 if (sql%notfound) then
177 raise no_data_found;
178 end if;
179 end UPDATE_ROW;
180
181 procedure DELETE_ROW (
182 X_FLEX_VALUE_SET_ID in NUMBER,
183 X_HIERARCHY_ID in NUMBER
184 ) is
185 begin
186 delete from FND_FLEX_HIERARCHIES_TL
187 where FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID
188 and HIERARCHY_ID = X_HIERARCHY_ID;
189
190 if (sql%notfound) then
191 raise no_data_found;
192 end if;
193
194 delete from FND_FLEX_HIERARCHIES
195 where FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID
196 and HIERARCHY_ID = X_HIERARCHY_ID;
197
198 if (sql%notfound) then
199 raise no_data_found;
200 end if;
201 end DELETE_ROW;
202
203 procedure ADD_LANGUAGE
204 is
205 begin
206 /* Mar/19/03 requested by Ric Ginsberg */
207 /* The following delete and update statements are commented out */
208 /* as a quick workaround to fix the time-consuming table handler issue */
209 /* Eventually we'll need to turn them into a separate fix_language procedure */
210 /*
211
212 delete from FND_FLEX_HIERARCHIES_TL T
213 where not exists
214 (select NULL
215 from FND_FLEX_HIERARCHIES B
216 where B.FLEX_VALUE_SET_ID = T.FLEX_VALUE_SET_ID
217 and B.HIERARCHY_ID = T.HIERARCHY_ID
218 );
219
220 update FND_FLEX_HIERARCHIES_TL T set (
221 HIERARCHY_NAME,
222 DESCRIPTION
223 ) = (select
224 B.HIERARCHY_NAME,
225 B.DESCRIPTION
226 from FND_FLEX_HIERARCHIES_TL B
227 where B.FLEX_VALUE_SET_ID = T.FLEX_VALUE_SET_ID
228 and B.HIERARCHY_ID = T.HIERARCHY_ID
229 and B.LANGUAGE = T.SOURCE_LANG)
230 where (
231 T.FLEX_VALUE_SET_ID,
232 T.HIERARCHY_ID,
233 T.LANGUAGE
234 ) in (select
235 SUBT.FLEX_VALUE_SET_ID,
236 SUBT.HIERARCHY_ID,
237 SUBT.LANGUAGE
238 from FND_FLEX_HIERARCHIES_TL SUBB, FND_FLEX_HIERARCHIES_TL SUBT
239 where SUBB.FLEX_VALUE_SET_ID = SUBT.FLEX_VALUE_SET_ID
240 and SUBB.HIERARCHY_ID = SUBT.HIERARCHY_ID
241 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
242 and (SUBB.HIERARCHY_NAME <> SUBT.HIERARCHY_NAME
243 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
244 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
245 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
246 ));
247 */
248
249 insert into FND_FLEX_HIERARCHIES_TL (
250 FLEX_VALUE_SET_ID,
251 HIERARCHY_ID,
252 HIERARCHY_NAME,
253 LAST_UPDATE_DATE,
254 LAST_UPDATED_BY,
255 CREATION_DATE,
256 CREATED_BY,
257 LAST_UPDATE_LOGIN,
258 DESCRIPTION,
259 LANGUAGE,
260 SOURCE_LANG
261 ) select
262 B.FLEX_VALUE_SET_ID,
263 B.HIERARCHY_ID,
264 B.HIERARCHY_NAME,
265 B.LAST_UPDATE_DATE,
266 B.LAST_UPDATED_BY,
267 B.CREATION_DATE,
268 B.CREATED_BY,
269 B.LAST_UPDATE_LOGIN,
270 B.DESCRIPTION,
271 L.LANGUAGE_CODE,
272 B.SOURCE_LANG
273 from FND_FLEX_HIERARCHIES_TL B, FND_LANGUAGES L
274 where L.INSTALLED_FLAG in ('I', 'B')
275 and B.LANGUAGE = userenv('LANG')
276 and not exists
277 (select NULL
278 from FND_FLEX_HIERARCHIES_TL T
279 where T.FLEX_VALUE_SET_ID = B.FLEX_VALUE_SET_ID
280 and T.HIERARCHY_ID = B.HIERARCHY_ID
281 and T.LANGUAGE = L.LANGUAGE_CODE);
282 end ADD_LANGUAGE;
283
284 PROCEDURE load_row
285 (x_flex_value_set_name IN VARCHAR2,
286 x_hierarchy_code IN VARCHAR2,
287 x_who IN fnd_flex_loader_apis.who_type,
288 x_hierarchy_name IN VARCHAR2,
289 x_description IN VARCHAR2)
290 IS
291 l_flex_value_set_id NUMBER;
292 l_hierarchy_id NUMBER;
293 l_rowid VARCHAR2(64);
294 BEGIN
295 SELECT flex_value_set_id
296 INTO l_flex_value_set_id
297 FROM fnd_flex_value_sets
298 WHERE flex_value_set_name = x_flex_value_set_name;
299
300 BEGIN
301 SELECT hierarchy_id
302 INTO l_hierarchy_id
303 FROM fnd_flex_hierarchies
304 WHERE flex_value_set_id = l_flex_value_set_id
305 AND hierarchy_code = x_hierarchy_code;
306
307 fnd_flex_hierarchies_pkg.update_row
308 (X_FLEX_VALUE_SET_ID => l_flex_value_set_id,
309 X_HIERARCHY_ID => l_hierarchy_id,
310 X_HIERARCHY_CODE => x_hierarchy_code,
311 X_HIERARCHY_NAME => x_hierarchy_name,
312 X_DESCRIPTION => x_description,
313 X_LAST_UPDATE_DATE => x_who.last_update_date,
314 X_LAST_UPDATED_BY => x_who.last_updated_by,
315 X_LAST_UPDATE_LOGIN => x_who.last_update_login);
316 EXCEPTION
317 WHEN no_data_found THEN
318 SELECT fnd_flex_hierarchies_s.NEXTVAL
319 INTO l_hierarchy_id
320 FROM dual;
321
322 fnd_flex_hierarchies_pkg.insert_row
323 (X_ROWID => l_rowid,
324 X_FLEX_VALUE_SET_ID => l_flex_value_set_id,
325 X_HIERARCHY_ID => l_hierarchy_id,
326 X_HIERARCHY_CODE => x_hierarchy_code,
327 X_HIERARCHY_NAME => x_hierarchy_name,
328 X_DESCRIPTION => x_description,
329 X_CREATION_DATE => x_who.creation_date,
330 X_CREATED_BY => x_who.created_by,
331 X_LAST_UPDATE_DATE => x_who.last_update_date,
332 X_LAST_UPDATED_BY => x_who.last_updated_by,
333 X_LAST_UPDATE_LOGIN => x_who.last_update_login);
334 END;
335 END load_row;
336
337 PROCEDURE translate_row
338 (x_flex_value_set_name IN VARCHAR2,
339 x_hierarchy_code IN VARCHAR2,
340 x_who IN fnd_flex_loader_apis.who_type,
341 x_hierarchy_name IN VARCHAR2,
342 x_description IN VARCHAR2)
343 IS
344 BEGIN
345 UPDATE fnd_flex_hierarchies_tl SET
346 hierarchy_name = Nvl(x_hierarchy_name, hierarchy_name),
347 description = Nvl(x_description, description),
348 last_update_date = x_who.last_update_date,
349 last_updated_by = x_who.last_updated_by,
350 last_update_login = x_who.last_update_login,
351 source_lang = userenv('LANG')
352 WHERE ((flex_value_set_id, hierarchy_id) =
353 (SELECT flex_value_set_id, hierarchy_id
354 FROM fnd_flex_hierarchies
355 WHERE (flex_value_set_id =
356 (SELECT flex_value_set_id
357 FROM fnd_flex_value_sets
358 WHERE flex_value_set_name = x_flex_value_set_name))
359 AND hierarchy_code = x_hierarchy_code))
360 AND userenv('LANG') in (language, source_lang);
361 END translate_row;
362
363 end FND_FLEX_HIERARCHIES_PKG;