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;

Purchasing – UAT Closed Period Data Collection

When carrying out UAT testing, there is often a need to include changing a requisition raised in an old, closed purchasing period, so you can test the update process.

In order to do that, you need to be able to identify open POs that have not been fully matched and closed.

This SQL will do that – it finds open POs and counts receipts and invoices raised against the PO.

A simple test is to use open POs with no invoices against them, but it’s useful to also check against some that have been receipted and invoiced.

SELECT DISTINCT pha.segment1 po_num
              , pav.agent_name buyer
              , haout2.NAME buyer_hr_org
              , fu2.description
              , fu2.user_name
              , '--------------------'
              , prha.segment1 req_num
              , prha.creation_date
              , '--------------------'
              , (SELECT COUNT(DISTINCT receipt_num)
                   FROM po.rcv_shipment_lines rsl
                      , po.rcv_shipment_headers rsh
                  WHERE rsl.shipment_header_id = rsh.shipment_header_id
                    AND pha.po_header_id = rsl.po_header_id) rx_ct
              -- count of receipts
,               (SELECT   COUNT(DISTINCT aia.invoice_num)
                     FROM ap.ap_invoices_all aia
                        , ap.ap_invoice_distributions_all aida
                        , po.po_distributions_all pda
                    WHERE aia.invoice_id = aida.invoice_id
                      AND aida.po_distribution_id = pda.po_distribution_id
                      AND pda.po_header_id = pha.po_header_id
                 GROUP BY pha.segment1) inv_ct                -- invoice count
              , (SELECT   SUM(pla.unit_price * pla.quantity)
                     FROM po.po_lines_all pla
                    WHERE pla.po_header_id = pha.po_header_id
                 GROUP BY pha.po_header_id) amount
              , (SELECT   SUM(aida.amount)
                     FROM ap.ap_invoices_all aia
                        , ap.ap_invoice_distributions_all aida
                        , po.po_distributions_all pda
                    WHERE aia.invoice_id = aida.invoice_id
                      AND aida.po_distribution_id = pda.po_distribution_id
                      AND pda.po_header_id = pha.po_header_id
                 GROUP BY pha.segment1) matched_amt
              , '--------------------'
              , papf.full_name req_prepaper
              , haout.NAME hr_org
              , fu.description
              , fu.user_name
              , '--------------------'
              , prla.suggested_vendor_name supplier
              , prla.suggested_vendor_location site
           FROM po.po_requisition_headers_all prha
              , po.po_requisition_lines_all prla
              , po.po_line_locations_all plla
              , po.po_lines_all pla
              , po.po_headers_all pha
              , hr.per_all_people_f papf
              , hr.per_all_people_f papf2
              , hr.per_all_assignments_f paaf
              , hr.per_all_assignments_f paaf2
              , hr.hr_all_organization_units_tl haout
              , hr.hr_all_organization_units_tl haout2
              , apps.po_agents_v pav
              , applsys.fnd_user fu
              , applsys.fnd_user fu2
          WHERE prha.requisition_header_id = prla.requisition_header_id
            AND plla.line_location_id = prla.line_location_id
            AND pla.po_line_id = plla.po_line_id
            AND prha.preparer_id = papf.person_id
            AND pha.po_header_id = plla.po_header_id
            AND pha.agent_id = pav.agent_id
            AND haout.organization_id = paaf.organization_id
            AND haout2.organization_id = paaf2.organization_id
            AND paaf.person_id = papf.person_id
            AND paaf2.person_id = papf2.person_id
            AND pav.agent_id = papf2.person_id
            AND papf.person_id = fu.employee_id
            AND pha.created_by = fu2.user_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
            AND SYSDATE BETWEEN papf2.effective_start_date AND NVL(papf2.effective_end_date, SYSDATE + 1)
            AND SYSDATE BETWEEN paaf2.effective_start_date AND NVL(paaf2.effective_end_date, SYSDATE + 1)
            AND paaf.primary_flag = 'Y'
            AND paaf.assignment_type = 'E'
            AND paaf2.primary_flag = 'Y'
            AND paaf2.assignment_type = 'E'
            AND prha.authorization_status = 'APPROVED'
            AND pha.authorization_status = 'APPROVED'
            AND LOWER(pha.closed_code) = 'open'
            AND prha.creation_date BETWEEN '20-MAY-2008' AND '16-JUN-2008'
       ORDER BY prha.segment1 DESC;

