Implementation Guide: Auto-generate work orders from customer purchase orders

# Example SharePoint list schema (create via PowerShell or manual)
# Columns: CustomerName (Text), CustomerPartNumber (Text), InternalSKU (Text),
#          ProductName (Text), BOM_ID (Text), UOM (Choice), IsActive (Boolean)

# If using Airtable, create via API:
curl -X POST 'https://api.airtable.com/v0/meta/bases/{baseId}/tables' \
  -H 'Authorization: Bearer {pat}' \
  -H 'Content-Type: application/json' \
  -d '{"name": "PO_SKU_Crosswalk", "fields": [{"name": "CustomerName", "type": "singleLineText"}, {"name": "CustomerPartNumber", "type": "singleLineText"}, {"name": "InternalSKU", "type": "singleLineText"}, {"name": "BOM_ID", "type": "singleLineText"}, {"name": "UOM", "type": "singleSelect", "options": {"choices": [{"name": "EA"}, {"name": "LB"}, {"name": "FT"}, {"name": "KG"}]}}]}'

# Connect to Exchange Online PowerShell
Connect-ExchangeOnline -UserPrincipalName admin@clientdomain.com

# Create shared mailbox
New-Mailbox -Shared -Name 'Purchase Orders' -DisplayName 'Purchase Orders' -Alias 'purchaseorders' -PrimarySmtpAddress 'po@clientdomain.com'

# Grant full access to service account
Add-MailboxPermission -Identity 'po@clientdomain.com' -User 'svc-automation@clientdomain.com' -AccessRights FullAccess -InheritanceType All

# Grant Send-As permission for auto-replies
Add-RecipientPermission -Identity 'po@clientdomain.com' -Trustee 'svc-automation@clientdomain.com' -AccessRights SendAs -Confirm:$false

# Set retention policy (create first if needed)
New-RetentionPolicyTag -Name 'PO-7Year' -Type All -RetentionEnabled $true -AgeLimitForRetention 2555 -RetentionAction MoveToArchive
New-RetentionPolicy -Name 'PO Retention Policy' -RetentionPolicyTagLinks 'PO-7Year'
Set-Mailbox -Identity 'po@clientdomain.com' -RetentionPolicy 'PO Retention Policy'

# Login to Azure CLI
az login

# Set subscription (use CSP subscription for billing)
az account set --subscription 'MSP-CSP-ClientName'

# Create resource group
az group create --name rg-clientname-docai --location eastus

# Create Document Intelligence resource (S0 tier for production)
az cognitiveservices account create \
  --name docai-clientname \
  --resource-group rg-clientname-docai \
  --kind FormRecognizer \
  --sku S0 \
  --location eastus \
  --yes

# Get the endpoint and key
az cognitiveservices account show \
  --name docai-clientname \
  --resource-group rg-clientname-docai \
  --query properties.endpoint -o tsv

az cognitiveservices account keys list \
  --name docai-clientname \
  --resource-group rg-clientname-docai \
  --query key1 -o tsv

# Test with a sample PO (using curl)
ENDPOINT=$(az cognitiveservices account show --name docai-clientname --resource-group rg-clientname-docai --query properties.endpoint -o tsv)
KEY=$(az cognitiveservices account keys list --name docai-clientname --resource-group rg-clientname-docai --query key1 -o tsv)

curl -X POST "${ENDPOINT}formrecognizer/documentModels/prebuilt-invoice:analyze?api-version=2023-07-31" \
  -H "Content-Type: application/pdf" \
  -H "Ocp-Apim-Subscription-Key: ${KEY}" \
  --data-binary @sample-po.pdf

onedrive --version

# Make.com setup is GUI-based via https://www.make.com
# 
# 1. Create Organization: 'ClientName Manufacturing'
# 2. Subscribe to Pro plan ($16/month, 10,000 ops)
# 3. Create Connections:
#
# Connection 1: Microsoft 365 (OAuth 2.0)
#   - Module: Microsoft 365 Email
#   - Auth: Sign in with svc-automation@clientdomain.com
#   - Permissions: Mail.Read, Mail.ReadWrite, Mail.Send
#
# Connection 2: Azure Document Intelligence (API Key)
#   - Module: HTTP > Make a Request (custom)
#   - Base URL: {endpoint from Step 4}
#   - Headers: Ocp-Apim-Subscription-Key: {key from Step 4}
#
# Connection 3: ERP/MRP API (varies by ERP)
#   - For MRPeasy: HTTP module with API key auth
#     Base URL: https://api.mrpeasy.com/v1
#     Header: Authorization: Bearer {mrpeasy_api_key}
#   - For Odoo: HTTP module with session auth
#     Base URL: https://clientname.odoo.com/api/v1
#
# Connection 4: Microsoft Teams (OAuth 2.0)
#   - Module: Microsoft Teams
#   - Auth: Sign in with svc-automation@clientdomain.com
#   - Used for sending approval requests to production manager
#
# Connection 5: SharePoint (OAuth 2.0)
#   - Module: Microsoft SharePoint
#   - Used for reading/writing crosswalk table and archiving POs

