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

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;

/

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

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;

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