Abgleich von Bargeldbeständen

• •OpenAI API Key

{
  "id": "n4bNr0cnmlkuN8fy",
  "meta": {
    "instanceId": "db1715da5f21adba44ce4ea3b08abb06cd1771e876f5ad2751fcafd78c5eb9dc",
    "templateCredsSetupCompleted": true
  },
  "name": "CashReconciliation",
  "tags": [
    {
      "id": "7Hqs1zOnO1KyMmlS",
      "name": "Cashreconciliation",
      "createdAt": "2025-09-25T22:03:57.474Z",
      "updatedAt": "2025-09-25T22:03:57.474Z"
    },
    {
      "id": "HdENOIIKDc5O1stL",
      "name": "Accountant",
      "createdAt": "2025-09-25T22:04:06.515Z",
      "updatedAt": "2025-09-25T22:04:06.515Z"
    },
    {
      "id": "kAdvJMsTQvyVnrF9",
      "name": "AccountReceivable",
      "createdAt": "2025-09-25T22:04:25.528Z",
      "updatedAt": "2025-09-25T22:04:25.528Z"
    },
    {
      "id": "lsoR6uHgfiOrR6C6",
      "name": "OrdertoCash",
      "createdAt": "2025-09-25T22:04:29.775Z",
      "updatedAt": "2025-09-25T22:04:29.775Z"
    },
    {
      "id": "uKun50piys98JE3C",
      "name": "Invoices",
      "createdAt": "2025-09-18T00:47:51.695Z",
      "updatedAt": "2025-09-18T00:47:51.695Z"
    }
  ],
  "nodes": [
    {
      "id": "451c356d-2215-4c59-8f92-40678b080c2b",
      "name": "Bei Klick auf 'Workflow ausführen'",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        224,
        0
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "8e42332e-f1cb-41f8-a0e1-f37b6ab3e75a",
      "name": "Text extrahieren",
      "type": "n8n-nodes-base.mistralAi",
      "position": [
        1344,
        0
      ],
      "parameters": {
        "options": {
          "batch": false
        },
        "binaryProperty": "Data"
      },
      "credentials": {
        "mistralCloudApi": {
          "id": "<Mistral OCR API KEY>",
          "name": "Mistral Cloud account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "a8014539-db77-4ade-97c5-d42077119291",
      "name": "Kontoauszug abrufen",
      "type": "n8n-nodes-base.microsoftOneDrive",
      "position": [
        896,
        0
      ],
      "parameters": {
        "fileId": "01WVQSKIIAS4II25G37JGK6QHSYCDROS76",
        "operation": "get"
      },
      "credentials": {
        "microsoftOneDriveOAuth2Api": {
          "id": "<Microsoft One Drive API KEY>",
          "name": "Microsoft Drive account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "822968f4-1066-417d-9521-d1064f511bb7",
      "name": "Code in JavaScript",
      "type": "n8n-nodes-base.code",
      "position": [
        672,
        0
      ],
      "parameters": {
        "jsCode": "// n8n Code node\n// Input: 1 item that contains `json.data` array\n// Output: one item with a single JSON array erpLedger\n\nconst data = items[0].json.data;   // all rows live here\n\nconst ledger = data\n  .map(d => {\n    const row = d.values?.[0];     // [\"Ansys\", 1, \"08-15-2025\", 5096.96]\n    if (!row || row.length < 4) return null;\n\n    return {\n      CustomerName: row[0],              // first column\n      invoice_number: row[1],            // second column\n      invoice_due_date: row[2],          // third column\n      amount: Number(row[3]),            // fourth column\n      id: String(row[1])                 // use invoice number as ID\n    };\n  })\n  .filter(r => r !== null);\n\nreturn [\n  {\n    json: {\n      erpLedger: ledger\n    }\n  }\n];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "3a9cc7b1-aeeb-4f5f-b61f-db5fe3dfc402",
      "name": "OpenAI Chat Model1",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        1568,
        224
      ],
      "parameters": {
        "model": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4.1-mini"
        },
        "options": {
          "temperature": 0
        }
      },
      "credentials": {
        "openAiApi": {
          "id": "<OPENAI API KEY>",
          "name": "OpenAi account 2"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "99f88b81-4e58-4a5d-a47a-67f6ba0771a4",
      "name": "Transaktionen, Übereinstimmungen, Zusammenfassung abrufen",
      "type": "n8n-nodes-base.code",
      "position": [
        1920,
        0
      ],
      "parameters": {
        "jsCode": "// n8n Code node\n// Input: one item with .json.output (string containing transactions, matches, summary)\n// Output: multiple items (one per row in the reconciliation table)\n\nconst raw = $input.first().json.output;\n\n// ---------- Step 1: Parse safely ----------\nlet transactions = [];\nlet matches = [];\nlet summary = {};\n\ntry {\n  // Normalize separators: replace triple dashes with blank lines\n  const normalized = raw.replace(/---/g, \"\\n\\n\");\n\n  // Split into JSON blocks\n  const blocks = normalized\n    .split(/\\n\\s*\\n/)\n    .map(b => b.trim())\n    .filter(Boolean);\n\n  if (blocks[0]) {\n    transactions = JSON.parse(blocks[0]);\n  }\n  if (blocks[1]) {\n    matches = JSON.parse(blocks[1]);\n  }\n  if (blocks[2]) {\n    summary = JSON.parse(blocks[2]);\n  }\n} catch (e) {\n  return [{\n    json: { error: \"Parse failed\", message: e.message, rawStart: raw.substring(0, 200) }\n  }];\n}\n\n// ---------- Step 2: Index matches by transaction_id ----------\nconst matchMap = {};\nfor (const m of matches) {\n  matchMap[m.transaction_id] = {\n    ...m,\n    // Normalize classification fields\n    unmatched_classification: m.unmatched_classification || m.classification || null\n  };\n}\n\n// ---------- Step 3: Build reconciliation rows ----------\nconst rows = [];\n\nfor (const txn of transactions) {\n  const m = matchMap[txn.transaction_id];\n\n  if (m && Array.isArray(m.matches) && m.matches.length > 0) {\n    // Matched transaction (can have multiple invoices)\n    for (const match of m.matches) {\n      rows.push({\n        \"Bank Transaction Date\": new Date(txn.date).toLocaleDateString(\"en-US\"),\n        \"Bank Transaction Description\": txn.description,\n        \"Bank Amount\": txn.amount,\n        \"ERP Invoice Number(s)\": match.invoice_number || null,\n        \"ERP Customer Name(s)\": \"N/A\",  // not provided in your JSON\n        \"ERP Amount(s)\": txn.amount,\n        \"Match Status\": \"Matched\",\n        \"Confidence Score\": match.confidence || null,\n        \"Reason\": match.reason || \"\"\n      });\n    }\n  } else {\n    // Unmatched transaction\n    rows.push({\n      \"Bank Transaction Date\": new Date(txn.date).toLocaleDateString(\"en-US\"),\n      \"Bank Transaction Description\": txn.description,\n      \"Bank Amount\": txn.amount,\n      \"ERP Invoice Number(s)\": null,\n      \"ERP Customer Name(s)\": \"N/A\",\n      \"ERP Amount(s)\": null,\n      \"Match Status\": m?.unmatched_classification || \"Unapplied\",\n      \"Confidence Score\": null,\n      \"Reason\": m?.reason || \"No match\"\n    });\n  }\n}\n\n// ---------- Step 4: Return as n8n items ----------\nreturn rows.map(r => ({ json: r }));\n"
      },
      "typeVersion": 2,
      "alwaysOutputData": true
    },
    {
      "id": "b9395a80-9e22-4e11-8e8e-85f558cc618f",
      "name": "Daten aus unstrukturierter Datei extrahieren",
      "type": "n8n-nodes-base.microsoftOneDrive",
      "position": [
        1120,
        0
      ],
      "parameters": {
        "fileId": "={{ $json.id }}",
        "operation": "download",
        "binaryPropertyName": "=Data"
      },
      "credentials": {
        "microsoftOneDriveOAuth2Api": {
          "id": "<Microsoft One Drive API KEY>",
          "name": "Microsoft Drive account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "464d6980-ee91-4509-b433-012adb2bcf88",
      "name": "Rechnungs- und Kontoauszugsdaten verarbeiten",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        1568,
        0
      ],
      "parameters": {
        "text": "=You are a cash reconciliation specialist.\n\nINPUT DATA:\n- Bank transactions (raw text): {{ $json.extractedText }}\n- ERP ledger entries (JSON): {{ JSON.stringify($('Code in JavaScript').item.json.erpLedger) }}\n\nTASKS\n1) Parse bank text into JSON rows with fields:\n   [{\"date\":\"YYYY-MM-DD\",\"description\":\"string\",\"amount\":number,\"currency\":\"string\",\"transaction_id\":\"string\"}]\n2) Match each bank transaction to one or more ERP entries (keys: exact amount, date ±2 days, reference similarity).\n3) Unmatched items: classify as \"unapplied\", \"suspense\", or \"needs_review\" with reasons.\n4) For partial/one-to-many matches, propose splits with allocation amounts.\n5) Provide a summary: total_txns, total_matched, total_unmatched, reconciliation_rate_pct.\n6) Add a confidence score (0–1) and a short reason for each match/split.\n\nCONSTRAINTS\n- Return JSON ONLY. No prose, no markdown.\n- Limit candidates to top 3 per transaction by confidence.\n- If best confidence < 0.6, treat as unmatched.\n- Use transaction_id and invoice numbers from the inputs.\n\nI want the output in Tabular format\n",
        "options": {},
        "promptType": "define",
        "hasOutputParser": true
      },
      "typeVersion": 2.2
    },
    {
      "id": "84487907-0767-4877-89cf-d8ce56a9ac39",
      "name": "Notiz",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -560,
        -432
      ],
      "parameters": {
        "color": 4,
        "width": 2080,
        "height": 272,
        "content": "## **Problem Statement**\n### Cash reconciliation is one of the most time-consuming and error-prone processes for Accounts Receivable teams. Every day, specialists need to take the bank statement, scan through hundreds of line items, and manually check which transactions correspond to outstanding invoices in the ERP system. This slows down the month-end close, creates a backlog of unapplied cash, and impacts visibility into actual cash flow.\n\n## **The challenge is twofold**\n\n### Volume & Complexity – Bank statements contain dozens of deposits, withdrawals, fees, and transfers. Invoices may partially match or differ slightly in timing/amounts, making manual matching tedious.\n### Accuracy & Speed – Missing a match means open invoices stay unresolved, while mis-matches lead to reconciliation errors and corrections later in accounting."
      },
      "typeVersion": 1
    },
    {
      "id": "f02dc91c-34e1-48b5-8aca-51ad5c3001db",
      "name": "Notiz1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -560,
        -64
      ],
      "parameters": {
        "color": 6,
        "width": 736,
        "height": 288,
        "content": "## **Value**:\n\n### Time saved: Removes repetitive manual matching.\n### Cash flow visibility: Gives near real-time reconciliation metrics.\n### Error reduction: Uses AI confidence scoring and reasons for unmatched items.\n### Scalability: Can handle daily statement volumes without extra staff."
      },
      "typeVersion": 1
    },
    {
      "id": "5e1cd291-8e85-4869-ad70-f8a3958ac55b",
      "name": "Notiz2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        208,
        176
      ],
      "parameters": {
        "color": 4,
        "width": 1312,
        "height": 176,
        "content": "## ***Input***:\n\n### Open invoices are loaded from Excel.\n### Daily bank statement is fetched from OneDrive.\n### OCR extracts transaction data from the statement."
      },
      "typeVersion": 1
    },
    {
      "id": "a45de2dd-7ee6-4fa9-a167-d22ceab156e2",
      "name": "Notiz3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1712,
        240
      ],
      "parameters": {
        "color": 4,
        "width": 784,
        "height": 240,
        "content": "## ***AI Processing***:\n\n### Both invoice data and bank transactions are passed into an OpenAI Chat model.\n### The model evaluates and returns:\n Transaction → Invoice matches\n Confidence scores\n Unmatched transactions with reasons\n Summary metrics (total matched, unmatched, reconciliation %)."
      },
      "typeVersion": 1
    },
    {
      "id": "123cec6b-f238-4a07-a75f-2646c3ce0106",
      "name": "Notiz4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        208,
        368
      ],
      "parameters": {
        "color": 4,
        "width": 1328,
        "height": 496,
        "content": "## ***Post-Processing***:\n\n### Custom code nodes parse the AI output.\n### Results are converted into a structured table with columns like:\n\nBank Transaction Date\nDescription\nAmount\nERP Invoice Number(s)\nERP Customer Name(s)\nERP Amount(s)\nMatch Status\nConfidence Score\nReason\n\n## ***Output***:\n\n### The AR specialist sees a ready-made reconciliation table showing exactly which invoices can be closed in the ERP and which need further review. This reduces manual effort, improves reconciliation accuracy, and accelerates cash application."
      },
      "typeVersion": 1
    },
    {
      "id": "38449472-1724-44cc-aa6b-af80c8eaeb6b",
      "name": "Rechnungsdaten abrufen",
      "type": "n8n-nodes-base.microsoftExcel",
      "position": [
        448,
        0
      ],
      "parameters": {
        "table": {
          "__rl": true,
          "mode": "list",
          "value": "{6220E30B-55BD-614F-AA73-5C275D263361}",
          "cachedResultUrl": "https://netorg17303936x-my.sharepoint.com/personal/vinay_optinext_ca/_layouts/15/Doc.aspx?sourcedoc=%7B3B366271-85B1-4FF7-9FE1-BDD145027E90%7D&file=CashARData.xlsx&action=default&mobileredirect=true&DefaultItemOpen=1&activeCell=Sheet1!A1:D51",
          "cachedResultName": "Table1"
        },
        "filters": {},
        "rawData": true,
        "resource": "table",
        "workbook": {
          "__rl": true,
          "mode": "list",
          "value": "01WVQSKILRMI3DXMMF65HZ7YN52FCQE7UQ",
          "cachedResultUrl": "https://netorg17303936x-my.sharepoint.com/personal/vinay_optinext_ca/_layouts/15/Doc.aspx?sourcedoc=%7B3B366271-85B1-4FF7-9FE1-BDD145027E90%7D&file=CashARData.xlsx&action=default&mobileredirect=true&DefaultItemOpen=1",
          "cachedResultName": "CashARData"
        },
        "operation": "getRows",
        "returnAll": true,
        "worksheet": {
          "__rl": true,
          "mode": "list",
          "value": "{A31DAD5C-F7E0-2A4B-B868-E4B2444E9398}",
          "cachedResultUrl": "https://netorg17303936x-my.sharepoint.com/personal/vinay_optinext_ca/_layouts/15/Doc.aspx?sourcedoc=%7B3B366271-85B1-4FF7-9FE1-BDD145027E90%7D&file=CashARData.xlsx&action=default&mobileredirect=true&DefaultItemOpen=1&activeCell=Sheet1!A1",
          "cachedResultName": "Sheet1"
        }
      },
      "credentials": {
        "microsoftExcelOAuth2Api": {
          "id": "<Microsoft Account API KEY>",
          "name": "Microsoft Excel account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "7c23ec26-e63b-4dfe-b82b-79b5a892c91d",
      "name": "Notiz5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -560,
        272
      ],
      "parameters": {
        "color": 2,
        "width": 704,
        "height": 176,
        "content": "***Possible Enhancements***: \n\n1. Getting Invoice data from Data Table such as Snowflake, Databricks\n2. Getting Bank Statement from Bank accounts directly \n3. Posting the Data back to either ERP Systems or Data based with Matched Invoices to update the cash flow. "
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "983d50ae-2007-4b12-9645-838649f3db28",
  "connections": {
    "8e42332e-f1cb-41f8-a0e1-f37b6ab3e75a": {
      "main": [
        [
          {
            "node": "464d6980-ee91-4509-b433-012adb2bcf88",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "38449472-1724-44cc-aa6b-af80c8eaeb6b": {
      "main": [
        [
          {
            "node": "822968f4-1066-417d-9521-d1064f511bb7",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "822968f4-1066-417d-9521-d1064f511bb7": {
      "main": [
        [
          {
            "node": "a8014539-db77-4ade-97c5-d42077119291",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "a8014539-db77-4ade-97c5-d42077119291": {
      "main": [
        [
          {
            "node": "b9395a80-9e22-4e11-8e8e-85f558cc618f",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "3a9cc7b1-aeeb-4f5f-b61f-db5fe3dfc402": {
      "ai_languageModel": [
        [
          {
            "node": "464d6980-ee91-4509-b433-012adb2bcf88",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "99f88b81-4e58-4a5d-a47a-67f6ba0771a4": {
      "main": [
        []
      ]
    },
    "451c356d-2215-4c59-8f92-40678b080c2b": {
      "main": [
        [
          {
            "node": "38449472-1724-44cc-aa6b-af80c8eaeb6b",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b9395a80-9e22-4e11-8e8e-85f558cc618f": {
      "main": [
        [
          {
            "node": "8e42332e-f1cb-41f8-a0e1-f37b6ab3e75a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "464d6980-ee91-4509-b433-012adb2bcf88": {
      "main": [
        [
          {
            "node": "99f88b81-4e58-4a5d-a47a-67f6ba0771a4",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}