Adding a Concurrent Program to Request Group from backend

DECLARE
  l_program_short_name  VARCHAR2 (200);
  l_program_application VARCHAR2 (200);
  l_request_group       VARCHAR2 (200);
  l_group_application   VARCHAR2 (200);
  l_check               VARCHAR2 (2);
  --
BEGIN
  --
  l_program_short_name  := 'XX_CON_PRG_SHORT_NAME';
  l_program_application := 'CONC PRG APPLI APPLION NAME';
  l_request_group       := 'XX REQUEST GROUP NAME';
  l_group_application   := 'ABOVE REQUEST GROUP APPLICATION';
  --Calling API to assign concurrent program to a reqest group
   apps.fnd_program.add_to_group (program_short_name  => l_program_short_name,
                                  program_application => l_program_application,
                                  request_group       => l_request_group,
                                  group_application   => l_group_application                         
                                 );
  --
  COMMIT;
  --
  BEGIN
    --To check whether a paramter is assigned to a Concurrent Program or not
     SELECT 'Y'
       INTO l_check
       FROM fnd_request_groups frg,
      fnd_request_group_units frgu,
      fnd_concurrent_programs fcp
      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.concurrent_program_name = 'XX_CON_PRG_SHORT_NAME';
    --
    dbms_output.put_line ('Adding Concurrent Program to Request Group Succeeded');
    --
  EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line ('Adding Concurrent Program to Request Group Failed');
  END;
END;

PL/SQL Script to Submit a Concurrent Program from backend

DECLARE
l_responsibility_id NUMBER;
l_application_id    NUMBER;
l_user_id            NUMBER;
l_request_id            NUMBER;
BEGIN
  --
  SELECT DISTINCT fr.responsibility_id, frx.application_id
  INTO l_responsibility_id,l_application_id
  FROM apps.fnd_responsibility frx,apps.fnd_responsibility_tl fr
  WHERE fr.responsibility_id = frx.responsibility_id
  AND UPPER (fr.responsibility_name) LIKE UPPER('XX RESP NAME');
  --
   SELECT user_id
   INTO l_user_id
   FROM fnd_user
   WHERE user_name = 'XXUSER_NAME';
  --
  --To set environment context.
  --
  apps.fnd_global.apps_initialize (l_user_id,l_responsibility_id,l_application_id);
  --
  --Submitting Concurrent Request
  --
  l_request_id := fnd_request.submit_request (
                            application   => 'XXCUST',
                            program       => 'XXEMP',
                            description   => 'XXTest Employee Details',
                            start_time    => sysdate,
                            sub_request   => FALSE,
argument1     => NULL
  );
  --
  COMMIT;
  --
  IF l_request_id = 0
  THEN
     dbms_output.put_line ('Concurrent request failed to submit');
  ELSE
     dbms_output.put_line('Successfully Submitted the Concurrent Request');
  END IF;
  --
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Error While Submitting Concurrent Request '||SQLCODE||'-'||sqlerrm);
END;
/

SQL Query to find Security profiles attached to an User

SELECT
    fa.application_short_name,
    fpo.profile_option_name,
    fpo.user_profile_option_name,
    fu.user_name,
    fpov.profile_option_value
FROM
    fnd_profile_options_vl      fpo,
    fnd_application             fa,
    fnd_profile_option_values   fpov,
    fnd_user                    fu
WHERE
    fpo.application_id = fa.application_id
    AND fpo.profile_option_id = fpov.profile_option_id
    AND fpo.application_id = fpov.application_id
    AND fpov.level_value = fu.user_id
    AND fu.user_name = '&1'

FND_PROFILE & FND_GLOBAL values

Following are the FND_PROFILE values that can be used in the PL/SQL code:

   fnd_profile.value('PROFILEOPTION');
   fnd_profile.value('MFG_ORGANIZATION_ID');
   fnd_profile.value('ORG_ID');
   fnd_profile.value('LOGIN_ID');
   fnd_profile.value('USER_ID');
   fnd_profile.value('USERNAME');
   fnd_profile.value('CONCURRENT_REQUEST_ID');
   fnd_profile.value('GL_SET_OF_BKS_ID');
   fnd_profile.value('SO_ORGANIZATION_ID');
   fnd_profile.value('APPL_SHRT_NAME');
   fnd_profile.value('RESP_NAME');
   fnd_profile.value('RESP_ID');

Following are the FND_GLOBAL values that can be used in the PL/SQL code:

   FND_GLOBAL.USER_ID;
   FND_GLOBAL.APPS_INTIALIZE;
   FND_GLOBAL.LOGIN_ID;
   FND_GLOBAL.CONC_LOGIN_ID;
   FND_GLOBAL.PROG_APPL_ID;
   FND_GLOBAL.CONC_PROGRAM_ID;
   FND_GLOBAL.CONC_REQUEST_ID;

