Add a Concurrent Program to a Responsibility using PL/SQL Script

Input:
l_program_name: Give concurrent program name you want to add to a responsibility               
l_responsibility_name: Give responsibility name for which you want add the above program.

Script:
DECLARE
   -- Provide concurrent program name and Responsibility name here:
   l_program_name                VARCHAR2 (240) := '<&>';
   l_responsibility_name         VARCHAR2 (240) := 'XX OKS INQUIRY';

   ---- Private variables to program ----
   l_prog_short_name             VARCHAR2 (240);
   l_prog_appl_short_name        VARCHAR2 (240);

   l_req_group_name              VARCHAR2 (240);
   l_req_group_appl_short_name   VARCHAR2 (240);
BEGIN
   -- Fetch Request group details from Responsibility
   BEGIN
      SELECT rga.application_short_name rg_appl_short_name,
             rg.request_group_name
        INTO l_req_group_appl_short_name, l_req_group_name
        FROM fnd_responsibility_vl resp,
             fnd_request_groups rg,
             fnd_application rga
       WHERE     UPPER (resp.responsibility_name) LIKE
                    UPPER (l_responsibility_name || '%')
             AND resp.group_application_id = rg.application_id
             AND resp.request_group_id = rg.request_group_id
             AND rga.application_id = rg.application_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (
            'Exception when fetching request group details:' || SQLERRM);
         RETURN;
   END;

   -- Fetch Program details from Conc program name
   BEGIN
      SELECT cp.concurrent_program_name, cpa.application_short_name
        INTO l_prog_short_name, l_prog_appl_short_name
        FROM fnd_concurrent_programs_vl cp, fnd_application cpa
       WHERE     1 = 1
             AND cp.user_concurrent_program_name LIKE l_program_name || '%'
             AND cp.application_id = cpa.application_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (
            'Exception when fetching program details:' || SQLERRM);
         RETURN;
   END;


   FND_PROGRAM.add_to_group (l_prog_short_name           -- program_short_name
                                              ,
                             l_prog_appl_short_name -- application name/ application short name
                                                   ,
                             l_req_group_name            -- Request Group Name
                                             ,
                             l_req_group_appl_short_name); --Request Group Application
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Exception :' || SQLERRM);
      ROLLBACK;
END;
/

Query to get list of parameters of a concurrent program

Input: 
Replace <&Conc Program Name> in the following query with the concurrent program name:

Query:

select
 params.column_seq_num sequence
,params.end_user_column_name parameter
,params.description
,params.enabled_flag enabled
,vs.flex_value_set_name value_set
,params.default_type
,params.default_value
,params.required_flag required
,params.security_enabled_flag enable_security
,params.range_code range
,params.display_flag display
,params.display_size
,params.maximum_description_len description_size
,params.concatenation_description_len concatenated_description_size
,params.form_left_prompt prompt
,params.srw_param token
,'N'    cd_parameter  --cp.cd_parameter
from FND_DESCR_FLEX_COL_USAGE_VL params, fnd_flex_value_sets vs, fnd_concurrent_programs_vl fcp
where 1=1
and fcp.user_concurrent_program_name like '<&Conc Program Name>'||'%'
and descriptive_flexfield_name = '$SRS$.'||fcp.concurrent_program_name
and vs.flex_value_set_id=params.flex_value_set_id(+)

Query to get RTF/XSL-FO layout template or data template file

Background: In most cases, you will not have access to UI for higher level instances and you want to check the RTF or XSL-FO layout template registed in XML Publisher responsibility.

The XML publisher layout templates are stored in the following tables:
XDO

XDO_LOBS table stores the documents uploaded to both Template defintion and Data Defintion.


Input:
LOB_TYPE--> For Data template give it as DATA_TEMPLATE, For template give it as  'TEMPLATE_SOURCE', For preview XML give 'XML_SAMPLE'
APPLICATION_SHORT_NAME --> Application_short_name
LOB_CODE--> Template code/Data Defintion code

SELECT xdo_file_type,
       file_content_type,
       file_data -- Actual content.
