Stage Pending Remittances for Dynamics 365 — Instructions

Purpose

Automatically stage pending customer remittance advice emails from Outlook into Microsoft Dynamics 365 (Finance & Operations). For each qualifying email, extract the structured payment data, wrap it in the DXC Dynamics envelope, and drop the record into the SharePoint intake folder that Dynamics monitors. Each email is processed independently.

Trigger

Whenever I get an email about Customer remittance or Payment advice from the following emails:

• User to provide emails (optional)

Storage location (SharePoint)

• Site: Pending remittances site — “https://.sharepoint.com/sites/"
• Library: Documents / Shared Documents — “https://.sharepoint.com/sites//Shared%20Documents"
• Intake folders:
  • Waiting — new records land here for Dynamics to pick up (target for this workflow)
  • Processed — records Dynamics has successfully ingested
  • Error — records that failed ingestion
• The record file always goes into the Waiting folder.

Workflow (repeat per email)

1. Identify

Find every pending remittance advice email in the Inbox. A genuine advice names a customer/payer, states a payment total, and lists one or more invoices. Process each matching email independently through the steps below.

2. Extract

Pull the fields below into the record. Skip any field you can’t find — never invent values. Normalise dates to ISO (YYYY-MM-DD); keep amounts numeric.

{
  "header": {
    "email_message_id": "",
    "received_at": "",
    "sender_email": "",
    "payer_name": "",
    "payer_abn": "",
    "payment_reference": "",
    "payment_date": "",
    "payment_amount": 0,
    "payment_method": "",
    "status": "Pending",
    "extraction_confidence": "",
    "requires_review": true
  },
  "lines": [
    {
      "line_number": 0,
      "invoice_number": "",
      "invoice_date": "",
      "gross_amount": 0,
      "discount_amount": 0,
      "net_amount": 0,
      "erp_invoice_id": 0,
      "match_status": "Matched"
    }
  ]
}

• Defaults to keep: status = "Pending", requires_review = true, match_status = "Matched".
• Set extraction_confidence (High / Medium / Low) based on how cleanly the source parsed.
• Reconciliation guard: the sum of the line net_amount values must equal the header payment_amount. If they don’t reconcile, flag for review rather than forcing a match.

3. Wrap in the Dynamics envelope

Place the Step 2 JSON in as an escaped string inside Message:

{
  "dataAreaId": "<User entered dataAreaId>",
  "DXCAgentId": "DXCAgentForPendingRemittanceCreation",
  "MessageId": "<email message ID, max 60 chars>",
  "MessageStatus": 1,
  "Message": "<the Step 2 JSON as an escaped string>"
}

Do not rename or alter JSON keys. Only update values.

• Fixed values (use exactly): dataAreaId = "<User entered dataAreaId>", DXCAgentId = "DXCAgentForPendingRemittanceCreation", MessageStatus = 1.
• MessageId: the email’s message ID, max 60 characters. The full internet message ID is usually longer than 60 chars, so use its unique local part — the text before the @ (e.g. DS1PR01MB88936767AF3AE8FC2846D6DC81F72). It is stable, unique, and keeps reprocessing idempotent.

4. Save

Save the completed envelope to workspace output as <MessageId>.json — one unique file per email.

5. Place in SharePoint

Workspace output can’t be written straight into the SharePoint intake folder, so:

1. Save to output first — workspace replication syncs it to your OneDrive Cowork area.
2. Do a server-side copy of that synced file into the site’s Waiting folder (resolve the Waiting folder, then copy — do not attempt a direct upload to that path).
3. Verify the copy landed (name, size, and content hash match the source).
4. Once verified, delete the files from your OneDrive Cowork area.

6. File the email

Move the processed email to the Pending remittances folder in Outlook.

7. Confirm

Report back per email:

• Payer name
• Payment amount
• Number of invoice lines
• Filename created
• Confirmation the file is in Waiting and the email was moved

Guardrails

• Do not rename or alter JSON keys in JSON Dynamics envelope json string. Only update values.
• Never fabricate names, ABNs, payment references, dates, or amounts — leave missing fields blank.
• Compute totals/percentages with code, not by hand; always run the reconciliation guard.
• One record file per email; the filename is the MessageId.
• If an email can’t be parsed, say so plainly and route it for manual review rather than guessing values.