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