1 package body AK_SECURITY_GRP as
2 /* $Header: akdgsecb.pls 120.2 2005/09/15 22:26:42 tshort ship $ */
3
4 --=======================================================
5 -- Procedure DOWNLOAD_RESP
6 --
7 -- Usage Group API for downloading attributes
8 --
9 -- Desc This API first write out standard loader
10 -- file header for attributes to a flat file.
11 -- Then it calls the private API to extract the
12 -- attributes selected by application ID or by
13 -- key values from the database to the output file.
14 --
15 -- Results The API returns the standard p_return_status parameter
16 -- indicating one of the standard return statuses :
17 -- * Unexpected error
18 -- * Error
19 -- * Success
20 -- Parameters p_directory
21 -- p_nls_language : IN optional
22 -- NLS language for database. If none if given,
23 -- the current NLS language will be used.
24 --
25 -- One of the following three parameters must be given:
26 --
27 -- p_application_id : IN optional
28 -- If given, all attributes for this application ID
29 -- will be written to the output file.
30 -- p_application_short_name : IN optional
31 -- If given, all attributes for this application short
32 -- name will be written to the output file.
33 -- Application short name will be ignored if an
34 -- application ID is given.
35 -- p_excluded_pk_tbl : IN optional
36 -- If given, only excluded_items whose key values are
37 -- included in this table will be written to the
38 -- output file.
39 -- p_resp_pk_tbl : IN optional
40 -- If given, only resp_sec_attributes whose key values are
41 -- included in this table will be written to the
42 -- output file.
43 --
44 --
45 -- Version Initial version number = 1.0
46 -- History Current version number = 1.0
47 --=======================================================
48 procedure DOWNLOAD_RESP (
49 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
50 p_api_version_number IN NUMBER,
51 p_init_msg_tbl IN BOOLEAN := FALSE,
52 p_msg_count OUT NOCOPY NUMBER,
53 p_msg_data OUT NOCOPY VARCHAR2,
54 p_return_status OUT NOCOPY VARCHAR2,
55 p_nls_language IN VARCHAR2 := FND_API.G_MISS_CHAR,
56 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
57 p_application_short_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
58 p_excluded_pk_tbl IN AK_SECURITY_PUB.Resp_PK_Tbl_Type :=
59 AK_SECURITY_PUB.G_MISS_RESP_PK_TBL,
60 p_resp_pk_tbl IN AK_SECURITY_PUB.Resp_PK_Tbl_Type :=
61 AK_SECURITY_PUB.G_MISS_RESP_PK_TBL
62 ) is
63 l_api_version_number CONSTANT number := 1.0;
64 l_api_name CONSTANT varchar2(30) := 'Download Resp';
65 l_application_id number;
66 l_index NUMBER;
67 l_index_out NUMBER;
68 l_nls_language VARCHAR2(30);
69 l_return_status varchar2(1);
70 begin
71 --
72 -- Check verion number
73 --
74 IF NOT FND_API.Compatible_API_Call (
75 l_api_version_number, p_api_version_number, l_api_name,
76 G_PKG_NAME) then
77 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
78 return;
79 END IF;
80
81 -- Initialize the message table if requested.
82
83 if p_init_msg_tbl then
84 FND_MSG_PUB.initialize;
85 end if;
86
87 savepoint Start_download;
88 if (AK_DOWNLOAD_GRP.G_WRITE_HEADER) then
89 --
90 -- Call private download procedure to verify parameters,
91 -- load application ID, and write header information such
92 -- as nls_language and codeset to data file.
93 --
94 AK_ON_OBJECTS_PVT.download_header(
95 p_validation_level => p_validation_level,
96 p_api_version_number => 1.0,
97 p_return_status => l_return_status,
98 p_nls_language => p_nls_language,
99 p_application_id => p_application_id,
100 p_application_short_name => p_application_short_name,
101 p_table_size => p_resp_pk_tbl.count,
102 p_download_by_object => AK_ON_OBJECTS_PVT.G_SECURITY,
103 p_nls_language_out => l_nls_language,
104 p_application_id_out => l_application_id
105 );
106 else
107 l_application_id := p_application_id;
108 select userenv('LANG') into l_nls_language
109 from dual;
110 end if;
111
112 --
113 -- If API call returns with an error status...
114 --
115 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
116 (l_return_status = FND_API.G_RET_STS_ERROR) then
117 RAISE FND_API.G_EXC_ERROR;
118 end if;
119
120 --
121 -- - call the download procedure for attributes to retrieve the
122 -- selected attributes from the database into a table of type
123 -- AK_ON_OBJECTS_PUB.Buffer_Tbl_Type.
124 --
125 -- dbms_output.put_line('Start downloading excluded items');
126
127 AK_SECURITY_PVT.DOWNLOAD_EXCLUDED(
128 p_validation_level => p_validation_level,
129 p_api_version_number => 1.0,
130 p_return_status => l_return_status,
131 p_application_id => l_application_id,
132 p_excluded_pk_tbl => p_excluded_pk_tbl,
133 p_nls_language => l_nls_language
134 );
135
136 --
137 -- If download call returns with an error status or
138 -- download failed to retrieve any information from the database..
139 --
140 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
141 (l_return_status = FND_API.G_RET_STS_ERROR) then
142 RAISE FND_API.G_EXC_ERROR;
143 end if;
144
145 AK_SECURITY_PVT.DOWNLOAD_RESP_SEC(
146 p_validation_level => p_validation_level,
147 p_api_version_number => 1.0,
148 p_return_status => l_return_status,
149 p_application_id => l_application_id,
150 p_resp_pk_tbl => p_resp_pk_tbl,
151 p_nls_language => l_nls_language
152 );
153
154 --
155 -- If download call returns with an error status or
156 -- download failed to retrieve any information from the database..
157 --
158 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
159 (l_return_status = FND_API.G_RET_STS_ERROR) then
160 RAISE FND_API.G_EXC_ERROR;
161 end if;
162
163 p_return_status := FND_API.G_RET_STS_SUCCESS;
164
165 FND_MSG_PUB.Count_And_Get (
166 p_count => p_msg_count,
167 p_data => p_msg_data);
168
169 EXCEPTION
170 WHEN FND_API.G_EXC_ERROR THEN
171 p_return_status := FND_API.G_RET_STS_ERROR;
172 FND_MSG_PUB.Count_And_Get (
173 p_count => p_msg_count,
174 p_data => p_msg_data);
175 WHEN OTHERS THEN
176 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
177 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
178 SUBSTR (SQLERRM, 1, 240) );
179 FND_MSG_PUB.Add;
180 FND_MSG_PUB.Count_And_Get (
181 p_count => p_msg_count,
182 p_data => p_msg_data);
183
184 end DOWNLOAD_RESP;
185
186 --=======================================================
187 -- Procedure CREATE_EXCLUDED_ITEM
188 --
189 -- Usage Group API for creating an attribute
190 --
191 -- Desc Calls the private API to creates an attribute
192 -- using the given info
193 --
194 -- Results The API returns the standard p_return_status parameter
195 -- indicating one of the standard return statuses :
196 -- * Unexpected error
197 -- * Error
198 -- * Success
199 -- Parameters Attribute columns
200 --
201 -- Version Initial version number = 1.0
202 -- History Current version number = 1.0
203 --=======================================================
204 procedure CREATE_EXCLUDED_ITEM (
205 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
206 p_api_version_number IN NUMBER,
207 p_init_msg_tbl IN BOOLEAN := FALSE,
208 p_msg_count OUT NOCOPY NUMBER,
209 p_msg_data OUT NOCOPY VARCHAR2,
210 p_return_status OUT NOCOPY VARCHAR2,
211 p_responsibility_id IN NUMBER,
212 p_resp_application_id IN NUMBER,
213 p_attribute_code IN VARCHAR2,
214 p_attribute_application_id IN NUMBER,
215 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
216 p_creation_date IN DATE := FND_API.G_MISS_DATE,
217 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
218 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
219 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM
220 ) is
221 l_api_version_number CONSTANT number := 1.0;
222 l_api_name CONSTANT varchar2(30) := 'Create_Excluded_Items';
223 l_return_status VARCHAR2(1);
224 begin
225 /* Check API version number */
226 IF NOT FND_API.Compatible_API_Call (
227 l_api_version_number, p_api_version_number, l_api_name,
228 G_PKG_NAME) then
229 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
230 return;
231 END IF;
232
233 -- Initialize the message table if requested.
234
235 if p_init_msg_tbl then
236 FND_MSG_PUB.initialize;
237 end if;
238
239 savepoint start_create_excluded_item;
240
241 --
242 -- Call private procedure to create an excluded item
243 --
244 AK_SECURITY_PVT.CREATE_EXCLUDED_ITEM(
245 p_validation_level => p_validation_level,
246 p_api_version_number => 1.0,
247 p_msg_count => p_msg_count,
248 p_msg_data => p_msg_data,
249 p_return_status => l_return_status,
250 p_responsibility_id => p_responsibility_id,
251 p_resp_application_id => p_resp_application_id,
252 p_attribute_code => p_attribute_code,
253 p_attribute_application_id => p_attribute_application_id,
254 p_created_by => p_created_by,
255 p_creation_date => p_creation_date,
256 p_last_updated_by => p_last_updated_by,
257 p_last_update_date => p_last_update_date,
258 p_last_update_login => p_lasT_update_login
259 );
260
261 --
262 -- If API call returns with an error status...
263 --
264 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
265 (l_return_status = FND_API.G_RET_STS_ERROR) then
266 RAISE FND_API.G_EXC_ERROR;
267 end if;
268
269 p_return_status := FND_API.G_RET_STS_SUCCESS;
270
271 EXCEPTION
272 WHEN FND_API.G_EXC_ERROR THEN
273 p_return_status := FND_API.G_RET_STS_ERROR;
274 rollback to start_create_excluded_item;
275 WHEN OTHERS THEN
276 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
277 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
278 SUBSTR (SQLERRM, 1, 240) );
279 FND_MSG_PUB.Add;
280 rollback to start_create_excluded_item;
281 end CREATE_EXCLUDED_ITEM;
282
283 --=======================================================
284 -- Procedure CREATE_RESP_SECURITY_ATTR
285 --
286 -- Usage Group API for creating an attribute
287 --
288 -- Desc Calls the private API to creates an attribute
289 -- using the given info
290 --
291 -- Results The API returns the standard p_return_status parameter
292 -- indicating one of the standard return statuses :
293 -- * Unexpected error
294 -- * Error
295 -- * Success
296 -- Parameters Attribute columns
297 --
298 -- Version Initial version number = 1.0
299 -- History Current version number = 1.0
300 --=======================================================
301 procedure CREATE_RESP_SECURITY_ATTR (
302 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
303 p_api_version_number IN NUMBER,
304 p_init_msg_tbl IN BOOLEAN := FALSE,
305 p_msg_count OUT NOCOPY NUMBER,
306 p_msg_data OUT NOCOPY VARCHAR2,
307 p_return_status OUT NOCOPY VARCHAR2,
308 p_responsibility_id IN NUMBER,
309 p_resp_application_id IN NUMBER,
310 p_attribute_code IN VARCHAR2,
311 p_attribute_application_id IN NUMBER,
312 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
313 p_creation_date IN DATE := FND_API.G_MISS_DATE,
314 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
315 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
316 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM
317 ) is
318 l_api_version_number CONSTANT number := 1.0;
319 l_api_name CONSTANT varchar2(30) := 'Create_Resp_Sec_Attr';
320 l_return_status VARCHAR2(1);
321 begin
322 /* Check API version number */
323 IF NOT FND_API.Compatible_API_Call (
324 l_api_version_number, p_api_version_number, l_api_name,
325 G_PKG_NAME) then
326 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
327 return;
328 END IF;
329
330 -- Initialize the message table if requested.
331
332 if p_init_msg_tbl then
333 FND_MSG_PUB.initialize;
334 end if;
335
336 savepoint start_create_resp_sec_attr;
337
338 --
339 -- Call private procedure to create a resp security attribute
340 --
341 AK_SECURITY_PVT.CREATE_RESP_SECURITY_ATTR(
342 p_validation_level => p_validation_level,
343 p_api_version_number => 1.0,
344 p_msg_count => p_msg_count,
345 p_msg_data => p_msg_data,
346 p_return_status => l_return_status,
347 p_responsibility_id => p_responsibility_id,
348 p_resp_application_id => p_resp_application_id,
349 p_attribute_code => p_attribute_code,
350 p_attribute_application_id => p_attribute_application_id,
351 p_created_by => p_created_by,
352 p_creation_date => p_creation_date,
353 p_last_updated_by => p_last_updated_by,
354 p_last_update_date => p_last_update_date,
355 p_last_update_login => p_lasT_update_login
356 );
357
358 --
359 -- If API call returns with an error status...
360 --
361 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
362 (l_return_status = FND_API.G_RET_STS_ERROR) then
363 RAISE FND_API.G_EXC_ERROR;
364 end if;
365
366 p_return_status := FND_API.G_RET_STS_SUCCESS;
367
368 EXCEPTION
369 WHEN FND_API.G_EXC_ERROR THEN
370 p_return_status := FND_API.G_RET_STS_ERROR;
371 rollback to start_create_resp_sec_attr;
372 WHEN OTHERS THEN
373 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
374 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
375 SUBSTR (SQLERRM, 1, 240) );
376 FND_MSG_PUB.Add;
377 rollback to start_create_resp_sec_attr;
378 end CREATE_RESP_SECURITY_ATTR;
379
380 end AK_SECURITY_grp;