Biblioteca de Consultas de Facturación 3PL

Las reglas Service facturan todo lo que se pueda contar con SQL: recepción, picking, despacho, devoluciones, servicios de valor agregado, mínimos, escalones y recargos. Esta página explica cómo LogisticaHQ ejecuta estas consultas, cómo probarlas, y ofrece 45 consultas de ejemplo para adaptar.

Los nombres de botones y campos aparecen como en la interfaz en inglés. Si trabaja con la interfaz en español, verá su traducción equivalente.

Cómo funcionan las consultas de las reglas Service

LogisticaHQ ejecuta la consulta de una regla Service en dos momentos:

• Cuando se crea una factura.
• Cuando usted hace clic en Re-generate lines en una factura en Borrador.

La consulta se ejecuta sobre su base de datos de LogisticaHQ y puede leer cualquier tabla. Limitarla al cliente y al período correctos es tarea de la consulta.

Los dos parámetros: @Id y @Rate

La consulta recibe dos parámetros: @Id, el Id de la factura que se está armando, que es una fila de ClientInvoices, y @Rate, el campo Tarifa de la regla. Las consultas de abajo escriben sus precios como números para que cada una se entienda sola; reemplace un precio único por @Rate para guardarlo en la regla.

• No existen @ClientId, @StartDate ni @EndDate. Una consulta que use alguno de ellos falla con "Must declare the scalar variable".
• Obtenga el cliente y el período uniendo ClientInvoices con Id = @Id. Esa unión le da tres columnas:
  • ClientId: el Cliente 3PL.
  • StartPeriod: el primer día del período.
  • EndPeriod: el primer día después del período. Filtre con < EndPeriod, nunca con <= EndPeriod.
• Escriba la tarifa dentro de la consulta. El diálogo de la regla no tiene tarifa para las reglas Service.

Columnas del resultado

Las columnas se reconocen por su nombre. Las demás columnas se ignoran.

Columna	Tipo	Significado
Item	texto	El código de ítem que muestra la línea.
Description	texto	La descripción de la línea.
Rate	decimal	La tarifa por unidad. Una tarifa negativa genera una línea de crédito.
Quantity	decimal	La cantidad. Las filas con cantidad cero o menor se descartan.
AdditionalCharges	texto, opcional	Una lista separada por comas con los nombres de los cargos adicionales que aplican a la línea, por ejemplo ITBMS. Los nombres que no están en la lista de cargos de la factura se ignoran. Use NULL si la línea no lleva cargos.

Cómo las filas se convierten en líneas:

• Cada fila devuelta se convierte en una línea de la factura. El total de la línea es Tarifa × Cantidad.
• El motor no agrupa ni combina filas. Use GROUP BY para obtener menos líneas, o funciones de agregado sin GROUP BY para obtener una sola línea.
• La tarifa y la cantidad se guardan con cuatro decimales. Los ejemplos convierten ambas a decimal(18,4).
• Envuelva las sumas en ISNULL(..., 0), para que un período sin actividad devuelva 0 y la línea se descarte.

Una consulta no puede ver las líneas de la factura que se está armando. Las líneas se guardan solo cuando terminan todas las reglas. Para cobrar un mínimo o un escalón basado en otros cargos, recalcule esos cargos dentro de la consulta.

Plantilla de cliente y período

Arme cada consulta a partir de este patrón:

SELECT
    'ITEM-CODE'                        AS Item,
    'Text printed on the invoice line' AS Description,
    CAST(1.00 AS decimal(18,4))        AS Rate,
    CAST(COUNT(*) AS decimal(18,4))    AS Quantity,
    'ITBMS'                            AS AdditionalCharges
FROM ClientInvoices ci
JOIN AuditRecords a ON a.ClientId = ci.ClientId
WHERE ci.Id = @Id
  AND a.SubType = 'Pick'
  AND CAST(a.Timestamp AT TIME ZONE 'SA Pacific Standard Time' AS date) >= ci.StartPeriod
  AND CAST(a.Timestamp AT TIME ZONE 'SA Pacific Standard Time' AS date) <  ci.EndPeriod;

Cómo maneja la plantilla las fechas y horas:

• Las horas de la actividad se guardan en UTC. AT TIME ZONE las convierte a hora local antes de compararlas con las fechas del período.
• Los ejemplos usan 'SA Pacific Standard Time', que es UTC-5 sin horario de verano. Sirve para Panamá, Colombia, Ecuador y Perú.
• Si su almacén está en otra zona, reemplácela por el nombre de zona horaria de Windows que le corresponda.
• Las columnas que ya son fechas, como InventorySnapshots.SnapshotDate, se comparan directamente.

De dónde salen los datos

Fuente	Requiere	Cómo limitarla
BillingServiceRecords: la actividad del cliente en el período, guardada con la factura.	Generate Service Records (Business - Multi Client) activado al crear la factura.	ClientInvoiceId = @Id. Ya viene limitada al cliente y al período.
BillingStorageRecords: los movimientos de almacenaje del cliente en el período, guardados con la factura.	Generate Storage Records activado al crear la factura.	ClientInvoiceId = @Id.
InventorySnapshots: una fila por línea de existencia por día.	Enable Daily Snapshot (Business - Inventory). Las fotos se conservan 90 días.	Haga JOIN con Products por ProductId con Products.ClientId = ci.ClientId, y filtre SnapshotDate por el período.
AuditRecords: el historial completo de actividad.	Nada.	ClientId = ci.ClientId más el filtro de período sobre Timestamp.
Documentos: PurchaseOrders, PickTickets, ToteMasters, TruckLoads, CustomerReturns, ProductionOrders.	Nada.	El ClientId del documento más una columna de fecha, por ejemplo ReceivingCompleted, ShippedDate, ShipDate o ProductionCompleted.
Tablas de existencias como InventoryLocations.	Nada.	No tienen ClientId. Haga JOIN con Products y filtre por Products.ClientId.

Códigos de actividad en BillingServiceRecords

Cada fila de BillingServiceRecords agrupa los eventos de una actividad sobre un documento. Las columnas principales son:

• Type y SubType: la actividad.
• ReferenceType y ReferenceCode: el documento, por ejemplo PurchaseOrder y el número de la orden de compra.
• Count: la cantidad de eventos.
• Quantity: el total de unidades.
• NumberOfPacks: el total de empaques.

SubType	Actividad	Qué usar
ReceivePo, NonPoReceive	Recepción contra orden de compra, y recepción sin orden	Quantity, NumberOfPacks
Pick	Picking (ReferenceType PickTicket, es decir, Despacho)	Quantity
SmallParcelShip, TruckLoadShip, PrivateFleetShip, ExternalShip	Envío (Type Handling)	Count. Estos eventos no traen cantidad.
ReceiveRma, NonRmaReceive	Devoluciones de clientes	Quantity
AdjustIn, AdjustOut	Ajustes de inventario	Count o Quantity
Repackage, PalletPrinting, KitDispatch	Servicios de manejo	Count
(Type CustomAction)	Acciones personalizadas del equipo de mano. SubType es el código propio de la acción.	Count

En los eventos sin cantidad, SUM(Quantity) devuelve NULL. Use SUM(Count), o COUNT(*) sobre AuditRecords.

Pruebe la consulta antes de usarla

1. Genere una factura en Borrador para el cliente y el período que quiere probar.
2. Abra la factura y copie su Id de la barra de direcciones del navegador. Es la última parte de .../clientInvoiceDetails/<Id>.
3. Vaya a Setup > System > Query builder.
4. Declare @Id en la primera línea, terminada en punto y coma, y pegue su consulta debajo.
5. Ejecute la consulta. Verifique que las columnas se llamen Item, Description, Rate, Quantity y AdditionalCharges, y que las cantidades sean las esperadas.
6. Copie la consulta sin la línea DECLARE en el campo Sql de la regla y guarde la regla.
7. Haga clic en Re-generate lines en la factura en Borrador para ver el resultado.

