[Home] [Help]
PACKAGE BODY: APPS.PER_SECURITY_PROFILES_PKG
Source
1 PACKAGE BODY PER_SECURITY_PROFILES_PKG as
2 /* $Header: peser01t.pkb 120.1.12000000.1 2007/01/22 04:21:43 appldev noship $ */
3
4
5 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
6 X_Security_Profile_Id IN OUT NOCOPY NUMBER,
7 X_Business_Group_Id NUMBER,
8 X_Position_Id NUMBER,
9 X_Organization_Id NUMBER,
10 X_Position_Structure_Id NUMBER,
11 X_Organization_Structure_Id NUMBER,
12 X_Include_Top_Org_Flag VARCHAR2,
13 X_Include_Top_Position_Flag VARCHAR2,
14 X_Security_Profile_Name VARCHAR2,
15 X_View_All_Applicants_Flag VARCHAR2,
16 X_View_All_Employees_Flag VARCHAR2,
17 X_View_All_Flag VARCHAR2,
18 X_View_All_Organizations_Flag VARCHAR2,
19 X_View_All_Payrolls_Flag VARCHAR2,
20 X_View_All_Positions_Flag VARCHAR2,
21 X_View_All_Cwk_Flag VARCHAR2,
22 X_View_All_Contacts_Flag VARCHAR2,
23 X_View_All_Candidates_Flag VARCHAR2,
24 X_Include_Exclude_Payroll_Flag VARCHAR2,
25 X_Reporting_Oracle_Username VARCHAR2,
26 X_Allow_Granted_Users_Flag VARCHAR2,
27 X_Restrict_By_Supervisor_Flag VARCHAR2,
28 X_Supervisor_Levels NUMBER,
29 X_Exclude_Secondary_Asgs_Flag VARCHAR2,
30 X_Exclude_Person_Flag VARCHAR2,
31 X_Named_Person_Id NUMBER,
32 X_Custom_Restriction_Flag VARCHAR2,
33 X_Restriction_Text VARCHAR2,
34 X_Exclude_Business_Groups_Flag VARCHAR2,
35 X_Org_Security_Mode VARCHAR2,
36 X_Restrict_On_Individual_Asg VARCHAR2,
37 X_Top_Organization_Method VARCHAR2,
38 X_Top_Position_Method VARCHAR2,
39 X_Request_Id NUMBER,
40 X_Program_Application_Id NUMBER,
41 X_Program_Id NUMBER,
42 X_Program_Update_Date DATE
43 ) IS
44 CURSOR C IS SELECT rowid FROM per_security_profiles
45 WHERE security_profile_id = X_Security_Profile_Id;
46 --
47 CURSOR C2 IS SELECT per_security_profiles_s.nextval FROM sys.dual;
48 BEGIN
49
50 if (X_Security_Profile_Id is NULL) then
51 OPEN C2;
52 FETCH C2 INTO X_Security_Profile_Id;
53 CLOSE C2;
54 end if;
55 INSERT INTO per_security_profiles(
56 security_profile_id,
57 business_group_id,
58 position_id,
59 organization_id,
60 position_structure_id,
61 organization_structure_id,
62 include_top_organization_flag,
63 include_top_position_flag,
64 security_profile_name,
65 view_all_applicants_flag,
66 view_all_employees_flag,
67 view_all_flag,
68 view_all_organizations_flag,
69 view_all_payrolls_flag,
70 view_all_positions_flag,
71 view_all_cwk_flag,
72 view_all_contacts_flag,
73 view_all_candidates_flag,
74 include_exclude_payroll_flag,
75 reporting_oracle_username,
76 allow_granted_users_flag,
77 restrict_by_supervisor_flag,
78 supervisor_levels,
79 exclude_secondary_asgs_flag,
80 exclude_person_flag,
81 named_person_id,
82 custom_restriction_flag,
83 restriction_text,
84 exclude_business_groups_flag,
85 org_security_mode,
86 restrict_on_individual_asg,
87 top_organization_method,
88 top_position_method,
89 request_id,
90 program_application_id,
91 program_id,
92 program_update_date
93 ) VALUES (
94 X_Security_Profile_Id,
95 X_Business_Group_Id,
96 X_Position_Id,
97 X_Organization_Id,
98 X_Position_Structure_Id,
99 X_Organization_Structure_Id,
100 X_Include_Top_Org_Flag,
101 X_Include_Top_Position_Flag,
102 X_Security_Profile_Name,
103 X_View_All_Applicants_Flag,
104 X_View_All_Employees_Flag,
105 X_View_All_Flag,
106 X_View_All_Organizations_Flag,
107 X_View_All_Payrolls_Flag,
108 X_View_All_Positions_Flag,
109 X_View_All_Cwk_Flag,
110 X_View_All_Contacts_Flag,
111 X_View_All_Candidates_Flag,
112 X_Include_Exclude_Payroll_Flag,
113 X_Reporting_Oracle_Username,
114 X_Allow_Granted_Users_Flag,
115 X_Restrict_By_Supervisor_Flag,
116 X_Supervisor_Levels,
117 X_Exclude_Secondary_Asgs_Flag,
118 X_Exclude_Person_Flag,
119 X_Named_Person_Id,
120 X_Custom_Restriction_Flag,
121 X_Restriction_Text,
122 X_Exclude_Business_Groups_Flag,
123 X_Org_Security_Mode,
124 X_Restrict_On_Individual_Asg,
125 X_Top_Organization_Method,
126 X_Top_Position_Method,
127 X_Request_Id,
128 X_Program_Application_Id,
129 X_Program_Id,
130 X_Program_Update_Date
131 );
132
133 OPEN C;
134 FETCH C INTO X_Rowid;
135 if (C%NOTFOUND) then
136 CLOSE C;
137 hr_utility.set_message(801, 'HR_6153_APPL_PROCEDURE_FAIL');
138 hr_utility.set_message_token('PROCEDURE','PER_SECURITY_PROFILES_V_PKG');
139 hr_utility.set_message_token('STEP','1');
140 hr_utility.raise_error;
141 end if;
142 CLOSE C;
143 END Insert_Row;
144 ----------------------------------------------------------------------------
145 PROCEDURE Lock_Row (X_Rowid VARCHAR2,
146 X_Security_Profile_Id NUMBER,
147 X_Business_Group_Id NUMBER,
148 X_Position_Id NUMBER,
149 X_Organization_Id NUMBER,
150 X_Position_Structure_Id NUMBER,
151 X_Organization_Structure_Id NUMBER,
152 X_Include_Top_Org_Flag VARCHAR2,
153 X_Include_Top_Position_Flag VARCHAR2,
154 X_Security_Profile_Name VARCHAR2,
155 X_View_All_Applicants_Flag VARCHAR2,
156 X_View_All_Employees_Flag VARCHAR2,
157 X_View_All_Flag VARCHAR2,
158 X_View_All_Organizations_Flag VARCHAR2,
159 X_View_All_Payrolls_Flag VARCHAR2,
160 X_View_All_Positions_Flag VARCHAR2,
161 X_View_All_Cwk_Flag VARCHAR2,
162 X_View_All_Contacts_Flag VARCHAR2,
163 X_View_All_Candidates_Flag VARCHAR2,
164 X_Include_Exclude_Payroll_Flag VARCHAR2,
165 X_Reporting_Oracle_Username VARCHAR2,
166 X_Allow_Granted_Users_Flag VARCHAR2,
167 X_Restrict_By_Supervisor_Flag VARCHAR2,
168 X_Supervisor_Levels NUMBER,
169 X_Exclude_Secondary_Asgs_Flag VARCHAR2,
170 X_Exclude_Person_Flag VARCHAR2,
171 X_Named_Person_Id NUMBER,
172 X_Custom_Restriction_Flag VARCHAR2,
173 X_Restriction_Text VARCHAR2,
174 X_Exclude_Business_Groups_Flag VARCHAR2,
175 X_Org_Security_Mode VARCHAR2,
176 X_Restrict_On_Individual_Asg VARCHAR2,
177 X_Top_Organization_Method VARCHAR2,
178 X_Top_Position_Method VARCHAR2
179 ) IS
180 CURSOR C IS
181 SELECT *
182 FROM per_security_profiles
183 WHERE rowid = X_Rowid
184 FOR UPDATE of Security_Profile_Id NOWAIT;
185 Recinfo C%ROWTYPE;
186 BEGIN
187 OPEN C;
188 FETCH C INTO Recinfo;
189 if (C%NOTFOUND) then
190 CLOSE C;
191 hr_utility.set_message(801, 'HR_6153_APPL_PROCEDURE_FAIL');
192 hr_utility.set_message_token('PROCEDURE','PER_SECURITY_PROFILES_V_PKG');
193 hr_utility.set_message_token('STEP','2');
194 hr_utility.raise_error;
195 end if;
196 CLOSE C;
197
198
199 recinfo.include_top_organization_flag := rtrim(recinfo.include_top_organization_flag);
200 recinfo.include_top_position_flag := rtrim(recinfo.include_top_position_flag);
201 recinfo.security_profile_name := rtrim(recinfo.security_profile_name);
202 recinfo.view_all_applicants_flag := rtrim(recinfo.view_all_applicants_flag);
203 recinfo.view_all_employees_flag := rtrim(recinfo.view_all_employees_flag);
204 recinfo.view_all_flag := rtrim(recinfo.view_all_flag);
205 recinfo.view_all_organizations_flag := rtrim(recinfo.view_all_organizations_flag);
206 recinfo.view_all_payrolls_flag := rtrim(recinfo.view_all_payrolls_flag);
207 recinfo.view_all_positions_flag := rtrim(recinfo.view_all_positions_flag);
208 recinfo.view_all_cwk_flag := rtrim(recinfo.view_all_cwk_flag);
209 recinfo.view_all_contacts_flag := rtrim(recinfo.view_all_contacts_flag);
210 recinfo.view_all_candidates_flag := rtrim(recinfo.view_all_candidates_flag);
211 recinfo.include_exclude_payroll_flag := rtrim(recinfo.include_exclude_payroll_flag);
212 recinfo.reporting_oracle_username := rtrim(recinfo.reporting_oracle_username);
213 recinfo.allow_granted_users_flag := rtrim(recinfo.allow_granted_users_flag);
214 recinfo.restrict_by_supervisor_flag := rtrim(recinfo.restrict_by_supervisor_flag);
215 recinfo.exclude_secondary_asgs_flag:=rtrim(recinfo.exclude_secondary_asgs_flag);
216 recinfo.exclude_person_flag:=rtrim(recinfo.exclude_person_flag);
217 recinfo.custom_restriction_flag:=rtrim(recinfo.custom_restriction_flag);
218 recinfo.restriction_text:=rtrim(recinfo.restriction_text);
219 recinfo.exclude_business_groups_flag:=rtrim(recinfo.exclude_business_groups_flag);
220 recinfo.org_security_mode:=rtrim(recinfo.org_security_mode);
221 recinfo.restrict_on_individual_asg:=rtrim(recinfo.restrict_on_individual_asg);
222 recinfo.top_organization_method:=rtrim(recinfo.top_organization_method);
223 recinfo.top_position_method:=rtrim(recinfo.top_position_method);
224
225 if (
226 ( (Recinfo.security_profile_id = X_Security_Profile_Id)
227 OR ( (Recinfo.security_profile_id IS NULL)
228 AND (X_Security_Profile_Id IS NULL)))
229 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
230 OR ( (Recinfo.business_group_id IS NULL)
231 AND (X_Business_Group_Id IS NULL)))
232 AND ( (Recinfo.position_id = X_Position_Id)
233 OR ( (Recinfo.position_id IS NULL)
234 AND (X_Position_Id IS NULL)))
235 AND ( (Recinfo.organization_id = X_Organization_Id)
236 OR ( (Recinfo.organization_id IS NULL)
237 AND (X_Organization_Id IS NULL)))
238 AND ( (Recinfo.position_structure_id = X_Position_Structure_Id)
239 OR ( (Recinfo.position_structure_id IS NULL)
240 AND (X_Position_Structure_Id IS NULL)))
241 AND ( (Recinfo.organization_structure_id = X_Organization_Structure_Id)
242 OR ( (Recinfo.organization_structure_id IS NULL)
243 AND (X_Organization_Structure_Id IS NULL)))
244 AND ( (Recinfo.include_top_organization_flag =
245 X_Include_Top_Org_Flag)
246 OR ( (Recinfo.include_top_organization_flag IS NULL)
247 AND (X_Include_Top_Org_Flag IS NULL)))
248 AND ( (Recinfo.include_top_position_flag = X_Include_Top_Position_Flag)
249 OR ( (Recinfo.include_top_position_flag IS NULL)
250 AND (X_Include_Top_Position_Flag IS NULL)))
251 AND ( (Recinfo.security_profile_name = X_Security_Profile_Name)
252 OR ( (Recinfo.security_profile_name IS NULL)
253 AND (X_Security_Profile_Name IS NULL)))
254 AND ( (Recinfo.view_all_applicants_flag = X_View_All_Applicants_Flag)
255 OR ( (Recinfo.view_all_applicants_flag IS NULL)
256 AND (X_View_All_Applicants_Flag IS NULL)))
257 AND ( (Recinfo.view_all_employees_flag = X_View_All_Employees_Flag)
258 OR ( (Recinfo.view_all_employees_flag IS NULL)
259 AND (X_View_All_Employees_Flag IS NULL)))
260 AND ( (Recinfo.view_all_flag = X_View_All_Flag)
261 OR ( (Recinfo.view_all_flag IS NULL)
262 AND (X_View_All_Flag IS NULL)))
263 AND ( (Recinfo.view_all_organizations_flag = X_View_All_Organizations_Flag)
264 OR ( (Recinfo.view_all_organizations_flag IS NULL)
265 AND (X_View_All_Organizations_Flag IS NULL)))
266 AND ( (Recinfo.view_all_payrolls_flag = X_View_All_Payrolls_Flag)
267 OR ( (Recinfo.view_all_payrolls_flag IS NULL)
268 AND (X_View_All_Payrolls_Flag IS NULL)))
269 AND ( (Recinfo.view_all_positions_flag = X_View_All_Positions_Flag)
270 OR ( (Recinfo.view_all_positions_flag IS NULL)
271 AND (X_View_All_Positions_Flag IS NULL )))
272 AND ( (Recinfo.view_all_cwk_flag = X_View_All_Cwk_flag)
273 OR ( (Recinfo.view_all_cwk_flag IS NULL)
274 AND (X_View_All_cwk_Flag IS NULL)))
275 AND ( (Recinfo.view_all_contacts_flag = X_View_All_Contacts_flag)
276 OR ( (Recinfo.view_all_contacts_flag IS NULL)
277 AND (X_View_All_contacts_Flag IS NULL)))
278 AND ( (Recinfo.view_all_candidates_flag = X_View_All_Candidates_flag)
279 OR ( (Recinfo.view_all_candidates_flag IS NULL)
280 AND (X_View_All_candidates_Flag IS NULL)))
281 AND ( (Recinfo.include_exclude_payroll_flag = X_Include_Exclude_Payroll_Flag)
282 OR ( (Recinfo.include_exclude_payroll_flag IS NULL)
283 AND (X_Include_Exclude_Payroll_Flag IS NULL)))
284 AND ( (Recinfo.reporting_oracle_username = X_Reporting_Oracle_Username)
285 OR ( (Recinfo.reporting_oracle_username IS NULL)
286 AND (X_Reporting_Oracle_Username IS NULL)))
287 AND ( (Recinfo.allow_granted_users_flag = X_Allow_Granted_Users_Flag)
288 OR ( (Recinfo.allow_granted_users_flag IS NULL)
289 AND (X_Allow_Granted_Users_Flag IS NULL)))
290 AND ( (Recinfo.restrict_by_supervisor_flag = X_Restrict_By_Supervisor_Flag)
291 OR ( (Recinfo.restrict_by_supervisor_flag IS NULL)
292 AND (X_Restrict_By_Supervisor_Flag IS NULL)))
293 AND ( (Recinfo.supervisor_levels = X_Supervisor_Levels)
294 OR ( (Recinfo.supervisor_levels IS NULL)
295 AND (X_Supervisor_Levels IS NULL)))
299 AND ( (Recinfo.exclude_person_flag = X_Exclude_Person_Flag)
296 AND ( (Recinfo.exclude_secondary_asgs_flag = X_Exclude_Secondary_Asgs_Flag)
297 OR ( (Recinfo.exclude_secondary_asgs_flag IS NULL)
298 AND (X_Exclude_Secondary_Asgs_Flag IS NULL)))
300 OR ( (Recinfo.exclude_person_flag IS NULL)
301 AND (X_Exclude_Person_Flag IS NULL)))
302 AND ( (Recinfo.named_person_id = X_Named_Person_Id)
303 OR ( (Recinfo.named_person_id IS NULL)
304 AND (X_Named_Person_Id IS NULL)))
305 AND ( (Recinfo.custom_restriction_flag = X_Custom_Restriction_Flag)
306 OR ( (Recinfo.custom_restriction_flag IS NULL)
307 AND (X_Custom_Restriction_Flag IS NULL)))
308 AND ( (Recinfo.restriction_text = X_Restriction_Text)
309 OR ( (Recinfo.restriction_text IS NULL)
310 AND (X_Restriction_Text IS NULL)))
311 AND ( (Recinfo.exclude_business_groups_flag = X_Exclude_Business_Groups_Flag)
312 OR ( (Recinfo.exclude_business_groups_flag IS NULL)
313 AND (X_Exclude_Business_Groups_Flag IS NULL)))
314 AND ( (Recinfo.org_security_mode = X_Org_Security_Mode)
315 OR ( (Recinfo.org_security_mode IS NULL)
316 AND (X_Org_Security_Mode IS NULL)))
317 AND ( (Recinfo.restrict_on_individual_asg = X_Restrict_On_Individual_Asg)
318 OR ( (Recinfo.restrict_on_individual_asg IS NULL)
319 AND (X_Restrict_On_Individual_Asg IS NULL)))
320 AND ( (Recinfo.top_organization_method = X_Top_Organization_Method)
321 OR ( (Recinfo.top_organization_method IS NULL)
322 AND (X_Top_Organization_Method IS NULL)))
323 AND ( (Recinfo.top_position_method = X_Top_Position_Method)
324 OR ( (Recinfo.top_position_method IS NULL)
325 AND (X_Top_Position_Method IS NULL)))
326 ) then
327 return;
328 else
329 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
330 APP_EXCEPTION.RAISE_EXCEPTION;
331 end if;
332 END Lock_Row;
333 -----------------------------------------------------------------------------
334 PROCEDURE Update_Row(X_Rowid VARCHAR2,
335 X_Security_Profile_Id NUMBER,
336 X_Business_Group_Id NUMBER,
337 X_Position_Id NUMBER,
338 X_Organization_Id NUMBER,
339 X_Position_Structure_Id NUMBER,
340 X_Organization_Structure_Id NUMBER,
341 X_Include_Top_Org_Flag VARCHAR2,
342 X_Include_Top_Position_Flag VARCHAR2,
343 X_Security_Profile_Name VARCHAR2,
344 X_View_All_Applicants_Flag VARCHAR2,
345 X_View_All_Employees_Flag VARCHAR2,
346 X_View_All_Flag VARCHAR2,
347 X_View_All_Organizations_Flag VARCHAR2,
348 X_View_All_Payrolls_Flag VARCHAR2,
349 X_View_All_Positions_Flag VARCHAR2,
350 X_View_All_Cwk_flag VARCHAR2,
351 X_View_All_Contacts_flag VARCHAR2,
352 X_View_All_Candidates_flag VARCHAR2,
353 X_Include_Exclude_Payroll_Flag VARCHAR2,
354 X_Reporting_Oracle_Username VARCHAR2,
355 X_Allow_Granted_Users_Flag VARCHAR2,
356 X_Restrict_By_Supervisor_Flag VARCHAR2,
357 X_Supervisor_Levels NUMBER,
358 X_Exclude_Secondary_Asgs_Flag VARCHAR2,
359 X_Exclude_Person_Flag VARCHAR2,
360 X_Named_Person_Id NUMBER,
361 X_Custom_Restriction_Flag VARCHAR2,
362 X_Restriction_Text VARCHAR2,
363 X_Exclude_Business_Groups_Flag VARCHAR2,
364 X_Org_Security_Mode VARCHAR2,
365 X_Restrict_On_Individual_Asg VARCHAR2,
366 X_Top_Organization_Method VARCHAR2,
367 X_Top_Position_Method VARCHAR2,
368 X_Request_Id NUMBER,
369 X_Program_Application_Id NUMBER,
370 X_Program_Id NUMBER,
371 X_Program_Update_Date DATE
372 ) IS
373 BEGIN
374 UPDATE per_security_profiles
375 SET
376 security_profile_id = X_Security_Profile_Id,
377 business_group_id = X_Business_Group_Id,
378 position_id = X_Position_Id,
379 organization_id = X_Organization_Id,
380 position_structure_id = X_Position_Structure_Id,
381 organization_structure_id = X_Organization_Structure_Id,
382 include_top_organization_flag = X_Include_Top_Org_Flag,
383 include_top_position_flag = X_Include_Top_Position_Flag,
384 security_profile_name = X_Security_Profile_Name,
385 view_all_applicants_flag = X_View_All_Applicants_Flag,
389 view_all_payrolls_flag = X_View_All_Payrolls_Flag,
386 view_all_employees_flag = X_View_All_Employees_Flag,
387 view_all_flag = X_View_All_Flag,
388 view_all_organizations_flag = X_View_All_Organizations_Flag,
390 view_all_positions_flag = X_View_All_Positions_Flag,
391 view_all_cwk_flag = X_View_All_Cwk_Flag,
392 view_all_contacts_flag = X_View_All_Contacts_flag,
393 view_all_candidates_flag = X_View_All_Candidates_flag,
394 include_exclude_payroll_flag = X_Include_Exclude_Payroll_Flag,
395 reporting_oracle_username = X_Reporting_Oracle_Username,
396 allow_granted_users_flag = X_Allow_Granted_Users_Flag,
397 restrict_by_supervisor_flag = X_Restrict_By_Supervisor_Flag,
398 supervisor_levels = X_Supervisor_Levels,
399 exclude_secondary_asgs_flag = X_Exclude_Secondary_Asgs_Flag,
400 exclude_person_flag = X_Exclude_Person_Flag,
401 named_person_id = X_Named_Person_Id,
402 custom_restriction_flag = X_Custom_Restriction_Flag,
403 restriction_text = X_Restriction_Text,
404 exclude_business_groups_flag = X_Exclude_Business_Groups_Flag,
405 org_security_mode = X_Org_Security_Mode,
406 restrict_on_individual_asg = X_Restrict_On_Individual_Asg,
407 top_organization_method = X_Top_Organization_Method,
408 top_position_method = X_Top_Position_Method,
409 request_id = X_Request_Id,
410 program_application_id = X_Program_Application_Id,
411 program_id = X_Program_Id,
412 program_update_date = X_Program_Update_Date
413 WHERE rowid = X_rowid;
414
415 if (SQL%NOTFOUND) then
416 hr_utility.set_message(801, 'HR_6153_APPL_PROCEDURE_FAIL');
417 hr_utility.set_message_token('PROCEDURE','PER_SECURITY_PROFILES_V_PKG');
418 hr_utility.set_message_token('STEP','3');
419 hr_utility.raise_error;
420 end if;
421
422 END Update_Row;
423 ------------------------------------------------------------------------------
424 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
425 BEGIN
426 DELETE FROM per_security_profiles
427 WHERE rowid = X_Rowid;
428
429 if (SQL%NOTFOUND) then
430 hr_utility.set_message(801, 'HR_6153_APPL_PROCEDURE_FAIL');
431 hr_utility.set_message_token('PROCEDURE','PER_SECURITY_PROFILES_V_PKG');
432 hr_utility.set_message_token('STEP','4');
433 hr_utility.raise_error;
434 end if;
435 END Delete_Row;
436 ------------------------------------------------------------------------------
437 procedure check_uniqueness(
438 p_security_profile_name varchar2,
439 p_row_id varchar2) is
440 l_dummy number;
441 --
442 cursor c1 is
443 select 1
444 from per_security_profiles
445 where upper(security_profile_name) = upper(P_SECURITY_PROFILE_NAME)
446 and (rowid <> P_ROW_ID or P_ROW_ID is null);
447 begin
448 open c1;
449 fetch c1 into l_dummy;
450 --
451 if c1%found then
452 close c1;
453 fnd_message.set_name('PAY', 'PER_7062_DEF_SECPROF_EXISTS');
454 fnd_message.raise_error;
455 end if;
456 close c1;
457 --
458 end check_uniqueness;
459 ------------------------------------------------------------------------------
460 procedure chk_reporting_username_unique(
461 p_reporting_oracle_username varchar2,
462 p_row_id varchar2,
463 p_is_base_user IN OUT NOCOPY varchar2) is
464 l_dummy number;
465 --
466 -- Uniqueness check on reporting username as well as returning whether this
467 -- user is the base user. This info will be used to ensure that View All = Y.
468 --
469 cursor c1 is
470 select 1
471 from per_security_profiles
472 where reporting_oracle_username = P_REPORTING_ORACLE_USERNAME
473 and (rowid <> P_ROW_ID or P_ROW_ID is null);
474 --
475 cursor c2 is
476 select 1
477 from all_tables
478 where owner = P_REPORTING_ORACLE_USERNAME
479 and table_name = 'PER_ALL_PEOPLE_F';
480 --
481 begin
482 open c1;
483 fetch c1 into l_dummy;
484 if c1%found then
485 close c1;
486 fnd_message.set_name('PAY', 'PER_7063_DEF_SECPROF_USERNAME');
487 fnd_message.raise_error;
488 end if;
489 close c1;
490 --
491 open c2;
492 fetch c2 into l_dummy;
493 if c2%found then
494 p_is_base_user := 'Y';
495 else
496 p_is_base_user := 'N';
497 end if;
498 close c2;
499 --
500 end chk_reporting_username_unique;
501 ------------------------------------------------------------------------------
502 procedure pre_delete_validation(
503 p_security_profile_id number,
504 p_view_all_flag varchar2,
505 p_secgen_warn IN OUT NOCOPY varchar2) is
506 l_dummy number;
507 --
508 -- Return p_secgen_warn = Y if there are people in the person_list for this
509 -- security profile (where client-side should act on warning).
510 --
511 cursor ppl is
512 select 1
513 from per_person_list
517 -- Checks for business groups using this profile (where profiles are
514 where security_profile_id = P_SECURITY_PROFILE_ID;
515 begin
516 --
518 -- created on creation of business group).
519 --
520 p_secgen_warn := 'N';
521 if p_view_all_flag = 'N' then
522 open ppl;
523 fetch ppl into l_dummy;
524 if ppl%found then
525 p_secgen_warn := 'Y';
526 end if;
527 close ppl;
528 end if;
529 --
530 end pre_delete_validation;
531 ------------------------------------------------------------------------------
532 function check_sql_fragment(p_restriction_text VARCHAR2)
533 return boolean is
534 l_sql_statement varchar2(20000);
535 l_cursor INTEGER;
536 begin
537 --
538 l_sql_statement:='select PERSON.person_id
539 from per_all_people_f PERSON
540 , per_all_assignments_f ASSIGNMENT
541 where PERSON.person_id=ASSIGNMENT.person_id
542 and '||p_restriction_text;
543
544 -- Bug 3648079
545 -- This procedure was executing the SQL statement and affecting the
546 -- performance of 'Verify' custom SQL function in the Define Security
547 -- Profile form. The procedure is changed to parse the sql statement
548 -- instead of executing it.
549 l_cursor := dbms_sql.open_cursor;
550 dbms_sql.parse(l_cursor,l_sql_statement,dbms_sql.NATIVE);
551 return TRUE;
552
553 EXCEPTION
554 when others then
555 --
556 -- There was an error whilst trying to verify the
557 -- custom restriction. Wrap the SQL error in a message
558 -- and raise it back to the user.
559 --
560 fnd_message.set_name('PER','HR_289835_PSP_CUSTOM_ERR');
561 fnd_message.set_token('SQLERRM',SQLERRM);
562 fnd_message.raise_error;
563 end check_sql_fragment;
564
565 -- Bug #2809163
566 procedure check_assigned_sec_profile(p_security_profile_id number)is
567 --
568 l_exists varchar2(1);
569 --
570 /*cursor sec_profile is
571 select 'x'
572 from fnd_profile_option_values fpv
573 where fpv.profile_option_value = to_char(p_security_profile_id);
574 */
575
576 -- Modified the cursor as follows for bug 5006762
577 --
578 cursor sec_profile is
579 select 'x'
580 from fnd_profile_option_values fpv ,
581 fnd_profile_options fp
582 where fpv.profile_option_value = to_char(p_security_profile_id)
583 -- added the following
584 and fp.application_id = fpv.application_id
585 and fp.profile_option_id = fpv.profile_option_id
586 and fp.PROFILE_OPTION_NAME = 'PER_SECURITY_PROFILE_ID';
587
588 -- end of bug 5006762
589 --
590 begin
591 --
592 -- Checks that the security profile is already assigned to any responsibility
593 -- If assigned, then user is not supposed to delete the profile
594 open sec_profile;
595 fetch sec_profile into l_exists;
596 IF sec_profile%found THEN
597 hr_utility.set_message(800, 'PER_289480_SEC_PROFILE_VALUE');
598 close sec_profile;
599 hr_utility.raise_error;
600 END IF;
601 --
602 close sec_profile;
603 --
604 end check_assigned_sec_profile;
605 --
606
607 END PER_SECURITY_PROFILES_PKG;