Freight Invoice & Surcharge Auditor

Compare structured freight invoices with your own contract rate card and surcharge rules. Get a reproducible expected charge, invoice difference, source row references and a package-by-package Decimal calculation. Use the results to prioritize a billing review.

This Actor does not obtain carrier tariffs, estimate zones, access carrier accounts, run OCR or use an LLM. A positive variance is not a confirmed overcharge or refund entitlement. Contract exclusions, separate packages, credit notes and corrections can require human reconciliation.

Quick start

The prefilled input contains 16 realistic synthetic invoice rows covering 11 unique shipments. Seven shipments can be calculated; four require manual reconciliation. It uses simulated contract rates, not a carrier's confidential tariff. Expected result events: 7 × $0.012 = $0.084.

Replace both file descriptors with your files and supply explicit surcharge rules. JSON details, CSV details, a currency-separated JSON summary and an HTML review report appear in the Output tab. The default Dataset contains one row per unique shipment ID.

Files

Each of invoiceFile and rateCardFile is a JSON descriptor:

• UTF-8 CSV: {"format":"csv","text":"shipment_id,...\n..." }
• XLSX: {"format":"xlsx","base64":"BASE64_FILE_BYTES","sheet":"Data"}
• Existing Apify upload: {"format":"csv","url":"https://api.apify.com/v2/key-value-stores/STORE_ID/records/invoice.csv"}

Upload your file to an Apify key-value store and use its HTTPS record URL. Select its store under fileStores to grant this run read-only access. Embedded CSV/base64 needs no storage permission. Arbitrary remote URLs, URL query strings (including embedded tokens), redirects, local file paths and legacy XLS/XLSM are rejected. Supply format explicitly when the record key has no extension. Multiple-sheet workbooks require an explicit sheet name. Data stays in the run's Apify storage; choose suitable account access and retention settings.

Invoice columns

Column names below are canonical and case-sensitive. Monetary values are plain decimal text, without currency symbols, grouping separators or exponent notation. Dates are ISO YYYY-MM-DD. Decimal inputs support up to 12 fractional digits and absolute values up to 1 trillion. Weight is kg, dimensions are cm.

Column	Meaning
shipment_id	Required string, unique customer shipment identity; all charge lines and packages share this ID
package_id	String; omitted/blank means one DEFAULT package
tracking_number	Required package tracking string; use text cells in XLSX to preserve leading zeroes
service, zone	Exact customer-defined contract identifiers; no fuzzy matching
invoice_date	Required ISO date; explicitly used as the tariff rating date in v1
actual_weight, billed_weight	Actual kg used for rating and invoiced kg used for comparison
length_cm, width_cm, height_cm	All three dimensions required; explicit three zeroes indicate no dimensional weight applies
charge_type	TRANSPORT, FUEL or an exact customer surcharge code
record_type	CHARGE, CREDIT or ADJUSTMENT; credits/adjustments require manual reconciliation
amount	Signed line amount; negative CHARGE is not automatically interpreted as a credit
currency	Exact three-uppercase-letter code; optional input currency fills only blank invoice currency

Repeat package metadata on each charge line or leave supplemental metadata blank. Conflicting nonblank values stop that shipment's calculation. If your contract rates by shipping date rather than invoice date, map the agreed contract rating date into invoice_date; the output invoice_date then represents that supplied rating date. V1 cannot independently reconcile multiple rating dates for one package.

Rate card columns

Every applicable row must explicitly provide:

Column	Rule
rate_id	Your contract reference
effective_from, effective_to	Inclusive ISO date range
service, zone, currency	Exact match to package metadata
weight_from_exclusive, weight_to_inclusive	Lower-exclusive / upper-inclusive band, applied to rounded rated kg
base_charge, per_kg_charge	Gross transport = base + per-kg × rated kg; set either explicitly to 0 when unused
weight_increment_kg	Positive rounding increment; rated kg rounds up
dimensional_divisor	cm³/kg divisor; 0 explicitly disables dimensional rating, and then nonzero dimensions are rejected
minimum_charge	Nonnegative transport floor
minimum_order	BEFORE_DISCOUNT or AFTER_DISCOUNT
discount_percent	0–100, affects transport only
discount_order	BEFORE_FUEL or AFTER_FUEL
fuel_percent	Explicit 0–100; use 0 for no fuel
fuel_basis	TRANSPORT or TRANSPORT_AND_ELIGIBLE_SURCHARGES
currency_decimals	Integer 0–4, explicit contract monetary precision

For each candidate tariff, dimensional kg = length × width × height / divisor. Rated kg = ceil(max(actual kg, dimensional kg) / increment) × increment. Exactly one dated band must match. Missing/overlapping tariffs produce a null expected charge.

Gross transport is rounded HALF_UP. BEFORE_DISCOUNT applies the minimum to gross transport then the discount; AFTER_DISCOUNT applies the minimum after discount. BEFORE_FUEL calculates fuel from that final discounted transport. AFTER_FUEL uses transport before discount for fuel, then applies the transport discount/final minimum. Extras are never discounted. Each component is rounded HALF_UP to the explicit currency precision before summing. Contracts needing another sequence, tier accumulation, piecewise rates, taxes, duties, currency conversion, or discount on surcharges are outside v1; do not represent them using guessed rules.

