[Home] [Help]
PACKAGE BODY: APPS.EGO_CHANGE_PEOPLE_IMPORT_PKG
Source
1 PACKAGE BODY EGO_CHANGE_PEOPLE_IMPORT_PKG AS
2 /* $Header: EGOCPIB.pls 120.1 2006/02/22 23:41:03 msarkhel noship $ */
3
4 --------------------------------------------------------------------
5 -- OPEN ISSUES:
6 --
7 --------------------------------------------------------------------
8
9 -- =================================================================
10 -- Global variables used in Concurrent Program.
11 -- =================================================================
12
13 G_USER_ID NUMBER := -1;
14 G_LOGIN_ID NUMBER := -1;
15 G_PROG_APPID NUMBER := -1;
16 G_PROG_ID NUMBER := -1;
17 G_REQUEST_ID NUMBER := -1;
18
19 -- =================================================================
20 -- Global constants that need to be used.
21 -- =================================================================
22 -- The user language (to display the error messages in appropriate language)
23 G_SESSION_LANG VARCHAR2(99) := USERENV('LANG');
24
25 --Indicates the object name
26 G_FND_OBJECT_NAME VARCHAR2(99) := 'ENG_CHANGE';
27
28 --Indicates the object id (set using g_Fnd_Object_Name)
29 G_FND_OBJECT_ID fnd_objects.object_id%TYPE;
30
31 -- Seeded value for all_users (group available in hz_parties)
32 G_ALL_USERS_PARTY_ID PLS_INTEGER := -1000;
33
34 -- Batch size that needs to be processed
35 G_BATCH_SIZE PLS_INTEGER;
36
37 -- Message array size
38 G_MAX_MESSAGE_SIZE PLS_INTEGER := 1000;
39
40 G_ERROR_TABLE_NAME VARCHAR2(99) := 'ENG_CHANGE_PEOPLE_INTF';
41 G_ERROR_ENTITY_CODE VARCHAR2(99) := 'EGO_CHANGE_PEOPLE';
42 G_ERROR_FILE_NAME VARCHAR2(99);
43 G_BO_IDENTIFIER VARCHAR2(99) := 'EGO_CHANGE_PEOPLE';
44 --
45 -- return status from VALIDATE_UPDATE_GRANT
46 -- used for status reference between
47 -- validate_no_grant_overlap and validate_update_grant
48 --
49 G_UPDATE_REC_DONE NUMBER := 1;
50 G_UPDATE_OVERLAP_ERROR NUMBER := -1;
51 G_UPDATE_REC_NOT_FOUND NUMBER := -2;
52 --
53 -- return status from VALIDATE_INSERT_GRANT
54 -- used for status reference between
55 -- validate_no_grant_overlap and validate_insert_grant
56 --
57 G_INSERT_REC_DONE NUMBER := 1;
58 G_INSERT_OVERLAP_ERROR NUMBER := -1;
59 --
60 -- variables that will be used across programs
61 --
62 G_DATA_SET_ID ENG_CHANGE_PEOPLE_INTF.data_set_id%TYPE;
63 G_FROM_LINE_NUMBER NUMBER;
64 G_TO_LINE_NUMBER NUMBER;
65 G_TRANSACTION_ID NUMBER;
66 G_DEBUG_MODE PLS_INTEGER;
67
68 G_TABLE_LOG BOOLEAN;
69
70
71 ----------------------------------------------------------------------
72 -- Global variables used for Parsing process.
73 ----------------------------------------------------------------------
74
75
76 ---------------------------------------------
77 -- PRIVATE PROCEDURES AND FUNCTIONS --
78 ---------------------------------------------
79
80 line_no PLS_INTEGER := 5000;
81 debug_line_count PLS_INTEGER := 0;
82
83 PROCEDURE debug_function (p_message VARCHAR2) IS
84 -- Start OF comments
85 -- API name : debug function
86 -- TYPE : PRIVATE
87 -- Pre-reqs : None
88 -- FUNCTION : log the error as per the debug mode chosen by the user
89 --
90 -- Parameters:
91 -- IN : message to be logged
92 BEGIN
93 IF G_DEBUG_MODE = DEBUG_MODE_FATAL THEN
94 -- only fatal errors should be logged
95 NULL;
96 ELSIF G_DEBUG_MODE = DEBUG_MODE_ERROR THEN
97 -- only errors needs to be logged
98 NULL;
99 ELSIF G_DEBUG_MODE = DEBUG_MODE_INFO THEN
100 -- all errors and info needs to be logged
101 NULL;
102 ELSIF G_DEBUG_MODE = DEBUG_MODE_DEBUG THEN
103 -- INSERT INTO idc_debug VALUES(line_no, p_message);
104 -- COMMIT;
105 line_no := line_no + 1;
106 END IF;
107 EXCEPTION
108 WHEN OTHERS THEN
109 ROLLBACK;
110 END debug_function;
111
112
113 PROCEDURE error_count_records IS
114 -- Start OF comments
115 -- API name : Error Count Records
116 -- TYPE : PRIVATE
117 -- Pre-reqs : None
118 -- FUNCTION : Get the number of errors encountered for each batch
119 --
120 -- Parameters:
121 -- IN : NONE
122 --
123 l_error_record_count PLS_INTEGER;
124 BEGIN
125 IF G_DEBUG_MODE = DEBUG_MODE_DEBUG THEN
126 SELECT COUNT(*)
127 INTO l_error_record_count
128 FROM ENG_CHANGE_PEOPLE_INTF
129 WHERE data_set_id = G_DATA_SET_ID
130 AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
131 AND process_status = G_PS_ERROR;
132 END IF;
133 EXCEPTION
134 WHEN OTHERS THEN ROLLBACK;
135 END error_count_records;
136
137
138 PROCEDURE write_log_now IS
139 -- Start OF comments
140 -- API name : write_log_now
141 -- TYPE : PRIVATE
142 -- Pre-reqs : NONE
143 --
144 -- FUNCTION : To check the size of error records and
145 -- commit them as per the required standards
146 --
147 -- Parameters:
148 -- IN : NONE
149 --
150 -- OUT : x_retcode NUMBER
151 -- return status of the program
152 --
153
154 BEGIN
155 IF G_TABLE_LOG THEN
156 ERROR_HANDLER.Log_Error(p_write_err_to_inttable => 'Y'
157 ,p_write_err_to_conclog => 'Y'
158 ,p_write_err_to_debugfile => ERROR_HANDLER.Get_Debug());
159 ELSE
160 ERROR_HANDLER.Log_Error(p_write_err_to_inttable => 'N'
161 ,p_write_err_to_conclog => 'Y'
162 ,p_write_err_to_debugfile => ERROR_HANDLER.Get_Debug());
163 END IF;
164 EXCEPTION
165 WHEN OTHERS THEN
166 ROLLBACK;
167 END write_log_now;
168
169
170 PROCEDURE check_and_write_log (x_retcode OUT NOCOPY NUMBER) IS
171 -- Start OF comments
172 -- API name : check_and_write_log
173 -- TYPE : PRIVATE
174 -- Pre-reqs : NONE
175 --
176 -- FUNCTION : To check the size of error records and
177 -- commit them as per the required standards
178 --
179 -- Parameters:
180 -- IN : NONE
181 --
182 -- OUT : x_retcode NUMBER
183 -- return status of the program
184 --
185
186 BEGIN
187 IF Error_Handler.Get_Message_Count() > G_MAX_MESSAGE_SIZE THEN
188 write_log_now();
189 error_Handler.Initialize();
190 END IF;
191 x_retcode := RETCODE_SUCCESS;
192 EXCEPTION
193 WHEN OTHERS THEN
194 x_retcode := RETCODE_ERROR;
195 ROLLBACK;
196 END;
197
198
199 PROCEDURE initialize_fnd_object_id(p_object_name IN VARCHAR2) IS
200 -- Start OF comments
201 -- API name : Initialize_fnd_object_id
202 -- TYPE : PRIVATE
203 -- Pre-reqs : None
204 -- FUNCTION : To obtain the object_id of the object
205 --
206 -- Parameters:
207 -- IN : object_name
208 --
209 CURSOR c_fnd_object_id(c_object_name IN VARCHAR2) IS
210 SELECT object_id
211 FROM fnd_objects
212 WHERE obj_name = c_object_name;
213
214 BEGIN
215 OPEN c_fnd_object_id(p_object_name);
216 FETCH c_fnd_object_id INTO G_FND_OBJECT_ID;
217 IF c_fnd_object_id%NOTFOUND THEN
218 G_FND_OBJECT_ID := NULL;
219 END IF;
220 CLOSE c_fnd_object_id;
221
222 EXCEPTION
223 WHEN OTHERS THEN
224 ROLLBACK;
225 IF c_fnd_object_id%ISOPEN THEN
226 CLOSE c_fnd_object_id;
227 END IF;
228 END initialize_fnd_object_id;
229
230
231 PROCEDURE initialize_all_users IS
232 -- Start OF comments
233 -- API name : Initialize_all_users
234 -- TYPE : PRIVATE
235 -- Pre-reqs : None
236 -- FUNCTION : To obtain the party_id for all_users
237 --
238 -- Parameters:
239 -- IN : object_name
240 --
241 CURSOR c_all_users_party_id IS
242 SELECT party_id
243 FROM hz_parties
244 WHERE party_type = 'GLOBAL'
245 AND party_name = 'All Users';
246
247 BEGIN
248 OPEN c_all_users_party_id;
249 FETCH c_all_users_party_id INTO G_ALL_USERS_PARTY_ID;
250 IF c_all_users_party_id%NOTFOUND THEN
251 G_ALL_USERS_PARTY_ID := NULL;
252 END IF;
253 CLOSE c_all_users_party_id;
254
255 EXCEPTION
256 WHEN OTHERS THEN
257 IF c_all_users_party_id%ISOPEN THEN
258 CLOSE c_all_users_party_id;
259 END IF;
260 END initialize_all_users;
261
262
263 PROCEDURE initialize_roles IS
264 -- Start OF comments
265 -- API name : Initialize_roles
266 -- TYPE : PRIVATE
267 -- Pre-reqs : The Object_id is populated into G_FND_OBJECT_ID
268 -- initialize_fnd_object_id must be called prior
269 -- to call to this routine
270 -- FUNCTION : To populate temporary table ENG_CHANGE_ROLES_TEMP
271 -- with the roles available for the specific object
272 --
273 -- Parameters:
274 -- IN : NONE
275 --
276 BEGIN
277 --Execute Immediate 'TRUNCATE TABLE ENG_CHANGE_ROLES_TEMP';
278 DELETE ENG_CHANGE_ROLES_TEMP;
279 INSERT into eng_change_roles_temp
280 (INTERNAL_ROLE_ID,INTERNAL_ROLE_NAME,DISPLAY_ROLE_NAME)
281 SELECT DISTINCT role_tl.menu_id internal_role_id,
282 role.menu_name internal_role_name,
283 role_tl.user_menu_name display_role_name
284 FROM fnd_menus_tl role_tl,
285 fnd_menus role,
286 fnd_menu_entries role_privs,
287 fnd_form_functions privs
288 WHERE privs.object_id = G_FND_OBJECT_ID
289 AND privs.function_id = role_privs.function_id
290 AND role_privs.menu_id = role_tl.menu_id
291 AND role_tl.menu_id = role.menu_id
292 AND role_tl.language = G_SESSION_LANG;
293 EXCEPTION
294 WHEN OTHERS THEN
295 ROLLBACK;
296 END initialize_roles;
297
298
299 PROCEDURE Initialize_Access_Changes
300 (p_login_person_id IN NUMBER
301 ,x_retcode OUT NOCOPY NUMBER) IS
302 -- Start OF comments
303 -- API name : Initialize_Access_Changes
304 -- TYPE : PRIVATE
305 -- Pre-reqs : Valid user has logged in
306 --
307 -- FUNCTION : To populate temporary table ENG_LOGIN_ACCESS_CHANGES
308 -- with the changes onto which the user can give access
309 --
310 -- Parameters:
311 -- IN : NONE
312 --
313 l_sec_predicate VARCHAR2(10000);
314 l_return_status VARCHAR2(10);
315
316 l_count NUMBER := 0;
317 l_select_sql VARCHAR2(32767);
318 l_insert_sql VARCHAR2(500);
319 cursor_select INTEGER;
320 cursor_insert INTEGER;
321 cursor_execute INTEGER;
322 l_change_notice_table DBMS_SQL.VARCHAR2_TABLE;
323 l_org_id_table DBMS_SQL.NUMBER_TABLE;
324 l_change_mgmt_type_code_table DBMS_SQL.VARCHAR2_TABLE;
325 indx NUMBER(10) := 1;
326
327 l_program_name VARCHAR2(99) := 'INITIALIZE_ACCESS_CHANGES';
328
329 BEGIN
330
331 --EXECUTE IMMEDIATE 'TRUNCATE TABLE ENG_LOGIN_ACCESS_CHANGES';
332 DELETE ENG_LOGIN_ACCESS_CHANGES;
333
334 l_select_sql := 'SELECT OUT_ENG_CHANGES.CHANGE_NOTICE, OUT_ENG_CHANGES.CHANGE_MGMT_TYPE_CODE, OUT_ENG_CHANGES.ORGANIZATION_ID '
335 || 'FROM ENG_ENGINEERING_CHANGES OUT_ENG_CHANGES ';
336
337 EGO_DATA_SECURITY.get_security_predicate(
338 p_api_version => 1.0,
339 p_function => 'ENG_EDIT_CHANGE', -- fnd_form_function.function_name which specify that user has access
340 p_object_name => 'ENG_CHANGE',
341 p_user_name => 'HZ_PARTY:'||TO_CHAR(p_login_person_id),
342 p_statement_type => 'EXISTS',
343 p_pk1_alias => 'OUT_ENG_CHANGES.CHANGE_ID',
344 p_pk2_alias => NULL,
345 p_pk3_alias => NULL,
346 p_pk4_alias => NULL,
347 p_pk5_alias => NULL,
348 x_predicate => l_sec_predicate,
349 x_return_status => l_return_status );
350
351 if (l_sec_predicate IS NOT NULL) then
352 l_select_sql := l_select_sql || ' WHERE ' || l_sec_predicate ;
353 end if;
354 l_insert_sql := 'INSERT INTO ENG_LOGIN_ACCESS_CHANGES(CHANGE_NOTICE,CHANGE_MGMT_TYPE_CODE,ORGANIZATION_ID) VALUES (:l_change_notice_table, :l_change_mgmt_type_code_table,:l_org_id_table) ';
355
356 cursor_select := DBMS_SQL.OPEN_CURSOR;
357 cursor_insert := DBMS_SQL.OPEN_CURSOR;
358 DBMS_SQL.PARSE(cursor_select,l_select_sql,DBMS_SQL.NATIVE);
359 DBMS_SQL.PARSE(cursor_insert,l_insert_sql,DBMS_SQL.NATIVE);
360
361 DBMS_SQL.DEFINE_ARRAY(cursor_select, 1,l_change_notice_table,2500, indx);
362 DBMS_SQL.DEFINE_ARRAY(cursor_select, 2,l_change_mgmt_type_code_table,2500, indx);
363 DBMS_SQL.DEFINE_ARRAY(cursor_select, 3,l_org_id_table,2500, indx);
364
365 cursor_execute := DBMS_SQL.EXECUTE(cursor_select);
366
367 LOOP
368 l_count := DBMS_SQL.FETCH_ROWS(cursor_select);
369 DBMS_SQL.COLUMN_VALUE(cursor_select, 1, l_change_notice_table);
370 DBMS_SQL.COLUMN_VALUE(cursor_select, 2, l_change_mgmt_type_code_table);
371 DBMS_SQL.COLUMN_VALUE(cursor_select, 3, l_org_id_table);
372
373
374 DBMS_SQL.BIND_ARRAY(cursor_insert,':l_change_notice_table',l_change_notice_table);
375 DBMS_SQL.BIND_ARRAY(cursor_insert,':l_change_mgmt_type_code_table',l_change_mgmt_type_code_table);
376 DBMS_SQL.BIND_ARRAY(cursor_insert,':l_org_id_table',l_org_id_table);
377 cursor_execute := DBMS_SQL.EXECUTE(cursor_insert);
378 l_change_notice_table.DELETE;
379 l_org_id_table.DELETE;
380 l_change_mgmt_type_code_table.DELETE;
381
382 --Can put a parameter based on which we can commit
383 --commit;
384
385 --For the final batch of records, either it will be 0 or < 2500
386 EXIT WHEN l_count <> 2500;
387 END LOOP;
388
389 DBMS_SQL.CLOSE_CURSOR(cursor_select);
390 DBMS_SQL.CLOSE_CURSOR(cursor_insert);
391
392 EXCEPTION
393 WHEN OTHERS THEN
394 x_retcode := RETCODE_ERROR;
395 IF DBMS_SQL.IS_OPEN(cursor_select) THEN
396 DBMS_SQL.CLOSE_CURSOR(cursor_select);
397 END IF;
398 IF DBMS_SQL.IS_OPEN(cursor_insert) THEN
399 DBMS_SQL.CLOSE_CURSOR(cursor_insert);
400 END IF;
401 RAISE;
402 END Initialize_Access_Changes;
403
404
405 PROCEDURE validate_update_grant
406 (p_transaction_type IN VARCHAR2
407 ,p_transaction_id IN NUMBER
408 ,p_change_id IN NUMBER
409 ,p_organization_id IN NUMBER
410 ,p_internal_role_id IN NUMBER
411 ,p_user_party_id_char IN VARCHAR2
412 ,p_group_party_id_char IN VARCHAR2
413 ,p_global_party_id_char IN VARCHAR2
414 ,p_company_party_id_char IN VARCHAR2
415 ,p_start_date IN DATE
416 ,p_end_date IN DATE
417 ,x_return_status OUT NOCOPY NUMBER) IS
418 -- Start OF comments
422 --
419 -- API name : validate_update_grant
420 -- TYPE : PRIVATE
421 -- Pre-reqs : NONE
423 -- FUNCTION : To check if the required grant can be updated
424 -- and updates fnd_grants if required
425 -- NO ACTION IS PERFORMED ON eng_change_people_intf
426 --
427 -- Parameters:
428 -- IN : NONE
429 --
430 -- OUT : x_return_status NUMBER
431 -- Indicates the status of the record
432 -- -1 Record not found for update
433 -- -2 Record found for update but will cause overlap
434 -- 1 Record found and updated
435 --
436
437 CURSOR c_get_update_grantid
438 (cp_change_id IN NUMBER
439 ,cp_organization_id IN NUMBER
440 ,cp_menu_id IN NUMBER
441 ,cp_object_id IN NUMBER
442 ,cp_user_party_id_char IN VARCHAR2
443 ,cp_group_party_id_char IN VARCHAR2
444 ,cp_global_party_id_char IN VARCHAR2
445 ,cp_company_party_id_char IN VARCHAR2
446 ,cp_start_date IN DATE
447 ) IS
448 SELECT grant_guid
449 FROM fnd_grants grants
450 WHERE grants.object_id = G_FND_OBJECT_ID
451 AND grants.menu_id = cp_menu_id
452 AND grants.instance_type = 'INSTANCE'
453 AND grants.instance_pk1_value = TO_CHAR(cp_change_id)
454 -- Commented as PK2_Value for ENG_CHANGE in fnd_objects is NULL
455 -- AND grants.instance_pk2_value = TO_CHAR(cp_organization_id)
456 AND ((grants.grantee_type = 'USER' AND grants.grantee_key = cp_user_party_id_char ) OR
457 (grants.grantee_type = 'GROUP' AND grants.grantee_key = cp_group_party_id_char) OR
458 (grants.grantee_type = 'GLOBAL' AND grants.grantee_key = cp_global_party_id_char) OR
459 (grants.grantee_type = 'COMPANY' AND grants.grantee_key = cp_company_party_id_char)
460 )
461 AND start_date = cp_start_date;
462
463 CURSOR c_get_valid_update
464 (cp_grant_guid IN RAW
465 ,cp_change_id IN NUMBER
466 ,cp_organization_id IN NUMBER
467 ,cp_menu_id IN NUMBER
468 ,cp_object_id IN NUMBER
469 ,cp_user_party_id_char IN VARCHAR2
470 ,cp_group_party_id_char IN VARCHAR2
471 ,cp_global_party_id_char IN VARCHAR2
472 ,cp_company_party_id_char IN VARCHAR2
473 ,cp_start_date IN DATE
474 ,cp_end_date IN DATE
475 ) IS
476 SELECT grant_guid
477 FROM fnd_grants grants
478 WHERE grants.grant_guid <> cp_grant_guid
479 AND grants.object_id = cp_object_id
480 AND grants.menu_id = cp_menu_id
481 AND grants.instance_type = 'INSTANCE'
482 AND grants.instance_pk1_value = TO_CHAR(cp_change_id)
483 -- Commented as PK2_Value for ENG_CHANGE in fnd_objects is NULL
484 -- AND grants.instance_pk2_value = TO_CHAR(cp_organization_id)
485 AND ((grants.grantee_type = 'USER' AND grants.grantee_key = cp_user_party_id_char ) OR
486 (grants.grantee_type = 'GROUP' AND grants.grantee_key = cp_group_party_id_char) OR
487 (grants.grantee_type = 'GLOBAL' AND grants.grantee_key = cp_global_party_id_char) OR
488 (grants.grantee_type = 'COMPANY' AND grants.grantee_key = cp_company_party_id_char)
489 )
490 AND start_date <= NVL(cp_end_date, start_date)
491 AND NVL(end_date,cp_start_date) >= cp_start_date;
492
493 l_token_tbl_two Error_Handler.Token_Tbl_Type;
494 l_token_tbl_one Error_Handler.Token_Tbl_Type;
495 l_grant_guid fnd_grants.grant_guid%TYPE;
496 l_temp_grant_guid fnd_grants.grant_guid%TYPE;
497
498 l_success VARCHAR2(999);
499
500 BEGIN
501 OPEN c_get_update_grantid
502 (cp_change_id => p_change_id
503 ,cp_organization_id => p_organization_id
504 ,cp_menu_id => p_internal_role_id
505 ,cp_object_id => G_FND_OBJECT_ID
506 ,cp_user_party_id_char => p_user_party_id_char
507 ,cp_group_party_id_char => p_group_party_id_char
508 ,cp_global_party_id_char => p_global_party_id_char
509 ,cp_company_party_id_char => p_company_party_id_char
510 ,cp_start_date => p_start_date
511 );
512 FETCH c_get_update_grantid INTO l_grant_guid;
513 IF c_get_update_grantid%FOUND THEN
514 --
515 -- there will be only one record with a given start date
516 -- check if the update will cause any overlaps
517 --
518 OPEN c_get_valid_update
519 (cp_grant_guid => l_grant_guid
520 ,cp_change_id => p_change_id
521 ,cp_organization_id => p_organization_id
522 ,cp_menu_id => p_internal_role_id
523 ,cp_object_id => G_FND_OBJECT_ID
524 ,cp_user_party_id_char => p_user_party_id_char
525 ,cp_group_party_id_char => p_group_party_id_char
526 ,cp_global_party_id_char => p_global_party_id_char
527 ,cp_company_party_id_char => p_company_party_id_char
531 FETCH c_get_valid_update INTO l_temp_grant_guid;
528 ,cp_start_date => p_start_date
529 ,cp_end_date => p_end_date
530 );
532 IF c_get_valid_update%FOUND THEN
533 --
534 -- overlap will occur after update
535 --
536 x_return_status := G_UPDATE_OVERLAP_ERROR;
537
538 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
539 l_token_tbl_two(1).token_name := 'START_DATE';
540 l_token_tbl_two(1).token_value := p_start_date;
541 l_token_tbl_two(2).token_name := 'END_DATE';
542 l_token_tbl_two(2).token_value := p_end_date;
543 error_handler.Add_Error_Message
544 ( p_message_name => 'ENG_CPI_OVERLAP_GRANT'
545 , p_application_id => 'ENG'
546 , p_message_text => NULL
547 , p_token_tbl => l_token_tbl_two
548 , p_message_type => 'E'
549 , p_row_identifier => p_transaction_id
550 , p_table_name => G_ERROR_TABLE_NAME
551 , p_entity_id => NULL
552 , p_entity_index => NULL
553 , p_entity_code => G_ERROR_ENTITY_CODE
554 );
555 END IF;
556 ELSE
557 -- update the grants
558 FND_GRANTS_PKG.Update_Grant
559 (p_api_version => 1.0
560 ,p_grant_guid => l_grant_guid
561 ,p_start_date => p_start_date
562 ,p_end_date => p_end_date
563 ,x_success => l_success
564 );
565 x_return_status := G_UPDATE_REC_DONE;
566 END IF; -- c_get_valid_update
567 CLOSE c_get_valid_update;
568 ELSE
569 -- no records found for validation
570 x_return_status := G_UPDATE_REC_NOT_FOUND;
571 IF p_transaction_type = 'UPDATE' THEN
572 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
573 l_token_tbl_one(1).token_name := 'TYPE';
574 l_token_tbl_one(1).token_value := p_transaction_type;
575 error_handler.Add_Error_Message
576 ( p_message_name => 'ENG_CPI_GRANT_NOT_FOUND'
577 , p_application_id => 'ENG'
578 , p_message_text => NULL
579 , p_token_tbl => l_token_tbl_one
580 , p_message_type => 'E'
581 , p_row_identifier => p_transaction_id
582 , p_table_name => G_ERROR_TABLE_NAME
583 , p_entity_id => NULL
584 , p_entity_index => NULL
585 , p_entity_code => G_ERROR_ENTITY_CODE
586 );
587 END IF;
588 END IF; -- p_transaction_type UPDATE
589 END IF; -- c_get_update_grantid
590 CLOSE c_get_update_grantid;
591
592 EXCEPTION
593 WHEN OTHERS THEN
594 IF c_get_update_grantid%ISOPEN THEN
595 CLOSE c_get_update_grantid;
596 END IF;
597 IF c_get_valid_update%ISOPEN THEN
598 CLOSE c_get_valid_update;
599 END IF;
600 END validate_update_grant;
601
602
603 PROCEDURE validate_insert_grant
604 (p_transaction_type IN VARCHAR2
605 ,p_transaction_id IN NUMBER
606 ,p_change_id IN NUMBER
607 ,p_organization_id IN NUMBER
608 ,p_internal_role_id IN NUMBER
609 ,p_internal_role_name IN VARCHAR2
610 ,p_grantee_type IN VARCHAR2
611 ,p_grantee_key IN VARCHAR2
612 ,p_user_party_id_char IN VARCHAR2
613 ,p_group_party_id_char IN VARCHAR2
614 ,p_global_party_id_char IN VARCHAR2
615 ,p_company_party_id_char IN VARCHAR2
616 ,p_start_date IN DATE
617 ,p_end_date IN DATE
618 ,x_return_status OUT NOCOPY NUMBER) IS
619 -- Start OF comments
620 -- API name : validate_insert_grant
621 -- TYPE : PRIVATE
622 -- Pre-reqs : NONE
623 --
624 -- FUNCTION : To check if the required grant is valid for insert
625 -- and inserts the record into fnd_grants if valid
626 -- NO ACTION IS PERFORMED ON eng_change_people_intf
627 --
628 -- Parameters:
629 -- IN : NONE
630 --
631 -- OUT : x_return_status NUMBER
632 -- Indicates the status of the record
633 -- -1 Record not found for update
634 -- -2 Record found for update but will cause overlap
635 -- 1 Record found and updated
636 --
637
638 CURSOR c_get_overlap_grantid
639 (cp_change_id IN NUMBER
640 ,cp_organization_id IN NUMBER
641 ,cp_menu_id IN NUMBER
642 ,cp_object_id IN NUMBER
643 ,cp_user_party_id_char IN VARCHAR2
644 ,cp_group_party_id_char IN VARCHAR2
645 ,cp_global_party_id_char IN VARCHAR2
646 ,cp_company_party_id_char IN VARCHAR2
647 ,cp_start_date IN DATE
648 ,cp_end_date IN DATE
649 ) IS
650 SELECT grant_guid
651 FROM fnd_grants grants
652 WHERE grants.object_id = cp_object_id
653 AND grants.menu_id = cp_menu_id
654 AND grants.instance_type = 'INSTANCE'
655 AND grants.instance_pk1_value = TO_CHAR(cp_change_id)
656 -- Commented as PK2_Value for ENG_CHANGE in fnd_objects is NULL
660 (grants.grantee_type = 'GLOBAL' AND grants.grantee_key = cp_global_party_id_char) OR
657 -- AND grants.instance_pk2_value = TO_CHAR(cp_organization_id)
658 AND ((grants.grantee_type = 'USER' AND grants.grantee_key = cp_user_party_id_char ) OR
659 (grants.grantee_type = 'GROUP' AND grants.grantee_key = cp_group_party_id_char) OR
661 (grants.grantee_type = 'COMPANY' AND grants.grantee_key = cp_company_party_id_char)
662 )
663 AND start_date <= NVL(cp_end_date, start_date)
664 AND NVL(end_date,cp_start_date) >= cp_start_date;
665
666
667 l_token_tbl_two Error_Handler.Token_Tbl_Type;
668 l_grant_guid fnd_grants.grant_guid%TYPE;
669 l_temp_grant_guid fnd_grants.grant_guid%TYPE;
670
671 l_success VARCHAR2(999);
672 l_errorcode NUMBER;
673
674 BEGIN
675 OPEN c_get_overlap_grantid
676 (cp_change_id => p_change_id
677 ,cp_organization_id => p_organization_id
678 ,cp_menu_id => p_internal_role_id
679 ,cp_object_id => G_FND_OBJECT_ID
680 ,cp_user_party_id_char => p_user_party_id_char
681 ,cp_group_party_id_char => p_group_party_id_char
682 ,cp_global_party_id_char => p_global_party_id_char
683 ,cp_company_party_id_char => p_company_party_id_char
684 ,cp_start_date => p_start_date
685 ,cp_end_date => p_end_date);
686
687 FETCH c_get_overlap_grantid INTO l_grant_guid;
688 IF c_get_overlap_grantid%FOUND THEN
689 -- overlap will occur with the current data
690 x_return_status := G_INSERT_OVERLAP_ERROR;
691 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
692 l_token_tbl_two(1).token_name := 'START_DATE';
693 l_token_tbl_two(1).token_value := p_start_date;
694 l_token_tbl_two(2).token_name := 'END_DATE';
695 l_token_tbl_two(2).token_value := p_end_date;
696 error_handler.Add_Error_Message
697 ( p_message_name => 'ENG_CPI_OVERLAP_GRANT'
698 , p_application_id => 'ENG'
699 , p_message_text => NULL
700 , p_token_tbl => l_token_tbl_two
701 , p_message_type => 'E'
702 , p_row_identifier => p_transaction_id
703 , p_table_name => G_ERROR_TABLE_NAME
704 , p_entity_id => NULL
705 , p_entity_index => NULL
706 , p_entity_code => G_ERROR_ENTITY_CODE
707 );
708 END IF;
709 ELSE
710 --
711 -- insert record into fnd_grants
712 --
713 FND_GRANTS_PKG.Grant_Function
714 (p_api_version => 1.0
715 ,p_menu_name => p_internal_role_name
716 ,p_object_name => G_FND_OBJECT_NAME
717 ,p_instance_type => 'INSTANCE'
718 ,p_instance_set_id => NULL
719 ,p_instance_pk1_value => TO_CHAR(p_change_id)
720 -- Passing NULL to as PK2_Value for ENG_CHANGE in fnd_objects is NULL
721 ,p_instance_pk2_value => NULL
722 ,p_instance_pk3_value => NULL
723 ,p_instance_pk4_value => NULL
724 ,p_instance_pk5_value => NULL
725 ,p_grantee_type => p_grantee_type
726 ,p_grantee_key => p_grantee_key
727 ,p_start_date => p_start_date
728 ,p_end_date => p_end_date
729 ,p_program_name => G_PACKAGE_NAME
730 ,p_program_tag => NULL
731 ,p_parameter1 => NULL
732 ,p_parameter2 => NULL
733 ,p_parameter3 => NULL
734 ,p_parameter4 => NULL
735 ,p_parameter5 => NULL
736 ,p_parameter6 => NULL
737 ,p_parameter7 => NULL
738 ,p_parameter8 => NULL
739 ,p_parameter9 => NULL
740 ,p_parameter10 => NULL
741 ,p_ctx_secgrp_id => -1
742 ,p_ctx_resp_id => -1
743 ,p_ctx_resp_appl_id => -1
744 ,p_ctx_org_id => -1
745 ,x_grant_guid => l_temp_grant_guid
746 ,x_success => l_success
747 ,x_errorcode => l_errorcode
748 );
749 x_return_status := G_INSERT_REC_DONE;
750 END IF; -- c_get_overlap_grantid
751 CLOSE c_get_overlap_grantid;
752
753 EXCEPTION
754 WHEN OTHERS THEN
755 IF c_get_overlap_grantid%ISOPEN THEN
756 CLOSE c_get_overlap_grantid;
757 END IF;
758 END validate_insert_grant;
759
760
761 PROCEDURE Validate_No_Grant_Overlap ( x_retcode OUT NOCOPY NUMBER) IS
762 -- Start OF comments
763 -- API name : Validate No Grant Overlap
764 -- TYPE : Private (called by load_interface_lines)
765 -- Pre-reqs : Data validated for all possible scenarios (but for grants)
766 -- FUNCTION : Validate grant overlap.
767 -- Take all records to be deleted and process them
771 CURSOR c_get_cpi_records IS
768 -- Take all the records to be updated and update grants
769 -- Finally insert new grants
770 --
772 SELECT change_id, organization_id, grantee_party_id, grantee_type,
773 start_date, end_date, transaction_id, internal_role_id, transaction_type,
774 internal_role_name,
775 DECODE(grantee_type, 'USER', 'HZ_PARTY:'||TO_CHAR(grantee_party_id),
776 'GROUP','HZ_GROUP:'||TO_CHAR(grantee_party_id),
777 -- bug: 3460466
778 -- All Users is now represented by grantee_key = 'GLOBAL' in fnd_grants
779 -- 'GLOBAL','HZ_GLOBAL:'||TO_CHAR(grantee_party_id),
780 'GLOBAL',grantee_type,
781 'HZ_COMPANY:'||TO_CHAR(grantee_party_id)) grantee_key,
782 DECODE(transaction_type, 'CREATE', ORDER_BY_CREATE,
783 'UPDATE', ORDER_BY_UPDATE,
784 'SYNC', ORDER_BY_SYNC,
785 'DELETE', ORDER_BY_DELETE,
786 ORDER_BY_OTHERS) trans_type
787 FROM eng_change_people_intf
788 WHERE data_set_id = G_DATA_SET_ID
789 AND process_status = G_PS_IN_PROCESS
790 ORDER BY trans_type, transaction_id;
791
792 CURSOR c_get_delete_grantid
793 (cp_change_id IN NUMBER
794 ,cp_organization_id IN NUMBER
795 ,cp_menu_id IN NUMBER
796 ,cp_object_id IN NUMBER
797 ,cp_user_party_id_char IN VARCHAR2
798 ,cp_group_party_id_char IN VARCHAR2
799 ,cp_global_party_id_char IN VARCHAR2
800 ,cp_company_party_id_char IN VARCHAR2
801 ,cp_start_date IN DATE
802 ,cp_end_date IN DATE
803 ) IS
804 SELECT grant_guid
805 FROM fnd_grants grants
806 WHERE grants.object_id = G_FND_OBJECT_ID
807 AND grants.menu_id = cp_menu_id
808 AND grants.instance_type = 'INSTANCE'
809 AND grants.instance_pk1_value = TO_CHAR(cp_change_id)
810 -- Commented as PK2_Value for ENG_CHANGE in fnd_objects is NULL
811 -- AND grants.instance_pk2_value = TO_CHAR(cp_organization_id)
812 AND ((grants.grantee_type = 'USER' AND grants.grantee_key = cp_user_party_id_char ) OR
813 (grants.grantee_type = 'GROUP' AND grants.grantee_key = cp_group_party_id_char) OR
814 (grants.grantee_type = 'GLOBAL' AND grants.grantee_key = cp_global_party_id_char) OR
815 (grants.grantee_type = 'COMPANY' AND grants.grantee_key = cp_company_party_id_char)
816 )
817 AND start_date = cp_start_date
818 AND ((end_date IS NULL AND cp_end_date is NULL) OR (end_date = cp_end_date));
819
820 l_token_tbl_none Error_Handler.Token_Tbl_Type;
821 l_token_tbl_one Error_Handler.Token_Tbl_Type;
822
823 l_user_party_id_char VARCHAR2(100);
824 l_group_party_id_char VARCHAR2(100);
825 l_global_party_id_char VARCHAR2(100);
826 l_company_party_id_char VARCHAR2(100);
827
828
829 l_grant_guid fnd_grants.grant_guid%TYPE;
830 l_temp_grant_guid fnd_grants.grant_guid%TYPE;
831 l_grant_guid_count NUMBER := 0;
832
833 l_record_count NUMBER := 0;
834 l_return_status NUMBER;
835 l_success VARCHAR2(999);
836
837 l_program_name VARCHAR2(99) := 'VALIDATE_NO_GRANT_OVERLAP';
838 l_boolean_delete boolean := TRUE;
839 l_boolean_create boolean := TRUE;
840 l_boolean_update boolean := TRUE;
841 l_boolean_sync boolean := TRUE;
842
843 BEGIN
844
845 FOR cr in c_get_cpi_records LOOP
846 IF cr.grantee_type = 'USER' THEN
847 l_user_party_id_char := 'HZ_PARTY:'||TO_CHAR(cr.grantee_party_id);
848 l_group_party_id_char := NULL;
849 l_global_party_id_char := NULL;
850 l_company_party_id_char := NULL;
851 ELSIF cr.grantee_type = 'GROUP' THEN
852 l_user_party_id_char := NULL;
853 l_group_party_id_char := 'HZ_GROUP:'||TO_CHAR(cr.grantee_party_id);
854 l_global_party_id_char := NULL;
855 l_company_party_id_char := NULL;
856 ELSIF cr.grantee_type = 'GLOBAL' THEN
857 l_user_party_id_char := NULL;
858 l_group_party_id_char := NULL;
859 -- bug: 3460466
860 -- All Users is now represented by grantee_key = 'GLOBAL' in fnd_grants
861 -- l_global_party_id_char := 'HZ_GLOBAL:'||TO_CHAR(cr.grantee_party_id);
862 l_global_party_id_char := cr.grantee_type;
863 l_company_party_id_char := NULL;
864 ELSIF cr.grantee_type = 'COMPANY' THEN
865 l_user_party_id_char := NULL;
866 l_group_party_id_char := NULL;
867 l_global_party_id_char := NULL;
868 l_company_party_id_char := 'HZ_COMPANY:'||TO_CHAR(cr.grantee_party_id);
869 ELSE
870 l_user_party_id_char := NULL;
871 l_group_party_id_char := NULL;
872 l_global_party_id_char := NULL;
873 l_company_party_id_char := NULL;
874 END IF;
875 IF cr.transaction_type = 'DELETE' THEN
876 ----------------------------
877 -- delete records first --
878 ----------------------------
879 OPEN c_get_delete_grantid
880 (cp_change_id => cr.change_id
881 ,cp_organization_id => cr.organization_id
882 ,cp_menu_id => cr.internal_role_id
883 ,cp_object_id => G_FND_OBJECT_ID
887 ,cp_company_party_id_char => l_company_party_id_char
884 ,cp_user_party_id_char => l_user_party_id_char
885 ,cp_group_party_id_char => l_group_party_id_char
886 ,cp_global_party_id_char => l_global_party_id_char
888 ,cp_start_date => cr.start_date
889 ,cp_end_date => cr.end_date);
890 FETCH c_get_delete_grantid INTO l_grant_guid;
891
892 IF c_get_delete_grantid%FOUND THEN
893 FND_GRANTS_PKG.Revoke_Grant
894 (p_api_version => 1.0
895 ,p_grant_guid => l_grant_guid
896 ,x_success => l_success
897 ,x_errorcode => l_return_status
898 );
899 UPDATE eng_change_people_intf
900 SET process_status = G_PS_SUCCESS
901 WHERE transaction_id = cr.transaction_id;
902 ELSE
903 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
904 l_token_tbl_one(1).token_name := 'TYPE';
905 l_token_tbl_one(1).token_value := cr.transaction_type;
906 error_handler.Add_Error_Message
907 ( p_message_name => 'ENG_CPI_GRANT_NOT_FOUND'
908 , p_application_id => 'ENG'
909 , p_message_text => NULL
910 , p_token_tbl => l_token_tbl_one
911 , p_message_type => 'E'
912 , p_row_identifier => cr.transaction_id
913 , p_table_name => G_ERROR_TABLE_NAME
914 , p_entity_id => NULL
915 , p_entity_index => NULL
916 , p_entity_code => G_ERROR_ENTITY_CODE
917 );
918 END IF;
919 UPDATE eng_change_people_intf
920 SET process_status = G_PS_ERROR
921 WHERE transaction_id = cr.transaction_id;
922 END IF; -- c_get_delete_grantid
923 CLOSE c_get_delete_grantid;
924
925 ELSIF cr.transaction_type = 'UPDATE' THEN
926 ----------------------------
927 -- check for update now --
928 ----------------------------
929 validate_update_grant
930 (p_transaction_type => cr.transaction_type
931 ,p_transaction_id => cr.transaction_id
932 ,p_change_id => cr.change_id
933 ,p_organization_id => cr.organization_id
934 ,p_internal_role_id => cr.internal_role_id
935 ,p_user_party_id_char => l_user_party_id_char
936 ,p_group_party_id_char => l_group_party_id_char
937 ,p_global_party_id_char => l_global_party_id_char
938 ,p_company_party_id_char => l_company_party_id_char
939 ,p_start_date => cr.start_date
940 ,p_end_date => cr.end_date
941 ,x_return_status => l_return_status
942 );
943 IF l_return_status = G_UPDATE_REC_DONE THEN
944 -- record successfully updated
945 UPDATE eng_change_people_intf
946 SET process_status = G_PS_SUCCESS
947 WHERE transaction_id = cr.transaction_id;
948 ELSIF l_return_status = G_UPDATE_REC_NOT_FOUND THEN
949 -- no record found for overlap
950 UPDATE eng_change_people_intf
951 SET process_status = G_PS_ERROR
952 WHERE transaction_id = cr.transaction_id;
953 ELSIF l_return_status = G_UPDATE_OVERLAP_ERROR THEN
954 -- overlap will occur if update is done
955 UPDATE eng_change_people_intf
956 SET process_status = G_PS_ERROR
957 WHERE transaction_id = cr.transaction_id;
958 END IF;
959
960 ELSIF cr.transaction_type = 'SYNC' THEN
961 ------------------------------------
962 -- check for SYNC opetaion --
963 -- (first UPDATE and then INSERT --
964 ------------------------------------
965 validate_update_grant
966 (p_transaction_type => cr.transaction_type
967 ,p_transaction_id => cr.transaction_id
968 ,p_change_id => cr.change_id
969 ,p_organization_id => cr.organization_id
970 ,p_internal_role_id => cr.internal_role_id
971 ,p_user_party_id_char => l_user_party_id_char
972 ,p_group_party_id_char => l_group_party_id_char
973 ,p_global_party_id_char => l_global_party_id_char
974 ,p_company_party_id_char => l_company_party_id_char
975 ,p_start_date => cr.start_date
976 ,p_end_date => cr.end_date
977 ,x_return_status => l_return_status
978 );
979 IF l_return_status = G_UPDATE_REC_DONE THEN
980 -- record successfully updated
981 UPDATE eng_change_people_intf
982 SET process_status = G_PS_SUCCESS
983 WHERE transaction_id = cr.transaction_id;
984 ELSIF l_return_status = G_UPDATE_OVERLAP_ERROR THEN
985 -- overlap will occur if update is done
986 UPDATE eng_change_people_intf
987 SET process_status = G_PS_ERROR
988 WHERE transaction_id = cr.transaction_id;
989 ELSIF l_return_status = G_UPDATE_REC_NOT_FOUND THEN
990 -- no record found for overlap
991 -- now insert the record.
992 validate_insert_grant
993 (p_transaction_type => cr.transaction_type
994 ,p_transaction_id => cr.transaction_id
995 ,p_change_id => cr.change_id
996 ,p_organization_id => cr.organization_id
997 ,p_internal_role_id => cr.internal_role_id
998 ,p_internal_role_name => cr.internal_role_name
999 ,p_grantee_type => cr.grantee_type
1003 ,p_global_party_id_char => l_global_party_id_char
1000 ,p_grantee_key => cr.grantee_key
1001 ,p_user_party_id_char => l_user_party_id_char
1002 ,p_group_party_id_char => l_group_party_id_char
1004 ,p_company_party_id_char => l_company_party_id_char
1005 ,p_start_date => cr.start_date
1006 ,p_end_date => cr.end_date
1007 ,x_return_status => l_return_status
1008 );
1009 IF l_return_status = G_INSERT_REC_DONE THEN
1010 -- record successfully inserted
1011 UPDATE eng_change_people_intf
1012 SET process_status = G_PS_SUCCESS
1013 WHERE transaction_id = cr.transaction_id;
1014 ELSIF l_return_status = G_INSERT_OVERLAP_ERROR THEN
1015 -- insert overlap error
1016 UPDATE eng_change_people_intf
1017 SET process_status = G_PS_ERROR
1018 WHERE transaction_id = cr.transaction_id;
1019 END IF;
1020 END IF;
1021
1022 ELSIF cr.transaction_type = 'CREATE' THEN
1023 ----------------------------
1024 -- check for create now --
1025 ----------------------------
1026 validate_insert_grant
1027 (p_transaction_type => cr.transaction_type
1028 ,p_transaction_id => cr.transaction_id
1029 ,p_change_id => cr.change_id
1030 ,p_organization_id => cr.organization_id
1031 ,p_internal_role_id => cr.internal_role_id
1032 ,p_internal_role_name => cr.internal_role_name
1033 ,p_grantee_type => cr.grantee_type
1034 ,p_grantee_key => cr.grantee_key
1035 ,p_user_party_id_char => l_user_party_id_char
1036 ,p_group_party_id_char => l_group_party_id_char
1037 ,p_global_party_id_char => l_global_party_id_char
1038 ,p_company_party_id_char => l_company_party_id_char
1039 ,p_start_date => cr.start_date
1040 ,p_end_date => cr.end_date
1041 ,x_return_status => l_return_status
1042 );
1043 IF l_return_status = G_INSERT_REC_DONE THEN
1044 -- record successfully inserted
1045 UPDATE eng_change_people_intf
1046 SET process_status = G_PS_SUCCESS
1047 WHERE transaction_id = cr.transaction_id;
1048 ELSIF l_return_status = G_INSERT_OVERLAP_ERROR THEN
1049 -- insert overlap error
1050 UPDATE eng_change_people_intf
1051 SET process_status = G_PS_ERROR
1052 WHERE transaction_id = cr.transaction_id;
1053 END IF;
1054
1055 END IF; -- cr.transaction_type
1056 l_record_count := l_record_count + 1;
1057 IF l_record_count > G_BATCH_SIZE THEN
1058 l_record_count := 1;
1059 COMMIT;
1060 END IF;
1061 check_and_write_log (x_retcode => x_retcode);
1062 IF (x_retcode = RETCODE_ERROR) THEN
1063 RETURN;
1064 END IF;
1065 END LOOP; -- c_get_cpi_records
1066 COMMIT;
1067
1068 EXCEPTION
1069 WHEN OTHERS THEN
1070 IF c_get_cpi_records%ISOPEN THEN
1071 CLOSE c_get_cpi_records;
1072 END IF;
1073 IF c_get_delete_grantid%ISOPEN THEN
1074 CLOSE c_get_delete_grantid;
1075 END IF;
1076
1077 END Validate_No_Grant_Overlap;
1078
1079 ---------------------------------------------
1080 -- PROCEDURES AND FUNCTIONS --
1081 ---------------------------------------------
1082
1083 FUNCTION get_curr_dataset_id RETURN NUMBER IS
1084 -- Start OF comments
1085 -- API name : Load Interfance Lines
1086 -- TYPE : Public (called by SQL Loader)
1087 -- Pre-reqs : None
1088 -- FUNCTION : Process and Load interfance lines into FND_GRANTS.
1089 -- Errors are populated in MTL_INTERFACE_ERRORS
1090 BEGIN
1091 IF G_CURR_DATASET_ID = -1 THEN
1092 SELECT ENG_CPI_DATASET_ID_S.NEXTVAL
1093 INTO G_CURR_DATASET_ID
1094 FROM DUAL;
1095 END IF;
1096 RETURN G_CURR_DATASET_ID;
1097 EXCEPTION
1098 WHEN OTHERS THEN
1099 G_CURR_DATASET_ID := -2;
1100 END get_curr_dataset_id;
1101
1102
1103 PROCEDURE load_interface_lines
1104 (
1105 x_retcode IN OUT NOCOPY VARCHAR2,
1106 x_errbuff IN OUT NOCOPY VARCHAR2,
1107 p_data_set_id IN NUMBER,
1108 p_bulk_batch_size IN NUMBER ,
1109 p_delete_lines IN NUMBER ,
1110 p_debug_mode IN NUMBER ,
1111 p_log_mode IN NUMBER
1112 ) IS
1113
1114 -- Start OF comments
1115 -- API name : Load Interfance Lines
1116 -- TYPE : Public (called by Concurrent Program)
1117 -- Pre-reqs : None
1118 -- FUNCTION : Process and Load interfance lines into FND_GRANTS.
1119 -- Errors are populated in MTL_INTERFACE_ERRORS
1120
1121
1122 --Currently, assume that the user who submits the 'Change People Import'
1123 --is always Internal user. So, can join with PER_ALL_PEOPLE_F to figure
1124 --out the party id.
1125 CURSOR c_user_party_id (cp_user_id IN NUMBER) IS
1126 SELECT employee.party_id, first_name ||' '|| last_name name
1127 FROM per_all_people_f employee, fnd_user users
1128 WHERE users.user_id = cp_user_id
1132 SELECT COUNT(*)
1129 AND employee.person_id = users.employee_id;
1130
1131 CURSOR c_count_cpi_lines (cp_data_set_id IN NUMBER) IS
1133 FROM eng_change_people_intf
1134 WHERE data_set_id = cp_data_set_id
1135 AND process_status = G_PS_TO_BE_PROCESSED;
1136
1137 CURSOR c_get_trans_id_limits (cp_data_set_id IN NUMBER) IS
1138 SELECT MIN(transaction_id), MAX(transaction_id)
1139 FROM eng_change_people_intf
1140 WHERE data_set_id = cp_data_set_id
1141 AND process_status = G_PS_TO_BE_PROCESSED;
1142
1143 CURSOR c_err_dates IS
1144 SELECT transaction_id,start_date,end_date
1145 FROM eng_change_people_intf
1146 WHERE data_set_id = G_DATA_SET_ID
1147 AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1148 AND process_status = G_PS_IN_PROCESS
1149 AND start_date > NVL(end_date,(start_date + 1));
1150
1151 --
1152 -- Select records to flag missing or invalid Transaction_Types
1153 --
1154 CURSOR c_err_transaction_type IS
1155 SELECT transaction_id, transaction_type
1156 FROM eng_change_people_intf
1157 WHERE data_set_id = G_DATA_SET_ID
1158 AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1159 AND process_status = G_PS_IN_PROCESS
1160 AND transaction_type NOT IN ('CREATE', 'UPDATE', 'DELETE', 'SYNC');
1161
1162
1163 --
1164 -- Select records with missing/invalid grantee type
1165 --
1166 CURSOR c_err_grantee_type IS
1167 SELECT transaction_id, grantee_type
1168 FROM eng_change_people_intf
1169 WHERE data_set_id = G_DATA_SET_ID
1170 AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1171 AND process_status = G_PS_IN_PROCESS
1172 AND (grantee_type IS NULL OR grantee_type NOT IN ('USER', 'GROUP', 'COMPANY', 'GLOBAL'));
1173
1174 --
1175 -- Select records to flag missing or invalid grantee_party_id
1176 --
1177 CURSOR c_err_grantee_id IS
1178 SELECT transaction_id, grantee_party_id, grantee_name, grantee_type
1179 FROM eng_change_people_intf
1180 WHERE data_set_id = G_DATA_SET_ID
1181 AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1182 AND process_status = G_PS_IN_PROCESS
1183 AND grantee_party_id IS NULL;
1184
1185 --
1186 -- Select records to flag missing or invalid role_id
1187 --
1188 CURSOR c_err_role_id IS
1189 SELECT transaction_id, internal_role_id, display_role_name, internal_role_name
1190 FROM eng_change_people_intf
1191 WHERE data_set_id = G_DATA_SET_ID
1192 AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1193 AND process_status = G_PS_IN_PROCESS
1194 AND internal_role_id IS NULL;
1195
1196 --
1197 -- Select records to flag missing or invalid organization_id
1198 --
1199 CURSOR c_err_org_id IS
1200 SELECT transaction_id, organization_id, organization_code
1201 FROM eng_change_people_intf
1202 WHERE data_set_id = G_DATA_SET_ID
1203 AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1204 AND process_status = G_PS_IN_PROCESS
1205 AND organization_id IS NULL;
1206
1207
1208 --
1209 -- Select records to flag missing or invalid Change_Mgmt_Types
1210 --
1211 -- Updated the cursor to get the valid change_mgmt_type_codes from
1212 -- the ENG_CHANGE_ORDER_TYPES_VL
1213 CURSOR c_err_chg_mgmt_type_code IS
1214 SELECT transaction_id, change_mgmt_type_code
1215 FROM eng_change_people_intf
1216 WHERE data_set_id = G_DATA_SET_ID
1217 AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1218 AND process_status = G_PS_IN_PROCESS
1219 AND (change_mgmt_type_code IS NULL OR
1220 --commenting out the following lines as ENG_CHANGE_MGMT_TYPES is obsoleted
1221 --change_mgmt_type_code NOT IN (SELECT CHANGE_MGMT_TYPE_CODE FROM ENG_CHANGE_MGMT_TYPES));
1222 change_mgmt_type_code NOT IN (SELECT CHANGE_MGMT_TYPE_CODE FROM ENG_CHANGE_ORDER_TYPES_VL
1223 WHERE TYPE_CLASSIFICATION = 'CATEGORY'));
1224
1225 --
1226 -- Select records for valid change numbers
1227 --
1228 CURSOR c_err_change_id IS
1229 SELECT transaction_id,organization_code,change_mgmt_type_code,change_notice
1230 FROM eng_change_people_intf
1231 WHERE data_set_id = G_DATA_SET_ID
1232 AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1233 AND process_status = G_PS_IN_PROCESS
1234 AND change_id IS NULL;
1235
1236 --
1237 -- Check whether the user can revoke/give grants
1238 --
1239 CURSOR c_get_grant_privileges (cp_change_notice IN NUMBER
1240 ,cp_organization_id IN NUMBER) IS
1241 SELECT change_notice
1242 FROM ENG_LOGIN_ACCESS_CHANGES
1243 WHERE change_notice = cp_change_notice
1244 AND organization_id = cp_organization_id;
1245
1246 --
1247 -- Check whether the user can revoke/give grants
1248 --
1249 CURSOR c_get_utl_file_dir IS
1250 SELECT VALUE
1251 FROM V$PARAMETER
1252 WHERE NAME = 'utl_file_dir';
1253
1254 l_token_tbl_none Error_Handler.Token_Tbl_Type;
1255 l_token_tbl_one Error_Handler.Token_Tbl_Type;
1256 l_token_tbl_two Error_Handler.Token_Tbl_Type;
1260 l_login_party_name VARCHAR2(240);
1257 l_token_tbl_three Error_Handler.Token_Tbl_Type;
1258
1259 l_login_party_id hz_parties.party_id%TYPE;
1261 l_cpi_lines_count PLS_INTEGER;
1262 l_loop_count PLS_INTEGER;
1263 l_transaction_id_min PLS_INTEGER;
1264 l_transaction_id_max PLS_INTEGER;
1265
1266 l_column_name VARCHAR2(99);
1267 l_transaction_id eng_change_people_intf.transaction_id%TYPE;
1268 l_msg_name VARCHAR2(99);
1269 l_msg_text VARCHAR2(999) := NULL;
1270 l_msg_type VARCHAR2(10) := 'E';
1271 l_sysdate DATE;
1272
1273 l_change_id NUMBER;
1274 l_change_notice VARCHAR2(10);
1275 l_retcode VARCHAR2(10);
1276 l_errbuff VARCHAR2(999);
1277
1278 l_log_output_dir VARCHAR2(200);
1279 l_log_return_status VARCHAR2(99);
1280 l_log_mesg_token_tbl ERROR_HANDLER.Mesg_Token_Tbl_Type;
1281
1282 l_program_name VARCHAR2(30) := 'LOAD_INTERFACE_LINES';
1283
1284 BEGIN
1285 IF (NVL(fnd_profile.value('CONC_REQUEST_ID'), 0) <> 0) THEN
1286 g_concReq_valid_flag := TRUE;
1287 END IF;
1288
1289 IF (g_concReq_valid_flag ) THEN
1290 FND_FILE.put_line(FND_FILE.LOG, ' ******** New Log ******** ');
1291 END IF;
1292 ERROR_HANDLER.initialize();
1293 ERROR_HANDLER.set_bo_identifier(G_BO_IDENTIFIER);
1294
1295 IF p_log_mode = LOG_INTO_FILE_ONLY THEN
1296 ERROR_HANDLER.Set_Debug('Y');
1297 G_TABLE_LOG := FALSE;
1298 ELSIF p_log_mode = LOG_INTO_FILE_AND_TABLE THEN
1299 ERROR_HANDLER.Set_Debug('Y');
1300 G_TABLE_LOG := TRUE;
1301 ELSIF p_log_mode = LOG_INTO_TABLE_ONLY THEN
1302 ERROR_HANDLER.Set_Debug('N');
1303 G_TABLE_LOG := TRUE;
1304 ELSE
1305 ERROR_HANDLER.Set_Debug('N');
1306 G_TABLE_LOG := FALSE;
1307 END IF;
1308 -- Bug: 3324531
1309 -- removed references to bom_globals
1310 IF p_debug_mode = DEBUG_MODE_DEBUG THEN
1311 G_DEBUG_MODE := DEBUG_MODE_DEBUG;
1312 ELSE
1313 -- default debug mode is set to log errors only in Phase I
1314 G_DEBUG_MODE := DEBUG_MODE_ERROR;
1315 END IF; -- p_debug_mode
1316
1317 IF ERROR_HANDLER.Get_Debug = 'Y' THEN
1318 -- intialise the file names, etc
1319 OPEN c_get_utl_file_dir;
1320 FETCH c_get_utl_file_dir INTO l_log_output_dir;
1321 IF c_get_utl_file_dir%FOUND THEN
1322 ------------------------------------------------------
1323 -- Trim to get only the first directory in the list --
1324 ------------------------------------------------------
1325 IF INSTR(l_log_output_dir,',') <> 0 THEN
1326 l_log_output_dir := SUBSTR(l_log_output_dir, 1, INSTR(l_log_output_dir, ',') - 1);
1327 END IF;
1328 G_ERROR_FILE_NAME := G_ERROR_TABLE_NAME||'.'||fnd_profile.value('CONC_REQUEST_ID')||'.err';
1329 error_handler.Open_Debug_Session(
1330 p_debug_filename => G_ERROR_FILE_NAME
1331 ,p_output_dir => l_log_output_dir
1332 ,x_return_status => l_log_return_status
1333 ,x_error_mesg => l_errbuff
1334 );
1335
1336
1337 IF (l_log_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1338 -- unable to open error log file
1339 ERROR_HANDLER.Add_Error_Message
1340 (p_message_text => l_errbuff
1341 ,p_message_type => 'E'
1342 ,p_entity_code => G_ERROR_ENTITY_CODE
1343 );
1344
1345 x_retcode := RETCODE_ERROR;
1346 x_errbuff := 'ENG_CPI_INVALID_LOG_FILE';
1347 RETURN;
1348 END IF;
1349 ELSE
1350 x_retcode := RETCODE_ERROR;
1351 x_errbuff := 'ENG_CPI_INVALID_LOG_DIR';
1352 RETURN;
1353 END IF;
1354 CLOSE c_get_utl_file_dir;
1355 END IF; -- error_handler.get_debug.
1356
1357 -- the values are chosen from the FND_GLOBALS
1358 G_USER_ID := FND_GLOBAL.user_id ;
1359 G_LOGIN_ID := FND_GLOBAL.login_id ;
1360 G_PROG_APPID := FND_GLOBAL.prog_appl_id ;
1361 G_PROG_ID := FND_GLOBAL.conc_program_id ;
1362 G_REQUEST_ID := FND_GLOBAL.conc_request_id ;
1363
1364 -- check whether the logged in user is a valid user
1365 OPEN c_user_party_id(cp_user_id => G_USER_ID);
1366 FETCH c_user_party_id INTO l_login_party_id, l_login_party_name;
1367 IF c_user_party_id%NOTFOUND THEN
1368 error_handler.Add_Error_Message
1369 ( p_message_name => 'ENG_CPI_INVALID_LOGIN'
1370 , p_application_id => 'ENG'
1371 , p_message_text => NULL
1372 , p_token_tbl => l_token_tbl_none
1373 , p_message_type => 'E'
1374 , p_row_identifier => NULL
1375 , p_table_name => G_ERROR_TABLE_NAME
1376 , p_entity_id => NULL
1377 , p_entity_index => NULL
1378 , p_entity_code => G_ERROR_ENTITY_CODE
1379 );
1380 x_retcode := RETCODE_ERROR;
1381 x_errbuff := 'ENG_CPI_INVALID_LOGIN';
1382
1383 RETURN;
1384 ELSE
1385 initialize_access_changes (p_login_person_id => l_login_party_id
1386 ,x_retcode => x_retcode);
1387 IF x_retcode = RETCODE_ERROR THEN
1388 x_errbuff := 'ENG_CPI_ERR_INIT_CHANGES';
1389 RETURN;
1390 END IF;
1391
1392 END IF;
1393 CLOSE c_user_party_id;
1394 -- END here
1395
1399
1396 initialize_fnd_object_id(p_object_name => G_FND_OBJECT_NAME);
1397 initialize_roles();
1398 initialize_all_users();
1400 -------------------------------------------
1401 -- All required values are initialized
1402 -- Go ahead with validating the records
1403 -------------------------------------------
1404
1405 G_DATA_SET_ID := p_data_set_id;
1406
1407 OPEN c_count_cpi_lines(cp_data_set_id => G_DATA_SET_ID);
1408 FETCH c_count_cpi_lines INTO l_cpi_lines_count;
1409 CLOSE c_count_cpi_lines;
1410 IF l_cpi_lines_count=0 THEN
1411 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
1412 error_handler.Add_Error_Message
1413 ( p_message_name => 'ENG_CPI_NO_LINES'
1414 , p_application_id => 'ENG'
1415 , p_message_text => NULL
1416 , p_token_tbl => l_token_tbl_none
1417 , p_message_type => 'E'
1418 , p_row_identifier => NULL
1419 , p_table_name => G_ERROR_TABLE_NAME
1420 , p_entity_id => NULL
1421 , p_entity_index => NULL
1422 , p_entity_code => G_ERROR_ENTITY_CODE
1423 );
1424 END IF;
1425 x_retcode := RETCODE_ERROR;
1426 x_errbuff := 'ENG_CPI_NO_LINES';
1427 RETURN;
1428 END IF;
1429
1430 -- initialize the loop counter values
1431 OPEN c_get_trans_id_limits (cp_data_set_id => G_DATA_SET_ID);
1432 FETCH c_get_trans_id_limits INTO l_transaction_id_min, l_transaction_id_max;
1433 CLOSE c_get_trans_id_limits;
1434 G_BATCH_SIZE := NVL(p_bulk_batch_size, G_BATCH_SIZE);
1435 l_loop_count := CEIL( (l_transaction_id_max - l_transaction_id_min + 1) / G_BATCH_SIZE );
1436 G_FROM_LINE_NUMBER := l_transaction_id_min;
1437 ---------------------------
1438 -- all variables set
1439 -- start the loop now
1440 ---------------------------
1441
1442 FOR l_batch_loop_counter IN 1..l_loop_count LOOP
1443 IF (l_transaction_id_max > (G_FROM_LINE_NUMBER + G_BATCH_SIZE -1)) THEN
1444 G_TO_LINE_NUMBER := G_FROM_LINE_NUMBER + G_BATCH_SIZE - 1;
1445 ELSE
1446 G_TO_LINE_NUMBER := l_transaction_id_max;
1447 END IF;
1448 -- call various validation routines
1449 -- the sequence of the calling valiadations does matter
1450 -- as the first error is reported and the record is flagged as error
1451 --
1452 -- setting up the status for record processing
1453 l_sysdate := SYSDATE;
1454 UPDATE eng_change_people_intf
1455 SET
1456 -- login_user_id = G_USER_ID,
1457 -- login_party_id = l_login_party_id,
1458 creation_date = l_sysdate,
1459 start_date = NVL(start_date, l_sysdate),
1460 transaction_type = UPPER(transaction_type),
1461 change_mgmt_type_code = UPPER(change_mgmt_type_code),
1462 grantee_type = UPPER(grantee_type),
1463 process_status = G_PS_IN_PROCESS
1464 WHERE data_set_id = G_DATA_SET_ID
1465 AND process_status = G_PS_TO_BE_PROCESSED
1466 AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER;
1467
1468 -- check the correct start and dates in the records
1469 FOR cr IN c_err_dates LOOP
1470 UPDATE eng_change_people_intf
1471 SET process_status = G_PS_ERROR
1472 WHERE transaction_id = cr.transaction_id;
1473 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
1474 l_msg_name := 'ENG_CPI_INVALID_DATES';
1475 l_token_tbl_two(1).token_name := 'START_DATE';
1476 l_token_tbl_two(1).token_value := cr.start_date;
1477 l_token_tbl_two(2).token_name := 'END_DATE';
1478 l_token_tbl_two(2).token_value := cr.end_date;
1479 error_handler.Add_Error_Message
1480 ( p_message_name => l_msg_name
1481 , p_application_id => 'ENG'
1482 , p_message_text => NULL
1483 , p_token_tbl => l_token_tbl_two
1484 , p_message_type => 'E'
1485 , p_row_identifier => cr.transaction_id
1486 , p_table_name => G_ERROR_TABLE_NAME
1487 , p_entity_id => NULL
1488 , p_entity_index => NULL
1489 , p_entity_code => G_ERROR_ENTITY_CODE
1490 );
1491 END IF;
1492 check_and_write_log (x_retcode => x_retcode);
1493 IF x_retcode = RETCODE_ERROR THEN
1494 RETURN;
1495 END IF;
1496 END LOOP; -- error Dates
1497
1498 -- find the error records with invalid transaction_type
1499 -- valid transaction_types are CREATE, UPDATE, SYNC, DELETE
1500 FOR cr IN c_err_transaction_type LOOP
1501 UPDATE eng_change_people_intf
1502 SET process_status = G_PS_ERROR
1503 WHERE transaction_id = cr.transaction_id;
1504 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
1505 IF ( cr.transaction_type IS NULL ) THEN
1506 l_msg_name := 'ENG_CPI_MISSING_VALUE';
1507 l_token_tbl_one(1).token_name := 'VALUE';
1508 l_token_tbl_one(1).token_value := 'TRANSACTION TYPE';
1509
1510 error_handler.Add_Error_Message
1511 ( p_message_name => l_msg_name
1512 , p_application_id => 'ENG'
1513 , p_message_text => NULL
1514 , p_token_tbl => l_token_tbl_one
1515 , p_message_type => 'E'
1516 , p_row_identifier => cr.transaction_id
1517 , p_table_name => G_ERROR_TABLE_NAME
1518 , p_entity_id => NULL
1519 , p_entity_index => NULL
1520 , p_entity_code => G_ERROR_ENTITY_CODE
1521 );
1522 ELSE
1523 l_msg_name := 'ENG_CPI_INVALID_VALUE2';
1524 l_token_tbl_two(1).token_name := 'NAME';
1528 error_handler.Add_Error_Message
1525 l_token_tbl_two(1).token_value := 'TRANSACTION TYPE';
1526 l_token_tbl_two(2).token_name := 'VALUE';
1527 l_token_tbl_two(2).token_value := cr.transaction_type;
1529 ( p_message_name => l_msg_name
1530 , p_application_id => 'ENG'
1531 , p_message_text => NULL
1532 , p_token_tbl => l_token_tbl_two
1533 , p_message_type => 'E'
1534 , p_row_identifier => cr.transaction_id
1535 , p_table_name => G_ERROR_TABLE_NAME
1536 , p_entity_id => NULL
1537 , p_entity_index => NULL
1538 , p_entity_code => G_ERROR_ENTITY_CODE
1539 );
1540 END IF;
1541 END IF;
1542 check_and_write_log (x_retcode => x_retcode);
1543 IF x_retcode = RETCODE_ERROR THEN
1544 RETURN;
1545 END IF;
1546 END LOOP; -- error Transaction Types
1547
1548 --
1549 -- validation for grantee_type and grantee_name combination
1550 --
1551 FOR cr IN c_err_grantee_type LOOP
1552 UPDATE eng_change_people_intf
1553 SET process_status = G_PS_ERROR
1554 WHERE transaction_id = cr.transaction_id;
1555 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
1556 IF ( cr.grantee_type IS NULL ) THEN
1557 l_msg_name := 'ENG_CPI_MISSING_VALUE';
1558 l_token_tbl_one(1).token_name := 'VALUE';
1559 l_token_tbl_one(1).token_value := 'GRANTEE TYPE';
1560 error_handler.Add_Error_Message
1561 ( p_message_name => l_msg_name
1562 , p_application_id => 'ENG'
1563 , p_message_text => NULL
1564 , p_token_tbl => l_token_tbl_one
1565 , p_message_type => 'E'
1566 , p_row_identifier => cr.transaction_id
1567 , p_table_name => G_ERROR_TABLE_NAME
1568 , p_entity_id => NULL
1569 , p_entity_index => NULL
1570 , p_entity_code => G_ERROR_ENTITY_CODE
1571 );
1572 ELSE
1573 -- cr.grantee_type NOT IN ('USER','GROUP','COMPANY','GLOBAL')
1574 l_msg_name := 'ENG_CPI_INVALID_VALUE2';
1575 l_token_tbl_two(1).token_name := 'NAME';
1576 l_token_tbl_two(1).token_value := 'GRANTEE TYPE';
1577 l_token_tbl_two(2).token_name := 'VALUE';
1578 l_token_tbl_two(2).token_value := cr.grantee_type;
1579 error_handler.Add_Error_Message
1580 ( p_message_name => l_msg_name
1581 , p_application_id => 'ENG'
1582 , p_message_text => NULL
1583 , p_token_tbl => l_token_tbl_two
1584 , p_message_type => 'E'
1585 , p_row_identifier => cr.transaction_id
1586 , p_table_name => G_ERROR_TABLE_NAME
1587 , p_entity_id => NULL
1588 , p_entity_index => NULL
1589 , p_entity_code => G_ERROR_ENTITY_CODE
1590 );
1591 END IF;
1592 END IF;
1593 check_and_write_log (x_retcode => x_retcode);
1594 IF x_retcode = RETCODE_ERROR THEN
1595 RETURN;
1596 END IF;
1597 END LOOP; -- error Grantee Types
1598
1599
1600 --Update the grantee_party id column for the people
1601 -- Fix to 4925242. Replaced upper(user_name) = upper(ecpi.grantee_name)
1602 -- with user_name = upper(ecpi.grantee_name)
1603 UPDATE eng_change_people_intf ecpi
1604 -- SET (ecpi.grantee_party_id, ecpi.grantee_name) =
1605 -- ( SELECT person_id, person_name
1606 SET (ecpi.grantee_party_id) =
1607 ( SELECT person_id
1608 FROM ego_people_v
1609 -- WHERE user_name = ecpi.grantee_user_name
1610 WHERE user_name = upper(ecpi.grantee_name)
1611 )
1612 WHERE ecpi.data_set_id = G_DATA_SET_ID
1613 AND ecpi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1614 AND ecpi.process_status = G_PS_IN_PROCESS
1615 AND ecpi.grantee_party_id IS NULL
1616 AND ecpi.grantee_type IS NOT NULL
1617 AND ecpi.grantee_type = 'USER';
1618
1619 --Update the grantee_party id column for the groups
1620 UPDATE eng_change_people_intf ecpi
1621 SET ecpi.grantee_party_id =
1622 ( SELECT group_id
1623 FROM ego_groups_v
1624 WHERE upper(group_name) = upper(ecpi.grantee_name)
1625 )
1626 WHERE ecpi.data_set_id = G_DATA_SET_ID
1627 AND ecpi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1628 AND ecpi.process_status = G_PS_IN_PROCESS
1629 AND ecpi.grantee_party_id IS NULL
1630 AND ecpi.grantee_type IS NOT NULL
1631 AND ecpi.grantee_type = 'GROUP';
1632
1633 --Update the grantee_party id column for the Companies
1634 --Company can be Enterprise / External Customer / External Supplier
1635 UPDATE eng_change_people_intf ecpi
1636 SET ecpi.grantee_party_id =
1637 ( SELECT company_id
1638 FROM ego_companies_v
1639 WHERE upper(company_name) = upper(ecpi.grantee_name)
1640 )
1641 WHERE ecpi.data_set_id = G_DATA_SET_ID
1642 AND ecpi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1643 AND ecpi.process_status = G_PS_IN_PROCESS
1644 AND ecpi.grantee_party_id IS NULL
1645 AND ecpi.grantee_type IS NOT NULL
1646 AND ecpi.grantee_type = 'COMPANY';
1647
1648 --Update the grantee_party id column for the Companies
1649 --Company can be Enterprise / External Customer / External Supplier
1650 UPDATE eng_change_people_intf ecpi
1651 SET ecpi.grantee_party_id = G_ALL_USERS_PARTY_ID
1652 WHERE ecpi.data_set_id = G_DATA_SET_ID
1656 AND ecpi.grantee_type IS NOT NULL
1653 AND ecpi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1654 AND ecpi.process_status = G_PS_IN_PROCESS
1655 AND ecpi.grantee_party_id IS NULL
1657 AND ecpi.grantee_type = 'GLOBAL';
1658
1659 -- For missing grantee_party_id, update process_status and log an error.
1660 -- Also, assign transaction_id, request_id
1661 FOR cr IN c_err_grantee_id LOOP
1662 UPDATE eng_change_people_intf
1663 SET process_status = G_PS_ERROR
1664 WHERE transaction_id = cr.transaction_id;
1665 -- Grantee Name check
1666 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
1667 IF ( cr.grantee_name IS NULL ) THEN
1668 l_msg_name := 'ENG_CPI_MISSING_VALUE';
1669 l_token_tbl_one(1).token_name := 'VALUE';
1670 l_token_tbl_one(1).token_value := 'GRANTEE NAME';
1671 error_handler.Add_Error_Message
1672 ( p_message_name => l_msg_name
1673 , p_application_id => 'ENG'
1674 , p_message_text => NULL
1675 , p_token_tbl => l_token_tbl_one
1676 , p_message_type => 'E'
1677 , p_row_identifier => cr.transaction_id
1678 , p_table_name => G_ERROR_TABLE_NAME
1679 , p_entity_id => NULL
1680 , p_entity_index => NULL
1681 , p_entity_code => G_ERROR_ENTITY_CODE
1682 );
1683 ELSE
1684 l_msg_name := 'ENG_CPI_INVALID_VALUE2';
1685 l_token_tbl_two(1).token_name := 'NAME';
1686 l_token_tbl_two(1).token_value := 'GRANTEE NAME';
1687 l_token_tbl_two(2).token_name := 'VALUE';
1688 l_token_tbl_two(2).token_value := cr.grantee_name;
1689 error_handler.Add_Error_Message
1690 ( p_message_name => l_msg_name
1691 , p_application_id => 'ENG'
1692 , p_message_text => NULL
1693 , p_token_tbl => l_token_tbl_two
1694 , p_message_type => 'E'
1695 , p_row_identifier => cr.transaction_id
1696 , p_table_name => G_ERROR_TABLE_NAME
1697 , p_entity_id => NULL
1698 , p_entity_index => NULL
1699 , p_entity_code => G_ERROR_ENTITY_CODE
1700 );
1701 END IF;
1702 END IF;
1703 check_and_write_log (x_retcode => x_retcode);
1704 IF x_retcode = RETCODE_ERROR THEN
1705 RETURN;
1706 END IF;
1707 END LOOP; -- c_err_grantee_id
1708
1709 -- Retrieval of Role Ids is done in 2 steps :
1710 --1) Retrieve and store the Display and Internal Role Names and Role Ids
1711 -- and store in a temp table. This is done by initialise_roles()
1712 --2) Verify the roles from the temporary table.
1713 --
1714 UPDATE eng_change_people_intf ecpi
1715 SET (ecpi.internal_role_id, ecpi.internal_role_name ) =
1716 ( SELECT role.internal_role_id,
1717 role.internal_role_name
1718 FROM eng_change_roles_temp role
1719 WHERE role.display_role_name = ecpi.display_role_name
1720 )
1721 WHERE ecpi.data_set_id = G_DATA_SET_ID
1722 AND ecpi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1723 AND ecpi.process_status = G_PS_IN_PROCESS
1724 AND ecpi.internal_role_id IS NULL
1725 AND ecpi.display_role_name IS NOT NULL
1726 AND EXISTS ( SELECT role2.internal_role_id
1727 FROM eng_change_roles_temp role2
1728 WHERE role2.display_role_name = ecpi.display_role_name
1729 );
1730
1731 -- For missing roles, update process_status and log an error.
1732 FOR cr IN c_err_role_id LOOP
1733 UPDATE eng_change_people_intf
1734 SET process_status = G_PS_ERROR
1735 WHERE transaction_id = cr.transaction_id;
1736 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
1737 IF ( cr.display_role_name IS NULL ) THEN
1738 l_msg_name := 'ENG_CPI_MISSING_VALUE';
1739 l_token_tbl_one(1).token_name := 'VALUE';
1740 l_token_tbl_one(1).token_value := 'DISPLAY ROLE NAME';
1741 error_handler.Add_Error_Message
1742 ( p_message_name => l_msg_name
1743 , p_application_id => 'ENG'
1744 , p_message_text => NULL
1745 , p_token_tbl => l_token_tbl_one
1746 , p_message_type => 'E'
1747 , p_row_identifier => cr.transaction_id
1748 , p_table_name => G_ERROR_TABLE_NAME
1749 , p_entity_id => NULL
1750 , p_entity_index => NULL
1751 , p_entity_code => G_ERROR_ENTITY_CODE
1752 );
1753 ELSE
1754 l_msg_name := 'ENG_CPI_INVALID_VALUE2';
1755 l_token_tbl_two(1).token_name := 'NAME';
1756 l_token_tbl_two(1).token_value := 'DISPLAY ROLE NAME';
1757 l_token_tbl_two(2).token_name := 'VALUE';
1758 l_token_tbl_two(2).token_value := cr.display_role_name;
1759 error_handler.Add_Error_Message
1760 ( p_message_name => l_msg_name
1761 , p_application_id => 'ENG'
1762 , p_message_text => NULL
1763 , p_token_tbl => l_token_tbl_two
1764 , p_message_type => 'E'
1765 , p_row_identifier => cr.transaction_id
1766 , p_table_name => G_ERROR_TABLE_NAME
1767 , p_entity_id => NULL
1768 , p_entity_index => NULL
1769 , p_entity_code => G_ERROR_ENTITY_CODE
1770 );
1771 END IF;
1772 END IF;
1773 check_and_write_log (x_retcode => x_retcode);
1774 IF x_retcode = RETCODE_ERROR THEN
1775 RETURN;
1776 END IF;
1777 END LOOP; -- c_err_role_id
1781 SET ecpi.organization_id =
1778
1779 --Update the organization id column
1780 UPDATE eng_change_people_intf ecpi
1782 ( SELECT mp.organization_id
1783 FROM mtl_parameters mp
1784 WHERE mp.organization_code = ecpi.organization_code
1785 )
1786 WHERE ecpi.data_set_id = G_DATA_SET_ID
1787 AND ecpi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1788 AND ecpi.process_status = G_PS_IN_PROCESS
1789 AND ecpi.organization_id IS NULL
1790 AND ecpi.organization_code IS NOT NULL
1791 AND EXISTS ( SELECT mp2.organization_id
1792 FROM mtl_parameters mp2
1793 WHERE mp2.organization_code = ecpi.organization_code
1794 );
1795
1796 -- For missing organization_id, update process_status and log an error.
1797 -- Also, assign transaction_id, request_id
1798
1799 FOR cr IN c_err_org_id LOOP
1800 UPDATE eng_change_people_intf
1801 SET process_status = G_PS_ERROR
1802 WHERE transaction_id = cr.transaction_id;
1803 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
1804 IF ( cr.organization_code IS NULL ) THEN
1805 l_msg_name := 'ENG_CPI_MISSING_VALUE';
1806 l_token_tbl_one(1).token_name := 'VALUE';
1807 l_token_tbl_one(1).token_value := 'ORGANIZATION CODE';
1808 error_handler.Add_Error_Message
1809 ( p_message_name => l_msg_name
1810 , p_application_id => 'ENG'
1811 , p_message_text => NULL
1812 , p_token_tbl => l_token_tbl_one
1813 , p_message_type => 'E'
1814 , p_row_identifier => cr.transaction_id
1815 , p_table_name => G_ERROR_TABLE_NAME
1816 , p_entity_id => NULL
1817 , p_entity_index => NULL
1818 , p_entity_code => G_ERROR_ENTITY_CODE
1819 );
1820 ELSE
1821 l_msg_name := 'ENG_CPI_INVALID_VALUE2';
1822 l_token_tbl_two(1).token_name := 'NAME';
1823 l_token_tbl_two(1).token_value := 'ORGANIZATION CODE';
1824 l_token_tbl_two(2).token_name := 'VALUE';
1825 l_token_tbl_two(2).token_value := cr.organization_code;
1826 error_handler.Add_Error_Message
1827 ( p_message_name => l_msg_name
1828 , p_application_id => 'ENG'
1829 , p_message_text => NULL
1830 , p_token_tbl => l_token_tbl_two
1831 , p_message_type => 'E'
1832 , p_row_identifier => cr.transaction_id
1833 , p_table_name => G_ERROR_TABLE_NAME
1834 , p_entity_id => NULL
1835 , p_entity_index => NULL
1836 , p_entity_code => G_ERROR_ENTITY_CODE
1837 );
1838 END IF;
1839 END IF;
1840 check_and_write_log (x_retcode => x_retcode);
1841 IF x_retcode = RETCODE_ERROR THEN
1842 RETURN;
1843 END IF;
1844 END LOOP; -- c_err_org_id
1845
1846
1847 --
1848 -- Organization id is obtained, Please check the change_mgmt_type_code now
1849 --
1850
1851 -- find the error records with invalid change_mgmt_type_code
1852 -- --valid change_mgmt_type_code are in ENG_CHANGE_MGMT_TYPES Table
1853 -- valid change_mgmt_type_codes are available in the ENG_CHANGE_ORDER_TYPES table.
1854 -- Table ENG_CHANGE_MGMT_TYPES has been obsoleted.
1855
1856 FOR cr IN c_err_chg_mgmt_type_code LOOP
1857 UPDATE eng_change_people_intf
1858 SET process_status = G_PS_ERROR
1859 WHERE transaction_id = cr.transaction_id;
1860 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
1861 IF ( cr.change_mgmt_type_code IS NULL ) THEN
1862 l_msg_name := 'ENG_CPI_MISSING_VALUE';
1863 l_token_tbl_one(1).token_name := 'VALUE';
1864 l_token_tbl_one(1).token_value := 'CHANGE MGMT TYPE';
1865
1866 error_handler.Add_Error_Message
1867 ( p_message_name => l_msg_name
1868 , p_application_id => 'ENG'
1869 , p_message_text => NULL
1870 , p_token_tbl => l_token_tbl_one
1871 , p_message_type => 'E'
1872 , p_row_identifier => cr.transaction_id
1873 , p_table_name => G_ERROR_TABLE_NAME
1874 , p_entity_id => NULL
1875 , p_entity_index => NULL
1876 , p_entity_code => G_ERROR_ENTITY_CODE
1877 );
1878 ELSE
1879
1880 l_msg_name := 'ENG_CPI_INVALID_VALUE2';
1881 l_token_tbl_two(1).token_name := 'NAME';
1882 l_token_tbl_two(1).token_value := 'CHANGE MGMT TYPE';
1883 l_token_tbl_two(2).token_name := 'VALUE';
1884 l_token_tbl_two(2).token_value := cr.change_mgmt_type_code;
1885 error_handler.Add_Error_Message
1886 ( p_message_name => l_msg_name
1887 , p_application_id => 'ENG'
1888 , p_message_text => NULL
1889 , p_token_tbl => l_token_tbl_two
1890 , p_message_type => 'E'
1891 , p_row_identifier => cr.transaction_id
1892 , p_table_name => G_ERROR_TABLE_NAME
1893 , p_entity_id => NULL
1894 , p_entity_index => NULL
1895 , p_entity_code => G_ERROR_ENTITY_CODE
1896 );
1897 END IF;
1898 END IF;
1899 check_and_write_log (x_retcode => x_retcode);
1900 IF x_retcode = RETCODE_ERROR THEN
1901 RETURN;
1902 END IF;
1903 END LOOP; -- error Change Mgmt Type Codes
1904
1905
1906 UPDATE eng_change_people_intf ecpi
1907 SET ecpi.change_id =
1908 ( SELECT change_id
1912 AND ecpi.change_mgmt_type_code = eec.change_mgmt_type_code
1909 FROM eng_engineering_changes eec
1910 WHERE ecpi.change_notice = eec.change_notice
1911 AND ecpi.organization_id = eec.organization_id
1913 )
1914 WHERE ecpi.data_set_id = G_DATA_SET_ID
1915 AND ecpi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
1916 AND ecpi.process_status = G_PS_IN_PROCESS
1917 AND ecpi.change_id IS NULL
1918 AND EXISTS ( SELECT change_id
1919 FROM eng_engineering_changes eec
1920 WHERE ecpi.change_notice = eec.change_notice
1921 AND ecpi.organization_id = eec.organization_id
1922 AND ecpi.change_mgmt_type_code = eec.change_mgmt_type_code
1923 );
1924
1925 -- For missing organization_id, update process_status and log an error.
1926 -- Also, assign transaction_id, request_id
1927
1928 FOR cr IN c_err_change_id LOOP
1929 UPDATE eng_change_people_intf
1930 SET process_status = G_PS_ERROR
1931 WHERE transaction_id = cr.transaction_id;
1932 IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
1933 IF ( cr.change_notice IS NULL ) THEN
1934 l_msg_name := 'ENG_CPI_MISSING_VALUE';
1935 l_token_tbl_one(1).token_name := 'VALUE';
1936 l_token_tbl_one(1).token_value := 'CHANGE NOTICE';
1937 error_handler.Add_Error_Message
1938 ( p_message_name => l_msg_name
1939 , p_application_id => 'ENG'
1940 , p_message_text => NULL
1941 , p_token_tbl => l_token_tbl_one
1942 , p_message_type => 'E'
1943 , p_row_identifier => cr.transaction_id
1944 , p_table_name => G_ERROR_TABLE_NAME
1945 , p_entity_id => NULL
1946 , p_entity_index => NULL
1947 , p_entity_code => G_ERROR_ENTITY_CODE
1948 );
1949 ELSE
1950 l_msg_name := 'ENG_CPI_INVALID_VALUE3';
1951 l_token_tbl_three(1).token_name := 'CHANGE_NOTICE';
1952 l_token_tbl_three(1).token_value := cr.change_notice;
1953 l_token_tbl_three(2).token_name := 'CHANGE_MGMT_TYPE_CODE';
1954 l_token_tbl_three(2).token_value := cr.change_mgmt_type_code;
1955 l_token_tbl_three(3).token_name := 'ORGANIZATION';
1956 l_token_tbl_three(3).token_value := cr.organization_code;
1957 error_handler.Add_Error_Message
1958 ( p_message_name => l_msg_name
1959 , p_application_id => 'ENG'
1960 , p_message_text => NULL
1961 , p_token_tbl => l_token_tbl_three
1962 , p_message_type => 'E'
1963 , p_row_identifier => cr.transaction_id
1964 , p_table_name => G_ERROR_TABLE_NAME
1965 , p_entity_id => NULL
1966 , p_entity_index => NULL
1967 , p_entity_code => G_ERROR_ENTITY_CODE
1968 );
1969 END IF;
1970 END IF;
1971 check_and_write_log (x_retcode => x_retcode);
1972 IF x_retcode = RETCODE_ERROR THEN
1973 RETURN;
1974 END IF;
1975 END LOOP; -- c_err_change_notice
1976
1977 --Retrieve the Change Id from Change Notice and Organization Id.
1978
1979 /***********************************/
1980 -- commit the data after every batch
1981 --
1982 -- increment the loop values
1983 G_FROM_LINE_NUMBER := G_TO_LINE_NUMBER + 1;
1984 END LOOP; -- l_batch_loop_counter
1985
1986 --
1987 -- upload the data into fnd_grants
1988 --
1989 validate_no_grant_overlap(x_retcode => x_retcode);
1990 write_log_now();
1991 --
1992 -- call purge_interface_lines if required
1993 --
1994
1995 IF p_delete_lines IN (DELETE_ALL, DELETE_ERROR, DELETE_SUCCESS) THEN
1996 purge_interface_lines
1997 (p_data_set_id => p_data_set_id
1998 ,p_closed_date => NULL
1999 ,p_delete_line_type => p_delete_lines
2000 -- ,p_delete_error_log => NULL
2001 ,x_retcode => l_retcode
2002 ,x_errbuff => l_errbuff
2003 );
2004 END IF;
2005 IF l_retcode = RETCODE_SUCCESS THEN
2006 x_retcode := RETCODE_SUCCESS;
2007 END IF;
2008 EXCEPTION
2009 WHEN OTHERS THEN
2010 ROLLBACK;
2011 x_retcode := RETCODE_ERROR;
2012 x_errbuff := 'EGO_IPI_EXCEPTION';
2013 IF c_user_party_id%ISOPEN THEN
2014 CLOSE c_user_party_id;
2015 END IF;
2016 IF c_count_cpi_lines %ISOPEN THEN
2017 CLOSE c_count_cpi_lines;
2018 END IF;
2019 IF c_get_trans_id_limits %ISOPEN THEN
2020 CLOSE c_get_trans_id_limits;
2021 END IF;
2022 IF c_err_dates%ISOPEN THEN
2023 CLOSE c_err_dates;
2024 END IF;
2025 IF c_err_transaction_type%ISOPEN THEN
2026 CLOSE c_err_transaction_type;
2027 END IF;
2028 IF c_err_grantee_type%ISOPEN THEN
2029 CLOSE c_err_grantee_type;
2030 END IF;
2031 IF c_err_grantee_id%ISOPEN THEN
2032 CLOSE c_err_grantee_id;
2033 END IF;
2034 IF c_err_role_id%ISOPEN THEN
2035 CLOSE c_err_role_id;
2036 END IF;
2037 IF c_err_org_id%ISOPEN THEN
2038 CLOSE c_err_org_id;
2039 END IF;
2040 IF c_err_chg_mgmt_type_code%ISOPEN THEN
2041 CLOSE c_err_chg_mgmt_type_code;
2042 END IF;
2043 IF c_err_change_id%ISOPEN THEN
2044 CLOSE c_err_change_id;
2045 END IF;
2046 IF c_get_grant_privileges%ISOPEN THEN
2047 CLOSE c_get_grant_privileges;
2048 END IF;
2049 IF c_get_utl_file_dir%ISOPEN THEN
2050 CLOSE c_get_utl_file_dir;
2051 END IF;
2052
2053 END load_interface_lines;
2054
2055
2056 PROCEDURE purge_interface_lines
2057 ( p_data_set_id IN NUMBER,
2058 p_closed_date IN DATE,
2059 p_delete_line_type IN NUMBER,
2060 -- p_delete_error_log IN NUMBER,
2061 x_retcode OUT NOCOPY VARCHAR2,
2062 x_errbuff OUT NOCOPY VARCHAR2
2063 ) IS
2064 -- Start OF comments
2065 -- API name : Clean Interface Lines
2066 -- TYPE : Public (called by Concurrent Program)
2067 -- Pre-reqs : None
2068 -- FUNCTION : Removes all the interface lines
2069 --
2070 BEGIN
2071 -- validate the given parameters
2072 IF (p_data_set_id IS NULL AND p_closed_date IS NULL)
2073 OR NVL(p_delete_line_type,-1) NOT IN (DELETE_ALL, DELETE_ERROR, DELETE_SUCCESS) THEN
2074 -- invalid parameters
2075 x_retcode := RETCODE_ERROR;
2076 x_errbuff := 'ENG_CPI_INSUFFICIENT_PARAMS';
2077 ELSE
2078 IF p_delete_line_type = DELETE_ALL THEN
2079 --
2080 -- delete all lines
2081 --
2082 DELETE mtl_interface_errors
2083 WHERE table_name = G_ERROR_TABLE_NAME
2084 AND transaction_id IN
2085 ( SELECT transaction_id
2086 FROM eng_change_people_intf
2087 WHERE data_set_id = NVL(p_data_set_id, data_set_id)
2088 AND creation_date <= NVL(p_closed_date, creation_date)
2089 );
2090 DELETE eng_change_people_intf
2091 WHERE data_set_id = NVL(p_data_set_id, data_set_id)
2092 AND creation_date <= NVL(p_closed_date, creation_date);
2093 ELSIF p_delete_line_type = DELETE_ERROR THEN
2094 --
2095 -- delete all error lines
2096 --
2097 DELETE mtl_interface_errors
2098 WHERE table_name = G_ERROR_TABLE_NAME
2099 AND transaction_id IN
2100 ( SELECT transaction_id
2101 FROM eng_change_people_intf
2102 WHERE data_set_id = NVL(p_data_set_id, data_set_id)
2103 AND creation_date <= NVL(p_closed_date, creation_date)
2104 );
2105 DELETE eng_change_people_intf
2106 WHERE data_set_id = NVL(p_data_set_id, data_set_id)
2107 AND creation_date <= NVL(p_closed_date, creation_date)
2108 AND process_status = G_PS_ERROR;
2109 ELSIF p_delete_line_type = DELETE_SUCCESS THEN
2110 --
2111 -- delete all success lines
2112 --
2113 DELETE eng_change_people_intf
2114 WHERE data_set_id = NVL(p_data_set_id, data_set_id)
2115 AND creation_date <= NVL(p_closed_date, creation_date)
2116 AND process_status = G_PS_SUCCESS;
2117 END IF;
2118 COMMIT;
2119 x_retcode := RETCODE_SUCCESS;
2120 END IF;
2121
2122 EXCEPTION
2123 WHEN OTHERS THEN
2124 x_retcode := RETCODE_ERROR;
2125 x_errbuff := 'ENG_CPI_EXCEPTION';
2126 ROLLBACK;
2127
2128 END purge_interface_lines;
2129
2130 END EGO_CHANGE_PEOPLE_IMPORT_PKG;