Purchasing – Information Templates – SQL

As covered in previous posts information templates and linking an information template to a List of Values, it is possible to use Information Templates as follows:

Information templates are additional information templates that a requester in iProcurement completes for certain items. They allow us to set up templates to cover common forms of purchasing (e.g. Agency Staff), so that when a user in iProcurement raises a requisition for Agency Staff, they are forced to complete additional fields via an Information Template.

You can access the details behind Information Templates using SQL – probably the thing you’ll want to do is to get at the data users have entered into the various Information Template fields for reporting purposes.

Sections:

  1. Smart Form Headers
  2. Smart Form Fields
  3. Header and Fields Joined
  4. Including Smart Form Data Entered Against A Requisition

Smart Form Headers

-- holds the smart form headers
SELECT * FROM icx.por_templates_all_tl;

Smart Form Fields

-- holds the smart form fields
SELECT * FROM icx.por_template_attributes_tl;

Header and Fields Joined

-- header and fields together

SELECT   ptat2.template_code 
       , ptat2.template_name
       , ptat.attribute_code
       , ptat.attribute_name
       , ptat.description
       , ptat.creation_date
       , ptab.node_display_flag displayed
    FROM icx.por_template_attributes_tl ptat
       , icx.por_template_attributes_b ptab
       , icx.por_templates_all_tl ptat2
       , applsys.fnd_user fu
   WHERE ptat.attribute_code = ptab.attribute_code
     AND ptab.template_code = ptat2.template_code
     AND ptat.created_by = fu.user_id
	 AND template_name = 'CHEESE_TEMPLATE'
ORDER BY ptat.creation_date DESC;

Using the “Cheese Template” analogy used in the Information Template links at the top of this page, we have an Information Template like this:

The corresponding SQL returns this data:

Including Smart Form Data Entered Against A Requisition

Ultimately you might want to get a list of requisition lines, and link through to find out the values entered into the smart form fields for each line.

To do that, each smart form field is included in a sub-select – e.g.

, (SELECT b.requisition_line_id
      , RTRIM(a.description) col
      , RTRIM(b.attribute_value) val
   FROM icx.por_template_attributes_tl a
      , icx.por_template_info b
  WHERE b.attribute_code = a.attribute_code
    AND a.attribute_code = 'IFT_22') line1

