Procurement Approval Workflow, Node Reference and Operator Runbook

{
  "formTitle": "FieldOps Procurement Request"
}

// Deterministic regex parse of a templated procurement email. No LLM (extraction is roadmap).

const m = $input.first().json;

const subject = String(m.subject || '');
const from = String(m.from || m.fromEmail || '');
let body = String(m.text || m.textPlain || m.snippet || '');

// Cut quoted reply history so a forwarded thread can't yield stale values.
const cut = body.search(/^\s*(>|On .+wrote:|-----Original Message-----|_{5,}|From:\s)/m);
if (cut > 0) body = body.slice(0, cut);

// Accept "Key: value" lines, case-insensitive, with common label variants.
const ALIASES = {
  business_unit: ['business unit', 'business_unit', 'bu'],
  sku:           ['sku', 'part number', 'part no', 'material', 'item number'],
  item_description: ['description', 'item', 'item description'],
  quantity:      ['quantity', 'qty'],
  category:      ['category'],
  cost_center:   ['cost center', 'cost centre', 'cost_center', 'cc'],
  preferred_vendor: ['preferred vendor', 'vendor', 'supplier'],
  justification: ['justification', 'reason', 'notes'],
  requester_email: ['requester email', 'requester', 'requested by'],
};

const found = {};
for (const raw of body.split(/\r?\n/)) {
  const mm = raw.match(/^\s*\*?\s*([A-Za-z _\/-]{2,30}?)\s*\*?\s*[:\-]\s*(.+?)\s*$/);
  if (!mm) continue;
  const label = mm[1].trim().toLowerCase();
  const value = mm[2].trim();
  for (const [field, names] of Object.entries(ALIASES)) {
    if (names.includes(label) && !(field in found)) { found[field] = value; break; }
  }
}

// Sender is the requester unless the template overrides.
const senderMatch = from.match(/[\w.+-]+@[\w.-]+\.\w+/);
if (!found.requester_email && senderMatch) found.requester_email = senderMatch[0];

// Parseable only if the fields the pipeline can't invent are present.
const REQUIRED = ['business_unit', 'sku', 'quantity'];
const missing = REQUIRED.filter(f => !found[f] || String(found[f]).trim() === '');
// "two boxes" strips to "" and Number("") is 0: require non-empty and > 0.
const qtyRaw = String(found.quantity || '').replace(/[^\d.]/g, '');
const qtyNum = qtyRaw === '' ? NaN : Number(qtyRaw);
const qtyValid = Number.isFinite(qtyNum) && qtyNum > 0;
if (!missing.includes('quantity') && !qtyValid) missing.push('quantity');

return [{ json: {
  ...found,
  quantity: qtyValid ? qtyNum : null,
  category: found.category || 'MRO',
  intake_channel: 'email',
  parse_ok: missing.length === 0,
  parse_missing: missing,
  source_subject: subject,
  source_from: from,
}}];

// Normalize every intake channel to one purchase-requisition shape.
const f = $input.first().json;
const now = new Date().toISOString();

const norm = (v) => (v === undefined || v === null ? '' : String(v).trim());

// request_id: deterministic on content + minute, so a same-minute resubmit collides for dedup.
const seed = [norm(f.business_unit), norm(f.sku), norm(f.quantity),
              norm(f.requester_email), now.slice(0, 16)].join('|');
let h = 0;
for (let i = 0; i < seed.length; i++) { h = (h * 31 + seed.charCodeAt(i)) >>> 0; }
const request_id = 'PR-' + now.slice(0, 10).replace(/-/g, '') + '-' + h.toString(36).toUpperCase().padStart(7, '0');

return [{ json: {
  request_id,
  intake_channel: norm(f.intake_channel) || 'form',
  business_unit: norm(f.business_unit) || 'BU-01',
  sku: norm(f.sku).toUpperCase(),
  item_description: norm(f.item_description),
  quantity: (() => {
    // Throw on bad quantity; defaulting to 1 is a silent wrong order.
    const q = Number(f.quantity);
    if (!Number.isFinite(q) || q <= 0) {
      throw new Error('Invalid quantity: ' + JSON.stringify(f.quantity) +
                      ' (request ' + request_id + '). Refusing to default.');
    }
    return q;
  })(),
  category: norm(f.category) || 'MRO',
  cost_center: norm(f.cost_center) || 'CC-4400',
  preferred_vendor: norm(f.preferred_vendor),
  justification: norm(f.justification),
  requester_email: norm(f.requester_email),
  created_at: now,
  state: 'RECEIVED',
}}];

