Oracle EBS 使用者對應的責任及功能查詢（四層）

Begin
  For r1 In (Select fd.RESPONSIBILITY_APPLICATION_ID,
                    fd.RESPONSIBILITY_ID,
                    fd.SECURITY_GROUP_ID,
                    fd.USER_ID
               From FND_USER_RESP_GROUPS_DIRECT fd, fnd_user fu
              Where fd.USER_ID = fu.user_id
                And fu.user_name = 'ZDCHEN' --使用者名
                And fd.END_DATE Is Null
              Order By fd.responsibility_application_id,
                       fd.responsibility_id,
                       fd.security_group_id) Loop
    --擷取對應的菜單
    For r3 In (Select RESPONSIBILITY_NAME, MENU_ID
                 From FND_RESPONSIBILITY_VL
                Where APPLICATION_ID = r1.responsibility_application_id
                  And RESPONSIBILITY_ID = r1.responsibility_id
                  And END_DATE Is Null) Loop
      --第一層
      For r4 In (Select v.PROMPT title, --Nvl(v.PROMPT, ff.USER_FUNCTION_NAME) 
                        v.SUB_MENU_ID
                   From FND_MENU_ENTRIES_VL v --, fnd_form_functions_vl ff
                  Where v.MENU_ID = r3.menu_id
                       --And v.FUNCTION_ID = ff.FUNCTION_ID(+)
                    And v.GRANT_FLAG = 'Y'
                    And v.PROMPT Is Not Null
                       --排除的功能
                    And Not Exists
                  (Select a.ACTION_ID
                           From FND_RESP_FUNCTIONS a
                          Where a.APPLICATION_ID =
                                r1.responsibility_application_id
                            And a.RESPONSIBILITY_ID = r1.responsibility_id
                            And a.ACTION_ID = v.FUNCTION_ID)
                       --排除的子菜單
                    And Not Exists
                  (Select a.ACTION_ID
                           From FND_RESP_FUNCTIONS a
                          Where a.APPLICATION_ID =
                                r1.responsibility_application_id
                            And a.RESPONSIBILITY_ID = r1.responsibility_id
                            And a.ACTION_ID = v.SUB_MENU_ID)
                  Order By ENTRY_SEQUENCE) Loop
        If r4.SUB_MENU_ID Is Null Then
          dbms_output.put_line(r3.RESPONSIBILITY_NAME || '---' || r4.title);
        Else
          --第二層
          For r5 In (Select v.PROMPT title, v.SUB_MENU_ID
                       From FND_MENU_ENTRIES_VL v
                      Where v.MENU_ID = r4.SUB_MENU_ID
                        And v.GRANT_FLAG = 'Y'
                        And v.PROMPT Is Not Null
                           --排除的功能
                        And Not Exists
                      (Select a.ACTION_ID
                               From FND_RESP_FUNCTIONS a
                              Where a.APPLICATION_ID =
                                    r1.responsibility_application_id
                                And a.RESPONSIBILITY_ID =
                                    r1.responsibility_id
                                And a.ACTION_ID = v.FUNCTION_ID)
                           --排除的子菜單
                        And Not Exists
                      (Select a.ACTION_ID
                               From FND_RESP_FUNCTIONS a
                              Where a.APPLICATION_ID =
                                    r1.responsibility_application_id
                                And a.RESPONSIBILITY_ID =
                                    r1.responsibility_id
                                And a.ACTION_ID = v.SUB_MENU_ID)
                      Order By ENTRY_SEQUENCE) Loop
            If r5.SUB_MENU_ID Is Null Then
              dbms_output.put_line(r3.RESPONSIBILITY_NAME || '---' ||
                                   r4.title || '---' || r5.title);
            Else
              --第三層
              For r6 In (Select v.PROMPT title, v.SUB_MENU_ID
                           From FND_MENU_ENTRIES_VL v
                          Where v.MENU_ID = r5.SUB_MENU_ID
                            And v.GRANT_FLAG = 'Y'
                            And v.PROMPT Is Not Null
                               --排除的功能
                            And Not Exists
                          (Select a.ACTION_ID
                                   From FND_RESP_FUNCTIONS a
                                  Where a.APPLICATION_ID =
                                        r1.responsibility_application_id
                                    And a.RESPONSIBILITY_ID =
                                        r1.responsibility_id
                                    And a.ACTION_ID = v.FUNCTION_ID)
                               --排除的子菜單
                            And Not Exists
                          (Select a.ACTION_ID
                                   From FND_RESP_FUNCTIONS a
                                  Where a.APPLICATION_ID =
                                        r1.responsibility_application_id
                                    And a.RESPONSIBILITY_ID =
                                        r1.responsibility_id
                                    And a.ACTION_ID = v.SUB_MENU_ID)
                          Order By ENTRY_SEQUENCE) Loop
                If r6.SUB_MENU_ID Is Null Then
                  dbms_output.put_line(r3.RESPONSIBILITY_NAME || '---' ||
                                       r4.title || '---' || r5.title ||
                                       '---' || r6.title);
                Else
                  --第四層
                  For r7 In (Select v.PROMPT title, v.SUB_MENU_ID
                               From FND_MENU_ENTRIES_VL v
                              Where v.MENU_ID = r6.SUB_MENU_ID
                                And v.GRANT_FLAG = 'Y'
                                And v.PROMPT Is Not Null
                                   --排除的功能
                                And Not Exists
                              (Select a.ACTION_ID
                                       From FND_RESP_FUNCTIONS a
                                      Where a.APPLICATION_ID =
                                            r1.responsibility_application_id
                                        And a.RESPONSIBILITY_ID =
                                            r1.responsibility_id
                                        And a.ACTION_ID = v.FUNCTION_ID)
                                   --排除的子菜單
                                And Not Exists
                              (Select a.ACTION_ID
                                       From FND_RESP_FUNCTIONS a
                                      Where a.APPLICATION_ID =
                                            r1.responsibility_application_id
                                        And a.RESPONSIBILITY_ID =
                                            r1.responsibility_id
                                        And a.ACTION_ID = v.SUB_MENU_ID)
                              Order By ENTRY_SEQUENCE) Loop
                    If r7.SUB_MENU_ID Is Null Then
                      dbms_output.put_line(r3.RESPONSIBILITY_NAME || '---' ||
                                           r4.title || '---' || r5.title ||
                                           '---' || r6.title || '---' ||
                                           r7.title);
                    End If;
                  End Loop;
                  --第四層結束
                End If;
              End Loop;
              --第三層結束
            End If;
          End Loop;
          --第二層結束
        End If;
      End Loop;
      --第一層輸出結束
    End Loop;
    --擷取對應菜單結束
  End Loop;
End;