You then join that to the requisition line using the requisition_line_id

    --basic report showing how to pull in smart form field data from requisition lines
    SELECT prha.segment1 req_num
         , prla.line_num
         , prha.creation_date
         , prha.segment1
         , prla.item_description
         , prla.quantity
         , prla.unit_price
         , line1.val "Type of Cheese"
         , line2.val "Size of Cheese"
         , line3.val "Colour of Cheese"
         , line4.val "Weight of Cheese"
         , line5.val "Cheese Smell Strength"
      FROM po.po_requisition_headers_all prha
      JOIN po.po_requisition_lines_all prla ON prha.requisition_header_id = prla.requisition_header_id
 LEFT JOIN (SELECT b.requisition_line_id, RTRIM(a.description) col, RTRIM(b.attribute_value) val FROM icx.por_template_attributes_tl a, icx.por_template_info b WHERE b.attribute_code = a.attribute_code AND a.attribute_code = 'IFT_22') line1 ON prla.requisition_line_id = line1.requisition_line_id 
 LEFT JOIN (SELECT b.requisition_line_id, RTRIM(a.description) col, RTRIM(b.attribute_value) val FROM icx.por_template_attributes_tl a, icx.por_template_info b WHERE b.attribute_code = a.attribute_code AND a.attribute_code = 'IFT_23') line2 ON prla.requisition_line_id = line2.requisition_line_id
 LEFT JOIN (SELECT b.requisition_line_id, RTRIM(a.description) col, RTRIM(b.attribute_value) val FROM icx.por_template_attributes_tl a, icx.por_template_info b WHERE b.attribute_code = a.attribute_code AND a.attribute_code = 'IFT_24') line3 ON prla.requisition_line_id = line3.requisition_line_id
 LEFT JOIN (SELECT b.requisition_line_id, RTRIM(a.description) col, RTRIM(b.attribute_value) val FROM icx.por_template_attributes_tl a, icx.por_template_info b WHERE b.attribute_code = a.attribute_code AND a.attribute_code = 'IFT_25') line4 ON prla.requisition_line_id = line4.requisition_line_id
 LEFT JOIN (SELECT b.requisition_line_id, RTRIM(a.description) col, RTRIM(b.attribute_value) val FROM icx.por_template_attributes_tl a, icx.por_template_info b WHERE b.attribute_code = a.attribute_code AND a.attribute_code = 'IFT_26') line5 ON prla.requisition_line_id = line5.requisition_line_id 
     WHERE 1 = 1
--       AND prha.segment1 IN ('1232597','1232598','1232599')
       AND prha.creation_date > '01-MAY-2016'
       AND 1 = 1;

Example data returned:

Purchasing – Information Templates – List of Values

When setting up an Information Template you may wish to allow users to pick options from a drop-down list. Oracle called these “LOV”s – which stands for “LIST OF VALUES”.

Here is an example Information Template:

You can see there is a “LOV” column. This allows you to link the field on your Information Template to an LOV.

Setting up a Value Set

In the example above, lets say we want to set up an LOV for “Cheese Smell Strength” with the following options:

  1. Pleasant
  2. Greasy
  3. Sharp
  4. Offensive
  5. Old Socks

To do this, go to Purchasing Superuser and navigate to Setup > Flexfields > Validation > Sets.

In the “Value Sets” form, enter the following:

  1. Value Set Name: e.g. CHEESE_LOV
  2. Maximum Size – state the maximum character width of your LOV entries – e.g. 10

Fill in the form:

Save changes

Populate the Value Set with Values

Now to populate the LOV with some entries.

Purchasing Superuser > Setup > Flexfields > Validation > Values. When the “Find Value Set” form opens, enter the LOV name in the “Name” field and press “Find”.

In the Value and Description fields, enter the required information. The “Value” is the actual value contained in the drop-down list, and the “Description” is the part the user sees in the drop down list.

Save changes.

Now, return to your Information Template (CTRL + L) and pull in the LOV name in the relevant LOV field:

When the iProc user sees that field in a “Non Catalog Requisition Template” they see the following:

Purchasing – Information Templates

Information templates are additional information templates that a requester in iProcurement completes for certain items. They allow us to set up templates to cover common forms of purchasing (e.g. Agency Staff), so that when a user in iProcurement raises a requisition for Agency Staff, they are forced to complete additional fields via an Information Template.

They are for use with Non Catalogue Requests / Smart Forms, and not for use with Catalogue items, mainly because it is assumed that if you are buying something from a catalogue all relevant information is already supplied in the item description / part code etc. of the catalogue item you are buying.

