[Home] [Help]
PACKAGE BODY: APPS.FEM_COLUMN_REQUIREMNT_PKG
Source
1 package body FEM_COLUMN_REQUIREMNT_PKG as
2 /* $Header: fem_colrqmnt_pkb.plb 120.0 2005/06/06 21:39:13 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_COLUMN_NAME in VARCHAR2,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_ACTIVITY_DIM_COMPONENT_FLAG in VARCHAR2,
8 X_COST_OBJ_DIM_REQUIREMENT_COD in VARCHAR2,
9 X_COST_OBJ_DIM_COMPONENT_FLAG in VARCHAR2,
10 X_DATA_LENGTH in NUMBER,
11 X_DATA_SCALE in NUMBER,
12 X_DATA_PRECISION in NUMBER,
13 X_UOM_COLUMN_NAME in VARCHAR2,
14 X_DIMENSION_ID in NUMBER,
15 X_FEM_DATA_TYPE_CODE in VARCHAR2,
16 X_DATA_TYPE in VARCHAR2,
17 X_PROCESS_KEY_CANDIDATE_FLG in NUMBER,
18 X_PROCESS_KEY_COL_ID in NUMBER,
19 X_DISPLAY_SEQ in NUMBER,
20 X_RESTRICTED_FLAG in VARCHAR2,
21 X_ACTIVITY_DIM_REQUIREMENT_COD in VARCHAR2,
22 X_DISPLAY_NAME in VARCHAR2,
23 X_DESCRIPTION in VARCHAR2,
24 X_CREATION_DATE in DATE,
25 X_CREATED_BY in NUMBER,
26 X_LAST_UPDATE_DATE in DATE,
27 X_LAST_UPDATED_BY in NUMBER,
28 X_LAST_UPDATE_LOGIN in NUMBER
29 ) is
30 cursor C is select ROWID from FEM_COLUMN_REQUIREMNT_B
31 where COLUMN_NAME = X_COLUMN_NAME
32 ;
33 begin
34 insert into FEM_COLUMN_REQUIREMNT_B (
35 OBJECT_VERSION_NUMBER,
36 ACTIVITY_DIM_COMPONENT_FLAG,
37 COST_OBJ_DIM_REQUIREMENT_CODE,
38 COST_OBJ_DIM_COMPONENT_FLAG,
39 DATA_LENGTH,
40 DATA_SCALE,
41 DATA_PRECISION,
42 UOM_COLUMN_NAME,
43 DIMENSION_ID,
44 COLUMN_NAME,
45 FEM_DATA_TYPE_CODE,
46 DATA_TYPE,
47 PROCESS_KEY_CANDIDATE_FLG,
48 PROCESS_KEY_COL_ID,
49 DISPLAY_SEQ,
50 RESTRICTED_FLAG,
51 ACTIVITY_DIM_REQUIREMENT_CODE,
52 CREATION_DATE,
53 CREATED_BY,
54 LAST_UPDATE_DATE,
55 LAST_UPDATED_BY,
56 LAST_UPDATE_LOGIN
57 ) values (
58 X_OBJECT_VERSION_NUMBER,
59 X_ACTIVITY_DIM_COMPONENT_FLAG,
60 X_COST_OBJ_DIM_REQUIREMENT_COD,
61 X_COST_OBJ_DIM_COMPONENT_FLAG,
62 X_DATA_LENGTH,
63 X_DATA_SCALE,
64 X_DATA_PRECISION,
65 X_UOM_COLUMN_NAME,
66 X_DIMENSION_ID,
67 X_COLUMN_NAME,
68 X_FEM_DATA_TYPE_CODE,
69 X_DATA_TYPE,
70 X_PROCESS_KEY_CANDIDATE_FLG,
71 X_PROCESS_KEY_COL_ID,
72 X_DISPLAY_SEQ,
73 X_RESTRICTED_FLAG,
74 X_ACTIVITY_DIM_REQUIREMENT_COD,
75 X_CREATION_DATE,
76 X_CREATED_BY,
77 X_LAST_UPDATE_DATE,
78 X_LAST_UPDATED_BY,
79 X_LAST_UPDATE_LOGIN
80 );
81
82 insert into FEM_COLUMN_REQUIREMNT_TL (
83 COLUMN_NAME,
84 DISPLAY_NAME,
85 DESCRIPTION,
86 CREATION_DATE,
87 CREATED_BY,
88 LAST_UPDATED_BY,
89 LAST_UPDATE_DATE,
90 LAST_UPDATE_LOGIN,
91 LANGUAGE,
92 SOURCE_LANG
93 ) select
94 X_COLUMN_NAME,
95 X_DISPLAY_NAME,
96 X_DESCRIPTION,
97 X_CREATION_DATE,
98 X_CREATED_BY,
99 X_LAST_UPDATED_BY,
100 X_LAST_UPDATE_DATE,
101 X_LAST_UPDATE_LOGIN,
102 L.LANGUAGE_CODE,
103 userenv('LANG')
104 from FND_LANGUAGES L
105 where L.INSTALLED_FLAG in ('I', 'B')
106 and not exists
107 (select NULL
108 from FEM_COLUMN_REQUIREMNT_TL T
109 where T.COLUMN_NAME = X_COLUMN_NAME
110 and T.LANGUAGE = L.LANGUAGE_CODE);
111
112 open c;
113 fetch c into X_ROWID;
114 if (c%notfound) then
115 close c;
116 raise no_data_found;
117 end if;
118 close c;
119
120 end INSERT_ROW;
121
122 procedure LOCK_ROW (
123 X_COLUMN_NAME in VARCHAR2,
124 X_OBJECT_VERSION_NUMBER in NUMBER,
125 X_ACTIVITY_DIM_COMPONENT_FLAG in VARCHAR2,
126 X_COST_OBJ_DIM_REQUIREMENT_COD in VARCHAR2,
127 X_COST_OBJ_DIM_COMPONENT_FLAG in VARCHAR2,
128 X_DATA_LENGTH in NUMBER,
129 X_DATA_SCALE in NUMBER,
130 X_DATA_PRECISION in NUMBER,
131 X_UOM_COLUMN_NAME in VARCHAR2,
132 X_DIMENSION_ID in NUMBER,
133 X_FEM_DATA_TYPE_CODE in VARCHAR2,
134 X_DATA_TYPE in VARCHAR2,
135 X_PROCESS_KEY_CANDIDATE_FLG in NUMBER,
136 X_PROCESS_KEY_COL_ID in NUMBER,
137 X_DISPLAY_SEQ in NUMBER,
138 X_RESTRICTED_FLAG in VARCHAR2,
139 X_ACTIVITY_DIM_REQUIREMENT_COD in VARCHAR2,
140 X_DISPLAY_NAME in VARCHAR2,
141 X_DESCRIPTION in VARCHAR2
142 ) is
143 cursor c is select
144 OBJECT_VERSION_NUMBER,
145 ACTIVITY_DIM_COMPONENT_FLAG,
146 COST_OBJ_DIM_REQUIREMENT_CODE,
147 COST_OBJ_DIM_COMPONENT_FLAG,
148 DATA_LENGTH,
149 DATA_SCALE,
150 DATA_PRECISION,
151 UOM_COLUMN_NAME,
152 DIMENSION_ID,
153 FEM_DATA_TYPE_CODE,
154 DATA_TYPE,
155 PROCESS_KEY_CANDIDATE_FLG,
156 PROCESS_KEY_COL_ID,
157 DISPLAY_SEQ,
158 RESTRICTED_FLAG,
159 ACTIVITY_DIM_REQUIREMENT_CODE
160 from FEM_COLUMN_REQUIREMNT_B
161 where COLUMN_NAME = X_COLUMN_NAME
162 for update of COLUMN_NAME nowait;
163 recinfo c%rowtype;
164
165 cursor c1 is select
166 DISPLAY_NAME,
167 DESCRIPTION,
168 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
169 from FEM_COLUMN_REQUIREMNT_TL
170 where COLUMN_NAME = X_COLUMN_NAME
171 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
172 for update of COLUMN_NAME nowait;
173 begin
174 open c;
175 fetch c into recinfo;
176 if (c%notfound) then
177 close c;
178 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
179 app_exception.raise_exception;
180 end if;
181 close c;
182 if ( (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
183 AND (recinfo.ACTIVITY_DIM_COMPONENT_FLAG = X_ACTIVITY_DIM_COMPONENT_FLAG)
184 AND ((recinfo.COST_OBJ_DIM_REQUIREMENT_CODE = X_COST_OBJ_DIM_REQUIREMENT_COD)
185 OR ((recinfo.COST_OBJ_DIM_REQUIREMENT_CODE is null) AND (X_COST_OBJ_DIM_REQUIREMENT_COD is null)))
186 AND (recinfo.COST_OBJ_DIM_COMPONENT_FLAG = X_COST_OBJ_DIM_COMPONENT_FLAG)
187 AND ((recinfo.DATA_LENGTH = X_DATA_LENGTH)
188 OR ((recinfo.DATA_LENGTH is null) AND (X_DATA_LENGTH is null)))
189 AND ((recinfo.DATA_SCALE = X_DATA_SCALE)
190 OR ((recinfo.DATA_SCALE is null) AND (X_DATA_SCALE is null)))
191 AND ((recinfo.DATA_PRECISION = X_DATA_PRECISION)
192 OR ((recinfo.DATA_PRECISION is null) AND (X_DATA_PRECISION is null)))
193 AND ((recinfo.UOM_COLUMN_NAME = X_UOM_COLUMN_NAME)
194 OR ((recinfo.UOM_COLUMN_NAME is null) AND (X_UOM_COLUMN_NAME is null)))
195 AND ((recinfo.DIMENSION_ID = X_DIMENSION_ID)
196 OR ((recinfo.DIMENSION_ID is null) AND (X_DIMENSION_ID is null)))
197 AND (recinfo.FEM_DATA_TYPE_CODE = X_FEM_DATA_TYPE_CODE)
198 AND (recinfo.DATA_TYPE = X_DATA_TYPE)
199 AND (recinfo.PROCESS_KEY_CANDIDATE_FLG = X_PROCESS_KEY_CANDIDATE_FLG)
200 AND ((recinfo.PROCESS_KEY_COL_ID = X_PROCESS_KEY_COL_ID)
201 OR ((recinfo.PROCESS_KEY_COL_ID is null) AND (X_PROCESS_KEY_COL_ID is null)))
202 AND ((recinfo.DISPLAY_SEQ = X_DISPLAY_SEQ)
203 OR ((recinfo.DISPLAY_SEQ is null) AND (X_DISPLAY_SEQ is null)))
204 AND (recinfo.RESTRICTED_FLAG = X_RESTRICTED_FLAG)
205 AND ((recinfo.ACTIVITY_DIM_REQUIREMENT_CODE = X_ACTIVITY_DIM_REQUIREMENT_COD)
206 OR ((recinfo.ACTIVITY_DIM_REQUIREMENT_CODE is null) AND (X_ACTIVITY_DIM_REQUIREMENT_COD is null)))
207 ) then
208 null;
209 else
210 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
211 app_exception.raise_exception;
212 end if;
213
214 for tlinfo in c1 loop
215 if (tlinfo.BASELANG = 'Y') then
216 if ( (tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
217 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
218 ) then
219 null;
220 else
221 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
222 app_exception.raise_exception;
223 end if;
224 end if;
225 end loop;
226 return;
227 end LOCK_ROW;
228
229 procedure UPDATE_ROW (
230 X_COLUMN_NAME in VARCHAR2,
231 X_OBJECT_VERSION_NUMBER in NUMBER,
232 X_ACTIVITY_DIM_COMPONENT_FLAG in VARCHAR2,
233 X_COST_OBJ_DIM_REQUIREMENT_COD in VARCHAR2,
234 X_COST_OBJ_DIM_COMPONENT_FLAG in VARCHAR2,
235 X_DATA_LENGTH in NUMBER,
236 X_DATA_SCALE in NUMBER,
237 X_DATA_PRECISION in NUMBER,
238 X_UOM_COLUMN_NAME in VARCHAR2,
239 X_DIMENSION_ID in NUMBER,
240 X_FEM_DATA_TYPE_CODE in VARCHAR2,
241 X_DATA_TYPE in VARCHAR2,
242 X_PROCESS_KEY_CANDIDATE_FLG in NUMBER,
243 X_PROCESS_KEY_COL_ID in NUMBER,
244 X_DISPLAY_SEQ in NUMBER,
245 X_RESTRICTED_FLAG in VARCHAR2,
246 X_ACTIVITY_DIM_REQUIREMENT_COD in VARCHAR2,
247 X_DISPLAY_NAME in VARCHAR2,
248 X_DESCRIPTION in VARCHAR2,
249 X_LAST_UPDATE_DATE in DATE,
250 X_LAST_UPDATED_BY in NUMBER,
251 X_LAST_UPDATE_LOGIN in NUMBER
252 ) is
253 begin
254 update FEM_COLUMN_REQUIREMNT_B set
255 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
256 ACTIVITY_DIM_COMPONENT_FLAG = X_ACTIVITY_DIM_COMPONENT_FLAG,
257 COST_OBJ_DIM_REQUIREMENT_CODE = X_COST_OBJ_DIM_REQUIREMENT_COD,
258 COST_OBJ_DIM_COMPONENT_FLAG = X_COST_OBJ_DIM_COMPONENT_FLAG,
259 DATA_LENGTH = X_DATA_LENGTH,
260 DATA_SCALE = X_DATA_SCALE,
261 DATA_PRECISION = X_DATA_PRECISION,
262 UOM_COLUMN_NAME = X_UOM_COLUMN_NAME,
263 DIMENSION_ID = X_DIMENSION_ID,
264 FEM_DATA_TYPE_CODE = X_FEM_DATA_TYPE_CODE,
265 DATA_TYPE = X_DATA_TYPE,
266 PROCESS_KEY_CANDIDATE_FLG = X_PROCESS_KEY_CANDIDATE_FLG,
267 PROCESS_KEY_COL_ID = X_PROCESS_KEY_COL_ID,
268 DISPLAY_SEQ = X_DISPLAY_SEQ,
269 RESTRICTED_FLAG = X_RESTRICTED_FLAG,
270 ACTIVITY_DIM_REQUIREMENT_CODE = X_ACTIVITY_DIM_REQUIREMENT_COD,
271 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
272 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
273 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
274 where COLUMN_NAME = X_COLUMN_NAME;
275
276 if (sql%notfound) then
277 raise no_data_found;
278 end if;
279
280 update FEM_COLUMN_REQUIREMNT_TL set
281 DISPLAY_NAME = X_DISPLAY_NAME,
282 DESCRIPTION = X_DESCRIPTION,
283 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
284 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
285 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
286 SOURCE_LANG = userenv('LANG')
287 where COLUMN_NAME = X_COLUMN_NAME
288 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
289
290 if (sql%notfound) then
291 raise no_data_found;
292 end if;
293 end UPDATE_ROW;
294
295 procedure DELETE_ROW (
296 X_COLUMN_NAME in VARCHAR2
297 ) is
298 begin
299 delete from FEM_COLUMN_REQUIREMNT_TL
300 where COLUMN_NAME = X_COLUMN_NAME;
301
302 if (sql%notfound) then
303 raise no_data_found;
304 end if;
305
306 delete from FEM_COLUMN_REQUIREMNT_B
307 where COLUMN_NAME = X_COLUMN_NAME;
308
309 if (sql%notfound) then
310 raise no_data_found;
311 end if;
312 end DELETE_ROW;
313
314 procedure ADD_LANGUAGE
315 is
316 begin
317 delete from FEM_COLUMN_REQUIREMNT_TL T
318 where not exists
319 (select NULL
320 from FEM_COLUMN_REQUIREMNT_B B
321 where B.COLUMN_NAME = T.COLUMN_NAME
322 );
323
324 update FEM_COLUMN_REQUIREMNT_TL T set (
325 DISPLAY_NAME,
326 DESCRIPTION
327 ) = (select
328 B.DISPLAY_NAME,
329 B.DESCRIPTION
330 from FEM_COLUMN_REQUIREMNT_TL B
331 where B.COLUMN_NAME = T.COLUMN_NAME
332 and B.LANGUAGE = T.SOURCE_LANG)
333 where (
334 T.COLUMN_NAME,
335 T.LANGUAGE
336 ) in (select
337 SUBT.COLUMN_NAME,
338 SUBT.LANGUAGE
339 from FEM_COLUMN_REQUIREMNT_TL SUBB, FEM_COLUMN_REQUIREMNT_TL SUBT
340 where SUBB.COLUMN_NAME = SUBT.COLUMN_NAME
341 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
342 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
343 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
344 ));
345
346 insert into FEM_COLUMN_REQUIREMNT_TL (
347 COLUMN_NAME,
348 DISPLAY_NAME,
349 DESCRIPTION,
350 CREATION_DATE,
351 CREATED_BY,
352 LAST_UPDATED_BY,
353 LAST_UPDATE_DATE,
354 LAST_UPDATE_LOGIN,
355 LANGUAGE,
356 SOURCE_LANG
357 ) select /*+ ORDERED */
358 B.COLUMN_NAME,
359 B.DISPLAY_NAME,
360 B.DESCRIPTION,
361 B.CREATION_DATE,
362 B.CREATED_BY,
363 B.LAST_UPDATED_BY,
364 B.LAST_UPDATE_DATE,
365 B.LAST_UPDATE_LOGIN,
366 L.LANGUAGE_CODE,
367 B.SOURCE_LANG
368 from FEM_COLUMN_REQUIREMNT_TL B, FND_LANGUAGES L
369 where L.INSTALLED_FLAG in ('I', 'B')
370 and B.LANGUAGE = userenv('LANG')
371 and not exists
372 (select NULL
373 from FEM_COLUMN_REQUIREMNT_TL T
374 where T.COLUMN_NAME = B.COLUMN_NAME
375 and T.LANGUAGE = L.LANGUAGE_CODE);
376 end ADD_LANGUAGE;
377 PROCEDURE TRANSLATE_ROW(
378 x_COLUMN_NAME in varchar2,
379 x_owner in varchar2,
380 x_last_update_date in varchar2,
381 x_DISPLAY_NAME in varchar2,
382 x_description in varchar2,
383 x_custom_mode in varchar2) is
384
385 owner_id number;
386 ludate date;
387 row_id varchar2(64);
388 f_luby number; -- entity owner in file
389 f_ludate date; -- entity update date in file
390 db_luby number; -- entity owner in db
391 db_ludate date; -- entity update date in db
392 begin
393
394
395 -- Translate owner to file_last_updated_by
396 f_luby := fnd_load_util.owner_id(x_owner);
397
398 -- Translate char last_update_date to date
399 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
400 begin
401 select LAST_UPDATED_BY, LAST_UPDATE_DATE
402 into db_luby, db_ludate
403 from FEM_COLUMN_REQUIREMNT_TL
404 where COLUMN_NAME = x_COLUMN_NAME
405 and LANGUAGE = userenv('LANG');
406
407 -- Test for customization and version
408 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
409 db_ludate, x_custom_mode)) then
410 -- Update translations for this language
411 update FEM_COLUMN_REQUIREMNT_TL set
412 DISPLAY_NAME = decode(x_DISPLAY_NAME,
413 fnd_load_util.null_value, null, -- Real null
414 null, x_DISPLAY_NAME, -- No change
415 x_DISPLAY_NAME),
416 DESCRIPTION = nvl(x_description, DESCRIPTION),
417 LAST_UPDATE_DATE = f_ludate,
418 LAST_UPDATED_BY = f_luby,
419 LAST_UPDATE_LOGIN = 0,
420 SOURCE_LANG = userenv('LANG')
421 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
422 and COLUMN_NAME = x_COLUMN_NAME;
423 end if;
424 exception
425 when no_data_found then
426 -- Do not insert missing translations, skip this row
427 null;
428 end;
429 end TRANSLATE_ROW;
430
431
432 end FEM_COLUMN_REQUIREMNT_PKG;