Tuesday, July 1, 2014

LookUps In Oracle Apps

Lookups in Oracle are a collection of values. Lookups are a static collection of codes which are used by oracle for working.
Each lookup when defined will have a lookup code and a corresponding meaning for that code. Oracle will internally use codes for working and use meaning when communicating to the user.The meaning of the lookup code is displayed on the screen and the lookup code is stored in the database.
It can be easily understood by following examples:
Example 1: On a form, we have a field called 'Gender'. This fields possible values can be 'Male' and 'Female'. We can create a lookup of type 'Sex' and store 'Male' and 'Female' as the lookup values. In the runtime, we can retrieve these values and show it as the list of values on the field.

Example 2: A bank provides facilities of Saving Account, Current Account and Fixed Deposit Account. We can define a lookup type called 'Account Type' and associate these account type values with this look type.
When ever a lookup is created and entry will be created in the table FND_LOOKUP_TYPES,
Lookup Information:
select *
  from fnd_lookup_types_VL
 Where lookup_type = '&Enter_lookup_type';
=> Note:You can use the above table to find lookups available in the system

For each lookup type when the value is entered for the lookup entries will be created in the table FND_LOOKUP_VALUES.
Values of a lookup:
select *
  from fnd_lookup_values
 Where lookup_type = '&Enter_lookup_type';

Note:For the lookups already defined in Oracle each is associated with an application , thus when searching for application specific lookups user should search with the specific application.

Lookup type can be defined at three levels:

System: These are lookups which are used internally by the system. Users can see and use the lookup but they cannot disable the existing values or add new values.
Extensible : These are system provided lookups. Users can add their values if they want , but Seeded values by Oracle cannot be changed
User Defined : User Defined lookups are client specific which are created to meet the business specific customization s. These are created by the users and the users can add new values and disable the older ones.

Lookup APIs:
FND_LOOKUP_TYPES_PKG.INSERT_ROW for inserting new lookup type,
FND_LOOKUP_VALUES_PKG.INSERT_ROW for inserting new lookup value.

Oracle Applications package to create and insert (FND) LOOKUP Types & Values
Below is a sample script to create lookup types in Oracle and also to load lookup values for the corresponding lookup types using standard Oracle APIs. I have used the FND Application ID to create lookups and customization level is user. There are many other ways to load data into lookups, we can also use FNDLOAD which is a very good option to migrate data lookups from one instance to another.

DECLARE
   ln_rowid    VARCHAR2 (1000);
   ln_rowid1   VARCHAR2 (1000);
BEGIN
   fnd_lookup_types_pkg.insert_row (x_rowid                    => ln_rowid,
                                    x_lookup_type              => 'XXTEST',
                                    x_security_group_id        => 0,
                                    x_view_application_id      => 0,
                                    x_application_id           => 0,
                                    x_customization_level      => 'U',
                                    x_meaning                  => 'XXTEST',
                                    x_description              => 'XXTEST',
                                    x_creation_date            => SYSDATE,
                                    x_created_by               => 0,
                                    x_last_update_date         => SYSDATE,
                                    x_last_updated_by          => 0,
                                    x_last_update_login        => -1
                                   );
   DBMS_OUTPUT.put_line (ln_rowid);
   fnd_lookup_values_pkg.insert_row (x_rowid                    => ln_rowid1,
                                     x_lookup_type              => 'XXTEST',
                                     x_security_group_id        => 0,
                                     x_view_application_id      => 0,
                                     x_lookup_code              => 'XXHR_INFO',
                                     x_tag                      => NULL,
                                     x_attribute_category       => NULL,
                                     x_attribute1               => NULL,
                                     x_attribute2               => NULL,
                                     x_attribute3               => NULL,
                                     x_attribute4               => NULL,
                                     x_enabled_flag             => 'Y',
                                     x_start_date_active        => TO_DATE ('01-JAN-1950',
                                                                            'DD-MON-YYYY'
                                                                           ),
                                     x_end_date_active          => NULL,
                                     x_territory_code           => NULL,
                                     x_attribute5               => NULL,
                                     x_attribute6               => NULL,
                                     x_attribute7               => NULL,
                                     x_attribute8               => NULL,
                                     x_attribute9               => NULL,
                                     x_attribute10              => NULL,
                                     x_attribute11              => NULL,
                                     x_attribute12              => NULL,
                                     x_attribute13              => NULL,
                                     x_attribute14              => NULL,
                                     x_attribute15              => NULL,
                                     x_meaning                  => 'XXHR Information',
                                     x_description              => NULL,
                                     x_creation_date            => SYSDATE,
                                     x_created_by               => 0,
                                     x_last_update_date         => SYSDATE,
                                     x_last_updated_by          => 0,
                                     x_last_update_login        => -1
                                    );
   DBMS_OUTPUT.put_line (ln_rowid1);
   COMMIT;
