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;