codbex / codbex-olympus

Enterprise Resource Planing
Eclipse Public License 2.0
0 stars 0 forks source link

[Hestia] Add report for inventory availability #12

Open MinaDoncheva opened 1 month ago

MinaDoncheva commented 1 month ago

Add report using this SQL query:

SELECT 
    p.PRODUCT_NAME,
    SUM(s.STOCKRECORD_DIRECTION) +
    COALESCE((
        SELECT SUM(si.STOCKADJUSTMENTITEM_ADJUSTEDQUANTITY)
        FROM CODBEX_STOCKADJUSTMENTITEM si
        JOIN CODBEX_STOCKADJUSTMENT sa ON si.STOCKADJUSTMENTITEM_STOCKADJUSTMENT = sa.STOCKADJUSTMENT_ID
        WHERE si.STOCKADJUSTMENTITEM_PRODUCT = p.PRODUCT_ID
    ), 0) AS QUANTITY_LEFT
FROM 
    CODBEX_PRODUCT p
LEFT JOIN 
    CODBEX_STOCKRECORD s ON s.STOCKRECORD_PRODUCT = p.PRODUCT_ID
GROUP BY 
    p.PRODUCT_NAME;

Shows available units of all products