AP Payment Documents

-- ##############################################################################
--      PAYMENT DOCUMENTS
-- ##############################################################################
   
         select payment_document_name
              , payment_document_id
              , payment_instruction_id
              , internal_bank_account_id
              , cba.bank_account_name
              , cba.bank_account_num
              , cba.currency_code
	          , paper_stock_type
              , attached_remittance_stub_flag
              , number_of_lines_per_remit_stub
              , number_of_setup_documents
	          , ce_payment_documents.format_code
              , first_available_document_num
              , last_available_document_number
              , last_issued_document_number
              , manual_payments_only_flag
              , ce_payment_documents.attribute_category
              , inactive_date
              , decode(inactive_date, 'Y', 'N') status
	          , meaning paper_stock_type_meaning
	          , fmts.format_name 
	       from ce_payment_documents
	          , ce_lookups lookup
	          , iby_formats_vl fmts
              , ce_bank_accounts cba
          where lookup_type = 'CE_PAPER_STOCK_TYPES' 
	        and paper_stock_type = lookup.lookup_code 
	        and ce_payment_documents.format_code=fmts.format_code
            and internal_bank_account_id = cba.bank_account_id;

User Accounts – Basic Details

-- ##############################################################################
--      BASIC USER ACCOUNTS
-- ##############################################################################

     select fu.user_id
          , fu.user_name
          , fu.description
          , fu.email_address
          , fu.employee_id
          , fu.last_logon_date
          , to_char(fu.start_date, 'DD-MON-YYYY') start_date
          , to_char(fu.end_date, 'DD-MON-YYYY') end_date
          , fu.password_lifespan_days pwd_days
          , fu.password_date
          , ppx.full_name hr_full_name
       from fnd_user fu
  left join per_people_x ppx on fu.employee_id = ppx.person_id
      where 1 = 1
--        and lower(fu.email_address) like '%bob%'
        and fu.user_name = 'BOBHOPE'
   order by fu.user_name;

Attachments

Basic FND Attachments SQL

Attachments can be attached to various “things” in Oracle EBS, like Purchase Orders or Invoices.

For example, if you email out POs, the email PO (e.g. a PDF) will be attached to the PO. If you scan invoices, often the image, or a link to the image, will be attached to the invoice.

The fnd_document_entities_tl.user_entity_name will list the entity (e.g. PO Head or Invoice). And then you can get the document ID the attachment is linked to, from fnd_attached_documents.pk1_value. From there you can link back to your key document, such as the PO number of AP Invoice.

-- ##############################################################################
--        BASIC ATTACHMENT INFO
-- ##############################################################################

  SELECT ad.pk1_value -- key document_id
       , fu.description
       , det.user_entity_name -- e.g. PO Head, Invoice, Disbursement Payment Instruction etc
       , ad.creation_date
       , ad.entity_name
       , d.document_id
       , dt.file_name
    FROM applsys.fnd_document_datatypes dat
       , applsys.fnd_document_entities_tl det
       , applsys.fnd_documents_tl dt
       , applsys.fnd_documents d
       , applsys.fnd_document_categories_tl dct
       , applsys.fnd_attached_documents ad
       , applsys.fnd_user fu
   WHERE d.document_id = ad.document_id
     AND dt.document_id = d.document_id
     AND dct.category_id = d.category_id
     AND d.datatype_id = dat.datatype_id
     AND ad.entity_name = det.data_object_code
     AND ad.created_by = fu.user_id
     and ad.pk1_value = '85233'
--     and ad.entity_name = 'AP_INVOICES'
--     AND d.document_id = 1005520
     and 1 = 1;

Counting Entity Names

You can count the number of entities your attachments are linked to:

-- ##############################################################################
--        COUNT ENTITY NAME TYPES
-- ##############################################################################

  SELECT det.user_entity_name
       , count(*) ct
    FROM applsys.fnd_document_datatypes dat
       , applsys.fnd_document_entities_tl det
       , applsys.fnd_documents_tl dt
       , applsys.fnd_documents d
       , applsys.fnd_document_categories_tl dct
       , applsys.fnd_attached_documents ad
       , applsys.fnd_user fu
   WHERE d.document_id = ad.document_id
     AND dt.document_id = d.document_id
     AND dct.category_id = d.category_id
     AND d.datatype_id = dat.datatype_id
     AND ad.entity_name = det.data_object_code
     AND ad.created_by = fu.user_id
--     and ad.pk1_value = '85233'
--     and ad.entity_name = 'AP_INVOICES'
--     AND d.document_id = 1005520
     and 1 = 1
     group by det.user_entity_name;

SQL Example – Link to Purchase Order

Here’s an example linking back to the Purchase Order:

-- source view = 'FND_ATTACHED_DOCS_FORM_VL'

-- ##############################################################################
--        DOCUMENT ATTACHMENTS 
-- ##############################################################################

  SELECT CASE
            WHEN(det.user_entity_name LIKE 'PO Head%')
               THEN (SELECT pha.segment1
                       FROM po.po_headers_all pha
                      WHERE pha.po_header_id = ad.pk1_value)
            WHEN(det.user_entity_name = 'PO Line')
               THEN (SELECT pha.segment1
                       FROM po.po_headers_all pha
                          , po.po_lines_all pla
                      WHERE pha.po_header_id = pla.po_header_id
                        AND pla.po_line_id = ad.pk1_value)
            WHEN(det.user_entity_name = 'REQ Line')
               THEN (SELECT prha.segment1
                       FROM po.po_requisition_headers_all prha
                          , po.po_requisition_lines_all prla
                      WHERE prha.requisition_header_id =
                                                    prla.requisition_header_id
                        AND prla.requisition_line_id = ad.pk1_value)
            WHEN(det.user_entity_name LIKE 'REQ%')
               THEN (SELECT prha.segment1
                       FROM po.po_requisition_headers_all prha
                      WHERE prha.requisition_header_id = ad.pk1_value)
            ELSE 'UNDEFINED'
         END doc_num
       , CASE
            WHEN(det.user_entity_name LIKE 'PO%')
               THEN 'PO'
            WHEN(det.user_entity_name LIKE 'REQ%')
               THEN 'REQ'
            ELSE 'UNDEFINED'
         END doc_type
       --, ad.seq_num
,        SUBSTR(fu.description, 0, 30) || '...' created_by
       , dt.file_name
       , dt.description
       , det.user_entity_name
       , ad.creation_date
       , ad.entity_name
       , ad.pk1_value
    FROM applsys.fnd_document_datatypes dat
       , applsys.fnd_document_entities_tl det
       , applsys.fnd_documents_tl dt
       , applsys.fnd_documents d
       , applsys.fnd_document_categories_tl dct
       , applsys.fnd_attached_documents ad
       , applsys.fnd_user fu
   WHERE d.document_id = ad.document_id
     AND dt.document_id = d.document_id
     AND dct.category_id = d.category_id
     AND d.datatype_id = dat.datatype_id
     AND ad.entity_name = det.data_object_code
     AND ad.created_by = fu.user_id
     AND d.creation_date > '01-OCT-2007'
     AND (
             det.user_entity_name LIKE 'PO%'
          OR det.user_entity_name LIKE 'REQ%'
         )
     AND dt.file_name NOT LIKE '%.pdf%'
     AND dat.user_name = 'File'
ORDER BY 7 DESC;

Requisition Change Requests

Sections:

  1. Basics of a Requisition Change Request
  2. Basic Workflow Details for Change Linked to Requisition
  3. Change Summary
  4. PORPOCHA Errors – General
  5. Count Summary of Workflow Errors Linked to Change Requests
  6. All Change Requests With Line Count
  7. Accepted Change Requests
  8. Change Requests – Rejected in Error
  9. Detail Drill Down Against Requisition Lines
  10. Detail Drill Down Against Purchase Order
  11. Change Request Details For Rejection Errors
  12. Summary Counts
  13. Change Cancel Notifications
  14. In Process Pos Linked To Change Requests
  15. Broken Change Requests

Basics of a Requisition Change Request

When a requisition change request is created, the following happens (this isn’t a comprehensive list, just some basic notes):

  1. A POREQCHA workflow is launched (Requestor Change Order Approval)
  2. The change_pending_flag flag on the requisition header table (PO_REQUISITION_HEADERS_ALL) is set to Y
  3. Further down the line, other workflows are launched to update the PO.
  4. The PO related workflows are called PORPOCHA (PO Change Approval for Requestor)
  5. The parent workflow for the PORPOCHA workfow is the POREQCHA workflow.
  6. More than one PORPOCHA can be linked to one POREQCHA – e.g. item keys:
    • INFORM_5588997_5567356
    • RESPONSE_5588997_55673159
  7. If the POREQCHA workflows error, they might in turn launch WFERROR workflows
  8. Once the change has been processed, the change_pending_flag flag on the requisition header table (PO_REQUISITION_HEADERS_ALL) is set to N

This basic SQL lists active changes:

select distinct
       wf_item_type
     , wf_item_key
     , document_num
     , ref_po_num
  from po_change_requests 
 where document_num IN ('1231231','1231232') 
   and document_type = 'REQ'
   and change_active_flag = 'Y';

Once you have the item key for the POREQCHA you can run more SQL to find the workflow hierarchy to see which workflows have been launched by the parent POREQCHA:

           select LPAD(' ', (LEVEL - 1) * 10, ' ') || item_type || ' - ' || item_key hier
                 , LEVEL
                 , item_type
                 , item_key
                 , begin_date
                 , end_date
                 , parent_item_type
                 , parent_item_key
              from wf_items
  connect by prior item_key = parent_item_key
        start with item_key = '7404732-5579277-4689465' AND item_type = 'POREQCHA';

Basic Workflow Details for Change Linked to Requisition

-- ##############################################################################
--       BASIC WORKFLOW DETAILS FOR CHANGE LINKED TO REQUISITION
-- ##############################################################################

