Order Process and Status

Top

The Order Process and Status Diagram details the process steps and the Order status workflow from the moment when a NEW Order is created till the moment the Order is acknowledged COMPLETE.

The process is started when a Customer drives to the Car Service Shop, presents the documents about the Vehicle, the Vehicle Owner and the Vehicle Contact Person and asks to place an Order.

The Shop Employee searches the Vehicle in the Shop database and the process continues as detailed in the diagram below:

Order Header entities

Entity: ORDER_STATUS

Top

List of all status types an Order can pass through.

Attributes
ID PRIMARY KEY
Name Order status type. NOT NULL UNIQUE. Can't have two status types with the same name.
Description Order status type description.

Example

ORDER_STATUS

Name
NEW
SUBMITTED
APPROVED
IN PROGRESS
CANCELED
READY
PAID
COMPLETE

Entity: ORDER_HEADER

Top

This entity records information about all Orders.

Attributes
ID PRIMARY KEY
Shop_ID Lookup for the Shop at which the Order was placed. The same database can handle multiple Shops. NOT NULL
Vehicle_ID Lookup for the Vehicle for which the Order is placed. See Entity: VEHICLE for more information. NOT NULL
Invoiced_Customer_ID Lookup for the Customer who is Invoiced (can be the Vehicle Owner). See Entity: VEHICLE for more information. NOT NULL
Contact_Customer_ID Lookup for the Customer who can be contacted for Order (can be the Vehicle Owner or the Vehicle Contact Person). See Entity: VEHICLE for more information. NOT NULL
Created_Emp_Login_ID Lookup for the Shop Employee who created the Order. NOT NULL
Created_Time Timestamp when Order was created. NOT NULL
Submitted_Emp_Login_ID Lookup for the Shop Employee who Submitted the Order.
Approved_Emp_Login_ID Lookup for the Shop Employee who Approved the Order.
Managed_Emp_Login_ID Lookup for the Shop Employee who managed and has taken care of Order at all stages.
Note: The same Shop Employee can be granted with 'Submit Orders' and 'Manage Orders' responsibilities. In this case Submitted_Emp_Login_ID and Managed_Emp_Login_ID are the same. See Subject Area: EMPLOYEE AND ROLE for more information.
Order_No Shop internal number for the Order. Cannot have two orders with the same Order_No. NOT NULL UNIQUE
Complaint_Specifications Short description of vehicle repair and maintenance requirements. NOT NULL
Customer_Is_Waiting Flag attribute to indicate if Customer is waiting in the Shop till Order is completed. NOT NULL
Customer_Notes Customers can add notes to Order (for example information about preferred payment method etc. )
Start_Time Timestamp when work on Order should start. NOT NULL
Due_Time Timestamp when work on Order should be ready. NOT NULL
Vehicle_Arrival_Time Timestamp when Vehicle arrived to the Service Shop. NOT NULL
Is_Vehicle_Towed Flag attribute to indicate if Vehicle was towed. NOT NULL
Odometer_Value Vehicle Odometer mileage value.
Odometer_Time Timestamp when Vehicle Odometer mileage was read.
Price_Before_Tax Order total price calculated before Tax. NOT NULL
Tax_Value Order total Tax Value. NOT NULL
Price_After_Tax Order total price calculated after Tax. Price_After_Tax = Price_Before_Tax + Tax_Value. NOT NULL
Price_Before_Discount Order total price calculated before Discount. NOT NULL
Order_Items_Discount Order discount calculated as sum of discount values applied at Order Lines level. See Entity: ORDER_LINE, Entity: ORDER_LINE_DISCOUNT and Entity: DISCOUNT for more information. NOT NULL
Order_Discount Order discount value applied at total Order Value ( for example a discount of 25% from total Order value). See Entity: ORDER_DISCOUNT and Entity: DISCOUNT for more information. NOT NULL
Total_Discount_Value Total Discount Value = Order_Items_Discount + Order_Discount. NOT NULL
Price_After_Discount Order total price calculated after Discount. NOT NULL
Price_Adjustment Shop Employee can apply a price adjustment at Order level.
Price_Adjustment_Reason Reason for price adjustment.
Price Order final price resulted after taxes, discounts and adjustments were applied. NOT NULL
Latest_Status Latest status of Order. Every time when Order status changes, application should automatically update this attribute and also insert the Order latest status in the ORDER_HISTORY entity.
See Entity: ORDER_HISTORY for more information. NOT NULL
Latest_Status_Update The timestamp when the Order entered the latest status. Every time when Order status changes, application should automatically update this attribute and also update the ORDER_HISTORY.Start_Time attribute for the latest (most recent) Order status. The ORDER_HEADER.Latest_Status_Update attribute should be always synchronized with the ORDER_HISTORY.Start_Time attribute for the latest (most recent) Order status.
See Entity: ORDER_HISTORY for more information. NOT NULL
Is_Submitted Flag attribute to indicate if Order has been submitted. NOT NULL
Submitted_Time Timestamp when Order was submitted. If Is_Submitted is FALSE then Submitted_Time is NULL.
Is_Approved Flag attribute to indicate if Order has been approved. NOT NULL
Approved_Time Timestamp when Order was Approved. If Is_Approved is FALSE then Approved_Time is NULL.
Is_Canceled Flag attribute to indicate if the Order is Canceled.
Example: Shop Employee presses the Cancel Order button. In this case:
⦁ Is_Canceled flag changes from FALSE to TRUE
⦁ at the same time the Order Status changes to CANCELED.
⦁ at the same time, the application should insert automatically a record with the most recent Order status (CANCELED) in the ORDER_HISTORY entity. NOT NULL
Canceled_Time Timestamp when Order was Canceled.
Cancel_Reason Order Cancel reason.
Is_Ready Flag attribute to indicate if the work on all Order Lines is finished.
Example:
A Shop Employee can press an 'Order Ready' button when the work on all Order Lines is finished. In this case:
⦁ Is_Ready flag changes from FALSE to TRUE.
⦁ at the same time the Order Status changes from IN_PROGRESS to READY.
⦁ at the same time, the application should insert automatically a record with the most recent Order status (READY) in the ORDER_HISTORY entity. NOT NULL
Ready_Time Timestamp when the work on all Order Lines is finished.
Is_Paid Flag attribute to indicate if Order has been paid.
Example:
⦁ If Is_Paid is TRUE then Order is paid
⦁ If Is_Paid is FALSE then Order has not been paid. NOT NULL
Payment_Time Timestamp when Order was paid. If Is_Paid is FALSE then Payment_Time is NULL.
Is_Completed Flag attribute to indicate if Customer acknowledged Order Completed.
Example:
⦁ Is_Completed flag changes from FALSE to TRUE when Order work is finished, Order is paid, Customer received Invoice and Customer acknowledged Order Completed.
⦁ at the same time the Order Status changes to COMPLETE.
⦁ at the same time application should automatically insert a record with the most recent Order status (COMPLETE) in the ORDER_HISTORY entity. NOT NULL
Completed_Time Timestamp when Order was acknowledged Complete.
Comments Internal notes used for clarifications on record content.

