Dynamics 365 Finance and SQL Server: keep manufacturing order lines current

What this looks like in manufacturing

A multi-plant manufacturer exposes open orders in SQL Server so planners can compare commitments with local production information. A customer changes one line’s quantity without changing the header fields the report currently watches. The next planning meeting still sees the old demand. Another plant uses the same visible order number, making a document-number-only join even more dangerous. The useful integration preserves company and line keys, detects the relevant line changes, and labels the freshness of both the source extract and the planning calculation.

Records, ownership, and update rules

Work through the process

1. 01
   Define what each row represents

   Write down whether the SQL dataset contains one row per order line, delivery schedule or production allocation. Reconcile a representative order manually. If one source line expands into several rows, specify how quantities aggregate before anyone uses the result for planning.
2. 02
   Select entities and dependency coverage

   Inspect Dynamics data entities and their keys rather than reading arbitrary application tables as if they were a stable API. Identify the line and related changes the process needs. Validate that the configured extraction path captures those changes independently of a header update.
3. 03
   Control the database contribution

   Keep SQL-generated recommendations in their own columns or tables. Permit a return update only through the approved application operation, after confirming object access and business validation. Direct edits to a reporting projection should never masquerade as accepted ERP instructions.
4. 04
   Test freshness and restart behavior

   Change a line quantity, a delivery date and a relevant child reference separately. Record when each arrives in SQL. Interrupt the test connection, resume it and reconcile the selected period. Confirm that database capture and upstream extraction each have an observable health signal.

Handle the exceptions explicitly

Same order number in two companies

Use company plus document and line identity. Quarantine ambiguous historical rows until their legal-entity context can be recovered.

A child value changes without its parent

Treat the missing update as a coverage problem. Review entity configuration or an explicit refresh process before advertising the projection as current.

A reporting user edits a recommendation

Retain the edit as a proposal with an author and timestamp. Do not propagate it into ERP without the required application approval.

What to verify before expanding

• Two companies with an identical displayed order number remain separate in SQL.
• A line-only quantity change appears without requiring a manual header edit.
• Repeated extraction leaves one row per declared business key, including after a restart.
• The report exposes source freshness separately from the age of the derived planning calculation.

Technical references

• Microsoft: data entities overview