SQL Query to Find Oracle Web ADI Importer Package Procedure Name

 SELECT ba.attribute2     wed_adi_package_procedure_name
  FROM apps.bne_attributes      ba,
       apps.bne_param_lists_b   bplb,
       apps.bne_interfaces_b    bib,
       apps.bne_integrators_tl  bit
 WHERE     bib.upload_param_list_code = bplb.param_list_code
       AND bib.integrator_code = bit.integrator_code
       AND ba.attribute_code = bplb.attribute_code
       AND bit.user_name = :Integrator_name
       AND bit.language = 'US';

Query to list the Oracle RICEW Objects

Pass the application short name &APP_SHORT_NAME parameter to find RICE objects for the particular application and include any exclusion characters in parameter &EXCLUSION_CHARACTERS :

SELECT 'Concurrent Program'                AS "Object Type",

       cp.user_concurrent_program_name     AS "RICE Name",

       cp.description,

       flv.meaning                         EXECUTION_TYPE,

       exe.executable_name                 Executable_Name,

       exe.execution_file_name             Execution_File_Name,

       appl.application_name

  FROM apps.fnd_application_vl          appl,

       apps.fnd_concurrent_programs_vl  cp,

       apps.fnd_executables             exe,

       apps.fnd_lookup_values_vl        flv

 WHERE     1 = 1

       AND appl.application_id = cp.application_id

       AND UPPER (cp.user_concurrent_program_name) LIKE '%&APP_SHORT_NAME%'

       AND UPPER (cp.user_concurrent_program_name) NOT LIKE '%&EXCLUSION_CHARACTERS%'

       AND cp.enabled_flag = 'Y'

       AND cp.executable_id = exe.executable_id

       AND exe.execution_method_code = flv.lookup_code

       AND flv.lookup_type = 'CP_EXECUTION_METHOD_CODE'

UNION ALL

SELECT 'Alert'             AS "Object Type",

       alr.alert_name      AS "RICE Name",

       alr.description     AS "Description",

       NULL                EXECUTION_TYPE,

       NULL                Executable_Name,

       NULL                Execution_File_Name,

       appl.application_name

  FROM apps.fnd_application_vl appl, apps.alr_alerts alr

 WHERE     1 = 1

       AND appl.application_id = alr.application_id

       AND UPPER (alr.alert_name) LIKE '%&APP_SHORT_NAME%'

       AND alr.enabled_flag = 'Y'

       AND SYSDATE BETWEEN NVL (alr.start_date_active, SYSDATE - 1)

                       AND NVL (end_date_active, SYSDATE + 1)

UNION ALL

SELECT 'Form'                      AS "Object Type",

       frm.user_form_name          AS "RICE Name",

       frm.description             AS "Description",

       NULL                        EXECUTION_TYPE,

       ffv.function_name           Executable_Name,

       frm.form_name || '.fmb'     Execution_File_Name,

       appl.application_name

  FROM apps.fnd_application_vl     appl,

       apps.fnd_form_vl            frm,

       apps.fnd_form_functions_vl  ffv

 WHERE     1 = 1

       AND appl.application_id = frm.application_id

       AND appl.application_short_name = '&APP_SHORT_NAME'

       AND UPPER (frm.form_name) LIKE '%&APP_SHORT_NAME%'

       AND frm.form_id = ffv.form_id

UNION ALL

SELECT 'Web ADI'                AS "Object Type",

       intg.user_name           AS "RICE Name",

       NULL                     AS "Description",

       NULL                     EXECUTION_TYPE,

       intg.integrator_code     Executable_Name,

       NULL                     Execution_File_Name,

       appl.application_name

  FROM apps.fnd_application_vl  appl,

       apps.bne_integrators_vl  intg,

       apps.bne_interfaces_vl   intf,

       apps.bne_param_lists_vl  pl,

       apps.bne_attributes      att

 WHERE     1 = 1

       AND appl.application_id = intg.application_id

       AND intg.enabled_flag = 'Y'

       AND UPPER (intg.integrator_code) LIKE '&APP_SHORT_NAME'||'%'

       AND intg.integrator_code = intf.integrator_code(+)

       AND intf.upload_param_list_code = pl.param_list_code(+)

       AND pl.attribute_code = att.attribute_code(+)

