PO, REQ and Receipt Counts in Oracle apps

Count of requisitions raised by user

SELECT   COUNT(DISTINCT prha.requisition_header_id) req_count
       , MAX(prha.creation_date) last_req_raised_date
       , ROUND(SYSDATE - MAX(prha.creation_date), 2) time_since_last_req
       , papf.full_name
       , haou.NAME hr_org
       , hla.description user_loc
       , papf.email_address email
    FROM po.po_requisition_headers_all prha
       , hr.per_all_people_f papf
       , hr.per_all_assignments_f paaf
       , hr.hr_all_organization_units haou
       , hr.hr_locations_all hla
   WHERE prha.preparer_id = papf.person_id
     AND papf.person_id = paaf.person_id
     AND paaf.organization_id = haou.organization_id
     AND paaf.location_id = hla.location_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 papf.current_employee_flag = 'Y'
     AND paaf.assignment_type = 'E'
     AND paaf.primary_flag = 'Y'
     AND prha.creation_date >= '01-APR-2009'
     AND prha.authorization_status = 'APPROVED'
GROUP BY papf.full_name
       , haou.NAME
       , hla.description
       , papf.email_address
ORDER BY 1 DESC;

-- another version

SELECT   COUNT(*) ct
       , papf.full_name
       , haout.NAME
    FROM hr.per_all_people_f papf
       , hr.per_all_assignments_f paaf
       , hr.hr_locations_all_tl hlat
       , hr.hr_all_organization_units_tl haout
       , applsys.fnd_user fu
       , po.po_headers_all pha
       , po.po_agents pa
   WHERE fu.employee_id = papf.person_id
     AND papf.person_id = paaf.person_id
     AND paaf.location_id = hlat.location_id
     AND paaf.organization_id = haout.organization_id
     AND pha.agent_id = pa.agent_id
     AND pa.agent_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
     AND paaf.primary_flag = 'Y'
     AND paaf.assignment_type = 'E'
     AND pha.creation_date >= '03-DEC-2007'
GROUP BY papf.full_name
       , haout.NAME
ORDER BY 1 DESC;

Count of requisitions - by HR ORg

SELECT   COUNT(*)
       , SUBSTR(haout.NAME, 0, 200) hr_org
       , SUBSTR(haout.NAME, 0, 2) service
    FROM po.po_requisition_headers_all prha
       , hr.per_all_people_f papf
       , hr.per_all_assignments_f paaf
       , hr.hr_locations_all_tl hlat
       , hr.hr_all_organization_units_tl haout
       , hr.per_jobs pj
   WHERE prha.preparer_id = papf.person_id
     AND papf.person_id = paaf.person_id
     AND paaf.location_id = hlat.location_id
     AND paaf.organization_id = haout.organization_id
     AND paaf.job_id = pj.job_id
     AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
     AND prha.creation_date BETWEEN '01-APR-2007' AND '31-OCT-2007'
GROUP BY SUBSTR(haout.NAME, 0, 200)
       , SUBSTR(haout.NAME, 0, 2)
ORDER BY 1 DESC;

Summary of requisition volumes

--BY DAY

SELECT   COUNT(*) tally

       , TO_CHAR(prha.creation_date, 'RRRR-MM-DD') the_date

    FROM po.po_requisition_headers_all prha

GROUP BY TO_CHAR(prha.creation_date, 'RRRR-MM-DD')

ORDER BY TO_CHAR(prha.creation_date, 'RRRR-MM-DD') DESC;

 

--BY MONTH

SELECT   COUNT(*) tally

       , TO_CHAR(prha.creation_date, 'RRRR-MM') the_date

    FROM po.po_requisition_headers_all prha

GROUP BY TO_CHAR(prha.creation_date, 'RRRR-MM')

ORDER BY TO_CHAR(prha.creation_date, 'RRRR-MM') DESC;

 

--BY YEAR 

SELECT   COUNT(*) tally

       , TO_CHAR(prha.creation_date, 'RRRR') the_date

    FROM po.po_requisition_headers_all prha

GROUP BY TO_CHAR(prha.creation_date, 'RRRR')

