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