[Home] [Help]
PACKAGE BODY: APPS.GCS_BUILD_EPB_DIM_TR_PKG
Source
1 PACKAGE BODY GCS_BUILD_EPB_DIM_TR_PKG AS
2 /* $Header: gcsdimtrb.pls 120.2 2006/06/09 17:48:36 skamdar noship $ */
3 --
4 -- Package
5 -- build_epb_dimtr_pkg
6 -- Purpose
7 -- Creates GCS_DYN_EPB_DIMTR_PKG
8 -- History
9 -- 12-MAR-04 R Goyal Created
10 --
11 --
12 --
13 -- Public procedures
14 --
15 PROCEDURE build_epb_dimtr_pkg IS
16
17 -- row number to be used in dynamically creating the package
18 r NUMBER := 1;
19 body VARCHAR2(10000);
20
21 body_len NUMBER;
22 curr_pos NUMBER;
23 line_num NUMBER := 1;
24 err VARCHAR2(2000);
25 l_global_vs_id NUMBER;
26
27 felm_obj_def_id NUMBER;
28 interco_obj_def_id NUMBER;
29 cat_obj_def_id NUMBER;
30 na_obj_def_id NUMBER;
31
32 l_felm_value_set VARCHAR2(150);
33 l_felm_value_set_id NUMBER;
34 l_interco_value_set VARCHAR2(150);
35 l_interco_value_set_id NUMBER;
36 l_na_value_set VARCHAR2(150);
37 l_na_value_set_id NUMBER;
38 l_cat_value_set VARCHAR2(150);
39
40 -- Store whether a dimension is used by GCS and the respective table info
41 --Bugfix 5308890: Hardcode mapping for Intercopmany to 'N'
42 l_interco_req VARCHAR2(1) := 'N';
43 l_interco_tab VARCHAR2(30);
44 l_interco_b VARCHAR2(30);
45 l_interco_btab VARCHAR2(30);
46 l_interco_tltab VARCHAR2(30);
47 l_interco_attrtab VARCHAR2(30);
48 l_interco_col VARCHAR2(30);
49 l_interco_name VARCHAR2(30);
50 l_interco_column VARCHAR2(30);
51
52 --Bugfix 5308890: Hardcode mapping for Financial Element to 'N'
53 l_felm_req VARCHAR2(1) := 'N';
54 l_felm_tab VARCHAR2(30);
55 l_felm_btab VARCHAR2(30);
56 l_felm_b VARCHAR2(30);
57 l_felm_tltab VARCHAR2(30);
58 l_felm_attrtab VARCHAR2(30);
59 l_felm_col VARCHAR2(30);
60 l_felm_name VARCHAR2(30);
61 l_felm_column VARCHAR2(30);
62
63 --Bugfix 5308890: Hardcode mapping for Natural Account to 'N'
64 l_na_req VARCHAR2(1) := 'N';
65 l_na_tab VARCHAR2(30);
66 l_na_b VARCHAR2(30);
67 l_na_btab VARCHAR2(30);
68 l_na_tltab VARCHAR2(30);
69 l_na_attrtab VARCHAR2(30);
70 l_na_col VARCHAR2(30);
71 l_na_name VARCHAR2(30);
72 l_na_column VARCHAR2(30);
73
74 l_category_req VARCHAR2(1);
75 l_category_tab VARCHAR2(30);
76 l_category_b VARCHAR2(30);
77 l_category_btab VARCHAR2(30);
78 l_category_tltab VARCHAR2(30);
79 l_cat_attrtab VARCHAR2(30);
80 l_category_col VARCHAR2(30);
81 l_category_name VARCHAR2(30);
82 l_cat_column VARCHAR2(30);
83
84 l_fe_value_setid NUMBER;
85 l_interco_value_setid NUMBER;
86 l_na_value_setid NUMBER;
87 l_cat_value_setid NUMBER;
88
89 BEGIN
90
91 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
92 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'GCS_BUILD_EPB_DIM_TR_PKG' || '.' || 'BUILD_EPB_DIMTR_PKG',
93 GCS_UTILITY_PKG.g_module_enter || 'BUILD_EPB_DIM_TR_PKG' ||
94 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
95 END IF;
96 FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_enter || 'BUILD_EPB_DIMTR_PKG' || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
97
98
99 --Bugfix 5308890: Comment out the check for intercompany, natural account, and financial element as they are now supported in EPB
100 /*
101 -- Set the value sets
102 begin
103 l_felm_value_set_id := gcs_utility_pkg.g_gcs_dimension_info ('FINANCIAL_ELEM_ID').associated_value_set_id;
104 exception
105 when no_data_found then
106 l_felm_value_set_id := -1;
107 end;
108
109 begin
110 l_interco_value_set_id := gcs_utility_pkg.g_gcs_dimension_info ('INTERCOMPANY_ID').associated_value_set_id;
111 exception
112 when no_data_found then
113 l_interco_value_set_id := -1;
114 end;
115
116 begin
117 l_na_value_set_id := gcs_utility_pkg.g_gcs_dimension_info ('NATURAL_ACCOUNT_ID').associated_value_set_id;
118 exception
119 when no_data_found then
120 l_na_value_set_id := -1;
121 end;
122 */
123
124 --Bugfix 5308890: Comment out check for Financial Element, Intercompany and Natural Account well
125 /*
126 -- Set the required flags
127 begin
128 SELECT enabled_flag, 'FEM_' || substr(epb_column,0, 10), epb_column
129 INTO l_felm_req, l_felm_tab, l_felm_column
130 FROM GCS_EPB_DIM_MAPS
131 WHERE gcs_column = 'FINANCIAL_ELEM_ID';
132 exception
133 when no_data_found then
134 l_felm_req := 'N';
135 end;
136
137 -- Get the GVS
138 SELECT fch_global_vs_combo_id
139 INTO l_global_vs_id
140 FROM gcs_system_options;
141
142 -- get the value set name
143 begin
144 SELECT value_set_display_code, value_set_id
145 INTO l_felm_value_set, l_fe_value_setid
146 FROM fem_value_sets_b
147 WHERE value_set_id = ( SELECT gvs.value_set_id
148 FROM FEM_GLOBAL_VS_COMBO_DEFS gvs, FEM_TAB_COLUMNS_B dim
149 WHERE gvs.global_vs_combo_id = l_global_vs_id
150 AND dim.fem_data_type_code = 'DIMENSION'
151 AND gvs.dimension_id = dim.dimension_id
152 AND dim.table_name = 'FEM_BALANCES'
153 AND dim.column_name = l_felm_column) ;
154 exception
155 when no_data_found then
156 l_felm_value_set := '-1';
157 end;
158
159 -- Set the table names, column names and column id's to be used in the main sql
160 IF substr(l_felm_tab,14) <> '0' THEN
161 l_felm_b := substr(l_felm_tab, 0, 13) || '_B';
162 l_felm_btab := substr(l_felm_tab, 0, 13) || '_B_T';
163 l_felm_tltab := substr(l_felm_tab, 0, 13) || '_TL_T';
164 l_felm_attrtab := substr(l_felm_tab, 0, 13) || '_ATTR_T';
165 l_felm_col := substr(l_felm_tab, 5, 9) || '_DISPLAY_CODE';
166 l_felm_name := substr(l_felm_tab, 5, 9) || '_NAME';
167 felm_obj_def_id := get_obj_def_id(substr(l_felm_tab, 13, 1));
168 ELSE
169 l_felm_b := l_felm_tab || '_B';
170 l_felm_btab := l_felm_tab || '_B_T';
171 l_felm_tltab := l_felm_tab || '_TL_T';
172 l_felm_attrtab := l_felm_tab || '_ATTR_T';
173 l_felm_col := substr(l_felm_tab, 5, 10) || '_DISPLAY_CODE';
174 l_felm_name := substr(l_felm_tab, 5, 10) || '_NAME';
175 -- felm_obj_def_id := 1220;
176 felm_obj_def_id := 28;
177 END IF;
178
179 begin
180 SELECT enabled_flag, 'FEM_' || substr(epb_column,0, 10), epb_column
181 INTO l_interco_req, l_interco_tab, l_interco_column
182 FROM GCS_EPB_DIM_MAPS
183 WHERE gcs_column = 'INTERCOMPANY_ID';
184 exception
185 when no_data_found then
186 l_interco_req := 'N';
187 end;
188
189 -- get the value set name
190 begin
191 SELECT value_set_display_code, value_set_id
192 INTO l_interco_value_set, l_interco_value_setid
193 FROM fem_value_sets_b
194 WHERE value_set_id = ( SELECT gvs.value_set_id
195 FROM FEM_GLOBAL_VS_COMBO_DEFS gvs, FEM_TAB_COLUMNS_B dim
196 WHERE gvs.global_vs_combo_id = l_global_vs_id
197 AND dim.fem_data_type_code = 'DIMENSION'
198 AND gvs.dimension_id = dim.dimension_id
199 AND dim.table_name = 'FEM_BALANCES'
200 AND dim.column_name = l_interco_column) ;
201 exception
202 when no_data_found then
203 l_interco_value_set := '-1';
204 end;
205
206 IF substr(l_interco_tab,14) <> '0' THEN
207 l_interco_b := substr(l_interco_tab, 0, 13) || '_B';
208 l_interco_btab := substr(l_interco_tab, 0, 13) || '_B_T';
209 l_interco_tltab := substr(l_interco_tab, 0, 13) || '_TL_T';
210 l_interco_attrtab := substr(l_interco_tab, 0, 13) || '_ATTR_T';
211 l_interco_col := substr(l_interco_tab, 5, 9) || '_DISPLAY_CODE';
212 l_interco_name := substr(l_interco_tab, 5, 9) || '_NAME';
213 interco_obj_def_id := get_obj_def_id(substr(l_interco_tab, 13, 1));
214 ELSE
215 l_interco_b := l_interco_tab || '_B';
216 l_interco_btab := l_interco_tab || '_B_T';
217 l_interco_tltab := l_interco_tab || '_TL_T';
218 l_interco_attrtab := l_interco_tab || '_ATTR_T';
219 l_interco_col := substr(l_interco_tab, 5, 10) || '_DISPLAY_CODE';
220 l_interco_name := substr(l_interco_tab, 5, 10) || '_NAME';
221 interco_obj_def_id := 28;
222 -- interco_obj_def_id := 1220;
223 END IF;
224
225 begin
226 SELECT enabled_flag, 'FEM_' || substr(epb_column,0, 10), epb_column
227 INTO l_na_req, l_na_tab, l_na_column
228 FROM GCS_EPB_DIM_MAPS
229 WHERE gcs_column = 'NATURAL_ACCOUNT_ID';
230 exception
231 when no_data_found then
232 l_na_req := 'N' ;
233 end;
234
235 -- get the value set name
236 begin
237 SELECT value_set_display_code, value_set_id
238 INTO l_na_value_set, l_na_value_setid
239 FROM fem_value_sets_b
240 WHERE value_set_id = ( SELECT gvs.value_set_id
241 FROM FEM_GLOBAL_VS_COMBO_DEFS gvs, FEM_TAB_COLUMNS_B dim
242 WHERE gvs.global_vs_combo_id = l_global_vs_id
243 AND dim.fem_data_type_code = 'DIMENSION'
244 AND dim.table_name = 'FEM_BALANCES'
245 AND gvs.dimension_id = dim.dimension_id
246 AND dim.column_name = l_na_column) ;
247 exception
248 when no_data_found then
249 l_na_value_set := '-1';
250 end;
251
252 IF substr(l_na_tab,14) <> '0' THEN
253 l_na_b := substr(l_na_tab, 0, 13) || '_B';
254 l_na_btab := substr(l_na_tab, 0, 13) || '_B_T';
255 l_na_tltab := substr(l_na_tab, 0, 13) || '_TL_T';
256 l_na_attrtab := substr(l_na_tab, 0, 13) || '_ATTR_T';
257 l_na_col := substr(l_na_tab, 5, 9) || '_DISPLAY_CODE';
258 l_na_name := substr(l_na_tab, 5, 9) || '_NAME';
259 na_obj_def_id := get_obj_def_id(substr(l_na_tab, 13, 1));
260 ELSE
261 l_na_b := l_na_tab || '_B';
262 l_na_btab := l_na_tab || '_B_T';
263 l_na_tltab := l_na_tab || '_TL_T';
264 l_na_attrtab := l_na_tab || '_ATTR_T';
265 l_na_col := substr(l_na_tab, 5, 10) || '_DISPLAY_CODE';
266 l_na_name := substr(l_na_tab, 5, 10) || '_NAME';
267 -- na_obj_def_id := 1220;
268 na_obj_def_id := 28;
269 END IF;
270
271 */
272
273 -- Get the GVS
274 SELECT fch_global_vs_combo_id
275 INTO l_global_vs_id
276 FROM gcs_system_options;
277
278 begin
279 SELECT enabled_flag, 'FEM_' || substr(epb_column,0, 10), epb_column
280 INTO l_category_req, l_category_tab, l_cat_column
281 FROM GCS_EPB_DIM_MAPS
282 WHERE gcs_column = 'CREATED_BY_OBJECT_ID';
283 exception
284 when no_data_found then
285 l_category_req := 'N' ;
286 end;
287
288 -- get the value set name
289 begin
290 SELECT value_set_display_code, value_set_id
291 INTO l_cat_value_set, l_cat_value_setid
292 FROM fem_value_sets_b
293 WHERE value_set_id = ( SELECT gvs.value_set_id
294 FROM FEM_GLOBAL_VS_COMBO_DEFS gvs, FEM_TAB_COLUMNS_B dim
295 WHERE gvs.global_vs_combo_id = l_global_vs_id
296 AND dim.fem_data_type_code = 'DIMENSION'
297 AND gvs.dimension_id = dim.dimension_id
298 AND dim.table_name = 'FEM_BALANCES'
299 AND dim.column_name = l_cat_column) ;
300 exception
301 when no_data_found then
302 l_cat_value_set := '-1';
303 end;
304
305 IF substr(l_category_tab,14) <> '0' THEN
306 l_category_b := substr(l_category_tab, 0, 13) || '_B';
307 l_category_btab := substr(l_category_tab, 0, 13) || '_B_T';
308 l_category_tltab := substr(l_category_tab, 0, 13) || '_TL_T';
309 l_cat_attrtab := substr(l_category_tab, 0, 13) || '_ATTR_T';
310 l_category_col := substr(l_category_tab, 5, 9) || '_DISPLAY_CODE';
311 l_category_name := substr(l_category_tab, 5, 9) || '_NAME';
312 cat_obj_def_id := get_obj_def_id(substr(l_category_tab, 13, 1));
313 ELSE
314 l_category_b := l_category_tab || '_B';
315 l_category_btab := l_category_tab || '_B_T';
316 l_category_tltab := l_category_tab || '_TL_T';
317 l_cat_attrtab := l_category_tab || '_ATTR_T';
318 l_category_col := substr(l_category_tab, 5, 10) || '_DISPLAY_CODE';
319 l_category_name := substr(l_category_tab, 5, 10) || '_NAME';
320 -- cat_obj_def_id := 1220;
321 cat_obj_def_id := 28;
322 END IF;
323
324
325
326 -- Create the package body
327 body:=
328 'CREATE OR REPLACE PACKAGE BODY GCS_DYN_EPB_DIMTR_PKG AS
329
330
331 /* $Header: gcsdimtrb.pls 120.2 2006/06/09 17:48:36 skamdar noship $ */
332 -- Store the log level
333 runtimeLogLevel NUMBER := FND_LOG.g_current_runtime_level;
334 statementLogLevel CONSTANT NUMBER := FND_LOG.level_statement;
335 procedureLogLevel CONSTANT NUMBER := FND_LOG.level_procedure;
336 exceptionLogLevel CONSTANT NUMBER := FND_LOG.level_exception;
337 errorLogLevel CONSTANT NUMBER := FND_LOG.level_error;
338 unexpectedLogLevel CONSTANT NUMBER := FND_LOG.level_unexpected;
339
340 g_src_sys_code NUMBER := GCS_UTILITY_PKG.g_gcs_source_system_code;
341
342 DIM_LOAD_ERROR EXCEPTION;
343
344
345 PROCEDURE Gcs_Epb_Tr_Dim (
346 errbuf OUT NOCOPY VARCHAR2,
347 retcode OUT NOCOPY VARCHAR2 ) IS
348
349 l_execution_mode VARCHAR2(1) := ''S'' ;
350 l_felm_req_id NUMBER;
351 l_int_req_id NUMBER;
352 l_na_req_id NUMBER;
353 l_cat_req_id NUMBER;
354
355 module VARCHAR2(30) := ''GCS_EPB_TR_DIM'';
356
357 BEGIN
358
359 runtimeLogLevel := FND_LOG.g_current_runtime_level;
360
361 IF (procedureloglevel >= runtimeloglevel ) THEN
362 FND_LOG.STRING(procedureloglevel, ''gcs.plsql.gcs_epb_dim_tr_pkg.gcs_epb_tr_dim.begin'' || GCS_UTILITY_PKG.g_module_enter, to_char(sysdate, ''DD-MON-YYYY HH:MI:SS''));
363 END IF;
364
365 FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_enter || ''Gcs_Epb_Tr_Dim'' || to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
366
367 ';
368
369 curr_pos := 1;
370 body_len := LENGTH(body);
371 WHILE curr_pos <= body_len LOOP
372 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
373 curr_pos := curr_pos + g_line_size;
374 r := r + 1;
375 END LOOP;
376
377 --Bugfix 5308890: This code will never be generated since l_felm_req has been hardcoded to 'N'
378 IF l_felm_req = 'Y' THEN
379 body:= ' INSERT INTO ' || l_felm_btab || ' (' || l_felm_col || ', value_set_display_code, status)';
380 body := body || '
381 SELECT ';
382 body := body || 'financial_elem_display_code, ''' || l_felm_value_set || ''' , ''LOAD''
383 FROM fem_fin_elems_b
384 WHERE value_set_id = ' || l_felm_value_set_id || '
385 AND financial_elem_display_code NOT IN
386 ( SELECT ' || l_felm_col || '
387 FROM ' || l_felm_b || '
388 WHERE value_set_id = ' || l_fe_value_setid || ' );' ;
389
390 body := body || '
391 ';
392
393 body := body || '
394 INSERT INTO ' || l_felm_tltab || ' (' || l_felm_col || ', value_set_display_code, status, language, description, ';
395 body := body || l_felm_name || ') ';
396 body := body || '
397 SELECT financial_elem_display_code, ''' || l_felm_value_set ;
398 body := body || ''', ''LOAD'', userenv(''LANG''), description, financial_elem_name ';
399 body := body || '
400 FROM fem_fin_elems_vl
401 WHERE value_set_id = ' || l_felm_value_set_id || '
402 AND financial_elem_display_code NOT IN
403 ( SELECT ' || l_felm_col || '
404 FROM ' || l_felm_b || '
405 WHERE value_set_id = ' || l_fe_value_setid || ' );' ;
406
407 curr_pos := 1;
408 body_len := LENGTH(body);
409 WHILE curr_pos <= body_len LOOP
410 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
411 curr_pos := curr_pos + g_line_size;
412 r := r + 1;
413 END LOOP;
414
415 body := '
416 ';
417 body := body || '
418 INSERT INTO ' || l_felm_attrtab || ' (' || l_felm_col ;
419 body := body || ', value_set_display_code, attribute_varchar_label, attribute_assign_value, status, version_display_code)
420 SELECT ';
421 body := body || 'financial_elem_display_code, ''' || l_felm_value_set ;
422 body := body || ''', ''SOURCE_SYSTEM_CODE'', ''GCS'', ''LOAD'', ''Default''
423 FROM fem_fin_elems_b
424 WHERE value_set_id = ' || l_felm_value_set_id || '
425 AND financial_elem_display_code NOT IN
426 ( SELECT ' || l_felm_col || '
427 FROM ' || l_felm_b || '
428 WHERE value_set_id = ' || l_fe_value_setid || ' );' ;
429
430 body := body || '
431 ';
432 body := body || '
433 INSERT INTO ' || l_felm_attrtab || ' (' || l_felm_col ;
434 body := body || ', value_set_display_code, attribute_varchar_label, attribute_assign_value, status, version_display_code)
435 SELECT ';
436 body := body || 'financial_elem_display_code, ''' || l_felm_value_set ;
437 body := body || ''', ''RECON_LEAF_NODE_FLAG'', ''Y'', ''LOAD'', ''Default''
438 FROM fem_fin_elems_b
439 WHERE value_set_id = ' || l_felm_value_set_id || '
440 AND financial_elem_display_code NOT IN
441 ( SELECT ' || l_felm_col || '
442 FROM ' || l_felm_b || '
443 WHERE value_set_id = ' || l_fe_value_setid || ' );' ;
444
445 body := body || '
446 ';
447 body := body || '
448 IF (SQL%ROWCOUNT <> 0) THEN
449 FEM_DIM_MEMBER_LOADER_PKG.Main(
450 errbuf => errbuf,
451 retcode => retcode,
452 p_execution_mode => ''S'',
453 p_dimension_id => ' || felm_obj_def_id || ');';
454
455 body := body || '
456 dbms_output.put_line(''FE dim load status = ''|| retcode ); ';
457
458 body := body || '
459 ';
460
461 body := body || '
462 IF retcode = ''2'' THEN
463 RAISE DIM_LOAD_ERROR;
464 END IF;
465 END IF ;
466 ';
467
468 curr_pos := 1;
469 body_len := LENGTH(body);
470 WHILE curr_pos <= body_len LOOP
471 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
472 curr_pos := curr_pos + g_line_size;
473 r := r + 1;
474 END LOOP;
475
476 END IF; -- if felm_req is Y
477
478
479 --Bugfix 5308890: This code will never be executed as l_na_req has been hard-coded to 'N'
480 IF l_na_req = 'Y' THEN
481 body:= 'INSERT INTO ' || l_na_btab || ' (' || l_na_col || ', value_set_display_code, status)
482 SELECT ';
483 body := body || 'natural_account_display_code, ''' || l_na_value_set || ''', ''LOAD''
484 FROM fem_nat_accts_b
485 WHERE value_set_id = ' || l_na_value_set_id || '
486 AND natural_account_display_code NOT IN
487 ( SELECT ' || l_na_col || '
488 FROM ' || l_na_b || '
489 WHERE value_set_id = ' || l_na_value_setid || ' );' ;
490
491 body := body || '
492 INSERT INTO ' || l_na_tltab || ' (' || l_na_col || ', value_set_display_code, status, language, description, ';
493 body := body || l_na_name || ')
494 SELECT natural_account_display_code, ''' || l_na_value_set ;
495 body := body || ''', ''LOAD'', userenv(''LANG''), description, natural_account_name
496 FROM fem_nat_accts_vl
497 WHERE value_set_id = ' || l_na_value_set_id || '
498 AND natural_account_display_code NOT IN
499 ( SELECT ' || l_na_col || '
500 FROM ' || l_na_b || '
501 WHERE value_set_id = ' || l_na_value_setid || ' );' ;
502
503 curr_pos := 1;
504 body_len := LENGTH(body);
505 WHILE curr_pos <= body_len LOOP
506 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
507 curr_pos := curr_pos + g_line_size;
508 r := r + 1;
509 END LOOP;
510
511 body := body || '
512 ';
513
514 body := 'INSERT INTO ' || l_na_attrtab || ' (' || l_na_col ;
515 body := body || ', value_set_display_code, attribute_varchar_label, attribute_assign_value, status, version_display_code)
516 SELECT ';
517 body := body || 'natural_account_display_code, ''' || l_na_value_set ;
518 body := body || ''', ''SOURCE_SYSTEM_CODE'', ''GCS'', ''LOAD'', ''Default''
519 FROM fem_nat_accts_b
520 WHERE value_set_id = ' || l_na_value_set_id || '
521 AND natural_account_display_code NOT IN
522 ( SELECT ' || l_na_col || '
523 FROM ' || l_na_b || '
524 WHERE value_set_id = ' || l_na_value_setid || ' );' ;
525 body := body || '
526 ';
527
528 body := body || '
529 INSERT INTO ' || l_na_attrtab || ' (' || l_na_col ;
530 body := body || ', value_set_display_code, attribute_varchar_label, attribute_assign_value, status, version_display_code)
531 SELECT ';
532 body := body || 'natural_account_display_code, ''' || l_na_value_set ;
533 body := body || ''', ''RECON_LEAF_NODE_FLAG'', ''Y'', ''LOAD'', ''Default''
534 FROM fem_nat_accts_b
535 WHERE value_set_id = ' || l_na_value_set_id || '
536 AND natural_account_display_code NOT IN
537 ( SELECT ' || l_na_col || '
538 FROM ' || l_na_b || '
539 WHERE value_set_id = ' || l_na_value_setid || ' );' ;
540
541 body := body || '
542 ';
543 body := body || '
544 IF (SQL%ROWCOUNT <> 0) THEN
545 FEM_DIM_MEMBER_LOADER_PKG.Main(
546 errbuf => errbuf,
547 retcode => retcode,
548 p_execution_mode => ''S'',
549 p_dimension_id => ' || na_obj_def_id || ';' ;
550
551 body := body || '
552 ';
553 body := body || '
554 dbms_output.put_line(''NA dim load status = ''|| retcode ); ';
555
556 body := body || '
557 ';
558
559 body := body || '
560 IF retcode = ''2'' THEN
561 RAISE DIM_LOAD_ERROR;
562 END IF;
563 END IF ;
564 ';
565
566
567 curr_pos := 1;
568 body_len := LENGTH(body);
569 WHILE curr_pos <= body_len LOOP
570 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
571 curr_pos := curr_pos + g_line_size;
572 r := r + 1;
573 END LOOP;
574
575 END IF; -- if na_req is Y
576
577
578 IF l_category_req = 'Y' THEN
579 body:= 'INSERT INTO ' || l_category_btab || ' (' || l_category_col || ', value_set_display_code, status)
580 SELECT ';
581 body := body || 'category_code, ''' || l_cat_value_set || ''', ''LOAD''
582 FROM gcs_categories_b
583 WHERE category_code NOT IN
584 ( SELECT ' || l_category_col || '
585 FROM ' || l_category_b || '
586 WHERE value_set_id = ' || l_cat_value_setid || ' );' ;
587
588 body := body || '
589 INSERT INTO ' || l_category_tltab || ' (' || l_category_col || ', value_set_display_code, status, language, description, ';
590 body := body || l_category_name || ')
591 SELECT category_code, ''' || l_cat_value_set ;
592 body := body || ''', ''LOAD'', userenv(''LANG''), description, category_name
593 FROM gcs_categories_tl
594 WHERE language = userenv(''LANG'')
595 AND category_code NOT IN
596 ( SELECT ' || l_category_col || '
597 FROM ' || l_category_b || '
598 WHERE value_set_id = ' || l_cat_value_setid || ' );' ;
599
600 curr_pos := 1;
601 body_len := LENGTH(body);
602 WHILE curr_pos <= body_len LOOP
603 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
604 curr_pos := curr_pos + g_line_size;
605 r := r + 1;
606 END LOOP;
607
608 body := '
609 ';
610 body := body || 'INSERT INTO ' || l_cat_attrtab || ' (' || l_category_col ;
611 body := body || ', value_set_display_code, attribute_varchar_label, attribute_assign_value, status, version_display_code)
612 SELECT ';
613 body := body || 'category_code, ''' || l_cat_value_set ;
614 body := body || ''', ''SOURCE_SYSTEM_CODE'', ''GCS'', ''LOAD'', ''Default''
615 FROM gcs_categories_b
616 WHERE category_code NOT IN
617 ( SELECT ' || l_category_col || '
618 FROM ' || l_category_b || '
619 WHERE value_set_id = ' || l_cat_value_setid || ' );' ;
620
621 body := body || '
622 INSERT INTO ' || l_cat_attrtab || ' (' || l_category_col ;
623 body := body || ', value_set_display_code, attribute_varchar_label, attribute_assign_value, status, version_display_code)
624 SELECT ';
625 body := body || 'category_code, ''' || l_cat_value_set ;
626 body := body || ''', ''RECON_LEAF_NODE_FLAG'', ''Y'', ''LOAD'', ''Default''
627 FROM gcs_categories_b
628 WHERE category_code NOT IN
629 ( SELECT ' || l_category_col || '
630 FROM ' || l_category_b || '
631 WHERE value_set_id = ' || l_cat_value_setid || ' );' ;
632
633 body := body || '
634 ';
635 body := body || '
636 IF (SQL%ROWCOUNT <> 0) THEN
637 FEM_DIM_MEMBER_LOADER_PKG.Main(
638 errbuf => errbuf,
639 retcode => retcode,
640 p_execution_mode => ''S'',
641 p_dimension_id => ' || cat_obj_def_id || '); ' ;
642
643 body := body || '
644 dbms_output.put_line(''Category dim load status = ''|| retcode ); ';
645
646 body := body || '
647 ';
648
649 body := body || '
650 IF retcode = ''2'' THEN
651 RAISE DIM_LOAD_ERROR;
652 END IF;
653 END IF;
654 ';
655
656
657 body := body || '
658 ';
659
660
661 curr_pos := 1;
662 body_len := LENGTH(body);
663 WHILE curr_pos <= body_len LOOP
664 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
665 curr_pos := curr_pos + g_line_size;
666 r := r + 1;
667 END LOOP;
668
669 END IF; -- if category_req is Y
670
671
672 --Bugfix 5308890: This code will never be executed since l_interco_req has been hardcoded to 'N'
673 IF l_interco_req = 'Y' THEN
674 body:= 'INSERT INTO ' || l_interco_btab || ' (' || l_interco_col || ', value_set_display_code, status)
675 SELECT ';
676 body := body || 'cctr_org_display_code, ''' || l_interco_value_set || ''', ''LOAD''
677 FROM fem_cctr_orgs_b
678 WHERE value_set_id = ' || l_interco_value_set_id || '
679 AND cctr_org_display_code NOT IN
680 ( SELECT ' || l_interco_col || '
681 FROM ' || l_interco_b || '
682 WHERE value_set_id = ' || l_interco_value_setid || ' );' ;
683
684 body := body || '
685 ';
686
687 body := body || '
688 INSERT INTO ' || l_interco_tltab || ' (' || l_interco_col || ', value_set_display_code, status, language, description, ';
689 body := body || l_interco_name || ')
690 SELECT cctr_org_display_code, ''' || l_interco_value_set ;
691 body := body || ''', ''LOAD'', userenv(''LANG''), description, company_cost_center_org_name
692 FROM fem_cctr_orgs_vl
693 WHERE value_set_id = ' || l_interco_value_set_id || '
694 AND cctr_org_display_code NOT IN
695 ( SELECT ' || l_interco_col || '
696 FROM ' || l_interco_b || '
697 WHERE value_set_id = ' || l_interco_value_setid || ' );' ;
698
699 curr_pos := 1;
700 body_len := LENGTH(body);
701 WHILE curr_pos <= body_len LOOP
702 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
703 curr_pos := curr_pos + g_line_size;
704 r := r + 1;
705 END LOOP;
706
707 body := body || '
708 ';
709
710
711 body := 'INSERT INTO ' || l_interco_attrtab || ' (' || l_interco_col ;
712 body := body || ', value_set_display_code, attribute_varchar_label, attribute_assign_value, status, version_display_code)
713 SELECT ';
714 body := body || 'cctr_org_display_code, ''' || l_interco_value_set ;
715 body := body || ''', ''SOURCE_SYSTEM_CODE'', ''GCS'', ''LOAD'', ''Default''
716 FROM fem_cctr_orgs_b
717 WHERE value_set_id = ' || l_interco_value_set_id || '
718 AND cctr_org_display_code NOT IN
719 ( SELECT ' || l_interco_col || '
720 FROM ' || l_interco_b || '
721 WHERE value_set_id = ' || l_interco_value_setid || ' );' ;
722
723 body := body || '
724 ';
725
726 body := body || '
727 INSERT INTO ' || l_interco_attrtab || ' (' || l_interco_col ;
728 body := body || ', value_set_display_code, attribute_varchar_label, attribute_assign_value, status, version_display_code)
729 SELECT ';
730 body := body || 'cctr_org_display_code, ''' || l_interco_value_set ;
731 body := body || ''', ''RECON_LEAF_NODE_FLAG'', ''Y'', ''LOAD'', ''Default''
732 FROM fem_cctr_orgs_b
733 WHERE value_set_id = ' || l_interco_value_set_id || '
734 AND cctr_org_display_code NOT IN
735 ( SELECT ' || l_interco_col || '
736 FROM ' || l_interco_b || '
737 WHERE value_set_id = ' || l_interco_value_setid || ' );' ;
738
739 body := body || '
740 ';
741 body := body || '
742 IF (SQL%ROWCOUNT <> 0) THEN
743 FEM_DIM_MEMBER_LOADER_PKG.Main(
744 errbuf => errbuf,
745 retcode => retcode,
746 p_execution_mode => ''S'',
747 p_dimension_id => ' || interco_obj_def_id || '); ' ;
748
749 body := body || '
750 dbms_output.put_line(''Intercompany dim load status = ''|| retcode ); ';
751
752 body := body || '
753 ';
754
755 body := body || '
756 IF retcode = ''2'' THEN
757 RAISE DIM_LOAD_ERROR;
758 END IF;
759 END IF;
760 ';
761
762 body := body || '
763 ';
764
765
766 curr_pos := 1;
767 body_len := LENGTH(body);
768 WHILE curr_pos <= body_len LOOP
769 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
770 curr_pos := curr_pos + g_line_size;
771 r := r + 1;
772 END LOOP;
773
774 END IF; -- if interco_req is Y
775
776 body:=
777 '
778 EXCEPTION
779
780 WHEN NO_DATA_FOUND THEN
781 IF (unexpectedloglevel >= runtimeloglevel ) THEN
782 FND_LOG.STRING(unexpectedloglevel, ''gcs.plsql.GCS_EPB_DIM_TR_PKG.GCS_EPB_TR_DIM'', ''GCS_NO_DATA_FOUND'');
783 END IF;
784 retcode := ''0'';
785 errbuf := ''GCS_NO_DATA_FOUND'';
786 RAISE NO_DATA_FOUND;
787
788 WHEN DIM_LOAD_ERROR THEN
789 IF (unexpectedloglevel >= runtimeloglevel ) THEN
790 FND_LOG.STRING(unexpectedloglevel, ''gcs.plsql.GCS_EPB_DIM_TR_PKG.GCS_EPB_TR_DIM'', ''GCS_DIM_LOAD_ERROR'');
791 END IF;
792 retcode := ''0'';
793 FND_FILE.PUT_LINE(FND_FILE.LOG, '' Dimension Load Error - '' || errbuf );
794 RAISE;
795
796 WHEN OTHERS THEN
797 errbuf := substr( SQLERRM, 1, 2000);
798 IF (unexpectedloglevel >= runtimeloglevel ) THEN
799 FND_LOG.STRING(unexpectedloglevel, ''gcs.plsql.GCS_EPB_DIM_TR_PKG.GCS_EPB_TR_DIM'', errbuf);
800 END IF;
801 retcode := ''0'';
802 RAISE;
803
804
805 END Gcs_Epb_Tr_Dim;
806
807 END GCS_DYN_EPB_DIMTR_PKG;
808 ';
809 curr_pos := 1;
810 body_len := LENGTH(body);
811 WHILE curr_pos <= body_len LOOP
812 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
813 curr_pos := curr_pos + g_line_size;
814 r := r + 1;
815 END LOOP;
816
817 ad_ddl.create_plsql_object(GCS_DYNAMIC_UTIL_PKG.g_applsys_username, 'GCS', 'GCS_DYN_EPB_DIMTR_PKG',1, r - 1, 'FALSE', err);
818
819 -- dbms_output.put_line('Error' || AD_DDL.error_buf);
820
821 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
822 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'GCS_BUILD_EPB_DIMTR_PKG' || '.' || 'BUILD_EPB_DIMTR_PKG',
823 GCS_UTILITY_PKG.g_module_success || 'BUILD_EPB_DIMTR_PKG' ||
824 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
825 END IF;
826 FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_success || 'BUILD_EPB_DIMTR_PKG' || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
827
828 EXCEPTION
829 WHEN OTHERS THEN
830 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
831 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
832 'GCS_BUILD_EPB_DIM_TR_PKG' || '.' || 'BUILD_EPB_DIMTR_PKG',
833 SUBSTR(SQLERRM, 1, 255));
834 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
835 'GCS_BUILD_EPB_DIM_TR_PKG' || '.' || 'BUILD_EPB_DIMTR_PKG',
836 GCS_UTILITY_PKG.g_module_failure || 'BUILD_EPB_DIMTR_PKG' ||
837 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
838 END IF;
839 FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
840 'BUILD_EPB_DIMTR_PKG' || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
841
842 END build_epb_dimtr_pkg;
843
844 FUNCTION get_obj_def_id ( num VARCHAR2) RETURN NUMBER IS
845
846 obj_def_id NUMBER;
847 BEGIN
848
849 IF (num = '1') THEN
850 obj_def_id := 19;
851 -- obj_def_id := 1211;
852 ELSIF (num = '2') THEN
853 obj_def_id := 20;
854 -- obj_def_id := 1212;
855 ELSIF (num = '3') THEN
856 obj_def_id := 21;
857 -- obj_def_id := 1213;
858 ELSIF (num = '4') THEN
859 obj_def_id := 22;
860 -- obj_def_id := 1214;
861 ELSIF (num = '5') THEN
862 obj_def_id := 23;
863 -- obj_def_id := 1215;
864 ELSIF (num = '6') THEN
865 obj_def_id := 24;
866 -- obj_def_id := 1216;
867 ELSIF (num = '7') THEN
868 obj_def_id := 25;
869 -- obj_def_id := 1217;
870 ELSIF (num = '8') THEN
871 obj_def_id := 26;
872 -- obj_def_id := 1218;
873 ELSIF (num = '9') THEN
874 obj_def_id := 27;
875 -- obj_def_id := 1219;
876 END IF;
877
878 RETURN obj_def_id;
879 END get_obj_def_id;
880
881 END GCS_BUILD_EPB_DIM_TR_PKG;