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";

Questions Related to Versions and Instance names of Database and EBS

1).To Get versions
SELECT SUBSTR(product,1,15) product,SUBSTR(version,1,15) version,SUBSTR(status,1,15) status
FROM product_component_version;
2).To Get Version
select * from v$version where banner like 'Oracle%';
3).To Get  Applications/Versions/Patch Levels
select   substr(a.APPLICATION_NAME,1,60) "Application Name"
,  substr(i.PRODUCT_VERSION,1,4)  "Version"
,  i.PATCH_LEVEL    "Patch Level"
,  i.APPLICATION_ID   "Application ID"
,  i.LAST_UPDATE_DATE   "Last Update"
from   APPS.FND_PRODUCT_INSTALLATIONS  i
,  APPS.FND_APPLICATION_ALL_VIEW  a
where   i.APPLICATION_ID   = a.APPLICATION_ID
order by a.APPLICATION_NAME
4). To Get objects under owner APPS (PKG,Views)
select owner,object_name,object_type from dba_objects where owner='APPS';
5).To Get server,instance or machine using ?
SELECT name FROM v$database;
select instance_name from v$instance;
select * from global_name;
SELECT VALUE FROM V$PARAMETER WHERE NAME='db_name';
select to_number(translate(substr(version,1,9),'1.$','1')) from v$instance;
select s.machine from v$session s where s.audsid = userenv('sessionid');
select global_name from global_name;
select sys_context('USERENV','DB_NAME') AS instance from dual;
select substr(release_name,1,7) Version,
    substr(rpad(MULTI_ORG_FLAG,2,' '),1,2) "MO",
    substr(rpad(MULTI_CURRENCY_FLAG,3,' '),1,3) "MRC"
    from apps.fnd_product_groups;
8).To Get Random Numbers
select dbms_random.random from dual;
9).To See wether multi org flag is set or not ?
select multi_org_flag from fnd_product_groups;

10). To get Patches levels
SELECT fa.application_short_name app,
  fpi.patch_level
FROM fnd_product_installations fpi,
  fnd_application fa
WHERE fa.application_id = fpi.application_id;
         (or)
 SELECT * FROM ad_applied_patches;

Initializing apps

DECLARE
   L_APPL_ID             number;
   l_appl_name           VARCHAR2 (100) := 'PER';
   L_USER_ID             number;
   l_user_name           VARCHAR2 (100) := 'XX_ADMIN';
   l_responsibility_id   NUMBER;
   l_resp_name           VARCHAR2 (200) := 'US Super HRMS Manager';
BEGIN

-- To get the Application ID of given Application.
   SELECT application_id
     INTO l_appl_id
     FROM fnd_application
    WHERE application_short_name = l_appl_name;

-- To get the User ID information of  given user
   SELECT user_id
     INTO l_user_id
     FROM fnd_user
    WHERE user_name = l_user_name;

-- To get the Resp ID information of the given responsibility.
   SELECT responsibility_id
     INTO l_responsibility_id
     FROM fnd_responsibility_tl
    WHERE responsibility_name = l_resp_name AND application_id = l_appl_id;

--Initialixze the Application to use the API.
   fnd_global.apps_initialize (l_user_id, l_responsibility_id, l_appl_id);
END;

How to format XML in Notepad++?

I have notepad++ and I got some XML code which is very long. When I pasted it in Notepad++,
there is a long line of code.(difficult to read and work with.)
Now, I want to know if there is a simple way to make the text readable. (by readable I mean properly tabbed code)

Here is the solution follow the process.
--plugins --plugin manager --show plugin manager --check XML tools
--install
--restart NOTEPAD++
--open xml file --plugins --XML tools --pretty print(xml only -- with line breaks)

To Create LOV and Date (calendar)LOV for WebADI

To create window LOV (value set) and Date LOV(calendar) for WebADI cloumns

--To Create LOV
declare
  ln_application_id  number;
  lc_integtr_code    varchar2 (50);
  lx_interface_code  varchar2 (50);
  lx_param_list_code varchar2 (50);
  ln_application_id  number;
begin
  bne_integrator_utils.create_table_lov (
  p_application_id => 800,
  p_interface_code => '<INTERFACE_CODE>',
  p_interface_col_name => '<P_FULL_NAME>', ----proc params
  p_id_col => 'LOOKUP_CODE',
  p_mean_col => 'LOOKUP_CODE',
  p_desc_col => null (or 'DESCRIPTION'),
  p_table => 'FND_COMMON_LOOKUPS',
  P_ADDL_W_C => ' LOOKUP_TYPE=''XX'' AND ENABLED_FLAG =''Y''',
  p_window_caption => 'SELECT Full Name',
  p_window_width => 400, p_window_height => 300,
  p_table_block_size => 10,
  p_table_sort_order => 'Yes',
  p_user_id => 83087 );
end;
/


-- To Add Date LOV(calender) to Web ADI Column
   declare
   ln_application_id    number;
   lc_integtr_code      varchar2 (50);
   lx_interface_code    varchar2 (50);
   lx_param_list_code   varchar2 (50);
   ln_application_id    number;
begin
   bne_integrator_utils.create_calendar_lov
                                (p_application_id          => 800,
                                 p_interface_code          => '<interface_code>',
                                 p_interface_col_name      => 'P_START_DATE',--proc params
                                 p_window_caption          => 'Select Date',
                                 p_window_width            => 400,
                                 p_window_height           => 300,
                                 p_table_columns           => 'START_DATE',
                                 p_user_id                 => 83087
                                );
end;
/

To Delete an Integrator and Interface from back end

-- To Get Integrator_code :
 SELECT applicatioN_id, Integrator_code, user_name FROM bne_integrators_vl
WHERE user_name  = '<Integrator Name>';

--To Get Interface Code :
SELECT * FROM bne_interfaces_b WHERE Integrator_code = '<Integrator_code>';

-- To Get interface columns :
SELECT * FROM bne_interface_cols_b WHERE interface_code = '<interface_code>';

--To Delete an Interface :
DECLARE
   v_value   NUMBER;
BEGIN
   V_VALUE :=
      bne_integrator_utils.DELETE_INTERFACE (800, '<interface_code>');
   DBMS_OUTPUT.put_line (v_value);
END;
/
commit;
/
 *800 is Application_id

--To Delete an Intergrator :
declare
l_number number:=0;
begin
l_number:=BNE_INTEGRATOR_UTILS.DELETE_INTEGRATOR(P_APPLICATION_ID => 800 ,
P_INTEGRATOR_CODE => '<integrator_code>');
dbms_output.put_line(' l_number : '||l_number);
end;
/
COMMIT;
/
*800 is Application_id

"BNE_UIX_PHYSICAL_DIRECTORY is not configured".

While generating the document through WEB ADI, the below error message is coming.
"BNE_UIX_PHYSICAL_DIRECTORY is not configured".

Solution

Make sure that no value for the profile option BNE_UIX_PHYSICAL_DIRECTORY exists in all R12 templates.

To implement the solution, please execute the following steps:

1. Go into the responsibility: System Administrator

2. Navigate to Profile -> System -> Profile: BNE%Directory%

3. Remove any value in the profile BNE_UIX_PHYSICAL_DIRECTORY (null value is the default)

4. Bounce the web server and retest the issue.