db2 如何在Maximo Query中计算库存

vlurs2pr 于 2022-11-07 发布在 DB2

关注(0)|答案(1)|浏览(174)

我在Maximo中很难计算或得到正确的库存值。
必填字段包括：
1.项目名称
1.项目名称
1.默认库位
1.发行单位
1.当前余额
1.未提货的硬保留数量
1.硬保留发运数量
1.总发运数量
1.库存中的过期数量
1.可用数量
1.当前保留的数量
1.硬保留数量
1.软保留数量
1.暂挂地点中的数量提货数量
缺少字段：
1.硬保留发运数量
1.总发运数量
1.库存中的过期数量
1.可用数量
1.当前保留的数量
1.硬保留数量
1.软保留数量
1.暂挂地点中的数量提货数量
下面是我部分查询：

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

有人能帮我如何获得缺失字段的值吗？

db2

来源：https://stackoverflow.com/questions/55261163/how-to-calculate-inventory-in-maximo-query

1条答案

按热度按时间

kr98yfug1#

下面的查询对我有效，能够获取正确的记录。
请检查并让我知道它是否适合您。

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

赞(0）回复(0）举报 2022-11-07

我来回答

db2 如何在Maximo Query中计算库存

1条答案

相关问题

热门标签

最新问答