Recreate Symbolic Links after Oracle Instance Refresh

After the non-Production instances (DEV, QA, TEST, etc.) are cloned/refreshed, you may have noticed that the symbolic links, that are used for host program files, may get overwritten by Production links and are still pointing to Production locations. To have the links work for that instance, you will have to delete the old symbolic links and recreate them.

Below is a bash shell script which can be run anywhere in UNIX. I would save the file as "xxrl_recreate_symlinks.sh" and execute as follow: 

%> ./xxrl_recreate_symlinks.sh

Note:

Please make sure the following are completed before you run the script:

1) File is executable:

%> chmod 777 xxrl_recreate_symlinks.sh

2) The .env file sets the $XX_TOP for that instance based on your company custom top.

#!/bin/sh

##*******************************************************************

## File Name         : xxrl_recreate_symlinks.sh

## File Location     : $XXRL_TOP/bin

## Created By        : Abul Mohsin

## Creation Date     : June 21, 2014

## Description       : This program deletes the old Symbolic Links

##                     that are copied over from PROD refresh, then 

##                     recreates them for that instance. If no *.prog

##                     files found, the program simply exists.

##

## Program Language  : Shell Script

## Specific Module   : Generic for all modules

## Create SymLink    : %> chmod 755 xxrl_data_ldr.prog

##                     %> ln -s $FND_TOP/bin/fndcpesr xxrl_data_ldr

##

## Execute Command   : %> ./xxrl_recreate_symlinks.sh

##

##*******************************************************************

## History: (Date,  Version, Programmer, Change Description)

##-------------------------------------------------------------------

## 06/21/14   1.0  A.Mohsin  Initial version

##

##*******************************************************************

##----------------------------------------------------

##-- set these variables based on user preference

##---------------------------------------------------- 

customTop=$XX_TOP         ##-- this is based on company's custom top

##----------------------------------------------------

##-- set these variables based on user preference

##----------------------------------------------------

fileExt="prog"            ##-- usually .prog file in Oracle

debugMode='N'             ##-- use 'Y' if you need to display everything

##----------------------------------------------------

##-- set up local variables based on previous ones

##----------------------------------------------------

binDir=${customTop}/bin

echo ' '

echo binDir=$binDir

##-- go to BIN directory first

cd $binDir

if [ "$debugMode" = 'Y' ]; then

  pwd

fi

##----------------------------------------------------

##-- get the number of symbolic link files from BIN dir

##----------------------------------------------------

fileListCnt=`ls *.${fileExt} | sed 's/\.[^.]*$//' | wc -l`

echo "${fileExt} File Count $fileListCnt"

echo ' '

##----------------------------------------------------

##-- if 1 or more files found in the BIN directory,

##-- then, get the symbolic link files from BIN directory

##----------------------------------------------------

if [ $fileListCnt -gt 0 ]; then

  fileList=`ls *.${fileExt} | sed 's/\.[^.]*$//'`

  for fileName in ${fileList};

  do

   

    ##----------------------------------------------------

    ##-- check if the file exists and is a symbolicLink

    ##----------------------------------------------------

    if [ $debugMode = 'Y' ]; then

      echo "SymbolicLink fileName="$fileName

    fi

    ##----------------------------------------------------

    ##-- check if the file exists and is a symbolicLink

    ##----------------------------------------------------

    if [[ -f "$fileName" && -L "$fileName" ]]; then

     

     

      ##----------------------------------------------------

      ##-- first, remove the SymbolicLink file

      ##----------------------------------------------------

      echo "  Deleting SymbolicLink $fileName"

     

      rm $fileName

     

      retCode=$?

     

      if [ $retCode != 0 ]; then

        echo "File $fileName was not removed. Please re-verify."

      fi

      ##----------------------------------------------------

      ##-- Recreating SymbolicLink file

      ##----------------------------------------------------

      echo "  Recreating SymbolicLink for $fileName.$fileExt"

      ln -s $FND_TOP/bin/fndcpesr $fileName

      retCode=$?

      if [ $retCode != 0 ]; then

        echo "SymbolicLink $fileName was not created. Please check the .prog file."

      fi

    ##----------------------------------------------------

    ##-- if the file is not a SymbolicLink, show err msg

    ##----------------------------------------------------

    else

      echo "  $fileName is NOT a symlink"

    fi

   

    echo ' '

  done

else

  echo "No SymbolicLink files found"

  echo "Exiting the program"

fi

exit

PL/SQL Developer: Can't Connect to Database Error

Right after you install PL/SQL Developer, you realize that you cannot connect to your databases. You get the following message:

ORA-12154: TNS:could not resolve the connect identifier specified

