Query To Get Active Users and Responsibilities with Department Name and Location Of User

Below Query will give you the output of Active FND Users and Responsibilities with their HR Department name and Location.

SELECT a.user_name, a.description, b.responsibility_name, fat.application_name, (SELECT NAME
                                                                                   FROM hr_all_organization_units haou
                                                                                  WHERE haou.organization_id = paaf.organization_id) "DEPARTMENT"
      , (SELECT town_or_city
           FROM hr_locations_all
          WHERE location_id = paaf.location_id) "LOCATION_OF_USER"
  FROM fnd_user a, fnd_responsibility_tl b, fnd_responsibility fr, fnd_user_resp_groups_direct c, fnd_application_tl fat, per_all_people_f papf, per_all_assignments_f paaf
 WHERE a.user_id = c.user_id
   AND a.employee_id = papf.person_id
   AND papf.person_id = paaf.person_id
   AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
   AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
   AND fat.application_id = c.responsibility_application_id
   AND fr.responsibility_id = b.responsibility_id
   AND fr.end_date IS NULL
   AND (c.end_date IS NULL OR c.end_date > SYSDATE) -- Active User Responsibilities
   AND (a.end_date IS NULL OR a.end_date > SYSDATE) -- Active Users
   AND b.responsibility_id = c.responsibility_id
--and a.USER_NAME in ('') -- Enter Username here

SOX Audit Report : Oracle EBS Financial Statement Generator (FSG) Report Details

Oracle EBS Financial Statement Generator (FSG) Report Details for SOX Audit

You may want to be able to audit FSG Report Details.

If you want to query information related to FSG reports

Query:

SELECT DISTINCT r.NAME "REPORT_NAME"
      , r.report_title
      , r.description
      , (SELECT fu.user_name
              FROM fnd_user fu
             WHERE fu.user_id = (r.created_by)) created_by
      , r.creation_date
      , (SELECT fu.user_name
              FROM fnd_user fu
             WHERE fu.user_id = (r.last_updated_by)) last_updated_by
      , r.last_update_date
FROM rg_reports_v r, fnd_id_flex_structures_v fst, fnd_user u, rg_report_axis_sets_v rw, rg_report_axis_sets_v rw2
 WHERE r.structure_id = fst.id_flex_num
   AND r.row_set_id = rw.axis_set_id
   AND r.column_set_id = rw2.axis_set_id
   AND r.created_by = u.user_id
   --AND r.NAME IN ('FSG Report Name')   -- Enter FSG Report Name Here
   AND r.last_update_date >= TO_DATE ('01-JUN-2016 00:00:00', 'DD-MON-YYYY HH24:MI:SS')
   AND r.last_update_date <= TO_DATE ('30-JUN-2016 23:59:59', 'DD-MON-YYYY HH24:MI:SS') 
ORDER BY 1, 2;

This query will give the output with FSG Report details for the period of 01-JUN-2016 to 30-JUN-2016.

Script to Monitor Server CPU Load Average

Pre-requisites to Run the script:

Make sure mailx  is installed on your OS, also check below environements/file is created prior to execute the scripts.

This script is tested on Linux Server.

This script needs to be deployed in database node/server.

#Base location for the DBA scripts
DBA_SCRIPTS_HOME=$HOME/DBA_MON

#OS User profile where database environment file is set
$HOME/.bash_profile

Create Custom .sysenv file for scripts

Download(.sysenv)

Download .sysenv file and save it under $HOME/DBA_MON
$ chmod 777 .sysenv

$ cat $HOME/DBA_MON/.sysenv

export DBA_SCRIPTS_HOME=$HOME/DBA_MON
export PATH=${PATH}:$DBA_SCRIPTS_HOME
export DBA_EMAIL_LIST=kiran.jadhav@domain.com,jadhav.kiran@domain.com
#Below parameter is used in script, whenever there is planned downtime you can set it to Y so there will be no false alert.
export DOWNTIME_MODE=N

Script to Monitor Server CPU Load Average and Send Notification if Utilization is above threshold.



Download(ServerLoadMon.sh)

Download ServerLoadMon.sh and save it under $HOME/DBA_MON/bin/
$ chmod 755 ServerLoadMon.sh

#!/bin/bash

############################################################
# Script Name : ServerLoadMon.sh                           #
#                                                          #
# Description:                                             #
# Script to Monitor Server CPU Load Average and            #
# Send Notification if Utilization is above threshold      #                                                                          
#                                                          #
# Usage : sh <script_name> <Threshold>                     #
# For example : sh ServerLoadMon.sh 10                     #
#                                                          #
# Created by : Kiran Jadhav -(https://h2hdba.blogspot.com) #
############################################################

