Standard Debug R12 API

API to debug:

fnd_log.STRING(  log_level => '6'
                                 ,module    => 'Project / Task Validate Process'
                                ,message   => 'Source project and Default task not defined.'
                               );

Here Log Level 6 Indicates that we are writing log for ‘UNEXPECTED’ level. We will use the same level for all logs. Module represents that for what interface we are writing logs and message is the log message.

Below profile option should be set at user level for using fnd_log.STRING API. Below are the profile options.

FND: Debug Log Level                                    LEVEL_UNEXPECTED

FND: Debug Log Enabled                              Yes

FND: Debug Log Module                               % or Module Name (e.g. GESSS Project / Task Validate Process)

If above profile options are set for the user, while running concurrent program from that user it will write log in FND_LOG_MESSAGES table.  If module is ‘%’ then it will write log for every concurrent program where we have used the API. If we set it to Module Level then it will write log only for that module e.g. if FND: Debug Log Module  is set to ‘Project / Task Validate Process' then it will write log for that concurrent program only.

To test it from Backend, make sure to manually initialize the PL/SQL layer logging for the current session using FND_GLOBAL.APPS_INITIALIZE.

Below is the sample code:

BEGIN

FND_GLOBAL.APPS_INITIALIZE(13831,20420,0);

fnd_log.STRING(  log_level => '6'

                                 ,module    => 'TEST'

                                ,message   => 'Source project and Default task not defined.'

                               );

END;

SELECT * FROM FND_LOG_MESSAGES

WHERE MODULE = 'TEST'

Tracking Oracle Customization's Using Register Flagged Files Tool in R12

Having Better Life With Customizations...

Whenever we implement Oracle Applications, the client often comes up with some businesses processes which cannot be completely implemented using the existing ERP processes. These are generally accommodated by customizations in the ERP. This gives rise to creation of custom objects/systems. Over a period of time when the actual use of system increases the number of customizations increases too.

During a course of time it may happen that the company needs to upgrade the environment or apply certain patches that are released by Oracle. It may happen that there is a strong impact on the customizations that are built. If the customizations are done by using ‘Customization By Modification’ approach there is a danger of the changes being completely washed off by the upgrade or patch application. So is there any way to avoid this? Or at least a way to minimize the impact?

Yes there is!!!

The way is to use ‘Register Flagged Files Tool’ in R12.

Register Flagged Files tool can be accessed through Oracle Applications Manager (OAM) responsibility in R12.

Flagged files is a concept within the Oracle eBusiness Suite (EBS) release 12, where you flag a standard deployment file, let's say a Forms file, a Package or a Java class file. When you run the patch analysis, the list of flagged files will be checked and in case one of these files gets patched, the analysis report will tell you.

You can flag as many files as you want, in whatever relationship they are with your customizations. In addition to the flag itself you can add a comment. You should use this comment to point to your customization reference (here XXAR_RPT_066 or XXAP_CUST_030). It is suggested to put the flagged files data file directly into your CEMLI patch. Herewith the flagged files registration will be executed right at the same time when the patch gets applied.

Amount to Word Function in Indian Format

SQL> SELECT AMOUNT_TO_WORDS(155221.25) FROM DUAL;

AMOUNT_TO_WORDS(155221.25)
-------------------------------------------------------------------------------
One lakh fifty-five thousand two hundred twenty-one rupees and twenty-five paise only

CREATE OR REPLACE FUNCTION AMOUNT_TO_WORDS (P_AMT  IN NUMBER)                                       