UNION ALL

SELECT 'Workflow'              AS "Object Type",

       wfit.display_name       AS "RICE Name",

       wfit.description        AS "Description",

       NULL                    EXECUTION_TYPE,

       wfit.Name               Executable_Name,

       wfit.Name || '.wft'     Execution_File_Name,

       appl.application_name

  FROM apps.fnd_application_vl appl, apps.wf_item_types_vl wfit

 WHERE     1 = 1

       AND wfit.name LIKE

                  DECODE (appl.application_short_name,

                          'OFA', 'FA',

                          appl.application_short_name)

               || '%'

       AND appl.application_name NOT LIKE '%Obsolete%'

       AND appl.application_name NOT IN

               ('Sourcing', 'iSupplier Portal', '&APP_SHORT_NAME'||'_AP_APPS')

UNION ALL

SELECT 'AOL Forms Custom Rules'                       AS "Object Type",

       ffv.user_function_name                         AS "RICE Name",

       FFCR.SEQUENCE || ' - ' || FFCR.DESCRIPTION     AS "Description",

       'Form Personalization'                         EXECUTION_TYPE,

       NULL                                           Executable_Name,

       FFCR.FORM_NAME                                 Execution_File_Name,

       fav.application_name

  FROM apps.fnd_form_custom_rules  ffcr,

       apps.fnd_form_vl            ff,

       apps.fnd_application_vl     fav,

       apps.fnd_form_functions_vl  ffv

 WHERE     ffcr.form_name = ff.form_name

       AND ff.application_id = fav.application_id

       AND ffcr.function_name = ffv.function_name

       AND ffcr.enabled = 'Y'

       AND (   UPPER (ffcr.description) LIKE '%&APP_SHORT_NAME%'

            OR ffcr.form_name LIKE '%&APP_SHORT_NAME%'

            OR ffcr.function_name LIKE'%&APP_SHORT_NAME%')

       AND ffcr.created_by NOT IN (122, 0)

SQL Query to Find the Concurrent Request Session Id

 SELECT sess.sid,

       sess.serial#,

       fcr.request_id,

       fcpt.user_concurrent_program_name,

       fcr.requested_start_date,

       fcr.phase_code,

       fcr.status_code

  FROM apps.fnd_concurrent_requests     fcr,

               v$session   sess,

               apps.fnd_concurrent_programs_tl  fcpt

 WHERE     fcr.request_id = &request_id

       AND fcr.phase_code = 'R'

       AND fcr.status_code = 'R'

       AND fcr.oracle_session_id = sess.audsid(+)

       AND fcr.concurrent_program_id = fcpt.concurrent_program_id

Query to find the EBS Instance Refresh/Clone Date

SELECT name, creation_date FROM apps.fnd_apps_system;

SELECT resetlogs_time FROM v$database; 

Query to find the Oracle EBS Application Version

 SELECT RELEASE_NAME FROM apps.FND_PRODUCT_GROUPS

Find a file under specific directories in UNIX

Use below command to find Filename under sub directories of $AP_TOP

find /$AP_TOP -name "Filename*" -print

 

Unix commands to get the count of files

 ls -l *.rdf | wc -l

SQL Query to find spaces in a data column

select *from (select 'testname@email.com ,noname@gmail.com  ' email from dual) where regexp_like(email,'(^ | $)')

How to search for a text from files in a directory on Linux

 find -type f -name "*.xml" -exec grep -l 'searchtext' {} +

Use the above command to find all files (in this case xnl) containing specific text 'searchtext' on Linux

How to test read/write permissions on UTL directory (in UNIX) from database or backend

Below is the sample script which can be run from TOAD/SQL*PLUS to test the read write permissions on UTL directory from database or backend

DECLARE
  l_file utl_file.file_type;
BEGIN
  l_file := utl_file.fopen( 'DBA_UTL_DIR_NAME', 'test_file_name.txt', 'W' );
  utl_file.put_line( l_file, 'Here is sample text' );
  utl_file.fclose( l_file );
END;

Direcory name (DBA_UTL_DIR_NAME) should exists in DBA_DIRECTORIES table  

Make sure that the UTL_FILE directory path exists in UNIX