// Collapse the BU's rule rows to ONE item (req + policy) so pricing doesn't fan out.
const req = $('Normalize Request').first().json;
const rules = $input.all().map(i => i.json).filter(r => r && r.business_unit);

// max_lead_time_days is per BU+category; take the strictest if rows disagree.
const leads = rules.map(r => Number(r.max_lead_time_days)).filter(n => Number.isFinite(n) && n > 0);
const maxLead = leads.length ? Math.min(...leads) : null;

return [{ json: { ...req,
  policy_rule_count: rules.length,
  max_lead_time_days: maxLead,
  has_policy: rules.length > 0,
}}];

// Derive amount from vendor catalogs; pick cheapest source within the BU lead-time tolerance.
const req = $('Normalize Request').first().json;
const qty = Number(req.quantity) || 1;

// BU lead-time tolerance, loaded before pricing.
const policy = $('Load BU Policy').first().json;
const maxLead = Number(policy.max_lead_time_days);
const hasTolerance = Number.isFinite(maxLead) && maxLead > 0;

const rows = $input.all().map(i => i.json).filter(r => r && r.sku && r.vendor_id);

if (rows.length === 0) {
  // No catalog match: route to sourcing, never auto-approve.
  return [{ json: { ...req, catalog_match: false, match_count: 0,
    derived_amount: null, state: 'NEEDS_SOURCING' }}];
}

const priced = rows
  .map(r => ({ ...r, unit_price: Number(r.unit_price), lead_time_days: Number(r.lead_time_days) }))
  .filter(r => Number.isFinite(r.unit_price))
  .sort((a, b) => a.unit_price - b.unit_price);

// Keep only vendors that can deliver within tolerance.
const eligible = hasTolerance
  ? priced.filter(r => Number.isFinite(r.lead_time_days) && r.lead_time_days <= maxLead)
  : priced;

// None in tolerance: take the fastest, flag a breach, force a human.
const breach = hasTolerance && eligible.length === 0;
const pool = breach
  ? [...priced].sort((a, b) => a.lead_time_days - b.lead_time_days)
  : eligible;

const best = pool[0];
const excluded = priced
  .filter(r => !pool.includes(r))
  .map(r => ({ vendor: r.vendor_name, price: r.unit_price, lead_time_days: r.lead_time_days }));

// VEND-A is the incumbent; delta vs it is the contract-leakage number.
const incumbent = priced.find(r => r.vendor_id === 'VEND-A') || null;
const derived_amount = Number((best.unit_price * qty).toFixed(2));
const incumbent_amount = incumbent ? Number((incumbent.unit_price * qty).toFixed(2)) : null;
const savings = incumbent_amount !== null
  ? Number((incumbent_amount - derived_amount).toFixed(2)) : 0;

// Lead-time premium: cost of not taking the cheapest source.
const cheapest = priced[0];
const lead_time_premium = (best.vendor_id !== cheapest.vendor_id)
  ? Number(((best.unit_price - cheapest.unit_price) * qty).toFixed(2)) : 0;

const maverick_flag = Boolean(req.preferred_vendor) && req.preferred_vendor !== best.vendor_id;

return [{ json: { ...req,
  catalog_match: true,
  match_count: priced.length,
  best_vendor_id: best.vendor_id,
  best_vendor_name: best.vendor_name,
  best_unit_price: best.unit_price,
  contract_flag: best.contract_flag,
  lead_time_days: best.lead_time_days,
  max_lead_time_days: hasTolerance ? maxLead : null,
  lead_time_breach: breach,
  lead_time_premium,
  excluded_on_lead_time: excluded,
  currency: best.currency || 'EUR',
  derived_amount,
  incumbent_amount,
  savings_vs_incumbent: savings,
  maverick_flag,
  alternatives: priced.map(p => ({
    vendor: p.vendor_name, price: p.unit_price,
    contract: p.contract_flag, lead_time_days: p.lead_time_days,
  })),
  state: 'PRICED',
}}];

// Post to #requests: requisition received and priced. Separate channel/audience from #approvals.
const j = $input.first().json;
const money = (n) => (n === null || n === undefined) ? 'n/a'
  : (j.currency || 'EUR') + ' ' + Number(n).toLocaleString('de-DE', {minimumFractionDigits: 2, maximumFractionDigits: 2});
