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