CREATE OR REPLACE VIEW view_warehousewise_stock AS 
SELECT SL.product_id,S.part_number,S.description,S.bar_code,S.brand_class_id AS brand_id,B.product_class AS brand,SL.warehouse_id,
SUM(SL.quantity*SL.unit_factor) AS quantity,S.isActive,S.inventory_item_type,S.rate,U.price AS selling_price_1,S.selling_price_2,U.name AS unit_name,
U.id AS unit_id,
S.generic_id,G.generic_name,T.percentage as tax_code
FROM erp_stock_ledger SL 
LEFT JOIN erp_store S on SL.product_id = S.id 
LEFT JOIN erp_product_class B ON B.id = S.brand_class_id 
LEFT JOIN erp_unit_of_measurement U ON U.product_id = S.id
LEFT JOIN  erp_inventory_generic_name G ON  G.id = S.generic_id
LEFT JOIN erp_mas_inv_tax T ON T.id = S.tax_code
WHERE stock_updated="Y"
group by SL.warehouse_id,SL.product_id 
UNION ALL 
SELECT S.id AS product_id,S.part_number,S.description,S.bar_code,S.brand_class_id AS brand_id,B.product_class AS brand,"0" AS warehouse_id,
"0" AS quantity,S.isActive,S.inventory_item_type ,S.rate,U.price AS selling_price_1,S.selling_price_2,U.name AS unit_name,U.id AS unit_id,
S.generic_id,
G.generic_name,T.percentage as tax_code
FROM erp_store S 
LEFT JOIN erp_product_class B ON B.id = S.brand_class_id 
LEFT JOIN  erp_inventory_generic_name G ON  G.id = S.generic_id
LEFT JOIN erp_unit_of_measurement U ON U.product_id = S.id
LEFT JOIN erp_mas_inv_tax T ON T.id = S.tax_code
ORDER BY `quantity` ASC