Example: base 10 + 2/kg × 3 kg = 16; minimum 15 BEFORE_DISCOUNT; 10% discount BEFORE_FUEL gives 14.40; fuel 20% gives 2.88; expected total 17.28 USD. With 50×40×30 cm and divisor 5000, rated weight is 12 kg and expected total is 36.72 USD.

Surcharge rules

surchargeRules is an array of exact dated rules, for example:

[{
  "rule_id": "REMOTE-2026",
  "code": "REMOTE",
  "service": "GROUND",
  "zone": "2",
  "currency": "USD",
  "effective_from": "2026-01-01",
  "effective_to": "2026-12-31",
  "kind": "FIXED",
  "value": "5",
  "fuel_eligible": true,
  "applies_when": "INVOICE_CODE_PRESENT"
}]

Supported kinds: FIXED and PERCENT_TRANSPORT (percentage of final discounted transport). ALWAYS applies the rule to every matching package even if its charge code is missing from the invoice. INVOICE_CODE_PRESENT applies only when that code is present; it cannot discover an omitted conditional surcharge. Rules apply once per package. Every rule requires an explicit boolean fuel_eligible. This affects fuel only with TRANSPORT_AND_ELIGIBLE_SURCHARGES. Multiple applicable rules for a code, or an invoiced code without an explicit matching rule, produce an uncalculable result.

No surcharge rules means you explicitly supply no extra-charge rules beyond the tariff's fuel. It does not certify that your contract has no additional conditions.

Column mapping and limits

columnMapping: {"invoice":{"shipment_id":"Shipment Reference","amount":"Net Amount"},"rateCard":{"zone":"Contract Zone"}}. Map canonical keys to exact source headers. Canonical/source collisions and duplicate source mappings are rejected.

tolerance is a nonnegative absolute Decimal (default 0.01, maximum 1000), independently applied in each currency. No conversion is performed.

Hard limits: 10 MiB per file, 50,000 invoice rows, 10,000 rate rows, 1,000 surcharge rules, 128 columns, 4,096 characters per cell, 64 MiB expanded XLSX, 1,000 ZIP members and 2 million visited XLSX cells. maxShipments defaults to 1,000 and supports 1–10,000; excess unique IDs reject the whole input before charging. Default memory 256 MB; hard run timeout 240 seconds.

Only CSV and XLSX are supported. Formulas and error cells on the selected sheet, macros, embedded objects and external workbook connections/relationships are rejected; nothing is executed. XLSX shipment/package/tracking identifiers must be text. Customer rows, invoice amounts and file contents are not written to application logs. Error logs contain only fixed diagnostic codes.

Findings and exports

Evidence row indexes count source records including the header and blank records; XLSX indexes are worksheet rows.

Output fields: shipment_id, tracking_number, service, invoice_date, billed_weight, expected_charge, invoiced_charge, difference, currency, finding_type, evidence_rows, calculation_breakdown. Additional fields: reason, billable, review_flags.

All monetary values are decimal strings (or null) to preserve precision. Difference = invoiced minus expected. WITHIN_TOLERANCE, POSITIVE_VARIANCE_REVIEW and NEGATIVE_VARIANCE_REVIEW are calculated comparisons. UNCALCULABLE_REVIEW has expected_charge/difference null with a reason and no shipment event.

Duplicate identical invoice rows and repeated charge codes within one package are ambiguous and uncalculable. Tracking reuse across different shipment IDs is flagged for context, without labeling either as an overcharge. Distinct package_id values are rated separately under one shipment ID. CREDIT/ADJUSTMENT records, missing tariffs and conflicting metadata are uncalculable; their invoice rows remain available for review. A null top-level service/tracking/date can mean different values across packages; package evidence retains the calculation.

• DETAILS: complete JSON array.
• DETAILS.csv: flat columns; nested calculations/evidence serialized as JSON cells. Potential spreadsheet formula prefixes are escaped with an apostrophe, including negative strings; JSON preserves original values.
• SUMMARY: counts and totals per currency, calculable shipments only.
• REPORT: readable HTML review summary.
• Default Dataset: same one-row-per-shipment results; export JSON/CSV through Apify.

Pricing and billing

$0.012 per successfully calculated and saved unique shipment_id ($12 per 1,000). Platform run usage is included in this event price. No start event, no separate invoice-line, fuel-line or package event.

Uncalculable shipments, credit/correction reviews and ambiguous duplicates are free. File/input errors and technical failures add no event for failed work; already saved successful shipments before a later failure remain billed. The runtime validates the active event price and rejects unexpected extra billing events.

Set Apify's maximum total charge per run as well as maxShipments. The run stops before saving the next billable shipment when the budget cannot cover it; the summary identifies omitted shipments. Previously saved shipment IDs are skipped on resurrection without recharging. An interruption between saving and charging may leave a free result. Uniqueness is per run; a new run performs and bills a new review. Apify may charge separate post-run storage/export operations according to your plan.

Development and versions

Product NEWAPI75, software 1.0.0. Python 3.13; apify 4.2.1 (Apache-2.0), openpyxl 3.1.5 (MIT), defusedxml 0.7.1 (PSF), httpx 0.28.1 (BSD-3-Clause). Pinned dependencies are in requirements.txt. Their published package metadata was checked on 2026-11-06.

python -m unittest discover -s tests -v runs controlled financial, ambiguity, parsing and billing tests. Fixtures are synthetic; no carrier account or paid data service is required.

Official implementation references: Apify PPE, Python SDK charging, openpyxl formula behavior, Python Decimal.

AFK Data Team / AFK Mesh.