Slow query joining orders and products
First things first - if you're going to need to maintain SQL, it's best to make it easy to maintain first: start with proper indentation and vertical spacing:

SELECT 
     product_table.ProductCode as product_code
    ,SUM(grouped_store_orders_table.po_qty*product_pack_sizes.pack_size_qty) AS order_qty
FROM 
    (SELECT 
         orders_table.barcode
        ,orders_table.po_qty
        ,orders_table.po_ordering_unit
     FROM orders_table 
     GROUP BY 
         orders_table.barcode, 
         orders_table.po_ordering_unit) AS grouped_store_orders_table
LEFT JOIN product_table
    ON product_table.barcode = grouped_store_orders_table.barcode
LEFT JOIN product_pack_sizes
    ON grouped_store_orders_table.barcode = product_pack_sizes.barcode 
    AND grouped_store_orders_table.po_ordering_unit = product_pack_sizes.unit_of_measure
WHERE product_table.Category IN (2,3,4,5) 
    AND product_table.DistroRoute &lt;&gt; 'C'
GROUP BY 
    product_table.ProductCode;


Aliases should make your life easier - I like dropping the AS keyword since it's not really needed, and I like to keep my aliases short/concise - and don't use them where they're not needed.

You're performing these joins, and then filtering product categories with a where clause; I don't know your data, but you may be able to significantly reduce the amount of rows involved in the join by filtering products first:

LEFT JOIN (SELECT * FROM product_table 
           WHERE Category IN (2,3,4,5) AND DistroRoute &lt;&gt; 'C') products
    ON products.barcode = source.barcode