SELECT
TOP (100) PERCENT TRIM(
dbo.PK010033.MANUFACTUREORDER_I
) AS [MO No],
TRIM(dbo.PK010033.ITEMNMBR) AS [Item No],
TRIM(dbo.IV00101.ITEMDESC) AS [Desc],
CONVERT(
varchar, dbo.PK010033.REQDATE, 101
) AS Required,
CAST(
dbo.PK010033.QTY_ALLOWED_I AS INT
) AS PickQty,
SUM(
CAST(dbo.PK010033.QTY_ISSUED_I AS INT)
) AS Issued,
CAST(
dbo.PK010033.QTY_ALLOWED_I - dbo.PK010033.QTY_ISSUED_I AS INT
) AS [Remaining Qty],
TRIM(dbo.IV00300.LOTNUMBR) AS [Lot Number],
TRIM(dbo.IV00300.BIN) AS BIN,
SUM(
CAST(
dbo.IV00300.QTYRECVD - dbo.IV00300.QTYSOLD AS INT
)
) AS AvailBinQty,
CONVERT(
varchar, dbo.IV00300.EXPNDATE, 101
) AS ExpireDate,
TRIM(dbo.PK010033.UOFM) AS PK_UoM,
TRIM(dbo.PK010033.LOCNCODE) AS Site,
TRIM(dbo.PK010033.RTSEQNUM_I) AS ROUTE_SEQ,
dbo.PK010033.BOMUSERDEF1_I AS [Offset Require Date],
dbo.PK010033.BOMUSERDEF2_I AS [Special Instructions],
CONVERT(
varchar, dbo.IV00300.EXPNDATE, 12
) AS [Expire No]
FROM
dbo.IV00101
RIGHT OUTER JOIN dbo.PK010033 ON dbo.IV00101.ITEMNMBR = dbo.PK010033.ITEMNMBR
LEFT OUTER JOIN dbo.IV00112
INNER JOIN dbo.IV00300 ON dbo.IV00112.ITEMNMBR = dbo.IV00300.ITEMNMBR
AND dbo.IV00112.BIN = dbo.IV00300.BIN ON dbo.PK010033.ITEMNMBR = dbo.IV00300.ITEMNMBR
AND dbo.PK010033.LOCNCODE = dbo.IV00300.LOCNCODE
GROUP BY
TRIM(
dbo.PK010033.MANUFACTUREORDER_I
),
TRIM(dbo.PK010033.ITEMNMBR),
TRIM(dbo.PK010033.UOFM),
TRIM(dbo.PK010033.RTSEQNUM_I),
TRIM(dbo.PK010033.LOCNCODE),
CONVERT(
varchar, dbo.PK010033.REQDATE, 101
),
dbo.PK010033.BOMUSERDEF1_I,
dbo.PK010033.BOMUSERDEF2_I,
dbo.PK010033.MANUFACTUREORDERST_I,
TRIM(dbo.IV00101.ITEMDESC),
CONVERT(
varchar, dbo.IV00300.EXPNDATE, 12
),
CONVERT(
varchar, dbo.IV00300.EXPNDATE, 101
),
TRIM(dbo.IV00300.BIN),
TRIM(dbo.IV00300.LOTNUMBR),
CAST(
dbo.PK010033.QTY_ALLOWED_I AS INT
),
CAST(
dbo.PK010033.QTY_ALLOWED_I - dbo.PK010033.QTY_ISSUED_I AS INT
)
HAVING
(
dbo.PK010033.MANUFACTUREORDERST_I = 3
)
AND (
TRIM(dbo.PK010033.LOCNCODE) = 'EF-R'
)
AND (
CAST(
dbo.PK010033.QTY_ALLOWED_I - dbo.PK010033.QTY_ISSUED_I AS INT
) > 0
)
AND (
SUM(
CAST(
dbo.IV00300.QTYRECVD - dbo.IV00300.QTYSOLD AS INT
)
) > 0
)
AND (
CONVERT(
varchar, dbo.IV00300.EXPNDATE, 101
) >= GETDATE()
)
ORDER BY
[MO No],
[Item No],
[Expire No]
Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>
Our community of experts have been thoroughly vetted for their expertise and industry experience.