[Home] [Help]
PACKAGE BODY: APPS.RRS_BULKLOAD_ENTITIES
Source
1 PACKAGE BODY RRS_BULKLOAD_ENTITIES
2 /* $Header: RRSSBLKB.pls 120.12 2010/10/04 19:08:31 sunarang ship $ */
3 AS
4 ----------------------------------------------------------------------------
5 -- Global constants
6 ----------------------------------------------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EGO_USER_ATTRS_BULK_PVT';
8 G_REQUEST_ID NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
9 G_PROGAM_APPLICATION_ID NUMBER := FND_GLOBAL.PROG_APPL_ID;
10 G_PROGAM_ID NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
11 G_USER_NAME FND_USER.USER_NAME%TYPE := FND_GLOBAL.USER_NAME;
12 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
13 G_LOGIN_ID NUMBER := FND_GLOBAL.LOGIN_ID;
14 G_CURRENT_USER_ID NUMBER;
15 G_CURRENT_LOGIN_ID NUMBER;
16 G_API_VERSION NUMBER := 1.0;
17 G_HZ_PARTY_ID VARCHAR2(30);
18 G_NO_USER_NAME_TO_VALIDATE EXCEPTION;
19 -- used for error handling.
20 G_ADD_ERRORS_TO_FND_STACK VARCHAR2(1);
21 G_APPLICATION_CONTEXT VARCHAR2(30);
22 G_ENTITY_ID NUMBER ;
23 G_ENTITY_CODE VARCHAR2(30) := 'RRS_SITE';
24 -- G_PK_COLS_TABLE PK_COL_TABLE;
25 -- G_SITE_NUMBER_EBI_COL VARCHAR2(50) := 'C_INTF_ATTR240';
26 G_DATE_FORMAT CONSTANT VARCHAR2(30) := 'SYYYY-MM-DD HH24:MI:SS';
27
28 G_APPLICATION_ID NUMBER(3) := 718;
29 G_DATA_ROWS_UPLOADED_NEW CONSTANT NUMBER := 0;
30 G_PS_TO_BE_PROCESSED CONSTANT NUMBER := 1;
31 G_PS_IN_PROCESS CONSTANT NUMBER := 2;
32 G_PS_GENERIC_ERROR CONSTANT NUMBER := 3;
33 G_PS_SUCCESS CONSTANT NUMBER := 4;
34 G_RETCODE_SUCCESS_WITH_WARNING CONSTANT VARCHAR(1) := 'W';
35
36 G_ERROR_TABLE_NAME VARCHAR2(99) := 'EGO_BULKLOAD_INTF';
37 G_ERROR_ENTITY_CODE VARCHAR2(99) := 'RRS_SITE';
38 G_ERROR_FILE_NAME VARCHAR2(99);
39 G_BO_IDENTIFIER VARCHAR2(99) := 'RRS_SITE';
40 G_DEBUG CONSTANT VARCHAR2(10) := NVL(FND_PROFILE.VALUE('RRS_DEBUG_TRACE'),0);
41
42 ---------------------------------------------------------------
43 -- Java Conc Program can continue writing to the same Error Log File.
44 -- using the below variable
45 ---------------------------------------------------------------
46 G_ERRFILE_PATH_AND_NAME VARCHAR2(10000);
47
48 ---------------------------------------------------------------
49 -- API Return statuses. --
50 ---------------------------------------------------------------
51 G_STATUS_SUCCESS CONSTANT VARCHAR2(1) := 'S';
52 G_STATUS_ERROR CONSTANT VARCHAR2(1) := 'E';
53
54
55 ---------------------------------------------------------------
56 -- UDA EGO WEBADI R12C uptake Bug: 6493113 EXPORT/IMPORT FUNCTIONALITY CAN'T BE USED
57 -- The following data level constants to be inserted into INTF tables
58 ---------------------------------------------------------------
59 G_RRS_SITE_DATA_LEVEL_ID NUMBER(5) := 71802;
60 G_RRS_LOCATION_DATA_LEVEL_ID NUMBER(5) := 71801;
61 G_RRS_TRADE_AREA_DATA_LEVEL_ID NUMBER(5) := 71803;
62
63
64 G_RRS_SITE_DATA_LEVEL VARCHAR2(100) := 'SITE_LEVEL';
65 G_RRS_LOCATION_DATA_LEVEL VARCHAR2(100) := 'LOCATION_LEVEL';
66 G_RRS_TRADE_AREA_DATA_LEVEL VARCHAR2(100) := 'TRADE_AREA_LEVEL';
67 --End of Bug 6493113
68
69
70
71 PROCEDURE SETUP_BULKLOAD_INTF(ERRBUF OUT NOCOPY VARCHAR2,
72 RETCODE OUT NOCOPY VARCHAR2,
73 ERROR_FILE OUT NOCOPY VARCHAR2,
74 p_result_format_usage_id IN Number) Is
75 l_process_status char(100);
76 BEGIN
77 DELETE FROM EGO_BULKLOAD_INTF
78 WHERE RESULTFMT_USAGE_ID = p_result_format_usage_id
79 AND PROCESS_STATUS <> G_DATA_ROWS_UPLOADED_NEW;
80 Open_Debug_Session;
81 ERROR_FILE := G_ERRFILE_PATH_AND_NAME;
82 Write_Conclog('Cleanup and Debug Session Setup Activities Completed');
83 RETCODE := 'S';
84 EXCEPTION
85 WHEN OTHERS THEN
86 ERRBUF := SUBSTRB(SQLERRM, 1,240);
87 RETCODE := 'E';
88 Write_Conclog(' Error in Setup_Bulkload_Intf Error API'||ERRBUF);
89 End SETUP_BULKLOAD_INTF;
90
91
92 PROCEDURE Open_Debug_Session IS
93 BEGIN
94 ----------------------------------------------------------------
95 -- Open the Debug Log Session, only if Profile is set to TRUE --
96 ----------------------------------------------------------------
97 IF (G_DEBUG = 1) THEN
98
99 ----------------------------------------------------------------------------------
100 -- Opens Error_Handler debug session, only if Debug session is not already open.
101 ----------------------------------------------------------------------------------
102 IF (Error_Handler.Get_Debug <> 'Y') THEN
103 Open_Debug_Session_Internal;
104 END IF;
105 END IF;
106 END Open_Debug_Session;
107
108 ----------------------------------------------------------
109 -- Internal procedure to open Debug Session. --
110 ----------------------------------------------------------
111 PROCEDURE open_debug_session_internal IS
112 CURSOR c_get_utl_file_dir IS
113 SELECT VALUE
114 FROM V$PARAMETER
115 WHERE NAME = 'utl_file_dir';
116
117 l_log_output_dir VARCHAR2(512);
118 l_log_return_status VARCHAR2(99);
119 l_errbuff VARCHAR2(999);
120 BEGIN
121 Error_Handler.initialize();
122 Error_Handler.set_bo_identifier(G_BO_IDENTIFIER);
123
124 OPEN c_get_utl_file_dir;
125 FETCH c_get_utl_file_dir INTO l_log_output_dir;
126 IF c_get_utl_file_dir%FOUND THEN
127 ------------------------------------------------------
128 -- Trim to get only the first directory in the list --
129 ------------------------------------------------------
130 IF INSTR(l_log_output_dir,',') <> 0 THEN
131 l_log_output_dir := SUBSTR(l_log_output_dir, 1, INSTR(l_log_output_dir, ',') - 1);
132 END IF;
133
134 G_ERROR_FILE_NAME := G_ERROR_TABLE_NAME||'_'||to_char(sysdate, 'DDMONYYYY_HH24MISS')||'.err';
135
136 -----------------------------------------------------------------------
137 -- To open the Debug Session to write the Debug Log. --
138 -- This sets Debug value so that Error_Handler.Get_Debug returns 'Y' --
139 -----------------------------------------------------------------------
140 Error_Handler.Open_Debug_Session(
141 p_debug_filename => G_ERROR_FILE_NAME
142 ,p_output_dir => l_log_output_dir
143 ,x_return_status => l_log_return_status
144 ,x_error_mesg => l_errbuff
145 );
146
147 ---------------------------------------------------------------
148 -- The Java Conc Program Should be writing to the same Error Log File.
149 ---------------------------------------------------------------
150 G_ERRFILE_PATH_AND_NAME := l_log_output_dir||'/'||G_ERROR_FILE_NAME;
151
152 Write_Conclog('Debug File name is => ' || G_ERRFILE_PATH_AND_NAME);
153
154 IF (l_log_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
155 Write_Conclog('Unable to open error log file. Error => '||l_errbuff);
156 END IF;
157
158 END IF; --IF c_get_utl_file_dir%FOUND THEN
159 CLOSE c_get_utl_file_dir;
160 END open_debug_session_internal;
161
162 PROCEDURE Developer_Debug(p_msg IN VARCHAR2) IS
163 l_err_msg VARCHAR2(240);
164 BEGIN
165 Error_Handler.Write_debug(p_msg);
166 EXCEPTION
167 WHEN OTHERS THEN
168 l_err_msg := SUBSTRB(SQLERRM, 1,240);
169 FND_FILE.put_line(FND_FILE.LOG, 'LOGGING SQL ERROR => '||l_err_msg);
170 END;
171
172 -----------------------------------------------------------------
173 -- Close the Debug Session, only if Debug is already Turned ON --
174 -----------------------------------------------------------------
175 PROCEDURE Close_Debug_Session IS
176
177 BEGIN
178 -----------------------------------------------------------------------------
179 -- Close Error_Handler debug session, only if Debug session is already open.
180 -----------------------------------------------------------------------------
181 IF (Error_Handler.Get_Debug = 'Y') THEN
182 Error_Handler.Close_Debug_Session;
183 END IF;
184
185 END Close_Debug_Session;
186 -----------------------------------------------
187 -- Write Debug statements to Concurrent Log --
188 -----------------------------------------------
189 PROCEDURE Write_Conclog (p_msg IN VARCHAR2) IS
190 l_err_msg VARCHAR2(240);
191 BEGIN
192 FND_FILE.put_line(FND_FILE.LOG, p_msg);
193 END Write_Conclog;
194 ---------------------------------------------------------------------------------
195 --LOAD SITE USER DEFINED ATTRIBUTES
196 --------------------------------------------------------------------------------
197 PROCEDURE LOAD_USERATTR_INTF(
198 p_resultfmt_usage_id IN NUMBER,
199 p_data_set_id IN NUMBER,
200 x_errbuff OUT NOCOPY VARCHAR2,
201 x_retcode OUT NOCOPY VARCHAR2,
202 p_entity_name IN VARCHAR2
203 ) Is
204 CURSOR c_user_attr_group_codes (c_resultfmt_usage_id IN NUMBER,c_attr_group_type IN VARCHAR2) IS
205 SELECT DISTINCT To_Number(SUBSTR(attribute_code, 1, INSTR(attribute_code, '$$') - 1)) attr_group_id,classification_code
206 FROM ego_results_fmt_usages
207 WHERE resultfmt_usage_id = c_resultfmt_usage_id
208 AND attribute_code LIKE '%$$%'
209 AND To_Number(SUBSTR(attribute_code, 1, INSTR(attribute_code, '$$') - 1)) IN
210 (
211 SELECT attr_group_id
212 FROM ego_attr_groups_v
213 WHERE attr_group_type = c_attr_group_type
214 AND application_id = G_APPLICATION_ID
215 );
216 CURSOR c_attr_grp_n_attr_int_names(p_attr_id IN NUMBER,c_attr_group_type IN VARCHAR2) IS
217 SELECT attr_group_name, attr_name
218 FROM ego_attrs_v
219 WHERE attr_id = p_attr_id
220 AND attr_group_type = c_attr_group_type
221 AND application_id = G_APPLICATION_ID;
222 TYPE L_USER_ATTR_REC_TYPE IS RECORD
223 (
224 DATA_SET_ID NUMBER(15),
225 TRANSACTION_ID NUMBER(15),
226 PK_ID NUMBER(15),
227 CLASSIFICATION_CODE VARCHAR2(30),
228 ROW_IDENTIFIER NUMBER(15),
229 ATTR_GROUP_NAME VARCHAR2(30),
230 ATTR_NAME VARCHAR2(30),
231 ATTR_DATATYPE_CODE VARCHAR2(1), --Valid Vals: C / N / D
232 ATTR_VALUE_STR VARCHAR2(10000),
233 ATTR_VALUE_NUM NUMBER,
234 ATTR_VALUE_DATE DATE,
235 INTF_COLUMN_NAME VARCHAR2(30),
236 ATTR_GROUP_ID NUMBER(15) --bug: 6493113
237 );
238 TYPE L_USER_ATTR_TBL_TYPE IS TABLE OF L_USER_ATTR_REC_TYPE
239 INDEX BY BINARY_INTEGER;
240 TYPE VARCHAR_TBL_TYPE IS TABLE OF VARCHAR2(256)
241 INDEX BY BINARY_INTEGER;
242 l_prod_col_name_tbl VARCHAR_TBL_TYPE;
243 l_intf_col_name_tbl VARCHAR_TBL_TYPE;
244 l_attr_id_table DBMS_SQL.NUMBER_TABLE;
245 l_intf_col_name_table DBMS_SQL.VARCHAR2_TABLE;
246 l_usr_attr_data_tbl L_USER_ATTR_TBL_TYPE;
247 l_pk_id_char VARCHAR(15);
248 l_site_num_char VARCHAR(1000);
249 l_trade_area_num_char VARCHAR(10);
250 l_count NUMBER(5);
251 l_data_type_code VARCHAR2(2);
252 l_transaction_id NUMBER(15);
253 l_attr_group_int_name EGO_ATTRS_V.ATTR_GROUP_NAME%TYPE;
254 l_attr_int_name EGO_ATTRS_V.ATTR_NAME%TYPE;
255
256 l_attr_group_id NUMBER(15); --bug 6493113
257 l_site_id NUMBER(15); --bug 6493113
258 l_attr_group_id_table DBMS_SQL.NUMBER_TABLE;
259 ---------------------------------------------------------
260 -- Example Data Types to be used in Bind Variable.
261 ---------------------------------------------------------
262 l_varchar_example VARCHAR2(10000);
263 l_number_example NUMBER;
264 l_date_example DATE;
265 --------------------------------------------------------------------
266 -- Actual Data to store corresponding data type value.
267 -- NOTE: for fixing Bug# 3808455, changed the size of l_varchar_data
268 -- to 10,000 chars. This is because, if there are 1000 Single
269 -- Quotes in the String Attr Value, then the Escaped value
270 -- becomes of Size 2000. So, for all better reasons, changing
271 -- to a huge size.
272 --------------------------------------------------------------------
273 l_varchar_data VARCHAR2(10000);
274 l_number_data NUMBER;
275 l_date_data DATE;
276 ---------------------------------------------------------
277 -- DBMS_SQL Open Cursor integers.
278 ---------------------------------------------------------
279 l_cursor_select INTEGER;
280 l_cursor_execute INTEGER;
281 l_cursor_attr_id_val INTEGER;
282 ---------------------------------------------------------
283 -- Used for indexes.
284 ---------------------------------------------------------
285 l_temp NUMBER(10) := 1;
286 l_actual_userattr_indx NUMBER(15);
287 l_indx NUMBER(15);
288 l_rows_per_attr_grp_indx NUMBER(15);
289 l_save_indx NUMBER(15);
290 l_attr_grp_has_data BOOLEAN;
291 l_attr_group_data_level VARCHAR2(30);
292 ---------------------------------------------------------
293 -- Long Dynamic SQL Strings
294 ---------------------------------------------------------
295 l_dyn_sql VARCHAR2(10000);
296 l_dyn_attr_id_val_sql VARCHAR2(10000);
297 ---------------------------------------------------------
298 -- To Number the Attribute Group Data Rows Uniquely.
299 ---------------------------------------------------------
300 L_ATTR_GRP_ROW_IDENT NUMBER(5) ;
301 ---------------------------------------------------------
302 -- Token tables to log errors, through Error_Handler
303 ---------------------------------------------------------
304 l_token_tbl_two Error_Handler.Token_Tbl_Type;
305 l_token_tbl_one Error_Handler.Token_Tbl_Type;
306 l_attr_group_type VARCHAR2(30);
307
308
309
310
311
312 BEGIN
313 Write_Conclog('Loading the User Defined Attributes for Entity '||p_entity_name);
314
315 IF (G_HZ_PARTY_ID IS NULL) THEN
316 IF (G_USER_NAME IS NOT NULL) THEN
317 SELECT 'HZ_PARTY:'||TO_CHAR(PERSON_ID)
318 INTO G_HZ_PARTY_ID
319 FROM EGO_PEOPLE_V
320 WHERE USER_NAME = G_USER_NAME;
321 ELSE
322 RAISE G_NO_USER_NAME_TO_VALIDATE;
323 END IF;
324 END IF;
325
326 IF p_entity_name = 'RRS_SITE' THEN
327 l_attr_group_type := 'RRS_SITEMGMT_GROUP';
328 ELSIF p_entity_name = 'RRS_LOCATION' THEN
329 l_attr_group_type := 'RRS_LOCATION_GROUP';
330 ELSIF p_entity_name = 'RRS_TRADE_AREA' THEN
331 l_attr_group_type := 'RRS_TRADE_AREA_GROUP';
332 END IF;
333 L_ATTR_GRP_ROW_IDENT := 0; /* Added by sushil for bug 10129629 */
334
335 FOR c_attr_grp_rec IN c_user_attr_group_codes
336 (
337 p_resultfmt_usage_id,l_attr_group_type
338 )
339 LOOP
340 l_dyn_sql := '';
341 l_dyn_sql := ' SELECT To_Number(SUBSTR(attribute_code, INSTR(attribute_code, ''$$'')+2)) attr_id, intf_column_name, To_Number(SUBSTR(attribute_code, 1, INSTR(attribute_code, ''$$'') - 1)) attr_group_id '; --bug 6493113
342 l_dyn_sql := l_dyn_sql || ' FROM ego_results_fmt_usages ';
343 l_dyn_sql := l_dyn_sql || ' WHERE resultfmt_usage_id = :RESULTFMT_USAGE_ID';
344 l_dyn_sql := l_dyn_sql || ' AND attribute_code LIKE :ATTRIBUTE_CODE ';
345 l_cursor_select := DBMS_SQL.OPEN_CURSOR;
346 DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);
347 DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_attr_id_table,2500, l_temp);
348 DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_intf_col_name_table,2500, l_temp);
349 DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_attr_group_id_table,2500, l_temp);
350
351
352 DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
353 DBMS_SQL.BIND_VARIABLE(l_cursor_select,':ATTRIBUTE_CODE', c_attr_grp_rec.attr_group_id||'$$%');
354 l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);
355
356 l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);
357 DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_attr_id_table);
358 DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_intf_col_name_table);
359 DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_attr_group_id_table);
360 --bug:6493113
361
362
363 DBMS_SQL.CLOSE_CURSOR(l_cursor_select);
364 --------------------------------------------------------------------
365 -- New DBMS_SQL Cursor for Select Attr Values.
366 --------------------------------------------------------------------
367 l_cursor_attr_id_val := DBMS_SQL.OPEN_CURSOR;
368 l_dyn_attr_id_val_sql := '';
369 l_dyn_attr_id_val_sql := ' SELECT ';
370 l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' TRANSACTION_ID , ';
371 IF p_entity_name = 'RRS_SITE' THEN
372 l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' INSTANCE_PK1_VALUE , ';
373 ELSIF p_entity_name = 'RRS_LOCATION' THEN
374 l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' INSTANCE_PK2_VALUE , ';
375 ELSIF p_entity_name = 'RRS_TRADE_AREA' THEN
376 l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' INSTANCE_PK3_VALUE , ';
377 END IF;
378 --------------------------------------------------------------------
379 -- Added the fix to fetch these cols also, as in case of New Item
380 -- Instance PK1 Value might not have been retrieved.
381 --------------------------------------------------------------------
382 -- l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql ||G_SITE_NUMBER_EBI_COL ||' , ';
383 Write_Conclog('*l_attr_id_table.COUNT*'||l_attr_id_table.COUNT);
384 FOR i IN 1..l_attr_id_table.COUNT LOOP
385 IF (i <> l_attr_id_table.COUNT) THEN
386 l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || l_intf_col_name_table(i) || ', ';
387 ELSE
388 l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || l_intf_col_name_table(i) ;
389 END IF;
390 END LOOP; --end: FOR i IN 1..l_attr_id_table.COUNT LOOP
391 l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' FROM EGO_BULKLOAD_INTF ' ;
392 l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
393 l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' AND PROCESS_STATUS = :PROCESS_STATUS ';
394 DBMS_SQL.PARSE(l_cursor_attr_id_val, l_dyn_attr_id_val_sql, DBMS_SQL.NATIVE);
395 --------------------------------------------------------------------
396 --Setting Data Type for Trasaction ID
397 --------------------------------------------------------------------
398 DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 1, l_number_example);
399 --------------------------------------------------------------------
400 --Setting Data Type for INSTANCE_PK1_VALUE (SITE ID)
401 --------------------------------------------------------------------
402 DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 2, l_varchar_example, 1000);
403 --------------------------------------------------------------------
404 --Setting Data Type for Site Num
405 --------------------------------------------------------------------
406 -- DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 3, l_varchar_example, 1000);
407 Write_Conclog('Executing the l_attr_id table looping');
408 --------------------------------------------------------------------
409 -- Loop to Bind the Data Types for the SELECT Columns.
410 --------------------------------------------------------------------
411 FOR i IN 1..l_attr_id_table.COUNT LOOP
412 ------------------------------------------------------------------------
413 -- Since TRANSACTION_ID, INSTANCE_PK1_VALUE, INSTANCE_PK2_VALUE,
414 -- INSTANCE_PK3_VALUE are added to the SELECT before the User-Defined
415 -- Attrs, we need to adjust the index as follows.
416 ------------------------------------------------------------------------
417 l_actual_userattr_indx := i + 2;
418 l_data_type_code := SUBSTR (l_intf_col_name_table(i), 1, 1);
419 ------------------------------------------------------------------------
420 -- Based on the Data Type of the attribute, define the column
421 ------------------------------------------------------------------------
422 IF (l_data_type_code = 'C') THEN
423 DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_varchar_example, 1000);
424 ELSIF (l_data_type_code = 'N') THEN
425 DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_number_example);
426 ELSE --IF (l_data_type_code = 'D') THEN
427 DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_date_example);
428 END IF; --IF (l_data_type_code = 'C') THEN
429 END LOOP; --FOR i IN 1..l_attr_id_table.COUNT LOOP
430 DBMS_SQL.BIND_VARIABLE(l_cursor_attr_id_val,':RESULTFMT_USAGE_ID',p_resultfmt_usage_id);
431 DBMS_SQL.BIND_VARIABLE(l_cursor_attr_id_val,':PROCESS_STATUS',G_PS_IN_PROCESS);
432
433 ------------------------------------------------------------------------
434 -- Execute to get the Item User-Defined Attr values.
435 ------------------------------------------------------------------------
436 l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_attr_id_val);
437 l_rows_per_attr_grp_indx := 0;
438 -- L_ATTR_GRP_ROW_IDENT := 0; /* commented by sushil for bug 10129629 */
439 ------------------------------------------------------------------------
440 -- Loop for each row found in EBI
441 ------------------------------------------------------------------------
442 Write_Conclog('Executing LOOP FOR CURSOR_ATTR_ID_VAL ');
443 LOOP --LOOP FOR CURSOR_ATTR_ID_VAL
444 IF DBMS_SQL.FETCH_ROWS(l_cursor_attr_id_val)>0 THEN
445 ------------------------------------------------------------------------
446 --Increment Row Identifier per (Attribute Group + Row) Combination.
447 ------------------------------------------------------------------------
448 L_ATTR_GRP_ROW_IDENT := L_ATTR_GRP_ROW_IDENT + 1;
449 ------------------------------------------------------------------------
450 -- First column is Transaction ID.
451 ------------------------------------------------------------------------
452 DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 1, l_transaction_id);
453 DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 2, l_pk_id_char);
454 -- DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 3, l_site_num_char);
455 FOR i IN 1..l_attr_id_table.COUNT LOOP
456 OPEN c_attr_grp_n_attr_int_names(l_attr_id_table(i),l_attr_group_type);
457 FETCH c_attr_grp_n_attr_int_names INTO
458 l_attr_group_int_name, l_attr_int_name;
459 Write_Conclog('Attribute group Internal Name '||l_attr_group_int_name);
460 Write_Conclog('Attribute Internal Name '||l_attr_int_name);
461 l_attr_grp_has_data := FALSE;
462 ------------------------------------------------------------------------
463 -- If one more Attribute found for the Attribute Group.
464 ------------------------------------------------------------------------
465 IF c_attr_grp_n_attr_int_names%FOUND THEN
466 l_rows_per_attr_grp_indx := l_rows_per_attr_grp_indx + 1;
467 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).DATA_SET_ID := p_data_set_id;
468 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).TRANSACTION_ID := l_transaction_id;
469 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).PK_ID := FND_NUMBER.CANONICAL_TO_NUMBER(l_pk_id_char);
470 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).CLASSIFICATION_CODE := c_attr_grp_rec.classification_code;
471 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ROW_IDENTIFIER := L_ATTR_GRP_ROW_IDENT;
472 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_DATATYPE_CODE := SUBSTR (l_intf_col_name_table(i), 1, 1);
473 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_GROUP_NAME := l_attr_group_int_name;
474 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_NAME := l_attr_int_name;
475 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_GROUP_ID := l_attr_group_id_table(i);
476 l_actual_userattr_indx := i + 2;
477 ------------------------------------------------------------------------
478 -- Depending upon the Data Type, populate corresponding field in the
479 -- User-Defined Attribute Data record.
480 ------------------------------------------------------------------------
481 IF (l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_DATATYPE_CODE = 'C') THEN
482 DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, l_actual_userattr_indx, l_varchar_data);
483 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_STR := l_varchar_data;
484 ELSIF (l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_DATATYPE_CODE = 'N') THEN
485 DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, l_actual_userattr_indx, l_number_data);
486 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_NUM := l_number_data;
487 ELSE --IF (l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_DATATYPE_CODE = 'D') THEN
488 DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, l_actual_userattr_indx, l_date_data);
489 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_DATE := l_date_data;
490 END IF; --end: IF (l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_DATATYPE_CODE = 'C') THEN
491 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).INTF_COLUMN_NAME := l_intf_col_name_table(i);
492 ------------------------------------------------------------------------
493 -- Donot populate NULL Attribute value in the User-Defined Attrs
494 -- Interface table.
495 ------------------------------------------------------------------------
496 IF ((l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_STR IS NULL) AND
497 (l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_NUM IS NULL) AND
498 (l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_DATE IS NULL)
499 ) THEN
500 ------------------------------------------------------------------------
501 -- If all attribute values are NULL value, then delete
502 -- the row from PLSQL table.
503 ------------------------------------------------------------------------
504 Write_Conclog('All the Attribute values are Null');
505 l_usr_attr_data_tbl.DELETE(l_rows_per_attr_grp_indx);
506 l_rows_per_attr_grp_indx := l_rows_per_attr_grp_indx - 1;
507 END IF; --end: IF ((l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_STR...
508 END IF; --end: IF c_attr_grp_n_attr_int_names%FOUND THEN
509 CLOSE c_attr_grp_n_attr_int_names;
510 END LOOP; --end: FOR i IN 1..l_attr_id_table.COUNT LOOP
511 ELSE --end: IF DBMS_SQL.FETCH_ROWS(l_cursor_attr_id_val)>0 THEN
512 Write_Conclog('No Rows Found (or) All rows are Done.');
513 EXIT;
514 END IF; --IF DBMS_SQL.FETCH_ROWS(l_cursor_attr_id_val)>0 THEN
515 END LOOP; --END: LOOP FOR CURSOR_ATTR_ID_VAL
516 l_attr_id_table.DELETE;
517 l_intf_col_name_table.DELETE;
518 ------------------------------------------------------------------------
519 DBMS_SQL.CLOSE_CURSOR(l_cursor_attr_id_val);
520 -------------------------------------------------------------------
521 -- Loop for all the rows to be inserted per Attribute Group.
522 -------------------------------------------------------------------
523 FOR i IN 1..l_rows_per_attr_grp_indx LOOP
524 -------------------------------------------------------------------------
525 -- Fix for Bug# 3808455. To avoid the following error:
526 -- ORA-01401: inserted value too large for column
527 -- [This is done because ATTR_DISP_VALUE size is 1000 Chars]
528 -------------------------------------------------------------------------
529 IF ( LENGTH(l_usr_attr_data_tbl(i).ATTR_VALUE_STR) > 1000 ) THEN
530 l_token_tbl_one(1).token_name := 'VALUE';
531 l_token_tbl_one(1).token_value := l_usr_attr_data_tbl(i).ATTR_VALUE_STR;
532 Write_Conclog('Inserted Attribute value too large....');
533 ELSE
534 l_varchar_data := NULL;
535 IF (l_usr_attr_data_tbl(i).ATTR_DATATYPE_CODE = 'C') THEN
536 l_varchar_data := l_usr_attr_data_tbl(i).ATTR_VALUE_STR;
537 ELSIF (l_usr_attr_data_tbl(i).ATTR_DATATYPE_CODE = 'N') THEN
538 IF (l_usr_attr_data_tbl(i).ATTR_VALUE_NUM IS NOT NULL) THEN
539 l_varchar_data := To_char(l_usr_attr_data_tbl(i).ATTR_VALUE_NUM);
540 END IF;
541 ELSE
542 IF (l_usr_attr_data_tbl(i).ATTR_VALUE_DATE IS NOT NULL) THEN
543 l_varchar_data := To_Char(l_usr_attr_data_tbl(i).ATTR_VALUE_DATE , G_DATE_FORMAT);
544 END IF;
545 END IF; --end: IF (l_usr_attr_data_tbl(i).ATTR_DATATYPE_CODE = 'C') THEN
546 Write_Conclog('DATA SET ID ' || l_usr_attr_data_tbl(i).DATA_SET_ID);
547 Write_Conclog('TRANSACTION_ID ' || l_usr_attr_data_tbl(i).TRANSACTION_ID);
548 Write_Conclog('PK_ID ' || l_usr_attr_data_tbl(i).PK_ID);
549 Write_Conclog('ROW_IDENT ' || l_usr_attr_data_tbl(i).ROW_IDENTIFIER);
550 Write_Conclog('CLASS ' || l_usr_attr_data_tbl(i).CLASSIFICATION_CODE);
551 Write_Conclog('ATTR_GROUP_INT_NAME ' || l_usr_attr_data_tbl(i).ATTR_GROUP_NAME);
552 Write_Conclog('ATTR_INT_NAME ' || l_usr_attr_data_tbl(i).ATTR_NAME);
553 Write_Conclog('ATTR_DISP_VALUE ' || l_varchar_data);
554
555 ---------------------------------------------------------------
556 -- UDA EGO WEBADI R12C uptake Bug: 6493113 EXPORT/IMPORT FUNCTIONALITY CAN'T BE USED
557 ----------------------------------------------------------------
558 IF p_entity_name = 'RRS_SITE' THEN
559 INSERT INTO RRS_SITE_UA_INTF
560 (
561 DATA_SET_ID ,
562 TRANSACTION_ID ,
563 SITE_ID ,
564 ROW_IDENTIFIER ,
565 PROCESS_STATUS ,
566 SITE_USE_TYPE_CODE ,
567 ATTR_GROUP_INT_NAME ,
568 ATTR_INT_NAME ,
569 ATTR_DISP_VALUE ,
570 DATA_LEVEL_ID ,
571 DATA_LEVEL_NAME
572 )
573 VALUES
574 (
575 l_usr_attr_data_tbl(i).DATA_SET_ID,
576 l_usr_attr_data_tbl(i).TRANSACTION_ID,
577 l_usr_attr_data_tbl(i).PK_ID,
578 l_usr_attr_data_tbl(i).ROW_IDENTIFIER,
579 G_PS_TO_BE_PROCESSED,
580 l_usr_attr_data_tbl(i).CLASSIFICATION_CODE,
581 l_usr_attr_data_tbl(i).ATTR_GROUP_NAME,
582 l_usr_attr_data_tbl(i).ATTR_NAME,
583 l_varchar_data,
584 G_RRS_SITE_DATA_LEVEL_ID,
585 G_RRS_SITE_DATA_LEVEL
586 );
587 --bug 6493113 datalevel UDA WEBADI issue debug
588 -- DELETE FROM RRS_SITES_EXT_B
589 -- WHERE ATTR_GROUP_ID = l_usr_attr_data_tbl(i).ATTR_GROUP_ID
590 -- AND SITE_ID = l_usr_attr_data_tbl(i).PK_ID;
591 Write_conclog('ATTR_ID: '||l_usr_attr_data_tbl(i).ATTR_GROUP_ID);
592 Write_conclog('Site_ID: '||l_usr_attr_data_tbl(i).PK_ID);
593 ELSIF p_entity_name = 'RRS_LOCATION' THEN
594 INSERT INTO RRS_LOCATION_UA_INTF
595 (
596 DATA_SET_ID ,
597 TRANSACTION_ID ,
598 LOCATION_ID ,
599 ROW_IDENTIFIER ,
600 PROCESS_STATUS ,
601 COUNTRY ,
602 ATTR_GROUP_INT_NAME ,
603 ATTR_INT_NAME ,
604 ATTR_DISP_VALUE ,
605 DATA_LEVEL_ID ,
606 DATA_LEVEL_NAME
607 )
608 VALUES
609 (
610 l_usr_attr_data_tbl(i).DATA_SET_ID,
611 l_usr_attr_data_tbl(i).TRANSACTION_ID,
612 l_usr_attr_data_tbl(i).PK_ID,
613 l_usr_attr_data_tbl(i).ROW_IDENTIFIER,
614 G_PS_TO_BE_PROCESSED,
615 l_usr_attr_data_tbl(i).CLASSIFICATION_CODE,
616 l_usr_attr_data_tbl(i).ATTR_GROUP_NAME,
617 l_usr_attr_data_tbl(i).ATTR_NAME,
618 l_varchar_data,
619 G_RRS_LOCATION_DATA_LEVEL_ID,
620 G_RRS_LOCATION_DATA_LEVEL
621 );
622 ELSIF p_entity_name = 'RRS_TRADE_AREA' THEN
623 INSERT INTO RRS_TRADEAREA_UA_INTF
624 (
625 DATA_SET_ID ,
626 TRANSACTION_ID ,
627 TRADE_AREA_ID ,
628 ROW_IDENTIFIER ,
629 PROCESS_STATUS ,
630 GROUP_ID ,
631 ATTR_GROUP_INT_NAME ,
632 ATTR_INT_NAME ,
633 ATTR_DISP_VALUE ,
634 DATA_LEVEL_ID ,
635 DATA_LEVEL_NAME
636 )
637 VALUES
638 (
639 l_usr_attr_data_tbl(i).DATA_SET_ID,
640 l_usr_attr_data_tbl(i).TRANSACTION_ID,
641 l_usr_attr_data_tbl(i).PK_ID,
642 l_usr_attr_data_tbl(i).ROW_IDENTIFIER,
643 G_PS_TO_BE_PROCESSED,
644 l_usr_attr_data_tbl(i).CLASSIFICATION_CODE,
645 l_usr_attr_data_tbl(i).ATTR_GROUP_NAME,
646 l_usr_attr_data_tbl(i).ATTR_NAME,
647 l_varchar_data,
648 G_RRS_TRADE_AREA_DATA_LEVEL_ID,
649 G_RRS_TRADE_AREA_DATA_LEVEL
650 ); --------End of bug 6493113
651 END IF;
652 END IF; --end: IF ( LENGTH(l_usr_attr_data_tbl(i)..
653 END LOOP; --FOR i IN 1..l_usr_attr_data_tbl.COUNT LOOP
654 Write_Conclog('Populated the User-Defined Attr Values for Attribute Group : '||l_attr_group_int_name);
655 END LOOP; --FOR c_attr_grp_rec IN c_user_attr_group_codes
656
657 EXCEPTION
658 WHEN OTHERS THEN
659 x_retcode := G_STATUS_ERROR;
660 x_errbuff := SUBSTRB(SQLERRM, 1,240);
661 Write_Conclog('Error! While Loading User Defined Attributes into Interface tables' ) ;
662 Write_Conclog('Error while processing Load User Attributes data API '||SQLCODE || ':'||SQLERRM);
663 END LOAD_USERATTR_INTF;
664 -----------------------end of load_userattr_intf---------------------------
665
666
667
668 PROCEDURE Get_Site_Security_Predicate (
669 p_object_name IN VARCHAR2
670 ,p_party_id IN VARCHAR2
671 ,p_privilege_name IN VARCHAR2
672 ,p_table_alias IN VARCHAR2
673 ,x_security_predicate OUT NOCOPY VARCHAR2
674 ) IS
675 l_return_status VARCHAR2(30);
676 l_table_alias VARCHAR2(100);
677 l_security_predicate VARCHAR2(32767);
678 l_api_name VARCHAR2(30);
679 l_request_id_clause VARCHAR2(500);
680 l_process_flag VARCHAR2(10);
681 BEGIN
682 l_api_name := 'Get_Site_Security_Predicate';
683 -- SetGlobals();
684
685 IF (LENGTH(p_table_alias) > 0) THEN
686 l_table_alias := p_table_alias || '.';
687 END IF;
688
689 EGO_DATA_SECURITY.get_security_predicate(
690 p_api_version => 1.0
691 ,p_function => p_privilege_name
692 ,p_object_name => p_object_name
693 ,p_user_name => p_party_id
694 ,p_statement_type => 'EXISTS'
695 ,p_pk1_alias => l_table_alias||'SITE_ID'
696 ,x_predicate => x_security_predicate
697 ,x_return_status => l_return_status
698 );
699
700
701 IF (x_security_predicate IS NULL) THEN
702 x_security_predicate := ' 1=1 '; --for internal users the security predicate is returned as null.
703 ELSE
704 x_security_predicate := x_security_predicate ||
705 ' AND NOT EXISTS
706 (SELECT 1
707 FROM RRS_SITES_INTERFACE rsi_e
708 WHERE rsi_e.TRANSACTION_TYPE = ''CREATE''
709 AND rsi_e.PROCESS_STATUS = ''1''
710 -- AND rsi_e.BATCH_ID = UAI2.BATCH_ID
711 AND rsi_e.SITE_ID = UAI2.SITE_ID
712 )';
713 END IF;
714 END Get_Site_Security_Predicate;
715
716
717 -------------------------------------------------------------------------------
718 --processing of Site Attributes from RRS_SITE_UA_INTF
719 --------------------------------------------------------------------------------
720 PROCEDURE PROCESS_USER_ATTRS_DATA(
721 ERRBUF OUT NOCOPY VARCHAR2
722 ,RETCODE OUT NOCOPY VARCHAR2
723 ,p_data_set_id IN NUMBER
724 ,p_purge_successful_lines IN VARCHAR2 DEFAULT FND_API.G_FALSE) is
725 l_entity_index_counter NUMBER := 0;
726 l_debug_level NUMBER := 0;
727 l_user_attrs_return_status VARCHAR2(100);
728 l_return_status VARCHAR2(1);
729 l_errorcode NUMBER;
730 l_msg_count NUMBER;
731 l_msg_data VARCHAR2(1000);
732 l_rel_sql VARCHAR2(100);
733 l_cnt NUMBER := 0;
734 CURSOR c_rrs_site_ua_intf is
735 select count(*) from RRS_SITE_UA_INTF
736 WHERE PROCESS_STATUS = G_PS_TO_BE_PROCESSED
737 AND DATA_SET_ID = p_data_set_id;
738 CURSOR c_rrs_loc_ua_intf is
739 select count(*) from RRS_LOCATION_UA_INTF
740 WHERE PROCESS_STATUS = G_PS_TO_BE_PROCESSED
741 AND DATA_SET_ID = p_data_set_id;
742 CURSOR c_rrs_ta_ua_intf is
743 SELECT COUNT(*) FROM RRS_TRADEAREA_UA_INTF
744 WHERE PROCESS_STATUS = G_PS_TO_BE_PROCESSED
745 AND DATA_SET_ID = p_data_set_id;
746
747 -------------------------------------------------------------
748 -- For Passing the privileges information to UDA API.
749 -------------------------------------------------------------
750 l_default_dl_view_priv_obj EGO_COL_NAME_VALUE_PAIR_OBJ;
751 l_default_dl_edit_priv_obj EGO_COL_NAME_VALUE_PAIR_OBJ;
752 l_default_dl_view_priv_list EGO_COL_NAME_VALUE_PAIR_ARRAY;
753 l_default_dl_edit_priv_list EGO_COL_NAME_VALUE_PAIR_ARRAY;
754 l_privilege_predicate_api_name VARCHAR2(250);
755 l_security_for_validate VARCHAR2(30) := NVL(FND_PROFILE.VALUE('RRS_ROLE_BASED_SECURITY_ENABLED'),'N') ;
756
757
758 BEGIN
759
760 Write_Conclog('Processing the User Defined Attributes ' );
761
762 IF (Error_Handler.Get_Debug = 'Y') THEN
763 l_debug_level := 3; --continue writing to the Debug Log opened.
764 ELSE
765 l_debug_level := 0; --Since Debug log is not opened, donot open Debug log for User-Attrs also.
766 END IF;
767
768 l_user_attrs_return_status := FND_API.G_RET_STS_SUCCESS;
769 Open c_rrs_site_ua_intf;
770 fetch c_rrs_site_ua_intf into l_cnt;
771 Close c_rrs_site_ua_intf;
772
773
774 IF l_cnt > 0 THEN
775
776
777 UPDATE RRS_SITE_UA_INTF
778 SET PROCESS_STATUS = G_PS_IN_PROCESS
779 ,REQUEST_ID = G_REQUEST_ID
780 ,PROGRAM_APPLICATION_ID = G_PROGAM_APPLICATION_ID
781 ,PROGRAM_ID = G_PROGAM_ID
782 ,PROGRAM_UPDATE_DATE = SYSDATE
783 ,CREATED_BY = DECODE(CREATED_BY, NULL, G_USER_ID, CREATED_BY)
784 ,CREATION_DATE = DECODE(CREATION_DATE, NULL, SYSDATE, CREATION_DATE)
785 ,LAST_UPDATED_BY = G_USER_ID
786 ,LAST_UPDATE_DATE = SYSDATE
787 ,LAST_UPDATE_LOGIN = G_LOGIN_ID
788 ,TRANSACTION_TYPE = UPPER(NVL(TRANSACTION_TYPE,EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE))
789 WHERE DATA_SET_ID = p_data_set_id
790 AND (PROCESS_STATUS IS NULL OR
791 PROCESS_STATUS = G_PS_TO_BE_PROCESSED);
792 l_rel_sql := 'SELECT CODE FROM RRS_SITES_OCV';
793
794
795 ----------------------------------------------------------
796 -- Code added for RBAC project - start.
797 ----------------------------------------------------------
798
799 IF l_security_for_validate = 'Y' THEN
800 l_privilege_predicate_api_name := 'RRS_BULKLOAD_ENTITIES.Get_Site_Security_Predicate';
801
802 -- creating default privileges
803 l_default_dl_view_priv_list := EGO_COL_NAME_VALUE_PAIR_ARRAY();
804 l_default_dl_edit_priv_list := EGO_COL_NAME_VALUE_PAIR_ARRAY();
805
806 l_default_dl_view_priv_obj := EGO_COL_NAME_VALUE_PAIR_OBJ(71802, NULL);
807 l_default_dl_edit_priv_obj := EGO_COL_NAME_VALUE_PAIR_OBJ(71802, NULL);
808
809 l_default_dl_view_priv_list.EXTEND;
810 l_default_dl_view_priv_list(l_default_dl_view_priv_list.COUNT) := l_default_dl_view_priv_obj;
811
812 l_default_dl_edit_priv_list.EXTEND;
813 l_default_dl_edit_priv_list(l_default_dl_edit_priv_list.COUNT) := l_default_dl_edit_priv_obj;
814
815 ELSE
816 l_privilege_predicate_api_name := NULL;
817 END IF;
818 ----------------------------------------------------------
819 -- Code added for RBAC project - complete.
820 ----------------------------------------------------------
821
822 Write_Conclog('Executing EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data -'||'RRS_SITE' );
823
824 EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data (
825 p_api_version => G_API_VERSION --IN NUMBER
826 ,p_application_id => 718 --IN NUMBER
827 ,p_attr_group_type => 'RRS_SITEMGMT_GROUP' --IN VARCHAR2
828 ,p_object_name => 'RRS_SITE' --IN VARCHAR2
829 ,p_hz_party_id => G_HZ_PARTY_ID
830 ,p_interface_table_name => 'RRS_SITE_UA_INTF' --IN VARCHAR2
831 ,p_data_set_id => p_data_set_id --IN NUMBER
832 ,p_entity_id => G_ENTITY_ID --IN NUMBER
833 ,p_entity_index => l_entity_index_counter --IN NUMBER
834 ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
835 --,p_debug_level => p_debug_level --IN NUMBER
836 ,p_debug_level => l_debug_level --IN NUMBER
837 ,p_init_error_handler => FND_API.G_FALSE --IN VARCHAR2
838 ,p_init_fnd_msg_list => FND_API.G_FALSE --IN VARCHAR2
839 ,p_log_errors => FND_API.G_TRUE --IN VARCHAR2
840 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE --IN VARCHAR2
841 ,p_commit => FND_API.G_FALSE --IN VARCHAR2
842 ,p_default_dl_view_priv_list => l_default_dl_view_priv_list --IN VARCHAR2
843 ,p_default_dl_edit_priv_list => l_default_dl_edit_priv_list
844 ,p_privilege_predicate_api_name => l_privilege_predicate_api_name
845 ,p_related_class_codes_query => l_rel_sql --IN VARCHAR2
846 ,p_validate => TRUE
847 ,p_do_dml => TRUE
848 ,x_return_status => l_user_attrs_return_status --OUT NOCOPY VARCHAR2
849 ,x_errorcode => l_errorcode --OUT NOCOPY NUMBER
850 ,x_msg_count => l_msg_count --OUT NOCOPY NUMBER
851 ,x_msg_data => l_msg_data --OUT NOCOPY VARCHAR2
852 );
853
854 Write_Conclog('Executed Site User Defined Attributes Upload API');
855 Write_Conclog('G_API_VERSION ' || G_API_VERSION);
856 Write_Conclog('G_HZ_PARTY_ID ' || G_HZ_PARTY_ID);
857 Write_Conclog('p_data_set_id ' || p_data_set_id);
858 Write_Conclog('G_ENTITY_ID ' || G_ENTITY_ID);
859 Write_Conclog('l_entity_index_counter ' || l_entity_index_counter);
860 Write_Conclog(' G_ENTITY_CODE ' || G_ENTITY_CODE );
861 Write_Conclog('l_debug_level ' || l_debug_level );
862 Write_Conclog('l_rel_sql ' || l_rel_sql );
863 Write_Conclog('Return Status '||l_user_attrs_return_status);
864 Write_Conclog('Error Code '||l_errorcode);
865 Write_Conclog('msg count '||l_msg_count);
866 Write_Conclog('msg data '||l_msg_data);
867
868
869 IF (FND_API.To_Boolean(p_purge_successful_lines)) THEN
870 -----------------------------------------------
871 -- Delete all successful rows from the table --
872 -- (they're the only rows still in process) --
873 -----------------------------------------------
874 DELETE FROM RRS_SITE_UA_INTF
875 WHERE DATA_SET_ID = p_data_set_id
876 AND PROCESS_STATUS = G_PS_IN_PROCESS;
877 ELSE
878 ----------------------------------------------
879 -- Mark all rows we've processed as success --
880 -- if they weren't marked as failure above --
881 ----------------------------------------------
882 UPDATE RRS_SITE_UA_INTF
883 SET PROCESS_STATUS = G_PS_SUCCESS
884 WHERE DATA_SET_ID = p_data_set_id
885 AND PROCESS_STATUS = G_PS_IN_PROCESS;
886 END IF;
887 END IF;
888 l_cnt := 0;
889 Open c_rrs_loc_ua_intf;
890 Fetch c_rrs_loc_ua_intf into l_cnt;
891 Close c_rrs_loc_ua_intf;
892 IF l_cnt > 0 THEN
893 UPDATE RRS_LOCATION_UA_INTF
894 SET PROCESS_STATUS = G_PS_IN_PROCESS
895 ,REQUEST_ID = G_REQUEST_ID
896 ,PROGRAM_APPLICATION_ID = G_PROGAM_APPLICATION_ID
897 ,PROGRAM_ID = G_PROGAM_ID
898 ,PROGRAM_UPDATE_DATE = SYSDATE
899 ,CREATED_BY = DECODE(CREATED_BY, NULL, G_USER_ID, CREATED_BY)
900 ,CREATION_DATE = DECODE(CREATION_DATE, NULL, SYSDATE, CREATION_DATE)
901 ,LAST_UPDATED_BY = G_USER_ID
902 ,LAST_UPDATE_DATE = SYSDATE
903 ,LAST_UPDATE_LOGIN = G_LOGIN_ID
904 ,TRANSACTION_TYPE = UPPER(NVL(TRANSACTION_TYPE,EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE))
905 WHERE DATA_SET_ID = p_data_set_id
906 AND (PROCESS_STATUS IS NULL OR
907 PROCESS_STATUS = G_PS_TO_BE_PROCESSED);
908 l_rel_sql := 'SELECT CODE FROM RRS_LOCATIONS_OCV' ;
909 Write_Conclog('Executing EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data -'||'RRS_LOCATION' );
910 EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data (
911 p_api_version => G_API_VERSION --IN NUMBER
912 ,p_application_id => 718 --IN NUMBER
913 ,p_attr_group_type => 'RRS_LOCATION_GROUP' --IN VARCHAR2
914 ,p_object_name => 'RRS_LOCATION' --IN VARCHAR2
915 ,p_hz_party_id => G_HZ_PARTY_ID
916 ,p_interface_table_name => 'RRS_LOCATION_UA_INTF' --IN VARCHAR2
917 ,p_data_set_id => p_data_set_id --IN NUMBER
918 ,p_entity_id => G_ENTITY_ID --IN NUMBER
919 ,p_entity_index => l_entity_index_counter --IN NUMBER
920 ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
921 --,p_debug_level => p_debug_level --IN NUMBER
922 ,p_debug_level => l_debug_level --IN NUMBER
923 ,p_init_error_handler => FND_API.G_FALSE --IN VARCHAR2
924 ,p_init_fnd_msg_list => FND_API.G_FALSE --IN VARCHAR2
925 ,p_log_errors => FND_API.G_TRUE --IN VARCHAR2
926 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE --IN VARCHAR2
927 ,p_commit => FND_API.G_FALSE --IN VARCHAR2
928 ,p_default_view_privilege => 'RRS_LOCATION_VIEW' --IN VARCHAR2
929 ,p_default_edit_privilege => NULL
930 ,p_privilege_predicate_api_name => NULL
931 ,p_related_class_codes_query => l_rel_sql --IN VARCHAR2
932 ,p_validate => TRUE
933 ,p_do_dml => TRUE
934 ,x_return_status => l_user_attrs_return_status --OUT NOCOPY VARCHAR2
935 ,x_errorcode => l_errorcode --OUT NOCOPY NUMBER
936 ,x_msg_count => l_msg_count --OUT NOCOPY NUMBER
937 ,x_msg_data => l_msg_data --OUT NOCOPY VARCHAR2
938 );
939 Write_Conclog('Executed Location User Defined Attributes Upload API');
940 Write_Conclog('Return Status '||l_user_attrs_return_status);
941 Write_Conclog('Error Code '||l_errorcode);
942 Write_Conclog('msg count '||l_msg_count);
943 Write_Conclog('msg data '||l_msg_data);
944 IF (FND_API.To_Boolean(p_purge_successful_lines)) THEN
945 -----------------------------------------------
946 -- Delete all successful rows from the table --
947 -- (they're the only rows still in process) --
948 -----------------------------------------------
949 DELETE FROM RRS_LOCATION_UA_INTF
950 WHERE DATA_SET_ID = p_data_set_id
951 AND PROCESS_STATUS = G_PS_IN_PROCESS;
952 ELSE
953 ----------------------------------------------
954 -- Mark all rows we've processed as success --
955 -- if they weren't marked as failure above --
956 ----------------------------------------------
957 UPDATE RRS_LOCATION_UA_INTF
958 SET PROCESS_STATUS = G_PS_SUCCESS
959 WHERE DATA_SET_ID = p_data_set_id
960 AND PROCESS_STATUS = G_PS_IN_PROCESS;
961 END IF;
962 END IF;
963 l_cnt := 0;
964 Open c_rrs_ta_ua_intf;
965 fetch c_rrs_ta_ua_intf into l_cnt;
966 Close c_rrs_ta_ua_intf;
967 IF l_cnt > 0 THEN
968 UPDATE RRS_TRADEAREA_UA_INTF
969 SET PROCESS_STATUS = G_PS_IN_PROCESS
970 ,REQUEST_ID = G_REQUEST_ID
971 ,PROGRAM_APPLICATION_ID = G_PROGAM_APPLICATION_ID
972 ,PROGRAM_ID = G_PROGAM_ID
973 ,PROGRAM_UPDATE_DATE = SYSDATE
974 ,CREATED_BY = DECODE(CREATED_BY, NULL, G_USER_ID, CREATED_BY)
975 ,CREATION_DATE = DECODE(CREATION_DATE, NULL, SYSDATE, CREATION_DATE)
976 ,LAST_UPDATED_BY = G_USER_ID
977 ,LAST_UPDATE_DATE = SYSDATE
978 ,LAST_UPDATE_LOGIN = G_LOGIN_ID
979 ,TRANSACTION_TYPE = UPPER(NVL(TRANSACTION_TYPE,EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE))
980 WHERE DATA_SET_ID = p_data_set_id
981 AND (PROCESS_STATUS IS NULL OR
982 PROCESS_STATUS = G_PS_TO_BE_PROCESSED);
983 l_rel_sql := 'SELECT CODE FROM RRS_TRADE_AREAS_OCV' ;
984
985 Write_Conclog('Executing EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data -'||'RRS_TRADE_AREA' );
986
987 EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data (
988 p_api_version => G_API_VERSION --IN NUMBER
989 ,p_application_id => 718 --IN NUMBER
990 ,p_attr_group_type => 'RRS_TRADE_AREA_GROUP' --IN VARCHAR2
991 ,p_object_name => 'RRS_TRADE_AREA' --IN VARCHAR2
992 ,p_hz_party_id => G_HZ_PARTY_ID
993 ,p_interface_table_name => 'RRS_TRADEAREA_UA_INTF' --IN VARCHAR2
994 ,p_data_set_id => p_data_set_id --IN NUMBER
995 ,p_entity_id => G_ENTITY_ID --IN NUMBER
996 ,p_entity_index => l_entity_index_counter --IN NUMBER
997 ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
998 --,p_debug_level => p_debug_level --IN NUMBER
999 ,p_debug_level => l_debug_level --IN NUMBER
1000 ,p_init_error_handler => FND_API.G_FALSE --IN VARCHAR2
1001 ,p_init_fnd_msg_list => FND_API.G_FALSE --IN VARCHAR2
1002 ,p_log_errors => FND_API.G_TRUE --IN VARCHAR2
1003 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE --IN VARCHAR2
1004 ,p_commit => FND_API.G_FALSE --IN VARCHAR2
1005 ,p_default_view_privilege => 'RRS_TRADE_AREA_VIEW' --IN VARCHAR2
1006 ,p_default_edit_privilege => NULL
1007 ,p_privilege_predicate_api_name => NULL
1008 ,p_related_class_codes_query => l_rel_sql --IN VARCHAR2
1009 ,p_validate => TRUE
1010 ,p_do_dml => TRUE
1011 ,x_return_status => l_user_attrs_return_status --OUT NOCOPY VARCHAR2
1012 ,x_errorcode => l_errorcode --OUT NOCOPY NUMBER
1013 ,x_msg_count => l_msg_count --OUT NOCOPY NUMBER
1014 ,x_msg_data => l_msg_data --OUT NOCOPY VARCHAR2
1015 );
1016 Write_Conclog('Executed Trade Area User Defined Attributes Upload API');
1017 Write_Conclog('Return Status '||l_user_attrs_return_status);
1018 Write_Conclog('Error Code '||l_errorcode);
1019 Write_Conclog('msg count '||l_msg_count);
1020 Write_Conclog('msg data '||l_msg_data);
1021 IF (FND_API.To_Boolean(p_purge_successful_lines)) THEN
1022 -----------------------------------------------
1023 -- Delete all successful rows from the table --
1024 -- (they're the only rows still in process) --
1025 -----------------------------------------------
1026 DELETE FROM RRS_TRADEAREA_UA_INTF
1027 WHERE DATA_SET_ID = p_data_set_id
1028 AND PROCESS_STATUS = G_PS_IN_PROCESS;
1029 ELSE
1030 ----------------------------------------------
1031 -- Mark all rows we've processed as success --
1032 -- if they weren't marked as failure above --
1033 ----------------------------------------------
1034 UPDATE RRS_TRADEAREA_UA_INTF
1035 SET PROCESS_STATUS = G_PS_SUCCESS
1036 WHERE DATA_SET_ID = p_data_set_id
1037 AND PROCESS_STATUS = G_PS_IN_PROCESS;
1038 END IF;
1039 END IF;
1040
1041 -------------------------------------------------------------------
1042 -- Finally, we log any errors that we've accumulated throughout --
1043 -- our conversions and looping (including all errors encountered --
1044 -- within our Business Object's processing) --
1045 -------------------------------------------------------------------
1046 Write_Conclog('****Dumping the List of Error messages into the Concurrent Log***');
1047
1048 ERROR_HANDLER.Log_Error(
1049 p_write_err_to_inttable => 'N'
1050 ,p_write_err_to_conclog => 'Y'
1051 ,p_write_err_to_debugfile => 'Y'
1052 );
1053 Write_Conclog('****End of All Error messages***');
1054 -----------------------------------------------------------
1055 -- Let caller know whether any rows failed in processing --
1056 -----------------------------------------------------------
1057 IF ( l_user_attrs_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1058 RETCODE := G_RETCODE_SUCCESS_WITH_WARNING;
1059 ELSE
1060 RETCODE := FND_API.G_RET_STS_SUCCESS;
1061 END IF;
1062 EXCEPTION
1063 WHEN OTHERS THEN
1064 ----------------------------------------
1065 -- Mark all rows in process as errors --
1066 ----------------------------------------
1067 l_cnt := 0;
1068 Open c_rrs_site_ua_intf;
1069 fetch c_rrs_site_ua_intf into l_cnt;
1070 Close c_rrs_site_ua_intf;
1071 IF l_cnt > 0 THEN
1072 UPDATE RRS_SITE_UA_INTF
1073 SET PROCESS_STATUS = G_PS_GENERIC_ERROR
1074 WHERE DATA_SET_ID = p_data_set_id
1075 AND PROCESS_STATUS = G_PS_IN_PROCESS;
1076 END IF;
1077 l_cnt := 0;
1078 Open c_rrs_loc_ua_intf;
1079 fetch c_rrs_loc_ua_intf into l_cnt;
1080 Close c_rrs_loc_ua_intf;
1081 IF l_cnt > 0 THEN
1082 UPDATE RRS_LOCATION_UA_INTF
1083 SET PROCESS_STATUS = G_PS_GENERIC_ERROR
1084 WHERE DATA_SET_ID = p_data_set_id
1085 AND PROCESS_STATUS = G_PS_IN_PROCESS;
1086 END IF;
1087 l_cnt := 0;
1088 Open c_rrs_ta_ua_intf;
1089 fetch c_rrs_ta_ua_intf into l_cnt;
1090 Close c_rrs_ta_ua_intf;
1091 IF l_cnt > 0 THEN
1092 UPDATE RRS_TRADEAREA_UA_INTF
1093 SET PROCESS_STATUS = G_PS_GENERIC_ERROR
1094 WHERE DATA_SET_ID = p_data_set_id
1095 AND PROCESS_STATUS = G_PS_IN_PROCESS;
1096 END IF;
1097 Write_Conclog('Error! While Processing User Defined Attributes ' ) ;
1098 Write_Conclog('Error while processing Process User Attrs data API '||SQLCODE || ':'||SQLERRM);
1099 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
1100 END Process_User_Attrs_Data;
1101 ---------------------------------------------------------------------
1102 -- Main API called by Java Concurrent program
1103 ---------------------------------------------------------------------
1104 PROCEDURE BulkLoadEntities(
1105 ERRBUF OUT NOCOPY VARCHAR2,
1106 RETCODE OUT NOCOPY VARCHAR2,
1107 result_format_usage_id IN NUMBER,
1108 user_id IN NUMBER,
1109 LANGAUGE IN VARCHAR2,
1110 resp_id IN NUMBER,
1111 appl_id IN NUMBER)
1112 IS
1113 l_region_application_id NUMBER := 0 ;
1114 l_customization_application_id NUMBER := 0 ;
1115 l_region_code VARCHAR2(30);
1116 Current_Error_Code VARCHAR2(20) := NULL;
1117 conc_status BOOLEAN ;
1118 l_target_api_call NUMBER :=0;
1119 l_debug VARCHAR2(80);
1120 l_errbuf varchar2(2000);
1121 l_retcode varchar2(2000);
1122 l_rrs_set_process_id NUMBER;
1123 l_process_status char(100);
1124 BEGIN
1125 Write_Conclog('Executing the BulkLoadEntities API ');
1126 Write_Conclog('User Defined Attributes Import Program *STARTED*');
1127 UPDATE EGO_BULKLOAD_INTF
1128 SET
1129 PROCESS_STATUS = G_PS_IN_PROCESS,
1130 LAST_UPDATE_LOGIN = FND_GLOBAL.login_id,
1131 REQUEST_ID = FND_GLOBAL.conc_request_id,
1132 PROGRAM_APPLICATION_ID = FND_GLOBAL.prog_appl_id,
1133 PROGRAM_ID = FND_GLOBAL.conc_program_id
1134 WHERE RESULTFMT_USAGE_ID = result_format_usage_id
1135 AND process_status = G_PS_TO_BE_PROCESSED;
1136
1137 SELECT rrs_site_intf_sets_s.NEXTVAL
1138 INTO l_rrs_set_process_id
1139 FROM dual;
1140 LOAD_USERATTR_INTF(
1141 p_resultfmt_usage_id => result_format_usage_id,
1142 p_data_set_id => l_rrs_set_process_id,
1143 x_errbuff => l_errbuf,
1144 x_retcode => l_retcode,
1145 p_entity_name => 'RRS_SITE'
1146 );
1147 LOAD_USERATTR_INTF(
1148 p_resultfmt_usage_id => result_format_usage_id,
1149 p_data_set_id => l_rrs_set_process_id,
1150 x_errbuff => l_errbuf,
1151 x_retcode => l_retcode,
1152 p_entity_name => 'RRS_LOCATION'
1153 );
1154 LOAD_USERATTR_INTF(
1155 p_resultfmt_usage_id => result_format_usage_id,
1156 p_data_set_id => l_rrs_set_process_id,
1157 x_errbuff => l_errbuf,
1158 x_retcode => l_retcode,
1159 p_entity_name => 'RRS_TRADE_AREA'
1160 );
1161 PROCESS_USER_ATTRS_DATA(
1162 ERRBUF => l_errbuf,
1163 RETCODE => l_retcode,
1164 p_data_set_id => l_rrs_set_process_id,
1165 p_purge_successful_lines => FND_API.G_FALSE);
1166
1167 Write_Conclog('Return Code After Executing LOAD and PROCESS APIs '||l_retcode);
1168 Write_Conclog('Error Code After Executing LOAD and PROCESS APIs '||l_errbuf);
1169
1170 IF l_retcode = 'S' THEN
1171 UPDATE EGO_BULKLOAD_INTF
1172 SET PROCESS_STATUS = G_PS_SUCCESS,
1173 LAST_UPDATE_LOGIN = FND_GLOBAL.login_id,
1174 REQUEST_ID = FND_GLOBAL.conc_request_id,
1175 PROGRAM_APPLICATION_ID = FND_GLOBAL.prog_appl_id,
1176 PROGRAM_ID = FND_GLOBAL.conc_program_id
1177 WHERE RESULTFMT_USAGE_ID = result_format_usage_id
1178 AND process_status = G_PS_IN_PROCESS;
1179 END IF ;
1180 ERRBUF := l_errbuf;
1181 RETCODE := l_retcode;
1182 Write_Conclog('User Defined Attributes Import Program *COMPLETED* ');
1183 EXCEPTION
1184 WHEN OTHERS THEN
1185 Write_Conclog('Error while processing BulkloadEntities API '||SQLCODE || ':'||SQLERRM);
1186 Write_Conclog('Error! While Running User Defined Attributes Import Program ');
1187 RETCODE := 'E';
1188 Current_Error_Code := To_Char(SQLCODE);
1189 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', Current_Error_Code);
1190 END BulkLoadEntities;
1191 END;