Query to find DFF

Let's say, we need to find Descriptive Flexfield (DFF) called, "Further Job Information".
 In the following example, I am trying to get all the information for "US" context code.

The following query will display the DFF related information. You can try changing "fdfv.title" value too see different DFF.

-----------------------------------------------------------------------------

-- Query to find DFF information

-----------------------------------------------------------------------------

SELECT

       fdf.title                             "DFF Title",

       fdf.application_table_name            "Application Table",

       fdf.context_column_name               "Context Column Name",

       --

       fdfcu.descriptive_flex_context_code   "DFF Context Code",

       fdfcu.column_seq_num                  "Sequence",

       fdfcu.end_user_column_name            "Segment Name",

       fdfcu.application_column_name         "Column Name",

       --

       ffv.flex_value_set_name               "Value Set Name"

  FROM

       fnd_descr_flex_col_usage_vl   fdfcu,

       fnd_descriptive_flexs_vl      fdf,

       fnd_flex_value_sets           ffv

 WHERE

       1 = 1

   --

   AND fdf.title = 'Further Job Information'        -- <change it>

   AND fdfcu.descriptive_flex_context_code = 'US'   -- <change it>

   AND fdfcu.enabled_flag = 'Y'

   --

   AND fdfcu.flex_value_set_id = ffv.flex_value_set_id

   AND fdfcu.descriptive_flexfield_name = fdf.descriptive_flexfield_name

   AND fdfcu.application_id = fdf.application_id

   --

 ORDER BY

       fdfcu.descriptive_flexfield_name,

       fdfcu.descriptive_flex_context_code,

       fdfcu.column_seq_num;

Query to find AP Supplier Remittance Email Addresses

This following query will display all the active vendors' email addresses by their associated sites. This will work only in Oracle R12.

SELECT

       -- s.vendor_id,

       -- st.vendor_site_id,

       -- s.party_id,

       -- st.party_site_id,

       s.vendor_name              "Vendor Name",

       s.segment1                 "Vendor Number",

       s.vendor_type_lookup_code  "Vendor Type",

       st.vendor_site_code        "Vendor Site Code",

       ou.name                    "Operating Unit",

       --

       iepa.remit_advice_delivery_method  "Remittance Delivery Method",

       iepa.remit_advice_email            "Remittance Advice Email"

  FROM

       ap.ap_suppliers              s,

       ap.ap_supplier_sites_all     st,

       hr_operating_units           ou,

       iby.iby_external_payees_all  iepa

 WHERE

       1=1

   --

   -- AND s.vendor_type_lookup_code = 'EMPLOYEE'

   --

   AND TRUNC (SYSDATE) BETWEEN TRUNC (s.start_date_active) AND TRUNC (NVL(s.end_date_active, SYSDATE+1))

   AND s.enabled_flag = 'Y'

   --

   AND iepa.supplier_site_id = st.vendor_site_id

   AND iepa.payee_party_id = s.party_id

   --

   AND st.org_id = ou.organization_id

   AND st.vendor_id = s.vendor_id

   --

 ORDER BY s.vendor_name, st.vendor_site_code;

Delete concurrent program from backend

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 := 'XX_TEST';     -- concurrent program short name

   lv_appl_short_name := 'XX';          -- 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;

Query to find Custom 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.

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

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  =  'XX AR Conversion Program'  -- <change it>

 ORDER BY fdfcuv.column_seq_num;