Data Model Pack

Help Docs → Point Of Sale → Subject area: DOCUMENT

Subject area: DOCUMENT

Document

Entity: DOCUMENT_CATEGORY
Entity: DOCUMENT_TYPE
Entity: DOCUMENT

Sale Order

Important

Each Sale Order (ORDER_HEADER) record must have a Document record assigned with Document Type '200 - Sale Order'.

Important

For each Sale Order Item (ORDER_LINE) assigned to a Sale Order (ORDER_HEADER), must update the STOCK_HISTORY with the Item quantity that was sold. See Subject Area: INVENTORY AND STOCK Entity: STOCK_HISTORY for more information.

Entity: ORDER_HEADER
Entity: ORDER_LINE

See Subject Area: ORDER for more information about Sale Order entities.

Sale Return

Entity: SALE_RETURN
Entity: SALE_RETURN_ITEM
Entity: REFUND

Purchase

Entity: PURCHASE
Entity: PURCHASE_ITEM

Stock Return

Entity: STOCK_RETURN
Entity: STOCK_RETURN_ITEM

Inventory Count

Important

Each Inventory Count record must have a Document record assigned with Document Type '300 - Inventory Count'.

Important

For each Inventory Count Item assigned to an Inventory Count, must update the STOCK_HISTORY to reflect the Item quantity resulted after Inventory Count. See Subject Area: INVENTORY AND STOCK Entity: STOCK_HISTORY for more information.

Entity: INVENTORY_COUNT
Entity: INVENTORY_COUNT_ITEM

See Subject Area: INVENTORY AND STOCK for more information about Inventory Count entities.

Loss and Damage

Entity: LOSS_AND_DAMAGE
Entity: LOSS_AND_DAMAGE_ITEM

Entity: DOCUMENT_CATEGORY

List of Document Categories.

Attributes
ID	PRIMARY KEY
Name	Document Category name. NOT NULL UNIQUE. Can't have two Document Categories with the same name.
Description	Document Category description.
Is_Active	Flag attribute to indicate if the Document Category is still available or it was canceled permanently. NOT NULL
Comments	Internal notes used for clarifications on record content.

Example

DOCUMENT_CATEGORY

Name
1 - Expense
2 - Sale
3 - Inventory
4 - Loss and Damage

Entity: DOCUMENT_TYPE

List of Document Types.

Attributes
ID	PRIMARY KEY
Document_Category_ID	Lookup for the Document Category to which the Document Type is assigned. NOT NULL
Name	Document Type name. NOT NULL UNIQUE. Can't have two Document Types with the same name.
Description	Document Type description.
Is_Active	Flag attribute to indicate if the Document Type is still available or it was canceled permanently. NOT NULL
Comments	Internal notes used for clarifications on record content.

Example

Document Category	Document Type
1 - Expense	100 - Purchase
1 - Expense	120 - Stock Return
2 - Sale	200 - Sale Order
2 - Sale	210 - Sale Return
2 - Sale	220 - Refund
3 - Inventory	300 - Inventory Count
4 - Loss and Damage	400 - Loss and Damage

Entity: DOCUMENT

List of documents recorded at a Store.

Attributes
ID	PRIMARY KEY
Store_ID	Lookup for the Store at which the document is recorded. NOT NULL
Document_Type_ID	Lookup for the Document Type. NOT NULL
Number	Document number. NOT NULL
External_Number	Document external number (for example when the document is a supplier invoice for a purchase).
Order_Header_ID	Lookup for the Sale Order if Document Type is '200 - Sale Order'.
Sale_Return_ID	Lookup for the Sale Return if Document Type is '210 - Sale Return'.
Purchase_ID	Lookup for the Purchase if Document Type is '100 - Purchase'.
Stock_Return_ID	Lookup for the Stock Return if Document Type is '120 - Stock Return'.
Inventory_Count_ID	Lookup for the Inventory Count if Document Type is '300 - Inventory Count'. See Subject Area: INVENTORY AND STOCK Entity: INVENTORY_COUNT for more information.
Loss_and_Damage_ID	Lookup for the Loss and Damage if Document Type is '400 - Loss and Damage'.
Created_Time	Timestamp when Document was created. NOT NULL
Created_Emp_Login_ID	Lookup for the Store Employee who created the Document. NOT NULL
Description	Stock movement description.
Comments	Internal notes used for clarifications on record content.

Entity: SALE_RETURN

