Esta biblioteca contiene consultas SQL esenciales para el Sistema de Gestión de Almacén P4. Todas las consultas son de solo lectura (sentencias SELECT) y están optimizadas para reportes y análisis.
-- Get full audit trail for all Purchase Orders with order details
SELECT
ar.Timestamp,
ar.Type as AuditType,
ar.SubType as AuditSubType,
ar.Username,
po.PurchaseOrderNumber,
po.PurchaseOrderState,
v.VendorCode,
v.CompanyName as VendorName,
w.WarehouseCode,
ar.Sku,
ar.Quantity,
ar.FromBin,
ar.ToBin,
ar.FromLpn,
ar.ToLpn,
ar.LotNumber,
ar.ExpiryDate,
ar.Reason,
ar.IntegrationMessage
FROM AuditRecords ar
INNER JOIN PurchaseOrders po ON ar.ReferenceId = po.Id
LEFT JOIN Vendors v ON po.VendorId = v.Id
LEFT JOIN Warehouses w ON po.WarehouseId = w.Id
WHERE ar.ReferenceType = 'PurchaseOrder'
ORDER BY ar.Timestamp DESC;
-- Get full audit trail for all Pick Tickets with order details
SELECT
ar.Timestamp,
ar.Type as AuditType,
ar.SubType as AuditSubType,
ar.Username,
pt.PickTicketNumber,
pt.PickTicketState,
c.CustomerCode,
c.CompanyName as CustomerName,
w.WarehouseCode,
ar.Sku,
ar.Quantity,
ar.FromBin,
ar.ToBin,
ar.FromLpn,
ar.ToLpn,
ar.LotNumber,
ar.ExpiryDate,
ar.Reason
FROM AuditRecords ar
INNER JOIN PickTickets pt ON ar.ReferenceId = pt.Id
LEFT JOIN Customers c ON pt.CustomerId = c.Id
LEFT JOIN Warehouses w ON pt.WarehouseId = w.Id
WHERE ar.ReferenceType = 'PickTicket'
ORDER BY ar.Timestamp DESC;
-- Get audit records for both PO and PT within a date range
SELECT
ar.Timestamp,
ar.ReferenceType,
ar.ReferenceCode as OrderNumber,
ar.Type as ActionType,
ar.Username,
ar.Sku,
ar.Quantity,
ar.FromBin,
ar.ToBin,
CASE
WHEN ar.ReferenceType = 'PurchaseOrder' THEN po.PurchaseOrderState
WHEN ar.ReferenceType = 'PickTicket' THEN pt.PickTicketState
END as CurrentState,
ar.Reason
FROM AuditRecords ar
LEFT JOIN PurchaseOrders po ON ar.ReferenceId = po.Id AND ar.ReferenceType = 'PurchaseOrder'
LEFT JOIN PickTickets pt ON ar.ReferenceId = pt.Id AND ar.ReferenceType = 'PickTicket'
WHERE ar.ReferenceType IN ('PurchaseOrder', 'PickTicket')
AND ar.Timestamp >= '2024-01-01'
AND ar.Timestamp <= '2024-12-31'
ORDER BY ar.Timestamp DESC;
-- Track specific user activities across all Operations
SELECT
ar.Timestamp,
ar.Username,
ar.Type as ActionType,
ar.SubType,
ar.ReferenceType,
ar.ReferenceCode,
ar.Sku,
ar.Quantity,
ar.FromBin,
ar.ToBin,
ar.Reason
FROM AuditRecords ar
WHERE ar.Username = 'username_here'
AND ar.Timestamp >= DATEADD(day, -7, GETDATE())
ORDER BY ar.Timestamp DESC;
-- Get all active POs with their line items and product details
SELECT
po.PurchaseOrderNumber,
po.PurchaseOrderState,
po.RequiredDate,
v.VendorCode,
v.CompanyName as VendorName,
w.WarehouseCode,
pol.LineNumber,
p.Sku,
p.Description as ProductDescription,
pol.OrderedQuantity,
pol.ReceivedQuantity,
(pol.OrderedQuantity - pol.ReceivedQuantity) as RemainingQuantity,
pol.LotNumber,
pol.Instructions
FROM PurchaseOrders po
INNER JOIN PurchaseOrderLines pol ON po.Id = pol.PurchaseOrderId
INNER JOIN Products p ON pol.ProductId = p.Id
LEFT JOIN Vendors v ON po.VendorId = v.Id
INNER JOIN Warehouses w ON po.WarehouseId = w.Id
WHERE po.PurchaseOrderState NOT IN ('Closed', 'Cancelled')
ORDER BY po.PurchaseOrderNumber, pol.LineNumber;
-- Monitor Receiving progress for open POs
SELECT
po.PurchaseOrderNumber,
po.PurchaseOrderState,
po.AppointmentDate,
v.CompanyName as VendorName,
dd.Name as DockDoor,
u.Username as AssignedTo,
po.TotalLines,
po.TotalQuantity,
po.ReceivingStarted,
po.ReceivingCompleted,
SUM(pol.OrderedQuantity) as TotalOrdered,
SUM(pol.ReceivedQuantity) as TotalReceived,
CAST(SUM(pol.ReceivedQuantity) * 100.0 / NULLIF(SUM(pol.OrderedQuantity), 0) as DECIMAL(5,2)) as PercentReceived
FROM PurchaseOrders po
LEFT JOIN PurchaseOrderLines pol ON po.Id = pol.PurchaseOrderId
LEFT JOIN Vendors v ON po.VendorId = v.Id
LEFT JOIN DockDoors dd ON po.DockDoorId = dd.Id
LEFT JOIN Users u ON po.AssignedUserId = u.Id
WHERE po.PurchaseOrderState IN ('Released', 'Receiving')
GROUP BY po.Id, po.PurchaseOrderNumber, po.PurchaseOrderState,
po.AppointmentDate, v.CompanyName, dd.Name, u.Username,
po.TotalLines, po.TotalQuantity, po.ReceivingStarted, po.ReceivingCompleted
ORDER BY po.AppointmentDate;
-- Find all POs with backorder relationships
SELECT
parent.PurchaseOrderNumber as OriginalPO,
parent.PurchaseOrderState as OriginalState,
child.PurchaseOrderNumber as BackorderPO,
child.PurchaseOrderState as BackorderState,
child.DateCreated as BackorderCreated,
v.CompanyName as VendorName
FROM PurchaseOrders child
INNER JOIN PurchaseOrders parent ON child.ParentBackOrderId = parent.Id
LEFT JOIN Vendors v ON parent.VendorId = v.Id
ORDER BY parent.PurchaseOrderNumber, child.DateCreated;
-- Monitor pick ticket Allocation and Fulfillment
SELECT
pt.PickTicketNumber,
pt.PickTicketState,
pt.RequiredDate,
c.CustomerCode,
c.CompanyName as CustomerName,
w.WarehouseCode,
pt.TotalLines,
pt.TotalQuantity,
pt.PercentageAllocated,
pt.PercentagePicked,
u.Username as AssignedPicker,
pt.ShipToName,
pt.ShipToCity,
pt.ShipToStateProvince,
pt.FreightType
FROM PickTickets pt
LEFT JOIN Customers c ON pt.CustomerId = c.Id
INNER JOIN Warehouses w ON pt.WarehouseId = w.Id
LEFT JOIN Users u ON pt.AssignedUserId = u.Id
WHERE pt.PickTicketState NOT IN ('Closed', 'Cancelled', 'Shipped')
ORDER BY pt.RequiredDate, pt.PickTicketNumber;
-- Get pick ticket lines with their Inventory allocations
SELECT
pt.PickTicketNumber,
ptl.LineNumber,
p.Sku,
p.Description,
ptl.OrderedQuantity,
ptl.AllocatedQuantity,
ptl.PickedQuantity,
ptl.ShippedQuantity,
ir.PickSequence,
b.BinCode,
lp.LicensePlateCode,
ir.Quantity as ReservedQuantity
FROM PickTickets pt
INNER JOIN PickTicketLines ptl ON pt.Id = ptl.PickTicketId
INNER JOIN Products p ON ptl.ProductId = p.Id
LEFT JOIN InventoryReservations ir ON ptl.Id = ir.PickTicketLineId
LEFT JOIN InventoryLocations il ON ir.InventoryLocationId = il.Id
LEFT JOIN Bins b ON il.BinId = b.Id
LEFT JOIN LicensePlates lp ON il.LicensePlateId = lp.Id
WHERE pt.PickTicketState IN ('Released', 'Allocated', 'Picking')
ORDER BY pt.PickTicketNumber, ptl.LineNumber, ir.PickSequence;
-- Analyze on-time Shipping performance
SELECT
CAST(pt.RequiredDate as DATE) as RequiredShipDate,
COUNT(DISTINCT pt.Id) as TotalOrders,
COUNT(DISTINCT CASE WHEN pt.ShippedDate <= pt.RequiredDate THEN pt.Id END) as OnTimeOrders,
COUNT(DISTINCT CASE WHEN pt.ShippedDate > pt.RequiredDate THEN pt.Id END) as LateOrders,
COUNT(DISTINCT CASE WHEN pt.ShippedDate IS NULL AND pt.RequiredDate < GETDATE() THEN pt.Id END) as OverdueOrders,
CAST(COUNT(DISTINCT CASE WHEN pt.ShippedDate <= pt.RequiredDate THEN pt.Id END) * 100.0 /
NULLIF(COUNT(DISTINCT CASE WHEN pt.ShippedDate IS NOT NULL THEN pt.Id END), 0) as DECIMAL(5,2)) as OnTimePercentage
FROM PickTickets pt
WHERE pt.RequiredDate >= DATEADD(month, -3, GETDATE())
AND pt.PickTicketState NOT IN ('Cancelled')
GROUP BY CAST(pt.RequiredDate as DATE)
ORDER BY RequiredShipDate DESC;
-- View cartonization results for pick tickets
SELECT
pt.PickTicketNumber,
c.CustomerCode,
cr.CartonNumber,
cs.Name as CartonSize,
cr.Sscc18Code,
cr.PackedWeight,
cr.WeightUnitOfMeasure,
COUNT(crc.Id) as ItemCount,
SUM(crc.Quantity) as TotalUnits
FROM PickTickets pt
INNER JOIN CartonizationResults cr ON pt.Id = cr.PickTicketId
INNER JOIN CartonizationResultContents crc ON cr.Id = crc.CartonizationResultId
LEFT JOIN CartonSizes cs ON cr.CartonSizeId = cs.Id
LEFT JOIN Customers c ON pt.CustomerId = c.Id
GROUP BY pt.PickTicketNumber, c.CustomerCode, cr.CartonNumber,
cs.Name, cr.Sscc18Code, cr.PackedWeight, cr.WeightUnitOfMeasure
ORDER BY pt.PickTicketNumber, cr.CartonNumber;
-- Get current Inventory levels by bin and product
SELECT
w.WarehouseCode,
z.ZoneCode,
b.BinCode,
p.Sku,
p.Description,
p.Category,
lp.LicensePlateCode,
il.Quantity,
ild.LotNumber,
ild.SerialNumber,
ild.ExpiryDate,
pp.Name as Packsize,
pp.EachCount
FROM InventoryLocations il
INNER JOIN InventoryLocationDetails ild ON il.Id = ild.InventoryLocationId
INNER JOIN Products p ON il.ProductId = p.Id
LEFT JOIN Bins b ON il.BinId = b.Id
LEFT JOIN Zones z ON b.ZoneId = z.Id
LEFT JOIN Warehouses w ON z.WarehouseId = w.Id
LEFT JOIN LicensePlates lp ON il.LicensePlateId = lp.Id
LEFT JOIN ProductPacksizes pp ON ild.ProductPacksizeId = pp.Id
WHERE il.Quantity > 0
ORDER BY w.WarehouseCode, z.ZoneCode, b.BinCode, p.Sku;
-- Find Products below minimum stock levels
SELECT
w.WarehouseCode,
p.Sku,
p.Description,
p.Category,
pwm.MinQuantity,
pwm.MaxQuantity,
COALESCE(SUM(il.Quantity), 0) as CurrentStock,
pwm.MinQuantity - COALESCE(SUM(il.Quantity), 0) as BelowMinBy,
CASE
WHEN COALESCE(SUM(il.Quantity), 0) = 0 THEN 'OUT OF STOCK'
WHEN COALESCE(SUM(il.Quantity), 0) < pwm.MinQuantity THEN 'LOW STOCK'
WHEN COALESCE(SUM(il.Quantity), 0) > pwm.MaxQuantity THEN 'OVERSTOCK'
ELSE 'OK'
END as StockStatus
FROM Products p
INNER JOIN ProductWarehouseMinMaxes pwm ON p.Id = pwm.ProductId
INNER JOIN Warehouses w ON pwm.WarehouseId = w.Id
LEFT JOIN InventoryLocations il ON p.Id = il.ProductId
AND il.BinId IN (SELECT Id FROM Bins WHERE ZoneId IN (SELECT Id FROM Zones WHERE WarehouseId = w.Id))
WHERE p.IsDiscontinued = 0
GROUP BY w.WarehouseCode, p.Sku, p.Description, p.Category, pwm.MinQuantity, pwm.MaxQuantity
HAVING COALESCE(SUM(il.Quantity), 0) < pwm.MinQuantity
ORDER BY w.WarehouseCode, BelowMinBy DESC;
-- Find Inventory expiring within specified days
DECLARE @DaysToExpiry INT = 30;
SELECT
w.WarehouseCode,
z.ZoneCode,
b.BinCode,
p.Sku,
p.Description,
ild.LotNumber,
ild.ExpiryDate,
DATEDIFF(day, GETDATE(), ild.ExpiryDate) as DaysUntilExpiry,
ild.Quantity,
lp.LicensePlateCode,
CASE
WHEN ild.ExpiryDate < GETDATE() THEN 'EXPIRED'
WHEN DATEDIFF(day, GETDATE(), ild.ExpiryDate) <= 7 THEN 'CRITICAL'
WHEN DATEDIFF(day, GETDATE(), ild.ExpiryDate) <= 30 THEN 'WARNING'
ELSE 'OK'
END as ExpiryStatus
FROM InventoryLocationDetails ild
INNER JOIN InventoryLocations il ON ild.InventoryLocationId = il.Id
INNER JOIN Products p ON il.ProductId = p.Id
LEFT JOIN Bins b ON il.BinId = b.Id
LEFT JOIN Zones z ON b.ZoneId = z.Id
LEFT JOIN Warehouses w ON z.WarehouseId = w.Id
LEFT JOIN LicensePlates lp ON il.LicensePlateId = lp.Id
WHERE ild.ExpiryDate IS NOT NULL
AND DATEDIFF(day, GETDATE(), ild.ExpiryDate) <= @DaysToExpiry
AND ild.Quantity > 0
ORDER BY ild.ExpiryDate, w.WarehouseCode, p.Sku;
-- Get all pending cycle counts with variance analysis
SELECT
pcc.DateCreated as CountRequested,
w.WarehouseCode,
z.ZoneCode,
b.BinCode,
p.Sku,
p.Description,
pcc.PreviousQuantity,
pcc.NewQuantity,
pcc.Adjustment,
ABS(pcc.Adjustment) as AbsoluteVariance,
CASE
WHEN pcc.PreviousQuantity = 0 THEN 100
ELSE CAST(ABS(pcc.Adjustment) * 100.0 / pcc.PreviousQuantity as DECIMAL(5,2))
END as VariancePercent,
u.Username as CountedBy,
pcc.Reason,
pcc.CycleCountStyle
FROM PendingCycleCounts pcc
INNER JOIN Products p ON pcc.ProductId = p.Id
LEFT JOIN Bins b ON pcc.BinId = b.Id
LEFT JOIN Zones z ON b.ZoneId = z.Id
LEFT JOIN Warehouses w ON z.WarehouseId = w.Id
LEFT JOIN Users u ON pcc.UserId = u.Id
ORDER BY pcc.DateCreated DESC;
-- Get Customer order history with statistics
SELECT
c.CustomerCode,
c.CompanyName,
COUNT(DISTINCT pt.Id) as TotalOrders,
SUM(pt.TotalQuantity) as TotalUnitsShipped,
MIN(pt.DateCreated) as FirstOrderDate,
MAX(pt.ShippedDate) as LastShipDate,
AVG(DATEDIFF(day, pt.ReleasedToFloorDate, pt.ShippedDate)) as AvgDaysToShip,
COUNT(DISTINCT CASE WHEN pt.ShippedDate > pt.RequiredDate THEN pt.Id END) as LateShipments
FROM Customers c
LEFT JOIN PickTickets pt ON c.Id = pt.CustomerId
WHERE pt.PickTicketState = 'Closed'
GROUP BY c.CustomerCode, c.CompanyName
ORDER BY TotalOrders DESC;
-- Analyze vendor Delivery performance
SELECT
v.VendorCode,
v.CompanyName,
COUNT(DISTINCT po.Id) as TotalPOs,
SUM(po.TotalQuantity) as TotalUnitsOrdered,
COUNT(DISTINCT CASE WHEN po.ReceivingCompleted <= po.RequiredDate THEN po.Id END) as OnTimePOs,
COUNT(DISTINCT CASE WHEN po.ReceivingCompleted > po.RequiredDate THEN po.Id END) as LatePOs,
AVG(DATEDIFF(day, po.ReleasedDate, po.ReceivingCompleted)) as AvgDaysToReceive,
CAST(COUNT(DISTINCT CASE WHEN po.ReceivingCompleted <= po.RequiredDate THEN po.Id END) * 100.0 /
NULLIF(COUNT(DISTINCT po.Id), 0) as DECIMAL(5,2)) as OnTimePercentage
FROM Vendors v
LEFT JOIN PurchaseOrders po ON v.Id = po.VendorId
WHERE po.PurchaseOrderState = 'Closed'
AND po.DateCreated >= DATEADD(month, -6, GETDATE())
GROUP BY v.VendorCode, v.CompanyName
ORDER BY TotalPOs DESC;
-- Analyze Customer returns by reason and product
SELECT
c.CustomerCode,
c.CompanyName,
cr.CustomerReturnNumber,
cr.CustomerReturnState,
cr.TrackingNumber,
p.Sku,
p.Description,
crl.Quantity,
crl.ReceivedQuantity,
crl.DamagedQuantity,
cr.Comments,
cr.DateCreated,
cr.ReceivingCompleted
FROM CustomerReturns cr
INNER JOIN CustomerReturnLines crl ON cr.Id = crl.CustomerReturnId
INNER JOIN Products p ON crl.ProductId = p.Id
INNER JOIN Customers c ON cr.CustomerId = c.Id
WHERE cr.DateCreated >= DATEADD(month, -3, GETDATE())
ORDER BY cr.DateCreated DESC;
-- Monitor active Production orders and their status
SELECT
po.ProductionOrderNumber,
po.ProductionOrderState,
po.ProductionStep,
wf.Name as WorkflowName,
inprod.Sku as InputProduct,
po.InQuantity as InputQuantity,
outprod.Sku as OutputProduct,
po.OutQuantity as OutputQuantity,
b.BinCode as WorkAreaBin,
Was this page helpful?