SELECT DISTINCT SUBSTR(poc.wf_item_type, 1, 25) item_type
              , SUBSTR(poc.wf_item_key, 1, 25) item_key
              , prh.requisition_header_id header_id
           FROM po.po_change_requests poc
              , po.po_requisition_headers_all prh
              , po.po_requisition_lines_all prl
          WHERE prh.requisition_header_id = poc.document_header_id
            AND prl.requisition_header_id = prh.requisition_header_id
            AND prl.requisition_line_id = poc.document_line_id
            AND prh.segment1 = '1231231'
            AND poc.document_type = 'REQ'
UNION ALL
SELECT DISTINCT SUBSTR(poc.wf_item_type, 1, 25) item_type
              , SUBSTR(poc.wf_item_key, 1, 25) item_key
              , poc.document_header_id header_id
           FROM po.po_change_requests poc
              , po.po_requisition_headers_all prh
              , po.po_requisition_lines_all prl
              , po.po_line_locations_all poll
          WHERE prl.requisition_header_id = prh.requisition_header_id
            AND prl.line_location_id = poll.line_location_id
            AND poll.line_location_id = poc.document_line_location_id
            AND prh.segment1 = '1231231';

Change Summary

-- ##############################################################################
--       CHANGE SUMMARY
-- ##############################################################################

  SELECT pcr.ref_po_num
       , pcr.document_line_number line_no
       , pha.creation_date
       , pcr.creation_date change_date
       , TO_CHAR(MONTHS_BETWEEN(pcr.creation_date, pha.creation_date)
          , 9999.99) diff
       , pcr.action_type
       , pcr.new_quantity - pcr.old_quantity qty_diff
       , DECODE(
            SIGN(pcr.new_quantity - pcr.old_quantity)
          , -1, 'DECREASE'
          , 1, 'INCREASE'
          , NULL
         ) qty_summary
       , pcr.new_price - pcr.old_price price_diff
       , DECODE(
            SIGN(pcr.new_price - pcr.old_price)
          , -1, 'DECREASE'
          , 1, 'INCREASE'
          , NULL
         ) price_summary
       , pav.agent_name buyer
    FROM po.po_change_requests pcr
       , apps.po_agents_v pav
       , po.po_headers_all pha
   WHERE pcr.ref_po_num = pha.segment1
     AND pav.agent_id = pha.agent_id
ORDER BY 1 DESC
       , 2;

PORPOCHA Errors – General

-- ##############################################################################
--       PORPOCHA ERRORS - GENERAL  
-- ##############################################################################

SELECT   pcr.change_request_group_id groupid
       , pcr.document_type doctype
       , pcr.document_header_id reqheaderid
       , pcr.ref_po_header_id poheaderid
       , pcr.document_num reqnum
       , pcr.ref_po_num ponum
       , wfi.parent_item_type || '_' || wfi.parent_item_key parenttypekey
       , wfi.item_type || '/' || wfi.item_key itemtypekey
       , pcr.document_line_id doclineid
       , pcr.document_line_number doclinenum
       , pcr.document_distribution_id docdistid
       , pcr.document_distribution_number docdistnum
       , wfias.error_stack
       , wfias.begin_date
       , pha.authorization_status
    FROM po.po_change_requests pcr
       , po.po_headers_all pha
       , applsys.wf_items wfi
       , applsys.wf_item_activity_statuses wfias
   WHERE wfi.item_type = wfias.item_type
     AND wfi.item_key = wfias.item_key
     AND wfias.item_type = 'PORPOCHA'
     --AND wfias.error_stack LIKE '%PO_ReqChangeRequest%'
     AND wfias.error_stack IS NOT NULL
     AND pcr.wf_item_type = wfi.parent_item_type
     AND pcr.wf_item_key = wfi.parent_item_key
     AND pcr.ref_po_num = pha.segment1
     AND pha.segment1 = 123123123
--     AND wfias.error_stack LIKE '%PO_ReqChangeRequestWF_PVT.UpdateReqLine%'
ORDER BY wfias.begin_date DESC;

Count Summary of Workflow Errors Linked to Change Requests

-- ##############################################################################
--       COUNT SUMMARY OF WORKFLOW ERRORS LINKED TO CHANGE REQUESTS
-- ##############################################################################

  SELECT COUNT(*) ct
       , LPAD(wfias.error_stack, 51) error_stack
    FROM po.po_change_requests pcr
       , applsys.wf_items wfi
       , applsys.wf_item_activity_statuses wfias
   WHERE wfi.item_type = wfias.item_type
     AND wfi.item_key = wfias.item_key
     AND wfias.item_type = 'PORPOCHA'
     AND wfias.error_stack IS NOT NULL
     AND pcr.wf_item_type = wfi.parent_item_type
     AND pcr.wf_item_key = wfi.parent_item_key
     AND pcr.ref_po_num IS NOT NULL
GROUP BY LPAD(wfias.error_stack, 51)
ORDER BY ct DESC;

All Change Requests With Line Count

-- ##############################################################################
--       ALL CHANGE REQUESTS WITH LINE COUNT
-- ##############################################################################

SELECT   pcr.ref_po_num
       , pha.creation_date
       , pcr.action_type
       , pcr.request_status
       , COUNT(*) lines
       , TO_CHAR(pcr.creation_date, 'RRRR-MM-DD') change_date
       , pav.agent_name buyer
    FROM po.po_change_requests pcr
       , apps.po_agents_v pav
       , po.po_headers_all pha
   WHERE pcr.ref_po_num IS NOT NULL
     AND pcr.ref_po_num = pha.segment1
     AND pav.agent_id = pha.agent_id
GROUP BY pcr.ref_po_num
       , pha.creation_date
       , pcr.action_type
       , pcr.request_status
       , TO_CHAR(pcr.creation_date, 'RRRR-MM-DD')
       , pav.agent_name
ORDER BY 6 DESC;

Accepted Change Requests

-- ##############################################################################
--       ACCEPTED CHANGE REQUESTS
-- ##############################################################################

SELECT   pcr.ref_po_num
       , pha.creation_date
       , pcr.action_type
       , COUNT(*) lines
       , TO_CHAR(pcr.creation_date, 'RRRR-MM-DD') change_date
       , pav.agent_name buyer
    FROM po.po_change_requests pcr
       , apps.po_agents_v pav
       , po.po_headers_all pha
   WHERE pcr.request_status = 'ACCEPTED'
     AND pcr.ref_po_num IS NOT NULL
     AND pcr.ref_po_num = pha.segment1
     AND pav.agent_id = pha.agent_id
     AND pcr.creation_date BETWEEN '19-DEC-2006' AND SYSDATE
GROUP BY pcr.ref_po_num
       , pha.creation_date
       , pcr.action_type
       , TO_CHAR(pcr.creation_date, 'RRRR-MM-DD')
       , pav.agent_name
ORDER BY 5 DESC;

Change Requests – Rejected in Error

-- ##############################################################################
--       CHANGE REQUESTS - REJECTED IN ERROR
-- ##############################################################################

SELECT DISTINCT pcr.change_request_id
              , pcr.ref_po_num
              , pcr.document_line_number line_no
              , pha.creation_date
              , pcr.response_reason
              , pcr.creation_date change_date
              , TRIM(TO_CHAR(MONTHS_BETWEEN(pcr.creation_date, pha.creation_date), 9999.99)) diff
              , pcr.action_type
              , pcr.old_quantity
              , pcr.new_quantity
              , pcr.old_amount
              , pcr.new_amount
              , CASE
                   WHEN(pcr.new_quantity - pcr.old_quantity > 0)
                      THEN 'QTY INCREASE'
                   WHEN(pcr.new_price - pcr.old_price > 0)
                      THEN 'PRICE INCREASE'
                   WHEN(pcr.new_quantity - pcr.old_quantity < 0)
                      THEN 'QTY DECREASE'
                   WHEN(pcr.new_price - pcr.old_price < 0)
                      THEN 'PRICE DECREASE'
                   WHEN(pcr.new_need_by_date <> pcr.old_need_by_date)
                      THEN 'NEED BY DATE CHANGE'
                   WHEN(pcr.action_type = 'MODIFICATION')
                      THEN 'MOD UNCHANGED'
                   WHEN(pcr.action_type = 'CANCELLATION')
                      THEN 'CANCELLATION'
                   ELSE 'SOMETHING_ELSE'
                END vb
              , pav.agent_name buyer
              , pcr.old_price
              , pcr.new_price
              , pcr.old_need_by_date
              , pcr.new_need_by_date
              , pcr.response_reason
           FROM po.po_change_requests pcr
              , apps.po_agents_v pav
              , po.po_headers_all pha
          WHERE pcr.ref_po_num = pha.segment1
            AND pav.agent_id = pha.agent_id
            AND pcr.response_reason IS NOT NULL
            AND pcr.response_reason = 'Exception when processing the change request'
            AND pcr.request_status = 'REJECTED'
       ORDER BY pcr.change_request_id DESC;

Detail Drill Down Against Requisition Lines

-- ##############################################################################
--       DETAIL DRILL DOWN AGAINST REQUISITION LINES
-- ##############################################################################

SELECT DISTINCT prha.segment1
              , prha.creation_date
              , prla.suggested_vendor_name
              , prla.line_num
              , prla.requisition_line_id
              , pcr.document_line_id
              --, pcr.old_amount
              --, pcr.new_amount
,               pcr.old_quantity old_qty
              , pcr.new_quantity new_qty
              , pcr.old_price old_price
              , pcr.new_price new_price
              , pcr.old_need_by_date
              , pcr.new_need_by_date
           FROM po.po_requisition_headers_all prha
              , po.po_requisition_lines_all prla
              , po.po_change_requests pcr
          WHERE prha.requisition_header_id = prla.requisition_header_id
            AND prla.requisition_line_id = pcr.document_line_id
            AND pcr.document_num = prha.segment1