This entity records information about a Sale Return requested for an Order. If a Customer requests to return a part or all the Order Items after the Order has reached the Ready status, a Sale Return should be processed.See Subject Area: ORDER, Entity: ORDER_HEADER, Attributes: Return_Required, Return_Quantity, Return_Time. for more information.

Important

Each Sale Return record must have a Document record assigned with Document Type '210 - Sale Return'.

Attributes
ID	PRIMARY KEY
Store_ID	Lookup for the Store at which the Sale Return is requested. NOT NULL
Order_ID	Lookup for the Order for which the Sale Return is requested. NOT NULL
Entry_Stock_Time	Timestamp when the Sale Return Items quantities are added to stock. NOT NULL
Is_Refund_Required	Flag attribute to indicate if Sale Return must be followed by money Refund. If an Order was paid IN_ADVANCE, any Sale Return or Cancel request issued after the Order has reached the Ready status must be followed by a Refund. See Subject Area: ORDER for more information. NOT NULL
Refund_Amount	Refund amount to be paid to Customer for Sale return.
Is_Refunded	Flag attribute to indicate if the full amount of Refund was paid to Customer. See Entity: REFUND for more information. NOT NULL
Refund_Time	Timestamp when the full amount of Refund was paid to Customer. See Entity: REFUND for more information. NOT NULL
Description	Sale Return description.
Comments	Internal notes used for clarifications on record content.

Entity: SALE_RETURN_ITEM

This entity records information about all Sale Return Items assigned to a Sale Return.

Important

For each Sale Return Item assigned to a Sale Return must update the STOCK_HISTORY with the returned Item quantity. See Subject Area: INVENTORY AND STOCK Entity: STOCK_HISTORY for more information.

Attributes
ID	PRIMARY KEY
Sale_Return_ID	Lookup for the Sale Return to which the Sale Return Item is assigned. NOT NULL
Order_Line_ID	Lookup for the Order Line that contains the Item to be returned. NOT NULL
Line_No	The sequential number of a Sale Return line. A Sale Return can have many lines. Each Sale Return line has a sequential number: 1, 2, 3 etc. This attribute can help to sort the Sale Return lines and also to reference a Sale Return line (for example when Store Employee and Supplier review a Sale Return) etc. NOT NULL
Return_Quantity	Item Quantity returned. NOT NULL.
Description	Sale Return Item description.
Comments	Internal notes used for clarifications on record content.

Entity: REFUND

This entity records information about all Refunds applied for a Sale Return.

Attributes
ID	PRIMARY KEY
Sale_Return_ID	Lookup for the Sale Return for which the Refund was applied. A Sale Return can have one or many refunds applied. For example a Sale Return has two refunds applied when: 30% of the total amount is refunded with CASH and the rest is refunded on Customer CARD. NOT NULL
Payment_Method_ID	Lookup for the Payment Method used for the Refund. NOT NULL
Refund_No	Refund number that Store associates with the Refund. Cannot have two refunds with the same Refund_No. NOT NULL UNIQUE
Amount	The Amount that is refunded. NOT NULL
Cash_Paid	This is the amount handed off to Customer if Cashier paid with Cash.
Cash_Change	This is the amount returned by Customer to Cashier if Cashier paid with Cash.
Refund_Time	Timestamp when refund was registered. NOT NULL
Comments	Internal notes used for clarifications on record content.

Entity: PURCHASE

This entity records information about a Purchase receive from a Supplier.

Important

Each Purchase record must have a Document record assigned with Document Type '100 - Purchase'.

Attributes
ID	PRIMARY KEY
Store_ID	Lookup for the Store at which the Purchase is recorded. NOT NULL
Supplier_ID	Lookup for the Supplier of the purchased items. NOT NULL
Entry_Stock_Time	Timestamp when the Purchase Items quantities are added to stock. NOT NULL
Description	Purchase description.
Is_Paid	Flag attribute to indicate if Purchase is paid. NOT NULL
Comments	Internal notes used for clarifications on record content.

Entity: PURCHASE_ITEM

This entity records information about all Purchase Items assigned to a Purchase.

Important

For each Purchase Item added to a Purchase must update the STOCK_HISTORY with the received Item quantity. See Subject Area: INVENTORY AND STOCK Entity: STOCK_HISTORY for more information.

