[Home] [Help]
PACKAGE BODY: APPS.AMS_CELLS_ALL_PKG
Source
1 PACKAGE BODY AMS_CELLS_ALL_PKG AS
2 /* $Header: amslcelb.pls 115.2 2000/01/09 17:36:39 pkm ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out VARCHAR2,
5 X_CELL_ID in NUMBER,
6 X_OWNER_ID in NUMBER,
7 X_OBJECT_VERSION_NUMBER in NUMBER,
8 X_CELL_CODE in VARCHAR2,
9 X_MARKET_SEGMENT_FLAG in VARCHAR2,
10 X_ENABLED_FLAG in VARCHAR2,
11 X_ORIGINAL_SIZE in NUMBER,
12 X_PARENT_CELL_ID in NUMBER,
13 X_CELL_NAME in VARCHAR2,
14 X_DESCRIPTION in VARCHAR2,
15 X_CREATION_DATE in DATE,
16 X_CREATED_BY in NUMBER,
17 X_LAST_UPDATE_DATE in DATE,
18 X_LAST_UPDATED_BY in NUMBER,
19 X_LAST_UPDATE_LOGIN in NUMBER
20 ) is
21 cursor C is select ROWID from AMS_CELLS_ALL_B
22 where CELL_ID = X_CELL_ID
23 ;
24 begin
25 insert into AMS_CELLS_ALL_B (
26 OWNER_ID,
27 CELL_ID,
28 OBJECT_VERSION_NUMBER,
29 CELL_CODE,
30 MARKET_SEGMENT_FLAG,
31 ENABLED_FLAG,
32 ORIGINAL_SIZE,
33 PARENT_CELL_ID,
34 CREATION_DATE,
35 CREATED_BY,
36 LAST_UPDATE_DATE,
37 LAST_UPDATED_BY,
38 LAST_UPDATE_LOGIN
39 ) values (
40 X_OWNER_ID,
41 X_CELL_ID,
42 X_OBJECT_VERSION_NUMBER,
43 X_CELL_CODE,
44 X_MARKET_SEGMENT_FLAG,
45 X_ENABLED_FLAG,
46 X_ORIGINAL_SIZE,
47 X_PARENT_CELL_ID,
48 X_CREATION_DATE,
49 X_CREATED_BY,
50 X_LAST_UPDATE_DATE,
51 X_LAST_UPDATED_BY,
52 X_LAST_UPDATE_LOGIN
53 );
54
55 insert into AMS_CELLS_ALL_TL (
56 DESCRIPTION,
57 LAST_UPDATE_LOGIN,
58 CELL_NAME,
59 CELL_ID,
60 LAST_UPDATE_DATE,
61 LAST_UPDATED_BY,
62 CREATION_DATE,
63 CREATED_BY,
64 LANGUAGE,
65 SOURCE_LANG
66 ) select
67 X_DESCRIPTION,
68 X_LAST_UPDATE_LOGIN,
69 X_CELL_NAME,
70 X_CELL_ID,
71 X_LAST_UPDATE_DATE,
72 X_LAST_UPDATED_BY,
73 X_CREATION_DATE,
74 X_CREATED_BY,
75 L.LANGUAGE_CODE,
76 userenv('LANG')
77 from FND_LANGUAGES L
78 where L.INSTALLED_FLAG in ('I', 'B')
79 and not exists
80 (select NULL
81 from AMS_CELLS_ALL_TL T
82 where T.CELL_ID = X_CELL_ID
83 and T.LANGUAGE = L.LANGUAGE_CODE);
84
85 open c;
86 fetch c into X_ROWID;
87 if (c%notfound) then
88 close c;
89 raise no_data_found;
90 end if;
91 close c;
92
93 end INSERT_ROW;
94
95 procedure LOCK_ROW (
96 X_CELL_ID in NUMBER,
97 X_OWNER_ID in NUMBER,
98 X_OBJECT_VERSION_NUMBER in NUMBER,
99 X_CELL_CODE in VARCHAR2,
100 X_MARKET_SEGMENT_FLAG in VARCHAR2,
101 X_ENABLED_FLAG in VARCHAR2,
102 X_ORIGINAL_SIZE in NUMBER,
103 X_PARENT_CELL_ID in NUMBER,
104 X_CELL_NAME in VARCHAR2,
105 X_DESCRIPTION in VARCHAR2
106 ) is
107 cursor c is select
108 OWNER_ID,
109 OBJECT_VERSION_NUMBER,
110 CELL_CODE,
111 MARKET_SEGMENT_FLAG,
112 ENABLED_FLAG,
113 ORIGINAL_SIZE,
114 PARENT_CELL_ID
115 from AMS_CELLS_ALL_B
116 where CELL_ID = X_CELL_ID
117 for update of CELL_ID nowait;
118 recinfo c%rowtype;
119
120 cursor c1 is select
121 CELL_NAME,
122 DESCRIPTION,
123 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
124 from AMS_CELLS_ALL_TL
125 where CELL_ID = X_CELL_ID
126 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
127 for update of CELL_ID nowait;
128 begin
129 open c;
130 fetch c into recinfo;
131 if (c%notfound) then
132 close c;
133 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
134 app_exception.raise_exception;
135 end if;
136 close c;
137 if ( ((recinfo.OWNER_ID = X_OWNER_ID)
138 OR ((recinfo.OWNER_ID is null) AND (X_OWNER_ID is null)))
139 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
140 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
141 AND (recinfo.CELL_CODE = X_CELL_CODE)
142 AND (recinfo.MARKET_SEGMENT_FLAG = X_MARKET_SEGMENT_FLAG)
143 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
144 AND ((recinfo.ORIGINAL_SIZE = X_ORIGINAL_SIZE)
145 OR ((recinfo.ORIGINAL_SIZE is null) AND (X_ORIGINAL_SIZE is null)))
146 AND ((recinfo.PARENT_CELL_ID = X_PARENT_CELL_ID)
147 OR ((recinfo.PARENT_CELL_ID is null) AND (X_PARENT_CELL_ID 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
155 for tlinfo in c1 loop
156 if (tlinfo.BASELANG = 'Y') then
157 if ( (tlinfo.CELL_NAME = X_CELL_NAME)
158 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
159 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
160 ) then
161 null;
162 else
163 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
164 app_exception.raise_exception;
165 end if;
166 end if;
167 end loop;
168 return;
169 end LOCK_ROW;
170
171 procedure UPDATE_ROW (
172 X_CELL_ID in NUMBER,
173 X_OWNER_ID in NUMBER,
174 X_OBJECT_VERSION_NUMBER in NUMBER,
175 X_CELL_CODE in VARCHAR2,
176 X_MARKET_SEGMENT_FLAG in VARCHAR2,
177 X_ENABLED_FLAG in VARCHAR2,
178 X_ORIGINAL_SIZE in NUMBER,
179 X_PARENT_CELL_ID in NUMBER,
180 X_CELL_NAME in VARCHAR2,
181 X_DESCRIPTION in VARCHAR2,
182 X_LAST_UPDATE_DATE in DATE,
183 X_LAST_UPDATED_BY in NUMBER,
184 X_LAST_UPDATE_LOGIN in NUMBER
185 ) is
186 begin
187 update AMS_CELLS_ALL_B set
188 OWNER_ID = X_OWNER_ID,
189 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
190 CELL_CODE = X_CELL_CODE,
191 MARKET_SEGMENT_FLAG = X_MARKET_SEGMENT_FLAG,
192 ENABLED_FLAG = X_ENABLED_FLAG,
193 ORIGINAL_SIZE = X_ORIGINAL_SIZE,
194 PARENT_CELL_ID = X_PARENT_CELL_ID,
195 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
196 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
197 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
198 where CELL_ID = X_CELL_ID;
199
200 if (sql%notfound) then
201 raise no_data_found;
202 end if;
203
204 update AMS_CELLS_ALL_TL set
205 CELL_NAME = X_CELL_NAME,
206 DESCRIPTION = X_DESCRIPTION,
207 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
208 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
209 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
210 SOURCE_LANG = userenv('LANG')
211 where CELL_ID = X_CELL_ID
212 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
213
214 if (sql%notfound) then
215 raise no_data_found;
216 end if;
217 end UPDATE_ROW;
218
219 procedure DELETE_ROW (
220 X_CELL_ID in NUMBER
221 ) is
222 begin
223 delete from AMS_CELLS_ALL_TL
224 where CELL_ID = X_CELL_ID;
225
226 if (sql%notfound) then
227 raise no_data_found;
228 end if;
229
230 delete from AMS_CELLS_ALL_B
231 where CELL_ID = X_CELL_ID;
232
233 if (sql%notfound) then
234 raise no_data_found;
235 end if;
236 end DELETE_ROW;
237
238 procedure ADD_LANGUAGE
239 is
240 begin
241 delete from AMS_CELLS_ALL_TL T
242 where not exists
243 (select NULL
244 from AMS_CELLS_ALL_B B
245 where B.CELL_ID = T.CELL_ID
246 );
247
248 update AMS_CELLS_ALL_TL T set (
249 CELL_NAME,
250 DESCRIPTION
251 ) = (select
252 B.CELL_NAME,
253 B.DESCRIPTION
254 from AMS_CELLS_ALL_TL B
255 where B.CELL_ID = T.CELL_ID
256 and B.LANGUAGE = T.SOURCE_LANG)
257 where (
258 T.CELL_ID,
259 T.LANGUAGE
260 ) in (select
261 SUBT.CELL_ID,
262 SUBT.LANGUAGE
263 from AMS_CELLS_ALL_TL SUBB, AMS_CELLS_ALL_TL SUBT
264 where SUBB.CELL_ID = SUBT.CELL_ID
265 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
266 and (SUBB.CELL_NAME <> SUBT.CELL_NAME
267 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
268 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
269 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
270 ));
271
272 insert into AMS_CELLS_ALL_TL (
273 DESCRIPTION,
274 LAST_UPDATE_LOGIN,
275 CELL_NAME,
276 CELL_ID,
277 LAST_UPDATE_DATE,
278 LAST_UPDATED_BY,
279 CREATION_DATE,
280 CREATED_BY,
281 LANGUAGE,
282 SOURCE_LANG
283 ) select
284 B.DESCRIPTION,
285 B.LAST_UPDATE_LOGIN,
286 B.CELL_NAME,
287 B.CELL_ID,
288 B.LAST_UPDATE_DATE,
289 B.LAST_UPDATED_BY,
290 B.CREATION_DATE,
291 B.CREATED_BY,
292 L.LANGUAGE_CODE,
293 B.SOURCE_LANG
294 from AMS_CELLS_ALL_TL B, FND_LANGUAGES L
295 where L.INSTALLED_FLAG in ('I', 'B')
296 and B.LANGUAGE = userenv('LANG')
297 and not exists
298 (select NULL
299 from AMS_CELLS_ALL_TL T
300 where T.CELL_ID = B.CELL_ID
301 and T.LANGUAGE = L.LANGUAGE_CODE);
302 end ADD_LANGUAGE;
303
304 PROCEDURE TRANSLATE_ROW (
305 x_cell_id IN NUMBER,
306 x_cell_name IN VARCHAR2,
307 x_description IN VARCHAR2,
308 x_owner IN VARCHAR2
309 )
310 IS
311 BEGIN
312 update ams_cells_all_tl set
313 cell_name = NVL (x_cell_name, cell_name),
314 description = nvl(x_description, description),
315 source_lang = userenv('LANG'),
316 last_update_date = sysdate,
317 last_updated_by = decode(x_owner, 'SEED', 1, 0),
318 last_update_login = 0
319 where cell_id = x_cell_id
320 and userenv('LANG') in (language, source_lang);
321 END Translate_Row;
322
323 PROCEDURE LOAD_ROW (
324 X_CELL_ID in NUMBER,
325 X_OWNER_ID in NUMBER,
326 X_CELL_CODE in VARCHAR2,
327 X_MARKET_SEGMENT_FLAG in VARCHAR2,
328 X_ENABLED_FLAG in VARCHAR2,
329 X_ORIGINAL_SIZE in NUMBER,
330 X_PARENT_CELL_ID in NUMBER,
331 X_CELL_NAME in VARCHAR2,
332 X_DESCRIPTION in VARCHAR2,
333 x_owner IN VARCHAR2
334 )
335 IS
336 l_user_id number := 0;
337 l_obj_verno number;
338 l_dummy_char varchar2(1);
339 l_row_id varchar2(100);
340 l_cell_id number;
341
342 CURSOR c_obj_verno IS
343 SELECT object_version_number
344 FROM ams_cells_all_b
345 WHERE cell_id = x_cell_id;
346
347 CURSOR c_chk_cel_exists is
348 SELECT 'x'
349 FROM ams_cells_all_b
350 WHERE cell_id = x_cell_id;
351
352 CURSOR c_get_cel_id is
353 SELECT ams_cells_all_b_s.NEXTVAL
354 FROM DUAL;
355 BEGIN
356 if X_OWNER = 'SEED' then
357 l_user_id := 1;
358 end if;
359
360 OPEN c_chk_cel_exists;
361 FETCH c_chk_cel_exists INTO l_dummy_char;
362 IF c_chk_cel_exists%notfound THEN
363 CLOSE c_chk_cel_exists;
364 OPEN c_get_cel_id;
365 FETCH c_get_cel_id INTO l_cell_id;
366 CLOSE c_get_cel_id;
367 l_obj_verno := 1;
368
369 AMS_Cells_All_PKG.Insert_Row (
370 X_ROWID => l_row_id,
371 X_CELL_ID => l_cell_id,
372 X_OWNER_ID => x_owner_id,
373 X_OBJECT_VERSION_NUMBER => l_obj_verno,
374 X_CELL_CODE => x_cell_code,
375 X_MARKET_SEGMENT_FLAG => x_market_segment_flag,
376 X_ENABLED_FLAG => x_enabled_flag,
377 X_ORIGINAL_SIZE => x_original_size,
378 X_PARENT_CELL_ID => x_parent_cell_id,
379 X_CELL_NAME => x_cell_name,
380 X_DESCRIPTION => x_description,
381 X_CREATION_DATE => SYSDATE,
382 X_CREATED_BY => l_user_id,
383 X_LAST_UPDATE_DATE => SYSDATE,
384 X_LAST_UPDATED_BY => l_user_id,
385 X_LAST_UPDATE_LOGIN => 0
386 );
387 ELSE
388 CLOSE c_chk_cel_exists;
389 OPEN c_obj_verno;
390 FETCH c_obj_verno INTO l_obj_verno;
391 CLOSE c_obj_verno;
392
393 AMS_Cells_All_PKG.Update_Row (
394 X_CELL_ID => x_cell_id,
395 X_OWNER_ID => x_owner_id,
396 X_OBJECT_VERSION_NUMBER => l_obj_verno,
397 X_CELL_CODE => x_cell_code,
398 X_MARKET_SEGMENT_FLAG => x_market_segment_flag,
399 X_ENABLED_FLAG => x_enabled_flag,
400 X_ORIGINAL_SIZE => x_original_size,
401 X_PARENT_CELL_ID => x_parent_cell_id,
402 X_CELL_NAME => x_cell_name,
403 X_DESCRIPTION => x_description,
404 X_LAST_UPDATE_DATE => SYSDATE,
405 X_LAST_UPDATED_BY => l_user_id,
406 X_LAST_UPDATE_LOGIN => 0
407 );
408 END IF;
409 END Load_Row;
410
411 end AMS_CELLS_ALL_PKG;