Edit Report:Bookings (with inv details)
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Bookings (with inv details)
-- Sales Order Information
-- PLACE HOLDER REPORT FILTERS AND DRILL DOWN
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "today", end: "today" }
-- }
-- ROLLUP: {
-- columns: {
-- "ORDERNUMBER": "Count: {{count}}",
-- "ORDERVALUE": "Total: {{sum}}"
-- }
-- }
SELECT DISTINCT
debtorsmaster.debtorno AS CUSTOMERNUMBER,
salesorderdetails.orderno AS ORDERNUMBER,
orddate as DATE,
debtorsmaster.name AS CUSTOMER,
salesorderdetails.stkcode,
salesorderdetails.quantity,
salesorderdetails.qtyinvoiced,
CAST(sum((quantity - qtyinvoiced )) AS DECIMAL(10,2)) as RemainingQty,
CAST(sum(unitprice *(quantity )) AS DECIMAL(10,2)) as ORDERVALUE
FROM
salesorderdetails
LEFT JOIN salesorders ON salesorderdetails.orderno = salesorders.orderno
LEFT JOIN debtortrans ON debtortrans.order_ = salesorderdetails.orderno
LEFT JOIN stockmoves ON debtortrans.transno = stockmoves.transno
AND stockmoves.orderlineref = salesorderdetails.orderlineno
AND stockmoves.type = 10
LEFT JOIN stockmaster ON salesorderdetails.stkcode = stockmaster.stockid
LEFT JOIN debtorsmaster ON salesorders.debtorno = debtorsmaster.debtorno
WHERE
( orderlineref IS NOT NULL OR stockmoves.stockid IS NULL )
AND orddate between '{{range.start}}' and '{{range.end}}'
GROUP BY
salesorderdetails.orderno,
debtorsmaster.debtorno,
salesorderdetails.stkcode
ORDER BY
salesorderdetails.orderno ASC, salesorderdetails.stkcode ASC