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;

/

Apps Initialization script R12

Introduction:

Many times before executing standard APIs or querying from org stripped views, you need to initialize apps context.


Script:

set serveroutput on;
declare
l_user_name varchar2(240) := 'SERU'; -- Replace this with Oracle Apps user name
l_resp_name varchar2(240) := 'INV';   -- Responsibility Name
l_prod_short_name varchar2(240) := 'INV';  -- Product Short Name
l_resp_appl_id number;
l_user_id number;
l_resp_id number;
begin

-- Get the user id
begin
select user_id
 into l_user_id
 from fnd_user
where user_name=l_user_name;
exception
 when no_data_found then
  dbms_output.put_line('user '|| l_user_name ||' not found');
  return;
 when others then
 dbms_output.put_line('exception :'||sqlerrm);
 return;
end;

-- Get the resp id and resp application id
begin
select application_id, Responsibility_id
into l_resp_appl_id, l_resp_id
from fnd_responsibility_vl
where responsibility_name like l_resp_name||'%';
exception
 when no_data_found then
  dbms_output.put_line('Responsibility '|| l_resp_name ||' not found');
  return;
when others then
 dbms_output.put_line('exception :'||sqlerrm);
 return;
end;


-- call apps init
FND_GLOBAL.APPS_INITIALIZE(USER_ID => l_user_id,RESP_ID => l_resp_id,RESP_APPL_ID=>l_resp_appl_id);

-- set mo global context.
--mo_global.set_org_context(P_ORG_ID_CHAR=> '',P_SP_ID_CHAR=> null,P_APPL_SHORT_NAME=>'');
mo_global.set_org_context(l_prod_short_name);
--mo_global.set_policy_context ('S', v_org_id);

end;

/

Introduction to Oracle apps contracts

Introduction:
Nowa a days everything runs on contract. In indvidauls life examples are opening a bank account, credit card, taking loan, internet connection etc.. In business when you are purchasing or selling goods, they will have contracts with suppliers/customers.

So what is a contract?
Contract is made from set of clauses that govern a business transaction or set of transactions. Business transaction can be a RFQ,Purchase order, Blanket Purchase Agreement, Sales Order  or Sales Agreement or Sales Quote etc.

Clauses are also called articles or terms and conditions.

Coming to Oracle apps contract solution, it has following offers:

1) Oracle Contracts Core
    Oracle contracts core forms the technical foundation for other contract modules.
    Coming to the functional offering, It offers Library management (Articles, Contract Expert, Templates),  Repository contracts. - Mainly drafting part.
    Contracts Core library is the foundation for procurement contract and service contracts. (Execution or applying above terms on the document)
    We will discuss more in the future posts.

   This product is mainly deals with authoring and little of transaction processing. Not a convention ERP product.

2) Oracle Service Contracts
     Using this product, you can create Service Agreements, Subscription Agreement and Warranty and Extended warrenty contracts. Very powerful product can be useful across many industries.


3) Oracle Project Contracts
     This prodcut mainly used in Aerospace and defenese companies. Integrates with Projects.
      Plan and author in Project contracts and do accoutning(costing/revenue) part in Projects.

Script to Open Inventory periods till current month for all organizations

Intro:

Open Inventory accounting periods till current month for all the organizations.

This is useful when working in lower instances (non production instances) where the data or system is not maintained properly.


Script:

