Recreate Symbolic Links after Oracle Instance Refresh

After the non-Production instances (DEV, QA, TEST, etc) are refreshed, you may have noticed that the symbolic links, that are used for host program files, 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

##

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

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

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

## 06/21/14   1.0   Abul 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;

LEFT OUTER JOIN: Oracle HR Employee Phones

If you ever worked with PER_PHONES table in Oracle EBS to get the employee phones, you must have seen that PER_PHONES table contains multiple lines for different phone types for an employee.

For example, if I run the following query for an employee whose PERSON_ID is 1444, I get three records:

SELECT *

  FROM per_phones p

 WHERE 1=1

   AND p.parent_id = 1444;

You can run the following query to get the meaning of the phone types:

SELECT lookup_type,

       lookup_code,

       meaning

  FROM hr_lookups

 WHERE 1=1

   AND lookup_type  = 'PHONE_TYPE'

   AND enabled_flag = 'Y';

Now the problem is, when you connect this PER_PHONES (parent_id) table with PER_ALL_PEOPLE_F (person_id) table , you end up with multiple rows. In the above example, it will return three rows.

Now of course, the client will never want to see more than one row per employee. In my case, the client specifically asked for a condition which goes like: pick up Mobile Phone of the employee first; if Mobile Phone is NULL, then pick up Home Phone; if Home Phone is NULL, then pick up Work Phone, and so on.

I assumed that there would already be a view for this from Oracle. Since I did not find any, I had to come up with a query (to create a custom view) to avoid multiple rows per employee, using LEFT OUTER JOIN function.

SELECT

       pp.parent_id      parent_id,

       pp.parent_table   parent_table,

       --

       ppm.phone_number  mobile_phone,

       pph.phone_number  home_phone,

       ppw.phone_number  work_phone,

       ppo.phone_number  other_phone

  FROM

       (SELECT DISTINCT

               parent_id,

               parent_table

          FROM per_phones

       ) pp

  -- mobile phone

  LEFT OUTER JOIN per_phones  ppm

    ON (     ppm.phone_type   = 'M'

         AND ppm.parent_id    = pp.parent_id

         AND ppm.parent_table = pp.parent_table

       )

  -- home phone

  LEFT OUTER JOIN per_phones  pph

    ON (     pph.phone_type   = 'H1'

         AND pph.parent_id    = pp.parent_id

         AND pph.parent_table = pp.parent_table

       )

  -- work phone

  LEFT OUTER JOIN per_phones  ppw

    ON (     ppw.phone_type   = 'W1'

         AND ppw.parent_id    = pp.parent_id

         AND ppw.parent_table = pp.parent_table

       )

  -- other phone

  LEFT OUTER JOIN per_phones  ppo

    ON (     ppo.phone_type   = 'O'

         AND ppo.parent_id    = pp.parent_id

         AND ppo.parent_table = pp.parent_table

       )

 WHERE 1 = 1;

Hopefully, this is helpful. However, if there is any other query which may be useful, I would certainly love to hear or know about it.

BI Publisher Bursting

The following article, written by Gareth Roberts, is probably one of the best and most elaborate articles I have ever come across for BI Publisher's bursting feature. Very well written, step by step with screenshots and explanations.

http://garethroberts.blogspot.com/2008/03/bi-publisher-ebs-bursting-101.html