--            AND prla.unit_price = 0
--            AND prla.quantity = 1
            AND pcr.document_num = '7654321'
       ORDER BY 2 DESC;

Detail Drill Down Against Purchase Order

-- ##############################################################################
--       DETAIL DRILL DOWN AGAINST PURCHASE ORDER 
-- ##############################################################################

SELECT   pcr.initiator
       , pcr.request_reason
       , pcr.action_type
       , pcr.request_level
       , pcr.request_status
       , pcr.creation_date
       , pcr.last_update_date
       , pcr.old_quantity
       , pcr.new_quantity
       , pcr.response_date
       , pcr.response_reason
       , pcr.validation_error
       , pcr.ref_po_num
       , pcr.document_num req_no
    FROM po.po_change_requests pcr
   WHERE pcr.ref_po_num IN ('7654321','7654322','7654323')
ORDER BY pcr.creation_date DESC;

Change Request Details For Rejection Errors

-- ##############################################################################
--       CHANGE REQUEST DETAILS FOR REJECTION ERRORS 
-- ##############################################################################

SELECT   pcr.creation_date
       , pcr.request_reason
       , pcr.action_type
       , pcr.request_level
       , pcr.request_status
       , pcr.last_update_date
       , pcr.old_quantity
       , pcr.new_quantity
       , pcr.response_date
       , pcr.response_reason
       , pcr.validation_error
       , pcr.document_num req_num
       , pcr.ref_po_num po_num
       , fu.description
       , pha.authorization_status
    FROM po.po_change_requests pcr
       , applsys.fnd_user fu
       , po.po_headers_all pha
   WHERE pcr.request_status = 'REJECTED'
     AND pcr.ref_po_num IS NOT NULL
     AND pcr.last_updated_by = fu.user_id
     AND pcr.ref_po_num = pha.segment1
--     AND pcr.response_reason = 'Exception when processing the change request'
     AND pcr.response_reason = 'PO is already rejected'
ORDER BY pcr.creation_date DESC;

Summary Counts

-- ##############################################################################
--       SUMMARY COUNTS
-- ##############################################################################

-- SUMMARY OF RESPONSES

  SELECT COUNT(*)
       , pcr.request_status
    FROM po.po_change_requests pcr
   WHERE pcr.request_status IS NOT NULL
GROUP BY pcr.request_status
ORDER BY 1 DESC;

-- SUMMARY OF RESPONSES LIMIT BY DATE 

  SELECT COUNT(*)
       , pcr.request_status
    FROM po.po_change_requests pcr
   WHERE pcr.request_status IS NOT NULL
     AND TRUNC(pcr.creation_date) >= :thedate
GROUP BY pcr.request_status;

-- COUNT OF EXCEPTION ERRORS 

  SELECT COUNT(*)
       , pcr.request_status
    FROM po.po_change_requests pcr
   WHERE pcr.response_reason = 'Exception when processing the change request'
GROUP BY pcr.request_status;

-- SUMMARY OF REQUEST LEVELS

  SELECT COUNT(*)
       , pcr.request_level
    FROM po.po_change_requests pcr
GROUP BY pcr.request_level;

-- SUMMARY OF TYPES OF ACTIONS DONE TO REQS 

  SELECT COUNT(*)
       , pcr.action_type
    FROM po.po_change_requests pcr
GROUP BY pcr.action_type;

--SUMMARY OF VALIDATION ERRORS

SELECT   pcr.validation_error
       , COUNT(*)
    FROM po.po_change_requests pcr
   WHERE pcr.response_reason = 'Exception when processing the change request'
     AND pcr.response_reason IS NOT NULL
GROUP BY pcr.validation_error
ORDER BY 2 DESC;

--SUMMARY OF RESPONSE REASONS
SELECT   pcr.response_reason

       , COUNT(*)
    FROM po.po_change_requests pcr
   WHERE pcr.response_reason IS NOT NULL
GROUP BY pcr.response_reason
ORDER BY 2 DESC;

--WHAT HAS BEEN REJECTED IN ERROR GROUPED BY BUYER

  SELECT COUNT(*)
       , pav.agent_name buyer
    FROM po.po_change_requests pcr
       , apps.po_agents_v pav
       , po.po_headers_all pha
   WHERE pcr.request_status = 'REJECTED'
     AND pcr.ref_po_num IS NOT NULL
     AND pcr.response_reason = 'Exception when processing the change request'
     AND pcr.ref_po_num = pha.segment1
     AND pav.agent_id = pha.agent_id
GROUP BY pav.agent_name
ORDER BY 1 DESC;

--WHAT HAS BEEN ACCEPTED GROUPED BY BUYER

  SELECT COUNT(*)
       , pav.agent_name buyer
    FROM po.po_change_requests pcr
       , apps.po_agents_v pav
       , po.po_headers_all pha
   WHERE pcr.request_status = 'ACCEPTED'
     AND pcr.ref_po_num IS NOT NULL
     AND pcr.ref_po_num = pha.segment1
     AND pav.agent_id = pha.agent_id
GROUP BY pav.agent_name
ORDER BY 1 DESC;

-- COUNT OF ALL CHANGES BY DATE 

  SELECT COUNT(*)
       , TO_CHAR(pcr.creation_date, 'RRRR-MM-DD') change_req_date
    FROM po.po_change_requests pcr
GROUP BY TO_CHAR(pcr.creation_date, 'RRRR-MM-DD')
ORDER BY 2 DESC;

-- COUNT OF ALL ACCEPTED CHANGES BY DATE 

  SELECT COUNT(*)
       , TO_CHAR(pcr.creation_date, 'RRRR-MM-DD') change_req_date
    FROM po.po_change_requests pcr
   WHERE pcr.request_status = 'ACCEPTED'
GROUP BY TO_CHAR(pcr.creation_date, 'RRRR-MM-DD')
ORDER BY 2 DESC;

-- COUNT OF ALL REJECTIONS CHANGES BY DATE 

  SELECT COUNT(*)
       , TO_CHAR(pcr.creation_date, 'RRRR-MM-DD') change_req_date
    FROM po.po_change_requests pcr
   WHERE pcr.request_status = 'REJECTED'
GROUP BY TO_CHAR(pcr.creation_date, 'RRRR-MM-DD')
ORDER BY 2 DESC;

-- COUNT EXCEPTION ERRORS BY DATE

  SELECT COUNT(*) line_count
       , TO_CHAR(pcr.creation_date, 'RRRR-MM-DD') change_req_date
    FROM po.po_change_requests pcr
   WHERE pcr.response_reason = 'Exception when processing the change request'
GROUP BY TO_CHAR(pcr.creation_date, 'RRRR-MM-DD')
ORDER BY 2 DESC;

Change Cancel Notifications

-- ##############################################################################
--       CHANGE CANCEL NOTIFICATIONS 
-- ##############################################################################

SELECT COUNT(*)
  FROM apps.wf_notifications wn
 WHERE wn.message_name = 'NEW_PO_CHANGE';

SELECT COUNT(*)
  FROM apps.wf_notifications wn
 WHERE wn.message_name = 'NEW_PO_CHANGE'
   AND wn.responder IS NULL;

SELECT COUNT(*)
  FROM apps.wf_notifications wn
 WHERE wn.message_name = 'NEW_PO_CHANGE'
   AND wn.responder LIKE '%email:%';

SELECT COUNT(*)
  FROM apps.wf_notifications wn
 WHERE wn.message_name = 'NEW_PO_CHANGE'
   AND wn.responder NOT LIKE '%email:%'
   AND wn.responder IS NOT NULL;

SELECT notification_id
     , MESSAGE_TYPE
     , message_name
     , status
     , mail_status
     , begin_date
     , end_date
     , from_user
     , to_user
     , subject
     , responder
  FROM apps.wf_notifications
 WHERE to_user LIKE '%Finn%'
   AND status = 'OPEN'
   AND message_name = 'NEW_PO_CHANGE';

In Process Pos Linked To Change Requests

-- ##############################################################################
--       IN PROCESS POS LINKED TO CHANGE REQUESTS
-- ##############################################################################

SELECT DISTINCT pha.segment1
              , pav.agent_name
              , pha.creation_date
              , pcr.creation_date
              , ROUND(MONTHS_BETWEEN(SYSDATE, pha.creation_date), 2) age
              , (SELECT wn.status
                   FROM apps.wf_notifications wn
                  WHERE wn.subject LIKE '%pha.segment1%') notif_check
           FROM po.po_headers_all pha
              , apps.po_agents_v pav
              , po.po_change_requests pcr
          WHERE pav.agent_id = pha.agent_id
            AND pha.segment1 = pcr.ref_po_num
            AND pha.authorization_status = 'IN PROCESS'
       ORDER BY 4 DESC;

Broken Change Requests

-- ##############################################################################
--       BROKEN CHANGE REQUESTS
-- ##############################################################################

-- error stack is populated, but workflow is active

SELECT DISTINCT
       wfi.begin_date
     , pcr.document_type doctype
     , pcr.document_header_id req_header_id
     , pcr.document_num req
     , pcr.ref_po_header_id po_header_id
     , pcr.ref_po_num po
     , wfi.parent_item_type parent_item_type
     , wfi.parent_item_key parent_item_key
     , wfias.error_stack
     , wfi.end_date
     , wfi.item_type
     , wfi.item_key
  FROM po.po_change_requests pcr
     , po.po_headers_all pha
     , applsys.wf_items wfi
     , applsys.wf_item_activity_statuses wfias
 WHERE wfi.item_type = wfias.item_type
   AND wfi.item_key = wfias.item_key
   AND wfias.item_type = 'PORPOCHA'
   AND wfias.error_stack IS NOT NULL
   AND pcr.wf_item_type = wfi.parent_item_type
   AND pcr.wf_item_key = wfi.parent_item_key
   AND pcr.ref_po_num = pha.segment1
   AND wfi.end_date IS NULL
   AND 1 = 1;

