Oracle APPS: SQL to fetch the running Concurrent Programs.

SELECT request_id ,user_concurrent_program_name,
         DECODE (phase_code,
                 'C', 'Completed',
                 'I', 'Inactive',
                 'P', 'Pending',
                 'R', 'Running') Phase,
         DECODE (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') status,
            actual_start_date,
         actual_completion_date,
         completion_text,
         argument_text,
         requestor--, cp.*
    FROM apps.fnd_conc_req_summary_v cp

Oracle Apps: SQL to find Schedule of a Concurrent Programs/Requests

SQL :

 SELECT r.request_id,
         p.user_concurrent_program_name
         || NVL2 (r.description, ' (' || r.description || ')', NULL)
            conc_prog,
         s.user_name requestor,
         r.argument_text arguments,
         r.requested_start_date next_run,
         r.last_update_date last_run,
         r.hold_flag on_hold,
         r.increment_dates,
         DECODE (c.class_type,
                 'P', 'Periodic',
                 'S', 'On Specific Days',
                 'X', 'Advanced',
                 c.class_type)
            schedule_type,
         CASE
            WHEN c.class_type = 'P'
            THEN
                  'Repeat every '
               || SUBSTR (c.class_info, 1, INSTR (c.class_info, ':') - 1)
               || DECODE (SUBSTR (c.class_info,
                                  INSTR (c.class_info,
                                         ':',
                                         1,
                                         1)
                                  + 1,
                                  1),
                          'N', ' minutes',
                          'M', ' months',
                          'H', ' hours',
                          'D', ' days')
               || DECODE (SUBSTR (c.class_info,
                                  INSTR (c.class_info,
                                         ':',
                                         1,
                                         2)
                                  + 1,
                                  1),
                          'S', ' from the start of the prior run',
                          'C', ' from the completion of the prior run')
            WHEN c.class_type = 'S'
            THEN
               NVL2 (dates.dates, 'Dates: ' || dates.dates || '. ', NULL)
               || DECODE (SUBSTR (c.class_info, 32, 1),
                          '1', 'Last day of month ')
               || DECODE (
                     SIGN (TO_NUMBER (SUBSTR (c.class_info, 33))),
                     '1',    'Days of week: '
                          || DECODE (SUBSTR (c.class_info, 33, 1), '1', 'Su ')
                          || DECODE (SUBSTR (c.class_info, 34, 1), '1', 'Mo ')
                          || DECODE (SUBSTR (c.class_info, 35, 1), '1', 'Tu ')
                          || DECODE (SUBSTR (c.class_info, 36, 1), '1', 'We ')
                          || DECODE (SUBSTR (c.class_info, 37, 1), '1', 'Th ')
                          || DECODE (SUBSTR (c.class_info, 38, 1), '1', 'Fr ')
                          || DECODE (SUBSTR (c.class_info, 39, 1), '1', 'Sa '))
         END
            AS schedule,
         c.date1 start_date,
         c.date2 end_date,
         c.class_info
    FROM fnd_concurrent_requests r,
         fnd_conc_release_classes c,
         fnd_concurrent_programs_tl p,
         fnd_user s,
         (WITH date_schedules
               AS (SELECT release_class_id,
                          RANK ()
                             OVER (PARTITION BY release_class_id ORDER BY s)
                             a,
                          s
                     FROM (SELECT c.class_info,
                                  l,
                                  c.release_class_id,
                                  DECODE (SUBSTR (c.class_info, l, 1),
                                          '1', TO_CHAR (l))
                                     s
                             FROM (    SELECT LEVEL l
                                         FROM DUAL
                                   CONNECT BY LEVEL <= 31),
                                  fnd_conc_release_classes c
                            WHERE c.class_type = 'S'
                                  AND INSTR (SUBSTR (c.class_info, 1, 31), '1') >
                                         0)
                    WHERE s IS NOT NULL)
              SELECT release_class_id,
                     SUBSTR (MAX (SYS_CONNECT_BY_PATH (s, ' ')), 2) dates
                FROM date_schedules
          START WITH a = 1
          CONNECT BY NOCYCLE PRIOR a = a - 1
            GROUP BY release_class_id) dates
   WHERE     r.phase_code = 'P'
         AND c.application_id = r.release_class_app_id
         AND c.release_class_id = r.release_class_id
         AND NVL (c.date2, SYSDATE + 1) > SYSDATE
         AND c.class_type IS NOT NULL
         AND p.concurrent_program_id = r.concurrent_program_id
         AND p.language = 'US'
         AND dates.release_class_id(+) = r.release_class_id
         AND r.requested_by = s.user_id
ORDER BY conc_prog, on_hold, next_run;

How To Configure Access To Request Output Of The Same Responsibility

In 11i there is a profile option: "Concurrent: Report Access Level" that defines who can access the output of concurrent requests. It could be set to 'User' or 'Responsibility'.  When set to Responsibility, the responsibility the report was created under must be assigned to the user who wishes to view the report.

In R12 this was replaced by Role Based Access Control. The UMX Role Based Access Control (RBAC) is to control who can view request output files.

For additional information, please see:

Oracle E-Business Suite Security Guide Release 12.2

• Chapter 2 - Access Control with Oracle User Management
• Chapter 3 - Oracle User Management Setup and Administration

Oracle E-Business Suite Setup Guide Release 12.2

• Chapter 4 - Concurrent Processing - Sections:
  • Controlling Access to Concurrent Programs with Request Security Groups
  • Controlling Access to Concurrent Programs using Role-Based Access Control (RBAC)

"Administrators can assign individual programs/sets, all programs/sets in a request group, programs/sets belonging to one or more applications, and so on, either to the
user directly or to a role that can then be assigned to one or more users."

"If applications are included in the request groups, all programs/requests sets that are created in these applications will also be automatically included. Please note that
request submission applies to both programs and request sets."

The following types of "instance sets" can be used for assignment (but administrators can create new instance sets based on their needs):

• All programs in a particular request security group
• All request sets in a particular request security group

To enable this functionality, the following are seeded:

• Permission "Submit Request"
• Permission "View Request"
• Permission Set "Request Operations" containing the permissions "Submit Request" and "View Request"
• Object "Concurrent Programs"
• Object Instance Set "Programs that can be accessed"
• Object Instance Set "Request sets that can be accessed"

To grant access to a request security group to a role, follow these steps:

1. Define your role (User Management responsibility).
2. Define your request security group (System Administrator responsibility).
3. Define your grant (Functional Administrator responsibility):

1. Enter a Name and Description (optional) for the grant.
2. Enter the Security Context for the grant.
3. Under Data Security, choose "Concurrent Programs" or "Request Sets" as the object. Click Next.
4. For the Object Data Context, select "Instance Set" for the Data Context Type.
   Choose either "Programs that can be accessed" or Request Sets that can be
   accessed" as appropriate. Click Next.
5. Review the Instance Set information. Under Instance Set Details, enter the
   request group and its application. Specifically, enter the request group name
   as Parameter 1 and the application short name as Parameter 2.
6. Choose "Request Operations" as the permission set under "Set". Click Next.
7. Review the grant information and save your work.

Note that there are two seeded grants for all users to account for request group assignments that already exist for legacy responsibilities. These are:

• Programs - Grant Defaults
• Request Sets - Grant Defaults


Viewing Requests
You can control users' access to viewing requests with RBAC.

Note: In previous releases, the Concurrent: Report Access Level profile was used to control privileges to report output files and log files generated by a concurrent program. This profile is no longer used.

Seeded "instance sets" allow administrators to grant:

• All requests submitted by a user
• All requests submitted by a user for a given application
• All requests belonging to a program submitted by a user
• All requests belonging to a request set submitted by a user (irrespective of the constituent programs' owning application) to another user (or a group of users - via a role).

System administrators can create new "instance sets" based on their needs. They can grant access to requests (of a particular program/set) submitted by all users to a specific
set of users. For example, say a given application's administrators group want to track all requests of a particular type or program submitted by business users. Then the
following approach, to grant specific programs' requests to a group of users, can be used:

1. Create an instance set that selects all the requests belonging to the particular program irrespective of which user submitted it.
For example,

&TABLE_ALIAS.request_id in
( select cr.request_id
from fnd_concurrent_requests cr, fnd_concurrent_programs cp
where cr.concurrent_program_id = cp.concurrent_program_id
and cr.program_application_id = cp.application_id
and cp.concurrent_program_name = &GRANT_ALIAS.PARAMETER1)

If you want to grant access to a set of programs instead of a single program,
'&GRANT_ALIAS.PARAMETER1' can be replaced with a subquery that returns all
the programs in a particular request group.

2. Create a grant to grant this instance set to (an existing) role, for example,
" Administrator" role, and assign the program name to grant. Use the
"Concurrent Requests" data object in creating this grant.

3. Ensure that the role is assigned to all users that should have access to these requests.

Concurrent Program Status Code & Phase Code meaning, SQL

Concurrent Program Status Code & Phase Code meaning


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

PHASE_CODE:

C - Completed
I - Inactive
P - Pending
R - Running

SQL:

  SELECT user_concurrent_program_name,
         DECODE (phase_code,
                 'C', 'Completed',
                 'I', 'Inactive',
                 'P', 'Pending',
                 'R', 'Running') Phase,
         DECODE (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') status,
            actual_start_date,
         actual_completion_date,
         completion_text,
         argument_text,
         requestor--, cp.*
    FROM fnd_conc_req_summary_v cp

Requisition Aging Reporting

SQL is here

SELECT
       r.segment1 req_num,
       r.org_id req_org_id,
       xle.legal_entity_name,
       r.creation_date rcdate,
       rl.need_by_date,
       rl.to_person_id,
       ppf.full_name requestor_name,
       rl.deliver_to_location_id,
       hrl.location_code location_code,
       hrl1.description,
       hrl.attribute2 legacy_location_code,
       p.bill_to_location_id,
       p.segment1 po_num,
       p.vendor_id,
       pov.segment1 vendor_num,
       pov.vendor_name vendor_name,
       p.creation_date pcdate,
       rsh.receipt_num,
       rsh.creation_date rshcdate,
       rsh.created_by,
       pah.action_date,
       pah.employee_id,
       ai.invoice_num,
       ai.invoice_date,
       rl.unit_price,
       rl.quantity,
       r.authorization_status
  FROM po_req_distributions_all rd,
       po_requisition_lines_all rl,
       po_requisition_headers_all r,
       hr_locations_all hrl,
       per_all_people_f ppf,
       po_headers_all p,
       po_distributions_all d,
       ap_invoice_distributions_all apid,
       ap_invoices_all ai,
       rcv_shipment_lines rsl,
       rcv_shipment_headers rsh,
       po_action_history pah,
       po_vendors pov,
       xle_le_ou_ledger_v xle,
       gl_code_combinations_kfv gcc,
       hr_locations_all hrl1
WHERE     r.requisition_header_id = rl.requisition_header_id
       AND rl.requisition_line_id = rd.requisition_line_id
       AND rl.deliver_to_location_id = hrl.location_id
       AND rl.to_person_id = ppf.person_id
       AND ppf.effective_start_date < SYSDATE
       AND ppf.effective_end_date > SYSDATE
       AND rd.distribution_id = d.req_distribution_id(+)
       AND d.po_header_id = p.po_header_id(+)
       AND d.po_distribution_id = apid.po_distribution_id(+)
       AND apid.invoice_id = ai.invoice_id(+)
       AND p.po_header_id = rsl.po_header_id(+)
       AND rsl.shipment_header_id = rsh.shipment_header_id(+)
       AND r.requisition_header_id = pah.object_id(+)
       AND pah.object_type_code = 'REQUISITION'
       AND p.vendor_id = pov.vendor_id(+)
       AND ai.legal_entity_id = xle.legal_entity_id(+)
       AND ai.org_id = xle.operating_unit_id(+)
       AND rd.code_combination_id = gcc.code_combination_id
       AND p.bill_to_location_id = hrl1.location_id(+)

Oracle EBS: Repairing the "XXX is not a valid responsibility for the current user" error in Oracle

This Knol covers how to "fix" a problem that can occur in Oracle when you have granted a user access to a new web-based responsibility but the middle-tier application servers have not picked up this change.

Below are detailed instructions on how to clear the cache on the middle-tier application server(s). As it says in the warning when you try and do it there will be a performance hit while it re-reads all the data from the database - use on Production Systems at you own risk!!

At the moment I'm currently configuring Oracle Internet Expenses (11, not 12) and several times we've granted a user the "Internet Expenses" responsibility, they've logged into Oracle, selected Internet Expenses and then received an error along the lines of "Internet Expenses is not a valid responsibility for the current user. Please contact your System Administrator". For example when trying to access "Function Administrator" privilege you get the message:

 

Figure 1: Sample error for "Functional Administrator" Responsibility


You only get this issue with Web-based responsibilities. If I'd assigned "Payables Manager" then it works without any issues, the reason for this error is that in order to improve performance Oracle caches some information on the web server. In order to "fix" this problem we need to clear the cache by following these steps;

Step 1: Log in and select the "Functional Administrator" responsibility

Now this is where we get a delicious taste of irony; this responsibility is web-based so if you are trying to fix a problem that's occurring now and you don't already have this responsibility then I'm afraid you're too late. You'll have to bounce the Apache server (something that will require a DBA). In short; you need to have granted yourself this responsibility BEFORE you run into problems!

Figure 2: "Functional Administrator" Welcome Screen

Step 2: Select "Core Services" (the tab at the top right)

Figure 3: "Function Administrator" > "Core Services"

Step 3: Select "Caching Framework" (second option from the right on blue bar)

 

Figure 4: "Core Services" > "Caching Framework"

Step 4: Select "Global Configuration" (bottom option on the left)

Figure 5: "Caching Framework" > "Global Configuration"

This page shows you the currently configured Caching Statistics and Policy. The bit we're interested in though is the "Clear All Cache" button the right-hand side.

Step 5: Click "Clear All Cache"

Figure 6: Clear Cache Warning Message

Read the message, it's there for a reason!

Step 6: Click "Yes"

Figure 7: Confirmation Message

And we're done, the user should now be able to log in with the new responsibility.

BI Publisher issue ends with java.lang.outOfMemory error

BI Publisher issue ends with java.lang.outOfMemory error  tmp file keeps extending upto around 30 MB even though program end with warning before than. i.e. tmp file keep extending after java.lang.outofmemory error.

  

Solution:

1.       Configure the Output Post Processor's JVM. These steps set the JVM to 2GB, depending upon

your server's size you might find 3 GB (-mx3072m), 4GB (-mx4096m) or even 5GB (-mx5120m) is a better value. This setting prevents the error "java.lang.OutOfMemoryError: Java heap space" in the Output Post Processor's log associated to the Subledger Accounting Program.

Login to SQL*Plus as APPS.

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

Bounce the concurrent managers

2.       Increase the number of Output Post Processor as follows:

Logon to Applications with "System Administrator" responsibility 

Navigate to Concurrent -> Manager -> Define

Query for "Output Post Processor" 

Click on "Work Shifts" and Increase the number of processes. 

(If you have 2 processes then make them 4).

3.       1. Go to the XML Publisher Administrator responsibility.

2. Go to the Administration TAB

3. Set the three fields in the Admin page:

Under General -

Temporary directory : set the temporary directory to a large existing writable directory on the concurrent manager tier : Directory should have atleast 20 Gb free space. Set that directory name as temporary directory.

Under FO Processing -

Use XML Publisher's XSLT processor - True

Enable scalable feature of XSLT processor - NULL

Enable XSLT runtime optimization - True

4. Navigate to System Administrator => Profile => System => Query for the below profile and set the value at site level which is enough to complete the requests. (Input is in seconds)

Concurrent:OPP Process Timeout

5. Bounce the concurrent managers

4.       check the OPP Timeout must be set greater than 30min long (to include larger

XML data)

"Concurrent:OPP Process Timeout"

"Concurrent:OPP Response Timeout".

Change the both the values to big values like 10800.

Normally they would be in hundreds.

Please change them at site level. Then only they will reflect

References:

 BUG 8905212 - XML REPORT ENDS WITH JAVA.LANG.OUTOFMEMORY ERROR

XML/BI Publisher Reports Generating "java.lang.OutOfMemoryError" and OPP Logfile Shows "Check that the heap size per Output Post-processor service process is Sufficient" After Submitting Many Jobs [ID 371303.1]

Output Post Processor Fails Due To java.lang.OutOfMemoryError: ZIP002:OutOfMemoryError, MEM_ERROR in deflate_init2

How to find the current SQL

Run following Sql,

SELECT S.USERNAME, S.SID, S.SERIAL#, SQL_TEXT FROM V$SESSION S, V$SQLTEXT_WITH_NEWLINES T WHERE S.SQL_ID IS NOT NULL AND S.SQL_ID = T.SQL_ID ORDER BY S.SID,T.PIECE;

or

SELECT sql_text FROM v$sql WHERE sql_id IN (SELECT sql_id FROM v$session);

General Unix .profile settings

FORMS_PATH=$ORACLE_HOME/forms:$ORACLE_HOME/forms/webutil:$FORMS_PATH;
export FORMS_PATH
CLASSPATH=$ORACLE_HOME/jre:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$CLASSPATH;

export CLASSPATH
export PS1="[\$LOGNAME@$(hostname) \$PWD]$ "
export TERM=vt100
export EDITOR=/usr/bin/vi
export HISTSIZE=12800
stty erase ^?
alias c=clear
set -o vi
echo "Choose Environment: 1-for R1 2-for R2"
read c
if [ "$c" -eq 1 ]; then
. /orasoft01/product/app//apps/apps_st/appl/.env
else
. /orasoft01/product/app//apps/apps_st/appl/.env
fi