Adding the "next-highest" unit of measure to a label

Here's a trick if you need to print the next-highest unit of measure on a label. For example, your base unit-of-measure for an item is "each" but you receive or use as a "case".

Any of the Panatracker labeling views can be altered to include the extra columns for the unit of measure and the conversion factor. Here's an example for the lot receipts label. See the part below in red for the extra fields added. These can then be pulled on to the label.

CREATE VIEW [dbo].[PanatrackerGP6_LabelReceivingUnit_Lot]
AS
SELECT TrxUnit.Oid TrxOid,
TrxUnit.CreateTime,
TrxUnit.CreateUserName,
TrxUnit.SiteCode,
TrxUnit.BinCode,
TrxUnit.ReceivedQuantity AS Quantity,
TrxUnit.UnitOfMeasure AS TransactionUnitOfMeasure,
TrxUnit.TrackingOption AS ItemTrackingOption,
TrxUnit.ItemCode,
TrxUnit.ItemDescription,
ISNULL(IV00101.ITMGEDSC, '') AS GenericDescription,
TrxUnit.LotCode,
TrxUnit.VendorItemCode AS VendorItemCode,
ISNULL(CONVERT(VARCHAR(8), TrxUnit.UnitExpirationDate, 1), 'N/A') AS ExpirationDate,
ISNULL(CONVERT(VARCHAR(8), TrxUnit.UnitManufactureDate, 1), 'N/A') AS ManufactureDate,
ISNULL(TrxUnit.UnitAttribute1, '') AS UnitAttribute1,
ISNULL(TrxUnit.UnitAttribute2, '') AS UnitAttribute2,
ISNULL(TrxUnit.UnitAttribute3, '') AS UnitAttribute3,
ISNULL(TrxUnit.UnitAttribute4, '') AS UnitAttribute4,
ISNULL(TrxUnit.UnitAttribute5, '') AS UnitAttribute5,
ISNULL(RTRIM(IV00101.ITMSHNAM), 'N/A') AS ItemShortName,
Trx.TransactionCode,
Trx.SubmitTime,
ISNULL(Trx.FlexField1, '') AS FlexField1,
'' AS FlexField2,
ISNULL(Trx.PurchaseOrderCode, '') AS PurchaseOrderCode,
Trx.VendorCode AS VendorCode,
CONVERT(VARCHAR(8), GETDATE(), 1) AS UnitReceiveDate,
ISNULL(RTRIM(dbo.IV40201.BASEUOFM), 'N/A') AS BaseUnitOfMeasure,
(SELECT UOFM
FROM (SELECT *, row_number() OVER (ORDER BY seqnumbr) AS RANK
FROM iv40202
WHERE UOMSCHDL = IV00101.UOMSCHDL) AS A
WHERE A.[RANK] = 2) as NextUOFM,
(SELECT EQUOMQTY
FROM (SELECT *, row_number() OVER (ORDER BY seqnumbr) AS RANK
FROM iv40202
WHERE UOMSCHDL = IV00101.UOMSCHDL) AS A
WHERE A.[RANK] = 2) as NextUOFMQty
FROM PanatrackerGP6_TrxReceivingUnit AS TrxUnit
INNER JOIN dbo.PanatrackerGP6_TrxReceiving AS Trx ON TrxUnit.TrxReceivingOid = Trx.Oid
LEFT JOIN dbo.IV00101 ON TrxUnit.ItemCode = IV00101.ITEMNMBR
LEFT JOIN dbo.IV40201 ON dbo.IV00101.UOMSCHDL = dbo.IV40201.UOMSCHDL
WHERE TrxUnit.TrackingOption = 3

Have more questions? Submit a request

Comments