DECLARE @Id UNIQUEIDENTIFIER = '00000000-0000-0000-0000-000000000000';

SELECT
    'PICK-UNIT'                                        AS Item,
    'Units picked'                                     AS Description,
    CAST(0.30 AS decimal(18,4))                        AS Rate,
    CAST(ISNULL(SUM(r.Quantity), 0) AS decimal(18,4))  AS Quantity,
    'ITBMS'                                            AS AdditionalCharges
FROM ClientInvoices ci
JOIN BillingServiceRecords r ON r.ClientInvoiceId = ci.Id
WHERE ci.Id = @Id
  AND r.SubType = 'Pick';

El Query builder está disponible solo para usuarios que no están vinculados a un cliente.

Consultas de ejemplo

Cada ejemplo devuelve las cinco columnas y une ClientInvoices con @Id. Adapte cada uno antes de usarlo:

• Reemplace los códigos de ítem, los textos y las tarifas por los suyos. Los textos de Item y Description pueden estar en español.
• Quite ITBMS donde el servicio no esté gravado.

Los ejemplos que leen BillingServiceRecords o BillingStorageRecords requieren la configuración de captura correspondiente. Los ejemplos de almacenaje basados en InventorySnapshots requieren Enable Daily Snapshot.

Recepción

1. Recepción por orden de compra

Cobra una tarifa fija por cada orden de compra recibida en el período. Requiere Generate Service Records.

-- 1. Recepción por orden de compra
SELECT
    'RCV-PO'                                            AS Item,
    'Receiving fee per purchase order'                  AS Description,
    CAST(15.00 AS decimal(18,4))                        AS Rate,
    CAST(COUNT(DISTINCT r.ReferenceId) AS decimal(18,4)) AS Quantity,
    'ITBMS'                                             AS AdditionalCharges
FROM ClientInvoices ci
JOIN BillingServiceRecords r ON r.ClientInvoiceId = ci.Id
WHERE ci.Id = @Id
  AND r.ReferenceType = 'PurchaseOrder'
  AND r.SubType = 'ReceivePo';

2. Recepción por línea de orden de compra

Cobra por cada línea de orden de compra con cantidad recibida, en órdenes cuya recepción terminó en el período. Lee las órdenes de compra directamente, así que no requiere ninguna configuración de captura.

-- 2. Recepción por línea de orden de compra
SELECT
    'RCV-LINE'                              AS Item,
    'Receiving fee per purchase order line' AS Description,
    CAST(1.25 AS decimal(18,4))             AS Rate,
    CAST(COUNT(*) AS decimal(18,4))         AS Quantity,
    'ITBMS'                                 AS AdditionalCharges
FROM ClientInvoices ci
JOIN PurchaseOrders po      ON po.ClientId = ci.ClientId
JOIN PurchaseOrderLines pol ON pol.PurchaseOrderId = po.Id
WHERE ci.Id = @Id
  AND po.IsWarehouseTransfer = 0
  AND pol.ReceivedQuantity > 0
  AND CAST(po.ReceivingCompleted AT TIME ZONE 'SA Pacific Standard Time' AS date) >= ci.StartPeriod
  AND CAST(po.ReceivingCompleted AT TIME ZONE 'SA Pacific Standard Time' AS date) <  ci.EndPeriod;

3. Recepción por unidad, una línea por documento

Cobra por unidad recibida y genera una línea de factura por orden de compra (o recepción sin orden), igual que el ejemplo incorporado. Requiere Generate Service Records.

-- 3. Recepción por unidad, una línea por documento
SELECT
    'RCV-UNIT'                                          AS Item,
    CONCAT('Units received ', ISNULL(r.ReferenceCode, '(no PO)')) AS Description,
    CAST(0.08 AS decimal(18,4))                         AS Rate,
    CAST(ISNULL(SUM(r.Quantity), 0) AS decimal(18,4))   AS Quantity,
    'ITBMS'                                             AS AdditionalCharges
FROM ClientInvoices ci
JOIN BillingServiceRecords r ON r.ClientInvoiceId = ci.Id
WHERE ci.Id = @Id
  AND r.SubType IN ('ReceivePo', 'NonPoReceive')
GROUP BY r.ReferenceCode;

4. Recepción por caja (empaque)

Cobra por caja recibida, usando el número de empaques registrado en cada recepción. Requiere Generate Service Records.

-- 4. Recepción por caja (empaque)
SELECT
    'RCV-CASE'                                              AS Item,
    'Cases received'                                        AS Description,
    CAST(0.35 AS decimal(18,4))                             AS Rate,
    CAST(ISNULL(SUM(r.NumberOfPacks), 0) AS decimal(18,4))  AS Quantity,
    'ITBMS'                                                 AS AdditionalCharges
FROM ClientInvoices ci
JOIN BillingServiceRecords r ON r.ClientInvoiceId = ci.Id
WHERE ci.Id = @Id
  AND r.SubType IN ('ReceivePo', 'NonPoReceive');

5. Recepción por paleta (LPN)

Cobra por cada placa (paleta) distinta en la que se recibió mercancía. Lee el historial de auditoría directamente, así que no requiere configuración de captura.

-- 5. Recepción por paleta (LPN)
SELECT
    'RCV-PALLET'                                    AS Item,
    'Pallets received'                              AS Description,
    CAST(4.50 AS decimal(18,4))                     AS Rate,
    CAST(COUNT(DISTINCT a.ToLpnId) AS decimal(18,4)) AS Quantity,
    'ITBMS'                                         AS AdditionalCharges
FROM ClientInvoices ci
JOIN AuditRecords a ON a.ClientId = ci.ClientId
WHERE ci.Id = @Id
  AND a.SubType IN ('ReceivePo', 'NonPoReceive')
  AND a.ToLpnId IS NOT NULL
  AND CAST(a.Timestamp AT TIME ZONE 'SA Pacific Standard Time' AS date) >= ci.StartPeriod
  AND CAST(a.Timestamp AT TIME ZONE 'SA Pacific Standard Time' AS date) <  ci.EndPeriod;

6. Recepción por contenedor de importación

Cobra por cada número de contenedor distinto en órdenes de compra cuya recepción terminó en el período.

-- 6. Recepción por contenedor de importación
SELECT
    'RCV-CNTR'                                        AS Item,
    'Container unloading'                             AS Description,
    CAST(120.00 AS decimal(18,4))                     AS Rate,
    CAST(COUNT(DISTINCT po.Container) AS decimal(18,4)) AS Quantity,
    'ITBMS'                                           AS AdditionalCharges
FROM ClientInvoices ci
JOIN PurchaseOrders po ON po.ClientId = ci.ClientId
WHERE ci.Id = @Id
  AND ISNULL(po.Container, '') <> ''
  AND CAST(po.ReceivingCompleted AT TIME ZONE 'SA Pacific Standard Time' AS date) >= ci.StartPeriod
  AND CAST(po.ReceivingCompleted AT TIME ZONE 'SA Pacific Standard Time' AS date) <  ci.EndPeriod;

7. Recargo por recepción express

Agrega un recargo por cada orden de compra que su equipo marcó como express. Usa una convención: escriba EXPRESS en el campo Tag1 de la orden (adapte la columna y el valor a su práctica).

