Skip to content
WiMetrix Docs

Packing

Purpose

Section titled “Purpose”

This T-SQL query fetches data from multiple tables in the Essentials and Packing schemas to generate a comprehensive data on carton details, pre-packing information, shipping, and associated locations. It organizes data using several common table expressions (CTEs) and ends by inserting the results into [dbo].[PackingData].

Dependencies

Section titled “Dependencies”

This query depends on the following tables across different schemas:

  • Essentials Schema

    • [Essentials].[Location]: Provides main location information.
    • [Essentials].[SubLocation]: Contains sub-location data tied to each location.
    • [Essentials].[ShipDetail]: Includes shipping details associated with each carton.
    • [Essentials].[UpcMappingDetail]: Maps UPC details related to items within each carton.
    • [Essentials].[PackAndShipDetail]: Links packing and shipping details.
    • [Essentials].[PODetail]: Holds PO details for each item in a shipment.

  • Packing Schema

    • [Packing].[CartonContent]: Contains details of the contents within each carton.
    • [Packing].[CartonDetail]: Main table for carton information, including carton IDs and packing details.
    • [Packing].[PackJobDetail]: Links carton details with the packing job it belongs to.
    • [Packing].[PackStation]: Identifies the pack station associated with each packing job.
    • [Packing].[PrePackingDetail]: Links cartons with pre-packing information and blueprints.
    • [Packing].[PrePackingList]: Lists pre-packing information for each carton.
    • [Packing].[CartonBluePrint]: Contains blueprints defining carton dimensions and configurations.
    • [Packing].[BluePrintUpcMapping]: Maps blueprint details with UPCs for packing specifications.

Target Table

Section titled “Target Table”
  • [dbo].[PackingData]: Target table where the final query result is inserted.

Query Breakdown

Section titled “Query Breakdown”

1. LocationDetail CTE

Section titled “1. LocationDetail CTE”

Joins [Essentials].[Location] and [Essentials].[SubLocation] to obtain location names and sub-location details for cartons.

WITH LocationDetail AS (
SELECT
  l.LocationID,sl.SubLocationID,l.Description AS LocationName,LocationType,sl.Name AS SubLocationName
FROM
  [Essentials].[Location] l
JOIN
  [Essentials].[SubLocation] sl ON sl.LocationID=l.LocationID
)

  • Fields:

    • LocationID, SubLocationID: Unique identifiers for locations and sub-locations.
    • LocationName: Description of the main location.
    • LocationType: Type or category of the location.
    • SubLocationName: Name of the sub-location.

  • Purpose: Provides detailed location and sub-location data, which is later joined to carton details for mapping current, target, and previous locations.

2. RankedCartons CTE

Section titled “2. RankedCartons CTE”

Applies a DENSE_RANK() function to rank records from [Packing].[CartonContent] based on CartonScanDate in descending order.

RankedCartons AS (
    SELECT
        *,
        DENSE_RANK() OVER (PARTITION BY CartonDetailID, UpcMappingDetailID ORDER BY CartonScanDate DESC) AS Rank
    FROM [Packing].[CartonContent]
)

  • Fields:

    • All fields from [Packing].[CartonContent], plus:
    • Rank: Assigned rank for each carton content based on the latest CartonScanDate.

  • Purpose: Identifies the latest scan data for each CartonDetailID and UpcMappingDetailID to ensure the most recent carton scan is processed.

3. ScannedCartons CTE

Section titled “3. ScannedCartons CTE”

Aggregates data from RankedCartons to determine the scanned quantity and latest scan time for each carton detail.

ScannedCartons AS (
SELECT
  CartonDetailID,CartonScanDate,SUM(Quantity) AS ScannedQty,MAX(Timestamp) AS LastScannedTime
FROM
  RankedCartons
WHERE
  Rank = 1
GROUP BY
  CartonDetailID,CartonScanDate
)

  • Fields:

    • CartonDetailID, CartonScanDate: Identifiers for each scanned carton and date.
    • ScannedQty: Total quantity of items scanned per carton.
    • LastScannedTime: Timestamp of the most recent scan.

  • Purpose: Prepares summary data of carton scans, focusing only on the most recent scan by filtering for Rank = 1.

4. CartonDetail CTE

Section titled “4. CartonDetail CTE”

Combines carton details with location, pack job, and scan data to compile enriched carton information.

