Inventory fact table

ActionDate

TIMESTAMP

The date on which the action happened.     For Audits: action date is the date when the audit is completed.

For Purchase Orders: action date is the date when the stock is delivered.

For Transfer Orders: action date is the date when the stock is dispatched for the transfer-out center and it is the date when the stock is delivered for the transfer-in center.

actionorgponumber

BIGINT

PO number for various activites like buy, transfer and return

ActionQuantity

FLOAT

The quantity corresponding to the action.

ActionType

NVARCHAR(800)

The activity that resulted in the increase or decrease of product stock from a center.   Possible Actions are as below:

PO: Purchase Order placed from an external vendor

PO - Return: Return of items to an external vendor

Transfer - In: Transfer refers to the movement of products between centers. Transfer-In is when products move in to a center. 

Transfer-Out: Transfer refers to the movement of products between centers. Transfer-Out is when products move out from a center.

Transfer - Return: Transfer refers to the movement of products between centers. Transfer-Return is when products are returned to the origin center. 

Sale: Sale of a product to a customer

Retail Refund: Return of a product from a customer to the business

Consumption: Product used as part of a service

Conversion: Products converted from retail to consumable or vice versa

Checkout: Movement of products from store to floor

Audit: Reconciling of actual stock vs. expected stock

Bundle: The bundled product formed from individual products as a result of a bundling action

Bundle-Product: The individual products combined to form a bundle as a result of a bundling action

Unbundle: The product from which individual products were separated out as a result of an unbundling action

Unbundle-Product: The individual products separated out from a bundle as a result of an unbundling action

BundleVersionId

VARCHAR(36)

An identifier for the version of the bundled product.

Centerwid

BIGINT

A unique identifier for the center, generated in Centre dimension(Bi_DimCentre).

Bi_DimCenter

Centerwid

COGS

FLOAT

Current 'perpetual avg' of the product  * Action Quantity

This is the cost of goods sold as per Weighted Average costing method.           In case of refund, this value is populated based on the value in the initial sale record.

COGSWithTax

FLOAT

Current 'perpetual avg with tax' of the product  * Action Quantity

This is the cost of goods sold with tax as per Weighted Average costing method.      In case of refund, this value is populated based on the value in the initial sale record.

CumulativeCost

FLOAT

The total cost of the stock available in hand as per Weighted Average costing method.

CumulativeCostWithTax

FLOAT

The total cost with tax of the stock available in hand as per Weighted Average costing method.

CumulativeStock

FLOAT

The total stock available in hand (agnostic of Purchase Order) corresponding to the product and center associated with that row. To get the current Cumulative Stock for a product, look at the latest entry against that product and center.

DeliveredPricePU

FLOAT

The final price at which the corresponding product is delivered per unit.

DeliveredPricePUWithTax

FLOAT

The final price at which the corresponding product is delivered per unit including tax.

ETLCreateddate

TIMESTAMP

Latest timestamp at which the corresponding record was either inserted or updated in this table.

FactInventoryPk

BIGINT

A unique identifier(integer value) for each record generated in the fact table.

factinventorywid

BIGINT

A unique identifier(integer value) for each record generated in the fact table.

Yes

FIFOCost

FLOAT

'Delivery price per unit' of the associated PO * Action quantity 

This is the cost of goods sold as per FIFO costing method. The 'poid' field indicates the Purchase Order from where the delivery price is taken.

FIFOCostWithTax

FLOAT

'Deliver price pu with tax' of the associated PO  * Action quantity 

This is the cost of goods sold with tax as per FIFO costing method. The 'poid' field indicates the Purchase Order from where the delivery price is taken.

FIFOStock

FLOAT

The stock available in hand corresponding to the Purchase Order and product associated with that row. This is as per FIFO method. To get the current FIFO stock for a product against a purchase order, look at the latest entry against that purchase order and product.

IntransitDate

TIMESTAMP

The date on which the product is considered to be in transit. This can be different for different products in a Purchase Order.

InventoryActionid

VARCHAR(36)

An identifier that provides more details about the action. This gets populated differently depending on the action type. This field can be used to join this data source with the Sales data source.            The list below shows how this field gets populated for the various action types. 

- For purchase order, transfer order, and their returns: the POID gets populated here.

- For sale: the Invoice Item ID of the sale transaction gets populated here.

- For retail refund: the Invoice Item ID of the associated sale transaction gets populated in case sale and refund happen in the same center. The Invoice Item ID of the refund transaction gets populated in case sale and refund happen in different centers. 

