Cost & Gross Margin Review
FY2026 year to date

SELECT a.acctnumber, a.fullname,
  CASE WHEN ap.startdate >= TO_DATE('2026-01-01','YYYY-MM-DD') THEN 'FY26' ELSE 'FY25' END AS fy,
  CASE WHEN t.memo LIKE 'Beg Balance%' THEN 'synthetic' ELSE 'real' END AS src,
  ROUND(SUM(-tal.amount),2) AS amount
FROM transactionaccountingline tal
JOIN transaction t            ON tal.transaction = t.id
JOIN transactionline tl       ON tl.transaction = tal.transaction AND tl.id = tal.transactionline
JOIN account a                ON tal.account = a.id
JOIN accountingperiod ap      ON t.postingperiod = ap.id
WHERE t.posting = 'T' AND tal.posting = 'T'
  AND tl.subsidiary IN (1,2,3)
  AND a.accttype IN ('Income','COGS')
  AND ((ap.startdate >= TO_DATE('2025-01-01','YYYY-MM-DD') AND ap.startdate < TO_DATE('2025-10-01','YYYY-MM-DD'))
    OR ap.startdate >= TO_DATE('2026-01-01','YYYY-MM-DD'))
GROUP BY a.acctnumber, a.fullname,
  CASE WHEN ap.startdate >= TO_DATE('2026-01-01','YYYY-MM-DD') THEN 'FY26' ELSE 'FY25' END,
  CASE WHEN t.memo LIKE 'Beg Balance%' THEN 'synthetic' ELSE 'real' END
ORDER BY a.acctnumber, fy, src

SELECT
  COALESCE(c.name, BUILTIN.DF(i.csegmh_cseg_1), BUILTIN.DF(p.csegmh_cseg_1), 'Uncategorized') AS category,
  CASE WHEN ap.startdate >= TO_DATE('2026-01-01','YYYY-MM-DD') THEN 'FY26' ELSE 'FY25' END AS fy,
  ROUND(SUM(CASE WHEN a.accttype = 'Income' THEN -tal.amount ELSE 0 END),2) AS revenue,
  ROUND(SUM(CASE WHEN a.accttype = 'COGS'   THEN  tal.amount ELSE 0 END),2) AS cogs
FROM transactionaccountingline tal
JOIN transaction t        ON tal.transaction = t.id
JOIN transactionline tl   ON tl.transaction = tal.transaction AND tl.id = tal.transactionline
JOIN account a            ON tal.account = a.id
JOIN accountingperiod ap  ON t.postingperiod = ap.id
LEFT JOIN item i          ON tl.item = i.id
LEFT JOIN item p          ON i.parent = p.id
LEFT JOIN classification c ON i.class = c.id
WHERE t.posting = 'T' AND tal.posting = 'T'
  AND tl.subsidiary IN (1,2,3)
  AND a.accttype IN ('Income','COGS')
  AND (t.memo IS NULL OR t.memo NOT LIKE 'Beg Balance%')
  AND ((ap.startdate >= TO_DATE('2025-01-01','YYYY-MM-DD') AND ap.startdate < TO_DATE('2025-10-01','YYYY-MM-DD'))
    OR ap.startdate >= TO_DATE('2026-01-01','YYYY-MM-DD'))
GROUP BY COALESCE(c.name, BUILTIN.DF(i.csegmh_cseg_1), BUILTIN.DF(p.csegmh_cseg_1), 'Uncategorized'),
  CASE WHEN ap.startdate >= TO_DATE('2026-01-01','YYYY-MM-DD') THEN 'FY26' ELSE 'FY25' END
ORDER BY category, fy

SELECT iil.item, i.itemid,
  COALESCE(c.name, BUILTIN.DF(i.csegmh_cseg_1), BUILTIN.DF(p.csegmh_cseg_1), 'Uncategorized') AS category,
  iil.location, l.name AS locname,
  iil.quantityonhand AS qoh, iil.onhandvaluemli AS val, iil.quantityonorder AS qoo
FROM inventoryitemlocations iil
JOIN item i               ON iil.item = i.id
LEFT JOIN item p          ON i.parent = p.id
LEFT JOIN classification c ON i.class = c.id
JOIN location l           ON iil.location = l.id
WHERE iil.quantityonhand <> 0 OR iil.quantityonorder <> 0

SELECT tl.item, tl.location,
  SUM(ABS(tl.quantity)) AS units, SUM(ABS(tl.netamount)) AS rev
FROM transactionline tl
JOIN transaction t ON tl.transaction = t.id
WHERE t.type IN ('CustInvc','CashSale')
  AND tl.mainline = 'F' AND tl.taxline = 'F'
  AND tl.item IS NOT NULL AND tl.subsidiary IN (1,2,3)
  AND t.trandate >= TO_DATE('2025-09-18','YYYY-MM-DD')
