A complete, self-contained CREATE TABLE + INSERT script for standing up the entire mail approve/reject pipeline on a brand-new, empty client database that has none of it yet — modeled 1:1 on the Leave workflow, after its own bugs were found and fixed on the live reference server.
Nothing about this feature. The only tables assumed to already exist are the absolute platform bootstrap layer — the handful every single GB5 module depends on, regardless of feature: MCLIENT (tenant), MUSER (users), MROLE / MUSERGROUP (permissions), MORGANIZATIONUNIT (org structure), MENTITY (the entity registry), and MAUTONUMBER (the global id generator). It also assumes your target entity (e.g. TLeave) is already registered in MENTITY, since that happens as part of that module's own deployment, not here. Every other table — all 15 of them — is created by Part A below.
Everything below is written against Leave. To reuse it for a different document type, search the script for @@ and replace each of these seven placeholders.
| Placeholder | What it means | Leave's real value |
|---|---|---|
| @@ENTITY_NAME@@ | Human name for the workflow/mails, e.g. "Expense" or "Purchase Order" | Leave |
| @@ENTITY_TABLE@@ | The entity's table/DTO name prefix | TLeave |
| @@ENTITY_ID@@ | MENTITY.ENTITYID for the target table — look it up first | -1399999827 |
| @@APPROVER_FIELD@@ | The field naming the approver, resolved by a DB_ENRICH qualifier before the workflow starts | ReportingToEmployeeId |
| @@CONTEXT_ID_FIELD@@ | The field holding the workflow task id | WorkflowTaskId |
| @@ASSIGNEE_FIELD@@ | The field holding the assignee's user id | AssignedToUserId |
| @@MAIL_FIELD@@ | The field the recipient's email address is read from | MailId |
| @@SAVE_ENDPOINT@@ | The entity's real save/action API, called when a button is clicked | /WorkFlow/WorkFlowActions |
Each is wrapped in IF OBJECT_ID(...) IS NULL, so this is safe to run even if some or all of them already exist. No placeholders in this part — it's identical for every entity. Foreign keys to tables outside this feature's scope (report/file/menu/job tables etc.) are deliberately not enforced, matching what the live reference database itself does.
One continuous script, in dependency order — replace the @@...@@ placeholders first, then run top to bottom in a single connection (the steps chain together through T-SQL variables, so they can't be split across separate query windows).
Run this last. Expect exactly 4 rows back — one per event (the "please review" trigger, Approved, Rejected, Returned) — each pointing at the right mail template with the right recipient.
Every column, type, default, primary key, foreign key, and check constraint for all 15 tables was read directly from unisoftgb4's system catalogs (sys.columns, sys.foreign_keys, sys.check_constraints) — not copied from repository migration files, several of which were found to be stale or incomplete.
Part A was executed for real against live unisoftgb4 — in an isolated schema (zzstagetest), not dbo, so nothing touched the real reference data — with every foreign key pointing at the real MCLIENT/MUSER/MENTITY/MORGANIZATIONUNIT rows and every internal FK chained between the 15 new tables. All 15 created without a single error; verified via sys.tables; then every constraint and table was dropped, leaving the database exactly as it was before.
Every NOT-NULL-with-no-default column across all 11 base-schema seed tables was enumerated and checked against this script's INSERT statements — this caught two real missing columns (MMAILTEMPLATE.ATTACHMENTFILEID/BODYFILEID, MEVENTTYPEACTION.TRIGGERREPORTID) that would otherwise have failed on first run.
The full seed script (Part B), with placeholders substituted for Leave, was run against live unisoftgb4 wrapped in BEGIN TRANSACTION / ROLLBACK — proving every statement actually executes without error, with nothing permanently written. This first run caught the MAUTONUMBER drift described above; the script was fixed and re-run clean.