Entity: ORDER_HISTORY

Top

This entity records the full status history for an ORDER.

Note

Every time when the Order Status changes from an old Order Status to a new Order Status, the application should automatically:

  • Create a new record in the ORDER_HISTORY entity to add the new Order Status and the Start Time of the new Order Status.
  • Update the record of the old Order Status in the ORDER_HISTORY entity:

    Synchronize the ORDER_HISTORY.End_Time of the old Order Status with the ORDER_HISTORY.Start_Time of the new Order Status.

Attributes
ID PRIMARY KEY
Order_ID Lookup for the Order to which the Order Status is assigned. NOT NULL
Order_Status_ID Lookup for the Order Status to be assigned. NOT NULL
Start_Time Timestamp when Order entered the status. NOT NULL
End_Time Timestamp when Order exit the status. When End_Time is NULL, it means Order is still in that status.

Entity: ORDER_DISCOUNT

Top

This entity records the discounts applied at ORDER level (for example 25% discount from Order total value).

Note

The discounts applied at Order Line are recorded in the ORDER_LINE_DISCOUNT entity.

Attributes
ID PRIMARY KEY
Discount_ID Lookup for the Discount to be applied at ORDER level. See Entity: DISCOUNT for more information. NOT NULL
Order_ID Lookup for the Order to which the discount is applied. NOT NULL
Value Calculated Value of the Discount. See Entity: DISCOUNT for more information about discount calculation. NOT NULL
Comments Internal notes used for clarifications on record content.