return [{ json: { ...j, slack_payload: {
  text: 'New requisition ' + j.request_id,
  blocks: [
    {type: 'section', text: {type: 'mrkdwn',
      text: '*New requisition* `' + j.request_id + '`\n' + j.sku +
            (j.item_description ? ' - ' + j.item_description : '') + '  ×' + j.quantity}},
    {type: 'context', elements: [{type: 'mrkdwn',
      text: j.business_unit + '  |  ' + money(j.derived_amount) + ' via ' + (j.best_vendor_name || 'n/a') +
            '  |  raised by ' + (j.requester_email || 'unknown') + ' (' + (j.intake_channel || 'form') + ')'}]},
  ]}}}];

"url": "={{ $env.REQUESTS_SLACK_WEBHOOK }}"

// Route on DoA bands; self-check grades its own output. Deterministic, no LLM.
const req = $input.first().json;
const amount = Number(req.derived_amount);

const rules = $('DoA Rule Lookup').all().map(i => i.json).filter(r => r && r.business_unit && r.category);
const base = { ...req, evaluated_at: new Date().toISOString() };

// Default-deny: no rule row for this BU/category.
if (rules.length === 0) {
  return [{ json: { ...base, route: 'default_deny',
    route_reason: 'no_rule_row_for_bu_category', approver: null,
    self_check: 'pass', state: 'AWAITING_APPROVAL' }}];
}

// Default-deny: no vendor within lead-time tolerance.
if (req.lead_time_breach) {
  const r0 = rules[0];
  return [{ json: { ...base, route: 'default_deny',
    route_reason: 'lead_time_breach', approver: r0.approver_primary || null,
    approver_fallback: r0.approver_fallback, escalation_hours: Number(r0.escalation_hours),
    self_check: 'pass', state: 'AWAITING_APPROVAL' }}];
}

const bands = rules.filter(r =>
  amount >= Number(r.threshold_min) && amount < Number(r.threshold_max));

// Default-deny: amount outside every band.
if (bands.length === 0) {
  return [{ json: { ...base, route: 'default_deny',
    route_reason: 'amount_outside_all_bands', approver: null,
    self_check: 'pass', state: 'AWAITING_APPROVAL' }}];
}

// Self-check: exactly one band must match; overlapping bands are a config error.
const self_check = bands.length === 1 ? 'pass' : 'FAIL_overlapping_bands';
if (self_check !== 'pass') {
  return [{ json: { ...base, route: 'default_deny',
    route_reason: 'overlapping_rule_bands', matched_bands: bands.length,
    approver: null, self_check, state: 'AWAITING_APPROVAL' }}];
}

const band = bands[0];
const autoUnder = Number(band.auto_approve_under) || 0;

// approval_mode is config, not code.
let route;
if (autoUnder > 0 && amount < autoUnder) route = 'auto_approve';
else if (String(band.approval_mode).toLowerCase() === 'dual') route = 'dual_approver';
else route = 'single_approver';

return [{ json: { ...base,
  route, route_reason: 'matched_band',
  band_min: Number(band.threshold_min), band_max: Number(band.threshold_max),
  approval_mode: band.approval_mode,
  approver: route === 'auto_approve' ? null : band.approver_primary,
  approver_fallback: band.approver_fallback,
  escalation_hours: Number(band.escalation_hours),
  auto_approve_under: autoUnder,
  self_check,
  state: route === 'auto_approve' ? 'APPROVED' : 'AWAITING_APPROVAL',
}}];

// Build the Slack approval card and both decision links (same resume URL, different decision).
const j = $input.first().json;
const resume = $execution.resumeUrl;

const money = (n) => (n === null || n === undefined)
  ? 'n/a'
  : (j.currency || 'EUR') + ' ' + Number(n).toLocaleString('de-DE', {minimumFractionDigits: 2, maximumFractionDigits: 2});

// resumeUrl already carries ?signature=; join with & or the resume 401s.
const join = (u, kv) => u + (u.includes('?') ? '&' : '?') + kv;
const approveUrl = join(resume, 'decision=approve');
const rejectUrl  = join(resume, 'decision=reject');

const alts = Array.isArray(j.alternatives) ? j.alternatives : [];
const altLines = alts.slice(0, 4)
  .map(a => '• ' + a.vendor + '  ' + money(a.price) + '  _' + a.contract + '_')
  .join('\n') || '_no alternatives_';

