ACCEPT input1       PROMPT "Enter Application Short Name (PA/FA/PO)? "
SET HEADING ON
SET NULL ""
SET PAGESIZE 9999
SET LINESIZE 80
SET PAUSE OFF
SET VERIFY OFF
SET FEEDBACK OFF
SPOOL    UT_CPROG.log
TTITLE LEFT  ' Application:     < ' o1 ' ( ' o2 ' ) >'  SKIP 1 -
             ' Executable Type: < ' o3 ' >'             SKIP 1 -
             '----------------------------------------'        -
             '----------------------------------------' SKIP 1
BREAK ON REPORT
BREAK ON c1           SKIP PAGE ON c3 SKIP PAGE
ACCEPT input1       PROMPT "Enter Application Short Name (PA/FA/PO)? "
SET HEADING ON
SET NULL ""
SET PAGESIZE 9999
SET LINESIZE 80
SET PAUSE OFF
SET VERIFY OFF
SET FEEDBACK OFF
SPOOL    UT_CPROG.log
TTITLE LEFT  ' Application:     < ' o1 ' ( ' o2 ' ) >'  SKIP 1 -
             ' Executable Type: < ' o3 ' >'             SKIP 1 -
             '----------------------------------------'        -
             '----------------------------------------' SKIP 1
BREAK ON REPORT
BREAK ON c1           SKIP PAGE ON c3 SKIP PAGE
COL c1                NEW_VALUE o1                      NOPRINT
COL c2                NEW_VALUE o2                      NOPRINT
COL c3                NEW_VALUE o3                      NOPRINT
COL c4   FORMAT A18   HEADING 'Concurrent|Program Name'
COL c5   FORMAT A61   HEADING 'Concurrent User Program Name'
SELECT   SUBSTR(a.application_name,1,60) c1
,        a.application_short_name c2
,        DECODE(SUBSTR(cp.user_concurrent_program_name,4,1),':'
         ,      'Concurrent Manager Executable'
         ,      'Subprogram or Function') c3
,        SUBSTR(cp.concurrent_program_name,1,16) c4
,        SUBSTR(cp.user_concurrent_program_name,1,55) c5
FROM     fnd_concurrent_programs cp ,        fnd_application a
WHERE    cp.application_id = a.application_id
AND      a.application_short_name LIKE UPPER('&input1%') UNION ALL
SELECT   SUBSTR(a.application_name,1,60) c1
,        a.application_short_name c2  ,        'Form Executable' c3
,        SUBSTR(f.form_name,1,16) c4 ,        SUBSTR(f.user_form_name,1,55) c5
FROM     fnd_form f ,        fnd_application a
WHERE    f.application_id = a.application_id
AND      a.application_short_name LIKE UPPER('&input1%') ORDER BY 1,2,3,4;
SPOOL OFF
/

Return to : Oracle Database, SQL, Application, Programming Tips