Let’s say we want to set up a new Template to cover the purchase of Cheese.

  1. Purchasing Superuser
  2. Setup > Information Templates
  3. The form must be completed as follows:
    • Template: Give the Template a name
    • Available In All Organizations: YOU MUST TICK THIS BOX
    • Attachment Category: MUST BE SET TO “To Supplier”
    • In the “Attributes” section, each line corresponds to a field which you want the user in iProcurement to fill in
      • Seq: These must be sequentially numbered
      • Attribute Name: Internal name for the field – the user in iProcurement does not see this (e.g. CHEESE_NAME)
      • Attribute Description: The description of the field which the iProcurement user will see (e.g. Type of Cheese)
      • Default Value: Text entered here will appear as the default value in the field when viewed in iProcurement
      • LOV: It is possible to link a field on the Template to a “List of Values” so that instead of typing text into the field, the user in iProcurement can pick a value from a list. Refer to how to set up an LOV for use with an Information Template
      • Mandatory: If you tick this box, the user will have to complete this field
      • Enabled: If you tick this box, the user will be able to see the field in iProcurement

Cheese Template Definition

Linking an Information Template to a Purchase Category

Now you have created the Template, there are various routes available to allow you to pull the Template through into iProcurement. There must be some trigger that links this Template through to a specific Purchase type.

One route you can choose is to link the Information Template to a Purchase Category. Once you have done this, then whenever a user in iProcurement adds a line to the Shopping Cart which is linked to that same category, they will be forced to complete the fields on the Template.

For example:

  1. On the example above, click on the “Associate Template” button at the bottom of the Define Information Template screen
  2. The “Information Template Association” box opens up, with the “Type” defaulting to “Item Number”.
  3. Change the “Type” to “Item Category”.
  4. Add in the name of the category you are linking to the Template in the “Item Category” field – in this example I have set up a test category called “TESTING.CHEESE”
  5. Click “Close” to return to the “Define Information Template” screen
  6. Save changes

How it works

  1. Go to iProcurement
  2. Raise a Non Catalogue Requisition for something (some cheese!)
  3. Attached the TESTING.CHEESE” category to the Non Catalogue Requisition line
  4. Complete the form as normal.
  5. When the “Add to Cart” button is pressed, iProcurement recognises that that chosen category is linked to an Information Template, and forces the user to complete additional fields:
  6. The user cannot continue until the required fields have been completed
  7. Once that item has been Added to Cart, it appears in Shopping Cart as follows:
  8. If the user clicks on the link in the “Special Info” column they will be able to update the information held in the Template fields
  9. When the Req. is Approved, and once it has been converted to a PO, the details might be printed on your Purchase Order, depending on whether you have configured your system to view such data, since the Information Template data is held as a “Short Text” Attachment on the PO lines.
  10. To review the information on the PO, go to Purchase Orders > Purchase Order Summary, query the PO number and drill down to view the lines:
  11. The information pulled in from the Information Template is stored as an attachment. Click on the attachment icon (circled above) to view that information:
  12. It is also possible to access the same attachment by opening the PO, clicking on the line, and clicking on the same Attachment icon.

Linking an Information Template to a Smart Form

Smart forms allow us to hard code certain fields on a Non Catalogue Requisition and to add them to a Store. We can also link Information Templates to Smart Forms.

  1. Internet Procurement Catalog Administration
  2. Stores Tab > Smart Forms sub link
  3. Click “Create Smart Form”
  4. Fill in the details on the Smart Form
  5. Click on the “Add Templates” button
  6. Search for and attach the Information Template
  7. Save changes
  8. You now have a new Non Catalogue Request Template, but you need to make it visible in iProcurement – for example, by attaching it to a Store.
  9. For example, go to the Stores Tab > Create a new Store
  10. Give the Store a name and click on “Add Smart Forms” to attach the Smart Form you created earlier
  11. Continue through and save your changes.
  12. Your Store will be listed in iProcurement as follows:
  13. Click on the “Smart Forms” Store link
  14. You can now access the Smart Form – note the CHEESE_TEMPLATE” additional fields from the Information Template down at the bottom:

Requisition Preparer vs Requester

Preparer

  1. Requisitions are created by the preparer
  2. The preparer is stored at requisition header, and the preparer_id is the HR person_id for the user raising the REQ

