TO find OUT previous, CURRENT AND NEXT years FIRST DATE AND last DATE

SELECT TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, -TO_NUMBER (TO_CHAR (SYSDATE, 'mm')))), 'YEAR' ) privious_year_first_date,
  TRUNC (last_day (add_months (sysdate,      -to_number (TO_CHAR (sysdate, 'mm')))) ) privious_year_last_date,
  TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, 12   - TO_NUMBER (TO_CHAR (SYSDATE, 'mm')) ) ), 'YEAR' ) current_year_first_date,
  TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, 12   - TO_NUMBER (TO_CHAR (SYSDATE, 'mm')) ) ) ) current_year_last_date,
  TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, 24   - TO_NUMBER (TO_CHAR (SYSDATE, 'mm')) ) ), 'YEAR' ) next_year_first_date,
  TRUNC (last_day (add_months (sysdate, 24   - to_number (TO_CHAR (sysdate, 'mm')) ) ) ) next_year_last_date
FROM DUAL;

Difference Between Security Group and Security Profile

Oracle HRMS provides two different security models which enable you to set up security specifically for your enterprise: Standard HRMS security and Security Groups Enabled security (formerly called Cross Business Group Responsibility Security). This note provides an overview of both models and a simplistic setup for the Security Groups Enabled Model.

Standard Security Model
-----------------------
Standard HRMS security restricts access to your enterprises records and data. To set up Standard HRMS Security, you first create responsibilities and then define the windows, menus items, workflows, data and records the user can access. The System Administrator then assigns users to as many of these responsibilities as is required to complete their business tasks.

If you are using Standard HRMS Security, you must ensure that the Enable Multiple Security Groups profile option is set to the default value No. You must then create a security profile for each distinct security grouping of employees your enterprise requires.

You then create a responsibility for each user type you require, for example HR Manager, Branch Manager and Salesperson, and link the security profile and responsibility to a business group. These three elements create a security grouping to which you assign employees.

Security Groups Enabled Model
-----------------------------
The main difference between the two security models is that the Security Groups Enabled model enables your enterprise to share security profiles and responsibilities between users and business groups. This reduces the set up time, and also increases the flexibility of this security model. The key to re-usability is the relationship between the security elements and the users that you create during the set up process.

Attention: Once you have set up Security Groups Enabled security, you cannot revert to Standard HRMS Security.

Security Groups Enabled Model Step-by-Step Setup Information
------------------------------

1. Set Enable Security Groups profile option for the application Oracle Human Resources to YES.

Navigation: System Administration -> Profile -> System.
Application = Oracle Human Resources, Find %Enable%


2. Define a user.

Responsibility - System Administrator.
Navigation: Security -> User -> Define

3. Define a responsibility.

Responsibility - System Administrator.
Navigation: Security -> Responsibility -> Define

4. Set HR User type profile option for the new responsibility to HR User or HR w/Payroll User.
(HR:User Type)
Navigation: System Administration -> Profile -> System.
Responsibility = <new responsibility>, Find = HR%

5. Run Enable Multiple Security Group process.
Attention: Once you have set up Security Groups Enabled security, you CANNOT revert to Standard HRMS Security.

Responsibility - HRMS Manager
Navigation: Process & Reports -> Submit Processes and Reports

Select Enable Multiple Security Group.

6. Define a security profile with the restrictions required
(organization or position hierarchies or payroll).

Responsibility - HRMS Manager
Navigation: Security -> Profiles

7. Assign security profiles.

Responsibility - HRMS Manager
Navigation: Security -> Assign Security Profiles.
Combine the Username, Business Group and responsibility as of the appropriate start date.

8. Run Security List Maintenance (LISTGEN)

Responsibility - HRMS Manager
Navigation: Process & Reports -> Submit Processes and Reports

Select Security List Maintenance

To get Business Group, Legal Entity Name, Operating Unit Details

