[Home] [Help]
PACKAGE BODY: APPS.RRS_SITE_UDA_BULKLOAD_INTF
Source
4 ----------------------------------------------------------------------------
1 PACKAGE BODY RRS_SITE_UDA_BULKLOAD_INTF AS
2 /* $Header: RRSIMPUB.pls 120.2 2011/02/14 23:08:30 sunarang noship $*/
3
5 -- Global constants
6 ----------------------------------------------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'RRS_SITE_UDA_BULKLOAD_INTF';
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_UDA';
24 -- G_PK_COLS_TABLE PK_COL_TABLE;
28 G_APPLICATION_ID NUMBER(3) := 718;
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
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) := 'RRS_INTERFACE_ERRORS';
37 G_ERROR_ENTITY_CODE VARCHAR2(99) := 'RRS_SITE_UDA';
38 G_ERROR_FILE_NAME VARCHAR2(99);
39 G_BO_IDENTIFIER VARCHAR2(99) := 'RRS_SITE_UDA';
40 G_DEBUG CONSTANT VARCHAR2(10) := NVL(FND_PROFILE.VALUE('RRS_DEBUG_TRACE'),0);
41
42 G_ERRFILE_PATH_AND_NAME VARCHAR2(10000);
43
44 ---------------------------------------------------------------
45 -- API Return statuses. --
46 ---------------------------------------------------------------
47 G_STATUS_SUCCESS CONSTANT VARCHAR2(1) := 'S';
48 G_STATUS_ERROR CONSTANT VARCHAR2(1) := 'E';
49
50 G_RRS_SITE_DATA_LEVEL_ID NUMBER(5) := 71802;
51 G_RRS_LOCATION_DATA_LEVEL_ID NUMBER(5) := 71801;
52 G_RRS_TRADE_AREA_DATA_LEVEL_ID NUMBER(5) := 71803;
53
54 G_RRS_SITE_DATA_LEVEL VARCHAR2(100) := 'SITE_LEVEL';
55 G_RRS_LOCATION_DATA_LEVEL VARCHAR2(100) := 'LOCATION_LEVEL';
56 G_RRS_TRADE_AREA_DATA_LEVEL VARCHAR2(100) := 'TRADE_AREA_LEVEL';
57 --End of Bug 6493113
58
59
60 /*
61 FUNCTION RETURN_PROCESS_STATUS RETURN VARCHAR2 IS
62 l_status varchar2(10);
63 BEGIN
64 BEGIN
65 select distinct process_status
66 into l_status
67 from rrs_site_ua_intf
68 where data_set_id = 10000;
69 EXCEPTION
70 WHEN NO_DATA_FOUND THEN NULL;
71 WHEN TOO_MANY_ROWS THEN NULL;
72 END;
73 return(l_status);
74 END;
75 */
76
77 PROCEDURE Open_Debug_Session IS
78 BEGIN
79 ----------------------------------------------------------------
80 -- Open the Debug Log Session, only if Profile is set to TRUE --
81 ----------------------------------------------------------------
82 IF (G_DEBUG = 1) THEN
83
84 ----------------------------------------------------------------------------------
85 -- Opens Error_Handler debug session, only if Debug session is not already open.
86 ----------------------------------------------------------------------------------
87 IF (Error_Handler.Get_Debug <> 'Y') THEN
88 Open_Debug_Session_Internal;
89 END IF;
90 END IF;
91 END Open_Debug_Session;
92
93 ----------------------------------------------------------
94 -- Internal procedure to open Debug Session. --
95 ----------------------------------------------------------
96 PROCEDURE open_debug_session_internal IS
97 CURSOR c_get_utl_file_dir IS
98 SELECT VALUE
99 FROM V$PARAMETER
100 WHERE NAME = 'utl_file_dir';
101
102 l_log_output_dir VARCHAR2(512);
103 l_log_return_status VARCHAR2(99);
104 l_errbuff VARCHAR2(999);
105 BEGIN
106 Error_Handler.initialize();
107 Error_Handler.set_bo_identifier(G_BO_IDENTIFIER);
108
109 OPEN c_get_utl_file_dir;
110 FETCH c_get_utl_file_dir INTO l_log_output_dir;
111 IF c_get_utl_file_dir%FOUND THEN
112 ------------------------------------------------------
113 -- Trim to get only the first directory in the list --
114 ------------------------------------------------------
115 IF INSTR(l_log_output_dir,',') <> 0 THEN
116 l_log_output_dir := SUBSTR(l_log_output_dir, 1, INSTR(l_log_output_dir, ',') - 1);
117 END IF;
118
119 G_ERROR_FILE_NAME := G_ERROR_TABLE_NAME||'_'||to_char(sysdate, 'DDMONYYYY_HH24MISS')||'.err';
120
121 -----------------------------------------------------------------------
122 -- To open the Debug Session to write the Debug Log. --
123 -- This sets Debug value so that Error_Handler.Get_Debug returns 'Y' --
124 -----------------------------------------------------------------------
125 Error_Handler.Open_Debug_Session(
126 p_debug_filename => G_ERROR_FILE_NAME
127 ,p_output_dir => l_log_output_dir
128 ,x_return_status => l_log_return_status
129 ,x_error_mesg => l_errbuff
130 );
131
132 ---------------------------------------------------------------
133 -- The Java Conc Program Should be writing to the same Error Log File.
134 ---------------------------------------------------------------
135 G_ERRFILE_PATH_AND_NAME := l_log_output_dir||'/'||G_ERROR_FILE_NAME;
136
137 Write_Conclog('Debug File name is => ' || G_ERRFILE_PATH_AND_NAME);
138
139 IF (l_log_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
140 Write_Conclog('Unable to open error log file. Error => '||l_errbuff);
141 END IF;
142
143 END IF; --IF c_get_utl_file_dir%FOUND THEN
144 CLOSE c_get_utl_file_dir;
145 END open_debug_session_internal;
146
147 PROCEDURE Developer_Debug(p_msg IN VARCHAR2) IS
148 l_err_msg VARCHAR2(240);
149 BEGIN
150 Error_Handler.Write_debug(p_msg);
151 EXCEPTION
152 WHEN OTHERS THEN
153 l_err_msg := SUBSTRB(SQLERRM, 1,240);
154 FND_FILE.put_line(FND_FILE.LOG, 'LOGGING SQL ERROR => '||l_err_msg);
155 END;
156
157 -----------------------------------------------------------------
161
158 -- Close the Debug Session, only if Debug is already Turned ON --
159 -----------------------------------------------------------------
160 PROCEDURE Close_Debug_Session IS
162 BEGIN
163 -----------------------------------------------------------------------------
164 -- Close Error_Handler debug session, only if Debug session is already open.
165 -----------------------------------------------------------------------------
166 IF (Error_Handler.Get_Debug = 'Y') THEN
167 Error_Handler.Close_Debug_Session;
168 END IF;
169
170 END Close_Debug_Session;
171 -----------------------------------------------
172 -- Write Debug statements to Concurrent Log --
173 -----------------------------------------------
174 PROCEDURE Write_Conclog (p_msg IN VARCHAR2) IS
175 l_err_msg VARCHAR2(240);
176 BEGIN
177 FND_FILE.put_line(FND_FILE.LOG, p_msg);
178 END Write_Conclog;
179
180
181
182 PROCEDURE Get_Site_Security_Predicate (
183 p_object_name IN VARCHAR2
184 ,p_party_id IN VARCHAR2
185 ,p_privilege_name IN VARCHAR2
186 ,p_table_alias IN VARCHAR2
187 ,x_security_predicate OUT NOCOPY VARCHAR2
188 ) IS
189 l_return_status VARCHAR2(30);
190 l_table_alias VARCHAR2(100);
191 l_security_predicate VARCHAR2(32767);
192 l_api_name VARCHAR2(30);
193 l_request_id_clause VARCHAR2(500);
194 l_process_flag VARCHAR2(10);
195 BEGIN
196 l_api_name := 'Get_Site_Security_Predicate';
197 -- SetGlobals();
198
199 IF (LENGTH(p_table_alias) > 0) THEN
200 l_table_alias := p_table_alias || '.';
201 END IF;
202
203 EGO_DATA_SECURITY.get_security_predicate(
204 p_api_version => 1.0
205 ,p_function => p_privilege_name
206 ,p_object_name => p_object_name
207 ,p_user_name => p_party_id
208 ,p_statement_type => 'EXISTS'
209 ,p_pk1_alias => l_table_alias||'SITE_ID'
210 ,x_predicate => x_security_predicate
211 ,x_return_status => l_return_status
212 );
213
214
215 IF (x_security_predicate IS NULL) THEN
216 x_security_predicate := ' 1=1 '; --for internal users the security predicate is returned as null.
217 ELSE
218 x_security_predicate := x_security_predicate ||
219 ' AND NOT EXISTS
220 (SELECT 1
221 FROM RRS_SITES_INTERFACE rsi_e
222 WHERE rsi_e.TRANSACTION_TYPE = ''CREATE''
223 AND rsi_e.PROCESS_STATUS = ''1''
224 -- AND rsi_e.BATCH_ID = UAI2.BATCH_ID
225 AND rsi_e.SITE_ID = UAI2.SITE_ID
226 )';
227 END IF;
228 END Get_Site_Security_Predicate;
229
230
231
232 PROCEDURE LOAD_USERATTR_DATA(
233 ERRBUF OUT NOCOPY VARCHAR2
234 ,RETCODE OUT NOCOPY VARCHAR2
235 ,p_batch_id IN NUMBER
236 ,p_data_set_id IN NUMBER
237 ,p_purge_successful_lines IN VARCHAR2 DEFAULT FND_API.G_FALSE) IS
238 l_entity_index_counter NUMBER := 0;
239 l_debug_level NUMBER := 0;
240 l_user_attrs_return_status VARCHAR2(100);
241 l_return_status VARCHAR2(1);
242 l_errorcode NUMBER;
243 l_msg_count NUMBER;
244 l_msg_data VARCHAR2(1000);
245 l_rel_sql VARCHAR2(1000);
246 l_cnt NUMBER := 0;
247
248 l_default_dl_view_priv_obj EGO_COL_NAME_VALUE_PAIR_OBJ;
249 l_default_dl_edit_priv_obj EGO_COL_NAME_VALUE_PAIR_OBJ;
250 l_default_dl_view_priv_list EGO_COL_NAME_VALUE_PAIR_ARRAY;
251 l_default_dl_edit_priv_list EGO_COL_NAME_VALUE_PAIR_ARRAY;
252
253 l_privilege_predicate_api_name VARCHAR2(250);
254 l_security_for_validate VARCHAR2(30) := NVL(FND_PROFILE.VALUE('RRS_ROLE_BASED_SECURITY_ENABLED'),'N');
255
256 TYPE local_site_id_list_rec is RECORD (
257 site_id rrs_site_ua_intf.site_id%TYPE,
258 site_number rrs_site_ua_intf.site_number%TYPE,
259 transaction_type rrs_site_ua_intf.transaction_type%TYPE
260 );
261
262 TYPE local_site_id_list_tab IS TABLE OF local_site_id_list_rec;
263 l_site_id_list local_site_id_list_tab;
264
265 local_processing_errors rrs_processing_errors_tab;
266 p_processing_errors rrs_processing_errors_tab;
267 l_row_status varchar2(1);
268
269 CURSOR c_rrs_site_ua_intf is
270 select count(*) from RRS_SITE_UA_INTF
271 WHERE PROCESS_STATUS = G_PS_TO_BE_PROCESSED
272 AND data_set_id = p_data_set_id;
273 CURSOR c_rrs_loc_ua_intf is
274 select count(*) from RRS_LOCATION_UA_INTF
275 WHERE PROCESS_STATUS = G_PS_TO_BE_PROCESSED
276 AND data_set_id = p_data_set_id;
277 CURSOR c_rrs_ta_ua_intf is
278 SELECT count(*) FROM RRS_TRADEAREA_UA_INTF
279 WHERE PROCESS_STATUS = G_PS_TO_BE_PROCESSED
280 AND data_set_id = p_data_set_id;
281 BEGIN
282
283 Write_Conclog('Processing the User Defined Attributes ' );
284 IF (Error_Handler.Get_Debug = 'Y') THEN
285 l_debug_level := 3; --continue writing to the Debug Log opened.
286 ELSE
287 l_debug_level := 0; --Since Debug log is not opened, donot open Debug log for User-Attrs also.
288 END IF;
292 Open c_rrs_site_ua_intf;
289 Write_Conclog('Executing EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data -'||'RRS_SITES' );
290 l_user_attrs_return_status := FND_API.G_RET_STS_SUCCESS;
291
293 fetch c_rrs_site_ua_intf into l_cnt;
294 Close c_rrs_site_ua_intf;
295
296 IF l_cnt > 0 THEN
297
298
299 -----------------------------------------------
300 -- Set this global variable once per session --
301 -----------------------------------------------
302 IF (G_HZ_PARTY_ID IS NULL) THEN
303 IF (G_USER_NAME IS NOT NULL) THEN
304 SELECT 'HZ_PARTY:'||TO_CHAR(PERSON_ID)
305 INTO G_HZ_PARTY_ID
306 FROM EGO_PEOPLE_V
307 WHERE USER_NAME = G_USER_NAME;
308 ELSE
309 RAISE G_NO_USER_NAME_TO_VALIDATE;
310 END IF;
311 END IF;
312
313
314 UPDATE RRS_SITE_UA_INTF
315 SET PROCESS_STATUS = G_PS_IN_PROCESS
316 ,REQUEST_ID = G_REQUEST_ID
317 ,PROGRAM_APPLICATION_ID = G_PROGAM_APPLICATION_ID
318 ,PROGRAM_ID = G_PROGAM_ID
319 ,PROGRAM_UPDATE_DATE = SYSDATE
320 ,TRANSACTION_TYPE = UPPER(NVL(TRANSACTION_TYPE,EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE))
321 WHERE DATA_SET_ID = p_data_set_id
322 AND (PROCESS_STATUS IS NULL OR
323 PROCESS_STATUS = G_PS_TO_BE_PROCESSED);
324
325 l_rel_sql := ' (SELECT RSU.SITE_USE_TYPE_CODE '||
326 ' FROM RRS_SITE_USES RSU' ||
327 ' WHERE RSU.SITE_ID = UAI2.SITE_ID)'||
328 ' UNION ALL ' ||
329 ' (SELECT UAI2.SITE_USE_TYPE_CODE FROM DUAL)' ;
330
331
332
333
334 IF l_security_for_validate = 'Y' THEN
335
336
337 /* RBAC 2 related code starts here */
338
339
340 select distinct site_id , site_number, transaction_type
341 Bulk Collect
342 into l_site_id_list
343 from rrs_site_ua_intf
344 where data_set_id = p_data_set_id;
345
346 if l_site_id_list.count > 0 then
347
348 FND_FILE.put_line(FND_FILE.log,'Count is : '||l_site_id_list.count);
349
350 for i in l_site_id_list.First..l_site_id_list.Last Loop
351
352 l_row_status := 'S';
353 p_processing_errors := rrs_processing_errors_tab();
354 local_processing_errors := rrs_processing_errors_tab();
355
356
357 If (EGO_SECURITY_PUB.check_user_privilege(p_api_version => G_API_VERSION, p_privilege => 'RRS_VIEW_SITE', p_object_name => 'RRS_SITE', p_object_key => l_site_id_list(i).site_id, p_user_id => FND_GLOBAL.USER_ID )) = FND_API.G_TRUE then
358
359 null;
360 else
361 l_row_status := 'E';
362 rrs_import_interface_pkg.prepare_error_mesg(
363 p_site_id => NULL
364 ,p_site_id_num => l_site_id_list(i).site_number
365 ,p_column_name => 'SITE_ID'
366 ,p_message_name => 'MESSAGE NAME'
367 ,p_message_text => 'User does not have View Privileges on Site : '||l_site_id_list(i).site_number
368 ,p_source_table_name => 'RRS_SITE_UA_INTF'
369 ,p_destination_table_name => 'RRS_SITES_EXT_B'
370 ,p_process_status => '3'
371 ,p_transaction_type => l_site_id_list(i).transaction_type
372 ,p_batch_id => p_batch_id
373 ,p_processing_errors => local_processing_errors
374 );
375
376
377
378 end if;
379
380 If (EGO_SECURITY_PUB.check_user_privilege(p_api_version => G_API_VERSION, p_privilege => 'RRS_EDIT_SITE',
381 p_object_name => 'RRS_SITE', p_object_key => l_site_id_list(i).site_id,
382 p_user_id => FND_GLOBAL.USER_ID )) = FND_API.G_TRUE then
383
384 null;
385 else
386 l_row_status := 'E';
387 rrs_import_interface_pkg.prepare_error_mesg(
388 p_site_id => NULL
389 ,p_site_id_num => l_site_id_list(i).site_number
390 ,p_column_name => 'SITE_ID'
391 ,p_message_name => 'MESSAGE NAME'
392 ,p_message_text => 'User does not have Edit Privileges on Site : '||l_site_id_list(i).site_number
393 ,p_source_table_name => 'RRS_SITE_UA_INTF'
394 ,p_destination_table_name => 'RRS_SITES_EXT_B'
395 ,p_process_status => '3'
396 ,p_transaction_type => l_site_id_list(i).transaction_type
397 ,p_batch_id => p_batch_id
398 ,p_processing_errors => local_processing_errors
399 );
400
401
402 end if;
403
404 if l_row_status = 'E' then
405
406 Update RRS_SITE_UA_INTF
407 Set Process_status = '3'
408 Where data_set_id = p_data_set_id
409 and process_status = G_PS_IN_PROCESS
410 and site_id = l_site_id_list(i).site_id
411 and transaction_type = l_site_id_list(i).transaction_type;
412
413 rrs_import_interface_pkg.Write_interface_errors(p_processing_errors => local_processing_errors);
414
415 end if;
416
417
418 end loop; -- l_site_id_list.First to Last
419
420 end if; -- l_site_id_list > 0
421
422
423 /* RBAC 2 related code ends here */
424
425
426
430 l_default_dl_view_priv_list := EGO_COL_NAME_VALUE_PAIR_ARRAY();
427 l_privilege_predicate_api_name := 'RRS_SITE_UDA_BULKLOAD_INTF.Get_Site_Security_Predicate';
428
429 -- creating default privileges
431 l_default_dl_edit_priv_list := EGO_COL_NAME_VALUE_PAIR_ARRAY();
432
433 l_default_dl_view_priv_obj := EGO_COL_NAME_VALUE_PAIR_OBJ(71802, NULL);
434 l_default_dl_edit_priv_obj := EGO_COL_NAME_VALUE_PAIR_OBJ(71802, NULL);
435
436 l_default_dl_view_priv_list.EXTEND;
437 l_default_dl_view_priv_list(l_default_dl_view_priv_list.COUNT) := l_default_dl_view_priv_obj;
438
439 l_default_dl_edit_priv_list.EXTEND;
440 l_default_dl_edit_priv_list(l_default_dl_edit_priv_list.COUNT) := l_default_dl_edit_priv_obj;
441
442 ELSE
443 l_privilege_predicate_api_name := NULL;
444 END IF;
445
446 Write_Conclog('Executing EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data -'||'RRS_SITE' );
447 EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data (
448 p_api_version => G_API_VERSION --IN NUMBER
449 ,p_application_id => 718 --IN NUMBER
450 ,p_attr_group_type => 'RRS_SITEMGMT_GROUP' --IN VARCHAR2
451 ,p_object_name => 'RRS_SITE' --IN VARCHAR2
452 ,p_hz_party_id => G_HZ_PARTY_ID
453 ,p_interface_table_name => 'RRS_SITE_UA_INTF' --IN VARCHAR2
454 ,p_data_set_id => p_data_set_id --IN NUMBER
455 ,p_entity_id => G_ENTITY_ID --IN NUMBER
456 ,p_entity_index => l_entity_index_counter --IN NUMBER
457 ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
458 --,p_debug_level => p_debug_level --IN NUMBER
459 ,p_debug_level => l_debug_level --IN NUMBER
460 ,p_init_error_handler => FND_API.G_TRUE --IN VARCHAR2
461 ,p_init_fnd_msg_list => FND_API.G_TRUE --IN VARCHAR2
462 ,p_log_errors => FND_API.G_TRUE --IN VARCHAR2
463 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE --IN VARCHAR2
464 ,p_commit => FND_API.G_TRUE --IN VARCHAR2
465 ,p_default_dl_view_priv_list => l_default_dl_view_priv_list --IN VARCHAR2
466 ,p_default_dl_edit_priv_list => l_default_dl_edit_priv_list
467 ,p_privilege_predicate_api_name => l_privilege_predicate_api_name
468 ,p_related_class_codes_query => l_rel_sql --IN VARCHAR2
469 ,p_validate => TRUE
470 ,p_do_dml => TRUE
471 ,p_do_req_def_valiadtion => TRUE
472 ,x_return_status => l_user_attrs_return_status --OUT NOCOPY VARCHAR2
473 ,x_errorcode => l_errorcode --OUT NOCOPY NUMBER
474 ,x_msg_count => l_msg_count --OUT NOCOPY NUMBER
475 ,x_msg_data => l_msg_data --OUT NOCOPY VARCHAR2
476 );
477
478 Write_Conclog('Executed Site User Defined Attributes Upload API');
479 Write_Conclog('G_API_VERSION ' || G_API_VERSION);
480 Write_Conclog('G_HZ_PARTY_ID ' || G_HZ_PARTY_ID);
481 Write_Conclog('p_data_set_id ' || p_data_set_id);
482 Write_Conclog('G_ENTITY_ID ' || G_ENTITY_ID);
483 Write_Conclog('l_entity_index_counter ' || l_entity_index_counter);
484 Write_Conclog(' G_ENTITY_CODE ' || G_ENTITY_CODE );
485 Write_Conclog('l_debug_level ' || l_debug_level );
486 Write_Conclog('l_rel_sql ' || l_rel_sql );
487 Write_Conclog('Return Status '||l_user_attrs_return_status);
488 Write_Conclog('Error Code '||l_errorcode);
489 Write_Conclog('msg count '||l_msg_count);
490 Write_Conclog('msg data '||l_msg_data);
491
492 IF l_user_attrs_return_status = 'U' then
493
494 rollback;
495 Write_Conclog('Returs Status is Unexpected. Transaction has been rollbacked. Please check the Database alert log file');
496
497 ELSE
498
499
500 IF ( p_purge_successful_lines = 'Y') THEN
501 -----------------------------------------------
502 -- Delete all successful rows from the table --
503 -- (they're the only rows still in process) --
504 -----------------------------------------------
505 DELETE FROM RRS_SITE_UA_INTF
506 WHERE BATCH_ID = p_batch_id
507 AND PROCESS_STATUS = G_PS_IN_PROCESS;
508 ELSE
509 ----------------------------------------------
510 -- Mark all rows we've processed as success --
511 -- if they weren't marked as failure above --
512 ----------------------------------------------
513 UPDATE RRS_SITE_UA_INTF
514 SET PROCESS_STATUS = G_PS_SUCCESS
515 WHERE batch_id = p_batch_id
516 AND PROCESS_STATUS = G_PS_IN_PROCESS;
517 END IF;
518
519 END IF;
520
521
522 END IF;
523 l_cnt := 0;
524 Open c_rrs_loc_ua_intf;
525 Fetch c_rrs_loc_ua_intf into l_cnt;
526 Close c_rrs_loc_ua_intf;
527 IF l_cnt > 0 THEN
528 UPDATE RRS_LOCATION_UA_INTF
529 SET PROCESS_STATUS = G_PS_IN_PROCESS
530 ,REQUEST_ID = G_REQUEST_ID
534 ,CREATED_BY = DECODE(CREATED_BY, NULL, G_USER_ID, CREATED_BY)
531 ,PROGRAM_APPLICATION_ID = G_PROGAM_APPLICATION_ID
532 ,PROGRAM_ID = G_PROGAM_ID
533 ,PROGRAM_UPDATE_DATE = SYSDATE
535 ,CREATION_DATE = DECODE(CREATION_DATE, NULL, SYSDATE, CREATION_DATE)
536 ,LAST_UPDATED_BY = G_USER_ID
537 ,LAST_UPDATE_DATE = SYSDATE
538 ,LAST_UPDATE_LOGIN = G_LOGIN_ID
539 ,TRANSACTION_TYPE = UPPER(NVL(TRANSACTION_TYPE,EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE))
540 WHERE DATA_SET_ID = p_data_set_id
541 AND (PROCESS_STATUS IS NULL OR
542 PROCESS_STATUS = G_PS_TO_BE_PROCESSED);
543 -- l_rel_sql := 'SELECT CODE FROM RRS_LOCATIONS_OCV' ;
544
545 l_rel_sql := ' (SELECT HL.COUNTRY '||
546 ' FROM HZ_LOCATIONS HL' ||
547 ' WHERE HL.LOCATION_ID = UAI2.LOCATION_ID)'||
548 ' UNION ALL ' ||
549 ' (SELECT UAI2.COUNTRY FROM DUAL)' ;
550
551 Write_Conclog('Executing EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data -'||'RRS_LOCATION' );
552
553 EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data (
554 p_api_version => G_API_VERSION --IN NUMBER
555 ,p_application_id => 718 --IN NUMBER
556 ,p_attr_group_type => 'RRS_LOCATION_GROUP' --IN VARCHAR2
557 ,p_object_name => 'RRS_LOCATION' --IN VARCHAR2
558 ,p_hz_party_id => G_HZ_PARTY_ID
559 ,p_interface_table_name => 'RRS_LOCATION_UA_INTF' --IN VARCHAR2
560 ,p_data_set_id => p_data_set_id --IN NUMBER
561 ,p_entity_id => G_ENTITY_ID --IN NUMBER
562 ,p_entity_index => l_entity_index_counter --IN NUMBER
563 ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
564 --,p_debug_level => p_debug_level --IN NUMBER
565 ,p_debug_level => l_debug_level --IN NUMBER
566 ,p_init_error_handler => FND_API.G_TRUE --IN VARCHAR2
567 ,p_init_fnd_msg_list => FND_API.G_TRUE --IN VARCHAR2
568 ,p_log_errors => FND_API.G_TRUE --IN VARCHAR2
569 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE --IN VARCHAR2
570 ,p_commit => FND_API.G_TRUE --IN VARCHAR2
571 ,p_default_view_privilege => 'RRS_LOCATION_VIEW' --IN VARCHAR2
572 ,p_default_edit_privilege => NULL
573 ,p_privilege_predicate_api_name => NULL
574 ,p_related_class_codes_query => l_rel_sql --IN VARCHAR2
575 ,p_validate => TRUE
576 ,p_do_dml => TRUE
577 ,p_do_req_def_valiadtion => TRUE
578 ,x_return_status => l_user_attrs_return_status --OUT NOCOPY VARCHAR2
579 ,x_errorcode => l_errorcode --OUT NOCOPY NUMBER
580 ,x_msg_count => l_msg_count --OUT NOCOPY NUMBER
581 ,x_msg_data => l_msg_data --OUT NOCOPY VARCHAR2
582 );
583 Write_Conclog('Executed Location User Defined Attributes Upload API');
584 Write_Conclog('Return Status '||l_user_attrs_return_status);
585 Write_Conclog('Error Code '||l_errorcode);
586 Write_Conclog('msg count '||l_msg_count);
587 Write_Conclog('msg data '||l_msg_data);
588
589 IF l_user_attrs_return_status = 'U' then
590
591 rollback;
592 Write_Conclog('Returs Status is Unexpected. Transaction has been rollbacked. Please check the Database alert log file');
593
594 ELSE
595
596 IF ( p_purge_successful_lines = 'Y' ) THEN
597 -----------------------------------------------
598 -- Delete all successful rows from the table --
599 -- (they're the only rows still in process) --
600 -----------------------------------------------
601 DELETE FROM RRS_LOCATION_UA_INTF
602 WHERE BATCH_ID = p_batch_id
603 AND PROCESS_STATUS = G_PS_IN_PROCESS;
604 ELSE
605 ----------------------------------------------
606 -- Mark all rows we've processed as success --
607 -- if they weren't marked as failure above --
608 ----------------------------------------------
609 UPDATE RRS_LOCATION_UA_INTF
610 SET PROCESS_STATUS = G_PS_SUCCESS
611 WHERE batch_id = p_batch_id
612 AND PROCESS_STATUS = G_PS_IN_PROCESS;
613 END IF;
614 END IF;
615
616 END IF;
617 l_cnt := 0;
618 Open c_rrs_ta_ua_intf;
619 fetch c_rrs_ta_ua_intf into l_cnt;
620 Close c_rrs_ta_ua_intf;
621 IF l_cnt > 0 THEN
622 UPDATE RRS_TRADEAREA_UA_INTF
623 SET PROCESS_STATUS = G_PS_IN_PROCESS
624 ,REQUEST_ID = G_REQUEST_ID
625 ,PROGRAM_APPLICATION_ID = G_PROGAM_APPLICATION_ID
626 ,PROGRAM_ID = G_PROGAM_ID
627 ,PROGRAM_UPDATE_DATE = SYSDATE
628 ,CREATED_BY = DECODE(CREATED_BY, NULL, G_USER_ID, CREATED_BY)
629 ,CREATION_DATE = DECODE(CREATION_DATE, NULL, SYSDATE, CREATION_DATE)
630 ,LAST_UPDATED_BY = G_USER_ID
631 ,LAST_UPDATE_DATE = SYSDATE
632 ,LAST_UPDATE_LOGIN = G_LOGIN_ID
636 PROCESS_STATUS = G_PS_TO_BE_PROCESSED);
633 ,TRANSACTION_TYPE = UPPER(NVL(TRANSACTION_TYPE,EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE))
634 WHERE DATA_SET_ID = p_data_set_id
635 AND (PROCESS_STATUS IS NULL OR
637 -- l_rel_sql := 'SELECT CODE FROM RRS_TRADE_AREAS_OCV' ;
638
639
640 l_rel_sql := ' (SELECT to_char(RTA.GROUP_ID) '||
641 ' FROM RRS_TRADE_AREAS RTA' ||
642 ' WHERE RTA.TRADE_AREA_ID = UAI2.TRADE_AREA_ID)'||
643 ' UNION ALL ' ||
644 ' (SELECT UAI2.GROUP_ID FROM DUAL)' ;
645
646 Write_Conclog('Executing EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data -'||'RRS_TRADE_AREA' );
647
648 EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data (
649 p_api_version => G_API_VERSION --IN NUMBER
650 ,p_application_id => 718 --IN NUMBER
651 ,p_attr_group_type => 'RRS_TRADE_AREA_GROUP' --IN VARCHAR2
652 ,p_object_name => 'RRS_TRADE_AREA' --IN VARCHAR2
653 ,p_hz_party_id => G_HZ_PARTY_ID
654 ,p_interface_table_name => 'RRS_TRADEAREA_UA_INTF' --IN VARCHAR2
655 ,p_data_set_id => p_data_set_id --IN NUMBER
656 ,p_entity_id => G_ENTITY_ID --IN NUMBER
657 ,p_entity_index => l_entity_index_counter --IN NUMBER
658 ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
659 --,p_debug_level => p_debug_level --IN NUMBER
660 ,p_debug_level => l_debug_level --IN NUMBER
661 ,p_init_error_handler => FND_API.G_TRUE --IN VARCHAR2
662 ,p_init_fnd_msg_list => FND_API.G_TRUE --IN VARCHAR2
663 ,p_log_errors => FND_API.G_TRUE --IN VARCHAR2
664 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE --IN VARCHAR2
665 ,p_commit => FND_API.G_TRUE --IN VARCHAR2
666 ,p_default_view_privilege => 'RRS_TRADE_AREA_VIEW' --IN VARCHAR2
667 ,p_default_edit_privilege => NULL
668 ,p_privilege_predicate_api_name => NULL
669 ,p_related_class_codes_query => l_rel_sql --IN VARCHAR2
670 ,p_validate => TRUE
671 ,p_do_dml => TRUE
672 ,p_do_req_def_valiadtion => TRUE
673 ,x_return_status => l_user_attrs_return_status --OUT NOCOPY VARCHAR2
674 ,x_errorcode => l_errorcode --OUT NOCOPY NUMBER
675 ,x_msg_count => l_msg_count --OUT NOCOPY NUMBER
676 ,x_msg_data => l_msg_data --OUT NOCOPY VARCHAR2
677 );
678 Write_Conclog('Executed Trade Area User Defined Attributes Upload API');
679 Write_Conclog('Return Status '||l_user_attrs_return_status);
680 Write_Conclog('Error Code '||l_errorcode);
681 Write_Conclog('msg count '||l_msg_count);
682 Write_Conclog('msg data '||l_msg_data);
683
684 IF l_user_attrs_return_status = 'U' then
685
686 rollback;
687 Write_Conclog('Returs Status is Unexpected. Transaction has been rollbacked. Please check the Database alert log file');
688
689 ELSE
690 IF ( p_purge_successful_lines = 'Y' ) THEN
691 -----------------------------------------------
692 -- Delete all successful rows from the table --
693 -- (they're the only rows still in process) --
694 -----------------------------------------------
695 DELETE FROM RRS_TRADEAREA_UA_INTF
696 WHERE BATCH_ID = p_batch_id
697 AND PROCESS_STATUS = G_PS_IN_PROCESS;
698 ELSE
699 ----------------------------------------------
700 -- Mark all rows we've processed as success --
701 -- if they weren't marked as failure above --
702 ----------------------------------------------
703 UPDATE RRS_TRADEAREA_UA_INTF
704 SET PROCESS_STATUS = G_PS_SUCCESS
705 WHERE batch_id = p_batch_id
706 AND PROCESS_STATUS = G_PS_IN_PROCESS;
707 END IF;
708 END IF;
709 END IF;
710
711 -------------------------------------------------------------------
712 -- Finally, we log any errors that we've accumulated throughout --
713 -- our conversions and looping (including all errors encountered --
714 -- within our Business Object's processing) --
715 -------------------------------------------------------------------
716 Write_Conclog('****Dumping the List of Error messages into the Concurrent Log***');
717
718 ERROR_HANDLER.Log_Error(
719 p_write_err_to_inttable => 'N'
720 ,p_write_err_to_conclog => 'Y'
721 ,p_write_err_to_debugfile => 'Y'
722 );
723 Write_Conclog('****End of All Error messages***');
724 -----------------------------------------------------------
725 -- Let caller know whether any rows failed in processing --
726 -----------------------------------------------------------
730 RETCODE := G_RETCODE_SUCCESS_WITH_WARNING;
727 IF ( l_user_attrs_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
728 RETCODE := FND_API.G_RET_STS_SUCCESS;
729 ELSIF ( l_user_attrs_return_status = G_RETCODE_SUCCESS_WITH_WARNING ) THEN
731 ELSIF ( l_user_attrs_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
732 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
733 ROLLBACK;
734 END IF;
735
736 BEGIN
737 INSERT INTO rrs_interface_errors
738 (SITE_ID
739 ,SITE_IDENTIFICATION_NUMBER
740 ,COLUMN_NAME
741 ,MESSAGE_NAME
742 ,MESSAGE_TYPE
743 ,MESSAGE_TEXT
744 ,SOURCE_TABLE_NAME
745 ,DESTINATION_TABLE_NAME
746 ,CREATED_BY
747 ,CREATION_DATE
748 ,LAST_UPDATED_BY
749 ,LAST_UPDATE_DATE
750 ,LAST_UPDATE_LOGIN
751 ,REQUEST_ID
752 ,PROGRAM_APPLICATION_ID
753 ,PROGRAM_ID
754 ,PROGRAM_UPDATE_DATE
755 ,PROCESS_STATUS
756 ,TRANSACTION_TYPE
757 ,BATCH_ID
758 )
759 SELECT NULL
760 ,NULL
761 ,COLUMN_NAME
762 ,MESSAGE_NAME
763 ,MESSAGE_TYPE
764 ,ERROR_MESSAGE
765 ,'RRS_SITE_UA_INTF'
766 ,'RRS_INTERFACE_ERRORS'
767 ,CREATED_BY
768 ,CREATION_DATE
769 ,LAST_UPDATED_BY
770 ,LAST_UPDATE_DATE
771 ,LAST_UPDATE_LOGIN
772 ,REQUEST_ID
773 ,PROGRAM_APPLICATION_ID
774 ,PROGRAM_ID
775 ,PROGRAM_UPDATE_DATE
776 ,'3'
777 ,NULL
778 ,p_batch_id
779 FROM mtl_interface_errors
780 WHERE request_id = fnd_global.conc_request_id
781 AND program_application_id = FND_GLOBAL.PROG_APPL_ID;
782
783 END;
784
785
786 COMMIT;
787 EXCEPTION
788 WHEN OTHERS THEN
789 ----------------------------------------
790 -- Mark all rows in process as errors --
791 ----------------------------------------
792 Write_Conclog('Error! While Processing User Defined Attributes ' ) ;
793 Write_Conclog('Error while processing Process User Attrs data API '||SQLCODE || ':'||SQLERRM);
794 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
795 ROLLBACK;
796 END LOAD_USERATTR_DATA;
797 END;