Edit Report:Inventory QOH Report
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Inventory QOH Report
--
--
-- VARIABLE: {
-- name: "supplierno",
-- display: "Supplier Code",
-- type: "text",
-- }
SELECT
s.stockid,
description,
p.supplierno,
p.suppliers_partno,
s.barcode,
cast(p.price as DECIMAL(10,2)) as cost,
s.dwo,
s.discontinued,
(select bin from locstock where loccode='00' and locstock.stockid =s.stockid ) as bin,
(select sum(quantity) from locstock where loccode='00' and locstock.stockid =s.stockid ) as 'Warehouse QOH',
(select sum(quantity) from locstock where loccode='01' and locstock.stockid =s.stockid ) as 'Calgary SE QOH',
(select sum(quantity) from locstock where loccode='02' and locstock.stockid =s.stockid ) as 'Edmonton QOH',
(select sum(quantity) from locstock where loccode='03' and locstock.stockid =s.stockid ) as 'Regina QOH',
(select sum(quantity) from locstock where loccode='04' and locstock.stockid =s.stockid ) as 'Saskatoon QOH',
(select sum(quantity) from locstock where loccode='05' and locstock.stockid =s.stockid ) as 'Winnipeg QOH',
(select sum(quantity) from locstock where loccode='06' and locstock.stockid =s.stockid ) as 'The Boutique QOH',
(select sum(quantity) from locstock where loccode='07' and locstock.stockid =s.stockid ) as 'Calgary NE QOH',
(select sum(quantity) from locstock where loccode='08' and locstock.stockid =s.stockid ) as 'Red Deer QOH'
FROM
stockmaster s
inner join purchdata p on s.stockid = p.stockid
WHERE
s.discontinued = 0
{% if supplierno != ''%}
and p.supplierno ='{{ supplierno }}'
{% endif %}