Approval – Approval Limits

Sections:

  1. Standard Approval Limits
  2. Count of Staff Assigned to Job Titles with an Approval Limit
  3. Job Titles with Limits which Are Attached to Staff
  4. Simple View of Approval Limits / Doc Types against Job Title
  5. Viewing the Value Limits against Job Titles

Standard Approval Limits

-- ##############################################################################
--      STANDARD APPROVAL LIMITS
-- ##############################################################################
SELECT DISTINCT ppca.org_id
              , papf.full_name
              , papf.employee_number empno
              , NVL(fu.user_name, '###') login
              , fu.description login_name
              , pcga.control_group_name app_gp
              , pcr.amount_limit lim
              , pcr.segment1_low co
              , NVL(pcr.segment2_low, 'All') lo
              , CASE
                   WHEN pcr.segment2_high <> pcr.segment2_low
                      THEN pcr.segment2_high
                   ELSE ''
                END hi
              , TRUNC(ppca.last_update_date) updated_date
              , pj.NAME job_title
              , pcak.segment1 || '/' || pcak.segment2 || '/' || pcak.segment4 ch_acct
              , hlat.location_code user_location
              , haou.NAME hr_org
              , papf.email_address
              , papf2.full_name manager_full_name
              , papf2.employee_number manager_empno
              , TRIM(fu2.description) manager_desc
              , fu2.user_name manager_user_name
              , papf2.email_address manager_email
              , gal.authorization_limit gl_limit
           FROM applsys.fnd_user fu
           JOIN hr.per_all_people_f papf                ON papf.person_id =                     fu.employee_id
           JOIN hr.per_all_assignments_f paaf           ON paaf.person_id =                     papf.person_id
           JOIN hr.per_jobs pj                          ON paaf.job_id =                        pj.job_id
           JOIN hr.hr_all_organization_units haou       ON haou.organization_id =               paaf.organization_id
      LEFT JOIN hr.hr_locations_all_tl hlat             ON paaf.location_id =                   hlat.location_id
           JOIN hr.per_assignment_status_types past     ON paaf.assignment_status_type_id =     past.assignment_status_type_id AND past.per_system_status IN('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
      LEFT JOIN hr.per_person_type_usages_f pptu        ON papf.person_id =                     pptu.person_id
      LEFT JOIN hr.pay_cost_allocation_keyflex pcak     ON haou.cost_allocation_keyflex_id =    pcak.cost_allocation_keyflex_id
           JOIN po.po_position_controls_all ppca        ON ppca.job_id =                        paaf.job_id
      LEFT JOIN hr.per_all_people_f papf2               ON paaf.supervisor_id =                 papf2.person_id AND SYSDATE BETWEEN papf2.effective_start_date AND papf2.effective_end_date
      LEFT JOIN applsys.fnd_user fu2                    ON papf2.person_id =                    fu2.employee_id
           JOIN po.po_control_groups_all pcga           ON pcga.control_group_id =              ppca.control_group_id
           JOIN po.po_control_functions pcf             ON pcf.control_function_id =            ppca.control_function_id
           JOIN po.po_control_rules pcr                 ON pcr.control_group_id =               ppca.control_group_id AND pcr.object_code = 'ACCOUNT_RANGE'
      LEFT JOIN gl.gl_authorization_limits gal          ON papf.person_id =                     gal.employee_id
          WHERE SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
            AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
            AND SYSDATE BETWEEN pptu.effective_start_date AND pptu.effective_end_date
--            AND NVL(papf2.current_employee_flag, 'Y') = 'Y'
--            AND NVL(fu.end_date, SYSDATE + 1) > SYSDATE
--            AND papf.current_employee_flag = 'Y'
--            AND paaf.assignment_type = 'E'
--            AND paaf.primary_flag = 'Y'
--            AND papf.full_name LIKE :pn
            AND papf.employee_number = '1234'
       ORDER BY 4
              , 1;

Count of Staff Assigned to Job Titles with an Approval Limit

-- ##############################################################################
--      COUNT OF STAFF ASSIGNED TO JOB TITLES WITH AN APPROVAL LIMIT
-- ##############################################################################

SELECT DISTINCT pj.business_group_id org
              , pj.NAME job_title
              , (SELECT COUNT(*)
                   FROM hr.per_all_people_f papf
                      , hr.per_all_assignments_f paaf
                  WHERE papf.person_id = paaf.person_id
                    AND paaf.job_id = pj.job_id
                    AND paaf.assignment_number IS NOT NULL
                    AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
                    AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
                    AND paaf.primary_flag = 'Y'
                    AND paaf.assignment_type = 'E'
                    AND papf.current_employee_flag = 'Y') primary_people
              , (SELECT COUNT(*)
                   FROM hr.per_all_people_f papf
                      , hr.per_all_assignments_f paaf
                  WHERE papf.person_id = paaf.person_id
                    AND paaf.job_id = pj.job_id
                    AND paaf.assignment_number IS NOT NULL
                    AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
                    AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date) all_people
              , (SELECT DISTINCT pcr.amount_limit
                            FROM po.po_position_controls_all ppca
                               , po.po_control_rules pcr
                           WHERE pcr.control_group_id = ppca.control_group_id
                             AND ppca.job_id = pj.job_id
                             AND ppca.end_date IS NULL
                             AND pcr.object_code = 'DOCUMENT_TOTAL') limit_
           FROM hr.per_jobs pj
              , hr.hr_all_organization_units_tl bus_gp
              , po.po_position_controls_all ppca
              , po.po_control_rules pcr
          WHERE ppca.job_id = pj.job_id
            AND pj.business_group_id = bus_gp.organization_id
            AND pcr.control_group_id = ppca.control_group_id
            AND ppca.end_date IS NULL
       ORDER BY pj.NAME;

Job Titles with Limits which Are Attached to Staff


-- ##############################################################################
--       JOB TITLES WITH LIMITS WHICH ***ARE*** ATTACHED TO STAFF
--       (PRIMARY ASSIGNMENTS ONLY)
--       AND STAFF HAVE AN ORACLE ACCOUNT
-- ##############################################################################

SELECT DISTINCT pj.NAME job_title
              , pj.business_group_id bg
              , (SELECT COUNT(*)
                   FROM hr.per_all_people_f papf
                      , hr.per_all_assignments_f paaf
                      , applsys.fnd_user fu
                  WHERE papf.person_id = paaf.person_id
                    AND fu.employee_id = papf.person_id
                    AND paaf.job_id = pj.job_id
                    AND paaf.primary_flag = 'Y'
                    AND paaf.assignment_type = 'E'
                    AND papf.current_employee_flag = 'Y'
                    AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
                    AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date) ct
           FROM hr.per_jobs pj
              , hr.hr_all_organization_units_tl bus_gp
              , po.po_position_controls_all ppca
          WHERE ppca.job_id = pj.job_id
            AND pj.business_group_id = bus_gp.organization_id
            AND ppca.end_date IS NULL
            AND pj.DATE_TO IS NULL
            AND pj.job_id IN(
                   SELECT pj.job_id
                     FROM hr.per_all_people_f papf
                        , hr.per_all_assignments_f paaf
                    WHERE papf.person_id = paaf.person_id
                      AND paaf.job_id = pj.job_id
                      AND paaf.primary_flag = 'Y'
                      AND paaf.assignment_type = 'E'
                      AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
                      AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date)
       ORDER BY pj.NAME
              , pj.business_group_id;

Simple View of Approval Limits / Doc Types against Job Title

-- ##############################################################################
--        SIMPLE VIEW OF APPROVAL LIMITS / DOC TYPES AGAINST JOB TITLE
-- ##############################################################################

SELECT   pj.NAME
       , pcf.control_function_name
       , pcga.control_group_name control_group
       , pcr.amount_limit
       , pj.business_group_id org_id
    FROM po.po_position_controls_all ppca
       , po.po_control_groups_all pcga
       , po.po_control_functions pcf
       , po.po_control_rules pcr
       , hr.per_jobs pj
       , hr.hr_all_organization_units_tl bus_gp
   WHERE ppca.job_id = pj.job_id
     AND pcga.control_group_id = ppca.control_group_id
     AND pcga.control_group_id = pcr.control_group_id
     AND pcf.control_function_id = ppca.control_function_id
     AND pj.business_group_id = bus_gp.organization_id
     AND pcr.object_code = 'DOCUMENT_TOTAL'
     AND SYSDATE BETWEEN ppca.start_date AND NVL(ppca.end_date, SYSDATE + 1)
     AND pj.job_id IN(
            SELECT pj.job_id
              FROM hr.per_all_people_f papf
                 , hr.per_all_assignments_f paaf
             WHERE papf.person_id = paaf.person_id
               AND paaf.job_id = pj.job_id
               AND paaf.primary_flag = 'Y'
               AND paaf.assignment_type = 'E'
               AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
               AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date)
ORDER BY pj.NAME;

Viewing the Value Limits against Job Titles

-- ##############################################################################
--      VIEWING THE VALUE LIMITS AGAINST JOB TITLES
-- ##############################################################################

SELECT DISTINCT pj.NAME job
              , pcr.amount_limit
           FROM po.po_position_controls_all ppca
              , po.po_control_rules pcr
              , hr.per_jobs pj
              , hr.hr_all_organization_units_tl bus_gp
          WHERE pcr.control_group_id = ppca.control_group_id
            AND pj.business_group_id = bus_gp.organization_id
            AND ppca.end_date IS NULL
            AND ppca.job_id = pj.job_id
       ORDER BY pj.NAME;

Responsibilities – Attached to Menu

This SQL returns responsibilities with access to a particular menu, any level down, not just the top level.

Enter user menu name e.g. AR_INTERFACE_GUI.

-- ##############################################################################
--      RESPS ATTACHED TO A MENU 
-- ##############################################################################

  SELECT DISTINCT frt.responsibility_name
                , fr.responsibility_key
                , fr.responsibility_id
                , (SELECT DISTINCT COUNT (*) FROM applsys.fnd_user fu, apps.fnd_user_resp_groups_direct furg WHERE furg.user_id = fu.user_id AND frt.responsibility_id = furg.responsibility_id AND NVL(furg.end_date, SYSDATE + 1) > SYSDATE AND NVL(fu.end_date, SYSDATE + 1) > SYSDATE) user_count
                , fat.application_name app
                , fm.menu_name
                , fmt.user_menu_name
    FROM applsys.fnd_responsibility fr
    JOIN applsys.fnd_responsibility_tl frt ON fr.application_id =    frt.application_id
                                          AND fr.responsibility_id = frt.responsibility_id
    JOIN applsys.fnd_application_tl fat    ON fr.application_id =    fat.application_id 
    JOIN applsys.wf_local_user_roles wlur  ON fr.responsibility_id = wlur.role_orig_system_id 
    JOIN applsys.fnd_menus_tl fmt          ON fr.menu_id =           fmt.menu_id 
    JOIN applsys.fnd_menus fm              ON fm.menu_id =           fmt.menu_id
   WHERE fr.menu_id IN
            (    SELECT menu_id
                   FROM applsys.fnd_menu_entries fme
             CONNECT BY PRIOR fme.menu_id = fme.sub_menu_id
             START WITH fme.menu_id = (SELECT fmv.menu_id
                                         FROM apps.fnd_menus_vl fmv
                                        WHERE fmv.user_menu_name = :menu))
ORDER BY 2;

-- without linking to responsibilities (since some menus are not linked to responsibilities, but might be a parent menu containing a sub menu you want to delete)

  SELECT DISTINCT fm.menu_name
                , fmt.user_menu_name
    FROM applsys.fnd_menus_tl fmt
       , applsys.fnd_menus fm
   WHERE fm.menu_id = fmt.menu_id
--     AND fmt.user_menu_name != :menu
     AND fmt.menu_id IN
            (    SELECT menu_id
                   FROM applsys.fnd_menu_entries fme
             CONNECT BY PRIOR fme.menu_id = fme.sub_menu_id
             START WITH fme.menu_id = (SELECT fmv.menu_id
                                         FROM apps.fnd_menus_vl fmv
                                        WHERE fmv.user_menu_name = :menu))
ORDER BY 2;

Responsibilities – Attached to Function

This SQL will return responsibilities with access to a particular function, any level down, not just at the top level.

Search against a function name e.g. AR_ARXCWMAI_QIT.

-- ##############################################################################
--      RESPS ATTACHED TO A FUNCTION 
-- ############################################################################*/

SELECT DISTINCT frt.responsibility_id
              , frt.responsibility_name
              , fr.responsibility_key
              , fr.creation_date
              , fa.application_short_name
              , fmt.user_menu_name menu
              , (SELECT DISTINCT COUNT(*) FROM applsys.fnd_user fu, apps.fnd_user_resp_groups_direct furg WHERE furg.user_id = fu.user_id AND frt.responsibility_id = furg.responsibility_id AND NVL(furg.end_date, SYSDATE + 1) > SYSDATE AND NVL(fu.end_date, SYSDATE + 1) > SYSDATE AND fr.end_date IS NULL) user_count
           FROM applsys.fnd_responsibility fr
           JOIN applsys.fnd_responsibility_tl frt ON fr.application_id = frt.application_id
                                                 AND fr.responsibility_id = frt.responsibility_id 
           JOIN applsys.fnd_application fa        ON fa.application_id = fr.application_id 
           JOIN applsys.wf_local_user_roles wlur  ON fr.responsibility_id = wlur.role_orig_system_id 
      LEFT JOIN applsys.fnd_menus_tl fmt          ON fr.menu_id = fmt.menu_id          -- responsibilities are not always linked to a menu
          WHERE wlur.role_orig_system = 'FND_RESP'
            AND SYSDATE BETWEEN fr.start_date AND NVL(fr.end_date , SYSDATE + 1)
            AND SYSDATE BETWEEN wlur.start_date AND NVL(wlur.expiration_date, SYSDATE + 1)
            AND fr.menu_id IN(
                   SELECT     menu_id
                         FROM applsys.fnd_menu_entries fme
                   CONNECT BY PRIOR fme.menu_id = fme.sub_menu_id
                   START WITH fme.function_id =
                                     (SELECT function_id
                                        FROM applsys.fnd_form_functions fff
                                       WHERE fff.function_name = :function_name));

Request Groups

Sections:

  1. Request Group Details
  2. Request Groups And Responsibilities
  3. Concurrent Programs and Whether Assigned to a Request Group
  4. Request Sets and Whether Assigned to a Request Group
  5. Request Groups Against Resps – Basic List
  6. Request Sets
  7. Request Sets and Concurrent Programs – Comparison between two Request Groups

Request Group Details

This lists the contents of a request group.

-- ##############################################################################
--       REQUEST GROUP DETAILS
-- ##############################################################################

    SELECT frg.request_group_name
         , frg.creation_date
         , fu1.user_name created_by
         , fat1.application_name group_application
         , DECODE(frgu.request_unit_type, 'P', 'Program', 'S', 'Request Set', 'A', 'Application') type
         , CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcp.user_concurrent_program_name FROM applsys.fnd_concurrent_programs_tl fcp WHERE fcp.concurrent_program_id = frgu.request_unit_id AND fcp.application_id = fat2.application_id)
                WHEN frgu.request_unit_type = 'S' THEN
                (SELECT frst.user_request_set_name FROM applsys.fnd_request_sets_tl frst WHERE frst.request_set_id = frgu.request_unit_id AND frst.application_id = fat2.application_id)
                WHEN frgu.request_unit_type = 'A' THEN
                (SELECT fat.application_name FROM applsys.fnd_application_tl fat WHERE fat.application_id = frgu.request_unit_id)
           END name
         , CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcpt.enabled_flag FROM applsys.fnd_concurrent_programs fcpt WHERE fcpt.concurrent_program_id = frgu.request_unit_id AND fcpt.application_id = fat2.application_id)
           END job_enabled
         , CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcpt.concurrent_program_id FROM applsys.fnd_concurrent_programs fcpt WHERE fcpt.concurrent_program_id = frgu.request_unit_id AND fcpt.application_id = fat2.application_id)
           END job_id
         , CASE WHEN frgu.request_unit_type = 'S' THEN
                (SELECT frs.end_date_active FROM fnd_request_sets frs WHERE frs.request_set_id = frgu.request_unit_id AND frs.application_id = fat2.application_id)
           END request_set_end_date
         , CASE WHEN frgu.request_unit_type = 'S' THEN
                (SELECT frs.request_set_id FROM fnd_request_sets frs WHERE frs.request_set_id = frgu.request_unit_id AND frs.application_id = fat2.application_id)
           END request_set_id
         , fat2.application_name application
         , frgu.last_update_date line_updated
         , fu1.user_name line_updated_by 
      FROM applsys.fnd_request_groups frg
      JOIN applsys.fnd_request_group_units frgu ON frg.application_id =       frgu.application_id AND frg.request_group_id =     frgu.request_group_id
      JOIN applsys.fnd_application_tl fat1      ON frg.application_id =       fat1.application_id
      JOIN applsys.fnd_application_tl fat2      ON frgu.unit_application_id = fat2.application_id
      JOIN applsys.fnd_user fu1                 ON frg.created_by =           fu1.user_id
      JOIN applsys.fnd_user fu2                 ON frgu.last_updated_by =     fu2.user_id
     WHERE 1 = 1
       AND frg.request_group_name = 'All Reports'
       AND fat1.application_name = 'Payables'
       AND 1 = 1;