RETURN VARCHAR2 IS

    M_MAIN_AMT_TEXT      VARCHAR2(2000) ;

    M_TOP_AMT_TEXT       VARCHAR2(2000) ;

    M_BOTTOM_AMT_TEXT    VARCHAR2(2000) ;

    M_DECIMAL_TEXT       VARCHAR2(2000) ;

    M_TOP                NUMBER(20,5) ;

    M_MAIN_AMT           NUMBER(20,5) ;

    M_TOP_AMT            NUMBER(20,5) ;

    M_BOTTOM_AMT         NUMBER(20,5) ;

    M_DECIMAL            NUMBER(20,5) ;

    M_AMT                NUMBER(20,5);

    M_TEXT               VARCHAR2(2000) ;

    BEGIN

       M_MAIN_AMT        := NULL ;

       M_TOP_AMT_TEXT    := NULL ;

       M_BOTTOM_AMT_TEXT := NULL ;

       M_DECIMAL_TEXT    := NULL ;

      

       -- To get paise part

       M_DECIMAL    := P_AMT - TRUNC(P_AMT) ;

      

       IF M_DECIMAL >0 THEN

       M_DECIMAL := M_DECIMAL *100;

       END IF;

      

       M_AMT        := TRUNC(P_AMT) ;         

    

    

       M_TOP        := TRUNC(M_AMT / 100000) ;

       M_MAIN_AMT   := TRUNC(M_TOP / 100);

       M_TOP_AMT    := M_TOP - M_MAIN_AMT * 100 ;

       M_BOTTOM_AMT :=  M_AMT - (M_TOP * 100000) ;

    

      IF M_MAIN_AMT > 0 THEN

          M_MAIN_AMT_TEXT := TO_CHAR(TO_DATE(M_MAIN_AMT,'J'),'JSP') ;

          IF M_MAIN_AMT = 1 THEN

            M_MAIN_AMT_TEXT := M_MAIN_AMT_TEXT || ' CRORE ' ;

          ELSE

            M_MAIN_AMT_TEXT := M_MAIN_AMT_TEXT || ' CRORES ' ;

          END IF ;

       END IF ;

    

       IF M_TOP_AMT > 0 THEN

          M_TOP_AMT_TEXT := TO_CHAR(TO_DATE(M_TOP_AMT,'J'),'JSP') ;

          IF M_TOP_AMT = 1 THEN

            M_TOP_AMT_TEXT := M_TOP_AMT_TEXT || ' LAKH ' ;

          ELSE

            M_TOP_AMT_TEXT := M_TOP_AMT_TEXT || ' LAKHS ' ;

          END IF;

       END IF ;

       IF M_BOTTOM_AMT > 0 THEN

          M_BOTTOM_AMT_TEXT := TO_CHAR(TO_DATE(M_BOTTOM_AMT,'J'),'JSP') ;

       END IF ;

       IF M_DECIMAL > 0 THEN

          IF NVL(M_BOTTOM_AMT,0) + NVL(M_TOP_AMT,0) > 0 THEN

             M_DECIMAL_TEXT := ' AND ' || TO_CHAR(TO_DATE(M_DECIMAL,'J'),'JSP') || ' Paise ' ;

          ELSE

             M_DECIMAL_TEXT :=  TO_CHAR(TO_DATE(M_DECIMAL,'J'),'JSP') ||' Paise ';

          END IF ;

            END IF ;

       M_TEXT := LOWER(M_MAIN_AMT_TEXT || M_TOP_AMT_TEXT || M_BOTTOM_AMT_TEXT || ' Rupees' || M_DECIMAL_TEXT || ' ONLY') ;

       M_TEXT := UPPER(SUBSTR(M_TEXT,1,1))|| SUBSTR(M_TEXT,2);

       M_TEXT := ' '|| M_TEXT;

       RETURN(M_TEXT);

    

    END AMOUNT_TO_WORDS;

    /

Send an E-mail from PL/SQL

How to Send an E-mail from PL/SQL

In Oracle 8i, one can use the UTL_SMTP package for sending messages from the database via e-mail. However as yet it can not be used to attach files to. If the results from the query does not exceed varchar2(4000) however, we can use the following method to send the result via e-mail from the database. 

Note: This package requires that you install java option. 

DECLARE 

SendorAddress Varchar2(30) := 'oracleapps4all@gmail.com';

ReceiverAddress varchar2(30) := 'gangadharcharyk@trusstechnosofts.com';

EmailServer varchar2(30) := 'xxx.xxx.xxx.xxx';

Port number := 25;

conn UTL_SMTP.CONNECTION;

crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );

mesg VARCHAR2( 4000 );

mesg_body varchar2(4000);

cursor c1 is

select d.deptno,count(e.empno)

from dept d,

emp e

where e.deptno(+) = d.deptno

group by d.deptno

order by 1;

BEGIN 

conn:= utl_smtp.open_connection( EmailServer, Port );

utl_smtp.helo( conn, EmailServer );

utl_smtp.mail( conn, SendorAddress);

utl_smtp.rcpt( conn, ReceiverAddress);

