Edit Report:Chatters Invoices
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Chatters Invoices
-- Select Date Range.
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "yesterday" }
-- }
-- ROLLUP: {
-- columns: {
-- "YTD SALES": "Total: {{sum}}"
-- }
-- }
SELECT
d.customerref as 'po number',
s.transno AS 'invoice no',
s.trandate as 'date',
s.debtorno,
s.branchcode,
brname as 'name',
s.stockid,
s.description,
s.qty * - 1 AS 'qty',
s.price,
CAST(
(
s.price * ( s.qty * - 1 )) AS DECIMAL ( 10, 2 )) AS prediscounttotal,
( discountpercent * 100 ) AS discount,
( discountedlinetotal * - 1 ) AS 'item total',
t.ovamount AS 'sub total',
s.ovgst AS 'gst',
s.ovfreight AS 'shipping',
t.alloc AS 'invoice total'
FROM
stocktrans s
INNER JOIN debtortrans t ON s.transno = t.transno
INNER JOIN salesorders d ON d.orderno = s.orderno
WHERE
s.trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
AND s.debtorno = '24042'
AND s.type = '10'
AND s.loccode = '00'
GROUP BY
s.stockid,
s.transno
ORDER BY
s.transno ASC,
s.stockid ASC,
s.trandate DESC