Discoverer Reporting : using SQL to generate report usage statistics

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
;

See also…

How To Find Out Who The Owner of a Workbook is and To Whom it is Shared [ID 1076907.6]

 

 

SQL Query to identify Oracle Database Locks

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
;

..or you can try the following:

SELECT NVL(s.username, '(oracle)') AS username
,      s.sid
,      s.serial#
,      s.osuser
,      s.machine
,      s.program
,      s.module
,      o.object_name
,      sq.sql_text
,      TO_CHAR(s.logon_time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   v$locked_object l
,      dba_objects     o
,      v$session       s
,      v$process       p
,      v$sql sq
WHERE  l.object_id = o.object_id
AND    l.session_id = s.sid
AND    s.paddr = p.addr
AND    s.sql_address = sq.address
;

Locking sessions can be killed using the following SQL command, using the SID and SERIAL# from the query above:

ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>' IMMEDIATE;

UNIX shell scripting : extract the value of XML-tagged data from a text file

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>

How to re-start the Apache OC4J instance following OAF extensions/personalisations

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;

Alternatively, the entire Applications tier can be stopped and re-started using the following two commands (the APPS username and password must be provided)…

$ADMIN_SCRIPTS_HOME/adstpall.sh
$ADMIN_SCRIPTS_HOME/adstrtal.sh

Note: after stopping the applications, make sure that the FNDSM (Service Manager) process has finished before re-starting:

ps -fu $LOGNAME | grep FNDSM

Which Oracle responsibility should I use to run a concurrent program?

ALTER SESSION set nls_language = 'American';

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'
;

Oracle Discoverer – creating non-mandatory parameters with lists of values (LOV’s)

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.

Profile Options Used By OA Personalization Framework (see Metalink 275876.1)

Log on using the System Administrator responsibility.

The Profile Options used by the OA Personalization Framework (see Metalink Note 275876.1), are as follows:

1. Disable Self-service Personal (FND_DISABLE_OA_CUSTOMIZATIONS)

2. FND: Personalization Region Link Enabled (FND_PERSONALIZATION_REGION_LINK_ENABLED)

3. Personalize Self-service Defn (FND_CUSTOM_OA_DEFINTION)

4. FND:OA:Enable Defaults

5. FND: Developer Mode

6. FND: Disable Partial Page Rendering

7. FND: Diagnostics (FND_DIAGNOSTICS)  to enable “About This Page” link

8. Self Service Accessibility Features (ICX_ACCESSIBILITY_FEATURES) for configurable pages

For more information about the “About” Page, refer to the Discovering Page,
Technology Stack and Session Information section in the Testing and Debugging
chapter of the Oracle Application Framework Developer’s Guide.

R12 Banks, Branches and Bank Accounts

The following query yields the bank, branch and and account numbers in Oracle eBusiness R12:

SELECT hp.party_name BANK_NAME
, hop.organization_name BANK_BRANCH_NAME
, hop.bank_or_branch_number BRANCH_SORT_CODE
, ieba.masked_bank_account_num BANK_ACCT_MASKED
, ieba.bank_account_num BANK_ACCT
FROM hz_parties hp
, hz_organization_profiles hop
, iby_ext_bank_accounts ieba
WHERE hp.party_type(+) = 'ORGANIZATION'
AND hop.party_id(+) = ieba.branch_id
AND hp.party_id(+) = ieba.bank_id
AND NVL(hp.status(+),'X') = 'A'
AND NVL(hop.effective_end_date(+),SYSDATE + 1) > SYSDATE
AND ieba.bank_account_num = '20286184'
;

And account owners…

SELECT *
FROM iby_account_owners
WHERE ext_bank_account_id = 48586
;

BI Publisher: Configuring Concurrent Programs to Publish Large Quantities of Data

When a BI-published concurrent program uses an XML Data Template  to generate large quantities of data (for example the standard Account Analysis Report [XLAAARPT], in the Subledger Accounting application), the report may fail with the following error (in the log file):

Calling XDO Data Engine...
****Warning!!! Due to high volume of data, got out of memory exception...***
****Please retry with scalable option or modify the Data template to run in scalable mode...***
********************************************************************************

The problem is caused by the standard Java Concurrent Program, XML Publisher Data Template Executable [XDODTEXE], and the memory allocated to the program’s Java Virtual Machine (JVM) which is created at runtime by Oracle Applications.

There appear to be a number of ways to tackle this problem.

The quickest way seems to be entering the text string
-Xss2048k -Xmx2048m
…to the Executable Options field in the Concurrent Program definition screen:

-Xss: sets the JVM stack size (to 2MB in the above example).

-Xmx: sets the maximum memory (“heap size”) allocated to the JVM. In above example it is set to 2GB, which is the maximum usable memory for the 32-bit JVM used by the eBusiness Suite.

The JVM’s heap size can also be set (by a DBA) at site level, although I haven’t found it necessary to do this. To see what the value is set to, run the following query:

SELECT developer_parameters
FROM   fnd_cp_services
WHERE  service_id =
(
SELECT manager_type
FROM   fnd_concurrent_queues
WHERE  concurrent_queue_name = 'FNDCPOPP'
)

It is updated using SQL, and then the Concurrent Managers must be bounced:

UPDATE fnd_cp_services
SET    developer_parameters = 'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx2048m'
WHERE  service_id =
(
SELECT manager_type
FROM   fnd_concurrent_queues
WHERE  concurrent_queue_name = 'FNDCPOPP'
)

Oracle also recommend creating a Scalable Flag for the concurrent program (see Metalink Note 737311.1), but, again, I haven’t found it necessary to do this.

The scalability option is set by performing these steps:

Resp: System Administrator
Nav: Concurrent > Program > Define

Query back the concurrent program, and add a parameter called P_SCALABLE_FLAG:

Enabled: Yes
Value Set: yes_no
Default Type: Constant
Default Value: Y
Display: No
Token: ScalableFlag (this value is case sensitive)

You can also create a parameter called P_DEBUG_FLAG so that the log file includes the following:

  • Report SQL
  • Calling XDO Data Engine and the Scalable mode on

The parameter should have the following values:

Enabled: Yes
Value Set: yes_no
Default Type: Constant
Default Value: Y
Display: No
Token: DebugFlag (this value is case sensitive)

Other memory related issues…

If the concurrent program finishes with a Warning status, and the following message appears in the log file:

+------------- 1) PUBLISH -------------+
Beginning post-processing of request 4099006 on node C-ERP-DEVORA01 at 30-MAY-2012 10:31:15.
Post-processing of request 4099006 failed at 30-MAY-2012 10:31:16 with the error message:
One or more post-processing actions failed. Consult the OPP service log for details.
+--------------------------------------+