CartonDetail AS (
SELECT
  cd.CartonDetailID,PrePackingDetailID,CartonNumber,CartonNumberGlobal,CartonSequence,CartonSequenceGlobal,CartonStatus,TransferStatus AS CartonTransferStatus,ItemSizes,
  DetailUPCs,WeightPerUnits,AllowedQuantity,QtyPerCartons,UnitCosts,AccessoriesWeights,CartonWeight,CartonNetWeight,CartonType,CustomerBarCode,cd.SubLocationID AS CurrentSubLocationID,
  cl.LocationName AS CurrentLocationName,cl.SubLocationName AS CurrentSubLocationName,cd.ToSubLocationID,tl.LocationName AS ToLocationName,tl.SubLocationName AS ToSubLocationName,
  cd.LastSubLocationID,ll.LocationName AS LastLocationName,ll.SubLocationName AS LastSubLocationName,TransferredToID,ps.Name AS PackStationName,CompanyId,BrId AS Branch,cd.CreatedBy,cd.UpdatedBy,
  sc.ScannedQty,sc.CartonScanDate,sc.LastScannedTime,cd.CreatedAt,cd.UpdatedAt
FROM
  [Packing].[CartonDetail] cd
JOIN
  [Packing].[PackJobDetail] pjd ON pjd.CartonDetailID=cd.CartonDetailID
JOIN
  ScannedCartons sc
  ON sc.CartonDetailID=cd.CartonDetailID
JOIN
  [Packing].[PackStation] ps ON ps.PackStationID=pjd.PackStationID
LEFT JOIN
  [LocationDetail] cl ON cl.SubLocationID=cd.SubLocationID
LEFT JOIN
  [LocationDetail] tl ON tl.SubLocationID=cd.ToSubLocationID
LEFT JOIN
  [LocationDetail] ll ON ll.SubLocationID=cd.LastSubLocationID
)
  • Fields:
    • Extensive list of carton attributes such as CartonNumber, CartonSequence, weights, dimensions, and status fields (e.g., CartonStatus, CartonTransferStatus).
    • Location details like CurrentLocationName, ToLocationName, and LastLocationName.
    • ScannedQty, CartonScanDate, and LastScannedTime from ScannedCartons.
  • Purpose: Provides a detailed view of each carton, including its current status, location information, and the latest scan details. It serves as a foundation for the final report.

5. DataSetQuery CTE

Section titled “5. DataSetQuery CTE”

Enriches carton data with pre-packing, shipping, and order details by joining several related tables.

DataSetQuery AS (
SELECT
  cd.CartonDetailID,cd.CartonNumber,cd.CartonNumberGlobal,cd.CartonSequence,cd.CartonSequenceGlobal,cd.ItemSizes,upc.ItemSize,blupc.MaxQty AS PackRatio,cd.QtyPerCartons,
  CASE WHEN QtyPerCartons LIKE '%-%' THEN blupc.MaxQty ELSE cd.AllowedQuantity END AS AllowedQuantity,cd.AllowedQuantity AS CartonAllowedQuantity,cd.CartonWeight,cd.CartonNetWeight,
  cd.AccessoriesWeights,ppld.PrePackingDetailID,ppld.PrePackingListID,ppld.CartonBluePrintID,ppld.NumCartons,ppld.OrderQty AS OrderQtyInPrePack,upc.OrderQty AS OrderQtyInUPC,psh.OrderQty,
  ppld.PlanQty,ppl.ListName AS PrePackingListName,ppl.ShipDetailID,ppl.PackStatus,cd.CartonStatus,cd.CartonTransferStatus,ppl.TransferStatus AS PrePackTransferStatus,cbp.PackAndShipDetailID,
  cbp.PackType,cbp.BluePrintType,cbp.CLength,cbp.CWidth,cbp.CHeight,upc.PODetailID,po.OrId,po.PO,po.StyleID,po.BuyMonth,po.CompanyId AS PoCompanyId,po.ColorCode,po.ColorDesc, upc.SizeId,
  upc.DetailUPC,shp.ShipTo , shp.TransportMethod, shp.ExFactoryDate,upc.DescriptionDtl,upc.HTS,psh.Kit,shp.StoreDetail,psh.UDT,psh.ODT,shp.ShipType,cd.DetailUPCs,po.OrderType,CartonType,
  cd.CurrentLocationName,cd.CurrentSubLocationName,cd.ToLocationName,cd.ToSubLocationName,cd.LastLocationName,cd.LastSubLocationName,cd.CompanyId,cd.Branch,cd.PackStationName,cd.ScannedQty,
  cd.CartonScanDate,cd.LastScannedTime,cd.CreatedAt,cd.UpdatedAt
FROM CartonDetail cd
JOIN [Packing].[PrePackingDetail] ppld ON ppld.PrePackingDetailID=cd.PrePackingDetailID
JOIN [Packing].[PrePackingList] ppl ON ppl.PrePackingListID=ppld.PrePackingListID
JOIN [Packing].[CartonBluePrint] cbp ON cbp.CartonBluePrintID=ppld.CartonBluePrintID
JOIN [Packing].[BluePrintUpcMapping] blupc ON blupc.CartonBluePrintID=cbp.CartonBluePrintID
JOIN [Essentials].[ShipDetail] shp ON shp.ShipDetailID=cbp.ShipDetailID AND shp.ShipDetailID=ppl.ShipDetailID
JOIN [Essentials].[UpcMappingDetail] upc ON upc.UpcMappingDetailID=blupc.UpcMappingDetailID
JOIN [Essentials].[PackAndShipDetail] psh ON psh.ShipDetailID=shp.ShipDetailID AND psh.UpcMappingDetailID=upc.UpcMappingDetailID
JOIN [Essentials].[PODetail] po ON po.PODetailID=shp.PODetailID
)
  • Fields:
    • Carton details, size, pack ratio, dimensions, pre-packing list information, and associated order details.
    • Shipping and company-specific details (e.g., Exfactory, StoreDetail, TransportMethod, ShipTo).
    • PackStationName, CompanyId, Branch, ScannedQty, CartonScanDate, LastScannedTime.
  • Purpose: Assembles final enriched data across carton, location, packing, and shipping details, preparing it for the output.

