Query to Find Oracle Apps Module's Short Names

The following query can be used to find Application Short Name for any modules like Inventory, Order Management, Manufacturing, etc which are commonly used for uploading/downloading .ldt files, submitting a concurrent program from back end and many other purposes.

*********************************************************************************
SELECT fa.application_id    AS "Application ID",
  fat.application_name           AS "Application Name",
  fa.application_short_name  AS "Application Short Name",
  fa.basepath                          AS "Basepath"
FROM fnd_application fa,
  fnd_application_tl fat
WHERE fa.application_id                 = fat.application_id
AND fat.language                              = USERENV('LANG')
AND UPPER(fat.application_name) = UPPER('&Application_Name') /* Pass your Application Name as Parameter here */
ORDER BY fat.application_name;
/
*********************************************************************************

HAPPY LEARNING...!!!

How to Find the Responsibility to which the Concurrent Program is Attached

There will be a situation where you don't know from which responsibility to submit the concurrent program and in that case first you have find to which responsibility the concurrent program is attached so that you can submit the concurrent request from same responsibility.

The below query helps you to find the responsibility & request group details to which your concurrent program is attached.

SELECT frt.responsibility_name,
  frg.request_group_name,
  frg.description,
  frgu.request_unit_type,
  frgu.request_unit_id,
  fcpt.user_concurrent_program_name
FROM fnd_Responsibility fr,
  fnd_responsibility_tl frt,
  fnd_request_groups frg,
  fnd_request_group_units frgu,
  fnd_concurrent_programs fcp ,
  fnd_concurrent_programs_tl fcpt
WHERE frt.responsibility_id                 = fr.responsibility_id
AND frg.request_group_id                     = fr.request_group_id
AND frgu.request_group_id                   = frg.request_group_id
AND fcpt.concurrent_program_id         = frgu.request_unit_id
AND fcp.application_id                         = fcpt.application_id
AND fcp.concurrent_program_id          = fcpt.concurrent_program_id
AND frt.LANGUAGE                           = USERENV('LANG')
AND fcpt.LANGUAGE                         = USERENV('LANG')
AND UPPER(fcp.concurrent_program_name)       = UPPER(:Conc_prog_shrt_name) /*Pass Your Concurrent Program Short Name */
AND UPPER(fcpt.user_concurrent_program_name) = UPPER(:Conc_prg_name)/*Pass Your Concurrent Program Name*/
ORDER BY 1,2,3,4;
/

HAPPY LEARNING...!!!

How to Find Oracle APPs User's Password - Decrypt User Password

Here is one stop solution for haunting of your password search. To find password for specific user you have to just follow 2 simple steps as listed below :

Step-1 : Create a Package

--Package Specification
CREATE OR REPLACE PACKAGE xx_get_user_pwd
AS
  FUNCTION decrypt_pwd(
      KEY   IN VARCHAR2,
      VALUE IN VARCHAR2)
    RETURN VARCHAR2;
END xx_get_user_pwd;
/
--Package Body
CREATE OR REPLACE PACKAGE BODY xx_get_user_pwd
AS
  FUNCTION decrypt_pwd(
      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 xx_get_user_pwd;
/

Step-2 : Execute the below Query

--Query to Execute
SELECT usr.user_name,
  xx_get_user_pwd.decrypt_pwd (
  (SELECT
    (SELECT xx_get_user_pwd.decrypt_pwd (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 UPPER(usr.user_name) = UPPER('&USER_NAME');--Pass the User Name as Parameter
/

HAPPY LEARNING...!!!

Script/API to Delete Values in Value Set

In general, once you have set up and begun to use a flexfield, you should never change anything about its structure or its value sets (other than defining, enabling, and disabling values, shorthand aliases, and cross-validation and security rules). But many times in real time scenarios we need to add, modify or delete some or many values from existing value set.

Thus, below script helps you to delete required value from existing value set.

*********************************************************************

DECLARE
  l_err_msg VARCHAR2 (500) := NULL;
  CURSOR flex_values
  IS
    SELECT ffv.flex_value_id,
      ffv.flex_value
    FROM fnd_flex_value_sets ffvs,
      fnd_flex_values ffv,
      fnd_flex_values_tl ffvt
    WHERE 1                            = 1
    AND UPPER(flex_value_set_name)     = UPPER('XX_GROUP_BY_CLAUSE')--Pass Your Value Set Name
    AND ffv.flex_value_set_id          = ffvs.flex_value_set_id
    AND ffvt.flex_value_id             = ffv.flex_value_id
    AND ffvs.flex_value_set_id         = ffv.flex_value_set_id
    AND UPPER(ffvt.flex_value_meaning) = UPPER('Item Category') --Pass Your Value which you wish to delete from value set
      ;
BEGIN
  FOR i IN flex_values
  LOOP
    fnd_flex_values_pkg.delete_row (i.flex_value_id);
    COMMIT;
    DBMS_OUTPUT.put_line ('Flex Value '||'"'||i.flex_value_id ||'-'||i.flex_value|| '"'||' Deleted');
  END LOOP;
EXCEPTION
WHEN OTHERS THEN
  l_err_msg := SQLERRM;
  DBMS_OUTPUT.put_line ('In Exception ' || l_err_msg);
END;
*********************************************************************

HAPPY LEARNING…!!!