Attributes
ID	PRIMARY KEY
Purchase_ID	Lookup for the Purchase to which the Purchase Item is assigned. NOT NULL
Item_ID	Lookup for the Item added to Purchase. NOT NULL
Line_No	The sequential number of a Purchase line. A Purchase can have many lines. Each Purchase line has a sequential number: 1, 2, 3 etc. This attribute can help to sort the Purchase lines and also to reference a Purchase line (for example when Store Employee and Supplier review a Purchase) etc. NOT NULL
Quantity	Item Quantity added to Purchase. NOT NULL.
Supplier_Price_Before_Tax	Price before tax. NOT NULL
Supplier_Tax_Value	Tax Value applied. NOT NULL
Supplier_Price_After_Tax	Price_After_Tax = Price_Before_Tax + Tax_Value. NOT NULL
Supplier_Discount_Value	Discount value applied. NOT NULL
Supplier_Price	Purchase Item final price. NOT NULL
Description	Purchase Item description.
Comments	Internal notes used for clarifications on record content.

Entity: STOCK_RETURN

This entity records information about a Stock Return to a Supplier.

Important

Each Stock Return record must have a Document record assigned with Document Type '120 - Stock Return'.

Attributes
ID	PRIMARY KEY
Store_ID	Lookup for the Store at which the Stock Return is recorded. NOT NULL
Purchase_ID	Lookup for the Purchase for which the Stock Return is recorded. NOT NULL
Supplier_ID	Lookup for the Supplier. NOT NULL
Exit_Stock_Time	Timestamp when the Stock Return Items quantities are decreased from stock. NOT NULL
Description	Stock Return description.
Is_Paid	Flag attribute to indicate if Stock Return is paid by Supplier. NOT NULL
Comments	Internal notes used for clarifications on record content.

Entity: STOCK_RETURN_ITEM

This entity records information about all Stock Return Items assigned to a Stock Return.

Important

For each Stock Return Item assigned to a Stock Return must update the STOCK_HISTORY with the returned Item quantity. See Subject Area: INVENTORY AND STOCK Entity: STOCK_HISTORY for more information.

Attributes
ID	PRIMARY KEY
Stock_Return_ID	Lookup for the Stock Return to which the Stock Return Item is assigned. NOT NULL
Item_ID	Lookup for the Item assigned to the Stock Return. NOT NULL
Line_No	The sequential number of a Stock Return line. A Stock Return can have many lines. Each Stock Return line has a sequential number: 1, 2, 3 etc. This attribute can help to sort the Stock Return lines and also to reference a Stock Return line (for example when Store Employee and Supplier review a Stock Return) etc. NOT NULL
Quantity	Item Quantity assigned to Stock Return. NOT NULL.
Supplier_Price_Before_Tax	Price before tax. NOT NULL
Supplier_Tax_Value	Tax Value applied. NOT NULL
Supplier_Price_After_Tax	Price_After_Tax = Price_Before_Tax + Tax_Value. NOT NULL
Supplier_Discount_Value	Discount value applied. NOT NULL
Supplier_Price	Price paid at purchase. NOT NULL
Return_Amount	Amount paid by Supplier at return. NOT NULL
Description	Stock Return Item description.
Comments	Internal notes used for clarifications on record content.

Entity: LOSS_AND_DAMAGE

List of Loss and Damage evaluations performed at a Store.

Important

Each Loss and Damage record must have a Document record assigned with Document Type '400 - Loss and Damage'.

Attributes
ID	PRIMARY KEY
Store_ID	Lookup for the Store at which the Loss and Damage evaluation was performed. NOT NULL
Exit_Stock_Time	Timestamp when the results of the Loss and Damage evaluation were applied to Stock.
Description	Loss and Damage evaluation description.
Comments	Internal notes used for clarifications on record content.

Entity: LOSS_AND_DAMAGE_ITEM

List of Items evaluated during a Loss and Damage evaluation performed at a Store.

Important

For each Item assigned to a Loss and Damage, must update the STOCK_HISTORY with the Item quantity that resulted as Loss and Damage during evaluation. See Subject Area: INVENTORY AND STOCK Entity: STOCK_HISTORY for more information.

Attributes
ID	PRIMARY KEY
Loss_and_Damage_ID	Lookup for the Loss and Damage evaluation to which the evaluated Item is assigned. NOT NULL
Item_ID	Lookup for the Item that was evaluated. NOT NULL
Quantity	Item quantity evaluated as Loss and Damage. NOT NULL .
Description	Item evaluation description.
Comments	Internal notes used for clarifications on record content.

Accelerate every database schema design with powerful data models

We have already designed the data model and prepared the SQL scripts for creating the database schema objects.

Try Data Model Pack Free

Product

Resources

Support

Point Of Sale | Subject area: DOCUMENT

Subject area: DOCUMENT