For a moment, you may think, it is something to do with TNSNAMES.ora file or Pinging issue. It is, however, purely a PL/SQL Developer issue. When I downloaded SQL Developer from Oracle and installed it, I could connect to all my databases fine.

The solution is actually pretty simple. There are probably multiple ORACLE_HOME defined (such as for reports and forms, workflow, discoverer, etc.) in your machine, and PL/SQL Developer is probably not finding the correct one to choose from. All you have to do is to choose the correct version for your PL/SQL Developer. To do so, follow the below instruction.

1. Make sure you are connected to Network/VPN.
2. Open PL/SQL Developer.
3. Click on Tools -> Preferences. Select the very first option: Connection.
4. From Oracle Home drop down list, select the correct version. For example, OH159997875 works for my machine.
5. Restart PL/SQL Developer.
6. Cross your fingers and retry to connect to your databases.
7. If it works, say "Buiya". If it doesn't, scream "F***", and Google search.

Query to find Form Personalization

I found this query at http://ravivarma1985.blogspot.com/2012/05/sql-query-to-get-list-of.html. Thought I would re-post it for my own personal reference. Might come in handy.

SELECT ffv.form_id          "Form ID",

       ffv.form_name        "Form Name",

       ffv.user_form_name   "User Form Name",

       ffv.description      "Form Description",

       ffcr.sequence        "Sequence",

       ffcr.description     "Personalization Rule Name"

  FROM fnd_form_vl             ffv,

       fnd_form_custom_rules   ffcr

 WHERE ffv.form_name = ffcr.form_name

 ORDER BY ffv.form_name, ffcr.sequence;

Query to find Legal Entity, Organization, Company Code

SELECT

       xep.legal_entity_id        "Legal Entity ID",

       xep.name                   "Legal Entity",

       hr_outl.name               "Organization Name",

       hr_outl.organization_id    "Organization ID",

       hr_loc.location_id         "Location ID",

       hr_loc.country             "Country Code",

       hr_loc.location_code       "Location Code",

       glev.flex_segment_value    "Company Code"

  FROM

       xle_entity_profiles            xep,

       xle_registrations              reg,

       --

       hr_operating_units             hou,

       -- hr_all_organization_units      hr_ou,

       hr_all_organization_units_tl   hr_outl,

       hr_locations_all               hr_loc,

       --

       gl_legal_entities_bsvs         glev

 WHERE

       1=1

   AND xep.transacting_entity_flag   =  'Y'

   AND xep.legal_entity_id           =  reg.source_id

   AND reg.source_table              =  'XLE_ENTITY_PROFILES'

   AND reg.identifying_flag          =  'Y'

   AND xep.legal_entity_id           =  hou.default_legal_context_id

   AND reg.location_id               =  hr_loc.location_id

   AND xep.legal_entity_id           =  glev.legal_entity_id

   --

   -- AND hr_ou.organization_id         =  hou.business_group_id

   AND hr_outl.organization_id       =  hou.organization_id

 ORDER BY hr_outl.name

Query to find DFF

Let's say, we need to find Descriptive Flexfield (DFF) called, "Further Job Information". In the following example, I am trying to get all the information for "US" context code.

The following query will display the DFF related information. You can try changing "fdfv.title" value too see different DFF.

-----------------------------------------------------------------------------

-- Query to find DFF information

-----------------------------------------------------------------------------

SELECT

       fdf.title                             "DFF Title",

       fdf.application_table_name            "Application Table",

       fdf.context_column_name               "Context Column Name",

       --

       fdfcu.descriptive_flex_context_code   "DFF Context Code",

       fdfcu.column_seq_num                  "Sequence",

       fdfcu.end_user_column_name            "Segment Name",

       fdfcu.application_column_name         "Column Name",

       --

       ffv.flex_value_set_name               "Value Set Name"

  FROM

       fnd_descr_flex_col_usage_vl   fdfcu,

       fnd_descriptive_flexs_vl      fdf,

       fnd_flex_value_sets           ffv

 WHERE

       1 = 1

   --

   AND fdf.title = 'Further Job Information'        -- <change it>

   AND fdfcu.descriptive_flex_context_code = 'US'   -- <change it>

   AND fdfcu.enabled_flag = 'Y'

   --

   AND fdfcu.flex_value_set_id = ffv.flex_value_set_id

   AND fdfcu.descriptive_flexfield_name = fdf.descriptive_flexfield_name

   AND fdfcu.application_id = fdf.application_id

   --

 ORDER BY

       fdfcu.descriptive_flexfield_name,

       fdfcu.descriptive_flex_context_code,

       fdfcu.column_seq_num;