# Make.com Scenario: 'PO-Validation-Mapping'
# 
# Module 1: Data Store > Search Records (get extracted PO data)
#   OR continue from previous scenario via Router
#
# Module 2: Iterator > Iterate Line Items
#   - Loop through each PO line item
#
# Module 3: SharePoint > Search List Items (Crosswalk Lookup)
#   - List: PO_SKU_Crosswalk
#   - Filter: CustomerName = {{po.customer_name}} 
#            AND CustomerPartNumber = {{line_item.description}}
#   - Output: InternalSKU, BOM_ID, UOM
#
# Module 4: Router (Branching)
#   - Branch 1 (Match Found): Continue to ERP BOM lookup
#   - Branch 2 (No Match): Flag for manual review
#
# Branch 1, Module 5: HTTP > Make a Request (ERP BOM Lookup)
#   - For MRPeasy: GET https://api.mrpeasy.com/v1/bom/{bom_id}
#   - Validate: BOM exists, is active, has required materials
#
# Branch 1, Module 6: Set Variable > Build Work Order Payload
#   - internal_sku: {{3.InternalSKU}}
#   - bom_id: {{3.BOM_ID}}
#   - quantity: {{line_item.quantity}}
#   - due_date: calculate from PO required delivery date
#   - priority: map from PO terms (e.g., 'RUSH' = High)
#   - source_po_number: {{po.po_number}}
#   - customer_name: {{po.customer_name}}
#
# Branch 2, Module 5b: Microsoft Teams > Create Approval
#   - Send adaptive card to Production Manager
#   - Message: 'Unknown part number {customer_part} on PO {po_number}
#     from {customer}. Please map to internal SKU.'
#   - Include link to Crosswalk table for manual update
#
# Module 7: Aggregator > Aggregate all line item results
#   - Combine validated line items into a single WO payload array
#
# Module 8: Filter > Check if all items validated
#   - If all items mapped: proceed to approval
#   - If any items flagged: hold entire PO for review

# Make.com Module: Microsoft Teams > Send Adaptive Card
# 
# Adaptive Card JSON Template:
# {
#   "type": "AdaptiveCard",
#   "version": "1.4",
#   "body": [
#     {"type": "TextBlock", "text": "New Work Order Approval Required", "weight": "Bolder", "size": "Large"},
#     {"type": "FactSet", "facts": [
#       {"title": "PO Number", "value": "{{po_number}}"},
#       {"title": "Customer", "value": "{{customer_name}}"},
#       {"title": "PO Date", "value": "{{po_date}}"},
#       {"title": "Required Date", "value": "{{required_date}}"}
#     ]},
#     {"type": "TextBlock", "text": "Line Items:", "weight": "Bolder"},
#     {"type": "Table", "columns": [
#       {"width": 2}, {"width": 1}, {"width": 1}, {"width": 2}
#     ], "rows": [
#       {"cells": [
#         {"items": [{"type": "TextBlock", "text": "Customer Part#"}]},
#         {"items": [{"type": "TextBlock", "text": "Qty"}]},
#         {"items": [{"type": "TextBlock", "text": "Internal SKU"}]},
#         {"items": [{"type": "TextBlock", "text": "Product Name"}]}
#       ]}
#     ]}
#   ],
#   "actions": [
#     {"type": "Action.Submit", "title": "✅ Approve", "data": {"action": "approve", "po_id": "{{po_number}}"}},
#     {"type": "Action.Submit", "title": "❌ Reject", "data": {"action": "reject", "po_id": "{{po_number}}"}},
#     {"type": "Action.Submit", "title": "✏️ Edit & Approve", "data": {"action": "edit", "po_id": "{{po_number}}"}}
#   ]
# }
#
# Next Module: Microsoft Teams > Wait for Response
#   - Timeout: 4 hours (configurable)
#   - If no response in 4 hours: send reminder
#   - If no response in 8 hours: escalate to backup approver
#
# Routing after response:
#   - approve: proceed to work order creation
#   - reject: log rejection, notify sales/CS team
#   - edit: open WO details in ERP for manual adjustment, then auto-complete

# MRPeasy, Odoo, and Fishbowl API examples

