1 PACKAGE EGO_USER_ATTRS_DATA_PUB AUTHID DEFINER AS
2 /* $Header: EGOPEFBS.pls 120.5 2007/05/02 18:34:26 ssarnoba ship $ */
3 /*#
4 * This package provides User-Defined Attributes functionality for
5 * various objects--most notably Items, but also any other objects
6 * that have enabled the User-Defined Attributes framework (e.g.,
7 * Projects, Structures, etc.).
8 * This includes DML (insertion, update, and deletion) on User-Defined
9 * Attribute data), retrieval of such data, and copying of data from
10 * one object to another. It also includes Change control on data for
11 * objects that have enabled Change functionality for the User-Defined
12 * Attributes framework (for information about Change functionality,
13 * refer to the Change Management documentation).
14 *
15 * ------------------
16 * -- Object Types --
17 * ------------------
18 *
19 * Each of the following data types is defined as an Oracle object type
20 * that exists independently in the database. They are discussed here
21 * because all of these types were created for use by this package.
22 *
23 * ==========================
24 * = EGO_USER_ATTR_DATA_OBJ =
25 * ==========================
26 *
27 *<code><pre>
28 CREATE EGO_USER_ATTR_DATA_OBJ AS OBJECT
29 (
30 ROW_IDENTIFIER NUMBER
31 ,ATTR_NAME VARCHAR2(30)
32 ,ATTR_VALUE_STR VARCHAR2(1000)
33 ,ATTR_VALUE_NUM NUMBER
34 ,ATTR_VALUE_DATE DATE
35 ,ATTR_DISP_VALUE VARCHAR2(1000)
36 ,ATTR_UNIT_OF_MEASURE VARCHAR2(3)
37 ,USER_ROW_IDENTIFIER NUMBER
38 );
39 *</pre></code>
40 *
41 * EGO_USER_ATTR_DATA_OBJ is an object type that contains data for one
42 * attribute in an attribute group row. ROW_IDENTIFIER is a foreign key
43 * that associates each EGO_USER_ATTR_DATA_OBJ to one EGO_USER_ATTR_ROW_OBJ
44 * (discussed below). ATTR_NAME holds the internal name of the attribute.
45 * The value being passed for the attribute is stored in ATTR_VALUE_STR if
46 * the attribute is a string (translatable or not), in ATTR_VALUE_NUM
47 * if the attribute is a number, in ATTR_VALUE_DATE if the attribute is
48 * a date or datetime, or in ATTR_DISP_VALUE if the attribute has a value
49 * set with distinct internal and display values. NOTE: the attribute
50 * value must be passed in exactly ~one~ of these four fields.
51 * If the attribute is a number that has a Unit of Measure class associated
52 * with it, ATTR_UNIT_OF_MEASURE stores the UOM Code for the Unit of Measure
53 * in which the attribute's value will be displayed; however, the value
54 * itself will always be passed in ATTR_VALUE_NUM in the base units for
55 * the Unit of Measure class, not in the display units (unless they happen
56 * to be the same). For example, consider an attribute whose Unit of
57 * Measure class is Length (a UOM Class whose base unit we will assume for
58 * this example to be Centimeters). If the caller wants data for this
59 * attribute to be displayed in Feet (assuming its UOM_CODE is 'FT'),
60 * then ATTR_UNIT_OF_MEASURE should be passed with 'FT'; however, no
61 * matter in what unit the caller wants to display this attribute, the
62 * value in ATTR_VALUE_NUM will always be the attribute's value as
63 * expressed in Centimeters.
64 * The final field in the object type, USER_ROW_IDENTIFIER, is a numeric
65 * value used when reporting errors for this EGO_USER_ATTR_DATA_OBJ. When
66 * the errors are written to the MTL_INTERFACE_ERRORS table, the TRANSACTION_ID
67 * column stores the value passed in USER_ROW_IDENTIFIER; thus, to find
68 * errors logged for this EGO_USER_ATTR_DATA_OBJ, search for rows in
69 * MTL_INTERFACE_ERRORS whose TRANSACTION_ID column values match the
70 * passed-in USER_ROW_IDENTIFIER.
71 *
72 * ============================
73 * = EGO_USER_ATTR_DATA_TABLE =
74 * ============================
75 *
76 *<code><pre>
77 CREATE EGO_USER_ATTR_DATA_TABLE AS TABLE OF EGO_USER_ATTR_DATA_OBJ;
78 *</pre></code>
79 *
80 * =========================
81 * = EGO_USER_ATTR_ROW_OBJ =
82 * =========================
83 *
84 *<code><pre>
85 CREATE EGO_USER_ATTR_ROW_OBJ AS OBJECT
86 (
87 ROW_IDENTIFIER NUMBER
88 ,ATTR_GROUP_ID NUMBER
89 ,ATTR_GROUP_APP_ID NUMBER
90 ,ATTR_GROUP_TYPE VARCHAR2(40)
91 ,ATTR_GROUP_NAME VARCHAR2(30)
92 ,DATA_LEVEL_1 VARCHAR2(150)
93 ,DATA_LEVEL_2 VARCHAR2(150)
94 ,DATA_LEVEL_3 VARCHAR2(150)
95 ,TRANSACTION_TYPE VARCHAR2(10)
96 );
97 *</pre></code>
98 *
99 * EGO_USER_ATTR_ROW_OBJ contains row-level data about one attribute group
100 * row. ROW_IDENTIFIER is the unique numeric identifier for this attribute
101 * group row within a set of rows to be processed; no two EGO_USER_ATTR_ROW_OBJ
102 * elements in any single API call can share the same ROW_IDENTIFIER value.
103 * The attribute group whose row-level data this EGO_USER_ATTR_ROW_OBJ
104 * contains is identified either by ATTR_GROUP_ID or by the combination
105 * of ATTR_GROUP_APP_ID, ATTR_GROUP_TYPE, and ATTR_GROUP_NAME. (The first
106 * field is the numeric key for an attribute group, and the latter three
107 * fields form the composite key for an attribute group.)
108 * If the attribute group type has data levels defined and the attribute
109 * group is associated at a data level other than the highest data level
110 * defined for the attribute group type, the data level values are passed
111 * in DATA_LEVEL_1, DATA_LEVEL_2, and DATA_LEVEL_3 (as necessary).
112 * TRANSACTION_TYPE indicates the mode of DML operation to be performed
113 * on this attribute group row; valid values are
114 * EGO_USER_ATTRS_DATA_PVT.G_CREATE_MODE, EGO_USER_ATTRS_DATA_PVT.G_UPDATE_MODE,
115 * EGO_USER_ATTRS_DATA_PVT.G_DELETE_MODE, or, if the caller is uncertain
116 * whether this row exists in the database, EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE,
117 * which indicates that the API should determine whether to CREATE or
118 * UPDATE this attribute group row.
119 *
120 * ===========================
121 * = EGO_USER_ATTR_ROW_TABLE =
122 * ===========================
123 *
124 *<code><pre>
125 CREATE EGO_USER_ATTR_ROW_TABLE AS TABLE OF EGO_USER_ATTR_ROW_OBJ;
126 *</pre></code>
127 *
128 * ===============================
129 * = EGO_COL_NAME_VALUE_PAIR_OBJ =
130 * ===============================
131 *
132 *<code><pre>
133 CREATE EGO_COL_NAME_VALUE_PAIR_OBJ AS OBJECT
134 (
135 NAME VARCHAR2(30)
136 ,VALUE VARCHAR2(150)
137 );
138 *</pre></code>
139 *
140 * EGO_COL_NAME_VALUE_PAIR_OBJ contains the column name and corresponding
141 * value for one Primary Key, Classification Code, or other column relevant
142 * to the API.
143 *
144 * =================================
145 * = EGO_COL_NAME_VALUE_PAIR_ARRAY =
146 * =================================
147 *
148 *<code><pre>
149 CREATE EGO_COL_NAME_VALUE_PAIR_ARRAY AS VARRAY(6) OF EGO_COL_NAME_VALUE_PAIR_OBJ;
150 *</pre></code>
151 *
152 * ============================
153 * = EGO_USER_ATTR_CHANGE_OBJ =
154 * ============================
155 *
156 *<code><pre>
157 CREATE EGO_USER_ATTR_CHANGE_OBJ AS OBJECT
158 (
159 ROW_IDENTIFIER NUMBER
160 ,CHANGE_ID NUMBER
161 ,CHANGE_LINE_ID NUMBER
162 ,ACD_TYPE VARCHAR2(40))
163 );
164 *</pre></code>
165 *
166 * EGO_USER_ATTR_CHANGE_OBJ contains Change data for one attribute group
167 * row. ROW_IDENTIFIER is a foreign key that associates each
168 * EGO_USER_ATTR_CHANGE_OBJ to one EGO_USER_ATTR_ROW_OBJ (discussed above).
169 * CHANGE_ID and CHANGE_LINE_ID identify the Change Header and Line,
170 * respectively, that are applicable to the specified attribute group row.
171 * ACD_TYPE indicates whether the Change is of type Add, Change, or Delete
172 * (for details, refer to the Change Management documentation).
173 *
174 * ==============================
175 * = EGO_USER_ATTR_CHANGE_TABLE =
176 * ==============================
177 *
178 *<code><pre>
179 CREATE EGO_USER_ATTR_CHANGE_TABLE AS TABLE OF EGO_USER_ATTR_CHANGE_OBJ;
180 *</pre></code>
181 *
182 * ==============================
183 * = EGO_ATTR_GROUP_REQUEST_OBJ =
184 * ==============================
185 *
186 *<code><pre>
187 CREATE EGO_ATTR_GROUP_REQUEST_OBJ AS OBJECT
188 (
189 ATTR_GROUP_ID NUMBER
190 ,APPLICATION_ID NUMBER
191 ,ATTR_GROUP_TYPE VARCHAR2(40)
192 ,ATTR_GROUP_NAME VARCHAR2(30)
193 ,DATA_LEVEL_1 VARCHAR2(150)
194 ,DATA_LEVEL_2 VARCHAR2(150)
195 ,DATA_LEVEL_3 VARCHAR2(150)
196 ,ATTR_NAME_LIST VARCHAR2(3000)
197 );
198 *</pre></code>
199 *
200 * EGO_ATTR_GROUP_REQUEST_OBJ represents a request to retrieve data for
201 * one attribute group row from the database. It is very similar in
202 * structure to EGO_USER_ATTR_ROW_OBJ (discussed above); the notable
203 * difference is the field ATTR_NAME_LIST, which contains a comma-delimited
204 * list of attribute internal names specifying the attributes for which to
205 * retrieve data. If the field is empty, data will be fetched for all
206 * attributes in the attribute group row.
207 *
208 * ================================
209 * = EGO_ATTR_GROUP_REQUEST_TABLE =
210 * ================================
211 *
212 *<code><pre>
213 CREATE EGO_ATTR_GROUP_REQUEST_TABLE AS TABLE OF EGO_ATTR_GROUP_REQUEST_OBJ;
214 *</pre></code>
215 *
216 * ========================
217 * = EGO_VARCHAR_TBL_TYPE =
218 * ========================
219 *
220 *<code><pre>
221 CREATE EGO_VARCHAR_TBL_TYPE AS TABLE OF VARCHAR2(500);
222 *</pre></code>
223 *
224 * @rep:scope public
225 * @rep:product EGO
226 * @rep:displayname User-Defined Attributes
227 * @rep:lifecycle active
228 * @rep:category BUSINESS_ENTITY EGO_USER_DEFINED_ATTR_GROUP
229 */
230
231
232
233 ----------------
234 -- Procedures --
235 ----------------
236
237 /*#
238 * Processes User-Defined Attribute data for one object instance.
239 * Parameters provide identifying data and metadata for an object
240 * instance, along with an EGO_USER_ATTR_ROW_TABLE and an accompanying
241 * EGO_USER_ATTR_DATA_TABLE (described above). The procedure organizes
242 * the data from the two tables and calls Process_Row for each distinct
243 * attribute group row passing all the attribute data for that row.
244 * Current version: 1.0
245 *
246 * @param p_api_version Pass the value listed as 'Current version' above.
247 * @param p_object_name The name of the object to which this data applies
248 * (e.g., 'EGO_ITEM', 'PA_PROJECTS', etc.)
249 * @param p_attributes_row_table Contains row-level data and metadata
250 * about each attribute group being processed. See above for details
251 * about this data type.
252 * @param p_attributes_data_table Contains data and metadata about each
253 * attribute being processed. See above for details about this data type.
254 * @param p_pk_column_name_value_pairs Contains the Primary Key column
255 * names and values that identify the specific object instance to which
256 * this data applies. See above for details about this data type.
257 * @param p_class_code_name_value_pairs Contains the Classification Code(s)
258 * for the specific object instance to which this data applies. See
259 * above for details about this data type, and see User-Defined Attributes
260 * documentation for details about Classification Codes.
261 * @param p_user_privileges_on_object Contains the list of privileges
262 * granted to the current user on the specific object instance identified
263 * by p_pk_column_name_value_pairs. See above for details about this data
264 * type.
265 * @param p_entity_id Used in error reporting. See ERROR_HANDLER package
266 * for details.
267 * @param p_entity_index Used in error reporting. See ERROR_HANDLER package
268 * for details.
269 * @param p_entity_code Used in error reporting. See ERROR_HANDLER package
270 * for details.
271 * @param p_debug_level Used in debugging. Valid values range from 0 (no
272 * debugging) to 3 (full debugging). The debug file is created in the
273 * first directory in the list returned by the following query:
274 * SELECT VALUE FROM V$PARAMETER WHERE NAME = 'utl_file_dir';
275 * @param p_init_error_handler Indicates whether to initialize ERROR_HANDLER
276 * message stack (and open debug session, if applicable)
277 * @param p_write_to_concurrent_log Indicates whether to log ERROR_HANDLER
278 * messages to concurrent log (only applicable when called from concurrent
279 * program and when p_log_errors is passed as FND_API.G_TRUE).
280 * @param p_init_fnd_msg_list Indicates whether to initialize FND_MSG_PUB
281 * message stack.
282 * @param p_log_errors Indicates whether to write ERROR_HANDLER message
283 * stack to MTL_INTERFACE_ERRORS, the concurrent log (if applicable),
284 * and the debug file (if applicable); if FND_API.G_FALSE is passed,
285 * messages will still be added to ERROR_HANDLER's message stack, but
286 * the message stack will not be written to any destination.
287 * @param p_add_errors_to_fnd_stack Indicates whether messages written
288 * to ERROR_HANDLER message stack will also be written to FND_MSG_PUB
289 * message stack.
290 * @param p_commit Indicates whether to commit work for all attribute
291 * group rows that are processed successfully; if FND_API.G_FALSE is
292 * passed, the API will not commit any work.
293 * @param x_failed_row_id_list Returns a comma-delimited list of
294 * ROW_IDENTIFIERs (the field in EGO_USER_ATTR_ROW_OBJ, which is
295 * discussed above) indicating attribute group rows that failed
296 * in processing. An error will be logged for each failed row.
297 * @param x_return_status Returns one of three values indicating the
298 * most serious error encountered during processing:
299 * FND_API.G_RET_STS_SUCCESS if no errors occurred,
300 * FND_API.G_RET_STS_ERROR if at least one row encountered an error, and
301 * FND_API.G_RET_STS_UNEXP_ERROR if at least one row encountered an
302 * unexpected error.
303 * @param x_errorcode Reserved for future use.
304 * @param x_msg_count Indicates how many messages exist on ERROR_HANDLER
305 * message stack upon completion of processing.
306 * @param x_msg_data If exactly one message exists on ERROR_HANDLER
307 * message stack upon completion of processing, this parameter contains
308 * that message.
309 *
310 * @rep:scope public
311 * @rep:lifecycle active
312 * @rep:displayname Process User-Defined Attributes Data
313 */
314 PROCEDURE Process_User_Attrs_Data (
315 p_api_version IN NUMBER
316 ,p_object_name IN VARCHAR2
317 ,p_attributes_row_table IN EGO_USER_ATTR_ROW_TABLE
318 ,p_attributes_data_table IN EGO_USER_ATTR_DATA_TABLE
319 ,p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
320 ,p_class_code_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
321 ,p_user_privileges_on_object IN EGO_VARCHAR_TBL_TYPE DEFAULT NULL
322 ,p_entity_id IN NUMBER DEFAULT NULL
323 ,p_entity_index IN NUMBER DEFAULT NULL
324 ,p_entity_code IN VARCHAR2 DEFAULT NULL
325 ,p_debug_level IN NUMBER DEFAULT 0
326 ,p_init_error_handler IN VARCHAR2 DEFAULT FND_API.G_FALSE
327 ,p_write_to_concurrent_log IN VARCHAR2 DEFAULT FND_API.G_FALSE
328 ,p_init_fnd_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
329 ,p_log_errors IN VARCHAR2 DEFAULT FND_API.G_FALSE
330 ,p_add_errors_to_fnd_stack IN VARCHAR2 DEFAULT FND_API.G_FALSE
331 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
332 ,x_failed_row_id_list OUT NOCOPY VARCHAR2
333 ,x_return_status OUT NOCOPY VARCHAR2
334 ,x_errorcode OUT NOCOPY NUMBER
335 ,x_msg_count OUT NOCOPY NUMBER
336 ,x_msg_data OUT NOCOPY VARCHAR2
337 );
341 PROCEDURE Process_User_Attrs_Data (
338
339 /* Overload method with additional parameters x_extension_id, x_mode */
340
342 p_api_version IN NUMBER
343 ,p_object_name IN VARCHAR2
344 ,p_attributes_row_table IN EGO_USER_ATTR_ROW_TABLE
345 ,p_attributes_data_table IN EGO_USER_ATTR_DATA_TABLE
346 ,p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
347 ,p_class_code_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
348 ,p_user_privileges_on_object IN EGO_VARCHAR_TBL_TYPE DEFAULT NULL
349 ,p_entity_id IN NUMBER DEFAULT NULL
350 ,p_entity_index IN NUMBER DEFAULT NULL
351 ,p_entity_code IN VARCHAR2 DEFAULT NULL
352 ,p_debug_level IN NUMBER DEFAULT 0
353 ,p_init_error_handler IN VARCHAR2 DEFAULT FND_API.G_FALSE
354 ,p_write_to_concurrent_log IN VARCHAR2 DEFAULT FND_API.G_FALSE
355 ,p_init_fnd_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
356 ,p_log_errors IN VARCHAR2 DEFAULT FND_API.G_FALSE
357 ,p_add_errors_to_fnd_stack IN VARCHAR2 DEFAULT FND_API.G_FALSE
358 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
359 ,x_extension_id OUT NOCOPY NUMBER
360 ,x_mode OUT NOCOPY VARCHAR2
361 ,x_failed_row_id_list OUT NOCOPY VARCHAR2
362 ,x_return_status OUT NOCOPY VARCHAR2
363 ,x_errorcode OUT NOCOPY NUMBER
364 ,x_msg_count OUT NOCOPY NUMBER
365 ,x_msg_data OUT NOCOPY VARCHAR2
366 );
367
368
369 /*#
370 * Retrieves requested User-Defined Attribute data for one object instance.
371 * Parameters provide identifying data and metadata for an object instance,
372 * as well as an EGO_ATTR_GROUP_REQUEST_TABLE (described above) specifying
373 * the data to fetch. The procedure fetches the requested data from the
374 * database (transforming internal values to display values as necessary)
375 * and returns it in the form of two tables: an EGO_USER_ATTR_ROW_TABLE and
376 * a corresponding EGO_USER_ATTR_DATA_TABLE (both of which are described
377 * above).
378 * Current version: 1.0
379 *
380 * @param p_api_version Pass the value listed as 'Current version' above.
381 * @param p_object_name The name of the object to which this data applies
382 * (e.g., 'EGO_ITEM', 'PA_PROJECTS', etc.)
383 * @param p_pk_column_name_value_pairs Contains the Primary Key column
384 * names and values that identify the specific object instance to which
385 * this data applies. See above for details about this data type.
386 * @param p_attr_group_request_table Contains a list of elements, each
387 * of which identifies an attribute group whose data to retrieve. See
388 * above for details about this data type.
389 * @param p_user_privileges_on_object Contains the list of privileges
390 * granted to the current user on the specific object instance identified
391 * by p_pk_column_name_value_pairs. See above for details about this data
392 * type.
393 * @param p_entity_id Used in error reporting. See ERROR_HANDLER package
394 * for details.
395 * @param p_entity_index Used in error reporting. See ERROR_HANDLER package
396 * for details.
397 * @param p_entity_code Used in error reporting. See ERROR_HANDLER package
398 * for details.
399 * @param p_debug_level Used in debugging. Valid values range from 0 (no
400 * debugging) to 3 (full debugging). The debug file is created in the
401 * first directory in the list returned by the following query:
402 * SELECT VALUE FROM V$PARAMETER WHERE NAME = 'utl_file_dir';
403 * @param p_init_error_handler Indicates whether to initialize ERROR_HANDLER
404 * message stack (and open debug session, if applicable)
405 * @param p_init_fnd_msg_list Indicates whether to initialize FND_MSG_PUB
406 * message stack.
407 * @param p_add_errors_to_fnd_stack Indicates whether messages written
408 * to ERROR_HANDLER message stack will also be written to FND_MSG_PUB
409 * message stack.
410 * @param p_commit Indicates whether to commit work for all processing
411 * done by the API (which is currently none, as this API does not alter
412 * any database values); if FND_API.G_FALSE is passed, the API will not
413 * commit any work.
414 * @param x_attributes_row_table Contains row-level data and metadata
415 * about each attribute group whose data is being returned. See above
416 * for details about this data type.
417 * @param x_attributes_data_table Contains data and metadata about each
418 * attribute whose data is being returned. See above for details about
419 * this data type.
420 * @param x_return_status Returns one of three values indicating the
421 * most serious error encountered during processing:
422 * FND_API.G_RET_STS_SUCCESS if no errors occurred,
423 * FND_API.G_RET_STS_ERROR if at least one error occurred, and
424 * FND_API.G_RET_STS_UNEXP_ERROR if at least one unexpected error occurred.
425 * @param x_errorcode Reserved for future use.
426 * @param x_msg_count Indicates how many messages exist on ERROR_HANDLER
427 * message stack upon completion of processing.
428 * @param x_msg_data If exactly one message exists on ERROR_HANDLER
429 * message stack upon completion of processing, this parameter contains
430 * that message.
431 *
432 * @rep:scope public
433 * @rep:lifecycle active
434 * @rep:displayname Get User-Defined Attributes Data
435 */
439 ,p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
436 PROCEDURE Get_User_Attrs_Data (
437 p_api_version IN NUMBER
438 ,p_object_name IN VARCHAR2
440 ,p_attr_group_request_table IN EGO_ATTR_GROUP_REQUEST_TABLE
441 ,p_user_privileges_on_object IN EGO_VARCHAR_TBL_TYPE DEFAULT NULL
442 ,p_entity_id IN VARCHAR2 DEFAULT NULL
443 ,p_entity_index IN NUMBER DEFAULT NULL
444 ,p_entity_code IN VARCHAR2 DEFAULT NULL
445 ,p_debug_level IN NUMBER DEFAULT 0
446 ,p_init_error_handler IN VARCHAR2 DEFAULT FND_API.G_FALSE
447 ,p_init_fnd_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
448 ,p_add_errors_to_fnd_stack IN VARCHAR2 DEFAULT FND_API.G_FALSE
449 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
450 ,x_attributes_row_table OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
451 ,x_attributes_data_table OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
452 ,x_return_status OUT NOCOPY VARCHAR2
453 ,x_errorcode OUT NOCOPY NUMBER
454 ,x_msg_count OUT NOCOPY NUMBER
455 ,x_msg_data OUT NOCOPY VARCHAR2
456 );
457
458
459
460 /*#
461 * Copies all User-Defined Attribute data from one object instance to
462 * another object instance of the same type (e.g., from one EGO_ITEM to
463 * another or one PA_PROJECTS to another). Parameters provide
464 * identifying data and metadata for a source object instance and
465 * a destination object instance (referred to as "old" and "new",
466 * respectively); the procedure then copies all attribute group rows
467 * from the "old" instance (across all attribute group types) to
468 * the "new" instance.
469 * Current version: 1.0
470 *
471 * @param p_api_version Pass the value listed as 'Current version' above.
472 * @param p_application_id The application ID of the attribute groups that
473 * are to be copied (in other words, the first part of their composite key)
474 * @param p_object_id The numeric identifier of the object of which the
475 * source and destination object instances are examples (e.g., the numeric
476 * ID for EGO_ITEM, PA_PROJECTS, etc.)
477 * @param p_object_name The name of the object of which the source and
478 * destination object instances are examples (e.g., 'EGO_ITEM',
479 * 'PA_PROJECTS', etc.)
480 * @param p_old_pk_col_value_pairs Contains the Primary Key column
481 * names and values that identify the specific source object instance
482 * whose data is to be copied. See above for details about this data type.
483 * @param p_old_dtlevel_col_value_pairs If the attribute group type has data
484 * levels defined and the source object instance contains any attribute
485 * groups that are associated at a data level other than the highest level
486 * defined for the attribute group type (e.g., if the attribute group type
487 * is 'EGO_ITEMMGMT_GROUP' and the EGO_ITEM has at least one attribute
488 * group associated at the ITEM_REVISION_LEVEL), then this will contain
489 * data level column names and values up to and including those for the
490 * lowest data level at which any attribute group is associated. See above
491 * for details about this data type.
492 * @param p_new_pk_col_value_pairs As p_old_pk_col_value_pairs, except that
493 * these values identify the destination object instance instead of the
494 * source object instance. See above for details about this data type.
495 * @param p_new_dtlevel_col_value_pairs As p_old_dtlevel_col_value_pairs,
496 * except that these values are for the destination object instance
497 * instead of the source object instance. See above for details about
498 * this data type.
499 * @p_new_cc_col_value_pairs Contains the Classification Code(s) for the
500 * destination object instance. See above for details about this data
501 * type, and see User-Defined Attributes documentation for details about
502 * Classification Codes.
503 * @param p_init_error_handler Indicates whether to initialize ERROR_HANDLER
504 * message stack.
505 * @param p_init_fnd_msg_list Indicates whether to initialize FND_MSG_PUB
506 * message stack.
507 * @param p_add_errors_to_fnd_stack Indicates whether messages written
508 * to ERROR_HANDLER message stack will also be written to FND_MSG_PUB
509 * message stack.
510 * @param p_commit Indicates whether to commit work if all attribute
511 * group rows are copied successfully; if FND_API.G_FALSE is passed,
512 * the API will not commit any work.
513 * @param x_return_status Returns one of three values indicating the
514 * most serious error encountered during processing:
515 * FND_API.G_RET_STS_SUCCESS if no errors occurred,
516 * FND_API.G_RET_STS_ERROR if at least one error occurred, and
517 * FND_API.G_RET_STS_UNEXP_ERROR if at least one unexpected error occurred.
518 * @param x_errorcode Reserved for future use.
519 * @param x_msg_count Indicates how many messages exist on ERROR_HANDLER
520 * message stack upon completion of processing.
521 * @param x_msg_data If exactly one message exists on ERROR_HANDLER
522 * message stack upon completion of processing, then this parameter
523 * contains that message.
524 *
525 * @rep:scope public
526 * @rep:lifecycle active
527 * @rep:displayname Copy User-Defined Attributes Data
528 */
529 PROCEDURE Copy_User_Attrs_Data (
530 p_api_version IN NUMBER
531 ,p_application_id IN NUMBER
532 ,p_object_id IN NUMBER DEFAULT NULL
536 ,p_new_pk_col_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
533 ,p_object_name IN VARCHAR2 DEFAULT NULL
534 ,p_old_pk_col_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
535 ,p_old_dtlevel_col_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
537 ,p_new_dtlevel_col_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
538 ,p_new_cc_col_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
539 ,p_init_error_handler IN VARCHAR2 DEFAULT FND_API.G_FALSE
540 ,p_init_fnd_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
541 ,p_add_errors_to_fnd_stack IN VARCHAR2 DEFAULT FND_API.G_FALSE
542 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
543 ,x_return_status OUT NOCOPY VARCHAR2
544 ,x_errorcode OUT NOCOPY NUMBER
545 ,x_msg_count OUT NOCOPY NUMBER
546 ,x_msg_data OUT NOCOPY VARCHAR2
547 );
548
549 /*#
550 * Validate_Required_Attrs validates data for one object instance by
551 * checking which fields required fields does not have a value.
552 * The procedure takes in a primary key object, the object name,
553 * class code and data level information, as well as a list of
554 * attribute group types on which object data should be
555 * validated.
556 * The procedure returns a table of user attribute object listing
557 * required attributes for which the object does not have a value.
558 * Current version: 1.0
559 *
560 * @param p_api_version Pass the value listed as 'Current version' above.
561 * @param p_object_name The name of the object to which this data applies
562 * (e.g., 'EGO_ITEM', 'PA_PROJECTS', etc.)
563 * @param p_pk_column_name_value_pairs Contains the Primary Key column
564 * names and values that identify the specific object instance to which
565 * this data applies. See above for details about this data type.
566 * @param p_class_code_name_value_pairs Contains the Classification Code(s)
567 * for the specific object instance to which this data applies. See
568 * above for details about this data type, and see User-Defined Attributes
569 * documentation for details about Classification Codes.
570 * @param p_data_level_name_value_pairs Contains data level information
571 * for the specific object instance to which this data applies. See
572 * above for details about this data type.
573 * @param p_attr_group_type_table Contains a list of elements, each
574 * of which identifies an attribute group type whose data to retrieve.
575 * @param p_entity_id Used in error reporting. See ERROR_HANDLER package
576 * for details.
577 * @param p_entity_index Used in error reporting. See ERROR_HANDLER package
578 * for details.
579 * @param p_entity_code Used in error reporting. See ERROR_HANDLER package
580 * for details.
581 * @param p_debug_level Used in debugging. Valid values range from 0 (no
582 * debugging) to 3 (full debugging). The debug file is created in the
583 * first directory in the list returned by the following query:
584 * SELECT VALUE FROM V$PARAMETER WHERE NAME = 'utl_file_dir';
585 * @param p_init_error_handler Indicates whether to initialize ERROR_HANDLER
586 * message stack (and open debug session, if applicable)
587 * @param p_write_to_concurrent_log Indicates whether to log ERROR_HANDLER
588 * messages to concurrent log (only applicable when called from concurrent
589 * program and when p_log_errors is passed as FND_API.G_TRUE).
590 * @param p_init_fnd_msg_list Indicates whether to initialize FND_MSG_PUB
591 * message stack.
592 * @param p_log_errors Indicates whether to write ERROR_HANDLER message
593 * stack to MTL_INTERFACE_ERRORS, the concurrent log (if applicable),
594 * and the debug file (if applicable); if FND_API.G_FALSE is passed,
595 * messages will still be added to ERROR_HANDLER's message stack, but
596 * the message stack will not be written to any destination.
597 * @param p_add_errors_to_fnd_stack Indicates whether messages written
598 * to ERROR_HANDLER message stack will also be written to FND_MSG_PUB
599 * message stack.
600 * @param x_attributes_req_table Returns a table of user attribute
601 * object listing required attributes for which the object does not
602 * have a value.
603 * @param x_return_status Returns one of three values indicating the
604 * most serious error encountered during processing:
605 * FND_API.G_RET_STS_SUCCESS if no errors occurred
606 * FND_API.G_RET_STS_ERROR if at least one error occurred
607 * FND_API.G_RET_STS_UNEXP_ERROR if at least one unexpected error occurred
608 * @param x_errorcode Reserved for future use.
609 * @param x_msg_count Indicates how many messages exist on ERROR_HANDLER
610 * message stack upon completion of processing.
611 * @param x_msg_data If exactly one message exists on ERROR_HANDLER
612 * message stack upon completion of processing, this parameter contains
613 * that message.
614 *
615 * @rep:scope public
616 * @rep:lifecycle active
617 * @rep:displayname Validate Required Attributes
618 */
619
620 PROCEDURE Validate_Required_Attrs (
621 p_api_version IN NUMBER
622 ,p_object_name IN VARCHAR2
623 -- FND_OBJECTS.OBJECT_NAME
624 ,p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
625 -- Attr values to be validated
626 ,p_class_code_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
630 -- FND_DESCRIPTIVE_FLEXS.APPLICATION_TABLE_NAME
627 ,p_data_level_name IN EGO_DATA_LEVEL_B.DATA_LEVEL_NAME%TYPE := NULL
628 ,p_data_level_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
629 ,p_attr_group_type_table IN EGO_VARCHAR_TBL_TYPE
631 ,p_entity_id IN NUMBER
632 ,p_entity_index IN NUMBER
633 ,p_entity_code IN VARCHAR2
634 ,p_debug_level IN NUMBER
635 ,p_init_error_handler IN VARCHAR2
636 ,p_write_to_concurrent_log IN VARCHAR2
637 ,p_init_fnd_msg_list IN VARCHAR2
638 ,p_log_errors IN VARCHAR2
639 ,p_add_errors_to_fnd_stack IN VARCHAR2
640 ,x_attributes_req_table OUT NOCOPY EGO_USER_ATTR_TABLE
641 ,x_return_status OUT NOCOPY VARCHAR2
642 ,x_errorcode OUT NOCOPY NUMBER
643 ,x_msg_count OUT NOCOPY NUMBER
644 ,x_msg_data OUT NOCOPY VARCHAR2
645 );
646
647
648
649
650
651 /*#
652 * Build_Attr_Group_Row_Table builds up the EGO_USER_ATTR_ROW_TABLE.
653 * an instance of EGO_USER_ATTR_ROW_OBJ is built using the passed in
654 * infomation and appended to the table here.
655 * @param p_attr_group_row_table This is the table to which the row
656 * object is added.
657 * @param p_row_identifier Row identifier of the logical attribute
658 * group row.
659 * @param p_attr_group_id Attribute group id of the passed in attr group
660 * row.
661 * @param p_attr_group_app_id Application Id.
662 * @param p_attr_group_type Attribute group type of the attr group row
663 * being created.
664 * @param p_attr_group_name Attribute group internal name.
665 * @param p_data_level The data level internal name for which the attribute
666 * group data is being processed.
667 * @param p_data_level_1 The pk1 column value for the data level.
668 * @param p_data_level_2 The pk2 column value for the data level.
669 * @param p_data_level_3 The pk3 column value for the data level.
670 * @param p_data_level_4 The pk4 column value for the data level.
671 * @param p_data_level_5 The pk5 column value for the data level.
672 * @param p_transaction_type Transaction type, i.e. 'SYNC'/'CREATE'/'UPDATE'/'DELETE'
673 *
674 * @rep:scope public
675 * @rep:lifecycle active
676 * @rep:displayname Build attribute group row table.
677 */
678 FUNCTION Build_Attr_Group_Row_Table( p_attr_group_row_table IN EGO_USER_ATTR_ROW_TABLE
679 ,p_row_identifier IN NUMBER
680 ,p_attr_group_id IN NUMBER DEFAULT NULL
681 ,p_attr_group_app_id IN NUMBER
682 ,p_attr_group_type IN VARCHAR2
683 ,p_attr_group_name IN VARCHAR2
684 ,p_data_level IN VARCHAR2 DEFAULT NULL
685 ,p_data_level_1 IN VARCHAR2 DEFAULT NULL
686 ,p_data_level_2 IN VARCHAR2 DEFAULT NULL
687 ,p_data_level_3 IN VARCHAR2 DEFAULT NULL
688 ,p_data_level_4 IN VARCHAR2 DEFAULT NULL
689 ,p_data_level_5 IN VARCHAR2 DEFAULT NULL
690 ,p_transaction_type IN VARCHAR2
691 )
692 RETURN EGO_USER_ATTR_ROW_TABLE;
693
694 /*#
695 * Build_Attr_Group_Row_Object builds and trturns an instance of
696 * EGO_USER_ATTR_ROW_OBJ using the passed in infomation.
697 * @param p_row_identifier Row identifier of the logical attribute
698 * group row.
699 * @param p_attr_group_id Attribute group id of the passed in attr group
700 * row.
701 * @param p_attr_group_app_id Application Id.
702 * @param p_attr_group_type Attribute group type of the attr group row
703 * being created.
704 * @param p_attr_group_name Attribute group internal name.
705 * @param p_data_level The data level internal name for which the attribute
706 * group data is being processed.
707 * @param p_data_level_1 The pk1 column value for the data level.
708 * @param p_data_level_2 The pk2 column value for the data level.
709 * @param p_data_level_3 The pk3 column value for the data level.
710 * @param p_data_level_4 The pk4 column value for the data level.
711 * @param p_data_level_5 The pk5 column value for the data level.
712 * @param p_transaction_type Transaction type, i.e. 'SYNC'/'CREATE'/'UPDATE'/'DELETE'
713 *
714 * @rep:scope public
715 * @rep:lifecycle active
716 * @rep:displayname build attr group row object.
717 */
718
719 FUNCTION Build_Attr_Group_Row_Object( p_row_identifier IN NUMBER
720 ,p_attr_group_id IN NUMBER DEFAULT NULL
721 ,p_attr_group_app_id IN NUMBER
722 ,p_attr_group_type IN VARCHAR2
723 ,p_attr_group_name IN VARCHAR2
724 ,p_data_level IN VARCHAR2 DEFAULT NULL
725 ,p_data_level_1 IN VARCHAR2 DEFAULT NULL
726 ,p_data_level_2 IN VARCHAR2 DEFAULT NULL
727 ,p_data_level_3 IN VARCHAR2 DEFAULT NULL
728 ,p_data_level_4 IN VARCHAR2 DEFAULT NULL
729 ,p_data_level_5 IN VARCHAR2 DEFAULT NULL
730 ,p_transaction_type IN VARCHAR2
731 )
732 RETURN EGO_USER_ATTR_ROW_OBJ;
733
734
735 /*#
736 * Build_Attr_Group_Request_Table builds up the EGO_ATTR_GROUP_REQUEST_TABLE.
737 * An instance of EGO_ATTR_GROUP_REQUEST_OBJ is built using the passed in
738 * infomation and appended to the table here.
739 * @param p_ag_req_table This is the table to which the row object is added.
740 * @param p_attr_group_id Attribute group id for which the request object
741 * is to be built.
742 * @param p_application_id Application Id.
743 * @param p_attr_group_type Attribute group type of the attribute group.
744 * @param p_attr_group_name Attribute group internal name.
745 * @param p_data_level The data level internal name for which the attribute
746 * group request object is being built.
747 * @param p_data_level_1 The pk1 column value for the data level.
748 * @param p_data_level_2 The pk2 column value for the data level.
749 * @param p_data_level_3 The pk3 column value for the data level.
750 * @param p_data_level_4 The pk4 column value for the data level.
751 * @param p_data_level_5 The pk5 column value for the data level.
752 * @param p_attr_name_list Attribute name list.
753 *
754 * @rep:scope public
755 * @rep:lifecycle active
756 * @rep:displayname build attribute group request table.
757 */
758
759 FUNCTION Build_Attr_Group_Request_Table( p_ag_req_table IN EGO_ATTR_GROUP_REQUEST_TABLE
760 ,p_attr_group_id IN NUMBER DEFAULT NULL
761 ,p_application_id IN NUMBER
762 ,p_attr_group_type IN VARCHAR2
763 ,p_attr_group_name IN VARCHAR2
764 ,p_data_level IN VARCHAR2 DEFAULT NULL
765 ,p_data_level_1 IN VARCHAR2 DEFAULT NULL
766 ,p_data_level_2 IN VARCHAR2 DEFAULT NULL
767 ,p_data_level_3 IN VARCHAR2 DEFAULT NULL
768 ,p_data_level_4 IN VARCHAR2 DEFAULT NULL
769 ,p_data_level_5 IN VARCHAR2 DEFAULT NULL
770 ,p_attr_name_list IN VARCHAR2 DEFAULT NULL
771 )
772 RETURN EGO_ATTR_GROUP_REQUEST_TABLE;
773
774 /*#
775 * Build_Attr_Group_Request_Obj creates and returns an instance of
776 * EGO_ATTR_GROUP_REQUEST_OBJ using the passed in infomation.
777 * @param p_attr_group_id Attribute group id for which the request object
778 * is to be built.
779 * @param p_application_id Application Id.
780 * @param p_attr_group_type Attribute group type of the attribute group.
781 * @param p_attr_group_name Attribute group internal name.
782 * @param p_data_level The data level internal name for which the attribute
783 * group request object is being built.
784 * @param p_data_level_1 The pk1 column value for the data level.
785 * @param p_data_level_2 The pk2 column value for the data level.
786 * @param p_data_level_3 The pk3 column value for the data level.
787 * @param p_data_level_4 The pk4 column value for the data level.
788 * @param p_data_level_5 The pk5 column value for the data level.
789 * @param p_attr_name_list Attribute name list.
790 *
791 * @rep:scope public
792 * @rep:lifecycle active
793 * @rep:displayname build attribute group request table.
794 */
795 FUNCTION Build_Attr_Group_Request_Obj (p_attr_group_id IN NUMBER DEFAULT NULL
796 ,p_application_id IN NUMBER
797 ,p_attr_group_type IN VARCHAR2
798 ,p_attr_group_name IN VARCHAR2
799 ,p_data_level IN VARCHAR2 DEFAULT NULL
800 ,p_data_level_1 IN VARCHAR2 DEFAULT NULL
801 ,p_data_level_2 IN VARCHAR2 DEFAULT NULL
802 ,p_data_level_3 IN VARCHAR2 DEFAULT NULL
803 ,p_data_level_4 IN VARCHAR2 DEFAULT NULL
804 ,p_data_level_5 IN VARCHAR2 DEFAULT NULL
805 ,p_attr_name_list IN VARCHAR2 DEFAULT NULL
806 )
807 RETURN EGO_ATTR_GROUP_REQUEST_OBJ;
808
809
810
811 END EGO_USER_ATTRS_DATA_PUB;
812