ORDER BY TO_CHAR(prha.creation_date, 'RRRR') DESC;

 

--ALL REQs

SELECT COUNT(*) tally

  FROM po.po_requisition_headers_all prha;

 

Count of purchase orders raised by user

SELECT   COUNT(*) ct
       , papf.full_name
       , haout.NAME
    FROM hr.per_all_people_f papf
       , hr.per_all_assignments_f paaf
       , hr.hr_locations_all_tl hlat
       , hr.hr_all_organization_units_tl haout
       , applsys.fnd_user fu
       , po.po_headers_all pha
       , po.po_agents pa
   WHERE fu.employee_id = papf.person_id
     AND papf.person_id = paaf.person_id
     AND paaf.location_id = hlat.location_id
     AND paaf.organization_id = haout.organization_id
     AND pha.agent_id = pa.agent_id
     AND pa.agent_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
     AND paaf.primary_flag = 'Y'
     AND paaf.assignment_type = 'E'
     AND pha.creation_date >= '03-DEC-2007'
GROUP BY papf.full_name
       , haout.NAME
ORDER BY 1 DESC;

Count of Purchase Orders AutoCreated by user

SELECT   COUNT(*) ct
       , papf.full_name
       , haout.NAME
    FROM hr.per_all_people_f papf
       , hr.per_all_assignments_f paaf
       , hr.hr_locations_all_tl hlat
       , hr.hr_all_organization_units_tl haout
       , applsys.fnd_user fu
       , po.po_headers_all pha
       , po.po_agents pa
   WHERE fu.employee_id = papf.person_id
     AND papf.person_id = paaf.person_id
     AND paaf.location_id = hlat.location_id
     AND paaf.organization_id = haout.organization_id
     AND pha.agent_id = pa.agent_id
     AND pa.agent_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
     AND paaf.primary_flag = 'Y'
     AND paaf.assignment_type = 'E'
     AND pha.document_creation_method = 'AUTOCREATE'
     AND pha.creation_date >= '03-DEC-2007'
GROUP BY papf.full_name
       , haout.NAME
ORDER BY 1 DESC;

Summary of PO volumes

--BY DAY
SELECT   COUNT(*) tally
       , TO_CHAR(pha.creation_date, 'RRRR-MM-DD') the_date
    FROM po.po_headers_all pha
GROUP BY TO_CHAR(pha.creation_date, 'RRRR-MM-DD')
ORDER BY TO_CHAR(pha.creation_date, 'RRRR-MM-DD') DESC;

--BY DAY
SELECT   COUNT(*) tally
       , TO_CHAR(pha.creation_date, 'RRRR-MM-DD') the_date
       , pha.document_creation_method
    FROM po.po_headers_all pha
   WHERE TRUNC(pha.creation_date) >= TRUNC(SYSDATE) - 5
     AND pha.authorization_status = 'APPROVED'
     AND pha.type_lookup_code = 'STANDARD'
GROUP BY TO_CHAR(pha.creation_date, 'RRRR-MM-DD')
       , pha.document_creation_method
ORDER BY TO_CHAR(pha.creation_date, 'RRRR-MM-DD') DESC;

--BY MONTH
SELECT   COUNT(*) tally
       , TO_CHAR(pha.creation_date, 'RRRR-MM') the_date
    FROM po.po_headers_all pha
GROUP BY TO_CHAR(pha.creation_date, 'RRRR-MM')
ORDER BY TO_CHAR(pha.creation_date, 'RRRR-MM') DESC;

--BY MONTH ORDERED BY MONTH
SELECT   COUNT(*) tally
       , TO_CHAR(pha.creation_date, 'MON-RRRR') the_date
    FROM po.po_headers_all pha
GROUP BY TO_CHAR(pha.creation_date, 'MON-RRRR')
ORDER BY TO_CHAR(pha.creation_date, 'MON-RRRR') DESC;

--BY MONTH ORDERED BY TALLY
SELECT   COUNT(*) tally
       , TO_CHAR(pha.creation_date, 'MON-RRRR') the_date
    FROM po.po_headers_all pha