…consult the OPP service log file which is found in $APPLCSF/$APPLLOG, and which is called something like FNDOPP107254.txt.

(The contents of the OPP service log file can also be viewed from the Applications, Resp: Systems Administrator, Nav: Concurrent > Manager > Administer, select the “Output Post Processor” and click on the Processes button, choose the Active process and click on the Manager Log button.)

If a java.lang.OutOfMemoryError appears for your concurrent request ID, configure the XML Publisher Administrator Configuration settings as follows:

Resp: XML Publisher Administrator
Nav: Home > Administration > Configuration > Properties > General > Temporary Directory

Check that the temporary directory location (/tmp, in the above example) is at least 5GB or 20 times larger than largest XML data file that you generate. You can check the available space using the UNIX command df -h, as follows:

server1$ df -h /tmp
Filesystem             size   used  avail capacity  Mounted on
swap                    34G    17M    34G     1%    /tmp

It may also be necessary to set some FO Procesing properties on this page (although I haven’t had to do this, and the settings may require the application of one or two BI-Publisher-related patches – see Metalink Note 737311.1):

Use XML Publisher’s XSLT processor = True
Enable scalable feature of XSLT processor = False
Enable XSLT runtime optimization = True

For further information, see Metalink Notes:

737311.1: “How to Configure the Account Analysis Report in Release 12 for Large Reports”.

1410160.1: “Troubleshooting Known XML Publisher and E-Business Suite (EBS)”. “Integration Issues

Using the FND_STANDARD_DATE valueset in Oracle Applications R12

Date parameters for concurrent programs in Oracle Applications R12 must be set up using the FND_STANDARD_DATE valueset.

The date value entered by the user at runtime is passed to the underlying program as a DATE-TIME STRING, not as a DATE.

This format of this VARCHAR2 data item is “YYYY/MM/DD HH24:MI:SS” (e.g. “2011/10/01 00:00:00”).

In order to use this VARCHAR2 value in a WHERE clause predicate, it can be converted to a DATE value using the fnd_date.canonical_to_date function, as shown in the following example:

SELECT user_name
FROM   fnd_user
WHERE  last_update_date >  fnd_date.canonical_to_date('2011/12/31 00:00:00')