mesg:= 

'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||

'From:'||SendorAddress|| crlf ||

'Subject: Sending Mail From Database' || crlf ||

'To: '||ReceiverAddress || crlf ||

'' || crlf ||

'Dept No' ||' Count ' ||crlf||

'----------------------' ||' ------' ||crlf;

for c1rec in c1 LOOP

mesg := mesg || rpad(c1rec.deptno,22,' ') ||'

'||rpad(c1rec.count,10,' ') || crlf;

end loop;

utl_smtp.data( conn, mesg );

utl_smtp.quit( conn );

END;

Wrapping / encrypting PL/SQL code

Wrapping / encrypting PL/SQL code

Wrapping / encrypting PL/SQL code:

There are two way to wrap your pl/sql code.

·         Wrap

·         DBMS_DDL

Wrapping can be done with the wrap utility and DBMS_DDL subprograms. 

The wrap utility is run from the command line and processes an input SQL file, such as a SQL*Plus installation script. The DBMS_DDL subprograms wrap a single PL/SQL unit, such as a single CREATE PROCEDURE command, that has been generated dynamically.

WRAPPING ORACLE PL/SQL CODE WITH THE DBMS_DDL CREATE_WRAPPED PROCEDURE:

1. Create an anonymous block with your code (which needs to be encrypted) in generate_spec and generate_body, enclosed in quotes.

DECLARE

/* The package_text variable contains the text to create the package spec and body */

  package_text VARCHAR2(32767);

  FUNCTION generate_spec (pkgname VARCHAR2) RETURN VARCHAR2 AS

  BEGIN

     RETURN 'CREATE PACKAGE ' || pkgname || ' AS

PROCEDURE add (x NUMBER, y NUMBER); END ' || pkgname || ';';

  END generate_spec;

 

  FUNCTION generate_body (pkgname VARCHAR2) RETURN VARCHAR2 AS

  BEGIN

     RETURN 'CREATE PACKAGE BODY ' || pkgname || ' AS

PROCEDURE add (x NUMBER, y NUMBER) IS

BEGIN dbms_output.put_line (x+y); END add; END ' || pkgname || ';';

  END generate_body;

 

BEGIN

  -- generate package spec

  package_text := generate_spec('Calculation');

  -- create and wrap the package spec

  SYS.DBMS_DDL.CREATE_WRAPPED(package_text); 

  -- generate package body 

  package_text := generate_body('Calculation'); 

  -- create and wrap the package body 

  SYS.DBMS_DDL.CREATE_WRAPPED(package_text);

END;

2. The package object – calculation will get created in the data base in encrypted format.

Sql> Calculation.add(10, 20);

3. Check the all sources table. You can see an encrypted version of your code.

WRAPPING ORACLE PL/SQL CODE WITH THE WRAP UTILITY:

The wrap utility processes an input SQL file and obfuscates only the PL/SQL units in the file, such as a package specification, package body, function, procedure, type specification, or type body. It does not obfuscate PL/SQL content in anonymous blocks or triggers or non-PL/SQL code.
To run the wrap utility, enter the wrap command at your operating system prompt using the following syntax:
         wrap iname=input_file [oname=output_file]
Do not use any spaces around the equal signs.
Example:

1.      Write a procedure abc(), in a file abc.sql (do not compile it in database) and save it in C:\

create procedure abc is

begin

dbms_output.put_line('Hello World');

end abc;

2.      First generate the wrapped code for this sql file using the following command.

wrap iname=C:\abc.sql

You will get the following message:

      PL/SQL Wrapper: Release 9.0.1.3.1- Production on Sat Jun 0412:59:59  2011

Copyright (c) Oracle Corporation 1993, 2001.  All Rights Reserved.

Processing C:\abc.sql to abc.plb

3.      The wrapped file code abc.plb is created in the same directory (C:\abc.sql). It will look like this

     4.      Now compile the encrypted code (abc.plb) in the database. 

SQL> @C:\abc.plb

Procedure created.

5.      So, our source code is not visible through the USER_SOURCE, ALL_SOURCE, or DBA_SOURCE data dictionary views.

6.      Now execute the procedure

SQL> ed;

Wrote file afiedt.buf

  1  begin

  2  abc();

  3* end;

SQL> /

