Technical¶
Components¶
Lime CPQ consists of the following components:
- Web components:
- CPQ Guide
- Generate Order from Deal
- Order Guide
- Order Status Flow
- Quotation template
Lime CPQ is depending on limepkg-document-templates and limepkg-server-commands
Table and Field Definitions Lime CRM¶
These tables and fields must be created before you can use Lime CPQ. Some fields are included as a preparation to use with the ERP Connector add-on.
Article¶
Table database name: article
| Database Field Name | Field Type | Length | Description |
|---|---|---|---|
| articlegroup | Relation (field) | N/A | Which article group it belongs to |
| articleno | Text | 32 | Should contain the article number used in the ERP system |
| description | Text | 256 | The name of the article |
| price | Decimal | N/A | Price for one unit. Excl. VAT |
| unit | Text | 32 | st, h etc. |
| vat | Decimal | N/A | VAT percentage expressed as decimal number, e.g. 0.25 |
| costprice | Decimal | N/A | Cost price for one unit. Excl. VAT |
| grossmarginratio | Decimal | N/A | Gross margin ratio expressed as percent, e.g. 45 |
| incomeaccount | Text | 32 | Used if the deal article/order row should be connected to an income account |
| image | Image field | N/A | Image of the article |
| shorttext | Text | 512 | Shorter description of the article |
| longtext | Text | 4000 | Longer description of the article |
| active | Yes/No | N/A | Only active articles are selectable in the guides |
| orderrow | Relation (tab) | N/A | The order rows that are connected to the article |
| dealarticle | Relation (tab) | N/A | The deal articles that are linked to the article |
incomeaccount is included as a preparation for optional synchronization.
Article Group¶
Table database name: articlegroup
| Database Field Name | Field Type | Length | Description |
|---|---|---|---|
| name | Text | 64 | |
| active | Yes/No | N/A | Only active article groups are shown in the article group selector |
| article | Relation (tab) | N/A | The articles that belongs to the article group |
| dealarticle | Relation (tab) | N/A | The deal articles that are linked to the article group |
| orderrow | Relation (tab) | N/A | The order rows that are connected to the article group |
Deal¶
The Deal table is included in many base solutions and will get a few extra fields.
Table database name: deal
| Database Field Name | Field Type | Length | Description |
|---|---|---|---|
| dealarticle | Relation (tab) | N/A | All deal articles belonging to the deal |
| order | Relation (tab) | N/A | All orders connected to the deal |
| currency | Text | 3 | Currency |
| subtotal | Decimal | N/A | Total before discounts excl. VAT. The sum of all deal articles' subtotal. |
| discount | Decimal | N/A | Total discount for all units excl. VAT. The sum of all deal articles' discount. |
| value | Decimal | N/A | Total amount after discount excl. VAT. Calculated as subtotal - discount. |
| vat | Decimal | N/A | Total VAT as amount. The sum of all deal articles' VAT amounts. |
| cpq_additionalinfo | Text | 2048 | Additional information suitable to use in the template |
value is a standard field in the base solution and shown here for reference.
Deal Article¶
Deal article also copies and stores some data from the article (if selected) to provide a snapshot.
Table database name: dealarticle
| Database Field Name | Field Type | Length | Description |
|---|---|---|---|
| deal | Relation (field) | N/A | Relation to deal |
| row_id | Text | 32 | External id (Syncify) |
| position | Integer | N/A | Position of the deal article |
| articlegroup | Relation (field) | N/A | Relation to articlegroup |
| article | Relation (field) | N/A | Relation to article |
| item | Text | 50 | Article number |
| description | Text | 256 | Description of the deal article |
| quantity | Integer | N/A | Number of items |
| unit | Text | 32 | st, h etc. |
| vat | Decimal | N/A | VAT percentage expressed as decimal number, e.g. 0.25 |
| costprice | Decimal | N/A | Cost price for one unit excl. VAT |
| grossmarginratio | Decimal | N/A | Gross margin ratio expressed as percent, e.g. 45 |
| incomeaccount | Text | 32 | Used if the dealarticle should be connected to an income account |
| price_per_unit | Decimal | N/A | Price for one unit excl. VAT |
| discountperunit | Decimal | N/A | Discount for one unit excl. VAT |
| discountpercent | Decimal | N/A | Discount for unit in percent as decimal, e.g. 0.15 |
| discountclass | Option | N/A | How the discount was entered, options: amount, percent |
| subtotal | Decimal | N/A | Total before discounts excl. VAT. Calculated as quantity * price_per_unit |
| discount | Decimal | N/A | Total discount for all units excl. VAT. Calculated as quantity * discountperunit |
| totalvalue | Decimal | N/A | Excl. VAT. Calculated as subtotal - discount |
item, incomeaccount are included as a preparation for optional synchronization.
Order¶
Table database name: order
| Database Field Name | Field Type | Length | Description |
|---|---|---|---|
| order_number | Text | 64 | Unique order number |
| invoice_number | Text | 64 | Unique invoice number |
| invoice | Relation (field) | N/A | Relation to invoice synced by standard ERP connector |
| erp_db | Text | 32 | id to erp_db in Syncify's API |
| company | Relation (field) | N/A | Relation to company |
| customerid | Text | 128 | Customer ID for matching to company |
| deal | Relation (field) | N/A | Relation to deal |
| orderstatus | Option | N/A | The status of the order, see options below. |
| customer_reference | Text | 256 | Customer reference from ERP |
| coworker | Relation (field) | N/A | Company reference (Our reference) |
| order_date | Date | N/A | The order date |
| currency | Text | 3 | Currency |
| create_invoice_from_order | Yes/no | N/A | Set to true if invoice automatically should be created from order |
| subtotal | Decimal | N/A | Total before discounts excl. VAT. The sum of all order rows' subtotal. |
| discount | Decimal | N/A | Total discount for all units excl. VAT. The sum of all order rows' discount. |
| order_sum | Decimal | N/A | Order sum after discount excl. VAT. Calculated as subtotal - discount |
| order_vat | Decimal | N/A | Total VAT as amount. The sum of all order rows' VAT amounts. |
| order_total_sum | Decimal | N/A | Order sum after discount incl. VAT. Calculated as order_sum + order_vat |
| error_message | Text | 32 | Readonly, Set by Syncify if order status is set to 'error' |
| orderrow | Relation (tab) | N/A | All order rows belonging to the order |
invoice_number, invoice, erp_db, customerid, orderstatus, customer_reference, create_invoice_from_order, create_invoice_from_order, error_message are included as a preparation for optional synchronization.
Order Status Options¶
| Key | Description |
|---|---|
notready |
The order needs to be manually checked by the customer before it is fetched. |
readytosend |
Accepted by the customer. Fetched within 10 minutes. |
processing |
Fetched from Lime. Being processed by the Syncify service, not yet created in the ERP system. |
senttoerp |
Sent with success to the ERP system. |
error |
Something went wrong while fetching the order. See property 'error_message' on the order. |
Order Row¶
Orderrow is very similar to dealarticle and also copies and stores some data from the article (if selected) to provide a snapshot.
Table database name: orderrow
| Database Field Name | Field Type | Length | Description |
|---|---|---|---|
| order | Relation (field) | N/A | Relation to order |
| row_id | Text | 32 | External id (Syncify) |
| position | Integer | N/A | Position of the order row |
| articlegroup | Relation (field) | N/A | Relation to articlegroup |
| article | Relation (field) | N/A | Relation to article |
| item | Text | 50 | Article number |
| description | Text | 256 | Description of the order row |
| quantity | Integer | N/A | Number of items |
| unit | Text | 32 | st, h etc. |
| vat | Decimal | N/A | VAT percentage expressed as decimal number, e.g. 0.25 |
| costprice | Decimal | N/A | Cost price for one unit excl. VAT |
| grossmarginratio | Decimal | N/A | Gross margin ratio expressed as percent, e.g. 45 |
| incomeaccount | Text | 32 | Used if the order row should be connected to an income account |
| price_per_unit | Decimal | N/A | Price for one unit excl. VAT |
| discountperunit | Decimal | N/A | Discount for one unit excl. VAT |
| discountpercent | Decimal | N/A | Discount for unit in percent as decimal, e.g. 0.15 |
| discountclass | Option | N/A | How the discount was entered, options: amount, percent |
| subtotal | Decimal | N/A | Total before discounts excl. VAT. Calculated as quantity * price_per_unit |
| discount | Decimal | N/A | Total discount for all units excl. VAT. Calculated as quantity * discountperunit |
| totalvalue | Decimal | N/A | Excl. VAT. Calculated as subtotal - discount |
row_id, item, incomeaccount are included as a preparation for optional synchronization.
Invoice¶
| Database Field Name | Field Type | Length | Description |
|---|---|---|---|
| order | Relation (tab) | N/A | Relation to order |
invoice table is included as a preparation for optional synchronization.
Terminology & Calculations¶
Total, Subtotal & Vat¶
- subtotal is the total price before any discounts
- discount is the amount to discount
- total is the total price after discounts (subtotal - discount)
- vat is of course always based on the total, because the total is what's taxable
Gross Margin Ratio¶
The gross margin ratio is calculated using the following formula:
(price_per_unit - discountperunit - costprice) / (price_per_unit - discountperunit) * 100