eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.BSC_DIM_TPLATE

Source


1 PACKAGE BODY BSC_DIM_TPLATE AS
2 /* $Header: BSCUDIMB.pls 120.0 2005/06/01 16:56:48 appldev noship $ */
3 
4 G_Pro_Tbl       BSC_TEMPLATE.Project_Tbl_Type;
5 G_DF_Tbl        BSC_TEMPLATE.Dfamily_Tbl_Type;
6 G_DC_Tbl        BSC_TEMPLATE.Proj_Dim_Cols_Tbl_Type;
7 
8 
9 l_num_acct_type         number := 3;
10 l_num_account           number := 6;
11 l_num_project           number := 12;
12 
13 /*===========================================================================+
14 |
15 |   Name:          Create_Dimensions
16 |
17 |   Description:   To create Accout dimension for P/L KPI
18 |
19 |   History:
20 |       02-APR-1999   Alex Yang             Created.
21 |    12/22/1999   Henry Camacho Modified to Model 4.0
22 +============================================================================*/
23 
24 Function Create_Dimensions
25 Return Boolean
26 Is
27     l_acct_type     BSC_DIM_TPLATE.Acct_Type_Tbl_Type;
28     l_account       BSC_DIM_TPLATE.Acct_Tbl_Type;
29 
30     l_system_type       varchar2(60);
31     is_cross_template   boolean;
32 
33     l_code          number;
34     l_code_name     varchar2(30); -- ADRAO Changed to VARCHAR2(30)
35     l_project_count     number(3);
36 
37     l_count         number;
38     l_cursor        number;
39         l_ignore        number;
40     l_sql_stmt      varchar2(2000);
41     l_debug_stmt        varchar2(2000);
42 
43     l_num_of_levels number;
44     l_num_of_family number;
45     l_object_type      varchar2(30);
46 Begin
47   l_project_count:=0;
48   l_count:=0;
49   l_debug_stmt:='DEBUG: ';
50   l_object_type:= 'BSC_D_TYPE_OF_ACCOUNT';
51   is_cross_template:=FALSE;
52   l_acct_type(0).code  := 0;    l_acct_type(0).name := 'ALL';
53   l_acct_type(1).code  := 1;    l_acct_type(1).name := 'Incomes';
54   l_acct_type(2).code  := 2;    l_acct_type(2).name := 'Expenses';
55   l_acct_type(3).code  := 3;    l_acct_type(3).name := 'Profit';
56 
57   l_account(0).code       := 0;
58   l_account(0).name      := 'ALL';
59   l_account(0).acct_type := 0;
60   l_account(0).position  := 0;
61 
62   l_account(1).code       := 1;
63   l_account(1).name      := 'Sales';
64   l_account(1).acct_type := 1;
65   l_account(1).position  := 1;
66 
67   l_account(2).code       := 2;
68   l_account(2).name      := 'Cost of Sales';
69   l_account(2).acct_type := 2;
70   l_account(2).position  := 2;
71 
72   l_account(3).code       := 3;
73   l_account(3).name      := 'Other Incomes';
74   l_account(3).acct_type := 1;
75   l_account(3).position  := 3;
76 
77   l_account(4).code       := 4;
78   l_account(4).name      := 'Payroll Exp.';
79   l_account(4).acct_type := 2;
80   l_account(4).position  := 4;
81 
82   l_account(5).code       := 5;
83   l_account(5).name      := 'Other Expenses';
84   l_account(5).acct_type := 2;
85   l_account(5).position  := 5;
86 
87   l_account(6).code       := 6;
88   l_account(6).name      := 'Net Profit';
89   l_account(6).acct_type := 3;
90   l_account(6).position  := 6;
91 
92 
93   -- check template type (Tab or Cross)
94   Select PROPERTY_VALUE
95   Into   l_system_type
96   From   BSC_SYS_INIT
97   Where  PROPERTY_CODE = 'MODEL_TYPE';
98 
99   if (l_system_type = '1') then
100      Is_cross_template := TRUE;
101   end if;
102 
103   -- create Account Type dimension table
104   l_object_type := 'BSC_D_TYPE_OF_ACCOUNT';
105   Select count(*)
106   Into   l_count
107   From   USER_OBJECTS
108   Where  OBJECT_NAME = l_object_type;
109 
110   if (l_count <> 0) then
111       l_sql_stmt := 'Drop Table BSC_D_TYPE_OF_ACCOUNT';
112       l_debug_stmt := l_sql_stmt;
113 
114      BSC_APPS.DO_DDL(l_sql_stmt,
115                 ad_ddl.drop_table,
116                 'BSC_D_TYPE_OF_ACCOUNT');
117   end if;
118 
119   l_sql_stmt := 'Create Table BSC_D_TYPE_OF_ACCOUNT ('  ||
120                    'CODE           Number   NOT NULL,'||
121                    'LANGUAGE       Varchar2(4)   NOT NULL,'||
122                    'SOURCE_LANG    Varchar2(4)   NOT NULL,'||
123                    'USER_CODE      Varchar2(5), ' ||
124                    'NAME       Varchar2(20))'||
125                    ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.dimension_table_tbs_type)||
126                    ' '||BSC_APPS.bsc_storage_clause;
127 
128   l_debug_stmt := l_sql_stmt;
129 
130   BSC_APPS.DO_DDL(l_sql_stmt,
131                 ad_ddl.create_table,
132                 'BSC_D_TYPE_OF_ACCOUNT');
133 
134 
135   l_sql_stmt := 'CREATE UNIQUE INDEX BSC_D_TYPE_OF_ACCOUNT_U1 ON BSC_D_TYPE_OF_ACCOUNT (CODE,LANGUAGE)'||
136                 ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.dimension_index_tbs_type)||
137                 ' '||BSC_APPS.bsc_storage_clause;
138   l_debug_stmt := l_sql_stmt;
139  BSC_APPS.DO_DDL(l_sql_stmt,
140                 ad_ddl.create_index,
141                 'BSC_D_TYPE_OF_ACCOUNT_U1');
142 
143  --  Create View
144   l_sql_stmt := 'Create or replace view BSC_D_2_VL AS ('  ||
145         ' SELECT CODE,'||
146         ' USER_CODE,'||
147         ' NAME'||
148         ' FROM BSC_D_TYPE_OF_ACCOUNT '||
149         ' WHERE LANGUAGE = userenv(''LANG''))';
150 
151   l_debug_stmt := l_sql_stmt;
152   BSC_APPS.DO_DDL(l_sql_stmt,
153                 ad_ddl.create_view,
154                 'BSC_D_2_VL');
155 
156  --  Create Input table
157   l_object_type := 'BSC_DI_2';
158   Select count(*)
159   Into   l_count
160   From   USER_OBJECTS
161   Where  OBJECT_NAME = l_object_type;
162 
163   if (l_count <> 0) then
164       l_sql_stmt := 'Drop Table BSC_DI_2';
165       l_debug_stmt := l_sql_stmt;
166 
167      BSC_APPS.DO_DDL(l_sql_stmt,
168                 ad_ddl.drop_table,
169                 'BSC_DI_2');
170   end if;
171 
172   l_sql_stmt := 'Create Table BSC_DI_2 ('  ||
173                    'USER_CODE      Varchar2(5), ' ||
174                    'NAME       Varchar2(20))'||
175                    ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.input_table_tbs_type)||
176                    ' '||BSC_APPS.bsc_storage_clause;
177   l_debug_stmt := l_sql_stmt;
178 
179   BSC_APPS.DO_DDL(l_sql_stmt,
180                 ad_ddl.create_table,
181                 'BSC_DI_2');
182 
183   l_sql_stmt := 'CREATE UNIQUE INDEX BSC_DI_2_U1 ON BSC_DI_2 (USER_CODE)'||
184                 ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.input_index_tbs_type)||
185                 ' '||BSC_APPS.bsc_storage_clause;
186   l_debug_stmt := l_sql_stmt;
187   BSC_APPS.DO_DDL(l_sql_stmt,
188                 ad_ddl.create_index,
189                 'BSC_DI_2_U1');
190 
191 
192   -- create Account dimension table
193 
194   l_object_type := 'BSC_D_ACCOUNT';
195   Select count(*)
196   Into   l_count
197   From   USER_OBJECTS
198   Where  OBJECT_NAME = l_object_type;
199 
200   if (l_count <> 0) then
201       l_sql_stmt := 'Drop Table BSC_D_ACCOUNT';
202       l_debug_stmt := l_sql_stmt;
203 
204      BSC_APPS.DO_DDL(l_sql_stmt,
205                 ad_ddl.drop_table,
206                 'BSC_D_ACCOUNT');
207   end if;
208 
209   l_sql_stmt := 'Create Table BSC_D_ACCOUNT ( ' ||
210                     'CODE  Number NOT NULL,' ||
211                     'LANGUAGE       Varchar2(4)   NOT NULL,'||
212                     'SOURCE_LANG    Varchar2(4)   NOT NULL,'||
213                     'USER_CODE Varchar2(5), '  ||
214                     'NAME   Varchar2(20), ' ||
215                     'Typ_of_Acc_Code Number, '    ||
216                     'Typ_of_Acc_Code_Usr Varchar2(5), '  ||
217                     'Position Number(2))'||
218                     ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.dimension_table_tbs_type)||
219                     ' '||BSC_APPS.bsc_storage_clause;
220   l_debug_stmt := l_sql_stmt;
221 
222   BSC_APPS.DO_DDL(l_sql_stmt,
223                 ad_ddl.create_table,
224                 'BSC_D_ACCOUNT');
225 
226   l_sql_stmt := 'CREATE UNIQUE INDEX BSC_D_ACCOUNT_U1 ON BSC_D_ACCOUNT (CODE,LANGUAGE)'||
227                 ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.dimension_index_tbs_type)||
228                 ' '||BSC_APPS.bsc_storage_clause;
229   l_debug_stmt := l_sql_stmt;
230  BSC_APPS.DO_DDL(l_sql_stmt,
231                 ad_ddl.create_index,
232                 'BSC_D_ACCOUNT_U1');
233  --  Create View
234   l_sql_stmt := 'Create or replace view BSC_D_0_VL AS ('  ||
235         ' SELECT CODE,'||
236         ' USER_CODE,'||
237         ' NAME,'||
238         ' Typ_of_Acc_Code,'||
239         ' Typ_of_Acc_Code_Usr,'||
240         ' Position'||
241         ' FROM BSC_D_ACCOUNT '||
242         ' WHERE LANGUAGE = userenv(''LANG''))';
243 
244   l_debug_stmt := l_sql_stmt;
245   BSC_APPS.DO_DDL(l_sql_stmt,
246                 ad_ddl.create_view,
247                 'BSC_D_0_VL');
248 
249  --  Create Input table
250   l_object_type := 'BSC_DI_0' ;
251   Select count(*)
252   Into   l_count
253   From   USER_OBJECTS
254   Where  OBJECT_NAME = l_object_type ;
255 
256   if (l_count <> 0) then
257       l_sql_stmt := 'Drop Table BSC_DI_0';
258       l_debug_stmt := l_sql_stmt;
259 
260      BSC_APPS.DO_DDL(l_sql_stmt,
261                 ad_ddl.drop_table,
262                 'BSC_DI_0');
263   end if;
264 
265   l_sql_stmt := 'Create Table BSC_DI_0 ('  ||
266                    'USER_CODE      Varchar2(5), ' ||
267                    'NAME       Varchar2(20), '||
268                    'Typ_of_Acc_Code_Usr Varchar2(5),'||
269                    'Position Number(2))'||
270                    ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.input_table_tbs_type)||
271                    ' '||BSC_APPS.bsc_storage_clause;
272   l_debug_stmt := l_sql_stmt;
273 
274   BSC_APPS.DO_DDL(l_sql_stmt,
275                 ad_ddl.create_table,
276                 'BSC_DI_0');
277 
278   l_sql_stmt := 'CREATE UNIQUE INDEX BSC_DI_0_U1 ON BSC_DI_0 (USER_CODE)'||
279                 ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.input_index_tbs_type)||
280                 ' '||BSC_APPS.bsc_storage_clause;
281   l_debug_stmt := l_sql_stmt;
282   BSC_APPS.DO_DDL(l_sql_stmt,
283                 ad_ddl.create_index,
284                 'BSC_DI_0_U1');
285 
286 
287   -- create Subaccount Type dimension table
288 
289   l_object_type := 'BSC_D_SUBACCOUNT' ;
290   Select count(*)
291   Into   l_count
292   From   USER_OBJECTS
293   Where  OBJECT_NAME = l_object_type ;
294 
295   if (l_count <> 0) then
296       l_sql_stmt := 'Drop Table BSC_D_SUBACCOUNT';
297       l_debug_stmt := l_sql_stmt;
298 
299       BSC_APPS.DO_DDL(l_sql_stmt,
300                 ad_ddl.drop_table,
301                 'BSC_D_SUBACCOUNT');
302   end if;
303 
304   l_sql_stmt := 'Create Table BSC_D_SUBACCOUNT ( ' ||
305                    'CODE    Number NOT NULL,' ||
306                    'LANGUAGE       Varchar2(4)   NOT NULL,'||
307                    'SOURCE_LANG    Varchar2(4)   NOT NULL,'||
308                    'USER_CODE Varchar2(5), '  ||
309                    'NAME Varchar2(20), ' ||
310                    'Account_Code Number, '    ||
311                    'Account_Code_Usr Varchar2(5))'||
312                    ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.dimension_table_tbs_type)||
313                    ' '||BSC_APPS.bsc_storage_clause;
314 
315   l_debug_stmt := l_sql_stmt;
316   BSC_APPS.DO_DDL(l_sql_stmt,
317                 ad_ddl.create_table,
318                 'BSC_D_SUBACCOUNT');
319 
320   l_sql_stmt := 'CREATE UNIQUE INDEX BSC_D_SUBACCOUNT_U1 ON BSC_D_SUBACCOUNT (CODE,LANGUAGE)'||
321                 ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.dimension_index_tbs_type)||
322                 ' '||BSC_APPS.bsc_storage_clause;
323   l_debug_stmt := l_sql_stmt;
324 
325   BSC_APPS.DO_DDL(l_sql_stmt,
326         ad_ddl.create_index,
327                 'BSC_D_SUBACCOUNT_U1');
328 
329  --  Create View
330   l_sql_stmt := 'Create or replace view BSC_D_1_VL AS ('  ||
331         ' SELECT CODE,'||
332         ' USER_CODE,'||
333         ' NAME,'||
334         ' Account_Code ,'||
335         ' Account_Code_Usr'||
336         ' FROM BSC_D_SUBACCOUNT '||
337         ' WHERE LANGUAGE = userenv(''LANG''))';
338 
339   l_debug_stmt := l_sql_stmt;
340   BSC_APPS.DO_DDL(l_sql_stmt,
341                 ad_ddl.create_view,
342                 'BSC_D_1_VL');
343 
344  --  Create Input table
345 
346   l_object_type := 'BSC_DI_1' ;
347   Select count(*)
348   Into   l_count
349   From   USER_OBJECTS
350   Where  OBJECT_NAME = l_object_type;
351 
352   if (l_count <> 0) then
353       l_sql_stmt := 'Drop Table BSC_DI_1';
354       l_debug_stmt := l_sql_stmt;
355 
356      BSC_APPS.DO_DDL(l_sql_stmt,
357                 ad_ddl.drop_table,
358                 'BSC_DI_1');
359   end if;
360 
361   l_sql_stmt := 'Create Table BSC_DI_1 ('  ||
362                    'USER_CODE      Varchar2(5), ' ||
363                    'NAME       Varchar2(20), '||
364                    'Account_Code_Usr Varchar2(5))'||
365                    ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.input_table_tbs_type)||
366                    ' '||BSC_APPS.bsc_storage_clause;
367   l_debug_stmt := l_sql_stmt;
368 
369   BSC_APPS.DO_DDL(l_sql_stmt,
370                 ad_ddl.create_table,
371                 'BSC_DI_1');
372 
373   l_sql_stmt := 'CREATE UNIQUE INDEX BSC_DI_1_U1 ON BSC_DI_1 (USER_CODE)'||
374                 ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.input_index_tbs_type)||
378                 ad_ddl.create_index,
375                 ' '||BSC_APPS.bsc_storage_clause;
376   l_debug_stmt := l_sql_stmt;
377   BSC_APPS.DO_DDL(l_sql_stmt,
379                 'BSC_DI_1_U1');
380 
381   if (is_cross_template) then
382 
383       l_object_type := 'BSC_D_PROJECT' ;
384       Select count(*)
385       Into   l_project_count
386       From   USER_OBJECTS
387       Where  OBJECT_NAME = l_object_type;
388 
389       if (l_project_count <> 0) then
390           l_sql_stmt := 'Drop Table BSC_D_PROJECT';
391           l_debug_stmt := l_sql_stmt;
392        BSC_APPS.DO_DDL(l_sql_stmt,
393                 ad_ddl.drop_table,
394                 'BSC_D_PROJECT');
395 
396       end if;
397 
398       -- create Project dimension table
399 
400       l_sql_stmt := 'Create Table BSC_D_PROJECT (' ||
401             'CODE      NUMBER   NOT NULL,'     ||
402                     'LANGUAGE       Varchar2(4)   NOT NULL,'||
403                     'SOURCE_LANG    Varchar2(4)   NOT NULL,'||
404             'USER_CODE  VARCHAR2(5),'   ||
405             'NAME     VARCHAR2(20))'||
406             ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.dimension_table_tbs_type)||
407             ' '||BSC_APPS.bsc_storage_clause;
408 
409       l_debug_stmt := l_sql_stmt;
410       BSC_APPS.DO_DDL(l_sql_stmt,
411                 ad_ddl.create_table,
412                 'BSC_D_PROJECT');
413 
414       l_sql_stmt := 'CREATE UNIQUE INDEX BSC_D_PROJECT_U1 ON BSC_D_PROJECT (CODE,LANGUAGE)'||
415                     ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.dimension_index_tbs_type)||
416                     ' '||BSC_APPS.bsc_storage_clause;
417       l_debug_stmt := l_sql_stmt;
418       BSC_APPS.DO_DDL(l_sql_stmt,
419                 ad_ddl.create_index,
420                 'BSC_D_PROJECT_U1');
421      --  Create View
422       l_sql_stmt := 'Create or replace view BSC_D_3_VL AS ('  ||
423         ' SELECT CODE,'||
424         ' USER_CODE,'||
425         ' NAME'||
426         ' FROM BSC_D_PROJECT '||
427         ' WHERE LANGUAGE = userenv(''LANG''))';
428 
429       l_debug_stmt := l_sql_stmt;
430       BSC_APPS.DO_DDL(l_sql_stmt,
431                 ad_ddl.create_view,
432                 'BSC_D_3_VL');
433      --  Create Input table
434       l_object_type := 'BSC_DI_3' ;
435       Select count(*)
436       Into   l_count
437       From   USER_OBJECTS
438       Where  OBJECT_NAME = l_object_type;
439 
440       if (l_count <> 0) then
441           l_sql_stmt := 'Drop Table BSC_DI_3';
442           l_debug_stmt := l_sql_stmt;
443 
444          BSC_APPS.DO_DDL(l_sql_stmt,
445                     ad_ddl.drop_table,
446                     'BSC_DI_1');
447       end if;
448 
449       l_sql_stmt := 'Create Table BSC_DI_3 ('  ||
450                        'USER_CODE      Varchar2(5), ' ||
451                        'NAME       Varchar2(20))'||
452                         ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.input_table_tbs_type)||
453                         ' '||BSC_APPS.bsc_storage_clause;
454 
455       l_debug_stmt := l_sql_stmt;
456 
457       BSC_APPS.DO_DDL(l_sql_stmt,
458                     ad_ddl.create_table,
459                     'BSC_DI_1');
460 
461       l_sql_stmt := 'CREATE UNIQUE INDEX BSC_DI_3_U1 ON BSC_DI_3 (USER_CODE)'||
462                     ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.input_index_tbs_type)||
463                     ' '||BSC_APPS.bsc_storage_clause;
464       l_debug_stmt := l_sql_stmt;
465       BSC_APPS.DO_DDL(l_sql_stmt,
466                     ad_ddl.create_index,
467                     'BSC_DI_3_U1');
468 
469 
470       -- populating Project dimension value
471 
472      For i_project In 0 .. (l_num_project)
473      Loop
474        l_code := i_project;
475 
476        if l_code = 0 then
477          l_code_name := 'ALL';
478        else
479          l_code_name := 'Project ' || to_char(l_code);
480        end if;
481 
482 --       l_sql_stmt := 'Insert Into BSC_D_PROJECT (CODE, USER_CODE, NAME) Values ( ' ||
483 --         to_char(l_code) || ',  ' || to_char(l_code) || ', '
484 --         || '''' || l_code_name  || ''')';
485 
486        l_sql_stmt := 'Insert Into BSC_D_PROJECT  (CODE, USER_CODE, NAME,LANGUAGE,SOURCE_LANG) Values ( '||
487                    ' SELECT '||to_char(l_code) ||' AS CODE,' ||
488                     'to_char('||to_char(l_code)||') AS USER_CODE, ' ||
489                     ' ' || l_code_name|| ' AS NAME, ' ||
490             ' LNG.LANGUAGE_CODE AS LANGUAGE,USERENV(''LANG'') AS SOURCE_LANG '||
491             ' FROM DUAL , ' ||
492             ' FND_LANGUAGES LNG ';
493     l_sql_stmt := l_sql_stmt || ' AND LNG.INSTALLED_FLAG IN (''I'',''B'')';
494 
495 
496        l_debug_stmt := l_sql_stmt;
497        l_cursor := DBMS_SQL.Open_Cursor;
498        DBMS_SQL.Parse(l_cursor, l_sql_stmt, DBMS_SQL.native);
499        l_ignore := DBMS_SQL.Execute(l_cursor);
500        DBMS_SQL.Close_Cursor(l_cursor);
501     End Loop;
502 
503   end if;  -- is_cross_template
504 
505 
506   -- Insert records into BSC_D_TYPE_OF_ACCOUNT
507 
508   For i_acct_type In 0 .. (l_num_acct_type)
509   Loop
510     -- FEM_ALIAS translation
511     l_sql_stmt := 'INSERT INTO  BSC_D_TYPE_OF_ACCOUNT ' ||
515                     'to_char('||l_acct_type(i_acct_type).code||') AS USER_CODE, ' ||
512                ' (CODE,LANGUAGE,SOURCE_LANG,USER_CODE,NAME) ' ||
513                    ' SELECT '||l_acct_type(i_acct_type).code||' AS CODE,' ||
514             ' LNG.LANGUAGE_CODE AS LANGUAGE,USERENV(''LANG'') AS SOURCE_LANG,'||
516                     ' SUBSTRB(FEM_ALIAS.MEANING,1,20) AS NAME ' ||
517             ' FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_ALIAS, ' ||
518             '      FND_LANGUAGES LNG ';
519     -- To get ALL from commun
520         if l_acct_type(i_acct_type).code = 0 then
521         l_sql_stmt := l_sql_stmt || ' WHERE FEM_ALIAS.LOOKUP_TYPE =''BSC_UI_COMMON'' AND FEM_ALIAS.LOOKUP_CODE = ''ALL''';
522     else
523         l_sql_stmt := l_sql_stmt || ' WHERE FEM_ALIAS.LOOKUP_TYPE =''BSC_TPLATE_TAB_ACC_TYPE_ITEMS'' AND FEM_ALIAS.LOOKUP_CODE = '''||l_acct_type(i_acct_type).code||'''';
524     end if;
525     -- Only for current language
526 
527         l_sql_stmt := l_sql_stmt || ' AND FEM_ALIAS.LANGUAGE =userenv(''LANG'')';
528     l_sql_stmt := l_sql_stmt || ' AND LNG.INSTALLED_FLAG IN (''I'',''B'')';
529 
530     l_debug_stmt := l_sql_stmt;
531     l_cursor := DBMS_SQL.Open_Cursor;
532     DBMS_SQL.Parse(l_cursor, l_sql_stmt, DBMS_SQL.native);
533     l_ignore := DBMS_SQL.Execute(l_cursor);
534     DBMS_SQL.Close_Cursor(l_cursor);
535 
536   End Loop;
537 
538   -- Insert records into BSC_D_ACCOUNT and BSC_D_SUBACCOUNT
539 
540   For i_acct_ind In 0 .. (l_num_account)
541   Loop
542 
543     -- FEM_ALIAS translation
544     l_sql_stmt := 'INSERT INTO  BSC_D_ACCOUNT ' ||
545                '(CODE,LANGUAGE,SOURCE_LANG,USER_CODE,NAME,TYP_OF_ACC_CODE,TYP_OF_ACC_CODE_USR,POSITION) ' ||
546                    ' SELECT '||l_account(i_acct_ind).code||' AS CODE, '||
547             ' LNG.LANGUAGE_CODE AS LANGUAGE,USERENV(''LANG'') AS SOURCE_LANG,'||
548                     'to_char('||l_account(i_acct_ind).code||') AS USER_CODE, '||
549                     'SUBSTRB(FEM_ALIAS.MEANING,1,20) AS NAME,'||
550             l_account(i_acct_ind).acct_type||' AS TYP_OF_ACC_CODE, '||
551             l_account(i_acct_ind).acct_type||' AS TYP_OF_ACC_CODE_USR, '||
552             l_account(i_acct_ind).position ||' AS POSITION '||
553             'FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_ALIAS,'||
554             '      FND_LANGUAGES LNG ';
555     -- To get ALL from commun
556         if l_account(i_acct_ind).code = 0 then
557         l_sql_stmt :=l_sql_stmt || ' WHERE FEM_ALIAS.LOOKUP_TYPE =''BSC_UI_COMMON'' AND
558                   FEM_ALIAS.LOOKUP_CODE = ''ALL''';
559     else
560         l_sql_stmt :=l_sql_stmt || ' WHERE FEM_ALIAS.LOOKUP_TYPE =''BSC_TPLATE_TAB_ACC_ITEMS'' AND
561                   FEM_ALIAS.LOOKUP_CODE = '''||l_account(i_acct_ind).code||'''';
562     end if;
563     -- Only for current language
564         l_sql_stmt := l_sql_stmt || ' AND FEM_ALIAS.LANGUAGE =userenv(''LANG'')';
565     l_sql_stmt := l_sql_stmt || ' AND LNG.INSTALLED_FLAG IN (''I'',''B'')';
566 
567     l_debug_stmt := l_sql_stmt;
568     l_cursor := DBMS_SQL.Open_Cursor;
569     DBMS_SQL.Parse(l_cursor, l_sql_stmt, DBMS_SQL.native);
570     l_ignore := DBMS_SQL.Execute(l_cursor);
571     DBMS_SQL.Close_Cursor(l_cursor);
572 
573     -- FEM_ALIAS translation -Same itemns from maccount
574     l_sql_stmt := 'INSERT INTO  BSC_D_SUBACCOUNT ' ||
575                ' (CODE,LANGUAGE,SOURCE_LANG,USER_CODE,NAME,ACCOUNT_CODE,ACCOUNT_CODE_USR) ' ||
576                    ' SELECT '||l_account(i_acct_ind).code||' AS CODE, ' ||
577             ' LNG.LANGUAGE_CODE AS LANGUAGE,USERENV(''LANG'') AS SOURCE_LANG,'||
578                        ' to_char('||l_account(i_acct_ind).code||') AS USER_CODE,' ||
579                     ' SUBSTRB(FEM_ALIAS.MEANING,1,20) AS NAME, '||
580             l_account(i_acct_ind).code||' AS ACCOUNT_CODE, '||
581             l_account(i_acct_ind).code||' AS ACCOUNT_CODE_USR'||
582             ' FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_ALIAS, ' ||
583             '      FND_LANGUAGES LNG ';
584     -- To get ALL from commun
585         if l_account(i_acct_ind).code = 0 then
586         l_sql_stmt :=l_sql_stmt || ' WHERE FEM_ALIAS.LOOKUP_TYPE =''BSC_UI_COMMON'' AND
587                   FEM_ALIAS.LOOKUP_CODE = ''ALL''';
588     else
589         l_sql_stmt :=l_sql_stmt || ' WHERE FEM_ALIAS.LOOKUP_TYPE =''BSC_TPLATE_TAB_ACC_ITEMS'' AND
590                   FEM_ALIAS.LOOKUP_CODE = '''||l_account(i_acct_ind).code||'''';
591     end if;
592     -- Only for current language
593         l_sql_stmt := l_sql_stmt || ' AND FEM_ALIAS.LANGUAGE =userenv(''LANG'')';
594     l_sql_stmt := l_sql_stmt || ' AND LNG.INSTALLED_FLAG IN (''I'',''B'')';
595 
596 
597     l_debug_stmt := l_sql_stmt;
598     l_cursor := DBMS_SQL.Open_Cursor;
599     DBMS_SQL.Parse(l_cursor, l_sql_stmt, DBMS_SQL.native);
600     l_ignore := DBMS_SQL.Execute(l_cursor);
601     DBMS_SQL.Close_Cursor(l_cursor);
602 
603   End Loop;  -- i_acct_ind
604 
605   --
606   -- Configure Dimensions
607   --
608 
609   G_Pro_Tbl.Delete;
610   G_DF_Tbl.Delete;
611   G_DC_Tbl.Delete;
612 
613   -- define Account dimension levels
614 --2828685
615   G_Pro_Tbl(0).Dim_level_id   := 2;
616   G_Pro_Tbl(0).Table_name  := 'BSC_D_TYPE_OF_ACCOUNT';
617   G_Pro_Tbl(0).Table_Type    := 1;
618   G_Pro_Tbl(0).Level_pk_col  := 'TYP_OF_ACC_CODE';
619   G_Pro_Tbl(0).Abbreviation  := 'TyOfAc';
620   G_Pro_Tbl(0).Value_Order   := 2;
621   G_Pro_Tbl(0).Comp_Order    := 0;
625   G_Pro_Tbl(0).Caption_Tot   := 'ALL';
622   G_Pro_Tbl(0).Custom_Group  := 0;
623   G_Pro_Tbl(0).Name      := 'Account Type';
624   G_Pro_Tbl(0).Help      := 'Account Types';
626   G_Pro_Tbl(0).Caption_Com   := 'COMPARISON';
627   G_Pro_Tbl(0).parent_level  := NULL;
628   G_Pro_Tbl(0).fk_field      := NULL;
629   G_Pro_Tbl(0).rel_type      := NULL;
630   G_Pro_Tbl(0).direct_rel    := NULL;
631   G_Pro_Tbl(0).Level_View_Name := 'BSC_D_2_VL';
632 
633   G_Pro_Tbl(1).Dim_level_id   := 0;
634   G_Pro_Tbl(1).Table_name  := 'BSC_D_ACCOUNT';
635   G_Pro_Tbl(1).Table_Type    := 1;
636   G_Pro_Tbl(1).Level_pk_col  := 'ACCOUNT_CODE';
637   G_Pro_Tbl(1).Abbreviation  := 'Accnt';
638   G_Pro_Tbl(1).Value_Order   := 1;
639   G_Pro_Tbl(1).Comp_Order    := 0;
640   G_Pro_Tbl(1).Custom_Group  := 0;
641   G_Pro_Tbl(1).Name      := 'Account';
642   G_Pro_Tbl(1).Help      := 'Accounts';
643   G_Pro_Tbl(1).Caption_Tot   := 'ALL';
644   G_Pro_Tbl(1).Caption_Com   := 'COMPARISON';
645   G_Pro_Tbl(1).parent_level  := 2;
646   G_Pro_Tbl(1).fk_field      := 'TYP_OF_ACC_CODE';
647   G_Pro_Tbl(1).rel_type      := 1;
648   G_Pro_Tbl(1).direct_rel    := 1;
649   G_Pro_Tbl(1).Level_View_Name := 'BSC_D_0_VL';
650 
651   G_Pro_Tbl(2).Dim_level_id   := 1;
652   G_Pro_Tbl(2).Table_name  := 'BSC_D_SUBACCOUNT';
653   G_Pro_Tbl(2).Table_Type    := 1;
654   G_Pro_Tbl(2).Level_pk_col  := 'SUBACCOUNT_CODE';
655   G_Pro_Tbl(2).Abbreviation  := 'Sbccn';
656   G_Pro_Tbl(2).Value_Order   := 0;
657   G_Pro_Tbl(2).Comp_Order    := 0;
658   G_Pro_Tbl(2).Custom_Group  := 0;
659   G_Pro_Tbl(2).Name      := 'Sub-Account';
660   G_Pro_Tbl(2).Help      := 'Sub-Accounts';
661   G_Pro_Tbl(2).Caption_Tot   := 'ALL';
662   G_Pro_Tbl(2).Caption_Com   := 'COMPARISON';
663   G_Pro_Tbl(2).parent_level  := 0;
664   G_Pro_Tbl(2).fk_field      := 'ACCOUNT_CODE';
665   G_Pro_Tbl(2).rel_type      := 1;
666   G_Pro_Tbl(2).direct_rel    := 1;
667   G_Pro_Tbl(2).Level_View_Name := 'BSC_D_1_VL';
668 
669   G_Pro_Tbl(3).Dim_level_id   := 3;
670   G_Pro_Tbl(3).Table_name  := 'BSC_D_PROJECT';
671   G_Pro_Tbl(3).Table_Type    := 1;
672   G_Pro_Tbl(3).Level_pk_col  := 'PROJECT_CODE';
673   G_Pro_Tbl(3).Abbreviation  := 'Prjct';
674   G_Pro_Tbl(3).Value_Order   := 0;
675   G_Pro_Tbl(3).Comp_Order    := 0;
676   G_Pro_Tbl(3).Custom_Group  := 0;
677   G_Pro_Tbl(3).Name      := 'Project';
678   G_Pro_Tbl(3).Help      := 'Strategic Projects';
679   G_Pro_Tbl(3).Caption_Tot   := 'ALL';
680   G_Pro_Tbl(3).Caption_Com   := 'COMPARISON';
681   G_Pro_Tbl(3).parent_level  := NULL;
682   G_Pro_Tbl(3).fk_field      := NULL;
683   G_Pro_Tbl(3).rel_type      := NULL;
684   G_Pro_Tbl(3).direct_rel    := NULL;
685   G_Pro_Tbl(3).Level_View_Name := 'BSC_D_3_VL';
686 
687 -- 2828685 Synch the BSC_SYS_DIM_LEVELS_B
688 
689 
690   ----Define Dimension Columns
691   G_DC_Tbl(0).Dim_level_id  := -1;
692   G_DC_Tbl(0).Column_Name   := 'CODE';
693   G_DC_Tbl(0).Column_Type   := 'P';
694 
695   G_DC_Tbl(1).Dim_level_id  := -1;
696   G_DC_Tbl(1).Column_Name   := 'USER_CODE';
697   G_DC_Tbl(1).Column_Type   := 'U';
698 
699   G_DC_Tbl(2).Dim_level_id  := -1;
700   G_DC_Tbl(2).Column_Name   := 'NAME';
701   G_DC_Tbl(2).Column_Type   := 'D';
702 
703   G_DC_Tbl(3).Dim_level_id  := 0;
704   G_DC_Tbl(3).Column_Name   := 'TYP_OF_ACC_CODE';
705   G_DC_Tbl(3).Column_Type   := 'F';
706 
707   G_DC_Tbl(4).Dim_level_id  := 0;
708   G_DC_Tbl(4).Column_Name   := 'TYP_OF_ACC_CODE_USR';
709   G_DC_Tbl(4).Column_Type   := 'F';
710 
711   G_DC_Tbl(5).Dim_level_id  := 0;
712   G_DC_Tbl(5).Column_Name   := 'POSITION';
713   G_DC_Tbl(5).Column_Type   := 'A';
714 
715   G_DC_Tbl(6).Dim_level_id  := 1;
716   G_DC_Tbl(6).Column_Name   := 'ACCOUNT_CODE';
717   G_DC_Tbl(6).Column_Type   := 'F';
718 
719   G_DC_Tbl(7).Dim_level_id  := 1;
720   G_DC_Tbl(7).Column_Name   := 'ACCOUNT_CODE_USR';
721   G_DC_Tbl(7).Column_Type   := 'F';
722 
723 
724 
725   -- define dimension family
726 
727   G_DF_Tbl(0).Dim_group_id   := 1;
728   G_DF_Tbl(0).Name       := 'Dgrp Account';
729   G_DF_Tbl(0).Dim_level_id   := 0;
730   G_DF_Tbl(0).Dim_level_idx  := 1;
731   G_DF_Tbl(0).Total          := -1;
732   G_DF_Tbl(0).Comparison     := -1;
733   G_DF_Tbl(0).filter_col     := NULL;
734   G_DF_Tbl(0).filter_val     := 0;
735   G_DF_Tbl(0).default_val    := 'C';
736   G_DF_Tbl(0).default_type   := 0;
737   G_DF_Tbl(0).Parent_Total   := 2;
738   G_DF_Tbl(0).No_items       := 0;
739 
740   G_DF_Tbl(1).Dim_group_id   := 2;
741   G_DF_Tbl(1).Name       := 'Dgrp SubAcct';
742   G_DF_Tbl(1).Dim_level_id   := 1;
743   G_DF_Tbl(1).Dim_level_idx  := 1;
744   G_DF_Tbl(1).Total          := -1;
745   G_DF_Tbl(1).Comparison     := -1;
746   G_DF_Tbl(1).filter_col     := NULL;
747   G_DF_Tbl(1).filter_val     := 0;
748   G_DF_Tbl(1).default_val    := 'T';
749   G_DF_Tbl(1).default_type   := 0;
750   G_DF_Tbl(1).Parent_Total   := 2;
751   G_DF_Tbl(1).No_items       := 0;
752 
753   G_DF_Tbl(2).Dim_group_id   := 3;
754   G_DF_Tbl(2).Name       := 'Dgrp Acct Type';
755   G_DF_Tbl(2).Dim_level_id   := 2;
756   G_DF_Tbl(2).Dim_level_idx  := 1;
757   G_DF_Tbl(2).Total          := -1;
758   G_DF_Tbl(2).Comparison     := -1;
759   G_DF_Tbl(2).filter_col     := NULL;
763   G_DF_Tbl(2).Parent_Total   := 2;
760   G_DF_Tbl(2).filter_val     := 0;
761   G_DF_Tbl(2).default_val    := 'T';
762   G_DF_Tbl(2).default_type   := 0;
764   G_DF_Tbl(2).No_items       := 0;
765 
766   -- Project dimension family
767 
768   G_DF_Tbl(3).Dim_group_id   := 4;
769   G_DF_Tbl(3).Name       := 'Dgrp Project';
770   G_DF_Tbl(3).Dim_level_id   := 3;
771   G_DF_Tbl(3).Dim_level_idx  := 1;
772   G_DF_Tbl(3).Total          := -1;
773   G_DF_Tbl(3).Comparison     := -1;
774   G_DF_Tbl(3).filter_col     := NULL;
775   G_DF_Tbl(3).filter_val     := 0;
776   G_DF_Tbl(3).default_val    := 'C';
777   G_DF_Tbl(3).default_type   := 0;
778   G_DF_Tbl(3).Parent_Total   := 2;
779   G_DF_Tbl(3).No_items       := 0;
780 
781 
782 
783 
784   if (is_cross_template) then
785     l_num_of_levels := 4;
786     l_num_of_family := 4;
787   else
788     l_num_of_levels := 3;
789     l_num_of_family := 3;
790   end if;
791 
792   if (NOT define_dim_relations(l_num_of_levels, l_num_of_family)) then
793     l_debug_stmt := bsc_apps.get_message('BSC_ERROR_CONFIG_DIM');
794     Raise BSC_DIM_ERROR;
795   end if;
796 
797   Return(TRUE);
798 
799 EXCEPTION
800     WHEN BSC_DIM_ERROR THEN
801     BSC_MESSAGE.Add(
802         X_Message => l_debug_stmt,
803         X_Source  => 'bsc_dim_tplate.create_dimensions',
804         X_Mode    => 'I');
805 
806     Return(FALSE);
807 
808     WHEN OTHERS THEN
809     BSC_MESSAGE.Add(
810         X_Message => SQLERRM,
811         X_Source => 'bsc_dim_tplate.create_dimensions',
812         X_Mode => 'I');
813 
814     BSC_MESSAGE.Add(
815         X_Message => l_debug_stmt,
816         X_Source  => 'bsc_dim_tplate.create_dimensions',
817         x_type    => 3,
818         X_Mode    => 'I');
819 
820     Return(FALSE);
821 
822 End Create_Dimensions;
823 
824 
825 /*===========================================================================+
826 |
827 |   Name:          Define_Dim_Relations
828 |
829 |   Description:   To configue dimension family and relation
830 |
831 |   Parameters:
832 |   x_num_of_levels     number of dimension levels
833 |   x_num_of_family     number of dimension families
834 |
835 |   History:
836 |       02-APR-1999   Alex Yang             Created.
837 |    12/22/1999   Henry Camacho Modified to Model 4.0
838 +============================================================================*/
839 Function Define_Dim_Relations(
840         x_num_of_levels     IN  Number,
841         x_num_of_family     IN  Number
842 ) Return Boolean
843 Is
844     l_debug_stmt        varchar2(2000);
845     x_num_of_columns    Number;
846     i_dimension_col     Number;
847     l_sql           varchar2(32700);
848 Begin
849 
850   x_num_of_columns := 8;
851 
852   For i_dimension In 0 .. (x_num_of_levels -1)
853   Loop
854 
855     l_debug_stmt := 'Insert Into BSC_SYS_DIM_LEVELS_B, Dim_level_id=' ||
856             to_char(G_Pro_Tbl(i_dimension).Dim_level_id);
857 --2828685 ADD WHO COLUMNS
858     Insert Into BSC_SYS_DIM_LEVELS_B (
859     DIM_LEVEL_ID,
860     LEVEL_TABLE_NAME,
861     TABLE_TYPE,
862     LEVEL_PK_COL,
863     ABBREVIATION,
864     VALUE_ORDER_BY,
865     COMP_ORDER_BY,
866     CUSTOM_GROUP,
867     USER_KEY_SIZE,
868     DISP_KEY_SIZE,
869         EDW_FLAG,
870     LEVEL_VIEW_NAME,
871     CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE
872 )
873     Values (
874     G_Pro_Tbl(i_dimension).Dim_level_id,
875     G_Pro_Tbl(i_dimension).Table_name,
876     G_Pro_Tbl(i_dimension).Table_Type,
877     G_Pro_Tbl(i_dimension).Level_pk_col,
878     G_Pro_Tbl(i_dimension).abbreviation,
879     G_Pro_Tbl(i_dimension).Value_Order,
880     G_Pro_Tbl(i_dimension).Comp_Order,
881     G_Pro_Tbl(i_dimension).Custom_Group,
882     5, 20, 0,
883     G_Pro_Tbl(i_dimension).Level_View_Name,
884 1,SYSDATE,1,SYSDATE
885     );
886 
887     l_debug_stmt := 'Insert Into BSC_SYS_DIM_LEVELS_TL, Dim_level_id=' ||
888             to_char(G_Pro_Tbl(i_dimension).Dim_level_id);
889     --FEM translation
890     l_sql := 'INSERT INTO BSC_SYS_DIM_LEVELS_TL '||
891         ' (DIM_LEVEL_ID,LANGUAGE,SOURCE_LANG,NAME,HELP,TOTAL_DISP_NAME,COMP_DISP_NAME) '||
892             'SELECT '||
893                 G_Pro_Tbl(i_dimension).Dim_level_id||' AS DIM_LEVEL_ID, '||
894             'FEM_DIM.LANGUAGE AS LANGUAGE, '||
895             'FEM_DIM.SOURCE_LANG AS SOURCE_LANG, '||
896             'SUBSTR(FEM_DIM.MEANING,1,30) AS NAME, '||
897             'SUBSTR(FEM_DIM.MEANING,1,80) AS HELP, '||
898             'SUBSTR(FEM_ALL.MEANING,1,15) AS TOTAL_DISP_NAME, '||
899             'SUBSTR(FEM_COMP.MEANING,1,15) AS COMP_DISP_NAME '||
900         'FROM '||
901                         BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_DIM, '||
902             BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_ALL, '||
903             BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_COMP '||
904         'WHERE '||
905             'FEM_DIM.LOOKUP_TYPE =''BSC_TPLATE_TAB_DIM_LEVEL_NAME'' AND '||
906                 'FEM_DIM.LOOKUP_CODE = '''||G_Pro_Tbl(i_dimension).Dim_level_id||''' AND '||
910                 'FEM_COMP.LOOKUP_CODE = ''COMPARISON'' AND '||
907                 'FEM_ALL.LOOKUP_TYPE =''BSC_UI_COMMON'' AND '||
908                 'FEM_ALL.LOOKUP_CODE = ''ALL'' AND '||
909                 'FEM_COMP.LOOKUP_TYPE =''BSC_UI_COMMON'' AND '||
911                 'FEM_DIM.LANGUAGE = FEM_ALL.LANGUAGE AND '||
912                 'FEM_COMP.LANGUAGE = FEM_ALL.LANGUAGE';
913     BSC_APPS.Execute_Immediate(l_sql);
914 
915     l_debug_stmt := 'Insert Into BSC_SYS_DIM_LEVEL_COLS, Dim_level_id=' ||
916             to_char(G_Pro_Tbl(i_dimension).Dim_level_id);
917 
918 
919     For i_dimension_col In 0 .. (x_num_of_columns -1) LOOP
920        IF G_DC_Tbl(i_dimension_col).Dim_level_id = -1 THEN
921             Insert Into BSC_SYS_DIM_LEVEL_COLS (
922             DIM_LEVEL_ID,
923             COLUMN_NAME,
924             COLUMN_TYPE)
925             Values (
926             G_Pro_Tbl(i_dimension).Dim_level_id,
927             G_DC_Tbl(i_dimension_col).Column_Name,
928             G_DC_Tbl(i_dimension_col).Column_Type
929             );
930        ELSE
931            IF G_DC_Tbl(i_dimension_col).Dim_level_id = G_Pro_Tbl(i_dimension).Dim_level_id THEN
932             Insert Into BSC_SYS_DIM_LEVEL_COLS (
933             DIM_LEVEL_ID,
934             COLUMN_NAME,
935             COLUMN_TYPE)
936             Values (
937             G_Pro_Tbl(i_dimension).Dim_level_id,
938             G_DC_Tbl(i_dimension_col).Column_Name,
939             G_DC_Tbl(i_dimension_col).Column_Type
940             );
941         END IF;
942      END IF;
943 
944     End loop;
945 
946 
947 
948     if (G_Pro_Tbl(i_dimension).parent_level is NOT NULL) then
949 
950         l_debug_stmt := 'Insert Into BSC_SYS_DIM_LEVEL_RELS, Dim_level_id=' ||
951             to_char(G_Pro_Tbl(i_dimension).Dim_level_id);
952 
953         Insert Into BSC_SYS_DIM_LEVEL_RELS (
954         DIM_LEVEL_ID,
955         RELATION_COL,
956         PARENT_DIM_LEVEL_ID,
957         RELATION_TYPE,
958         DIRECT_RELATION )
959     Values (
960         G_Pro_Tbl(i_dimension).Dim_level_id,
961         G_Pro_Tbl(i_dimension).fk_field,     -- relation_field
962         G_Pro_Tbl(i_dimension).parent_level, -- parent_entity_code
963         G_Pro_Tbl(i_dimension).rel_type,     -- relation_type
964         G_Pro_Tbl(i_dimension).direct_rel    -- direct_relation
965     );
966 
967     end if;
968 
969     l_debug_stmt := 'Insert Into BSC_DB_TABLES_RELS, Dim_level_id=' ||
970             to_char(G_Pro_Tbl(i_dimension).Dim_level_id);
971     Insert Into BSC_DB_TABLES_RELS (
972     TABLE_NAME,
973     SOURCE_TABLE_NAME)
974     Values (
975     G_Pro_Tbl(i_dimension).Table_name,
976     'BSC_DI_' || G_Pro_Tbl(i_dimension).Dim_level_id
977     );
978 
979     l_debug_stmt := 'Insert Into BSC_DB_TABLES, Dim_level_id=' ||
980             to_char(G_Pro_Tbl(i_dimension).Dim_level_id);
981     Insert Into BSC_DB_TABLES (
982     TABLE_NAME,
983     TABLE_TYPE,
984     PERIODICITY_ID,
985     SOURCE_DATA_TYPE,
986     SOURCE_FILE_NAME)
987     Values (
988     'BSC_DI_' || G_Pro_Tbl(i_dimension).Dim_level_id,
989     2,
990     0,
991     0,
992     NULL
993     );
994 
995 
996   End Loop; -- dimension level loop
997 
998 
999   For i_dem_family In 0 .. (x_num_of_family -1)
1000   Loop
1001 
1002     l_debug_stmt := 'Insert Into MPROJ_DRILLS_FAMILIES,Dim_group_id=' ||
1003             to_char(G_DF_Tbl(i_dem_family).Dim_group_id);
1004 
1005     -- FEM translation
1006 --2828685 ADD WHO COLUMNS
1007     l_sql := 'INSERT INTO BSC_SYS_DIM_GROUPS_TL '||
1008         ' (DIM_GROUP_ID,LANGUAGE,SOURCE_LANG,NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) ' ||
1009         'SELECT '||
1010             G_DF_Tbl(i_dem_family).Dim_group_id||' AS DIM_GROUP_ID, '||
1011             'FEM_ALIAS.LANGUAGE AS LANGUAGE, '||
1012             'FEM_ALIAS.SOURCE_LANG AS SOURCE_LANG, '||
1013             'SUBSTR(FEM_ALIAS.MEANING,1,15) AS NAME '||
1014             ',1,SYSDATE,1,SYSDATE '||
1015         'FROM '||
1016             BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_ALIAS '||
1017         'WHERE '||
1018             'FEM_ALIAS.LOOKUP_TYPE = ''BSC_TPLATE_TAB_DIM_GROUP_NAME'' AND '||
1019                 'FEM_ALIAS.LOOKUP_CODE = '''||G_DF_Tbl(i_dem_family).Dim_group_id||'''';
1020     BSC_APPS.Execute_Immediate(l_sql);
1021 
1022     l_debug_stmt := 'Insert Into BSC_SYS_DIM_LEVELS_BY_GROUP, ' ||
1023     ' Dim_group_id=' || to_char(G_DF_Tbl(i_dem_family).Dim_group_id) ||
1024         ', Dim_level_id=' || to_char(G_DF_Tbl(i_dem_family).Dim_level_id);
1025 
1026     Insert Into BSC_SYS_DIM_LEVELS_BY_GROUP (
1027     DIM_GROUP_ID,
1028     DIM_LEVEL_ID,
1029     DIM_LEVEL_INDEX,
1030     TOTAL_FLAG,
1031     COMPARISON_FLAG,
1032     FILTER_COLUMN,
1033     FILTER_VALUE,
1034     DEFAULT_VALUE,
1035     DEFAULT_TYPE,
1036     PARENT_IN_TOTAL,
1037     NO_ITEMS )
1038     Values (
1039     G_DF_Tbl(i_dem_family).Dim_group_id,     -- family_code
1040     G_DF_Tbl(i_dem_family).Dim_level_id,    -- entity_code
1041     G_DF_Tbl(i_dem_family).Dim_level_idx,       -- order_r
1042     G_DF_Tbl(i_dem_family).Total,       -- total
1043     G_DF_Tbl(i_dem_family).Comparison,  -- comparison
1044     G_DF_Tbl(i_dem_family).Filter_col,  -- condition_field
1045     G_DF_Tbl(i_dem_family).Filter_val,  -- condition_value
1046     G_DF_Tbl(i_dem_family).Default_val,     -- init
1047     G_DF_Tbl(i_dem_family).Default_type,    -- init_type
1048     G_DF_Tbl(i_dem_family).Parent_Total,    -- status_whn_parnt_is_total
1049     G_DF_Tbl(i_dem_family).No_items     -- hide_if_no_items
1050     );
1051 
1052   End Loop; -- dimension family loop
1053 
1054   Return(TRUE);
1055 
1056 EXCEPTION
1057 
1058     WHEN OTHERS THEN
1059     BSC_MESSAGE.Add(
1060         X_Message => SQLERRM,
1061         X_Source => 'bsc_dim_tplate.define_dim_relations',
1062         X_Mode => 'I');
1063 
1064     BSC_MESSAGE.Add(
1065         X_Message => l_debug_stmt,
1066         X_Source  => 'bsc_template.create_crx_template',
1067         x_type    => 3,
1068         X_Mode    => 'I');
1069 
1070     Return(FALSE);
1071 
1072 End Define_Dim_Relations;
1073 
1074 
1075 END BSC_DIM_TPLATE;