SQL Script to display MENU or Form Functions attached to Responsibility

SELECT LEVEL,
                  LPAD (' ', (LEVEL-1)*3)||prompt,
                  Description
    FROM  fnd_menu_entries_vl fme
 WHERE  prompt IS NOT NULL
       AND grant_flag='Y'
      AND NOT EXISTS (SELECT 1
                     FROM fnd_resp_functions frf,
                          fnd_responsibility_vl frv
                    WHERE frv.responsibility_id = frf.responsibility_id
                      AND frf.application_id = frv.application_id
                      AND frf.rule_type='M'
                      AND frf.action_id = NVL(fme.sub_menu_id,-1)
                      AND frv.responsibility_name=:RESP_NAME)
   AND NOT EXISTS (SELECT 1
                     FROM fnd_resp_functions frf,
                          fnd_responsibility_vl frv
                    WHERE frv.responsibility_id = frf.responsibility_id
                      AND frf.application_id = frv.application_id
                      AND frf.rule_type='F'
                      AND frf.action_id = NVL(fme.function_id,-1)
                      AND frv.responsibility_name=:RESP_NAME)                   
 CONNECT BY PRIOR sub_menu_id = menu_id
   AND prompt IS NOT NULL
   AND NOT EXISTS (SELECT 1
                     FROM fnd_resp_functions frf,
                          fnd_responsibility_vl frv
                    WHERE frv.responsibility_id = frf.responsibility_id
                      AND frf.application_id = frv.application_id
                      AND frf.rule_type='M'
                      AND frf.action_id = NVL(fme.menu_id,-1)
                      AND frv.responsibility_name=:RESP_NAME)
 START WITH menu_id = (SELECT menu_id
                         FROM fnd_responsibility_vl
                        WHERE responsibility_name=:RESP_NAME)
ORDER SIBLINGS BY entry_sequence

Comments

Post a Comment

Popular posts from this blog

Oracle BI Publisher Tags For-each grouping for Matrix report

For-Each Extensions For-each tags can be confusing.  We will be going over the following tags:     For-each@ section :    For-each@ cell   For-each- group :   field1; field2;     For-each-group: current-group() ;field3;   For-each-group@ column       Sample XML:  <CORPORATION>      <NAME>Vision</NAME>     <DEPT>     <DEPT_NAME>IT</DEPT_NAME>      <EMPLOYEE>         <EMPNO>6</EMPNO>        <ENAME>Rich Walker</ENAME>        <JOB>CLERK</JOB>        <SAL>100</SAL>     </<EMPLOYEE>>     <EMPLOYEE>        ...
Read more

SQL Scripts for Custom Table Creation in Oracle Application 12.2.x

Due to introduction of Online Patching, Post 12.2.x version, there are certain steps to be followed while creating Custom Table. 1. For new table creations - after creating the table in custom schema execute below script to generate edition view and synonym for it in APPS schema. exec AD_ZD_TABLE.UPGRADE('xxcust_schema','xxcust_Table'); 2. For table alterations - after running the DDL run below script to regenerate the edition view for synchronizing any table changes. exec AD_ZD_TABLE.PATCH('xxcust_schema','xxcust_table') 3. Table grants must be done through API to avoid invalids. exec AD_ZD.GRANT_PRIVS('SELECT','xxcust_table,'xx_role') 4. To view objects in all editions, add "_ae" to the data dictionary views select * from user_objects_ae; 5. To update seed data tables in Patch edition, execute prepare command. example:  exec ad_zd_seed.prepare('WF_MESSAGES');  The prepare Procedure will creat...
Read more

Oracle Fusion Business Intelligence Roles

Oracle Business Intelligence roles apply to both Oracle Business Intelligence Publisher and Oracle Fusion Transactional Business Intelligence. They grant access to Business Intelligence functionality, such as the ability to run or author reports. These roles are in addition to the roles that grant access to reports, subject areas, Business Intelligence catalog folders, and HCM data. This topic describes the Business Intelligence roles. This table lists the Business Intelligence roles. Business Intelligence Role Description BI Consumer Role Runs Business Intelligence reports. BI Author Role Creates and edits reports. BI Administrator Role Performs administrative tasks such as creating and editing dashboards and modifying security permissions for reports, folders, and so on. BI Publisher Data Model Developer Role Creates and edits Business Intelligence Publisher data models. BI Consumer Role The predefined Transactional Business Intelligence Transacti...
Read more