[Home] [Help]
PACKAGE BODY: APPS.PER_SECURITY_PROFILES_PKG
Source
1 PACKAGE BODY PER_SECURITY_PROFILES_PKG as
2 /* $Header: peser01t.pkb 120.3 2010/05/14 07:43:33 rnemani 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)))
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)))
299 AND ( (Recinfo.exclude_person_flag = X_Exclude_Person_Flag)
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,
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,
389 view_all_payrolls_flag = X_View_All_Payrolls_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 --Bug 5021035 starts here5021035
426 l_dummy_number number;
427 v_record_exists boolean := FALSE;
428 l_security_profile_id per_security_profiles.SECURITY_PROFILE_ID%type;
429 cursor prfl_assignmen(p_profile_id number) is select null
430 from PER_SEC_PROFILE_ASSIGNMENTS a
431 where a.security_profile_id = p_profile_id;
432 --Bug 5021035 ends here
433 BEGIN
434 --Bug 5021035 starts here5021035
435 select security_profile_id into l_security_profile_id from per_security_profiles where rowid =X_Rowid;
436
437 open prfl_assignmen(l_security_profile_id);
438 fetch prfl_assignmen into l_dummy_number;
439 v_record_exists := prfl_assignmen%FOUND;
440 close prfl_assignmen;
441 if v_record_exists then
442 hr_utility.set_message(800,'PER_449748_PROFILE_DEL');
443 hr_utility.raise_error;
444 end if;
445 --Bug 5021035 ends here
446 DELETE FROM per_security_profiles
447 WHERE rowid = X_Rowid;
448
449 if (SQL%NOTFOUND) then
450 hr_utility.set_message(801, 'HR_6153_APPL_PROCEDURE_FAIL');
451 hr_utility.set_message_token('PROCEDURE','PER_SECURITY_PROFILES_V_PKG');
452 hr_utility.set_message_token('STEP','4');
453 hr_utility.raise_error;
454 end if;
455 END Delete_Row;
456 ------------------------------------------------------------------------------
457 procedure check_uniqueness(
458 p_security_profile_name varchar2,
459 p_row_id varchar2) is
460 l_dummy number;
461 --
462 cursor c1 is
463 select 1
464 from per_security_profiles
465 where upper(security_profile_name) = upper(P_SECURITY_PROFILE_NAME)
466 and (rowid <> P_ROW_ID or P_ROW_ID is null);
467 begin
468 open c1;
469 fetch c1 into l_dummy;
470 --
471 if c1%found then
472 close c1;
473 fnd_message.set_name('PAY', 'PER_7062_DEF_SECPROF_EXISTS');
474 fnd_message.raise_error;
475 end if;
476 close c1;
477 --
478 end check_uniqueness;
479 ------------------------------------------------------------------------------
480 procedure chk_reporting_username_unique(
481 p_reporting_oracle_username varchar2,
482 p_row_id varchar2,
483 p_is_base_user IN OUT NOCOPY varchar2) is
484 l_dummy number;
485 --
486 -- Uniqueness check on reporting username as well as returning whether this
487 -- user is the base user. This info will be used to ensure that View All = Y.
488 --
489 cursor c1 is
490 select 1
491 from per_security_profiles
492 where reporting_oracle_username = P_REPORTING_ORACLE_USERNAME
493 and (rowid <> P_ROW_ID or P_ROW_ID is null);
494 --
495 cursor c2 is
496 select 1
497 from all_tables
498 where owner = P_REPORTING_ORACLE_USERNAME
499 and table_name = 'PER_ALL_PEOPLE_F';
500 --
501 begin
502 open c1;
503 fetch c1 into l_dummy;
504 if c1%found then
505 close c1;
506 fnd_message.set_name('PAY', 'PER_7063_DEF_SECPROF_USERNAME');
507 fnd_message.raise_error;
508 end if;
509 close c1;
510 --
511 open c2;
512 fetch c2 into l_dummy;
513 if c2%found then
514 p_is_base_user := 'Y';
515 else
516 p_is_base_user := 'N';
517 end if;
518 close c2;
519 --
520 end chk_reporting_username_unique;
521 ------------------------------------------------------------------------------
522 procedure pre_delete_validation(
523 p_security_profile_id number,
524 p_view_all_flag varchar2,
525 p_secgen_warn IN OUT NOCOPY varchar2) is
526 l_dummy number;
527 --
528 -- Return p_secgen_warn = Y if there are people in the person_list for this
529 -- security profile (where client-side should act on warning).
530 --
531 cursor ppl is
532 select 1
533 from per_person_list
534 where security_profile_id = P_SECURITY_PROFILE_ID;
535 begin
536 --
537 -- Checks for business groups using this profile (where profiles are
538 -- created on creation of business group).
539 --
540 p_secgen_warn := 'N';
541 if p_view_all_flag = 'N' then
542 open ppl;
543 fetch ppl into l_dummy;
544 if ppl%found then
545 p_secgen_warn := 'Y';
546 end if;
547 close ppl;
548 end if;
549 --
550 end pre_delete_validation;
551 ------------------------------------------------------------------------------
552 function check_sql_fragment(p_restriction_text VARCHAR2)
553 return boolean is
554 l_sql_statement varchar2(20000);
555 l_cursor INTEGER;
556 begin
557 --
558
559 IF instr(upper(p_restriction_text), 'PERSON_TYPE.') = 0 THEN
560
561 l_sql_statement:='select PERSON.person_id
562 from per_all_people_f PERSON
563 , per_all_assignments_f ASSIGNMENT
564 where PERSON.person_id=ASSIGNMENT.person_id
565 and '||p_restriction_text;
566
567 ELSE
568
569 l_sql_statement:='select PERSON.person_id
570 from per_all_people_f PERSON
571 , per_all_assignments_f hr_asg
572 where PERSON.person_id=hr_asg.person_id
573 and EXISTS (SELECT NULL FROM per_all_assignments_f ASSIGNMENT,
574 per_all_people_f PERSON,
575 per_person_type_usages_f PERSON_TYPE
576 WHERE ASSIGNMENT.rowid = hr_asg.rowid
577 AND PERSON.person_id = ASSIGNMENT.person_id
578 AND trunc(sysdate)
579 BETWEEN PERSON.effective_start_date AND PERSON.effective_end_date
580 AND PERSON.person_id = PERSON_TYPE.person_id
581 AND trunc(sysdate) BETWEEN
582 PERSON_TYPE.effective_start_date AND
583 PERSON_TYPE.effective_end_date AND '||p_restriction_text||')';
584
585 END IF;
586
587
588 -- Bug 3648079
589 -- This procedure was executing the SQL statement and affecting the
590 -- performance of 'Verify' custom SQL function in the Define Security
591 -- Profile form. The procedure is changed to parse the sql statement
592 -- instead of executing it.
593 l_cursor := dbms_sql.open_cursor;
594 dbms_sql.parse(l_cursor,l_sql_statement,dbms_sql.NATIVE);
595 return TRUE;
596
597 EXCEPTION
598 when others then
599 --
600 -- There was an error whilst trying to verify the
601 -- custom restriction. Wrap the SQL error in a message
602 -- and raise it back to the user.
603 --
604 fnd_message.set_name('PER','HR_289835_PSP_CUSTOM_ERR');
605 fnd_message.set_token('SQLERRM',SQLERRM);
606 fnd_message.raise_error;
607 end check_sql_fragment;
608
609 -- Bug #2809163
610 procedure check_assigned_sec_profile(p_security_profile_id number)is
611 --
612 l_exists varchar2(1);
613 --
614 /*cursor sec_profile is
615 select 'x'
616 from fnd_profile_option_values fpv
617 where fpv.profile_option_value = to_char(p_security_profile_id);
618 */
619
620 -- Modified the cursor as follows for bug 5006762
621 --
622 cursor sec_profile is
623 select 'x'
624 from fnd_profile_option_values fpv ,
625 fnd_profile_options fp
626 where fpv.profile_option_value = to_char(p_security_profile_id)
627 -- added the following
628 and fp.application_id = fpv.application_id
629 and fp.profile_option_id = fpv.profile_option_id
630 and fp.PROFILE_OPTION_NAME = 'PER_SECURITY_PROFILE_ID';
631
632 -- end of bug 5006762
633 --
634 begin
635 --
636 -- Checks that the security profile is already assigned to any responsibility
637 -- If assigned, then user is not supposed to delete the profile
638 open sec_profile;
639 fetch sec_profile into l_exists;
640 IF sec_profile%found THEN
641 hr_utility.set_message(800, 'PER_289480_SEC_PROFILE_VALUE');
642 close sec_profile;
643 hr_utility.raise_error;
644 END IF;
645 --
646 close sec_profile;
647 --
648 end check_assigned_sec_profile;
649 --
650
651 END PER_SECURITY_PROFILES_PKG;