1. Connect to the Applications server as ‘applmgr’.
2. Create an $XDO_TOP/temp directory.
3. Go to the $XDO_TOP/resource folder.
4. Create an xdodebug.cfg file with the following 2 lines (specifying the actual file path, not the UNIX environment variable):

LogLevel=STATEMENT
LogDir=<path to $XDO_TOP>/temp

5. Restart the Concurrent Managers.
6. Run a concurrent request with XML Publisher bursting, and debug files will be created under the $XDO_TOP/temp.

Note: for standalone instances of BI Publisher, the xdodebug.cfg file should be created in the $AF_JRE_TOP/lib folder.

Application developers can define their own Profile Options, and it’s often useful to define a list of values (LOV) to validate the value assigned to the profile option.

Conventional SQL is used to define these LOV’s, but the exact syntax is a little Applications-specific.

Here is an example, which is entered (Responsibility: Application Developer; Navigation Path:  Profile) in the “SQL Validation used for the Profile Option’s List of Values” field:

SQL="
SELECT SUBSTR(qrslt.display_name||' ('||qrslt.email_address||')',1,80) \"Full Name\"
,      SUBSTR(qrslt.name,1,30)  \"Name\"
INTO   :visible_option_value
,      :profile_option_value
FROM
(
SELECT display_name
,      name
,      email_address
FROM   wf_users
WHERE  orig_system = 'FND_USR'
AND    email_address IS NOT NULL
AND    status = 'ACTIVE'
AND    TRUNC(SYSDATE) >= TRUNC(start_date)
AND    SYSDATE < NVL(expiration_date, SYSDATE + 1)
AND    name = 'APHELPDESK'
UNION
SELECT display_name
,      name
,      email_address
FROM   wf_users
WHERE  orig_system = 'PER'
AND    email_address IS NOT NULL
AND    status = 'ACTIVE'
AND    TRUNC(SYSDATE) >= TRUNC(start_date)
AND    SYSDATE < NVL(expiration_date, SYSDATE + 1)
)      qrslt
ORDER BY 1"
COLUMN="\"Full Name\"(30),\"Name\"(30)"

Oracle Discoverer holds a set of database tables for each Discoverer EUL being used. In the following queries, the prefix eul_owner should be replaced by the database owner corresponding to the specific EUL in question.

The following reporting statistics may be of interest…

1. Discoverer Reports run in the last year (i.e. since SYSDATE-365):

SELECT qs.qs_doc_name              report_run
,      qs.qs_doc_details           report_detail
,      qs.qs_created_date          date_run
,      fnd.user_name               user_name
FROM   <eul_owner>.eul5_qpp_stats  qs
,      fnd_user                    fnd
WHERE  1 = 1
AND    fnd.user_id(+) = REPLACE(qs.qs_created_by,'#','')
-- AND    user_name = 'SYSADMIN'
AND    qs.qs_created_date > SYSDATE - 365
ORDER BY qs.qs_created_date DESC
;

2. Discoverer Report usage by USER:

SELECT COUNT(*)                    num_reports
,      fnd.user_name               user_name
,      ppx.full_name               employee_name
FROM   <eul_owner>.eul5_qpp_stats  qs
,      fnd_user                    fnd
,      per_people_x                ppx
WHERE  1 = 1
AND    fnd.user_id(+) = REPLACE(qs.qs_created_by,'#','')
AND    ppx.person_id(+) = fnd.employee_id
-- AND    user_name = 'SYSADMIN'
AND    qs.qs_created_date > SYSDATE - 365
GROUP BY fnd.user_name
,      ppx.full_name
ORDER BY COUNT(*) DESC
;

3. Discoverer Report usage by REPORT:

SELECT COUNT(*)                    num_reports
,      qs.qs_doc_name              report_run
,      qs.qs_doc_details           report_detail
FROM   <eul_owner>.eul5_qpp_stats  qs
,      fnd_user                    fnd
,      per_people_x                ppx
WHERE  1 = 1
AND    fnd.user_id(+) = REPLACE(qs.qs_created_by,'#','')
AND    ppx.person_id(+) = fnd.employee_id
-- AND    user_name = 'SYSADMIN'
AND    qs.qs_created_date > SYSDATE - 365
GROUP BY qs.qs_doc_name
,      qs.qs_doc_details
ORDER BY COUNT(*) DESC
;

