Sunday, October 22, 2017

Query to ger Responsibility Name for given Form Name

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 applsys.fnd_form_functions a
                                            WHERE 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 applsys.fnd_form_functions a
                                        WHERE 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 applsys.fnd_form_functions a
                                                                  WHERE function_name =
                                                                           :pc_function_name))
                                       CONNECT BY PRIOR menu_id = sub_menu_id)))
ORDER BY responsibility_id

Give Form Function Name as parameter value for pc_function_name.

No comments:

Post a Comment

SupplierAddressImportTemplate.xlsm South Africa Suburb Field mapping in POZ_SUPPLIER_ADDRESSES_INT

Suburb mpping in Supplier Address Import Template will be mapped to Address Element Attribute2 (HZ_LOCATIONS. ADDR_ELEMENT_ATTRIBUTE2)