const flags = [];
if (j.maverick_flag) flags.push(':warning: *Off-contract request* - requester named ' + (j.preferred_vendor || 'another vendor') + ', not the best contracted source');
if (j.route === 'default_deny') flags.push(':no_entry: *Default-deny* - ' + (j.route_reason || 'no matching rule') + '. This did NOT auto-approve.');
if (j.route === 'dual_approver') flags.push(':busts_in_silhouette: *Dual approval* - this is gate 1 of 2');

const blocks = [
  {type: 'header', text: {type: 'plain_text', text: 'Purchase requisition ' + j.request_id}},
  {type: 'section', fields: [
    {type: 'mrkdwn', text: '*Business unit*\n' + j.business_unit},
    {type: 'mrkdwn', text: '*Cost center*\n' + (j.cost_center || 'n/a')},
    {type: 'mrkdwn', text: '*Item*\n' + j.sku + (j.item_description ? ' - ' + j.item_description : '')},
    {type: 'mrkdwn', text: '*Quantity*\n' + j.quantity},
    {type: 'mrkdwn', text: '*Amount (derived)*\n' + money(j.derived_amount)},
    {type: 'mrkdwn', text: '*Best source*\n' + (j.best_vendor_name || 'n/a')},
  ]},
  {type: 'section', text: {type: 'mrkdwn',
    text: '*Savings vs incumbent:* ' + money(j.savings_vs_incumbent) +
          '\n*Lead time:* ' + (j.lead_time_days ?? 'n/a') + ' days   *Pricing:* ' + (j.contract_flag || 'n/a')}},
  {type: 'section', text: {type: 'mrkdwn', text: '*Priced against*\n' + altLines}},
];
if (flags.length) blocks.push({type: 'section', text: {type: 'mrkdwn', text: flags.join('\n')}});
blocks.push({type: 'context', elements: [{type: 'mrkdwn',
  text: 'Requested by ' + (j.requester_email || 'unknown') + ' via ' + (j.intake_channel || 'form') +
        '  |  routes to ' + (j.approver || 'a human') + '  |  escalates in ' + (j.escalation_hours ?? 24) + 'h'}]});
blocks.push({type: 'actions', elements: [
  {type: 'button', style: 'primary', text: {type: 'plain_text', text: 'Approve'}, url: approveUrl},
  {type: 'button', style: 'danger',  text: {type: 'plain_text', text: 'Reject'},  url: rejectUrl},
]});

return [{ json: { ...j,
  slack_payload: {
    text: 'Approval needed: ' + j.request_id + ' ' + money(j.derived_amount),
    blocks,
  },
  approve_url: approveUrl,
  reject_url: rejectUrl,
  awaiting_since: new Date().toISOString(),
}}];

"url": "={{ $env.APPROVALS_SLACK_WEBHOOK }}"

// Resume from Slack button, email link, or Wait timeout. Timeout = no decision = escalate.
const prior = $('Build Approval Card').first().json;

// Webhook resume exposes the caller's query; timeout has none.
const first = $input.first().json || {};
const q = first.query || (first.body && first.body.query) || {};
const decision = String(q.decision || '').toLowerCase();

const base = { ...prior, decided_at: new Date().toISOString() };

if (decision === 'approve') {
  return [{ json: { ...base, decision: 'approved', decided_by: prior.approver || 'approver',
                    state: prior.route === 'dual_approver' ? 'GATE1_APPROVED' : 'APPROVED' }}];
}
if (decision === 'reject') {
  return [{ json: { ...base, decision: 'rejected', decided_by: prior.approver || 'approver',
                    state: 'REJECTED' }}];
}

// No decision: wait elapsed, escalate to configured fallback.
return [{ json: { ...base, decision: 'timed_out', decided_by: null,
                  escalated_to: prior.approver_fallback || null,
                  state: 'ESCALATED' }}];

// Build the #orders post for every terminal state (approved/rejected/escalated/needs-sourcing).
const j = $input.first().json;

const money = (n) => (n === null || n === undefined) ? 'n/a'
  : (j.currency || 'EUR') + ' ' + Number(n).toLocaleString('de-DE',
      {minimumFractionDigits: 2, maximumFractionDigits: 2});