Order Line entities

Entity: ORDER_LINE_CAUSE

Top

List of causes for creating an Order Line.

Attributes
ID PRIMARY KEY
Name Order line cause. NOT NULL UNIQUE. Can't have two Order Line causes with the same name.
Description Order line cause description.

Example

ORDER_LINE_CAUSE

Name
Preventive Maintenance
Reparation
etc.

Entity: ORDER_LINE_STATUS

Top

List of all status types an Order Line can pass through.

Attributes
ID PRIMARY KEY
Name Order Line status type. NOT NULL UNIQUE. Can't have two status types with the same name.
Description Order Line status type description.

Example

ORDER_STATUS

Name
NEW
SUBMITTED
APPROVED
IN PROGRESS
CANCELED
READY
PAID
COMPLETE

Important

The application code should keep the Order Status and the Order Lines Status logically synchronized through Order process. For example:

  • When a 'NEW' Order is created, all the assigned Order Lines will also have the Status 'NEW'
  • When an Order is 'SUBMITTED' for approval, all the Order Lines will also be 'SUBMITTED' for approval.
  • An Order is 'APPROVED' only when all Order Lines are 'APPROVED'.
  • An Order is 'IN PROGRESS' only when at least one Order Line is 'IN PROGRESS'.
  • An Order is 'CANCELED' only when all Order Lines are 'CANCELED'.
  • An Order is 'READY' only when all Order Lines are 'READY'.
  • An Order is 'PAID' only when all Order Lines are 'PAID'.
  • An Order is 'COMPLETE' only when all Order Lines are 'COMPLETE'.

Entity: ORDER_LINE

Top

This entity records information about all the Order Lines assigned to an Order. Each time when a vehicle Part or a Labor item is added to an Order, an Order Line is created and assigned to the Order.

Important

An Order Line is created when:

  • a Labor item is selected and added to an Order
  • a Part is selected and added to an Order
  • a Service is selected and added to an Order. In this case, for each Service Item which is part of the Service, an Order Line is added to the Order. The added Order Line can be a vehicle Part or a Labor item. See Entity: SERVICE_ITEM for more information.
