1 package body FEM_COL_POPULATION_TMPLT_PKG as
2 /* $Header: FEMCOLPOPB.pls 120.0 2005/06/06 21:20:56 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_COL_POP_TEMPLT_OBJ_DEF_ID in NUMBER,
6 X_SOURCE_TABLE_NAME in VARCHAR2,
7 X_TARGET_COLUMN_NAME in VARCHAR2,
8 X_TARGET_TABLE_NAME in VARCHAR2,
9 X_DATA_POPULATION_METHOD_CODE in VARCHAR2,
10 X_SOURCE_COLUMN_NAME in VARCHAR2,
11 X_DIMENSION_ID in NUMBER,
12 X_ATTRIBUTE_ID in NUMBER,
13 X_ATTRIBUTE_VERSION_ID in NUMBER,
14 X_AGGREGATION_METHOD in VARCHAR2,
15 X_CONSTANT_NUMERIC_VALUE in NUMBER,
16 X_CONSTANT_ALPHANUMERIC_VALUE in VARCHAR2,
17 X_CONSTANT_DATE_VALUE in DATE,
18 X_OBJECT_VERSION_NUMBER in NUMBER,
19 X_SYSTEM_RESERVED_FLAG in VARCHAR2,
20 X_ENG_PROC_PARAM in VARCHAR2,
21 X_PARAMETER_FLAG in VARCHAR2,
22 X_DESCRIPTION in VARCHAR2,
23 X_CREATION_DATE in DATE,
24 X_CREATED_BY in NUMBER,
25 X_LAST_UPDATE_DATE in DATE,
26 X_LAST_UPDATED_BY in NUMBER,
27 X_LAST_UPDATE_LOGIN in NUMBER
28 ) is
29 cursor C is select ROWID from FEM_COL_POPULATION_TMPLT_B
30 where COL_POP_TEMPLT_OBJ_DEF_ID = X_COL_POP_TEMPLT_OBJ_DEF_ID
31 and SOURCE_TABLE_NAME = X_SOURCE_TABLE_NAME
32 and TARGET_COLUMN_NAME = X_TARGET_COLUMN_NAME
33 and TARGET_TABLE_NAME = X_TARGET_TABLE_NAME
34 ;
35 begin
36 insert into FEM_COL_POPULATION_TMPLT_B (
37 COL_POP_TEMPLT_OBJ_DEF_ID,
38 TARGET_TABLE_NAME,
39 TARGET_COLUMN_NAME,
40 DATA_POPULATION_METHOD_CODE,
41 SOURCE_TABLE_NAME,
42 SOURCE_COLUMN_NAME,
43 DIMENSION_ID,
44 ATTRIBUTE_ID,
45 ATTRIBUTE_VERSION_ID,
46 AGGREGATION_METHOD,
47 CONSTANT_NUMERIC_VALUE,
48 CONSTANT_ALPHANUMERIC_VALUE,
49 CONSTANT_DATE_VALUE,
50 OBJECT_VERSION_NUMBER,
51 SYSTEM_RESERVED_FLAG,
52 ENG_PROC_PARAM,
53 PARAMETER_FLAG,
54 CREATION_DATE,
55 CREATED_BY,
56 LAST_UPDATE_DATE,
57 LAST_UPDATED_BY,
58 LAST_UPDATE_LOGIN
59 ) values (
60 X_COL_POP_TEMPLT_OBJ_DEF_ID,
61 X_TARGET_TABLE_NAME,
62 X_TARGET_COLUMN_NAME,
63 X_DATA_POPULATION_METHOD_CODE,
64 X_SOURCE_TABLE_NAME,
65 X_SOURCE_COLUMN_NAME,
66 X_DIMENSION_ID,
67 X_ATTRIBUTE_ID,
68 X_ATTRIBUTE_VERSION_ID,
69 X_AGGREGATION_METHOD,
70 X_CONSTANT_NUMERIC_VALUE,
71 X_CONSTANT_ALPHANUMERIC_VALUE,
72 X_CONSTANT_DATE_VALUE,
73 X_OBJECT_VERSION_NUMBER,
74 X_SYSTEM_RESERVED_FLAG,
75 X_ENG_PROC_PARAM,
76 X_PARAMETER_FLAG,
77 X_CREATION_DATE,
78 X_CREATED_BY,
79 X_LAST_UPDATE_DATE,
80 X_LAST_UPDATED_BY,
81 X_LAST_UPDATE_LOGIN
82 );
83
84 insert into FEM_COL_POPULATION_TMPLT_TL (
85 COL_POP_TEMPLT_OBJ_DEF_ID,
86 TARGET_TABLE_NAME,
87 TARGET_COLUMN_NAME,
88 SOURCE_TABLE_NAME,
89 CREATED_BY,
90 CREATION_DATE,
91 LAST_UPDATED_BY,
92 LAST_UPDATE_DATE,
93 LAST_UPDATE_LOGIN,
94 DESCRIPTION,
95 LANGUAGE,
96 SOURCE_LANG
97 ) select
98 X_COL_POP_TEMPLT_OBJ_DEF_ID,
99 X_TARGET_TABLE_NAME,
100 X_TARGET_COLUMN_NAME,
101 X_SOURCE_TABLE_NAME,
102 X_CREATED_BY,
103 X_CREATION_DATE,
104 X_LAST_UPDATED_BY,
105 X_LAST_UPDATE_DATE,
106 X_LAST_UPDATE_LOGIN,
107 X_DESCRIPTION,
108 L.LANGUAGE_CODE,
109 userenv('LANG')
110 from FND_LANGUAGES L
111 where L.INSTALLED_FLAG in ('I', 'B')
112 and not exists
113 (select NULL
114 from FEM_COL_POPULATION_TMPLT_TL T
115 where T.COL_POP_TEMPLT_OBJ_DEF_ID = X_COL_POP_TEMPLT_OBJ_DEF_ID
116 and T.SOURCE_TABLE_NAME = X_SOURCE_TABLE_NAME
117 and T.TARGET_COLUMN_NAME = X_TARGET_COLUMN_NAME
118 and T.TARGET_TABLE_NAME = X_TARGET_TABLE_NAME
119 and T.LANGUAGE = L.LANGUAGE_CODE);
120
121 open c;
122 fetch c into X_ROWID;
123 if (c%notfound) then
124 close c;
125 raise no_data_found;
126 end if;
127 close c;
128
129 end INSERT_ROW;
130
131 procedure LOCK_ROW (
132 X_COL_POP_TEMPLT_OBJ_DEF_ID in NUMBER,
133 X_SOURCE_TABLE_NAME in VARCHAR2,
134 X_TARGET_COLUMN_NAME in VARCHAR2,
135 X_TARGET_TABLE_NAME in VARCHAR2,
136 X_DATA_POPULATION_METHOD_CODE in VARCHAR2,
137 X_SOURCE_COLUMN_NAME in VARCHAR2,
138 X_DIMENSION_ID in NUMBER,
139 X_ATTRIBUTE_ID in NUMBER,
140 X_ATTRIBUTE_VERSION_ID in NUMBER,
141 X_AGGREGATION_METHOD in VARCHAR2,
142 X_CONSTANT_NUMERIC_VALUE in NUMBER,
143 X_CONSTANT_ALPHANUMERIC_VALUE in VARCHAR2,
144 X_CONSTANT_DATE_VALUE in DATE,
145 X_OBJECT_VERSION_NUMBER in NUMBER,
146 X_SYSTEM_RESERVED_FLAG in VARCHAR2,
147 X_ENG_PROC_PARAM in VARCHAR2,
148 X_PARAMETER_FLAG in VARCHAR2,
149 X_DESCRIPTION in VARCHAR2
150 ) is
151 cursor c is select
152 DATA_POPULATION_METHOD_CODE,
153 SOURCE_COLUMN_NAME,
154 DIMENSION_ID,
155 ATTRIBUTE_ID,
156 ATTRIBUTE_VERSION_ID,
157 AGGREGATION_METHOD,
158 CONSTANT_NUMERIC_VALUE,
159 CONSTANT_ALPHANUMERIC_VALUE,
160 CONSTANT_DATE_VALUE,
161 OBJECT_VERSION_NUMBER,
162 SYSTEM_RESERVED_FLAG,
163 ENG_PROC_PARAM,
164 PARAMETER_FLAG
165 from FEM_COL_POPULATION_TMPLT_B
166 where COL_POP_TEMPLT_OBJ_DEF_ID = X_COL_POP_TEMPLT_OBJ_DEF_ID
167 and SOURCE_TABLE_NAME = X_SOURCE_TABLE_NAME
168 and TARGET_COLUMN_NAME = X_TARGET_COLUMN_NAME
169 and TARGET_TABLE_NAME = X_TARGET_TABLE_NAME
170 for update of COL_POP_TEMPLT_OBJ_DEF_ID nowait;
171 recinfo c%rowtype;
172
173 cursor c1 is select
174 DESCRIPTION,
175 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
176 from FEM_COL_POPULATION_TMPLT_TL
177 where COL_POP_TEMPLT_OBJ_DEF_ID = X_COL_POP_TEMPLT_OBJ_DEF_ID
178 and SOURCE_TABLE_NAME = X_SOURCE_TABLE_NAME
179 and TARGET_COLUMN_NAME = X_TARGET_COLUMN_NAME
180 and TARGET_TABLE_NAME = X_TARGET_TABLE_NAME
181 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
182 for update of COL_POP_TEMPLT_OBJ_DEF_ID nowait;
183 begin
184 open c;
185 fetch c into recinfo;
186 if (c%notfound) then
187 close c;
188 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
189 app_exception.raise_exception;
190 end if;
191 close c;
192 if ( (recinfo.DATA_POPULATION_METHOD_CODE = X_DATA_POPULATION_METHOD_CODE)
193 AND ((recinfo.SOURCE_COLUMN_NAME = X_SOURCE_COLUMN_NAME)
194 OR ((recinfo.SOURCE_COLUMN_NAME is null) AND (X_SOURCE_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.ATTRIBUTE_ID = X_ATTRIBUTE_ID)
198 OR ((recinfo.ATTRIBUTE_ID is null) AND (X_ATTRIBUTE_ID is null)))
199 AND ((recinfo.ATTRIBUTE_VERSION_ID = X_ATTRIBUTE_VERSION_ID)
200 OR ((recinfo.ATTRIBUTE_VERSION_ID is null) AND (X_ATTRIBUTE_VERSION_ID is null)))
201 AND ((recinfo.AGGREGATION_METHOD = X_AGGREGATION_METHOD)
202 OR ((recinfo.AGGREGATION_METHOD is null) AND (X_AGGREGATION_METHOD is null)))
203 AND ((recinfo.CONSTANT_NUMERIC_VALUE = X_CONSTANT_NUMERIC_VALUE)
204 OR ((recinfo.CONSTANT_NUMERIC_VALUE is null) AND (X_CONSTANT_NUMERIC_VALUE is null)))
205 AND ((recinfo.CONSTANT_ALPHANUMERIC_VALUE = X_CONSTANT_ALPHANUMERIC_VALUE)
206 OR ((recinfo.CONSTANT_ALPHANUMERIC_VALUE is null) AND (X_CONSTANT_ALPHANUMERIC_VALUE is null)))
207 AND ((recinfo.CONSTANT_DATE_VALUE = X_CONSTANT_DATE_VALUE)
208 OR ((recinfo.CONSTANT_DATE_VALUE is null) AND (X_CONSTANT_DATE_VALUE is null)))
209 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
210 AND (recinfo.SYSTEM_RESERVED_FLAG = X_SYSTEM_RESERVED_FLAG)
211 AND ((recinfo.ENG_PROC_PARAM = X_ENG_PROC_PARAM)
212 OR ((recinfo.ENG_PROC_PARAM is null) AND (X_ENG_PROC_PARAM is null)))
213 AND ((recinfo.PARAMETER_FLAG = X_PARAMETER_FLAG)
214 OR ((recinfo.PARAMETER_FLAG is null) AND (X_PARAMETER_FLAG is null)))
215 ) then
216 null;
217 else
218 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
219 app_exception.raise_exception;
220 end if;
221
222 for tlinfo in c1 loop
223 if (tlinfo.BASELANG = 'Y') then
224 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
225 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
226 ) then
227 null;
228 else
229 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
230 app_exception.raise_exception;
231 end if;
232 end if;
233 end loop;
234 return;
235 end LOCK_ROW;
236
237 procedure UPDATE_ROW (
238 X_COL_POP_TEMPLT_OBJ_DEF_ID in NUMBER,
239 X_SOURCE_TABLE_NAME in VARCHAR2,
240 X_TARGET_COLUMN_NAME in VARCHAR2,
241 X_TARGET_TABLE_NAME in VARCHAR2,
242 X_DATA_POPULATION_METHOD_CODE in VARCHAR2,
243 X_SOURCE_COLUMN_NAME in VARCHAR2,
244 X_DIMENSION_ID in NUMBER,
245 X_ATTRIBUTE_ID in NUMBER,
246 X_ATTRIBUTE_VERSION_ID in NUMBER,
247 X_AGGREGATION_METHOD in VARCHAR2,
248 X_CONSTANT_NUMERIC_VALUE in NUMBER,
249 X_CONSTANT_ALPHANUMERIC_VALUE in VARCHAR2,
250 X_CONSTANT_DATE_VALUE in DATE,
251 X_OBJECT_VERSION_NUMBER in NUMBER,
252 X_SYSTEM_RESERVED_FLAG in VARCHAR2,
253 X_ENG_PROC_PARAM in VARCHAR2,
254 X_PARAMETER_FLAG in VARCHAR2,
255 X_DESCRIPTION in VARCHAR2,
256 X_LAST_UPDATE_DATE in DATE,
257 X_LAST_UPDATED_BY in NUMBER,
258 X_LAST_UPDATE_LOGIN in NUMBER
259 ) is
260 begin
261 update FEM_COL_POPULATION_TMPLT_B set
262 DATA_POPULATION_METHOD_CODE = X_DATA_POPULATION_METHOD_CODE,
263 SOURCE_COLUMN_NAME = X_SOURCE_COLUMN_NAME,
264 DIMENSION_ID = X_DIMENSION_ID,
265 ATTRIBUTE_ID = X_ATTRIBUTE_ID,
266 ATTRIBUTE_VERSION_ID = X_ATTRIBUTE_VERSION_ID,
267 AGGREGATION_METHOD = X_AGGREGATION_METHOD,
268 CONSTANT_NUMERIC_VALUE = X_CONSTANT_NUMERIC_VALUE,
269 CONSTANT_ALPHANUMERIC_VALUE = X_CONSTANT_ALPHANUMERIC_VALUE,
270 CONSTANT_DATE_VALUE = X_CONSTANT_DATE_VALUE,
271 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
272 SYSTEM_RESERVED_FLAG = X_SYSTEM_RESERVED_FLAG,
273 ENG_PROC_PARAM = X_ENG_PROC_PARAM,
274 PARAMETER_FLAG = X_PARAMETER_FLAG,
275 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
276 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
277 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
278 where COL_POP_TEMPLT_OBJ_DEF_ID = X_COL_POP_TEMPLT_OBJ_DEF_ID
279 and SOURCE_TABLE_NAME = X_SOURCE_TABLE_NAME
280 and TARGET_COLUMN_NAME = X_TARGET_COLUMN_NAME
281 and TARGET_TABLE_NAME = X_TARGET_TABLE_NAME;
282
283 if (sql%notfound) then
284 raise no_data_found;
285 end if;
286
287 update FEM_COL_POPULATION_TMPLT_TL set
288 DESCRIPTION = X_DESCRIPTION,
289 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
290 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
291 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
292 SOURCE_LANG = userenv('LANG')
293 where COL_POP_TEMPLT_OBJ_DEF_ID = X_COL_POP_TEMPLT_OBJ_DEF_ID
294 and SOURCE_TABLE_NAME = X_SOURCE_TABLE_NAME
295 and TARGET_COLUMN_NAME = X_TARGET_COLUMN_NAME
296 and TARGET_TABLE_NAME = X_TARGET_TABLE_NAME
297 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
298
299 if (sql%notfound) then
300 raise no_data_found;
301 end if;
302 end UPDATE_ROW;
303
304 procedure DELETE_ROW (
305 X_COL_POP_TEMPLT_OBJ_DEF_ID in NUMBER,
306 X_SOURCE_TABLE_NAME in VARCHAR2,
307 X_TARGET_COLUMN_NAME in VARCHAR2,
308 X_TARGET_TABLE_NAME in VARCHAR2
309 ) is
310 begin
311 delete from FEM_COL_POPULATION_TMPLT_TL
312 where COL_POP_TEMPLT_OBJ_DEF_ID = X_COL_POP_TEMPLT_OBJ_DEF_ID
313 and SOURCE_TABLE_NAME = X_SOURCE_TABLE_NAME
314 and TARGET_COLUMN_NAME = X_TARGET_COLUMN_NAME
315 and TARGET_TABLE_NAME = X_TARGET_TABLE_NAME;
316
317 if (sql%notfound) then
318 raise no_data_found;
319 end if;
320
321 delete from FEM_COL_POPULATION_TMPLT_B
322 where COL_POP_TEMPLT_OBJ_DEF_ID = X_COL_POP_TEMPLT_OBJ_DEF_ID
323 and SOURCE_TABLE_NAME = X_SOURCE_TABLE_NAME
324 and TARGET_COLUMN_NAME = X_TARGET_COLUMN_NAME
325 and TARGET_TABLE_NAME = X_TARGET_TABLE_NAME;
326
327 if (sql%notfound) then
328 raise no_data_found;
329 end if;
330 end DELETE_ROW;
331
332 procedure ADD_LANGUAGE
333 is
334 begin
335 delete from FEM_COL_POPULATION_TMPLT_TL T
336 where not exists
337 (select NULL
338 from FEM_COL_POPULATION_TMPLT_B B
339 where B.COL_POP_TEMPLT_OBJ_DEF_ID = T.COL_POP_TEMPLT_OBJ_DEF_ID
340 and B.SOURCE_TABLE_NAME = T.SOURCE_TABLE_NAME
341 and B.TARGET_COLUMN_NAME = T.TARGET_COLUMN_NAME
342 and B.TARGET_TABLE_NAME = T.TARGET_TABLE_NAME
343 );
344
348 B.DESCRIPTION
345 update FEM_COL_POPULATION_TMPLT_TL T set (
346 DESCRIPTION
347 ) = (select
349 from FEM_COL_POPULATION_TMPLT_TL B
350 where B.COL_POP_TEMPLT_OBJ_DEF_ID = T.COL_POP_TEMPLT_OBJ_DEF_ID
351 and B.SOURCE_TABLE_NAME = T.SOURCE_TABLE_NAME
352 and B.TARGET_COLUMN_NAME = T.TARGET_COLUMN_NAME
353 and B.TARGET_TABLE_NAME = T.TARGET_TABLE_NAME
354 and B.LANGUAGE = T.SOURCE_LANG)
355 where (
356 T.COL_POP_TEMPLT_OBJ_DEF_ID,
357 T.SOURCE_TABLE_NAME,
358 T.TARGET_COLUMN_NAME,
359 T.TARGET_TABLE_NAME,
360 T.LANGUAGE
361 ) in (select
362 SUBT.COL_POP_TEMPLT_OBJ_DEF_ID,
363 SUBT.SOURCE_TABLE_NAME,
364 SUBT.TARGET_COLUMN_NAME,
365 SUBT.TARGET_TABLE_NAME,
366 SUBT.LANGUAGE
367 from FEM_COL_POPULATION_TMPLT_TL SUBB, FEM_COL_POPULATION_TMPLT_TL SUBT
368 where SUBB.COL_POP_TEMPLT_OBJ_DEF_ID = SUBT.COL_POP_TEMPLT_OBJ_DEF_ID
369 and SUBB.SOURCE_TABLE_NAME = SUBT.SOURCE_TABLE_NAME
370 and SUBB.TARGET_COLUMN_NAME = SUBT.TARGET_COLUMN_NAME
371 and SUBB.TARGET_TABLE_NAME = SUBT.TARGET_TABLE_NAME
372 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
373 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
374 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
375 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
376 ));
377
378 insert into FEM_COL_POPULATION_TMPLT_TL (
379 COL_POP_TEMPLT_OBJ_DEF_ID,
380 TARGET_TABLE_NAME,
381 TARGET_COLUMN_NAME,
382 SOURCE_TABLE_NAME,
383 CREATED_BY,
384 CREATION_DATE,
385 LAST_UPDATED_BY,
386 LAST_UPDATE_DATE,
387 LAST_UPDATE_LOGIN,
388 DESCRIPTION,
389 LANGUAGE,
390 SOURCE_LANG
391 ) select /*+ ORDERED */
392 B.COL_POP_TEMPLT_OBJ_DEF_ID,
393 B.TARGET_TABLE_NAME,
394 B.TARGET_COLUMN_NAME,
395 B.SOURCE_TABLE_NAME,
396 B.CREATED_BY,
397 B.CREATION_DATE,
398 B.LAST_UPDATED_BY,
399 B.LAST_UPDATE_DATE,
400 B.LAST_UPDATE_LOGIN,
401 B.DESCRIPTION,
402 L.LANGUAGE_CODE,
403 B.SOURCE_LANG
404 from FEM_COL_POPULATION_TMPLT_TL B, FND_LANGUAGES L
405 where L.INSTALLED_FLAG in ('I', 'B')
406 and B.LANGUAGE = userenv('LANG')
407 and not exists
408 (select NULL
409 from FEM_COL_POPULATION_TMPLT_TL T
410 where T.COL_POP_TEMPLT_OBJ_DEF_ID = B.COL_POP_TEMPLT_OBJ_DEF_ID
411 and T.SOURCE_TABLE_NAME = B.SOURCE_TABLE_NAME
412 and T.TARGET_COLUMN_NAME = B.TARGET_COLUMN_NAME
413 and T.TARGET_TABLE_NAME = B.TARGET_TABLE_NAME
414 and T.LANGUAGE = L.LANGUAGE_CODE);
415 end ADD_LANGUAGE;
416
417 end FEM_COL_POPULATION_TMPLT_PKG;
[Home] [Help]