SyteLine Open Purchase Order Report Query Generator

-- Open and late purchase order lines, SyteLine 9/10 (run on a read-only login)
DECLARE @Site nvarchar(20) = N'MAIN';
DECLARE @DaysLate int = 0;

SELECT
    p.po_num,
    pi.po_line,
    pi.po_release,
    p.vend_num,
    va.name AS vendor_name,
    p.buyer,
    pi.whse,
    pi.item,
    pi.qty_ordered,
    pi.qty_received,
    pi.qty_ordered - pi.qty_received AS qty_open,
    pi.item_cost,
    (pi.qty_ordered - pi.qty_received) * pi.item_cost AS open_value,
    pi.due_date,
    DATEDIFF(day, pi.due_date, GETDATE()) AS days_late
FROM po_mst p
JOIN poitem_mst pi
    ON pi.site_ref = p.site_ref
   AND pi.po_num = p.po_num
JOIN vendaddr_mst va
    ON va.site_ref = p.site_ref
   AND va.vend_num = p.vend_num
WHERE p.site_ref = @Site
  AND pi.stat IN ('O')
  AND pi.qty_ordered > pi.qty_received
  AND p.vend_num LIKE N'%'
  AND ISNULL(p.buyer, N'') LIKE N'%'
  AND pi.whse LIKE N'%'
  AND DATEDIFF(day, pi.due_date, GETDATE()) >= @DaysLate
ORDER BY pi.due_date, p.vend_num;

-- Receipt transactions (ref_type P, trans_type R) compared with the PO line due date
DECLARE @Site nvarchar(20) = N'MAIN';
DECLARE @Lookback int = 90;

SELECT
    p.vend_num,
    va.name AS vendor_name,
    COUNT(*) AS receipts,
    SUM(CASE WHEN CAST(m.trans_date AS date) <= CAST(pi.due_date AS date) THEN 1 ELSE 0 END) AS on_time_receipts,
    CAST(100.0 * SUM(CASE WHEN CAST(m.trans_date AS date) <= CAST(pi.due_date AS date) THEN 1 ELSE 0 END) / COUNT(*) AS decimal(5,1)) AS on_time_pct
FROM matltran_mst m
JOIN poitem_mst pi
    ON pi.site_ref = m.site_ref
   AND pi.po_num = m.ref_num
   AND pi.po_line = m.ref_line_suf
   AND pi.po_release = m.ref_release
JOIN po_mst p
    ON p.site_ref = pi.site_ref
   AND p.po_num = pi.po_num
JOIN vendaddr_mst va
    ON va.site_ref = p.site_ref
   AND va.vend_num = p.vend_num
WHERE m.site_ref = @Site
  AND m.ref_type = 'P'
  AND m.trans_type = 'R'
  AND m.qty > 0
  AND m.trans_date >= DATEADD(day, -@Lookback, GETDATE())
  AND p.vend_num LIKE N'%'
GROUP BY p.vend_num, va.name
ORDER BY on_time_pct, receipts DESC;