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 Requests 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

Request Group Details

-- ##############################################################################
--       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
         , 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 requests, 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 Requests and Whether Assigned to a Request Group

-- ##############################################################################
--        CONCURRENT REQUESTS 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;

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;

Lookup Values

-- ##############################################################################
--      LOOKUPS
-- ##############################################################################

     select flt.lookup_type
          , fltt.description
          , fat.application_name app
          , flv.lookup_code
          , flv.meaning
          , flv.enabled_flag
          , TO_CHAR(start_date_active, 'DD-MON-RRRR') from_
          , TO_CHAR(end_date_active, 'DD-MON-RRRR') to_
          , flv.creation_date
          , fu_cr.user_name cr_by
          , flv.last_update_date
          , fu_up.user_name up_by
       from fnd_lookup_types flt 
       JOIN fnd_lookup_types_tl fltt ON flt.lookup_type = fltt.lookup_type
       JOIN fnd_application_tl fat on flt.application_id = fat.application_id
       JOIN fnd_lookup_values flv ON flv.lookup_type = flt.lookup_type
       JOIN fnd_user fu_cr ON flv.created_by = fu_cr.user_id
       JOIN fnd_user fu_up ON flv.last_updated_by = fu_up.user_id
      WHERE 1 = 1
        AND flt.LOOKUP_TYPE = 'BOB_HOPE_JOKES'
        AND 1 = 1;

User Logins

If a user is end-dated on a test system, and you remove the end-date from their account but don’t reset their password, you will show as the person who last updated the record even if that user logs in.

If you reset a user’s password, then when they log in they have to choose a new password. They then show as the last person to update their FND user account.

-- user, login details, login count

select fu.user_name
     , (select max(start_time) from applsys.fnd_logins fl where fl.user_id = fu.user_id) last_login
     , (select count(*) from applsys.fnd_logins fl where fl.user_id = fu.user_id) login_count
     , (select count(*) from applsys.fnd_logins fl where fl.user_id = fu.user_id AND start_time > TRUNC(SYSDATE)) login_count_today 
  from applsys.fnd_user fu
 where 1 = 1
--   AND fu.user_name IN ('BOBHOPE')
   AND NVL(fu.end_date, SYSDATE + 1) > SYSDATE
   ORDER BY 3 DESC;

-- login details line level

SELECT fu.description
     , fu.user_name
     , fl.start_time
     , fl.*
  FROM applsys.fnd_logins fl
  JOIN applsys.fnd_user fu ON fl.user_id = fu.user_id 
 WHERE 1 = 1
   AND fl.login_type = 'FORM'
   AND fl.user_id > 0 -- NOT SYSADMIN
--   AND fu.user_name = 'BOBHOPE'
   AND fl.start_time > TRUNC (SYSDATE) - 1
   AND 1 = 1
ORDER BY fl.start_time DESC;

-- logins last x days

SELECT fu.description
     , fu.user_name
     , count(*) ct
  FROM applsys.fnd_logins fl
  JOIN applsys.fnd_user fu ON fl.user_id = fu.user_id
 WHERE 1 = 1
   AND fl.start_time > TRUNC (SYSDATE) - 5
   AND fl.login_type = 'FORM'
   AND fl.user_id > 0 -- NOT SYSADMIN
--   AND fu.user_name = 'BOBHOPE'
   AND 1 = 1
GROUP BY fu.description
     , fu.user_name
ORDER BY 3 DESC;

-- unsuccessful logins

SELECT fu.description
     , fu.user_name
     , ful.*
  FROM applsys.fnd_unsuccessful_logins ful
  JOIN applsys.fnd_user fu ON ful.user_id = fu.user_id 
 WHERE 1 = 1
--   AND fu.user_name = 'BOBHOPE'
ORDER BY ful.attempt_time DESC;


HR Locations

-- ##############################################################################
--        LOCATIONS BASIC DETAILS
-- ##############################################################################

      SELECT bus_gp.NAME bus_gp
           , inv_org.NAME inv_org
           , hla.location_code
           , hla.description
           , hla.inactive_date
           , hla.address_line_1
           , hla.address_line_2
           , hla.address_line_3
           , hla.town_or_city
           , hla.postal_code
        FROM hr.hr_locations_all hla
   LEFT JOIN hr.hr_all_organization_units_tl bus_gp ON hla.business_group_id = bus_gp.organization_id
   LEFT JOIN hr.hr_all_organization_units_tl inv_org ON hla.inventory_organization_id = inv_org.organization_id
        JOIN applsys.fnd_user fu ON hla.created_by = fu.user_id;

-- ##############################################################################
--      LOCATIONS - HR STAFF ASSIGNMENT LINKS
-- ##############################################################################

-- count

      SELECT hla.location_code
           , hla.description description
           , COUNT(*) assignment_count
        FROM hr.hr_locations_all hla
        JOIN hr.per_all_assignments_f paaf ON hla.location_id = paaf.location_id
        JOIN hr.per_all_people_f papf ON paaf.person_id = papf.person_id AND (SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date) AND (SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date)
       WHERE paaf.primary_flag = 'Y'
         AND paaf.assignment_type = 'E'
    GROUP BY hla.description
           , hla.location_code
    ORDER BY 3 DESC;

-- details

      SELECT papf.employee_number
           , papf.full_name
           , paaf.assignment_number
           , hla.location_code
           , hla.description description
        FROM hr.hr_locations_all hla
        JOIN hr.per_all_assignments_f paaf ON hla.location_id = paaf.location_id
        JOIN hr.per_all_people_f papf ON paaf.person_id = papf.person_id AND (SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date) AND (SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date)
       WHERE paaf.primary_flag = 'Y'
         AND paaf.assignment_type = 'E'
    ORDER BY 3 DESC;

-- ##############################################################################
--        LOCATIONS - HR ORG LINKS
-- ##############################################################################

-- count

      SELECT hla.location_code
           , hla.description description
           , COUNT(*) ct
        FROM hr.hr_locations_all hla
        JOIN hr.hr_all_organization_units haou ON haou.location_id = hla.location_id
    GROUP BY hla.description
           , hla.location_code
    ORDER BY 3 DESC;

-- details

      SELECT haou.name hr_org
           , hla.location_code
           , hla.description description
        FROM hr.hr_locations_all hla
        JOIN hr.hr_all_organization_units haou ON haou.location_id = hla.location_id;