1 package body OKE_OBJECT_ATTRIBUTES_PKG as
2 /* $Header: OKEOBATB.pls 120.1 2005/06/02 12:00:04 appldev $ */
3 procedure INSERT_ROW (
4 X_ROWID IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
5 X_DATABASE_OBJECT_NAME in VARCHAR2,
6 X_ATTRIBUTE_CODE in VARCHAR2,
7 X_DATATYPE in VARCHAR2,
8 X_SECURABLE_FLAG in VARCHAR2,
9 X_QUERYABLE_FLAG in VARCHAR2,
10 X_VIEW_COLUMN_FLAG in VARCHAR2,
11 X_FORM_ITEM_FLAG in VARCHAR2,
12 X_ATTRIBUTE_GROUP_CODE in VARCHAR2,
13 X_PARENT_ATTRIBUTE_CODE in VARCHAR2,
14 X_ATTRIBUTE_NAME in VARCHAR2,
15 X_DESCRIPTION in VARCHAR2,
16 X_CREATION_DATE in DATE,
17 X_CREATED_BY in NUMBER,
18 X_LAST_UPDATE_DATE in DATE,
19 X_LAST_UPDATED_BY in NUMBER,
20 X_LAST_UPDATE_LOGIN in NUMBER
21 ) is
22 cursor C is select ROWID from OKE_OBJECT_ATTRIBUTES_B
23 where DATABASE_OBJECT_NAME = X_DATABASE_OBJECT_NAME
24 and ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
25 ;
26 begin
27 insert into OKE_OBJECT_ATTRIBUTES_B (
28 DATABASE_OBJECT_NAME,
29 ATTRIBUTE_CODE,
30 DATATYPE,
31 SECURABLE_FLAG,
32 QUERYABLE_FLAG,
33 VIEW_COLUMN_FLAG,
34 FORM_ITEM_FLAG,
35 ATTRIBUTE_GROUP_CODE,
36 PARENT_ATTRIBUTE_CODE,
37 CREATION_DATE,
38 CREATED_BY,
39 LAST_UPDATE_DATE,
40 LAST_UPDATED_BY,
41 LAST_UPDATE_LOGIN
42 ) values (
43 X_DATABASE_OBJECT_NAME,
44 X_ATTRIBUTE_CODE,
45 X_DATATYPE,
46 X_SECURABLE_FLAG,
47 X_QUERYABLE_FLAG,
48 X_VIEW_COLUMN_FLAG,
49 X_FORM_ITEM_FLAG,
50 X_ATTRIBUTE_GROUP_CODE,
51 X_PARENT_ATTRIBUTE_CODE,
52 X_CREATION_DATE,
53 X_CREATED_BY,
54 X_LAST_UPDATE_DATE,
55 X_LAST_UPDATED_BY,
56 X_LAST_UPDATE_LOGIN
57 );
58
59 insert into OKE_OBJECT_ATTRIBUTES_TL (
60 DATABASE_OBJECT_NAME,
61 ATTRIBUTE_CODE,
62 CREATION_DATE,
63 CREATED_BY,
64 LAST_UPDATE_DATE,
65 LAST_UPDATED_BY,
66 LAST_UPDATE_LOGIN,
67 ATTRIBUTE_NAME,
68 DESCRIPTION,
69 LANGUAGE,
70 SOURCE_LANG
71 ) select
72 X_DATABASE_OBJECT_NAME,
73 X_ATTRIBUTE_CODE,
74 X_CREATION_DATE,
75 X_CREATED_BY,
76 X_LAST_UPDATE_DATE,
77 X_LAST_UPDATED_BY,
78 X_LAST_UPDATE_LOGIN,
79 X_ATTRIBUTE_NAME,
80 X_DESCRIPTION,
81 L.LANGUAGE_CODE,
82 userenv('LANG')
83 from FND_LANGUAGES L
84 where L.INSTALLED_FLAG in ('I', 'B')
85 and not exists
86 (select NULL
87 from OKE_OBJECT_ATTRIBUTES_TL T
88 where T.DATABASE_OBJECT_NAME = X_DATABASE_OBJECT_NAME
89 and T.ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
90 and T.LANGUAGE = L.LANGUAGE_CODE);
91
92 open c;
93 fetch c into X_ROWID;
94 if (c%notfound) then
95 close c;
96 raise no_data_found;
97 end if;
98 close c;
99
100 end INSERT_ROW;
101
102 procedure LOCK_ROW (
103 X_DATABASE_OBJECT_NAME in VARCHAR2,
104 X_ATTRIBUTE_CODE in VARCHAR2,
105 X_DATATYPE in VARCHAR2,
106 X_SECURABLE_FLAG in VARCHAR2,
107 X_QUERYABLE_FLAG in VARCHAR2,
108 X_VIEW_COLUMN_FLAG in VARCHAR2,
109 X_FORM_ITEM_FLAG in VARCHAR2,
110 X_ATTRIBUTE_GROUP_CODE in VARCHAR2,
111 X_PARENT_ATTRIBUTE_CODE in VARCHAR2,
112 X_ATTRIBUTE_NAME in VARCHAR2,
113 X_DESCRIPTION in VARCHAR2
114 ) is
115 cursor c is select
116 DATATYPE,
117 SECURABLE_FLAG,
118 QUERYABLE_FLAG,
119 VIEW_COLUMN_FLAG,
120 FORM_ITEM_FLAG,
121 ATTRIBUTE_GROUP_CODE,
122 PARENT_ATTRIBUTE_CODE
123 from OKE_OBJECT_ATTRIBUTES_B
124 where DATABASE_OBJECT_NAME = X_DATABASE_OBJECT_NAME
125 and ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
126 for update of DATABASE_OBJECT_NAME nowait;
127 recinfo c%rowtype;
128
129 cursor c1 is select
130 ATTRIBUTE_NAME,
131 DESCRIPTION,
132 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
133 from OKE_OBJECT_ATTRIBUTES_TL
134 where DATABASE_OBJECT_NAME = X_DATABASE_OBJECT_NAME
135 and ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
136 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
137 for update of DATABASE_OBJECT_NAME nowait;
138 begin
139 open c;
140 fetch c into recinfo;
141 if (c%notfound) then
142 close c;
143 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
144 app_exception.raise_exception;
145 end if;
146 close c;
147 if ( (recinfo.DATATYPE = X_DATATYPE)
148 AND ((recinfo.SECURABLE_FLAG = X_SECURABLE_FLAG)
149 OR ((recinfo.SECURABLE_FLAG is null) AND (X_SECURABLE_FLAG is null)))
150 AND ((recinfo.QUERYABLE_FLAG = X_QUERYABLE_FLAG)
151 OR ((recinfo.QUERYABLE_FLAG is null) AND (X_QUERYABLE_FLAG is null)))
152 AND (recinfo.VIEW_COLUMN_FLAG = X_VIEW_COLUMN_FLAG)
153 AND ((recinfo.FORM_ITEM_FLAG = X_FORM_ITEM_FLAG)
154 OR ((recinfo.FORM_ITEM_FLAG is null) AND (X_FORM_ITEM_FLAG is null)))
155 AND ((recinfo.ATTRIBUTE_GROUP_CODE = X_ATTRIBUTE_GROUP_CODE)
156 OR ((recinfo.ATTRIBUTE_GROUP_CODE is null) AND (X_ATTRIBUTE_GROUP_CODE is null)))
157 AND ((recinfo.PARENT_ATTRIBUTE_CODE = X_PARENT_ATTRIBUTE_CODE)
158 OR ((recinfo.PARENT_ATTRIBUTE_CODE is null) AND (X_PARENT_ATTRIBUTE_CODE is null)))
159 ) then
160 null;
161 else
162 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
163 app_exception.raise_exception;
164 end if;
165
166 for tlinfo in c1 loop
167 if (tlinfo.BASELANG = 'Y') then
168 if ( ((tlinfo.ATTRIBUTE_NAME = X_ATTRIBUTE_NAME)
169 OR ((tlinfo.ATTRIBUTE_NAME is null) AND (X_ATTRIBUTE_NAME is null)))
170 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
171 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
172 ) then
173 null;
174 else
175 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
176 app_exception.raise_exception;
177 end if;
178 end if;
179 end loop;
180 return;
181 end LOCK_ROW;
182
183 procedure UPDATE_ROW (
184 X_DATABASE_OBJECT_NAME in VARCHAR2,
185 X_ATTRIBUTE_CODE in VARCHAR2,
186 X_DATATYPE in VARCHAR2,
187 X_SECURABLE_FLAG in VARCHAR2,
188 X_QUERYABLE_FLAG in VARCHAR2,
189 X_VIEW_COLUMN_FLAG in VARCHAR2,
190 X_FORM_ITEM_FLAG in VARCHAR2,
191 X_ATTRIBUTE_GROUP_CODE in VARCHAR2,
192 X_PARENT_ATTRIBUTE_CODE in VARCHAR2,
193 X_ATTRIBUTE_NAME in VARCHAR2,
194 X_DESCRIPTION in VARCHAR2,
195 X_LAST_UPDATE_DATE in DATE,
196 X_LAST_UPDATED_BY in NUMBER,
197 X_LAST_UPDATE_LOGIN in NUMBER
198 ) is
199 begin
200 update OKE_OBJECT_ATTRIBUTES_B set
201 DATATYPE = X_DATATYPE,
202 SECURABLE_FLAG = X_SECURABLE_FLAG,
203 QUERYABLE_FLAG = X_QUERYABLE_FLAG,
204 VIEW_COLUMN_FLAG = X_VIEW_COLUMN_FLAG,
205 FORM_ITEM_FLAG = X_FORM_ITEM_FLAG,
206 ATTRIBUTE_GROUP_CODE = X_ATTRIBUTE_GROUP_CODE,
207 PARENT_ATTRIBUTE_CODE = X_PARENT_ATTRIBUTE_CODE,
208 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
209 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
210 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
211 where DATABASE_OBJECT_NAME = X_DATABASE_OBJECT_NAME
212 and ATTRIBUTE_CODE = X_ATTRIBUTE_CODE;
213
214 if (sql%notfound) then
215 raise no_data_found;
216 end if;
217
218 update OKE_OBJECT_ATTRIBUTES_TL set
219 ATTRIBUTE_NAME = X_ATTRIBUTE_NAME,
220 DESCRIPTION = X_DESCRIPTION,
221 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
222 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
223 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
224 SOURCE_LANG = userenv('LANG')
225 where DATABASE_OBJECT_NAME = X_DATABASE_OBJECT_NAME
226 and ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
227 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
228
229 if (sql%notfound) then
230 raise no_data_found;
231 end if;
232 end UPDATE_ROW;
233
234 procedure DELETE_ROW (
235 X_DATABASE_OBJECT_NAME in VARCHAR2,
236 X_ATTRIBUTE_CODE in VARCHAR2
237 ) is
238 begin
239 delete from OKE_OBJECT_ATTRIBUTES_TL
240 where DATABASE_OBJECT_NAME = X_DATABASE_OBJECT_NAME
241 and ATTRIBUTE_CODE = X_ATTRIBUTE_CODE;
242
243 if (sql%notfound) then
244 raise no_data_found;
245 end if;
246
247 delete from OKE_OBJECT_ATTRIBUTES_B
248 where DATABASE_OBJECT_NAME = X_DATABASE_OBJECT_NAME
249 and ATTRIBUTE_CODE = X_ATTRIBUTE_CODE;
250
251 if (sql%notfound) then
252 raise no_data_found;
253 end if;
254 end DELETE_ROW;
255
256 procedure ADD_LANGUAGE
257 is
258 begin
259 delete from OKE_OBJECT_ATTRIBUTES_TL T
260 where not exists
261 (select NULL
262 from OKE_OBJECT_ATTRIBUTES_B B
263 where B.DATABASE_OBJECT_NAME = T.DATABASE_OBJECT_NAME
264 and B.ATTRIBUTE_CODE = T.ATTRIBUTE_CODE
265 );
266
267 update OKE_OBJECT_ATTRIBUTES_TL T set (
268 ATTRIBUTE_NAME,
269 DESCRIPTION
270 ) = (select
271 B.ATTRIBUTE_NAME,
272 B.DESCRIPTION
273 from OKE_OBJECT_ATTRIBUTES_TL B
274 where B.DATABASE_OBJECT_NAME = T.DATABASE_OBJECT_NAME
275 and B.ATTRIBUTE_CODE = T.ATTRIBUTE_CODE
276 and B.LANGUAGE = T.SOURCE_LANG)
277 where (
278 T.DATABASE_OBJECT_NAME,
279 T.ATTRIBUTE_CODE,
280 T.LANGUAGE
281 ) in (select
282 SUBT.DATABASE_OBJECT_NAME,
283 SUBT.ATTRIBUTE_CODE,
284 SUBT.LANGUAGE
285 from OKE_OBJECT_ATTRIBUTES_TL SUBB, OKE_OBJECT_ATTRIBUTES_TL SUBT
286 where SUBB.DATABASE_OBJECT_NAME = SUBT.DATABASE_OBJECT_NAME
287 and SUBB.ATTRIBUTE_CODE = SUBT.ATTRIBUTE_CODE
288 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
289 and (SUBB.ATTRIBUTE_NAME <> SUBT.ATTRIBUTE_NAME
290 or (SUBB.ATTRIBUTE_NAME is null and SUBT.ATTRIBUTE_NAME is not null)
291 or (SUBB.ATTRIBUTE_NAME is not null and SUBT.ATTRIBUTE_NAME is null)
292 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
293 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
294 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
295 ));
296
297 insert into OKE_OBJECT_ATTRIBUTES_TL (
298 DATABASE_OBJECT_NAME,
299 ATTRIBUTE_CODE,
300 CREATION_DATE,
301 CREATED_BY,
302 LAST_UPDATE_DATE,
303 LAST_UPDATED_BY,
304 LAST_UPDATE_LOGIN,
305 ATTRIBUTE_NAME,
306 DESCRIPTION,
307 LANGUAGE,
308 SOURCE_LANG
309 ) select
310 B.DATABASE_OBJECT_NAME,
311 B.ATTRIBUTE_CODE,
312 B.CREATION_DATE,
313 B.CREATED_BY,
314 B.LAST_UPDATE_DATE,
315 B.LAST_UPDATED_BY,
316 B.LAST_UPDATE_LOGIN,
317 B.ATTRIBUTE_NAME,
318 B.DESCRIPTION,
319 L.LANGUAGE_CODE,
320 B.SOURCE_LANG
321 from OKE_OBJECT_ATTRIBUTES_TL B, FND_LANGUAGES L
322 where L.INSTALLED_FLAG in ('I', 'B')
323 and B.LANGUAGE = userenv('LANG')
324 and not exists
325 (select NULL
326 from OKE_OBJECT_ATTRIBUTES_TL T
327 where T.DATABASE_OBJECT_NAME = B.DATABASE_OBJECT_NAME
328 and T.ATTRIBUTE_CODE = B.ATTRIBUTE_CODE
329 and T.LANGUAGE = L.LANGUAGE_CODE);
330 end ADD_LANGUAGE;
331
332 end OKE_OBJECT_ATTRIBUTES_PKG;
[Home] [Help]