# ============================================================
# MRPeasy API - Create Manufacturing Order
# ============================================================
# Make.com HTTP Module:
# Method: POST
# URL: https://api.mrpeasy.com/v1/manufacturing-orders
# Headers:
#   Content-Type: application/json
#   Authorization: Bearer {mrpeasy_api_key}
# Body:
# {
#   "article_id": "{{internal_sku}}",
#   "quantity": {{quantity}},
#   "deadline": "{{due_date}}",
#   "bom_id": "{{bom_id}}",
#   "customer_order_number": "{{po_number}}",
#   "notes": "Auto-generated from PO #{{po_number}} | Customer: {{customer_name}} | Approved by: {{approver_name}} at {{approval_timestamp}}",
#   "priority": "{{priority}}"
# }

# ============================================================
# Odoo XML-RPC - Create Manufacturing Order
# ============================================================
# Python snippet (for n8n Code node or standalone script):
import xmlrpc.client
url = 'https://clientname.odoo.com'
db = 'clientname'
uid = 2  # admin user ID
password = 'api_key_here'
models = xmlrpc.client.ServerProxy(f'{url}/xmlrpc/2/object')

# Create manufacturing order
mo_id = models.execute_kw(db, uid, password, 'mrp.production', 'create', [{
    'product_id': internal_product_id,
    'product_qty': quantity,
    'bom_id': bom_id,
    'date_planned_start': due_date,
    'origin': f'PO #{po_number}',
    'company_id': 1,
}])
print(f'Created Manufacturing Order ID: {mo_id}')

# ============================================================
# Fishbowl REST API - Create Work Order  
# ============================================================
# Make.com HTTP Module:
# Method: POST
# URL: https://{fishbowl_server}:443/api/work-orders
# Headers:
#   Content-Type: application/json
#   Authorization: Bearer {fishbowl_token}
# Body:
# {
#   "partNumber": "{{internal_sku}}",
#   "quantityToManufacture": {{quantity}},
#   "dueDate": "{{due_date}}",
#   "customerPO": "{{po_number}}",
#   "note": "Auto-generated from PO #{{po_number}}"
# }

az storage table create --name POWOAuditLog --account-name clientstorageacct
az storage entity insert --table-name POWOAuditLog --entity PartitionKey=2025-01 RowKey={guid} ...

{
  "name": "PO-Email-Intake-Monitor",
  "scheduling": {"type": "interval", "interval": 5},
  "modules": [
    {
      "id": 1,
      "module": "microsoft365:watchEmails",
      "parameters": {
        "mailbox": "po@clientdomain.com",
        "folder": "Inbox",
        "hasAttachments": true,
        "markAsRead": true,
        "limit": 10
      }
    },
    {
      "id": 2,
      "module": "builtin:filter",
      "parameters": {
        "condition": "{{1.subject}} does not contain 'Auto-Reply' AND {{1.subject}} does not contain 'Out of Office' AND {{1.from.address}} is not in ['noreply@', 'mailer-daemon@']"
      }
    },
    {
      "id": 3,
      "module": "builtin:iterator",
      "parameters": {
        "array": "{{1.attachments}}"
      }
    },
    {
      "id": 4,
      "module": "builtin:filter",
      "parameters": {
        "condition": "{{3.filename}} matches '*.pdf' OR {{3.filename}} matches '*.PDF' OR {{3.filename}} matches '*.tif' OR {{3.filename}} matches '*.tiff' OR {{3.filename}} matches '*.png'"
      }
    },
    {
      "id": 5,
      "module": "microsoft365:getAttachmentContent",
      "parameters": {
        "messageId": "{{1.id}}",
        "attachmentId": "{{3.id}}"
      }
    },
    {
      "id": 6,
      "module": "builtin:setVariable",
      "parameters": {
        "variables": [
          {"name": "source_type", "value": "email"},
          {"name": "source_email_from", "value": "{{1.from.address}}"},
          {"name": "source_email_subject", "value": "{{1.subject}}"},
          {"name": "source_email_date", "value": "{{1.receivedDateTime}}"},
          {"name": "original_filename", "value": "{{3.filename}}"},
          {"name": "file_content", "value": "{{5.data}}"}
        ]
      }
    },
    {
      "id": 7,
      "module": "webhook:trigger",
      "mapper": "call PO-OCR-Extraction scenario"
    }
  ]
}