FROM XDO_LOBS
WHERE LOB_TYPE = '<LOB_TYPE >'
  AND APPLICATION_SHORT_NAME = '<APPLICATION_SHORT_NAME >'
  AND LOB_CODE = <LOB_CODE >



NOTE: You must use SQL Developer or Toad or any tool which supports exporting/viewing blob data.

Query to get list of responsibilities that have access to a concurrent program

Background:
Some times you know the concurrent program but you do not know how to access (do not to which responsibility it is attached). In this case, the following query can be helpful

Input:
Concurrent program name

 SELECT frv.responsibility_name,fcp.user_concurrent_program_name, frg.*
  FROM fnd_request_groups frg,
      fnd_request_group_units frgu,
      fnd_concurrent_programs_vl fcp,
      fnd_responsibility_vl frv
      WHERE frg.request_group_id    = frgu.request_group_id
    AND frg.application_id          = frgu.application_id
    AND frgu.request_unit_id        = fcp.concurrent_program_id
    AND frgu.unit_application_id    = fcp.application_id
    AND fcp.user_concurrent_program_name like '<conc program name>'||'%'
    and frv.request_group_id=frg.request_group_id

Defaulting value on a DFF Segment using sql

Here are the steps to default a value on a DFF segment:

1) Go to DFF definition screen and open the segment which you want to add/update:

2) Click on Open button, then you see following screen:

3)  For default type select “SQL Statement” and in the default value give your sql:       

4) If you want to reference other fields in the flex you can do by using :$FLEX$.segment1

Example:

Select  col1   From sk_temp xt

Where 1=1 And  :$FLEX$.segment1 = xt.col2

Use :$FLEX$.valueset name if segement1 is based on a value set

Difference between dba_* and all_* and user_* dictionary views

Difference between dba_* and all_* and user_* dictionary views

• An ALL_* view displays all the information accessible to the current user, including information from the current user's schema as well as information from objects in other schemas, if the current user has access to those objects by way of grants of privileges or roles.
• A DBA_* view displays all relevant information in the entire database. DBA_ views are intended only for administrators. They can be accessed only by users with the SELECT ANY TABLE privilege. This privilege is assigned to the DBA role when the system is initially installed.
• A USER_* view displays all the information from the schema of the current user. No special privileges are required to query these views.

PLSQL Script to Set MOAC Context

PLSQL Script to Set MOAC Context:

declare

  l_user_id fnd_user.user_id%type;

  l_resp_id fnd_responsibility.responsibility_id%type;

  l_appl_id fnd_application.application_id%type;

  l_appl_short_name fnd_application_vl.application_short_name%type;

  l_ou_value fnd_profile_option_values.profile_option_value%type;

  l_sp_value fnd_profile_option_values.profile_option_value%type;

  l_prg varchar2(3) := '000';

begin

select user_id into l_user_id

from fnd_user

where user_name = upper('&user_name');

l_prg  := '010';

select responsibility_id into l_resp_id

from fnd_responsibility_vl

where responsibility_name = ('&resp_name');

l_prg  := '020';

select application_id, application_short_name into l_appl_id, l_appl_short_name

from fnd_application_vl

where application_short_name = upper('&appl_short_name');

l_prg  := '030';

l_ou_value := fnd_profile.value_specific(

  'ORG_ID',l_user_id, l_resp_id, l_appl_id);

l_sp_value := fnd_profile.value_specific(

  'XLA_MO_SECURITY_PROFILE_LEVEL', l_user_id, l_resp_id, l_appl_id);

dbms_output.put_line('MO: Operating Unit: '||l_ou_value);

dbms_output.put_line('MO: Security Profile: '||l_sp_value);

if l_sp_value is null and l_ou_value is null then

  dbms_output.put_line('No operating unit or security profile information 

    found');

else

 l_prg  := '040';

  mo_global.set_org_access(l_ou_value, l_sp_value, l_appl_short_name);

  

end if;

exception when others then

  dbms_output.put_line('Error: '||sqlerrm);

  dbms_output.put_line('Exception at: '||l_prg );

end;

/