SELECT distinct hrl.country, hroutl_bg.NAME bg, hroutl_bg.organization_id,
       lep.legal_entity_id, lep.NAME legal_entity,
       hroutl_ou.NAME ou_name, hroutl_ou.organization_id org_id,
       hrl.location_id,
       hrl.location_code,
       glev.FLEX_SEGMENT_VALUE
  FROM xle_entity_profiles lep,
       xle_registrations reg,
       hr_locations_all hrl,
       hz_parties hzp,
       fnd_territories_vl ter,
       hr_operating_units hro,
       hr_all_organization_units_tl hroutl_bg,
       hr_all_organization_units_tl hroutl_ou,
       hr_organization_units gloperatingunitseo,
       gl_legal_entities_bsvs glev
 WHERE lep.transacting_entity_flag = 'Y'
   AND lep.party_id = hzp.party_id
   AND lep.legal_entity_id = reg.source_id
   AND reg.source_table = 'XLE_ENTITY_PROFILES'
   AND hrl.location_id = reg.location_id
   AND reg.identifying_flag = 'Y'
   AND ter.territory_code = hrl.country
   AND lep.legal_entity_id = hro.default_legal_context_id
   AND gloperatingunitseo.organization_id = hro.organization_id
   AND hroutl_bg.organization_id = hro.business_group_id
   AND hroutl_ou.organization_id = hro.organization_id
   AND glev.legal_entity_id = lep.legal_entity_id

To Create User,Responsibility and delete Responsibility from DataBase

1) API to create FND User
BEGIN
  fnd_user_pkg.CreateUser ( x_user_name =>'Operations',
                            x_owner => NULL,
                            x_unencrypted_password =>'welcome1',
                            x_start_date =>TO_DATE('01-JAN-2000'),
                            x_end_date => NULL, x_last_logon_date => NULL,
                            x_description => 'Operations User',
                            x_password_date => NULL,
                            x_password_accesses_left => NULL,
                            x_employee_id => NULL,
                            x_email_address => NULL,
                            x_fax => NULL,
                            x_customer_id => NULL,
                            x_supplier_id => NULL);
  COMMIT;
END;


API to add responsibilities to a user

BEGIN
  fnd_user_pkg.addresp(username =>'XXTEST',
                       resp_app =>'SYSADMIN',
                       resp_key =>'SYSTEM_ADMINISTRATOR',
                       security_group =>'STANDARD',
                       description => NULL,
                       start_date =>TO_DATE('01-JAN-2000'),
                       end_date =>NULL );
  COMMIT;
END;

--(or)
declare

    l_user_name varchar2(100) := UPPER('XXTEST');
        l_resp_key         varchar2(100) := '&resp_key';
        l_app_short_name varchar2(100);

begin
 select application_short_name
 into l_app_short_name
 from fnd_application fa, fnd_responsibility fr
 where fa.application_id = fr.application_id
 and trunc(sysdate) between start_date and nvl(end_date, '31-dec-4712')
 and fr.responsibility_key = l_resp_key;

 fnd_user_resp_groups_api.load_row
                              (x_user_name           => l_user_name,
                               x_resp_key            => l_resp_key,
                               x_app_short_name      => l_app_short_name ,
                               x_security_group      => 'STANDARD',
                               x_owner               => 'CUST',
                               x_start_date          => TO_CHAR ( SYSDATE, 'YYYY/MM/DD'),
                               x_end_date            => NULL,
                               x_description         => NULL
                              );

commit;
end;
/

--API to remove responsibilities from a user

BEGIN
  fnd_user_pkg.delresp(username =>'XXTEST',
                       resp_app =>'SYSADMIN',
                       resp_key =>'SYSTEM_ADMINISTRATOR',
                       security_group =>'STANDARD' );
  COMMIT;
END;