{
  "name": "PO-Scan-Folder-Monitor",
  "scheduling": {"type": "interval", "interval": 5},
  "modules": [
    {
      "id": 1,
      "module": "onedrive:watchFiles",
      "parameters": {
        "folder": "/PurchaseOrders/Incoming/",
        "fileTypes": ["pdf", "tif", "tiff", "png"]
      }
    },
    {
      "id": 2,
      "module": "onedrive:downloadFile",
      "parameters": {
        "fileId": "{{1.id}}"
      }
    },
    {
      "id": 3,
      "module": "onedrive:moveFile",
      "parameters": {
        "fileId": "{{1.id}}",
        "destination": "/PurchaseOrders/Processing/"
      }
    },
    {
      "id": 4,
      "module": "builtin:setVariable",
      "parameters": {
        "variables": [
          {"name": "source_type", "value": "scan"},
          {"name": "original_filename", "value": "{{1.name}}"},
          {"name": "file_content", "value": "{{2.data}}"}
        ]
      }
    },
    {
      "id": 5,
      "module": "webhook:trigger",
      "mapper": "call PO-OCR-Extraction scenario"
    }
  ]
}

## Make.com Scenario Module Sequence: PO-OCR-Extraction

### Module Sequence
Webhook Trigger (from intake) 
  → POST to Azure Analyze 
  → Sleep 8s 
  → GET Result (with retry loop) 
  → Parse JSON 
  → Normalize Fields 
  → Output to Mapping Scenario

### Module 1: HTTP - Submit Document for Analysis
Method: POST
URL: {{AZURE_ENDPOINT}}/formrecognizer/documentModels/prebuilt-invoice:analyze?api-version=2023-07-31
Headers:
  Ocp-Apim-Subscription-Key: {{AZURE_KEY}}
  Content-Type: application/pdf
Body Type: Binary
Body: {{webhook.file_content}}
Parse Response: Yes
Advanced:
  Timeout: 30 seconds
  Follow Redirect: Yes

### Module 2: Tools - Sleep
Delay: 8 seconds

### Module 3: HTTP - Poll for Results (with Repeater)
Method: GET
URL: {{1.headers.operation-location}}
Headers:
  Ocp-Apim-Subscription-Key: {{AZURE_KEY}}
Parse Response: Yes

Error Handler: 
  - If status != 'succeeded': Retry (max 5, interval 5s)
  - If status == 'failed': Break + notify MSP

// Field Extraction Mapping
// Azure prebuilt-invoice field -> Internal field name

const result = analyzeResult.documents[0].fields;

const extractedPO = {
  // Header fields
  po_number: result.InvoiceId?.content || result.PurchaseOrder?.content || 'UNKNOWN',
  po_date: result.InvoiceDate?.content || result.InvoiceDate?.valueDate || null,
  due_date: result.DueDate?.content || result.DueDate?.valueDate || null,
  
  // Customer information (note: on a PO, the 'Vendor' in invoice model = the customer)
  customer_name: result.VendorName?.content || result.CustomerName?.content || '',
  customer_address: result.VendorAddress?.content || '',
  customer_id: result.VendorAddressRecipient?.content || result.CustomerId?.content || '',
  
  // Ship-to information
  ship_to_name: result.ShippingAddressRecipient?.content || '',
  ship_to_address: result.ShippingAddress?.content || '',
  
  // Financial
  subtotal: result.SubTotal?.valueCurrency?.amount || 0,
  total: result.InvoiceTotal?.valueCurrency?.amount || 0,
  currency: result.InvoiceTotal?.valueCurrency?.currencyCode || 'USD',
  
  // Payment terms
  payment_terms: result.PaymentTerm?.content || '',
  
  // Line items
  line_items: (result.Items?.valueArray || []).map((item, index) => ({
    line_number: index + 1,
    description: item.valueObject?.Description?.content || '',
    product_code: item.valueObject?.ProductCode?.content || '',
    quantity: item.valueObject?.Quantity?.valueNumber || 0,
    unit: item.valueObject?.Unit?.content || 'EA',
    unit_price: item.valueObject?.UnitPrice?.valueCurrency?.amount || 0,
    amount: item.valueObject?.Amount?.valueCurrency?.amount || 0,
    date: item.valueObject?.Date?.content || null
  })),
  
  // Confidence scores
  confidence: {
    overall: analyzeResult.documents[0].confidence,
    po_number: result.InvoiceId?.confidence || 0,
    customer_name: result.VendorName?.confidence || 0
  },
  
  // Metadata
  page_count: analyzeResult.pages?.length || 1,
  extraction_timestamp: new Date().toISOString(),
  source_type: webhook.source_type,
  original_filename: webhook.original_filename
};

return extractedPO;

### Module 5: Filter - Confidence Check
Condition: extractedPO.confidence.overall >= 0.70
  AND extractedPO.po_number != 'UNKNOWN'
  AND extractedPO.line_items.length > 0

If PASS: Continue to mapping scenario
If FAIL: Route to manual review queue with low-confidence alert