Request Groups And Responsibilities

-- ##############################################################################
--       REQUEST GROUPS AND RESPONSIBILITIES
-- ##############################################################################

-- This is useful if you want to work out which responsibilities have access to
-- specific concurrent programs, request sets or applications

-- concurrent requests

SELECT frg.request_group_name
     , frg.request_group_id
     , fat.application_name
     , frt.responsibility_name
     , frt.creation_date
     , frt.created_by
     , fcpt.user_concurrent_program_name job_name
     , DECODE(frgu.request_unit_type,'S','Request Set','P','Program') job_type
     , fat2.application_name job_app
  FROM applsys.fnd_request_groups frg
  JOIN applsys.fnd_request_group_units frgu    ON frg.application_id =       frgu.application_id AND frg.request_group_id = frgu.request_group_id AND frgu.request_unit_type = 'P'
  JOIN applsys.fnd_concurrent_programs_tl fcpt ON frgu.request_unit_id =     fcpt.concurrent_program_id
  JOIN applsys.fnd_responsibility fr           ON fr.request_group_id =      frg.request_group_id
  JOIN applsys.fnd_responsibility_tl frt       ON fr.responsibility_id =     frt.responsibility_id
  JOIN applsys.fnd_application_tl fat          ON frgu.application_id =      fat.application_id
  JOIN applsys.fnd_application_tl fat2         ON frgu.unit_application_id = fat2.application_id
 WHERE 1 = 1
   AND frt.responsibility_name = 'AP Manager'
   AND frg.request_group_name = 'All Reports'
   AND fat.application_name = 'Payables'
   AND fcpt.user_concurrent_program_name LIKE 'AP%'
--   AND fr.end_date IS NULL
   AND 1 = 1;

-- request sets