END;

Clearing Apache Cache And Bouncing

Clearing Apache Cache without bouncing

Every time you assign yourself a new responsibility or you change a profile option or upload a new piece of code, you need to bounce the listener.Here is a way to get around this without actually bouncing the apache server

Assign "Functional Administrator" responsibility.
Login to the responsibility and click on the "Core Services" tab (top-right).
Click on the "Caching Framework" link in the blue menu bar.
Click on "Global Configuration" link in the left vertical menu.
In the "Cache Policy" region click on the "Clear All Cache" button.
Click the "Yes" button to confirm the action.
Click the "Apply" button to apply the changes.

This clears the Apache cache just like bouncing the Apache listener does, but no one is disconnected and you don't even need the DBA.

Bouncing the apache server

we need to run two scripts:
1)Script which is responsible for bouncing Oracle HTTP Server (adapcctl.sh)
2)Script which responsible for bouncing OC4J container (adoacorectl.sh)

So basically, here is the sequence of steps you need to do :
1) Connect to Host Server using Putty
2) Change the Directory by using this command  cd $ADMIN_SCRIPTS_HOME
3) And Execute below Commands in current Directory
   a) adapcctl.sh stop
   b) adoacorectl.sh stop
   c) adapcctl.sh start
   d) adoacorectl.sh start

 and to check the status adopmnctl.sh

EIT and SIT In Oracle Apps

Oracle Apps provides several unique features that enables the storage of additional data elements that do not find a storage in the seeded Oracle forms. These include Special Information Types (SIT) or Extra Information Types (EIT). Though either one can be used to store data, there are certain peculiarities which an implementation consultant should be aware of. Appropriate choice would make the implementation effective and also enable data security.

The section below addresses some of the key differences between the two

Special Information Types (SIT)

SITs are created from Personal Analysis flexfield, a type of Key Flexfield and are visible across the Business Group
SITs are primarily attached to People related data; can also be used with Jobs, Assignment & Locations
SIT form has a start date and end date associated with the SIT row.
Sometimes, SIT can cause performance issues (though very minor)
SITs have limited security restrictions, thus making them unsuitable for storing sensitive data
The Business thumb rule to be adopted while deciding to go for an SIT is dictated by the nature of the data that is to be stored. Generally individual (personal) data that is not very sensitive can be stored using SIT.

Extra Information Types (EIT)

EITs are a type of Descriptive Key Flexfield; can be restricted to specific Responsibility
EIT's can be attached to People, Assignment, Job, Position, Location and Organization (via a classification)
EIT forms do not have start and end dates. If you want to create dated information, then you would have to use two of the EIT flexfield attributes to store this information.
EIT’s can be used at Person, Assignment, Job, Location level etc. related issues (like Bonus, Incentive info etc.) may be created in EIT
EITs don’t have any performance issue
EITs can be tied to a responsibility so that the appropriate population can also access the EITS. Since EITs are a type of DFF’s it can be migrated across instances using a tool like Kintana which is not possible with Key Flexfields, to which SITs belong.

The Business thumb rule to be adopted while deciding to go for an EIT is dictated by the nature of the data that is to be stored. Generally compensation data that is may be of sensitive nature (like Employee Incentive Bonus plans, Stock option data etc.) can be stored using EIT

To Find RDF Location in Oracle Apps

To find RDF location using concurrent program or execution file name

SELECT Fa.APPLICATION_NAME,
  '$'
  ||Fa.BASEPATH
  ||'/'
  ||'reports/US' REPORTS_PATH
from FND_CONCURRENT_PROGRAMS_TL FC,
FND_APPLICATION_VL Fa
where 1                          =1
and  FA.APPLICATION_ID       =FC.APPLICATION_ID
AND USER_CONCURRENT_PROGRAM_NAME = 'Past Due Invoice Report' ;

