[Home] [Help]
PACKAGE BODY: APPS.EDW_SEC_UTIL
Source
1 package body edw_sec_util as
2 /* $Header: EDWSUTLB.pls 115.5 2002/12/06 02:57:37 tiwang noship $*/
3
4 -- This procedure refreshes security metadata tables from owb repository
5
6 PROCEDURE refresh_sec_metadata(Errbuf out NOCOPY varchar2, Retcode out NOCOPY varchar2) IS
7
8 v_Errorcode number;
9 v_ErrorText varchar2(200);
10
11 g_conc_program_id number;
12
13 x_object_name varchar2(50) := 'EDW_SEC_UTIL.REFRESH_SEC_METADATA';
14 x_object_type varchar2(30) := 'Security Procedure';
15
16 x_message varchar2(2000);
17
18 BEGIN
19
20 Errbuf := NULL;
21 Retcode := 0;
22
23 g_conc_program_id := FND_GLOBAL.conc_request_id;
24
25
26 -- Call procedure to refresh EDW metadata tables
27
28 edw_metadata_refresh.refresh_metadata_tables(Errbuf ,retcode);
29
30 -- First delete data from tables
31
32 delete from edw_sec_dim_info_t;
33 delete from edw_sec_fact_info_t;
34 delete from edw_sec_lvl_info_t;
35 delete from edw_sec_itemset_info_t;
36
37
38 -- Now populate tables from owb repository views
39
40 insert into edw_sec_dim_info_t
41 (dim_id,
42 dim_name,
43 dim_long_name,
44 table_name,
45 lowest_level_col_name,
46 context_name)
47 select
48 dim_id,
49 dim_name,
50 dim_long_name,
51 table_name,
52 lowest_level_col_name,
53 context_name
54 from edw_sec_dim_info_v;
55
56 insert into edw_sec_fact_info_t
57 (fact_id,
58 fact_name,
59 fact_long_name,
60 dim_id,
61 fk_col_name)
62 select
63 fact_id,
64 fact_name,
65 fact_long_name,
66 dim_id,
67 fk_col_name
68 from edw_sec_fact_info_v;
69
70 insert into edw_sec_lvl_info_t
71 (dim_id,
72 level_id,
73 level_name,
74 level_long_name,
75 star_level_name_col_name)
76 select
77 dim_id,
78 level_id,
79 level_name,
80 level_long_name,
81 star_level_name_col_name
82 from edw_sec_lvl_info_v;
83
84 insert into edw_sec_itemset_info_t
85 (fact_id,
86 fact_name,
87 fact_long_name,
88 itemset_name,
89 fk_col_name)
90 select
91 fact_id,
92 fact_name,
93 fact_long_name,
94 itemset_name,
95 fk_col_name
96 from edw_sec_itemset_info_v;
97
98 COMMIT;
99
100
101 -- Call procedure to upgrade EDW security access setup data
102
103 edw_sec_util.upgrade_sec_access_data;
104
105
106
107 EXCEPTION
108
109 WHEN OTHERS THEN
110
111 v_ErrorCode := SQLCODE;
112 v_ErrorText := SUBSTR(SQLERRM, 1, 200);
113
114 -- Log error message
115
116 x_message := 'Oracle error occured.
117 Errorcode is : ' || v_ErrorCode || ' and Errortext is : ' || v_ErrorText ;
118
119
120 edw_sec_util.log_error(x_object_name, x_object_type, null, g_conc_program_id, x_message);
121
122 Errbuf := v_ErrorText;
123 Retcode := SQLCODE;
124
125
126 END refresh_sec_metadata;
127
128
129
130 PROCEDURE log_error(x_object_name varchar2, x_object_type varchar2, x_resp_id number, x_conc_id number, x_message varchar2) IS
131
132 BEGIN
133
134 -- Log error message into edw_error_log table
135
136 insert into edw_error_log
137 (object_name, object_type, resp_id, concurrent_id, message,
138 last_update_date, last_updated_by, creation_date, created_by, last_update_login)
139 values
140 (x_object_name, x_object_type, x_resp_id, x_conc_id, x_message,
141 sysdate, 0, sysdate, 0, 0);
142 commit;
143
144 EXCEPTION
145
146 -- What do we do if error logging fails ..??
147 -- If we raise,it may go into infinite loop as outer procedure will again try to log error
148
149 WHEN OTHERS THEN
150 null;
151
152 END log_error;
153
154
155 PROCEDURE upgrade_sec_access_data IS
156
157 -- This procedure upgrades data in security access table(edw_sec_dim_access)
158
159 v_Errorcode number;
160 v_ErrorText varchar2(200);
161
162 -- g_conc_program_id number;
163
164 x_object_name varchar2(50) := 'EDW_SEC_UTIL.UPGRADE_SEC_ACCESS_DATA';
165 x_object_type varchar2(30) := 'Security Procedure';
166
167 x_message varchar2(2000);
168
169 x_dim_id edw_sec_dim_access.dim_id%TYPE;
170 x_level_id edw_sec_dim_access.level_id%TYPE;
171
172
173 cursor dim_cursor is
174 select distinct dim_short_name from edw_sec_dim_access edw
175 where dim_id <>
176 (
177 select
178 dim.dim_id
179 from
180 edw_sec_dim_info_t dim
181 WHERE
182 edw.dim_short_name = dim.dim_name
183 )
184 ;
185 cursor level_cursor is
186 select distinct level_short_name from edw_sec_dim_access edw
187 where level_id <>
188 (select
189 lvl.level_id
190 from
191 edw_sec_lvl_info_t lvl
192 WHERE
193 edw.level_short_name = lvl.level_name
194 and edw.dim_id = lvl.dim_id
195 );
196
197
198 dim_rec dim_cursor%ROWTYPE;
199 level_rec level_cursor%ROWTYPE;
200
201
202
203 BEGIN
204
205
206 FOR dim_rec IN dim_cursor LOOP
207
208 select dim_id into x_dim_id
209 from edw_sec_dim_info_v
210 where dim_name = dim_rec.dim_short_name;
211
212 update edw_sec_dim_access
213 set dim_id = x_dim_id
214 where dim_short_name = dim_rec.dim_short_name;
215
216 END LOOP;
217
218
219
220 FOR level_rec IN level_cursor LOOP
221
222 select level_id into x_level_id
223 from edw_sec_lvl_info_v
224 where level_name = level_rec.level_short_name;
225
226 update edw_sec_dim_access
227 set level_id = x_level_id
228 where level_short_name = level_rec.level_short_name;
229
230 END LOOP;
231
232 COMMIT;
233
234 EXCEPTION
235
236 WHEN OTHERS THEN
237
238 v_ErrorCode := SQLCODE;
239 v_ErrorText := SUBSTR(SQLERRM, 1, 200);
240
241 -- Log error message
242
243 x_message := 'Oracle error occured.
244 Errorcode is : ' || v_ErrorCode || ' and Errortext is : ' || v_ErrorText ;
245
246
247 edw_sec_util.log_error(x_object_name, x_object_type, null, null, x_message);
248
249 RAISE;
250
251 END upgrade_sec_access_data;
252
253
254
255
256
257 END edw_sec_util;