-- 7. Recargo por recepción express
SELECT
    'RCV-EXPRESS'                       AS Item,
    'Express receiving surcharge'       AS Description,
    CAST(35.00 AS decimal(18,4))        AS Rate,
    CAST(COUNT(*) AS decimal(18,4))     AS Quantity,
    'ITBMS'                             AS AdditionalCharges
FROM ClientInvoices ci
JOIN PurchaseOrders po ON po.ClientId = ci.ClientId
WHERE ci.Id = @Id
  AND po.Tag1 = 'EXPRESS'
  AND CAST(po.ReceivingCompleted AT TIME ZONE 'SA Pacific Standard Time' AS date) >= ci.StartPeriod
  AND CAST(po.ReceivingCompleted AT TIME ZONE 'SA Pacific Standard Time' AS date) <  ci.EndPeriod;

Ubicación de mercancía (put-away)

8. Put-away por paleta movida

Cobra por cada movimiento de placa (paleta). LogisticaHQ registra el put-away como un movimiento de piso normal, así que esta consulta cuenta todos los movimientos de paleta del período, no solo el put-away.

-- 8. Put-away por paleta movida
SELECT
    'PUT-PALLET'                    AS Item,
    'Put-away / pallet moves'       AS Description,
    CAST(2.00 AS decimal(18,4))     AS Rate,
    CAST(COUNT(*) AS decimal(18,4)) AS Quantity,
    'ITBMS'                         AS AdditionalCharges
FROM ClientInvoices ci
JOIN AuditRecords a ON a.ClientId = ci.ClientId
WHERE ci.Id = @Id
  AND a.SubType = 'LicensePlateMove'
  AND CAST(a.Timestamp AT TIME ZONE 'SA Pacific Standard Time' AS date) >= ci.StartPeriod
  AND CAST(a.Timestamp AT TIME ZONE 'SA Pacific Standard Time' AS date) <  ci.EndPeriod;

9. Put-away por unidad movida

Cobra por unidad movida entre ubicaciones (movimientos de producto y de ubicación completa). Igual que la anterior, incluye todos los movimientos, no solo el put-away.

-- 9. Put-away por unidad movida
SELECT
    'PUT-UNIT'                                        AS Item,
    'Put-away / units moved'                          AS Description,
    CAST(0.03 AS decimal(18,4))                       AS Rate,
    CAST(ISNULL(SUM(a.Quantity), 0) AS decimal(18,4)) AS Quantity,
    'ITBMS'                                           AS AdditionalCharges
FROM ClientInvoices ci
JOIN AuditRecords a ON a.ClientId = ci.ClientId
WHERE ci.Id = @Id
  AND a.SubType IN ('ProductMove', 'BinMove')
  AND CAST(a.Timestamp AT TIME ZONE 'SA Pacific Standard Time' AS date) >= ci.StartPeriod
  AND CAST(a.Timestamp AT TIME ZONE 'SA Pacific Standard Time' AS date) <  ci.EndPeriod;

Almacenaje

10. Almacenaje por paleta-día

Cuenta las paletas (placas) ocupadas del cliente en cada foto diaria de inventario y cobra por paleta-día. Requiere la configuración de foto diaria de inventario.

-- 10. Almacenaje por paleta-día
WITH Daily AS (
    SELECT s.SnapshotDate, COUNT(DISTINCT s.LicensePlate) AS Pallets
    FROM ClientInvoices ci
    JOIN InventorySnapshots s ON s.SnapshotDate >= ci.StartPeriod
                             AND s.SnapshotDate <  ci.EndPeriod
    JOIN Products p           ON p.Id = s.ProductId
                             AND p.ClientId = ci.ClientId
    WHERE ci.Id = @Id
      AND s.Quantity > 0
      AND s.LicensePlate IS NOT NULL
    GROUP BY s.SnapshotDate
)
SELECT
    'STO-PALLET-DAY'                                 AS Item,
    'Pallet storage (pallet-days)'                   AS Description,
    CAST(0.60 AS decimal(18,4))                      AS Rate,
    CAST(ISNULL(SUM(Pallets), 0) AS decimal(18,4))   AS Quantity,
    'ITBMS'                                          AS AdditionalCharges
FROM Daily;

11. Almacenaje por paleta-mes (tarifa mensual)

Mismo conteo de paleta-días, pero dividido entre 30 para que la factura muestre su tarifa mensual publicada por paleta.

-- 11. Almacenaje por paleta-mes (tarifa mensual)
WITH Daily AS (
    SELECT s.SnapshotDate, COUNT(DISTINCT s.LicensePlate) AS Pallets
    FROM ClientInvoices ci
    JOIN InventorySnapshots s ON s.SnapshotDate >= ci.StartPeriod
                             AND s.SnapshotDate <  ci.EndPeriod
    JOIN Products p           ON p.Id = s.ProductId
                             AND p.ClientId = ci.ClientId
    WHERE ci.Id = @Id
      AND s.Quantity > 0
      AND s.LicensePlate IS NOT NULL
    GROUP BY s.SnapshotDate
)
SELECT
    'STO-PALLET-MONTH'                                       AS Item,
    CONCAT('Pallet storage (', ISNULL(SUM(Pallets), 0), ' pallet-days / 30)') AS Description,
    CAST(18.00 AS decimal(18,4))                             AS Rate,
    CAST(ISNULL(SUM(Pallets), 0) / 30.0 AS decimal(18,4))    AS Quantity,
    'ITBMS'                                                  AS AdditionalCharges
FROM Daily;

12. Almacenaje por ubicación-día

Cuenta las ubicaciones distintas con mercancía del cliente en cada foto diaria y cobra por ubicación-día.

-- 12. Almacenaje por ubicación-día
WITH Daily AS (
    SELECT s.SnapshotDate, COUNT(DISTINCT s.Bin) AS Bins
    FROM ClientInvoices ci
    JOIN InventorySnapshots s ON s.SnapshotDate >= ci.StartPeriod
                             AND s.SnapshotDate <  ci.EndPeriod
    JOIN Products p           ON p.Id = s.ProductId
                             AND p.ClientId = ci.ClientId
    WHERE ci.Id = @Id
      AND s.Quantity > 0
      AND s.Bin IS NOT NULL
    GROUP BY s.SnapshotDate
)
SELECT
    'STO-BIN-DAY'                                AS Item,
    'Bin storage (bin-days)'                     AS Description,
    CAST(0.25 AS decimal(18,4))                  AS Rate,
    CAST(ISNULL(SUM(Bins), 0) AS decimal(18,4))  AS Quantity,
    'ITBMS'                                      AS AdditionalCharges
FROM Daily;

13. Almacenaje por metro cúbico-día

Multiplica la cantidad en existencia de cada día por el Cube del producto y cobra por metro cúbico-día. El Cube del producto se guarda en centímetros cúbicos cuando las dimensiones están en centímetros, por eso la consulta divide entre 1,000,000; si sus dimensiones están en pulgadas, divida entre 1,728 y cobre por pie cúbico.

-- 13. Almacenaje por metro cúbico-día
WITH Daily AS (
    SELECT s.SnapshotDate, SUM(s.Quantity * ISNULL(p.Cube, 0)) / 1000000.0 AS CubeOnHand
    FROM ClientInvoices ci
    JOIN InventorySnapshots s ON s.SnapshotDate >= ci.StartPeriod
                             AND s.SnapshotDate <  ci.EndPeriod
    JOIN Products p           ON p.Id = s.ProductId
                             AND p.ClientId = ci.ClientId
    WHERE ci.Id = @Id
      AND s.Quantity > 0
    GROUP BY s.SnapshotDate
)
SELECT
    'STO-CUBE-DAY'                                     AS Item,
    'Storage by volume (cubic metre-days)'             AS Description,
    CAST(0.45 AS decimal(18,4))                        AS Rate,
    CAST(ISNULL(SUM(CubeOnHand), 0) AS decimal(18,4))  AS Quantity,
    'ITBMS'                                            AS AdditionalCharges