SELECT frg.request_group_name
     , fat.application_name
     , frt.responsibility_name
     , frst.user_request_set_name job_name
     , DECODE(frgu.request_unit_type,'S','Request Set','P','Program') job_type
     , fat2.application_name job_app
  FROM applsys.fnd_request_groups frg
  JOIN applsys.fnd_request_group_units frgu ON frg.application_id =       frgu.application_id AND frg.request_group_id = frgu.request_group_id AND frgu.request_unit_type = 'S'
  JOIN applsys.fnd_responsibility fr        ON fr.request_group_id =      frg.request_group_id
  JOIN applsys.fnd_responsibility_tl frt    ON fr.responsibility_id =     frt.responsibility_id
  JOIN applsys.fnd_request_sets_tl frst     ON frgu.request_unit_id =     frst.request_set_id
  JOIN applsys.fnd_application_tl fat       ON frgu.application_id =      fat.application_id
  JOIN applsys.fnd_application_tl fat2      ON frgu.unit_application_id = fat2.application_id
 WHERE 1 = 1 
   AND frt.responsibility_name = 'Project Super User'
ORDER BY 1, 2, 4;

-- linked to applications

SELECT DISTINCT
       frg.request_group_name
     , fat.application_name
     , frt.responsibility_name
     , fat2.application_name assigned_application_name
  FROM applsys.fnd_request_groups frg       
  JOIN applsys.fnd_request_group_units frgu ON frg.application_id =       frgu.application_id
                                           AND frg.request_group_id =     frgu.request_group_id
  JOIN applsys.fnd_responsibility fr        ON fr.request_group_id =      frg.request_group_id
  JOIN applsys.fnd_responsibility_tl frt    ON fr.responsibility_id =     frt.responsibility_id 
  JOIN applsys.fnd_application_tl fat       ON frgu.application_id =      fat.application_id 
  JOIN applsys.fnd_application_tl fat2      ON frgu.unit_application_id = fat2.application_id
 WHERE frt.responsibility_name = 'Project Super User'
ORDER BY 1, 2;

Concurrent Programs and Whether Assigned to a Request Group

-- ##############################################################################
--        CONCURRENT PROGRAMS AND WHETHER ASSIGNED TO A REQUEST GROUP
-- ##############################################################################

SELECT   fcpt.user_concurrent_program_name request_name
       , fat.application_name
       , fcpt.creation_date
       , fu.user_name created_by
       , (SELECT COUNT(*)
             FROM applsys.fnd_request_group_units frgu
            WHERE frgu.request_unit_type = 'P'
              AND frgu.request_unit_id = fcpt.concurrent_program_id) request_group_count
    FROM applsys.fnd_concurrent_programs_tl fcpt
    JOIN applsys.fnd_application_tl fat ON fcpt.application_id = fat.application_id
    JOIN applsys.fnd_user fu            ON fcpt.created_by =     fu.user_id
   WHERE fcpt.user_concurrent_program_name = 'Purge Transaction Objects Diagnostics'
ORDER BY fcpt.user_concurrent_program_name;

Request Sets and Whether Assigned to a Request Group

-- ##############################################################################
--        REQUEST SETS AND WHETHER ASSIGNED TO A REQUEST GROUP
-- ##############################################################################

SELECT   frst.user_request_set_name
       , fat.application_name
       , frst.creation_date
       , fu.description created_by
       , (SELECT COUNT(*)
             FROM applsys.fnd_request_group_units frgu
            WHERE frgu.request_unit_type = 'S'
              AND frgu.request_unit_id = frst.request_set_id) request_group_count
    FROM applsys.fnd_request_sets_tl frst
    JOIN applsys.fnd_application_tl fat ON frst.application_id = fat.application_id
    JOIN applsys.fnd_user fu            ON frst.created_by =     fu.user_id
   WHERE frst.user_request_set_name = 'Purge Transaction Objects Diagnostics'
ORDER BY frst.user_request_set_name;

Request Groups Against Resps – Basic List

-- ##############################################################################
--       REQUEST GROUPS AGAINST RESPS - BASIC LIST
-- ##############################################################################

SELECT rtl.responsibility_name
     , rg.request_group_name
     , rg.description
     , fat.application_name
  FROM applsys.fnd_responsibility_tl rtl
  JOIN applsys.fnd_responsibility r   ON rtl.responsibility_id =  r.responsibility_id
  JOIN applsys.fnd_request_groups rg  ON r.request_group_id =     rg.request_group_id
                                     AND r.group_application_id = rg.application_id
  JOIN applsys.fnd_application_tl fat ON r.application_id =       fat.application_id
 WHERE rtl.responsibility_name LIKE '%User%';

Request Sets

-- ##############################################################################
--       REQUESET SETS
-- ##############################################################################

select * from applsys.fnd_request_sets_tl frst;

Request Sets and Concurrent Programs – Comparison between two Request Groups

If you have two request groups and want to see what’s in one but not the other, you can export their contents to Excel and do a Vlookup or something like that. Or you can use MINUS instead. You put e.g. Request Group 1 in the first SQL, and Request Group 2 in the second SQL, with a Minus between them. That’ll show you what’s in the first group and not in the second. You can then swap the names around and re-do to compare the other way round.

-- ##############################################################################
--       COMPARING TWO REQUEST GROUPS - IN ONE AND NOT IN THE OTHER
-- ##############################################################################

    SELECT CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcp.user_concurrent_program_name FROM fnd_concurrent_programs_tl fcp WHERE fcp.concurrent_program_id = frgu.request_unit_id AND fcp.application_id = fat2.application_id)
                WHEN frgu.request_unit_type = 'S' THEN
                (SELECT frst.user_request_set_name FROM fnd_request_sets_tl frst WHERE frst.request_set_id = frgu.request_unit_id AND frst.application_id = fat2.application_id)
                WHEN frgu.request_unit_type = 'A' THEN
                (SELECT fat.application_name FROM fnd_application_tl fat WHERE fat.application_id = frgu.request_unit_id)
           END name
         , DECODE(frgu.request_unit_type, 'P', 'Program', 'S', 'Request Set', 'A', 'Application') type
         , CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcp.creation_date FROM fnd_concurrent_programs_tl fcp WHERE fcp.concurrent_program_id = frgu.request_unit_id AND fcp.application_id = fat2.application_id)
           END job_created
         , CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcp.last_update_date FROM fnd_concurrent_programs_tl fcp WHERE fcp.concurrent_program_id = frgu.request_unit_id AND fcp.application_id = fat2.application_id)
           END job_updated
         , CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcpt.enabled_flag FROM fnd_concurrent_programs fcpt WHERE fcpt.concurrent_program_id = frgu.request_unit_id AND fcpt.application_id = fat2.application_id)
           END job_enabled
         , CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcpt.concurrent_program_id FROM fnd_concurrent_programs fcpt WHERE fcpt.concurrent_program_id = frgu.request_unit_id AND fcpt.application_id = fat2.application_id)
           END job_id
         , CASE WHEN frgu.request_unit_type = 'S' THEN
                (SELECT frs.end_date_active FROM fnd_request_sets frs WHERE frs.request_set_id = frgu.request_unit_id AND frs.application_id = fat2.application_id)
           END request_set_end_date
         , CASE WHEN frgu.request_unit_type = 'S' THEN
                (SELECT frs.request_set_id FROM fnd_request_sets frs WHERE frs.request_set_id = frgu.request_unit_id AND frs.application_id = fat2.application_id)
           END request_set_id
         , fat2.application_name application
      FROM fnd_request_groups frg
      JOIN fnd_request_group_units frgu ON frg.application_id =       frgu.application_id AND frg.request_group_id =     frgu.request_group_id
      JOIN fnd_application_tl fat1      ON frg.application_id =       fat1.application_id
      JOIN fnd_application_tl fat2      ON frgu.unit_application_id = fat2.application_id
     WHERE 1 = 1
       AND frg.request_group_name LIKE 'Request Group 1'
--       AND frg.request_group_name LIKE 'Request Group 2'
       AND 1 = 1
minus
    SELECT CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcp.user_concurrent_program_name FROM fnd_concurrent_programs_tl fcp WHERE fcp.concurrent_program_id = frgu.request_unit_id AND fcp.application_id = fat2.application_id)
                WHEN frgu.request_unit_type = 'S' THEN
                (SELECT frst.user_request_set_name FROM fnd_request_sets_tl frst WHERE frst.request_set_id = frgu.request_unit_id AND frst.application_id = fat2.application_id)
                WHEN frgu.request_unit_type = 'A' THEN
                (SELECT fat.application_name FROM fnd_application_tl fat WHERE fat.application_id = frgu.request_unit_id)
           END name
         , DECODE(frgu.request_unit_type, 'P', 'Program', 'S', 'Request Set', 'A', 'Application') type
         , CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcp.creation_date FROM fnd_concurrent_programs_tl fcp WHERE fcp.concurrent_program_id = frgu.request_unit_id AND fcp.application_id = fat2.application_id)
           END job_created
         , CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcp.last_update_date FROM fnd_concurrent_programs_tl fcp WHERE fcp.concurrent_program_id = frgu.request_unit_id AND fcp.application_id = fat2.application_id)
           END job_updated
         , CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcpt.enabled_flag FROM fnd_concurrent_programs fcpt WHERE fcpt.concurrent_program_id = frgu.request_unit_id AND fcpt.application_id = fat2.application_id)
           END job_enabled
         , CASE WHEN frgu.request_unit_type = 'P' THEN
                (SELECT fcpt.concurrent_program_id FROM fnd_concurrent_programs fcpt WHERE fcpt.concurrent_program_id = frgu.request_unit_id AND fcpt.application_id = fat2.application_id)
           END job_id
         , CASE WHEN frgu.request_unit_type = 'S' THEN
                (SELECT frs.end_date_active FROM fnd_request_sets frs WHERE frs.request_set_id = frgu.request_unit_id AND frs.application_id = fat2.application_id)
           END request_set_end_date
         , CASE WHEN frgu.request_unit_type = 'S' THEN
                (SELECT frs.request_set_id FROM fnd_request_sets frs WHERE frs.request_set_id = frgu.request_unit_id AND frs.application_id = fat2.application_id)
           END request_set_id
         , fat2.application_name application
      FROM fnd_request_groups frg
      JOIN fnd_request_group_units frgu ON frg.application_id =       frgu.application_id AND frg.request_group_id =     frgu.request_group_id
      JOIN fnd_application_tl fat1      ON frg.application_id =       fat1.application_id
      JOIN fnd_application_tl fat2      ON frgu.unit_application_id = fat2.application_id
     WHERE 1 = 1
