1 Reply Latest reply on Dec 4, 2015 7:51 PM by Hussein Sawwan-Oracle

    We want to get the complete detail of responsibility along with all menue,submenue,function,subfunctions,other functio

    1973864


      Hi

      Urgent Querry

       

      We want to get the complete detail of responsibility along with all menues,submenue,function,subfunctions,other function which are comming from the sub functions with assigned users responsibilities.

       

      I have developed querry but it is not showing the complete sub-functions.Please guide me how to get the complete functions in queery. We have developed following querry.Thanks.

       

       


      select a.*,fu.user_id, fu.user_name, papf.full_name,
             fu.start_date user_start_date, fu.end_date user_end_date,
             fu.creation_date user_creation_date, fu.employee_id, fu.description from

      (

      SELECT second.application_id
             , second.application_name
             , second.responsibility_id
             , second.responsibility_name
             , second.menu_id "Menu ID"
             , second.user_menu_name "Main Menu Name"
             , second.entry_sequence "Seq"
             , second.prompt "Prompt"
             , second.function_id "Function ID"
             , second.user_function_name "Function"
             , second.func_descrip "Function Descrip"
             , second.sub_menu_id "SubMenu ID"
             , second.sub_menu_name "SubMenu Name"
             , second.sub_seq "Sub Seq"
             , second.sub_prompt "SubPrompt"
             , second.sub_func_id "SubFunction ID"
             , second.sub_func "SubFunction"
             , second.sub_func_descrip "SubFunction Descrip"
             , second.sub_sub_menu_id "Sub-SubMenu ID"
             , second.grant_flag "Grant Flag"
             , second.resp_end_date "Resp End Date"
      /*      , DECODE( exc.rule_type
                     , 'F', ( SELECT 'Ex F: ' || exc.action_id
                                FROM fnd_form_functions_vl fnc
                               WHERE fnc.function_id = exc.action_id
                                 AND second.function_id = exc.action_id ) )
                  excluded_function
             , DECODE( exc.rule_type
                     , 'F', ( SELECT 'Ex SF: ' || exc.action_id
                                FROM fnd_form_functions_vl fnc
                               WHERE fnc.function_id = exc.action_id
                                 AND second.sub_func_id = exc.action_id ) )
                  excluded_sub_function
             , DECODE( exc.rule_type
                     , 'M', ( SELECT 'Ex M: ' || exc.action_id
                                FROM fnd_form_functions_vl fnc
                               WHERE fnc.function_id = exc.action_id
                                 AND second.menu_id = exc.action_id ) )
                  excluded_menu
             , DECODE( exc.rule_type
                     , 'M', ( SELECT 'Ex SM: ' || exc.action_id
                                FROM fnd_form_functions_vl fnc
                               WHERE fnc.function_id = exc.action_id
                                 AND second.sub_menu_id = exc.action_id ) )
                  excluded_sub_menu
             , DECODE( exc.rule_type
                     , 'M', ( SELECT 'Ex SSM: ' || exc.action_id
                                FROM fnd_form_functions_vl fnc
                               WHERE fnc.function_id = exc.action_id
                                 AND second.sub_sub_menu_id = exc.action_id ) )
                  excluded_sub_sub_menu
               */  
                 
          FROM (  SELECT FIRST.application_id
                       , FIRST.application_name
                       , FIRST.responsibility_id
                       , FIRST.responsibility_name
                       , FIRST.end_date AS resp_end_date
                       , FIRST.menu_id
                       , FIRST.user_menu_name
                       , FIRST.entry_sequence
                       , FIRST.prompt
                       , FIRST.function_id
                       , ffft.user_function_name
                       , ffft.description AS func_descrip
                       , FIRST.sub_menu_id
                       , fmv2.user_menu_name AS sub_menu_name
                       , fme2.entry_sequence AS sub_seq
                       , fmet2.prompt AS sub_prompt
                       , fme2.function_id AS sub_func_id
                       , ffft2.user_function_name AS sub_func
                       , ffft2.description AS sub_func_descrip
                       , fme2.sub_menu_id AS sub_sub_menu_id
                       , FIRST.grant_flag
                    FROM (  SELECT fat.application_id
                                 , fat.application_name
                                 , fr.responsibility_id
                                 , frt.responsibility_name
                                 , fr.end_date
                                 , fr.menu_id
                                 , fmv.user_menu_name
                                 , fme.entry_sequence
                                 , fmet.prompt
                                 , fme.sub_menu_id
                                 , fme.function_id
                                 , fme.grant_flag
                              FROM apps.fnd_application_tl fat
                                 , apps.fnd_responsibility fr
                                 , apps.fnd_menus_vl fmv
                                 , apps.fnd_responsibility_tl frt
                                 , apps.fnd_menu_entries fme
                                 , apps.fnd_menu_entries_tl fmet
                             --joins and constant selection
                             WHERE fat.application_id = fr.application_id(+)
                               AND fr.menu_id = fmv.menu_id(+)
                               AND fr.responsibility_id = frt.responsibility_id(+)
                               AND fr.menu_id = fme.menu_id(+)
                               AND fme.menu_id = fmet.menu_id(+)
                               AND fme.entry_sequence = fmet.entry_sequence(+)
                               AND fmet.language = 'US'
                               --------------------------------------
                               -- add specific selection criteria  --
                               --------------------------------------
                               --and   fat.application_id = 840
                               --for DEVL  19080 rows
                               --and fr.responsibility_id = 51856
                               --AND fat.application_id = &appid
                              and frt.RESPONSIBILITY_NAME like '%PK%'
                          ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) FIRST ---for application, responsibility and main menu info
                       , apps.fnd_menus_vl fmv2                                                                 ---for submenu info
                       , apps.fnd_menu_entries fme2
                       , apps.fnd_menu_entries_tl fmet2
                       , apps.fnd_form_functions_tl ffft                                                       ---for function info
                       , apps.fnd_form_functions_tl ffft2                                                   ---for subfunction info
                   --left outer joins keep original records and add any sub menu and function info
                   WHERE FIRST.function_id = ffft.function_id(+)
                     AND FIRST.sub_menu_id = fmv2.menu_id(+)
                     AND FIRST.sub_menu_id = fme2.menu_id(+)
                     AND fme2.menu_id = fmet2.menu_id(+)
                     AND fme2.entry_sequence = fmet2.entry_sequence(+)
                     AND fme2.function_id = ffft2.function_id(+)
                ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21) second -- adds any sub menu and function info
               LEFT OUTER JOIN apps.fnd_resp_functions exc                                                        ---for exclusions
                  ON ( second.application_id = exc.application_id
                  AND second.responsibility_id = exc.responsibility_id
                  AND ( second.function_id = exc.action_id
                    OR second.sub_func_id = exc.action_id
                    OR second.menu_id = exc.action_id
                    OR second.sub_menu_id = exc.action_id
                    OR second.sub_sub_menu_id = exc.action_id ) )

      order by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21 ) a ,
      apps.fnd_user_resp_groups_direct user_resp,
      apps.fnd_user fu,
      apps.per_all_people_f papf

      where     a.responsibility_id = user_resp.RESPONSIBILITY_ID
            and a.application_id = user_resp.RESPONSIBILITY_APPLICATION_ID
            and user_resp.user_id(+) = fu.user_id
            and fu.employee_id = papf.person_id
           
      order by a.responsibility_name,fu.description   

       

      No dout community has healthy envirnament.Promprt response will be highly appreciated.Thanks

       

      Regards

      Hussnain