SELECT Fa.APPLICATION_NAME,
  '$'
  ||Fa.BASEPATH
  ||'/'
  ||'reports/US' REPORTS_PATH,
  FE.EXECUTION_FILE_NAME
from APPS.FND_EXECUTABLES_VL FE,
  APPS.FND_APPLICATION_VL FA
WHERE FE.EXECUTION_METHOD_CODE='P'
and FA.APPLICATION_ID       =FE.APPLICATION_ID
and FE.EXECUTION_FILE_NAME    ='ARXPDI';

Tuesday, April 8, 2014

To copy responsibilities of one user account to another user account

To copy responsibilities of one user account to another user account

DECLARE
  --
  resp_count NUMBER := 0;
  --
  CURSOR src_user_resp_details
  IS
    SELECT DISTINCT fa.application_short_name,
      fr.responsibility_key                  ,
      fsg.security_group_key
       FROM fnd_application fa      ,
      fnd_responsibility fr         ,
      fnd_user fu                   ,
      fnd_user_resp_groups_all furga,
      fnd_security_groups fsg
      WHERE 1                               = 1
    AND fu.user_name                        = 'XXTEST'
    AND fu.user_id                          = furga.user_id
    AND fa.application_id                   = fr.application_id
    AND furga.responsibility_id             = fr.responsibility_id
    AND furga.responsibility_application_id = fa.application_id
    AND fsg.security_group_id               = furga.security_group_id
    AND furga.end_date IS NULL;
  --
  --
BEGIN
  FOR user_resp_details_rec IN src_user_resp_details
  LOOP
    BEGIN
      --
      fnd_user_pkg.addresp
                 (username            => 'HCM',
                  resp_app            => user_resp_details_rec.application_short_name,
                  resp_key            => user_resp_details_rec.responsibility_key,
                  security_group      => user_resp_details_rec.security_group_key,
                  description         => NULL,
                  start_date          => SYSDATE,
                  end_date            => NULL
                 );
      --
      resp_count := resp_count + 1;
      --
    EXCEPTION
    WHEN OTHERS THEN
      --
      DBMS_OUTPUT.put_line ( 'Error while Adding Responsibility: ' || SQLERRM );
      DBMS_OUTPUT.put_line ( 'resp_app: ' || user_resp_details_rec.application_short_name );
      DBMS_OUTPUT.put_line ( 'resp_key: ' || user_resp_details_rec.responsibility_key );
      --
    END;
  END LOOP;
  --
  DBMS_OUTPUT.put_line (resp_count || ' Responsibilities Successfully Copied!!' );
  --
  COMMIT;
END;
/

Thursday, February 27, 2014

Fndload scripts for WebAdi in R12

--To download Web ADI Integrators,use the below commands

FNDLOAD apps/$apps 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneintegrator.lct XX_TEST_INTG.ldt BNE_INTEGRATORS INTEGRATOR_ASN="PER" INTEGRATOR_CODE="<INTEGRATOR_CODE>"

--To upload Web ADI Integrators, use the below commands

FNDLOAD apps/$apps 0 Y UPLOAD $BNE_TOP/patch/115/import/bneintegrator.lct XX_TEST_INTG.ldt



Update Custom WebADI:

 After moving the custom webadi’s from one instance to another instance, whenever you query for your integrator,update option is disabled for that particular custom Integrators.
 To get enabled ‘Update’ use the below query.

UPDATE bne_integrators_b
SET source  = 'C'
WHERE integrator_code IN ('INTEGRATOR_CODE');
 

Responsibilities under user in Oracle Apps

SELECT r.responsibility_key,a.application_name, fa.APPLICATION_SHORT_NAME,u.user_name
    FROM fnd_user u,
         fnd_user_resp_groups ur,
         fnd_responsibility r,
         fnd_application_tl a,
         fnd_application fa
   WHERE  ur.responsibility_id = r.responsibility_id
         AND u.user_id = ur.user_id
         AND fa.APPLICATION_ID = a.APPLICATION_ID
         and R.APPLICATION_ID = a.APPLICATION_ID
         AND upper(u.user_name) = UPPER ('HCM')
         and a.LANGUAGE = 'US'
ORDER BY 1;

