Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

Category Archives: Scripts

Changing Oracle HRMS installation from FULL to SHARED

When Oracle HRMS is installed in the FULL mode, users may encounter errors while accessing HRMS forms from non-HRMS responsibilities. For example, users cannot access the Employees form from Customer Support responsibility or the Positions form from Purchasing Super User responsibility. This happens because forms included in non-HRMS responsibility menus are to be used on a Shared HR environment and cannot be used on an HR full installed environment.

The best way to resolve this problem is to work on the forms from an HRMS responsibility. But if that option is not available, then the Oracle HRMS installation has to be manually changed from FULL mode to SHARED mode.

The steps involved in changing Oracle HRMS installation from FULL to SHARED are:

1. Check whether the hrglobal.drv file has been run in the instance. In case the hrglobal.drv file has already been run against the instance, then the only way out is to perform a new installation in SHARED mode and export all data from the old instance to the new one. (I sincerely hope that no one reading this post has to choose this option! )

2. If the hrglobal.drv file has NEVER been run against the system, then find the application_id of all HRMS related products installed in FULL mode in the instance.

3. Update the FND_PRODUCT_INSTALLATIONS table to change all HRMS related products from FULL to SHARED


update FND_PRODUCT_INSTALLATIONS
set STATUS = 'S'
where APPLICATION_ID IN (<appl_id1>,<appl_id2>);

Where appl_id1, appl_id2 are the application_id obtained from step#2.

Note:

1. Consult Oracle Support and raise an SR before trying to change the installation mode of Oracle HRMS.

2. The following SQL query checks whether hrglobal.drv has been run in an instance


select substr(application_short_name,1,4) app
, rpad(decode
(Legislation_code,null,'GLOBAL',Legislation_code),7,' ') LE_CODE
, rpad(status,14,' ') "Install Status"
, rpad(action,15,' ') "DataInst Action"
, last_update_date
from hr_legislation_installations
where application_short_name in ('PER','PAY')
order by LE_CODE,app;

If the Install Status column is ‘I’ for any row, it means that hrglobal.drv has been run in the instance.

3. The following SQL query shows the install status of HRMS related products


col a.application_name for a40
col a.application_short_name for a4
col b.status for a1
SELECT a.application_name,
a.application_id,
application_short_name,
DECODE(b.status,'I','I',
DECODE(b.status,'S','S',
DECODE(b.status,'N','N',b.status)))
FROM fnd_application_vl a, fnd_product_installations b
WHERE a.application_id = b.application_id
AND b.application_id IN (453,800,801,804,805,808,809,810,8301,8302,8303,8403)
ORDER BY a.application_id;

References:

Metalink Note#369459.1: How Does One Verify which HRMS Products have been Installed?

Metalink Note#414631.1: SQL: HRMS Products Installation Status

Metalink Note#116376.1: You Have Installed, Now Before You Setup, Read This! – HR Shared

There was another note on the entire process of going from full to shared install but that seems to be no longer publicly available.

 

Advertisements

Interacting with SQL and PL/SQL from shell scripts

Today’s post is a  shell script which I had written for monitoring and storing the status of network links in Oracle tables. I am posting it here since it serves as a pretty good example of interacting with SQL and PL/SQL from a shell script including the passing of script variables.


#!/bin/bash
# This script pings the IP addresses provided to determine the status of the network link.
# In addition to logging the status in a log file, it also updates a table in the Oracle database
# with the status of the link. Additionally, if the status of a link has changed since it was
# last checked, records are inserted into the SMS tables(so that the SMS sending program can
# send the link status to the appropriate persons).

#------------------------------------------------------------------------------------------------#
# Enter the list of the IP addresses which you want to ping. Seperate multiple entries with spaces
#HOSTS="180.100.4.9 180.100.4.45 180.100.4.69"
HOSTS="192.168.56.10"
#------------------------------------------------------------------------------------------------#

#-----------------------------------------------------------------------------------------------#
# Enter the 10-digit phone numbers where texts will be sent. Seperate multiple entries with spaces.
PHONES="9999999999 8888888888"
#-----------------------------------------------------------------------------------------------#

# Count the number of phone numbers provided
NUMBER_COUNT=0
for myPhone in $PHONES
do
NUMBER_COUNT=$((NUMBER_COUNT+1))
done

#-------------------------------------------------------------------------#
# Number of packets to be sent to each IP address. Change this as required
COUNT=3
#-------------------------------------------------------------------------#

# This part will check the response of the IP pings.
# All packets received -- Link is UP
# No packets received -- Link is DOWN
# Some packets are received -- Link is experiencing packet loss
for $HOST in $HOSTS
do
 STATUS=UP
 count=$(ping -c $COUNT $HOST | grep 'received' | awk -F',' '{ print $2 }' | awk '{ print $1 }')
 if [ $count -eq 0 ]; then
 # 100% failed
 STATUS=DOWN
 echo "$HOST: $STATUS on $(date)" >> link_status.log
 elif [ $count -gt 0 ] && [ $count -lt $COUNT ]; then
 # Some packets were received
 STATUS=PACKET_LOSS
 echo "$HOST: $STATUS on $(date)" >> link_status.log
 else
 # 100% received
 echo "$HOST: $STATUS on $(date)" >> link_status.log
 fi

#Insert the link status into Oracle tables
sqlplus -s apps/apps << EOM >> link_status.log
set verify off
set heading off
set feedback off
set serveroutput on;
declare
v_ip varchar2(20);
v_old_status varchar2(15);
v_sms_id number;
v_sms_text varchar2(60);
v_server_name varchar2(20);
v_all_phones varchar2(100);
v_phone_no number;
v_docket_id varchar2(10);
begin
v_all_phones:='$PHONES';

--Fetch the previous status of the link
select status, server_name into v_old_status, v_server_name from xxx_link_status where server_ip='$HOST';

--Update table with current status of the link
update xxx_link_status set status='$STATUS',last_update=sysdate where server_ip='$HOST';

--If the current status is different from the previous status, then insert into SMS tables so that
--the SMSs will be sent during the next run of the SMS program
if v_old_status<>'$STATUS' then
 v_sms_text:=v_server_name||' link status: '||'$STATUS';

--This is required for the SMS table
 select sms_seq.nextval
 into v_sms_id
 from dual;

--This is required for the SMS table
 select docket_seq.nextval
 into v_docket_id
 from dual;

--Insert a record into the SMS_MESSAGES table
 BEGIN
 insert into sms_messages
 (sms_id, sms_date, sms_text, docket, resource_id)
 values
 (v_sms_id, sysdate, v_sms_text, v_docket_id, '');
 commit;
 dbms_output.put_line('Successfully updated sms_messages');
 EXCEPTION
 when others then
 dbms_output.put_line('Could not update sms_messages');
 END;

--Insert one record in SMS_MESSAGES for each phone number to which the SMS is to be sent
 for i in 1..$NUMBER_COUNT loop
 select to_number(substr(v_all_phones,((11*i)-10),10)) into v_phone_no from dual;

select docket_seq.nextval
 into v_docket_id
 from dual;
 BEGIN
 execute immediate
 'insert into sms_numbers values ('
 ||v_sms_id||
 ','||v_phone_no||
 ','||'''N'''||
 ','||'NULL'||
 ','||v_docket_id||
 ','||'NULL'||
 ')';
 commit;
 dbms_output.put_line('Successfully updated sms_numbers');
 EXCEPTION
 when others then
 dbms_output.put_line('Could not update SMS_NUMBERS');
 END;

end loop;
end if;
end;
/
exit;
EOM

done
echo '++++++++================================================++++++++' >> link_status.log