The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Security_Profile_Id IN OUT NOCOPY NUMBER,
X_Business_Group_Id NUMBER,
X_Position_Id NUMBER,
X_Organization_Id NUMBER,
X_Position_Structure_Id NUMBER,
X_Organization_Structure_Id NUMBER,
X_Include_Top_Org_Flag VARCHAR2,
X_Include_Top_Position_Flag VARCHAR2,
X_Security_Profile_Name VARCHAR2,
X_View_All_Applicants_Flag VARCHAR2,
X_View_All_Employees_Flag VARCHAR2,
X_View_All_Flag VARCHAR2,
X_View_All_Organizations_Flag VARCHAR2,
X_View_All_Payrolls_Flag VARCHAR2,
X_View_All_Positions_Flag VARCHAR2,
X_View_All_Cwk_Flag VARCHAR2,
X_View_All_Contacts_Flag VARCHAR2,
X_View_All_Candidates_Flag VARCHAR2,
X_Include_Exclude_Payroll_Flag VARCHAR2,
X_Reporting_Oracle_Username VARCHAR2,
X_Allow_Granted_Users_Flag VARCHAR2,
X_Restrict_By_Supervisor_Flag VARCHAR2,
X_Supervisor_Levels NUMBER,
X_Exclude_Secondary_Asgs_Flag VARCHAR2,
X_Exclude_Person_Flag VARCHAR2,
X_Named_Person_Id NUMBER,
X_Custom_Restriction_Flag VARCHAR2,
X_Restriction_Text VARCHAR2,
X_Exclude_Business_Groups_Flag VARCHAR2,
X_Org_Security_Mode VARCHAR2,
X_Restrict_On_Individual_Asg VARCHAR2,
X_Top_Organization_Method VARCHAR2,
X_Top_Position_Method VARCHAR2,
X_Request_Id NUMBER,
X_Program_Application_Id NUMBER,
X_Program_Id NUMBER,
X_Program_Update_Date DATE
) IS
CURSOR C IS SELECT rowid FROM per_security_profiles
WHERE security_profile_id = X_Security_Profile_Id;
CURSOR C2 IS SELECT per_security_profiles_s.nextval FROM sys.dual;
INSERT INTO per_security_profiles(
security_profile_id,
business_group_id,
position_id,
organization_id,
position_structure_id,
organization_structure_id,
include_top_organization_flag,
include_top_position_flag,
security_profile_name,
view_all_applicants_flag,
view_all_employees_flag,
view_all_flag,
view_all_organizations_flag,
view_all_payrolls_flag,
view_all_positions_flag,
view_all_cwk_flag,
view_all_contacts_flag,
view_all_candidates_flag,
include_exclude_payroll_flag,
reporting_oracle_username,
allow_granted_users_flag,
restrict_by_supervisor_flag,
supervisor_levels,
exclude_secondary_asgs_flag,
exclude_person_flag,
named_person_id,
custom_restriction_flag,
restriction_text,
exclude_business_groups_flag,
org_security_mode,
restrict_on_individual_asg,
top_organization_method,
top_position_method,
request_id,
program_application_id,
program_id,
program_update_date
) VALUES (
X_Security_Profile_Id,
X_Business_Group_Id,
X_Position_Id,
X_Organization_Id,
X_Position_Structure_Id,
X_Organization_Structure_Id,
X_Include_Top_Org_Flag,
X_Include_Top_Position_Flag,
X_Security_Profile_Name,
X_View_All_Applicants_Flag,
X_View_All_Employees_Flag,
X_View_All_Flag,
X_View_All_Organizations_Flag,
X_View_All_Payrolls_Flag,
X_View_All_Positions_Flag,
X_View_All_Cwk_Flag,
X_View_All_Contacts_Flag,
X_View_All_Candidates_Flag,
X_Include_Exclude_Payroll_Flag,
X_Reporting_Oracle_Username,
X_Allow_Granted_Users_Flag,
X_Restrict_By_Supervisor_Flag,
X_Supervisor_Levels,
X_Exclude_Secondary_Asgs_Flag,
X_Exclude_Person_Flag,
X_Named_Person_Id,
X_Custom_Restriction_Flag,
X_Restriction_Text,
X_Exclude_Business_Groups_Flag,
X_Org_Security_Mode,
X_Restrict_On_Individual_Asg,
X_Top_Organization_Method,
X_Top_Position_Method,
X_Request_Id,
X_Program_Application_Id,
X_Program_Id,
X_Program_Update_Date
);
END Insert_Row;
SELECT *
FROM per_security_profiles
WHERE rowid = X_Rowid
FOR UPDATE of Security_Profile_Id NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Security_Profile_Id NUMBER,
X_Business_Group_Id NUMBER,
X_Position_Id NUMBER,
X_Organization_Id NUMBER,
X_Position_Structure_Id NUMBER,
X_Organization_Structure_Id NUMBER,
X_Include_Top_Org_Flag VARCHAR2,
X_Include_Top_Position_Flag VARCHAR2,
X_Security_Profile_Name VARCHAR2,
X_View_All_Applicants_Flag VARCHAR2,
X_View_All_Employees_Flag VARCHAR2,
X_View_All_Flag VARCHAR2,
X_View_All_Organizations_Flag VARCHAR2,
X_View_All_Payrolls_Flag VARCHAR2,
X_View_All_Positions_Flag VARCHAR2,
X_View_All_Cwk_flag VARCHAR2,
X_View_All_Contacts_flag VARCHAR2,
X_View_All_Candidates_flag VARCHAR2,
X_Include_Exclude_Payroll_Flag VARCHAR2,
X_Reporting_Oracle_Username VARCHAR2,
X_Allow_Granted_Users_Flag VARCHAR2,
X_Restrict_By_Supervisor_Flag VARCHAR2,
X_Supervisor_Levels NUMBER,
X_Exclude_Secondary_Asgs_Flag VARCHAR2,
X_Exclude_Person_Flag VARCHAR2,
X_Named_Person_Id NUMBER,
X_Custom_Restriction_Flag VARCHAR2,
X_Restriction_Text VARCHAR2,
X_Exclude_Business_Groups_Flag VARCHAR2,
X_Org_Security_Mode VARCHAR2,
X_Restrict_On_Individual_Asg VARCHAR2,
X_Top_Organization_Method VARCHAR2,
X_Top_Position_Method VARCHAR2,
X_Request_Id NUMBER,
X_Program_Application_Id NUMBER,
X_Program_Id NUMBER,
X_Program_Update_Date DATE
) IS
BEGIN
UPDATE per_security_profiles
SET
security_profile_id = X_Security_Profile_Id,
business_group_id = X_Business_Group_Id,
position_id = X_Position_Id,
organization_id = X_Organization_Id,
position_structure_id = X_Position_Structure_Id,
organization_structure_id = X_Organization_Structure_Id,
include_top_organization_flag = X_Include_Top_Org_Flag,
include_top_position_flag = X_Include_Top_Position_Flag,
security_profile_name = X_Security_Profile_Name,
view_all_applicants_flag = X_View_All_Applicants_Flag,
view_all_employees_flag = X_View_All_Employees_Flag,
view_all_flag = X_View_All_Flag,
view_all_organizations_flag = X_View_All_Organizations_Flag,
view_all_payrolls_flag = X_View_All_Payrolls_Flag,
view_all_positions_flag = X_View_All_Positions_Flag,
view_all_cwk_flag = X_View_All_Cwk_Flag,
view_all_contacts_flag = X_View_All_Contacts_flag,
view_all_candidates_flag = X_View_All_Candidates_flag,
include_exclude_payroll_flag = X_Include_Exclude_Payroll_Flag,
reporting_oracle_username = X_Reporting_Oracle_Username,
allow_granted_users_flag = X_Allow_Granted_Users_Flag,
restrict_by_supervisor_flag = X_Restrict_By_Supervisor_Flag,
supervisor_levels = X_Supervisor_Levels,
exclude_secondary_asgs_flag = X_Exclude_Secondary_Asgs_Flag,
exclude_person_flag = X_Exclude_Person_Flag,
named_person_id = X_Named_Person_Id,
custom_restriction_flag = X_Custom_Restriction_Flag,
restriction_text = X_Restriction_Text,
exclude_business_groups_flag = X_Exclude_Business_Groups_Flag,
org_security_mode = X_Org_Security_Mode,
restrict_on_individual_asg = X_Restrict_On_Individual_Asg,
top_organization_method = X_Top_Organization_Method,
top_position_method = X_Top_Position_Method,
request_id = X_Request_Id,
program_application_id = X_Program_Application_Id,
program_id = X_Program_Id,
program_update_date = X_Program_Update_Date
WHERE rowid = X_rowid;
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
BEGIN
DELETE FROM per_security_profiles
WHERE rowid = X_Rowid;
END Delete_Row;
select 1
from per_security_profiles
where upper(security_profile_name) = upper(P_SECURITY_PROFILE_NAME)
and (rowid <> P_ROW_ID or P_ROW_ID is null);
select 1
from per_security_profiles
where reporting_oracle_username = P_REPORTING_ORACLE_USERNAME
and (rowid <> P_ROW_ID or P_ROW_ID is null);
select 1
from all_tables
where owner = P_REPORTING_ORACLE_USERNAME
and table_name = 'PER_ALL_PEOPLE_F';
procedure pre_delete_validation(
p_security_profile_id number,
p_view_all_flag varchar2,
p_secgen_warn IN OUT NOCOPY varchar2) is
l_dummy number;
select 1
from per_person_list
where security_profile_id = P_SECURITY_PROFILE_ID;
end pre_delete_validation;
l_sql_statement:='select PERSON.person_id
from per_all_people_f PERSON
, per_all_assignments_f ASSIGNMENT
where PERSON.person_id=ASSIGNMENT.person_id
and '||p_restriction_text;
select 'x'
from fnd_profile_option_values fpv
where fpv.profile_option_value = to_char(p_security_profile_id);
select 'x'
from fnd_profile_option_values fpv ,
fnd_profile_options fp
where fpv.profile_option_value = to_char(p_security_profile_id)
-- added the following
and fp.application_id = fpv.application_id
and fp.profile_option_id = fpv.profile_option_id
and fp.PROFILE_OPTION_NAME = 'PER_SECURITY_PROFILE_ID';