Edit Report:On Account WPG Store
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- On Account WPG Store
--
--
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start:"yesterday", end: "today" }
-- }
-- ROLLUP: {
-- columns: {
-- "Total": "Total: {{sum}}"
-- }
-- }
--
--
SELECT
d.debtorno as 'Customer#',
b.branchcode as 'Shipto#',
DATE_FORMAT(trandate, "%d/%m/%Y") as 'Date',
transno as 'Invoice#',
order_ as 'Order#',
s.typename,
b.brname,
ovamount as Subtotal,
ovgst as Tax,
CAST( ( ovamount + ovgst + ovfreight + ovdiscount ) AS DECIMAL ( 10, 2 )) AS Total
FROM
debtortrans d
join custbranch b on d.debtorno = b.debtorno and b.branchcode = d.branchcode
join systypenames s on d.type = s.typeid
WHERE
1
and DATE(trandate) BETWEEN DATE('{{ range.start }}') AND DATE('{{ range.end }}')
and type in (10, 11)
AND d.debtorno in (
'2189', '9817', '3621', '2077', '0254',
'3566', '9444', '0775', '0054', '0170',
'1664', '0963', '2580', '8754', '2000',
'10253', '0944', '8755', '10254'
)
and transno in (select transno from stockmoves where type in ('10','11') and loccode='05' and DATE(trandate) BETWEEN DATE('{{ range.start }}') AND DATE('{{ range.end }}'))