select * from DBA_DIRECTORIES where directory_name = 'DBA_UTL_DIR_NAME'

About this Page Personalization Profile Option Values

Set the values of following profiles to enable Personalization Page link in OAF Pages

• FND: Personalization Region Link Enabled    Yes
• Personalize Self-Service Defn                    Yes
• Disable Self-Service Personal                    No

Set FORMS_PATH in Oracle R12

When you face below errors while compiling the custom form:
identifier 'APP_WINDOW.CLOSE_FIRST_WINDOW' must be declared
Bad bind variable parameter.G_query_find

Run the below command to set the FORMS_PATH:
export FORMS_PATH=$AU_TOP/resource:$AU_TOP/forms/US:$AU_TOP/resource/US

Compile the form using the below command:
frmcmp_batch userid=apps/pwd module=$XX_TOP/forms/US/XXFORMfmb output_file=$XX_TOP/forms/US/XXFORM.fmx module_type=form batch=no compile_all=special

Employee Supervisor Hierarchy Query Oracle R12

SELECT   e.*
      FROM (SELECT DISTINCT
      papf.employee_number,
                            papf.full_name "EMPLOYEE_FULL_NAME",
                            papf1.employee_number "SUPERVISOR_EMP_NUMBER",
                            papf1.full_name "SUPERVISOR_FULL_NAME",
       papf.person_id,
                            paaf.supervisor_id
                       FROM apps.per_all_people_f papf,
                            apps.per_all_assignments_f paaf,
                            apps.per_all_people_f papf1,
                            apps.per_person_types ppt
                      WHERE papf.person_id = paaf.person_id
                        AND papf1.person_id = paaf.supervisor_id
                        AND papf.business_group_id = paaf.business_group_id
                        AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                                AND papf.effective_end_date
                            AND TRUNC (SYSDATE) BETWEEN papf1.effective_start_date
                                                AND papf1.effective_end_date                     
                        AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
                                                AND paaf.effective_end_date
                        AND ppt.person_type_id = papf.person_type_id
                        AND ppt.user_person_type <> 'Ex-employee') e
CONNECT BY PRIOR person_id = supervisor_id
START WITH person_id = :Person_id ; -- List the person id to know who all report under him like Manager id or person id of VP

Query to find Concurrent Requests Ran count by Template/Layout Name for Each Month

SELECT user_concurrent_program_name program_name,
         xtt.template_name,
         hou.name operating_unit,
         TO_CHAR (actual_start_date, 'MON-YYYY') program_ran_month,
         --fu.user_name,
         --frt.responsibility_name,
         COUNT (1) COUNT
    FROM apps.fnd_concurrent_programs_tl fcpt,
         apps.fnd_concurrent_programs fcp,
         apps.fnd_concurrent_requests fcr,
         apps.fnd_user fu,
         apps.fnd_responsibility_tl frt,
         apps.hr_operating_units hou,
         apps.fnd_conc_pp_actions fcpa,
         apps.xdo_templates_b xtb,
         apps.xdo_templates_tl xtt
   WHERE     1 = 1
         AND fcp.concurrent_Program_id = fcpt.concurrent_program_id
         AND fcp.concurrent_program_id = fcr.concurrent_program_id(+)
         AND fu.user_id = fcr.requested_by
         AND frt.responsibility_id = fcr.RESPONSIBILITY_ID
         AND fcr.org_id = hou.organization_id
         AND fcr.request_id = fcpa.concurrent_request_id
         AND xtb.template_code = fcpa.ARGUMENT2
         AND xtb.template_code = xtt.template_code
         AND user_concurrent_program_name LIKE 'XX Conc Program Name%'
GROUP BY user_concurrent_program_name,
         hou.name,
         TO_CHAR (actual_start_date, 'MON-YYYY'),
         xtt.template_name
ORDER BY TO_DATE (TO_CHAR (actual_start_date, 'MON-YYYY'), 'MON-YYYY') DESC

Oracle R12 SQL Query for Supplier Addresses on the Address Book Suppliers Entry/Update Screen

