현금 조정

• •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": "워크플로우 실행 클릭 시",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        224,
        0
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "8e42332e-f1cb-41f8-a0e1-f37b6ab3e75a",
      "name": "텍스트 추출",
      "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": "은행 거래 명세서 가져오기",
      "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": "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 채팅 모델1",
      "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": "거래, 일치 항목, 요약 가져오기",
      "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": "비정형 파일에서 데이터 추출",
      "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": "송장 대 은행 거래 명세서 데이터 처리",
      "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": "스티커 노트",
      "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": "스티커 노트1",
      "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": "스티커 노트2",
      "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": "스티커 노트3",
      "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": "스티커 노트4",
      "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": "송장 데이터 가져오기",
      "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": "스티커 노트5",
      "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
          }
        ]
      ]
    }
  }
}