FROM Daily;

14. Almacenaje por SKU

Cobra una tarifa mensual por cada SKU distinto que el cliente tuvo en existencia en algún momento del período.

-- 14. Almacenaje por SKU
SELECT
    'STO-SKU'                                          AS Item,
    'SKU maintenance (SKUs stored in the period)'      AS Description,
    CAST(2.00 AS decimal(18,4))                        AS Rate,
    CAST(COUNT(DISTINCT s.ProductId) AS decimal(18,4)) AS Quantity,
    'ITBMS'                                            AS AdditionalCharges
FROM ClientInvoices ci
JOIN InventorySnapshots s ON s.SnapshotDate >= ci.StartPeriod
                         AND s.SnapshotDate <  ci.EndPeriod
JOIN Products p           ON p.Id = s.ProductId
                         AND p.ClientId = ci.ClientId
WHERE ci.Id = @Id
  AND s.Quantity > 0;

15. Almacenaje por zona con tarifas distintas

Cobra paleta-días con una tarifa distinta por zona (por ejemplo, un cuarto frío). Reemplace los códigos de zona por los suyos.

-- 15. Almacenaje por zona con tarifas distintas
WITH Daily AS (
    SELECT s.SnapshotDate, s.Zone, COUNT(DISTINCT s.LicensePlate) AS Pallets
    FROM ClientInvoices ci
    JOIN InventorySnapshots s ON s.SnapshotDate >= ci.StartPeriod
                             AND s.SnapshotDate <  ci.EndPeriod
    JOIN Products p           ON p.Id = s.ProductId
                             AND p.ClientId = ci.ClientId
    WHERE ci.Id = @Id
      AND s.Quantity > 0
      AND s.LicensePlate IS NOT NULL
    GROUP BY s.SnapshotDate, s.Zone
)
SELECT
    'STO-ZONE'                                        AS Item,
    CONCAT('Pallet storage - zone ', ISNULL(Zone, '(none)')) AS Description,
    CAST(CASE Zone
            WHEN 'COLD' THEN 1.20
            WHEN 'RACK' THEN 0.60
            ELSE 0.50
         END AS decimal(18,4))                        AS Rate,
    CAST(SUM(Pallets) AS decimal(18,4))               AS Quantity,
    'ITBMS'                                           AS AdditionalCharges
FROM Daily
GROUP BY Zone;

16. Almacenaje con mínimo mensual

Factura las paleta-días y, cuando el monto queda por debajo del mínimo pactado, agrega una línea de ajuste por la diferencia. La línea de ajuste desaparece sola cuando no hace falta.

-- 16. Almacenaje con mínimo mensual
WITH Daily AS (
    SELECT s.SnapshotDate, COUNT(DISTINCT s.LicensePlate) AS Pallets
    FROM ClientInvoices ci
    JOIN InventorySnapshots s ON s.SnapshotDate >= ci.StartPeriod
                             AND s.SnapshotDate <  ci.EndPeriod
    JOIN Products p           ON p.Id = s.ProductId
                             AND p.ClientId = ci.ClientId
    WHERE ci.Id = @Id
      AND s.Quantity > 0
      AND s.LicensePlate IS NOT NULL
    GROUP BY s.SnapshotDate
),
Total AS (
    SELECT CAST(ISNULL(SUM(Pallets), 0) AS decimal(18,4)) AS PalletDays FROM Daily
)
SELECT 'STO-PALLET-DAY' AS Item, 'Pallet storage (pallet-days)' AS Description,
       CAST(0.60 AS decimal(18,4)) AS Rate, PalletDays AS Quantity, 'ITBMS' AS AdditionalCharges
FROM Total
UNION ALL
SELECT 'STO-MINIMUM', 'Storage minimum adjustment (minimum 150.00)',
       CAST(150.00 - PalletDays * 0.60 AS decimal(18,4)),
       CAST(CASE WHEN PalletDays * 0.60 < 150.00 THEN 1 ELSE 0 END AS decimal(18,4)),
       'ITBMS'
FROM Total;

17. Almacenaje por paleta-día desde los registros de almacenaje

Reconstruye el saldo diario de cada paleta a partir de los registros de almacenaje capturados con la factura (el patrón del ejemplo VTA_015) y cobra por paleta-día. Requiere Generate Storage Records en lugar de la foto diaria.

-- 17. Almacenaje por paleta-día desde los registros de almacenaje
WITH Period AS (
    SELECT ci.StartPeriod, ci.EndPeriod
    FROM ClientInvoices ci
    WHERE ci.Id = @Id
),
Days AS (
    SELECT StartPeriod AS BillDay, EndPeriod FROM Period WHERE StartPeriod < EndPeriod
    UNION ALL
    SELECT DATEADD(day, 1, BillDay), EndPeriod FROM Days WHERE DATEADD(day, 1, BillDay) < EndPeriod
),
Moves AS (
    SELECT sr.ToLpn AS Lpn, CAST(sr.Timestamp AT TIME ZONE 'SA Pacific Standard Time' AS date) AS MoveDay, sr.Quantity AS Delta
    FROM ClientInvoices ci
    JOIN BillingStorageRecords sr ON sr.ClientInvoiceId = ci.Id
    WHERE ci.Id = @Id
      AND sr.ToLpn IS NOT NULL
      AND sr.Type IN ('ProductAdd', 'ProductMove', 'LicensePlateMove')
      AND ISNULL(sr.SubType, '') <> 'PeriodEnd'
    UNION ALL
    SELECT sr.FromLpn, CAST(sr.Timestamp AT TIME ZONE 'SA Pacific Standard Time' AS date), -sr.Quantity
    FROM ClientInvoices ci
    JOIN BillingStorageRecords sr ON sr.ClientInvoiceId = ci.Id
    WHERE ci.Id = @Id
      AND sr.FromLpn IS NOT NULL
      AND sr.Type IN ('ProductRemove', 'ProductMove', 'LicensePlateMove')
      AND ISNULL(sr.SubType, '') <> 'PeriodEnd'
),
Balances AS (
    SELECT d.BillDay, m.Lpn, SUM(m.Delta) AS Qty
    FROM Days d
    JOIN Moves m ON m.MoveDay <= d.BillDay
    GROUP BY d.BillDay, m.Lpn
)
SELECT
    'STO-PALLET-DAY'                                   AS Item,
    'Pallet storage (pallet-days, storage records)'    AS Description,
    CAST(0.60 AS decimal(18,4))                        AS Rate,
    CAST(COUNT(*) AS decimal(18,4))                    AS Quantity,
    'ITBMS'                                            AS AdditionalCharges
FROM Balances
WHERE Qty > 0
OPTION (MAXRECURSION 0);

Picking

18. Picking por pedido (despacho)

Cobra por cada despacho con actividad de picking en el período. Requiere Generate Service Records.

-- 18. Picking por pedido (despacho)
SELECT
    'PICK-ORDER'                                         AS Item,
    'Order picking fee'                                  AS Description,
    CAST(2.50 AS decimal(18,4))                          AS Rate,
    CAST(COUNT(DISTINCT r.ReferenceId) AS decimal(18,4)) AS Quantity,
    'ITBMS'                                              AS AdditionalCharges
FROM ClientInvoices ci
JOIN BillingServiceRecords r ON r.ClientInvoiceId = ci.Id
WHERE ci.Id = @Id
  AND r.ReferenceType = 'PickTicket'
  AND r.SubType = 'Pick';