SELECT DISTINCT pv.segment1 vendor_number,
                  pv.vendor_name,
                  hps.party_site_name address_name,
                  hou.name operating_unit,
                  hcp2.email_address,
                  DECODE (hps.status,  'A', 'Active',  'I', 'Inactive') status
    FROM apps.po_vendors pv,
         apps.po_vendor_sites_all pvsa,
         apps.hr_operating_units hou,
         apps.hz_contact_points hcp2,
         apps.hz_party_sites hps
   WHERE     1 = 1
         AND pv.vendor_id = pvsa.vendor_id
         AND pvsa.org_id = hou.organization_id
         AND hcp2.owner_table_id(+) = pvsa.party_site_id
         AND hcp2.contact_point_type(+) = 'EMAIL'
         AND hcp2.status(+) = 'A'
         AND hcp2.owner_table_name(+) = 'HZ_PARTY_SITES'
         AND pvsa.party_site_id = hps.party_site_id
         AND (pv.end_date_active IS NULL OR pv.end_date_active > SYSDATE)
ORDER BY pv.segment1

Oracle PA (Projects) Expenditure Details Interfaced from OTL SQL Query

SELECT p.segment1 project_number,
       p.name project_name,
       pt.project_type,
       pt.project_type_class_code,
       p.project_id,
       ei.task_id,
       t.task_number,
       t.task_name,
       ei.expenditure_item_date,
       ei.expenditure_type,
       ei.quantity,
       DECODE (ei.unit_of_measure,
               NULL, pa_utils4.get_unit_of_measure (ei.expenditure_type),
               ei.unit_of_measure)
          unit_of_measure,
       ei.burden_cost,
       (SELECT expenditure_category
          FROM pa_expenditure_types
         WHERE expenditure_type = ei.expenditure_type)
          expenditure_category,
       (SELECT revenue_category_code
          FROM pa_expenditure_types
         WHERE expenditure_type = ei.expenditure_type)
          revenue_category_code,
       x.incurred_by_person_id,
       (SELECT p.full_name
          FROM per_all_people_f p
         WHERE     p.person_id = x.incurred_by_person_id
               AND ei.expenditure_item_date BETWEEN p.effective_start_date
                                                AND p.effective_end_date)
          employee_name,
          (SELECT u.user_name
          FROM fnd_user u
         WHERE     u.employee_id = x.incurred_by_person_id) Username
  FROM pa_projects_all p,
       pa_tasks t,
       pa_expenditure_items_all ei,
       pa_expenditures_all x,
       pa_project_types_all pt,
       hr_all_organization_units_tl haot
 WHERE     t.project_id = p.project_id
       AND ei.project_id = p.project_id
       AND p.project_type = pt.project_type
       AND p.org_id = pt.org_id
       AND ei.task_id = t.task_id
       AND ei.expenditure_id = x.expenditure_id
       AND NVL (ei.override_to_organization_id,
                x.incurred_by_organization_id) = haot.organization_id
       AND haot.language = USERENV ('LANG')     
      AND x.incurred_by_person_id = :X_PERSON_ID -- Person Id or Resource Id of the employee for which time is entered
       AND ei.expenditure_item_date BETWEEN :Week_Start_Date AND :Week_End_Date

Oracle R12 Suppliers Contact Information SQL Query

SELECT DISTINCT asu.segment1 vendor_number,
                  asu.vendor_name,
                  asu.start_date_active vendor_start_date_active,
                  asu.end_date_active vendor_end_date_active,
                  assa.vendor_site_code,
                  assa.inactive_date vendor_site_inactive_date,
                  hou.name Operating_Unit_Name,
                  assa.address_line1,
                  assa.city,
                  assa.state,
                  assa.zip,
                  hpc.party_name Contact_Name,
                  hpr.primary_phone_country_code Contact_phone_country_code,
                  hpr.primary_phone_area_code Contact_phone_area,
                  hpr.primary_phone_number Contact_phone_number,
                  hpr.email_address Contact_email_address,
                  hpcp.status contact_status
    FROM ap_suppliers asu,
         ap_supplier_sites_all assa,
         hz_relationships hr,
         ap_supplier_contacts asco,
         hz_org_contacts hoc,
         hz_parties hpc,
         hz_parties hpr,
         hz_contact_points hpcp,
         hr_operating_units hou
   WHERE     1 = 1
         AND asu.vendor_id = assa.vendor_id
         AND assa.org_id = hou.organization_id
         AND assa.party_site_id = asco.org_party_site_id(+)
         AND asco.relationship_id = hoc.party_relationship_id(+)
         AND hoc.party_relationship_id = hr.relationship_id(+)
         AND asu.party_id = hr.subject_id(+)
         AND hr.relationship_code(+) = 'CONTACT'
         AND hr.object_table_name(+) = 'HZ_PARTIES'
         AND hr.object_id = hpc.party_id(+)
         AND hr.party_id = hpr.party_id(+)
         AND hpr.party_type(+) = 'PARTY_RELATIONSHIP'
         AND hpr.party_id = hpcp.owner_table_id(+)
         AND hpcp.owner_table_name(+) = 'HZ_PARTIES'