{{AZURE_ENDPOINT}}/formrecognizer/documentModels/{{CUSTOM_MODEL_ID}}:analyze?api-version=2023-07-31

Columns:
- ID (auto, SharePoint built-in)
- CustomerName (Single line text, indexed)
- CustomerPartNumber (Single line text, indexed)
- CustomerPartVariants (Multi-line text) // JSON array of known variations
- InternalSKU (Single line text)
- ProductName (Single line text)
- BOM_ID (Single line text)
- DefaultUOM (Choice: EA, LB, FT, KG, GAL, BOX, SET)
- UOMConversionFactor (Number, default 1) // e.g., customer orders in DOZ, internal is EA, factor = 12
- DefaultLeadTimeDays (Number)
- IsActive (Yes/No, default Yes)
- LastUsedDate (DateTime)
- CreatedBy (Person)
- Notes (Multi-line text)

For each line_item in extractedPO.line_items:

  Module A: SharePoint > Search List Items
    List: PO_SKU_Crosswalk
    Filter: 
      CustomerName = {{extractedPO.customer_name}}
      AND CustomerPartNumber = {{line_item.product_code}}
      AND IsActive = Yes
    Limit: 1

Branch 1 (Exact Match Found):
      Set mapped_sku = {{A.InternalSKU}}
      Set mapped_bom = {{A.BOM_ID}}
      Set mapped_qty = {{line_item.quantity}} * {{A.UOMConversionFactor}}
      Set mapping_confidence = 1.0
      Set mapping_method = 'exact'
      
      // Update LastUsedDate
      SharePoint > Update List Item
        ID: {{A.ID}}
        LastUsedDate: now()

# Router Branch 2 Setup (No Exact Match, Try Fuzzy)

Branch 2 (No Exact Match - Try Fuzzy):
      // Normalize the customer part number
      Set normalized_part = UPPER(REPLACE(REPLACE(REPLACE(
        {{line_item.product_code}}, '-', ''), ' ', ''), '.', ''))
      
      // Search with normalized comparison
      SharePoint > Search List Items
        Filter: CustomerName = {{extractedPO.customer_name}}
                AND IsActive = Yes
        Limit: 100  // get all for this customer

const normalizePartNum = (pn) => 
  pn.toUpperCase().replace(/[-\s.\/]/g, '').replace(/^0+/, '');

const searchNormalized = normalizePartNum(line_item.product_code);

// Also check description-based matching
const searchDesc = line_item.description.toLowerCase();

let bestMatch = null;
let bestScore = 0;

for (const row of crosswalkRows) {
  // Check primary part number
  const rowNormalized = normalizePartNum(row.CustomerPartNumber);
  if (rowNormalized === searchNormalized) {
    bestMatch = row;
    bestScore = 0.95; // normalized exact match
    break;
  }
  
  // Check variant list
  if (row.CustomerPartVariants) {
    const variants = JSON.parse(row.CustomerPartVariants);
    for (const v of variants) {
      if (normalizePartNum(v) === searchNormalized) {
        bestMatch = row;
        bestScore = 0.90;
        break;
      }
    }
  }
  
  // Check if part number is contained in description
  if (searchDesc.includes(row.InternalSKU.toLowerCase()) ||
      searchDesc.includes(row.ProductName.toLowerCase())) {
    if (bestScore < 0.70) {
      bestMatch = row;
      bestScore = 0.70;
    }
  }
}

return { bestMatch, bestScore };

# Router Branch 3 (No Match, Manual Review via Teams Adaptive Card)

Branch 3 (No Match Found - Manual Review):
      // Create Teams adaptive card for manual mapping
      Teams > Send Adaptive Card to Production Manager:
        Title: 'New Part Number Mapping Required'
        Body: 
          PO: {{extractedPO.po_number}}
          Customer: {{extractedPO.customer_name}}
          Customer Part#: {{line_item.product_code}}
          Description: {{line_item.description}}
          Qty: {{line_item.quantity}}
          
          [Dropdown: Select Internal SKU from ERP item list]
          [Submit: Save Mapping]
      
      // On response: Create new crosswalk entry
      SharePoint > Create List Item in PO_SKU_Crosswalk:
        CustomerName: {{extractedPO.customer_name}}
        CustomerPartNumber: {{line_item.product_code}}
        InternalSKU: {{teams_response.selected_sku}}
        BOM_ID: (lookup from ERP)
        CreatedBy: {{teams_response.responder}}

import pandas as pd

# Export from ERP: past 12 months of sales orders with customer PO references
history = pd.read_csv('sales_order_history.csv')