--To get Assigned responsibilities to a user
SELECT frt.responsibility_name r_name, fr.application_id, fr.responsibility_id
                  FROM fnd_user fu, fnd_responsibility fr, fnd_responsibility_tl frt, fnd_user_resp_groups furg
                 WHERE fu.user_id = furg.user_id
                   AND fr.responsibility_id = frt.responsibility_id
                        and frt.language = userenv('LANG')
                   AND furg.responsibility_id = fr.responsibility_id
                   AND fu.user_name = &1
                   AND SYSDATE BETWEEN fr.START_DATE AND nvl(fr.end_date, SYSDATE)
                   AND SYSDATE BETWEEN furg.START_DATE AND nvl(furg.end_date, SYSDATE);

To add and delete a responsibilities to user from DataBase(Backend)

--API to add responsibilities to a user

BEGIN
  fnd_user_pkg.addresp(username =>'XXTEST',
                       resp_app =>'SYSADMIN',
                       resp_key =>'SYSTEM_ADMINISTRATOR',
                       security_group =>'STANDARD',
                       description => NULL,
                       start_date =>TO_DATE('01-JAN-2000'),
                       end_date =>NULL );
  COMMIT;
END;

/
(or)
/
declare
  l_user_name      VARCHAR2(100) := UPPER('XXTEST');
  l_resp_key       VARCHAR2(100) := 'SYSTEM_ADMINISTRATOR';
  l_app_short_name VARCHAR2(100);
BEGIN
  SELECT application_short_name
INTO l_app_short_name
FROM fnd_application fa,
  fnd_responsibility fr
WHERE fa.application_id = fr.application_id
AND TRUNC(sysdate) BETWEEN start_date AND NVL(end_date, '31-dec-4712')
AND fr.responsibility_key = l_resp_key;
fnd_user_resp_groups_api.load_row
                              (x_user_name           => l_user_name,
                               x_resp_key            => l_resp_key,
                               x_app_short_name      => l_app_short_name ,
                               x_security_group      => 'STANDARD',
                               x_owner               => 'CUST',
                               x_start_date          => TO_CHAR ( SYSDATE, 'YYYY/MM/DD'),
                               x_end_date            => NULL,
                               x_description         => NULL
                              );

commit;
end;
/
--API to remove responsibilities from that user

BEGIN
  fnd_user_pkg.delresp(username =>'XXTEST',
                       resp_app =>'SYSADMIN',
                       resp_key =>'SYSTEM_ADMINISTRATOR',
                       security_group =>'STANDARD' );
  COMMIT;
END;

To Create a new user from DataBase(Backend)

-- API to create User

BEGIN
  fnd_user_pkg.CreateUser ( x_user_name =>'XXTEST',
                            x_owner => NULL,
                            x_unencrypted_password =>'welcome1',
                            x_start_date =>TO_DATE('01-JAN-2000'),
                            x_end_date => NULL, x_last_logon_date => NULL,
                            x_description => 'Operations User',
                            x_password_date => NULL,
                            x_password_accesses_left => NULL,
                            x_employee_id => NULL,
                            x_email_address => NULL,
                            x_fax => NULL,
                            x_customer_id => NULL,
                            x_supplier_id => NULL);
  COMMIT;
END;

AOL Queries

--SQL To List All Oracle Applications With Its Version
SELECT fa.application_short_name,
  fat.application_name,
  fpi.product_version
FROM apps.fnd_application fa,
  apps.fnd_application_tl fat,
  apps.fnd_product_installations fpi
WHERE fa.application_id = fpi.application_id
AND fa.application_id   = fat.application_id
and fat.language        = userenv('LANG')
order by application_short_name;

--Query to Find Concurrent Program Responsibilities From Which It Can be Run
SELECT DISTINCT fcpt.user_concurrent_program_name,
  fcpt.description,
  frt.responsibility_name,
  request_group_name
FROM apps.fnd_concurrent_programs_tl fcpt,
  apps.fnd_request_groups frg,
  apps.fnd_request_group_units frgu,
  apps.fnd_responsibility fr,
  apps.fnd_responsibility_tl frt
