[Home] [Help]
PACKAGE BODY: APPS.FEM_FUNC_DIM_PVT
Source
1 PACKAGE BODY FEM_FUNC_DIM_PVT AS
2 /* $Header: FEMVFUNCDIMB.pls 120.0 2006/05/11 05:49:14 ahyanki noship $ */
3
4 --------------------------------------------------------------------------------
5 -- PRIVATE CONSTANTS
6 --------------------------------------------------------------------------------
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FEM_FUNC_DIM_PVT;';
9
10 --------------------------------------------------------------------------------
11 -- PRIVATE SPECIFICATIONS
12 --------------------------------------------------------------------------------
13
14
15
16
17 PROCEDURE CopyFuncDimRec(
18 p_source_obj_def_id IN NUMBER
19 ,p_target_obj_def_id IN NUMBER
20 ,p_created_by IN NUMBER
21 ,p_creation_date IN DATE
22 );
23
24
25 PROCEDURE DeleteFuncDimRec(
26 p_obj_def_id IN NUMBER
27 );
28
29 --------------------------------------------------------------------------------
30 -- PUBLIC BODIES
31 --------------------------------------------------------------------------------
32 --
33 -- PROCEDURE
34 -- CopyObjectDefinition
35 --
36 -- DESCRIPTION
37 -- Creates all the detail records of a new Functional Dimension Definition(target)
38 -- by copying the detail records of another Functional Dimension Definition (source).
39 --
40 -- IN
41 -- p_source_obj_def_id - Source Object Definition ID.
42 -- p_target_obj_def_id - Target Object Definition ID.
43 -- p_created_by - FND User ID (optional).
44 -- p_creation_date - System Date (optional).
45 --
46 --------------------------------------------------------------------------------
47 PROCEDURE CopyObjectDefinition(
48 p_source_obj_def_id IN NUMBER
49 ,p_target_obj_def_id IN NUMBER
50 ,p_created_by IN NUMBER
51 ,p_creation_date IN DATE
52 )
53 --------------------------------------------------------------------------------
54 IS
55
56 g_api_name CONSTANT VARCHAR2(30) := 'CopyObjectDefinition';
57
58 BEGIN
59
60
61 CopyFuncDimRec(
62 p_source_obj_def_id => p_source_obj_def_id
63 ,p_target_obj_def_id => p_target_obj_def_id
64 ,p_created_by => p_created_by
65 ,p_creation_date => p_creation_date
66
67 );
68
69
70 EXCEPTION
71
72 WHEN OTHERS THEN
73 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, g_api_name);
74 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
75
76 END CopyObjectDefinition;
77
78
79 --
80 -- PROCEDURE
81 -- DeleteObjectDefinition
82 --
83 -- DESCRIPTION
84 -- Deletes all the details records related to a FUnctional Dimension Definition.
85 --
86 -- IN
87 -- p_obj_def_id - Object Definition ID.
88 --
89 --------------------------------------------------------------------------------
90 PROCEDURE DeleteObjectDefinition(
91 p_obj_def_id IN NUMBER
92 )
93 --------------------------------------------------------------------------------
94 IS
95
96 g_api_name CONSTANT VARCHAR2(30) := 'DeleteObjectDefinition';
97
98 BEGIN
99
100 DeleteFuncDimRec(
101 p_obj_def_id => p_obj_def_id
102 );
103
104 EXCEPTION
105
106 WHEN OTHERS THEN
107 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, g_api_name);
108 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
109
110 END DeleteObjectDefinition;
111
112 --
113 -- PROCEDURE
114 -- GetDataColumnDimension
115 --
116 -- DESCRIPTION
117 -- Fetches dimension_id and functional dimension set name for a given
118 -- version id, table name and column name
119 --
120 -- IN
121 -- p_version_id - given version id.
122 -- p_table_name - given table name.
123 -- p_column_name - given column name.
124 -- x_dimension_id - out parameter for dimension id.
125 -- x_func_dim_set_name - out parameter for functional dimension set name.
126
127 --------------------------------------------------------------------------------
128 PROCEDURE GetDataColumnDimension(
129 p_version_id IN NUMBER
130 ,p_table_name IN VARCHAR2
131 ,p_column_name IN VARCHAR2
132 ,x_dimension_id OUT NOCOPY NUMBER
133 ,x_func_dim_set_name OUT NOCOPY VARCHAR2
134 )
135 --------------------------------------------------------------------------------
136 IS
137
138
139
140
141 TYPE FuncDimRec IS RECORD (
142 dimension_id FEM_FUNC_DIM_SETS_B.DIMENSION_ID%TYPE,
143 func_dim_set_name FEM_FUNC_DIM_SETS_TL.FUNC_DIM_SET_NAME%TYPE);
144 l_func_dim_rec FuncDimRec;
145
146 g_api_name CONSTANT VARCHAR2(30) := 'GetDataColumnDimension';
147
148 BEGIN
149
150 Select DIMENSION_ID,FUNC_DIM_SET_NAME into l_func_dim_rec
151 from FEM_FUNC_DIM_SETS_VL,FEM_FUNC_DIM_SET_MAPS
152 where FEM_FUNC_DIM_SETS_VL.FUNC_DIM_SET_ID = FEM_FUNC_DIM_SET_MAPS.FUNC_DIM_SET_ID
153 and FEM_FUNC_DIM_SETS_VL.FUNC_DIM_SET_OBJ_DEF_ID = p_version_id
154 and FEM_FUNC_DIM_SET_MAPS.TABLE_NAME = p_table_name
155 and FEM_FUNC_DIM_SET_MAPS.COLUMN_NAME = p_column_name;
156
157 x_dimension_id := l_func_dim_rec.dimension_id ;
158 x_func_dim_set_name := l_func_dim_rec.func_dim_set_name ;
159
160 EXCEPTION
161
162 --
163 WHEN OTHERS THEN
164
165 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, g_api_name);
166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
167
168 END GetDataColumnDimension;
169
170 --
171 -- PROCEDURE
172 -- UpdateColumnDisplayNames
173 --
174 -- DESCRIPTION
175 -- Updates display name for a column-table combination in FEM_TAB_COLUMNS_VL
176 -- depending upon given set_ids in a collection.
177 -- IN
178 -- p_sets - given set ids.
179 --
180 --------------------------------------------------------------------------------
181 PROCEDURE UpdateColumnDisplayNames(
182 p_api_version IN NUMBER,
183 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
184 p_commit IN VARCHAR2 := FND_API.G_FALSE,
185 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
186 x_return_status OUT NOCOPY VARCHAR2,
187 x_msg_count OUT NOCOPY NUMBER,
188 x_msg_data OUT NOCOPY VARCHAR2,
189 --
190 p_sets IN FEM_FUNC_DIM_SET_TYP
191 )
192 -------------------------------------------------------------------------------
193 IS
194 --
195 l_api_name CONSTANT VARCHAR2(30) := 'UpdateColumnDisplayNames';
196 l_api_version CONSTANT NUMBER := 1.0;
197 --
198 l_set_id NUMBER;
199 l_set_name VARCHAR2(30);
200 l_table_name VARCHAR2(30);
201 l_column_name VARCHAR2(30);
202
203 g_api_name CONSTANT VARCHAR2(30) := 'UpdateColumnDisplayNames';
204
205 CURSOR cur_fetch_table_column_combo (cur_set_id NUMBER) IS
206 Select TABLE_NAME,COLUMN_NAME from
207 FEM_FUNC_DIM_SET_MAPS
208 where FUNC_DIM_SET_ID = cur_set_id;
209
210 BEGIN
211 SAVEPOINT Update_Column_Display_Pvt ;
212
213 IF NOT FND_API.Compatible_API_Call ( l_api_version,
214 p_api_version,
215 l_api_name,
216 G_PKG_NAME )
217 THEN
218 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
219 END IF;
220
221 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
222 FND_MSG_PUB.initialize ;
223 END IF;
224 --
225 x_return_status := FND_API.G_RET_STS_SUCCESS ;
226 --
227
228 FOR i IN p_sets.FIRST..p_sets.LAST LOOP -- Fetching corresponding l_set_name for set_ids in the array p_set_id
229
230 Select FUNC_DIM_SET_NAME INTO l_set_name from
231 FEM_FUNC_DIM_SETS_VL
232 where
233 FUNC_DIM_SET_ID = p_sets(i);
234
235 l_set_id := p_sets(i);
236
237 OPEN cur_fetch_table_column_combo (l_set_id); -- fetching table column combo for given set_id
238 LOOP
239
240 FETCH cur_fetch_table_column_combo INTO l_table_name,l_column_name;
241 EXIT WHEN cur_fetch_table_column_combo%NOTFOUND ;
242
243 UPDATE FEM_TAB_COLUMNS_VL SET DISPLAY_NAME = l_set_name WHERE
244 TABLE_NAME = l_table_name AND
245 COLUMN_NAME = l_column_name ;
246 END LOOP ; -- Ending inner loop
247 CLOSE cur_fetch_table_column_combo ;
248
249 END LOOP ; -- Ending outer loop
250
251 IF FND_API.To_Boolean(p_commit)
252 THEN
253 COMMIT WORK;
254 END IF;
255
256 EXCEPTION
257
258 --
259 WHEN FND_API.G_EXC_ERROR THEN
260
261 ROLLBACK TO Update_Column_Display_Pvt ;
262 x_return_status := FND_API.G_RET_STS_ERROR;
263 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
264 p_data => x_msg_data );
265
266
267 --
268 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
269
270 ROLLBACK TO Update_Column_Display_Pvt ;
271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
272 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
273 p_data => x_msg_data );
274
275 --
276 WHEN DUP_VAL_ON_INDEX THEN
277
278 ROLLBACK TO Update_Column_Display_Pvt ;
279 x_return_status := FND_API.G_RET_STS_ERROR;
280 FND_MESSAGE.SET_NAME('FEM', 'FEM_FUNC_DIM_DUP_DISP_UPD_ERR');
281 FND_MSG_PUB.ADD;
282
283
284 --
285 WHEN OTHERS THEN
286 ROLLBACK TO Update_Column_Display_Pvt ;
287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
288 --
289 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
290 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
291 l_api_name);
292 END if;
293 --
294 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
295 p_data => x_msg_data );
296
297 END UpdateColumnDisplayNames ;
298
299 --------------------------------------------------------------------------------
300 -- PRIVATE BODIES
301 --------------------------------------------------------------------------------
302
303 --
304 -- PROCEDURE
305 -- CopyFuncDimRec
306 --
307 -- DESCRIPTION
308 -- Creates a new Functional Dimension Definition by copying records in the
309 -- FEM_FUNC_DIM_SETS_VL and FEM_FUNC_DIM_SET_MAPS table.
310 --
311 -- IN
312 -- p_source_obj_def_id - Source Object Definition ID.
313 -- p_target_obj_def_id - Target Object Definition ID.
314 -- p_created_by - FND User ID (optional).
315 -- p_creation_date - System Date (optional).
316 --
317 --------------------------------------------------------------------------------
318 PROCEDURE CopyFuncDimRec(
319 p_source_obj_def_id IN NUMBER
320 ,p_target_obj_def_id IN NUMBER
321 ,p_created_by IN NUMBER
322 ,p_creation_date IN DATE
323 )
324 --------------------------------------------------------------------------------
325 IS
326 l_row_id VARCHAR2(500);
327 l_last_updated_by NUMBER;
328 l_last_update_login NUMBER;
329 l_source_table_name VARCHAR2(50);
330 l_old_func_dim_set_id NUMBER;
334
331 l_old_func_dim_set_map_id NUMBER;
332 l_new_func_dim_set_id NUMBER;
333 l_new_func_dim_set_map_id NUMBER;
335 CURSOR cur_func_dim_set_id IS
336 Select FUNC_DIM_SET_ID from
337 FEM_FUNC_DIM_SETS_VL
338 where
339 FUNC_DIM_SET_OBJ_DEF_ID = p_source_obj_def_id;
340
341 CURSOR cur_func_dim_set_map_id (p_old_func_dim_set_id NUMBER) IS
342 Select FUNC_DIM_SET_MAP_ID from
343 FEM_FUNC_DIM_SET_MAPS
344 where FUNC_DIM_SET_ID = p_old_func_dim_set_id;
345
346 BEGIN
347 l_last_updated_by := FND_GLOBAL.USER_ID;
348 l_last_update_login := FND_GLOBAL.LOGIN_ID;
349
350 OPEN cur_func_dim_set_id ;
351
352 LOOP
353
354 FETCH cur_func_dim_set_id INTO l_old_func_dim_set_id;
355 EXIT WHEN cur_func_dim_set_id%NOTFOUND ;
356 select FEM_FUNC_DIM_SET_S.NEXTVAL into l_new_func_dim_set_id from dual;
357 INSERT INTO FEM_FUNC_DIM_SETS_VL(
358 LAST_UPDATE_LOGIN
359 ,LAST_UPDATED_BY
360 ,LAST_UPDATE_DATE
361 ,CREATION_DATE
362 ,FUNC_DIM_SET_OBJ_DEF_ID
363 ,FUNC_DIM_SET_ID
364 ,DIMENSION_ID
365 ,OBJECT_VERSION_NUMBER
366 ,CREATED_BY
367 ,FUNC_DIM_SET_NAME)
368 SELECT
369 FND_GLOBAL.LOGIN_ID
370 ,FND_GLOBAL.USER_ID
371 ,SYSDATE
372 ,NVL(p_creation_date,creation_date)
373 ,p_target_obj_def_id
374 ,l_new_func_dim_set_id
375 ,DIMENSION_ID
376 ,OBJECT_VERSION_NUMBER
377 ,NVL(p_created_by,created_by)
378 ,FUNC_DIM_SET_NAME
379 FROM FEM_FUNC_DIM_SETS_VL
380 WHERE FUNC_DIM_SET_OBJ_DEF_ID = p_source_obj_def_id
381 AND FUNC_DIM_SET_ID = l_old_func_dim_set_id ;
382
383 OPEN cur_func_dim_set_map_id (l_old_func_dim_set_id);
384
385 LOOP
386 FETCH cur_func_dim_set_map_id INTO l_old_func_dim_set_map_id;
387 EXIT WHEN cur_func_dim_set_map_id%NOTFOUND;
388 select FEM_FUNC_DIM_SET_MAP_S.NEXTVAL into l_new_func_dim_set_map_id from dual;
389 INSERT INTO FEM_FUNC_DIM_SET_MAPS(
390 FUNC_DIM_SET_MAP_ID
391 ,FUNC_DIM_SET_ID
392 ,TABLE_NAME
393 ,COLUMN_NAME
394 ,CREATED_BY
395 ,CREATION_DATE
396 ,LAST_UPDATED_BY
397 ,LAST_UPDATE_DATE
398 ,LAST_UPDATE_LOGIN)
399 SELECT
400 l_new_func_dim_set_map_id
401 ,l_new_func_dim_set_id
402 ,TABLE_NAME
403 ,COLUMN_NAME
404 ,NVL(p_created_by,created_by)
405 ,NVL(p_creation_date,creation_date)
406 ,FND_GLOBAL.USER_ID
407 ,SYSDATE
408 ,FND_GLOBAL.LOGIN_ID
409 FROM FEM_FUNC_DIM_SET_MAPS
410 WHERE FUNC_DIM_SET_ID = l_old_func_dim_set_id
411 AND FUNC_DIM_SET_MAP_ID = l_old_func_dim_set_map_id ;
412 END LOOP; -- Ending inner loop
413 CLOSE cur_func_dim_set_map_id ;
414
415 END LOOP; -- Ending outer loop
416 CLOSE cur_func_dim_set_id ;
417
418 END CopyFuncDimRec;
419
420
421 --
422 -- PROCEDURE
423 -- DeleteFuncDimRec
424 --
425 -- DESCRIPTION
426 -- Deletes records related to a Functional Dimension Definition by performing deletes on records
427 -- in the FEM_FUNC_DIM_SET_MAPS and FEM_FUNC_DIM_SETS_VL table.
428 --
429 -- IN
430 -- p_obj_def_id - Object Definition ID.
431 --
432 --------------------------------------------------------------------------------
433 PROCEDURE DeleteFuncDimRec(
434 p_obj_def_id IN NUMBER
435 )
436 --------------------------------------------------------------------------------
437 IS
438
439 l_func_dim_set_id NUMBER;
440
441 CURSOR cur_func_dim_set_id IS
442 SELECT FUNC_DIM_SET_ID FROM FEM_FUNC_DIM_SETS_VL
443 WHERE FUNC_DIM_SET_OBJ_DEF_ID = p_obj_def_id ;
444
445 BEGIN
446 OPEN cur_func_dim_set_id;
447 LOOP
448 FETCH cur_func_dim_set_id INTO l_func_dim_set_id;
449 EXIT WHEN cur_func_dim_set_id%NOTFOUND ;
450
451 DELETE FROM FEM_FUNC_DIM_SET_MAPS
452 WHERE FUNC_DIM_SET_ID = l_func_dim_set_id ;
453
454 END LOOP;
455 CLOSE cur_func_dim_set_id;
456
457 DELETE FROM FEM_FUNC_DIM_SETS_VL
458 WHERE FUNC_DIM_SET_OBJ_DEF_ID = p_obj_def_id;
459
460
461 END DeleteFuncDimRec;
462
463 END FEM_FUNC_DIM_PVT;