# Group by customer + their part number, find the internal SKU used
crosswalk = history.groupby(['customer_name', 'customer_part_number']).agg({
    'internal_sku': 'first',
    'product_name': 'first',
    'bom_id': 'first',
    'uom': 'first',
    'order_date': 'max'  # most recent usage
}).reset_index()

crosswalk.to_csv('initial_crosswalk.csv', index=False)
print(f'Generated {len(crosswalk)} crosswalk mappings')
# Import this CSV into the SharePoint list

// Make.com Code Module (JavaScript)

// Input: validated PO with mapped SKUs
// Output: Array of WO payloads ready for ERP API

function buildWorkOrderPayloads(validatedPO, erpType) {
  const workOrders = [];
  
  for (const item of validatedPO.line_items) {
    if (!item.mapped_sku || item.mapping_confidence < 0.85) {
      continue; // Skip unmapped items (already flagged for review)
    }
    
    // Calculate dates
    const poDate = new Date(validatedPO.po_date);
    const requestedDate = validatedPO.due_date 
      ? new Date(validatedPO.due_date) 
      : new Date(poDate.getTime() + (item.default_lead_time_days || 14) * 86400000);
    
    // Determine priority
    let priority = 'normal';
    const poText = (validatedPO.payment_terms + ' ' + (validatedPO.notes || '')).toUpperCase();
    if (poText.includes('RUSH') || poText.includes('EXPEDITE') || poText.includes('HOT')) {
      priority = 'high';
    } else if (poText.includes('BLANKET') || poText.includes('SCHEDULE')) {
      priority = 'low';
    }
    
    // Calculate quantity with UOM conversion
    const quantity = item.quantity * (item.uom_conversion_factor || 1);
    
    // Build notes/reference string
    const notes = [
      `Auto-generated from PO #${validatedPO.po_number}`,
      `Customer: ${validatedPO.customer_name}`,
      `Customer Part#: ${item.original_product_code}`,
      `PO Line: ${item.line_number}`,
      `Approved by: ${validatedPO.approver_name} at ${validatedPO.approval_timestamp}`,
      item.notes ? `Special Instructions: ${item.notes}` : ''
    ].filter(Boolean).join(' | ');
    
    // Build ERP-specific payload
    let payload;
    
    switch (erpType) {
      case 'mrpeasy':
        payload = {
          endpoint: 'POST /v1/manufacturing-orders',
          body: {
            article_id: item.mapped_sku,
            quantity: quantity,
            deadline: requestedDate.toISOString().split('T')[0],
            bom_id: item.mapped_bom_id,
            customer_order_number: validatedPO.po_number,
            notes: notes,
            priority: priority === 'high' ? 1 : (priority === 'low' ? 3 : 2),
            status: 'planned' // Never auto-release
          }
        };
        break;
        
      case 'odoo':
        payload = {
          endpoint: 'POST /api/v1/mrp.production',
          body: {
            product_id: parseInt(item.mapped_sku),
            product_qty: quantity,
            bom_id: parseInt(item.mapped_bom_id),
            date_planned_start: requestedDate.toISOString(),
            origin: `PO #${validatedPO.po_number}`,
            priority: priority === 'high' ? '1' : (priority === 'low' ? '0' : '0'),
            state: 'confirmed', // Odoo: draft -> confirmed -> planned -> progress -> done
            company_id: 1
          }
        };
        break;
        
      case 'fishbowl':
        payload = {
          endpoint: 'POST /api/work-orders',
          body: {
            partNumber: item.mapped_sku,
            quantityToManufacture: quantity,
            dueDate: requestedDate.toISOString().split('T')[0],
            customerPO: validatedPO.po_number,
            note: notes,
            priority: priority === 'high' ? 5 : (priority === 'low' ? 1 : 3),
            statusId: 10 // Issued/Planned
          }
        };
        break;
        
      case 'epicor':
        payload = {
          endpoint: 'POST /api/v2/odata/Erp.BO.JobEntrySvc/Jobs',
          body: {
            PartNum: item.mapped_sku,
            ProdQty: quantity,
            ReqDueDate: requestedDate.toISOString().split('T')[0],
            PONum: validatedPO.po_number,
            CommentText: notes,
            JobFirm: false, // Unfirm initially
            JobEngineered: true,
            JobReleased: false
          }
        };
        break;
        
      default:
        throw new Error(`Unsupported ERP type: ${erpType}`);
    }
    
    workOrders.push({
      ...payload,
      _meta: {
        po_number: validatedPO.po_number,
        po_line: item.line_number,
        customer: validatedPO.customer_name,
        customer_part: item.original_product_code,
        internal_sku: item.mapped_sku,
        quantity: quantity,
        priority: priority,
        requested_date: requestedDate.toISOString()
      }
    });
  }
  
  return workOrders;
}