19. Picking por línea de pedido

Cobra por cada línea despachada de los despachos del cliente enviados en el período (sin transferencias entre almacenes).

-- 19. Picking por línea de pedido
SELECT
    'PICK-LINE'                      AS Item,
    'Picking fee per order line'     AS Description,
    CAST(0.60 AS decimal(18,4))      AS Rate,
    CAST(COUNT(*) AS decimal(18,4))  AS Quantity,
    'ITBMS'                          AS AdditionalCharges
FROM ClientInvoices ci
JOIN PickTickets pt      ON pt.ClientId = ci.ClientId
JOIN PickTicketLines ptl ON ptl.PickTicketId = pt.Id
WHERE ci.Id = @Id
  AND pt.IsWarehouseTransfer = 0
  AND ptl.ShippedQuantity > 0
  AND CAST(pt.ShippedDate AT TIME ZONE 'SA Pacific Standard Time' AS date) >= ci.StartPeriod
  AND CAST(pt.ShippedDate AT TIME ZONE 'SA Pacific Standard Time' AS date) <  ci.EndPeriod;

20. Picking por unidad

Cobra por unidad recogida en el período. Requiere Generate Service Records.

-- 20. Picking por unidad
SELECT
    'PICK-UNIT'                                        AS Item,
    'Units picked'                                     AS Description,
    CAST(0.30 AS decimal(18,4))                        AS Rate,
    CAST(ISNULL(SUM(r.Quantity), 0) AS decimal(18,4))  AS Quantity,
    'ITBMS'                                            AS AdditionalCharges
FROM ClientInvoices ci
JOIN BillingServiceRecords r ON r.ClientInvoiceId = ci.Id
WHERE ci.Id = @Id
  AND r.SubType = 'Pick';

21. Picking por caja de cartón

Cobra por cada caja de cartón armada para los pedidos del cliente despachados en el período.

-- 21. Picking por caja de cartón
SELECT
    'PICK-CARTON'                    AS Item,
    'Cartons picked and packed'      AS Description,
    CAST(0.90 AS decimal(18,4))      AS Rate,
    CAST(COUNT(*) AS decimal(18,4))  AS Quantity,
    'ITBMS'                          AS AdditionalCharges
FROM ClientInvoices ci
JOIN PickTickets pt ON pt.ClientId = ci.ClientId
JOIN ToteMasters tm ON tm.PickTicketId = pt.Id
WHERE ci.Id = @Id
  AND tm.ToteType = 'Carton'
  AND CAST(pt.ShippedDate AT TIME ZONE 'SA Pacific Standard Time' AS date) >= ci.StartPeriod
  AND CAST(pt.ShippedDate AT TIME ZONE 'SA Pacific Standard Time' AS date) <  ci.EndPeriod;

22. Picking por paleta o contenedor de salida

Cobra por cada contenedor en pedidos despachados, con una tarifa distinta para paletas, cajas y otros contenedores.

-- 22. Picking por paleta o contenedor de salida
SELECT
    'PICK-TOTE'                                       AS Item,
    CONCAT('Outbound ', ISNULL(tm.ToteType, 'Other')) AS Description,
    CAST(CASE tm.ToteType
            WHEN 'Pallet' THEN 6.00
            WHEN 'Carton' THEN 0.90
            ELSE 1.50
         END AS decimal(18,4))                        AS Rate,
    CAST(COUNT(*) AS decimal(18,4))                   AS Quantity,
    'ITBMS'                                           AS AdditionalCharges
FROM ClientInvoices ci
JOIN PickTickets pt ON pt.ClientId = ci.ClientId
JOIN ToteMasters tm ON tm.PickTicketId = pt.Id
WHERE ci.Id = @Id
  AND CAST(pt.ShippedDate AT TIME ZONE 'SA Pacific Standard Time' AS date) >= ci.StartPeriod
  AND CAST(pt.ShippedDate AT TIME ZONE 'SA Pacific Standard Time' AS date) <  ci.EndPeriod
GROUP BY tm.ToteType;

Empaque

23. Empaque por pedido

Cobra una tarifa de empaque por cada despacho enviado en el período (sin transferencias entre almacenes).

-- 23. Empaque por pedido
SELECT
    'PACK-ORDER'                     AS Item,
    'Packing fee per order'          AS Description,
    CAST(1.75 AS decimal(18,4))      AS Rate,
    CAST(COUNT(*) AS decimal(18,4))  AS Quantity,
    'ITBMS'                          AS AdditionalCharges
FROM ClientInvoices ci
JOIN PickTickets pt ON pt.ClientId = ci.ClientId
WHERE ci.Id = @Id
  AND pt.IsWarehouseTransfer = 0
  AND CAST(pt.ShippedDate AT TIME ZONE 'SA Pacific Standard Time' AS date) >= ci.StartPeriod
  AND CAST(pt.ShippedDate AT TIME ZONE 'SA Pacific Standard Time' AS date) <  ci.EndPeriod;

24. Material de empaque por tamaño de caja

Cobra material de empaque por caja, con precio según el nombre del tamaño de caja. Reemplace los nombres por los tamaños de caja que usted definió.

-- 24. Material de empaque por tamaño de caja
SELECT
    'PACK-MATERIAL'                                     AS Item,
    CONCAT('Packing material - ', ISNULL(cs.Name, 'no size')) AS Description,
    CAST(CASE cs.Name
            WHEN 'Small'  THEN 0.80
            WHEN 'Medium' THEN 1.20
            WHEN 'Large'  THEN 1.75
            ELSE 1.00
         END AS decimal(18,4))                          AS Rate,
    CAST(COUNT(*) AS decimal(18,4))                     AS Quantity,
    'ITBMS'                                             AS AdditionalCharges
FROM ClientInvoices ci
JOIN PickTickets pt ON pt.ClientId = ci.ClientId
JOIN ToteMasters tm ON tm.PickTicketId = pt.Id
LEFT JOIN CartonSizes cs ON cs.Id = tm.CartonSizeId
WHERE ci.Id = @Id
  AND tm.ToteType = 'Carton'
  AND CAST(pt.ShippedDate AT TIME ZONE 'SA Pacific Standard Time' AS date) >= ci.StartPeriod
  AND CAST(pt.ShippedDate AT TIME ZONE 'SA Pacific Standard Time' AS date) <  ci.EndPeriod
GROUP BY cs.Name;

Despacho y envío

25. Envío por despacho

Cobra por cada despacho enviado en el período, contado a partir de los eventos de envío. Requiere Generate Service Records.

-- 25. Envío por despacho
SELECT
    'SHIP-ORDER'                                         AS Item,
    'Shipping fee per shipment'                          AS Description,
    CAST(3.00 AS decimal(18,4))                          AS Rate,
    CAST(COUNT(DISTINCT r.ReferenceId) AS decimal(18,4)) AS Quantity,
    'ITBMS'                                              AS AdditionalCharges
FROM ClientInvoices ci
JOIN BillingServiceRecords r ON r.ClientInvoiceId = ci.Id
WHERE ci.Id = @Id
  AND r.Type = 'Handling'
  AND r.ReferenceType = 'PickTicket'
  AND r.SubType IN ('SmallParcelShip', 'ExternalShip', 'TruckLoadShip', 'PrivateFleetShip');

26. Envío por transportador

Cobra por pedido despachado con una tarifa distinta por transportador. Reemplace los nombres por los valores que usan sus pedidos.