6. Final SELECT Statement

Section titled “6. Final SELECT Statement”

The final select extracts all required fields from DataSetQuery and inserts them into [dbo].[PackingData] with an order by CartonDetailID.

-- Insert Data Into [dbo].[PackingData]
INSERT INTO [dbo].[PackingData] (
    CartonDetailID, CartonNumber, CartonNumberGlobal, CartonSequence, CartonSequenceGlobal, ItemSizes, ItemSize, PackRatio, QtyPerCartons, AllowedQuantity, CartonAllowedQuantity, CartonWeight,CartonNetWeight, AccessoriesWeights, PrePackingDetailID, PrePackingListID, CartonBluePrintID, NumCartons, OrderQtyInPrePack, OrderQtyInUPC, OrderQty, PlanQty, PrePackingListName,ShipDetailID, PackStatus, CartonStatus, CartonTransferStatus, PrePackTransferStatus, PackAndShipDetailID, PackType, BluePrintType, CLength, CWidth, CHeight, PODetailID, OrId, PO,StyleID, BuyMonth, CompanyId, ColorCode, ColorDesc, DetailUPC, ShipTo, TransportMethod, ExFactoryDate, DescriptionDtl, HTS, Kit, StoreDetail, UDT, ODT, ShipType, SizeId, DetailUPCs,OrderType, CartonType,CurrentLocationName, CurrentSubLocationName, ToLocationName, ToSubLocationName, LastLocationName, LastSubLocationName, PackStationName, Branch, ScannedQty,CartonScanDate,LastScannedTime,CreatedAt, UpdatedAt
)
SELECT
  CartonDetailID, CartonNumber, CartonNumberGlobal, CartonSequence, CartonSequenceGlobal, ItemSizes, ItemSize, PackRatio, QtyPerCartons, AllowedQuantity, CartonAllowedQuantity, CartonWeight,CartonNetWeight, AccessoriesWeights, PrePackingDetailID, PrePackingListID, CartonBluePrintID, NumCartons, OrderQtyInPrePack, OrderQtyInUPC, OrderQty, PlanQty, PrePackingListName,ShipDetailID, PackStatus, CartonStatus, CartonTransferStatus, PrePackTransferStatus, PackAndShipDetailID, PackType, BluePrintType, CLength, CWidth, CHeight, PODetailID, OrId, PO,StyleID, BuyMonth, CompanyId, ColorCode, ColorDesc, DetailUPC, ShipTo, TransportMethod, ExFactoryDate, DescriptionDtl, HTS, Kit, StoreDetail, UDT, ODT, ShipType, SizeId, DetailUPCs,OrderType, CartonType,CurrentLocationName, CurrentSubLocationName, ToLocationName, ToSubLocationName, LastLocationName, LastSubLocationName, PackStationName, Branch, ScannedQty,CartonScanDate,LastScannedTime,CreatedAt, UpdatedAt
FROM
  DataSetQuery
ORDER BY
  CartonDetailID
  • Purpose: Consolidates data from various tables and CTEs, producing a detailed packing report in the target table, ordered by CartonDetailID.

Summary

Section titled “Summary”

This query is structured to create a detailed and comprehensive record of carton and packing details by:

  1. Collecting location details.
  2. Ranking and filtering scanned cartons.
  3. Compiling carton detail information.
  4. Enhancing with pre-packing, shipping, and order data.
  5. Inserting the final dataset into the PackingData table for downstream analysis or reporting.