// Execute
const erpType = 'mrpeasy'; // Configure per client
const payloads = buildWorkOrderPayloads(validatedPO, erpType);
return payloads;

{
  "listName": "PO_WO_Audit_Log",
  "columns": [
    {"name": "RunID", "type": "SingleLineText", "indexed": true},
    {"name": "Timestamp", "type": "DateTime", "indexed": true},
    {"name": "Stage", "type": "Choice", "choices": ["Received", "OCR_Complete", "Mapped", "Validation_Pass", "Validation_Fail", "Approval_Sent", "Approved", "Rejected", "WO_Created", "WO_Failed", "Error"]},
    {"name": "PONumber", "type": "SingleLineText", "indexed": true},
    {"name": "POFileName", "type": "SingleLineText"},
    {"name": "POArchiveLink", "type": "Hyperlink"},
    {"name": "CustomerName", "type": "SingleLineText", "indexed": true},
    {"name": "SourceType", "type": "Choice", "choices": ["Email", "Scan", "EDI", "Portal"]},
    {"name": "ExtractedDataJSON", "type": "MultiLineText"},
    {"name": "MappedDataJSON", "type": "MultiLineText"},
    {"name": "OCRConfidence", "type": "Number"},
    {"name": "MappingConfidence", "type": "Number"},
    {"name": "LineItemCount", "type": "Number"},
    {"name": "MappedItemCount", "type": "Number"},
    {"name": "UnmappedItemCount", "type": "Number"},
    {"name": "ValidationErrors", "type": "MultiLineText"},
    {"name": "ApproverName", "type": "SingleLineText"},
    {"name": "ApprovalTimestamp", "type": "DateTime"},
    {"name": "ApprovalDecision", "type": "Choice", "choices": ["Approved", "Rejected", "Edited", "Auto-Approved", "Pending"]},
    {"name": "CreatedWOIDs", "type": "MultiLineText"},
    {"name": "ERPResponse", "type": "MultiLineText"},
    {"name": "ProcessingTimeMs", "type": "Number"},
    {"name": "ErrorDetails", "type": "MultiLineText"},
    {"name": "MSPTechNotes", "type": "MultiLineText"}
  ],
  "settings": {
    "versioningEnabled": true,
    "majorVersionLimit": 500,
    "enableAttachments": false,
    "noCrawl": false
  }
}

Connect-PnPOnline -Url 'https://clientname.sharepoint.com/sites/Manufacturing' -Interactive