Apps initialize in R12

FND_GLOBAL.APPS_INITIALIZE for initializing session in Oracle Ebusiness suite

FND_GLOBAL.APPS_INITIALIZE is used for initializing the session before calling any public or private API's in Oracle Ebusiness suite. Its not required for all the API's but its recommended that you set this profile before making any calls to either private or public API.

utilize the following query to get the respective values

SELECT fnd.user_id ,
  fresp.responsibility_id,
  fresp.application_id,
  fresp.responsibility_name
FROM fnd_user fnd ,
  fnd_responsibility_tl fresp
WHERE fnd.user_name = 'HCM'
AND fresp.responsibility_name LIKE 'US%';

l_user_id is the fnd user ID
l_resp_id is the responsibility ID
l_resp_appl_id is the responsibility application ID.

EXEC fnd_global.APPS_INITIALIZE(l_user_id,l_resp_id,l_resp_appl_id);
  (OR)
BEGIN
  fnd_global.APPS_INITIALIZE(l_user_id,l_resp_id,l_resp_appl_id);
mo_global.init('appl_shrt_name');
--Ex :: exec  mo_global.init('AR');
END;
/
Another option is Help > Diagnostics > Examine and get the values from $profile session values.

To Initialize session

SELECT fnd_global.session_id,USERENV('sessionid') FROM dual;

INSERT INTO FND_SESSIONS VALUES
  (USERENV('sessionid'),TRUNC(sysdate)
  );

Friday, February 21, 2014

Script To Reset Password for Oracle Apps from Backend

DECLARE
  v_user_name    VARCHAR2(30):=upper('HCM'); --HCM is username of my oracle Apps.
  v_new_password VARCHAR2(30):='welcome1';
  v_status       BOOLEAN;
BEGIN
  v_status   := fnd_user_pkg.ChangePassword ( username => v_user_name, newpassword => v_new_password );
  IF v_status =true THEN
    dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);
    COMMIT;
  ELSE
    DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
    ROLLBACK;
  END IF;
END;

Thursday, February 20, 2014

Oracle Global Temporary Tables

GLOBAL TEMPORARY

The data in a global temporary table is private,The data in a temporary table is visible only to the session that inserts the data into the table.

A temporary table has a definition that persists the same as the definitions of regular tables, but it contains either session-specific or transaction-specific data. You specify whether the data is session- or transaction-specific with the ON COMMIT keywords (below)

ON COMMIT

The ON COMMIT clause is relevant only if you are creating a temporary table. This clause specifies whether the data in the temporary table persists for the duration of a transaction or a session.

DELETE ROWS

Specify DELETE ROWS for a transaction-specific temporary table (this is the default). Oracle will truncate the table (delete all its rows) after each commit.

PRESERVE ROWS

Specify PRESERVE ROWS for a session-specific temporary table. Oracle will truncate the table (delete all its rows) when you terminate the session.

Temporary Table Example

The following statement creates a temporary table flight_schedule for use in an automated airline reservation scheduling system. Each client has its own session and can store temporary schedules. The temporary schedules are deleted at the end of the session.

 Ex:
   CREATE GLOBAL TEMPORARY TABLE flight_schedule (
   startdate DATE,
   enddate DATE,
   cost NUMBER)
   ON COMMIT PRESERVE ROWS;
  
 Ex:
  CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  column1  NUMBER,
  column2  NUMBER)
  ON COMMIT DELETE ROWS;
 
 Ex:Using Dynamic SQL creating global temp table
  BEGIN
  EXECUTE immediate 'CREATE GLOBAL TEMPORARY TABLE xx.xx_temp_table   
  (booking_id                     NUMBER, 
   name                           VARCHAR2(100))
  ON COMMIT PRESERVE ROWS';
  EXCEPTION
  WHEN OTHERS THEN
  NULL;
  END;
  / 
 
 Ex:Using Dynamic SQL Drop global temp table
  BEGIN
  EXECUTE immediate 'drop table xx.xx_temp_table';
  EXCEPTION
  WHEN OTHERS THEN
  NULL;
  END;
  /

 

Tuesday, November 26, 2013

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

Tuesday, November 19, 2013

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

Tuesday, October 29, 2013

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;

Thursday, October 24, 2013

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;

Tuesday, October 8, 2013

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)