FND_PROFILE and 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);


Ur's
Amaralam

Query to find Parameters and Value Sets associated with a Concurrent Program

 Following query finds the parameters and the value sets that are associated with a Concurrent Program. Change concurrent program name (fcpl.user_concurrent_program_name, see below) according to your search criteria. In this example, my concurrent program name is "XX AR Conversion Program".

-------------------------------------------------------------------------------
-- Query to find Parameters and Value Sets associated with a Concurrent Program
-------------------------------------------------------------------------------

SELECT fcpl.user_concurrent_program_name  "Concurrent Program Name",
       fcp.concurrent_program_name        "Program Short Name",
       fdfcuv.column_seq_num              "Column Seq #",
       fdfcuv.end_user_column_name        "Parameter Name",
       fdfcuv.form_left_prompt            "Prompt Name",
       fdfcuv.enabled_flag                "Enabled Flag",
       fdfcuv.required_flag               "Required Flag",
       fdfcuv.display_flag                "Display Flag",
       fdfcuv.flex_value_set_id           "Value Set ID",
       ffvs.flex_value_set_name           "Value Set Name",
       flv.meaning                        "Default Type",
       fdfcuv.default_value               "Default Value"
  FROM fnd_concurrent_programs      fcp,
       fnd_concurrent_programs_tl   fcpl,
       fnd_descr_flex_col_usage_vl  fdfcuv,
       fnd_flex_value_sets          ffvs,
       fnd_lookup_values            flv
 WHERE fcp.concurrent_program_id          =  fcpl.concurrent_program_id
   AND fdfcuv.descriptive_flexfield_name  =  '$SRS$.' || fcp.concurrent_program_name
   AND ffvs.flex_value_set_id             =  fdfcuv.flex_value_set_id
   AND flv.lookup_type(+)                 =  'FLEX_DEFAULT_TYPE'
   AND flv.lookup_code(+)                 =  fdfcuv.default_type
   AND fcpl.LANGUAGE                      =  USERENV('LANG')
   AND flv.LANGUAGE(+)                    =  USERENV('LANG')
   AND fdfcuv.enabled_flag                =  'Y'
   AND fcpl.user_concurrent_program_name  =  'IFFCO HRMS : Costing Summary Report - Consolidated'  --
 ORDER BY fdfcuv.column_seq_num;



Ur's
AmarAlam

Query to find Oracle Alert

The following query finds all enabled custom alerts. You can comment out the very last two lines (alr.enabled_flag and alr.created_by) to display all both enabled and disabled alerts.

-------------------------------------------------------------------------------
-- Query to find Custom Oracle Alert
-------------------------------------------------------------------------------
SELECT alr.application_id,
       alr.alert_id,
       alr.alert_name,
       alr.start_date_active,
       alr.description,
       alr.sql_statement_text
  FROM alr.alr_alerts alr
 WHERE 1=1
   AND alr.created_by <> 1      -- show only custom alerts
   AND alr.enabled_flag = 'Y';  -- show only enabled alerts

Ur's
AmarAlam

Active Responsibility List with Active User Count

 SELECT
       fat.application_name         "Application Name",
       frv.responsibility_name      "Active Responsibility Name",
       COUNT(fu.user_name)          "Active User Count"
  FROM
       fnd_user                     fu,
       fnd_user_resp_groups_direct  furgd,
       fnd_responsibility_vl        frv,
       fnd_application_tl           fat
 WHERE
       1=1
   --
   AND furgd.end_date IS NULL
   --
   AND TRUNC(SYSDATE) BETWEEN
          TRUNC(furgd.start_date) AND TRUNC(NVL(furgd.end_date, SYSDATE+1))
   AND TRUNC(SYSDATE) BETWEEN
          TRUNC(frv.start_date)   AND TRUNC(NVL(frv.end_date, SYSDATE+1))
   AND TRUNC(SYSDATE) BETWEEN
          TRUNC(fu.start_date)    AND TRUNC(NVL(fu.end_date, SYSDATE+1))
   --
   AND fat.application_id       =  frv.application_id
   --
   AND furgd.responsibility_id  =  frv.responsibility_id
   AND furgd.user_id            =  fu.user_id
   --
 GROUP BY fat.application_name, frv.responsibility_name
 ORDER BY fat.application_name, frv.responsibility_name;



Ur's
AmarAlam

Query to find the first word from a string

 WITH qry
AS
(
   SELECT NULL field_name FROM dual
      UNION ALL
   SELECT 'AMAR' FROM dual
      UNION ALL
   SELECT 'AMAR ALAM' FROM dual
      UNION ALL
   SELECT 'Abdul M Faheem' FROM dual
      UNION ALL
   SELECT 'Sravan P. Kumar' FROM dual
      UNION ALL
   SELECT 'IBM India Pvt, Ltd' FROM dual
      UNION ALL
   SELECT 'IBM India Pvt, Ltd.' FROM dual
      UNION ALL
   SELECT 'IBM India Pvt Ltd' FROM dual
)
SELECT field_name,
       REGEXP_REPLACE(field_name, '^(\w+)(.*?)(\w*$)', '\1')  exp1,
       REGEXP_REPLACE(field_name, '^(\w+)(.*?)(\w*$)', '\2')  exp2,
       REGEXP_REPLACE(field_name, '^(\w+)(.*?)(\w*$)', '\3')  exp3
  FROM qry;




Ur's
AmarAlam

Delete Concurrent Program from the Back-End

If you create an Executable without creating a concurrent program, the system will allow to delete the Executable. But once you create the Concurrent Program for that Executable, the system never allows you to delete the program -- it only gives the option to disable the Concurrent Program.

At that point, your only option is to delete the Concurrent Program and its Executable from the back-end. Following is a simple straight-forward query that you can use for deleting a Concurrent Program. This query first checks if the concurrent program and its executable exist in the system. If found, it will delete the program; if not found, it will just display a message.

In this example, 'XX_TEST' is my Concurrent Program's Short Name and 'XX' is the Application Short Name. You will have to use appropriate program name and application short name according to your need.

 -------------------------------------------------------------------------------
-- delete concurrent program definition and executable from back-end
-------------------------------------------------------------------------------
-- syntax:
--     delete_program    (program_short_name, application_short_name)
--     delete_executable (program_short_name, application_short_name)
-------------------------------------------------------------------------------
DECLARE
  lv_prog_short_name    VARCHAR2(240);
  lv_appl_short_name    VARCHAR2(240);

BEGIN
   -- set the variables first
   lv_prog_short_name := 'XXAJ_IFFCO_COST_SUMARY';     -- concurrent program short name
   lv_appl_short_name := 'XXIFF';          -- application short name
 
   -- see if the program exists. if found, delete the program
   IF fnd_program.program_exists    (lv_prog_short_name, lv_appl_short_name) AND
      fnd_program.executable_exists (lv_prog_short_name, lv_appl_short_name)   
   THEN
    
      fnd_program.delete_program(lv_prog_short_name, lv_appl_short_name);
      fnd_program.delete_executable(lv_prog_short_name, lv_appl_short_name);
    
      COMMIT;
 
      DBMS_OUTPUT.PUT_LINE (lv_prog_short_name || ' deleted successfully');
 
   -- if the program does not exist in the system
   ELSE
      DBMS_OUTPUT.PUT_LINE (lv_prog_short_name || ' not found');
   END IF;
 
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE ('Error: ' || SQLERRM);
 
END;

Ur's
AmarALam