GROUP BY tl.item, tl.location

SELECT tl.item, SUM(tal.amount) AS cogs
FROM transactionaccountingline tal
JOIN transaction t      ON tal.transaction = t.id
JOIN transactionline tl ON tl.transaction = tal.transaction AND tl.id = tal.transactionline
JOIN account a          ON tal.account = a.id
WHERE t.posting = 'T' AND tal.posting = 'T'
  AND a.accttype = 'COGS' AND tl.subsidiary IN (1,2,3)
  AND (t.memo IS NULL OR t.memo NOT LIKE 'Beg Balance%')
  AND t.trandate >= TO_DATE('2025-09-18','YYYY-MM-DD')
  AND tl.item IS NOT NULL
GROUP BY tl.item

// per item: qoh, val, qoo summed across locations
// velocity u = 12-mo units; dos = qoh / (u / 365)
// band: u == 0 → dead; dos > 720 → >2 yrs; > 365 → 1–2 yrs; > 180 → 6–12 mo; else < 6 mo
// excess = u == 0 ? val : (dos > 180 ? val * (1 - 180/dos) : 0)
// gm% = (rev - cogs) / rev; turns = cogs / val
// stranded = item-location rows with qoh > 0 and zero sales at that location
// aggregates: totals, bands, byCategory, byLocation, topN excess / dead / low-margin / on-order-into-excess

SELECT t.tranid, TO_CHAR(t.trandate,'YYYY-MM-DD') AS podate, BUILTIN.DF(t.status) AS status,
  v.entityid AS vendor, i.itemid,
  ABS(tl.quantity) AS qty, COALESCE(tl.quantityshiprecv,0) AS received,
  ABS(tl.quantity) - COALESCE(tl.quantityshiprecv,0) AS open_qty,
  ROUND(ABS(tl.netamount) * (ABS(tl.quantity) - COALESCE(tl.quantityshiprecv,0)) / NULLIF(ABS(tl.quantity),0),2) AS open_value,
  l.name AS locname
FROM transactionline tl
JOIN transaction t     ON tl.transaction = t.id
JOIN item i            ON tl.item = i.id
LEFT JOIN vendor v     ON t.entity = v.id
LEFT JOIN location l   ON tl.location = l.id
WHERE t.type = 'PurchOrd' AND tl.mainline = 'F' AND tl.taxline = 'F'
  AND t.status IN ('A','B','E')
  AND ABS(tl.quantity) - COALESCE(tl.quantityshiprecv,0) > 0
  AND i.itemid IN (/* excess / dead item list from A6 */)
ORDER BY open_value DESC

SELECT COALESCE(d.name,'(none)') AS department,
  ROUND(SUM(CASE WHEN ap.startdate >= TO_DATE('2026-01-01','YYYY-MM-DD')
                  AND (t.memo IS NULL OR t.memo NOT LIKE 'Beg Balance%') THEN tal.amount ELSE 0 END),0) AS fy26_real,
  ROUND(SUM(CASE WHEN ap.startdate >= TO_DATE('2026-01-01','YYYY-MM-DD')
                  AND t.memo LIKE 'Beg Balance%' THEN tal.amount ELSE 0 END),0) AS fy26_synth,
  ROUND(SUM(CASE WHEN ap.startdate <  TO_DATE('2025-10-01','YYYY-MM-DD')
                  AND (t.memo IS NULL OR t.memo NOT LIKE 'Beg Balance%') THEN tal.amount ELSE 0 END),0) AS fy25_real
FROM transactionaccountingline tal
JOIN transaction t       ON tal.transaction = t.id
JOIN transactionline tl  ON tl.transaction = tal.transaction AND tl.id = tal.transactionline
JOIN account a           ON tal.account = a.id
JOIN accountingperiod ap ON t.postingperiod = ap.id
LEFT JOIN department d   ON tl.department = d.id
WHERE t.posting = 'T' AND tal.posting = 'T' AND tl.subsidiary IN (1,2,3)
  AND a.accttype IN ('Expense','OthExpense')
  AND ((ap.startdate >= TO_DATE('2025-01-01','YYYY-MM-DD') AND ap.startdate < TO_DATE('2025-10-01','YYYY-MM-DD'))
    OR ap.startdate >= TO_DATE('2026-01-01','YYYY-MM-DD'))
GROUP BY COALESCE(d.name,'(none)')
ORDER BY 2 DESC
-- account-level variant: GROUP BY a.acctnumber, a.fullname; real only; FETCH FIRST 12 ROWS ONLY