-- 26. Envío por transportador
SELECT
    'SHIP-CARRIER'                                   AS Item,
    CONCAT('Shipments via ', x.CarrierName)          AS Description,
    CAST(CASE x.CarrierName
            WHEN 'DHL'   THEN 4.00
            WHEN 'FEDEX' THEN 4.00
            ELSE 3.00
         END AS decimal(18,4))                       AS Rate,
    CAST(COUNT(*) AS decimal(18,4))                  AS Quantity,
    'ITBMS'                                          AS AdditionalCharges
FROM ClientInvoices ci
JOIN PickTickets pt ON pt.ClientId = ci.ClientId
CROSS APPLY (SELECT ISNULL(NULLIF(pt.Carrier, ''), '(no carrier)') AS CarrierName) x
WHERE ci.Id = @Id
  AND pt.IsWarehouseTransfer = 0
  AND CAST(pt.ShippedDate AT TIME ZONE 'SA Pacific Standard Time' AS date) >= ci.StartPeriod
  AND CAST(pt.ShippedDate AT TIME ZONE 'SA Pacific Standard Time' AS date) <  ci.EndPeriod
GROUP BY x.CarrierName;

27. Manejo de paquetería frente a carga consolidada

Cobra el manejo de salida por contenedor con una tarifa distinta por modalidad de envío. Los eventos de envío no traen cantidad, por eso se usa Count. Requiere Generate Service Records.

-- 27. Manejo de paquetería frente a carga consolidada
SELECT
    'SHIP-HANDLING'                                  AS Item,
    CASE r.SubType
        WHEN 'SmallParcelShip'  THEN 'Parcel handling'
        WHEN 'TruckLoadShip'    THEN 'Truck load handling'
        WHEN 'PrivateFleetShip' THEN 'Private fleet handling'
        ELSE 'External carrier handling'
    END                                              AS Description,
    CAST(CASE r.SubType
            WHEN 'SmallParcelShip' THEN 1.25
            WHEN 'TruckLoadShip'   THEN 5.00
            ELSE 3.00
         END AS decimal(18,4))                       AS Rate,
    CAST(SUM(r.Count) AS decimal(18,4))              AS Quantity,
    'ITBMS'                                          AS AdditionalCharges
FROM ClientInvoices ci
JOIN BillingServiceRecords r ON r.ClientInvoiceId = ci.Id
WHERE ci.Id = @Id
  AND r.Type = 'Handling'
  AND r.SubType IN ('SmallParcelShip', 'ExternalShip', 'TruckLoadShip', 'PrivateFleetShip')
GROUP BY r.SubType;

28. Envío por carga (truck load)

Cobra por cada carga (truck load) despachada en el período.

-- 28. Envío por carga (truck load)
SELECT
    'SHIP-TRUCK'                     AS Item,
    'Truck load dispatch'            AS Description,
    CAST(45.00 AS decimal(18,4))     AS Rate,
    CAST(COUNT(*) AS decimal(18,4))  AS Quantity,
    'ITBMS'                          AS AdditionalCharges
FROM ClientInvoices ci
JOIN TruckLoads tl ON tl.ClientId = ci.ClientId
WHERE ci.Id = @Id
  AND tl.TruckLoadState = 'Shipped'
  AND CAST(tl.ShipDate AT TIME ZONE 'SA Pacific Standard Time' AS date) >= ci.StartPeriod
  AND CAST(tl.ShipDate AT TIME ZONE 'SA Pacific Standard Time' AS date) <  ci.EndPeriod;

29. Reembolso de flete con margen

Refactura el costo del transportador registrado en las cajas despachadas más un 10% de margen, en una sola línea. La cantidad es 1 solo cuando hubo costo; si no, la línea desaparece.

-- 29. Reembolso de flete con margen
SELECT
    'FREIGHT'                                                     AS Item,
    'Freight (carrier cost + 10%)'                                AS Description,
    CAST(ISNULL(SUM(tm.ShippingCost), 0) * 1.10 AS decimal(18,4)) AS Rate,
    CAST(CASE WHEN SUM(tm.ShippingCost) > 0 THEN 1 ELSE 0 END AS decimal(18,4)) AS Quantity,
    NULL                                                          AS AdditionalCharges
FROM ClientInvoices ci
JOIN PickTickets pt ON pt.ClientId = ci.ClientId
JOIN ToteMasters tm ON tm.PickTicketId = pt.Id
WHERE ci.Id = @Id
  AND CAST(pt.ShippedDate AT TIME ZONE 'SA Pacific Standard Time' AS date) >= ci.StartPeriod
  AND CAST(pt.ShippedDate AT TIME ZONE 'SA Pacific Standard Time' AS date) <  ci.EndPeriod;

Devoluciones

30. Devoluciones por RMA

Cobra por cada devolución de cliente cuya recepción terminó en el período.

-- 30. Devoluciones por RMA
SELECT
    'RET-RMA'                        AS Item,
    'Customer return processing'     AS Description,
    CAST(5.00 AS decimal(18,4))      AS Rate,
    CAST(COUNT(*) AS decimal(18,4))  AS Quantity,
    'ITBMS'                          AS AdditionalCharges
FROM ClientInvoices ci
JOIN CustomerReturns cr ON cr.ClientId = ci.ClientId
WHERE ci.Id = @Id
  AND CAST(cr.ReceivingCompleted AT TIME ZONE 'SA Pacific Standard Time' AS date) >= ci.StartPeriod
  AND CAST(cr.ReceivingCompleted AT TIME ZONE 'SA Pacific Standard Time' AS date) <  ci.EndPeriod;

31. Devoluciones por unidad

Cobra por unidad devuelta por los clientes (con o sin RMA). Requiere Generate Service Records.

-- 31. Devoluciones por unidad
SELECT
    'RET-UNIT'                                         AS Item,
    'Returned units processed'                         AS Description,
    CAST(0.50 AS decimal(18,4))                        AS Rate,
    CAST(ISNULL(SUM(r.Quantity), 0) AS decimal(18,4))  AS Quantity,
    'ITBMS'                                            AS AdditionalCharges
FROM ClientInvoices ci
JOIN BillingServiceRecords r ON r.ClientInvoiceId = ci.Id
WHERE ci.Id = @Id
  AND r.SubType IN ('ReceiveRma', 'NonRmaReceive');

32. Unidades devueltas dañadas

Cobra una tarifa de inspección o descarte por cada unidad dañada registrada en devoluciones recibidas en el período.

-- 32. Unidades devueltas dañadas
SELECT
    'RET-DAMAGED'                                            AS Item,
    'Damaged returned units (inspection / disposal)'         AS Description,
    CAST(1.00 AS decimal(18,4))                              AS Rate,
    CAST(ISNULL(SUM(crl.DamagedQuantity), 0) AS decimal(18,4)) AS Quantity,
    'ITBMS'                                                  AS AdditionalCharges
FROM ClientInvoices ci
JOIN CustomerReturns cr      ON cr.ClientId = ci.ClientId
JOIN CustomerReturnLines crl ON crl.CustomerReturnId = cr.Id
WHERE ci.Id = @Id
  AND CAST(cr.ReceivingCompleted AT TIME ZONE 'SA Pacific Standard Time' AS date) >= ci.StartPeriod
  AND CAST(cr.ReceivingCompleted AT TIME ZONE 'SA Pacific Standard Time' AS date) <  ci.EndPeriod;

Servicios de valor agregado, producción y control de inventario

33. Acciones personalizadas (VAS) por acción

Cobra cada acción personalizada registrada en el equipo de mano, una línea por acción, con una tarifa por acción. Estas acciones no traen una cantidad confiable, por eso se usa Count. Requiere Generate Service Records.