--       AND frg.request_group_name LIKE 'Request Group 1'
       AND frg.request_group_name LIKE 'Request Group 2'
       AND 1 = 1;

Extracting Menu Structures using SQL

Menus that are assigned to Oracle EBS responsibilities are often nested structures which contain lots of levels. The SQLs on this page explore that a bit.

Here are some basic details about menus assigned to responsibilities:

-- ##############################################################################
--      RESPONSIBILITIES ASSIGNED TO MENUS 
-- ##############################################################################

    SELECT fat.application_name
         , fa.application_short_name
         , frt.responsibility_id
         , frt.responsibility_name
         , fmt.user_menu_name
      FROM applsys.fnd_responsibility fr
      JOIN applsys.fnd_responsibility_tl frt ON fr.application_id =    frt.application_id 
      JOIN applsys.fnd_menus_tl fmt          ON fr.responsibility_id = frt.responsibility_id AND fr.menu_id = fmt.menu_id
      JOIN applsys.fnd_application fa        ON frt.application_id =   fa.application_id 
      JOIN applsys.fnd_application_tl fat    ON fr.application_id =    fa.application_id AND fa.application_id = fat.application_id 
     WHERE 1 = 1
       AND NVL(fr.end_date, SYSDATE + 1) > SYSDATE -- responsibility is active
       AND fmt.user_menu_name = 'PO_NAVIGATION'
  ORDER BY fa.application_short_name
         , frt.responsibility_name;

-- ##############################################################################
--       COUNT OF RESPONSIBILITIES ASSIGNED TO A MENU
-- ##############################################################################

SELECT fmt.user_menu_name
     , COUNT(DISTINCT frt.responsibility_id) count
  FROM applsys.fnd_responsibility fr
  JOIN applsys.fnd_responsibility_tl frt ON fr.responsibility_id = frt.responsibility_id AND fr.application_id = frt.application_id 
  JOIN applsys.fnd_menus_tl fmt ON fr.menu_id = fmt.menu_id
 WHERE fmt.user_menu_name = 'INV_NAVIGATE'
GROUP BY fmt.user_menu_name;

This SQL is useful because you can enter in the Menu Name, and the SQL will export the menu structure, showing menus, functions and prompts. The CONNECT BY is used to extract the menu levels / hierarchy.

-- ##############################################################################
--      MENU TREE VIEW
-- ##############################################################################
           SELECT fmev.entry_sequence seq
                , LEVEL
                , LPAD (' ', (LEVEL - 1) * 3, ' ') || fmv.user_menu_name menu
                , LPAD (' ', (LEVEL - 1) * 3, ' ') || fmev.prompt prompt
                , fmv.menu_name
                , fmv.user_menu_name
                , CASE
                     WHEN fmev.function_id IS NOT NULL
                     THEN
                        (SELECT user_function_name
                           FROM apps.fnd_form_functions_vl
                          WHERE function_id = fmev.function_id)
                  END user_function_name
                , CASE
                     WHEN fmev.function_id IS NOT NULL
                     THEN
                        (SELECT function_name
                           FROM apps.fnd_form_functions_vl
                          WHERE function_id = fmev.function_id)
                  END function_name
                , CASE
                     WHEN fmev.function_id IS NOT NULL
                     THEN
                        (SELECT vvv.description
                           FROM apps.fnd_form_functions_vl vvv
                          WHERE function_id = fmev.function_id)
                  END function_description
                , fmev.description entry_description
             FROM apps.fnd_menus_vl fmv
             JOIN apps.fnd_menu_entries_vl fmev ON fmev.menu_id = fmv.menu_id
            WHERE 1 = 1
              AND fmev.prompt IS NOT NULL
--              AND LEVEL = 1
       CONNECT BY fmev.menu_id = PRIOR fmev.sub_menu_id
       START WITH fmv.user_menu_name = 'PO_NAVIGATION'
ORDER SIBLINGS BY fmev.entry_sequence;

If you want a “flat” view of a menu, with just the top level menu / function details as you see on the SysAdmin Menu form, this can be used.

-- ##############################################################################
--      MENU FLAT VIEW
-- ##############################################################################
SELECT fmv.menu_name
     , fmev.entry_sequence seq
     , fmev.prompt
     , CASE WHEN fmev.sub_menu_id IS NOT NULL THEN (SELECT user_menu_name FROM apps.fnd_menus_vl e WHERE e.menu_id = fmev.sub_menu_id) END submenu                   
     , CASE WHEN fmev.function_id IS NOT NULL THEN (SELECT user_function_name FROM apps.fnd_form_functions_vl WHERE function_id = fmev.function_id) END user_fcn_name
     , fmev.description
  FROM apps.fnd_menus_vl fmv
  JOIN apps.fnd_menu_entries_vl fmev ON fmev.menu_id = fmv.menu_id
   AND user_menu_name = 'PO_NAVIGATION'
   AND 1 = 1;

Here are some more variations, looking only at menus instead of menus and functions together.

-- ##############################################################################
--      MENU TREE WALK EXCLUDING FUNCTIONS
-- ##############################################################################

-- v1

    SELECT LPAD(' ', (LEVEL - 1) * 3, ' ') || fmev.prompt prompt
         , LEVEL
         , fmev.entry_sequence
      FROM apps.fnd_menus_vl fmv
      JOIN apps.fnd_menu_entries_vl fmev ON fmev.menu_id = fmv.menu_id
       AND fmev.prompt IS NOT NULL
CONNECT BY fmev.menu_id = PRIOR fmev.sub_menu_id      
START WITH fmv.menu_name = 'INV_NAVIGATE'
ORDER SIBLINGS BY fmev.entry_sequence ;

-- v2

    SELECT LPAD(' ', (LEVEL - 1) * 3, ' ') || fmev.prompt prompt
         , LEVEL
         , fmev.entry_sequence
         , fmt.user_menu_name
         , fmev.creation_date
         , fu.user_name
      FROM apps.fnd_menus_vl fmv
      JOIN apps.fnd_menu_entries_vl fmev ON fmev.menu_id = fmv.menu_id
 LEFT JOIN applsys.fnd_menus_tl fmt ON fmv.menu_id = fmt.menu_id 
      JOIN applsys.fnd_user fu ON fmev.created_by = fu.user_id
     wHERE fmev.prompt IS NOT NULL
CONNECT BY fmev.menu_id = PRIOR fmev.sub_menu_id      
START WITH fmt.user_menu_name = 'INV_NAVIGATE'
ORDER SIBLINGS BY fmev.entry_sequence;

Here are lots more menu tree walking examples.

-- ##############################################################################
--      MORE MENU TREE WALKING EXAMPLES
-- ##############################################################################

-- v1

    SELECT fmev.entry_sequence seq
         , LEVEL
         , LPAD(' ', (LEVEL - 1) * 4, ' ') || fmev.prompt prompt
      FROM apps.fnd_menus_vl fmv
                 JOIN apps.fnd_menu_entries_vl fmev ON fmev.menu_id          = fmv.menu_id
           LEFT JOIN apps.fnd_responsibility_vl frv ON frv.menu_id           = fmv.menu_id
           LEFT JOIN apps.fnd_menus_vl fmv2         ON fmv2.menu_id          = fmev.sub_menu_id
           LEFT JOIN apps.fnd_resp_functions frf    ON frf.responsibility_id = frv.responsibility_id AND frf.action_id = fmev.sub_menu_id
     WHERE fmev.prompt IS NOT NULL
       AND frf.responsibility_id IS NULL
CONNECT BY fmev.menu_id = PRIOR fmev.sub_menu_id
       AND fmev.prompt IS NOT NULL
       AND frf.responsibility_id IS NULL
START WITH frv.responsibility_name = 'System Administrator'
ORDER SIBLINGS BY fmev.entry_sequence;

-- v2

    SELECT fmev.entry_sequence seq
         , LPAD(' ', (LEVEL - 1) * 4, ' ') || fmev.prompt prompt
         , fmev.menu_id
         , fmev.sub_menu_id         
         , fmev.function_id
      FROM apps.fnd_menus_vl fmv
      JOIN applsys.fnd_menus_tl fmt ON fmv.menu_id = fmt.menu_id
      JOIN apps.fnd_menu_entries_vl fmev ON fmev.menu_id = fmv.menu_id
 LEFT JOIN apps.fnd_responsibility_vl frv ON fmv.menu_id = frv.menu_id
     WHERE fmev.prompt IS NOT NULL
CONNECT BY fmev.menu_id = PRIOR fmev.sub_menu_id
       AND fmev.prompt IS NOT NULL
START WITH fmt.user_menu_name = 'INV_NAVIGATE'
ORDER SIBLINGS BY fmev.entry_sequence;
  
-- v3

      SELECT fmev.entry_sequence seq
           , LPAD(' ', (LEVEL - 1) * 10, ' ') || fmev.prompt prompt
           , fmv.menu_name
           , CASE
                 WHEN fmev.function_id IS NOT NULL
                    THEN 'Function'
                 ELSE 'Menu'
              END m_f        
           , CASE
                 WHEN fmev.function_id IS NOT NULL
                    THEN (SELECT 'Function: ' || function_name
                            FROM apps.fnd_form_functions_vl
                           WHERE function_id = fmev.function_id)
                 ELSE (SELECT 'Menu: ' || menu_name
                         FROM apps.fnd_menus_vl e
                        WHERE e.menu_id = fmev.sub_menu_id)
              END this
      FROM apps.fnd_menus_vl fmv
      JOIN apps.fnd_menu_entries_vl fmev ON fmev.menu_id = fmv.menu_id