# Initialize variables

THRESHOLD=$1
HOST_NAME=`hostname | cut -d'.' -f1`
PROGRAM=`basename $0 | cut -d'.' -f1`
export DBA_SCRIPTS_HOME=$HOME/DBA_MON
LOG_DIR=$DBA_SCRIPTS_HOME/logs/$HOST_NAME
OUT_FILE=$LOG_DIR/`echo $PROGRAM`.out
LOG_FILE=$LOG_DIR/`echo $PROGRAM`.log
ERR_FILE=$LOG_DIR/`echo $PROGRAM`.err
LOG_DATE=`date`


# Source the env
. $HOME/.bash_profile
. $DBA_SCRIPTS_HOME/.sysenv

if [ $? -ne 0 ]; then
   echo "$LOG_DATE" > $LOG_FILE  
   echo "Please pass correct environment : exiting the script  \n" >> $LOG_FILE
   cat $LOG_FILE
   exit
fi

if [ -s $OUT_FILE ]; then
 echo "$LOG_DATE" > $LOG_FILE
 echo "Deleting existing output file $OUT_FILE" >> $LOG_FILE
 rm -f $OUT_FILE
 cat $LOG_FILE
fi

if [ $DOWNTIME_MODE = "Y" ]; then
 echo "$LOG_DATE" >> $LOG_FILE
 echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
 cat $LOG_FILE
 exit
fi

usage()
{
  echo "$LOG_DATE" > $LOG_FILE
        echo "Script to monitor Server Load"  >> $LOG_FILE
        echo "Usage   : sh <script_name> <LoadAvg> " >> $LOG_FILE
  echo "For example : sh $PROGRAM.sh 10" >> $LOG_FILE
        echo
}