4. Discoverer Report ownership and assignment (users/responsibilities):

SELECT DISTINCT a.doc_name             disco_report
,      fu_owner.user_name              owner
,      d.responsibility_key            responsbility
,      fu.user_name
,      ppx.full_name                   employee_name
FROM   <eul_owner>.eul5_documents      a
,      <eul_owner>.eul5_access_privs   b
,      <eul_owner>.eul5_eul_users      c
,      fnd_responsibility              d
,      fnd_user                        fu
,      fnd_user                        fu_owner
,      per_people_x                    ppx
WHERE  a.doc_id = b.gd_doc_id(+)
AND    b.ap_type(+) = 'GD'
AND    b.ap_eu_id = c.eu_id(+)
AND    REPLACE(c.eu_username,'#','') = TO_CHAR(d.responsibility_id(+)) || TO_CHAR(d.application_id(+))
AND    TO_CHAR(fu.user_id(+)) = REPLACE(c.eu_username,'#','')
AND    TO_CHAR(fu_owner.user_id(+)) = REPLACE(a.doc_created_by,'#','')
AND    NVL(fu.end_date(+),SYSDATE + 1) > SYSDATE
AND    NVL(fu_owner.end_date(+),SYSDATE + 1) > SYSDATE
AND    ppx.person_id(+) = fu.employee_id
AND    (   fu_owner.user_name = <user>
       OR  fu.user_name = <user>
       )
ORDER BY a.doc_name ASC
;

The following query will list database locks, but needs to be run as a user with the appropriate access to the v$… and dba_locks tables:

SELECT w.session_id   waiting_session
,      h.session_id   holding_session
,      w.lock_type
--,      h.mode_held
--,      w.mode_requested
,      (
       SELECT module 
       ||     ' :: ' 
       ||     action 
       FROM   v$session 
       WHERE  sid=h.session_id
       )              holding_module_action
,      (
       SELECT module 
       ||     ' :: ' 
       ||     action 
       FROM   v$session 
       WHERE  sid = w.session_id
       )              waiting_module_action
FROM   (
       SELECT /*+ NO_MERGE */ * 
       FROM   dba_locks
       )              w
,      (
       SELECT /*+ NO_MERGE */ * 
       FROM   dba_locks
       )              h
WHERE  ((   (h.mode_held != 'None') 
        AND (h.mode_held != 'Null')
        AND (  (h.mode_requested = 'None') 
            OR (h.mode_requested = 'Null')
            )
        )
        AND ((  (w.mode_held = 'None') 
             OR (w.mode_held = 'Null')
             )
             AND (   (w.mode_requested != 'None') 
                 AND (w.mode_requested != 'Null')
                 )
             )
       )
AND    w.lock_type = h.lock_type
AND    w.lock_id1 = h.lock_id1
AND    w.lock_id2 = h.lock_id2
AND    w.session_id != h.session_id
;

UNIX Shell scripts are often used to perform interface data file manipulation tasks (basic validation, re-naming, archiving, etc). This is fine when the attributes being manipulated are, say, in the file name. But what if the script needs to grab a value from inside the data file? And maybe the file is in XML format.

For example , the XML data file contains the following string:

<FileSequenceNum >0000015</FileSequenceNum>

…and the value of FileSequenceNum is required by the shell script. There must be loads of ways to do this, but here are three examples:

TAG=FileSequenceNum
FILE=testfile.txt

VALUE=`sed -ne "/$TAG/s/[^0-9]*\([0-9]*\)..*/\1/p" $FILE`

Or

VALUE=`grep -w $ TAG $FILE | awk -F'>' '{ print $2 }' | awk -F'<' '{print $1}'`

Or

TAG1="FileSequenceNum "
TAG2="FileSequenceNum"