WHERE fcpt.concurrent_program_id = frgu.request_unit_id
AND frg.request_group_id         = frgu.request_group_id
AND frg.request_group_id         = fr.request_group_id
AND fr.responsibility_id         = frt.responsibility_id
AND fcpt.application_id          = frg.application_id
AND frg.application_id           = frgu.application_id
and fr.application_id            = frt.application_id
AND fcpt.user_concurrent_program_name LIKE 'XX%'--'%&p_conc_programe_name%';

--Query to find Responsibilities attached to a User
SELECT DISTINCT fu.user_id,
 fu.user_name user_name,
 fr.responsibility_id,
 fr.responsibility_name responsiblity,
 fa.application_name application
FROM apps.fnd_user fu,
 apps.fnd_user_resp_groups furg,
 apps.fnd_application_tl fa,
 apps.fnd_responsibility_tl fr
WHERE fu.user_name LIKE UPPER('&username')
AND fu.user_id = furg.user_id
and furg.responsibility_id = fr.responsibility_id
ORDER BY 1;

--Query To Find Value Sets Details
SELECT ffvs.flex_value_set_id,
  ffvs.flex_value_set_name,
  ffvs.description set_description ,
  fl.meaning validation_type,
  ffvt.value_column_name ,
  ffvt.meaning_column_name ,
  ffvt.id_column_name ,
  ffvt.application_table_name ,
  ffvt.additional_where_clause
FROM apps.fnd_flex_value_sets ffvs ,
  apps.fnd_flex_validation_tables ffvt,
  apps.fnd_lookups fl
WHERE ffvs.flex_value_set_id = ffvt.flex_value_set_id(+)
AND ffvs.flex_value_set_name LIKE NVL('&VALUE_SET_NAME_CASE_SENSITIVE','%')
and fl.lookup_type = 'SEG_VAL_TYPES'
AND fl.lookup_code = ffvs.validation_type

--Query to get Create Table Statement for an Existing Table
select dbms_metadata.get_ddl('INDEX','XX_TEMP_TABLE_N1','APPS') from dual;
or

SELECT dbms_metadata.get_ddl('TABLE','PER_ALL_PEOPLE_F','APPS') FROM dual;

--SQL to Get Values at All Levels for a Profile Option
SELECT fpot.user_profile_option_name  "Profile"
       ,fpov.profile_option_value     "Value"
       ,decode(fpov.level_id
             ,10001,'Site'
             ,10002,'Application'
             ,10003,'Responsibility'
             ,10004,'User'
             ,'UNKNOWN')              "Level"
       ,decode(fpov.level_id
             ,10002,fat.application_name
             ,10003,frt.responsibility_name
             ,10004,fu.user_name
             ,'n/a')                  "Level value"
FROM  applsys.fnd_application_tl         fat
      ,applsys.fnd_user                  fu
      ,applsys.fnd_responsibility_tl     frt
      ,applsys.fnd_profile_option_values fpov
      ,applsys.fnd_profile_options       fpo
      ,applsys.fnd_profile_options_tl    fpot
WHERE    UPPER(fpot.user_profile_option_name) LIKE '%&profile_name_case_sensitive%'
  AND    fpo.hierarchy_type = 'SECURITY'
  AND    fpot.profile_option_name   = fpo.profile_option_name
  AND    fpot.LANGUAGE              = 'US'
  AND    fpo.application_id         = fpov.application_id(+)
  AND    fpo.profile_option_id      = fpov.profile_option_id(+)
  AND    fpov.level_value           = frt.responsibility_id(+)
  AND    frt.LANGUAGE(+)            = 'US'
  AND    fpov.level_value           = fu.user_id(+)
  AND    fpov.level_value           = fat.application_id(+)
  AND    fat.LANGUAGE(+)            = 'US'
ORDER  BY "Profile"
          ,"Level"
          ,"Level value"
          ,"Value";