if [ $# -lt 1 ]; then
    usage
    echo "Error : Insufficient arguments." >> $LOG_FILE
 cat $LOG_FILE
    exit
fi

LOADAVG=`/usr/bin/uptime|awk '{print $(NF-2)}'|cut -d. -f1`
#echo $LOADAVG
 
if [ "$LOADAVG" -ge "$THRESHOLD" ]; then
 /usr/bin/uptime > $OUT_FILE 
   cat $OUT_FILE| mailx -s "Critical : High Load ( > $THRESHOLD ) average on $HOST_NAME" $DBA_EMAIL_LIST
else 
    echo "$LOG_DATE" > $OUT_FILE
 echo "Server Load is NORMAL" >> $OUT_FILE
fi

Logs and Out files will be generated under $DBA_SCRIPTS_HOME/logs/$HOST_NAME
So make sure to create logs/$HOST_NAME directory under $DBA_SCRIPTS_HOME before executing 
the script.

Once the script is ready, then as per the requirement please schedule it in crontab/OEM.

Execute the Script as below:

Syntax :  sh <script_name> <Threshold>  

$ cd $HOME/DBA_MON/bin
$ sh ServerLoadMon.sh 10

This script will send the notification with details if Server Load Average is above 10.

Script To Monitor Filesystem (Mount Point) Space or Utilization

Pre-requisites to Run the script:

Make sure mailx  is installed on your OS, also check below environements/file is created prior to execute the scripts.

This script is tested on Linux Server.

This script needs to be deployed in database node/server.

#Base location for the DBA scripts
DBA_SCRIPTS_HOME=$HOME/DBA_MON

#OS User profile where database environment file is set
$HOME/.bash_profile

Create Custom .sysenv file for scripts

Download(.sysenv)

Download .sysenv file and save it under $HOME/DBA_MON
$ chmod 777 .sysenv

$ cat $HOME/DBA_MON/.sysenv

export DBA_SCRIPTS_HOME=$HOME/DBA_MON
export PATH=${PATH}:$DBA_SCRIPTS_HOME
export DBA_EMAIL_LIST=kiran.jadhav@domain.com,jadhav.kiran@domain.com
#Below parameter is used in script, whenever there is planned downtime you can set it to Y so there will be no false alert.
export DOWNTIME_MODE=N

Script to check Filesystem space and send notification if filesystem usage% is more than given threshold..



Download(FSMon.sh)

Download FSMon.sh and save it under $HOME/DBA_MON/bin/
$ chmod 755 FSMon.sh

#!/bin/bash

############################################################
# Script Name : FSMon.sh                                   # 
#                                                          #
# Description:                                             # 
# Script to check Filesystem space and send notification   #
# if filesystem usage% is more than given threshold.       #                                                                          
#                                                          #
# Usage : sh <script_name> <Used% Threshold>               #
# For example : sh FSMon.sh 90                             #
#                                                          #
# Created by : Kiran Jadhav -(https://h2hdba.blogspot.com) #
############################################################

# Initialize variables

THRESHOLD=$1
HOST_NAME=`hostname | cut -d'.' -f1`
PROGRAM=`basename $0 | cut -d'.' -f1`
export DBA_SCRIPTS_HOME=$HOME/DBA_MON
LOG_DIR=$DBA_SCRIPTS_HOME/logs/$HOST_NAME
OUT_FILE=$LOG_DIR/`echo $PROGRAM`.out
LOG_FILE=$LOG_DIR/`echo $PROGRAM`.log
ERR_FILE=$LOG_DIR/`echo $PROGRAM`.err
LOG_DATE=`date`


# Source the env
. $HOME/.bash_profile
. $DBA_SCRIPTS_HOME/.sysenv

if [ $? -ne 0 ]; then
   echo "$LOG_DATE" > $LOG_FILE  
   echo "Please pass correct environment : exiting the script  \n" >> $LOG_FILE
   cat $LOG_FILE
   exit
fi

if [ -s $OUT_FILE ]; then
 echo "$LOG_DATE" > $LOG_FILE
 echo "Deleting existing output file $OUT_FILE" >> $LOG_FILE
 rm -f $OUT_FILE
 cat $LOG_FILE
fi

if [ $DOWNTIME_MODE = "Y" ]; then
 echo "$LOG_DATE" >> $LOG_FILE
 echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
 cat $LOG_FILE
 exit
fi

usage()
{
 echo "$LOG_DATE" > $LOG_FILE
        echo "Script to monitor OS Filesystem"  >> $LOG_FILE
        echo "Usage   : sh <script_name> <Used% Threshold>  " >> $LOG_FILE
 echo "For example : sh $PROGRAM.sh 90" >> $LOG_FILE
        echo
}

if [ $# -lt 1 ]; then
    usage
    echo "Error : Insufficient arguments." >> $LOG_FILE
 cat $LOG_FILE
    exit
fi


/bin/df -h|grep -ivE '^Filesystem|tmpfs|cdrom|vol' | awk '{print $(NF-1),"\t",$NF}'| while read output;
do
  #echo $output
  usep=$(echo $output | awk '{ print $1}' | cut -d'%' -f1  )
 # partition=$(echo $output | awk '{ print $1 }' )
  filesystem=$(echo $output | awk '{ print $2 }' )
  if [ $usep -ge $THRESHOLD ]; then
    echo "Filesystem: \"$filesystem\"   is \"($usep%)\"   FULL" >> $OUT_FILE
  fi
done 

if [ -s $OUT_FILE ]; then
 mailx -s "Critical: Filesystem usage is more than $THRESHOLD% on $HOST_NAME" $DBA_EMAIL_LIST < $OUT_FILE
else 
 echo "Disk Space Usage is normal" > $LOG_FILE  
fi

Logs and Out files will be generated under $DBA_SCRIPTS_HOME/logs/$HOST_NAME
So make sure to create logs/$HOST_NAME directory under $DBA_SCRIPTS_HOME before executing 
the script.

Once the script is ready, then as per the requirement please schedule it in crontab/OEM.

Execute the Script as below:

Syntax :  sh <script_name> <Used% Threshold>  

$ cd $HOME/DBA_MON/bin
$ sh FSMon.sh 90

This script will send the notification with details if any of the mount point usage 

reached to 90% or Above.

Script to Monitor Cost Manager and other Inventory Interface Managers

Pre-requisites to Run the script:

Make sure mailx and sendmail is installed on your OS, also check below environements/file is created prior to execute the scripts.

This script is tested on Linux Server.

This script needs to be deployed in database node/server.

#Base location for the DBA scripts
DBA_SCRIPTS_HOME=$HOME/DBA_MON

#OS User profile where database environment file is set
$HOME/.bash_profile

Create Custom .sysenv file for scripts

Download(.sysenv)

Download .sysenv file and save it under $HOME/DBA_MON
$ chmod 777 .sysenv

$ cat $HOME/DBA_MON/.sysenv

export DBA_SCRIPTS_HOME=$HOME/DBA_MON
export PATH=${PATH}:$DBA_SCRIPTS_HOME
export DBA_EMAIL_LIST=kiran.jadhav@domain.com,jadhav.kiran@domain.com
#Below parameter is used in script, whenever there is planned downtime you can set it to Y so there will be no false alert.
export DOWNTIME_MODE=N

Script to Monitor Cost Manager and other Inventory Interface Managers & to send alert notification with the details to DBA Team if its INACTIVE. 



Download(MonInterfaceMgr.sh)

Download MonInterfaceMgr.sh and save it under $HOME/DBA_MON/bin/
$ chmod 755 MonInterfaceMgr.sh

#!/bin/bash

###############################################################################
# Script Name : MonInterfaceMgr.sh                                            #
#                                                                             #
# Description:                                                                # 
# Script to monitor Inventory Interface Manager                               #
# Cost Manager; Lot Move Transaction; Material transaction; Move transaction  #
# to send alert notification with the details to DBA Team if its INACTIVE     #
#                                                                             #
# Usage : sh <script_name> <ORACLE_SID>                                       #
# For example : sh MonInterfaceMgr.sh ORCL                                    #
#                                                                             #
# Created by : Kiran Jadhav - (https://h2hdba.blogspot.com)                   #
###############################################################################

# Initialize variables

INSTANCE=$1
HOST_NAME=`hostname | cut -d'.' -f1`
PROGRAM=`basename $0 | cut -d'.' -f1`
export DBA_SCRIPTS_HOME=$HOME/DBA_MON
APPS_ID=`echo $INSTANCE | tr '[:lower:]' '[:upper:]'`
LOG_DIR=$DBA_SCRIPTS_HOME/logs/$HOST_NAME
OUT_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.html.out
LOG_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.log
ERR_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.err
LOG_DATE=`date`


# Source the env
. $HOME/.bash_profile
. $DBA_SCRIPTS_HOME/.sysenv

if [ $? -ne 0 ]; then
   echo "$LOG_DATE" > $LOG_FILE  
   echo "Please pass correct environment : exiting the script  \n" >> $LOG_FILE
   #cat $LOG_FILE
   exit
fi

if [ -s $OUT_FILE ]; then
 echo "$LOG_DATE" > $LOG_FILE
 echo "Deleting existing output file $OUT_FILE" >> $LOG_FILE
 rm -f $OUT_FILE
 cat $LOG_FILE
fi

# If there is a plan downtime then create $ORACLE_SID.down file in $DBA_SCRIPTS_HOME to silent the alerts during maintenance window.

if [ -f $DBA_SCRIPTS_HOME/`echo $ORACLE_SID`.down ]; then
 echo "$LOG_DATE" >> $LOG_FILE
        echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
        cat $LOG_FILE
 exit
fi

if [ $DOWNTIME_MODE = "Y" ]; then
 echo "$LOG_DATE" >> $LOG_FILE
 echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
 cat $LOG_FILE
 exit
fi

usage()
{
  echo "$LOG_DATE" > $LOG_FILE
        echo "Script to monitor Inventory Interface Manager"  >> $LOG_FILE
        echo "Usage   : sh <script_name> <ORACLE_SID> " >> $LOG_FILE
  echo "For example : sh $PROGRAM.sh $ORACLE_SID" >> $LOG_FILE
        echo
}

if [ $# -lt 1 ] || [ "$INSTANCE" != "$ORACLE_SID" ]; then
    usage
    echo "Error : Insufficient arguments." >> $LOG_FILE
 cat $LOG_FILE
    exit
fi

get_count()
{
 sqlplus -s '/as sysdba' <<!
 set heading off
 set feedback off
 SELECT COUNT(*) FROM
 (
 SELECT 
   x.PROCESS_TYPE "Name", 
   decode((select '1' 
  FROM APPS.FND_CONCURRENT_REQUESTS cr, 
  APPS.FND_CONCURRENT_PROGRAMS_VL cp, 
  APPS.FND_APPLICATION A 
    WHERE cp.concurrent_program_id = cr.concurrent_program_id 
   AND cp.CONCURRENT_PROGRAM_NAME = x.PROCESS_NAME 
   AND cp.APPLICATION_ID = a.application_id 
   AND a.APPLICATION_SHORT_NAME = x.PROCESS_APP_SHORT_NAME 
   AND PHASE_CODE != 'C' and rownum=1),'1','Active','Inactive') "Status", 
   x.WORKER_ROWS "Worker Rows", 
   x.TIMEOUT_HOURS "Timeout Hours", 
   x.TIMEOUT_MINUTES "Timeout Minutes", 
   x.PROCESS_HOURS "Process Interval Hours", 
   x.PROCESS_MINUTES "Process Interval Minutes", 
   x.PROCESS_SECONDS "Process Interval Seconds" 
 FROM ( 
   SELECT 
   MIPC.PROCESS_CODE , 
   MIPC.PROCESS_STATUS , 
   MIPC.PROCESS_INTERVAL , 
   MIPC.MANAGER_PRIORITY , 
   MIPC.WORKER_PRIORITY , 
   MIPC.WORKER_ROWS , 
   MIPC.PROCESSING_TIMEOUT , 
   MIPC.PROCESS_NAME , 
   MIPC.PROCESS_APP_SHORT_NAME , 
   A.MEANING PROCESS_TYPE , 
   FLOOR(MIPC.PROCESS_INTERVAL/3600) PROCESS_HOURS , 
   FLOOR((MIPC.PROCESS_INTERVAL - 
   (FLOOR(MIPC.PROCESS_INTERVAL/3600) * 3600))/60) PROCESS_MINUTES , 
   (MIPC.PROCESS_INTERVAL - (FLOOR(MIPC.PROCESS_INTERVAL/3600) * 3600) - 
   (FLOOR((MIPC.PROCESS_INTERVAL - 
   (FLOOR(MIPC.PROCESS_INTERVAL/3600) * 3600))/60) * 60)) PROCESS_SECONDS , 
   FLOOR(MIPC.PROCESSING_TIMEOUT/3600) TIMEOUT_HOURS , 
   FLOOR((MIPC.PROCESSING_TIMEOUT - 
   FLOOR(MIPC.PROCESSING_TIMEOUT/3600) * 3600)/60) TIMEOUT_MINUTES 
   FROM 
   APPS.MTL_INTERFACE_PROC_CONTROLS MIPC, 
   APPS.MFG_LOOKUPS A 
   WHERE 
   A.LOOKUP_TYPE = 'PROCESS_TYPE' AND 
   A.LOOKUP_CODE = MIPC.PROCESS_CODE 
 ) x 
 -- WHERE x.PROCESS_TYPE = 'Cost Manager' -- uncomment this to display only the cost manager; Possible Values: Cost Manager; Lot Move Transaction; Material transaction; Move transaction
 WHERE decode((select '1'
  FROM APPS.FND_CONCURRENT_REQUESTS cr, 
  APPS.FND_CONCURRENT_PROGRAMS_VL cp, 
  APPS.FND_APPLICATION A 
    WHERE cp.concurrent_program_id = cr.concurrent_program_id 
   AND cp.CONCURRENT_PROGRAM_NAME = x.PROCESS_NAME 
   AND cp.APPLICATION_ID = a.application_id 
   AND a.APPLICATION_SHORT_NAME = x.PROCESS_APP_SHORT_NAME 
   AND PHASE_CODE != 'C' and rownum=1),'1','Active','Inactive') <> 'Active'
 ORDER BY 1
 );

 exit;
!
}

count=`get_count`
#echo $count

echo "$LOG_DATE" > $ERR_FILE
get_count >> $ERR_FILE
ERR_COUNT=`grep "ORA-" $ERR_FILE |wc -l`

if [ $ERR_COUNT -gt 0 ]; then
 cat $ERR_FILE | mailx -s "<ERROR> Critical : $APPS_ID - One or More Interface Managers are Inactive on $HOST_NAME " $DBA_EMAIL_LIST
 exit
fi

if [ $count -gt 0 ];
then

 sqlplus -s '/as sysdba' <<EOF

 SET ECHO OFF
 SET pagesize 1000
 set feedback off
 set lines 180
 SET MARKUP HTML ON SPOOL ON -
 HEAD '<title></title> -
 <style type="text/css"> -
    table { background: #eee; } -
    th { font:bold 10pt Arial,Helvetica,sans-serif; color:#b7ceec; background:#151b54; padding: 5px; align:center; } -
    td { font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding: 5px; align:center; } -
 </style>' TABLE "border='1' align='left'" ENTMAP OFF

 spool $OUT_FILE


 PROMPT Hi Team,

 PROMPT
 PROMPT Please check below for the Inactive Interface Managers. Please start Inactive Managers ASAP.
 PROMPT


  SELECT 
   x.PROCESS_TYPE "Name", 
   decode((select '1' 
  FROM APPS.FND_CONCURRENT_REQUESTS cr, 
  APPS.FND_CONCURRENT_PROGRAMS_VL cp, 
  APPS.FND_APPLICATION A 
    WHERE cp.concurrent_program_id = cr.concurrent_program_id 
   AND cp.CONCURRENT_PROGRAM_NAME = x.PROCESS_NAME 
   AND cp.APPLICATION_ID = a.application_id 
   AND a.APPLICATION_SHORT_NAME = x.PROCESS_APP_SHORT_NAME 
   AND PHASE_CODE != 'C' and rownum=1),'1','Active','Inactive') "Status", 
   x.WORKER_ROWS "Worker Rows", 
   x.TIMEOUT_HOURS "Timeout Hours", 
   x.TIMEOUT_MINUTES "Timeout Minutes", 
   x.PROCESS_HOURS "Process Interval Hours", 
   x.PROCESS_MINUTES "Process Interval Minutes", 
   x.PROCESS_SECONDS "Process Interval Seconds" 
 FROM ( 
   SELECT 
   MIPC.PROCESS_CODE , 
   MIPC.PROCESS_STATUS , 
   MIPC.PROCESS_INTERVAL , 
   MIPC.MANAGER_PRIORITY , 
   MIPC.WORKER_PRIORITY , 
   MIPC.WORKER_ROWS , 
   MIPC.PROCESSING_TIMEOUT , 
   MIPC.PROCESS_NAME , 
   MIPC.PROCESS_APP_SHORT_NAME , 
   A.MEANING PROCESS_TYPE , 
   FLOOR(MIPC.PROCESS_INTERVAL/3600) PROCESS_HOURS , 
   FLOOR((MIPC.PROCESS_INTERVAL - 
   (FLOOR(MIPC.PROCESS_INTERVAL/3600) * 3600))/60) PROCESS_MINUTES , 
   (MIPC.PROCESS_INTERVAL - (FLOOR(MIPC.PROCESS_INTERVAL/3600) * 3600) - 
   (FLOOR((MIPC.PROCESS_INTERVAL - 
   (FLOOR(MIPC.PROCESS_INTERVAL/3600) * 3600))/60) * 60)) PROCESS_SECONDS , 
   FLOOR(MIPC.PROCESSING_TIMEOUT/3600) TIMEOUT_HOURS , 
   FLOOR((MIPC.PROCESSING_TIMEOUT - 
   FLOOR(MIPC.PROCESSING_TIMEOUT/3600) * 3600)/60) TIMEOUT_MINUTES 
   FROM 
   APPS.MTL_INTERFACE_PROC_CONTROLS MIPC, 
   APPS.MFG_LOOKUPS A 
   WHERE 
   A.LOOKUP_TYPE = 'PROCESS_TYPE' AND 
   A.LOOKUP_CODE = MIPC.PROCESS_CODE 
 ) x 
 -- WHERE x.PROCESS_TYPE = 'Cost Manager' -- uncomment this to display only the cost manager; Possible Values: Cost Manager; Lot Move Transaction; Material transaction; Move transaction
 ORDER BY 1;
 
PROMPT
PROMPT
PROMPT
PROMPT
PROMPT
PROMPT
PROMPT 
PROMPT
PROMPT
PROMPT
PROMPT
PROMPT
PROMPT <b>Step To Start Interface Managers which are in Inactive State:</b>
PROMPT
PROMPT 1. Login wuth SYSADMIN -> Select Responsibility - "Inventory" -> 
PROMPT 2. Navigate to "Setup" -> Transactions > Interface Managers 
PROMPT 3. Go to Menu -> Tools -> Launch Manager
PROMPT
PROMPT <b>Steps to Start -> "Cost Manager":</b>
PROMPT
PROMPT 1. Login with SYSADMIN -> Select Responsibility - "Inventory"
PROMPT 2. Navigate to "Setup" > Transactions > Interface Managers
PROMPT 3. Select "Cost Manager"
PROMPT 4. Go to Menu -> Tools -> Launch Manager
PROMPT 5. Go to Menu -> View -> Requests -> Query Name = "Cost Manager" 
PROMPT
PROMPT <b>Steps to Start -> "Process transaction interface" or "Transaction Manager":</b>
PROMPT
PROMPT 1. Login wuth SYSADMIN -> Select Responsibility - "Inventory"
PROMPT 2. Navigate to "Setup" -> Transactions > Interface Managers
PROMPT 3. Select "Material Transaction"
PROMPT 4. Go to Menu -> Tools -> Launch Manager
PROMPT 5. Go to Menu -> View -> Requests -> Query Name = "Process transaction interface"  and ""Inventory transaction worker"
PROMPT

 SPOOL OFF
 SET MARKUP HTML OFF
 exit;

EOF

(
echo "To: $DBA_EMAIL_LIST"
echo "MIME-Version: 1.0"
echo "Content-Type: multipart/alternative; "
echo ' boundary="PAA08673.1018277622/server.xyz.com"'
echo "Subject: Critical : $APPS_ID - One or More Interface Managers are Inactive on $HOST_NAME"
echo ""
echo "This is a MIME-encapsulated message"
echo ""
echo "--PAA08673.1018277622/server.xyz.com"
echo "Content-Type: text/html"
echo ""
cat $OUT_FILE
echo "--PAA08673.1018277622/server.xyz.com"
) | /usr/sbin/sendmail -t

echo "$LOG_DATE" > $LOG_FILE
echo "Details sent through an email" >> $LOG_FILE
cat $LOG_FILE

else 
    echo "$LOG_DATE" > $OUT_FILE
 echo "Interface Managers Running Fine" >> $OUT_FILE
fi

Logs and Out files will be generated under $DBA_SCRIPTS_HOME/logs/$HOST_NAME
So make sure to create logs/$HOST_NAME directory under $DBA_SCRIPTS_HOME before executing the script.

Once the script is ready, then as per the requirement please schedule it in crontab/OEM.

Execute the Script as below:

Syntax :  sh <script_name> <ORACLE_SID>

$ cd $HOME/DBA_MON/bin
$ sh MonInterfaceMgr.sh ORCL

This script will send the notification if Cost Manager or any of the Inventory Interface Manager is INACTIVE.

Script to Monitor Output Post Processor (OPP) Concurrent Manager

Pre-requisites to Run the script:

Make sure mailx and sendmail is installed on your OS, also check below environements/file is created prior to execute the scripts.

This script is tested on Linux Server.

This script needs to be deployed in database node/server.

#Base location for the DBA scripts
DBA_SCRIPTS_HOME=$HOME/DBA_MON

#OS User profile where database environment file is set
$HOME/.bash_profile

Create Custom .sysenv file for scripts

Download(.sysenv)

Download .sysenv file and save it under $HOME/DBA_MON
$ chmod 777 .sysenv

$ cat $HOME/DBA_MON/.sysenv

export DBA_SCRIPTS_HOME=$HOME/DBA_MON
export PATH=${PATH}:$DBA_SCRIPTS_HOME
export DBA_EMAIL_LIST=kiran.jadhav@domain.com,jadhav.kiran@domain.com
#Below parameter is used in script, whenever there is planned downtime you can set it to Y so there will be no false alert.
export DOWNTIME_MODE=N

Script to check Output Post Processor (OPP) Concurrent Manager status and to send alert notification if its DOWN. 



Download(MonOPPMgr.sh)

Download MonOPPMgr.sh and save it under $HOME/DBA_MON/bin/
$ chmod 755 MonOPPMgr.sh

#!/bin/bash

###########################################################################
# Script Name :  MonOPPMgr.sh                                             #
#                                                                         #
# Description:                                                            #
# Script to check Output Post Processor Concurrent Manager status and     #
# to send alert notification if its DOWN                                  #
#                                                                         #
# Usage : sh <script_name> <ORACLE_SID>                                   #
# For example : sh MonOPPMgr.sh ORCL                                      #
#                                                                         #
# Created by : Kiran Jadhav - (https://h2hdba.blogspot.com)               #
###########################################################################

# Initialize variables

INSTANCE=$1
HOST_NAME=`hostname | cut -d'.' -f1`
PROGRAM=`basename $0 | cut -d'.' -f1`
export DBA_SCRIPTS_HOME=$HOME/DBA_MON
APPS_ID=`echo $INSTANCE | tr '[:lower:]' '[:upper:]'`
LOG_DIR=$DBA_SCRIPTS_HOME/logs/$HOST_NAME
OUT_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.html.out
LOG_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.log
ERR_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.err
LOG_DATE=`date`


# Source the env
. $HOME/.bash_profile
. $DBA_SCRIPTS_HOME/.sysenv

if [ $? -ne 0 ]; then
   echo "$LOG_DATE" > $LOG_FILE  
   echo "Please pass correct environment : exiting the script  \n" >> $LOG_FILE
   cat $LOG_FILE
   exit
fi

if [ -s $OUT_FILE ]; then
 echo "$LOG_DATE" > $LOG_FILE
 echo "Deleting existing output file $OUT_FILE" >> $LOG_FILE
 rm -f $OUT_FILE
 cat $LOG_FILE
fi

# If there is a plan downtime then create $ORACLE_SID.down file in $DBA_SCRIPTS_HOME to silent the alerts during maintenance window.

if [ -f $DBA_SCRIPTS_HOME/`echo $ORACLE_SID`.down ]; then
 echo "$LOG_DATE" >> $LOG_FILE
        echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
        cat $LOG_FILE
 exit
fi

if [ $DOWNTIME_MODE = "Y" ]; then
 echo "$LOG_DATE" >> $LOG_FILE
 echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
 cat $LOG_FILE
 exit
fi

usage()
{
 echo "$LOG_DATE" > $LOG_FILE
    echo "Script To Check Concurrent Managers Status"  >> $LOG_FILE
    echo "Usage   : sh <script_name> <ORACLE_SID> " >> $LOG_FILE
 echo "For example : sh $PROGRAM.sh $ORACLE_SID" >> $LOG_FILE
    echo
}

if [ $# -lt 1 ] || [ "$INSTANCE" != "$ORACLE_SID" ]; then
    usage
    echo "Error : Insufficient arguments." >> $LOG_FILE
 cat $LOG_FILE
    exit
fi

get_count()
{
 sqlplus -s '/as sysdba' <<!
 set heading off
 set feedback off
 select count(1) from
 (
  SELECT b.user_concurrent_queue_name "Concurrent Manager", b.target_node "Node", a.running_processes "Actual Processes", a.max_processes "Target Processes"
    FROM apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_vl b
   WHERE a.concurrent_queue_id = b.concurrent_queue_id 
    AND a.concurrent_queue_name = 'FNDCPOPP' 
    AND (a.max_processes != a.min_processes OR a.running_processes != a.min_processes)
 );

 exit;
!
}

count=`get_count`
#echo $count

echo "$LOG_DATE" > $ERR_FILE
get_count >> $ERR_FILE
ERR_COUNT=`grep "ORA-" $ERR_FILE |wc -l`

if [ $ERR_COUNT -gt 0 ]; then
 cat $ERR_FILE | mailx -s "<ERROR> Critical : $APPS_ID - OPP Concurrent Manager is DOWN on $HOST_NAME  " $DBA_EMAIL_LIST
 exit
fi

if [ $count -gt 0 ];
then
 sqlplus -s '/as sysdba' <<EOF

 SET ECHO OFF
 SET pagesize 1000
 set feedback off
 set lines 180
 SET MARKUP HTML ON SPOOL ON -
 HEAD '<title></title> -
 <style type="text/css"> -
 table { background: #eee; } -
 th { font:bold 10pt Arial,Helvetica,sans-serif; color:#b7ceec; background:#151b54; padding: 5px; align:center; } -
 td { font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding: 5px; align:center; } -
 </style>' TABLE "border='1' align='left'" ENTMAP OFF

 spool $OUT_FILE


 PROMPT Hi Team,

 PROMPT
 PROMPT OPP concurrent manager is DOWN, Please check ASAP
 PROMPT


  SELECT b.user_concurrent_queue_name "Concurrent Manager", b.target_node "Node", a.running_processes "Actual Processes", a.max_processes "Target Processes"
    FROM apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_vl b
   WHERE a.concurrent_queue_id = b.concurrent_queue_id 
    AND a.concurrent_queue_name = 'FNDCPOPP' 
    AND (a.max_processes != a.min_processes OR a.running_processes != a.min_processes);

 SPOOL OFF
 SET MARKUP HTML OFF
 exit;

EOF

(
echo "To: $DBA_EMAIL_LIST"
echo "MIME-Version: 1.0"
echo "Content-Type: multipart/alternative; "
echo ' boundary="PAA08673.1018277622/server.xyz.com"'
echo "Subject: Critical : $APPS_ID - OPP Concurrent Manager is DOWN on $HOST_NAME "
echo ""
echo "This is a MIME-encapsulated message"
echo ""
echo "--PAA08673.1018277622/server.xyz.com"
echo "Content-Type: text/html"
echo ""
cat $OUT_FILE
echo "--PAA08673.1018277622/server.xyz.com"
) | /usr/sbin/sendmail -t

echo "$LOG_DATE" > $LOG_FILE
echo "Details sent through an email" >> $LOG_FILE
cat $LOG_FILE

else 
    echo "$LOG_DATE" > $OUT_FILE
 echo "OPP Concurrent Manager is up and running fine" >> $OUT_FILE
fi

Logs and Out files will be generated under $DBA_SCRIPTS_HOME/logs/$HOST_NAME
So make sure to create logs/$HOST_NAME directory under $DBA_SCRIPTS_HOME before executing the script.

Once the script is ready, then as per the requirement please schedule it in crontab/OEM.

Execute the Script as below:

Syntax :  sh <script_name> <ORACLE_SID>

$ cd $HOME/DBA_MON/bin
$ sh MonOPPMgr.sh ORCL

This script will send the notification with details if Output Post Processor (OPP) Concurrent Manager 

is Down or Inactive.