-- 33. Acciones personalizadas (VAS) por acción
SELECT
    'VAS'                                              AS Item,
    CONCAT('Value-added service: ', r.SubType)         AS Description,
    CAST(CASE r.SubType
            WHEN 'Labeling'    THEN 0.15
            WHEN 'ShrinkWrap'  THEN 3.50
            ELSE 1.00
         END AS decimal(18,4))                         AS Rate,
    CAST(SUM(r.Count) AS decimal(18,4))                AS Quantity,
    'ITBMS'                                            AS AdditionalCharges
FROM ClientInvoices ci
JOIN BillingServiceRecords r ON r.ClientInvoiceId = ci.Id
WHERE ci.Id = @Id
  AND r.Type = 'CustomAction'
GROUP BY r.SubType;

34. Reempaque

Cobra por cada operación de reempaque. Requiere Generate Service Records.

-- 34. Reempaque
SELECT
    'VAS-REPACK'                          AS Item,
    'Repackaging operations'              AS Description,
    CAST(2.00 AS decimal(18,4))           AS Rate,
    CAST(ISNULL(SUM(r.Count), 0) AS decimal(18,4)) AS Quantity,
    'ITBMS'                               AS AdditionalCharges
FROM ClientInvoices ci
JOIN BillingServiceRecords r ON r.ClientInvoiceId = ci.Id
WHERE ci.Id = @Id
  AND r.SubType = 'Repackage';

35. Armado de kits y órdenes de producción

Cobra una tarifa por cada orden de producción (armado de kits) terminada en el período más una tarifa por unidad armada. Devuelve dos líneas.

-- 35. Armado de kits y órdenes de producción
SELECT
    'PROD-ORDER'                     AS Item,
    'Production / kitting orders completed' AS Description,
    CAST(25.00 AS decimal(18,4))     AS Rate,
    CAST(COUNT(*) AS decimal(18,4))  AS Quantity,
    'ITBMS'                          AS AdditionalCharges
FROM ClientInvoices ci
JOIN ProductionOrders po ON po.ClientId = ci.ClientId
WHERE ci.Id = @Id
  AND CAST(po.ProductionCompleted AT TIME ZONE 'SA Pacific Standard Time' AS date) >= ci.StartPeriod
  AND CAST(po.ProductionCompleted AT TIME ZONE 'SA Pacific Standard Time' AS date) <  ci.EndPeriod
UNION ALL
SELECT
    'PROD-UNIT',
    'Units built (kits assembled)',
    CAST(0.75 AS decimal(18,4)),
    CAST(ISNULL(SUM(po.OutQuantity), 0) AS decimal(18,4)),
    'ITBMS'
FROM ClientInvoices ci
JOIN ProductionOrders po ON po.ClientId = ci.ClientId
WHERE ci.Id = @Id
  AND CAST(po.ProductionCompleted AT TIME ZONE 'SA Pacific Standard Time' AS date) >= ci.StartPeriod
  AND CAST(po.ProductionCompleted AT TIME ZONE 'SA Pacific Standard Time' AS date) <  ci.EndPeriod;

36. Despachos de kits quirúrgicos

Cobra por cada kit quirúrgico (bandeja) despachado en el período, para quienes usan la gestión de kits. Requiere Generate Service Records.

-- 36. Despachos de kits quirúrgicos
SELECT
    'KIT-DISPATCH'                        AS Item,
    'Surgical kit dispatches'             AS Description,
    CAST(12.00 AS decimal(18,4))          AS Rate,
    CAST(ISNULL(SUM(r.Count), 0) AS decimal(18,4)) AS Quantity,
    'ITBMS'                               AS AdditionalCharges
FROM ClientInvoices ci
JOIN BillingServiceRecords r ON r.ClientInvoiceId = ci.Id
WHERE ci.Id = @Id
  AND r.SubType = 'KitDispatch';

37. Etiquetado de paletas

Cobra por cada etiqueta de paleta impresa para las paletas de salida del cliente. Requiere Generate Service Records.

-- 37. Etiquetado de paletas
SELECT
    'LABEL-PALLET'                        AS Item,
    'Pallet labels printed'               AS Description,
    CAST(0.50 AS decimal(18,4))           AS Rate,
    CAST(ISNULL(SUM(r.Count), 0) AS decimal(18,4)) AS Quantity,
    'ITBMS'                               AS AdditionalCharges
FROM ClientInvoices ci
JOIN BillingServiceRecords r ON r.ClientInvoiceId = ci.Id
WHERE ci.Id = @Id
  AND r.SubType = 'PalletPrinting';

38. Conteos cíclicos por ubicación contada

Cobra por cada ubicación y día en que se hizo conteo cíclico de productos del cliente. Los conteos se asocian al cliente a través de sus productos.

-- 38. Conteos cíclicos por ubicación contada
SELECT
    'CYCLE-COUNT'                        AS Item,
    'Cycle counts (bins counted)'        AS Description,
    CAST(1.50 AS decimal(18,4))          AS Rate,
    CAST(COUNT(DISTINCT CONCAT(
            CONVERT(varchar(36), COALESCE(a.FromBinId, a.ToBinId)), '|',
            CONVERT(varchar(10), CAST(a.Timestamp AT TIME ZONE 'SA Pacific Standard Time' AS date), 23))) AS decimal(18,4)) AS Quantity,
    'ITBMS'                              AS AdditionalCharges
FROM ClientInvoices ci
JOIN Products p     ON p.ClientId = ci.ClientId
JOIN AuditRecords a ON a.ProductId = p.Id
WHERE ci.Id = @Id
  AND a.SubType = 'CycleCount'
  AND COALESCE(a.FromBinId, a.ToBinId) IS NOT NULL
  AND CAST(a.Timestamp AT TIME ZONE 'SA Pacific Standard Time' AS date) >= ci.StartPeriod
  AND CAST(a.Timestamp AT TIME ZONE 'SA Pacific Standard Time' AS date) <  ci.EndPeriod;

39. Ajustes de inventario

Cobra por cada ajuste de inventario (entrada o salida) hecho para el cliente. Requiere Generate Service Records.

-- 39. Ajustes de inventario
SELECT
    'ADJUST'                              AS Item,
    CASE r.SubType WHEN 'AdjustIn' THEN 'Inventory adjustments (in)'
                   ELSE 'Inventory adjustments (out)' END AS Description,
    CAST(1.00 AS decimal(18,4))           AS Rate,
    CAST(SUM(r.Count) AS decimal(18,4))   AS Quantity,
    'ITBMS'                               AS AdditionalCharges
FROM ClientInvoices ci
JOIN BillingServiceRecords r ON r.ClientInvoiceId = ci.Id
WHERE ci.Id = @Id
  AND r.SubType IN ('AdjustIn', 'AdjustOut')
GROUP BY r.SubType;

Condiciones comerciales: mínimos, escalones, recargos, cargos fijos e impuestos

40. Cargo mínimo mensual

Recalcula dentro de la consulta los cargos de manejo del mes y, cuando suman menos que el mínimo, agrega una línea por la diferencia. Una consulta no puede leer las líneas de la factura que se está generando, así que la base se recalcula aquí con las mismas tarifas de sus otras reglas.