PL/SQL procedure successfully completed.

SQL> set serveroutput on

SQL> /

Hello World

PL/SQL procedure successfully completed.

Frequently used queries for oracle apps developers

Frequently used queries for oracle apps developers

Get all the views that refer a particular table:

SELECT o.object_name, o.status, o.last_ddl_time

  FROM SYS.dba_objects o

 WHERE o.object_type = 'VIEW'

   AND EXISTS (

              SELECT 'x'

                   FROM SYS.dba_dependencies d

           WHERE d.referenced_type = 'TABLE'

                 AND d.TYPE = 'VIEW'

                    AND d.owner = 'APPS'

             AND d.NAME = o.object_name

                 AND d.referenced_name LIKE '');

Get all the tables with the column name:

SELECT

  TABLE_NAME

FROM

  all_tab_columns

WHERE

  column_name = ''

Get SQL help from the Data base itself:

If you need any syntax of a sql command or its details, you don’t need to search for it in google. what you just need to do is use the below sql command from the data base

In iSQL*Plus, click the Help button to display iSQL*Plus help. Or Use the below command line help system. In some DB system schema may not be available, then use sys.help instead.

select *

from   system.help

where  upper(topic)=upper('string')

Find from which responsibility a concurrent program can be run:

SELECT distinct

  a.user_concurrent_program_name,

  a.description,

  request_group_name,

  e.responsibility_name

FROM

  fnd_concurrent_programs_tl a,

  fnd_request_groups b,

  fnd_request_group_units c,

  fnd_responsibility d,

  fnd_responsibility_tl e

WHERE

  a.concurrent_program_id = c.request_unit_id     

and b.request_group_id = c.request_group_id

and b.request_group_id = d.request_group_id

and d.responsibility_id = e.responsibility_id

and a.application_id = b.application_id

and b.application_id = c.application_id

and d.application_id = e.application_id

and a.concurrent_program_id = :p_conc_program_id

Find all running concurrent requests with their run times:

The below query is useful for analyzing concurrent program run times and identifying their performance

select request_id,

         fcpt.user_concurrent_program_name,

         completion_text,

         actual_start_date,

         actual_completion_date,

         to_date((actual_completion_date - actual_start_date), 'HH:MM:SS') duration

 from fnd_concurrent_requests fcr,

         fnd_concurrent_programs fcp,

         fnd_concurrent_programs_tl fcpt

where fcr.concurrent_program_id = fcp.concurrent_program_id

    and fcp.concurrent_program_id = fcpt.concurrent_program_id

    and fcr.actual_start_date > sysdate - 1

    order by actual_completion_date - actual_start_date desc

Search all packages for a line of code:

SELECT  *

FROM    ALL_SOURCE

WHERE   TYPE IN ('PACKAGE', 'PACKAGE BODY')

AND     TEXT LIKE '%XXXX%'

The below table shows all the data base object information:

select * from dba_objects where object_name like '%HEADER%' and object_type = 'TABLE'

Sequal to get the concurrent program file name (procedure/pkg name) based on the concurrent program name

select fct.user_concurrent_program_name, 
          fcp.concurrent_program_name,
          fe.execution_file_name,
          fl.meaning execution_method
from fnd_concurrent_programs_tl fct,
        fnd_concurrent_programs fcp,
        fnd_executables fe,
        fnd_lookups fl
where
     upper(fct.user_concurrent_program_name) = upper('concurrent program') 
and     fct.concurrent_program_id = fcp.concurrent_program_id
and     fe.executable_id = fcp.executable_id
and     fl.lookup_code = fe.execution_method_code
and     fl.lookup_type = 'CP_EXECUTION_METHOD_CODE'

List of all active workflow users and their roles

select  wu.name user_name,

        wr.name role_name

from    wf_users wu,

        wf_user_roles wur,

        wf_roles wr

where   wu.name = wur.user_name

and     wur.role_name = wr.name

and     wu.status = 'ACTIVE'

and     wr.status = 'ACTIVE'

and     wr.orig_system = 'WF_LOCAL_ROLES'

order by wu.name,

        wr.name

Concurrent requests raised in the last day

