1 package body AK_QUERYOBJ_GRP as
2 /* $Header: akdgqryb.pls 120.2 2005/09/15 22:26:39 tshort ship $ */
3
4 --========================================================
5 -- Procedure DOWNLOAD_QUERY_OBJECT
6 --
7 -- Usage Group API for downloading query objects
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
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 query objects for this application ID
29 -- will be written to the output file.
30 -- p_application_short_name : IN optional
31 -- If given, all query objects 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_queryobj_pk_tbl : IN optional
36 -- If given, only query objects whose key values are
37 -- included in this table will be written to the
38 -- output file.
39 --
40 --
41 -- Version Initial version number = 1.0
42 -- History Current version number = 1.0
43 --=======================================================
44 procedure DOWNLOAD_QUERY_OBJECT (
45 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
46 p_api_version_number IN NUMBER,
47 p_init_msg_tbl IN BOOLEAN := FALSE,
48 p_msg_count OUT NOCOPY NUMBER,
49 p_msg_data OUT NOCOPY VARCHAR2,
50 p_return_status OUT NOCOPY VARCHAR2,
51 p_nls_language IN VARCHAR2 := FND_API.G_MISS_CHAR,
52 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
53 p_application_short_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
54 p_queryobj_pk_tbl IN AK_QUERYOBJ_PUB.queryobj_PK_Tbl_Type :=
55 AK_QUERYOBJ_PUB.G_MISS_QUERYOBJ_PK_TBL
56 ) is
57 l_api_version_number CONSTANT number := 1.0;
58 l_api_name CONSTANT varchar2(30) := 'Download Query Object';
59 l_application_id number;
60 l_index NUMBER;
61 l_index_out NUMBER;
62 l_nls_language VARCHAR2(30);
63 l_return_status varchar2(1);
64 begin
65 --
66 -- Check verion number
67 --
68 IF NOT FND_API.Compatible_API_Call (
69 l_api_version_number, p_api_version_number, l_api_name,
70 G_PKG_NAME) then
71 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
72 return;
73 END IF;
74
75 -- Initialize the message table if requested.
76
77 if p_init_msg_tbl then
78 FND_MSG_PUB.initialize;
79 end if;
80
81 savepoint Start_download;
82 if (AK_DOWNLOAD_GRP.G_WRITE_HEADER) then
83 --
84 -- Call private download procedure to verify parameters,
85 -- load application ID, and write header information such
86 -- as nls_language and codeset to data file.
87 --
88 AK_ON_OBJECTS_PVT.download_header(
89 p_validation_level => p_validation_level,
90 p_api_version_number => 1.0,
91 p_return_status => l_return_status,
92 p_nls_language => p_nls_language,
93 p_application_id => p_application_id,
94 p_application_short_name => p_application_short_name,
95 p_table_size => p_queryobj_pk_tbl.count,
96 p_download_by_object => AK_ON_OBJECTS_PVT.G_QUERYOBJ,
97 p_nls_language_out => l_nls_language,
98 p_application_id_out => l_application_id
99 );
100 else
101 l_application_id := p_application_id;
102 select userenv('LANG') into l_nls_language
103 from dual;
104 end if;
105
106 --
107 -- If API call returns with an error status...
108 --
109 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
110 (l_return_status = FND_API.G_RET_STS_ERROR) then
111 RAISE FND_API.G_EXC_ERROR;
112 end if;
113
114 --
115 -- - call the download procedure for attributes to retrieve the
116 -- selected attributes from the database into a table of type
117 -- AK_ON_OBJECTS_PUB.Buffer_Tbl_Type.
118 --
119 -- dbms_output.put_line('Start downloading query objects');
120
121 AK_QUERYOBJ_PVT.DOWNLOAD_QUERY_OBJECT(
122 p_validation_level => p_validation_level,
123 p_api_version_number => 1.0,
124 p_return_status => l_return_status,
125 p_application_id => l_application_id,
126 p_queryobj_pk_tbl => p_queryobj_pk_tbl,
127 p_nls_language => l_nls_language
128 );
129
130 --
131 -- If download call returns with an error status or
132 -- download failed to retrieve any information from the database..
133 --
134 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
135 (l_return_status = FND_API.G_RET_STS_ERROR) then
136 RAISE FND_API.G_EXC_ERROR;
137 end if;
138
139 p_return_status := FND_API.G_RET_STS_SUCCESS;
140
141 FND_MSG_PUB.Count_And_Get (
142 p_count => p_msg_count,
143 p_data => p_msg_data);
144
145 EXCEPTION
146 WHEN FND_API.G_EXC_ERROR THEN
147 p_return_status := FND_API.G_RET_STS_ERROR;
148 FND_MSG_PUB.Count_And_Get (
149 p_count => p_msg_count,
150 p_data => p_msg_data);
151 WHEN OTHERS THEN
152 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
153 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
154 SUBSTR (SQLERRM, 1, 240) );
155 FND_MSG_PUB.Add;
156 FND_MSG_PUB.Count_And_Get (
157 p_count => p_msg_count,
158 p_data => p_msg_data);
159
160 end DOWNLOAD_QUERY_OBJECT;
161
162 --=======================================================
163 -- Procedure CREATE_QUERY_OBJECT
164 --
165 -- Usage Group API for creating a query objec
166 --
167 -- Desc Calls the private API to creates a query object
168 -- using the given info
169 --
170 -- Results The API returns the standard p_return_status parameter
171 -- indicating one of the standard return statuses :
172 -- * Unexpected error
173 -- * Error
174 -- * Success
175 -- Parameters Query object columns
176 --
177 -- Version Initial version number = 1.0
178 -- History Current version number = 1.0
179 --=======================================================
180 procedure CREATE_QUERY_OBJECT (
181 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
182 p_api_version_number IN NUMBER,
183 p_init_msg_tbl IN BOOLEAN := FALSE,
184 p_msg_count OUT NOCOPY NUMBER,
185 p_msg_data OUT NOCOPY VARCHAR2,
186 p_return_status OUT NOCOPY VARCHAR2,
187 p_query_code IN VARCHAR2,
188 p_application_id IN NUMBER,
189 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
190 p_creation_date IN DATE := FND_API.G_MISS_DATE,
191 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
192 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
193 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM
194 ) is
195 l_api_version_number CONSTANT number := 1.0;
196 l_api_name CONSTANT varchar2(30) := 'Create_query_objects';
197 l_return_status VARCHAR2(1);
198 begin
199 /* Check API version number */
200 IF NOT FND_API.Compatible_API_Call (
201 l_api_version_number, p_api_version_number, l_api_name,
202 G_PKG_NAME) then
203 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
204 return;
205 END IF;
206
207 -- Initialize the message table if requested.
208
209 if p_init_msg_tbl then
210 FND_MSG_PUB.initialize;
211 end if;
212
213 savepoint start_create_query_object;
214
215 --
216 -- Call private procedure to create a query object
217 --
218 AK_QUERYOBJ_PVT.CREATE_QUERY_OBJECT(
219 p_validation_level => p_validation_level,
220 p_api_version_number => 1.0,
221 p_msg_count => p_msg_count,
222 p_msg_data => p_msg_data,
223 p_return_status => l_return_status,
224 p_query_code => p_query_code,
225 p_application_id => p_application_id,
226 p_created_by => p_created_by,
227 p_creation_date => p_creation_date,
228 p_last_updated_by => p_last_updated_by,
229 p_last_update_date => p_last_update_date,
230 p_last_update_login => p_lasT_update_login
231 );
232
233 --
234 -- If API call returns with an error status...
235 --
236 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
237 (l_return_status = FND_API.G_RET_STS_ERROR) then
238 RAISE FND_API.G_EXC_ERROR;
239 end if;
240
241 p_return_status := FND_API.G_RET_STS_SUCCESS;
242
243 EXCEPTION
244 WHEN FND_API.G_EXC_ERROR THEN
245 p_return_status := FND_API.G_RET_STS_ERROR;
246 rollback to start_create_query_object;
247 WHEN OTHERS THEN
248 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
249 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
250 SUBSTR (SQLERRM, 1, 240) );
251 FND_MSG_PUB.Add;
252 rollback to start_create_query_object;
253 end CREATE_QUERY_OBJECT;
254
255 --=======================================================
256 -- Procedure CREATE_QUERY_OBJECT_LINE
257 --
258 -- Usage Group API for creating a query object line
259 --
260 -- Desc Calls the private API to creates a query object line
261 -- using the given info
262 --
263 -- Results The API returns the standard p_return_status parameter
264 -- indicating one of the standard return statuses :
265 -- * Unexpected error
266 -- * Error
267 -- * Success
268 -- Parameters Query object line columns
269 --
270 -- Version Initial version number = 1.0
271 -- History Current version number = 1.0
272 --=======================================================
273 procedure CREATE_QUERY_OBJECT_LINE (
274 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
275 p_api_version_number IN NUMBER,
276 p_init_msg_tbl IN BOOLEAN := FALSE,
277 p_msg_count OUT NOCOPY NUMBER,
278 p_msg_data OUT NOCOPY VARCHAR2,
279 p_return_status OUT NOCOPY VARCHAR2,
280 p_query_code IN VARCHAR2,
281 p_seq_num IN NUMBER,
282 p_query_line_type IN VARCHAR2,
283 p_query_line IN VARCHAR2 := FND_API.G_MISS_CHAR,
284 p_linked_parameter IN VARCHAR2,
285 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
286 p_creation_date IN DATE := FND_API.G_MISS_DATE,
287 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
288 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
289 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM
290 ) is
291 l_api_version_number CONSTANT number := 1.0;
292 l_api_name CONSTANT varchar2(30) := 'Create_Query_Object_Line';
293 l_return_status VARCHAR2(1);
294 begin
295 /* Check API version number */
296 IF NOT FND_API.Compatible_API_Call (
297 l_api_version_number, p_api_version_number, l_api_name,
298 G_PKG_NAME) then
299 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
300 return;
301 END IF;
302
303 -- Initialize the message table if requested.
304
305 if p_init_msg_tbl then
306 FND_MSG_PUB.initialize;
307 end if;
308
309 savepoint start_create_query_object_line;
310
311 --
312 -- Call private procedure to create a query object line
313 --
314 AK_QUERYOBJ_PVT.CREATE_QUERY_OBJECT_LINE(
315 p_validation_level => p_validation_level,
316 p_api_version_number => 1.0,
317 p_msg_count => p_msg_count,
318 p_msg_data => p_msg_data,
319 p_return_status => l_return_status,
320 p_query_code => p_query_code,
321 p_seq_num => p_seq_num,
322 p_query_line_type => p_query_line_type,
323 p_linked_parameter => p_linked_parameter,
324 p_created_by => p_created_by,
325 p_creation_date => p_creation_date,
326 p_last_updated_by => p_last_updated_by,
327 p_last_update_date => p_last_update_date,
328 p_last_update_login => p_lasT_update_login
329 );
330
331 --
332 -- If API call returns with an error status...
333 --
334 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
335 (l_return_status = FND_API.G_RET_STS_ERROR) then
336 RAISE FND_API.G_EXC_ERROR;
337 end if;
338
339 p_return_status := FND_API.G_RET_STS_SUCCESS;
340
341 EXCEPTION
342 WHEN FND_API.G_EXC_ERROR THEN
343 p_return_status := FND_API.G_RET_STS_ERROR;
344 rollback to start_create_query_object_line;
345 WHEN OTHERS THEN
346 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
347 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
348 SUBSTR (SQLERRM, 1, 240) );
349 FND_MSG_PUB.Add;
350 rollback to start_create_query_object_line;
351 end CREATE_QUERY_OBJECT_LINE;
352
353 end AK_QUERYOBJ_GRP;
[Home] [Help]