How to calculate Inventory in Maximo Query

I'm having a hard time calculating or getting the right values for the inventory in maximo.

Required fields are:

1. Item
2. Description
3. Default Bin
4. Issue Unit
5. Current Balance
6. Hard Reserved Quantity Not Staged
7. Hard Reserved Quantity Shipped
8. Total Quantity Shipped
9. Expired Quantity in Stock
10. Quantity Available
11. Quantity Currently Reserved
12. Hard Reserved Quantity
13. Soft Reserved Quantity
14. Quantity Staged Quantity in Holding Location

Missing fields:

1. Hard Reserved Quantity Shipped
2. Total Quantity Shipped
3. Expired Quantity in Stock
4. Quantity Available
5. Quantity Currently Reserved
6. Hard Reserved Quantity
7. Soft Reserved Quantity
8. Quantity Staged Quantity in Holding Location

here is my partial query:

select 
   inventory.orgid,
   inventory.siteid,
   inventory.location,
   inventory.itemnum,
   item.description,
   invbalances.binnum 
   (select sum(invbalances.curbal) from invbalances),
   (Sum (INVRESERVE.RESERVEDQTY)) - Sum (INVRESERVE.STAGEDQTY) as HardReservedQuantityNotStaged,
   Sum (INVRESERVE.SHIPPEDQTY) as HardReservedQuantityShipped 
from inventory 
inner join ITEM on item.itemnum = inventory.itemnum 
left outer join INVBALANCES on item.itemnum = invbalances.itemnum 
left outer join INVRESERVE on item.itemnum = invreserve.itemnum 
left outer join ASSET on item.itemnum = asset.itemnum
where inventory.itemnum = '11453'
group by inventory.orgid, inventory.siteid, inventory.location,inventory.itemnum, item.description, invbalances.binnum

Can anyone help me how to get the values of the missing fields?

Below query worked for me and able to fetch the correct records.

Please check and let me know if it works for you.

