Check a price against the price list

You have agreed fixed prices for supplies with your vendors. The price list is in a data table preisliste with the item number as its key column, uploaded from Excel or CSV. During the review, purchasing enters the invoice’s item number and unit price, two fields the workflow declares itself. Then the script compares the unit price with the list price: when it is at most 2 % above it, the invoice goes to the normal approval; when it is higher or the item is missing from the list, the management approves, with a hint on the deviation.

The workflow

The workflow starts for every document of the type incoming-invoice. Its file workflow.yaml:

$schema: 'https://docs.acriva.de/schemas/workflow.schema.json'
format: 1
schemaVersion: 4
sdk: '1.0.0'
key: price-list-check
name: Preis gegen die Preisliste prüfen
template: custom
trigger:
  documentType: incoming-invoice
fields:
  - key: itemNumber
    kind: text
    labelDe: Artikelnummer
    labelEn: Item number
    documentTypes:
      - incoming-invoice
  - key: unitPrice
    kind: money
    labelDe: Einzelpreis
    labelEn: Unit price
    documentTypes:
      - incoming-invoice
start: review
steps:
  - kind: humanTask
    name: review
    label: Sachliche Prüfung
    taskKind: review
    assignees:
      - when: null
        assignee:
          kind: member
          member: einkauf@example.com
    fallback:
      kind: role
      role: Buchhaltung
    options:
      excludeRequester: false
      excludeActorsOfSteps: []
      pickFile: false
    next: checkPrice
    onRejected: rejected
  - kind: script
    name: checkPrice
    label: Preis prüfen
    script: check-price.ts
    hook: onApproved
    inputs:
      - kind: field
        key: itemNumber
        type: text
      - kind: field
        key: unitPrice
        type: money
      - kind: table
        key: preisliste
    returns:
      - key: price
        type: enumeration
        values:
          - listPrice
          - deviation
    branch: price
    exits:
      - value: listPrice
        next: approve
      - value: deviation
        next: managementApproval
    next: approve
  - kind: humanTask
    name: approve
    label: Freigabe
    taskKind: approve
    assignees:
      - when: null
        assignee:
          kind: costCenterResponsible
    fallback:
      kind: role
      role: Buchhaltung
    options:
      excludeRequester: true
      excludeActorsOfSteps: []
      pickFile: false
    next: approved
    onRejected: rejected
  - kind: humanTask
    name: managementApproval
    label: Freigabe Geschäftsführung
    taskKind: approve
    assignees:
      - when: null
        assignee:
          kind: role
          role: Geschäftsführung
    fallback:
      kind: role
      role: Buchhaltung
    options:
      excludeRequester: true
      excludeActorsOfSteps: []
      pickFile: false
    next: approved
    onRejected: rejected
  - kind: end
    name: approved
    label: Freigegeben
    outcome: approved
  - kind: end
    name: rejected
    label: Zurückgewiesen
    outcome: rejected

The script check-price.ts

Hook: onApproved · Inputs: itemNumber, unitPrice, data table preisliste · Return values: price (listPrice, deviation)

import { defineScript, money } from '@acriva/workflow-sdk';

/** How far a unit price may lie above the list price, as a share of it. */
const TOLERANCE = 0.02;

export default defineScript({
  hook: 'onApproved',
  outputs: { price: ['listPrice', 'deviation'] },
  async run(ctx) {
    const { itemNumber, unitPrice } = ctx.document.fields;
    const number = itemNumber?.trim();
    if (!number || unitPrice == null) {
      ctx.hint('Artikelnummer oder Einzelpreis fehlt, der Preis ist nicht geprüft.');
      return ctx.branch('deviation');
    }
    const item = await ctx.tables.lookup('preisliste', number);
    if (item === null || item.preis === null) {
      ctx.hint(`Der Artikel ${number} steht nicht in der Preisliste.`);
      return ctx.branch('deviation');
    }
    if (unitPrice > item.preis * (1 + TOLERANCE)) {
      ctx.hint(
        `${item.bezeichnung ?? number}: berechnet ${money.format(unitPrice)}, laut Preisliste ${money.format(item.preis)}.`,
      );
      return ctx.branch('deviation');
    }
    return ctx.branch('listPrice');
  },
});

What it does

A test runs each of these cases in the sandbox and compares the result.

A price above the price list

• Input: itemNumber TON-305A, unitPrice 99.5, last task outcome Approved
• Data table preisliste: 2 rows
  • artikelnummer PAP-A4-80, bezeichnung Kopierpapier A4, 80 g, 500 Blatt, preis 4.95
  • artikelnummer TON-305A, bezeichnung Toner 305A schwarz, preis 89.9
• Exit: deviation
• Changes: hint on the next task: “Toner 305A schwarz: berechnet 99,50 €, laut Preisliste 89,90 €.”

A price from the price list

• Input: itemNumber PAP-A4-80, unitPrice 4.95, last task outcome Approved
• Data table preisliste: 2 rows
  • artikelnummer PAP-A4-80, bezeichnung Kopierpapier A4, 80 g, 500 Blatt, preis 4.95
  • artikelnummer TON-305A, bezeichnung Toner 305A schwarz, preis 89.9
• Exit: listPrice
• Changes: none

An item that isn’t on the price list

• Input: itemNumber TON-410X, unitPrice 129, last task outcome Approved
• Data table preisliste: 2 rows
  • artikelnummer PAP-A4-80, bezeichnung Kopierpapier A4, 80 g, 500 Blatt, preis 4.95
  • artikelnummer TON-305A, bezeichnung Toner 305A schwarz, preis 89.9
• Exit: deviation
• Changes: hint on the next task: “Der Artikel TON-410X steht nicht in der Preisliste.”