Configuring Sendmail on Solaris – Step-by-Step Guide

 

In many enterprise environments, servers should not send emails using their local hostnames. Instead, outgoing emails must appear as if they are sent from a centralized corporate mail server or domain. This blog walks through configuring Sendmail on Solaris using SMF (Service Management Facility), along with validation and testing steps.

This guide is especially useful for Oracle E-Business Suite/application servers running on Solaris that rely on Sendmail for outbound notifications.

---

Prerequisites

• Solaris OS with Sendmail installed

• Root or equivalent privileges

• Access to corporate mail relay/server

---

Step 1: Stop the Sendmail Service

Before making any configuration changes, stop the Sendmail service to avoid conflicts.

svcadm disable -t network/smtp:sendmail

---

Step 2: Verify Sendmail Service Status

Confirm that the service is stopped.

svcs -a network/smtp:sendmail

The status should indicate disabled or maintenance.

---

Step 3: Backup Existing Configuration Files

Always take a backup before modifying Sendmail configuration files.

Navigate to the Sendmail configuration directory:

cd /etc/mail/cf/cf

Create a copy of the existing sendmail.mc file using the hostname:

cp sendmail.mc `uname -n`.mc

This ensures you can easily identify the configuration per host.

---

Step 4: Edit the New Configuration File

Edit the newly created .mc file:

vi `uname -n`.mc

Add the following line to configure masquerading:

define(`SMARTHOST', `mail.server-mycompany.co.in')dnl

Note: Replace mail.server-mycompany.co.in with your actual corporate mail server or domain name.

This setting ensures that outgoing emails appear to originate from the specified mail domain instead of the local server hostname.

---

Step 5: Build the Sendmail Configuration File

Use m4 to generate the .cf file from the .mc file:

make `uname -n`.cf

This command converts the macro configuration into a Sendmail-readable format.

Add the mail server name in the configuration file

vi `uname -n`.cf

# "Smart" relay host (may be null)

DSmail.server-mycompany.co.in

---

Step 6: Install the New Configuration File

First, back up the existing production configuration:

cp /etc/mail/sendmail.cf /etc/mail/sendmail.cf.`date +%d-%b-%Y-%H-%M`

Now, replace it with the newly generated configuration:

cp `uname -n`.cf /etc/mail/sendmail.cf

---

Step 7: Restart the Sendmail Service

Enable and start the Sendmail service:

svcadm enable network/smtp:sendmail

Verify the service status again if needed:

svcs network/smtp:sendmail

---

Step 8: Send a Test Email

Use mailx to send a basic test email:

mailx -s "Test Mail" -v jxxx@yyyy.zzz < /dev/null

Confirm that the recipient receives the email and that the From address shows the masqueraded domain.

---

Step 9: Test Mail Server Connectivity

Validate SMTP connectivity to the mail server:

telnet mailserver.com 25

A successful connection indicates that the server can reach the mail relay on port 25.

---

Conclusion

By configuring Sendmail , you ensure consistent and compliant outbound email delivery from Solaris servers. This setup is critical in enterprise environments where mail policies require all applications and servers to send emails through an approved mail domain.

Following these steps also minimizes downtime and allows safe testing before applying changes to production.

---

Oracle EBS Application R12 - Concurrent Related Queries

This blog consolidates a practical set of Oracle EBS concurrent request SQL queries that are extremely useful in a DBA’s daily support life. The queries help DBAs monitor concurrent manager activity, analyze program runtimes, identify long-running or blocked requests, trace SQL executed by concurrent programs, locate log and output files, verify scheduling and hold status, and safely troubleshoot problematic requests at the database level. 

/*=============================================
 1. concurrent request Executing SQL Query
===============================================

SELECT va.sql_text
      ,va.module
FROM APPS.fnd_concurrent_requests fcr,
     V$SESSION vs,
     V$SQLAREA va
WHERE fcr.oracle_session_id = vs.audsid
AND vs.sql_hash_value = va.hash_value
AND fcr.request_id = 59556211;

/*==============================================
 2. Number of Concurrent Requests Ran Per Day
===============================================
select trunc(ACTUAL_START_DATE),count(1)
from applsys.fnd_concurrent_requests 
where trunc(ACTUAL_START_DATE) <= trunc(sysdate)
group by trunc(ACTUAL_START_DATE)
order by trunc(ACTUAL_START_DATE) desc;

/*=============================================
 3. Concurrent Program Runtime Details
===============================================
SELECT
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,
      fu.user_name,
      f.argument_text,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
        || ' HOURS ' ||
      floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
        || ' MINUTES ' ||
      round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
        (floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60)))
        || ' SECS ' time_difference,
      p.concurrent_program_name,
      f.phase_code,
      f.status_code
from apps.fnd_concurrent_programs p,
     apps.fnd_concurrent_programs_tl pt,
     apps.fnd_concurrent_requests f,
     apps.fnd_user fu
where f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
and f.requested_by = fu.user_id
and pt.user_concurrent_program_name like '%Sales%Report%'
order by f.actual_start_date desc;

/*==============================================
 4. Average Runtime for Specific Concurrent Program (All Status)
===============================================
SELECT
    fcpt.user_concurrent_program_name AS program_name,
    ROUND(AVG((fr.actual_completion_date - fr.actual_start_date) * 24 * 60), 2) AS avg_runtime_minutes,
    COUNT(*) AS total_runs
FROM apps.fnd_concurrent_requests fr,
     apps.fnd_concurrent_programs_tl fcpt
WHERE fr.concurrent_program_id = fcpt.concurrent_program_id
AND fcpt.language = 'US'
AND fcpt.user_concurrent_program_name like '%Sales%Report%'
AND fr.actual_start_date IS NOT NULL
AND fr.actual_completion_date IS NOT NULL
AND fr.request_date > SYSDATE - 30
GROUP BY fcpt.user_concurrent_program_name
ORDER BY fcpt.user_concurrent_program_name;

/*===============================================
 5. Average Runtime – Completed Normal Requests Only
================================================
SELECT
    fcpt.user_concurrent_program_name AS program_name,
    ROUND(AVG((fr.actual_completion_date - fr.actual_start_date) * 24 * 60), 2) AS avg_runtime_minutes,
    MIN((fr.actual_completion_date - fr.actual_start_date) * 24 * 60) AS min_runtime_minutes,
    MAX((fr.actual_completion_date - fr.actual_start_date) * 24 * 60) AS max_runtime_minutes,
    COUNT(*) AS total_runs
FROM apps.fnd_concurrent_requests fr,
     apps.fnd_concurrent_programs_tl fcpt
WHERE fr.concurrent_program_id = fcpt.concurrent_program_id
AND fcpt.language = 'US'
AND fcpt.user_concurrent_program_name = 'Sales Report'
AND fr.phase_code = 'C'
AND fr.status_code = 'C'
AND fr.actual_start_date IS NOT NULL
AND fr.actual_completion_date IS NOT NULL
AND fr.request_date > SYSDATE - 30
GROUP BY fcpt.user_concurrent_program_name;

/*==============================================
 6. Concurrent Programs with Average Runtime > 1 Hour
===============================================
SELECT
    fcpt.user_concurrent_program_name AS program_name,
    ROUND(AVG((fr.actual_completion_date - fr.actual_start_date) * 24 * 60), 2) AS avg_runtime_minutes,
    MIN((fr.actual_completion_date - fr.actual_start_date) * 24 * 60),
    MAX((fr.actual_completion_date - fr.actual_start_date) * 24 * 60),
    COUNT(*)
FROM apps.fnd_concurrent_requests fr,
     apps.fnd_concurrent_programs_tl fcpt
WHERE fr.concurrent_program_id = fcpt.concurrent_program_id
AND fcpt.language = 'US'
AND fr.phase_code = 'C'
AND fr.status_code = 'C'
AND fr.actual_start_date IS NOT NULL
AND fr.actual_completion_date IS NOT NULL
AND fr.request_date > SYSDATE - 30
GROUP BY fcpt.user_concurrent_program_name
HAVING AVG((fr.actual_completion_date - fr.actual_start_date) * 24 * 60) > 60
ORDER BY avg_runtime_minutes DESC;

/*==============================================
 7. Output File Format of Concurrent Program
================================================
SELECT cp.concurrent_program_name,
       cp.user_concurrent_program_name,
       cp.output_file_type
FROM fnd_concurrent_programs_vl cp
WHERE cp.user_concurrent_program_name like '%Sales%Report%';

/*==============================================
 8. Pending / Standby Concurrent Programs
================================================
SELECT f.request_id,
       pt.user_concurrent_program_name,
       fu.user_name,
       f.argument_text,
       f.phase_code,
       f.status_code
FROM apps.fnd_concurrent_programs_tl pt,
     apps.fnd_concurrent_requests f,
     apps.fnd_user fu
WHERE f.concurrent_program_id = pt.concurrent_program_id
AND pt.language = USERENV('Lang')
AND f.actual_start_date IS NULL
AND f.requested_by = fu.user_id
AND f.phase_code = 'P'
AND pt.user_concurrent_program_name like '%Sales%Report%';

/*=============================================
 9. Find SID & PID from Concurrent Request ID
==============================================
SELECT C.SID, C.SERIAL#, A.PID
FROM GV$PROCESS A, FND_CONCURRENT_REQUESTS B, GV$SESSION C
WHERE B.ORACLE_PROCESS_ID = A.SPID
AND B.REQUEST_ID = '5324543211'
AND A.ADDR = C.PADDR;

/*=============================================
 10. Terminate Concurrent Program (DB Level – Use Carefully)
===============================================
--UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE request_ID = 12334435;

/*=============================================
 11. Currently Running Concurrent Programs
===============================================
SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,
round(((sysdate-a.actual_start_date)*24*60),2) Process_time,
a.request_id,
a.actual_start_date,
a.phase_code,
a.status_code
FROM apps.fnd_concurrent_requests a,
     apps.FND_CONCURRENT_PROGRAMS_TL c
WHERE a.concurrent_program_id = c.concurrent_program_id
AND status_code = 'R'
ORDER BY Process_time DESC;

/*=============================================
 12. Log and Output File Locations
===============================================
select logfile_name from fnd_concurrent_requests where request_id = <request_id>;
select outfile_name from fnd_concurrent_requests where request_id = <request_id>;

/*=============================================
 13. Internal Concurrent Manager Log File Location
===============================================
SELECT 'ICM_LOG_NAME=' || fcp.logfile_name
FROM fnd_concurrent_processes fcp,
     fnd_concurrent_queues fcq
WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id
AND fcp.queue_application_id = fcq.application_id
AND fcq.manager_type = '0'
AND fcp.process_status_code = 'A';

/*=============================================

14. Find responsibility name from concurrent program name
===============================================
SELECT frt.responsibility_name, frg.request_group_name,
    frgu.request_unit_type,frgu.request_unit_id,
    fcpt.user_concurrent_program_name
    FROM fnd_Responsibility fr, fnd_responsibility_tl frt,
    fnd_request_groups frg, fnd_request_group_units frgu,
    fnd_concurrent_programs_tl fcpt
    WHERE frt.responsibility_id = fr.responsibility_id
    AND frg.request_group_id = fr.request_group_id
    AND frgu.request_group_id = frg.request_group_id
    AND fcpt.concurrent_program_id = frgu.request_unit_id
    AND frt.LANGUAGE = USERENV('LANG')
    AND fcpt.LANGUAGE = USERENV('LANG')
    AND fcpt.user_concurrent_program_name = 'conc_prog_name'
    ORDER BY 1,2,3,4;


/*==============================================
 15. Long Running Concurrent Requests More than 30 Minutes
================================================
select distinct qt.user_concurrent_queue_name "Queue Name"
       ,c2.user_concurrent_program_name "Program Name"
      ,a.request_id "Request Id"
      ,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent Req"
      ,FLVP.meaning "Phase"
      ,FLVS.meaning "Status"
      ,a.argument_text "Arguments"
      --,b.os_process_id "OS_PID"
      ,vs.inst_id
      ,vs.sid "SID"
      ,vs.serial# "Serial#"
      ,vp.spid "SPID",vs.sql_id "SQL"
      ,vs.event "EVENT"
      ,TO_CHAR(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') "Start Date"
      ,round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) "TimeSpent"
      ,u.user_name "USERNAME"
   from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
    ,applsys.fnd_concurrent_queues q
    ,applsys.fnd_concurrent_queues_tl qt
    ,APPLSYS.fnd_concurrent_programs_tl c2
    ,APPLSYS.fnd_concurrent_programs c
    ,APPLSYS.FND_LOOKUP_VALUES FLVP
    ,APPLSYS.FND_LOOKUP_VALUES FLVS
    ,APPLSYS.FND_USER u
    ,gv$session vs
    ,gv$process vp
   where a.controlling_manager = b.concurrent_process_id
     and a.concurrent_program_id = c.concurrent_program_id
     and a.program_application_id = c.application_id
     and c2.concurrent_program_id = c.concurrent_program_id
     and a.phase_code in ('I','P','R','T')
     and u.user_id=a.requested_by
     and a.phase_code=FLVP.Lookup_Code
     and FLVP.Lookup_Type='CP_PHASE_CODE'
     and FLVP.language='US'
     and a.status_code=FLVS.Lookup_Code
     and FLVS.Lookup_Type='CP_STATUS_CODE'
     and FLVS.language='US'
     and FLVS.view_application_id=0
     and b.queue_application_id = q.application_id
     and b.concurrent_queue_id = q.concurrent_queue_id
     and q.application_id = qt.application_id
     and qt.language='US'
     and q.concurrent_queue_id = qt.concurrent_queue_id
     and c2.language = 'US'
     --and vs.process (+) = b.os_process_id
     and a.oracle_process_id = vp.spid
     and vs.paddr = vp.addr (+)
     and a.status_code='R'
     and c2.USER_CONCURRENT_PROGRAM_NAME  in ('Salres Report')
     and vs.inst_id=vp.inst_id
     and round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) > 30
   order by 11 desc;


/*================================================
 16. Find the completed Concurrent program Name using printer name
==================================================
select * from fnd_concurrent_requests where printer like 'PRINTER_NAME';

/*================================================
 17. Concurrent completed Error with start date:
==================================================
select request_id, actual_start_date,actual_completion_date
,round((actual_completion_date - actual_start_date) * 24 * 60,2) time_IN_MINUTE
,phase_code, status_code 
,fcp.user_concurrent_program_name
,(SELECT USER_NAME FROM FND_USER WHERE USER_ID = REQUESTED_BY) USER_NAME
from fnd_concurrent_Requests fcr
  ,fnd_concurrent_programs_vl fcp
where actual_start_date between trunc(sysdate - 1) and '05-JUL-2023 17:00:00'
and fcr.concurrent_program_id = fcp.concurrent_program_id
and status_code in ( 'X','E')
AND round((actual_completion_date - actual_start_date) * 24 * 60,2) > 10
order by ACTUAL_START_DATE ;

/*===============================================
 18. Concurrent Program on hold status
================================================
set lines 900 pages 300
set heading on
set feedback on
set markup html on
SELECT a.request_id,  program,
     to_char(a.requested_start_date,'DD-MON-YYYY HH24:MI:SS') req_start_date,
     to_char(a.LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI:SS')LAST_UPDATED_DATE,a.requestor,b.USER_NAME LAST_UPDATED_BY,
     decode(a.phase_code,
              'P','Pending',
                  'Running') Phase_code,
       decode(a.status_code,
              'R', 'Normal',
              'W', 'Paused',
              'Q', 'Standby',
              'I', 'Scheduled') Status_code,
     decode(a.hold_flag,'Y','Yes','No') Hold_flag
  FROM fnd_conc_req_summary_v a,fnd_user b
  WHERE trunc(a.requested_start_date) >= '31-DEC-2025'
 and hold_flag='Y'
 and a.status_code not in ('D')
 and a.last_updated_by=b.user_id
order by 3 desc;


/*===============================================
 19. Output Post Processor (OPP) Log File Location from Request ID
================================================
SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
WHERE fcpp.processor_id = fcp.concurrent_process_id
AND fcpp.action_type = 6
AND fcpp.concurrent_request_id =23245346;

/*=================================================
 20. Find SQL text from Concurrent Request ID
===================================================
SELECT A.REQUEST_ID, D.SID, D.SERIAL#, D.OSUSER, D.PROCESS, C.SPID,
       E.SQL_TEXT
  FROM APPS.FND_CONCURRENT_REQUESTS A,
       APPS.FND_CONCURRENT_PROCESSES B,
       V$PROCESS C,
       V$SESSION D,
       V$SQL E
WHERE A.CONTROLLING_MANAGER = B.CONCURRENT_PROCESS_ID
   AND C.PID = B.ORACLE_PROCESS_ID
   AND B.SESSION_ID = D.AUDSID
   AND D.SQL_ADDRESS = E.ADDRESS
   AND A.REQUEST_ID = &REQUEST_ID;



/*===============================================
 21. Fidn the Standby Concurrent log and output node name & update 
=================================================
select distinct OUTFILE_NODE_NAME from fnd_concurrent_requests;
--update apps.fnd_concurrent_requests set logfile_node_name = 'TESTAP’, outfile_node_name = 'TESTAP' where logfile_node_name = 'PRODAP' and outfile_node_name = 'PRODAP';
select distinct file_node_name from fnd_conc_req_outputs;
--update apps.fnd_conc_req_outputs set file_node_name='TESTAP' where file_node_name='PRODAP';

/*===============================================
 22. Concurrent program running more than 10 mins
=================================================
SELECT f.request_id,
pt.user_concurrent_program_name user_conc_program_name,
f.actual_start_date start_on,
f.status_code
FROM apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
WHERE
f.phase_code                          ='R'
and pt.language='US'
AND f.concurrent_program_id                 = pt.concurrent_program_id
AND f.program_application_id                = pt.application_id
AND ROUND (((SYSDATE - actual_start_date)*60*24), 2) > 10
AND pt.user_concurrent_program_name='Sales Report'
AND f.actual_start_date IS NOT NULL;



/*===============================================
 23. Pending concurrent programs 
=================================================
SELECT
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,fu.user_name,f.argument_text,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)
        || ' HOURS ' ||
        floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)
        || ' MINUTES ' ||
        round((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600 -
        (floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)*60) ))
        || ' SECS ' time_difference,
      p.concurrent_program_name concurrent_program_name,
      --decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
      f.phase_code,
      f.status_code
from  apps.fnd_concurrent_programs p,
      apps.fnd_concurrent_programs_tl pt,
      apps.fnd_concurrent_requests f,
      apps.fnd_user fu
where f.concurrent_program_id = p.concurrent_program_id
      and f.program_application_id = p.application_id
      and f.concurrent_program_id = pt.concurrent_program_id
      and f.program_application_id = pt.application_id
      AND pt.language = USERENV('Lang')
      and f.actual_start_date is null
      and f.requested_by = fu.user_id
      --and fu.user_name='XXXX' 
      and pt.user_concurrent_program_name like '%%Sales%Report%%' 
      --and f.argument_text like '%'
order by
      f.actual_start_date desc;

/*=============================================
 24. concurrent requests that ran for over X minutes (more than)
==============================================
SELECT f.request_id
, TRUNC(((f.actual_completion_date-f.actual_start_date)/(1/24))*60) runtime
, f.actual_start_date asd
, f.actual_completion_date acd
, DECODE(p.concurrent_program_name,
'ALECDC',p.concurrent_program_name||'['||f.description||']'
,p.concurrent_program_name) cpn
, pt.user_concurrent_program_name ucpn
, f.phase_code
, f.status_code
, f.logfile_name
, f.argument_text
FROM apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE TRUNC(((f.actual_completion_date-f.actual_start_date)/(1/24))*60) > NVL('&minutes',60)
and f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
ORDER by f.actual_completion_date-f.actual_start_date desc;


/*=============================================
 25. Find the trace file enabled concurrent program details
===============================================
SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from apps.fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, apps.fnd_concurrent_programs_vl prog,
apps.fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;



/*==============================================
 26. Find scheduled concurrent program for specific request
================================================
SELECT fcpl.user_concurrent_program_name AS concurrent_program_name,
       fcp.concurrent_program_name AS short_name,
       fcr.request_id,
       fcr.phase_code,
       fcr.status_code,
       fcr.requested_start_date,
       fcr.actual_start_date,
       fcr.actual_completion_date,
       fr.responsibility_name,
       fu.user_name
FROM   fnd_concurrent_requests fcr
       JOIN fnd_concurrent_programs_tl fcpl
         ON fcr.concurrent_program_id = fcpl.concurrent_program_id
       JOIN fnd_concurrent_programs fcp
         ON fcr.concurrent_program_id = fcp.concurrent_program_id
       JOIN fnd_responsibility_tl fr
         ON fcr.responsibility_id = fr.responsibility_id
       JOIN fnd_user fu
         ON fcr.requested_by = fu.user_id
WHERE  fcpl.user_concurrent_program_name = 'Gather Schema Statistics'
       AND fcr.phase_code = 'P' -- Pending
       AND fcr.status_code = 'Q' -- Scheduled
       AND fcpl.language = 'US' -- Language preference
ORDER  BY fcr.requested_start_date;



/*==============================================
 27. pending/scheduled normal status
================================================
SELECT
      f.request_id, pt.user_concurrent_program_name user_conc_program_name, fu.user_name, f.argument_text,f.org_id, f.REQUESTED_START_DATE, p.concurrent_program_name concurrent_program_name, f.phase_code, f.status_code
from 
apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f,
apps.fnd_user fu
where f.concurrent_program_id = p.concurrent_program_id
      and f.program_application_id = p.application_id
      and f.concurrent_program_id = pt.concurrent_program_id
      and f.program_application_id = pt.application_id
      AND pt.language = USERENV('Lang')
      --and f.actual_start_date is not null
      and f.requested_by = fu.user_id
      and f.Phase_code ='P' 
      and f.STATUS_CODE = 'I'
      --and f.org_id is not null
order by pt.user_concurrent_program_name asc;



/*===============================================
 28. find Concurrent program running time between 23:00 to 3 AM
=================================================
SELECT
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,fu.user_name,f.argument_text,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)
        || ' HOURS ' ||
        floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)
        || ' MINUTES ' ||
        round((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600 -
        (floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)*60) ))
        || ' SECS ' time_difference,
      p.concurrent_program_name concurrent_program_name,
      --decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
      f.phase_code,
      f.status_code,
      f.actual_start_date
from  apps.fnd_concurrent_programs p,
      apps.fnd_concurrent_programs_tl pt,
      apps.fnd_concurrent_requests f,
      apps.fnd_user fu
where f.concurrent_program_id = p.concurrent_program_id
      and f.program_application_id = p.application_id
      and f.concurrent_program_id = pt.concurrent_program_id
      and f.program_application_id = pt.application_id
      AND pt.language = USERENV('Lang')
      and f.actual_start_date is not null
      and f.requested_by = fu.user_id
      --and fu.user_name='XXXXX' 
      --and pt.user_concurrent_program_name like 'Gather%' 
      --and f.argument_text like '%UAE%'
      and f.actual_start_date >= sysdate- 2
      and TO_NUMBER(to_char(f.actual_start_date,'HH24'))  IN (23,0,1,2,3)
order by
      f.actual_start_date desc;

/*================================================
 29. Find which concurrent manager ran a specific concurrent request.
==================================================
select b.USER_CONCURRENT_QUEUE_NAME
from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where  a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and    a.CONCURRENT_PROCESS_ID = c.controlling_manager
and    c.request_id = &request_id;
--and    c.request_id in ('121196','125189','1251182','1251161','125157','125154','125250');



/*===============================================
 30. concurrent Program locked session status & kill query:
=================================================
select 'alter system kill session ''' ||s.sid||','||s.serial#||''||',@' || s.inst_id || '''' || ' immediate; ' from gv$session s where sql_id='fnpyvpk41nd5s';


/*===============================================
 31. Find the concurrent program completed more than 1 hour
================================================
SELECT *
FROM (
    SELECT 
        frt.responsibility_name,
        fcr.request_id,
        fcp.user_concurrent_program_name AS program_name,
        fu.user_name,
        fcr.actual_start_date,
        fcr.actual_completion_date,
        ROUND(((fcr.actual_completion_date - fcr.actual_start_date) * 24 * 60), 2) AS duration_minutes,
        ROW_NUMBER() OVER (PARTITION BY frt.responsibility_name ORDER BY fcr.actual_start_date DESC) AS rn
    FROM 
        fnd_concurrent_requests fcr
        JOIN fnd_user fu ON fcr.requested_by = fu.user_id
        JOIN fnd_concurrent_programs_tl fcp ON fcr.concurrent_program_id = fcp.concurrent_program_id
        JOIN fnd_responsibility_tl frt ON fcr.responsibility_id = frt.responsibility_id
    WHERE 
        fcr.phase_code = 'C'  -- Completed
        AND fcr.actual_start_date IS NOT NULL
        AND fcr.actual_completion_date IS NOT NULL
        AND ((fcr.actual_completion_date - fcr.actual_start_date) * 24 * 60) > 60
        AND fcp.LANGUAGE = 'US'
        AND frt.LANGUAGE = 'US'
)
WHERE rn = 1
ORDER BY responsibility_name;



/*==============================================
 32. Query to get the count of concurrent program for last one day
================================================
SELECT 
    e.user_concurrent_program_name,
    COUNT(DISTINCT c.request_id) AS request_count
FROM 
    fnd_concurrent_processes a,
    fnd_concurrent_queues_vl b, 
    fnd_concurrent_requests c,
    fnd_concurrent_programs d,
    fnd_concurrent_programs_tl e,
    fnd_user f
WHERE  
    a.concurrent_queue_id = b.concurrent_queue_id
    AND a.concurrent_process_id = c.controlling_manager
    AND c.concurrent_program_id = d.concurrent_program_id
    AND d.concurrent_program_id = e.concurrent_program_id
    AND b.user_concurrent_queue_name = 'Sales Report'
    AND c.requested_by = f.user_id
    AND e.language = 'US'
    AND TRUNC(c.request_date) >= TRUNC(SYSDATE - 1)
GROUP BY 
    e.user_concurrent_program_name
ORDER BY 
    request_count DESC;



/*==============================================
 33. Printer related queries to find the load of the printer using size of concurrent program.
===============================================
SELECT
    r.request_id,
    p.user_concurrent_program_name,
    r.argument_text,
    r.actual_start_date,
    r.actual_completion_date,
    r.status_code,
    r.completion_text,
    o.file_name,
    o.file_size / 1024 AS file_size_kb
FROM
    fnd_concurrent_requests r
JOIN
    fnd_concurrent_programs_tl p
    ON r.concurrent_program_id = p.concurrent_program_id
    AND p.language = 'US'
LEFT JOIN
    fnd_conc_req_outputs o
    ON r.request_id = o.concurrent_request_id
WHERE
    p.user_concurrent_program_name = 'Consumer Tax Invoice/DN'
    --AND r.actual_start_date >= SYSDATE - 7  -- Last 7 days (adjust as needed)
    AND TRUNC(r.actual_start_date) = TO_DATE('05-JUL-2025','DD-MON-YYYY')
ORDER BY
    r.actual_start_date DESC;
SELECT
    TRUNC(r.actual_start_date) AS run_date,
    COUNT(*) AS request_count,
    ROUND(SUM(o.file_size) / (1024 * 1024), 2) AS total_file_size_mb
FROM
    fnd_concurrent_requests r
JOIN
    fnd_concurrent_programs_tl p
    ON r.concurrent_program_id = p.concurrent_program_id
    AND p.language = 'US'
JOIN
    fnd_conc_req_outputs o
    ON r.request_id = o.concurrent_request_id
WHERE
    p.user_concurrent_program_name like '%%Invoice%'  -- Replace with your program
    AND r.actual_start_date >= TRUNC(SYSDATE - 7)           -- Last 7 days (adjust if needed)
    AND r.phase_code = 'C'                                  -- Completed
    AND r.status_code = 'C'                                 -- Normal
GROUP BY
    TRUNC(r.actual_start_date)
ORDER BY
    run_date DESC;