Requester

  1. The requester is the person who the requisition is being created on behalf of.
  2. The requester is stored at requisition line level

In iProc, in a requition, the preparer means the person who is preparing the requisition.

Requestor is someone who is requesting the item.

Preparer and Requestor may be different if the person (preparer) is preparing a requisition for an item requested by someone else (requestor).

-- ##############################################################################
--      Requisition Preparer vs Requester
-- ##############################################################################

     select distinct
            prha.segment1 req
          , prha.creation_date
          , fu.user_name
          , fu.email_address
          , ppx_prep.full_name preparer
          , ppx_requester.full_name requester
          , fu2.user_name
          , fu2.email_address
     from po_requisition_headers_all prha
     join po_requisition_lines_all prla on prha.requisition_header_id = prla.requisition_header_id
     join per_people_x ppx_prep on ppx_prep.person_id = prha.preparer_id
     join per_people_f ppx_requester on ppx_requester.person_id = prla.to_person_id
     join fnd_user fu on fu.user_id = prha.created_by
left join fnd_user fu2 on fu2.employee_id = ppx_requester.person_id
    where prha.creation_date > '29-NOV-2017'
      and prha.preparer_id <> prla.to_person_id
 order by prha.creation_date desc;

-- basic HR and user details
	  
   select ppx.full_name
		, ppx.person_id
		, ppx.employee_number
		, to_char(ppx.effective_start_date, 'DD-MON-YYYY') hr_record_start_date
		, fu.user_name
		, to_char(fu.start_date, 'DD-MON-YYYY') user_account_start_date
	 from per_people_x ppx
left join fnd_user fu on ppx.person_id = fu.employee_id
	where ppx.full_name in ('Hope, Bob', 'Fitzgerald, Ella');

Purchasing – Approval Limits

  1. Standard Approval Limits
  2. Count of Values per Job Title
  3. Big Tally List
  4. Job Titles with limits attached to staff
  5. Simple view of Approval Limits /Doc Types against job titles
  6. Viewing 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 = '111222'
       ORDER BY 4
              , 1;
			  
    

Count of Values per Job Title

SELECT DISTINCT pj.NAME job
              , COUNT(DISTINCT pcr.amount_limit) ct
           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
         HAVING COUNT(DISTINCT pcr.amount_limit) > 1
       GROUP BY pj.NAME
       ORDER BY 2 DESC;
	   
    

Big Tally List

-- ##############################################################################
--      BIG TALLY LIST
-- ############################################################################*/

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

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

Requisitions Converted to Purchase Orders – Timings

  1. Non Catalogue Requisitions Converted To POs – Details
  2. Requisitions Converted To POs – Average Summary

Non Catalogue Requisitions Converted To POs – Details

-- ##############################################################################
--        NON CATALOGUE REQUISITIONS CONVERTED TO POS - DETAILS
-- ############################################################################*/

SELECT   prha.segment1 req_num
       , prla.line_num req_line_num
       , pla.line_num po_line_num
       , pha.segment1 po_num
       , prha.creation_date r_date
       , pha.creation_date p_date
       , REPLACE(REPLACE(TO_CHAR(NUMTODSINTERVAL(pha.creation_date - prha.creation_date,'day')),'.000000000',''),'+0000000','') diff5
       , papf.full_name requisitioner
       , haout.NAME req_hr_org
       , pav.agent_name
    FROM po.po_requisition_headers_all prha
    JOIN po.po_requisition_lines_all prla      ON prha.requisition_header_id = prla.requisition_header_id
    JOIN po.po_line_locations_all plla         ON plla.line_location_id = prla.line_location_id
    JOIN po.po_lines_all pla                   ON pla.po_line_id = plla.po_line_id
    JOIN po.po_headers_all pha                 ON pha.po_header_id = plla.po_header_id
    JOIN hr.per_all_people_f papf              ON prha.preparer_id = papf.person_id
    JOIN hr.per_all_assignments_f paaf         ON paaf.person_id = papf.person_id
    JOIN hr.hr_all_organization_units_tl haout ON haout.organization_id = paaf.organization_id
    JOIN apps.po_agents_v pav                  ON pav.agent_id = pha.agent_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'
     AND prla.item_id IS NULL
     AND pha.segment1 IS NOT NULL
     AND prha.creation_date < pha.creation_date
     AND prla.catalog_type = 'NONCATALOG'
     AND prla.catalog_source = 'INTERNAL'
     AND prla.source_type_code = 'VENDOR'
     -- -----------------------------------------------PARAMETERS:
     AND prha.creation_date >= :startdate
     AND prha.creation_date <= :enddate
