[Home] [Help]
PACKAGE BODY: APPS.EDW_SEC_REF
Source
1 package body edw_sec_ref as
2 /* $Header: EDWSREFB.pls 115.2 2002/12/06 02:55:22 tiwang noship $*/
3
4 -- This procedure disables security for a reference
5
6 PROCEDURE disable_security
7 (application_short_name varchar2, responsibility_key varchar2,
8 fact_physical_name varchar2, fk_column_physical_name varchar2)
9 IS
10
11 x_object_name varchar2(30) := 'EDW_SEC_REF.DISABLE_SECURITY';
12 x_object_type varchar2(30) := 'Disable Security Procedure';
13
14 v_Errorcode number;
15 v_ErrorText varchar2(200);
16
17 x_message varchar2(2000);
18
19
20 x_appl_id number;
21 x_resp_id number;
22 x_fact_id number;
23 x_dim_id number;
24
25 x_rec_count number;
26
27 x_app_name varchar2(50);
28 x_resp_key varchar2(30);
29
30 x_dim_name varchar2(255);
31
32
33 BEGIN
34
35 x_app_name := application_short_name;
36 x_resp_key :=responsibility_key;
37
38
39 select application_id into x_appl_id from fnd_application_vl
40 where application_short_name = x_app_name;
41
42 select responsibility_id into x_resp_id from fnd_responsibility_vl
43 where responsibility_key = x_resp_key
44 and application_id = x_appl_id;
45
46 select distinct fact_id into x_fact_id from edw_sec_fact_info_t
47 where fact_name = fact_physical_name;
48
49 select dim_id into x_dim_id from edw_sec_fact_info_t
50 where fact_name = fact_physical_name
51 and fk_col_name = fk_column_physical_name;
52
53 select dim_name into x_dim_name from edw_sec_dim_info_t
54 where dim_id = x_dim_id;
55
56
57 -- Check if row already exists
58
59 select count(*) into x_rec_count from edw_sec_ref_info_t
60 where appl_id = x_appl_id
61 and resp_id = x_resp_id
62 and fact_id = x_fact_id
63 and fk_col_name = fk_column_physical_name;
64
65
66 IF (x_rec_count = 0) THEN
67
68 -- Insert Row
69
70 Insert into edw_sec_ref_info_t
71 (appl_id,
72 resp_id,
73 fact_id,
74 fact_name,
75 dim_id,
76 dim_name,
77 fk_col_name)
78 values
79 (x_appl_id,
80 x_resp_id,
81 x_fact_id,
82 fact_physical_name,
83 x_dim_id,
84 x_dim_name,
85 fk_column_physical_name);
86
87 commit;
88
89 END IF;
90
91
92 EXCEPTION
93
94 WHEN OTHERS THEN
95 RAISE;
96 /*
97 v_ErrorCode := SQLCODE;
98 v_ErrorText := SUBSTR(SQLERRM, 1, 200);
99
100
101 -- Log error message into edw_error_log table
102
103 x_message := 'Oracle error occured.
104 Errorcode is : ' || v_ErrorCode || ' and Errortext is : ' || v_ErrorText ;
105
106 edw_sec_util.log_error(x_object_name, x_object_type, null, null, x_message);
107
108 commit;
109 */
110
111
112 END disable_security;
113
114
115
116
117
118
119
120 -- This procedure enables security for a reference
121
122 PROCEDURE enable_security
123 (application_short_name varchar2, responsibility_key varchar2,
124 fact_physical_name varchar2, fk_column_physical_name varchar2)
125 IS
126
127 x_object_name varchar2(30) := 'EDW_SEC_REF.ENABLE_SECURITY';
128 x_object_type varchar2(30) := 'Enable Security Procedure';
129
130 v_Errorcode number;
131 v_ErrorText varchar2(200);
132
133 x_message varchar2(2000);
134
135
136 x_appl_id number;
137 x_resp_id number;
138 x_fact_id number;
139 x_dim_id number;
140
141 x_rec_count number;
142
143 x_app_name varchar2(50);
144 x_resp_key varchar2(30);
145
146 BEGIN
147
148 x_app_name := application_short_name;
149 x_resp_key :=responsibility_key;
150
151
152 select application_id into x_appl_id from fnd_application_vl
153 where application_short_name = x_app_name;
154
155 select responsibility_id into x_resp_id from fnd_responsibility_vl
156 where responsibility_key = x_resp_key
157 and application_id = x_appl_id;
158
159 select distinct fact_id into x_fact_id from edw_sec_fact_info_t
160 where fact_name = fact_physical_name;
161
162 select dim_id into x_dim_id from edw_sec_fact_info_t
163 where fact_name = fact_physical_name
164 and fk_col_name = fk_column_physical_name;
165
166 -- Delete Row
167
168 Delete from edw_sec_ref_info_t
169 where appl_id = x_appl_id
170 and resp_id = x_resp_id
171 and fact_id = x_fact_id
172 and fk_col_name = fk_column_physical_name;
173
174 commit;
175
176
177 EXCEPTION
178
179 WHEN OTHERS THEN
180 RAISE;
181 /*
182 v_ErrorCode := SQLCODE;
183 v_ErrorText := SUBSTR(SQLERRM, 1, 200);
184
185
186 -- Log error message into edw_error_log table
187
188 x_message := 'Oracle error occured.
189 Errorcode is : ' || v_ErrorCode || ' and Errortext is : ' || v_ErrorText ;
190
191 edw_sec_util.log_error(x_object_name, x_object_type, null, null, x_message);
192
193 commit;
194 */
195
196
197 END enable_security;
198
199
200
201 END edw_sec_ref;