[Home] [Help]
PACKAGE BODY: APPS.FEM_FACTOR_TABLES_UTIL_PKG
Source
1 PACKAGE BODY FEM_FACTOR_TABLES_UTIL_PKG AS
2 /* $Header: FEMFACTTABB.pls 120.1 2008/02/20 06:45:26 jcliving noship $ */
3
4 c_module_pkg CONSTANT VARCHAR2(80) := 'fem.plsql.FEM_FACTOR_TABLE_UTIL';
5 g_pkg_name CONSTANT VARCHAR2(30) := 'FEM_FACTOR_TABLE_UTIL';
6
7 FUNCTION is_matching_dimension_leaf (p_object_definition_id IN NUMBER,
8 p_level_num IN NUMBER) RETURN VARCHAR2 IS
9
10
11 l_api_name CONSTANT VARCHAR2(30) := 'is_matching_dimension_leaf';
12 l_api_version CONSTANT NUMBER := 1.0;
13
14 l_count NUMBER := 0;
15 l_leaf_flag VARCHAR2(10) :='Y';
16
17 BEGIN
18
19 FEM_ENGINES_PKG.Tech_Message (
20 p_severity => fnd_log.level_procedure
21 ,p_module => c_module_pkg||'.'||l_api_name
22 ,p_msg_text => 'Begining Function'
23 );
24
25 SELECT count(*) into l_count
26 FROM fem_factor_table_dims
27 WHERE object_definition_id = p_object_definition_id
28 AND dim_usage_code = 'MATCH'
29 AND level_num > p_level_num;
30
31 IF l_count > 0 THEN
32 l_leaf_flag := 'N';
33 END IF;
34
35 RETURN l_leaf_flag;
36
37 END is_matching_dimension_leaf;
38
39
40 PROCEDURE delete_member (p_object_definition_id IN NUMBER,
41 p_row_num IN NUMBER) IS
42
43 CURSOR c_del_mem_list IS
44 SELECT row_num
45 FROM fem_factor_table_fctrs
46 WHERE object_definition_id = p_object_definition_id
47 CONNECT BY prior row_num = parent_row_num
48 START WITH row_num = p_row_num;
49
50 l_api_name CONSTANT VARCHAR2(30) := 'delete_member';
51 l_api_version CONSTANT NUMBER := 1.0;
52
53 l_row_num NUMBER;
54
55
56 BEGIN
57
58 FEM_ENGINES_PKG.Tech_Message (
59 p_severity => fnd_log.level_procedure
60 ,p_module => c_module_pkg||'.'||l_api_name
61 ,p_msg_text => 'Beginning Function'
62 );
63
64 FOR row_num_rec IN c_del_mem_list LOOP
65 DELETE FROM fem_factor_table_fctrs
66 WHERE row_num = row_num_rec.row_num;
67 END LOOP;
68
69 FEM_ENGINES_PKG.Tech_Message (
70 p_severity => fnd_log.level_procedure
71 ,p_module => c_module_pkg||'.'||l_api_name
72 ,p_msg_text => 'Ending Function'
73 );
74
75 EXCEPTION
76 WHEN OTHERS THEN
77 FEM_ENGINES_PKG.Tech_Message (
78 p_severity => fnd_log.level_unexpected
79 ,p_module => c_module_pkg||'.'||l_api_name
80 ,p_msg_text => SQLERRM
81 );
82 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
83 RAISE fnd_api.g_exc_unexpected_error;
84
85 END delete_member;
86
87 --------------------------------------------------------------------------------
88 --
89 -- PROCEDURE
90 -- CopyObjectDefintion
91 --
92 -- DESCRIPTION
93 -- Duplicates all the parameters associated with a Factor table Version(source)
94 -- into the new Factor Table rule Version (target)
95 -- NOTE: It does not copy the Factor table Rule Version record into
96 -- FEM_OBJECT_DEFINITION_VL.
97 -- These records must already exist, this procedure shall only duplicate the
98 -- rule version data in rule specific tables.
99 --
100 -- IN
101 -- p_source_obj_def_id - Source Object Definition ID.
102 -- p_target_obj_def_id - Target Object Definition ID.
103 -- p_created_by - FND User ID.
104 -- p_creation_date - System Date.
105 --
106 --------------------------------------------------------------------------------
107
108 PROCEDURE CopyObjectDefinition (p_source_obj_def_id IN NUMBER,
109 p_target_obj_def_id IN NUMBER,
110 p_created_by IN NUMBER,
111 p_creation_date IN DATE )
112
113 IS
114
115 l_api_name CONSTANT VARCHAR2(30) := 'CopyObjectDefinition';
116 l_api_version CONSTANT NUMBER := 1.0;
117
118 CURSOR c_ft_dims IS
119 SELECT
120 object_definition_id,
121 level_num,
122 dimension_id,
123 dim_usage_code,
124 force_percent_flag,
125 hier_object_id,
126 hier_obj_def_id,
127 hier_group_id,
128 hier_relation_code
129 FROM fem_factor_table_dims
130 WHERE object_definition_id = p_source_obj_def_id;
131
132 CURSOR c_ft_fctrs IS
133 SELECT
134 object_definition_id,
135 row_num,
136 parent_row_num,
137 level_num,
138 dim_member,
139 factor_value
140 FROM fem_factor_table_fctrs
141 WHERE object_definition_id = p_source_obj_def_id;
142
143 BEGIN
144 FEM_ENGINES_PKG.Tech_Message (
145 p_severity => fnd_log.level_procedure
146 ,p_module => c_module_pkg||'.'||l_api_name
147 ,p_msg_text => 'Beginng Function'
148 );
149
150 INSERT INTO fem_factor_tables
151 (
152 object_definition_id,
153 factor_type,
154 created_by,
155 creation_date,
156 last_updated_by,
157 last_update_login,
158 last_update_date,
159 object_version_number
160 )
161 SELECT
162 p_target_obj_def_id AS object_definition_id,
163 factor_type,
164 p_created_by,
165 p_creation_date,
166 fnd_global.user_id,
167 fnd_global.login_id,
168 sysdate,
169 0
170 FROM fem_factor_tables
171 WHERE object_definition_id = p_source_obj_def_id;
172
173 FOR c_dim_rec IN c_ft_dims LOOP
174 INSERT INTO fem_factor_table_dims
175 (
176 object_definition_id,
177 level_num,
178 dimension_id,
179 dim_usage_code,
180 force_percent_flag,
181 hier_object_id,
182 hier_obj_def_id,
183 hier_group_id,
184 hier_relation_code,
185 creation_date,
186 created_by,
187 last_updated_by,
188 last_update_login,
189 last_update_date,
190 object_version_number
191 )
192 VALUES
193 (
194 p_target_obj_def_id,
195 c_dim_rec.level_num,
196 c_dim_rec.dimension_id,
197 c_dim_rec.dim_usage_code,
198 c_dim_rec.force_percent_flag,
199 c_dim_rec.hier_object_id,
200 c_dim_rec.hier_obj_def_id,
201 c_dim_rec.hier_group_id,
202 c_dim_rec.hier_relation_code,
203 p_creation_date,
204 p_created_by,
205 fnd_global.user_id,
206 fnd_global.login_id,
207 sysdate,
208 0
209 );
210 END LOOP;
211
212 FOR c_ft_fctrs_rec IN c_ft_fctrs LOOP
213 INSERT INTO fem_factor_table_fctrs
214 (
215 object_definition_id,
216 row_num,
217 parent_row_num,
218 level_num,
219 dim_member,
220 factor_value,
221 creation_date,
222 created_by,
223 last_updated_by,
224 last_update_login,
225 last_update_date,
226 object_version_number
227 )
228 VALUES
229 (
230 p_target_obj_def_id,
231 c_ft_fctrs_rec.row_num,
232 c_ft_fctrs_rec.parent_row_num,
233 c_ft_fctrs_rec.level_num,
234 c_ft_fctrs_rec.dim_member,
235 c_ft_fctrs_rec.factor_value,
236 p_creation_date,
237 p_created_by,
238 fnd_global.login_id,
239 fnd_global.user_id,
240 sysdate,
241 0
242 );
243 END LOOP;
244
245
246 FEM_ENGINES_PKG.Tech_Message (
247 p_severity => fnd_log.level_procedure
248 ,p_module => c_module_pkg||'.'||l_api_name
249 ,p_msg_text => 'Ending Function'
250 );
251
252 END CopyObjectDefinition;
253
254 PROCEDURE DeleteObjectDefinition ( p_obj_def_id IN NUMBER ) IS
255
256 l_ver_count NUMBER := 0;
257 l_object_id NUMBER;
258 l_api_name CONSTANT VARCHAR2(30) := 'DeleteObjectDefinition';
259 l_api_version CONSTANT NUMBER := 1.0;
260
261 BEGIN
262
263 DELETE FROM fem_factor_table_fctrs
264 WHERE object_definition_id = p_obj_def_id;
265
266 DELETE FROM fem_factor_tables
267 WHERE object_definition_id = p_obj_def_id;
268
269 DELETE FROM fem_factor_table_dims
270 WHERE object_definition_id = p_obj_def_id;
271
272 END DeleteObjectDefinition;
273
274 PROCEDURE VALIDATE_HIERARCHY (x_valid_flag OUT NOCOPY VARCHAR2,p_hier_obj_id IN NUMBER,p_dimension_id IN NUMBER) IS
275 l_valid_flag varchar2(1);
276 BEGIN
277 select nvl((select 'Y' from FEM_HIERARCHIES where hierarchy_obj_id = p_hier_obj_id and dimension_id = p_dimension_id),'N') into l_valid_flag from dual;
278 x_valid_flag := l_valid_flag;
279 END VALIDATE_HIERARCHY;
280
281 PROCEDURE VALIDATE_GROUP (x_valid_flag OUT NOCOPY VARCHAR2,p_hier_obj_id IN NUMBER,p_group_id IN NUMBER) IS
282 l_valid_flag varchar2(1);
283 BEGIN
284 select nvl((select 'Y' from FEM_HIER_DIMENSION_GRPS where hierarchy_obj_id = p_hier_obj_id and dimension_group_id = p_group_id),'N') into l_valid_flag from dual;
285 x_valid_flag := l_valid_flag;
286 END VALIDATE_GROUP;
287
288 PROCEDURE VALIDATE_DIM_MEMBER(x_valid_flag OUT NOCOPY VARCHAR2,p_hier_object_id IN NUMBER,p_group_id IN NUMBER,p_dimension_id IN NUMBER,p_member_id IN NUMBER) IS
289 l_query varchar2(2000);
290 l_main_query varchar2(2000);
291 l_valid_flag varchar2(1);
292 l_member_table_name varchar2(50);
293 l_member_col varchar2(50);
294 BEGIN
295
296 if (p_hier_object_id is null OR length(trim(p_hier_object_id)) = 0) then
297 x_valid_flag := 'Y';
298 return;
299 end if;
300
301 select member_b_table_name,member_col into l_member_table_name,l_member_col from fem_xdim_dimensions where dimension_id = p_dimension_id;
302 l_query := 'select ''Y'' from ' || l_member_table_name ||
303 ' WHERE DIMENSION_GROUP_ID IN (SELECT DIMENSION_GROUP_ID FROM FEM_HIER_DIMENSION_GRPS WHERE HIERARCHY_OBJ_ID = ' || p_hier_object_id ||
304 ') AND DIMENSION_GROUP_ID = ' || p_group_id ||
305 ' AND ' || l_member_col || ' = ' || p_member_id ;
306
307 l_main_query:= 'select nvl((' || l_query || '),''N'') from dual';
308 execute immediate l_main_query into l_valid_flag;
309
310 x_valid_flag := l_valid_flag;
311
312 END VALIDATE_DIM_MEMBER;
313
314 PROCEDURE GET_HIER_OBJ_DEF_ID(x_hier_obj_def_id OUT NOCOPY VARCHAR2,p_hier_obj_id IN VARCHAR2,p_hier_name IN VARCHAR2) IS
315 l_hier_obj_def_id NUMBER;
316 BEGIN
317
318 select nvl((select object_definition_id from fem_object_definition_vl where object_id = p_hier_obj_id and display_name = p_hier_name),-1) into l_hier_obj_def_id from dual;
319 x_hier_obj_def_id := l_hier_obj_def_id;
320
321 END GET_HIER_OBJ_DEF_ID;
322 END fem_factor_tables_util_pkg;