P4 Software
/
logisticaHQ-en
P4 Software
/
logisticaHQ-en
Service rules bill anything you can count with SQL: receiving, picking, shipping, returns, value-added services, minimums, tiers and surcharges. This page explains how LogisticaHQ runs these queries, how to test them, and gives 45 sample queries to adapt.
LogisticaHQ runs a Service rule's query at two moments:
The query runs against your LogisticaHQ database and can read any table. Limiting it to the right client and period is the query's job.
The query receives two parameters: @Id, the Id of the invoice being built, which is a row in ClientInvoices, and @Rate, the rule's Rate field. The queries below write their prices as numbers so each one reads on its own; replace a single price with @Rate to keep it on the rule.
@ClientId, @StartDate or @EndDate. A query that uses one of them fails with "Must declare the scalar variable".ClientInvoices on Id = @Id. It gives you three columns:
ClientId: the client.StartPeriod: the first day of the period.EndPeriod: the first day after the period. Filter with < EndPeriod, never <= EndPeriod.Columns are matched by name. Other columns are ignored.
| Column | Type | Meaning |
| Item | text | The item code shown on the line. |
| Description | text | The line description. |
| Rate | decimal | The price per unit. A negative rate makes a credit line. |
| Quantity | decimal | The number of units. Rows with a quantity of zero or less are dropped. |
| AdditionalCharges | text, optional | A comma-separated list of the names of additional charges to apply to the line, for example ITBMS. Names that are not on the invoice's list of charges are ignored. Use NULL for no charges. |
How rows become lines:
GROUP BY to get fewer lines, or no GROUP BY with aggregates to get a single line.decimal(18,4).ISNULL(..., 0), so a period with no activity returns 0 and the line is dropped.A query cannot see the lines of the invoice being built. Lines are saved only after all rules have run. To bill a minimum or a tier based on other charges, recompute those charges inside the query.
Start every query from this pattern:
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;How the template handles time:
AT TIME ZONE converts them to local time before comparing them with the period dates.'SA Pacific Standard Time', which is UTC-5 with no daylight saving time. It covers Panama, Colombia, Ecuador and Peru.InventorySnapshots.SnapshotDate, are compared directly.| Source | Needs | How to scope it |
BillingServiceRecords: the client's activity for the period, saved with the invoice. | Generate Service Records (Business - Multi Client) on when the invoice is created. | ClientInvoiceId = @Id. It is already limited to the client and the period. |
BillingStorageRecords: the client's storage movements for the period, saved with the invoice. | Generate Storage Records on when the invoice is created. | ClientInvoiceId = @Id. |
InventorySnapshots: one row per stock line per day. | Enable Daily Snapshot (Business - Inventory). Snapshots are kept for 90 days. | Join Products on ProductId with Products.ClientId = ci.ClientId, and filter SnapshotDate on the period. |
AuditRecords: the full activity history. | Nothing. | ClientId = ci.ClientId plus the period filter on Timestamp. |
Documents: PurchaseOrders, PickTickets, ToteMasters, TruckLoads, CustomerReturns, ProductionOrders. | Nothing. | The document's ClientId plus a date column, for example ReceivingCompleted, ShippedDate, ShipDate or ProductionCompleted. |
Stock tables such as InventoryLocations. | Nothing. | They have no ClientId. Join Products and filter on Products.ClientId. |
Each row in BillingServiceRecords groups the events of one activity on one document. The main columns are:
Type and SubType: the activity.ReferenceType and ReferenceCode: the document, for example PurchaseOrder and the purchase order number.Count: the number of events.Quantity: the total units.NumberOfPacks: the total packs.| SubType | Activity | Use |
| ReceivePo, NonPoReceive | Receiving against a purchase order, and blind receiving | Quantity, NumberOfPacks |
| Pick | Picking (ReferenceType PickTicket) | Quantity |
| SmallParcelShip, TruckLoadShip, PrivateFleetShip, ExternalShip | Shipping (Type Handling) | Count. These events have no quantity. |
| ReceiveRma, NonRmaReceive | Customer returns | Quantity |
| AdjustIn, AdjustOut | Inventory adjustments | Count or Quantity |
| Repackage, PalletPrinting, KitDispatch | Handling services | Count |
| (Type CustomAction) | Custom actions from the handheld. SubType is the action's own code. | Count |
For events without a quantity, SUM(Quantity) returns NULL. Use SUM(Count), or COUNT(*) on AuditRecords.
.../clientInvoiceDetails/<Id>.@Id on the first line, ending with a semicolon, and paste your query below it.DECLARE line into the rule's Sql field and save the rule.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';The query builder is available only to users who are not linked to a client.
Each sample returns the five columns and joins ClientInvoices on @Id. Adapt each one before you use it:
ITBMS where the service is not taxed.The samples that read BillingServiceRecords or BillingStorageRecords need the matching capture setting. The storage samples based on InventorySnapshots need Enable Daily Snapshot.
Charges a fixed fee for every purchase order received during the period. Needs Generate Service Records.
-- 1. Receiving per purchase order
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';Charges per purchase order line that received stock, for orders whose receiving was completed in the period. Reads the purchase orders directly, so no capture setting is needed.
-- 2. Receiving per purchase order line
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;Charges per unit received and prints one invoice line per purchase order (or blind receipt), like the built-in sample. Needs Generate Service Records.
-- 3. Receiving per unit, one line per document
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;Charges per case received, using the number of packs recorded on each receipt. Needs Generate Service Records.
-- 4. Receiving per case (pack)
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');Charges per distinct license plate (pallet) that stock was received onto. Reads the audit history directly, so no capture setting is needed.
-- 5. Receiving per pallet (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;Charges per distinct container number on purchase orders whose receiving was completed in the period.
-- 6. Receiving per container
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;Adds a surcharge per purchase order your team marked as express. This uses a convention: type EXPRESS in the order's Tag1 field (adapt the column and value to your own practice).
-- 7. Express receiving surcharge
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;Charges per license plate (pallet) move. LogisticaHQ records put-away as an ordinary floor move, so this counts every pallet move in the period, not only put-away.
-- 8. Put-away per pallet moved
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;Charges per unit moved between bins (product moves and full-bin moves). As above, this includes every move, not only put-away.
-- 9. Put-away per unit moved
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;Counts the client's occupied pallets (license plates) on each daily inventory snapshot and charges per pallet-day. Needs the daily inventory snapshot setting.
-- 10. Storage per pallet-day
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;Same count as pallet-days, but divides by 30 so the invoice shows your published monthly pallet rate.
-- 11. Storage per pallet-month (monthly rate)
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;Counts the distinct bins holding the client's stock on each daily snapshot and charges per bin-day.
-- 12. Storage per bin-day
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;Multiplies each day's quantity on hand by the product's Cube and charges per cubic metre-day. Product Cube is kept in cubic centimetres when dimensions are in centimetres, so the query divides by 1,000,000; if your dimensions are in inches, divide by 1,728 instead and bill per cubic foot.
-- 13. Storage per cubic metre-day
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;Charges a monthly fee for every distinct SKU the client had in stock at any point in the period.
-- 14. Storage per 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;Charges pallet-days with a different rate per zone (for example, a cold room). Replace the zone codes with your own.
-- 15. Storage by zone with different rates
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;Bills pallet-days and, when that amount is below the agreed minimum, adds a top-up line for the difference. The top-up line disappears on its own when it is not needed.
-- 16. Storage with a monthly minimum
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;Rebuilds each pallet's daily balance from the storage records captured with the invoice (the pattern used in the VTA_015 sample) and charges per pallet-day. Needs Generate Storage Records instead of the daily snapshot.
-- 17. Storage per pallet-day from storage records
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);Charges per pick ticket that had picking activity in the period. Needs Generate Service Records.
-- 18. Picking per order
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';Charges per shipped line on the client's pick tickets shipped in the period (warehouse transfers excluded).
-- 19. Picking per order line
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;Charges per unit picked in the period. Needs Generate Service Records.
-- 20. Picking per unit
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';Charges per carton built for the client's orders shipped in the period.
-- 21. Picking per carton
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;Charges per tote on shipped orders, with a different rate for pallets, cartons and other totes.
-- 22. Picking per outbound pallet or tote
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;Charges a packing fee per pick ticket shipped in the period (warehouse transfers excluded).
-- 23. Packing per order
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;Charges packing material per carton, priced by carton size name. Replace the size names with the ones defined in your carton sizes.
-- 24. Packing material by carton size
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;Charges per pick ticket shipped in the period, counted from the ship events. Needs Generate Service Records.
-- 25. Shipping per shipment
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');Charges per shipped order with a different rate for each carrier. Replace the carrier names with the values your orders use.
-- 26. Shipping per carrier
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;Charges outbound handling per tote with a different rate per shipping method. Ship events carry no quantity, so this uses the event Count. Needs Generate Service Records.
-- 27. Small parcel vs truckload handling
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;Charges per truck load shipped in the period.
-- 28. Shipping per truckload
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;Re-bills the carrier cost recorded on shipped cartons plus a 10% markup, as one line. The quantity is 1 only when there was a cost, so the line disappears otherwise.
-- 29. Freight pass-through with markup
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;Charges per customer return whose receiving was completed in the period.
-- 30. Returns per 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;Charges per unit received back from customers (with or without an RMA). Needs Generate Service Records.
-- 31. Returns per unit
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');Charges an inspection or disposal fee per damaged unit recorded on returns received in the period.
-- 32. Damaged returned units
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;Charges each custom action recorded on the handheld, one line per action, with a rate per action. Custom actions carry no reliable quantity, so this uses the event Count. Needs Generate Service Records.
-- 33. Custom actions (VAS) by action
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;Charges per repackaging operation. Needs Generate Service Records.
-- 34. Repackaging
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';Charges a fee per production (kitting) order completed in the period plus a fee per unit built. Returns two lines.
-- 35. Kitting and production orders
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;Charges per surgical kit (tray) dispatched in the period, for tenants that use kit management. Needs Generate Service Records.
-- 36. Surgical kit dispatches
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';Charges per pallet label printed for the client's outbound pallets. Needs Generate Service Records.
-- 37. Pallet labeling
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';Charges per bin and day in which the client's products were cycle counted. Counts are matched to the client through its products.
-- 38. Cycle counts per bin counted
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;Charges per inventory adjustment (in or out) made for the client. Needs Generate Service Records.
-- 39. Inventory adjustments
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;Recomputes the month's handling charges inside the query and, when they total less than the minimum, adds one line for the difference. A query cannot read the lines of the invoice being generated, so the base must be recomputed here with the same rates as your other rules.
-- 40. Minimum monthly charge
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;Prices all units picked in the month at the rate of the tier the total volume reaches, using CASE. Needs Generate Service Records.
-- 41. Tiered volume pricing
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;Adds a per-unit surcharge for picking and receiving done on Saturday, Sunday, before 07:00 or from 18:00 local time. Reads the audit history directly for exact event times.
-- 42. Weekend and after-hours surcharge
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;Reads the client's negotiated per-unit picking rate from the client's Info1 field, falling back to a standard rate when it is empty. One query can then be shared by every client's profile.
-- 43. Per-client special rate
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;Adds a fixed monthly account management fee that names the billed period. A Flat rule does the same without SQL; use this when you want the dates in the description.
-- 44. Account management flat fee
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;Shows how to choose, line by line, which additional charges apply: handling carries ITBMS and a fuel surcharge, while reimbursed freight carries none. The names must match the profile's additional charges.
-- 45. Taxable and non-taxable lines (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;Was this page helpful?