[Home] [Help]
PACKAGE BODY: SYSTEM.AD_MLS
Source
1 package body ad_mls as
2 /* $Header: admlsb.pls 120.0 2005/05/25 12:06:15 appldev noship $ */
3
4 mls_decode_1 varchar2(2000);
5 mls_decode_2 varchar2(2000);
6
7 procedure do_create_mls_schema
8 (install_group_num in number,
9 aol_schema in varchar2,
10 apps_schema in varchar2,
11 apps_mls_schema in varchar2,
12 invoker_mode in varchar2);
13 --
14 -- Purpose
15 -- The procedure does the work of creating an mls apps account.
16 -- Arguments
17 -- aol_schema AOL Schema
18 -- apps_schema APPS schema to base this mls apps schema on
19 -- Example
20 -- none
21 -- Notes
22 -- 1. none
23 --
24
25
26 -- Bug 3611969 : FIXED FILE.SQL.35 GSCC WARNINGS
27 -- sraghuve (07/05/2004)
28
29
30 procedure create_mls_schema
31 (aol_schema in varchar2)
32
33 is
34 begin
35
36 create_mls_schema
37 (aol_schema => aol_schema,
38 invoker_mode => 'FALSE');
39
40 end;
41
42
43 procedure create_mls_schema (aol_schema in varchar2,
44 invoker_mode in varchar2)
45
46 is
47 apps_schema varchar2(30);
48 apps_schema_mls varchar2(30);
49 begin
50
51 ad_apps_private.error_buf := null; -- reset error buffer
52
53 if ad_apps_private.is_mls is null then
54 ad_apps_private.initialize(aol_schema);
55 end if;
56
57 if ad_apps_private.is_mls = TRUE then
58
59 declare
60 c integer;
61 rows_processed integer;
62 install_group_num number;
63 statement varchar2(500);
64 begin
65 -- check existence of aol_schema account
66 if not ad_apps_private.check_if_schema_exists(aol_schema) then
67 raise_application_error(-20000,'The schema '||upper(aol_schema)||
68 ' does not exist.');
69 end if;
70
71 c := dbms_sql.open_cursor;
72
73 statement := 'select install_group_num from '||upper(aol_schema)||
74 '.fnd_oracle_userid where read_only_flag = ''U'''||
75 ' order by install_group_num' ;
76
77 dbms_sql.parse(c, statement, dbms_sql.native);
78 dbms_sql.define_column(c,1,install_group_num);
79 rows_processed := dbms_sql.execute(c);
80 -- loop through all install groups and create the multiple APPS_MLS
81 -- schemas as necessary
82 loop
83 if dbms_sql.fetch_rows(c) > 0 then
84 dbms_sql.column_value(c,1,install_group_num);
85
86 -- Process the install group
87
88 -- get the APPS account name ( apps[<IGN>][_<aol_schema>] )
89 ad_apps_private.get_apps_schema_name(install_group_num, aol_schema,
90 apps_schema, apps_schema_mls);
91
92 -- create the MLS APPS schema
93 do_create_mls_schema(install_group_num, aol_schema,
94 apps_schema, apps_schema_mls, invoker_mode);
95
96 else
97 -- no more product groups to process
98 dbms_sql.close_cursor(c);
99 exit;
100 end if;
101 end loop; -- loop over all product groups
102 exception
103 when others then
104 dbms_sql.close_cursor(c);
105 ad_apps_private.error_buf := 'statement='||
106 statement||':'||
107 ad_apps_private.error_buf;
108 raise;
109 end;
110
111 end if;
112
113 exception
114 when others then
115 ad_apps_private.error_buf := 'create_mls_schema('||aol_schema||
116 ', '||invoker_mode||'):'||
117 ad_apps_private.error_buf;
118 raise;
119
120 end create_mls_schema;
121
122
123 procedure build_lang_decode
124 (aol_schema in varchar2)
125 is
126 c integer;
127 rows_processed integer;
128 statement varchar2(500);
129 language_name varchar2(30);
130 attribute_column_name varchar2(30);
131 lset_number number;
132 ldecode_1 varchar2(2000);
133 ldecode_2 varchar2(2000);
134
135 begin
136 -- only do the work if it hasn't been done before
137 if ad_mls.mls_decode_1 is null then
138
139 c := dbms_sql.open_cursor;
140
141
142 -- Bug 3611969 : FIXED FILE.SQL.35 GSCC WARNINGS
143 -- sraghuve (07/05/2004)
144
145 ldecode_1 := 'nvl(decode(substr(userenv
146 (''LANGUAGE''),1,instr(userenv
147 (''LANGUAGE''),''_'')-1),';
148
149 ldecode_2 := 'nvl(decode(substr(userenv
150 (''LANGUAGE''),1,instr(userenv
151 (''LANGUAGE''),''_'')-1),';
152
153 -- Only select languages that are not the base language
154 -- Since the base language will be in the original column
155 statement := 'select language, attribute_column_name, '||
156 'translated_column_number from '||
157 aol_schema||'.ak_language_attribute_xrefs '||
158 'where language not in (select nls_language '||
159 'from '||aol_schema||'.fnd_languages '||
160 'where installed_flag = ''B'') ';
161 dbms_sql.parse(c, statement, dbms_sql.native);
162 dbms_sql.define_column(c,1,language_name,30);
163 dbms_sql.define_column(c,2,attribute_column_name,30);
164 dbms_sql.define_column(c,3,lset_number);
165 rows_processed := dbms_sql.execute(c);
166
167 loop
168 if dbms_sql.fetch_rows(c) > 0 then
169 dbms_sql.column_value(c,1,language_name);
170 dbms_sql.column_value(c,2,attribute_column_name);
171 dbms_sql.column_value(c,3,lset_number);
172 if lset_number = 1 then
173 ldecode_1 := ldecode_1 || '''' || language_name ||
174 ''', "' || attribute_column_name || '", ';
175 else
176 ldecode_2 := ldecode_2 || '''' || language_name ||
177 ''', "' || attribute_column_name || '", ';
178 end if;
179 else
180 -- no more records
181 dbms_sql.close_cursor(c);
182 exit;
183 end if;
184 end loop;
185
186 ad_mls.mls_decode_1 := ldecode_1;
187 ad_mls.mls_decode_2 := ldecode_2;
188 else
189 null;
190 end if;
191
192 exception
193 when others then
194 dbms_sql.close_cursor(c);
195 ad_apps_private.error_buf := 'build_lang_decode('||aol_schema||
196 '):'||ad_apps_private.error_buf;
197 raise;
198 end build_lang_decode;
199
200
201 procedure build_mls_column_list
202 (table_name in varchar2,
203 table_owner in varchar2,
204 aol_schema in varchar2,
205 view_column_list out nocopy varchar2,
206 select_list out nocopy varchar2)
207 is
208 c integer;
209 rows_processed integer;
210 statement varchar2(32000);
211 column_name varchar2(30);
212 mlsized varchar2(1);
213 translated_column_number number;
214 lview_column_list varchar2(20000) := null;
215 lselect_list varchar2(20000) := null;
216 counter number := 1;
217 begin
218 -- get value for mls_decode's
219 if ad_mls.mls_decode_1 is null then
220 build_lang_decode (aol_schema);
221 end if;
222
223 c := dbms_sql.open_cursor;
224 statement :=
225 'select dtc.column_name, '||
226 ' decode(atc.column_name,'''',''N'',''Y'') mlsized, '||
227 ' translated_column_number '||
228 'from all_tab_columns dtc, '||
229 aol_schema||'.ak_translated_columns atc '||
230 'where dtc.table_name = upper(:table_name) '||
231 'and nvl(atc.enabled_flag,''Y'') = ''Y'' '||
232 'and dtc.owner = upper(:table_owner) '||
233 'and atc.table_name (+) = dtc.table_name '||
234 'and atc.column_name (+) = dtc.column_name '||
235 'order by dtc.column_id ';
236 dbms_sql.parse(c, statement, dbms_sql.native);
237 dbms_sql.define_column(c,1,column_name,30);
238 dbms_sql.define_column(c,2,mlsized,1);
239 dbms_sql.define_column(c,3,translated_column_number);
240 dbms_sql.bind_variable(c,'table_name',table_name);
241 dbms_sql.bind_variable(c,'table_owner',table_owner);
242 rows_processed := dbms_sql.execute(c);
243 loop
244 if dbms_sql.fetch_rows(c) > 0 then
245
246 -- only add commas between columns
247 if counter > 1 then
248 lview_column_list := lview_column_list || ', ';
249 lselect_list := lselect_list || ', ';
250 end if;
251 counter := counter + 1;
252
253 dbms_sql.column_value(c,1,column_name);
254 dbms_sql.column_value(c,2,mlsized);
255 dbms_sql.column_value(c,3,translated_column_number);
256 if mlsized = 'Y' then
257 if translated_column_number = 1 then
258 lselect_list := lselect_list || ad_mls.mls_decode_1 ||
259 '"'||column_name || '"), "' || column_name ||'")' ;
260 lview_column_list := lview_column_list ||'"'|| column_name||'"';
261 elsif translated_column_number = 2 then
262 lselect_list := lselect_list || ad_mls.mls_decode_2 ||
263 '"'||column_name || '"), "' || column_name ||'")' ;
264 lview_column_list := lview_column_list ||'"'|| column_name||'"';
265 else
266 lselect_list := lselect_list||'"'|| column_name||'"';
267 lview_column_list := lview_column_list ||'"'|| column_name||'"';
268 end if;
269 else
270 lselect_list := lselect_list ||'"'|| column_name||'"';
271 lview_column_list := lview_column_list ||'"'|| column_name||'"';
272 end if;
273
274 else
275 -- no more records
276 dbms_sql.close_cursor(c);
277 exit;
278 end if;
279 end loop;
280 view_column_list := '('|| lview_column_list ||')';
281 select_list := lselect_list;
282
283 exception
284 when others then
285 dbms_sql.close_cursor(c);
286 ad_apps_private.error_buf := 'build_mls_column_list('||
287 ','||table_name||','||table_owner||','||aol_schema||
288 lview_column_list||','||lselect_list||
289 '):'||ad_apps_private.error_buf;
290 raise;
291 end;
292
293 procedure do_create_mls_schema
294 (install_group_num in number,
295 aol_schema in varchar2,
296 apps_schema in varchar2,
297 apps_mls_schema in varchar2,
298 invoker_mode in varchar2)
299 is
300
301 begin
302 -- Step 1) Check for existence of apps_mls_schema
303 if not ad_apps_private.check_if_schema_exists(apps_mls_schema) then
304 -- Error out if it doesn't exist
305 raise_application_error(-20000,'The schema '||
306 upper(apps_mls_schema)||' does not exist.');
307 end if;
308
309 -- Step 2) Check for existence of APPS*DDL in apps_schema and
310 -- apps_mls_schema;
311 ad_apps_private.check_for_apps_ddl(apps_schema);
312 ad_apps_private.check_for_apps_ddl(apps_mls_schema);
313
314 -- Step 4) Create grants/synonyms for tables and seqs to APPS
315 ad_apps_private.create_synonyms(apps_schema, apps_mls_schema,
316 apps_schema);
317
318 -- Step 5) Create odd synonyms in MLS account
319 ad_apps_private.copy_odd_synonyms(apps_schema, apps_mls_schema);
320
321 -- Step 6) Create MLS views in MLS schema
322 --
323 -- This doesn't work, as ak_translated_columns is obsolete in Rel 11.5
324 -- Leave the call here, as it may need to be fixed if we will support
325 -- the consulting MLS solution in Rel 11.5. Better to get an error here
326 -- and then know we need to fix something than just comment out the call
327 -- and get the wrong behavior silently.
328 --
329
330 ad_apps_private.create_special_views(install_group_num,
331 aol_schema, apps_mls_schema, TRUE);
332
333
334 -- Step 7)Copy views from APPS to MLS
335 ad_apps_private.copy_views(aol_schema, apps_schema, apps_mls_schema);
336
337 -- Step 8)Copy stored programs from APPS to MLS
338
339 if invoker_mode = 'FALSE' then
340 ad_apps_private.copy_stored_progs(apps_schema, apps_mls_schema,
341 'A', null,'none');
342 else
343 ad_invoker.invoker_mrc_grants(apps_schema, apps_mls_schema);
344 end if;
345 -- end if Invoker's Rights mode
346
347 -- Step 9 Compile invalid objects in appsuser schema
348 -- ignoring all compilation failures
349 ad_inst.compile_schema(apps_mls_schema);
350
351 exception
352 when others then
353 ad_apps_private.error_buf := 'do_create_mls_schema('||aol_schema||', '
354 ||apps_schema||', '||apps_mls_schema||', '||
355 invoker_mode||'):'||ad_apps_private.error_buf;
356 raise;
357
358 end do_create_mls_schema;
359
360
361 end ad_mls;