1 PACKAGE BODY IGS_SC_BULK_ASSIGN AS
2 /* $Header: IGSSC07B.pls 120.7 2006/02/02 07:10:55 skpandey noship $ */
3
4
5
6 Type StrArray IS VARRAY(10) of VARCHAR2(500);
7 Type DateArray IS VARRAY(10) of VARCHAR2(11);
8 l_usrroles_array StrArray;
9 l_startdate_array DateArray;
10 l_enddate_array DateArray;
11
12 l_prog_label CONSTANT VARCHAR2(500) :='igs.plsql.igs_sc_bulk_assign';
13 l_label VARCHAR2(4000);
14 l_debug_str VARCHAR2(32000);
15
16
17 PROCEDURE PopulateRolesArray(EncdStr VARCHAR2) IS
18 ------------------------------------------------------------------
19 --Updated by : ssawhney, Oracle India
20 --Date created: 27-MAY-2001
21 --
22 --Purpose: This procedure will populate the roles arrays with the values of
23 -- Role Name,Start Date and End Date
24 --
25 --Change History:
26 ------------------------------------------------------------------
27 --This Local variable will save the String passed as parameter
28 l_Param_Str VARCHAR2(4000);
29 l_Rec_Str VARCHAR2(500);
30 --This will have the end positions of the substring
31 l_End_Count NUMBER;
32 --This will have the send position of a particular record
33 l_End_Rec_Count NUMBER;
34 l_array_index binary_integer;
35 -- This will have the current array index
36
37 BEGIN
38 /* Debug */
39 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
40 l_label := 'igs.plsql.igs_sc_bulk_assign.PopulateRolesArray';
41 l_debug_str := 'Entering PopulateRolesArray. EncdStr is ' || EncdStr;
42 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
43 END IF;
44
45
46 L_Param_Str := EncdStr;
47 L_End_Count := INSTR(L_Param_Str,'\:\');
48 l_array_index := 1;
49
50 WHILE L_Param_Str IS NOT NULL LOOP
51
52 l_usrroles_array.EXTEND(1);
53 l_startdate_array.EXTEND(1);
54 l_enddate_array.EXTEND(1);
55
56 l_Rec_Str := SUBSTR(L_Param_Str,1,L_End_Count-1);
57 l_End_Rec_Count := INSTR(l_Rec_Str,'\~\');
58 l_usrroles_array(l_array_index) := SUBSTR(l_Rec_Str,1,l_End_Rec_Count-1);
59
60 l_Rec_Str := SUBSTR(l_Rec_Str,l_End_Rec_Count+3);
61 l_End_Rec_Count := INSTR(l_Rec_Str,'\~\');
62 l_startdate_array(l_array_index) := SUBSTR(l_Rec_Str,1,l_End_Rec_Count-1);
63 l_Rec_Str := SUBSTR(l_Rec_Str,l_End_Rec_Count+3);
64
65 IF l_Rec_Str IS NOT NULL THEN
66 l_enddate_array(l_array_index) := l_Rec_Str;
67 END IF;
68
69 l_Param_Str := SUBSTR(L_Param_Str,L_End_Count+3);
70 l_array_index := l_array_index +1;
71 L_End_Count := INSTR(L_Param_Str,'\:\');
72
73 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
74 l_label := 'igs.plsql.igs_sc_bulk_assign.PopulateRolesArray';
75 l_debug_str := 'Inside PopulateRolesArray. l_Rec_Str: '||l_Rec_Str|| ', l_End_Rec_Count: '||l_End_Rec_Count||
76 ', l_Param_Str: '||l_Param_Str|| ', l_array_index: '||l_array_index;
77 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
78 END IF;
79
80 END LOOP;
81 /* Debug */
82 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
83 l_label := 'igs.plsql.igs_sc_bulk_assign.PopulateRolesArray';
84 l_debug_str := 'Exiting PopulateRolesArray.';
85 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
86 END IF;
87
88 END PopulateRolesArray;
89
90
91
92 FUNCTION getTokens( EncdStr Varchar2)
93 RETURN StrArray
94 IS
95 ------------------------------------------------------------------
96 --Updated by : ssawhney, Oracle India
97 --Date created: 27-MAY-2001
98 --
99 --Purpose:This Function will return an array populated with user attributes
100 --
101 --Change History:
102 ------------------------------------------------------------------
103 --This Local variable will save the String passed as parameter
104 L_Param_Str VARCHAR2(4000);--This will have the beg position of the substring
105 --This will have the end positions of the substring
106 L_End_Count NUMBER;
107 l_tokenArray StrArray := StrArray();
108 l_array_index binary_integer;
109
110 BEGIN
111 /* Debug */
112 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
113 l_label := 'igs.plsql.igs_sc_bulk_assign.getTokens';
114 l_debug_str := 'Entering getTokens. EncdStr is ' || EncdStr||', l_array_index is ' || l_array_index;
115 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
116 END IF;
117
118 L_Param_Str := EncdStr;
119 L_End_Count := INSTR(L_Param_Str,'\:\');
120 l_array_index := 1;
121
122
123 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
124 l_label := 'igs.plsql.igs_sc_bulk_assign.getTokens';
125 l_debug_str := 'Inside getTokens. L_Param_Str is'||L_Param_Str|| ', L_End_Count is '||L_End_Count;
126 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
127 END IF;
128
129 WHILE L_Param_Str IS NOT NULL LOOP
130 l_tokenArray.EXTEND(1);
131 l_tokenArray(l_array_index) := SUBSTR(L_Param_Str,1,L_End_Count-1);
132 L_Param_Str := SUBSTR(L_Param_Str,L_End_Count+3);
133
134 l_array_index := l_array_index +1;
135 L_End_Count := INSTR(L_Param_Str,'\:\');
136
137 END LOOP;
138 /* Debug */
139 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
140 l_label := 'igs.plsql.igs_sc_bulk_assign.getTokens';
141 l_debug_str := 'Exiting getTokens. Returning l_tokenArray(1)= ' ||l_tokenArray(1);
142 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
143 END IF;
144
145 RETURN l_tokenArray;
146 End getTokens;
147
148
149 PROCEDURE BulkSecAssignment(
150 retValue out NOCOPY number,
151 orgUnitStr IN varchar2,
152 locStr IN varchar2,
153 pgmStr IN varchar2,
154 unitMdStr IN varchar2,
155 userRolesStr_one IN varchar2,
156 userRolesStr_two IN varchar2,
157 prsnGrpStr IN varchar2)
158 IS
159 ------------------------------------------------------------------
160 --Updated by : ssawhney, Oracle India
161 --Date created: 27-MAY-2001
162 --
163 --Purpose: This Procedure will carry out the Bulk Assignment of user Attributes
164 -- Does a submit request. Called by SS Page.
165 --
166 --Change History:
167 --who when what
168 --skpandey 08-SEP-2005 Bug: 4583789
169 -- Description: Corrected spelling
170 ------------------------------------------------------------------
171
172 BEGIN
173 /* Debug */
174 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
175 l_label := 'igs.plsql.igs_sc_bulk_assign.BulkSecAssignment';
176 l_debug_str := 'Entering BulkSecAssignment parameter values:'||
177 'Org Unit: '||orgUnitStr||','||
178 'Location: '||locStr||','||
179 'Program Type: '||pgmStr||','||
180 'Unit Mode: '||unitMdStr||','||
181 'User Roles Str1: '||userRolesStr_one||','||
182 'User Roles Str2: '||userRolesStr_two||','||
183 'Person Group: '||prsnGrpStr;
184 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
185 END IF;
186 retValue := fnd_request.submit_request('IGS','IGSSCJ01','Bulk Security Attributes Assignment',NULL,false,
187 orgUnitStr,locStr,pgmStr,unitMdStr,userRolesStr_one,userRolesStr_two,prsnGrpStr);
188
189 /* Debug */
190 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
191 l_label := 'igs.plsql.igs_sc_bulk_assign.BulkSecAssignment';
192 l_debug_str := 'Exiting BulkSecAssignment. retValue is '||retValue;
193 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
194 END IF;
195
196 END BulkSecAssignment;
197
198 PROCEDURE ASSIGN_USER_ATTRIBUTES (
199 ERRBUF out NOCOPY VARCHAR2,
200 RETCODE out NOCOPY NUMBER,
201 P_ORGUNIT_STR IN VARCHAR2 DEFAULT NULL,
202 P_LOCATIONS_STR IN VARCHAR2 DEFAULT NULL,
203 P_PGMTYPES_STR IN VARCHAR2 DEFAULT NULL,
204 P_UNITMODE_STR IN VARCHAR2 DEFAULT NULL,
205 P_USERROLES_STR_ONE IN VARCHAR2 DEFAULT NULL,
206 P_USERROLES_STR_TWO IN VARCHAR2 DEFAULT NULL,
207 P_PRSNGRP_STR IN VARCHAR2) IS
208 ------------------------------------------------------------------
209 --Updated by : ssawhney, Oracle India
210 --Date created: 27-MAY-2001
211 --
212 --Purpose: Called by SRS Conc Job
213 --
214 --Change History:
215 --gmaheswa 5-Jan-2004 Bug 4869737 Added a call to SET_ORG_ID to disable OSS for R12.
216 --gmaheswa 17-Jan-06 4938278: disable Business Events before starting bulk import process and enable after import.
217 ------------------------------------------------------------------
218 l_org_unit_array StrArray;
219 l_locations_array StrArray;
220 l_pgmtypes_array StrArray;
221 l_unitmd_array StrArray;
222 loc_prsnGrp_array StrArray;
223
224 l_GrpId igs_pe_persid_group_v.group_id%TYPE;
225 l_User_Id NUMBER;
226 l_sec_User_id NUMBER;
227 l_rowId VARCHAR2(500);
228 l_DupId NUMBER;
229 l_user_name VARCHAR2(320);
230 l_sec_User_Name VARCHAR2(320);
231 l_return_Status VARCHAR2(10);
232 l_return_message VARCHAR2(500);
233 --l_role_id NUMBER;
234 l_role_name VARCHAR2(1000);
235 l_last_update_date Date := Sysdate;
236 l_Ret_Status VARCHAR2(100);
237 l_msg_count NUMBER;
238 l_msg_data VARCHAR2(500);
239 l_party_rel_id VARCHAR2(100);
240 l_party_id NUMBER;
241 l_party_number NUMBER;
242 l_object_version NUMBER;
243 L_select VARCHAR2(32767);
244 l_status VARCHAR2(1);
245 L_str VARCHAR2(32000);
246 l_Inf_date VARCHAR2(11);
247 l_user_roles_param VARCHAR2(4000);
248 l_group_type IGS_PE_PERSID_GROUP_V.group_type%TYPE;
249
250 CURSOR Fnd_User_Check_C(Prsn_id NUMBER) IS
251 SELECT user_id,user_name, customer_id, employee_id
252 FROM FND_USER
253 WHERE Person_party_id = Prsn_id AND
254 SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE+1);
255
256 CURSOR IGS_SC_DUP_C(prsnId NUMBER,usrAttrId NUMBER,usrAttrVal VARCHAR2) IS
257 SELECT 1
258 FROM IGS_SC_PER_ATTR_VALS
259 WHERE person_id = prsnId AND
260 user_attrib_id = usrAttrId AND
261 user_attrib_value = usrAttrVal;
262
263 CURSOR DUP_ROLES_CHK_C(UserId NUMBER,RoleName VARCHAR2,RolrOrgSystem VARCHAR2,
264 RoleOrgSysId NUMBER, UsrOrgSystem VARCHAR2,UsrName VARCHAR2) IS
265 SELECT 1, START_DATE /*while assigning roles check if person is associated with mul FND USER */
266 FROM WF_LOCAL_USER_ROLES
267 WHERE user_orig_system_id =UserId AND
268 user_name=UsrName AND
269 role_name= RoleName AND
270 user_orig_system = UsrOrgSystem AND
271 role_orig_system = RolrOrgSystem AND
272 role_orig_system_id = RoleOrgSysId; -- and partition_id IS NOT NULL
273
274 CURSOR GET_ROLE_NAME_C(RoleId NUMBER) IS
275 SELECT NAME
276 FROM WF_LOCAL_ROLES
277 WHERE
278 ORIG_SYSTEM_ID = RoleId AND
279 ORIG_SYSTEM ='IGS'
280 AND partition_id=0 ; -- non registered orig systems are stored in partion 0
281
282 CURSOR DUP_ORGUNIT_CHECK_C(PrsnId NUMBER,OrgId NUMBER,RelCode VARCHAR2,DFLAG VARCHAR2) IS
283 Select 1
284 from HZ_RELATIONSHIPS
285 WHERE
286 SUBJECT_ID = PrsnId AND
287 OBJECT_ID = OrgId AND
288 object_type = 'ORGANIZATION' AND
289 subject_type = 'PERSON' AND
290 RELATIONSHIP_CODE = 'EMPLOYEE_OF'
291 AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE+1)
292 AND DIRECTIONAL_FLAG='F';
293
294 CURSOR get_person_num (cp_prsnId NUMBER) IS
295 SELECT party_number
296 FROM hz_parties
297 WHERE party_id=cp_prsnId;
298
299 TYPE cur_query IS REF CURSOR;
300 c_cur_query cur_query;
301
302 TYPE rec_query IS RECORD (
303 person_id NUMBER(30)
304 );
305 r_rec_query rec_query;
306 l_DupStartDate Date;
307 l_start_date Date;
308 l_cust_id number;
309 l_emp_id number;
310 l_sec_cust_id number;
311 l_sec_emp_id number;
312 l_role_exists varchar2(1);
313 --s binary_integer ;
314 l_roles WF_DIRECTORY.RoleTable ;
315 l_wf_orig_id number;
316 l_wf_orig_ref wf_local_roles.orig_system%type;
317 l_person_num igs_pe_person.person_number%type;
318
319 BEGIN
320
321 igs_ge_gen_003.set_org_id;
322
323 --Disable Business Event before running Bulk Process
324 IGS_PE_GEN_003.TURNOFF_TCA_BE (
325 P_TURNOFF => 'Y'
326 );
327
328 /* Debug */
329 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
330 l_label := 'igs.plsql.igs_sc_bulk_assign.assign_user_attributes';
331 l_debug_str := 'Entering assign_user_attributes parameter values:'||
332 'Org Unit: '||P_ORGUNIT_STR||','||
333 'Location: '||P_LOCATIONS_STR||','||
334 'Program Type: '||P_PGMTYPES_STR||','||
335 'Unit Mode: '||P_UNITMODE_STR||','||
336 'User Roles Str1: '||P_USERROLES_STR_ONE||','||
337 'User Roles Str2: '||P_USERROLES_STR_TWO||','||
338 'Person Group: '||P_PRSNGRP_STR;
339 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
340 END IF;
341
342 RETCODE :=0;
343 /* Now we have all the person Grp Code */
344
345 l_user_roles_param :=P_USERROLES_STR_ONE||P_USERROLES_STR_TWO;
346 loc_prsnGrp_array := getTokens(P_PRSNGRP_STR);
347
348 IF P_ORGUNIT_STR IS NOT NULL THEN
349 l_org_unit_array := getTokens(P_ORGUNIT_STR);
350 END IF;
351
352 IF P_LOCATIONS_STR IS NOT NULL THEN
353 l_locations_array := getTokens(P_LOCATIONS_STR);
354 END IF;
355
356 IF P_PGMTYPES_STR IS NOT NULL THEN
357 l_pgmtypes_array := getTokens(P_PGMTYPES_STR);
358 END IF;
359
360 IF P_UNITMODE_STR IS NOT NULL THEN
361 l_unitmd_array := getTokens(P_UNITMODE_STR);
362 END IF;
363
364 IF l_user_roles_param IS NOT NULL THEN
365 l_usrroles_array := StrArray();
366 l_startdate_array:= DateArray();
367 l_enddate_array := DateArray();
368 PopulateRolesArray(l_user_roles_param);
369 END IF;
370
371
372 FOR j IN 1..loc_prsnGrp_array.COUNT
373 LOOP
374 l_GrpId := loc_prsnGrp_array(j);
375
376 /* get the person id corresponding to user belonging to the grp and assigned them following attributes */
377 l_select :=igs_pe_dynamic_persid_group.get_dynamic_sql(l_GrpId, l_status, l_group_type);
378
379 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
380 l_label := 'igs.plsql.igs_sc_bulk_assign.assign_user_attributes';
381 l_debug_str := 'Person Group Array. Dynamic Person Group select is '||l_select ;
382 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
383 END IF;
384
385 IF l_status <> 'S' THEN
386 FND_MESSAGE.SET_NAME('IGF','IGF_AW_NO_QUERY');
387 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
388 RAISE NO_DATA_FOUND;
389 END IF;
390
391 --skpandey, Bug#4937960: Added logic as a part of Literal Fix
392 IF l_group_type = 'STATIC' THEN
393 OPEN c_cur_query FOR L_select USING l_GrpId;
394 ELSIF l_group_type = 'DYNAMIC' THEN
395 OPEN c_cur_query FOR L_select;
396 END IF;
397 LOOP
398 FETCH c_cur_query INTO r_rec_query;
399 EXIT WHEN c_cur_query%NOTFOUND;
400 /* Check Whether user id FND USER otherwise put a error msg in the error log and skip assignment */
401 OPEN Fnd_User_Check_C(r_rec_query.Person_id);
402 FETCH Fnd_User_Check_C INTO l_User_Id,l_user_name, l_cust_id, l_emp_id;
403
404 IF Fnd_User_Check_C%FOUND THEN
405 /* IF User is a FND USER then carry out the assignment*/
406
407 --IMP logic --simran.
408 IF l_emp_id IS NOT NULL and l_cust_id IS NULL THEN
409 l_wf_orig_id := l_emp_id;
410 l_wf_orig_ref:= 'PER';
411 ELSIF l_cust_id IS NOT NULL and (l_cust_id = r_rec_query.Person_id) THEN
412 l_wf_orig_id := l_User_Id;
413 l_wf_orig_ref:= 'FND_USR';
414 END IF;
415
416 IF l_org_unit_array IS NOT NULL THEN
417
418 /*Now iterate through the array to insert record into HZ_RELATIONSHIP */
419 FOR i IN 1..l_org_unit_array.COUNT
420 LOOP
421
422 /* First check whether this attribute already exist */
423 OPEN DUP_ORGUNIT_CHECK_C(r_rec_query.Person_id,l_org_unit_array(i),'EMPLOYEE_OF','F');
424 FETCH DUP_ORGUNIT_CHECK_C INTO l_DupId;
425
426 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
427 l_label := 'igs.plsql.igs_sc_bulk_assign.assign_user_attributes';
428 l_debug_str := 'Inside assign_user_attributes. l_org_unit_array(i) is '||l_org_unit_array(i) ;
429 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
430 END IF;
431
432 IF DUP_ORGUNIT_CHECK_C%NOTFOUND THEN
433
434 IGS_PE_RELATIONSHIPS_PKG.CREATUPDATE_PARTY_RELATIONSHIP(
435 p_action => 'INSERT',
436 p_subject_id => r_rec_query.Person_id,
437 p_object_id => l_org_unit_array(i),
438 p_party_relationship_type => 'EMPLOYMENT',
439 p_relationship_code => 'EMPLOYEE_OF',
440 p_comments => NULL,
441 p_start_date => trunc(SYSDATE),
442 p_end_date => null,
443 p_last_update_date => l_last_update_date,
444 p_return_status => l_Ret_Status,
445 p_msg_count => l_msg_count,
446 p_msg_data => l_msg_data,
447 p_party_relationship_id => l_party_rel_id,
448 p_party_id => l_party_id,
449 p_party_number => l_party_number,
450 p_caller => 'NOT_FAMILY',
451 P_Object_Version_Number => l_object_version,
452 P_Primary => null,
453 P_Secondary => null,
454 P_Joint_Salutation => null,
455 P_Next_To_Kin => null,
456 P_Rep_Faculty => null,
457 P_Rep_Staff => null,
458 P_Rep_Student => null,
459 P_Rep_Alumni => null,
460 p_directional_flag => 'F');
461
462 IF l_return_status IN ('E' , 'U') THEN
463 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
464 RAISE NO_DATA_FOUND;
465 END IF;
466
467 END IF; -- DUP_ORGUNIT_CHECK_C%NOTFOUND
468 CLOSE DUP_ORGUNIT_CHECK_C;
469 END LOOP; -- l_org_unit_array.COUNT
470 END IF; -- l_org_unit_array
471
472 IF l_locations_array IS NOT NULL THEN
473
474 /*Now iterate through Locations array to insert record into igs_sc_per_attr_vals */
475 FOR i IN 1..l_locations_array.COUNT
476 LOOP
477 /* First check that user does not have this attribute already assigned */
478 OPEN IGS_SC_DUP_C(r_rec_query.Person_id,6,l_locations_array(i));
479 FETCH IGS_SC_DUP_C INTO l_DupId;
480
481 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
482 l_label := 'igs.plsql.igs_sc_bulk_assign.assign_user_attributes';
483 l_debug_str := 'Inside assign_user_attributes. l_locations_array(i) is '||l_locations_array(i) ;
484 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
485 END IF;
486
487 IF IGS_SC_DUP_C%NOTFOUND THEN
488 IGS_SC_PER_ATTR_VALS_PKG.INSERT_ROW(
489 x_rowid => l_rowId,
490 x_person_id =>r_rec_query.Person_id,
491 x_user_attrib_id => 6,
492 x_user_attrib_value => l_locations_array(i)
493 );
494 END IF;
495 CLOSE IGS_SC_DUP_C;
496 END LOOP;
497 END IF; -- l_locations_array
498
499 IF l_pgmtypes_array IS NOT NULL THEN
500 /*Now iterate through PgmTypes array to insert record into igs_sc_per_attr_vals */
501 for i IN 1..l_pgmtypes_array.COUNT
502 LOOP
503 OPEN IGS_SC_DUP_C(r_rec_query.Person_id,7,l_pgmtypes_array(i));
504 FETCH IGS_SC_DUP_C INTO l_DupId;
505
506 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
507 l_label := 'igs.plsql.igs_sc_bulk_assign.assign_user_attributes';
508 l_debug_str := 'Inside assign_user_attributes. l_pgmtypes_array(i) is '||l_pgmtypes_array(i) ;
509 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
510 END IF;
511
512 IF IGS_SC_DUP_C%NOTFOUND THEN
513 IGS_SC_PER_ATTR_VALS_PKG.INSERT_ROW(
514 x_rowid => l_rowId,
515 x_person_id =>r_rec_query.Person_id,
516 x_user_attrib_id => 7,
517 x_user_attrib_value => l_pgmtypes_array(i)
518 );
519 END IF;
520 CLOSE IGS_SC_DUP_C;
521 END LOOP;
522 END IF; -- l_pgmtypes_array
523
524 IF l_unitmd_array IS NOT NULL THEN
525 /*Now iterate through Unit Md array to insert record into igs_sc_per_attr_vals */
526 FOR i IN 1..l_unitmd_array.COUNT
527 LOOP
528 OPEN IGS_SC_DUP_C(r_rec_query.Person_id,8,l_unitmd_array(i));
529 FETCH IGS_SC_DUP_C INTO l_DupId;
530
531 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
532 l_label := 'igs.plsql.igs_sc_bulk_assign.assign_user_attributes';
533 l_debug_str := 'Inside assign_user_attributes. l_unitmd_array(i) is '||l_unitmd_array(i) ;
534 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
535 END IF;
536
537 IF IGS_SC_DUP_C%NOTFOUND THEN
538 IGS_SC_PER_ATTR_VALS_PKG.INSERT_ROW(
539 x_rowid => l_rowId,
540 x_person_id =>r_rec_query.Person_id,
541 x_user_attrib_id => 8,
542 x_user_attrib_value => l_unitmd_array(i)
543 );
544 END IF;
545 CLOSE IGS_SC_DUP_C;
546 END LOOP;
547 END IF;
548
549 IF l_usrroles_array IS NOT NULL THEN
550 /*First check if the user has multiple FND USer associated since cursor is already open-refetch it. */
551 FETCH Fnd_User_Check_C INTO l_sec_User_Id,l_sec_User_Name,l_sec_cust_id, l_sec_emp_id;
552
553 --skpandey.Bug: 4583789
554 IF Fnd_User_Check_C%FOUND THEN
555 OPEN get_person_num (r_rec_query.Person_Id);
556 FETCH get_person_num INTO l_person_num;
557 CLOSE get_person_num;
558 FND_MESSAGE.SET_NAME('IGS','IGS_SC_MUL_FND_USER');
559 FND_MESSAGE.SET_TOKEN('PERS_NUM',l_person_num);
560 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
561 ELSE
562
563 /*Now iterate through UserRoles array to insert record into wf_local_user_roles */
564 FOR i IN 1..l_usrroles_array.COUNT
565 LOOP
566 IF l_enddate_array(i) IS NULL THEN
567 l_Inf_date := null ; --remove the logic for infinite date.
568 --l_Inf_date := igs_ge_date.igsdate(igs_ge_date.igschar('4712/12/31')) ; -- cannonical format is rrrr/mm/dd ;
569 ELSE
570 l_Inf_date :=igs_ge_date.igsdate((l_enddate_array(i) ));
571 END IF;
572
573 IF l_enddate_array(i) IS NOT NULL THEN
574 l_start_date:=igs_ge_date.igsdate((l_startdate_array(i) ));
575 END IF;
576
577 -- IMP: FND_USER has cust_id , person_party_id and emp_id. If person is from HZ the cust = pp_id
578 -- If person is from HR then cust is null and emp_id is not null
579 -- User WF APIs to verify if the user has any roles. First Check if user already has this role associated
580 -- get the role name, note its an igs role, so partion would be 0.
581
582 OPEN GET_ROLE_NAME_C(l_usrroles_array(i));
583 FETCH GET_ROLE_NAME_C INTO l_role_name;
584
585 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
586 l_label := 'igs.plsql.igs_sc_bulk_assign.assign_user_attributes';
587 l_debug_str := 'Inside assign_user_attributes. l_usrroles_array(i) is '||l_usrroles_array(i) ;
588 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
589 END IF;
590
591 CLOSE GET_ROLE_NAME_C;
592
593 l_role_exists := 'N';
594 WF_DIRECTORY.GetUserRoles( l_user_name, l_roles) ;
595 FOR s in 1..l_roles.count LOOP
596 IF l_roles(s) = l_role_name THEN
597 l_role_exists := 'Y' ;
598 END IF;
599 END LOOP;
600
601
602 IF l_role_exists = 'Y' THEN
603 /*Role is already assigned Update the end_date */
604
605
606 IGS_SC_DATA_SEC_APIS_PKG.Update_Local_User_Role(
607 p_api_version => 1.0,
608 p_user_name => l_user_name,
609 p_role_name => l_role_name,
610 p_user_orig_system => l_wf_orig_ref,
611 p_user_orig_system_id => l_wf_orig_id,
612 p_role_orig_system => 'IGS',
613 p_role_orig_system_id => l_usrroles_array(i),
614 p_start_date => NVL(l_DupStartDate,TRUNC(SYSDATE)),
615 p_expiration_date => l_Inf_date,
616 p_security_group_id => 0,
617 x_return_status => l_return_status,
618 x_return_message => l_return_message);
619 IF l_return_status IN ('E' , 'U') THEN
620
621 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
622
623 END IF;
624
625 ELSE
626
627 IGS_SC_DATA_SEC_APIS_PKG.Insert_Local_User_Role(
628 p_api_version => 1.0,
629 p_user_name => l_user_name,
630 p_role_name => l_role_name,
631 p_user_orig_system => l_wf_orig_ref,
632 p_user_orig_system_id => l_wf_orig_id,
633 p_role_orig_system => 'IGS',
634 p_role_orig_system_id => l_usrroles_array(i),
635 p_start_date => trunc(SYSDATE), --sysdate, --l_startdate_array(i),
636 p_expiration_date => l_Inf_date , --sysdate+2, --l_enddate_array(i),
637 p_security_group_id => 0,
638 x_return_status => l_return_status,
639 x_return_message => l_return_message);
640
641 IF l_return_status IN ('E' , 'U') THEN
642
643 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
644 --RAISE NO_DATA_FOUND;
645 END IF;
646 END IF; -- DUP_ROLES_CHK_C%FOUND
647
648
649 --CLOSE DUP_ROLES_CHK_C;
650 END LOOP; -- l_usrroles_array
651 END IF; -- Fnd_User_Check_C%FOUND
652 END IF; -- l_usrroles_array
653 ELSE -- Fnd_User_Check_C%FOUND
654 OPEN get_person_num (r_rec_query.Person_Id);
655 FETCH get_person_num INTO l_person_num;
656 CLOSE get_person_num;
657 FND_MESSAGE.SET_NAME('IGS','IGS_SC_NO_FND_USER');
658 FND_MESSAGE.SET_TOKEN('PERS_NUM',l_person_num);
659 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
660 END IF; /*END of IF of FND_USER */
661 CLOSE Fnd_User_Check_C;
662
663 END LOOP; -- c_cur_query
664 CLOSE c_cur_query;
665 END LOOP;
666 /* Debug */
667 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
668 l_label := 'igs.plsql.igs_sc_bulk_assign.assign_user_attributes';
669 l_debug_str := 'Exiting assign_user_attributes.';
670 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
671 END IF;
672
673 --Enable Business Event before quiting Bulk Process
674 IGS_PE_GEN_003.TURNOFF_TCA_BE (
675 P_TURNOFF => 'N'
676 );
677 EXCEPTION
678 WHEN NO_DATA_FOUND THEN
679 /* Debug */
680 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
681 l_label := 'igs.plsql.igs_sc_bulk_assign.assign_user_attributes';
682 l_debug_str := 'NO_DATA_FOUND exception in assign_user_attributes.'||SQLERRM;
683 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
684 END IF;
685 --Enable Business Event before quiting Bulk Process
686 IGS_PE_GEN_003.TURNOFF_TCA_BE (
687 P_TURNOFF => 'N'
688 );
689 ROLLBACK;
690 retcode := 2;
691 errbuf := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
692 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
693
694 IF Fnd_User_Check_C%isopen then
695 CLOSE Fnd_User_Check_C;
696 END IF;
697
698 IF GET_ROLE_NAME_C%isopen then
699 CLOSE GET_ROLE_NAME_C;
700 END IF;
701 IF IGS_SC_DUP_C%isopen then
702 CLOSE IGS_SC_DUP_C;
703 END IF;
704
705 WHEN OTHERS THEN
706 /* Debug */
707 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
708 l_label := 'igs.plsql.igs_sc_bulk_assign.assign_user_attributes';
709 l_debug_str := 'Exception in assign_user_attributes.'||SQLERRM;
710 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
711 END IF;
712
713 --Enable Business Event before quiting Bulk Process
714 IGS_PE_GEN_003.TURNOFF_TCA_BE (
715 P_TURNOFF => 'N'
716 );
717 ROLLBACK;
718 retcode := 2;
719 errbuf := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
720 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
721
722 IF Fnd_User_Check_C%isopen then
723 CLOSE Fnd_User_Check_C;
724 END IF;
725
726 IF GET_ROLE_NAME_C%isopen then
727 CLOSE GET_ROLE_NAME_C;
728 END IF;
729 IF IGS_SC_DUP_C%isopen then
730 CLOSE IGS_SC_DUP_C;
731 END IF;
732
733 END Assign_User_attributes;
734
735 END IGS_SC_BULK_ASSIGN;
736
[Home] [Help]