Inventory & Warehouse — Data model

12 tables · auto-generated from migrations-v2/*.sql — do not edit by hand.

ER diagram

%%{init: {"theme":"base","themeVariables":{"darkMode":true,"background":"#1e1e1e","primaryColor":"#2d2d30","primaryBorderColor":"#569cd6","primaryTextColor":"#d4d4d4","lineColor":"#6796c6","secondaryColor":"#3a3d41","secondaryBorderColor":"#dcdcaa","secondaryTextColor":"#d4d4d4","tertiaryColor":"#252526","tertiaryBorderColor":"#569cd6","noteBkgColor":"#3a3d41","noteTextColor":"#dcdcaa","clusterBkg":"#252526","clusterBorder":"#569cd6","titleColor":"#569cd6","attributeBackgroundColorOdd":"#252526","attributeBackgroundColorEven":"#2d2d30"},"er":{"useMaxWidth":false}}}%%
erDiagram
  INVENTORY_ADJUSTMENT_REASON {
    integer id PK
    text company_id FK
    text code
    text name
    text debit_transaction_kind
    text credit_transaction_kind
    integer require_note
    integer active
  }
  INVENTORY_CYCLE_COUNT {
    integer id PK
    text company_id FK
    integer location_id FK
    integer adjustment_document_id FK
    text count_no
    text state
    integer snapshot_at
    text counted_by
  }
  INVENTORY_CYCLE_COUNT_LINE {
    integer id PK
    text company_id FK
    integer cycle_count_id FK
    integer item_variant_id FK
    integer location_id FK
    integer line_no
    text lot_code
    text serial_code
  }
  INVENTORY_PICK_LIST {
    integer id PK
    text company_id FK
    integer item_variant_id FK
    integer warehouse_location_id FK
    integer staging_location_id FK
    integer removal_policy_id FK
    integer posted_document_id FK
    text pick_no
  }
  INVENTORY_PICK_LIST_LINE {
    integer id PK
    text company_id FK
    integer pick_list_id FK
    integer item_variant_id FK
    integer source_location_id FK
    integer staging_location_id FK
    integer reservation_id FK
    integer line_no
  }
  INVENTORY_PICK_TICKET {
    integer id PK
    text company_id FK
    integer item_variant_id FK
    integer location_id FK
    text source_ref
    numeric requested_qty
    text status
    text allocation_payload
  }
  INVENTORY_PUTAWAY_RULE {
    integer id PK
    text company_id FK
    integer item_variant_id FK
    integer item_category_id FK
    integer warehouse_location_id FK
    integer source_location_id FK
    integer target_location_id FK
    integer target_zone_id FK
  }
  INVENTORY_REMOVAL_POLICY {
    integer id PK
    text company_id FK
    integer item_variant_id FK
    integer location_id FK
    text scope_type
    text strategy
    integer exclude_expired
    text quality_state
  }
  INVENTORY_REPOST_REQUEST {
    integer id PK
    text company_id FK
    integer item_variant_id FK
    integer location_id FK
    text trigger_kind
    text anchor_date
    text status
    text progress_cursor
  }
  INVENTORY_RESERVATION {
    integer id PK
    text company_id FK
    integer item_variant_id FK
    integer location_id FK
    integer owner_party_id FK
    text lot_code
    text serial_code
    text package_id
  }
  STOCK_BALANCE {
    text company_id FK
    integer item_variant_id FK
    integer location_id FK
    integer identity_bundle_id
    text lot_code
    text serial_code
    integer owner_party_id
    text package_id
  }
  STOCK_LEDGER_ENTRY {
    integer id PK
    text company_id FK
    integer item_variant_id FK
    integer location_id FK
    integer base_uom_id FK
    integer original_uom_id FK
    integer identity_bundle_id FK
    integer owner_party_id FK
  }
  INVENTORY_CYCLE_COUNT ||--o{ INVENTORY_CYCLE_COUNT_LINE : cycle_count
  INVENTORY_REMOVAL_POLICY ||--o{ INVENTORY_PICK_LIST : removal_policy
  INVENTORY_PICK_LIST ||--o{ INVENTORY_PICK_LIST_LINE : pick_list
  INVENTORY_RESERVATION ||--o{ INVENTORY_PICK_LIST_LINE : reservation
  STOCK_LEDGER_ENTRY ||--o{ STOCK_LEDGER_ENTRY : reverses

Cross-module references

• INVENTORY_ADJUSTMENT_REASON.company_id → COMPANY (core)
• INVENTORY_CYCLE_COUNT.company_id → COMPANY (core)
• INVENTORY_CYCLE_COUNT.location_id → LOCATION (core)
• INVENTORY_CYCLE_COUNT.adjustment_document_id → DOCUMENT (core)
• INVENTORY_CYCLE_COUNT_LINE.company_id → COMPANY (core)
• INVENTORY_CYCLE_COUNT_LINE.item_variant_id → ITEM_VARIANT (core)
• INVENTORY_CYCLE_COUNT_LINE.location_id → LOCATION (core)
• INVENTORY_PICK_LIST.company_id → COMPANY (core)
• INVENTORY_PICK_LIST.item_variant_id → ITEM_VARIANT (core)
• INVENTORY_PICK_LIST.warehouse_location_id → LOCATION (core)
• INVENTORY_PICK_LIST.staging_location_id → LOCATION (core)
• INVENTORY_PICK_LIST.posted_document_id → DOCUMENT (core)
• INVENTORY_PICK_LIST_LINE.company_id → COMPANY (core)
• INVENTORY_PICK_LIST_LINE.item_variant_id → ITEM_VARIANT (core)
• INVENTORY_PICK_LIST_LINE.source_location_id → LOCATION (core)
• INVENTORY_PICK_LIST_LINE.staging_location_id → LOCATION (core)
• INVENTORY_PICK_TICKET.company_id → COMPANY (core)
• INVENTORY_PICK_TICKET.item_variant_id → ITEM_VARIANT (core)
• INVENTORY_PICK_TICKET.location_id → LOCATION (core)
• INVENTORY_PUTAWAY_RULE.company_id → COMPANY (core)
• INVENTORY_PUTAWAY_RULE.item_variant_id → ITEM_VARIANT (core)
• INVENTORY_PUTAWAY_RULE.item_category_id → ITEM_CATEGORY (core)
• INVENTORY_PUTAWAY_RULE.warehouse_location_id → LOCATION (core)
• INVENTORY_PUTAWAY_RULE.source_location_id → LOCATION (core)
• INVENTORY_PUTAWAY_RULE.target_location_id → LOCATION (core)
• INVENTORY_PUTAWAY_RULE.target_zone_id → LOCATION (core)
• INVENTORY_REMOVAL_POLICY.company_id → COMPANY (core)
• INVENTORY_REMOVAL_POLICY.item_variant_id → ITEM_VARIANT (core)
• INVENTORY_REMOVAL_POLICY.location_id → LOCATION (core)
• INVENTORY_REPOST_REQUEST.company_id → COMPANY (core)
• INVENTORY_REPOST_REQUEST.item_variant_id → ITEM_VARIANT (core)
• INVENTORY_REPOST_REQUEST.location_id → LOCATION (core)
• INVENTORY_RESERVATION.company_id → COMPANY (core)
• INVENTORY_RESERVATION.item_variant_id → ITEM_VARIANT (core)
• INVENTORY_RESERVATION.location_id → LOCATION (core)
• INVENTORY_RESERVATION.owner_party_id → PARTY (core)
• STOCK_BALANCE.company_id → COMPANY (core)
• STOCK_BALANCE.item_variant_id → ITEM_VARIANT (core)
• STOCK_BALANCE.location_id → LOCATION (core)
• STOCK_LEDGER_ENTRY.company_id → COMPANY (core)
• STOCK_LEDGER_ENTRY.item_variant_id → ITEM_VARIANT (core)
• STOCK_LEDGER_ENTRY.location_id → LOCATION (core)
• STOCK_LEDGER_ENTRY.base_uom_id → UOM (core)
• STOCK_LEDGER_ENTRY.original_uom_id → UOM (core)
• STOCK_LEDGER_ENTRY.identity_bundle_id → IDENTITY_BUNDLE (core)
• STOCK_LEDGER_ENTRY.owner_party_id → PARTY (core)

Data dictionary

erp_inventory_adjustment_reason · 9 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
code	text
name	text
debit_transaction_kind	text
credit_transaction_kind	text
require_note	integer
active	integer
created_at	integer

erp_inventory_cycle_count · 11 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
count_no	text
location_id	integer	FK	erp_location
state	text
snapshot_at	integer
counted_by	text
approved_by	text
adjustment_document_id	integer	FK	erp_document
note	text
created_at	integer

erp_inventory_cycle_count_line · 13 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
cycle_count_id	integer	FK	erp_inventory_cycle_count
line_no	integer
item_variant_id	integer	FK	erp_item_variant
location_id	integer	FK	erp_location
lot_code	text
serial_code	text
snapshot_qty	numeric
counted_qty	numeric
delta_qty	numeric
uom	text
created_at	integer

erp_inventory_pick_list · 17 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
pick_no	text
source_ref	text
demand_type	text
demand_ref	text
demand_line_ref	text
item_variant_id	integer	FK	erp_item_variant
warehouse_location_id	integer	FK	erp_location
staging_location_id	integer	FK	erp_location
requested_qty	numeric
status	text
removal_policy_id	integer	FK	erp_inventory_removal_policy
idempotency_key	text
created_by	text
created_at	integer
posted_document_id	integer	FK	erp_document

erp_inventory_pick_list_line · 17 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
pick_list_id	integer	FK	erp_inventory_pick_list
line_no	integer
item_variant_id	integer	FK	erp_item_variant
source_location_id	integer	FK	erp_location
staging_location_id	integer	FK	erp_location
lot_code	text
serial_code	text
package_id	text
quality_state	text
reservation_id	integer	FK	erp_inventory_reservation
allocation_kind	text
qty	numeric
sort_key	text
status	text
created_at	integer

erp_inventory_pick_ticket · 9 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
source_ref	text
item_variant_id	integer	FK	erp_item_variant
location_id	integer	FK	erp_location
requested_qty	numeric
status	text
allocation_payload	text
created_at	integer

erp_inventory_putaway_rule · 15 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
scope_type	text
item_variant_id	integer	FK	erp_item_variant
item_category_id	integer	FK	erp_item_category
storage_category	text
warehouse_location_id	integer	FK	erp_location
source_location_id	integer	FK	erp_location
target_location_id	integer	FK	erp_location
target_zone_id	integer	FK	erp_location
capacity_qty	numeric
priority	integer
enforcement	text
active	integer
created_at	integer

erp_inventory_removal_policy · 11 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
scope_type	text
item_variant_id	integer	FK	erp_item_variant
location_id	integer	FK	erp_location
strategy	text
exclude_expired	integer
quality_state	text
priority	integer
active	integer
created_at	integer

erp_inventory_repost_request · 12 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
trigger_kind	text
item_variant_id	integer	FK	erp_item_variant
location_id	integer	FK	erp_location
anchor_date	text
status	text
progress_cursor	text
error_class	text
error_message	text
idempotency_key	text
created_at	integer

erp_inventory_reservation · 20 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
item_variant_id	integer	FK	erp_item_variant
location_id	integer	FK	erp_location
lot_code	text
serial_code	text
owner_party_id	integer	FK	erp_party
package_id	text
quality_state	text
demand_type	text
demand_ref	text
demand_line_ref	text
qty	numeric
strength	text
state	text
expires_at	integer
priority	integer
idempotency_key	text
created_by	text
created_at	integer

erp_stock_balance · 15 cols

column	type	key	references
company_id	text	FK	erp_company
item_variant_id	integer	FK	erp_item_variant
location_id	integer	FK	erp_location
identity_bundle_id	integer
lot_code	text
serial_code	text
owner_party_id	integer
package_id	text
quality_state	text
on_hand_qty	numeric
reserved_qty	numeric
available_qty	numeric
value	numeric
last_sle_id	integer
updated_at	integer

erp_stock_ledger_entry · 28 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
effective_date	text
posting_at	integer
item_variant_id	integer	FK	erp_item_variant
location_id	integer	FK	erp_location
seq	integer
qty_delta	numeric
base_uom_id	integer	FK	erp_uom
base_uom	text
original_qty	numeric
original_uom_id	integer	FK	erp_uom
original_uom	text
identity_bundle_id	integer	FK	erp_identity_bundle
package_id	text
owner_party_id	integer	FK	erp_party
origin_doctype	text
origin_doc_id	text
origin_line_id	text
operation_group	text
unit_cost	numeric
value_delta	numeric
valuation_state	text
idempotency_key	text
is_reversal	integer
reverses_id	integer	FK	erp_stock_ledger_entry
dimension_values	text
created_at	integer