# Create retention label
$label = New-PnPRetentionLabel -Name 'PO-WO-Audit-7Year' `
  -RetentionDuration 2555 `
  -RetentionAction Keep `
  -IsRecordLabel $true

# Apply to list
Set-PnPList -Identity 'PO_WO_Audit_Log' -DefaultLabel 'PO-WO-Audit-7Year'

## Make.com Scenario: PO-WO-Daily-HealthCheck

### Scenario Configuration
Name: PO-WO-Daily-HealthCheck
Schedule: Daily at 7:00 AM (client local time)
Timeout: 5 minutes

Module 1: Tools > Set Multiple Variables
  - check_date: {{formatDate(addDays(now; -1); 'YYYY-MM-DD')}}
  - check_start: {{check_date}}T00:00:00Z
  - check_end: {{check_date}}T23:59:59Z
  - alerts: [] (empty array)

Module 2: SharePoint > Get List Items (Audit Log)
  List: PO_WO_Audit_Log
  Filter: Timestamp >= {{check_start}} AND Timestamp <= {{check_end}}
  Limit: 500

const entries = items; // from Module 2
const alerts = [];

// Count by stage
const received = entries.filter(e => e.Stage === 'Received').length;
const ocrDone = entries.filter(e => e.Stage === 'OCR_Complete').length;
const mapped = entries.filter(e => e.Stage === 'Mapped').length;
const approved = entries.filter(e => e.Stage === 'Approved').length;
const created = entries.filter(e => e.Stage === 'WO_Created').length;
const errors = entries.filter(e => e.Stage === 'Error' || e.Stage === 'WO_Failed');
const pending = entries.filter(e => e.Stage === 'Approval_Sent' && 
  !entries.some(e2 => e2.RunID === e.RunID && ['Approved','Rejected'].includes(e2.Stage)));

// Alert: POs received but no WO created (dropped POs)
const receivedRunIDs = new Set(entries.filter(e => e.Stage === 'Received').map(e => e.RunID));
const createdRunIDs = new Set(entries.filter(e => e.Stage === 'WO_Created').map(e => e.RunID));
const droppedIDs = [...receivedRunIDs].filter(id => !createdRunIDs.has(id));
const droppedNonPending = droppedIDs.filter(id => 
  !pending.some(p => p.RunID === id));

if (droppedNonPending.length > 0) {
  alerts.push({
    severity: 'HIGH',
    message: `${droppedNonPending.length} PO(s) received but no WO created (not pending approval). Run IDs: ${droppedNonPending.join(', ')}`
  });
}

// Alert: Pending approvals older than 8 hours
if (pending.length > 0) {
  alerts.push({
    severity: 'MEDIUM',
    message: `${pending.length} PO(s) awaiting approval for 8+ hours. PO numbers: ${pending.map(p => p.PONumber).join(', ')}`
  });
}

// Alert: Error rate above threshold
if (received > 0 && errors.length / received > 0.1) {
  alerts.push({
    severity: 'HIGH',
    message: `Error rate is ${((errors.length/received)*100).toFixed(1)}% (${errors.length}/${received}). Review error details.`
  });
}

// Alert: Zero POs (might mean intake is broken)
const dayOfWeek = new Date(check_date).getDay();
if (received === 0 && dayOfWeek >= 1 && dayOfWeek <= 5) {
  alerts.push({
    severity: 'MEDIUM',
    message: 'No POs received on a weekday. Verify email intake is functioning.'
  });
}

// Calculate average processing time
const completedRuns = entries.filter(e => e.Stage === 'WO_Created' && e.ProcessingTimeMs);
const avgTime = completedRuns.length > 0 
  ? (completedRuns.reduce((sum, e) => sum + e.ProcessingTimeMs, 0) / completedRuns.length / 1000).toFixed(1)
  : 'N/A';

return {
  summary: {
    date: check_date,
    pos_received: received,
    ocr_completed: ocrDone,
    successfully_mapped: mapped,
    approved: approved,
    wos_created: created,
    errors: errors.length,
    pending_approval: pending.length,
    avg_processing_time_sec: avgTime,
    success_rate: received > 0 ? ((created/received)*100).toFixed(1) + '%' : 'N/A'
  },
  alerts: alerts,
  error_details: errors.map(e => ({runId: e.RunID, po: e.PONumber, error: e.ErrorDetails}))
};

Module 4: HTTP > Test Azure Doc Intelligence API
  Method: GET
  URL: {{AZURE_ENDPOINT}}/formrecognizer/info?api-version=2023-07-31
  Headers: Ocp-Apim-Subscription-Key: {{AZURE_KEY}}
  Expected: HTTP 200
  On failure: Add to alerts array

Module 5: HTTP > Test ERP API Connectivity
  Method: GET  
  URL: (ERP health endpoint, e.g., MRPeasy: GET /v1/articles?limit=1)
  Headers: Authorization: Bearer {{ERP_API_KEY}}
  Expected: HTTP 200
  On failure: Add to alerts array

Module 6: Router
  Branch A (alerts.length > 0 AND any HIGH severity):
    Email > Send to MSP alerts + Client production manager
    Subject: '[ACTION REQUIRED] PO Automation Alert - {{client_name}} - {{check_date}}'
    
  Branch B (alerts.length > 0, MEDIUM only):
    Email > Send to MSP monitoring only
    Subject: '[INFO] PO Automation Notice - {{client_name}} - {{check_date}}'
    
  Branch C (No alerts - all clear):
    Email > Send daily summary to Client production manager
    Subject: 'Daily PO Automation Summary - {{check_date}} - {{summary.pos_received}} POs / {{summary.wos_created}} WOs'

Module 7: SharePoint > Create List Item
  List: HealthCheck_History
  Fields: Date, Summary JSON, Alerts JSON, API_Status

<h2>PO Automation Daily Report — {{check_date}}</h2>
<table border='1' cellpadding='8'>
  <tr><td><b>POs Received</b></td><td>{{summary.pos_received}}</td></tr>
  <tr><td><b>WOs Created</b></td><td>{{summary.wos_created}}</td></tr>
  <tr><td><b>Success Rate</b></td><td>{{summary.success_rate}}</td></tr>
  <tr><td><b>Avg Processing Time</b></td><td>{{summary.avg_processing_time_sec}}s</td></tr>
  <tr><td><b>Pending Approvals</b></td><td>{{summary.pending_approval}}</td></tr>
  <tr><td><b>Errors</b></td><td>{{summary.errors}}</td></tr>
</table>
{{#if alerts}}
<h3 style='color:red'>⚠️ Alerts</h3>
<ul>
{{#each alerts}}<li><b>[{{severity}}]</b> {{message}}</li>{{/each}}
</ul>
{{/if}}