select request_id,

       decode(parent_request_id, -1, 'None') as parent_request_id,

       fcpt.user_concurrent_program_name,

       decode(  fcr.status_code,

                'A', 'Waiting',

                'B', 'Resuming',

                'C', 'Normal',

                'D', 'Cancelled',

                'E', 'Error',

                'F', 'Scheduled',

                'G', 'Warning',

                'H', 'On Hold',

                'I', 'Normal',

                'M', 'No Manager',

                'Q', 'Standby',

                'R', 'Normal',

                'S', 'Suspended',

                'T', 'Terminating',

                'U', 'Disabled',

                'W', 'Paused',

                'X', 'Terminated',

                'Z', 'Waiting') as status,       

       decode(  fcr.phase_code,

                'C', 'Completed',

                'I', 'Inactive',

                'P', 'Pending',

                'R', 'Running') as phase,

       fu.user_name,

       fr.responsibility_name,              

       completion_text,

       argument_text,

       request_date,

       requested_start_date,

       actual_start_date,

       actual_completion_date,

       fcp.concurrent_program_name,

       fe.executable_name,

       fe.execution_file_name

from   fnd_concurrent_requests fcr,

       fnd_concurrent_programs fcp,

       fnd_concurrent_programs_tl fcpt,

       fnd_executables fe,

       fnd_responsibility_vl fr,

       fnd_user fu

where  fcr.concurrent_program_id = fcp.concurrent_program_id

and    fcp.concurrent_program_id = fcpt.concurrent_program_id

and    fcp.executable_id = fe.executable_id

and    fcr.responsibility_id = fr.responsibility_id

and    fcr.requested_by = fu.user_id

and    fcr.request_date > sysdate - 1

--and    fcr.concurrent_program_id = XXXX

order by request_id desc

How to find the Trace file generated for a concurrent program

Check out the following blog post for enabling the trace file for a concurrent program.

http://oracleapps4u.blogspot.com/2011/05/enable-trace-file-for-concurrent.html

You can use the following query to locate the trace file, it takes as input the ' Concurrent Request id'

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',
'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
fnd_concurrent_requests req, 
v$session ses,
v$process proc, 
v$parameter dest, 
v$parameter dbnm,
fnd_concurrent_programs_vl prog, 
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;

The output would be 'directory'/'filename.trc'
eg: 'directory'/ora_3465_act.trc.

In the above directory, do 'ls -l' for '*3465*trc' and any latest file that you see with 3465 as a part of the file name would be the trace file.

Removing Table Lock

Login to database as SYS or SYSTEM user. Find out the SESSION_ID by running the following SELECT statement 
SELECT SESSION_ID 
FROM DBA_DML_LOCKS 
WHERE NAME = Table Name;

Use this session id to find SERIAL# by using following SELECT statment 
SELECT SID,SERIAL# 
FROM V$SESSION 
WHERE SID IN (SELECT SESSION_ID 
FROM DBA_DML_LOCKS 
WHERE NAME = Table Name)

Use ALTER SYSTEM command to KILL SESSION and this will release the lock. 
ALTER SYSTEM KILL SESSION 'SID,SERIALl#';

This script will report the SQL text of some of the locks currently being held in the database

select s.username username, 

       a.sid sid, 

       a.owner||'.'||a.object object, 

       s.lockwait, 

       t.sql_text SQL

from   v$sqltext t, 

       v$session s, 

       v$access a

where  t.address=s.sql_address 

and    t.hash_value=s.sql_hash_value 

and    s.sid = a.sid 

and    a.owner != 'SYS'

and    upper(substr(a.object,1,2)) != 'V$'

Script to check Responsibilities assigned to a particular user or users assigned for particular responsibility or all users and their responsibilities:

 SELECT fu.user_id,

fu.user_name,

fur.responsibility_id,
          fr.responsibility_name
FROM fnd_user fu,

fnd_user_resp_groups fur,

fnd_responsibility_vl fr
WHERE fu.user_id = fur.user_id
AND fr.application_id = fur.responsibility_application_id
AND fr.responsibility_id = fur.responsibility_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date)
AND TRUNC (NVL ((fr.end_date - 1), SYSDATE))
AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
AND TRUNC (NVL ((fur.end_date - 1), SYSDATE))
AND user_name like ‘OPERATIONS’ --- for all user or for particular user
AND fur.responsibility_application_id = 155 –- Check particular resp.

order by user_name

Script to find Oracle API's for any module:

select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like '%Header%'
and a.type = u.object_type
and a.name like 'AR_%API%' –- Checking for AR Related APIs
order by
a.owner, a.name

Query used for audit point of view i.e. when a profile is changed and by which user 

SELECT t.user_profile_option_name,

profile_option_value,

v.creation_date,

v.last_update_date,

v.creation_date - v.last_update_date "Change Date",

(SELECT UNIQUE user_name

FROM fnd_user

WHERE user_id = v.created_by) "Created By",

(SELECT user_name

FROM fnd_user

WHERE user_id = v.last_updated_by) "Last Update By"

FROM fnd_profile_options o,

fnd_profile_option_values v,

fnd_profile_options_tl t

WHERE o.profile_option_id = v.profile_option_id

AND o.application_id = v.application_id

AND start_date_active <= SYSDATE

AND NVL (end_date_active, SYSDATE) >= SYSDATE

AND o.profile_option_name = t.profile_option_name

AND level_id =  :p_value

AND t.LANGUAGE IN (SELECT language_code

FROM fnd_languages

WHERE installed_flag = 'B'

UNION

SELECT nls_language

FROM fnd_languages

WHERE installed_flag = 'B')

ORDER BY user_profile_option_name;

:p_value is (10001 -> site level, 10002 -> application level, 10003 -> responsibility level, 10004 – user level)

Check Current Applied Patch

SELECT patch_name, patch_type,

maint_pack_level,

creation_date

FROM applsys.ad_applied_patches

ORDER BY creation_date DESC

Query used to view the patch level status of all modules

SELECT a.application_name,

DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,

patch_level

FROM apps.fnd_application_vl a,

apps.fnd_product_installations b

WHERE a.application_id = b.application_id;

This query will shows concurrent program processing time

SELECT f.request_id ,

pt.user_concurrent_program_name user_concurrent_program_name

, f.actual_start_date actual_start_date

, f.actual_completion_date actual_completion_date,

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,

DECODE(p.concurrent_program_name,'ALECDC',p.concurrent_program_name||'['||f.descriptio

n||']',p.concurrent_program_name) concurrent_program_name

, decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase

, f.status_code

FROM apps.fnd_concurrent_programs p

, apps.fnd_concurrent_programs_tl pt

, apps.fnd_concurrent_requests f

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

ORDER by f.actual_completion_date-f.actual_start_date desc;

View all types of request Application wise

SELECT fa.application_short_name,

fcpv.user_concurrent_program_name,

description,

DECODE (fcpv.execution_method_code,

'B', 'Request Set Stage Function',

'Q', 'SQL*Plus',

'H', 'Host',

'L', 'SQL*Loader',

'A', 'Spawned',

'I', 'PL/SQL Stored Procedure',

'P', 'Oracle Reports',

'S', 'Immediate',

fcpv.execution_method_code

) exe_method,

output_file_type,

program_type,

printer_name,

minimum_width,

minimum_length,

concurrent_program_name,

concurrent_program_id

FROM fnd_concurrent_programs_vl fcpv, fnd_application fa

WHERE fcpv.application_id = fa.application_id

ORDER BY description

Script to display status of all the Concurrent Managers

select distinct Concurrent_Process_Id CpId, PID Opid,

            Os_Process_ID Osid, 

            Q.Concurrent_Queue_Name Manager,

            P.process_status_code Status,

            To_Char(P.Process_Start_Date, 'MM-DD-YYYY HH:MI:SSAM') Started_At

from   Fnd_Concurrent_Processes P, 

            Fnd_Concurrent_Queues Q, 

            FND_V$Process

where  Q.Application_Id = Queue_Application_ID

  and  Q.Concurrent_Queue_ID = P.Concurrent_Queue_ID

  and  Spid = Os_Process_ID

  and  Process_Status_Code not in ('K','S')

order  by Concurrent_Process_ID, Os_Process_Id, Q.Concurrent_Queue_Name

Print Oracle Apps versions

SELECT substr(a.application_short_name, 1, 5) code,

       substr(t.application_name, 1, 50) application_name,

       p.product_version version

FROM   fnd_application a,

       fnd_application_tl t,

       fnd_product_installations p

WHERE  a.application_id = p.application_id

AND    a.application_id = t.application_id

AND    t.language = USERENV('LANG')