For example, I almost always use the following global variable assignments in my package specification to use throughout the entire package body:

   g_user_id      PLS_INTEGER  :=  fnd_global.user_id;
   g_login_id     PLS_INTEGER  :=  fnd_global.login_id;
   g_conc_req_id  PLS_INTEGER  :=  fnd_global.conc_request_id;
   g_org_id       PLS_INTEGER  :=  fnd_profile.value('ORG_ID');
   g_sob_id       PLS_INTEGER  :=  fnd_profile.value('GL_SET_OF_BKS_ID');

And initialize the application environment as follows:

   v_resp_appl_id  := fnd_global.resp_appl_id;
   v_resp_id       := fnd_global.resp_id;
   v_user_id       := fnd_global.user_id;
   
   FND_GLOBAL.APPS_INITIALIZE(v_user_id,v_resp_id, v_resp_appl_id);

Query to find all responsibilities of a user

SELECT fu.user_name                "User Name",
       frt.responsibility_name     "Responsibility Name",
       furg.start_date             "Start Date",
       furg.end_date               "End Date",     
       fr.responsibility_key       "Responsibility Key",
       fa.application_short_name   "Application Short Name"
  FROM fnd_user_resp_groups_direct        furg,
       applsys.fnd_user                   fu,
       applsys.fnd_responsibility_tl      frt,
       applsys.fnd_responsibility         fr,
       applsys.fnd_application_tl         fat,
       applsys.fnd_application            fa
 WHERE furg.user_id             =  fu.user_id
   AND furg.responsibility_id   =  frt.responsibility_id
   AND fr.responsibility_id     =  frt.responsibility_id
   AND fa.application_id        =  fat.application_id
   AND fr.application_id        =  fat.application_id
   AND frt.language             =  USERENV('LANG')
   AND UPPER(fu.user_name)      =  UPPER('xxuser')
 ORDER BY frt.responsibility_name;

SQL Query to get Oracle Value Set definition

Query 1:

select b.flex_value_set_name,a.additional_where_clause from FND_FLEX_VALIDATION_TABLES a, fnd_flex_value_sets b
where a.application_table_name = 'MTL_SECONDARY_INVENTORIES'
and a.value_column_name ='SECONDARY_INVENTORY_NAME'
and a.flex_value_set_id = b.flex_value_set_id



Query 2:

SELECT ffvs.flex_value_set_id ,
       ffvs.flex_value_set_name ,
       ffvs.description set_description ,
       ffvs.validation_type,
       ffvt.value_column_name ,
       ffvt.meaning_column_name ,
       ffvt.id_column_name ,
       ffvt.application_table_name ,
       ffvt.additional_where_clause
FROM   APPS.fND_FLEX_VALUE_SETS FFVS ,
       APPS.FND_FLEX_VALIDATION_TABLES FFVT
WHERE  ffvs.flex_value_set_id = ffvt.flex_value_set_id
   AND ffvs.flex_value_set_name IN ('VALUE SET NAME')

How to find password of a User in Oracle Apps R12

--Package Specification

CREATE OR REPLACE PACKAGE get_pwd

AS

   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

      RETURN VARCHAR2;

END get_pwd;

/

--Package Body
CREATE OR REPLACE PACKAGE BODY get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';

END get_pwd;
/

--Query to execute
SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name = '&USER_NAME';

Example of $FLEX$ Syntax Used In Value Set

Example of $FLEX$ Syntax Used In Value Set ($flex$ in oracle apps)

Example of using “:$FLEX$.Value_Set_Name” to set up value sets where one segment depends on a prior segment that itself depends on a prior segment. Suppose you have a three-segment flexfield where the first segment is Country, the second segment is State, and the third segment is District. You could limit your third segment's values to only include Districts that are available for the Address specified in the first two segments. Your three value sets might be defined as follows: 

 

Segment Name              Country_Segment

Value Set Name             Country_Value_Set

Validation Table             Country_Table

Value Column               Country_NAME

Description Column     Country_DESCRIPTION

Hidden ID Column       Country_ID

SQL Where Clause       (none)

 

Segment Name             State_Segment

Value Set Name            State_Value_Set

Validation Table            State_Table

Value Column              State_Name

Description Column    State_DESCRIPTION

Hidden ID Column       State_ID

SQL Where Clause      WHERE Country_ID = :$FLEX$.Country_Value_Set

 

Segment Name            District_Segment

Value Set Name           District_Value_Set

Validation Table           District_Table

Value Column              District_NAME

Description Column   District_DESCRIPTION

Hidden ID Column     District_ID

SQL Where Clause     WHERE Country_ID = :$FLEX$. Country_Value_Set

                                                                               AND Country_ID = :$FLEX$.State_Value_Set 

 

In this example, Country_ID is the hidden ID column and Country_Name is the value column of the Country_Value_Set value set. The Model segment uses the hidden ID column of the previous value set, Country_Value_Set, to compare against its WHERE clause. The end user never sees the hidden ID value for this example.

 

"Example of $FLEX$ Syntax Used in Oracle Apps ($flex$ in oracle apps) "