- For consumption: the Appointment ID of the service sale gets populated here.

- For other action types: an identifier associated with the action gets populated here.

InventoryRecordType

INT

The code to indicate the record is of a retail or consumable product.

invoicenum

VARCHAR(2048)

Invoice number of PO record. This will be populated only when action type is of 'PO'

ItemdeliveredDt

TIMESTAMP

The date on which the product is delivered. This can be different for different products in a Purchase Order.

ItemDeliveryOther

FLOAT

Other charges recorded in the PO for the corresponding product per unit. This field is only populated for action types corresponding to Purchase Orders and Transfer Orders.

ItemDiscount

FLOAT

The discount for the corresponding product per unit. This field is only populated for action types corresponding to Purchase Orders and Transfer Orders.

ItemShippingCost

FLOAT

The shipping charges for the corresponding product per unit. This field is only populated for action types corresponding to Purchase Orders and Transfer Orders.

ItemTax

FLOAT

The tax for the corresponding product per unit.This field is only populated for action types corresponding to Purchase Orders and Transfer Orders.

OrderedQuantity

INT

The quantity ordered through the corresponding Purchase Order or Transfer Order. This field is only populated for action types corresponding to Purchase Orders and Transfer Orders.

OrderRaisedDate

TIMESTAMP

The date on which the order is raised.

OrderType

VARCHAR(800)

This indicates if the order is internal or external.

- External: Orders raised from external vendors.

- Internal: Orders raised between centers of same organization.

OrganizationId

VARCHAR(36)

A unique identifier for the organition for which this data belongs

Bi_DimCenter

OrganizationId

OrgPOnumber

BIGINT

The Purchase Order number corresponding to the action, in the organization where the order originated.

PerpetualAvg

FLOAT

Cumulative Cost/ Cumulative Stock

This is the per unit cost of the total stock available in hand as per Weighted Average costing method.

PerpetualAvgWithTax

FLOAT

Cumulative Cost with Tax/ Cumulative Stock

This is the per unit cost with tax of the total stock available in hand as per Weighted Average costing method.

PODID

integer

Internal to the ETL, says from which POD data is being synced the table

PodName

VARCHAR(20)

Internal to the ETL, says from which POD data is being synced the table

POID

VARCHAR(36)

Internal DB storage identifier.

PONumber

BIGINT

The Purchase Order number corresponding to the action. This maps to the PO number in Core. In case of stock outflow, this is the PO that the outflow is associated with as per FIFO logic.

productwid

BIGINT

A unique identifier for a product, generated in Product dimension(Bi_DimProduct).

Bi_DimProduct

productwid

ProductType

NVARCHAR(800)

This indicates if the order quantity is that of a retail or consumable product.

RefundInvoiceItemid

VARCHAR(36)

The unique identifier for each line item within the invoice that has been subjected to Refund.

transfercenterwid

BIGINT

A unique identifier for the center corresponding to a product transfer. This is populated for action types related to transfers.(Bi_DimCenter)

Bi_DimCenter

Centerwid

Vendorwid

BIGINT

A unique identifier for the vendor in Analytics.(Bi_DimVendor)

Bi_DimVendor

Vendorwid

Void

BOOLEAN

Indicates if the row should be inserted or deleted. 1- delete, 0-insert

Volumeperitem

FLOAT

The volume per unit of the product. For example, if the sold product is a 150 ml shampoo, this field would display '150'. The unit of measure of the volume is recorded in the 'volume type name' field.

VolumeTypename

NVARCHAR(800)

Unit of measure of the volume populated in the 'volume per item' field. For example, if the product sold is a 150 ml shampoo, this field would display 'ml'.

pocreationdate

TIMESTAMP

Date on which Purchase Order is created in the system.

postatus

VARCHAR(100)

Indicates current status of Purchase Order

reconciliationdate

TIMESTAMP

Date on which an audit is reconciled in the system

isopenbalanceentry

BOOLEAN

This is the internal column to identify the opening balance entry for that product & center combination.

actionvendorwid

BIGINT

This column holds the vendor id of the actionID. Existing column vendorwid contains vendor id of the POID.

groupid

Internal to Zenoti. Used for data processing.

BIGINT

batchwid

BIGINT

Adds batch ID to the Inventory details.

batchtransactionid

VARCHAR(36)

cumulativebatchstock

FLOAT