-- 40. Cargo mínimo mensual
WITH Activity AS (
    SELECT
        COUNT(DISTINCT CASE WHEN r.SubType = 'Pick' AND r.ReferenceType = 'PickTicket'
                            THEN r.ReferenceId END)                       AS Orders,
        ISNULL(SUM(CASE WHEN r.SubType = 'Pick' THEN r.Quantity END), 0)  AS UnitsPicked,
        COUNT(DISTINCT CASE WHEN r.SubType = 'ReceivePo'
                            THEN r.ReferenceId END)                       AS PosReceived
    FROM ClientInvoices ci
    LEFT JOIN BillingServiceRecords r ON r.ClientInvoiceId = ci.Id
    WHERE ci.Id = @Id
),
Billed AS (
    SELECT Orders * 2.50 + UnitsPicked * 0.30 + PosReceived * 15.00 AS Amount
    FROM Activity
)
SELECT
    'MIN-MONTHLY'                                           AS Item,
    'Minimum monthly charge adjustment (minimum 500.00)'    AS Description,
    CAST(500.00 - Amount AS decimal(18,4))                  AS Rate,
    CAST(CASE WHEN Amount < 500.00 THEN 1 ELSE 0 END AS decimal(18,4)) AS Quantity,
    'ITBMS'                                                 AS AdditionalCharges
FROM Billed;

41. Tarifa escalonada por volumen

Cobra todas las unidades recogidas del mes a la tarifa del escalón que alcanza el volumen total, usando CASE. Requiere Generate Service Records.

-- 41. Tarifa escalonada por volumen
WITH Volume AS (
    SELECT ISNULL(SUM(r.Quantity), 0) AS Units
    FROM ClientInvoices ci
    LEFT JOIN BillingServiceRecords r ON r.ClientInvoiceId = ci.Id
                                     AND r.SubType = 'Pick'
    WHERE ci.Id = @Id
)
SELECT
    'PICK-TIER'                                       AS Item,
    CONCAT('Units picked - tier ',
        CASE WHEN Units <= 5000  THEN '1 to 5,000'
             WHEN Units <= 20000 THEN '5,001 to 20,000'
             ELSE 'over 20,000' END)                  AS Description,
    CAST(CASE WHEN Units <= 5000  THEN 0.30
              WHEN Units <= 20000 THEN 0.25
              ELSE 0.20 END AS decimal(18,4))         AS Rate,
    CAST(Units AS decimal(18,4))                      AS Quantity,
    'ITBMS'                                           AS AdditionalCharges
FROM Volume;

42. Recargo por fin de semana y fuera de horario

Agrega un recargo por unidad al picking y la recepción hechos sábado, domingo, antes de las 07:00 o desde las 18:00 hora local. Lee el historial de auditoría directamente para tener la hora exacta de cada evento.

-- 42. Recargo por fin de semana y fuera de horario
WITH Events AS (
    SELECT a.SubType,
           CAST(a.Timestamp AT TIME ZONE 'SA Pacific Standard Time' AS datetime2(0)) AS LocalTime,
           a.Quantity
    FROM ClientInvoices ci
    JOIN AuditRecords a ON a.ClientId = ci.ClientId
    WHERE ci.Id = @Id
      AND a.SubType IN ('Pick', 'ReceivePo')
      AND CAST(a.Timestamp AT TIME ZONE 'SA Pacific Standard Time' AS date) >= ci.StartPeriod
      AND CAST(a.Timestamp AT TIME ZONE 'SA Pacific Standard Time' AS date) <  ci.EndPeriod
)
SELECT
    'SURCHARGE-OFFHOURS'                                  AS Item,
    CASE SubType WHEN 'Pick' THEN 'Weekend / after-hours surcharge - units picked'
                 ELSE 'Weekend / after-hours surcharge - units received' END AS Description,
    CAST(0.10 AS decimal(18,4))                           AS Rate,
    CAST(ISNULL(SUM(Quantity), 0) AS decimal(18,4))       AS Quantity,
    'ITBMS'                                               AS AdditionalCharges
FROM Events
WHERE DATEDIFF(day, '19000101', CAST(LocalTime AS date)) % 7 IN (5, 6)
   OR CAST(LocalTime AS time) <  '07:00'
   OR CAST(LocalTime AS time) >= '18:00'
GROUP BY SubType;

43. Tarifa especial por cliente

Lee la tarifa negociada de picking por unidad desde el campo Info1 del cliente y usa una tarifa estándar cuando está vacío. Así una sola consulta sirve para los perfiles de todos los clientes.

-- 43. Tarifa especial por cliente
SELECT
    'PICK-UNIT'                                              AS Item,
    'Units picked (contract rate)'                           AS Description,
    CAST(COALESCE(TRY_CAST(NULLIF(c.Info1, '') AS decimal(18,4)), 0.30)
         AS decimal(18,4))                                   AS Rate,
    CAST(ISNULL(SUM(r.Quantity), 0) AS decimal(18,4))        AS Quantity,
    'ITBMS'                                                  AS AdditionalCharges
FROM ClientInvoices ci
JOIN Clients c ON c.Id = ci.ClientId
LEFT JOIN BillingServiceRecords r ON r.ClientInvoiceId = ci.Id
                                 AND r.SubType = 'Pick'
WHERE ci.Id = @Id
GROUP BY c.Info1;

44. Cargo fijo de administración de cuenta

Agrega un cargo fijo mensual de administración de cuenta que indica el período facturado. Una regla Flat hace lo mismo sin SQL; use esta cuando quiera las fechas en la descripción.

-- 44. Cargo fijo de administración de cuenta
SELECT
    'ACCOUNT-MGMT'                                         AS Item,
    CONCAT('Account management ',
           CONVERT(varchar(10), ci.StartPeriod, 103), ' - ',
           CONVERT(varchar(10), DATEADD(day, -1, ci.EndPeriod), 103)) AS Description,
    CAST(350.00 AS decimal(18,4))                          AS Rate,
    CAST(1 AS decimal(18,4))                               AS Quantity,
    'ITBMS'                                                AS AdditionalCharges
FROM ClientInvoices ci
WHERE ci.Id = @Id;

45. Líneas gravadas y exentas (AdditionalCharges)

Muestra cómo elegir, línea por línea, qué cargos adicionales aplican: el manejo lleva ITBMS y recargo de combustible, y el flete reembolsado no lleva ninguno. Los nombres deben coincidir con los cargos adicionales del perfil.

-- 45. Líneas gravadas y exentas (AdditionalCharges)
SELECT
    'SHIP-ORDER'                                         AS Item,
    'Shipping fee per shipment'                          AS Description,
    CAST(3.00 AS decimal(18,4))                          AS Rate,
    CAST(COUNT(DISTINCT r.ReferenceId) AS decimal(18,4)) AS Quantity,
    'ITBMS,Fuel surcharge'                               AS AdditionalCharges
FROM ClientInvoices ci
JOIN BillingServiceRecords r ON r.ClientInvoiceId = ci.Id
WHERE ci.Id = @Id
  AND r.Type = 'Handling'
  AND r.ReferenceType = 'PickTicket'
  AND r.SubType IN ('SmallParcelShip', 'ExternalShip', 'TruckLoadShip', 'PrivateFleetShip')
UNION ALL
SELECT
    'FREIGHT',
    'Freight reimbursement (not taxed)',
    CAST(ISNULL(SUM(tm.ShippingCost), 0) AS decimal(18,4)),
    CAST(CASE WHEN SUM(tm.ShippingCost) > 0 THEN 1 ELSE 0 END AS decimal(18,4)),
    NULL
FROM ClientInvoices ci
JOIN PickTickets pt ON pt.ClientId = ci.ClientId
JOIN ToteMasters tm ON tm.PickTicketId = pt.Id
WHERE ci.Id = @Id
  AND CAST(pt.ShippedDate AT TIME ZONE 'SA Pacific Standard Time' AS date) >= ci.StartPeriod
  AND CAST(pt.ShippedDate AT TIME ZONE 'SA Pacific Standard Time' AS date) <  ci.EndPeriod;