GROUP BY TO_CHAR(pha.creation_date, 'MON-RRRR')
ORDER BY 1 DESC;

--BY YEAR 
SELECT   COUNT(*) tally
       , TO_CHAR(pha.creation_date, 'RRRR') the_date
    FROM po.po_headers_all pha
GROUP BY TO_CHAR(pha.creation_date, 'RRRR')
ORDER BY TO_CHAR(pha.creation_date, 'RRRR') DESC;

--ALL POs
SELECT COUNT(*) tally
  FROM po.po_headers_all pha;

Summary of receipt volumes

 

--BY DAY
SELECT   COUNT(*) tally
       , TO_CHAR(rsh.creation_date, 'RRRR-MM-DD') the_date
    FROM po.rcv_shipment_headers rsh
   WHERE rsh.creation_date >= '01-NOV-2006'
GROUP BY TO_CHAR(rsh.creation_date, 'RRRR-MM-DD')
ORDER BY TO_CHAR(rsh.creation_date, 'RRRR-MM-DD') DESC;

--BY MONTH
SELECT   COUNT(*) tally
       , TO_CHAR(rsh.creation_date, 'RRRR-MM') the_date
    FROM po.rcv_shipment_headers rsh
GROUP BY TO_CHAR(rsh.creation_date, 'RRRR-MM')
ORDER BY TO_CHAR(rsh.creation_date, 'RRRR-MM') DESC;

--BY MONTH ORDERED BY MONTH
SELECT   COUNT(*) tally
       , TO_CHAR(rsh.creation_date, 'MON-RRRR') the_date
    FROM po.rcv_shipment_headers rsh
GROUP BY TO_CHAR(rsh.creation_date, 'MON-RRRR')
ORDER BY TO_CHAR(rsh.creation_date, 'MON-RRRR') DESC;

--BY MONTH ORDERED BY TALLY
SELECT   COUNT(*) tally
       , TO_CHAR(rsh.creation_date, 'MON-RRRR') the_date
    FROM po.rcv_shipment_headers rsh
GROUP BY TO_CHAR(rsh.creation_date, 'MON-RRRR')
ORDER BY 1 DESC;

--BY YEAR 
SELECT   COUNT(*) tally
       , TO_CHAR(rsh.creation_date, 'RRRR') the_date
    FROM po.rcv_shipment_headers rsh
GROUP BY TO_CHAR(rsh.creation_date, 'RRRR')
ORDER BY TO_CHAR(rsh.creation_date, 'RRRR') DESC;

--ALL RECEIPTS
SELECT COUNT(*) tally
  FROM po.rcv_shipment_headers rsh;

Basic count of PO and REQ volume with value

 

-- REQs
SELECT   COUNT(DISTINCT prha.requisition_header_id) tally
       , SUM(prla.quantity * prla.unit_price) total_value
       , TO_CHAR(prha.creation_date, 'RRRR-MM-DD') the_date
    FROM po.po_requisition_headers_all prha
       , po.po_requisition_lines_all prla
   WHERE prha.requisition_header_id = prla.requisition_header_id
     -- FLAG FOR CBS REQ LINES
     AND prla.item_id IS NOT NULL
     AND prha.creation_date BETWEEN '01-FEB-2008' AND '01-MAY-2008'
GROUP BY TO_CHAR(prha.creation_date, 'RRRR-MM-DD')
ORDER BY TO_CHAR(prha.creation_date, 'RRRR-MM-DD');

-- POs
SELECT   COUNT(DISTINCT pha.po_header_id) tally
       , SUM(pla.quantity * pla.unit_price) total_value
       , TO_CHAR(pha.creation_date, 'RRRR-MM-DD') the_date
    FROM po.po_headers_all pha
       , po.po_lines_all pla
   WHERE pha.po_header_id = pla.po_header_id
     AND pha.creation_date BETWEEN '01-FEB-2007' AND '01-MAY-2007'
GROUP BY TO_CHAR(pha.creation_date, 'RRRR-MM-DD')
ORDER BY TO_CHAR(pha.creation_date, 'RRRR-MM-DD');