-- Oracle Applications Contracts blog ---
DECLARE
   l_organization_id             NUMBER ;
   l_period_set_name             VARCHAR2 (50);
   l_accounted_period_type       VARCHAR2 (50);
   l_last_scheduled_close_date   DATE;
   l_prior_period_open           BOOLEAN;
   l_new_acct_period_id          NUMBER;
   l_duplicate_open_period       BOOLEAN;
   l_commit_complete             BOOLEAN := TRUE;
   l_return_status               VARCHAR2 (1);
   l_last_period_end_date        DATE;

   CURSOR acct_period_list (
      org_id                        IN NUMBER,
      periodsetname                 IN VARCHAR2,
      acctperiodtype                IN VARCHAR2,
      l_last_scheduled_close_date   IN DATE)
   IS
        SELECT glp.PERIOD_SET_NAME,
               glp.PERIOD_NAME,
               glp.START_DATE,
               glp.END_DATE,
               glp.PERIOD_TYPE ACCOUNTED_PERIOD_TYPE,
               glp.PERIOD_YEAR,
               glp.PERIOD_NUM PERIOD_NUMBER
          FROM GL_PERIODS glp
         WHERE     glp.ADJUSTMENT_PERIOD_FLAG = 'N'
               AND glp.period_type = acctperiodtype
               AND glp.PERIOD_SET_NAME = periodsetname
               AND glp.PERIOD_NAME NOT IN (SELECT OAP.PERIOD_NAME
                                             FROM ORG_ACCT_PERIODS OAP
                                            WHERE     OAP.PERIOD_SET_NAME =
                                                         glp.PERIOD_SET_NAME
                                                  AND OAP.PERIOD_NAME =
                                                         glp.PERIOD_NAME
                                                  AND OAP.organization_id =
                                                         org_id)
               AND glp.end_date >= l_last_scheduled_close_date
               AND glp.end_date <= LAST_DAY (SYSDATE)
      ORDER BY glp.start_date ASC;

   CURSOR cur_inv_org
   IS
        SELECT organization_id, organization_code
          FROM mtl_parameters
      ORDER BY organization_code;
BEGIN
   FOR inv_rec IN cur_inv_org
   LOOP
      l_organization_id := inv_rec.organization_id;
      DBMS_OUTPUT.put_line ('Processing org ' || inv_rec.organization_code);

      SELECT glsob.period_set_name, accounted_period_type
        INTO l_period_set_name, l_accounted_period_type
        FROM cst_organization_definitions cod, gl_sets_of_books glsob
       WHERE     glsob.set_of_books_id = cod.set_of_books_id
             AND cod.organization_id = l_organization_id;

      SELECT MAX (schedule_close_date)
        INTO l_last_scheduled_close_date
        FROM org_acct_periods
       WHERE organization_id = l_organization_id;

      DBMS_OUTPUT.put_line ('l_period_set_name : ' || l_period_set_name);
      DBMS_OUTPUT.put_line (
         'l_accounted_period_type : ' || l_accounted_period_type);
      DBMS_OUTPUT.put_line (
         'l_last_scheduled_close_date : ' || l_last_scheduled_close_date);


      FOR per_rec IN acct_period_list (l_organization_id,
                                       l_period_set_name,
                                       l_accounted_period_type,
                                       l_last_scheduled_close_date)
      LOOP


         CST_AccountingPeriod_PUB.open_period (
            p_api_version                 => 1.0,
            p_org_id                      => l_organization_id,
            p_user_id                     => -1,
            p_login_id                    => -1,
            p_acct_period_type            => per_rec.ACCOUNTED_PERIOD_TYPE,
            p_org_period_set_name         => per_rec.PERIOD_SET_NAME,
            p_open_period_name            => per_rec.PERIOD_NAME,
            p_open_period_year            => per_rec.PERIOD_YEAR,
            p_open_period_num             => per_rec.PERIOD_NUMBER,
            x_last_scheduled_close_date   => l_last_scheduled_close_date,
            p_period_end_date             => per_rec.end_date,
            x_prior_period_open           => l_prior_period_open,
            x_new_acct_period_id          => l_new_acct_period_id,
            x_duplicate_open_period       => l_duplicate_open_period,
            x_commit_complete             => l_commit_complete,
            x_return_status               => l_return_status);

         IF l_return_status <> FND_API.g_ret_sts_success
         THEN
            DBMS_OUTPUT.put_line ('Error while opening period ');
         ELSE
            --DBMS_OUTPUT.put_line ('Period(s) Opened Successfully');
            COMMIT;
         END IF;
      END LOOP;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);

END;