Requisitions by Value
SELECT SUM(prla.unit_price * prla.quantity) req_value
, prha.segment1 req
, prha.creation_date req_date
, pha.segment1 po
, pha.creation_date po_date
, papf.full_name
, haout.NAME
, pv.vendor_name supplier
, pvsa.vendor_site_code 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
, apps.po_vendors pv
, apps.po_vendor_sites_all pvsa
, hr.per_all_people_f papf
, hr.per_all_assignments_f paaf
, hr.hr_all_organization_units_tl haout
WHERE prha.requisition_header_id = prla.requisition_header_id
AND prla.line_location_id = plla.line_location_id
AND plla.po_line_id = pla.po_line_id
AND pla.po_header_id = pha.po_header_id
AND plla.po_header_id = pha.po_header_id
AND prha.preparer_id = papf.person_id
AND haout.organization_id = paaf.organization_id
AND paaf.person_id = papf.person_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 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 prha.authorization_status = 'APPROVED'
AND pha.authorization_status = 'APPROVED'
AND ( prha.cancel_flag <> 'Y'
OR prha.cancel_flag IS NULL)
AND ( prla.cancel_flag <> 'Y'
OR prla.cancel_flag IS NULL)
GROUP BY prha.segment1
, pha.segment1
, papf.full_name
, haout.NAME
, pv.vendor_name
, pvsa.vendor_site_code
, prha.creation_date
, pha.creation_date;
POs by Value
SELECT pha.segment1 po
, pha.creation_date
, SUM(pla.unit_price * pla.quantity) po_value
, pv.vendor_name
, pvsa.vendor_site_code site
FROM po.po_headers_all pha
, po.po_lines_all pla
, apps.po_vendors pv
, apps.po_vendor_sites_all pvsa
WHERE pha.po_header_id = pla.po_header_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 pha.creation_date >= '01-APR-2009'
AND pha.creation_date < '01-APR-2010'
AND pha.org_id = 7042
AND pha.authorization_status = 'APPROVED'
AND pha.type_lookup_code = 'STANDARD'
GROUP BY pha.segment1
, pha.creation_date
, pv.vendor_name
, pvsa.vendor_site_code
HAVING SUM(pla.unit_price * pla.quantity) > 0
ORDER BY 2 DESC;
Lists Requisitions With Counts And Values Grouped By Value Range
SELECT SUBSTR(hrorg, 0, 2) service
, sum_value summary
, COUNT(req_id) req_ct
, SUM(sum_val) total_value
FROM (SELECT req_id
, CASE
WHEN sum_val = 0.00 THEN '00: Zero'
WHEN sum_val > = 0.00 AND sum_val <= 250 THEN '01: 1 - 250'
WHEN sum_val > 250 AND sum_val <= 500 THEN '02: 250 - 500'
WHEN sum_val > 500 AND sum_val <= 1000 THEN '03: 500 - 1,000'
WHEN sum_val > 1001 AND sum_val <= 2500 THEN '04: 1,000 - 2,500'
WHEN sum_val > 2501 AND sum_val <= 5000 THEN '05: 2,500 - 5,000'
WHEN sum_val > 5001 AND sum_val <= 10000 THEN '06: 5,000 - 10,000'
WHEN sum_val > 10001 AND sum_val <= 25000 THEN '07: 10,000 - 25,000'
WHEN sum_val > 25001 AND sum_val <= 50000 THEN '08: 25,000 - 50,000'
WHEN sum_val > 50001 AND sum_val <= 100000 THEN '09: 50,000 - 100,000'
WHEN sum_val > 100001 AND sum_val <= 250000 THEN '10: 100,000 - 250,000'
WHEN sum_val > 250001 AND sum_val <= 500000 THEN '11: 250,000 - 500,000'
WHEN sum_val > 500001 AND sum_val <= 1000000 THEN '12: 500,000 - 1,000,000'
WHEN sum_val > 1000001 AND sum_val <= 11000000 THEN '13: 1,000,000 - 11,000,000'
ELSE 'OTHER'
END sum_value
, hrorg
, sum_val
FROM (SELECT prha.requisition_header_id req_id
, SUM(prla.unit_price * prla.quantity) sum_val
, haout.NAME hrorg
FROM po.po_requisition_headers_all prha
, po.po_requisition_lines_all prla
, hr.per_all_people_f papf
, hr.per_all_assignments_f paaf
, hr.hr_all_organization_units_tl haout
WHERE prha.requisition_header_id = prla.requisition_header_id
AND prha.preparer_id = papf.person_id
AND papf.person_id = paaf.person_id
AND paaf.organization_id = haout.organization_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 (
prha.cancel_flag <> 'Y'
OR prha.cancel_flag IS NULL
)
AND prha.authorization_status = 'APPROVED'
AND (
prla.cancel_flag <> 'Y'
OR prla.cancel_flag IS NULL
)
AND TRUNC(prha.creation_date) >= '01-OCT-2009'
AND TRUNC(prha.creation_date) <= '01-JAN-2010'
AND prha.org_id = :bg
GROUP BY prha.requisition_header_id
, prla.requisition_header_id
, haout.NAME))
WHERE SUBSTR(hrorg, 0, 2) IN('AD', 'AE', 'AF')
GROUP BY sum_value
, SUBSTR(hrorg, 0, 2)
ORDER BY 1
, 2;
Another version
SELECT CASE
-- -----------------------------------------------PUNCHOUT:
WHEN prla.catalog_type = 'EXTERNAL'
AND prla.catalog_source = 'EXTERNAL'
AND prla.source_type_code = 'VENDOR'
THEN 'PUNCHOUT'
-- -----------------------------------------------INTERNAL CATALOGUE:
WHEN prla.catalog_type = 'CATALOG'
AND prla.catalog_source = 'INTERNAL'
AND prla.source_type_code = 'VENDOR'
AND prla.item_id IS NULL
THEN 'INTERNAL_CATATALOG'
-- -----------------------------------------------LOCAL INV CATALOGUE:
WHEN prla.catalog_type = 'CATALOG'
AND prla.catalog_source = 'INTERNAL'
AND prla.source_type_code = 'VENDOR'
AND prla.item_id IS NOT NULL
THEN 'LOCAL_INV_CATALOG'
-- -----------------------------------------------NON CATALOGUE
WHEN(
prla.catalog_type = 'NONCATALOG'
AND (
prla.catalog_source = 'INTERNAL'
OR prla.catalog_source IS NULL
)
AND prla.source_type_code = 'VENDOR'
)
OR (
prla.catalog_type IS NULL
AND prla.catalog_source IS NULL
AND prla.source_type_code = 'VENDOR'
)
THEN 'NON_CATALOG'
ELSE 'Other'
END order_type
, COUNT(DISTINCT prha.requisition_header_id) req_count
, COUNT(prla.requisition_line_id) line_count
, SUM(prla.unit_price * prla.quantity) total_value
FROM po.po_requisition_lines_all prla
, po.po_requisition_headers_all prha
, hr.per_all_people_f papf
, hr.per_all_assignments_f paaf
, hr.hr_all_organization_units_tl haout
, hr.hr_all_organization_units_tl bus_gp
WHERE prha.requisition_header_id = prla.requisition_header_id
AND prha.preparer_id = papf.person_id
AND papf.person_id = paaf.person_id
AND prha.org_id = bus_gp.organization_id
AND paaf.organization_id = haout.organization_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 prha.authorization_status = 'APPROVED'
AND prha.creation_date >= '01-JAN-2013'
GROUP BY CASE
-- -----------------------------------------------PUNCHOUT:
WHEN prla.catalog_type = 'EXTERNAL'
AND prla.catalog_source = 'EXTERNAL'
AND prla.source_type_code = 'VENDOR'
THEN 'PUNCHOUT'
-- -----------------------------------------------INTERNAL CATALOGUE:
WHEN prla.catalog_type = 'CATALOG'
AND prla.catalog_source = 'INTERNAL'
AND prla.source_type_code = 'VENDOR'
AND prla.item_id IS NULL
THEN 'INTERNAL_CATATALOG'
-- -----------------------------------------------LOCAL INV CATALOGUE:
WHEN prla.catalog_type = 'CATALOG'
AND prla.catalog_source = 'INTERNAL'
AND prla.source_type_code = 'VENDOR'
AND prla.item_id IS NOT NULL
THEN 'LOCAL_INV_CATALOG'
-- -----------------------------------------------NON CATALOGUE
WHEN(
prla.catalog_type = 'NONCATALOG'
AND (
prla.catalog_source = 'INTERNAL'
OR prla.catalog_source IS NULL
)
AND prla.source_type_code = 'VENDOR'
)
OR (
prla.catalog_type IS NULL
AND prla.catalog_source IS NULL
AND prla.source_type_code = 'VENDOR'
)
THEN 'NON_CATALOG'
ELSE 'Other'
END
ORDER BY 1
, 2
, 3
, 4
, 5;
No comments:
Post a Comment