ORDER BY prha.segment1
       , prha.creation_date;

Requisitions Converted To POs – Average Summary

-- ##############################################################################
--        REQUISITIONS CONVERTED TO POS - AVERAGE SUMMARY
-- ############################################################################*/

SELECT   SUBSTR(AVG(pha.creation_date - prha.creation_date), 0, 6) avg_days
       , COUNT(DISTINCT prha.requisition_header_id) count_req
       , COUNT(prla.requisition_line_id) count_lines
    FROM po.po_requisition_headers_all prha
    JOIN po.po_requisition_lines_all prla      ON prha.requisition_header_id = prla.requisition_header_id
    JOIN po.po_line_locations_all plla         ON plla.line_location_id =      prla.line_location_id
    JOIN po.po_lines_all pla                   ON pla.po_line_id =             plla.po_line_id
    JOIN po.po_headers_all pha                 ON pha.po_header_id =           plla.po_header_id
    JOIN hr.per_all_people_f papf              ON prha.preparer_id =           papf.person_id
    JOIN hr.per_all_assignments_f paaf         ON paaf.person_id =             papf.person_id
    JOIN hr.hr_all_organization_units_tl haout ON haout.organization_id =      paaf.organization_id
    JOIN apps.po_agents_v pav                  ON pav.agent_id =               pha.agent_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 1 = 1
     -- -----------------------------------------------JOB CHECKING STUFF
     AND paaf.primary_flag = 'Y'
     AND paaf.assignment_type = 'E'
     -- -----------------------------------------------ONLY INCLUDE CONVERTED REQS
     AND pha.segment1 IS NOT NULL
     AND prha.creation_date < pha.creation_date
     -- -----------------------------------------------NON CATALOGUE:
     AND prla.catalog_type = 'NONCATALOG'
     AND prla.catalog_source = 'INTERNAL'
     AND prla.source_type_code = 'VENDOR'
     -- -----------------------------------------------PARAMETERS:
     AND prha.creation_date >= :startdate
     AND prha.creation_date <= :enddate
ORDER BY 1 DESC;

Purchasing – Receipts

Sections:

  1. Basic Receipt Details
  2. Receipt Counts Per Req And PO
  3. Receipt Counts Per PO
  4. Requisition To PO to Receipt

