[Home] [Help]
PACKAGE BODY: APPS.GCS_FEM_HIER_SYNC_PKG
Source
1 PACKAGE BODY GCS_FEM_HIER_SYNC_PKG AS
2 /* $Header: gcs_hier_syncb.pls 120.2 2007/02/19 20:50:38 skamdar ship $ */
3 --
4 -- Private Global Variables
5 --
6 -- The API name
7 g_api CONSTANT VARCHAR2 (30) := 'GCS_FEM_HIER_SYNC_PKG';
8
9 --
10 -- Private Procedures
11 --
12 PROCEDURE synchronize_hierarchy_private(
13 p_hierarchy_id IN NUMBER,
14 p_start_date IN DATE DEFAULT NULL,
15 p_end_date IN DATE DEFAULT NULL,
16 x_errbuf OUT NOCOPY VARCHAR2,
17 x_retcode OUT NOCOPY VARCHAR2
18 ) IS
19
20 l_hierarchy_name VARCHAR2(150);
21 l_folder_name VARCHAR2(150);
22 l_start_date DATE;
23 l_top_entity_display_code VARCHAR2(150);
24
25 --Bugfix 5744068: Adding users to folder automatically
26 l_user_assigned_flag VARCHAR2(1);
27 l_msg_count NUMBER;
28 l_msg_data VARCHAR2(2000);
29 l_return_status VARCHAR2(1);
30
31 BEGIN
32
33 --Bugfix 5744068: Check folder access prior to executing loader
34 BEGIN
35 SELECT 'Y'
36 INTO l_user_assigned_flag
37 FROM fem_user_folders fuf
38 WHERE fuf.folder_id = 1100
39 AND fuf.user_id = FND_GLOBAL.USER_ID;
40 EXCEPTION
41 WHEN NO_DATA_FOUND THEN
42 fem_folders_utl_pkg.assign_user_to_folder(
43 p_api_version => 1.0,
44 p_folder_id => 1100,
45 p_write_flag => 'Y',
46 x_msg_count => l_msg_count,
47 x_msg_data => l_msg_data,
48 x_return_status => l_return_status);
49 END;
50
51 --Bugfix 5744068: Check folder access prior to executing loader
52 BEGIN
53 SELECT 'Y'
54 INTO l_user_assigned_flag
55 FROM fem_user_folders fuf
56 WHERE fuf.folder_id = 1000
57 AND fuf.user_id = FND_GLOBAL.USER_ID;
58 EXCEPTION
59 WHEN NO_DATA_FOUND THEN
60 fem_folders_utl_pkg.assign_user_to_folder(
61 p_api_version => 1.0,
62 p_folder_id => 1000,
63 p_write_flag => 'Y',
64 x_msg_count => l_msg_count,
65 x_msg_data => l_msg_data,
66 x_return_status => l_return_status);
67 END;
68
69 SELECT folder_name
70 INTO l_folder_name
71 FROM fem_folders_vl
72 WHERE folder_id = 1100;
73
74 SELECT hierarchy_name,
75 ghv.start_date,
76 feb.entity_display_code
77 INTO l_hierarchy_name,
78 l_start_date,
79 l_top_entity_display_code
80 FROM gcs_hierarchies_vl ghv,
81 gcs_hierarchies_b ghb,
82 fem_entities_b feb
83 WHERE ghb.hierarchy_id = p_hierarchy_id
84 AND ghb.top_entity_id = feb.entity_id
85 AND ghv.hierarchy_id = p_hierarchy_id;
86
87 IF (p_start_date IS NOT NULL) THEN
88 l_start_date := p_start_date;
89 END IF;
90
91 l_hierarchy_name := substr(l_hierarchy_name, 1, 110) || ' effective since ' || l_start_date;
92
93 -- Step 1: insert hierarchy header info
94
95 INSERT INTO fem_hierarchies_t
96 ( hierarchy_object_name,
97 folder_name,
98 language,
99 dimension_varchar_label,
100 hierarchy_type_code,
101 group_sequence_enforced_code,
102 multi_top_flag,
103 multi_value_set_flag,
104 hierarchy_usage_code,
105 flattened_rows_flag,
106 status,
107 hier_obj_def_display_name,
108 effective_start_date
109 )
110 VALUES
111 ( l_hierarchy_name,
112 l_folder_name,
113 USERENV('LANG'),
114 'ENTITY',
115 'OPEN',
116 'NO_GROUPS',
117 'N',
118 'N',
119 'STANDARD',
120 'N',
121 'LOAD',
122 l_hierarchy_name,
123 l_start_date
124 );
125
126 -- Step 2: insert fem_hier_values_sets_t
127 -- we use 'ENTITY' as value_set_display_code for now;
128
129 INSERT INTO fem_hier_value_sets_t
130 ( hierarchy_object_name,
131 value_set_display_code,
132 language,
133 status
134 )
135 VALUES
136 ( l_hierarchy_name,
137 'ENTITY',
138 USERENV('LANG'),
139 'LOAD'
140 );
141
142 -- Step 3: insert all relationships
143
144 INSERT INTO fem_entities_hier_t
145 ( hierarchy_object_name,
146 hierarchy_obj_def_display_name,
147 parent_display_code,
148 parent_value_set_display_code,
149 child_display_code,
150 child_value_set_display_code,
151 display_order_num,
152 weighting_pct,
153 status,
154 language)
155 SELECT
156 l_hierarchy_name,
157 l_hierarchy_name,
158 fev_p.entity_display_code,
159 'ENTITY',
160 fev_c.entity_display_code,
161 'ENTITY',
162 1,
163 NULL,
164 'LOAD',
165 USERENV('LANG')
166 FROM fem_entities_b fev_p,
167 fem_entities_b fev_c,
168 gcs_cons_relationships gcr
169 WHERE gcr.hierarchy_id = p_hierarchy_id
170 AND gcr.parent_entity_id = fev_p.entity_id
171 AND l_start_date BETWEEN gcr.start_date AND NVL(gcr.end_date, l_start_date)
172 AND gcr.dominant_parent_flag= 'Y'
173 AND gcr.child_entity_id = fev_c.entity_id;
174
175 -- we need to insert a record for the top node
176
177 INSERT INTO fem_entities_hier_t
178 (
179 hierarchy_object_name,
180 hierarchy_obj_def_display_name,
181 parent_display_code,
182 parent_value_set_display_code,
183 child_display_code,
184 child_value_set_display_code,
185 display_order_num,
186 weighting_pct,
187 status,
188 language
189 )
190 VALUES
191 (
192 l_hierarchy_name,
193 l_hierarchy_name,
194 l_top_entity_display_code,
195 'ENTITY',
196 l_top_entity_display_code,
197 'ENTITY',
198 1,
199 NULL,
200 'LOAD',
201 USERENV('LANG')
202 );
203
204 -- Step 4: run loader program
205
206 FEM_HIER_LOADER_PKG.MAIN
207 ( errbuf => x_errbuf,
208 retcode => x_retcode,
209 p_execution_mode => 'S',
210 p_object_definition_id => NULL,
211 p_dimension_varchar_label => 'ENTITY',
212 p_hierarchy_object_name => l_hierarchy_name,
213 p_hier_obj_def_display_name => l_hierarchy_name
214 );
215
216 END synchronize_hierarchy_private;
217
218 --
219 -- Public Procedures
220 --
221
222 PROCEDURE synchronize_hierarchy(
223 p_hierarchy_id IN NUMBER,
224 x_errbuf OUT NOCOPY VARCHAR2,
225 x_retcode OUT NOCOPY VARCHAR2)
226 IS
227 l_hierarchy_name VARCHAR2 (150);
228 l_top_entity_display_code VARCHAR2 (150);
229 l_start_date DATE;
230 l_api_name VARCHAR2 (30) := 'initial_push';
231 l_folder_name VARCHAR2 (150);
232
233 BEGIN
234
235 fnd_file.put_line(fnd_file.log, 'Preparing Hierarchy for Integration with EPF');
236
237 synchronize_hierarchy_private(
238 p_hierarchy_id => p_hierarchy_id,
239 x_errbuf => x_errbuf,
240 x_retcode => x_retcode);
241
242 fnd_file.put_line(fnd_file.log, 'End of Integration with EPF');
243
244 EXCEPTION
245 WHEN OTHERS THEN
246 x_retcode := gcs_utility_pkg.g_ret_sts_error;
247 fnd_file.put_line(fnd_file.log, 'Error occurring during integration with EPF');
248 END synchronize_hierarchy;
249
250 PROCEDURE entity_added(
251 p_hierarchy_id IN NUMBER,
252 p_cons_relationship_id IN NUMBER)
253 IS
254 l_start_date DATE;
255 l_parent_entity_id NUMBER;
256 l_child_entity_id NUMBER;
257 l_entity_type VARCHAR2(1);
258 l_parent_depth_num NUMBER(15);
259 l_entity_type_attr NUMBER :=
260 gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE').attribute_id;
261 l_entity_type_version NUMBER :=
262 gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE').version_id;
263 l_elim_entity_attr NUMBER :=
264 gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ELIMINATION_ENTITY').attribute_id;
265 l_elim_entity_version NUMBER :=
266 gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ELIMINATION_ENTITY').version_id;
267 l_oper_entity_attr NUMBER :=
268 gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-OPERATING_ENTITY').attribute_id;
269 l_oper_entity_version NUMBER :=
270 gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-OPERATING_ENTITY').version_id;
271 l_prior_relation_exists BOOLEAN := FALSE;
272 l_object_definition_id NUMBER(9);
273 l_object_id NUMBER(9);
274 l_hierarchy_name VARCHAR2(150);
275 l_errbuf VARCHAR2(200);
276 l_retcode VARCHAR2(200);
277
278 CURSOR c_prior_relationship_exists (p_hierarchy_id NUMBER,
279 p_child_entity_id NUMBER)
280 IS
281 SELECT gcr.parent_entity_id,
282 gcr.child_entity_id,
283 gcr.start_date,
284 gcr.end_date
285 FROM gcs_cons_relationships gcr
286 WHERE gcr.hierarchy_id = p_hierarchy_id
287 AND gcr.dominant_parent_flag = 'Y'
288 AND gcr.child_entity_id = p_child_entity_id;
289
290 BEGIN
291
292 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
293 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.ENTITY_ADDED.begin', '<<Enter>>');
294 END IF;
295
296 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
297 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.ENTITY_ADDED', 'p_hierarchy_id : ' || p_hierarchy_id);
298 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.ENTITY_ADDED', 'p_cons_relationship_id: ' || p_cons_relationship_id);
299 END IF;
300
301 SELECT gcr.parent_entity_id,
302 gcr.child_entity_id,
303 gcr.start_date,
304 fea.dim_attribute_varchar_member,
305 substr(ghv.hierarchy_name,1,110) || ' effective since'
306 INTO l_parent_entity_id,
307 l_child_entity_id,
308 l_start_date,
309 l_entity_type,
310 l_hierarchy_name
311 FROM gcs_cons_relationships gcr,
312 fem_entities_attr fea,
313 gcs_hierarchies_vl ghv
314 WHERE gcr.cons_relationship_id = p_cons_relationship_id
315 AND gcr.hierarchy_id = ghv.hierarchy_id
316 AND gcr.dominant_parent_flag = 'Y'
317 AND gcr.child_entity_id = fea.entity_id
318 AND fea.attribute_id = l_entity_type_attr
319 AND fea.version_id = l_entity_type_version;
320
321 -- Check if prior relationship exists
322 FOR v_prior_relationship_exists IN c_prior_relationship_exists( p_hierarchy_id,
323 l_child_entity_id) LOOP
324
325 IF (v_prior_relationship_exists.parent_entity_id <> l_parent_entity_id) THEN
326 l_prior_relation_exists := TRUE;
327 EXIT;
328 END IF;
329 END LOOP;
330
331 SELECT fodb.object_definition_id
332 INTO l_object_definition_id
333 FROM fem_object_definition_vl fodb,
334 fem_hierarchies fh
335 WHERE fh.hierarchy_obj_id = fodb.object_id
336 AND fh.dimension_id = 18
337 AND fodb.display_name like l_hierarchy_name || '%'
338 AND l_start_date between fodb.effective_start_date and fodb.effective_end_date
339 AND effective_end_date = TO_DATE('01-01-2500','DD-MM-YYYY');
340
341 IF (l_prior_relation_exists) THEN
342
343 UPDATE fem_object_definition_b
344 SET effective_end_date = l_start_date
345 WHERE object_definition_id = l_object_definition_id;
346
347 synchronize_hierarchy_private(
348 p_hierarchy_id => p_hierarchy_id,
349 p_start_date => l_start_date,
350 x_errbuf => l_errbuf,
351 x_retcode => l_retcode);
352
353 ELSE
354
355 BEGIN
356 SELECT feh.parent_depth_num
357 INTO l_parent_depth_num
358 FROM fem_entities_hier feh
359 WHERE feh.hierarchy_obj_def_id = l_object_definition_id
360 AND feh.parent_id = l_parent_entity_id
361 AND feh.single_depth_flag = 'Y'
362 AND ROWNUM < 2;
363 EXCEPTION
364 WHEN OTHERS THEN
365 SELECT feh.child_depth_num
366 INTO l_parent_depth_num
367 FROM fem_entities_hier feh
368 WHERE feh.hierarchy_obj_def_id = l_object_definition_id
369 AND feh.child_id = l_parent_entity_id
370 AND feh.single_depth_flag = 'Y'
371 AND ROWNUM < 2;
372 END;
373
374 INSERT INTO fem_entities_hier
375 (hierarchy_obj_def_id,
376 parent_depth_num,
377 parent_id,
378 parent_value_set_id,
379 child_depth_num,
380 child_id,
381 child_value_set_id,
382 single_depth_flag,
383 display_order_num,
384 creation_date,
385 created_by,
386 last_update_date,
387 last_updated_by,
388 last_update_login,
389 object_version_number,
390 read_only_flag
391 )
392 VALUES
393 (l_object_definition_id,
394 l_parent_depth_num,
395 l_parent_entity_id,
396 18,
397 l_parent_depth_num + 1,
398 l_child_entity_id,
399 18,
400 'Y',
401 1,
402 sysdate,
403 FND_GLOBAL.USER_ID,
404 sysdate,
405 FND_GLOBAL.USER_ID,
406 FND_GLOBAL.LOGIN_ID,
407 1,
408 'N'
409 );
410
411 IF (l_entity_type = 'C') THEN
412 INSERT INTO fem_entities_hier
413 (hierarchy_obj_def_id,
414 parent_depth_num,
415 parent_id,
416 parent_value_set_id,
417 child_depth_num,
418 child_id,
419 child_value_set_id,
420 single_depth_flag,
421 display_order_num,
422 creation_date,
423 created_by,
424 last_update_date,
425 last_updated_by,
426 last_update_login,
427 object_version_number,
428 read_only_flag
429 )
430 SELECT l_object_definition_id,
431 l_parent_depth_num + 1,
432 l_child_entity_id,
433 18,
434 l_parent_depth_num + 2,
435 fea.dim_attribute_numeric_member,
436 18,
437 'Y',
438 1,
439 sysdate,
440 FND_GLOBAL.USER_ID,
441 sysdate,
442 FND_GLOBAL.USER_ID,
443 FND_GLOBAL.LOGIN_ID,
444 1,
445 'N'
446 FROM fem_entities_attr fea
447 WHERE fea.entity_id = l_child_entity_id
448 AND fea.attribute_id IN (l_oper_entity_attr, l_elim_entity_attr)
449 AND fea.version_id IN (l_oper_entity_version, l_elim_entity_version);
450 END IF;
451 END IF;
452
453 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
454 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.ENTITY_ADDED.end', '<<Exit>>');
455 END IF;
456
457 EXCEPTION
458 WHEN OTHERS THEN
459 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
460 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, g_api || '.ENTITY_ADDED', SQLERRM);
461 END IF;
462 END entity_added;
463
464
465
466 END gcs_fem_hier_sync_pkg;
467