Inventory Fact Consolidated
ZTZenoti Docs Team Updated April 10, 2026 5 min readGuide
Consolidated fact table capturing inventory actions including purchase orders, stock transfers, deliveries, adjustments, and associated cost calculations (COGS, FIFO, perpetual average) across centers and products.
Columns
| Column Name | Data Type | Nullable | Description |
|---|---|---|---|
| organization name | VARCHAR(512) | Yes | Name of the organization. Sourced from DimOrganization.organization_name. |
| organization account name | VARCHAR(256) | Yes | Account name of the organization. Sourced from DimOrganization.organization_account_name. |
| zone name | VARCHAR(800) | Yes | Zone name of the center. Sourced from DimCenter.zone_name. |
| center name | VARCHAR(128) | Yes | Name of the center. Sourced from DimCenter.center_name. |
| center city | VARCHAR(128) | Yes | City of the center. Sourced from DimCenter.city. |
| center zip | VARCHAR(32) | Yes | Zip code of the center. Sourced from DimCenter.zip. |
| center state name | VARCHAR(4096) | Yes | State of the center. Sourced from DimCenter.state_name. |
| center company name | VARCHAR(200) | Yes | Company name of the center. Sourced from DimCenter.company_name. |
| country name | VARCHAR(4096) | Yes | Country of the center. Sourced from DimCenter.country_name. |
| operating center | BOOLEAN | Yes | Indicates whether the center is operating. Sourced from DimCenter.operating_center. |
| center wid | BIGINT | Yes | Warehouse identifier for the center. Sourced from DimCenter.center_wid. |
| transfer center organization name | VARCHAR(512) | Yes | Organization name of the transfer center. Sourced from DimCenter.organization_name (transfer). |
| transfer center organization account name | VARCHAR(256) | Yes | Organization account name of the transfer center. Sourced from DimCenter.organization_account_name (transfer). |
| transfer center zone name | VARCHAR(800) | Yes | Zone name of the transfer center. Sourced from DimCenter.zone_name (transfer). |
| transfer center name | VARCHAR(128) | Yes | Name of the transfer center. Sourced from DimCenter.center_name (transfer). |
| transfer center city | VARCHAR(128) | Yes | City of the transfer center. Sourced from DimCenter.city (transfer). |
| transfer center zip | VARCHAR(32) | Yes | Zip code of the transfer center. Sourced from DimCenter.zip (transfer). |
| transfer center state name | VARCHAR(4096) | Yes | State of the transfer center. Sourced from DimCenter.state_name (transfer). |
| transfer center company name | VARCHAR(200) | Yes | Company name of the transfer center. Sourced from DimCenter.company_name (transfer). |
| transfer center country name | VARCHAR(4096) | Yes | Country of the transfer center. Sourced from DimCenter.country_name (transfer). |
| is transfer center operating | BOOLEAN | Yes | Indicates whether the transfer center is operating. Sourced from DimCenter.operating_center (transfer). |
| product name | VARCHAR(1024) | Yes | Name of the product. Sourced from DimProduct.product_name. |
| product category | VARCHAR(65535) | Yes | Category of the product. Sourced from DimProduct.category. |
| product parent category | VARCHAR(65535) | Yes | Parent category of the product. Sourced from DimProduct.parent_category. |
| product business unit | VARCHAR(800) | Yes | Business unit of the product. Sourced from DimProduct.business_unit. |
| consummable | BOOLEAN | Yes | Indicates whether the product is consumable. Sourced from DimProduct.consummable. |
| product has variant | BOOLEAN | Yes | Indicates whether the product has variants. Sourced from DimProduct.has_variant. |
| retail | BOOLEAN | Yes | Indicates whether the product is a retail product. Sourced from DimProduct.retail. |
| is product inactive | BOOLEAN | Yes | Indicates whether the product is inactive. Sourced from DimProduct.is_inactive. |
| product tags | VARCHAR(65535) | Yes | Tags associated with the product. Sourced from DimProduct.tags. |
| product color name | VARCHAR(2048) | Yes | Color variant name of the product. Sourced from DimProduct.color_name. |
| product size name | VARCHAR(2048) | Yes | Size variant name of the product. Sourced from DimProduct.size_name. |
| poid | VARCHAR(36) | Yes | Unique identifier (UUID) of the purchase order. |
| po number | BIGINT | Yes | Purchase order number. |
| action type | VARCHAR(1600) | Yes | Type of inventory action (e.g., Received, Sold, Transferred). |
| action date | TIMESTAMP | Yes | Date and time of the inventory action. |
| order raised date | TIMESTAMP | Yes | Date and time when the order was raised. |
| in transit date | TIMESTAMP | Yes | Date and time when the item entered transit. |
| item delivery date | TIMESTAMP | Yes | Date and time of item delivery. |
| transfer center wid | BIGINT | Yes | Warehouse identifier for the transfer center. |
| order type | VARCHAR(800) | Yes | Type of order (e.g., Purchase Order, Transfer Order). |
| vendor name | VARCHAR(1024) | Yes | Name of the vendor. |
| volume type name | VARCHAR(1200) | Yes | Name of the volume measurement type. |
| volume per item | DOUBLE PRECISION | Yes | Volume per item unit. |
| product type | VARCHAR(1200) | Yes | Type classification of the product. |
| order quantity | INTEGER | Yes | Quantity ordered. |
| action quantity | DOUBLE PRECISION | Yes | Quantity involved in the action. |
| delivery price per unit | DOUBLE PRECISION | Yes | Price per unit at delivery. |
| item tax | DOUBLE PRECISION | Yes | Tax amount per item. |
| item discount | DOUBLE PRECISION | Yes | Discount amount per item. |
| item shipping cost | DOUBLE PRECISION | Yes | Shipping cost per item. |
| cogs | DOUBLE PRECISION | Yes | Cost of goods sold (COGS). |
| cogs with tax | DOUBLE PRECISION | Yes | COGS including tax. |
| fifo cost | DOUBLE PRECISION | Yes | FIFO (First In First Out) cost. |
| fifo stock | DOUBLE PRECISION | Yes | FIFO stock quantity. |
| cumulative stock | DOUBLE PRECISION | Yes | Cumulative stock quantity. |
| perpetual avg with tax | DOUBLE PRECISION | Yes | Perpetual average cost including tax. |
| perpetual avg | DOUBLE PRECISION | Yes | Perpetual average cost. |
| cumulative cost | DOUBLE PRECISION | Yes | Cumulative cost of stock. |
| fifo cost with tax | DOUBLE PRECISION | Yes | FIFO cost including tax. |
| cumulative cost with tax | DOUBLE PRECISION | Yes | Cumulative cost including tax. |
| deliver price pu with tax | DOUBLE PRECISION | Yes | Delivery price per unit including tax. |
| item delivery other charges | DOUBLE PRECISION | Yes | Other charges associated with item delivery. |
| product wid | BIGINT | Yes | Warehouse identifier for the product. Sourced from DimProduct.product_wid. |
| inventory action id | VARCHAR(36) | Yes | Unique identifier (UUID) of the inventory action. |
| desired inventory | FLOAT | Yes | Desired inventory level. |
| order inventory | FLOAT | Yes | Order inventory level. |
| alert inventory | FLOAT | Yes | Alert inventory threshold. |
| consumable desired inventory | FLOAT | Yes | Desired inventory level for consumables. |
| consumable order inventory | FLOAT | Yes | Order inventory level for consumables. |
| consumable alert inventory | FLOAT | Yes | Alert inventory threshold for consumables. |
| PO Invoice Number | VARCHAR(2048) | Yes | Purchase order invoice number. |
| Action Org PO Number | BIGINT | Yes | Organization-level PO number for the action. |
| Org PO Number | BIGINT | Yes | Organization-level PO number. |
| fact inventory wid | BIGINT | Yes | Warehouse identifier for the inventory fact record. |
| po creation date | TIMESTAMP | Yes | Date and time the purchase order was created. |
| po status | VARCHAR(100) | Yes | Status of the purchase order. |
| audit reconciliation date | TIMESTAMP | Yes | Date of inventory audit reconciliation. |
| is open balance entry | BOOLEAN | Yes | Indicates whether this is an open balance entry. |
| action vendor name | VARCHAR(1024) | Yes | Vendor name associated with the specific action. |
| FactInventoryPk | BIGINT | Yes | Primary key of the inventory fact record. |
| Center Code | VARCHAR(40) | Yes | Code of the center. Sourced from DimCenter.center_code. |
| Transfer Center Code | VARCHAR(40) | Yes | Code of the transfer center. Sourced from DimCenter.center_code (transfer). |
| batch wid | BIGINT | Yes | Warehouse identifier for the batch. |
| batch number | VARCHAR(256) | Yes | Batch number identifier. |
| batch expiry | TIMESTAMP | Yes | Expiry date and time of the batch. |
| batch transaction id | VARCHAR(36) | Yes | Transaction ID for the batch (added via ALTER). |
| cumulative batch stock | FLOAT | Yes | Cumulative stock quantity for the batch (added via ALTER). |
Primary Key
No explicit primary key is defined. inventory action id serves as the logical key. fact inventory wid is used as the distribution key and FactInventoryPk as a surrogate key.
Foreign Keys
center name,center wid,Center Codereferences DimCenter (primary center)transfer center name,transfer center wid,Transfer Center Codereferences DimCenter (transfer center)product name,product widreferences DimProductpoid,po numberreferences purchase order entityvendor namereferences vendor entity
Enumerator Values
| Column Name | Known Values |
|---|---|
| action type | Received, Sold, Transferred, Adjusted, Returned, etc. |
| order type | Purchase Order, Transfer Order, etc. |
| po status | Open, Closed, Partially Received, etc. |
Standard ETL metadata columns (EtlLastUpdatedOn, EtlLastUpdatedBy, podname, groupid) are present in the underlying table but omitted from this reference for brevity.
Was this article helpful?
Your feedback helps us improve our documentation.