CONNECT BY fmev.menu_id = PRIOR fmev.sub_menu_id      
START WITH fmv.user_menu_name = 'CST_NAVIGATE'
ORDER SIBLINGS BY fmev.entry_sequence;

-- v4
-- https://community.oracle.com/thread/535595

    SELECT LEVEL
         , fm.menu_id
         , fm.menu_name
         , fm.user_menu_name
         , fme.sub_menu_id
         , fme.function_id
         , fff.function_name
         , fff.user_function_name
      FROM apps.fnd_menus_vl fm
         , apps.fnd_menu_entries fme
         , apps.fnd_form_functions_vl fff
     WHERE fme.menu_id = fm.menu_id
       AND fff.function_id(+) = fme.function_id
CONNECT BY fm.menu_id = PRIOR fme.sub_menu_id
START WITH fm.user_menu_name = 'INV_NAVIGATE';

-- v5
-- https://community.oracle.com/thread/1057001

       SELECT LPAD(' ',(LEVEL - 1) * 20)
           || TRIM(user_menu_name)
           || ' -> '
           || CASE
                 WHEN me.function_id IS NOT NULL
                    THEN (SELECT user_function_name
                            FROM apps.fnd_form_functions_vl
                           WHERE function_id = me.function_id)
                 ELSE (SELECT user_menu_name
                         FROM apps.fnd_menus_vl e
                        WHERE e.menu_id = me.sub_menu_id)
              END nam
      FROM apps.fnd_menus_vl fm
         , apps.fnd_menu_entries me
     WHERE me.menu_id = fm.menu_id
START WITH fm.user_menu_name = 'INV_NAVIGATE'
CONNECT BY PRIOR me.sub_menu_id = fm.menu_id;

-- v6
-- another one

    SELECT m.menu_name
         , m.user_menu_name
         , m.sub_menu
         , LEVEL
         , SYS_CONNECT_BY_PATH(m.sub_menu, '/') PATH
      FROM (SELECT fmv.menu_name
                 , fmv.user_menu_name
                 , fmv.user_menu_name sub_menu
                 , fmev.sub_menu_id
                 , fmv.menu_id
              FROM apps.fnd_menu_entries_vl fmev
                 , apps.fnd_menus_vl fmv
             WHERE fmv.menu_id = fmev.menu_id) m
CONNECT BY PRIOR sub_menu_id = menu_id
START WITH user_menu_name = 'INV_NAVIGATE';

Responsibilities – Exclusions

Each responsibility is linked to a menu. If you have 2 responsibilities linked to the same menu, you can limit what specific parts of the menu each responsibility has access to by using exclusions.

You can decide to restrict functions, or menus… but getting that data out in a meaninful way can be a bit limited. The main limitation with using the responsibility form is that you can’t search for items in it. For example, you might want to check if a responsibility has a specific exclusion against it. If you click into the exclusions, and press F11, enter something to search for, and press CTRL + F11, the search doesn’t work, it just returns everything.

This SQL can be handy when you want to search for specific exclusions, or just want to export everything at once.

Simple Exclusions List

  SELECT frt.responsibility_name
       , frf.creation_date
       , frf.created_by
       , DECODE (frf.rule_type,  'M', 'Menu',  'F', 'Function') type_
       , CASE
            WHEN frf.rule_type = 'M'
            THEN
               (SELECT fmv.user_menu_name
                  FROM apps.fnd_menus_vl fmv
                 WHERE frf.action_id = fmv.menu_id
                   AND frf.rule_type = 'M')
            WHEN frf.rule_type = 'F'
            THEN
               (SELECT ffvl.user_function_name
                  FROM apps.fnd_form_functions_vl ffvl
                 WHERE frf.action_id = ffvl.function_id
                   AND frf.rule_type = 'F')
         END
            detail
       , CASE
            WHEN frf.rule_type = 'M'
            THEN
               (SELECT fmv.menu_name
                  FROM apps.fnd_menus fmv
                 WHERE frf.action_id = fmv.menu_id
                   AND frf.rule_type = 'M')
            WHEN frf.rule_type = 'F'
            THEN
               (SELECT ffvl.function_name
                  FROM apps.fnd_form_functions ffvl
                 WHERE frf.action_id = ffvl.function_id
                   AND frf.rule_type = 'F')
         END
            detail2
    FROM apps.fnd_resp_functions frf
       , applsys.fnd_responsibility_tl frt
       , applsys.fnd_user fu
   WHERE frf.responsibility_id = frt.responsibility_id
     AND frf.created_by = fu.user_id
     AND frt.responsibility_name LIKE '%General%Ledger%';

All Exclusions On Responsibilities, Another Way To Get Info Using A Union

SELECT frt.responsibility_name
     , fmv.menu_name
     , fmv.user_menu_name menu
     , fmv.description menu_desc
     , '' fcn_name
     , '' fcn
     , '' fcn_desc
  FROM apps.fnd_resp_functions frf
     , applsys.fnd_responsibility_tl frt
     , apps.fnd_menus_vl fmv
 WHERE frf.responsibility_id = frt.responsibility_id
   AND frf.action_id = fmv.menu_id
   AND frf.rule_type = 'M'
   AND frt.responsibility_name = 'AP Inquiry'
UNION
SELECT frt.responsibility_name
     , ffvl.function_name
     , '' menu
     , '' menu_desc
     , ffvl.function_name fcn_name
     , ffvl.user_function_name fcn
     , ffvl.description fcn_desc
  FROM apps.fnd_resp_functions frf
     , applsys.fnd_responsibility_tl frt
     , apps.fnd_form_functions_vl ffvl
 WHERE frf.responsibility_id = frt.responsibility_id
   AND frf.action_id = ffvl.function_id
   AND frf.rule_type = 'F'
   AND frt.responsibility_name = 'AP Inquiry';

All Exclusions On Responsibilities With Resp Assigned Stats

SELECT frt.responsibility_name
     , (SELECT DISTINCT COUNT (*) 
	FROM applsys.fnd_user fu, apps.fnd_user_resp_groups_direct furg 
	WHERE furg.user_id = fu.user_id AND frt.responsibility_id = furg.responsibility_id 
	AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE 
	AND NVL (fu.end_date, SYSDATE + 1) > SYSDATE) user_ct
     , fmv.menu_name
     , fmv.user_menu_name menu
     , fmv.description menu_desc
     , '' fcn_name
     , '' fcn
     , '' fcn_desc
  FROM apps.fnd_resp_functions frf
     , applsys.fnd_responsibility_tl frt
     , apps.fnd_menus_vl fmv
 WHERE frf.responsibility_id = frt.responsibility_id
   AND frf.action_id = fmv.menu_id
   AND frf.rule_type = 'M'
--   AND frt.responsibility_name = 'AP Inquiry'
   AND (SELECT DISTINCT COUNT (*) 
	FROM applsys.fnd_user fu, apps.fnd_user_resp_groups_direct furg 
	WHERE furg.user_id = fu.user_id AND frt.responsibility_id = furg.responsibility_id 
	AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE 
	AND NVL (fu.end_date, SYSDATE + 1) > SYSDATE) > 0
UNION
SELECT frt.responsibility_name
     , (SELECT DISTINCT COUNT (*) 
	FROM applsys.fnd_user fu, apps.fnd_user_resp_groups_direct furg 
	WHERE furg.user_id = fu.user_id 
	AND frt.responsibility_id = furg.responsibility_id 
	AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE 
	AND NVL (fu.end_date, SYSDATE + 1) > SYSDATE) user_ct
     , ffvl.function_name
     , '' menu
     , '' menu_desc
     , ffvl.function_name fcn_name
     , ffvl.user_function_name fcn
     , ffvl.description fcn_desc
  FROM apps.fnd_resp_functions frf
     , applsys.fnd_responsibility_tl frt
     , apps.fnd_form_functions_vl ffvl
 WHERE frf.responsibility_id = frt.responsibility_id
   AND frf.action_id = ffvl.function_id
   AND frf.rule_type = 'F'
--   AND frt.responsibility_name = 'AP Inquiry'
   AND (SELECT DISTINCT COUNT (*) 
	FROM applsys.fnd_user fu, apps.fnd_user_resp_groups_direct furg 
	WHERE furg.user_id = fu.user_id AND frt.responsibility_id = furg.responsibility_id 
	AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE 
	AND NVL (fu.end_date, SYSDATE + 1) > SYSDATE) > 0;

Menu Exclusions On Responsibilities

SELECT frt.responsibility_name
     , fmv.menu_name
     , fmv.user_menu_name ex_name
     , fmv.description menu_description
  FROM apps.fnd_resp_functions frf
     , applsys.fnd_responsibility_tl frt
     , apps.fnd_menus_vl fmv
 WHERE frf.responsibility_id = frt.responsibility_id
   AND frf.action_id = fmv.menu_id
   AND frf.rule_type = 'M'
   AND frt.responsibility_name = 'Inventory';

Function Exclusions On Responsibilities

SELECT frt.responsibility_name
     , ffvl.function_name
     , ffvl.user_function_name
     , ffvl.description function_description
  FROM apps.fnd_resp_functions frf
     , applsys.fnd_responsibility_tl frt
     , apps.fnd_form_functions_vl ffvl
 WHERE frf.responsibility_id = frt.responsibility_id
   AND frf.action_id = ffvl.function_id
   AND frf.rule_type = 'F'
   AND ffvl.function_name = 'INV_INVTTMTX_MISC'
--   AND frt.responsibility_name = 'Inventory'
   AND 1 = 1;