Distribution Operations Automation

A governed import platform replaced brittle file handling with structured partner mapping, duplicate review, and stronger operational visibility

This engagement addressed a controlled import and review stage within Scan-Based Trading, or SBT, a retail-supplier operating model in which point-of-sale scan activity drives downstream settlement, replenishment, and related operational processes. For that model to work, inbound partner data has to arrive in a usable form, be interpreted correctly, and be handled in a way operations staff can trust.

The problem was not simply moving CSV files. Different trading partners produced different file structures, delivery methods, and handling rules. Files had to be identified, staged, parsed, mapped, reviewed, and either released or held. Duplicate conditions also mattered materially, including invoice number, invoice date, UPC-related checks, and total movement.

The imported files also had to be standardized into a form suitable for controlled import into a downstream accounting platform. That platform could process only one import at a time and did not provide its own internal job queue for managing multiple pending imports. A separate monitoring and sequencing service was therefore required to control release order and ensure files were introduced serially and accurately.

Engagement Snapshot

  • Business Context: a controlled import stage within Scan-Based Trading
  • Environment: partner-driven distribution and retail data exchange operations
  • System Type: browser-based workflow and administration platform
  • Primary Problem: replace a fully manual SFTP-to-accounting import process with governed intake, mapping, duplicate review, and controlled downstream sequencing
  • Constraints: partner file variability, accounting-platform-native import limitations, single-import processing, no internal job queue, labor-intensive exception handling, and production support edge cases
  • Engagement Focus: workflow automation, import governance, exception reduction, and operational reliability
  • Representative Technologies: Angular, Kendo UI, ASP.NET Core, Entity Framework Core, SQL Server, Active Directory, Sage 100, SFTP

The Situation

Before this work, the process was fully manual. Partner files were received by way of SFTP, prepared, and then imported directly into the accounting platform through its native import mechanism.

That approach created several operational problems. The accounting platform could process only one import at a time and did not provide its own internal queue for managing multiple pending imports. File structure varied by partner, duplicate conditions were difficult to detect in advance, and exceptions that surfaced during import were laborious to identify and resolve manually.

What was needed was not simply faster file movement. The process needed a governed intake and review stage that could standardize incoming files, apply partner-specific mapping rules, expose duplicate and exception conditions earlier, and control how files were released into the downstream accounting import process.

Constraints and Risks

  • Partner file variability: inbound SFTP files differed by partner in structure, field usage, and formatting
  • Manual downstream import: files ultimately had to be introduced through the accounting platform’s native import process rather than a governed internal job model
  • Single-import limitation: the accounting platform could process only one import at a time and did not provide its own queue for pending work
  • Exception burden: duplicate conditions, format issues, and import failures were laborious to identify and resolve manually
  • Operational dependency: this stage affected the reliability of downstream Scan-Based Trading and accounting-related processing
  • Production support realities: timeout behavior, SFTP key changes, empty staging files, service startup failures, and overlapping downstream activity all had to be handled cleanly

What We Did

Built a governed application layer for intake, mapping, and review

The solution was implemented as a browser-based internal platform rather than a narrow import utility. It provided operator-facing workflow surfaces for partner setup, file mapping, import review, and duplicate control within an existing administrative application framework.

Implemented partner configuration and mapping control

A dedicated Partner Configuration interface was built to manage partner identity, active state, customer identification, source and destination locations, archive paths, and transfer settings such as protocol, host, credentials, and SSH host key fingerprint. A File Data Map Editor was also implemented so inbound columns could be translated into destination fields through a visible mapping surface rather than opaque backend logic.

Created import review and duplicate handling

An Import Manager view was built to expose evaluated-on date, effective date, invoice number, status, row count, date conflict, parse errors, partner identity, and release information. Later work extended this area with Hold Duplicates and clearer behavior around duplicate release.

Standardized and sequenced downstream accounting imports

Beyond partner-specific intake and review, the workflow also had to prepare imported files for reliable downstream accounting import. Incoming data was standardized into a consistent structure, and a separate monitoring and sequencing service was written because the accounting platform could process only one import at a time and did not manage queued jobs internally.

That control layer ensured files were introduced serially, monitored during processing using the Windows API, and not released into the accounting platform in a way that could create overlap or processing errors.

Hardened the system under production conditions

Follow-on work included investigation of database timeout behavior, analysis of a recurring early-morning timeout concern, troubleshooting duplicate-file processing, support for SFTP disruptions after a public-key change, processing staged files when the service starts, handling empty files in the staging directory so they no longer prevented service startup, and troubleshooting simultaneous downstream jobs when only one was expected.

Resulting Workflow

The resulting workflow became materially more governable. Partners could be configured through formal records. Transfer and file-handling behavior could be defined explicitly. Mappings could be maintained as data. Imports could be reviewed through a structured results surface. Duplicate scenarios could be held, reviewed, and released with clearer operational context. Standardized files could then be introduced into the downstream accounting platform in controlled sequence.

  • Partner setup moved into a formal configuration surface
  • Mapping logic became visible, editable, and maintainable
  • Import outcomes became easier to interpret operationally
  • Duplicate decisions gained explicit hold-and-release behavior
  • Downstream accounting import became safer through standardization, monitoring, and serial sequencing

Outcome

This engagement produced a governed system for a specific operational stage within Scan-Based Trading, then improved it under production use. The result was stronger control over partner-specific file intake, clearer duplicate handling, standardized downstream accounting imports, and better supportability as real operating conditions surfaced timing issues, transfer disruptions, startup failures, and service-hosting changes.

  • Greater operational control through explicit configuration, mapping, and review interfaces
  • Lower manual friction by replacing opaque handling with governed workflow surfaces
  • Improved supportability through clearer diagnostics and stronger startup and service behavior
  • Safer downstream accounting import through standardized files, controlled sequencing, and monitoring of serial processing

Discuss Your Situation

If your organization is working through operational complexity, process friction, unreliable handoffs, scaling pressure, or systems that have become difficult to manage with confidence, a focused discussion can help clarify the right next step.

This can be valuable whether you are trying to improve reliability, reduce manual effort, strengthen visibility and control, support growth, or define a more supportable path forward for an important business process.

to discuss the workflow, control points, sequencing requirements, and operational constraints involved in your environment.