[Home] [Help]
PACKAGE BODY: APPS.IMC_THREE_SIXTY_COLS_PKG
Source
1 package body IMC_THREE_SIXTY_COLS_PKG as
2 /* $Header: ARHTSDCB.pls 120.2 2005/11/24 00:48:27 smattegu noship $ */
3 procedure INSERT_ROW (
4 X_COLUMN_ID in out NOCOPY NUMBER,
5 X_QUERY_ID in NUMBER,
6 X_FILTER_QUERY_ID in NUMBER,
7 X_COLUMN_NAME IN VARCHAR2,
8 X_COLUMN_DATA_TYPE in VARCHAR2,
9 X_COLUMN_LENGTH in NUMBER,
10 X_FILTER_FLAG in VARCHAR2,
11 X_RANGE_FILTER_FLAG in VARCHAR2,
12 X_HYPERLINK_FLAG in VARCHAR2,
13 X_DISPLAY_FLAG in VARCHAR2,
14 X_SORT_FLAG in VARCHAR2,
15 X_SECURITY_FUNCTION in VARCHAR2,
16 X_SEQ_NO in NUMBER,
17 X_COLUMN_LABEL in VARCHAR2,
18 X_CREATION_DATE in DATE,
19 X_CREATED_BY in NUMBER,
20 X_LAST_UPDATE_DATE in DATE,
21 X_LAST_UPDATED_BY in NUMBER,
22 X_LAST_UPDATE_LOGIN in NUMBER,
23 X_OBJECT_VERSION_NUMBER in NUMBER)
24 is
25 cursor C2 is select IMC_THREE_SIXTY_COLS_S.nextval from dual;
26
27 begin
28 if ( x_column_id is null) or (x_column_id = fnd_api.g_miss_num) then
29 OPEN C2;
30 FETCH C2 INTO X_COLUMN_ID;
31 if (c2%notfound) then
32 close c2;
33 raise no_data_found;
34 end if;
35 CLOSE C2;
36 end if;
37
38 insert into IMC_THREE_SIXTY_COLS_B (
39 COLUMN_ID,
40 QUERY_ID,
41 FILTER_QUERY_ID,
42 COLUMN_NAME,
43 COLUMN_DATA_TYPE,
44 COLUMN_LENGTH,
45 FILTER_FLAG,
46 RANGE_FILTER_FLAG,
47 HYPERLINK_FLAG,
48 DISPLAY_FLAG,
49 SORT_FLAG,
50 SECURITY_FUNCTION,
51 SEQ_NO,
52 CREATION_DATE,
53 CREATED_BY,
54 LAST_UPDATE_DATE,
55 LAST_UPDATED_BY,
56 LAST_UPDATE_LOGIN,
57 OBJECT_VERSION_NUMBER
58 ) values (
59 X_COLUMN_ID,
60 X_QUERY_ID,
61 X_FILTER_QUERY_ID,
62 X_COLUMN_NAME,
63 X_COLUMN_DATA_TYPE,
64 X_COLUMN_LENGTH,
65 X_FILTER_FLAG,
66 X_RANGE_FILTER_FLAG,
67 X_HYPERLINK_FLAG,
68 X_DISPLAY_FLAG,
69 X_SORT_FLAG,
70 X_SECURITY_FUNCTION,
71 X_SEQ_NO,
72 X_CREATION_DATE,
73 X_CREATED_BY,
74 X_LAST_UPDATE_DATE,
75 X_LAST_UPDATED_BY,
76 X_LAST_UPDATE_LOGIN,
77 1
78 );
79
80 insert into IMC_THREE_SIXTY_COLS_TL (
81 COLUMN_ID,
82 COLUMN_LABEL,
83 CREATED_BY,
84 CREATION_DATE,
85 LAST_UPDATE_LOGIN,
86 LAST_UPDATE_DATE,
87 LAST_UPDATED_BY,
88 LANGUAGE,
89 SOURCE_LANG,
90 OBJECT_VERSION_NUMBER
91 ) select
92 X_COLUMN_ID,
93 X_COLUMN_LABEL,
94 X_CREATED_BY,
95 X_CREATION_DATE,
96 X_LAST_UPDATE_LOGIN,
97 X_LAST_UPDATE_DATE,
98 X_LAST_UPDATED_BY,
99 L.LANGUAGE_CODE,
100 userenv('LANG'),
101 1
102 from FND_LANGUAGES L
103 where L.INSTALLED_FLAG in ('I', 'B')
104 and not exists
105 (select NULL
106 from IMC_THREE_SIXTY_COLS_TL T
107 where T.COLUMN_ID = X_COLUMN_ID
108 and T.LANGUAGE = L.LANGUAGE_CODE);
109
110 end INSERT_ROW;
111
112 procedure LOCK_ROW (
113 X_COLUMN_ID in NUMBER,
114 X_QUERY_ID in NUMBER,
115 X_FILTER_QUERY_ID in NUMBER,
116 X_COLUMN_NAME IN VARCHAR2,
117 X_COLUMN_DATA_TYPE in VARCHAR2,
118 X_COLUMN_LENGTH in NUMBER,
119 X_FILTER_FLAG in VARCHAR2,
120 X_RANGE_FILTER_FLAG in VARCHAR2,
121 X_HYPERLINK_FLAG in VARCHAR2,
122 X_DISPLAY_FLAG in VARCHAR2,
123 X_SORT_FLAG in VARCHAR2,
124 X_SECURITY_FUNCTION in VARCHAR2,
125 X_SEQ_NO in NUMBER,
126 X_OBJECT_VERSION_NUMBER in NUMBER,
127 X_COLUMN_LABEL in VARCHAR2
128 ) is
129 cursor c is select
130 QUERY_ID,
131 FILTER_QUERY_ID,
132 COLUMN_DATA_TYPE,
133 COLUMN_LENGTH,
134 FILTER_FLAG,
135 RANGE_FILTER_FLAG,
136 HYPERLINK_FLAG,
137 DISPLAY_FLAG,
138 SORT_FLAG,
139 SECURITY_FUNCTION,
140 SEQ_NO,
141 OBJECT_VERSION_NUMBER
142 from IMC_THREE_SIXTY_COLS_B
143 where COLUMN_ID = X_COLUMN_ID
144 for update of COLUMN_ID nowait;
145 recinfo c%rowtype;
146
147 cursor c1 is select
148 COLUMN_LABEL,
149 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
150 from IMC_THREE_SIXTY_COLS_TL
151 where COLUMN_ID = X_COLUMN_ID
152 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
153 for update of COLUMN_ID nowait;
154 begin
155 open c;
156 fetch c into recinfo;
157 if (c%notfound) then
158 close c;
159 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
160 app_exception.raise_exception;
161 end if;
162 close c;
163 if ( (recinfo.QUERY_ID = X_QUERY_ID)
164 AND ((recinfo.FILTER_QUERY_ID = X_FILTER_QUERY_ID)
165 OR ((recinfo.FILTER_QUERY_ID is null) AND (X_FILTER_QUERY_ID is null)))
166 AND (recinfo.COLUMN_DATA_TYPE = X_COLUMN_DATA_TYPE)
167 AND (recinfo.COLUMN_LENGTH = X_COLUMN_LENGTH)
168 AND (recinfo.FILTER_FLAG = X_FILTER_FLAG)
169 AND (recinfo.RANGE_FILTER_FLAG = X_RANGE_FILTER_FLAG)
170 AND (recinfo.HYPERLINK_FLAG = X_HYPERLINK_FLAG)
171 AND (recinfo.DISPLAY_FLAG = X_DISPLAY_FLAG)
172 AND (recinfo.SORT_FLAG = X_SORT_FLAG)
173 AND ((recinfo.SECURITY_FUNCTION = X_SECURITY_FUNCTION)
174 OR ((recinfo.SECURITY_FUNCTION is null) AND (X_SECURITY_FUNCTION is null)))
175 AND (recinfo.SEQ_NO = X_SEQ_NO)
176 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
177 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND
178 (X_OBJECT_VERSION_NUMBER is null)))
179 ) then
180 null;
181 else
182 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
183 app_exception.raise_exception;
184 end if;
185
186 for tlinfo in c1 loop
187 if (tlinfo.BASELANG = 'Y') then
188 if ( (tlinfo.COLUMN_LABEL = X_COLUMN_LABEL)
189 ) then
190 null;
191 else
192 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
193 app_exception.raise_exception;
194 end if;
195 end if;
196 end loop;
197 return;
198 end LOCK_ROW;
199
200 procedure UPDATE_ROW (
201 X_COLUMN_ID in NUMBER,
202 X_QUERY_ID in NUMBER,
203 X_FILTER_QUERY_ID in NUMBER,
204 X_COLUMN_NAME IN VARCHAR2,
205 X_COLUMN_DATA_TYPE in VARCHAR2,
206 X_COLUMN_LENGTH in NUMBER,
207 X_FILTER_FLAG in VARCHAR2,
208 X_RANGE_FILTER_FLAG in VARCHAR2,
209 X_HYPERLINK_FLAG in VARCHAR2,
210 X_DISPLAY_FLAG in VARCHAR2,
211 X_SORT_FLAG in VARCHAR2,
212 X_SECURITY_FUNCTION in VARCHAR2,
213 X_SEQ_NO in NUMBER,
214 X_OBJECT_VERSION_NUMBER in NUMBER,
215 X_COLUMN_LABEL in VARCHAR2,
216 X_LAST_UPDATE_DATE in DATE,
217 X_LAST_UPDATED_BY in NUMBER,
218 X_LAST_UPDATE_LOGIN in NUMBER
219 ) is
220 begin
221 update IMC_THREE_SIXTY_COLS_B set
222 QUERY_ID = X_QUERY_ID,
223 FILTER_QUERY_ID = X_FILTER_QUERY_ID,
224 COLUMN_NAME = X_COLUMN_NAME,
225 COLUMN_DATA_TYPE = X_COLUMN_DATA_TYPE,
226 COLUMN_LENGTH = X_COLUMN_LENGTH,
227 FILTER_FLAG = X_FILTER_FLAG,
228 RANGE_FILTER_FLAG = X_RANGE_FILTER_FLAG,
229 HYPERLINK_FLAG = X_HYPERLINK_FLAG,
230 DISPLAY_FLAG = X_DISPLAY_FLAG,
231 SORT_FLAG = X_SORT_FLAG,
232 SECURITY_FUNCTION = X_SECURITY_FUNCTION,
233 SEQ_NO = X_SEQ_NO,
234 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
235 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
236 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
237 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
238 where COLUMN_ID = X_COLUMN_ID;
239
240 if (sql%notfound) then
241 raise no_data_found;
242 end if;
243
244 update IMC_THREE_SIXTY_COLS_TL set
245 COLUMN_LABEL = X_COLUMN_LABEL,
246 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
247 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
248 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
249 SOURCE_LANG = userenv('LANG')
250 where COLUMN_ID = X_COLUMN_ID
251 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
252
253 if (sql%notfound) then
254 raise no_data_found;
255 end if;
256 end UPDATE_ROW;
257
258 procedure DELETE_ROW (
259 X_COLUMN_ID in NUMBER
260 ) is
261 begin
262 delete from IMC_THREE_SIXTY_COLS_TL
263 where COLUMN_ID = X_COLUMN_ID;
264
265 if (sql%notfound) then
266 raise no_data_found;
267 end if;
268
269 delete from IMC_THREE_SIXTY_COLS_B
270 where COLUMN_ID = X_COLUMN_ID;
271
272 if (sql%notfound) then
273 raise no_data_found;
274 end if;
275 end DELETE_ROW;
276
277 procedure ADD_LANGUAGE
278 is
279 begin
280 delete from IMC_THREE_SIXTY_COLS_TL T
281 where not exists
282 (select NULL
283 from IMC_THREE_SIXTY_COLS_B B
284 where B.COLUMN_ID = T.COLUMN_ID
285 );
286
287 update IMC_THREE_SIXTY_COLS_TL T set (
288 COLUMN_LABEL
289 ) = (select
290 B.COLUMN_LABEL
291 from IMC_THREE_SIXTY_COLS_TL B
292 where B.COLUMN_ID = T.COLUMN_ID
293 and B.LANGUAGE = T.SOURCE_LANG)
294 where (
295 T.COLUMN_ID,
296 T.LANGUAGE
297 ) in (select
298 SUBT.COLUMN_ID,
299 SUBT.LANGUAGE
300 from IMC_THREE_SIXTY_COLS_TL SUBB, IMC_THREE_SIXTY_COLS_TL SUBT
301 where SUBB.COLUMN_ID = SUBT.COLUMN_ID
302 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
303 and (SUBB.COLUMN_LABEL <> SUBT.COLUMN_LABEL
304 ));
305
306 insert into IMC_THREE_SIXTY_COLS_TL (
307 COLUMN_ID,
308 COLUMN_LABEL,
309 CREATED_BY,
310 CREATION_DATE,
311 LAST_UPDATE_LOGIN,
312 LAST_UPDATE_DATE,
313 LAST_UPDATED_BY,
314 OBJECT_VERSION_NUMBER,
315 LANGUAGE,
316 SOURCE_LANG
317 ) select
318 B.COLUMN_ID,
319 B.COLUMN_LABEL,
320 B.CREATED_BY,
321 B.CREATION_DATE,
322 B.LAST_UPDATE_LOGIN,
323 B.LAST_UPDATE_DATE,
324 B.LAST_UPDATED_BY,
325 B.OBJECT_VERSION_NUMBER,
326 L.LANGUAGE_CODE,
327 B.SOURCE_LANG
328 from IMC_THREE_SIXTY_COLS_TL B, FND_LANGUAGES L
329 where L.INSTALLED_FLAG in ('I', 'B')
330 and B.LANGUAGE = userenv('LANG')
331 and not exists
332 (select NULL
333 from IMC_THREE_SIXTY_COLS_TL T
334 where T.COLUMN_ID = B.COLUMN_ID
335 and T.LANGUAGE = L.LANGUAGE_CODE);
336 end ADD_LANGUAGE;
337
338 procedure TRANSLATE_ROW (
339 X_COLUMN_ID in NUMBER,
340 X_COLUMN_LABEL in VARCHAR2,
341 X_OWNER in VARCHAR2) IS
342
343 BEGIN
344 -- only update rows that have not been altered by user
345 update IMC_THREE_SIXTY_COLS_TL set
346 COLUMN_LABEL= X_COLUMN_LABEL,
347 source_lang = userenv('LANG'),
348 last_update_date = sysdate,
349 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
350 last_update_login = 0
351 where COLUMN_ID= X_COLUMN_ID
352 and userenv('LANG') in (language, source_lang);
353
354
355 END TRANSLATE_ROW;
356
357 procedure LOAD_ROW (
358 X_COLUMN_ID in NUMBER,
359 X_QUERY_ID in NUMBER,
360 X_FILTER_QUERY_ID in NUMBER,
361 X_COLUMN_NAME IN VARCHAR2,
362 X_COLUMN_DATA_TYPE in VARCHAR2,
363 X_COLUMN_LENGTH in NUMBER,
364 X_FILTER_FLAG in VARCHAR2,
365 X_RANGE_FILTER_FLAG in VARCHAR2,
366 X_HYPERLINK_FLAG in VARCHAR2,
367 X_DISPLAY_FLAG in VARCHAR2,
368 X_SORT_FLAG in VARCHAR2,
369 X_SECURITY_FUNCTION in VARCHAR2,
370 X_SEQ_NO in NUMBER,
371 X_OBJECT_VERSION_NUMBER in NUMBER,
372 X_COLUMN_LABEL in VARCHAR2,
373 X_LAST_UPDATE_DATE in DATE,
374 X_LAST_UPDATED_BY in NUMBER,
375 X_LAST_UPDATE_LOGIN in NUMBER,
376 X_OWNER in VARCHAR2)
377 IS
378
379 BEGIN
380
381 declare
382 user_id number := 0;
383 row_id varchar2(64);
384 L_COLUMN_ID NUMBER := X_COLUMN_ID;
385 L_OBJECT_VERSION_NUMBER number;
386
387 begin
388
389 if (X_OWNER = 'SEED') then
390 user_id := 1;
391 end if;
392
393 L_OBJECT_VERSION_NUMBER := NVL(X_OBJECT_VERSION_NUMBER, 1) + 1;
394
395 IMC_THREE_SIXTY_COLS_PKG.UPDATE_ROW (
396 X_COLUMN_ID => X_COLUMN_ID,
397 X_QUERY_ID => X_QUERY_ID,
398 X_FILTER_QUERY_ID => X_FILTER_QUERY_ID,
399 X_COLUMN_NAME => X_COLUMN_NAME,
400 X_COLUMN_DATA_TYPE => X_COLUMN_DATA_TYPE,
401 X_COLUMN_LENGTH => X_COLUMN_LENGTH,
402 X_FILTER_FLAG => X_FILTER_FLAG,
403 X_RANGE_FILTER_FLAG => X_RANGE_FILTER_FLAG,
404 X_HYPERLINK_FLAG => X_HYPERLINK_FLAG,
405 X_DISPLAY_FLAG => X_DISPLAY_FLAG,
406 X_SORT_FLAG => X_SORT_FLAG,
407 X_SECURITY_FUNCTION => X_SECURITY_FUNCTION,
408 X_SEQ_NO => X_SEQ_NO,
409 X_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER,
410 X_COLUMN_LABEL => X_COLUMN_LABEL,
411 X_LAST_UPDATE_DATE => SYSDATE,
412 X_LAST_UPDATED_BY => user_id,
413 X_LAST_UPDATE_LOGIN => 0);
414
415 exception
416 when NO_DATA_FOUND then
417
418 IMC_THREE_SIXTY_COLS_PKG.INSERT_ROW(
419 X_COLUMN_ID => L_COLUMN_ID,
420 X_QUERY_ID => X_QUERY_ID,
421 X_FILTER_QUERY_ID => X_FILTER_QUERY_ID,
422 X_COLUMN_NAME => X_COLUMN_NAME,
423 X_COLUMN_DATA_TYPE => X_COLUMN_DATA_TYPE,
424 X_COLUMN_LENGTH => X_COLUMN_LENGTH,
425 X_FILTER_FLAG => X_FILTER_FLAG,
426 X_RANGE_FILTER_FLAG => X_RANGE_FILTER_FLAG,
427 X_HYPERLINK_FLAG => X_HYPERLINK_FLAG,
428 X_DISPLAY_FLAG => X_DISPLAY_FLAG,
429 X_SORT_FLAG => X_SORT_FLAG,
430 X_SECURITY_FUNCTION => X_SECURITY_FUNCTION,
431 X_SEQ_NO => X_SEQ_NO,
432 X_COLUMN_LABEL => X_COLUMN_LABEL,
433 X_CREATION_DATE => SYSDATE,
434 X_CREATED_BY => user_id,
435 X_LAST_UPDATE_DATE => SYSDATE,
436 X_LAST_UPDATED_BY => user_id,
437 X_LAST_UPDATE_LOGIN => 0,
438 X_OBJECT_VERSION_NUMBER => 1);
439 end;
440
441 END LOAD_ROW;
442
443
444 end IMC_THREE_SIXTY_COLS_PKG;