Ниже запрос работал у меня и смог получить правильные записи.
Пожалуйста, проверьте и дайте мне знать, работает ли это для вас.
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
person
Swaroop
schedule
07.08.2019