// Time-to-decision: capture now, while both timestamps exist.
let elapsed = null;
if (j.awaiting_since && j.decided_at) {
  const ms = new Date(j.decided_at) - new Date(j.awaiting_since);
  if (Number.isFinite(ms) && ms >= 0) {
    const mins = Math.round(ms / 60000);
    elapsed = mins < 60 ? mins + ' min'
            : (mins < 1440 ? (mins / 60).toFixed(1) + ' h' : (mins / 1440).toFixed(1) + ' days');
  }
}

const state = String(j.state || '').toUpperCase();
const MAP = {
  APPROVED:        [':white_check_mark:', 'Approved'],
  GATE1_APPROVED:  [':white_check_mark:', 'Approved at gate 1 of 2'],
  REJECTED:        [':x:', 'Rejected'],
  ESCALATED:       [':alarm_clock:', 'Escalated, no decision in time'],
  NEEDS_SOURCING:  [':mag:', 'No catalog match, sourcing required'],
};
const [icon, label] = MAP[state] || [':grey_question:', state || 'Unknown'];

const lines = [];
lines.push(icon + ' *' + label + '* - `' + j.request_id + '`');
lines.push('*' + (j.sku || 'n/a') + '*' + (j.item_description ? ' - ' + j.item_description : '') +
           '  ×' + (j.quantity ?? 'n/a'));

if (state === 'NEEDS_SOURCING') {
  lines.push('_No approved vendor lists this item. Routed for RFQ / three quotes rather than auto-approved._');
} else {
  lines.push(money(j.derived_amount) + ' via *' + (j.best_vendor_name || 'n/a') + '*' +
             (j.savings_vs_incumbent ? '  (' + money(j.savings_vs_incumbent) + ' under the incumbent contract price)' : ''));
}
if (j.maverick_flag) {
  lines.push(':warning: Off-contract request corrected at intake - requester named ' +
             (j.preferred_vendor || 'another vendor') + '.');
}
if (state === 'ESCALATED') {
  lines.push(':arrow_right: Escalated to *' + (j.escalated_to || 'fallback approver') +
             '* after ' + (j.escalation_hours ?? 24) + 'h with no decision.');
}
if (j.route === 'default_deny') {
  lines.push(':no_entry: Reached a human by *default-deny* (' + (j.route_reason || 'no matching rule') +
             '). It did not auto-approve.');
}

const ctx = [];
ctx.push(j.business_unit + (j.cost_center ? ' / ' + j.cost_center : ''));
ctx.push('raised by ' + (j.requester_email || 'unknown') + ' via ' + (j.intake_channel || 'form'));
if (j.decided_by) ctx.push('decided by ' + j.decided_by);
if (elapsed) ctx.push('time to decision ' + elapsed);

// No PO number invented; the ERP owns that sequence (write-back is roadmap).
const poLine = (state === 'APPROVED' || state === 'GATE1_APPROVED')
  ? '_Ready for PO creation. ERP write-back is roadmap, so the purchase order number still comes from your ERP. Internal reference `' + j.request_id + '`._'
  : null;

const blocks = [
  {type: 'section', text: {type: 'mrkdwn', text: lines.join('\n')}},
];
if (poLine) blocks.push({type: 'section', text: {type: 'mrkdwn', text: poLine}});
blocks.push({type: 'context', elements: [{type: 'mrkdwn', text: ctx.join('  |  ')}]});

return [{ json: { ...j,
  time_to_decision: elapsed,
  slack_payload: { text: label + ': ' + j.request_id, blocks },
}}];

"url": "={{ $env.ORDERS_SLACK_WEBHOOK }}"

// RECORD: one row per state transition (the audit + drift instrument).
const j = $input.first().json;
return [{ json: {
  request_id: j.request_id || 'UNKNOWN',
  event: j.state || 'UNKNOWN',
  detail: JSON.stringify({
    route: j.route || null,
    route_reason: j.route_reason || null,
    self_check: j.self_check || null,
    derived_amount: j.derived_amount ?? null,
    currency: j.currency || 'EUR',
    best_vendor: j.best_vendor_name || null,
    savings_vs_incumbent: j.savings_vs_incumbent ?? null,
    maverick_flag: j.maverick_flag ?? null,
    catalog_match: j.catalog_match ?? null,
    approver: j.approver || null,
  }),
  actor: j.requester_email || 'system',
  ts: new Date().toISOString(),
}}];