SELECT
    item.itemnum item,
    item.description description,
    inventory.binnum default_bin,
    inventory.issueunit issue_unit,
    (
        SELECT
            nvl(SUM(ib.curbal),0)
        FROM
            invbalances ib
        WHERE
            ib.itemnum = inventory.itemnum
            AND   ib.location = inventory.location
            AND   ib.itemsetid = inventory.itemsetid
            AND   ib.siteid = inventory.siteid
    ) current_balance,
    ( (
        SELECT
            nvl(SUM(reservedqty),0)
        FROM
            invreserve
        WHERE
            inventory.itemnum = invreserve.itemnum
            AND   inventory.location = invreserve.location
            AND   inventory.itemsetid = invreserve.itemsetid
            AND   inventory.siteid = invreserve.storelocsiteid
            AND   invreserve.restype IN (
                'APHARD',
                'HARD'
            )
    ) - (
        SELECT
            SUM(invuseline.quantity)
        FROM
            invuseline
            JOIN invreserve ON invreserve.requestnum = invuseline.requestnum
                               AND invreserve.restype IN (
                'APHARD',
                'HARD'
            )
            JOIN invuse ON invuseline.invusenum = invuse.invusenum
                           AND invuse.siteid = invuseline.siteid
                           AND invuse.status IN (
                'SHIPPED',
                'STAGED'
            )
        WHERE
            inventory.itemnum = invuseline.itemnum
            AND   inventory.location = invuseline.fromstoreloc
            AND   inventory.siteid = invuseline.siteid
            AND   inventory.itemsetid = invuseline.itemsetid
    ) + (
        SELECT
            nvl(SUM(matrectrans.quantity),0)
        FROM
            matrectrans
            JOIN invuse ON invuse.invuseid = matrectrans.invuseid
                           AND invuse.receipts = 'PARTIAL'
            JOIN invuseline ON invuseline.invuselineid = matrectrans.invuselineid
            JOIN invreserve ON invreserve.requestnum = invuseline.requestnum
                               AND invreserve.restype IN (
                'APHARD',
                'HARD'
            )
        WHERE
            inventory.itemnum = matrectrans.itemnum
            AND   inventory.location = matrectrans.fromstoreloc
            AND   inventory.siteid = matrectrans.siteid
            AND   inventory.itemsetid = matrectrans.itemsetid
            AND   matrectrans.status = 'COMP'
    ) ) hard_reserved_qty_not_staged,
    ( (
        SELECT
            nvl(SUM(invuseline.quantity),0)
        FROM
            invuseline
            JOIN invuse ON invuseline.invusenum = invuse.invusenum
                           AND invuse.siteid = invuseline.siteid
                           AND invuse.status IN (
                'SHIPPED',
                'COMPLETE'
            )
            JOIN invreserve ON invreserve.requestnum = invuseline.requestnum
                               AND invreserve.restype IN (
                'APHARD',
                'HARD'
            )
        WHERE
            inventory.itemnum = invuseline.itemnum
            AND   inventory.location = invuseline.fromstoreloc
            AND   inventory.siteid = invuseline.siteid
            AND   inventory.itemsetid = invuseline.itemsetid
    ) - (
        SELECT
            nvl(SUM(matrectrans.quantity),0)
        FROM
            matrectrans
            JOIN invuse ON invuse.invuseid = matrectrans.invuseid
                           AND invuse.receipts = 'PARTIAL'
            JOIN invuseline ON invuseline.invuselineid = matrectrans.invuselineid
            JOIN invreserve ON invreserve.requestnum = invuseline.requestnum
                               AND invreserve.restype IN (
                'APHARD',
                'HARD'
            )
        WHERE
            inventory.itemnum = matrectrans.itemnum
            AND   inventory.location = matrectrans.fromstoreloc
            AND   inventory.siteid = matrectrans.siteid
            AND   inventory.itemsetid = matrectrans.itemsetid
            AND   matrectrans.status = 'COMP'
    ) ) hard_reserved_qty_shipped,
    (
        SELECT
            nvl(SUM(invuseline.quantity),0)
        FROM
            invuseline
            JOIN invuse ON invuseline.invusenum = invuse.invusenum
                           AND invuse.siteid = invuseline.siteid
                           AND invuse.status = 'SHIPPED'
        WHERE
            inventory.itemnum = invuseline.itemnum
            AND   inventory.location = invuseline.fromstoreloc
            AND   inventory.siteid = invuseline.siteid
            AND   inventory.itemsetid = invuseline.itemsetid
    ) - (
        SELECT
            nvl(SUM(quantity),0)
        FROM
            matrectrans
        WHERE
            inventory.itemnum = matrectrans.itemnum
            AND   inventory.location = matrectrans.fromstoreloc
            AND   inventory.siteid = matrectrans.siteid
            AND   inventory.itemsetid = matrectrans.itemsetid
            AND   status = 'COMP'
    ) total_quantity_shipped,
    (
        SELECT
            nvl(SUM(ib.curbal),0)
        FROM
            invbalances ib
        WHERE
            ib.itemnum = inventory.itemnum
            AND   ib.location = inventory.location
            AND   ib.itemsetid = inventory.itemsetid
            AND   ib.siteid = inventory.siteid
    ) - ( (
        SELECT
            nvl(SUM(reservedqty),0)
        FROM
            invreserve
        WHERE
            inventory.itemnum = invreserve.itemnum
            AND   inventory.location = invreserve.location
            AND   inventory.itemsetid = invreserve.itemsetid
            AND   inventory.siteid = invreserve.storelocsiteid
            AND   invreserve.restype IN (
                'APHARD',
                'HARD'
            )
    ) - ( (
        SELECT
            nvl(SUM(invuseline.quantity),0)
        FROM
            invuseline
            JOIN invuse ON invuseline.invusenum = invuse.invusenum
                           AND invuse.siteid = invuseline.siteid
                           AND invuse.status IN (
                'STAGED',
                'SHIPPED'
            )
            JOIN invreserve ON invreserve.requestnum = invuseline.requestnum
                               AND invreserve.restype IN (
                'APHARD',
                'HARD'
            )
        WHERE
            inventory.itemnum = invuseline.itemnum
            AND   inventory.location = invuseline.fromstoreloc
            AND   inventory.siteid = invuseline.siteid
            AND   inventory.itemsetid = invuseline.itemsetid
    ) ) + (
        SELECT
            nvl(SUM(matrectrans.quantity),0)
        FROM
            matrectrans
            JOIN invuse ON invuse.invuseid = matrectrans.invuseid
                           AND invuse.receipts = 'PARTIAL'
            JOIN invuseline ON invuseline.invuselineid = matrectrans.invuselineid
            JOIN invreserve ON invreserve.requestnum = invuseline.requestnum
                               AND invreserve.restype IN (
                'APHARD',
                'HARD'
            )
        WHERE
            inventory.itemnum = matrectrans.itemnum
            AND   inventory.location = matrectrans.fromstoreloc
            AND   inventory.siteid = matrectrans.siteid
            AND   inventory.itemsetid = matrectrans.itemsetid
            AND   matrectrans.status = 'COMP'
    ) ) quantity_available,
    (
        SELECT
            nvl(SUM(reservedqty),0)
        FROM
            invreserve
        WHERE
            inventory.itemnum = invreserve.itemnum
            AND   inventory.location = invreserve.location
            AND   inventory.itemsetid = invreserve.itemsetid
            AND   inventory.siteid = invreserve.storelocsiteid
    ) quantity_currently_reserved,
    (
        SELECT
            nvl(SUM(reservedqty),0)
        FROM
            invreserve
        WHERE
            inventory.itemnum = invreserve.itemnum
            AND   inventory.location = invreserve.location
            AND   inventory.itemsetid = invreserve.itemsetid
            AND   inventory.siteid = invreserve.storelocsiteid
            AND   invreserve.restype IN (
                'APHARD',
                'HARD'
            )
    ) hard_reserved_quantity,
    (
        SELECT
            nvl(SUM(reservedqty),0)
        FROM
            invreserve
        WHERE
            inventory.itemnum = invreserve.itemnum
            AND   inventory.location = invreserve.location
            AND   inventory.itemsetid = invreserve.itemsetid
            AND   inventory.siteid = invreserve.storelocsiteid
            AND   invreserve.restype IN (
                'APSOFT',
                'SOFT'
            )
    ) soft_reserved_quantity,
    (
        SELECT
            nvl(SUM(invuseline.quantity),0)
        FROM
            invuseline
            JOIN invuse ON invuseline.invusenum = invuse.invusenum
                           AND invuse.siteid = invuseline.siteid
                           AND invuse.status = 'STAGED'
        WHERE
            inventory.itemnum = invuseline.itemnum
            AND   inventory.location = invuseline.fromstoreloc
            AND   inventory.siteid = invuseline.siteid
            AND   inventory.itemsetid = invuseline.itemsetid
    ) quantity_staged
FROM
    inventory
    JOIN item ON item.itemsetid = inventory.itemsetid
                 AND item.itemnum = inventory.itemnum