ORDER BY asu.segment1

Query to find responsibilities attached to a form function

SELECT DISTINCT responsibility_id, responsibility_name
  FROM apps.fnd_responsibility_vl a
 WHERE     a.end_date IS NULL
       AND a.menu_id IN
              (    SELECT menu_id
                     FROM apps.fnd_menu_entries_vl
               START WITH menu_id IN
                             (SELECT menu_id
                                FROM apps.fnd_menu_entries_vl
                               WHERE function_id IN
                                        (SELECT function_id
                                           FROM apps.fnd_form_functions_vl a
                                          WHERE (function_name = :pc_function_name OR USER_FUNCTION_NAME = :pc_function_name )))
               CONNECT BY PRIOR menu_id = sub_menu_id)
       AND a.responsibility_id NOT IN
              (SELECT responsibility_id
                 FROM apps.fnd_responsibility_vl
                WHERE responsibility_id IN
                         (SELECT responsibility_id
                            FROM applsys.fnd_resp_functions resp
                           WHERE action_id IN
                                    (SELECT function_id
                                       FROM apps.fnd_form_functions_vl a
                                      WHERE (function_name = :pc_function_name OR USER_FUNCTION_NAME =  :pc_function_name ))))
       AND a.responsibility_id NOT IN
              (SELECT responsibility_id
                 FROM apps.fnd_responsibility_vl
                WHERE responsibility_id IN
                         (SELECT responsibility_id
                            FROM applsys.fnd_resp_functions resp
                           WHERE action_id IN
                                    (    SELECT menu_id
                                           FROM apps.fnd_menu_entries_vl
                                     START WITH menu_id IN
                                                   (SELECT menu_id
                                                      FROM apps.fnd_menu_entries_vl
                                                     WHERE function_id IN
                                                              (SELECT function_id
                                                                 FROM apps.fnd_form_functions_vl a
                                                                WHERE (function_name =
                                                                         :pc_function_name OR USER_FUNCTION_NAME =  :pc_function_name )))
                                     CONNECT BY PRIOR menu_id = sub_menu_id)))

Oracle Web ADI Excel and IE Setups

There are few important setups that needs to be done in Microsoft Excel and Internet Explorer (IE) to work with Oracle Web ADI. Please make sure to have the below setups done before testing a Web ADI otherwise you might encounter with Run time errors while opening the excel sheets.

Excel Setups:

1. Open Excel and go to File --> Options




































2. From Trust Center click on Trust Center Settings...

3. Select Macro Settings from the left navigation pane. 

   a) Under Macro Settings section select "Enable all macros" 

   b) from Developer Macro Settings check the 

      "Trust access to the VBA project object model"  and click OK

4. Under the Protected View uncheck the below two protected view options

Click OK and exit Excel.

Internet Explorer (IE) Setups:

1. Open IE and select Tools --> Internet Options

    Then click on the "Security" tab and click on the "Custom level..."

2. Scroll down to Scripting section and then
    Enable the Allow status bar updates via script option.

Click OK button. 

Close IE and Re Open to test the Web ADI documents. 

ALTER SESSION SET CURRENT_SCHEMA

You can use below command to avoid the use of public synonyms.  By setting the current_schema attribute to the schema owner name it is not necessary to create public synonyms for production table names

ALTER SESSION SET CURRENT_SCHEMA = "XX_SCHEMA_NAME"

Ex:  ALTER SESSION SET CURRENT_SCHEMA = "APPS"