Basic Receipt Details

    SELECT mp.organization_code org
         , prha.segment1 req_num
         , prha.creation_date req_date
         , pla.line_num po_line_num
         , pla.item_id
         , msib.segment1
         , msib.description
         , pha.segment1 po_num
         , pha.creation_date po_date
         , rsh.receipt_num
         , rsh.creation_date rcpt_create_date
         , rt.quantity qty_received
         , rt.transaction_type
         , rt.destination_type_code
         , pda.quantity_billed qty_billed
         , (pda.quantity_billed * prla.unit_price) amt_billed
         , fu.description
      FROM po.po_requisition_headers_all prha
      JOIN po.po_requisition_lines_all prla  ON prha.requisition_header_id = prla.requisition_header_id
      JOIN po.po_req_distributions_all prda  ON prla.requisition_line_id = prda.requisition_line_id
      JOIN po.po_line_locations_all plla     ON plla.line_location_id = prla.line_location_id
      JOIN po.po_lines_all pla               ON pla.po_line_id = plla.po_line_id
      JOIN po.po_headers_all pha             ON pha.po_header_id = plla.po_header_id
      JOIN po.po_distributions_all pda       ON pla.po_line_id = pda.po_line_id
      JOIN po.rcv_transactions rt            ON pda.po_distribution_id = rt.po_distribution_id
      JOIN po.rcv_shipment_headers rsh       ON rt.shipment_header_id = rsh.shipment_header_id
      JOIN po.rcv_shipment_lines rsl         ON rt.shipment_line_id = rsl.shipment_line_id
      JOIN applsys.fnd_user fu               ON rsh.created_by = fu.user_id
      JOIN hr.hr_all_organization_units haou ON plla.ship_to_organization_id = haou.organization_id
      JOIN inv.mtl_parameters mp             ON mp.organization_id = rt.organization_id
 LEFT JOIN inv.mtl_system_items_b msib       ON pla.item_id = msib.inventory_item_id AND msib.organization_id = mp.organization_id
     WHERE 1 = 1
       AND pha.creation_date > '25-MAY-2016'
       AND rsh.creation_date > '25-MAY-2016'
--       AND pla.item_id IS NOT NULL
--       AND mp.organization_id = 84
--       AND rsh.receipt_num IS NULL
--       AND rsh.receipt_num = 53511
--       AND rt.transaction_type != 'RECEIVE'
--       AND rt.destination_type_code = 'EXPENSE'
           AND 1 = 1;

SELECT prha.segment1 req
     , prha.creation_date req_date
     , plla.need_by_date need_by
     , pla.line_num po_line
     , pha.segment1 po_num
     , pha.creation_date po_date
     , iio.organization_code inv_org
     , tbl_rx.rcpt_date first_receipt_date
     , pv.vendor_name supplier
     , pv.segment1 supp_number
     , pvsa.vendor_site_code site
  FROM po.po_requisition_headers_all prha
     , po.po_requisition_lines_all prla
     , po.po_req_distributions_all prda
     , po.po_line_locations_all plla
     , po.po_lines_all pla
     , po.po_headers_all pha
     , po.po_distributions_all pda
     , apps.po_vendors pv
     , apps.po_vendor_sites_all pvsa
     , apps.invbv_inventory_organizations iio
     , (  SELECT rt.po_distribution_id
               , MIN (rsh.creation_date) rcpt_date
            FROM po.rcv_transactions rt
               , po.rcv_shipment_headers rsh
               , po.rcv_shipment_lines rsl
           WHERE rt.shipment_header_id = rsh.shipment_header_id(+)
             AND rt.shipment_line_id = rsl.shipment_line_id(+)
             AND NVL (rt.transaction_type, 'RECEIVE') = 'RECEIVE'
--             AND rt.creation_date BETWEEN '01-JUL-2012' AND '10-JUL-2012'
        GROUP BY rt.po_distribution_id) tbl_rx
 WHERE prha.requisition_header_id = prla.requisition_header_id
   AND plla.line_location_id = prla.line_location_id
   AND pla.po_line_id = plla.po_line_id
   AND pha.po_header_id = plla.po_header_id
   AND pla.po_line_id = pda.po_line_id
   AND prla.requisition_line_id = prda.requisition_line_id
   AND pha.vendor_id = pv.vendor_id
   AND pha.vendor_site_id = pvsa.vendor_site_id
   AND pv.vendor_id = pvsa.vendor_id
   AND pda.po_distribution_id = tbl_rx.po_distribution_id(+)
   AND plla.ship_to_organization_id = iio.organization_id
   --   and pha.segment1 = 988783
      AND pha.creation_date BETWEEN '15-JUL-2012' AND '20-JUL-2012'
   AND 1 = 1;

