[Home] [Help]
PACKAGE BODY: APPS.GCS_IC_LOB_ELIM_PARENT_PKG
Source
1 PACKAGE BODY GCS_IC_LOB_ELIM_PARENT_PKG AS
2 /* $Header: gcsiclbb.pls 120.3 2007/02/01 21:55:10 skamdar noship $*/
3
4 --
5 -- Package
6 -- CREATE_ELIM_PARENT_LOB
7 -- Purpose
8 -- Creates the elimination parent elimination line of business.
9 -- History
10 -- 17-AUG-04 Srini Pala Created
11 --
12
13 -- Public variables
14
15 g_pkg_name VARCHAR2(80) := 'gcs.plsql.GCS_IC_LOB_ELIM_PARENT_PKG';
16 g_nl CONSTANT VARCHAR2(1) := fnd_global.newline;
17 g_no_rows NUMBER := 0;
18 g_fnd_user_id NUMBER := fnd_global.user_id;
19 g_fnd_login_id NUMBER := fnd_global.login_id;
20
21
22 -- Bug fix : 5257413, removed the parameter values_set_id
23
24 PROCEDURE CREATE_ELIM_PARENT_LOB (p_errbuf OUT NOCOPY VARCHAR2,
25 p_retcode OUT NOCOPY VARCHAR2,
26 p_hierarchy_name IN VARCHAR2,
27 p_hierarchy_obj_id IN VARCHAR2,
28 p_version_name IN VARCHAR2
29 ) IS
30
31 l_api_name VARCHAR2(50) := 'CREATE_ELIM_PARENT_LOB';
32 l_version_id NUMBER ;
33 l_vs_display_code VARCHAR2(150);
34 req_id NUMBER;
35 l_hier_obj_name VARCHAR2(150);
36 l_hier_obj_def_dis_name VARCHAR2(150);
37 l_submit_req NUMBER :=0;
38 l_existing_hier_dis_name VARCHAR2(150);
39 -- bug fix : 5257413
40 l_hierarchy_obj_def_id NUMBER;
41 l_attribute_id NUMBER;
42
43 l_value_set_id NUMBER;
44 l_elims_const CONSTANT VARCHAR2(20) := ' Eliminations';
45
46
47 NO_USER_DIM1_PROC_KEY EXCEPTION;
48 CONSOLIDATION_GVSC_UNDEFINED EXCEPTION;
49
50 x_return_status varchar2(100);
51 x_msg_count number;
52 x_msg_data varchar2(4000);
53
54 --Bugfix 5851171: Removed check to only retrieve enabled parent values, as elim lobs are required for even disabled parents
55 CURSOR c_additional_lobs IS
56 SELECT DISTINCT
57 fudb.value_set_id,
58 fudb.user_dim1_display_code || l_elims_const elim_lob_display_code,
59 fudb.enabled_flag,
60 fudb.personal_flag,
61 fudb.read_only_flag,
62 fudb.object_version_number
63 FROM fem_user_dim1_b fudb,
64 fem_user_dim1_hier fudh
65 WHERE fudh.hierarchy_obj_def_id = l_hierarchy_obj_def_id
66 AND fudh.parent_id <> fudh.child_id
67 AND fudh.parent_value_set_id = fudb.value_set_id
68 AND fudb.user_dim1_id = fudh.parent_id
69 AND NOT EXISTS
70 (SELECT 'X'
71 FROM fem_user_dim1_attr fuda
72 WHERE fuda.value_set_id = fudb.value_set_id
73 AND fuda.user_dim1_id = fudb.user_dim1_id
74 AND fuda.attribute_id = l_attribute_id
75 AND fuda.version_id = l_version_id);
76
77 BEGIN
78
79 -- Bugfix 5257413: Added some logging information
80 fnd_file.put_line(fnd_file.log, '.............Beginning of Concurrent Program.............');
81 fnd_file.put_line(fnd_file.log, '.............Beginning of Parameter Listing.............');
82 fnd_file.put_line(fnd_file.log, 'Hierarchy Identifier: ' || p_hierarchy_obj_id);
83 fnd_file.put_line(fnd_file.log, 'Version Name : ' || p_version_name);
84 fnd_file.put_line(fnd_file.log, 'Hierarchy Name : ' || p_hierarchy_name);
85 fnd_file.put_line(fnd_file.log, '.............End of Parameter Listing.............');
86
87 -- Bug fix : 5257413, Start
88 -- Checking if the User Dimension-01 is a part of the Processing Key
89 BEGIN
90 --SKAMDAR: Ensure user dimension 1 is enabled for processing
91 --Prior check was made against fem_tab_column_prop but we can go straight to utility package
92 IF (gcs_utility_pkg.g_gcs_dimension_info('USER_DIM1_ID').required_for_gcs = 'N') THEN
93 RAISE NO_USER_DIM1_PROC_KEY;
94 END IF;
95 EXCEPTION
96 WHEN NO_DATA_FOUND THEN
97 RAISE NO_USER_DIM1_PROC_KEY;
98 END;
99
100 BEGIN
101
102 SELECT attribute_id
103 INTO l_attribute_id
104 FROM fem_dim_attributes_b
105 WHERE dimension_id = 19
106 AND attribute_varchar_label = 'ELIMINATION_LOB';
107
108 EXCEPTION
109 WHEN NO_DATA_FOUND THEN
110 FEM_DIM_ATTRIBUTES_UTIL_PKG.CREATE_ATTRIBUTE(
111 x_attribute_id => l_attribute_id
112 ,x_msg_count => x_msg_count
113 ,x_msg_data => x_msg_data
114 ,x_return_status => x_return_status
115 ,p_api_version => 1.0
116 ,p_commit => FND_API.G_TRUE
117 ,p_attr_varchar_label => 'ELIMINATION_LOB'
118 ,p_attr_name => 'Elimination Line of Business'
119 ,p_attr_description => 'Elimination Line of Business'
120 ,p_dimension_varchar_label => 'USER_DIM1'
121 ,p_allow_mult_versions_flag => 'N'
122 ,p_queryable_for_reporting_flag => 'Y'
123 ,p_use_inheritance_flag => 'N'
124 ,p_attr_order_type_code => 'NOMINAL'
125 ,p_allow_mult_assign_flag => 'N'
126 ,p_personal_flag => 'N'
127 ,p_attr_data_type_code => 'DIMENSION'
128 ,p_attr_dimension_varchar_label => 'USER_DIM1'
129 ,p_version_display_code => 'Default'
130 ,p_version_name => 'Default'
131 ,p_version_description => 'Default Verison' );
132
133
134 END;
135
136 -- SKAMDAR: Retrieve Version Value for Attribute
137 SELECT version_id
138 INTO l_version_id
139 FROM fem_dim_attr_versions_b
140 WHERE attribute_id = l_attribute_id
141 AND default_version_flag = 'Y';
142
143 fnd_file.put_line(fnd_file.log, '.............Beginning of Attribute Information.............');
144 fnd_file.put_line(fnd_file.log, 'Attribute Identifier: ' || l_attribute_id);
145 fnd_file.put_line(fnd_file.log, 'Version Identifier : ' || l_version_id);
146 fnd_file.put_line(fnd_file.log, '.............End of Attribute Information.............');
147
148 --SKAMDAR: Modifying select statement to use version_name passed into the concurrent program
149 SELECT object_definition_id
150 INTO l_hierarchy_obj_def_id
151 FROM fem_object_definition_vl
152 WHERE object_id = p_hierarchy_obj_id
153 AND display_name = p_version_name;
154
155 -- Retreiving the value_set_id of user dimension-1
156 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
157 fnd_log.STRING (fnd_log.level_procedure,
158 g_pkg_name || '.' || l_api_name,
159 'Retreive the value_set_id of user dimension-1' );
160 END IF;
161
162 --SKAMDAR: Information can be retrieved from the utility package rather than executing a select against fem tables
163 BEGIN
164
165 l_value_set_id := gcs_utility_pkg.g_gcs_dimension_info('USER_DIM1_ID').associated_value_set_id;
166
167 SELECT value_set_display_code
168 INTO l_vs_display_code
169 FROM fem_value_sets_b fvsb
170 WHERE fvsb.value_set_id = l_value_set_id;
171
172 EXCEPTION
173 WHEN NO_DATA_FOUND THEN
174 --If no data is found then the consolidation chart of accounts has not been assigned
175 RAISE CONSOLIDATION_GVSC_UNDEFINED;
176 END;
177
178 fnd_file.put_line(fnd_file.log, '.............Beginning of Hierarchy Information.............');
179 fnd_file.put_line(fnd_file.log, 'Hierarchy Object Def Identifier: ' || l_hierarchy_obj_def_id);
180 fnd_file.put_line(fnd_file.log, 'Value Set Identifier : ' || l_value_set_id);
181 fnd_file.put_line(fnd_file.log, 'Value Set Display Code : ' || l_vs_display_code);
182 fnd_file.put_line(fnd_file.log, '.............End of Hierarchy Information.............') ;
183
184
185 fnd_file.put_line(fnd_file.log, '.............Beginning Generation of Eliminations LOBs (_B Records).............');
186 g_no_rows := 0;
187 --SKAMDAR: Can change this into bulk statement in future.
188 FOR v_additional_lobs IN c_additional_lobs LOOP
189 INSERT INTO fem_user_dim1_b
190 (user_dim1_id,
191 value_set_id,
192 user_dim1_display_code,
193 enabled_flag,
194 personal_flag,
195 creation_date,
196 created_by,
197 last_updated_by,
198 last_update_date,
199 last_update_login,
200 read_only_flag,
201 object_version_number,
202 dimension_group_id)
203 SELECT fnd_flex_values_s.nextval,
204 v_additional_lobs.value_set_id,
205 v_additional_lobs.elim_lob_display_code,
206 v_additional_lobs.enabled_flag,
207 v_additional_lobs.personal_flag,
208 sysdate,
209 g_fnd_user_id,
210 g_fnd_user_id,
211 sysdate,
212 g_fnd_login_id,
213 v_additional_lobs.read_only_flag,
214 v_additional_lobs.object_version_number,
215 NULL
216 FROM DUAL;
217
218 g_no_rows := g_no_rows + 1;
219
220 END LOOP;
221
222 fnd_file.put_line(fnd_file.log, '# of rows: ' || g_no_rows);
223 fnd_file.put_line(fnd_file.log, '.............Completed Generation of Eliminations LOBs (_B Records).............');
224
225 fnd_file.put_line(fnd_file.log, '.............Beginning Generation of Eliminations LOBs (_TL Records).............');
226
227 INSERT INTO fem_user_dim1_tl
228 (user_dim1_id,
229 value_set_id,
230 language,
231 source_lang,
232 user_dim1_name,
233 description,
234 creation_date,
235 created_by,
236 last_updated_by,
237 last_update_date,
238 last_update_login)
239 SELECT fudb.user_dim1_id,
240 fudb.value_set_id,
241 fl.language_code,
242 userenv('LANG'),
243 fudb.user_dim1_display_code,
244 fudb.user_dim1_display_code,
245 sysdate,
246 g_fnd_user_id,
247 g_fnd_user_id,
248 sysdate,
249 g_fnd_login_id
250 FROM fem_user_dim1_b fudb,
251 fnd_languages fl
252 WHERE fl.installed_flag in ('I', 'B')
253 AND fudb.user_dim1_display_code like '%'||l_elims_const
254 AND NOT EXISTS
255 (SELECT 'X'
256 FROM fem_user_dim1_tl fudt
257 WHERE fudt.user_dim1_id = fudb.user_dim1_id
258 AND fudt.language = fl.language_code);
259
260 g_no_rows := NVL(SQL%ROWCOUNT,0);
261
262 fnd_file.put_line(fnd_file.log, '# of rows: ' || g_no_rows);
263 fnd_file.put_line(fnd_file.log, '.............Completed Generation of Eliminations LOBs (_TL Records).............');
264
265 fnd_file.put_line(fnd_file.log, '.............Beginning Generation of Eliminations LOBs (_ATTR Records).............');
266
267 INSERT INTO fem_user_dim1_attr
268 (attribute_id,
269 version_id,
270 user_dim1_id,
271 value_set_id,
272 creation_date,
273 created_by,
274 last_updated_by,
275 last_update_date,
276 last_update_login,
277 object_version_number,
278 aw_snapshot_flag,
279 dim_attribute_numeric_member,
280 dim_attribute_varchar_member
281 )
282 SELECT fdab.attribute_id,
283 fdavb.version_id,
284 fudb.user_dim1_id,
285 fudb.value_set_id,
286 sysdate,
287 g_fnd_user_id,
288 g_fnd_user_id,
289 sysdate,
290 g_fnd_login_id,
291 1,
292 'N',
293 DECODE(fdab.attribute_varchar_label, 'SOURCE_SYSTEM_CODE', fdab.default_assignment, NULL),
294 DECODE(fdab.attribute_varchar_label, 'RECON_LEAF_NODE_FLAG', fdab.default_assignment, NULL)
295 FROM fem_dim_attributes_b fdab,
296 fem_dim_attr_versions_b fdavb,
297 fem_user_dim1_b fudb
298 WHERE fudb.value_set_id = l_value_set_id
299 AND fudb.user_dim1_display_code LIKE '%'||l_elims_const
300 AND fdab.dimension_id = 19
301 AND fdab.attribute_id = fdavb.attribute_id
302 AND fdavb.default_version_flag = 'Y'
303 AND fdab.attribute_varchar_label IN ('SOURCE_SYSTEM_CODE', 'RECON_LEAF_NODE_FLAG')
304 AND NOT EXISTS (SELECT 'X'
305 FROM fem_user_dim1_attr fuda
306 WHERE fuda.attribute_id = fdavb.attribute_id
307 AND fuda.version_id = fdavb.version_id
308 AND fuda.user_dim1_id = fudb.user_dim1_id
309 AND fuda.value_set_id = fudb.value_set_id);
310
311 g_no_rows := NVL(SQL%ROWCOUNT,0);
312
313 fnd_file.put_line(fnd_file.log, '# of rows: ' || g_no_rows);
314 fnd_file.put_line(fnd_file.log, '.............Completed Generation of Eliminations LOBs (_ATTR Records).............');
315
316 fnd_file.put_line(fnd_file.log, '.............Beginning Generation of Elimination Attributes.............');
317 INSERT INTO fem_user_dim1_attr(
318 attribute_id,
319 version_id,
320 user_dim1_id,
321 value_set_id,
322 dim_attribute_numeric_member,
323 creation_date,
324 created_by,
325 last_updated_by,
326 last_update_date,
327 last_update_login,
328 object_version_number,
329 aw_snapshot_flag)
330 SELECT DISTINCT
331 l_attribute_id,
332 l_version_id,
333 fudb.user_dim1_id,
334 fudb.value_set_id,
335 fudb1.user_dim1_id,
336 sysdate,
337 g_fnd_user_id,
338 g_fnd_user_id,
339 sysdate,
340 g_fnd_login_id ,
341 1,
342 'N'
343 FROM fem_user_dim1_hier fudh,
344 fem_user_dim1_b fudb,
345 fem_user_dim1_b fudb1
346 WHERE fudh.hierarchy_obj_def_id = l_hierarchy_obj_def_id
347 AND fudh.parent_id <> fudh.child_id
348 AND fudh.parent_id = fudb.user_dim1_id
349 AND fudh.parent_value_set_id = fudb.value_set_id
350 AND fudb.value_set_id = fudb1.value_set_id
351 AND fudb.user_dim1_display_code||l_elims_const = fudb1.user_dim1_display_code
352 AND NOT EXISTS
353 (SELECT 'X'
354 FROM fem_user_dim1_attr fuda1
355 WHERE fuda1.attribute_id = l_attribute_id
356 AND fuda1.version_id = l_version_id
357 AND fuda1.user_dim1_id = fudb.user_dim1_id
358 AND fuda1.value_set_id = fudb.value_set_id);
359
360 g_no_rows := NVL(SQL%ROWCOUNT,0);
361
362 fnd_file.put_line(fnd_file.log, '# of rows: ' || g_no_rows);
363 fnd_file.put_line(fnd_file.log, '.............Completed Generation of Elimination Attributes .............');
364
365 INSERT INTO fem_hierarchies_t
366 (hierarchy_object_name,
367 folder_name,
368 language,
369 dimension_varchar_label,
370 hierarchy_type_code,
371 group_sequence_enforced_code,
372 multi_top_flag,
373 multi_value_set_flag,
374 hierarchy_usage_code,
375 flattened_rows_flag,
376 status,
377 hier_obj_def_display_name ,
378 effective_start_date,
379 effective_end_date)
380 SELECT p_hierarchy_name,
381 'Default',
382 USERENV('LANG'),
383 'USER_DIM1',
384 'OPEN',
385 'NO_GROUPS',
386 fh.multi_top_flag,
387 fh.multi_value_set_flag,
388 'STANDARD',
389 'Y',
390 'LOAD',
391 p_version_name,
392 fodb.effective_start_date,
393 fodb.effective_end_date
394 FROM fem_object_definition_b fodb,
395 fem_hierarchies fh
396 WHERE fodb.object_definition_id = l_hierarchy_obj_def_id
397 AND fodb.object_id = fh.hierarchy_obj_id;
398
399 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
400 fnd_log.STRING(fnd_log.level_procedure,
401 g_pkg_name || '.' || l_api_name,
402 'Insert new hierarchy value set '
403 ||' into FEM_HIER_VALUE_SETS_T ');
404 END IF;
405
406 INSERT INTO fem_hier_value_sets_t
407 (hierarchy_object_name,
408 value_set_display_code,
409 language,
410 status)
411 VALUES (p_hierarchy_name,
412 l_vs_display_code,
413 USERENV('LANG'),
414 'LOAD'
415 );
416
417 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
418 fnd_log.STRING(fnd_log.level_procedure,
419 g_pkg_name || '.' || l_api_name,
420 'Insert existing hierarchy members '
421 ||' into fem_user_dim1_hier_T ');
422 END IF;
423
424 INSERT INTO fem_user_dim1_hier_t
425 (hierarchy_object_name,
426 hierarchy_obj_def_display_name,
427 parent_display_code,
428 parent_value_set_display_code,
429 child_display_code,
430 child_value_set_display_code,
431 display_order_num,
432 status,
433 language)
434 SELECT p_hierarchy_name,
435 p_version_name,
436 parent.user_dim1_display_code,
437 l_vs_display_code,
438 child.user_dim1_display_code,
439 l_vs_display_code,
440 1,
441 'LOAD',
442 USERENV('LANG')
443 FROM fem_user_dim1_hier fudh,
444 fem_object_definition_tl fodt,
445 fem_object_catalog_tl foct,
446 fem_user_dim1_b parent,
447 fem_user_dim1_b child
448 WHERE fudh.hierarchy_obj_def_id = l_hierarchy_obj_def_id
449 AND fudh.hierarchy_obj_def_id = fodt.object_definition_id
450 AND fodt.language = userenv('LANG')
451 AND fodt.object_id = foct.object_id
452 AND foct.language = userenv('LANG')
453 AND parent_value_set_id = l_value_set_id
454 AND child_value_set_id = l_value_set_id
455 AND fudh.parent_id = parent.user_dim1_id
456 AND fudh.child_id = child.user_dim1_id
457 AND fudh.single_depth_flag = 'Y';
458
459 l_submit_req := SQL%ROWCOUNT;
460 g_no_rows := NVL(SQL%ROWCOUNT,0);
461
462 INSERT INTO fem_user_dim1_hier_t
463 (hierarchy_object_name,
464 hierarchy_obj_def_display_name,
465 parent_display_code,
466 parent_value_set_display_code,
467 child_display_code,
468 child_value_set_display_code,
469 display_order_num,
470 status,
471 language)
472 SELECT DISTINCT
473 p_hierarchy_name,
474 p_version_name,
475 fudh.parent_display_code,
476 l_vs_display_code,
477 fudh.parent_display_code ||l_elims_const,
478 l_vs_display_code ,
479 1,
480 'LOAD',
481 USERENV('LANG')
482 FROM fem_user_dim1_hier_t fudh
483 WHERE fudh.hierarchy_object_name = p_hierarchy_name
484 AND fudh.hierarchy_obj_def_display_name = p_version_name
485 AND fudh.parent_display_code <> fudh.child_display_code
486 AND fudh.parent_value_set_display_code = fudh.child_value_set_display_code;
487
488 g_no_rows := NVL(SQL%ROWCOUNT,0);
489
490 COMMIT;
491
492 -- Submit Dimension hierarchy loader.
493 fnd_file.put_line(fnd_file.log, '.............Beginning Submission of EPF Hierarchy Loader.............');
494 IF (l_submit_req >= 1) THEN
495 req_id := fnd_request.submit_request(
496 application => 'FEM',
497 program => 'FEM_HIER_LOADER',
498 sub_request => FALSE,
499 argument1 => 1400, -- p_obj_defn_id
500 argument2 => 'S', -- p_exec_mode
501 argument3 => 'USER_DIM1',
502 argument4 => p_hierarchy_name,
503 argument5 => p_version_name);
504 COMMIT;
505
506 fnd_file.put_line(fnd_file.log, 'Request Identifier: ' || req_id);
507 fnd_file.put_line(fnd_file.log, '.............Completed Submission of EPF Hierarchy Loader.............');
508
509 IF req_id <= 0 THEN
510 p_errbuf := FND_MESSAGE.get;
511 p_retcode := '2';
512 END IF;
513 END IF;
514
515 EXCEPTION
516 WHEN NO_USER_DIM1_PROC_KEY THEN
517 fnd_file.put_line(fnd_file.log, '<<<<<<<<<<<<<<<<<<<<<<Beginning of Error>>>>>>>>>>>>>>>>>>>>>>>>>>>');
518 fnd_file.put_line(fnd_file.log, 'User Dimension 1 must be part of the consolidation processing key in order to run this program.');
519 fnd_file.put_line(fnd_file.log, '<<<<<<<<<<<<<<<<<<<<<<<<End of Error>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>');
520 p_errbuf := 'User Dimension 1 must be part of the consolidation processing key in order to run this program.';
521 p_retcode := 2;
522 WHEN CONSOLIDATION_GVSC_UNDEFINED THEN
523 fnd_file.put_line(fnd_file.log, '<<<<<<<<<<<<<<<<<<<<<<Beginning of Error>>>>>>>>>>>>>>>>>>>>>>>>>>>');
524 fnd_file.put_line(fnd_file.log, 'The consolidation chart of accounts must be assigned before running this program.');
525 fnd_file.put_line(fnd_file.log, '<<<<<<<<<<<<<<<<<<<<<<<<End of Error>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>');
526 p_errbuf := 'The consolidation chart of accounts must be assigned before running this program.';
527 p_retcode := 2;
528 WHEN OTHERS THEN
529 fnd_file.put_line(fnd_file.log, '<<<<<<<<<<<<<<<<<<<<<<Beginning of Error>>>>>>>>>>>>>>>>>>>>>>>>>>>');
530 fnd_file.put_line(fnd_file.log, SQLERRM);
531 fnd_file.put_line(fnd_file.log, '<<<<<<<<<<<<<<<<<<<<<<<<End of Error>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>');
532 p_errbuf := SUBSTR(SQLERRM, 1, 255);
533 p_retcode := 2;
534 ROLLBACK;
535
536
537 END CREATE_ELIM_PARENT_LOB;
538
539
540
541 END GCS_IC_LOB_ELIM_PARENT_PKG;