Attributes
ID PRIMARY KEY
Shop_ID Lookup for the Shop at which the Order was placed. NOT NULL
Order_ID Lookup for the Order to which the Order Line is assigned. NOT NULL
Order_Line_Cause_ID Lookup for the Order Line cause. NOT NULL
Created_Emp_Login_ID Lookup for the Shop Employee who created the Order Line. NOT NULL
Created_Time Timestamp when Order Line was created. NOT NULL
Submitted_Emp_Login_ID Lookup for the Shop Employee who Submitted the Order Line.
Approved_Emp_Login_ID Lookup for the Shop Employee who Approved the Order Line.
Managed_Emp_Login_ID Lookup for the Shop Employee who managed and has taken care of Order Line at all stages.
Line_No The sequential number of an Order Line. An Order can have many lines. Each Order Line has a sequential number: 1, 2, 3 etc. This attribute can help to sort the Order lines and also to reference an Order Line (for example when Shop Employee and Customer review an Order) etc. NOT NULL
Is_Customer_Item Flag attribute to indicate if the Order Line is created for a Customer Item. For example when the Customer bought a vehicle Part from elsewhere and brings the Part to the Car Sevice Shop to be used for a reparation. In this case the Part is registered as an Order Line with Part_Cost = 0 and Is_Customer_Item flag is set 'TRUE'. NOT NULL
Customer_Notes Customer Notes at Order Line level.
Description Order Line description.
Service_ID The Order Line record can be created for a Service, for a Labor item or for a vehicle Part. When the Order Line is created for a Service, the Service_ID is the lookup for that Service. In this case the Labor_ID and the Part_ID are NULL.
Workplace_ID Lookup for the Workplace where the Labor work for the Order Line is performed. If the Order Line was not created for a Labor item then Workplace_ID is NULL.
Labor_ID The Order Line record can be created for a Service, for a Labor item or for a vehicle Part. When the Order Line is created for a Labor item, the Labor_ID is the lookup for that Labor item. In this case the Service_ID and the Part_ID are NULL.
Labor_Rate Labor Rate for the Labor assigned to the Order Line. Standard Labor Rate is obtained from the Entity: LABOR using the lookup Labor_ID. If the Order Line was not created for a Labor item then Labor Rate is NULL.
Labor_Hours Labor Hours for the Labor assigned to the Order Line. Labor Hours are obtained from the Entity: LABOR using the lookup Labor_ID. If the Order Line was not created for a Labor item then Labor Hours is NULL.
Labor_Cost Labor Cost for the Labor assigned to the Order Line. Labor_Cost=Labor_Rate*Labor_Hours. If the Order Line was not created for a Labor item then Labor_Cost is NULL.
Part_ID The Order Line record can be created for a Service, for a Labor item or for a vehicle Part. When the Order Line is created for a vehicle Part, the Part_ID is the lookup for that vehicle Part. In this case the Service_ID and the Labor_ID are NULL.
Part_Cost Cost of the Part assigned to the Order Line. See Entity: PRICE for more information. The ORDER_LINE.Part_Cost and PRICE.Part_Cost attributes should be synchronized.
If the Order Line was not created for a Part then Part_Cost is NULL.
Quantity Part Quantity. If the Order Line was not created for a Part then Quantity is NULL.
Price_Before_Tax Order Line price calculated before Tax. If the Order Line was created for a Part then Price_Before_Tax=Part_Cost*Quantity. If the Order Line was created for a Labor item then Price_Before_Tax = Labor_Cost = Labor_Rate*Labor_Hours. NOT NULL
Tax_Value Order Line Tax Value. NOT NULL
Price_After_Tax Order Line price calculated after Tax. Price_After_Tax = Price_Before_Tax + Tax_Value. NOT NULL
Markup_Percentage Markup Percentage applied to Part or Labor. NOT NULL
Price_Before_Discount Order Line price calculated before Discount. Price_Before_Discount = Price_After_Tax + Price_After_Tax*Markup_Percentage/100. NOT NULL
Discount_Value Order Line discount value calculated as a sum of all discounts applied for that Order Line. See Entity: ORDER_LINE_DISCOUNT and Entity: DISCOUNT for more information. NOT NULL
Price_After_Discount Order Line price calculated after Discount. Price_After_Discount = Price_Before_Discount - Discount_Value. NOT NULL
Price Order Line final price. NOT NULL
Latest_Status Latest status of Order Line. Every time when Order Line status changes, application should automatically update this attribute and also insert the Order Line latest status in the ORDER_LINE_HISTORY entity.
See Entity: ORDER_LINE_HISTORY for more information. NOT NULL
Latest_Status_Update The timestamp when the Order Line entered the latest status. Every time when Order Line status changes, application should automatically update this attribute and also update the ORDER_LINE_HISTORY.Start_Time attribute for the latest (most recent) Order Line status. The ORDER_LINE.Latest_Status_Update attribute should be always synchronized with the ORDER_LINE_HISTORY.Start_Time attribute for the latest (most recent) Order Line status.
See Entity: ORDER_LINE_HISTORY for more information. NOT NULL
Is_Submitted Flag attribute to indicate if Order Line has been submitted. NOT NULL
Submitted_Time Timestamp when Order Line was submitted. If Is_Submitted is FALSE then Submitted_Time is NULL.
Is_Approved Flag attribute to indicate if Order Line has been approved. NOT NULL
Approved_Time Timestamp when Order Line was Approved. If Is_Approved is FALSE then Approved_Time is NULL.
Is_Canceled Flag attribute to indicate if the Order Line is Canceled.
Example: Shop Employee presses the Cancel Order Line button to announce when Order Line is Canceled. In this case:
⦁ Is_Canceled flag changes from FALSE to TRUE
⦁ at the same time the Order Line Status changes to CANCELED.
⦁ at the same time, the application should insert automatically a record with the most recent Order Line status (CANCELED) in the ORDER_LINE_HISTORY entity. NOT NULL
Canceled_Time Timestamp when Order Line was Canceled.
Cancel_Reason Order Line Cancel reason.
Is_Ready Flag attribute to indicate if the work on Order Line is finished.
Example:
A Shop Employee can press an 'Order Line Ready' button to announce when the work on the Order Line is finished. In this case:
⦁ Is_Ready flag changes from FALSE to TRUE.
⦁ at the same time the Order Line Status changes from IN_PROGRESS to READY.
⦁ at the same time, the application should insert automatically a record with the most recent Order Line status (READY) in the ORDER_LINE_HISTORY entity. NOT NULL
Ready_Time Timestamp when the work on the Order Line is finished.
Is_Paid Flag attribute to indicate if Order Line has been paid.
Example:
⦁ If Is_Paid is TRUE then Order Line is paid
⦁ If Is_Paid is FALSE then Order Line has not been paid. NOT NULL
Payment_Time Timestamp when Order Line was paid. If Is_Paid is FALSE then Payment_Time is NULL.
Is_Completed Flag attribute to indicate if Customer acknowledged Order Line Completed.
Example:
⦁ Is_Completed flag changes from FALSE to TRUE when Order Line work is finished, Order Line is paid, Customer received Invoice and Customer acknowledged Order Line Completed.
⦁ at the same time the Order Line Status changes to COMPLETE.
⦁ at the same time application should automatically insert a record with the most recent Order Line status (COMPLETE) in the ORDER_LINE_HISTORY entity. NOT NULL
Completed_Time Timestamp when Order Line was acknowledged Complete.
Comments Internal notes used for clarifications on record content.

