Returns a list of order items from purchase orders created during the time frame that you specify.

View-Specific Information

Select

To retrieve detailed line items for a specific purchase order:

SELECT 
  PurchaseOrderNumber,
  ItemSequenceNumber,
  ItemTitle,
  VendorProductId,
  BuyerProductId,
  OrderedAmount,
  UnitOfMeasure,
  NetPrice,
  TotalPrice,
  CurrencyCode
FROM VendorCentral.DirectFulfillmentOrderItems
WHERE PurchaseOrderNumber = 'PO-12345-67890';

You can retrieve all order items requiring shipment from recent orders. The following query joins order items with order headers to show all items requiring shipment, sorted by deadline:

SELECT 
  PurchaseOrderNumber,
  ItemSequenceNumber,
  ItemTitle,
  VendorProductId,
  OrderedAmount,
  UnitOfMeasure,
  NetPrice,
  OrderDate,
  RequiredDate,
  ShipFromId
FROM VendorCentral.DirectFulfillmentOrderItems
INNER JOIN VendorCentral.DirectFulfillmentOrders 
  ON PurchaseOrderNumber = PurchaseOrderNumber
WHERE OrderStatus = 'NEW'
  AND OrderDate >= DATEADD(day, -7, GETDATE())
ORDER BY RequiredDate ASC, ItemSequenceNumber ASC;

To retrieve gift items requiring additional handling, such as requiring special packaging and gift messages:

SELECT 
  PurchaseOrderNumber,
  ItemSequenceNumber,
  ItemTitle,
  GiftMessage,
  GiftWrapId,
  OrderedAmount
FROM VendorCentral.DirectFulfillmentOrderItems
WHERE GiftMessage IS NOT NULL
  AND OrderStatus = 'NEW';

To retrieve scheduled delivery items with delivery windows:

SELECT 
  PurchaseOrderNumber,
  ItemSequenceNumber,
  ItemTitle,
  DeliveryServiceType,
  EarliestDeliveryDate,
  LatestDeliveryDate,
  OrderedAmount
FROM VendorCentral.DirectFulfillmentOrderItems
WHERE DeliveryServiceType IS NOT NULL
  AND EarliestDeliveryDate >= GETDATE()
ORDER BY EarliestDeliveryDate ASC;

To identify high-velocity items ordered frequently, helping with inventory planning and warehouse slotting decisions:

SELECT 
  VendorProductId,
  BuyerProductId,
  ItemTitle,
  COUNT(DISTINCT PurchaseOrderNumber) AS OrderFrequency,
  SUM(CAST(OrderedAmount AS INT)) AS TotalUnitsOrdered,
  AVG(CAST(NetPrice AS DECIMAL(18,2))) AS AvgUnitPrice,
  MIN(OrderDate) AS FirstOrderDate,
  MAX(OrderDate) AS LastOrderDate
FROM VendorCentral.DirectFulfillmentOrderItems 
INNER JOIN VendorCentral.DirectFulfillmentOrders 
  ON PurchaseOrderNumber = PurchaseOrderNumber
WHERE OrderDate >= DATEADD(day, -30, GETDATE())
GROUP BY 
  VendorProductId,
  BuyerProductId,
  ItemTitle
HAVING COUNT(DISTINCT PurchaseOrderNumber) >= 5
ORDER BY TotalUnitsOrdered DESC;

The HAVING clause filters the results to products ordered at least five times.

Columns