grep -w $TAG1 dt_test.xml | sed -e 's/^[ \t]*//' | sed "s/<$TAG1>\(.*\)<\/$TAG2.*/\1/"

echo "Value is: $VALUE"

In the last example, two $TAG variables are used because the data file contains a trailing space in the opening XML tag name, but no space in the closing tag name:

<FileSequenceNum >0000015<FileSequenceNum>

Log on to the database/applications server, and run the following UNIX commands…

cd $ADMIN_SCRIPTS_HOME
adapcctl.sh stop; adoacorectl.sh stop;
adoacorectl.sh start; adapcctl.sh start;

Or, without changing directory…

$ADMIN_SCRIPTS_HOME/adapcctl.sh stop; $ADMIN_SCRIPTS_HOME/adoacorectl.sh stop;
$ADMIN_SCRIPTS_HOME/adoacorectl.sh start; $ADMIN_SCRIPTS_HOME/adapcctl.sh start;

SELECT fa.application_short_name
,      frg.request_group_name
,      frt.responsibility_name
FROM   fnd_concurrent_programs_vl   fcpv
,      fnd_request_group_units      frgu
,      fnd_request_groups           frg
,      fnd_application              fa
,      fnd_responsibility           fr
,      fnd_responsibility_tl        frt
WHERE  1 = 1
AND    fcpv.concurrent_program_id = frgu.request_unit_id
AND    frg.request_group_id = frgu.request_group_id
AND    fa.application_id = frg.application_id
AND    frg.request_group_id = fr.request_group_id
AND    NVL(fr.end_date,SYSDATE + 1) > SYSDATE
AND    fr.responsibility_id = frt.responsibility_id
AND    fcpv.user_concurrent_program_name = 'Aged Creditors Report'
;

1. Firstly create a Custom Dicoverer Folder to return the list of values, as follows. In Discoverer Administrator, Right-click on the Business Area containing your reports-related Discoverer objects (Folders, etc.), and choose New Custom Folder…

2. Paste in the SQL statement which will reurn the required list of values. For example, and General Ledger accounting segment value:

SELECT 'All'                      DEPARTMENT
FROM   dual
UNION
SELECT ffv.flex_value             DEPARTMENT
FROM   apps.fnd_flex_value_sets   ffvs
,      apps.fnd_flex_values       ffv
WHERE  ffv.flex_value_set_id = ffvs.flex_value_set_id
AND    ffvs.flex_value_set_name = 'DEPARTMENT'
AND    ffv.enabled_flag = 'Y'
AND    ffv.summary_flag = 'N'

Click on Validate SQL to ensure that the syntax is correct, and that the database object names are valid.

3. Right-click on the new folder, choose Properties, and and assign useful values for the Name, Description and Identifier fields; say GL_DEPARTMENT_LOV.

4. Expand the folder to show the Department Item, right-click on the item, and choose New Item Class. Click Next > three times, accepting the default attribute values, then finally giving the new item class a meaningful name: say GL_DEPARTMENT_LOV. The Item Class should now be visible in the Item classes tab in Discoverer Administrator.

5. Return to the Data tab, and expand the folder on which the actual Discoverer report is based. Find the item for which this list of values is being built, right-click on it, and click Properties. In the Item class attribute, choose your new item class from the dropdown, and click Apply.

6. In Disoverer Desktop, re-connect to the database, to reflect the changes just made to the EUL, and open the Disoverer Workbook.

7. Go to Tools > Parameters > New, to create a new parameter (based on the Department field, in this example). Choosing the Department field in the For Item field means that the resulting parameter will automatically use the ist of values created above.

8. In order to provide an “All” option for the list of values (ie. to make the parameter effectively non-mandatory), you must ensure that the list-of-values SQL query contains a UNION (see above example).

9. Then, Discoverer Desktop, go to Tools > Conditions, highlight the condition created for the new Parameter, and click Edit.

10. Click Advanced >>, and then Add. Using the OR grouping, create a new condition :GL_DEPARTMENT = ‘All’. Click OK to save.