Entity: ORDER_LINE_HISTORY

Top

This entity records the full status history for an ORDER LINE.

Note

Every time when the Order Line Status changes from an old Order Line Status to a new Order Line Status, the application should automatically:

  • Create a new record in the ORDER_LINE_HISTORY entity to add the new Order Line Status and the Start Time of the new Order Line Status.
  • Update the record of the old Order Line Status in the ORDER_LINE_HISTORY entity:

    Synchronize the ORDER_LINE_HISTORY.End_Time of the old Order Line Status with the ORDER_LINE_HISTORY.Start_Time of the new Order Line Status.

Attributes
ID PRIMARY KEY
Order_Line_ID Lookup for the Order Line to which the Order Line Status is assigned. NOT NULL
Order_Line_Status_ID Lookup for the Order Line Status to be assigned. NOT NULL
Start_Time Timestamp when Order Line entered the status. NOT NULL
End_Time Timestamp when Order Line exit the status. When End_Time is NULL, it means Order Line is still in that status. NULL

Entity: ORDER_LINE_DISCOUNT

Top

This entity records the discounts applied at ORDER LINE level.

Note

The discounts applied at Order Header level are recorded in the ORDER_DISCOUNT entity.

Attributes
ID PRIMARY KEY
Discount_ID Lookup for the Discount to be applied for the ORDER LINE. See Entity: DISCOUNT for more information. NOT NULL
Order_Line_ID Lookup for the Order Line to which the discount is applied. NOT NULL
Value Calculated Value of the Discount. See Entity: DISCOUNT for more information on calculation formula. NOT NULL
Comments Internal notes used for clarifications on record content.

Order Review entities

Entity: ORDER_REVIEW

Top

This entity records Customer reviews on Orders.

Note

Customers who have an online account can rate their Order experience on a scale from 1 to 5 and provide Order Reviews.

Attributes
ID PRIMARY KEY
Order_ID Lookup for the Order for which the Order review is recorded. NOT NULL
Customer_ID Lookup for the Customer who recorded the Order review. NOT NULL
Rating Rating from 1 to 5. NOT NULL
Review Order review text filled in by Customer. NOT NULL
Review_Date Timestamp when the Order review was created. NOT NULL
Reply Reply text from Shop Employee to Customer review.
Reply_Date Timestamp when the reply to Customer review was created.
Is_Compensated Flag attribute to indicate if Customer received a money compensation for the Review.
Example:
When a Customer sent a negative review for an Order that did not meet the expectations, the Shop can decide to offer a money compensation to Customer.
⦁ If Customer received a compensation for the Review then Is_Compensated attribute is set TRUE.
⦁ If Customer did not receive a compensation for the Review then Is_Compensated attribute is set FALSE. NOT NULL
Compensation_Value The money amount received by the Customer as a compensation for the Order Review.
⦁ If Is_Compensated is TRUE then the Compensation_Value is the money amount expressed in Shop currency received by Customer as compensation. This money amount is also added to the Customer total Credit.
Customer can use the Credit with the next Order placed at the Shop. See Subject Area: CUSTOMER, Entity: CUSTOMER, Attribute: Credit.
⦁ If Is_Compensated is FALSE then Compensation_Value is NULL.
Comments Internal notes used for clarifications on record content.
Top