1 package AK_ON_OBJECTS_PVT AUTHID CURRENT_USER as
2 /* $Header: akdvons.pls 120.3 2005/09/15 22:18:29 tshort ship $ */
3
4 -- Global constants holding the package and file names to be used by
5 -- messaging routines in the case of an unexpected error.
6
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AK_ON_OBJECTS_PVT';
8
9 -- Constants for use across different AK private APIs
10 G_MAX_FILE_LINE_LEN CONSTANT NUMBER := 80;
11 G_CREATE CONSTANT VARCHAR2(1) := 'C';
12 G_DOWNLOAD CONSTANT VARCHAR2(1) := 'D';
13 G_UPDATE CONSTANT VARCHAR2(1) := 'U';
14
15 G_ATTRIBUTE CONSTANT VARCHAR2(10) := 'ATTRIBUTE';
16 G_FLOW CONSTANT VARCHAR2(10) := 'FLOW';
17 G_OBJECT CONSTANT VARCHAR2(10) := 'OBJECT';
18 G_REGION CONSTANT VARCHAR2(10) := 'REGION';
19 G_CUSTOM_REGION CONSTANT VARCHAR2(15) := 'CUSTOM_REGION';
20 G_SECURITY CONSTANT VARCHAR2(10) := 'SECURITY';
21 G_QUERYOBJ CONSTANT VARCHAR2(10) := 'QUERYOBJ';
22 G_AMPARAM_REGISTRY CONSTANT VARCHAR2(20) := 'AMPARAM_REGISTRY';
23
24 -- PL/SQL tables for log file in upload and download
25 G_LOG_BUFFER_TBL AK_ON_OBJECTS_PUB.Buffer_Tbl_Type;
26
27 -- Write mode of output file
28 G_WRITE_MODE VARCHAR2(1);
29
30 -- Index for AK_LOADER_TEMP
31 G_TBL_INDEX NUMBER;
32
33 -- Total number of records in AK_LOADER_TEMP for upload
34 G_UPL_TABLE_NUM NUMBER;
35
36 -- Current session id
37 G_SESSION_ID NUMBER;
38
39 --Procedures for use by AK private APIs internal use
40
41 --==============================================
42 -- Procedure APPEND_BUFFER_TABLES
43 --
44 -- Usage Private procedure for appending one buffer table to the
45 -- end of another buffer table.
46 -- This procedure is intended to be called only by other APIs
47 -- that are owned by the Core Modules Team (AK)
48 --
49 -- Desc Appends all elements in the from_table to the end of the
50 -- to_table. Both tables must be of type Buffer_Table_Type.
51 --
52 -- Results The procedure returns the standard p_return_status parameter
53 -- indicating one of the standard return statuses :
54 -- * Unexpected error
55 -- * Error
56 -- * Success
57 -- Parameters p_from_table : IN required
58 -- The from buffer table containing elements to be
59 -- appended to the end of the to buffer table.
60 -- p_to_table : IN OUT
61 -- The target buffer table which will have the elements
62 -- in the from table appended to it.
63 --==============================================
64 procedure APPEND_BUFFER_TABLES (
65 p_return_status OUT NOCOPY VARCHAR2,
66 p_from_table IN AK_ON_OBJECTS_PUB.Buffer_Tbl_Type,
67 p_to_table IN OUT NOCOPY AK_ON_OBJECTS_PUB.Buffer_Tbl_Type
68 );
69
70
71 --==============================================
72 -- Procedure DOWNLOAD_HEADER
73 --
74 -- Usage Private procedure for writing standard header information
75 -- to a loader file.
76 -- This procedure is intended to be called only by other APIs
77 -- that are owned by the Core Modules Team (AK)
78 --
79 -- Desc This procedure writes all the standard header information
80 -- including the DEFINE section to the loader file.
81 --
82 -- Results The API returns the standard p_return_status parameter
83 -- indicating one of the standard return statuses :
84 -- * Unexpected error
85 -- * Error
86 -- * Success
87 -- Parameters p_nls_language : IN optional
88 -- The NLS language of the database. If this is omitted,
89 -- the default NLS language defined in the database
90 -- will be used.
91 -- p_application_id : IN optional
92 -- The application ID to be used to extract data from
93 -- the database. If p_application_id is omitted, then
94 -- either p_application_short_name must be given, or
95 -- p_table_size must be greater than 0.
96 -- p_application_short_name : IN optional
97 -- The application short name to be used to extract data
98 -- from the database. If p_application_short_name is
99 -- not provided, then either p_application_id must be
100 -- given, or p_table_size must be greater than 0.
101 -- p_application_short_name will be ignored if
102 -- p_application_id is given.
103 -- p_table_size : IN required
104 -- The size of the PL/SQL table containing the list of
105 -- flows, objects, regions, or attributes to be extracted
106 -- from the database. If p_table_size is 0, then either
107 -- p_application_id or p_application_short_name must
108 -- be provided.
109 -- p_download_by_object : IN required
110 -- Must be one of the following literal defined in
111 -- AK_ON_OBJECTS_PVT package:
112 -- G_ATTRIBUTE - Caller is DOWNLOAD_ATTRIBUTE API
113 -- G_OBJECT - Caller is DOWNLOAD_OBJECT API
114 -- G_REGION - Caller is DOWNLOAD_REGION API
115 -- G_FLOW - Caller is DOWNLOAD_FLOW API
116 -- This parameter is used to determine which portions
117 -- of the DEFINE section should be written to the file.
118 -- p_nls_language_out : OUT
119 -- This parameter will be loaded with p_nls_language if
120 -- one is given, or with the default NLS language in the
121 -- database if p_nls_language is not provided.
122 -- p_application_id_out : OUT
123 -- This parameter will be loaded with p_application_id if
124 -- one is given, or with the application ID of the
125 -- p_application_short_name parameter if no application ID
126 -- is provided. If both p_application_short_name and
127 -- p_application_id are not given, the p_application_id_out
128 -- will be null.
129 --==============================================
130 procedure DOWNLOAD_HEADER (
131 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
132 p_api_version_number IN NUMBER,
133 p_return_status OUT NOCOPY VARCHAR2,
134 p_nls_language IN VARCHAR2 := FND_API.G_MISS_CHAR,
135 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
136 p_application_short_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
137 p_table_size IN NUMBER,
138 p_download_by_object IN VARCHAR2,
139 p_nls_language_out OUT NOCOPY VARCHAR2,
140 p_application_id_out OUT NOCOPY NUMBER
141 );
142
143 --=======================================================
144 -- Procedure GET_TOKEN
145 --
146 -- Usage Private procedure for returning the first token from
147 -- the given input string.
148 -- This function is intended to be called only by other APIs
149 -- that are owned by the Core Modules Team (AK)
150 --
151 -- Desc This procedure parses the input string and returns the first
152 -- token in the string. It then removes that token from the
153 -- input string.
154 --
155 -- Results The API returns the standard p_return_status parameter
156 -- indicating one of the standard return statuses :
157 -- * Unexpected error
158 -- * Error (including parse error, such as empty input string)
159 -- * Success
160 -- The procedure returns the first token in the input string
161 -- in the p_token parameter. It also removes the token from
162 -- the input string.
163 -- Parameters p_in_buf : IN OUT
164 -- The input string to be parsed. The procedure would
165 -- remove the first token from this string before
166 -- passing it back to the calling API.
167 -- p_token : OUT
168 -- The first token parsed in the input string, with
169 -- all the escaped characters in the token already
170 -- replaced with their original characters.
171 --=======================================================
172 procedure GET_TOKEN (
173 p_return_status OUT NOCOPY VARCHAR2,
174 p_in_buf IN OUT NOCOPY VARCHAR2,
175 p_token OUT NOCOPY VARCHAR2
176 );
177
178 --=======================================================
179 -- Procedure READ_LINE
180 --
181 -- Usage Private procedure for reading the next line from a file.
182 -- This function is intended to be called only by other APIs
183 -- that are owned by the Core Modules Team (AK)
184 --
185 -- Desc This procedure reads the next logical line from a flat file
186 -- whose file handle is given as the p_file_handle parameter.
187 -- This means that the file to be read must already be opened.
188 -- A logical line may contain many physical lines in the
189 -- file, each except the last physical line has a trailing
190 -- character indicating that the line is to be continued.
191 --
192 -- Results The API returns the standard p_return_status parameter
193 -- indicating one of the standard return statuses :
194 -- * Unexpected error
195 -- * Error
196 -- * Success
197 -- The procedure returns the data read from the file into
198 -- the p_buffer parameter. It also return the number of physical
199 -- lines read when reading the next logical line through the
200 -- p_lines_read parameter. If the end-of-file is reached, the
201 -- p_eof_flag will be loaded with 'Y'.
202 -- Parameters p_file_handle : IN required
203 -- The file handle of the file to be read.
204 -- p_buffer : OUT
205 -- The buffer where the logical line read from the file
206 -- will be loaded.
207 -- p_lines_read : OUT
208 -- The number of lines read from the file when reading
209 -- the current logical line
210 -- p_eof_flag : OUT
211 -- This flag will be loaded with 'Y' if the procedure
212 -- has encountered the end-of-file while trying to read
213 -- the next line from the file.
214 -- Notes This procedure will NOT close the file after the last
215 -- line is read - the caller must close the file by calling
216 -- the CLOSE_FILE API.
217 --=======================================================
218 PROCEDURE READ_LINE (
219 p_return_status OUT NOCOPY VARCHAR2,
220 p_index IN OUT NOCOPY Number,
221 p_buffer OUT NOCOPY AK_ON_OBJECTS_PUB.Buffer_Type,
222 p_lines_read OUT NOCOPY number,
223 p_eof_flag OUT NOCOPY VARCHAR2,
224 p_upl_loader_cur IN OUT NOCOPY AK_ON_OBJECTS_PUB.LoaderCurTyp
225 );
226
227
228 --==============================================
229 -- Function REPLACE_SPECIAL_CHARS
230 --
231 -- Usage Private function for replacing all special characters
232 -- with escaped characters.
233 -- This function is intended to be called only by other APIs
234 -- that are owned by the Core Modules Team (AK)
235 --
236 -- Desc Replaces all special characters in the input string
237 -- with the corresponding escaped characters, for instance,
238 -- the 'tab' character will be replaced by 't'.
239 --
240 -- Results The procedure returns a string which is the result of
241 -- replacing all special characters with their corresponding
242 -- escaped characters.
243 -- Parameters p_buffer : IN required
244 -- The input string with special characters.
245 --==============================================
246 function REPLACE_SPECIAL_CHAR (
247 p_buffer IN VARCHAR2
248 ) return VARCHAR2;
249
250 --=======================================================
251 -- Procedure SET_WHO
252 --
253 -- Usage Private procedure for setting the who columns values.
254 -- This function is intended to be called only by other APIs
255 -- that are owned by the Core Modules Team (AK)
256 --
257 -- Desc This procedure returns to the caller the values of the
258 -- who columns for updating or creating a record. It returns
259 -- a different set of values depending on whether the update
260 -- is initiated by a user or by the loader.
261 --
262 -- Results The API returns the standard p_return_status parameter
263 -- indicating one of the standard return statuses :
264 -- * Unexpected error
265 -- * Error
266 -- * Success
267 -- Parameters p_loader_timestamp : IN optional
268 -- The timestamp to be used when the record are to be
269 -- updated or created by the loader. It should not
270 -- contain any value (ie, G_MISS_DATE) if the record
271 -- is not being updated or created by the loader.
272 -- p_created_by : OUT
273 -- It contains the value to be used for the CREATED_BY
274 -- who column. This value should be ignored by the
275 -- caller if the caller is only updating a record.
276 -- p_creation_date : OUT
277 -- It contains the value to be used for the CREATION_DATE
278 -- who column. This value should be ignored by the
279 -- caller if the caller is only updating a record.
280 -- p_last_updated_by : OUT
281 -- It contains the value to be used for the LAST_UPDATED_BY
282 -- who column.
283 -- p_last_update_date : OUT
284 -- It contains the value to be used for the LAST_UPDATE_DATE
285 -- who column.
286 -- p_last_update_login : OUT
287 -- It contains the value to be used for the
288 -- LAST_UPDATE_LOGIN who column.
289 --=======================================================
290 procedure SET_WHO (
291 p_return_status OUT NOCOPY VARCHAR2,
292 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
293 p_created_by OUT NOCOPY NUMBER,
294 p_creation_date OUT NOCOPY DATE,
295 p_last_updated_by OUT NOCOPY NUMBER,
296 p_last_update_date OUT NOCOPY DATE,
297 p_last_update_login OUT NOCOPY NUMBER
298 );
299
300 function IS_UPDATEABLE (
301 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
302 p_created_by IN OUT NOCOPY NUMBER,
303 p_creation_date IN OUT NOCOPY DATE,
304 p_last_updated_by IN OUT NOCOPY NUMBER,
305 p_db_last_updated_by IN NUMBER,
306 p_last_update_date IN OUT NOCOPY DATE,
307 p_db_last_update_date IN DATE,
308 p_last_update_login IN OUT NOCOPY NUMBER,
309 p_create_or_update IN VARCHAR2
310 ) return BOOLEAN;
311
312 --==============================================
313 -- Procedure UPLOAD
314 --
315 -- Usage Private API for loading flows, objects, regions,
316 -- and attributes from a loader file to the database.
317 -- This procedure is intended to be called only by other APIs
318 -- that are owned by the Core Modules Team (AK)
319 --
320 -- Desc This API parses the header information and the DEFINE
321 -- section, and calls the appropriate private API to read
322 -- in all flow, object, region, and attribute data
323 -- (including all the tables in these business objects)
324 -- from the loader file, and update them to the database.
325 --
326 -- Results The API returns the standard p_return_status parameter
327 -- indicating one of the standard return statuses :
328 -- * Unexpected error
329 -- * Error
330 -- * Success
331 -- Parameters
332 --==============================================
333 procedure UPLOAD (
334 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
335 p_api_version_number IN NUMBER,
336 p_init_msg_tbl IN BOOLEAN := FALSE,
337 p_msg_count OUT NOCOPY NUMBER,
338 p_msg_data OUT NOCOPY VARCHAR2,
339 p_return_status OUT NOCOPY VARCHAR2
340 );
341
342 --=======================================================
343 -- Function VALID_APPLICATION_ID
344 --
345 -- Usage Private function for validating an application ID. This
346 -- API should only be called by other APIs that are
347 -- owned by the Core Modules Team (AK).
348 --
349 -- Desc This function checks to see if the given application ID is
350 -- a valid application ID in the FND_APPLICATION table.
351 --
352 -- Results This function returns the standard p_return_status parameter
353 -- indicating one of the standard return statuses :
354 -- * Unexpected error
355 -- * Error
356 -- * Success
357 -- Parameters p_application_id : IN required
358 -- The application ID that needs to be checked against
359 -- the FND_APPLICATION table.
360 -- This function will return TRUE if the application ID
361 -- exists in the FND_APPLICATION table, or FALSE otherwise.
362 --
363 -- Version Initial version number = 1.0
364 -- History Current version number = 1.0
365 --=======================================================
366 function VALID_APPLICATION_ID (
367 p_api_version_number IN NUMBER,
368 p_return_status OUT NOCOPY VARCHAR2,
369 p_application_id IN NUMBER
370 ) return BOOLEAN;
371
372 --=======================================================
373 -- Function VALID_LOOKUP_CODE
374 --
375 -- Usage Private function for validating a lookup code. This
376 -- API should only be called by other APIs that are
377 -- owned by the Core Modules Team (AK).
378 --
379 -- Desc This function checks to see if the given lookup type and
380 -- lookup code exists in the AK_LOOKUP_CODES table.
381 --
382 -- Results This function returns the standard p_return_status parameter
383 -- indicating one of the standard return statuses :
384 -- * Unexpected error
385 -- * Error
386 -- * Success
387 -- Parameters p_lookup_type : IN required
388 -- The type of the lookup code to be verified
389 -- p_lookup_code : IN required
390 -- The lookup code to be verified against AK_LOOKUP_CODES
391 -- This function will return TRUE if the lookup type and
392 -- lookup code exists in the AK_LOOKUP_CODES table, or
393 -- FALSE otherwise.
394 --
395 -- Version Initial version number = 1.0
396 -- History Current version number = 1.0
397 --=======================================================
398 function VALID_LOOKUP_CODE (
399 p_api_version_number IN NUMBER,
400 p_return_status OUT NOCOPY VARCHAR2,
401 p_lookup_type IN VARCHAR2,
402 p_lookup_code IN VARCHAR2
403 ) return BOOLEAN;
404
405 --=======================================================
406 -- Function VALID_YES_NO
407 --
408 -- Usage Private function for validating a Y/N column. This
409 -- API should only be called by other APIs that are
410 -- owned by the Core Modules Team (AK).
411 --
412 -- Desc This function checks to see if the given value is
413 -- either 'Y' or 'N'. It is used for checking for valid
414 -- data in columns that accepts only 'Y' or 'N' as valid
415 -- values.
416 --
417 -- Results This function returns the standard p_return_status parameter
418 -- indicating one of the standard return statuses :
419 -- * Unexpected error
420 -- * Error
421 -- * Success
422 -- Parameters p_value : IN required
423 -- The value to be checked
424 -- This function will return TRUE if the value is either
425 -- 'Y' or 'N', or FALSE otherwise.
426 --
427 -- Version Initial version number = 1.0
428 -- History Current version number = 1.0
429 --=======================================================
430 function VALID_YES_NO (
431 p_value IN VARCHAR2
432 ) return BOOLEAN;
433
434 --=======================================================
435 -- Procedure WRITE_FILE
436 --
437 -- Usage Private procedure for writing the contents in a PL/SQL
438 -- table to a file.
439 -- This function is intended to be called only by other APIs
440 -- that are owned by the Core Modules Team (AK)
441 --
442 -- Desc This procedure writes the contents in the PL/SQL table passed
443 -- into the specified file. The file could be overwritten
444 -- or appended depending on the value of the parameter
445 -- p_write_mode.
446 --
447 -- Results The API returns the standard p_return_status parameter
448 -- indicating one of the standard return statuses :
449 -- * Unexpected error
450 -- * Error
451 -- * Success
452 -- Parameters
453 -- p_buffer_tbl : IN required
454 -- The PL/SQL table of type Buffer_Tbl_Type whose
455 -- content is to be written to a file.
456 -- p_write_mode : IN optional
457 -- It must be G_APPEND or G_OVERWRITE if a value
458 -- is given. It tells this procedure whether to
459 -- write the PL/SQL table contents to the end of the
460 -- file (default), or to overwrite the file with the
461 -- contents in the PL/SQL table.
462 --=======================================================
463 procedure WRITE_FILE (
464 p_return_status OUT NOCOPY VARCHAR2,
465 p_buffer_tbl IN AK_ON_OBJECTS_PUB.Buffer_Tbl_Type,
466 p_write_mode IN VARCHAR2 := AK_ON_OBJECTS_PUB.G_APPEND
467 );
468
469 --=======================================================
470 -- Procedure WRITE_LOG_FILE
471 --
472 -- Usage Private procedure for writing the contents in a PL/SQL
473 -- table to a file.
474 -- This function is intended to be called only by other APIs
475 -- that are owned by the Core Modules Team (AK)
476 --
477 -- Desc This procedure writes the contents in the PL/SQL table passed
478 -- into the specified file. The file could be overwritten
479 -- or appended depending on the value of the parameter
480 -- p_write_mode.
481 --
482 -- Results The API returns the standard p_return_status parameter
483 -- indicating one of the standard return statuses :
484 -- * Unexpected error
485 -- * Error
486 -- * Success
487 -- Parameter
488 -- p_buffer_tbl : IN required
489 -- The PL/SQL table of type Buffer_Tbl_Type whose
490 -- content is to be written to a file.
491 -- p_write_mode : IN optional
492 -- It must be G_APPEND or G_OVERWRITE if a value
493 -- is given. It tells this procedure whether to
494 -- write the PL/SQL table contents to the end of the
495 -- file (default), or to overwrite the file with the
496 -- contents in the PL/SQL table.
497 --=======================================================
498 procedure WRITE_LOG_FILE (
499 p_return_status OUT NOCOPY VARCHAR2,
500 p_buffer_tbl IN AK_ON_OBJECTS_PUB.Buffer_Tbl_Type,
501 p_write_mode IN VARCHAR2 := AK_ON_OBJECTS_PUB.G_APPEND
502 );
503
504 procedure WRITE_TO_TABLE (
505 p_buffer IN VARCHAR2
506 );
507
508 end AK_ON_OBJECTS_PVT;