1 package body AK_ATTRIBUTE_GRP as
2 /* $Header: akdgattb.pls 120.2 2005/09/15 22:26:30 tshort ship $ */
3
4 --=======================================================
5 -- Procedure CREATE_ATTRIBUTE
6 --
7 -- Usage Group API for creating an attribute
8 --
9 -- Desc Calls the private API to creates an attribute
10 -- using the given info
11 --
12 -- Results The API returns the standard p_return_status parameter
13 -- indicating one of the standard return statuses :
14 -- * Unexpected error
15 -- * Error
16 -- * Success
17 -- Parameters Attribute columns
18 --
19 -- Version Initial version number = 1.0
20 -- History Current version number = 1.0
21 --=======================================================
22 procedure CREATE_ATTRIBUTE (
23 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
24 p_api_version_number IN NUMBER,
25 p_init_msg_tbl IN BOOLEAN := FALSE,
26 p_msg_count OUT NOCOPY NUMBER,
27 p_msg_data OUT NOCOPY VARCHAR2,
28 p_return_status OUT NOCOPY VARCHAR2,
29 p_attribute_application_id IN NUMBER,
30 p_attribute_code IN VARCHAR2,
31 p_attribute_label_length IN NUMBER := FND_API.G_MISS_NUM,
32 p_attribute_value_length IN NUMBER := FND_API.G_MISS_NUM,
33 p_bold IN VARCHAR2,
34 p_italic IN VARCHAR2,
35 p_vertical_alignment IN VARCHAR2,
36 p_horizontal_alignment IN VARCHAR2,
37 p_data_type IN VARCHAR2,
38 p_upper_case_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
39 p_default_value_varchar2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
40 p_default_value_number IN NUMBER := FND_API.G_MISS_NUM,
41 p_default_value_date IN DATE := FND_API.G_MISS_DATE,
42 p_lov_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
43 p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
44 p_item_style IN VARCHAR2,
45 p_display_height IN NUMBER := FND_API.G_MISS_NUM,
46 p_css_class_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
47 p_poplist_viewobject IN VARCHAR2 := FND_API.G_MISS_CHAR,
48 p_poplist_display_attr IN VARCHAR2 := FND_API.G_MISS_CHAR,
49 p_poplist_value_attr IN VARCHAR2 := FND_API.G_MISS_CHAR,
50 p_css_label_class_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
51 p_precision IN NUMBER := FND_API.G_MISS_NUM,
52 p_expansion IN NUMBER := FND_API.G_MISS_NUM,
53 p_als_max_length IN NUMBER := FND_API.G_MISS_NUM,
54 p_name IN VARCHAR2,
55 p_attribute_label_long IN VARCHAR2 := FND_API.G_MISS_CHAR,
56 p_attribute_label_short IN VARCHAR2 := FND_API.G_MISS_CHAR,
57 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR
58 ) is
59 l_api_version_number CONSTANT number := 1.0;
60 l_api_name CONSTANT varchar2(30) := 'Create_Attribute';
61 l_return_status VARCHAR2(1);
62 l_pass NUMBER := 2;
63 l_copy_redo_flag BOOLEAN := FALSE;
64 begin
65 /* Check API version number */
66 IF NOT FND_API.Compatible_API_Call (
67 l_api_version_number, p_api_version_number, l_api_name,
68 G_PKG_NAME) then
69 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
70 return;
71 END IF;
72
73 -- Initialize the message table if requested.
74
75 if p_init_msg_tbl then
76 FND_MSG_PUB.initialize;
77 end if;
78
79 savepoint start_create_attribute;
80
81 --
82 -- Call private procedure to create an attribute
83 --
84 AK_ATTRIBUTE_PVT.CREATE_ATTRIBUTE(
85 p_validation_level => p_validation_level,
86 p_api_version_number => 1.0,
87 p_msg_count => p_msg_count,
88 p_msg_data => p_msg_data,
89 p_return_status => l_return_status,
90 p_attribute_application_id => p_attribute_application_id,
91 p_attribute_code => p_attribute_code,
92 p_attribute_label_length => p_attribute_label_length,
93 p_attribute_value_length => p_attribute_value_length,
94 p_bold => p_bold,
95 p_italic => p_italic,
96 p_vertical_alignment => p_vertical_alignment,
97 p_horizontal_alignment => p_horizontal_alignment,
98 p_data_type => p_data_type,
99 p_upper_case_flag => p_upper_case_flag,
100 p_default_value_varchar2 => p_default_value_varchar2,
101 p_default_value_number => p_default_value_number,
102 p_default_value_date => p_default_value_date,
103 p_lov_region_application_id => p_lov_region_application_id,
104 p_lov_region_code => p_lov_region_code,
105 p_item_style => p_item_style,
106 p_display_height => p_display_height,
107 p_css_class_name => p_css_class_name,
108 p_poplist_viewobject => p_poplist_viewobject,
109 p_poplist_display_attr => p_poplist_display_attr,
110 p_poplist_value_attr => p_poplist_value_attr,
111 p_css_label_class_name => p_css_label_class_name,
112 p_precision => p_precision,
113 p_expansion => p_expansion,
114 p_als_max_length => p_als_max_length,
115 p_name => p_name,
116 p_attribute_label_long => p_attribute_label_long,
117 p_attribute_label_short => p_attribute_label_short,
118 p_description => p_description,
119 p_pass => l_pass,
120 p_copy_redo_flag => l_copy_redo_flag
121 );
122
123 --
124 -- If API call returns with an error status...
125 --
126 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
127 (l_return_status = FND_API.G_RET_STS_ERROR) then
128 RAISE FND_API.G_EXC_ERROR;
129 end if;
130
131 p_return_status := FND_API.G_RET_STS_SUCCESS;
132
133 EXCEPTION
134 WHEN FND_API.G_EXC_ERROR THEN
135 p_return_status := FND_API.G_RET_STS_ERROR;
136 rollback to start_create_attribute;
137 WHEN OTHERS THEN
138 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
139 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
140 SUBSTR (SQLERRM, 1, 240) );
141 FND_MSG_PUB.Add;
142 rollback to start_create_attribute;
143 end CREATE_ATTRIBUTE;
144
145 --=======================================================
146 -- Procedure DELETE_ATTRIBUTE
147 --
148 -- Usage Group API for deleting an attribute
149 --
150 -- Desc Calls the private API to deletes an attribute
151 -- with the given key value.
152 --
153 -- Results The API returns the standard p_return_status parameter
154 -- indicating one of the standard return statuses :
155 -- * Unexpected error
156 -- * Error
157 -- * Success
158 -- Parameters p_attribute_application_id : IN required
159 -- p_attribute_code : IN required
160 -- Key value of the attribute to be deleted.
161 -- p_delete_cascade : IN required
162 -- If p_delete_cascade flag is 'Y', also delete all
163 -- rows in other tables that references this attribute.
164 -- Otherwise, this attribute will not be deleted if there
165 -- are any other rows referencing it.
166 --
167 -- Version Initial version number = 1.0
168 -- History Current version number = 1.0
169 --=======================================================
170 procedure DELETE_ATTRIBUTE (
171 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
172 p_api_version_number IN NUMBER,
173 p_init_msg_tbl IN BOOLEAN := FALSE,
174 p_msg_count OUT NOCOPY NUMBER,
175 p_msg_data OUT NOCOPY VARCHAR2,
176 p_return_status OUT NOCOPY VARCHAR2,
177 p_attribute_application_id IN NUMBER,
178 p_attribute_code IN VARCHAR2,
179 p_delete_cascade IN VARCHAR2 := 'N'
180 ) is
181 l_api_version_number CONSTANT number := 1.0;
182 l_api_name CONSTANT varchar2(30) := 'Delete_Attribute';
183 l_return_status VARCHAR2(1);
184 begin
185 --
186 -- Check API version number
187 --
188 IF NOT FND_API.Compatible_API_Call (
189 l_api_version_number, p_api_version_number, l_api_name,
190 G_PKG_NAME) then
191 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
192 return;
193 END IF;
194
195 -- Initialize the message table if requested.
196
197 if p_init_msg_tbl then
198 FND_MSG_PUB.initialize;
199 end if;
200
201 savepoint start_delete_attribute;
202
203 --
204 -- Call private procedure to create an attribute
205 --
206 AK_ATTRIBUTE_PVT.DELETE_ATTRIBUTE(
207 p_validation_level => p_validation_level,
208 p_api_version_number => 1.0,
209 p_msg_count => p_msg_count,
210 p_msg_data => p_msg_data,
211 p_return_status => l_return_status,
212 p_attribute_application_id => p_attribute_application_id,
213 p_attribute_code => p_attribute_code,
214 p_delete_cascade => p_delete_cascade
215 );
216
217 --
218 -- If API call returns with an error status...
219 --
220 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
221 (l_return_status = FND_API.G_RET_STS_ERROR) then
222 RAISE FND_API.G_EXC_ERROR;
223 end if;
224
225 p_return_status := FND_API.G_RET_STS_SUCCESS;
226
227 EXCEPTION
228 WHEN FND_API.G_EXC_ERROR THEN
229 p_return_status := FND_API.G_RET_STS_ERROR;
230 rollback to start_delete_attribute;
231 WHEN OTHERS THEN
232 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
233 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
234 SUBSTR (SQLERRM, 1, 240) );
235 FND_MSG_PUB.Add;
236 rollback to start_delete_attribute;
237 end DELETE_ATTRIBUTE;
238
239 --=======================================================
240 -- Procedure DOWNLOAD_ATTRIBUTE
241 --
242 -- Usage Group API for downloading attributes
243 --
244 -- Desc This API first write out standard loader
245 -- file header for attributes to a flat file.
246 -- Then it calls the private API to extract the
247 -- attributes selected by application ID or by
248 -- key values from the database to the output file.
249 --
250 -- Results The API returns the standard p_return_status parameter
251 -- indicating one of the standard return statuses :
252 -- * Unexpected error
253 -- * Error
254 -- * Success
255 -- Parameters
256 -- p_nls_language : IN optional
257 -- NLS language for database. If none if given,
258 -- the current NLS language will be used.
259 --
260 -- One of the following three parameters must be given:
261 --
262 -- p_application_id : IN optional
263 -- If given, all attributes for this application ID
264 -- will be written to the output file.
265 -- p_application_short_name : IN optional
266 -- If given, all attributes for this application short
267 -- name will be written to the output file.
268 -- Application short name will be ignored if an
269 -- application ID is given.
270 -- p_attribute_pk_tbl : IN optional
271 -- If given, only attributes whose key values are
272 -- included in this table will be written to the
273 -- output file.
274 --
275 --
276 -- Version Initial version number = 1.0
277 -- History Current version number = 1.0
278 --=======================================================
279 procedure DOWNLOAD_ATTRIBUTE (
280 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
281 p_api_version_number IN NUMBER,
282 p_init_msg_tbl IN BOOLEAN := FALSE,
283 p_msg_count OUT NOCOPY NUMBER,
284 p_msg_data OUT NOCOPY VARCHAR2,
285 p_return_status OUT NOCOPY VARCHAR2,
286 p_nls_language IN VARCHAR2 := FND_API.G_MISS_CHAR,
287 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
288 p_application_short_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
289 p_attribute_pk_tbl IN AK_ATTRIBUTE_PUB.Attribute_PK_Tbl_Type :=
290 AK_ATTRIBUTE_PUB.G_MISS_ATTRIBUTE_PK_TBL
291 ) is
292 l_api_version_number CONSTANT number := 1.0;
293 l_api_name CONSTANT varchar2(30) := 'Download';
294 l_application_id number;
295 l_index NUMBER;
296 l_index_out NUMBER;
297 l_nls_language VARCHAR2(30);
298 l_return_status varchar2(1);
299 l_dum NUMBER;
300 begin
301 --
302 -- Check verion number
303 --
304 IF NOT FND_API.Compatible_API_Call (
305 l_api_version_number, p_api_version_number, l_api_name,
306 G_PKG_NAME) then
307 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
308 return;
309 END IF;
310
311 -- Initialize the message table if requested.
312
313 if p_init_msg_tbl then
314 FND_MSG_PUB.initialize;
315 end if;
316
317 savepoint Start_download;
318
319 if (AK_DOWNLOAD_GRP.G_WRITE_HEADER) then
320 --
321 -- Call private download procedure to verify parameters,
322 -- load application ID, and write header information such
323 -- as nls_language and codeset to data file.
324 --
325 AK_ON_OBJECTS_PVT.download_header(
326 p_validation_level => p_validation_level,
327 p_api_version_number => 1.0,
328 p_return_status => l_return_status,
329 p_nls_language => p_nls_language,
330 p_application_id => p_application_id,
331 p_application_short_name => p_application_short_name,
332 p_table_size => p_attribute_pk_tbl.count,
333 p_download_by_object => AK_ON_OBJECTS_PVT.G_ATTRIBUTE,
334 p_nls_language_out => l_nls_language,
335 p_application_id_out => l_application_id
336 );
337 else
338 l_application_id := p_application_id;
339 select userenv('LANG') into l_nls_language
340 from dual;
341 end if;
342 --
343 -- If API call returns with an error status...
344 --
345 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
346 (l_return_status = FND_API.G_RET_STS_ERROR) then
347 RAISE FND_API.G_EXC_ERROR;
348 end if;
349
350 --
351 -- - call the download procedure for attributes to retrieve the
352 -- selected attributes from the database into a table of type
353 -- AK_ON_OBJECTS_PUB.Buffer_Tbl_Type.
354 --
355 AK_ATTRIBUTE_PVT.DOWNLOAD_ATTRIBUTE(
356 p_validation_level => p_validation_level,
357 p_api_version_number => 1.0,
358 p_return_status => l_return_status,
359 p_application_id => l_application_id,
360 p_attribute_pk_tbl => p_attribute_pk_tbl,
361 p_nls_language => l_nls_language
362 );
363
364 --
365 -- If download call returns with an error status or
366 -- download failed to retrieve any information from the database..
367 --
368 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
369 (l_return_status = FND_API.G_RET_STS_ERROR) then
370 RAISE FND_API.G_EXC_ERROR;
371 end if;
372
373 p_return_status := FND_API.G_RET_STS_SUCCESS;
374
375 FND_MSG_PUB.Count_And_Get (
376 p_count => p_msg_count,
377 p_data => p_msg_data);
378
379 EXCEPTION
380 WHEN FND_API.G_EXC_ERROR THEN
381 p_return_status := FND_API.G_RET_STS_ERROR;
382 FND_MSG_PUB.Count_And_Get (
383 p_count => p_msg_count,
384 p_data => p_msg_data);
385 WHEN OTHERS THEN
386 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
387 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
388 SUBSTR (SQLERRM, 1, 240) );
389 FND_MSG_PUB.Add;
390 FND_MSG_PUB.Count_And_Get (
391 p_count => p_msg_count,
392 p_data => p_msg_data);
393
394 end DOWNLOAD_ATTRIBUTE;
395
396 --=======================================================
397 -- Procedure UPDATE_ATTRIBUTE
398 --
399 -- Usage Group API for updating an attribute
400 --
401 -- Desc This API calls the private API to update
402 -- an attribute using the given info
403 --
407 -- * Error
404 -- Results The API returns the standard p_return_status parameter
405 -- indicating one of the standard return statuses :
406 -- * Unexpected error
408 -- * Success
409 -- Parameters Attribute columns
410 --
411 -- Version Initial version number = 1.0
412 -- History Current version number = 1.0
413 --=======================================================
414 procedure UPDATE_ATTRIBUTE (
415 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
416 p_api_version_number IN NUMBER,
417 p_init_msg_tbl IN BOOLEAN := FALSE,
418 p_msg_count OUT NOCOPY NUMBER,
419 p_msg_data OUT NOCOPY VARCHAR2,
420 p_return_status OUT NOCOPY VARCHAR2,
421 p_attribute_application_id IN NUMBER,
422 p_attribute_code IN VARCHAR2,
423 p_attribute_label_length IN NUMBER := FND_API.G_MISS_NUM,
424 p_attribute_value_length IN NUMBER := FND_API.G_MISS_NUM,
425 p_bold IN VARCHAR2 := FND_API.G_MISS_CHAR,
426 p_italic IN VARCHAR2 := FND_API.G_MISS_CHAR,
427 p_vertical_alignment IN VARCHAR2 := FND_API.G_MISS_CHAR,
428 p_horizontal_alignment IN VARCHAR2 := FND_API.G_MISS_CHAR,
429 p_data_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
430 p_upper_case_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
431 p_default_value_varchar2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
432 p_default_value_number IN NUMBER := FND_API.G_MISS_NUM,
433 p_default_value_date IN DATE := FND_API.G_MISS_DATE,
434 p_lov_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
435 p_lov_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
436 p_item_style IN VARCHAR2,
437 p_display_height IN NUMBER := FND_API.G_MISS_NUM,
438 p_css_class_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
439 p_poplist_viewobject IN VARCHAR2 := FND_API.G_MISS_CHAR,
440 p_poplist_display_attr IN VARCHAR2 := FND_API.G_MISS_CHAR,
441 p_poplist_value_attr IN VARCHAR2 := FND_API.G_MISS_CHAR,
442 p_css_label_class_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
443 p_precision IN NUMBER := FND_API.G_MISS_NUM,
444 p_expansion IN NUMBER := FND_API.G_MISS_NUM,
445 p_als_max_length IN NUMBER := FND_API.G_MISS_NUM,
446 p_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
447 p_attribute_label_long IN VARCHAR2 := FND_API.G_MISS_CHAR,
448 p_attribute_label_short IN VARCHAR2 := FND_API.G_MISS_CHAR,
449 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR
450 ) is
451 l_api_version_number CONSTANT number := 1.0;
452 l_api_name CONSTANT varchar2(30) := 'Update_Attribute';
453 l_return_status VARCHAR2(1);
454 l_pass NUMBER := 2;
455 l_copy_redo_flag BOOLEAN := FALSE;
456 begin
457 --
458 -- Check API version number
459 --
460 IF NOT FND_API.Compatible_API_Call (
461 l_api_version_number, p_api_version_number, l_api_name,
462 G_PKG_NAME) then
463 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
464 return;
465 END IF;
466
467 -- Initialize the message table if requested.
468
469 if p_init_msg_tbl then
470 FND_MSG_PUB.initialize;
471 end if;
472
473 savepoint start_update_attribute;
474
475 --
476 -- Call private procedure to create an attribute
477 --
478 AK_ATTRIBUTE_PVT.UPDATE_ATTRIBUTE(
479 p_validation_level => p_validation_level,
480 p_api_version_number => 1.0,
481 p_msg_count => p_msg_count,
482 p_msg_data => p_msg_data,
483 p_return_status => l_return_status,
484 p_attribute_application_id => p_attribute_application_id,
485 p_attribute_code => p_attribute_code,
486 p_attribute_label_length => p_attribute_label_length,
487 p_attribute_value_length => p_attribute_value_length,
488 p_bold => p_bold,
489 p_italic => p_italic,
490 p_vertical_alignment => p_vertical_alignment,
491 p_horizontal_alignment => p_horizontal_alignment,
492 p_data_type => p_data_type,
493 p_upper_case_flag => p_upper_case_flag,
494 p_default_value_varchar2 => p_default_value_varchar2,
495 p_default_value_number => p_default_value_number,
496 p_default_value_date => p_default_value_date,
497 p_lov_region_application_id => p_lov_region_application_id,
498 p_lov_region_code => p_lov_region_code,
499 p_item_style => p_item_style,
500 p_display_height => p_display_height,
501 p_css_class_name => p_css_class_name,
502 p_poplist_viewobject => p_poplist_viewobject,
503 p_poplist_display_attr => p_poplist_display_attr,
504 p_poplist_value_attr => p_poplist_value_attr,
505 p_css_label_class_name => p_css_label_class_name,
506 p_precision => p_precision,
507 p_expansion => p_expansion,
508 p_als_max_length => p_als_max_length,
509 p_name => p_name,
510 p_attribute_label_long => p_attribute_label_long,
511 p_attribute_label_short => p_attribute_label_short,
512 p_description => p_description,
513 p_pass => l_pass,
514 p_copy_redo_flag => l_copy_redo_flag
515 );
516
517 --
518 -- If API call returns with an error status...
519 --
520 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
521 (l_return_status = FND_API.G_RET_STS_ERROR) then
522 --dbms_output.put_line('Update_Attribute failed');
523 RAISE FND_API.G_EXC_ERROR;
524 end if;
525
526 p_return_status := FND_API.G_RET_STS_SUCCESS;
527
528 EXCEPTION
529 WHEN FND_API.G_EXC_ERROR THEN
530 p_return_status := FND_API.G_RET_STS_ERROR;
531 rollback to start_update_attribute;
532 WHEN OTHERS THEN
533 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
534 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
535 SUBSTR (SQLERRM, 1, 240) );
536 FND_MSG_PUB.Add;
537 rollback to start_update_attribute;
538 end UPDATE_ATTRIBUTE;
539
540 end AK_Attribute_grp;
[Home] [Help]