1 PACKAGE EGO_USER_ATTRS_BULK_PVT AUTHID CURRENT_USER AS
2 /* $Header: EGOVBUAS.pls 120.16.12020000.4 2012/07/13 01:37:57 mshirkol ship $ */
3
4 ----------------
5 -- Data Types --
6 ----------------
7
8 TYPE EGO_USER_ATTRS_BULK_STR_TBL IS TABLE OF VARCHAR2(1000);
9 TYPE EGO_USER_ATTRS_BULK_NUM_TBL IS TABLE OF NUMBER;
10 TYPE EGO_USER_ATTRS_BULK_DATE_TBL IS TABLE OF DATE;
11
12
13
14 ----------------------
15 -- Global Variables --
16 ----------------------
17
18 G_NULL_TOKEN_NUM CONSTANT VARCHAR2(8) := '9.97E125';
19 G_NULL_TOKEN_STR CONSTANT VARCHAR2(6) := 'CHR(1)';
20 G_NULL_TOKEN_DATE CONSTANT VARCHAR2(20) := 'TO_DATE(''3'',''J'')';
21
22 G_NULL_NUM_VAL CONSTANT VARCHAR2(8) := '9.99E125';
23 G_NULL_CHAR_VAL CONSTANT VARCHAR2(2) := '!';
24 G_NULL_DATE_VAL CONSTANT VARCHAR2(20) := 'TO_DATE(''1'',''j'')';
25 G_NULL_NUM_VAL_STR CONSTANT VARCHAR2(130):= '999000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000';
26
27 G_NULL_TOKEN_NUM_1 CONSTANT VARCHAR2(8) := '9.91E125';
28 G_NULL_TOKEN_STR_1 CONSTANT VARCHAR2(6) := 'CHR(2)';
29 G_NULL_TOKEN_DATE_1 CONSTANT VARCHAR2(20) := 'TO_DATE(''5'',''J'')';
30
31
32 /*
33 * PROCESS_STATUS constants
34 * ------------------------
35 * The following constants are used in the PROCESS_STATUS column of the table
36 *
37 * G_PS_TO_BE_PROCESSED: row should be processed
38 * G_PS_IN_PROCESS: row is being processed
39 * G_PS_GENERIC_ERROR: some row in the same logical Attribute Group as this row
40 * encountered an error (all error statuses described below
41 * are set to this status at the completion of processing)
42 * G_PS_SUCCESS: row processed succcessfully
43 *
44 * In addition to the four basic error statuses above, there are several internal
45 * statuses that may appear in the interface table at times (for instance, while
46 * a data set is being processed, or if the process encountered a fatal error)
47 */
48 G_PS_TO_BE_PROCESSED CONSTANT NUMBER := 1;
49 G_PS_IN_PROCESS CONSTANT NUMBER := 2;
50 G_PS_GENERIC_ERROR CONSTANT NUMBER := 3;
51 G_PS_SUCCESS CONSTANT NUMBER := 4;
52
53 /* 3*/ G_PS_BAD_ATTR_OR_AG_METADATA CONSTANT NUMBER := POWER(2,3); -- 8
54 /* 4*/ G_PS_MULTIPLE_ENTRIES CONSTANT NUMBER := POWER(2,4); -- 16
55 /* 5*/ G_PS_MULTIPLE_VALUES CONSTANT NUMBER := POWER(2,5); -- 32
56 /* 6*/ G_PS_NO_PRIVILEGES CONSTANT NUMBER := POWER(2,6); -- 64
57 /* 7*/ G_PS_VALUE_NOT_IN_VS CONSTANT NUMBER := POWER(2,7); -- 128
58 /* 8*/ G_PS_INVALID_NUMBER_DATA CONSTANT NUMBER := POWER(2,8); -- 256
59 /* 9*/ G_PS_INVALID_DATE_DATA CONSTANT NUMBER := POWER(2,9); -- 512
60 /*10*/ G_PS_INVALID_DATE_TIME_DATA CONSTANT NUMBER := POWER(2,10); -- 1024
61 /*11*/ G_PS_MAX_LENGTH_VIOLATION CONSTANT NUMBER := POWER(2,11); -- 2048
62 /*12*/ G_PS_BAD_TTYPE_UPDATE CONSTANT NUMBER := POWER(2,12); -- 4096
63 /*13*/ G_PS_BAD_TTYPE_CREATE CONSTANT NUMBER := POWER(2,13); -- 8192
64 /*14*/ G_PS_BAD_TTYPE_DELETE CONSTANT NUMBER := POWER(2,14); -- 16384
65 /*15*/ G_PS_REQUIRED_ATTRIBUTE CONSTANT NUMBER := POWER(2,15); -- 32768
66 /*16*/ G_PS_AG_NOT_ASSOCIATED CONSTANT NUMBER := POWER(2,16); -- 65536
67 /*17*/ G_PS_IDENTICAL_ROWS CONSTANT NUMBER := POWER(2,17); -- 131072
68 /*18*/ G_PS_BAD_PK_VAL CONSTANT NUMBER := POWER(2,18); -- 262144
69 /*19*/ G_PS_DATA_IN_WRONG_COL CONSTANT NUMBER := POWER(2,19); -- 524288
70 /*20*/ G_PS_BAD_ATTRS_IN_TVS_WHERE CONSTANT NUMBER := POWER(2,20); -- 1048576
71 /*21*/ G_PS_VALUE_NOT_IN_TVS CONSTANT NUMBER := POWER(2,21); -- 2097152
72 /*22*/ G_PS_BAD_TVS_SETUP CONSTANT NUMBER := POWER(2,22); -- 4194304
73 /*23*/ G_PS_MAX_VAL_VIOLATION CONSTANT NUMBER := POWER(2,23); -- 8388608
74 /*24*/ G_PS_MIN_VAL_VIOLATION CONSTANT NUMBER := POWER(2,24); -- 16777216
75 /*25*/ G_PS_BAD_ATTR_GRP_ID CONSTANT NUMBER := POWER(2,25); -- 33554432
76 /*26*/ G_PS_TL_COL_IS_A_UK CONSTANT NUMBER := POWER(2,26); -- 67108864
77 /*27*/ G_PS_PRE_EVENT_FAILED CONSTANT NUMBER := POWER(2,27); -- 134217728
78 /*28*/ G_PS_INVALID_UOM CONSTANT NUMBER := POWER(2,28); -- 268435456
79 /*29*/ G_PS_VAL_RANGE_VIOLATION CONSTANT NUMBER := POWER(2,29); -- 536870912
80 /*30*/ G_PS_INVALID_DATA_LEVEL CONSTANT NUMBER := POWER(2,30); -- 1073741824
81 /*
82 ****** THE ERROR STATUS G_PS_OTHER_ATTRS_INVALID SHOULD ALWAYS BE THE LAST ONE ******
83 */
84 /*30*/ G_PS_OTHER_ATTRS_INVALID CONSTANT NUMBER := POWER(2,31); --
85 --
86 -- DO NOT EXCEED THE LIMIT
87 -- BITAND is failing in error_log for NUMBER > 31
88 --
89
90
91 ----------------
92 -- Procedures --
93 ----------------
94
95 ------------------------------------------------------------------------------------------
96 --API Name : Bulk_Load_User_Attrs_Data
97 --Description : The api would would do all the validations for the rows in interface table
98 -- and updloads the valid rows. For bad rows the error is reported and the row
99 -- is marked as bad.
100
101 --Parameteres required : p_api_version
102 -- p_application_id
103 -- p_attr_group_type
104 -- p_object_name
105 -- p_interface_table_name
106 -- p_data_set_id
107
108 --Return parametere : x_return_status = 1 if no associations exist
109 -- 0 in all other cases
110 --
111 ------------------------------------------------------------------------------------------
112
113 PROCEDURE Bulk_Load_User_Attrs_Data (
114 p_api_version IN NUMBER
115 ,p_application_id IN NUMBER
116 ,p_attr_group_type IN VARCHAR2
117 ,p_object_name IN VARCHAR2
118 ,p_hz_party_id IN VARCHAR2
119 ,p_interface_table_name IN VARCHAR2
120 ,p_data_set_id IN NUMBER
121 ,p_entity_id IN NUMBER DEFAULT NULL
122 ,p_entity_index IN NUMBER DEFAULT NULL
123 ,p_entity_code IN VARCHAR2 DEFAULT NULL
124 ,p_debug_level IN NUMBER DEFAULT 0
125 ,p_init_error_handler IN VARCHAR2 DEFAULT FND_API.G_TRUE
126 ,p_init_fnd_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
127 ,p_log_errors IN VARCHAR2 DEFAULT FND_API.G_FALSE
128 ,p_add_errors_to_fnd_stack IN VARCHAR2 DEFAULT FND_API.G_FALSE
129 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
130 ,p_default_dl_view_priv_list IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
131 ,p_default_dl_edit_priv_list IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
132 ,p_default_view_privilege IN VARCHAR2 DEFAULT NULL
133 ,p_default_edit_privilege IN VARCHAR2 DEFAULT NULL
134 ,p_privilege_predicate_api_name IN VARCHAR2 DEFAULT NULL
135 ,p_related_class_codes_query IN VARCHAR2 DEFAULT '-100'
136 ,p_validate IN BOOLEAN DEFAULT TRUE
137 ,p_do_dml IN BOOLEAN DEFAULT TRUE
138 ,p_do_req_def_valiadtion IN BOOLEAN DEFAULT TRUE
139 ,x_return_status OUT NOCOPY VARCHAR2
140 ,x_errorcode OUT NOCOPY NUMBER
141 ,x_msg_count OUT NOCOPY NUMBER
142 ,x_msg_data OUT NOCOPY VARCHAR2
143 );
144
145
146
147
148 ------------------------------------------------------------------------------------------
149 --API Name : Get_Datatype_Error_Val
150 --Description : The api would return the error code to be added to process status in case
151 -- the datatype of the attribute is not same as the data type of the entered
152 -- data.
153
154 --Parameteres required : p_value_to_convert
155 -- p_datatype
156 ------------------------------------------------------------------------------------------
157 FUNCTION Get_Datatype_Error_Val (
158 p_value_to_convert IN VARCHAR2
159 ,p_datatype IN VARCHAR2
160 ) RETURN NUMBER;
161
162
163
164 ------------------------------------------------------------------------------------------
165 --API Name : Get_Max_Min_Error_Val
166 --Description : The api would return the error code to be added to process status in case
167 -- the value entered for the attribute is not in the limits given in the value
168 -- set.
169
170 --Parameteres required : p_value_to_check
171 -- p_datatype
172 -- p_min_value
173 -- p_max_value
174 ------------------------------------------------------------------------------------------
175 FUNCTION Get_Max_Min_Error_Val (
176 p_value_to_check IN VARCHAR2
177 ,p_datatype IN VARCHAR2
178 ,p_min_value IN VARCHAR2
179 ,p_max_value IN VARCHAR2
180 ) RETURN NUMBER;
181
182
183
184 ------------------------------------------------------------------------------------------
185 --API Name : Get_Date
186 --Description : The api would return the date value of a given string, in case the given
187 -- string is not in the valid format or is not a valid date it returns a null
188
189 --Parameteres required : p_date
190 ------------------------------------------------------------------------------------------
191
192 FUNCTION Get_Date (
193 p_date IN VARCHAR2
194 ,p_format IN VARCHAR2 DEFAULT NULL
195 ) RETURN DATE;
196
197
198
199
200
201
202 ---------------------------------------------------------------------------------------------------------------------
203 --API Name : Apply_Template_On_Intf_Table
204 --Description : The api would apply the attribute values in the template to the interface
205 -- table, this api should be called after the rows in the interface table are
206 -- validated.
207 --Parameteres required : p_api_version
208 -- p_application_id
209 -- p_object_name
210 -- p_interface_table_name
211 -- p_data_set_id
212 -- p_template_id
213 -- p_Classification_code
214 -- p_attr_group_type
215 -- p_target_entity_sql : this parameter should contain a query which would give a list of
216 -- entities on which the template is to be applied and which template
217 -- is to be applied and a rownum column.
218 -- e.g. 'SELECT ROWNUM ENTITYNUMBER,
219 -- Decode(ROWNUM, 1,256678,2,256679,null) INVENTORY_ITEM_ID,
220 -- 204 ORGANIZATION_ID, 14978 ITEM_CATALOG_GROUP_ID, -
221 -- 1 TEMPLATE_ID
222 -- FROM ego_itm_usr_Attr_intrfc WHERE ROWNUM<3';
223 -- p_class_code_hierarchy_sql : this would take in the comma seperated list or a SQL which returns
224 -- the class_codes which might have to be considered while applying the
225 -- template.
226 --
227 -- p_hierarchy_template_tbl_sql: this paramter takes in a wrapper SQL over the template table to tweak
228 -- the values to be applied bby the template. Items uses it to give in the
229 -- SQL which returns the attr values for the template even for parent ICC's
230 --Return parametere : x_return_status = 1 if no associations exist
231 -- 0 in all other cases
232 -- x_return_status
233 -- x_errorcode
234 -- x_msg_count
235 -- x_msg_data
236 --
237 --
238 ----------------------------------------------------------------------------------------------------------------------
239
240
241
242 PROCEDURE Apply_Template_On_Intf_Table(
243 p_api_version IN NUMBER
244 ,p_application_id IN NUMBER
245 ,p_object_name IN VARCHAR2
246 ,p_interface_table_name IN VARCHAR2
247 ,p_data_set_id IN NUMBER
248 ,p_attr_group_type IN VARCHAR2
249 ,p_request_id IN NUMBER
250 ,p_program_application_id IN NUMBER
251 ,p_program_id IN NUMBER
252 ,p_program_update_date IN DATE
253 ,p_current_user_party_id IN NUMBER
254 ,p_target_entity_sql IN VARCHAR2
255 ,p_process_status IN NUMBER DEFAULT G_PS_IN_PROCESS
256 ,p_class_code_hierarchy_sql IN VARCHAR2 DEFAULT NULL
257 ,p_hierarchy_template_tbl_sql IN VARCHAR2 DEFAULT NULL
258 ,x_return_status OUT NOCOPY VARCHAR2
259 ,x_errorcode OUT NOCOPY NUMBER
260 ,x_msg_count OUT NOCOPY NUMBER
261 ,x_msg_data OUT NOCOPY VARCHAR2
262 );
263
264
265
266
267
268 -------------------------------------------------------------------------------------------------------
269 --API Name : Insert_Default_Val_Rows
270 --Description : This API would insert rows with default values for attributes in attribute
271 -- groups not present in the interface table. Here only single row attr groups
272 -- with no required attrs are processed.
273
274 --Parameteres : p_api_version : Api version
275 --Parameteres : p_application_id : Application Id
276 --Parameteres : p_attr_group_type : The type of attr groups to be processed for the given
277 -- data set id.
278 --Parameteres : p_object_name : Object name.
279 --Parameteres : p_interface_table_name : Interface table name for the UDA.
280 --Parameteres : p_data_set_id : Data set to be processed.
281 --Parameteres : p_target_entity_sql : This SQL should return all the entities to be processed,
282 -- it should give the pk's, class code and data level of
283 -- the entity. Sample SQL:
284 -- 'SELECT INVENTORY_ITEM_ID,
285 -- ORGANIZATION_ID,
286 -- ITEM_CATALOG_GROUP_ID,
287 -- REVISION_ID
288 -- FROM MTL_SYSTEM_ITEMS_INTERFACE
289 -- WHERE SET_PROCESS_ID = 2910
290 -- AND PROCESS_FLAG = 7'
291
292 --Parameteres : p_additional_class_Code_query : This sQL should return all the classification
293 -- codes for which attr group associations are to be
294 -- considered, for example this can give all the parent
295 -- class codes for an entity. Sampl SQL:
296 -- SELECT CHILD_CATALOG_GROUP_ID
297 -- FROM EGO_ITEM_CAT_DENORM_HIER
298 -- WHERE PARENT_CATALOG_GROUP_ID = ENTITY.ITEM_CATALOG_GROUP_ID
299 --Parameteres : p_extra_column_names: This parapeter can be used to specify the columns whcih the
300 -- defaulting API should populate while creating attr rows.
301 -- It takes in comma seperated column names with no comma at the
302 -- beginning and the end.
303 --Parameteres : p_extra_column_values:This parapeter would contain the values for the columns passed
304 -- in the parameter p_extra_column_names.
305 -- It takes in comma seperated values with no comma at the
306 -- beginning and the end.
307 -- p_extra_column_names and p_extra_column_values should co exist.
311 PROCEDURE Insert_Default_Val_Rows (
308 --Parameteres : p_commit : Should the changes made be commited in the API
309 ------------------------------------------------------------------------------------------
310
312 p_api_version IN NUMBER
313 ,p_application_id IN NUMBER
314 ,p_attr_group_type IN VARCHAR2
315 ,p_object_name IN VARCHAR2
316 ,p_interface_table_name IN VARCHAR2
317 ,p_data_set_id IN NUMBER
318 ,p_target_entity_sql IN VARCHAR2
319 ,p_attr_groups_to_exclude IN VARCHAR2 DEFAULT NULL
320 ,p_additional_class_Code_query IN VARCHAR2 DEFAULT NULL
321 ,p_extra_column_names IN VARCHAr2 DEFAULT NULL
322 ,p_extra_column_values IN VARCHAR2 DEFAULT NULL
323 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
324 ,p_process_status IN NUMBER DEFAULT G_PS_IN_PROCESS
325 ,p_comp_seq_id IN NUMBER DEFAULT NULL
326 ,p_bill_seq_id IN NUMBER DEFAULT NULL
327 ,p_structure_type_id IN NUMBER DEFAULT NULL
328 ,p_data_level_column IN VARCHAR2 DEFAULT NULL
329 ,p_datalevel_id IN NUMBER DEFAULT NULL
330 ,p_context_id IN NUMBER DEFAULT NULL
331 ,p_transaction_id IN NUMBER DEFAULT NULL
332 ,x_return_status OUT NOCOPY VARCHAR2
333 ,x_msg_data OUT NOCOPY VARCHAR2
334 );
335
336
337 -------------------------------------------------------------------------------------------------------
338 --API Name : Mark_Unchanged_Attr_Rows
339 --Description : This API would insert rows with default values for attributes in attribute
340 -- groups not present in the interface table. Here only single row attr groups
341 -- with no required attrs are processed.
342
343 --Parameteres : p_api_version : Api version
344 --Parameteres : p_application_id : Application Id
345 --Parameteres : p_attr_group_type : The type of attr groups to be processed for the given
346 -- data set id.
347 --Parameteres : p_object_name : Object name.
348 --Parameteres : p_interface_table_name : Interface table name for the UDA.
349 --Parameteres : p_data_set_id : Data set to be processed.
350 --Parameteres : p_commit : Should the changes made be commited in the API
351 -------------------------------------------------------------------------------------------------------
352
353
354 PROCEDURE Mark_Unchanged_Attr_Rows ( p_api_version IN NUMBER
355 ,p_application_id IN NUMBER
356 ,p_attr_group_type IN VARCHAR2
357 ,p_object_name IN VARCHAR2
358 ,p_interface_table_name IN VARCHAR2
359 ,p_data_set_id IN NUMBER
360 ,p_new_status IN NUMBER
361 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
362 ,x_return_status OUT NOCOPY VARCHAR2
363 ,x_msg_data OUT NOCOPY VARCHAR2
364 );
365
366
367 -- abedajna Bug 6322809
368 function get_num_occur (p_string in varchar2, p_char in varchar2) return number;
369
370 -- abedajna Bug 6322809
371 function get_order_by (p_whereclause in varchar2, p_len in number) return number;
372
373 -- abedajna Bug 6322809
374 function process_whereclause (p_whereclausein in varchar2) return varchar2;
375
376
377
378 END EGO_USER_ATTRS_BULK_PVT;
379
[Home] [Help]