[Home] [Help]
PACKAGE BODY: APPS.ITA_SETUP_HIERARCHY_PKG
Source
1 package body ITA_SETUP_HIERARCHY_PKG as
2 /* $Header: itathirb.pls 120.0 2005/05/31 16:35:42 appldev noship $ */
3
4
5 procedure INSERT_ROW (
6 X_HIERARCHY_LEVEL_CODE in VARCHAR2,
7 X_HIERARCHY_LEVEL_NAME in VARCHAR2,
8 X_SECURITY_GROUP_ID in NUMBER,
9 X_OBJECT_VERSION_NUMBER in NUMBER,
10 X_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_DATE in DATE,
13 X_LAST_UPDATED_BY in NUMBER,
14 X_LAST_UPDATE_LOGIN in NUMBER
15 ) is
16 begin
17 insert into ITA_SETUP_HIERARCHY_B (
18 HIERARCHY_LEVEL_CODE,
19 SECURITY_GROUP_ID,
20 OBJECT_VERSION_NUMBER,
21 CREATION_DATE,
22 CREATED_BY,
23 LAST_UPDATE_DATE,
24 LAST_UPDATED_BY,
25 LAST_UPDATE_LOGIN
26 ) values (
27 X_HIERARCHY_LEVEL_CODE,
28 X_SECURITY_GROUP_ID,
29 X_OBJECT_VERSION_NUMBER,
30 X_CREATION_DATE,
31 X_CREATED_BY,
32 X_LAST_UPDATE_DATE,
33 X_LAST_UPDATED_BY,
34 X_LAST_UPDATE_LOGIN
35 );
36
37 insert into ITA_SETUP_HIERARCHY_TL (
38 HIERARCHY_LEVEL_CODE,
39 HIERARCHY_LEVEL_NAME,
40 SECURITY_GROUP_ID,
41 OBJECT_VERSION_NUMBER,
42 CREATION_DATE,
43 CREATED_BY,
44 LAST_UPDATE_DATE,
45 LAST_UPDATED_BY,
46 LAST_UPDATE_LOGIN,
47 LANGUAGE,
48 SOURCE_LANG
49 ) select
50 X_HIERARCHY_LEVEL_CODE,
51 X_HIERARCHY_LEVEL_NAME,
52 X_SECURITY_GROUP_ID,
53 X_OBJECT_VERSION_NUMBER,
54 X_CREATION_DATE,
55 X_CREATED_BY,
56 X_LAST_UPDATE_DATE,
57 X_LAST_UPDATED_BY,
58 X_LAST_UPDATE_LOGIN,
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 ITA_SETUP_HIERARCHY_TL tl
66 where
67 tl.HIERARCHY_LEVEL_CODE = X_HIERARCHY_LEVEL_CODE and
68 tl.LANGUAGE = L.LANGUAGE_CODE);
69 end INSERT_ROW;
70
71
72 procedure LOCK_ROW (
73 X_HIERARCHY_LEVEL_CODE in VARCHAR2,
74 X_HIERARCHY_LEVEL_NAME in VARCHAR2,
75 X_SECURITY_GROUP_ID in NUMBER,
76 X_OBJECT_VERSION_NUMBER in NUMBER
77 ) is
78 cursor c is select
79 HIERARCHY_LEVEL_CODE,
80 SECURITY_GROUP_ID,
81 OBJECT_VERSION_NUMBER
82 from ITA_SETUP_HIERARCHY_B
83 where HIERARCHY_LEVEL_CODE = X_HIERARCHY_LEVEL_CODE
84 for update of HIERARCHY_LEVEL_CODE nowait;
85 recinfo c%rowtype;
86
87 cursor c1 is select
88 HIERARCHY_LEVEL_NAME,
89 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
90 from ITA_SETUP_HIERARCHY_TL
91 where
92 HIERARCHY_LEVEL_CODE = X_HIERARCHY_LEVEL_CODE and
93 userenv('LANG') in (LANGUAGE, SOURCE_LANG)
94 for update of HIERARCHY_LEVEL_CODE nowait;
95 begin
96 open c;
97 fetch c into recinfo;
98 if (c%notfound) then
99 close c;
100 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
101 app_exception.raise_exception;
102 end if;
103 close c;
104 if ( (recinfo.HIERARCHY_LEVEL_CODE = X_HIERARCHY_LEVEL_CODE)
105 and ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
106 or ((recinfo.SECURITY_GROUP_ID is null) and (X_SECURITY_GROUP_ID is null)))
107 and ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
108 or ((recinfo.OBJECT_VERSION_NUMBER is null) and (X_OBJECT_VERSION_NUMBER is null)))
109 ) then
110 null;
111 else
112 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
113 app_exception.raise_exception;
114 end if;
115
116 for tlinfo in c1 loop
117 if (tlinfo.BASELANG = 'Y') then
118 if ( ((tlinfo.HIERARCHY_LEVEL_NAME = X_HIERARCHY_LEVEL_NAME)
119 or ((tlinfo.HIERARCHY_LEVEL_NAME is null) and (X_HIERARCHY_LEVEL_NAME is null)))
120 ) then
121 null;
122 else
123 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
124 app_exception.raise_exception;
125 end if;
126 end if;
127 end loop;
128 return;
129 end LOCK_ROW;
130
131
132 procedure UPDATE_ROW (
133 X_HIERARCHY_LEVEL_CODE in VARCHAR2,
134 X_HIERARCHY_LEVEL_NAME in VARCHAR2,
135 X_SECURITY_GROUP_ID in NUMBER,
136 X_OBJECT_VERSION_NUMBER in NUMBER,
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 ITA_SETUP_HIERARCHY_B set
143 HIERARCHY_LEVEL_CODE = X_HIERARCHY_LEVEL_CODE,
144 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
145 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
146 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
147 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
148 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
149 where HIERARCHY_LEVEL_CODE = X_HIERARCHY_LEVEL_CODE;
150
151 if (sql%notfound) then
152 raise no_data_found;
153 end if;
154
155 update ITA_SETUP_HIERARCHY_TL set
156 HIERARCHY_LEVEL_NAME = X_HIERARCHY_LEVEL_NAME,
157 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
158 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
159 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
160 SOURCE_LANG = userenv('LANG')
161 where
162 HIERARCHY_LEVEL_CODE = X_HIERARCHY_LEVEL_CODE and
163 userenv('LANG') in (LANGUAGE, SOURCE_LANG);
164
165 if (sql%notfound) then
166 raise no_data_found;
167 end if;
168 end UPDATE_ROW;
169
170
171 procedure DELETE_ROW (
172 X_HIERARCHY_LEVEL_CODE in VARCHAR2
173 ) is
174 begin
175 delete from ITA_SETUP_HIERARCHY_TL
176 where HIERARCHY_LEVEL_CODE = X_HIERARCHY_LEVEL_CODE;
177
178 if (sql%notfound) then
179 raise no_data_found;
180 end if;
181
182 delete from ITA_SETUP_HIERARCHY_B
183 where HIERARCHY_LEVEL_CODE = X_HIERARCHY_LEVEL_CODE;
184
185 if (sql%notfound) then
186 raise no_data_found;
187 end if;
188 end DELETE_ROW;
189
190
191 procedure LOAD_ROW (
192 X_HIERARCHY_LEVEL_CODE in VARCHAR2,
193 X_HIERARCHY_LEVEL_NAME in VARCHAR2,
194 X_LAST_UPDATE_DATE in VARCHAR2,
195 X_OWNER in VARCHAR2,
196 X_CUSTOM_MODE in VARCHAR2
197 ) is
198
199 f_luby number; -- entity owner in file
200 f_ludate date; -- entity update date in file
201 db_luby number; -- entity owner in db
202 db_ludate date; -- entity update date in db
203
204 begin
205 -- Translate owner to file_last_updated_by
206 f_luby := fnd_load_util.owner_id(X_OWNER);
207
208 -- Translate char last_update_date to date
209 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
210
211 select LAST_UPDATED_BY, LAST_UPDATE_DATE into db_luby, db_ludate
212 from ITA_SETUP_HIERARCHY_B
213 where HIERARCHY_LEVEL_CODE = X_HIERARCHY_LEVEL_CODE;
214
215 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, X_CUSTOM_MODE))
216 then ITA_SETUP_HIERARCHY_PKG.UPDATE_ROW (
217 X_HIERARCHY_LEVEL_CODE => X_HIERARCHY_LEVEL_CODE,
218 X_HIERARCHY_LEVEL_NAME => X_HIERARCHY_LEVEL_NAME,
219 X_SECURITY_GROUP_ID => null,
220 X_OBJECT_VERSION_NUMBER => 1,
221 X_LAST_UPDATE_DATE => f_ludate,
222 X_LAST_UPDATED_BY => f_luby,
223 X_LAST_UPDATE_LOGIN => 0);
224 end if;
225 exception when NO_DATA_FOUND
226 then ITA_SETUP_HIERARCHY_PKG.INSERT_ROW (
227 X_HIERARCHY_LEVEL_CODE => X_HIERARCHY_LEVEL_CODE,
228 X_HIERARCHY_LEVEL_NAME => X_HIERARCHY_LEVEL_NAME,
229 X_SECURITY_GROUP_ID => null,
230 X_OBJECT_VERSION_NUMBER => 1,
231 X_CREATION_DATE => f_ludate,
232 X_CREATED_BY => f_luby,
233 X_LAST_UPDATE_DATE => f_ludate,
234 X_LAST_UPDATED_BY => f_luby,
235 X_LAST_UPDATE_LOGIN => 0);
236 end LOAD_ROW;
237
238
239 procedure ADD_LANGUAGE
240 is
241 begin
242 delete from ITA_SETUP_HIERARCHY_TL tl
243 where not exists (
244 select null
245 from ITA_SETUP_HIERARCHY_B b
246 where b.HIERARCHY_LEVEL_CODE = tl.HIERARCHY_LEVEL_CODE
247 );
248
249 update ITA_SETUP_HIERARCHY_TL tl set (
250 HIERARCHY_LEVEL_NAME
251 ) = (select
252 b.HIERARCHY_LEVEL_NAME
253 from ITA_SETUP_HIERARCHY_TL b
254 where
255 b.HIERARCHY_LEVEL_CODE = tl.HIERARCHY_LEVEL_CODE and
256 b.LANGUAGE = tl.SOURCE_LANG)
257 where (
258 tl.HIERARCHY_LEVEL_CODE,
259 tl.LANGUAGE
260 ) in (select
261 subtl.HIERARCHY_LEVEL_CODE,
262 subtl.LANGUAGE
263 from ITA_SETUP_HIERARCHY_TL subb, ITA_SETUP_HIERARCHY_TL subtl
264 where
265 subb.HIERARCHY_LEVEL_CODE = subtl.HIERARCHY_LEVEL_CODE and
266 subb.LANGUAGE = subtl.SOURCE_LANG and
267 (subb.HIERARCHY_LEVEL_NAME <> subtl.HIERARCHY_LEVEL_NAME or
268 (subb.HIERARCHY_LEVEL_NAME is null and subtl.HIERARCHY_LEVEL_NAME is not null) or
269 (subb.HIERARCHY_LEVEL_NAME is not null and subtl.HIERARCHY_LEVEL_NAME is null)));
270
271 insert into ITA_SETUP_HIERARCHY_TL (
272 HIERARCHY_LEVEL_CODE,
273 HIERARCHY_LEVEL_NAME,
274 CREATED_BY,
275 CREATION_DATE,
276 LAST_UPDATED_BY,
277 LAST_UPDATE_DATE,
278 LAST_UPDATE_LOGIN,
279 SECURITY_GROUP_ID,
280 OBJECT_VERSION_NUMBER,
281 LANGUAGE,
282 SOURCE_LANG
283 ) select
284 b.HIERARCHY_LEVEL_CODE,
285 b.HIERARCHY_LEVEL_NAME,
286 b.CREATED_BY,
287 b.CREATION_DATE,
288 b.LAST_UPDATED_BY,
289 b.LAST_UPDATE_DATE,
290 b.LAST_UPDATE_LOGIN,
291 b.SECURITY_GROUP_ID,
292 b.OBJECT_VERSION_NUMBER,
293 L.LANGUAGE_CODE,
294 b.SOURCE_LANG
295 from ITA_SETUP_HIERARCHY_TL b, FND_LANGUAGES L
296 where
297 L.INSTALLED_FLAG in ('I', 'B') and
298 b.LANGUAGE = userenv('LANG') and
299 not exists (
300 select null
301 from ITA_SETUP_HIERARCHY_TL tl
302 where
303 tl.HIERARCHY_LEVEL_CODE = b.HIERARCHY_LEVEL_CODE and
304 tl.LANGUAGE = L.LANGUAGE_CODE);
305 end ADD_LANGUAGE;
306
307
308 procedure TRANSLATE_ROW (
309 X_HIERARCHY_LEVEL_CODE in VARCHAR2,
310 X_HIERARCHY_LEVEL_NAME in VARCHAR2,
311 X_LAST_UPDATE_DATE in VARCHAR2,
312 X_OWNER in VARCHAR2,
313 X_CUSTOM_MODE in VARCHAR2
314 ) is
315
316 f_luby number; -- entity owner in file
317 f_ludate date; -- entity update date in file
318 db_luby number; -- entity owner in db
319 db_ludate date; -- entity update date in db
320
321 begin
322 -- Translate owner to file_last_updated_by
323 f_luby := fnd_load_util.owner_id(X_OWNER);
324
325 -- Translate char last_update_date to date
326 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
327
328 select LAST_UPDATED_BY, LAST_UPDATE_DATE into db_luby, db_ludate
329 from ITA_SETUP_HIERARCHY_TL
330 where HIERARCHY_LEVEL_CODE = X_HIERARCHY_LEVEL_CODE and LANGUAGE = userenv('LANG');
331
332 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, X_CUSTOM_MODE))
333 then update ITA_SETUP_HIERARCHY_TL set
334 HIERARCHY_LEVEL_NAME = nvl(X_HIERARCHY_LEVEL_NAME, HIERARCHY_LEVEL_NAME),
335 SOURCE_LANG = userenv('LANG'),
336 LAST_UPDATE_DATE = f_ludate,
337 LAST_UPDATED_BY = f_luby,
338 LAST_UPDATE_LOGIN = 0
339 where HIERARCHY_LEVEL_CODE = X_HIERARCHY_LEVEL_CODE and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
340 end if;
341 end TRANSLATE_ROW;
342
343
344 end ITA_SETUP_HIERARCHY_PKG;