Vhs - Roe Auto Update (daily Forex Sync, Tenant-aware)

The workflow JSON

Copy or download the full n8n JSON below. Paste it into a new n8n workflow, add your credentials, activate. Full import guide →

{
  "name": "VHS - ROE Auto Update (Daily Forex Sync, Tenant-Aware)",
  "nodes": [
    {
      "parameters": {
        "rule": {
          "interval": [
            {
              "triggerAtHour": 6,
              "triggerAtMinute": 0
            }
          ]
        }
      },
      "id": "schedule-trigger-roe",
      "name": "Daily 6 AM Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "typeVersion": 1.2,
      "position": [
        0,
        0
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "SELECT CmpCode, CmpName\nFROM dbo.company\nORDER BY CmpCode",
        "options": {}
      },
      "id": "mssql-get-tenants",
      "name": "Get Active Tenants",
      "type": "n8n-nodes-base.microsoftSql",
      "typeVersion": 1.1,
      "position": [
        260,
        0
      ],
      "credentials": {
        "microsoftSql": {
          "name": "<your credential>"
        }
      }
    },
    {
      "parameters": {
        "options": {}
      },
      "id": "split-tenants",
      "name": "Split In Batches (per tenant)",
      "type": "n8n-nodes-base.splitInBatches",
      "typeVersion": 3,
      "position": [
        520,
        0
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "=SELECT DISTINCT CurrencyCode, CurrencyName\nFROM dbo.Currency\nWHERE status = 'A'\n  AND Cmpcode = '{{ $json.CmpCode }}'\n  AND CurrencyCode <> 'USD'\nORDER BY CurrencyCode",
        "options": {}
      },
      "id": "mssql-get-currencies-per-tenant",
      "name": "Get Tenant Currencies",
      "type": "n8n-nodes-base.microsoftSql",
      "typeVersion": 1.1,
      "position": [
        780,
        0
      ],
      "credentials": {
        "microsoftSql": {
          "name": "<your credential>"
        }
      }
    },
    {
      "parameters": {
        "jsCode": "// Build comma-separated currency codes scoped to the current tenant\nconst items = $input.all();\nconst tenant = $('Split In Batches (per tenant)').item.json;\n\nconst codes = items.map(item => item.json.CurrencyCode).filter(Boolean);\nconst currencyMap = {};\nfor (const item of items) {\n  currencyMap[item.json.CurrencyCode] = item.json.CurrencyName;\n}\n\nreturn [{\n  json: {\n    cmpcode: tenant.CmpCode,\n    cmpname: tenant.CmpName,\n    currencyCodes: codes.join(','),\n    currencyMap: currencyMap,\n    currencyCount: codes.length,\n    today: new Date().toISOString().split('T')[0]\n  }\n}];"
      },
      "id": "code-prepare-codes",
      "name": "Prepare Tenant Currency List",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        1040,
        0
      ]
    },
    {
      "parameters": {
        "conditions": {
          "options": {
            "caseSensitive": true,
            "leftValue": "",
            "typeValidation": "strict"
          },
          "conditions": [
            {
              "id": "condition-has-currencies",
              "leftValue": "={{ $json.currencyCount }}",
              "rightValue": 0,
              "operator": {
                "type": "number",
                "operation": "gt"
              }
            }
          ],
          "combinator": "and"
        },
        "options": {}
      },
      "id": "if-tenant-has-currencies",
      "name": "Tenant Has Currencies?",
      "type": "n8n-nodes-base.if",
      "typeVersion": 2.2,
      "position": [
        1300,
        0
      ]
    },
    {
      "parameters": {
        "url": "=https://api.exchangerate.host/latest?base=USD&symbols={{ $json.currencyCodes }}",
        "authentication": "none",
        "method": "GET",
        "options": {
          "response": {
            "response": {
              "responseFormat": "json"
            }
          },
          "timeout": 30000
        }
      },
      "id": "http-forex-primary",
      "name": "Fetch Forex Rates (Primary)",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4.2,
      "position": [
        1560,
        -100
      ],
      "onError": "continueRegularOutput"
    },
    {
      "parameters": {
        "url": "=https://open.er-api.com/v6/latest/USD",
        "authentication": "none",
        "method": "GET",
        "options": {
          "response": {
            "response": {
              "responseFormat": "json"
            }
          },
          "timeout": 30000
        }
      },
      "id": "http-forex-fallback",
      "name": "Fetch Forex Rates (Fallback)",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4.2,
      "position": [
        1560,
        140
      ],
      "onError": "continueRegularOutput"
    },
    {
      "parameters": {
        "jsCode": "// Merge results from primary and fallback forex APIs for the current tenant\nconst primaryData = $('Fetch Forex Rates (Primary)').first()?.json || {};\nconst fallbackData = $('Fetch Forex Rates (Fallback)').first()?.json || {};\nconst prepData = $('Prepare Tenant Currency List').first().json;\n\nconst { cmpcode, cmpname, currencyMap, today } = prepData;\n\nlet rates = {};\nlet source = 'none';\n\nif (primaryData.success === true && primaryData.rates) {\n  rates = primaryData.rates;\n  source = 'exchangerate.host';\n} else if (fallbackData.result === 'success' && fallbackData.rates) {\n  rates = fallbackData.rates;\n  source = 'open.er-api.com';\n}\n\nif (Object.keys(rates).length === 0) {\n  return [{\n    json: {\n      error: true,\n      cmpcode,\n      cmpname,\n      message: `Both forex APIs failed for tenant ${cmpcode}`,\n      today\n    }\n  }];\n}\n\nconst roeEntries = [];\nconst rateDetails = [];\n\nfor (const [code, name] of Object.entries(currencyMap)) {\n  if (rates[code] !== undefined && rates[code] !== null) {\n    const rate = parseFloat(rates[code]);\n    if (rate > 0) {\n      roeEntries.push({\n        currencyCode: code,\n        currencyName: name,\n        roe: Math.round(rate * 10000) / 10000\n      });\n      rateDetails.push(`${code}: ${rate}`);\n    }\n  }\n}\n\nreturn [{\n  json: {\n    error: false,\n    cmpcode,\n    cmpname,\n    source,\n    today,\n    roeDate: today,\n    applyDays: 1,\n    roeEntries,\n    rateCount: roeEntries.length,\n    rateDetails: rateDetails.join(', '),\n    currencyMap\n  }\n}];"
      },
      "id": "code-merge-rates",
      "name": "Merge & Normalize Rates",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        1820,
        0
      ]
    },
    {
      "parameters": {
        "conditions": {
          "options": {
            "caseSensitive": true,
            "leftValue": "",
            "typeValidation": "strict"
          },
          "conditions": [
            {
              "id": "condition-no-error",
              "leftValue": "={{ $json.error }}",
              "rightValue": false,
              "operator": {
                "type": "boolean",
                "operation": "equals"
              }
            }
          ],
          "combinator": "and"
        },
        "options": {}
      },
      "id": "if-rates-ok",
      "name": "Rates Retrieved OK?",
      "type": "n8n-nodes-base.if",
      "typeVersion": 2.2,
      "position": [
        2080,
        0
      ]
    },
    {
      "parameters": {
        "url": "={{ $env.VHS_API_BASE_URL || 'http://localhost:8080' }}/api/v1/roe/check?cmpcode={{ $json.cmpcode }}&date={{ $json.today }}",
        "authentication": "none",
        "method": "GET",
        "sendHeaders": true,
        "headerParameters": {
          "parameters": [
            {
              "name": "Authorization",
              "value": "=Bearer {{ $env.VHS_SERVICE_TOKEN }}"
            },
            {
              "name": "Content-Type",
              "value": "application/json"
            }
          ]
        },
        "options": {
          "response": {
            "response": {
              "responseFormat": "json"
            }
          },
          "timeout": 15000
        }
      },
      "id": "http-check-roe-exists",
      "name": "Check ROE Exists Today (per tenant)",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4.2,
      "position": [
        2340,
        -100
      ]
    },
    {
      "parameters": {
        "jsCode": "// Decide whether to CREATE or UPDATE based on per-tenant check result\nconst checkResult = $('Check ROE Exists Today (per tenant)').first().json;\nconst roeData = $('Merge & Normalize Rates').first().json;\n\nconst roeExists = checkResult?.data?.exists === true;\n\n// Build the request body matching CreateRoeRequest DTO (cmpcode is required\n// after the tenant-isolation refactor \u2014 backend will reject NULL).\nconst requestBody = {\n  cmpcode: roeData.cmpcode,\n  roeDate: roeData.roeDate,\n  applyDays: roeData.applyDays,\n  roeEntries: roeData.roeEntries\n};\n\nreturn [{\n  json: {\n    action: roeExists ? 'UPDATE' : 'CREATE',\n    roeExists,\n    existingCount: checkResult?.data?.count || 0,\n    requestBody,\n    cmpcode: roeData.cmpcode,\n    cmpname: roeData.cmpname,\n    rateCount: roeData.rateCount,\n    source: roeData.source,\n    today: roeData.today,\n    rateDetails: roeData.rateDetails\n  }\n}];"
      },
      "id": "code-decide-action",
      "name": "Decide Create or Update",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        2600,
        -100
      ]
    },
    {
      "parameters": {
        "conditions": {
          "options": {
            "caseSensitive": true,
            "leftValue": "",
            "typeValidation": "strict"
          },
          "conditions": [
            {
              "id": "condition-is-create",
              "leftValue": "={{ $json.action }}",
              "rightValue": "CREATE",
              "operator": {
                "type": "string",
                "operation": "equals"
              }
            }
          ],
          "combinator": "and"
        },
        "options": {}
      },
      "id": "if-create-or-update",
      "name": "Create or Update?",
      "type": "n8n-nodes-base.if",
      "typeVersion": 2.2,
      "position": [
        2860,
        -100
      ]
    },
    {
      "parameters": {
        "url": "={{ $env.VHS_API_BASE_URL || 'http://localhost:8080' }}/api/v1/roe/create",
        "authentication": "none",
        "method": "POST",
        "sendHeaders": true,
        "headerParameters": {
          "parameters": [
            {
              "name": "Authorization",
              "value": "=Bearer {{ $env.VHS_SERVICE_TOKEN }}"
            },
            {
              "name": "Content-Type",
              "value": "application/json"
            }
          ]
        },
        "sendBody": true,
        "specifyBody": "json",
        "jsonBody": "={{ JSON.stringify($json.requestBody) }}",
        "options": {
          "response": {
            "response": {
              "responseFormat": "json"
            }
          },
          "timeout": 30000
        }
      },
      "id": "http-create-roe",
      "name": "Create ROE (POST, per tenant)",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4.2,
      "position": [
        3120,
        -200
      ]
    },
    {
      "parameters": {
        "url": "={{ $env.VHS_API_BASE_URL || 'http://localhost:8080' }}/api/v1/roe?cmpcode={{ $json.cmpcode }}",
        "authentication": "none",
        "method": "PUT",
        "sendHeaders": true,
        "headerParameters": {
          "parameters": [
            {
              "name": "Authorization",
              "value": "=Bearer {{ $env.VHS_SERVICE_TOKEN }}"
            },
            {
              "name": "Content-Type",
              "value": "application/json"
            }
          ]
        },
        "sendBody": true,
        "specifyBody": "json",
        "jsonBody": "={{ JSON.stringify($json.requestBody) }}",
        "options": {
          "response": {
            "response": {
              "responseFormat": "json"
            }
          },
          "timeout": 30000
        }
      },
      "id": "http-update-roe",
      "name": "Update ROE (PUT, per tenant)",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4.2,
      "position": [
        3120,
        0
      ]
    },
    {
      "parameters": {
        "jsCode": "// Build per-tenant summary of what happened\nconst decision = $('Decide Create or Update').first().json;\n\nlet apiResult;\ntry {\n  if (decision.action === 'CREATE') {\n    apiResult = $('Create ROE (POST, per tenant)').first()?.json || {};\n  } else {\n    apiResult = $('Update ROE (PUT, per tenant)').first()?.json || {};\n  }\n} catch (e) {\n  apiResult = { message: 'Could not read API result' };\n}\n\nconst success = apiResult?.status === 'success' || apiResult?.message?.includes('success') || !!apiResult?.data;\n\nreturn [{\n  json: {\n    success,\n    cmpcode: decision.cmpcode,\n    cmpname: decision.cmpname,\n    action: decision.action,\n    date: decision.today,\n    rateCount: decision.rateCount,\n    source: decision.source,\n    rateDetails: decision.rateDetails,\n    apiMessage: apiResult?.message || apiResult?.data || JSON.stringify(apiResult).substring(0, 300),\n    existingCount: decision.existingCount,\n    timestamp: new Date().toISOString()\n  }\n}];"
      },
      "id": "code-build-summary",
      "name": "Build Tenant Summary",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        3380,
        -100
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "=INSERT INTO roe_auto_update_log (update_date, cmpcode, action_taken, rate_count, forex_source, rate_details, api_response, success, created_at)\nVALUES (\n  '{{ $json.date }}',\n  '{{ $json.cmpcode }}',\n  '{{ $json.action }}',\n  {{ $json.rateCount }},\n  '{{ $json.source }}',\n  '{{ $json.rateDetails.substring(0, 500) }}',\n  '{{ $json.apiMessage.substring(0, 500).replace(/'/g, \"''\") }}',\n  '{{ $json.success ? \"Y\" : \"N\" }}',\n  GETDATE()\n)",
        "options": {}
      },
      "id": "mssql-log-update",
      "name": "Log ROE Update (per tenant)",
      "type": "n8n-nodes-base.microsoftSql",
      "typeVersion": 1.1,
      "position": [
        3640,
        -100
      ],
      "credentials": {
        "microsoftSql": {
          "name": "<your credential>"
        }
      }
    },
    {
      "parameters": {
        "jsCode": "// Aggregate every tenant's outcome into a single summary for the email\nconst summaries = $items('Build Tenant Summary').map(i => i.json);\nconst overallSuccess = summaries.every(s => s.success);\n\nconst rows = summaries.map(s =>\n  `<tr>\n    <td style=\"padding:8px 12px;border-bottom:1px solid #e8eef3;\">${s.cmpcode} \u2014 ${s.cmpname || ''}</td>\n    <td style=\"padding:8px 12px;border-bottom:1px solid #e8eef3;\">${s.action}</td>\n    <td style=\"padding:8px 12px;border-bottom:1px solid #e8eef3;text-align:center;\">${s.rateCount}</td>\n    <td style=\"padding:8px 12px;border-bottom:1px solid #e8eef3;\">${s.source}</td>\n    <td style=\"padding:8px 12px;border-bottom:1px solid #e8eef3;\">${s.success ? '\u2713 OK' : '\u2717 FAIL \u2014 ' + (s.apiMessage || '').substring(0, 80)}</td>\n  </tr>`\n).join('');\n\nreturn [{\n  json: {\n    overallSuccess,\n    tenantCount: summaries.length,\n    successCount: summaries.filter(s => s.success).length,\n    rowsHtml: rows,\n    date: summaries[0]?.date || new Date().toISOString().split('T')[0],\n    timestamp: new Date().toISOString()\n  }\n}];"
      },
      "id": "code-aggregate",
      "name": "Aggregate All Tenants",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        780,
        360
      ]
    },
    {
      "parameters": {
        "fromEmail": "=VHS System <no-reply@yourcompany.com>",
        "toEmail": "={{ $env.FINANCE_TEAM_EMAIL || 'finance@yourcompany.com' }}",
        "subject": "=ROE Auto-Updated \u2014 {{ $json.date }} ({{ $json.successCount }}/{{ $json.tenantCount }} tenants)",
        "emailFormat": "html",
        "html": "=<!DOCTYPE html>\n<html>\n<body style=\"margin:0;padding:0;background:#f4f7fa;font-family:'Segoe UI',Arial,sans-serif;color:#1a2940;\">\n  <div style=\"max-width:760px;margin:32px auto;background:#fff;border-radius:12px;overflow:hidden;box-shadow:0 4px 24px rgba(10,47,78,0.08);\">\n    <div style=\"background:#0a2f4e;padding:24px 36px;\">\n      <h1 style=\"color:#fff;margin:0;font-size:18px;\">ROE Auto-Update Report (per tenant)</h1>\n    </div>\n    <div style=\"padding:28px 36px;\">\n      <p style=\"margin:0 0 16px;font-size:14px;\">Date: <strong>{{ $json.date }}</strong> &middot; Tenants processed: <strong>{{ $json.successCount }} / {{ $json.tenantCount }}</strong></p>\n      <table style=\"width:100%;border-collapse:collapse;font-size:13px;margin-bottom:16px;\">\n        <thead>\n          <tr style=\"background:#f8fafc;\">\n            <th style=\"padding:10px 12px;text-align:left;border-bottom:2px solid #e8eef3;\">Tenant</th>\n            <th style=\"padding:10px 12px;text-align:left;border-bottom:2px solid #e8eef3;\">Action</th>\n            <th style=\"padding:10px 12px;text-align:center;border-bottom:2px solid #e8eef3;\">Rates</th>\n            <th style=\"padding:10px 12px;text-align:left;border-bottom:2px solid #e8eef3;\">Source</th>\n            <th style=\"padding:10px 12px;text-align:left;border-bottom:2px solid #e8eef3;\">Result</th>\n          </tr>\n        </thead>\n        <tbody>\n          {{ $json.rowsHtml }}\n        </tbody>\n      </table>\n      <p style=\"font-size:12px;color:#64748b;margin:0;\">Each tenant's rates are isolated via <code>roe.cmpcode</code>. Manually review in <strong>Settings &gt; ROE Master</strong> if any tenant failed.</p>\n    </div>\n    <div style=\"background:#f8fafc;padding:16px 36px;border-top:1px solid #e8eef3;\">\n      <p style=\"color:#94a3b8;font-size:11px;margin:0;text-align:center;\">VHS ROE Auto-Update \u2022 {{ $json.timestamp }}</p>\n    </div>\n  </div>\n</body>\n</html>",
        "options": {
          "appendAttribution": false
        }
      },
      "id": "send-email-roe",
      "name": "Notify Finance Team",
      "type": "n8n-nodes-base.emailSend",
      "typeVersion": 2.1,
      "position": [
        1040,
        360
      ],
      "credentials": {
        "smtp": {
          "name": "<your credential>"
        }
      }
    },
    {
      "parameters": {
        "content": "## VHS - ROE Auto-Update (Tenant-Aware)\n\n**Schedule**: Daily at 6:00 AM (before business hours)\n\n**Flow** (per tenant, looped):\n1. Fetch active tenants from `dbo.company`\n2. For each tenant: fetch active currencies from `dbo.Currency` (filtered by `Cmpcode`)\n3. If tenant has currencies \u2192 call two forex APIs in parallel\n4. Normalize rates and build `CreateRoeRequest` body **including the `cmpcode` field**\n5. Check existing ROE via `GET /api/v1/roe/check?cmpcode={tenant}&date={today}`\n6. CREATE (`POST /api/v1/roe/create` \u2014 `cmpcode` in body) or UPDATE (`PUT /api/v1/roe?cmpcode={tenant}` \u2014 `cmpcode` in query) per tenant\n7. Log per-tenant result to `roe_auto_update_log` (now includes `cmpcode` column)\n8. After all tenants finish, send a single consolidated email\n\n## Why this changed\n\nThe `roe` table is now tenant-scoped (`cmpcode` column). The previous workflow:\n- Queried a non-existent `CurrencyMaster` table (real table is `Currency`)\n- Wrote a single global ROE row, leaking rates across tenants\n- The backend now **rejects** ROE writes without a `cmpcode`\n\n**Environment Variables**:\n- `VHS_API_BASE_URL`: e.g. `http://localhost:8080`\n- `VHS_SERVICE_TOKEN`: JWT for n8n service account (any tenant \u2014 cmpcode is in the body, not the JWT, for this admin endpoint)\n- `FINANCE_TEAM_EMAIL`: recipient for the consolidated daily email",
        "height": 540,
        "width": 560,
        "color": 5
      },
      "id": "sticky-note-roe-doc",
      "name": "Workflow Documentation",
      "type": "n8n-nodes-base.stickyNote",
      "typeVersion": 1,
      "position": [
        -60,
        -640
      ]
    },
    {
      "parameters": {
        "content": "## One-Time Setup SQL\n\n```sql\n-- New table layout: per-tenant log\nIF OBJECT_ID('dbo.roe_auto_update_log','U') IS NULL\nCREATE TABLE roe_auto_update_log (\n    id            INT IDENTITY(1,1) PRIMARY KEY,\n    update_date   VARCHAR(10),\n    cmpcode       VARCHAR(3),\n    action_taken  VARCHAR(10),\n    rate_count    INT,\n    forex_source  VARCHAR(50),\n    rate_details  VARCHAR(MAX),\n    api_response  VARCHAR(MAX),\n    success       VARCHAR(1),\n    created_at    DATETIME DEFAULT GETDATE()\n);\n\n-- If you already have the legacy table without cmpcode, add the column:\nIF COL_LENGTH('dbo.roe_auto_update_log','cmpcode') IS NULL\n  ALTER TABLE dbo.roe_auto_update_log ADD cmpcode VARCHAR(3) NULL;\n```\n\n## VHS Service Account\n\nUse a long-lived JWT from `/api/v1/auth/login` with admin privileges.\n`cmpcode` is supplied in the request body/query \u2014 the JWT's tenant claim is\nignored by `RoeController` for this admin endpoint.",
        "height": 460,
        "width": 540,
        "color": 3
      },
      "id": "sticky-note-roe-sql",
      "name": "Setup SQL & Auth",
      "type": "n8n-nodes-base.stickyNote",
      "typeVersion": 1,
      "position": [
        560,
        -640
      ]
    }
  ],
  "connections": {
    "Daily 6 AM Trigger": {
      "main": [
        [
          {
            "node": "Get Active Tenants",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Active Tenants": {
      "main": [
        [
          {
            "node": "Split In Batches (per tenant)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Split In Batches (per tenant)": {
      "main": [
        [
          {
            "node": "Aggregate All Tenants",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Get Tenant Currencies",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Tenant Currencies": {
      "main": [
        [
          {
            "node": "Prepare Tenant Currency List",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Prepare Tenant Currency List": {
      "main": [
        [
          {
            "node": "Tenant Has Currencies?",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Tenant Has Currencies?": {
      "main": [
        [
          {
            "node": "Fetch Forex Rates (Primary)",
            "type": "main",
            "index": 0
          },
          {
            "node": "Fetch Forex Rates (Fallback)",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Split In Batches (per tenant)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch Forex Rates (Primary)": {
      "main": [
        [
          {
            "node": "Merge & Normalize Rates",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch Forex Rates (Fallback)": {
      "main": [
        [
          {
            "node": "Merge & Normalize Rates",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge & Normalize Rates": {
      "main": [
        [
          {
            "node": "Rates Retrieved OK?",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Rates Retrieved OK?": {
      "main": [
        [
          {
            "node": "Check ROE Exists Today (per tenant)",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Split In Batches (per tenant)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Check ROE Exists Today (per tenant)": {
      "main": [
        [
          {
            "node": "Decide Create or Update",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Decide Create or Update": {
      "main": [
        [
          {
            "node": "Create or Update?",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Create or Update?": {
      "main": [
        [
          {
            "node": "Create ROE (POST, per tenant)",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Update ROE (PUT, per tenant)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Create ROE (POST, per tenant)": {
      "main": [
        [
          {
            "node": "Build Tenant Summary",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Update ROE (PUT, per tenant)": {
      "main": [
        [
          {
            "node": "Build Tenant Summary",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Build Tenant Summary": {
      "main": [
        [
          {
            "node": "Log ROE Update (per tenant)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Log ROE Update (per tenant)": {
      "main": [
        [
          {
            "node": "Split In Batches (per tenant)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Aggregate All Tenants": {
      "main": [
        [
          {
            "node": "Notify Finance Team",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  },
  "active": false,
  "settings": {
    "executionOrder": "v1",
    "saveManualExecutions": true,
    "callerPolicy": "workflowsFromSameOwner",
    "errorWorkflow": ""
  },
  "versionId": "vhs-roe-auto-update-v2-tenant-aware",
  "tags": [
    {
      "name": "VHS",
      "id": "vhs"
    },
    {
      "name": "Finance",
      "id": "finance"
    },
    {
      "name": "ROE",
      "id": "roe"
    },
    {
      "name": "Multi-Tenant",
      "id": "multi-tenant"
    },
    {
      "name": "Automation",
      "id": "automation"
    }
  ]
}

Credentials you'll need

Each integration node will prompt for credentials when you import. We strip credential IDs before publishing — you'll add your own.

• microsoftSql
• smtp

About this workflow

VHS - ROE Auto Update (Daily Forex Sync, Tenant-Aware). Uses microsoftSql, httpRequest, emailSend. Scheduled trigger; 21 nodes.

Source: https://github.com/arthijustinraj/anchordevelopment/blob/64f38fe9a551bbee6d3e5f88e600ea867f4daebc/n8n-workflows/roe-auto-update.json — original creator credit. Request a take-down →

More Web Scraping workflows → · Browse all categories →

Related workflows

Workflows that share integrations, category, or trigger type with this one. All free to copy and import.