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"

Queries to Check Oracle Application and Database Versions

Application Version:

SELECT RELEASE_NAME, APPLICATIONS_SYSTEM_NAME, ARU_RELEASE_NAME FROM apps.fnd_product_groups

Database Version:

 SELECT * FROM v$version