Receipt Counts Per Req And PO

-- ############################################################################   
-- RECEIPT COUNTS PER REQ AND PO
-- ############################################################################

SELECT prha.segment1 req_num
     , prha.creation_date req_date
     , pla.line_num po_line_num
     , pha.segment1 po_num
     , pha.creation_date po_date
     , pda.quantity_billed qty_billed
     , (SELECT COUNT(*) FROM po.rcv_transactions rt WHERE pda.po_distribution_id = rt.po_distribution_id AND rt.transaction_type = 'RECEIVE') rcpt_ct
  FROM po.po_requisition_headers_all prha
     , po.po_requisition_lines_all prla
     , po.po_req_distributions_all prda
     , po.po_line_locations_all plla
     , po.po_lines_all pla
     , po.po_headers_all pha
     , po.po_distributions_all pda
 WHERE prha.requisition_header_id = prla.requisition_header_id
   AND plla.line_location_id = prla.line_location_id
   AND pla.po_line_id = plla.po_line_id
   AND pha.po_header_id = plla.po_header_id
   AND pla.po_line_id = pda.po_line_id
   AND prla.requisition_line_id = prda.requisition_line_id
   AND prha.creation_date > '01-OCT-2014'
   AND prha.creation_date < '02-OCT-2014'
   AND 1 = 1;

Receipt Counts Per PO

-- ############################################################################
-- RECEIPT COUNTS PER PO
-- ############################################################################

SELECT pha.segment1 po_num
     , pha.creation_date po_date
     , COUNT (DISTINCT PLA.PO_LINE_ID) line_ct
     , COUNT (DISTINCT RT.TRANSACTION_ID) rx_ct
  FROM po.po_lines_all pla
     , po.po_headers_all pha
     , po.po_distributions_all pda
     , po.rcv_transactions rt
 WHERE PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
   AND pla.po_line_id = pda.po_line_id
   AND pda.po_distribution_id = rt.po_distribution_id
   AND rt.transaction_type = 'RECEIVE'
   AND pha.creation_date > '01-MAR-2014'
   AND pha.creation_date < '10-MAR-2014'
   AND 1 = 1
GROUP BY pha.segment1
     , pha.creation_date;

Requisition To PO to Receipt

-- ############################################################################
-- REQ TO PO TO RECEIPT
-- ############################################################################

SELECT prha.segment1 req_num
     , prha.creation_date req_date
     , pla.line_num po_line_num
     , pha.segment1 po_num
     , pha.creation_date po_date
     , rsh.receipt_num
     , rsh.creation_date rcpt_date
     , rt.quantity qty_received
     , rt.transaction_type
     , pda.quantity_billed qty_billed
     , (pda.quantity_billed * prla.unit_price) amt_billed
  FROM po.po_requisition_headers_all prha
     , po.po_requisition_lines_all prla
     , po.po_req_distributions_all prda
     , po.po_line_locations_all plla
     , po.po_lines_all pla
     , po.po_headers_all pha
     , po.po_distributions_all pda
     , po.rcv_transactions rt
     , po.rcv_shipment_headers rsh
     , po.rcv_shipment_lines rsl
 WHERE prha.requisition_header_id = prla.requisition_header_id
   AND plla.line_location_id = prla.line_location_id
   AND pla.po_line_id = plla.po_line_id
   AND pha.po_header_id = plla.po_header_id
   AND pla.po_line_id = pda.po_line_id
   AND prla.requisition_line_id = prda.requisition_line_id
   AND pda.po_distribution_id = rt.po_distribution_id(+)
   AND rt.shipment_header_id = rsh.shipment_header_id(+)
   AND rt.shipment_line_id = rsl.shipment_line_id(+)
   AND NVL(rt.transaction_type, 'RECEIVE') = 'RECEIVE'
   AND pha.segment1 = 111222333;