[Home] [Help]
PACKAGE BODY: APPS.EGO_ITEM_PEOPLE_IMPORT_PKG
Source
1 PACKAGE BODY EGO_ITEM_PEOPLE_IMPORT_PKG AS
2 /* $Header: EGOCIPIB.pls 120.16 2007/10/03 12:45:57 srajapar ship $ */
3
4 -- =================================================================
5 -- Global variables used in Concurrent Program.
6 -- =================================================================
7
8 G_USER_ID NUMBER := -1;
9 G_LOGIN_ID NUMBER := -1;
10 G_PROG_APPID NUMBER := -1;
11 G_PROG_ID NUMBER := -1;
12 G_REQUEST_ID NUMBER := -1;
13
14 -- =================================================================
15 -- Global constants that need to be used.
16 -- =================================================================
17 -- The user language (to display the error messages in appropriate language)
18 G_SESSION_LANG VARCHAR2(99) := USERENV('LANG');
19
20 --Indicates the object name
21 G_FND_OBJECT_NAME VARCHAR2(99) := 'EGO_ITEM';
22
23 --Indicates the object id (set using g_Fnd_Object_Name)
24 G_FND_OBJECT_ID fnd_objects.object_id%TYPE;
25
26 -- Seeded value for all_users (group available in hz_parties)
27 G_ALL_USERS_PARTY_ID PLS_INTEGER := -1000;
28
29 -- Batch size that needs to be processed
30 G_BATCH_SIZE NUMBER;
31
32 -- Message array size
33 G_MAX_MESSAGE_SIZE PLS_INTEGER := 1000;
34
35 G_ERROR_TABLE_NAME VARCHAR2(99) := 'EGO_ITEM_PEOPLE_INTF';
36 G_ERROR_ENTITY_CODE VARCHAR2(99) := 'EGO_ITEM_PEOPLE';
37 G_ERROR_FILE_NAME VARCHAR2(99);
38 G_BO_IDENTIFIER VARCHAR2(99) := 'EGO_ITEM_PEOPLE';
39 --
40 -- return status from VALIDATE_UPDATE_GRANT
41 -- used for status reference between
42 -- validate_no_grant_overlap and validate_update_grant
43 --
44 G_UPDATE_REC_DONE NUMBER := 1;
45 G_UPDATE_OVERLAP_ERROR NUMBER := -1;
46 G_UPDATE_REC_NOT_FOUND NUMBER := -2;
47 --
48 -- return status from VALIDATE_INSERT_GRANT
49 -- used for status reference between
50 -- validate_no_grant_overlap and validate_insert_grant
51 --
52 G_INSERT_REC_DONE NUMBER := 1;
53 G_INSERT_OVERLAP_ERROR NUMBER := -1;
54 --
55 -- variables that will be used across programs
56 --
57 G_DATA_SET_ID EGO_ITEM_PEOPLE_INTF.data_set_id%TYPE;
58 G_FROM_LINE_NUMBER NUMBER;
59 G_TO_LINE_NUMBER NUMBER;
60 G_TRANSACTION_ID NUMBER;
61 G_DEBUG_MODE PLS_INTEGER;
62
63 -- intermediate statuses for errors
64 G_INT_ITEM_VAL_ERROR NUMBER := 100;
65 G_INT_ORG_VAL_ERROR NUMBER := 110;
66
67 G_HAS_ERRORS BOOLEAN;
68
69 G_TABLE_LOG BOOLEAN;
70 G_FILE_LOG BOOLEAN;
71 --Indicates the person has Global access (full privileges) to access
72 --all the Items.
73 --Eg. This can happen when the profile is set to grant internal employees
74 --an 'Item Owner' role. Then an employee gets Full access to all the items.
75 --He can make grants to the items etc.,
76 G_FULL_ACCESS_ITEMS BOOLEAN := FALSE;
77
78 -----------------------------------------------------------------------
79 -- Debug Profile option used to write Error_Handler.Write_Debug --
80 -- Profile option name = INV_DEBUG_TRACE ; --
81 -- User Profile Option Name = INV: Debug Trace --
82 -- Values: 1 (True) ; 0 (False) --
83 -- NOTE: This better than MRP_DEBUG which is used at many places. --
84 -----------------------------------------------------------------------
85 G_ERR_HANDLER_DEBUG CONSTANT VARCHAR2(10) := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
86
87 -- =================================================================
88 -- Global variables used for Bulk Processing
89 -- =================================================================
90 -- TYPE g_t_grant_guid_table IS TABLE OF fnd_grants.grant_guid%TYPE INDEX BY BINARY_INTEGER;
91 --
92 -- TYPE g_t_transaction_id_table IS TABLE OF ego_item_people_intf.transaction_id%TYPE INDEX BY BINARY_INTEGER;
93 -- TYPE g_t_start_date_table IS TABLE OF ego_item_people_intf.start_date%TYPE INDEX BY BINARY_INTEGER;
94 -- TYPE g_t_end_date_table IS TABLE OF ego_item_people_intf.end_date%TYPE INDEX BY BINARY_INTEGER;
95 -- TYPE g_t_grantee_type_table IS TABLE OF ego_item_people_intf.grantee_type%TYPE INDEX BY BINARY_INTEGER;
96 -- TYPE g_t_grantee_name_table IS TABLE OF ego_item_people_intf.grantee_name%TYPE INDEX BY BINARY_INTEGER;
97 -- TYPE g_t_inventory_item_id_table IS TABLE OF ego_item_people_intf.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
98 -- TYPE g_t_internal_role_id_table IS TABLE OF ego_item_people_intf.internal_role_id%TYPE INDEX BY BINARY_INTEGER;
99 -- TYPE g_t_grantee_party_id_table IS TABLE OF ego_item_people_intf.grantee_party_id%TYPE INDEX BY BINARY_INTEGER;
100 -- TYPE g_t_grantee_key_table IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
101
102 ----------------------------------------------------------------------
103 -- Global variables used for Parsing process.
104 ----------------------------------------------------------------------
105
106 /*----------------------------------------------------------------------
107 -- TODO
108
109 1. Didnot do a bulk call for Error writing, as Rahul said he has the
110 Generic Error Handler API that can be used for the same.
111 So referred Item Category Assignment API for coding call to Generic
112 Error API.
113
114 ----------------------------------------------------------------------*/
115
116 ---------------------------------------------
117 -- PRIVATE PROCEDURES AND FUNCTIONS --
118 ---------------------------------------------
119
120 debug_line_count PLS_INTEGER := 0;
121
122 ----------------------------------------------------------
123 -- Writing given string to Concurrent Log File --
124 ----------------------------------------------------------
125
126 PROCEDURE Conc_Log (p_msg IN VARCHAR2) IS
127 BEGIN
128 FND_FILE.put_line(which => fnd_file.log
129 ,buff => p_msg);
130 EXCEPTION
131 WHEN OTHERS THEN
132 NULL;
133 END Conc_Log;
134
135 ----------------------------------------------------------
136 -- Writing given string to Concurrent Output --
137 ----------------------------------------------------------
138
139 PROCEDURE Conc_Output (p_msg IN VARCHAR2) IS
140 BEGIN
141 FND_FILE.put_line (which => fnd_file.output
142 ,buff => p_msg);
143 EXCEPTION
144 WHEN OTHERS THEN
145 NULL;
146 END Conc_Output;
147
148 ----------------------------------------------------------
149 -- Writing given string to Error Handler Log File --
150 ----------------------------------------------------------
151
152 PROCEDURE Write_Debug (p_message VARCHAR2) IS
153 -- Start OF comments
154 -- API name : debug function
155 -- TYPE : PRIVATE
156 -- Pre-reqs : None
157 -- FUNCTION : log the error as per the debug mode chosen by the user
158 --
159 -- Parameters:
160 -- IN : message to be logged
161 BEGIN
162 -- DEBUG_MODE_FATAL NUMBER := 1;
163 -- DEBUG_MODE_ERROR NUMBER := 2;
164 -- DEBUG_MODE_INFO NUMBER := 3;
165 -- DEBUG_MODE_DEBUG NUMBER := 4;
166
167 IF G_DEBUG_MODE = DEBUG_MODE_FATAL THEN
168 -- only fatal errors should be logged
169 NULL;
170 ELSIF G_DEBUG_MODE = DEBUG_MODE_ERROR THEN
171 -- only errors needs to be logged
172 NULL;
173 ELSIF G_DEBUG_MODE = DEBUG_MODE_INFO THEN
174 -- all errors and info needs to be logged
175 NULL;
176 ELSIF G_DEBUG_MODE = DEBUG_MODE_DEBUG THEN
177 -- developer debug mode
178 Conc_log ('['||To_Char(SYSDATE,'DD-MON-RRRR HH24:MI:SS')|| ' => '||p_message);
179 END IF;
180 -- sri_debug(p_message);
181 -------------------------------------------------
182 -- If Error Handler Profile set to TRUE --
183 -------------------------------------------------
184 IF (G_ERR_HANDLER_DEBUG = 1) THEN
185 Error_Handler.Write_Debug('['||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'] '|| p_message);
186 END IF;
187 EXCEPTION
188 WHEN OTHERS THEN
189 ROLLBACK;
190 RAISE;
191 END Write_Debug;
192
193 ----------------------------------------------------------
194 -- --
195 ----------------------------------------------------------
196
197 PROCEDURE error_count_records IS
198 -- Start OF comments
199 -- API name : Error Count Records
200 -- TYPE : PRIVATE
201 -- Pre-reqs : None
202 -- FUNCTION : Get the number of errors encountered for each batch
203 --
204 -- Parameters:
205 -- IN : NONE
206 --
207 l_error_record_count PLS_INTEGER;
208 BEGIN
209 IF G_DEBUG_MODE = DEBUG_MODE_DEBUG THEN
210 SELECT COUNT(*)
211 INTO l_error_record_count
212 FROM ego_item_people_intf
213 WHERE data_set_id = G_DATA_SET_ID
214 AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
215 AND process_status = G_PS_ERROR;
216
217 Write_Debug (' Total error records from ' || TO_CHAR(G_FROM_LINE_NUMBER) || ' to ' || TO_CHAR(G_TO_LINE_NUMBER)|| ' is ' ||to_char(l_error_record_count));
218 END IF;
219 EXCEPTION
220 WHEN OTHERS THEN
221 RAISE;
222 END error_count_records;
223
224 ----------------------------------------------------------
225 -- --
226 ----------------------------------------------------------
227
228 PROCEDURE purge_login_items_table IS
229 -- Start of comments
230 -- API name : purge_login_items_table
231 -- TYPE : PRIVATE
232 -- Pre-reqs : None
233 -- FUNCTION : Delete the records from EGO_LOGIN_ITEMS_TEMP
234 --
235 -- Parameters:
236 -- IN : NONE
237 --
238 BEGIN
239 DELETE EGO_LOGIN_ITEMS_TEMP WHERE CONC_REQUEST_ID = G_REQUEST_ID;
240 EXCEPTION
241 WHEN OTHERS THEN
242 NULL;
243 END purge_login_items_table;
244
245 ----------------------------------------------------------
246 -- To flush the errors recorded in Error Handler into --
247 -- appropriate destination --
248 ----------------------------------------------------------
249 PROCEDURE write_log_now IS
250 -- Start OF comments
251 -- API name : write_log_now
252 -- TYPE : PRIVATE
253 -- Pre-reqs : NONE
254 -- FUNCTION : To write the error into appropriate log
255 --
256 -- Parameters:
257 -- IN : NONE
258 --
259
260 BEGIN
261 G_HAS_ERRORS := TRUE;
262 IF G_TABLE_LOG THEN
263 ERROR_HANDLER.Log_Error(p_write_err_to_inttable => 'Y'
264 ,p_write_err_to_conclog => 'Y'
265 ,p_write_err_to_debugfile => 'N');
266 ELSE
267 ERROR_HANDLER.Log_Error(p_write_err_to_inttable => 'N'
268 ,p_write_err_to_conclog => 'Y'
269 ,p_write_err_to_debugfile => 'N');
270 END IF;
271 EXCEPTION
272 WHEN OTHERS THEN
273 ROLLBACK;
274 RAISE;
275 END write_log_now;
276
277
278 ----------------------------------------------------------
279 -- --
280 ----------------------------------------------------------
281 PROCEDURE check_and_write_log (p_msg_size IN NUMBER
282 ,x_retcode OUT NOCOPY VARCHAR2) IS
283 -- Start OF comments
284 -- API name : check_and_write_log
285 -- TYPE : PRIVATE
286 -- Pre-reqs : NONE
287 --
288 -- FUNCTION : To check the size of error records and
289 -- commit them as per the required standards
290 --
291 -- Parameters:
292 -- IN : NONE
293 --
294 -- OUT : x_retcode VARCHAR2
295 -- return status of the program
296 --
297
298 BEGIN
299 IF Error_Handler.Get_Message_Count() > p_msg_size THEN
300 write_log_now();
301 error_Handler.Initialize();
302 END IF;
303 x_retcode := RETCODE_SUCCESS;
304 EXCEPTION
305 WHEN OTHERS THEN
306 x_retcode := RETCODE_ERROR;
307 purge_login_items_table();
308 ROLLBACK;
309 RAISE;
310 END;
311
312 ----------------------------------------------------------
313 -- Get the object id for the object name passeed --
314 ----------------------------------------------------------
315 PROCEDURE initialize_fnd_object_id(p_object_name IN VARCHAR2) IS
316 -- Start OF comments
317 -- API name : Initialize_fnd_object_id
318 -- TYPE : PRIVATE
319 -- Pre-reqs : None
320 -- FUNCTION : To obtain the object_id of the object
321 --
322 -- Parameters:
323 -- IN : object_name
324 --
325 CURSOR c_fnd_object_id(c_object_name IN VARCHAR2) IS
326 SELECT object_id
327 FROM fnd_objects
328 WHERE obj_name = c_object_name;
329
330 BEGIN
331
332 OPEN c_fnd_object_id(p_object_name);
333 FETCH c_fnd_object_id INTO G_FND_OBJECT_ID;
334 IF c_fnd_object_id%NOTFOUND THEN
335 G_FND_OBJECT_ID := NULL;
336 END IF;
337 CLOSE c_fnd_object_id;
338
339 EXCEPTION
340 WHEN OTHERS THEN
341 IF c_fnd_object_id%ISOPEN THEN
342 CLOSE c_fnd_object_id;
343 END IF;
344 RAISE;
345 END initialize_fnd_object_id;
346
347
348 ----------------------------------------------------------
349 -- Get party id for all users. If record not defined, --
350 -- take the value as -1000
351 ----------------------------------------------------------
352 PROCEDURE initialize_all_users IS
353 -- Start OF comments
354 -- API name : Initialize_all_users
355 -- TYPE : PRIVATE
356 -- Pre-reqs : None
357 -- FUNCTION : To obtain the party_id for all_users
358 --
359 -- Parameters:
360 -- IN : object_name
361 --
362 CURSOR c_all_users_party_id IS
363 SELECT party_id
364 FROM hz_parties
365 WHERE party_type = 'GLOBAL'
366 AND party_name = 'All Users';
367
368 BEGIN
369
370 OPEN c_all_users_party_id;
371 FETCH c_all_users_party_id INTO G_ALL_USERS_PARTY_ID;
372 IF c_all_users_party_id%NOTFOUND THEN
373 G_ALL_USERS_PARTY_ID := -1000;
374 END IF;
375 CLOSE c_all_users_party_id;
376
377 EXCEPTION
378 WHEN OTHERS THEN
379 IF c_all_users_party_id%ISOPEN THEN
380 CLOSE c_all_users_party_id;
381 END IF;
382 RAISE;
383 END initialize_all_users;
384
385 -----------------------------------------------------------
386 -- Populate temporary table EGO_LOGIN_ITEMS_TEMP with --
387 -- the itmes on which the user has 'EGO_ADD_ITEM_PEOPLE' --
388 -- privilege --
389 -----------------------------------------------------------
390 PROCEDURE Initialize_Access_Items
391 (p_login_person_id IN NUMBER,
392 x_retcode OUT NOCOPY VARCHAR2) IS
393 -- Start OF comments
394 -- API name : Initialize_Access_Items
395 -- TYPE : PRIVATE
396 -- Pre-reqs : Valid user has logged in
397 --
398 -- FUNCTION : To populate temporary table EGO_LOGIN_ITEMS_TEMP
399 -- with the items onto which the user can give access
400 --
401 -- Parameters:
402 -- IN : NONE
403 --
404 l_sec_predicate VARCHAR2(10000);
405 l_return_status VARCHAR2(10);
406
407 l_count NUMBER := 0;
408 l_select_sql VARCHAR2(32767);
409 l_insert_sql VARCHAR2(500);
410 cursor_select INTEGER;
411 cursor_insert INTEGER;
412 cursor_execute INTEGER;
413 l_item_id_table DBMS_SQL.NUMBER_TABLE;
414 l_org_id_table DBMS_SQL.NUMBER_TABLE;
415 l_conc_req_id_table DBMS_SQL.NUMBER_TABLE;
416 indx NUMBER(10) := 1;
417
418 l_program_name VARCHAR2(99) := 'INITIALIZE_ACCESS_ITEMS';
419
420 BEGIN
421
422
423 -----------------------------------------------------------------------
424 -- Fix for Bug# 3603328.
425 -- Deleting the entire table, is avoided.
426 -- Now seeding Item rows striped with Concurrent Request ID.
427 -- Rows will be deleted, per Conc Req ID, at the end of processing.
428 -----------------------------------------------------------------------
429 -- DELETE EGO_LOGIN_ITEMS_TEMP;
430
431 EGO_DATA_SECURITY.get_security_predicate(
432 p_api_version => 1.0,
433 p_function => 'EGO_ADD_ITEM_PEOPLE',
434 p_object_name => G_FND_OBJECT_NAME,
435 p_user_name => 'HZ_PARTY:'||TO_CHAR(p_login_person_id),
436 p_statement_type => 'EXISTS',
437 p_pk1_alias => 'OUT_MSIB.INVENTORY_ITEM_ID',
438 p_pk2_alias => 'OUT_MSIB.ORGANIZATION_ID',
439 p_pk3_alias => NULL,
440 p_pk4_alias => NULL,
441 p_pk5_alias => NULL,
442 x_predicate => l_sec_predicate,
443 x_return_status => l_return_status );
444
445 --Check for Full access to items, and RETURN if TRUE;
446 IF ((l_sec_predicate IS NULL) OR (l_sec_predicate = '')) THEN
447 G_FULL_ACCESS_ITEMS := TRUE ;
448 x_retcode := RETCODE_SUCCESS;
449 --If there user has Full access to items, then there is no need
450 --to populate the temp table. Hence return;
451 RETURN;
452 END IF;
453
454 ----------------------------------------------------------------------------------
455 -- NOTE: Aliasing of the following Table needs to be done to OUT_MSIB as the
456 -- Security predicate also has references to MTL_SYSTEM_ITEMS, and we need to
457 -- to differentiate it in the eventual SQL generated.
458 ----------------------------------------------------------------------------------
459 l_select_sql :=
460 ' SELECT OUT_MSIB.INVENTORY_ITEM_ID, OUT_MSIB.ORGANIZATION_ID, ' || G_REQUEST_ID ||
461 ' FROM MTL_SYSTEM_ITEMS OUT_MSIB ';
462
463 l_select_sql := l_select_sql || 'WHERE ' || l_sec_predicate ;
464
465 Write_Debug('Access Items SQL => '||l_select_sql);
466
467 l_insert_sql := 'INSERT INTO EGO_LOGIN_ITEMS_TEMP (INVENTORY_ITEM_ID, ORGANIZATION_ID, CONC_REQUEST_ID) ';
468 l_insert_sql := l_insert_sql || ' VALUES (:l_item_id_table, :l_org_id_table, :l_conc_req_id_table) ';
469
470 cursor_select := DBMS_SQL.OPEN_CURSOR;
471 cursor_insert := DBMS_SQL.OPEN_CURSOR;
472 DBMS_SQL.PARSE(cursor_select,l_select_sql,DBMS_SQL.NATIVE);
473 DBMS_SQL.PARSE(cursor_insert,l_insert_sql,DBMS_SQL.NATIVE);
474
475 DBMS_SQL.DEFINE_ARRAY(cursor_select, 1,l_item_id_table,2500, indx);
476 DBMS_SQL.DEFINE_ARRAY(cursor_select, 2,l_org_id_table,2500, indx);
477 DBMS_SQL.DEFINE_ARRAY(cursor_select, 3,l_conc_req_id_table,2500, indx);
478
479 Write_Debug('Select Access Items execute...');
480 cursor_execute := DBMS_SQL.EXECUTE(cursor_select);
481
482 LOOP
483 l_count := DBMS_SQL.FETCH_ROWS(cursor_select);
484 DBMS_SQL.COLUMN_VALUE(cursor_select, 1, l_item_id_table);
485 DBMS_SQL.COLUMN_VALUE(cursor_select, 2, l_org_id_table);
486 DBMS_SQL.COLUMN_VALUE(cursor_select, 3, l_conc_req_id_table);
487
488 DBMS_SQL.BIND_ARRAY(cursor_insert,':l_item_id_table',l_item_id_table);
489 DBMS_SQL.BIND_ARRAY(cursor_insert,':l_org_id_table',l_org_id_table);
490 DBMS_SQL.BIND_ARRAY(cursor_insert,':l_conc_req_id_table',l_conc_req_id_table);
491
492 Write_Debug('Inserting ''Access Items'' into table');
493 cursor_execute := DBMS_SQL.EXECUTE(cursor_insert);
494 l_item_id_table.DELETE;
495 l_org_id_table.DELETE;
496 l_conc_req_id_table.DELETE;
497
498 --For the final batch of records, either it will be 0 or < 2500
499 EXIT WHEN l_count <> 2500;
500 END LOOP;
501
502 DBMS_SQL.CLOSE_CURSOR(cursor_select);
503 DBMS_SQL.CLOSE_CURSOR(cursor_insert);
504
505 Write_Debug('Cursors Insert Access Items and Select Access Items closed...');
506 EXCEPTION
507 WHEN OTHERS THEN
508 Write_Debug(' EXCEPTION in Initialize_Access_items');
509 x_retcode := RETCODE_ERROR;
510 IF DBMS_SQL.IS_OPEN(cursor_select) THEN
511 DBMS_SQL.CLOSE_CURSOR(cursor_select);
512 END IF;
513 IF DBMS_SQL.IS_OPEN(cursor_insert) THEN
514 DBMS_SQL.CLOSE_CURSOR(cursor_insert);
515 END IF;
516 RAISE;
517 END Initialize_Access_Items;
518
519 ----------------------------------------------------------
520 -- Delete lines from the interface and error table --
521 ----------------------------------------------------------
522 PROCEDURE purge_lines
523 ( p_data_set_id IN NUMBER,
524 p_closed_date IN DATE,
525 p_delete_line_type IN NUMBER,
526 x_retcode OUT NOCOPY VARCHAR2,
527 x_errbuff OUT NOCOPY VARCHAR2
528 ) IS
529 -- Start OF comments
530 -- API name : Clean Interface Lines
531 -- TYPE : Public (called by Concurrent Program)
532 -- Pre-reqs : None
533 -- FUNCTION : Removes all the interface lines
534 --
535 l_program_name CONSTANT VARCHAR2(30) := 'PURGE_LINES';
536 BEGIN
537
538
539 -------------------------------------------------------------------------------
540 -- Validate the given parameters.
541 -- Perform the DELETE operation accordingly.
542 -------------------------------------------------------------------------------
543 IF (p_data_set_id IS NULL AND p_closed_date IS NULL)
544 OR NVL(p_delete_line_type,EGO_ITEM_PUB.G_INTF_DELETE_NONE) NOT IN
545 (EGO_ITEM_PUB.G_INTF_DELETE_ALL
546 ,EGO_ITEM_PUB.G_INTF_DELETE_ERROR
547 ,EGO_ITEM_PUB.G_INTF_DELETE_SUCCESS
548 ,EGO_ITEM_PUB.G_INTF_DELETE_NONE
549 ) THEN
550 -- invalid parameters
551 x_retcode := RETCODE_ERROR;
552 fnd_message.set_name('EGO','EGO_IPI_INSUFFICIENT_PARAMS');
553 fnd_message.set_token('PROG_NAME',l_program_name);
554 x_errbuff := fnd_message.get();
555 Conc_Output (p_msg => x_errbuff);
556 ELSE
557 IF p_delete_line_type = EGO_ITEM_PUB.G_INTF_DELETE_ALL THEN
558 --
559 -- delete all lines
560 --
561 DELETE mtl_interface_errors
562 WHERE table_name = G_ERROR_TABLE_NAME
563 AND transaction_id IN
564 (SELECT transaction_id
565 FROM ego_item_people_intf
566 WHERE data_set_id = NVL(p_data_set_id, data_set_id)
567 AND creation_date <= NVL(p_closed_date, creation_date)
568 );
569 DELETE ego_item_people_intf
570 WHERE data_set_id = NVL(p_data_set_id, data_set_id)
571 AND creation_date <= NVL(p_closed_date, creation_date);
572
573 ELSIF p_delete_line_type = EGO_ITEM_PUB.G_INTF_DELETE_ERROR THEN
574 --
575 -- delete all error lines
576 --
577 DELETE mtl_interface_errors
578 WHERE table_name = G_ERROR_TABLE_NAME
579 AND transaction_id IN
580 (SELECT transaction_id
581 FROM ego_item_people_intf
582 WHERE data_set_id = NVL(p_data_set_id, data_set_id)
583 AND creation_date <= NVL(p_closed_date, creation_date)
584 );
585 DELETE ego_item_people_intf
586 WHERE data_set_id = NVL(p_data_set_id, data_set_id)
587 AND creation_date <= NVL(p_closed_date, creation_date)
588 AND process_status = G_PS_ERROR;
589
590 ELSIF p_delete_line_type = EGO_ITEM_PUB.G_INTF_DELETE_SUCCESS THEN
591 --
592 -- delete all success lines
593 --
594 DELETE ego_item_people_intf
595 WHERE data_set_id = NVL(p_data_set_id, data_set_id)
596 AND creation_date <= NVL(p_closed_date, creation_date)
597 AND process_status = G_PS_SUCCESS;
598 END IF;
599 IF p_delete_line_type IN
600 (EGO_ITEM_PUB.G_INTF_DELETE_ALL
601 ,EGO_ITEM_PUB.G_INTF_DELETE_ERROR
602 ,EGO_ITEM_PUB.G_INTF_DELETE_SUCCESS
603 ) THEN
604 COMMIT WORK;
605 END IF;
606 x_retcode := RETCODE_SUCCESS;
607 END IF;
608
609 EXCEPTION
610 WHEN OTHERS THEN
611 x_retcode := RETCODE_ERROR;
612 fnd_message.set_name('EGO','EGO_IPI_EXCEPTION');
613 fnd_message.set_token('PROG_NAME',l_program_name);
614 x_errbuff := fnd_message.get();
615 Conc_Output (p_msg => x_errbuff);
616 Write_Debug (x_errbuff);
617 -- ROLLBACK;
618 RAISE;
619 END purge_lines;
620
621
622 ----------------------------------------------------------
623 -- --
624 ----------------------------------------------------------
625 PROCEDURE validate_update_grant
626 (p_transaction_type IN VARCHAR2
627 ,p_transaction_id IN NUMBER
628 ,p_inventory_item_id IN NUMBER
629 ,p_organization_id IN NUMBER
630 ,p_internal_role_id IN NUMBER
631 ,p_user_party_id_char IN VARCHAR2
632 ,p_group_party_id_char IN VARCHAR2
633 ,p_company_party_id_char IN VARCHAR2
634 ,p_global_party_id_char IN VARCHAR2
635 ,p_start_date IN DATE
636 ,p_end_date IN DATE
637 ,x_return_status OUT NOCOPY NUMBER) IS
638 -- Start OF comments
639 -- API name : validate_update_grant
640 -- TYPE : PRIVATE
641 -- Pre-reqs : NONE
642 --
643 -- FUNCTION : To check if the required grant can be updated
644 -- and updates fnd_grants if required
645 -- NO ACTION IS PERFORMED ON ego_item_people_intf
646 --
647 -- Parameters:
648 -- IN : NONE
649 --
650 -- OUT : x_return_status NUMBER
651 -- Indicates the status of the record
652 -- -1 Record not found for update
653 -- -2 Record found for update but will cause overlap
654 -- 1 Record found and updated
655 --
656
657 CURSOR c_get_update_grantid
658 (cp_inv_item_id IN NUMBER
659 ,cp_organization_id IN NUMBER
660 ,cp_menu_id IN NUMBER
661 ,cp_object_id IN NUMBER
662 ,cp_user_party_id_char IN VARCHAR2
663 ,cp_group_party_id_char IN VARCHAR2
664 ,cp_company_party_id_char IN VARCHAR2
665 ,cp_global_party_id_char IN VARCHAR2
666 ,cp_start_date IN DATE
667 ) IS
668 SELECT grant_guid
669 FROM fnd_grants grants
670 WHERE grants.object_id = G_FND_OBJECT_ID
671 AND grants.menu_id = cp_menu_id
672 AND grants.instance_type = 'INSTANCE'
673 AND grants.instance_pk1_value = TO_CHAR(cp_inv_item_id)
674 AND grants.instance_pk2_value = TO_CHAR(cp_organization_id)
675 AND ((grants.grantee_type = 'USER' AND grants.grantee_key = cp_user_party_id_char ) OR
676 (grants.grantee_type = 'GROUP' AND grants.grantee_key = cp_group_party_id_char) OR
677 (grants.grantee_type = 'COMPANY' AND grants.grantee_key = cp_company_party_id_char) OR
678 (grants.grantee_type = 'GLOBAL' AND grants.grantee_key = cp_global_party_id_char)
679 )
680 AND start_date = cp_start_date;
681
682 CURSOR c_get_valid_update
683 (cp_grant_guid IN RAW
684 ,cp_inv_item_id IN NUMBER
685 ,cp_organization_id IN NUMBER
686 ,cp_menu_id IN NUMBER
687 ,cp_object_id IN NUMBER
688 ,cp_user_party_id_char IN VARCHAR2
689 ,cp_group_party_id_char IN VARCHAR2
690 ,cp_company_party_id_char IN VARCHAR2
691 ,cp_global_party_id_char IN VARCHAR2
692 ,cp_start_date IN DATE
693 ,cp_end_date IN DATE
694 ) IS
695 SELECT grant_guid
696 FROM fnd_grants grants
697 WHERE grants.grant_guid <> cp_grant_guid
698 AND grants.object_id = cp_object_id
699 AND grants.menu_id = cp_menu_id
700 AND grants.instance_type = 'INSTANCE'
701 AND grants.instance_pk1_value = TO_CHAR(cp_inv_item_id)
702 AND grants.instance_pk2_value = TO_CHAR(cp_organization_id)
703 AND ((grants.grantee_type = 'USER' AND grants.grantee_key = cp_user_party_id_char ) OR
704 (grants.grantee_type = 'GROUP' AND grants.grantee_key = cp_group_party_id_char) OR
705 (grants.grantee_type = 'COMPANY' AND grants.grantee_key = cp_company_party_id_char) OR
706 (grants.grantee_type = 'GLOBAL' AND grants.grantee_key = cp_global_party_id_char)
707 )
708 AND start_date <= NVL(cp_end_date, start_date)
709 AND NVL(end_date,cp_start_date) >= cp_start_date;
710
711 l_token_tbl_two Error_Handler.Token_Tbl_Type;
712 l_token_tbl_one Error_Handler.Token_Tbl_Type;
713 l_grant_guid fnd_grants.grant_guid%TYPE;
714 l_temp_grant_guid fnd_grants.grant_guid%TYPE;
715
716 l_success VARCHAR2(999);
717 l_item_number VARCHAR2(100);
718
719 BEGIN
720 OPEN c_get_update_grantid
721 (cp_inv_item_id => p_inventory_item_id
722 ,cp_organization_id => p_organization_id
723 ,cp_menu_id => p_internal_role_id
724 ,cp_object_id => G_FND_OBJECT_ID
725 ,cp_user_party_id_char => p_user_party_id_char
726 ,cp_group_party_id_char => p_group_party_id_char
727 ,cp_company_party_id_char => p_company_party_id_char
728 ,cp_global_party_id_char => p_global_party_id_char
729 ,cp_start_date => p_start_date
730 );
731 FETCH c_get_update_grantid INTO l_grant_guid;
732 IF c_get_update_grantid%FOUND THEN
733 --
734 -- there will be only one record with a given start date
735 -- check if the update will cause any overlaps
736 --
737 OPEN c_get_valid_update
738 (cp_grant_guid => l_grant_guid
739 ,cp_inv_item_id => p_inventory_item_id
740 ,cp_organization_id => p_organization_id
741 ,cp_menu_id => p_internal_role_id
742 ,cp_object_id => G_FND_OBJECT_ID
743 ,cp_user_party_id_char => p_user_party_id_char
744 ,cp_group_party_id_char => p_group_party_id_char
745 ,cp_company_party_id_char => p_company_party_id_char
746 ,cp_global_party_id_char => p_global_party_id_char
747 ,cp_start_date => p_start_date
748 ,cp_end_date => p_end_date
749 );
750 FETCH c_get_valid_update INTO l_temp_grant_guid;
751 IF c_get_valid_update%FOUND THEN
752 --
753 -- overlap will occur after update
754 --
755 x_return_status := G_UPDATE_OVERLAP_ERROR;
756
757 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
758 l_token_tbl_two(1).token_name := 'START_DATE';
759 l_token_tbl_two(1).token_value := p_start_date;
760 l_token_tbl_two(2).token_name := 'END_DATE';
761 l_token_tbl_two(2).token_value := p_end_date;
762 error_handler.Add_Error_Message
763 ( p_message_name => 'EGO_IPI_OVERLAP_GRANT'
764 , p_application_id => 'EGO'
765 , p_message_text => NULL
766 , p_token_tbl => l_token_tbl_two
767 , p_message_type => 'E'
768 , p_row_identifier => p_transaction_id
769 , p_table_name => G_ERROR_TABLE_NAME
770 , p_entity_id => NULL
771 , p_entity_index => NULL
772 , p_entity_code => G_ERROR_ENTITY_CODE
773 );
774 END IF;
775 ELSE
776 -- update the grants
777 FND_GRANTS_PKG.Update_Grant
778 (p_api_version => 1.0
779 ,p_grant_guid => l_grant_guid
780 ,p_start_date => p_start_date
781 ,p_end_date => p_end_date
782 ,x_success => l_success
783 );
784 x_return_status := G_UPDATE_REC_DONE;
785 END IF; -- c_get_valid_update
786 CLOSE c_get_valid_update;
787 ELSE
788 -- no records found for validation
789 x_return_status := G_UPDATE_REC_NOT_FOUND;
790 IF p_transaction_type = 'UPDATE' THEN
791 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
792 l_token_tbl_one(1).token_name := 'ITEM';
793 -- query the item number
794 SELECT CONCATENATED_SEGMENTS
795 INTO l_item_number
796 FROM MTL_SYSTEM_ITEMS_KFV
797 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
798 AND ORGANIZATION_ID = p_organization_id;
799 l_token_tbl_one(1).token_value := l_item_number;
800 error_handler.Add_Error_Message
801 ( p_message_name => 'EGO_IPI_GRANT_NOT_FOUND'
802 , p_application_id => 'EGO'
803 , p_message_text => NULL
804 , p_token_tbl => l_token_tbl_one
805 , p_message_type => 'E'
806 , p_row_identifier => p_transaction_id
807 , p_table_name => G_ERROR_TABLE_NAME
808 , p_entity_id => NULL
809 , p_entity_index => NULL
810 , p_entity_code => G_ERROR_ENTITY_CODE
811 );
812 END IF;
813 END IF; -- p_transaction_type UPDATE
814 END IF; -- c_get_update_grantid
815 CLOSE c_get_update_grantid;
816
817 EXCEPTION
818 WHEN OTHERS THEN
819 Write_Debug(' EXCEPTION in validate_update_grant ');
820 IF c_get_update_grantid%ISOPEN THEN
821 CLOSE c_get_update_grantid;
822 END IF;
823 IF c_get_valid_update%ISOPEN THEN
824 CLOSE c_get_valid_update;
825 END IF;
826 RAISE;
827 END validate_update_grant;
828
829
830 ----------------------------------------------------------
831 -- --
832 ----------------------------------------------------------
833 PROCEDURE validate_insert_grant
834 (p_transaction_type IN VARCHAR2
835 ,p_transaction_id IN NUMBER
836 ,p_inventory_item_id IN NUMBER
837 ,p_organization_id IN NUMBER
838 ,p_internal_role_id IN NUMBER
839 ,p_internal_role_name IN VARCHAR2
840 ,p_grantee_type IN VARCHAR2
841 ,p_grantee_key IN VARCHAR2
842 ,p_user_party_id_char IN VARCHAR2
843 ,p_group_party_id_char IN VARCHAR2
844 ,p_company_party_id_char IN VARCHAR2
845 ,p_global_party_id_char IN VARCHAR2
846 ,p_start_date IN DATE
847 ,p_end_date IN DATE
848 ,x_return_status OUT NOCOPY NUMBER) IS
849 -- Start OF comments
850 -- API name : validate_insert_grant
851 -- TYPE : PRIVATE
852 -- Pre-reqs : NONE
853 --
854 -- FUNCTION : To check if the required grant is valid for insert
855 -- and inserts the record into fnd_grants if valid
856 -- NO ACTION IS PERFORMED ON ego_item_people_intf
857 --
858 -- Parameters:
859 -- IN : NONE
860 --
861 -- OUT : x_return_status NUMBER
862 -- Indicates the status of the record
863 -- -1 Record not found for update
864 -- -2 Record found for update but will cause overlap
865 -- 1 Record found and updated
866 --
867
868 CURSOR c_get_overlap_grantid
869 (cp_inv_item_id IN NUMBER
870 ,cp_organization_id IN NUMBER
871 ,cp_menu_id IN NUMBER
872 ,cp_object_id IN NUMBER
873 ,cp_user_party_id_char IN VARCHAR2
874 ,cp_group_party_id_char IN VARCHAR2
875 ,cp_company_party_id_char IN VARCHAR2
876 ,cp_global_party_id_char IN VARCHAR2
877 ,cp_start_date IN DATE
878 ,cp_end_date IN DATE
879 ) IS
880 SELECT grant_guid
881 FROM fnd_grants grants
882 WHERE grants.object_id = cp_object_id
883 AND grants.menu_id = cp_menu_id
884 AND grants.instance_type = 'INSTANCE'
885 AND grants.instance_pk1_value = TO_CHAR(cp_inv_item_id)
886 AND grants.instance_pk2_value = TO_CHAR(cp_organization_id)
887 AND ((grants.grantee_type = 'USER' AND grants.grantee_key = cp_user_party_id_char ) OR
888 (grants.grantee_type = 'GROUP' AND grants.grantee_key = cp_group_party_id_char) OR
889 (grants.grantee_type = 'COMPANY' AND grants.grantee_key = cp_company_party_id_char) OR
890 (grants.grantee_type = 'GLOBAL' AND grants.grantee_key = cp_global_party_id_char)
891 )
892 AND start_date <= NVL(cp_end_date, start_date)
893 AND NVL(end_date,cp_start_date) >= cp_start_date;
894
895
896 l_token_tbl_two Error_Handler.Token_Tbl_Type;
897 l_grant_guid fnd_grants.grant_guid%TYPE;
898 l_temp_grant_guid fnd_grants.grant_guid%TYPE;
899
900 l_success VARCHAR2(999);
901 l_errorcode NUMBER;
902
903 BEGIN
904 OPEN c_get_overlap_grantid
905 (cp_inv_item_id => p_inventory_item_id
906 ,cp_organization_id => p_organization_id
907 ,cp_menu_id => p_internal_role_id
908 ,cp_object_id => G_FND_OBJECT_ID
909 ,cp_user_party_id_char => p_user_party_id_char
910 ,cp_group_party_id_char => p_group_party_id_char
911 ,cp_company_party_id_char => p_company_party_id_char
912 ,cp_global_party_id_char => p_global_party_id_char
913 ,cp_start_date => p_start_date
914 ,cp_end_date => p_end_date);
915
916 FETCH c_get_overlap_grantid INTO l_grant_guid;
917 IF c_get_overlap_grantid%FOUND THEN
918 -- overlap will occur with the current data
919 x_return_status := G_INSERT_OVERLAP_ERROR;
920 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
921 l_token_tbl_two(1).token_name := 'START_DATE';
922 l_token_tbl_two(1).token_value := p_start_date;
923 l_token_tbl_two(2).token_name := 'END_DATE';
924 l_token_tbl_two(2).token_value := p_end_date;
925 error_handler.Add_Error_Message
926 ( p_message_name => 'EGO_IPI_OVERLAP_GRANT'
927 , p_application_id => 'EGO'
928 , p_message_text => NULL
929 , p_token_tbl => l_token_tbl_two
930 , p_message_type => 'E'
931 , p_row_identifier => p_transaction_id
932 , p_table_name => G_ERROR_TABLE_NAME
933 , p_entity_id => NULL
934 , p_entity_index => NULL
935 , p_entity_code => G_ERROR_ENTITY_CODE
936 );
937 END IF;
938 ELSE
939 --
940 -- insert record into fnd_grants
941 --
942 FND_GRANTS_PKG.Grant_Function
943 (p_api_version => 1.0
944 ,p_menu_name => p_internal_role_name
945 ,p_object_name => G_FND_OBJECT_NAME
946 ,p_instance_type => 'INSTANCE'
947 ,p_instance_set_id => NULL
948 ,p_instance_pk1_value => TO_CHAR(p_inventory_item_id)
949 ,p_instance_pk2_value => TO_CHAR(p_organization_id)
950 ,p_instance_pk3_value => NULL
951 ,p_instance_pk4_value => NULL
952 ,p_instance_pk5_value => NULL
953 ,p_grantee_type => p_grantee_type
954 ,p_grantee_key => p_grantee_key
955 ,p_start_date => p_start_date
956 ,p_end_date => p_end_date
957 ,p_program_name => G_PACKAGE_NAME
958 ,p_program_tag => NULL
959 ,p_parameter1 => NULL
960 ,p_parameter2 => NULL
961 ,p_parameter3 => NULL
962 ,p_parameter4 => NULL
963 ,p_parameter5 => NULL
964 ,p_parameter6 => NULL
965 ,p_parameter7 => NULL
966 ,p_parameter8 => NULL
967 ,p_parameter9 => NULL
968 ,p_parameter10 => NULL
969 ,p_ctx_secgrp_id => -1
970 ,p_ctx_resp_id => -1
971 ,p_ctx_resp_appl_id => -1
972 ,p_ctx_org_id => -1
973 ,x_grant_guid => l_temp_grant_guid
974 ,x_success => l_success
975 ,x_errorcode => l_errorcode
976 );
977 x_return_status := G_INSERT_REC_DONE;
978 END IF; -- c_get_overlap_grantid
979 CLOSE c_get_overlap_grantid;
980
981 EXCEPTION
982 WHEN OTHERS THEN
983 Write_Debug(' EXCEPTION in validate_insert_grant ');
984 IF c_get_overlap_grantid%ISOPEN THEN
985 CLOSE c_get_overlap_grantid;
986 END IF;
987 RAISE;
988 END validate_insert_grant;
989
990
991 ----------------------------------------------------------
992 -- --
993 ----------------------------------------------------------
994 PROCEDURE Validate_No_Grant_Overlap ( x_retcode OUT NOCOPY VARCHAR2) IS
995 -- Start OF comments
996 -- API name : Validate No Grant Overlap
997 -- TYPE : Private (called by load_interface_lines)
998 -- Pre-reqs : Data validated for all possible scenarios (but for grants)
999 -- FUNCTION : Validate grant overlap.
1000 -- Take all records to be deleted and process them
1001 -- Take all the records to be updated and update grants
1002 -- Finally insert new grants
1003 --
1004
1005 CURSOR c_get_ipi_records IS
1006 SELECT item_number, inventory_item_id, organization_id, grantee_party_id, grantee_type,
1007 start_date, end_date, transaction_id, internal_role_id, transaction_type,
1008 internal_role_name,
1009 DECODE(grantee_type, 'USER', 'HZ_PARTY:'||TO_CHAR(grantee_party_id),
1010 'GROUP','HZ_GROUP:'||TO_CHAR(grantee_party_id),
1011 'COMPANY','HZ_COMPANY:'||TO_CHAR(grantee_party_id),
1012 -- bug: 3460466
1013 -- All Users is now represented by grantee_key = 'GLOBAL' in fnd_grants
1014 -- 'GLOBAL','HZ_GLOBAL:'||TO_CHAR(grantee_party_id),
1015 'GLOBAL',grantee_type,
1016 TO_CHAR(grantee_party_id)) grantee_key,
1017 DECODE(transaction_type, 'CREATE', ORDER_BY_CREATE,
1018 'UPDATE', ORDER_BY_UPDATE,
1019 'SYNC', ORDER_BY_SYNC,
1020 'DELETE', ORDER_BY_DELETE,
1021 ORDER_BY_OTHERS) trans_type
1022 FROM ego_item_people_intf
1023 WHERE data_set_id = G_DATA_SET_ID
1024 AND process_status = G_PS_IN_PROCESS
1025 ORDER BY trans_type, transaction_id
1026 FOR UPDATE OF transaction_id;
1027
1028 CURSOR c_get_delete_grantid
1029 (cp_inv_item_id IN NUMBER
1030 ,cp_organization_id IN NUMBER
1031 ,cp_menu_id IN NUMBER
1032 ,cp_object_id IN NUMBER
1033 ,cp_user_party_id_char IN VARCHAR2
1034 ,cp_group_party_id_char IN VARCHAR2
1035 ,cp_company_party_id_char IN VARCHAR2
1036 ,cp_global_party_id_char IN VARCHAR2
1037 ,cp_start_date IN DATE
1038 ,cp_end_date IN DATE
1039 ) IS
1040 SELECT grant_guid
1041 FROM fnd_grants grants
1042 WHERE grants.object_id = G_FND_OBJECT_ID
1043 AND grants.menu_id = cp_menu_id
1044 AND grants.instance_type = 'INSTANCE'
1045 AND grants.instance_pk1_value = TO_CHAR(cp_inv_item_id)
1046 AND grants.instance_pk2_value = TO_CHAR(cp_organization_id)
1047 AND ((grants.grantee_type = 'USER' AND grants.grantee_key = cp_user_party_id_char ) OR
1048 (grants.grantee_type = 'GROUP' AND grants.grantee_key = cp_group_party_id_char) OR
1049 (grants.grantee_type = 'COMPANY' AND grants.grantee_key = cp_company_party_id_char) OR
1050 (grants.grantee_type = 'GLOBAL' AND grants.grantee_key = cp_global_party_id_char)
1051 )
1052 AND start_date = cp_start_date
1053 AND ((end_date IS NULL AND cp_end_date is NULL) OR (end_date = cp_end_date));
1054
1055 -- 3578536
1056 CURSOR c_count_ipi_lines (cp_data_set_id IN NUMBER) IS
1057 SELECT COUNT(*)
1058 FROM ego_item_people_intf
1059 WHERE data_set_id = cp_data_set_id
1060 AND process_status = G_PS_IN_PROCESS;
1061
1062 l_token_tbl_none Error_Handler.Token_Tbl_Type;
1063 l_token_tbl_one Error_Handler.Token_Tbl_Type;
1064
1065 l_user_party_id_char VARCHAR2(100);
1066 l_group_party_id_char VARCHAR2(100);
1067 l_company_party_id_char VARCHAR2(100);
1068 l_global_party_id_char VARCHAR2(100);
1069
1070 l_grant_guid fnd_grants.grant_guid%TYPE;
1071 l_temp_grant_guid fnd_grants.grant_guid%TYPE;
1072 l_grant_guid_count NUMBER := 0;
1073
1074 l_ipi_lines_count NUMBER := 0;
1075 l_record_count NUMBER := 0;
1076 l_return_status NUMBER;
1077 l_success VARCHAR2(999);
1078
1079 l_program_name VARCHAR2(99) := 'VALIDATE_NO_GRANT_OVERLAP';
1080 l_boolean_delete boolean := TRUE;
1081 l_boolean_create boolean := TRUE;
1082 l_boolean_update boolean := TRUE;
1083 l_boolean_sync boolean := TRUE;
1084
1085 BEGIN
1086
1087 OPEN c_count_ipi_lines(cp_data_set_id => G_DATA_SET_ID);
1088 FETCH c_count_ipi_lines INTO l_ipi_lines_count;
1089 CLOSE c_count_ipi_lines;
1090 IF l_ipi_lines_count = 0 THEN
1091 RETURN;
1092 END IF;
1093 WHILE (l_ipi_lines_count > 0 ) LOOP
1094 FOR cr in c_get_ipi_records LOOP
1095 IF cr.grantee_type = 'USER' THEN
1096 l_user_party_id_char := 'HZ_PARTY:'||TO_CHAR(cr.grantee_party_id);
1097 l_group_party_id_char := NULL;
1098 l_company_party_id_char:= NULL;
1099 l_global_party_id_char := NULL;
1100 ELSIF cr.grantee_type = 'GROUP' THEN
1101 l_user_party_id_char := NULL;
1102 l_group_party_id_char := 'HZ_GROUP:'||TO_CHAR(cr.grantee_party_id);
1103 l_company_party_id_char:= NULL;
1104 l_global_party_id_char := NULL;
1105 ELSIF cr.grantee_type = 'COMPANY' THEN
1106 l_user_party_id_char := NULL;
1107 l_group_party_id_char := NULL;
1108 l_company_party_id_char:= 'HZ_COMPANY:'||TO_CHAR(cr.grantee_party_id);
1109 l_global_party_id_char := NULL;
1110 ELSIF cr.grantee_type = 'GLOBAL' THEN
1111 l_user_party_id_char := NULL;
1112 l_group_party_id_char := NULL;
1113 l_company_party_id_char:= NULL;
1114 -- bug: 3460466
1115 -- All Users is now represented by grantee_key = 'GLOBAL' in fnd_grants
1116 -- l_global_party_id_char := 'HZ_GLOBAL:'||TO_CHAR(cr.grantee_party_id);
1117 l_global_party_id_char := cr.grantee_type;
1118 ELSE
1119 l_user_party_id_char := NULL;
1120 l_group_party_id_char := NULL;
1121 l_company_party_id_char:= NULL;
1122 l_global_party_id_char := NULL;
1123 END IF;
1124 IF cr.transaction_type = 'DELETE' THEN
1125 ----------------------------
1126 -- delete records first --
1127 ----------------------------
1128 OPEN c_get_delete_grantid
1129 (cp_inv_item_id => cr.inventory_item_id
1130 ,cp_organization_id => cr.organization_id
1131 ,cp_menu_id => cr.internal_role_id
1132 ,cp_object_id => G_FND_OBJECT_ID
1133 ,cp_user_party_id_char => l_user_party_id_char
1134 ,cp_group_party_id_char => l_group_party_id_char
1135 ,cp_company_party_id_char => l_company_party_id_char
1136 ,cp_global_party_id_char => l_global_party_id_char
1137 ,cp_start_date => cr.start_date
1138 ,cp_end_date => cr.end_date);
1139 FETCH c_get_delete_grantid INTO l_grant_guid;
1140
1141 IF c_get_delete_grantid%FOUND THEN
1142 FND_GRANTS_PKG.Revoke_Grant
1143 (p_api_version => 1.0
1144 ,p_grant_guid => l_grant_guid
1145 ,x_success => l_success
1146 ,x_errorcode => l_return_status
1147 );
1148 UPDATE ego_item_people_intf
1149 SET process_status = G_PS_SUCCESS
1150 WHERE CURRENT OF c_get_ipi_records;
1151 ELSE
1152 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
1153 l_token_tbl_one(1).token_name := 'ITEM';
1154 l_token_tbl_one(1).token_value := cr.item_number;
1155 error_handler.Add_Error_Message
1156 ( p_message_name => 'EGO_IPI_GRANT_NOT_FOUND'
1157 , p_application_id => 'EGO'
1158 , p_message_text => NULL
1159 , p_token_tbl => l_token_tbl_one
1160 , p_message_type => 'E'
1161 , p_row_identifier => cr.transaction_id
1162 , p_table_name => G_ERROR_TABLE_NAME
1163 , p_entity_id => NULL
1164 , p_entity_index => NULL
1165 , p_entity_code => G_ERROR_ENTITY_CODE
1166 );
1167 END IF;
1168 UPDATE ego_item_people_intf
1169 SET process_status = G_PS_ERROR
1170 WHERE CURRENT OF c_get_ipi_records;
1171 END IF; -- c_get_delete_grantid
1172 CLOSE c_get_delete_grantid;
1173
1174 ELSIF cr.transaction_type = 'UPDATE' THEN
1175 ----------------------------
1176 -- check for update now --
1177 ----------------------------
1178 validate_update_grant
1179 (p_transaction_type => cr.transaction_type
1180 ,p_transaction_id => cr.transaction_id
1181 ,p_inventory_item_id => cr.inventory_item_id
1182 ,p_organization_id => cr.organization_id
1183 ,p_internal_role_id => cr.internal_role_id
1184 ,p_user_party_id_char => l_user_party_id_char
1185 ,p_group_party_id_char => l_group_party_id_char
1186 ,p_company_party_id_char => l_company_party_id_char
1187 ,p_global_party_id_char => l_global_party_id_char
1188 ,p_start_date => cr.start_date
1189 ,p_end_date => cr.end_date
1190 ,x_return_status => l_return_status
1191 );
1192 IF l_return_status = G_UPDATE_REC_DONE THEN
1193 -- record successfully updated
1194 UPDATE ego_item_people_intf
1195 SET process_status = G_PS_SUCCESS
1196 WHERE CURRENT OF c_get_ipi_records;
1197 ELSIF l_return_status = G_UPDATE_REC_NOT_FOUND THEN
1198 -- no record found for overlap
1199 UPDATE ego_item_people_intf
1200 SET process_status = G_PS_ERROR
1201 WHERE CURRENT OF c_get_ipi_records;
1202 ELSIF l_return_status = G_UPDATE_OVERLAP_ERROR THEN
1203 -- overlap will occur if update is done
1204 UPDATE ego_item_people_intf
1205 SET process_status = G_PS_ERROR
1206 WHERE CURRENT OF c_get_ipi_records;
1207 END IF;
1208
1209 ELSIF cr.transaction_type = 'SYNC' THEN
1210 ------------------------------------
1211 -- check for SYNC opetaion --
1212 -- (first UPDATE and then INSERT --
1213 ------------------------------------
1214 validate_update_grant
1215 (p_transaction_type => cr.transaction_type
1216 ,p_transaction_id => cr.transaction_id
1217 ,p_inventory_item_id => cr.inventory_item_id
1218 ,p_organization_id => cr.organization_id
1219 ,p_internal_role_id => cr.internal_role_id
1220 ,p_user_party_id_char => l_user_party_id_char
1221 ,p_group_party_id_char => l_group_party_id_char
1222 ,p_company_party_id_char => l_company_party_id_char
1223 ,p_global_party_id_char => l_global_party_id_char
1224 ,p_start_date => cr.start_date
1225 ,p_end_date => cr.end_date
1226 ,x_return_status => l_return_status
1227 );
1228 IF l_return_status = G_UPDATE_REC_DONE THEN
1229 -- record successfully updated
1230 -- 4669015 setting successful status to 'UPDATE'/'CREATE'
1231 UPDATE ego_item_people_intf
1232 SET process_status = G_PS_SUCCESS,
1233 transaction_type = 'UPDATE'
1234 WHERE CURRENT OF c_get_ipi_records;
1235 ELSIF l_return_status = G_UPDATE_OVERLAP_ERROR THEN
1236 -- overlap will occur if update is done
1237 UPDATE ego_item_people_intf
1238 SET process_status = G_PS_ERROR
1239 WHERE CURRENT OF c_get_ipi_records;
1240 ELSIF l_return_status = G_UPDATE_REC_NOT_FOUND THEN
1241 -- no record found for overlap
1242 -- now insert the record.
1243 validate_insert_grant
1244 (p_transaction_type => cr.transaction_type
1245 ,p_transaction_id => cr.transaction_id
1246 ,p_inventory_item_id => cr.inventory_item_id
1247 ,p_organization_id => cr.organization_id
1248 ,p_internal_role_id => cr.internal_role_id
1249 ,p_internal_role_name => cr.internal_role_name
1250 ,p_grantee_type => cr.grantee_type
1251 ,p_grantee_key => cr.grantee_key
1252 ,p_user_party_id_char => l_user_party_id_char
1253 ,p_group_party_id_char => l_group_party_id_char
1254 ,p_company_party_id_char => l_company_party_id_char
1255 ,p_global_party_id_char => l_global_party_id_char
1256 ,p_start_date => cr.start_date
1257 ,p_end_date => cr.end_date
1258 ,x_return_status => l_return_status
1259 );
1260 IF l_return_status = G_INSERT_REC_DONE THEN
1261 -- record successfully inserted
1262 -- 4669015 setting successful status to 'UPDATE'/'CREATE'
1263 UPDATE ego_item_people_intf
1264 SET process_status = G_PS_SUCCESS,
1265 transaction_type = 'CREATE'
1266 WHERE CURRENT OF c_get_ipi_records;
1267 ELSIF l_return_status = G_INSERT_OVERLAP_ERROR THEN
1268 -- insert overlap error
1269 UPDATE ego_item_people_intf
1270 SET process_status = G_PS_ERROR
1271 WHERE CURRENT OF c_get_ipi_records;
1272 END IF;
1273 END IF;
1274
1275 ELSIF cr.transaction_type = 'CREATE' THEN
1276 ----------------------------
1277 -- check for create now --
1278 ----------------------------
1279 validate_insert_grant
1280 (p_transaction_type => cr.transaction_type
1281 ,p_transaction_id => cr.transaction_id
1282 ,p_inventory_item_id => cr.inventory_item_id
1283 ,p_organization_id => cr.organization_id
1284 ,p_internal_role_id => cr.internal_role_id
1285 ,p_internal_role_name => cr.internal_role_name
1286 ,p_grantee_type => cr.grantee_type
1287 ,p_grantee_key => cr.grantee_key
1288 ,p_user_party_id_char => l_user_party_id_char
1289 ,p_group_party_id_char => l_group_party_id_char
1290 ,p_company_party_id_char => l_company_party_id_char
1291 ,p_global_party_id_char => l_global_party_id_char
1292 ,p_start_date => cr.start_date
1293 ,p_end_date => cr.end_date
1294 ,x_return_status => l_return_status
1295 );
1296 IF l_return_status = G_INSERT_REC_DONE THEN
1297 -- record successfully inserted
1298 UPDATE ego_item_people_intf
1299 SET process_status = G_PS_SUCCESS
1300 WHERE CURRENT OF c_get_ipi_records;
1301 ELSIF l_return_status = G_INSERT_OVERLAP_ERROR THEN
1302 -- insert overlap error
1303 UPDATE ego_item_people_intf
1304 SET process_status = G_PS_ERROR
1305 WHERE CURRENT OF c_get_ipi_records;
1306 END IF;
1307
1308 END IF; -- cr.transaction_type
1309 check_and_write_log(p_msg_size => G_MAX_MESSAGE_SIZE
1310 ,x_retcode => x_retcode);
1311 IF (x_retcode = RETCODE_ERROR) THEN
1312 RETURN;
1313 END IF;
1314 l_ipi_lines_count := l_ipi_lines_count - 1;
1315 l_record_count := l_record_count + 1;
1316 IF (l_record_count = G_BATCH_SIZE OR l_ipi_lines_count = 0) THEN
1317 l_record_count := 0;
1318 EXIT;
1319 -- 3578536
1320 -- COMMIT;
1321 END IF;
1322 END LOOP; -- c_get_ipi_records
1323 --
1324 -- committing the data as one loop is completed
1325 --
1326 COMMIT;
1327 END LOOP; -- l_batch_loop_counter
1328 EXCEPTION
1329 WHEN OTHERS THEN
1330 Write_Debug(' EXCEPTION in validate_No_Grant_Overlap ');
1331 IF c_get_ipi_records%ISOPEN THEN
1332 CLOSE c_get_ipi_records;
1333 END IF;
1334 IF c_count_ipi_lines%ISOPEN THEN
1335 CLOSE c_count_ipi_lines;
1336 END IF;
1337 IF c_get_delete_grantid%ISOPEN THEN
1338 CLOSE c_get_delete_grantid;
1339 END IF;
1340 RAISE;
1341
1342 END Validate_No_Grant_Overlap;
1343
1344 ----------------------------------------------------------
1345 -- To open the Debug Session for writing Debug Log --
1346 ----------------------------------------------------------
1347 PROCEDURE open_debug_session IS
1348
1349 CURSOR c_get_utl_file_dir IS
1350 SELECT VALUE
1351 FROM V$PARAMETER
1352 WHERE NAME = 'utl_file_dir';
1353
1354 --local variables
1355 l_log_output_dir VARCHAR2(200);
1356 l_log_return_status VARCHAR2(99);
1357 l_errbuff VARCHAR2(999);
1358 BEGIN
1359
1360 OPEN c_get_utl_file_dir;
1361 FETCH c_get_utl_file_dir INTO l_log_output_dir;
1362 --Conc_Log('UTL_FILE_DIR : '||l_log_output_dir);
1363 IF c_get_utl_file_dir%FOUND THEN
1364 ------------------------------------------------------
1365 -- Trim to get only the first directory in the list --
1366 ------------------------------------------------------
1367 IF INSTR(l_log_output_dir,',') <> 0 THEN
1368 l_log_output_dir := SUBSTR(l_log_output_dir, 1, INSTR(l_log_output_dir, ',') - 1);
1369 --Conc_Log('Log Output Dir : '||l_log_output_dir);
1370 END IF;
1371
1372
1373 G_ERROR_FILE_NAME := G_ERROR_TABLE_NAME||'_'||to_char(sysdate, 'DDMONYYYY_HH24MISS')||'.err';
1374 --Conc_Log('Trying to open the Error File => '||G_ERROR_FILE_NAME);
1375
1376 Error_Handler.Open_Debug_Session(
1377 p_debug_filename => G_ERROR_FILE_NAME
1378 ,p_output_dir => l_log_output_dir
1379 ,x_return_status => l_log_return_status
1380 ,x_error_mesg => l_errbuff
1381 );
1382
1383 Conc_Log(' Log file location --> '||l_log_output_dir||'/'||G_ERROR_FILE_NAME ||' created with status '|| l_log_return_status);
1384
1385 IF (l_log_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1386 Conc_Log('Unable to open error log file. Error => '||l_errbuff);
1387 END IF;
1388
1389 END IF;--IF c_get_utl_file_dir%FOUND THEN
1390
1391 END open_debug_session;
1392
1393
1394 ---------------------------------------------
1395 -- PUBLIC PROCEDURES AND FUNCTIONS --
1396 ---------------------------------------------
1397
1398 FUNCTION get_curr_dataset_id RETURN NUMBER IS
1399 -- Start OF comments
1400 -- API name : Load Interfance Lines
1401 -- TYPE : Public (called by SQL Loader)
1402 -- Pre-reqs : None
1403 -- FUNCTION : Process and Load interfance lines into FND_GRANTS.
1404 -- Errors are populated in MTL_INTERFACE_ERRORS
1405 BEGIN
1406 IF G_CURR_DATASET_ID = -1 THEN
1407 SELECT EGO_IPI_DATASET_ID_S.NEXTVAL
1408 INTO G_CURR_DATASET_ID
1409 FROM DUAL;
1410 END IF;
1411 RETURN G_CURR_DATASET_ID;
1412 EXCEPTION
1413 WHEN OTHERS THEN
1414 Write_Debug(' EXCEPTION in get_curr_dataset_id ');
1415 G_CURR_DATASET_ID := -2;
1416 RAISE;
1417 END get_curr_dataset_id;
1418
1419 -------------------------------------------------------------------------------
1420 -- Main procedure called by the Item People Import Concurrent Program
1421 -------------------------------------------------------------------------------
1422 PROCEDURE load_interface_lines
1423 (
1424 x_errbuff OUT NOCOPY VARCHAR2,
1425 x_retcode OUT NOCOPY VARCHAR2,
1426 p_data_set_id IN NUMBER,
1427 p_bulk_batch_size IN NUMBER DEFAULT EGO_ITEM_PEOPLE_IMPORT_PKG.RECOMMENDED_BATCH_SIZE,
1428 p_delete_lines IN NUMBER DEFAULT EGO_ITEM_PUB.G_INTF_DELETE_NONE,
1429 p_debug_mode IN NUMBER DEFAULT EGO_ITEM_PEOPLE_IMPORT_PKG.DEBUG_MODE_ERROR,
1430 p_log_mode IN NUMBER DEFAULT EGO_ITEM_PEOPLE_IMPORT_PKG.LOG_INTO_TABLE_ONLY
1431 ) IS
1432
1433 -- Start OF comments
1434 -- API name : Load Interfance Lines
1435 -- TYPE : Public (called by Concurrent Program)
1436 -- Pre-reqs : None
1437 -- FUNCTION : Process and Load interfance lines into FND_GRANTS.
1438 -- Errors are populated in MTL_INTERFACE_ERRORS
1439
1440 -- ======================================================================
1441 -- the record types used from other procedures
1442 -- noted down here for quick reference
1443 -- Error record type
1444 -- ======================================================================
1445 -- TYPE Error_Rec_Type IS RECORD
1446 -- (organization_id NUMBER
1447 -- ,entity_id VARCHAR2(3)
1448 -- ,message_text VARCHAR2(2000)
1449 -- ,entity_index NUMBER
1450 -- ,message_type VARCHAR2(1)
1451 -- ,row_identifier VARCHAR2(80)
1452 -- ,bo_identifier VARCHAR2(3) := 'ECO'
1453 -- );
1454 --
1455 -- TYPE Error_Tbl_Type IS TABLE OF Error_Rec_Type INDEX BY BINARY_INTEGER;
1456 --
1457 -- TYPE Mesg_Token_Rec_Type IS RECORD
1458 -- (message_name VARCHAR2(30) := NULL
1459 -- ,application_id VARCHAR2(3) := NULL
1460 -- ,message_text VARCHAR2(2000) := NULL
1461 -- ,token_name VARCHAR2(30) := NULL
1462 -- ,token_value VARCHAR2(100) := NULL
1463 -- ,translate BOOLEAN := FALSE
1464 -- ,message_type VARCHAR2(1) := NULL
1465 -- );
1466 --
1467 -- TYPE Mesg_Token_Tbl_Type IS TABLE OF Mesg_Token_Rec_Type INDEX BY BINARY_INTEGER;
1468 --
1469 -- TYPE Token_Rec_Type IS RECORD
1470 -- (token_value VARCHAR2(100) := NULL
1471 -- ,token_name VARCHAR2(30) := NULL
1472 -- ,translate BOOLEAN := FALSE
1473 -- );
1474 --
1475 -- TYPE Token_Tbl_Type IS TABLE OF Token_Rec_Type INDEX BY BINARY_INTEGER;
1476 -- ======================================================================
1477 -- 5375467 modified query to validate user against ego_people_v
1478 CURSOR c_user_party_id (cp_user_id IN NUMBER) IS
1479 SELECT person_id, person_name
1480 FROM ego_people_v
1481 WHERE user_id = cp_user_id;
1482
1483 CURSOR c_count_ipi_lines (cp_data_set_id IN NUMBER) IS
1484 SELECT COUNT(*)
1485 FROM ego_item_people_intf
1486 WHERE data_set_id = cp_data_set_id
1487 AND process_status = G_PS_TO_BE_PROCESSED;
1488
1489 CURSOR c_get_trans_id_limits (cp_data_set_id IN NUMBER) IS
1490 SELECT MIN(transaction_id), MAX(transaction_id)
1491 FROM ego_item_people_intf
1492 WHERE data_set_id = cp_data_set_id
1493 AND process_status = G_PS_IN_PROCESS;
1494
1495 CURSOR c_err_mand_params IS
1496 SELECT transaction_id
1497 FROM ego_item_people_intf
1498 WHERE data_set_id = G_DATA_SET_ID
1499 AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1500 AND process_status = G_PS_IN_PROCESS
1501 AND request_id = G_REQUEST_ID
1502 AND ( (inventory_item_id IS NULL AND item_number IS NULL)
1503 OR
1504 (organization_id IS NULL AND organization_code IS NULL)
1505 OR
1506 (internal_role_id IS NULL AND internal_role_name IS NULL AND display_role_name IS NULL)
1507 OR
1508 (grantee_type IS NULL)
1509 )
1510 FOR UPDATE OF transaction_id;
1511
1512 CURSOR c_err_dates IS
1513 SELECT transaction_id
1514 FROM ego_item_people_intf
1515 WHERE data_set_id = G_DATA_SET_ID
1516 AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1517 AND process_status = G_PS_IN_PROCESS
1518 AND request_id = G_REQUEST_ID
1519 AND start_date > NVL(end_date,(start_date + 1))
1520 FOR UPDATE OF transaction_id;
1521
1522 --
1523 -- Select records to flag missing or invalid Transaction_Types
1524 --
1525 CURSOR c_err_transaction_type IS
1526 SELECT transaction_id, transaction_type
1527 FROM ego_item_people_intf
1528 WHERE data_set_id = G_DATA_SET_ID
1529 AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1530 AND process_status = G_PS_IN_PROCESS
1531 AND request_id = G_REQUEST_ID
1532 AND transaction_type NOT IN ('CREATE', 'UPDATE', 'DELETE', 'SYNC')
1533 FOR UPDATE OF transaction_id;
1534
1535 --
1536 -- Select records with missing/invalid grantee type
1537 --
1538 CURSOR c_err_grantee_type IS
1539 SELECT transaction_id, grantee_type
1540 FROM ego_item_people_intf
1541 WHERE data_set_id = G_DATA_SET_ID
1542 AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1543 AND process_status = G_PS_IN_PROCESS
1544 AND request_id = G_REQUEST_ID
1545 AND (grantee_type IS NULL OR grantee_type NOT IN ('USER', 'GROUP', 'COMPANY', 'GLOBAL'))
1546 FOR UPDATE OF transaction_id;
1547
1548 --
1549 -- Select records to flag missing or invalid grantee_party_id
1550 --
1551 CURSOR c_err_grantee_id IS
1552 SELECT transaction_id, grantee_party_id, grantee_name, grantee_type
1553 FROM ego_item_people_intf
1554 WHERE data_set_id = G_DATA_SET_ID
1555 AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1556 AND process_status = G_PS_IN_PROCESS
1557 AND request_id = G_REQUEST_ID
1558 AND grantee_party_id IS NULL
1559 FOR UPDATE OF transaction_id;
1560
1561 --
1562 -- Select records to flag missing or invalid role_id
1563 --
1564 CURSOR c_err_role_id IS
1565 SELECT transaction_id, internal_role_id, display_role_name, internal_role_name
1566 FROM ego_item_people_intf
1567 WHERE data_set_id = G_DATA_SET_ID
1568 AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1569 AND process_status = G_PS_IN_PROCESS
1570 AND request_id = G_REQUEST_ID
1571 AND internal_role_id IS NULL
1572 FOR UPDATE OF transaction_id;
1573
1574 --
1575 -- Select records to flag missing or invalid organization_id
1576 --
1577
1578 -- bug 4628705
1579 -- CURSOR c_err_org_id IS
1580 -- SELECT transaction_id, organization_id, organization_code
1581 -- FROM ego_item_people_intf
1582 -- WHERE data_set_id = G_DATA_SET_ID
1583 -- AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1584 -- AND process_status = G_PS_IN_PROCESS
1585 -- AND organization_id IS NULL
1586 -- FOR UPDATE OF transaction_id;
1587
1588 -- bug 3710151
1589 -- --
1590 -- -- Select records for valid item numbers
1591 -- --
1592 -- CURSOR c_get_item_number IS
1593 -- SELECT transaction_id, inventory_item_id, item_number, organization_id, organization_code
1594 -- FROM ego_item_people_intf
1595 -- WHERE data_set_id = G_DATA_SET_ID
1596 -- AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1597 -- AND process_status = G_PS_IN_PROCESS
1598 -- AND organization_id IS NOT NULL
1599 -- FOR UPDATE OF transaction_id;
1600 --
1601 -- --
1602 -- -- Check whether the user can revoke/give grants
1603 -- --
1604 -- CURSOR c_get_grant_privileges (cp_inventory_item_id IN NUMBER
1605 -- ,cp_organization_id IN NUMBER) IS
1606 -- SELECT inventory_item_id
1607 -- FROM EGO_LOGIN_ITEMS_TEMP
1608 -- WHERE inventory_item_id = cp_inventory_item_id
1609 -- AND organization_id = cp_organization_id
1610 -- AND conc_request_id = G_REQUEST_ID;
1611 --
1612 -- Select records to flag missing or invalid item number
1613 --
1614
1615 -- bug 4628705
1616 -- CURSOR c_err_item_id IS
1617 -- SELECT transaction_id, item_number, inventory_item_id, organization_code
1618 -- FROM ego_item_people_intf
1619 -- WHERE data_set_id = G_DATA_SET_ID
1620 -- AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1621 -- AND process_status = G_INT_ITEM_VAL_ERROR
1622 -- AND process_status = G_PS_IN_PROCESS
1623 -- AND inventory_item_id IS NULL
1624 -- FOR UPDATE OF transaction_id;
1625
1626 -- bug 4628705
1627 CURSOR c_err_records IS
1628 SELECT transaction_id, item_number, inventory_item_id,
1629 organization_code, organization_id
1630 FROM ego_item_people_intf
1631 WHERE data_set_id = G_DATA_SET_ID
1632 AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1633 AND request_id = G_REQUEST_ID
1634 AND process_status IN (G_INT_ORG_VAL_ERROR, G_INT_ITEM_VAL_ERROR);
1635
1636 --
1637 -- Select records to flag where user does not have privilege
1638 --
1639 CURSOR c_err_access_items IS
1640 SELECT transaction_id, item_number, organization_code
1641 FROM ego_item_people_intf
1642 WHERE data_set_id = G_DATA_SET_ID
1643 AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1644 AND process_status = G_PS_ERROR
1645 AND request_id = G_REQUEST_ID
1646 AND inventory_item_id IS NOT NULL
1647 FOR UPDATE OF transaction_id;
1648 -- bug 3710151 ends
1649
1650 --
1651 -- Select the directory where the error log file needs to be saved
1652 --
1653 CURSOR c_get_utl_file_dir IS
1654 SELECT VALUE
1655 FROM V$PARAMETER
1656 WHERE NAME = 'utl_file_dir';
1657
1658 l_token_tbl_none Error_Handler.Token_Tbl_Type;
1659 l_token_tbl_one Error_Handler.Token_Tbl_Type;
1660 l_token_tbl_two Error_Handler.Token_Tbl_Type;
1661 l_token_tbl_three Error_Handler.Token_Tbl_Type;
1662
1663 l_login_party_id hz_parties.party_id%TYPE;
1664 l_login_party_name VARCHAR2(240);
1665 l_ipi_lines_count NUMBER;
1666 l_loop_count NUMBER;
1667 l_transaction_id_min NUMBER;
1668 l_transaction_id_max NUMBER;
1669
1670 l_column_name VARCHAR2(99);
1671 l_transaction_id ego_item_people_intf.transaction_id%TYPE;
1672 l_msg_name VARCHAR2(99);
1673 l_msg_text VARCHAR2(999) := NULL;
1674 l_msg_type VARCHAR2(10) := 'E';
1675 l_sysdate DATE;
1676
1677 l_inventory_item_id NUMBER;
1678
1679 l_log_output_dir VARCHAR2(200);
1680 l_log_return_status VARCHAR2(99);
1681 l_log_mesg_token_tbl ERROR_HANDLER.Mesg_Token_Tbl_Type;
1682
1683 l_program_name VARCHAR2(30) := 'LOAD_INTERFACE_LINES';
1684 l_err_msg_sql VARCHAR2(4000);
1685 --
1686 l_return_status VARCHAR2(10);
1687 l_msg_count NUMBER ;
1688 l_msg_data fnd_new_messages.message_text%TYPE;
1689
1690 BEGIN
1691 G_HAS_ERRORS := FALSE;
1692 x_retcode := RETCODE_SUCCESS;
1693 IF (NVL(fnd_profile.value('CONC_REQUEST_ID'), 0) <> 0) THEN
1694 g_concReq_valid_flag := TRUE;
1695 END IF;
1696
1697 IF p_debug_mode = DEBUG_MODE_DEBUG THEN
1698 G_DEBUG_MODE := DEBUG_MODE_DEBUG;
1699 ELSE
1700 ------------------------------------------------------------
1701 -- Not yet classified, for the INFO level etc., conditions.
1702 ------------------------------------------------------------
1703 G_DEBUG_MODE := DEBUG_MODE_ERROR;
1704 END IF; -- IF p_debug_mode
1705
1706 ERROR_HANDLER.initialize();
1707 ERROR_HANDLER.set_bo_identifier(G_BO_IDENTIFIER);
1708
1709 --Opens Error_Handler debug session, only if Debug session is not already open.
1710 IF (Error_Handler.Get_Debug <> 'Y') THEN
1711 Open_Debug_Session;
1712 END IF;
1713
1714 Write_Debug('Start of the Process');
1715 Write_Debug('Data_set_id ' || to_char(p_data_set_id));
1716 Write_Debug('Bulk batch size '|| to_char(p_bulk_batch_size));
1717 Write_Debug('Delete Lines '|| to_char (p_delete_lines));
1718 Write_Debug('Log Mode ' || to_char (p_log_mode));
1719
1720 IF p_log_mode = LOG_INTO_TABLE_AND_FILE THEN
1721 G_TABLE_LOG := TRUE;
1722 ELSIF p_log_mode = LOG_INTO_TABLE_ONLY THEN
1723 G_TABLE_LOG := TRUE;
1724 ELSIF p_log_mode = LOG_INTO_FILE_ONLY THEN
1725 G_TABLE_LOG := FALSE;
1726 ELSE
1727 G_TABLE_LOG := FALSE;
1728 END IF;
1729
1730 Write_Debug('Debug Mode => '||G_DEBUG_MODE);
1731
1732 -------------------------------------------------------------------------
1733 -- the values are chosen from the FND_GLOBALS
1734 -------------------------------------------------------------------------
1735 G_USER_ID := FND_GLOBAL.user_id ;
1736 G_LOGIN_ID := FND_GLOBAL.login_id ;
1737 G_PROG_APPID := FND_GLOBAL.prog_appl_id ;
1738 G_PROG_ID := FND_GLOBAL.conc_program_id ;
1739 -- bug 3710151
1740 G_REQUEST_ID := NVL(FND_GLOBAL.conc_request_id, -1) ;
1741
1742 Write_Debug('FND_GLOBAL.user_id : '||FND_GLOBAL.user_id);
1743 Write_Debug('FND_GLOBAL.conc_request_id : '||G_REQUEST_ID);
1744
1745 G_DATA_SET_ID := p_data_set_id;
1746 -------------------------------------------------------------------------
1747 -- check whether the logged in user is a valid user
1748 -------------------------------------------------------------------------
1749 OPEN c_user_party_id(cp_user_id => G_USER_ID);
1750 FETCH c_user_party_id INTO l_login_party_id, l_login_party_name;
1751 Write_Debug('Login Party Id : '||l_login_party_id||' AND '||'Login Party Name : '||l_login_party_name);
1752 CLOSE c_user_party_id;
1753
1754 Write_Debug('Counting total lines for curr data_set_id');
1755 OPEN c_count_ipi_lines(cp_data_set_id => G_DATA_SET_ID);
1756 FETCH c_count_ipi_lines INTO l_ipi_lines_count;
1757 CLOSE c_count_ipi_lines;
1758 IF l_ipi_lines_count = 0 THEN
1759 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
1760 error_handler.Add_Error_Message
1761 ( p_message_name => 'EGO_IPI_NO_LINES'
1762 , p_application_id => 'EGO'
1763 , p_message_text => NULL
1764 , p_token_tbl => l_token_tbl_none
1765 , p_message_type => 'E'
1766 , p_row_identifier => NULL
1767 , p_table_name => G_ERROR_TABLE_NAME
1768 , p_entity_id => NULL
1769 , p_entity_index => NULL
1770 , p_entity_code => G_ERROR_ENTITY_CODE
1771 );
1772 END IF;
1773 x_retcode := RETCODE_SUCCESS;
1774 fnd_message.set_name('EGO', 'EGO_IPI_NO_LINES');
1775 l_msg_data := fnd_message.get();
1776 Write_Debug (l_msg_data);
1777 -- conc_output (x_errbuff);
1778 RETURN;
1779 END IF;
1780
1781 Write_Debug('Initalizing table Ego_Login_Items_Temp');
1782 initialize_access_items (p_login_person_id => l_login_party_id
1783 ,x_retcode => x_retcode);
1784 IF x_retcode = RETCODE_ERROR THEN
1785 Write_Debug('Error Initalizing Ego_Login_Items_Temp');
1786 fnd_message.set_name('EGO', 'EGO_IPI_ERR_INIT_ITEMS');
1787 x_errbuff := fnd_message.get();
1788 -- conc_output (x_errbuff);
1789 purge_login_items_table();
1790 RETURN;
1791 END IF;
1792 Write_Debug('Successfully Initalized Ego_Login_Items_Temp');
1793
1794 Write_Debug('Initalizing FND Object Id');
1795 initialize_fnd_object_id(p_object_name => G_FND_OBJECT_NAME);
1796 Write_Debug('Initalizing All Users global var : G_ALL_USERS_PARTY_ID');
1797 initialize_all_users();
1798
1799 -----------------------------------------------------------------------
1800 -- setting up the records for processing
1801 -----------------------------------------------------------------------
1802 Write_Debug('Setting the Start Date to SysDate if NULL.');
1803 l_sysdate := SYSDATE;
1804 UPDATE ego_item_people_intf
1805 SET creation_date = NVL(creation_date,l_sysdate),
1806 last_update_date = l_sysdate,
1807 last_updated_by = G_USER_ID,
1808 last_update_login = G_LOGIN_ID,
1809 request_id = G_REQUEST_ID,
1810 program_application_id = G_PROG_APPID,
1811 program_id = G_PROG_ID,
1812 program_update_date = l_sysdate,
1813 start_date = NVL(start_date, l_sysdate),
1814 transaction_type = UPPER(transaction_type),
1815 grantee_type = UPPER(grantee_type),
1816 process_status = G_PS_IN_PROCESS,
1817 transaction_id = NVL(transaction_id, EGO_IPI_TRANSACTION_ID_S.NEXTVAL)
1818 WHERE data_set_id = G_DATA_SET_ID
1819 AND process_status = G_PS_TO_BE_PROCESSED;
1820
1821 -------------------------------------------
1822 -- All required values are initialized
1823 -- Go ahead with validating the records
1824 -------------------------------------------
1825
1826 Write_Debug('Getting Min and Max Transaction Ids');
1827
1828 -------------------------------------------------------------------------
1829 -- initialize the loop counter values
1830 -------------------------------------------------------------------------
1831 OPEN c_get_trans_id_limits (cp_data_set_id => G_DATA_SET_ID);
1832 FETCH c_get_trans_id_limits INTO l_transaction_id_min, l_transaction_id_max;
1833 CLOSE c_get_trans_id_limits;
1834 G_BATCH_SIZE := NVL(p_bulk_batch_size, G_BATCH_SIZE);
1835 l_loop_count := CEIL( (l_transaction_id_max - l_transaction_id_min + 1) / G_BATCH_SIZE );
1836 G_FROM_LINE_NUMBER := l_transaction_id_min;
1837
1838 ---------------------------
1839 -- all variables set
1840 -- start the loop now
1841 ---------------------------
1842 Write_Debug ('Processing lines from Intf table according to batch size for '||l_loop_count||' times');
1843 FOR l_batch_loop_counter IN 1..l_loop_count LOOP
1844 Write_Debug (' Loop execution '|| to_char (l_batch_loop_counter) || ' of ' || to_char(l_loop_count));
1845 IF (l_transaction_id_max > (G_FROM_LINE_NUMBER + G_BATCH_SIZE -1)) THEN
1846 G_TO_LINE_NUMBER := G_FROM_LINE_NUMBER + G_BATCH_SIZE - 1;
1847 ELSE
1848 G_TO_LINE_NUMBER := l_transaction_id_max;
1849 END IF;
1850 Write_Debug (' Loop execution from '|| G_FROM_LINE_NUMBER || ' to ' || G_TO_LINE_NUMBER);
1851 -------------------------------------------------------------------------
1852 -- call various validation routines
1853 -- the sequence of the calling valiadations does matter
1854 -- as the first error is reported and the record is flagged as error
1855 -------------------------------------------------------------------------
1856
1857 Write_Debug('Checking for Invalid records and flagging Error');
1858 -------------------------------------------------------------------------
1859 -- check for mandatory data to be present before flagging error
1860 -------------------------------------------------------------------------
1861 FOR cr IN c_err_mand_params LOOP
1862 UPDATE ego_item_people_intf
1863 SET process_status = G_PS_ERROR
1864 WHERE CURRENT OF c_err_mand_params;
1865 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
1866 l_msg_name := 'EGO_INTF_MAND_PARAM_MISSING';
1867 error_handler.Add_Error_Message
1868 ( p_message_name => l_msg_name
1869 , p_application_id => 'EGO'
1870 , p_message_text => NULL
1871 , p_token_tbl => l_token_tbl_none
1872 , p_message_type => 'E'
1873 , p_row_identifier => cr.transaction_id
1874 , p_table_name => G_ERROR_TABLE_NAME
1875 , p_entity_id => NULL
1876 , p_entity_index => NULL
1877 , p_entity_code => G_ERROR_ENTITY_CODE
1878 );
1879 END IF;
1880 check_and_write_log(p_msg_size => G_MAX_MESSAGE_SIZE
1881 ,x_retcode => x_retcode);
1882 IF x_retcode = RETCODE_ERROR THEN
1883 RETURN;
1884 END IF;
1885 END LOOP; -- error mandatory data in record
1886
1887 Write_Debug('Checking for StartDate > EndDate and flagging Error');
1888 -------------------------------------------------------------------------
1889 -- check the correct start and end dates in the records
1890 -------------------------------------------------------------------------
1891 FOR cr IN c_err_dates LOOP
1892 UPDATE ego_item_people_intf
1893 SET process_status = G_PS_ERROR
1894 WHERE CURRENT OF c_err_dates;
1895 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
1896 l_msg_name := 'EGO_IPI_INVALID_DATES';
1897 error_handler.Add_Error_Message
1898 ( p_message_name => l_msg_name
1899 , p_application_id => 'EGO'
1900 , p_message_text => NULL
1901 , p_token_tbl => l_token_tbl_none
1902 , p_message_type => 'E'
1903 , p_row_identifier => cr.transaction_id
1904 , p_table_name => G_ERROR_TABLE_NAME
1905 , p_entity_id => NULL
1906 , p_entity_index => NULL
1907 , p_entity_code => G_ERROR_ENTITY_CODE
1908 );
1909 END IF;
1910 check_and_write_log(p_msg_size => G_MAX_MESSAGE_SIZE
1911 ,x_retcode => x_retcode);
1912 IF x_retcode = RETCODE_ERROR THEN
1913 RETURN;
1914 END IF;
1915 END LOOP; -- error Dates
1916
1917 Write_Debug('Erroring out Invalid Transaction Type records');
1918 -------------------------------------------------------------------------
1919 -- find the error records with invalid transaction_type
1920 -- valid transaction_types are CREATE, UPDATE, SYNC, DELETE
1921 -------------------------------------------------------------------------
1922 FOR cr IN c_err_transaction_type LOOP
1923 UPDATE ego_item_people_intf
1924 SET process_status = G_PS_ERROR
1925 WHERE CURRENT OF c_err_transaction_type;
1926 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
1927 IF ( cr.transaction_type IS NULL ) THEN
1928 l_msg_name := 'EGO_IPI_MISSING_VALUE';
1929 l_token_tbl_one(1).token_name := 'VALUE';
1930 l_token_tbl_one(1).token_value := 'TRANSACTION TYPE';
1931
1932 error_handler.Add_Error_Message
1933 ( p_message_name => l_msg_name
1934 , p_application_id => 'EGO'
1935 , p_message_text => NULL
1936 , p_token_tbl => l_token_tbl_one
1937 , p_message_type => 'E'
1938 , p_row_identifier => cr.transaction_id
1939 , p_table_name => G_ERROR_TABLE_NAME
1940 , p_entity_id => NULL
1941 , p_entity_index => NULL
1942 , p_entity_code => G_ERROR_ENTITY_CODE
1943 );
1944 ELSE
1945
1946 l_msg_name := 'EGO_IPI_INVALID_VALUE';
1947 l_token_tbl_two(1).token_name := 'NAME';
1948 l_token_tbl_two(1).token_value := 'TRANSACTION TYPE';
1949 l_token_tbl_two(2).token_name := 'VALUE';
1950 l_token_tbl_two(2).token_value := cr.transaction_type;
1951 error_handler.Add_Error_Message
1952 ( p_message_name => l_msg_name
1953 , p_application_id => 'EGO'
1954 , p_message_text => NULL
1955 , p_token_tbl => l_token_tbl_two
1956 , p_message_type => 'E'
1957 , p_row_identifier => cr.transaction_id
1958 , p_table_name => G_ERROR_TABLE_NAME
1959 , p_entity_id => NULL
1960 , p_entity_index => NULL
1961 , p_entity_code => G_ERROR_ENTITY_CODE
1962 );
1963 END IF;
1964 END IF;
1965 check_and_write_log(p_msg_size => G_MAX_MESSAGE_SIZE
1966 ,x_retcode => x_retcode);
1967 IF x_retcode = RETCODE_ERROR THEN
1968 RETURN;
1969 END IF;
1970 END LOOP; -- error Transaction Types
1971
1972 --error_count_records();
1973 Write_Debug('Erroring out Invalid Grantee Type / Grantee Name records');
1974
1975 -------------------------------------------------------------------------
1976 -- validation for grantee_type and grantee_name combination
1977 -------------------------------------------------------------------------
1978 FOR cr IN c_err_grantee_type LOOP
1979 UPDATE ego_item_people_intf
1980 SET process_status = G_PS_ERROR
1981 WHERE CURRENT OF c_err_grantee_type;
1982 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
1983 IF ( cr.grantee_type IS NULL ) THEN
1984
1985 Write_Debug (to_char(cr.transaction_id)||' Missing Grantee Type');
1986 l_msg_name := 'EGO_IPI_MISSING_VALUE';
1987 l_token_tbl_one(1).token_name := 'VALUE';
1988 l_token_tbl_one(1).token_value := 'GRANTEE TYPE';
1989 error_handler.Add_Error_Message
1990 ( p_message_name => l_msg_name
1991 , p_application_id => 'EGO'
1992 , p_message_text => NULL
1993 , p_token_tbl => l_token_tbl_one
1994 , p_message_type => 'E'
1995 , p_row_identifier => cr.transaction_id
1996 , p_table_name => G_ERROR_TABLE_NAME
1997 , p_entity_id => NULL
1998 , p_entity_index => NULL
1999 , p_entity_code => G_ERROR_ENTITY_CODE
2000 );
2001
2002 ELSE
2003 -- cr.grantee_type NOT IN ('USER','GROUP','COMPANY')
2004 Write_Debug (to_char(cr.transaction_id)||' Invalid Grantee Type');
2005 l_msg_name := 'EGO_IPI_INVALID_VALUE';
2006 l_token_tbl_two(1).token_name := 'NAME';
2007 l_token_tbl_two(1).token_value := 'GRANTEE TYPE';
2008 l_token_tbl_two(2).token_name := 'VALUE';
2009 l_token_tbl_two(2).token_value := cr.grantee_type;
2010 error_handler.Add_Error_Message
2011 ( p_message_name => l_msg_name
2012 , p_application_id => 'EGO'
2013 , p_message_text => NULL
2014 , p_token_tbl => l_token_tbl_two
2015 , p_message_type => 'E'
2016 , p_row_identifier => cr.transaction_id
2017 , p_table_name => G_ERROR_TABLE_NAME
2018 , p_entity_id => NULL
2019 , p_entity_index => NULL
2020 , p_entity_code => G_ERROR_ENTITY_CODE
2021 );
2022 END IF;
2023 END IF;
2024 check_and_write_log(p_msg_size => G_MAX_MESSAGE_SIZE
2025 ,x_retcode => x_retcode);
2026 IF x_retcode = RETCODE_ERROR THEN
2027 RETURN;
2028 END IF;
2029 END LOOP; -- error Grantee Types
2030
2031 --error_count_records();
2032 Write_Debug ('Grantee Type Completed ');
2033
2034 ----------------------------------------------------------------------------
2035 -- Fix for bug# 3433718. Allowing to pass case-insensitive Username. --
2036 -- Fnd_User.User_Name is unique, irrespective of the case. --
2037 ----------------------------------------------------------------------------
2038 Write_Debug('Updating the grantee_party_id in Intf table for People');
2039
2040 -------------------------------------------------------------------------
2041 -- Update the grantee_party id column for the people
2042 -------------------------------------------------------------------------
2043 UPDATE ego_item_people_intf eipi
2044 SET (eipi.grantee_party_id) =
2045 ( SELECT person_id
2046 FROM ego_people_v
2047 WHERE UPPER(user_name) = UPPER(eipi.grantee_name)
2048 )
2049 WHERE eipi.data_set_id = G_DATA_SET_ID
2050 AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
2051 AND eipi.process_status = G_PS_IN_PROCESS
2052 AND eipi.grantee_party_id IS NULL
2053 AND eipi.grantee_type IS NOT NULL
2054 AND eipi.grantee_type = 'USER';
2055
2056 Write_Debug('Updating the grantee_party_id in Intf table for Groups');
2057
2058 -------------------------------------------------------------------------
2059 --Update the grantee_party id column for the groups
2060 -------------------------------------------------------------------------
2061 UPDATE ego_item_people_intf eipi
2062 SET eipi.grantee_party_id =
2063 ( SELECT group_id
2064 FROM ego_groups_v
2065 WHERE group_name = eipi.grantee_name
2066 )
2067 WHERE eipi.data_set_id = G_DATA_SET_ID
2068 AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
2069 AND eipi.process_status = G_PS_IN_PROCESS
2070 AND eipi.grantee_party_id IS NULL
2071 AND eipi.grantee_type IS NOT NULL
2072 AND eipi.grantee_type = 'GROUP';
2073
2074 Write_Debug('Updating the grantee_party_id in Intf table for Compnys');
2075
2076 -------------------------------------------------------------------------
2077 --Update the grantee_party id column for the Companies
2078 --Company can be Enterprise / External Customer / External Supplier
2079 -------------------------------------------------------------------------
2080 UPDATE ego_item_people_intf eipi
2081 SET eipi.grantee_party_id =
2082 ( SELECT company_id
2083 FROM ego_companies_v
2084 WHERE company_name = eipi.grantee_name
2085 )
2086 WHERE eipi.data_set_id = G_DATA_SET_ID
2087 AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
2088 AND eipi.process_status = G_PS_IN_PROCESS
2089 AND eipi.grantee_party_id IS NULL
2090 AND eipi.grantee_type IS NOT NULL
2091 AND eipi.grantee_type = 'COMPANY';
2092
2093 Write_Debug('Updating the grantee_party_id in Intf table for AllUsrs');
2094
2095 -------------------------------------------------------------------------
2096 --Update the grantee_party id column for the All Users
2097 -------------------------------------------------------------------------
2098 UPDATE ego_item_people_intf eipi
2099 SET eipi.grantee_party_id = G_ALL_USERS_PARTY_ID
2100 WHERE eipi.data_set_id = G_DATA_SET_ID
2101 AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
2102 AND eipi.process_status = G_PS_IN_PROCESS
2103 AND eipi.grantee_party_id IS NULL
2104 AND eipi.grantee_type IS NOT NULL
2105 AND eipi.grantee_type = 'GLOBAL';
2106
2107 Write_Debug('Erroring out NULL Grantee_party_id records');
2108
2109 -------------------------------------------------------------------------
2110 -- For missing grantee_party_id, update process_status and log an error.
2111 -- Also, assign transaction_id, request_id
2112 -------------------------------------------------------------------------
2113 FOR cr IN c_err_grantee_id LOOP
2114 UPDATE ego_item_people_intf
2115 SET process_status = G_PS_ERROR
2116 WHERE CURRENT OF c_err_grantee_id;
2117 -------------------------------------------------------------------------
2118 -- Grantee Name check
2119 -------------------------------------------------------------------------
2120 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
2121 IF ( cr.grantee_name IS NULL ) THEN
2122 Write_Debug (to_char(cr.transaction_id) || ' Missing Grantee Name ');
2123 l_msg_name := 'EGO_IPI_MISSING_VALUE';
2124 l_token_tbl_one(1).token_name := 'VALUE';
2125 l_token_tbl_one(1).token_value := 'GRANTEE NAME';
2126 error_handler.Add_Error_Message
2127 ( p_message_name => l_msg_name
2128 , p_application_id => 'EGO'
2129 , p_message_text => NULL
2130 , p_token_tbl => l_token_tbl_one
2131 , p_message_type => 'E'
2132 , p_row_identifier => cr.transaction_id
2133 , p_table_name => G_ERROR_TABLE_NAME
2134 , p_entity_id => NULL
2135 , p_entity_index => NULL
2136 , p_entity_code => G_ERROR_ENTITY_CODE
2137 );
2138 ELSE
2139 Write_Debug (to_char(cr.transaction_id) || ' Invalid Grantee Name ');
2140 l_msg_name := 'EGO_IPI_INVALID_VALUE';
2141 l_token_tbl_two(1).token_name := 'NAME';
2142 l_token_tbl_two(1).token_value := 'GRANTEE NAME';
2143 l_token_tbl_two(2).token_name := 'VALUE';
2144 l_token_tbl_two(2).token_value := cr.grantee_name;
2145 error_handler.Add_Error_Message
2146 ( p_message_name => l_msg_name
2147 , p_application_id => 'EGO'
2148 , p_message_text => NULL
2149 , p_token_tbl => l_token_tbl_two
2150 , p_message_type => 'E'
2151 , p_row_identifier => cr.transaction_id
2152 , p_table_name => G_ERROR_TABLE_NAME
2153 , p_entity_id => NULL
2154 , p_entity_index => NULL
2155 , p_entity_code => G_ERROR_ENTITY_CODE
2156 );
2157 END IF;
2158 END IF;
2159 check_and_write_log(p_msg_size => G_MAX_MESSAGE_SIZE
2160 ,x_retcode => x_retcode);
2161 IF x_retcode = RETCODE_ERROR THEN
2162 RETURN;
2163 END IF;
2164 END LOOP; -- c_err_grantee_id
2165
2166 --error_count_records();
2167 Write_Debug (' Grantee Name Completed ');
2168
2169 -------------------------------------------------------------------------
2170 -- Retrieval of Role Ids is done in 2 steps :
2171 --1) Retrieve and store the Display and Internal Role Names and Role Ids
2172 -- and store in a temp table. This is done by initialise_roles()
2173 --2) Verify the roles from the temporary table.
2174 -------------------------------------------------------------------------
2175
2176 Write_Debug('Updating the Role Id, Role Name columns in Intf table');
2177 -------------------------------------------------------------------------
2178 -- Fix for Bug# 3050477.
2179 -- Reference to EGO_OBJECT_ROLES is removed.
2180 -- bug 4930322 modified the query to avoid full table scans
2181 -------------------------------------------------------------------------
2182 UPDATE ego_item_people_intf eipi
2183 SET (eipi.internal_role_id, eipi.internal_role_name ) =
2184 ( SELECT roles.menu_id internal_role_id,
2185 roles.menu_name internal_role_name
2186 FROM (
2187 SELECT DISTINCT e.menu_id role_id
2188 FROM fnd_form_functions f, fnd_menu_entries e
2189 WHERE e.function_id = f.function_id
2190 AND f.object_id = G_FND_OBJECT_ID
2191 ) obj_roles,
2192 fnd_menus roles,
2193 fnd_menus_tl roles_tl
2194 WHERE obj_roles.role_id = roles.menu_id
2195 AND obj_roles.role_id = roles_tl.menu_id
2196 AND roles_tl.language = G_SESSION_LANG
2197 AND roles_tl.user_menu_name = eipi.display_role_name
2198 )
2199 WHERE eipi.data_set_id = G_DATA_SET_ID
2200 AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
2201 AND eipi.process_status = G_PS_IN_PROCESS
2202 AND eipi.internal_role_id IS NULL
2203 AND eipi.display_role_name IS NOT NULL;
2204
2205 Write_Debug('Erroring out NULL Role Id records');
2206
2207 -------------------------------------------------------------------------
2208 -- For missing roles, update process_status and log an error.
2209 -------------------------------------------------------------------------
2210 FOR cr IN c_err_role_id LOOP
2211 UPDATE ego_item_people_intf
2212 SET process_status = G_PS_ERROR
2213 WHERE CURRENT OF c_err_role_id;
2214
2215 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
2216 IF ( cr.display_role_name IS NULL ) THEN
2217 l_msg_name := 'EGO_IPI_MISSING_VALUE';
2218 l_token_tbl_one(1).token_name := 'VALUE';
2219 l_token_tbl_one(1).token_value := 'DISPLAY ROLE NAME';
2220 error_handler.Add_Error_Message
2221 ( p_message_name => l_msg_name
2222 , p_application_id => 'EGO'
2223 , p_message_text => NULL
2224 , p_token_tbl => l_token_tbl_one
2225 , p_message_type => 'E'
2226 , p_row_identifier => cr.transaction_id
2227 , p_table_name => G_ERROR_TABLE_NAME
2228 , p_entity_id => NULL
2229 , p_entity_index => NULL
2230 , p_entity_code => G_ERROR_ENTITY_CODE
2231 );
2232 ELSE
2233 l_msg_name := 'EGO_IPI_INVALID_VALUE';
2234 l_token_tbl_two(1).token_name := 'NAME';
2235 l_token_tbl_two(1).token_value := 'DISPLAY ROLE NAME';
2236 l_token_tbl_two(2).token_name := 'VALUE';
2237 l_token_tbl_two(2).token_value := cr.display_role_name;
2238 error_handler.Add_Error_Message
2239 ( p_message_name => l_msg_name
2240 , p_application_id => 'EGO'
2241 , p_message_text => NULL
2242 , p_token_tbl => l_token_tbl_two
2243 , p_message_type => 'E'
2244 , p_row_identifier => cr.transaction_id
2245 , p_table_name => G_ERROR_TABLE_NAME
2246 , p_entity_id => NULL
2247 , p_entity_index => NULL
2248 , p_entity_code => G_ERROR_ENTITY_CODE
2249 );
2250 END IF;
2251 END IF;
2252 check_and_write_log(p_msg_size => G_MAX_MESSAGE_SIZE
2253 ,x_retcode => x_retcode);
2254 IF x_retcode = RETCODE_ERROR THEN
2255 RETURN;
2256 END IF;
2257 END LOOP; -- c_err_role_id
2258
2259 --error_count_records();
2260 Write_Debug (' Roles Completed ');
2261
2262
2263 Write_Debug('Updating the Organization Id column in Intf table');
2264 --Update the organization id column
2265
2266 UPDATE ego_item_people_intf eipi
2267 SET eipi.process_status = G_INT_ORG_VAL_ERROR
2268 WHERE eipi.data_set_id = G_DATA_SET_ID
2269 AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
2270 AND eipi.process_status = G_PS_IN_PROCESS
2271 AND ( (organization_id IS NOT NULL
2272 AND
2273 NOT EXISTS
2274 ( SELECT mp.organization_id
2275 FROM mtl_parameters mp
2276 WHERE mp.organization_id = eipi.organization_id
2277 )
2278 )
2279 OR
2280 (organization_id IS NULL
2281 AND
2282 NOT EXISTS
2283 ( SELECT mp.organization_id
2284 FROM mtl_parameters mp
2285 WHERE mp.organization_code = eipi.organization_code
2286 )
2287 )
2288 );
2289
2290 UPDATE ego_item_people_intf eipi
2291 SET organization_code =
2292 ( SELECT mp.organization_code
2293 FROM mtl_parameters mp
2294 WHERE mp.organization_id = eipi.organization_id
2295 )
2296 WHERE eipi.data_set_id = G_DATA_SET_ID
2297 AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
2298 AND eipi.process_status = G_PS_IN_PROCESS
2299 AND eipi.organization_id IS NOT NULL;
2300
2301 UPDATE ego_item_people_intf eipi
2302 SET organization_id =
2303 ( SELECT mp.organization_id
2304 FROM mtl_parameters mp
2305 WHERE mp.organization_code = eipi.organization_code
2306 )
2307 WHERE eipi.data_set_id = G_DATA_SET_ID
2308 AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
2309 AND eipi.process_status = G_PS_IN_PROCESS
2310 AND eipi.organization_id IS NULL;
2311
2312 Write_Debug (' Organization Id Completed ');
2313
2314 -------------------------------------------------------------------
2315 --
2316 -- Organization id is obtained, Please check the item_id now
2317 --
2318 -------------------------------------------------------------------
2319
2320 Write_Debug('Updating the Inv Item Id column in Intf table');
2321 --Retrieve the Item Id from Item Num and Organization Id.
2322 l_column_name := 'ITEM_NUMBER';
2323 -- bug 3710151
2324 --
2325 -- FOR cr IN c_get_item_number LOOP
2326 --
2327 -- Write_Debug(' calling validate with params item number ' || cr.item_number || ' org id ' || to_char(cr.organization_id) );
2328 --
2329 -- -------------------------------------------------------------------
2330 -- -- Retrieval of Item id is through FLEX APIs.
2331 -- -------------------------------------------------------------------
2332 -- IF FND_FLEX_KEYVAL.Validate_Segs
2333 -- ( operation => 'FIND_COMBINATION'
2334 -- , appl_short_name => 'INV'
2335 -- , key_flex_code => 'MSTK'
2336 -- , structure_number => 101
2337 -- , concat_segments => cr.item_number
2338 -- , data_set => cr.organization_id
2339 -- )
2340 -- THEN
2341 -- l_inventory_item_id := FND_FLEX_KEYVAL.combination_id;
2342 -- UPDATE ego_item_people_intf
2343 -- SET inventory_item_id = l_inventory_item_id
2344 -- WHERE CURRENT OF c_get_item_number;
2345 --
2346 -- --------------------------------------------------------------------
2347 -- -- check whether the logged in user can give access to the items
2348 -- --------------------------------------------------------------------
2349 -- IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
2350 --
2351 -- --------------------------------------------------------------------
2352 -- --If the person has Full access to items, there is no need
2353 -- --to check for Grant privileges.
2354 -- --------------------------------------------------------------------
2355 -- IF (G_FULL_ACCESS_ITEMS = TRUE) THEN
2356 -- Write_Debug('No need to check for Grant Privileges');
2357 -- NULL; --Do nothing
2358 -- ELSE
2359 --
2360 -- Write_Debug('Check for Grant Privileges on items in Intf');
2361 -- OPEN c_get_grant_privileges (
2362 -- cp_inventory_item_id => l_inventory_item_id,
2363 -- cp_organization_id => cr.organization_id
2364 -- );
2365 -- FETCH c_get_grant_privileges INTO l_inventory_item_id;
2366 -- IF c_get_grant_privileges%NOTFOUND THEN
2367 -- -- the user cannot grant privileges on this item
2368 -- UPDATE ego_item_people_intf
2369 -- SET process_status = G_PS_ERROR
2370 -- WHERE CURRENT OF c_get_item_number;
2371 -- l_msg_name := 'EGO_IPI_CANNOT_GRANT';
2372 -- l_token_tbl_three(1).token_name := 'USER';
2373 -- l_token_tbl_three(1).token_value := l_login_party_name;
2374 -- l_token_tbl_three(2).token_name := 'ITEM';
2375 -- l_token_tbl_three(2).token_value := cr.item_number;
2376 -- l_token_tbl_three(3).token_name := 'ORGANIZATION';
2377 -- l_token_tbl_three(3).token_value := cr.organization_code;
2378 -- error_handler.Add_Error_Message
2379 -- ( p_message_name => l_msg_name
2380 -- , p_application_id => 'EGO'
2381 -- , p_message_text => NULL
2382 -- , p_token_tbl => l_token_tbl_three
2383 -- , p_message_type => 'E'
2384 -- , p_row_identifier => cr.transaction_id
2385 -- , p_table_name => G_ERROR_TABLE_NAME
2386 -- , p_entity_id => NULL
2387 -- , p_entity_index => NULL
2388 -- , p_entity_code => G_ERROR_ENTITY_CODE
2389 -- );
2390 -- END IF; --IF c_get_grant_privileges%NOTFOUND THEN
2391 -- CLOSE c_get_grant_privileges;
2392 -- END IF; --IF (G_FULL_ACCESS_ITEMS = TRUE) THEN
2393 -- END IF; --IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
2394 -- ELSE -- valid item number (from fnd_flex_listval.validate_segs)
2395 --
2396 -- Write_Debug('Erroring out Invalid Item Number records');
2397 -- --
2398 -- -- invalid item number
2399 -- --
2400 -- UPDATE ego_item_people_intf
2401 -- SET process_status = G_PS_ERROR
2402 -- WHERE CURRENT OF c_get_item_number;
2403 -- IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
2404 -- IF ( cr.inventory_item_id IS NULL ) THEN
2405 -- IF ( cr.item_number IS NULL ) THEN
2406 -- l_msg_name := 'EGO_IPI_MISSING_VALUE';
2407 -- l_token_tbl_one(1).token_name := 'VALUE';
2408 -- l_token_tbl_one(1).token_value := 'ITEM NUMBER';
2409 -- error_handler.Add_Error_Message
2410 -- ( p_message_name => l_msg_name
2411 -- , p_application_id => 'EGO'
2412 -- , p_message_text => NULL
2413 -- , p_token_tbl => l_token_tbl_one
2414 -- , p_message_type => 'E'
2415 -- , p_row_identifier => cr.transaction_id
2416 -- , p_table_name => G_ERROR_TABLE_NAME
2417 -- , p_entity_id => NULL
2418 -- , p_entity_index => NULL
2419 -- , p_entity_code => G_ERROR_ENTITY_CODE
2420 -- );
2421 -- ELSE
2422 -- l_msg_name := 'EGO_IPI_INVALID_ITEM';
2423 -- l_token_tbl_two(1).token_name := 'ITEM';
2424 -- l_token_tbl_two(1).token_value := cr.item_number;
2425 -- l_token_tbl_two(2).token_name := 'ORGANIZATION';
2426 -- l_token_tbl_two(2).token_value := cr.organization_code;
2427 -- error_handler.Add_Error_Message
2428 -- ( p_message_name => l_msg_name
2429 -- , p_application_id => 'EGO'
2430 -- , p_message_text => NULL
2431 -- , p_token_tbl => l_token_tbl_two
2432 -- , p_message_type => 'E'
2433 -- , p_row_identifier => cr.transaction_id
2434 -- , p_table_name => G_ERROR_TABLE_NAME
2435 -- , p_entity_id => NULL
2436 -- , p_entity_index => NULL
2437 -- , p_entity_code => G_ERROR_ENTITY_CODE
2438 -- );
2439 -- END IF; -- item number is null
2440 -- END IF; -- inventory item id is null
2441 -- END IF; -- G_debug_mode
2442 -- END IF; -- valid item number (from fnd_flex_listval.validate_segs)
2443 -- check_and_write_log(p_msg_size => G_MAX_MESSAGE_SIZE
2444 -- ,x_retcode => x_retcode);
2445 -- IF x_retcode = RETCODE_ERROR THEN
2446 -- RETURN;
2447 -- END IF;
2448 -- END LOOP; -- c_get_item_number
2449
2450 --Update the inventory_item_id column
2451 UPDATE ego_item_people_intf eipi
2452 SET process_status = G_INT_ITEM_VAL_ERROR
2453 WHERE eipi.data_set_id = G_DATA_SET_ID
2454 AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
2455 AND eipi.process_status = G_PS_IN_PROCESS
2456 AND ( (inventory_item_id IS NOT NULL
2457 AND
2458 NOT EXISTS
2459 (SELECT 'x' FROM mtl_system_items_b_kfv item
2460 WHERE item.organization_id = eipi.organization_id
2461 AND item.inventory_item_id = eipi.inventory_item_id)
2462 )
2463 OR
2464 (inventory_item_id IS NULL
2465 AND
2466 NOT EXISTS
2467 (SELECT 'x' FROM mtl_system_items_b_kfv item
2468 WHERE item.organization_id = eipi.organization_id
2469 AND item.concatenated_segments = eipi.item_number)
2470 )
2471 );
2472
2473 UPDATE ego_item_people_intf eipi
2474 SET item_number =
2475 (Select concatenated_segments
2476 from mtl_system_items_b_kfv item
2477 where item.organization_id = eipi.organization_id
2478 and item.inventory_item_id = eipi.inventory_item_id)
2479 WHERE eipi.data_set_id = G_DATA_SET_ID
2480 AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
2481 AND eipi.process_status = G_PS_IN_PROCESS
2482 AND eipi.inventory_item_id IS NOT NULL;
2483
2484 UPDATE ego_item_people_intf eipi
2485 SET inventory_item_id =
2486 (Select inventory_item_id
2487 from mtl_system_items_b_kfv item
2488 where item.organization_id = eipi.organization_id
2489 and item.concatenated_segments = eipi.item_number)
2490 WHERE eipi.data_set_id = G_DATA_SET_ID
2491 AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
2492 AND eipi.process_status = G_PS_IN_PROCESS
2493 AND eipi.inventory_item_id IS NULL;
2494
2495 --
2496 -- flash all invalid item numbers
2497 --
2498 Write_Debug('Flashing messages for all invalid item records');
2499 FOR cr IN c_err_records LOOP
2500
2501 -- UPDATE ego_item_people_intf
2502 -- SET process_status = G_PS_ERROR
2503 -- WHERE CURRENT OF c_err_item_id;
2504
2505 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
2506 IF ( cr.organization_code IS NULL ) THEN
2507 l_msg_name := 'EGO_IPI_INVALID_VALUE';
2508 l_token_tbl_two(1).token_name := 'NAME';
2509 l_token_tbl_two(1).token_value := 'ORGANIZATION ID';
2510 l_token_tbl_two(2).token_name := 'VALUE';
2511 l_token_tbl_two(2).token_value := cr.organization_id;
2512 ELSIF (cr.organization_id IS NULL) THEN
2513 l_msg_name := 'EGO_IPI_INVALID_VALUE';
2514 l_token_tbl_two(1).token_name := 'NAME';
2515 l_token_tbl_two(1).token_value := 'ORGANIZATION CODE';
2516 l_token_tbl_two(2).token_name := 'VALUE';
2517 l_token_tbl_two(2).token_value := cr.organization_code;
2518 ELSIF ( cr.item_number IS NULL ) THEN
2519 l_msg_name := 'EGO_IPI_INVALID_VALUE';
2520 l_token_tbl_two(1).token_name := 'NAME';
2521 l_token_tbl_two(1).token_value := 'ITEM ID';
2522 l_token_tbl_two(2).token_name := 'VALUE';
2523 l_token_tbl_two(2).token_value := cr.inventory_item_id;
2524 ELSE
2525 l_msg_name := 'EGO_IPI_INVALID_ITEM';
2526 l_token_tbl_two(1).token_name := 'ITEM';
2527 l_token_tbl_two(1).token_value := cr.item_number;
2528 l_token_tbl_two(2).token_name := 'ORGANIZATION';
2529 l_token_tbl_two(2).token_value := cr.organization_code;
2530 END IF;
2531 error_handler.Add_Error_Message
2532 ( p_message_name => l_msg_name
2533 , p_application_id => 'EGO'
2534 , p_message_text => NULL
2535 , p_token_tbl => l_token_tbl_two
2536 , p_message_type => 'E'
2537 , p_row_identifier => cr.transaction_id
2538 , p_table_name => G_ERROR_TABLE_NAME
2539 , p_entity_id => NULL
2540 , p_entity_index => NULL
2541 , p_entity_code => G_ERROR_ENTITY_CODE
2542 );
2543 check_and_write_log(p_msg_size => G_MAX_MESSAGE_SIZE
2544 ,x_retcode => x_retcode);
2545 IF x_retcode = RETCODE_ERROR THEN
2546 RETURN;
2547 END IF;
2548 END IF; -- G_debug_mode
2549
2550 END LOOP; -- c_err_records
2551
2552 IF (G_FULL_ACCESS_ITEMS = TRUE) THEN
2553 Write_Debug('No need to check for Grant Privileges');
2554 NULL; --Do nothing
2555 ELSE
2556 --
2557 -- mark all item numbers on which privilege is not available
2558 --
2559
2560 Write_Debug('Checking for access privilege on items to be granted ');
2561 UPDATE ego_item_people_intf eipi
2562 SET process_status = G_PS_ERROR
2563 WHERE eipi.data_set_id = G_DATA_SET_ID
2564 AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
2565 AND eipi.process_status = G_PS_IN_PROCESS
2566 -- 6459864: ignoring privilege check when defaulting people from style-sku
2567 AND eipi.created_by <> -99
2568 AND eipi.inventory_item_id IS NOT NULL
2569 AND NOT EXISTS
2570 (SELECT 'X'
2571 FROM EGO_LOGIN_ITEMS_TEMP
2572 WHERE inventory_item_id = eipi.inventory_item_id
2573 AND organization_id = eipi.organization_id
2574 AND conc_request_id = G_REQUEST_ID
2575 );
2576
2577 Write_Debug('Flashing messages for all items on which user does not have any privilege ');
2578 FOR cr IN c_err_access_items LOOP
2579 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
2580 l_msg_name := 'EGO_IPI_CANNOT_GRANT';
2581 l_token_tbl_three(1).token_name := 'USER';
2582 l_token_tbl_three(1).token_value := l_login_party_name;
2583 l_token_tbl_three(2).token_name := 'ITEM';
2584 l_token_tbl_three(2).token_value := cr.item_number;
2585 l_token_tbl_three(3).token_name := 'ORGANIZATION';
2586 l_token_tbl_three(3).token_value := cr.organization_code;
2587 error_handler.Add_Error_Message
2588 ( p_message_name => l_msg_name
2589 , p_application_id => 'EGO'
2590 , p_message_text => NULL
2591 , p_token_tbl => l_token_tbl_three
2592 , p_message_type => 'E'
2593 , p_row_identifier => cr.transaction_id
2594 , p_table_name => G_ERROR_TABLE_NAME
2595 , p_entity_id => NULL
2596 , p_entity_index => NULL
2597 , p_entity_code => G_ERROR_ENTITY_CODE
2598 );
2599 END IF; -- G_debug_mode
2600 check_and_write_log(p_msg_size => G_MAX_MESSAGE_SIZE
2601 ,x_retcode => x_retcode);
2602 IF x_retcode = RETCODE_ERROR THEN
2603 RETURN;
2604 END IF;
2605 END LOOP; -- c_err_access_items
2606 END IF; -- check for full access on items.
2607 -- bug 3710151 ends
2608
2609 -- setting all error records to status error
2610 UPDATE ego_item_people_intf eipi
2611 SET eipi.process_status = G_PS_ERROR
2612 WHERE eipi.data_set_id = G_DATA_SET_ID
2613 AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
2614 AND eipi.process_status IN (G_INT_ITEM_VAL_ERROR
2615 ,G_INT_ORG_VAL_ERROR
2616 );
2617
2618 error_count_records();
2619 Write_Debug (' Item Number Completed ');
2620 -- commit the data after every batch
2621 --
2622 -- increment the loop values
2623 G_FROM_LINE_NUMBER := G_TO_LINE_NUMBER + 1;
2624 END LOOP; -- l_count_ipi_records
2625
2626 Write_Debug('Checking for Grant Overlap on the items');
2627 --
2628 -- upload the data into fnd_grants
2629 --
2630 validate_no_grant_overlap(x_retcode => x_retcode);
2631 check_and_write_log(p_msg_size => 0
2632 ,x_retcode => x_retcode);
2633
2634 ----------------------------------------------------------------
2635 /* Calling API: Write_Error_into_ConcurrentLog from EGOPOPIB.pls
2636 Writing Errors into Concurrent Log in case User chose to
2637 delete data from the interface tables OR Error Link page is not
2638 working.
2639 Bug# 4540712 (RSOUNDAR)
2640 */
2641 ----------------------------------------------------------------
2642 l_err_msg_sql := 'SELECT INTF.ITEM_NUMBER as ITEM_NUMBER, '||
2643 ' INTF.ORGANIZATION_CODE as ORGANIZATION_CODE, '||
2644 ' MIERR.ERROR_MESSAGE as ERROR_MESSAGE '||
2645 ' FROM EGO_ITEM_PEOPLE_INTF INTF, MTL_INTERFACE_ERRORS MIERR '||
2646 ' WHERE MIERR.TRANSACTION_ID = INTF.TRANSACTION_ID '||
2647 ' AND MIERR.REQUEST_ID = INTF.REQUEST_ID '||
2648 ' AND MIERR.request_id = :1';
2649
2650 EGO_ITEM_OPEN_INTERFACE_PVT.Write_Error_into_ConcurrentLog
2651 (p_entity_name => 'EGO_ITEM_PEOPLE'
2652 ,p_table_name => 'EGO_ITEM_PEOPLE_INTF'
2653 ,p_selectQuery => l_err_msg_sql
2654 ,p_request_id => G_REQUEST_ID
2655 ,x_return_status => l_return_status
2656 ,x_msg_count => l_msg_count
2657 ,x_msg_data => l_msg_data
2658 );
2659 Write_Debug('Returned from EGO_ITEM_OPEN_INTERFACE_PVT.Write_Error_into_concurrentlog with status '||l_return_status);
2660 IF NVL(l_return_status,FND_API.G_RET_STS_SUCCESS)
2661 = FND_API.G_RET_STS_UNEXP_ERROR THEN
2662 Write_Debug ('Error Message from EGO_ITEM_OPEN_INTERFACE_PVT.Write_Error_into_concurrentlog: '|| l_msg_data);
2663 END IF;
2664
2665 -------------------------------------------------------------------------------
2666 -- Fix for Bug# 3603328
2667 -- Deleting the entire table, is avoided.
2668 -- Now seeding Item rows striped with Concurrent Request ID.
2669 -- Rows will be deleted, per Conc Req ID, at the end of processing.
2670 --
2671 -- These lines are purged irrespective of the value for "p_delete_lines"
2672 -------------------------------------------------------------------------------
2673 purge_login_items_table();
2674
2675 Write_Debug('based on p_delete_lines :'||To_char(p_delete_lines)||' purge the intf table');
2676 --
2677 -- call purge_interface_lines if required
2678 --
2679 IF p_delete_lines IN
2680 (EGO_ITEM_PUB.G_INTF_DELETE_ALL
2681 ,EGO_ITEM_PUB.G_INTF_DELETE_ERROR
2682 ,EGO_ITEM_PUB.G_INTF_DELETE_SUCCESS
2683 ) THEN
2684 purge_lines
2685 (p_data_set_id => p_data_set_id
2686 ,p_closed_date => NULL
2687 ,p_delete_line_type => p_delete_lines
2688 ,x_retcode => x_retcode
2689 ,x_errbuff => x_errbuff
2690 );
2691 END IF;
2692
2693 IF x_retcode = RETCODE_SUCCESS AND G_HAS_ERRORS THEN
2694 x_retcode := RETCODE_WARNING;
2695 END IF;
2696
2697 Write_Debug('Load_interface_lines completed!');
2698
2699 Conc_Log('Loading of Item People Import Interface lines complete.');
2700
2701 -----------------------------------------------------------------------------
2702 --Close Error_Handler debug session, only if Debug session is already open.
2703 -----------------------------------------------------------------------------
2704 IF (Error_Handler.Get_Debug = 'Y') THEN
2705 Error_Handler.Close_Debug_Session;
2706 END IF;
2707
2708 EXCEPTION
2709 WHEN OTHERS THEN
2710 x_retcode := RETCODE_ERROR;
2711 purge_login_items_table();
2712 fnd_message.set_name ('EGO','EGO_IPI_EXCEPTION');
2713 fnd_message.set_token('PROG_NAME',l_program_name);
2714 x_errbuff := fnd_message.get();
2715 Conc_Output (p_msg => x_errbuff);
2716 Write_Debug (x_errbuff ||': Details => ' || SQLERRM(SQLCODE));
2717
2718 IF c_user_party_id%ISOPEN THEN
2719 CLOSE c_user_party_id;
2720 END IF;
2721
2722 IF c_count_ipi_lines %ISOPEN THEN
2723 CLOSE c_count_ipi_lines;
2724 END IF;
2725 IF c_get_trans_id_limits %ISOPEN THEN
2726 CLOSE c_get_trans_id_limits;
2727 END IF;
2728 IF c_err_mand_params%ISOPEN THEN
2729 CLOSE c_err_mand_params;
2730 END IF;
2731 IF c_err_dates%ISOPEN THEN
2732 CLOSE c_err_dates;
2733 END IF;
2734 IF c_err_transaction_type%ISOPEN THEN
2735 CLOSE c_err_transaction_type;
2736 END IF;
2737 IF c_err_grantee_type%ISOPEN THEN
2738 CLOSE c_err_grantee_type;
2739 END IF;
2740 IF c_err_grantee_id%ISOPEN THEN
2741 CLOSE c_err_grantee_id;
2742 END IF;
2743 IF c_err_role_id%ISOPEN THEN
2744 CLOSE c_err_role_id;
2745 END IF;
2746 -- bug 4628705
2747 -- IF c_err_org_id%ISOPEN THEN
2748 -- CLOSE c_err_org_id;
2749 -- END IF;
2750 -- bug 3710151
2751 -- IF c_get_item_number%ISOPEN THEN
2752 -- CLOSE c_get_item_number;
2753 -- END IF;
2754 -- IF c_get_grant_privileges%ISOPEN THEN
2755 -- CLOSE c_get_grant_privileges;
2756 -- END IF;
2757 -- bug 4628705
2758 -- IF c_err_item_id%ISOPEN THEN
2759 -- CLOSE c_err_item_id;
2760 -- END IF;
2761 IF c_err_records%ISOPEN THEN
2762 CLOSE c_err_records;
2763 END IF;
2764 IF c_err_access_items%ISOPEN THEN
2765 CLOSE c_err_access_items;
2766 END IF;
2767 -- bug 3710151 end
2768 IF c_get_utl_file_dir%ISOPEN THEN
2769 CLOSE c_get_utl_file_dir;
2770 END IF;
2771 -----------------------------------------------------
2772 -- Close Debug Session, as we cant proceed further.
2773 -----------------------------------------------------
2774 Error_Handler.Close_Debug_Session;
2775
2776 -------------------------------------------------------------
2777 -- Rollback incomplete processing, and raise the exception
2778 -- to trace all the way up.
2779 -------------------------------------------------------------
2780 ROLLBACK;
2781 RAISE;
2782
2783 END load_interface_lines;
2784
2785
2786 ----------------------------------------------------------
2787 -- --
2788 ----------------------------------------------------------
2789 PROCEDURE purge_interface_lines
2790 ( x_errbuff OUT NOCOPY VARCHAR2,
2791 x_retcode OUT NOCOPY VARCHAR2,
2792 p_data_set_id IN NUMBER,
2793 p_closed_date IN VARCHAR2,
2794 p_delete_line_type IN NUMBER
2795 ) IS
2796 -- Start OF comments
2797 -- API name : Clean Interface Lines
2798 -- TYPE : Public (called by Concurrent Program)
2799 -- Pre-reqs : None
2800 -- FUNCTION : Removes all the interface lines
2801 --
2802 l_closed_date DATE;
2803 l_program_name CONSTANT VARCHAR2(30) := 'PURGE_INTERFACE_LINES';
2804 BEGIN
2805 -- validate the given parameters
2806 G_DEBUG_MODE := DEBUG_MODE_DEBUG;
2807 IF (p_data_set_id IS NULL AND p_closed_date IS NULL)
2808 OR NVL(p_delete_line_type,-1) NOT IN
2809 (EGO_ITEM_PUB.G_INTF_DELETE_ALL
2810 ,EGO_ITEM_PUB.G_INTF_DELETE_ERROR
2811 ,EGO_ITEM_PUB.G_INTF_DELETE_SUCCESS
2812 ,EGO_ITEM_PUB.G_INTF_DELETE_NONE
2813 ) THEN
2814 -- invalid parameters
2815 x_retcode := RETCODE_ERROR;
2816 fnd_message.set_name('EGO','EGO_IPI_INSUFFICIENT_PARAMS');
2817 x_errbuff := fnd_message.get();
2818 conc_output (x_errbuff);
2819 ELSE
2820 -- call purge lines program with sufficient parameters.
2821 IF p_closed_date IS NULL THEN
2822 l_closed_date := NULL;
2823 ELSE
2824 l_closed_date := fnd_date.canonical_to_date(p_closed_date);
2825 END IF;
2826 purge_lines
2827 (p_data_set_id => p_data_set_id
2828 ,p_closed_date => l_closed_date
2829 ,p_delete_line_type => p_delete_line_type
2830 ,x_retcode => x_retcode
2831 ,x_errbuff => x_errbuff
2832 );
2833 END IF;
2834
2835 EXCEPTION
2836 WHEN OTHERS THEN
2837 x_retcode := RETCODE_ERROR;
2838 fnd_message.set_name('EGO','EGO_IPI_EXCEPTION');
2839 fnd_message.set_token('PROG_NAME',l_program_name);
2840 x_errbuff := fnd_message.get();
2841 conc_output (x_errbuff);
2842 Write_Debug (x_errbuff);
2843 ROLLBACK;
2844 RAISE;
2845
2846 END purge_interface_lines;
2847
2848 END EGO_ITEM_PEOPLE_IMPORT_PKG;